PYTHON1.day19




1.表结构调整(alter table)
   1)添加字段
     - 在最后添加字段:alter table 表名 add 字段名 类型
     - 在最前添加字段:alter table 表名 add 字段名 类型 first
     - 在指定位置添加字段:alter table 表名 add 字段名 类型 after 字段名

    eg:
        create table student(stu_no varchar(32),stu_name varchar(128));

   2)添加字段
     alter table student add age int;--添加到最后
     alter table student add id int first;--添加到最前

     --在stu_name 后面添加tel_no字段
     alter table student add tel_no varchar(32) after stu_name;

   3)修改字段
     - 修改类型:alter table 表名 modity 字段名 类型(宽度)
     -修改名称:alter table 表名 change 原字段名 新字段名 类型(宽度)

     -eg:
         --修改学生名称长度64
         alter table student modify stu_name varchar(64);
         --修改age 为stu_age
         alter table student change age stu_age int;

  4)删除字段
     - 语法:alter table 表名 drop 字段名

     - eg:alter table student drop id;   
    
2.约束(constraint)
     1)什么是约束
       - 为保证数据正确性,完整性,一致性,数据必须遵循的规则
     2)约束类型
       -非空约束:字段的值不能为空
       -唯一性约束:字段值唯一
       -主键约束:字段作为主键,非空,唯一性约束
       -默认值:未填写值时,设置默认值
       -自动增加:字段值自动增加
       -外键约束
      
     3)非空约束(not null)
       - 指定字段的值不能为空,如果插入时该字段值为空,则报错,无法插入
       -语法:字段名称 数据类型(宽度)not null
       - eg:
           creat table customer(cust_no varchar(32) not null,cust_name varchar(128) not null,tel_no varchar(32) not null)default charset=utf8;

           --插入一笔带空值的数据
           insert into customer(cust_no,cust_name)values('c0001','Jerry');
     4)唯一约束(unique)
       - 该字段的唯一,不重复   
       - 语法:字段名称 数据类型 unique
       -eg: create table customer(cust_no varchar(32)unique,cust_name varchar(128) not null,tel_no varchar(32) not null)default charset=utf8;
            insert into customer_new values('c001','Jerry','123456');
            insert into customer_new values('c001','tom','123456');

     5)主键(primary key,简写pk)
       - 主键用来唯一标识表中的一笔记录,非空,唯一主键和一笔数据有唯一对应关系,
         一个表最多只有一个主键
         可以单个字段作为主键,也可以多个字段共同构成主键
       -语法:字段名称 类型(宽度)primary key
       -eg:
         create table customer0(cust_no varchar(32)primary key,cust_name varchar(128) not null,tel_no varchar(32) not null)default charset=utf8;
         insert into customer0 values('c001','Jerry','123456');
         insert into customer0 values(Null,'tom','123456');

          
     6)默认值(default)
       - 指定某个字段的默认值,如果插入一笔数据,该字段没有值,系统自动填写一个默认值
       - 语法:字段名称:类型(宽度)default 默认值     
       eg:
       create table customer1(cust_no varchar(32)primary key,cust_name varchar(128) not null,tel_no varchar(32) not null,status tinyint default 0)default charset=utf8;
        insert into customer1(cust_no,cust_name,tel_no) values('c001','Jerry','123456');

         
     7)自动增长(auto_increment)
        - 指定为自动增长的字段,插入时不需要设置值,系统在最大值基础上加1,可以和主键共同使用
        -语法:字段名称 数据类型(宽度)auto_increment
        -示例:
             create table ai_test(id int primary key auto_increment,name varchar(32));
             insert into ai_test values(null,'tom');
         
     8)外键约束
         - 什么是外键:在当前表中不是主键,在另一个表中是主键
         - 外键的作用:保证数据一致性,完整性
         - 使用外键的条件
           a)表的存储引擎类型为innodb
           b)被参照字段在外表中必须是主键
           c)当前表和外表中类型必须一致
         -语法
             constraint 外键名称 foreign key (当前表字段)
             reference 参照表(参照字段)

         -eg:   
            create table account(acct_no varchar(32)primary key,
            cust_no varchar(32) not null,
            constraint fk_cust_no foreign key(cust_no) references customer1(cust_no)
            )default charset=utf8;
   
           
         --在account表中插入cust_no 为‘c0001’
         -- 的数据,插入失败(account参照了一个不存在的实体)   
          insert into account values('62234560001','c0001');   
          --在customer表中插入一笔数据(cust_no为‘c0001’以满足account参照完整性    ,在执行上面的插入语句,则可以成)
          insert into customer(cust_no,cust_name,tel_no)values('c0001','Jerry','13511220003');
          --删除customer 表中cust_no 为‘c0001’实体,
          --报错,因为删除以后又会造成参照不完整
            delect from cu

         
