约束条件与数据类型的宽度一样，都是可选参数
作用：用于保证数据的完整性和一致性
主要分为：
PRIMARY KEY (PK) #标识该字段为该表的主键，可以唯一的标识记录
FOREIGN KEY (FK) #标识该字段为该表的外键
NOT NULL #标识该字段不能为空
UNIQUE KEY (UK) #标识该字段的值是唯一的
AUTO_INCREMENT #标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT #为该字段设置默认值
UNSIGNED #无符号
ZEROFILL #使用0填充说明:
#1. 是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值
#2. 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值
sex enum('male','female') not null default 'male'
#必须为正值（无符号） 不允许为空 默认是20
age int unsigned NOT NULL default 20
# 3. 是否是key
主键 primary key
外键 foreign key
索引 (index,unique...)not null 与default
是否可空，null表示空，非字符串
not null - 不可空
null - 可空
默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值
mysql> create database db1; # 创建db1数据库
Query OK, 1 row affected (0.00 sec)
mysql> use db1; # 选择db1数据库
Database changed
mysql> create table tb1(id int not null default 2,num int not null); # 创建tb1表并约束
Query OK, 0 rows affected (0.03 sec)
mysql> desc tb1; # 查看tb1表结构
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | | 2 | |
| num | int(11) | NO | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
验证1：
mysql> create table t11(id int); # 创建t11表 id字段默认可以为空
Query OK, 0 rows affected (0.16 sec)
mysql> desc t11; # 查看t11表结构
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> select * from t11; # 查看t11表数据为空
Empty set (0.00 sec)
mysql> insert into t11 values(); # 在t11表中插入一个空值
Query OK, 1 row affected (0.00 sec)
mysql> select * from t11; # 查看t11表数据，可以看到一个NULL的值
+------+
| id |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
验证2：
mysql> create table t12(id int not null);
Query OK, 0 rows affected (0.05 sec)
mysql> desc t12;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into t12 values();
ERROR 1364 (HY000): Field 'id' doesn't have a default value
验证3:
第一种情况
mysql> create table t13(id int default 1);
Query OK, 0 rows affected (0.11 sec)
mysql> desc t13;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | 1 | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into t13 values();
Query OK, 1 row affected (0.01 sec)
mysql> select * from t13;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
第二种情况:
mysql> create table t14(id int not null default 2);
Query OK, 0 rows affected (0.40 sec)
mysql> desc t14;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | | 2 | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)
mysql> select * from t14;
Empty set (0.00 sec)
mysql> insert into t14 values();
Query OK, 1 row affected (0.01 sec)
mysql> select * from t14;
+----+
| id |
+----+
| 2 |
+----+
1 row in set (0.00 sec)
练习:
创建学生表student2,设置每个字段的约束条件.
mysql> create table student2(id int not null,name varchar(50) not null,age int(3) unsigned not null default 18,sex enum('male','female') default 'male', fav set('smoke','drink','tanngtou') default 'drink,tangtou');
Query OK, 0 rows affected (0.38 sec)
# 只插入了not null约束条件的字段对应的值
mysql> insert into student2(id,name) values(1,'张三');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student2;
+----+--------+-----+------+---------------+
| id | name | age | sex | fav |
+----+--------+-----+------+---------------+
| 1 | 张三 | 18 | male | drink,tangtou |
+----+--------+-----+------+---------------+
1 row in set (0.00 sec)
unique,在mysql中称为单列唯一
举例说明：创建公司部门表（每个公司都有唯一的一个部门）
mysql> create table department(id int,name char(10));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into department values(1,'IT'),(2,'IT');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from department;
+------+------+
| id | name |
+------+------+
| 1 | IT |
| 2 | IT |
+------+------+
2 rows in set (0.00 sec)
发现:同时插入两个IT部门也是可以的，但这是不合理的，所以我们要设置name字段为unique解决这种不合理的现象。
接下来，使用约束条件unique，来对公司部门的字段进行设置
第一种创建unique的方式
例子1
mysql> create table department1(id int,name char(10) unique);
Query OK, 0 rows affected (0.10 sec)
mysql> insert into departement1 values(1,'张三'),(2,'张三'); # name的值不唯一，报错
ERROR 1146 (42S02): Table 'db1.departement1' doesn't exist
例子2
mysql> create table department2(id int unique,name char(10) unique);
Query OK, 0 rows affected (0.11 sec)
mysql> insert into department2 values(1,'张三'),(2,'李四');
Query OK, 2 rows affected (0.38 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from department2;
+------+--------+
| id | name |
+------+--------+
| 1 | 张三 |
| 2 | 李四 |
+------+--------+
2 rows in set (0.00 sec)
第二种创建unique的方式
mysql> create table department3(id int,name char(10), unique(id),unique(name));
Query OK, 0 rows affected (0.12 sec)
mysql> insert into department3 values(1,'哈哈'),(2,'嘿嘿');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from department3;
+------+--------+
| id | name |
+------+--------+
| 1 | 哈哈 |
| 2 | 嘿嘿 |
+------+--------+
2 rows in set (0.00 sec)
联合唯一:
创建services表
mysql> create table services(id int,ip char(15),port int,unique(id),unique(ip,port));
Query OK, 0 rows affected (0.44 sec)
mysql> desc services;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | UNI | NULL | |
| ip | char(15) | YES | MUL | NULL | |
| port | int(11) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
# 联合唯一，只要两列记录，有一列不同，既符合联合唯一的约束
mysql> insert into services values(1,'192.168.11.23',80),(2,'192.168.11.23',81),(3,'192.168.11.25',80);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from services;
+------+---------------+------+
| id | ip | port |
+------+---------------+------+
| 1 | 192.168.11.23 | 80 |
| 2 | 192.168.11.23 | 81 |
| 3 | 192.168.11.25 | 80 |
+------+---------------+------+
3 rows in set (0.00 sec)
mysql> insert into services values(4,'192.168.11.23',80); # 报错,不满足联合唯一原则
ERROR 1062 (23000): Duplicate entry '192.168.11.23-80' for key 'ip'
primary key
一个表中可以：
单列做主键
多列做主键（复合主键）
约束：等价于 not null unique,字段的值不为空且唯一
存储引擎默认是（innodb）:对于innodb存储引擎来说，一张表必须有一个主键
单列主键
# 创建t14表，为id字段设置主键，唯一的不同的记录
mysql> create table t14(id int primary key,name char(16));
Query OK, 0 rows affected (0.04 sec)
mysql> insert into t14 values(1,'小黑'),(2,'小白');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into t14 values(2,'wxxx'); #设置id为主键，主键内容不能重复
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
# not null + unique的化学反应，相当于给id设置primary key
mysql> create table t15(id int not null unique,name char(16));
Query OK, 0 rows affected (0.39 sec)
mysql> desc t15;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(16) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into t15 values(1,'小黑'),(2,'小白');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into t15 values(2,'小花'); # 报错，主键不能相同
ERROR 1062 (23000): Duplicate entry '2' for key 'id'
符合主键
mysql> create table t16(ip char(15), port int, primary key(ip,port));
Query OK, 0 rows affected (0.09 sec)
mysql> insert into t16 values('192.168.11.22',80),('192.168.11.22',81);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t16;
+---------------+------+
| ip | port |
+---------------+------+
| 192.168.11.22 | 80 |
| 192.168.11.22 | 81 |
+---------------+------+
2 rows in set (0.00 sec)
auto_increment
约束:约束的字段为自动增长，约束的字段必须同时被key约束
(重点)验证:
不指定id，则自动增长
mysql> create table student(id int primary key auto_increment,name varchar(20),sex enum('maale','female') default 'male');
Query OK, 0 rows affected (0.04 sec)
mysql> desc student;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| sex | enum('male','female') | YES | | male | |
+-------+-----------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
# 插入记录
mysql> insert into student(name) values('哈哈'),('嘿嘿');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from student;
+----+--------+------+
| id | name | sex |
+----+--------+------+
| 1 | 哈哈 | male |
| 2 | 嘿嘿 | male |
+----+--------+------+
2 rows in set (0.00 sec)
也可以指定id
mysql> insert into student values(4,'asb','female');
Query OK, 1 row affected (0.01 sec)
mysql> insert into student values(7,'web','female');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+--------+--------+
| id | name | sex |
+----+--------+--------+
| 1 | 哈哈 | male |
| 2 | 嘿嘿 | male |
| 4 | asb | female |
| 7 | web | female |
+----+--------+--------+
4 rows in set (0.00 sec)
# 再次插入一条不指定id的记录，会在之前的最后一条记录继续增长
mysql> insert into student(name) values('哦哦');
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+--------+--------+
| id | name | sex |
+----+--------+--------+
| 1 | 哈哈 | male |
| 2 | 嘿嘿 | male |
| 4 | asb | female |
| 7 | web | female |
| 8 | 哦哦 | male |
+----+--------+--------+
5 rows in set (0.00 sec)
对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长
mysql> delete from student;
Query OK, 5 rows affected (0.00 sec)
mysql> select * from student;
Empty set (0.00 sec)
mysql> insert into student(name) values('嗯嗯');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+--------+------+
| id | name | sex |
+----+--------+------+
| 9 | 嗯嗯 | male |
+----+--------+------+
1 row in set (0.00 sec)
# 应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它
mysql> truncate student;
Query OK, 0 rows affected (0.02 sec)
mysql> insert into student(name) values('呃呃');
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+--------+------+
| id | name | sex |
+----+--------+------+
| 1 | 呃呃 | male |
+----+--------+------+
1 row in set (0.00 sec)
清空表区分delete和truncate的区别：
delete from t1; 如果有自增id，新增的数据，仍然是以删除前的最后一样作为起始.
truncate table t1;数据量大，删除速度比上一条快，且直接从零开始.
foreign key
公司有3个部门，但是很多的员工，那意味着部门这个字段需要重复存储，部门名字越长，越浪费。
这个时候，
解决方法：
我们完全可以定义一个部门表
然后让员工信息表关联该表，如何关联，即foreign key
我们可以将上表改为如下结构：
此时有两张表，一张是employee表,简称emp表（关联表，也就主表）。一张是department表，简称dep表（被关联表，也叫从表）
创建两张表操作:
1 创建表时先创建主表，再创建关联表
先创建主表(dep表)
mysql> create table dep(id int primary key,name varchar(20) not null,descripe varchar(20) not null);
Query OK, 0 rows affected (0.42 sec)
再创建关联表(emp表)
mysql> create table emp(
id int primary key,
name varchar(20) not null,
age int not null,
dep_id int,
constraint fk_dep foreign key(dep_id) references dep(id)
);
Query OK, 0 rows affected (0.11 sec)
2 插入记录时，先往被关联表中插入记录，再往关联表中插入记录
mysql> insert into dep values
(1,'IT','IT技术有限部门'),
(2,'销售部','销售部门'),
(3,'财务部','花钱太多部门');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into emp values
(1,'zhangsan',18,1),
(2,'lisi',19,1),
(3,'wangwu',20,2),
(4,'zhuliu',40,3),
(5,'chenqi',18,2);
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
3 删除表
按道理来说，删除了部门表中的某个部门，员工表的有关联的记录相继删除
mysql> delete from dep where id=3; # 报错，应该先删除被关联表
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db1`.`emp`, CONSTRAINT `fk_dep` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`))
但是先删除员工表的记录之后，再删除当前部门就没有任何问题
mysql> delete from emp where id=3;
Query OK, 1 row affected (0.01 sec)
mysql> delete from emp where id=4;
Query OK, 1 row affected (0.01 sec)
mysql> select * from emp;
+----+----------+-----+--------+
| id | name | age | dep_id |
+----+----------+-----+--------+
| 1 | zhangsan | 18 | 1 |
| 2 | lisi | 19 | 1 |
| 5 | chenqi | 18 | 2 |
+----+----------+-----+--------+
3 rows in set (0.01 sec)
4 rows in set (0.00 sec)
mysql> delete from dep where id=3;
Query OK, 1 row affected (0.00 sec)
mysql> select * from dep;
+----+-----------+----------------------+
| id | name | descripe |
+----+-----------+----------------------+
| 1 | IT | IT技术有限部门 |
| 2 | 销售部 | 销售部门 |
+----+-----------+----------------------+
2 rows in set (0.00 sec)
上面的删除表记录的操作比较繁琐，按道理讲，裁掉一个部门，该部门的员工也会被裁掉。其实呢，在建表的时候还有个很重要的内容，叫同步删除，同步更新
接下来将刚建好的两张表全部删除，先删除关联表（emp）,再删除被关联表（dep）
接下来：
重复上面的操作建表
注意：在关联表中加入
on delete cascade #同步删除
on update cascade #同步更新
删除表
mysql> drop table emp;
Query OK, 0 rows affected (0.12 sec)
mysql> drop table dep;
Query OK, 0 rows affected (0.01 sec)
创建表
mysql> create table dep(
id int primary key,
name varchar(20) not null,
descripe varchar(20) not null
);
Query OK, 0 rows affected (0.04 sec)
mysql> create table emp(
id int primary key,
name varchar(20) not null,
age int not null,
dep_id int,
constraint fk_dep foreign key(dep_id) references dep(id)
on delete cascade # 同步删除
on update cascade # 同步更新
);
Query OK, 0 rows affected (0.06 sec)
插入数据:
mysql> insert into dep values
(1,'IT','IT技术有限部门'),
(2,'销售部','销售部门'),
(3,'财务部','花钱太多部门');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into emp values
(1,'zhangsan',18,1),
(2,'lisi',19,1),
(3,'wangwu',20,2),
(4,'zhuliu',40,3),
(5,'chenqi',18,2);
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
接下来的操作，就复合我们正常的生活中的情况了
再去删被关联表(dep)的记录，关联表(emp)中的记录也跟着删除
mysql> delete from dep where id=3;
Query OK, 1 row affected (0.00 sec)
mysql> select * from dep;
+----+-----------+----------------------+
| id | name | descripe |
+----+-----------+----------------------+
| 1 | IT | IT技术有限部门 |
| 2 | 销售部 | 销售部门 |
+----+-----------+----------------------+
2 rows in set (0.00 sec)
mysql> select * from emp;
+----+----------+-----+--------+
| id | name | age | dep_id |
+----+----------+-----+--------+
| 1 | zhangsan | 18 | 1 |
| 2 | lisi | 19 | 1 |
| 3 | wangwu | 20 | 2 |
| 5 | chenqi | 18 | 2 |
+----+----------+-----+--------+
4 rows in set (0.00 sec)
再去更改被关联表(dep)的记录，关联表(emp)中的记录也跟着更改
mysql> update dep set id=222 where id=2;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from dep;
+-----+-----------+----------------------+
| id | name | descripe |
+-----+-----------+----------------------+
| 1 | IT | IT技术有限部门 |
| 222 | 销售部 | 销售部门 |
+-----+-----------+----------------------+
2 rows in set (0.00 sec)
mysql> select * from emp;
+----+----------+-----+--------+
| id | name | age | dep_id |
+----+----------+-----+--------+
| 1 | zhangsan | 18 | 1 |
| 2 | lisi | 19 | 1 |
| 3 | wangwu | 20 | 222 |
| 5 | chenqi | 18 | 222 |
+----+----------+-----+--------+
4 rows in set (0.00 sec)
