表的基本约束

1. NOT NULL ：非空，用于保证该字段的值不能为空。
2. DEFAULT：默认值，用于保证该字段有默认值。
3. UNIQUE：唯一索引，用于保证该字段的值具有唯一性，可以为空。
4. PRIMARY KEY：主键，用于保证该字段的值具有唯一性并且非空。
5. FOREIGN KEY：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值，在从表添加外键约束，用于引用主表中某些的值。
6. INDEX : 索引,提高查询效率

not null/default/unique

举例:

添加约束

//创表时对护照加入唯一索引
create table db1.t43 (姓名 char(10),护照 char(18) unique);
//创表时加入非空和默认
mysql> create table db1.t31(
    -> name char(10) not null , 
    -> class char(7) default "nsd",
    -> likes set("money","game","film","music") not null  default "film,music" );
//原有的表中加入约束
alter table 表名 add primary key(表头);

 删除约束

alter table 表名 drop primary key;
//删除默认
alter table 表名 alter 表头 drop default;

primary key

主键的标志为PRI

• 表头值不允许重复，不允许赋NULL值
• 一个表中只能有一个primary key 表头
• 多个表头做主键，称为复合主键，必须一起创建和删除
• 主键标志PRI
• 主键通常与auto_increment连用
• 通常把表中唯一标识记录的表头设置为主键[行号表]

补充清空表内容命令区别

delete from 表名;

• 清空表内容,并不重置自增序列;

truncate from 表名;

• 彻底清空所有数据,并且重置自增序列;

添加主键的方法

创表,定义表头后跟primary key,或者定义完所有表头,后primary key(表头名,表头名.....)

删除主键

alter table 表名 drop primary key

foreign key

外键使用规则：

• 表存储引擎必须是innodb
• 表头数据类型要一致
• 被参照表头必须要是索引类型的一种(primary key)

• 被参考的表不能删除

作用：

• 插入记录时，表头值在另一个表的表头值范围内选择

创表中创外键命令格式

    create table   库.表(
    表头列表 , 
    foreign key(表头名)        #指定外键
    references 库.表(表头名)   #指定参考的表头名
    on update  cascade         #同步更新
    on  delete  cascade        #同步删除
    )engine=innodb;

 实现同步更新/删除

列如:有员工表(编号,姓名,部门编号),部门表(部门编号,部门名)

将员工表的部门编号设置为外键,外到部门表的部门编号

员工在更新人员数据过程中,如果部门编号在部门表不存在,则更新失败

部门表中部门编号修改,同步更新至员工表中的部门编号

部门表中某行被删除,则员工表中对应被删除的部门编号的员工随之被删除掉

查看外键命令

show create table 表名 \G;

删除外键

 alter table 表名 drop FOREIGN KEY  gz_ibfk_1;
//外键的别名需要从'show create table ...'获取

index

• 使用规则：

  • 一个表中可以有多个index
  • 任何数据类型的表头都可以设置索引
  • 表头值可以重复，也可以赋NULL值
  • 通常在where条件中的表头上设置Index
  • index索引标志MUL

  功能 :

• 提高检索效率

建表时创建索引的命令格式

    CREATE TABLE  库.表(
    字段列表 ,
    INDEX(字段名) ,
    INDEX(字段名) 
    );

查看索引详细信息

show index   from   表名;

删除索引

drop index 索引名 on 表名;

现存表中创建索引

create index 索引名 on 表名;

查看&分析sql查询施行

explain select * from tarena.user where name="sshd" \G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user  表名
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL      使用的索引名
      key_len: NULL
          ref: NULL
         rows: 27         查找的总行数  //经过index优化后变成比27还要少!!!
     filtered: 10.00
        Extra: Using where   额外说明
1 row in set, 1 warning (0.00 sec)