3.索引
     1)什么是索引
       - 索引是提高查询效率的一种技术(相当于一本字典的索引或目录)   
       - 索引是一种单独存放的数据结构,包含着数据表中所有的记录的引用指针
       - 根据索引能快速找到数据所在位置
       - 通过避免全表扫描提高检索效率
     2)索引类别
        - 普通索引,唯一索引
        - 单列索引,组合索引

     3)如何创建索引
       - 语法:
         index|unique|primary key(字段名称)
   
       - 说明:
         index :创建普通索引
         unique:创建唯一索引
         primary key :主键,自动成为唯一索引
        
       - 示例:
             创建交易流水表,在流水号上创建唯一索引
             create table acct_trans_detail(trans_sn varchar(32) not null,
             trans_date datetime not null,acct_no varchar(32)not null,
             trans_type int null,
             amt decimal(10,2) not null,
             unique(trans_sn),
             index(trans_date)
             );
             insert into acct_trans_detail values('20180101',now(),'622000001',1,1000);
            
             show index from acct_trans_detail;
             show index from acct_trans_detailG
           
       - 示例:通过修改的方式创建索引
             在acct_trans_detail表acct_no字段上创建普通索引
             acct_trans_detail 表名
             acct_no 字段名称
              alter table acct_trans_detail add index idx_acct_no(acct_no);
              或
              create index idx_acct_no on acct_trans_detail(acct_no);

4)删除索引
   - 语法:drop index 索引名称 on 表名
   - 示例:drop index idx_acct_no on acct_trans_detail;
  
  5)索引的优点
     提高查询效率
     唯一索引 能够保证数据的唯一性
     在使用分组,排序等字句时,能提高效率
    
     缺点:
         索引需要额外的存储空间
         维护索引结构需要额外的开销
         会降低增,删,改的效率
        
     6)索引使用原则
       - 使用恰当的索引,索引不是越多越好
       - 避免对经常更新的表使用过多索引
       - 在经常作为查询条件的字段上建立索引
       -字段值太少不宜使用索引(如性别,状态)
       -主键和唯一索引查询效率较高
       -在经常排序的字段上使用索引
       - 数据量太少不适合使用索引
       - 二进制类型字段不适合使用索引
      
4.表的复制,重命名
   1)复制
     - 完全复制
      create table acct_new select * from acct;

     - 部分复制(只复制满足条件的)
      create table acct_new select * from acct where balance<2000;

     - 只复制结构,不复制数据(没有满足条件的数据)
       create table acct_new select * from acct where 1=0;

     注意:该方法复制表,不会复制建的属性   
    
   2)重命名
     - 格式:
           alter table 原表名 rename to 新表名

     - 示例:
           alter table acct rename to acct_new;

 
  作业:
     1.修改表orders,在order_id 添加主键
     2.在cust_id,order_date。products_num字段添加非空约束
     3.在status字段上添加默认值,默认为1
     4.在order_date字段上添加普通索引
    
   1.alter  table orders modify order_id varchar(32) primary key;
     或
     alter table orders add primary key(order_id)
2.
   alter table orders modify cust_id varchar(32) not null;
3.
alter table order modify status enum('1','2','3','4','5','6','9') default 1;
4.
creat index idx_order_date on orders(order_date);

原文地址:https://www.cnblogs.com/shengjia/p/10385582.html