字段类型的约束条件

一、约束条件

1、unsigned

  1. unsigned
  2.     无符号
  3.         id int unsigned

2、zerofill

  1. zerofill
  2.     0填充
  3.         id int zerofill

3、not null

  1. not null
  2.     非空(不能为空)
  3. 案例：
  4.     create table t1(id int,name varchar(16) not null);
  5.     insert into t1(name) values("Yie");        #成功
  6.     insert into t1(id) values(1);            #报错

4、default

  1. default
  2.     默认值(用户给了就使用用户的，不给就使用默认的)
  3. 案例：
  4.     create table t2(id int,name varchar(16) default "匿名用户");
  5.     insert into t2(id) values(1);        #name字段显示 '匿名用户'
  6.     insert into t2 values(2,"Yie"),(3,"zpx");

5、unique

  1. unique
  2.     唯一(保证字段(一个、多个)在整个表中没有重复的数据)
  3. # 单列唯一
  4.     create table t3(id int,name varchar(16) unique);
  5.     insert into t3 values(1,"Yie"),(2,"Yie"); #name字段不唯一报错
  6.     insert into t3 values(1,"Yie"),(2,"zpx");     #成功
  7. # 联合唯一
  8.     create table server(
  9.         id int,
  10.         host varchar(32),
  11.         port varchar(32),
  12.         unique(host,port)
  13.     );
  14.     这时，插入的记录两两组合唯一才行。
  15.     insert into server(host,port) values(127.0.0.1,3306),(127.0.0.1,8080); #成功
  16.     insert into server(host,port) values(127.0.0.1,3306);        #重复,失败

6、primary key

  1. primary key
  2.     主键
  3.         1.但从约束条件上而言主键相当于not null + unique(非空且唯一)
  4.         2.主键的功能目前简单的理解为能够加快数据的查询速度相当于书的目录
  5.         3.InnoDB存储引擎规定每张表都必须有且只有一个主键
  6.             2.1.表中没有任何的主键和其他约束条件
  7.                 InnoDB默认会采用一个隐藏字段作为表的主键
  8.             2.2.表中没有主键但是有非空且唯一的字段
  9.                 InnoDB会自动将该字段升级为主键
  10.         结论:每张表都必须要有一个id(sid nid uid gid等等任一id)字段并且该字段就是主键
  12. 和unique一样，也分单列和联合主键
  13. # 单列主键
  14.     案例一：# 使用primary key，将id字段设置为主键
  15.     mysql> create table t4 (
  16.         id int primary key,
  17.         name char(8) 
  18.     );
  19.     案例二：# 不使用primary key，但是有费控且唯一的字段，InnoDB自动将该字段升级为主键
  20.     mysql> mysql> create table t5(
  21.         id int,
  22.         name char(8),
  23.         phone_num int not null unique,
  24.         age int not null unique,
  25.         birth int not null unique
  26.     );
  27.     mysql> desc t5;
  28.     +-----------+---------+------+-----+---------+-------+
  29.     | Field     | Type    | Null | Key | Default | Extra |
  30.     +-----------+---------+------+-----+---------+-------+
  31.     | id        | int(11) | YES  |     | NULL    |       |
  32.     | name      | char(8) | YES  |     | NULL    |       |
  33.     | phone_num | int(11) | NO   | PRI | NULL    |       |
  34.     | age       | int(11) | NO   | UNI | NULL    |       |
  35.     | birth     | int(11) | NO   | UNI | NULL    |       |
  36.     +-----------+---------+------+-----+---------+-------+
  37.     5 rows in set (0.05 sec)
  38.     #将age，phone_num，birth字段设置为not null和unique，InnoDB自动将最靠前的字段升级为primary。
  40. # 联合主键
  41.     案例三：# 将手机号和QQ号设置为联合主键
  42.     create table t6(
  43.         id int,
  44.         name char(8),
  45.         phone_num bigint,
  46.         QQ bigint,
  47.         primary key(phone_num,QQ)
  48.     );
  49.     mysql> desc t6;
  50.     +-----------+------------+------+-----+---------+-------+
  51.     | Field     | Type       | Null | Key | Default | Extra |
  52.     +-----------+------------+------+-----+---------+-------+
  53.     | id        | int(11)    | YES  |     | NULL    |       |
  54.     | name      | char(8)    | YES  |     | NULL    |       |
  55.     | phone_num | bigint(20) | NO   | PRI | 0       |       |
  56.     | QQ        | bigint(20) | NO   | PRI | 0       |       |
  57.     +-----------+------------+------+-----+---------+-------+
  58.     4 rows in set (0.00 sec)

