为了防止数据的准确性和可靠性

防止不符合规范的数据进入数据库，在用户对数据进行插入、修改、删除等操作时，DBMS自动按照一定的约束条件对数据进行监测，使不符合规范的数据不能进入数据库，以确保数据库中存储的数据正确、有效、相容

约束条件和数据类型的宽度一样都是可选参数，主要分为：

  1. NOT NULL # 非空约束，指定某列（字段）不能为空。比如学生姓名
  2. UNIQUE # 唯一约束，指定某列或者几列的组合不能重复，唯一性，可以为空。比如学生ID不可重复
  3. primary key # 主键，用于保证该列的值可以唯一的标识该列记录，并且非空。比如学生ID
  4. foreign key # 外键，用于限制两张表的关系，用于保证该字段的值必须来自于主表的关联列的值，在从表添加外键约束，用于主表某列的值。比如学生表的class_id字段，引用于教室表的主键id
  5. default # 用于保证该字段有默认值 比如性别

NOT NULL

NULL 表示 空，非字符串

NOT NULL 表示非空

  1. # 创建score表 字段为id int类型  并且插入数据时该字段不能为空
  2. create table score(id int NOT NULL);

DEFAULT

我们约束某一列不为空，如果这一列中经常有重复的内容，就需要我们频繁的插入，这样会给我们的操作带来新的负担，于是就出现了默认值的概念

默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值

  1. # 创建score表 字段id int数据类型 默认不能为空
  2. # 字段 id2 int数据类型 并且不能为空 如果没有指定默认值则为222
  3. create table score(id int NOT NULL ,id2 int NOT NULL default 222);

设置严格模式：

  • 不支持对not null字段插入null值
  • 不支持对自增长字段插入”值
  • 不支持text字段有默认值
  1. # 直接在mysql中生效(重启失效):
  2. set sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";
  4. # 配置文件添加(永久失效)：
  5. sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

UNIQUE

唯一约束，指定某列或者几列组合不能重复

示例：
  1. # 方法一：
  2. create table department1(
  3. id int,
  4. name varchar(20) unique,
  5. comment varchar(100)
  6. );
  8. # 方法二：
  9. create table department2(
  10. id int,
  11. name varchar(20),
  12. comment varchar(100),
  13. unique(name)
  14. );

not null 和 uniqce联合使用

这样使用 如果是表中的第一个字段，也就相当于主键效果

主键： 第一个字段 不能为空 并且不重复

  1. create table t1(id int not null unique);

联合唯一
  1. create table service(
  2. id int primary key auto_increment,
  3. name varchar(20),
  4. host varchar(15) not null,
  5. port int not null,
  6. unique(host,port) #联合唯一
  7. );

PRIMARY KEY

主键 为了保证表中的每一条数据都是该字段的唯一值。
他是用来独一无二的确认表中的每一行数据

主键可以包含一个字段或者多个字段，但是一张表只能由一个主键
当主键包含多个栏位时，称为组合key（Composite key）,也可以称为联合主键

主键可以在创建新表时(create table 表时) 添加某个字段位primary key
或者在修改的时候 使用alter table来修改表的字段为主键

表中的第一个字段 + not null + unique = primary key

单字段主键
  1. # 方法一
  2. # not null + unique
  4. create table test_table(
  5. id int NOT NULL unique,  # 相当于主键
  6. name char(20) NOT NULL 
  7. )
  9. # 方法二
  10. # 在字段后面加上 primary key
  12. create table test_table1(
  13. id int primary key,
  14. name char(20) NOT NULL 
  15. );
  17. # 方法三
  18. # 在所有字段后单独定义primary key
  20. create table test_table3(
  21. id int,
  22. name char(20) NOT NULL,
  23. primary key(id)
  24. )
  26. # 方法四
  27. # 给已经建成的表添加约束 
  29. create table test_table4(
  30. id int,
  31. name char(20)
  32. );
  34. alter table test_table4 modify id int primary key;

多字段主键
  1. create table test(
  2. id int,
  3. name char(20),
  4. primary key(id,name)
  5. );

AUTO_INCREMENT

约束字段为自动增长，被约束的字段必须同时被key约束

