索引与完整性约束（note6）

第七周（mysql索引与完整性）

1. 索引

1. 索引的分类：

   • 普通索引(index)：最基本的索引类型，没有唯一性之类的限制，创建普通索引的关键字是index
   • 唯一性索引(unique)：与普通索引基本相同，唯一区别就是所有值只能出现一次
   • 主键(primary key)：主键是一种唯一性索引
   • 全文索引(fulltext)：mysql支持全文检索和全文索引，全文索引只能在varchar或text类型的列上创建

2. create index语句创建：可以在一个已有表上创建，一个表可以创建多个索引，如果一个索引列可包含多个列，中间用逗号隔开，但它们属于同一个表，这样的索引叫作重复索引

   #	例子一：
   #根据xs表的学号列上的前5个字符创建一个升序索引xh_xs
   USE xscj;
   CREATE INDEX xh_xs
   ON xs(学号(5) ASC);
   
   #在xs_kc表的学号列和课程号列上建立一个复合索引xskc_in
   CREATE INDEX xskc_in
   ON xs_kc(学号,课程号);
   
   
   
   

3. 在建立表是创建索引：索引也可以在创建表时一起创建

   #	例子二：
   #在mytest数据库中创建成绩cj表,学号和课程号的联合主键，并在成绩列上创建索引
   USE mytest;
   CREATE TABLE xs_kc(
   学号 CHAR(6) NOT NULL,
   课程号 CHAR(3) NOT NULL,
   成绩 TINYINT(1),
   学分 TINYINT(1),
   PRIMARY KEY(学号,课程号),
   INDEX cj(成绩)
   );
   
   SHOW INDEX FROM xs_kc;  //查看命令执行结果
   

4. alter table 语句创建

   #	例子三：
   #在xs表的姓名列上创建一个非唯一的索引
   USE xscj;
   ALTER TABLE xs ADD INDEX xs_xm USING BTREE(姓名);
   
   #以xs表为例(假设表中主键未定),创建这样的索引,以加速表的检索速度
   ALTER TABLE xs ADD INDEX mark(出生日期,性别);
   

5. 删除索引

   #当一个索引不在需要的时候，可以用 drop index 语句或者 alter table 语句删除它
   （1）使用 drop index： drop index 索引名 on 表名
   （2）使用 alter table： 
   		alter [ignore] table 表名 drop primary key	//删除主键
   		alter [ignore] table 表名 索引名			//删除索引
   		alter [ignore] table 表名 drop foreign key fk symbol //删除外键
   		
   #其中，drop 子句可以删除各种类型的索引。用户使用 drop primary key 字句时不需要提供索引名称，因为一个表只有一个主键
    
    
    #	例子四：
    #删除xs表上的mark索引
    #删除xs表上的mark索引
   DROP INDEX mark ON xs;
   
   ALTER TABLE xs DROP INDEX mark;
   
   #如果从表中删除列，索引可能会受影响。如果所删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将会被删除。
   

2.Mysql 数据完整性约束

1. 主键约束

   • 作为列的完整性约束时，只需要在列定义的时候加上关键字primary key；

   • 作为表的完整性约束时，需要在语句最后加上一条primary key(列…)子句

     #创建表xs1,将姓名定义为主键
     CREATE TABLE xs1(
     学号 VARCHAR(6) NULL,
     姓名 VARCHAR(8) NOT NULL PRIMARY KEY,
     出生日期 DATETIME
     );
     
     #当表中的主键为复合主键时，只能定义为表的完整性约束
     #创建course表来记录每门课程的学生学号、姓名、课程号、学分和毕业日期。
     #其中学号、课程号和毕业日期构成复合主键
     CREATE TABLE  course(
     学号 VARCHAR(6) NOT NULL,
     姓名 VARCHAR(8) NOT NULL,
     毕业日期 DATE NOT NULL,
     课程号 VARCHAR(3),
     学分 TINYINT,
     PRIMARY KEY(学号,姓名,毕业日期)
     );
     

2. 替代键约束

3. 参照完整性约束