7、auto_increment

  1. auto_increment
  2.     自增(专门配合主键一起使用的 让主键能够自增)
  3. 案例：#创建一个自增的整数主键id
  4.     create table t7(
  5.         id int primary key auto_increment,
  6.         name varchar(32)
  7.     );
  10. 主键的自增特性
  11. #主键的自增是不会收到delete from删除操作的影响
  12.     也就是使用delete删除某条记录，下一次再插入记录的时候，不会依据跟现有表的id增加
  13. #truncate既可以清空表数据也会重置主键值
  14.     使用truncat清空表的时候，再插入记录，id会从1开始。

8、外键foreign key # 很重要

8.1使用外键的原因：

  1. 当一张表，出现'重复'数据，造成存取数据'速度缓慢'，并且'浪费磁盘空间'的时候，可以引入一个概念，两张表建立关系。
  3. 例如，有这么一张员工表
  4.     id    name    age     dep_name    dep_desc
  5.     1    yly        18        外交部           搞外交
  6.     2    zpx        28        教学部           教书育人
  7.     3    aaa        38        教学部           教书育人
  8.     4    bbb        48        安保部           保家卫国
  9.     5    ccc     58        财务部           发工资
  10. 显示了每个员工的信息，以及所处部门的信息。
  11. """
  12. 上述表不合理之处
  13.     1.表内部数据混乱(可忽略)
  14.     2.反复的录入重复数据(可忽略)
  15.     3.修改数据太过繁琐  浪费磁盘空间(可忽略)
  16.     4.极大地影响了操作数据的效率
  17. """
  18. 可以拆分为两张基本表，员工表，部门表
  20. id    name    age 
  21. 1    yly        18
  22. 2    zpx        28
  23. 3    aaa        38
  24. 4    bbb        48
  25. 5    ccc     58
  27. id    dep_name    dep_desc
  28. 1    外交部        搞外交
  29. 2    教学部        教书育人
  30. 3    安保部        保家卫国
  31. 4    财务部        发工资
  33. """拆分完之后解决了上述四个缺陷"""
  35. 外键
  36.     用来记录表与表之间的关系

8.2如何判断两张表的关系

  1. 1.多对一：'多条记录对一条记录'
  2.     多的一方设置外键
  3. 2.多对多：'多条记录对多条记录'
  4.     创建第三张表，在第三张表上创建外键
  5. 3.一对一：'一条记录对一条记录'
  6.     一对一就没有必要使用外键
  7. 4.'没有关系'
  9. """查找表关系:换位思考"""
  10. 书籍表与出版社表
  11.     1.先站在书籍表
  12.         问:一本书能够对应多个出版社
  13.         答:不可以
  14.     2.再站在出版社表
  15.         问:一个出版社能否对应多本书
  16.         答:可以
  17.     结论:一个可以一个不可以 表关系为"多对一"
  18.            那么外键字段建在"多"的一方

8.3外键格式

  1. 格式：
  2.     [外键字段名] [字段类型] [约束条件],
  3.     foreign key(外键字段名) references 外表名(id)
  4.     on update cascade
  5.     on delete cascade,
  7. 容易出错的地方：
  8.     1、foreign key是需要逗号分隔;
  9.     2、references是复数形式，有s；
  10.     3、连接外表的id必须是主键。
  12. 外键约束：
  13.     1.在创建表的时候需要先创建被关联表(没有外键的表)
  14.     2.在写入数据的时候也需要先写入被关联表(没有外键的表)
  15.     3.被关联表里面的数据无法直接删除和修改关联字段的操作
  16. # 级联更新 级联删除
  17. create table emp(
  18.     id int primary key auto_increment,
  19.     name varchar(32),
  20.     age int,
  21.     dep_id int,
  22.     foreign key(dep_id) references dep(id) 
  23.     on update cascade 
  24.     on delete cascade
  25. );
  26. create table dep(
  27.     id int primary key auto_increment,
  28.     name varchar(32)
  29. );