使用方法
  1. create table student(
  2. id int primary key auto_increment,
  3. name char(10) not null,
  4. sex enum('男','女') default '男'
  5. );
  7. insert into student values('马海燕');
  8. insert into student values('马海阳');
  9. insert into student values(4,'asb','男');
  12. # 对于自增的字段，在用delete删除后，再插入值，该字段仍然按照删除前的位置继续增长
  13. delete from student where id = 2;
  14. insert into student(name) values('赵振');
  16. # 显示
  18. mysql> select * from student;
  19. +----+------+------+
  20. | id | name | sex  |
  21. +----+------+------+
  22. |  1 | 马   | 男   |
  23. |  3 | 赵   | 男   |
  24. +----+------+------+
  25. 2 rows in set (0.00 sec)
  27. # 可以通过show create table student 查看下一次自增的数字
  28. # AUTO_INCREMENT=4
  30. mysql> show create table student;
  31. +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  32. | Table   | Create Table                                                                                                                                                                                                    |
  33. +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  34. | student | CREATE TABLE `student` (
  35.   `id` int(11) NOT NULL AUTO_INCREMENT,
  36.   `name` char(1) NOT NULL,
  37.   `sex` enum('男','女') DEFAULT '男',
  38.   PRIMARY KEY (`id`)
  39. ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8    |
  40. +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  41. 1 row in set (0.00 sec)
  44. # 应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它
  46. mysql> truncate student;
  47. Query OK, 0 rows affected (0.01 sec)
  49. mysql> insert into student(name) values('egon');
  50. Query OK, 1 row affected (0.01 sec)
  52. mysql> select * from student;
  53. +----+------+------+
  54. | id | name | sex  |
  55. +----+------+------+
  56. |  1 | egon | male |
  57. +----+------+------+
  58. row in set (0.00 sec)

offset偏移量
  1. # 在创建完表后，修改自增字段的起始值
  3. alter table student auto_increment=3;
  5. mysql> insert into student(name) values('alex');
  6. Query OK, 1 row affected, 1 warning (0.35 sec)
  8. mysql> select * from student;
  9. +----+------+------+
  10. | id | name | sex  |
  11. +----+------+------+
  12. |  1 | 马   | 男   |
  13. |  3 | 赵   | 男   |
  14. |  5 | a    | 男   |
  15. +----+------+------+
  16. 3 rows in set (0.00 sec)
  19. #也可以创建表时指定auto_increment的初始值，注意初始值的设置为表选项，应该放到括号外
  20. create table student(
  21. id int primary key auto_increment,
  22. name varchar(20),
  23. sex enum('male','female') default 'male'
  24. )auto_increment=3;
  29. #设置步长
  30. sqlserver：自增步长
  31.     基于表级别
  32.     create table t1（
  33.         id int。。。
  34.     ）engine=innodb,auto_increment=2 步长=2 default charset=utf8
  36. mysql自增的步长：
  37.     show session variables like 'auto_inc%';
  39.     #基于会话级别
  40.     set session auth_increment_increment=2 #修改会话级别的步长
  42.     #基于全局级别的
  43.     set global auth_increment_increment=2 #修改全局级别的步长（所有会话都生效）
  46. #！！！注意了注意了注意了！！！
  47. If the value of auto_increment_offset is greater than that of auto_increment_increment, the value of auto_increment_offset is ignored. 
  48. 翻译：如果auto_increment_offset的值大于auto_increment_increment的值，则auto_increment_offset的值会被忽略 ，这相当于第一步步子就迈大了，扯着了蛋
  49. 比如：设置auto_increment_offset=3，auto_increment_increment=2
  54. mysql> set global auto_increment_increment=5;
  55. Query OK, 0 rows affected (0.00 sec)
  57. mysql> set global auto_increment_offset=3;
  58. Query OK, 0 rows affected (0.00 sec)
  60. mysql> show variables like 'auto_incre%'; #需要退出重新登录
  61. +--------------------------+-------+
  62. | Variable_name            | Value |
  63. +--------------------------+-------+
  64. | auto_increment_increment | 1     |
  65. | auto_increment_offset    | 1     |
  66. +--------------------------+-------+
  70. create table student(
  71. id int primary key auto_increment,
  72. name varchar(20),
  73. sex enum('male','female') default 'male'
  74. );
  76. mysql> insert into student(name) values('egon1'),('egon2'),('egon3');
  77. mysql> select * from student;
  78. +----+-------+------+
  79. | id | name  | sex  |
  80. +----+-------+------+
  81. |  3 | egon1 | male |
  82. |  8 | egon2 | male |
  83. | 13 | egon3 | male |
  84. +----+-------+------+
  86. 步长:auto_increment_increment,起始偏移量:auto_increment_offset

FOREIKEY 外键

假设我们要描述所有公司的员工，需要描述的属性有这些 ： 工号 姓名 部门

公司有3个部门，但是有1个亿的员工，那意味着部门这个字段需要重复存储，部门名字越长，越浪费

解决方法： 我们完全可以定义一个部门表 然后让员工信息表关联该表，如何关联，即foreign key

  1. mysql> create table departments (dep_id int(4),dep_name varchar(11));
  2. Query OK, 0 rows affected (0.02 sec)
  4. mysql> desc departments;
  5. +----------+-------------+------+-----+---------+-------+
  6. | Field    | Type        | Null | Key | Default | Extra |
  7. +----------+-------------+------+-----+---------+-------+
  8. | dep_id   | int(4)      | YES  |     | NULL    |       |
  9. | dep_name | varchar(11) | YES  |     | NULL    |       |
  10. +----------+-------------+------+-----+---------+-------+
  11. 2 rows in set (0.00 sec)
  13. # 创建外键不成功
  14. mysql> create table staff_info (s_id int,name varchar(20),dep_id int,foreign key(dep_id) references departments(dep_id));
  15. ERROR 1215 (HY000): Cannot add foreign key 
  17. # 设置dep_id非空，仍然不能成功创建外键
  18. mysql> alter table departments modify dep_id int(4) not null;
  19. Query OK, 0 rows affected (0.02 sec)
  20. Records: 0  Duplicates: 0  Warnings: 0
  22. mysql> desc departments;
  23. +----------+-------------+------+-----+---------+-------+
  24. | Field    | Type        | Null | Key | Default | Extra |
  25. +----------+-------------+------+-----+---------+-------+
  26. | dep_id   | int(4)      | NO   |     | NULL    |       |
  27. | dep_name | varchar(11) | YES  |     | NULL    |       |
  28. +----------+-------------+------+-----+---------+-------+
  29. 2 rows in set (0.00 sec)
  31. mysql> create table staff_info (s_id int,name varchar(20),dep_id int,foreign key(dep_id) references departments(dep_id));
  32. ERROR 1215 (HY000): Cannot add foreign key constraint
  34. # 当设置字段为unique唯一字段时，设置该字段为外键成功
  35. mysql> alter table departments modify dep_id int(4) unique;
  36. Query OK, 0 rows affected (0.01 sec)
  37. Records: 0  Duplicates: 0  Warnings: 0
  39. mysql> desc departments;                                                                                                       +----------+-------------+------+-----+---------+-------+
  40. | Field    | Type        | Null | Key | Default | Extra |
  41. +----------+-------------+------+-----+---------+-------+
  42. | dep_id   | int(4)      | YES  | UNI | NULL    |       |
  43. | dep_name | varchar(11) | YES  |     | NULL    |       |
  44. +----------+-------------+------+-----+---------+-------+
  45. 2 rows in set (0.01 sec)
  47. mysql> create table staff_info (s_id int,name varchar(20),dep_id int,foreign key(dep_id) references departments(dep_id));
  48. Query OK, 0 rows affected (0.02 sec

操作实例
  1. # 表类型必须是innodb存储引擎，且被关联的字段，即references指定的另外一个表的字段，必须保证唯一
  2. create table department(
  3. id int primary key,
  4. name varchar(20) not null
  5. )engine=innodb;
  7. # dpt_id外键，关联父表（department主键id），同步更新，同步删除
  8. create table employee(
  9. id int primary key,
  10. name varchar(20) not null,
  11. dpt_id int,
  12. foreign key(dpt_id)
  13. references department(id)
  14. on delete cascade  # 级连删除
  15. on update cascade # 级连更新
  16. )engine=innodb;
  19. # 先往父表department中插入记录
  20. insert into department values
  21. (1,'教质部'),
  22. (2,'技术部'),
  23. (3,'人力资源部');
  26. # 再往子表employee中插入记录
  27. insert into employee values
  28. (1,'yuan',1),
  29. (2,'nezha',2),
  30. (3,'egon',2),
  31. (4,'alex',2),
  32. (5,'wusir',3),
  33. (6,'李沁洋',3),
  34. (7,'皮卡丘',3),
  35. (8,'程咬金',3),
  36. (9,'程咬银',3)
  37. ;
  40. # 删父表department，子表employee中对应的记录跟着删
  41. mysql> delete from department where id=2;
  42. Query OK, 1 row affected (0.00 sec)
  44. mysql> select * from employee;
  45. +----+-----------+--------+
  46. | id | name      | dpt_id |
  47. +----+-----------+--------+
  48. |  1 | yuan      |      1 |
  49. |  5 | wusir     |      3 |
  50. |  6 | 李沁洋    |      3 |
  51. |  7 | 皮卡丘    |      3 |
  52. |  8 | 程咬金    |      3 |
  53. |  9 | 程咬银    |      3 |
  54. +----+-----------+--------+
  55. 6 rows in set (0.00 sec)
  58. # 更新父表department，子表employee中对应的记录跟着改
  59. mysql> update department set id=2 where id=3;
  60. Query OK, 1 row affected (0.01 sec)
  61. Rows matched: 1  Changed: 1  Warnings: 0
  63. mysql> select * from employee;
  64. +----+-----------+--------+
  65. | id | name      | dpt_id |
  66. +----+-----------+--------+
  67. |  1 | yuan      |      1 |
  68. |  5 | wusir     |      2 |
  69. |  6 | 李沁洋    |      2 |
  70. |  7 | 皮卡丘    |      2 |
  71. |  8 | 程咬金    |      2 |
  72. |  9 | 程咬银    |      2 |
  73. +----+-----------+--------+
  74. 6 rows in set (0.00 sec)

on delete
  1.    . cascade方式
  2. 在父表上update/delete记录时，同步update/delete掉子表的匹配记录 
  4.    . set null方式
  5. 在父表上update/delete记录时，将子表上匹配记录的列设为null
  6. 要注意子表的外键列不能为not null  
  8.    . No action方式
  9. 如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作  
  11.    . Restrict方式
  12. 同no action, 都是立即检查外键约束
  14.    . Set default方式
  15. 父表有变更时,子表将外键列设置成一个默认的值 但Innodb不能识别