8.4多对一

  1. # 针对具有表关系的SQL建议先写普通字段 最后再考虑外键字段
  2. create table publish(
  3.     id int primary key auto_increment,
  4.     name varchar(32)
  5. );
  6. create table book(
  7.     id int primary key auto_increment,
  8.     title varchar(32),
  9.     price int,
  10.     pub_id int,
  11.     foreign key(pub_id) references publish(id)
  12. );
  13. "注意：得先建立没有外键依赖的表"

8.5多对多

  1. 以书籍表与作者表为例
  2.     1.先站在书籍表的基础之上
  3.         问:一本书能否对应多个作者
  4.         答:可以
  5.     2.在站在作者表的基础之上
  6.         问:一个作者能否对应多本书
  7.          答:可以
  8.     结论:两个都可以那么表关系就是"多对多"
  9.         外键字段建在第三张关系表中
  11. # 先写普通字段之后在考虑外键字段
  12. '''错误的创建方式'''
  13. create table book1(
  14.     id int primary key auto_increment,
  15.     title varchar(32),
  16.     author_id int,
  17.     foreign key(author_id) references author1(id) 
  18.     on update cascade 
  19.     on delete cascade
  20. );
  21. create table author1(
  22.     id int primary key auto_increment,
  23.     name varchar(32),
  24.     book_id int,
  25.     foreign key(book_id) references book1(id) 
  26.     on update cascade 
  27.     on delete cascade
  28. );
  29. '''正确的创建方式'''
  30. create table book1(
  31.     id int primary key auto_increment,
  32.     title varchar(32)
  33. );
  34. create table author1(
  35.     id int primary key auto_increment,
  36.     name varchar(32)
  37. );
  38. create table book2author(
  39.     id int primary key auto_increment,
  40.     author_id int,
  41.     foreign key(author_id) references author1(id) 
  42.     on update cascade 
  43.     on delete cascade,
  44.     book_id int,
  45.     foreign key(book_id) references book1(id) 
  46.     on update cascade 
  47.     on delete cascade
  48. );
  50. 分析：
  51.     此时有三个表
  52.     1、book表:负责存储有哪些书及id号
  53.     2、作者表:负责存储有哪些作者及id号
  54.     3、关系表:可以清楚看到，哪些作者出了哪些书，哪些书对应着哪些作者

8.6 一对一

  1. # 简介
  2.     当一个表储存了很多数据，但有些数据是经常查询的，另外一部分数据是偶尔才查询，
  3.     可以拆分开为一对一的对应关系以加快存取效率。
  6. # 场景
  7.     QQ用户表
  8.     id        name    gender        age        |    phone_num        hobby
  9.     1        yly        male        18        |    152xxxx6666        swim、play
  10.     2        zpx        female        15        |    153xxxx6666        watch、dance
  11.     3        aaa        male        12        |    154xxxx6666        play
  12. 此时的QQ用户表，常用的是name,gender,age,想查看某个人的'详细信息'phone_num、hobby，需要另外再点击，这个时候就可以分为用户表和用户详细表，并且关系为一一对应。
  15. 外键字段建在哪里?
  16.     理论上建在任何一方都可以但是推荐建在查询频率较高的表中，并且外键需要设置为unique。
  18. create table user(
  19.     id int primary key auto_increment,
  20.     name varchar(32),
  21.     detail_id int unique,
  22.     foreign key(detail_id) references user_detail(id)
  23.     on update cascade
  24.     on delete cascade
  25. );
  26. create table user_detail(
  27.     id int primary key auto_increment,
  28.     addr varchar(32),
  29.     phone bigint
  30. );

二、查看最后一次插入的ID号：select last_insert_id();

  1. 查看最后一次插入的id号
  2. select last_insert_id();    #查看最后一次插入的主键id，当一次性查入多条时，只会显示第一个。

last_insert_id().jpg