目录
第13章 DML
13.1 添加语句
（1）添加一条记录到某个表中
insert into 表名称 values(值列表); #值列表中的值的顺序、类型、个数必须与表结构一一对应
mysql> desc teacher;
+----------+------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------------+------+-----+---------+-------+
| tid | int(11) | YES | | NULL | |
| tname | varchar(5) | YES | | NULL | |
| salary | double | YES | | NULL | |
| weight | double | YES | | NULL | |
| birthday | date | YES | | NULL | |
| gender | enum('男','女') | YES | | NULL | |
| blood | enum('A','B','AB','O') | YES | | NULL | |
| phone | char(11) | YES | | NULL | |
+----------+------------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
insert into teacher values(1,'张三',15000,120.5,'1990-5-1','男','O','13789586859');
insert into teacher values(2,'李四',15000,'1990-5-1','男','O','13789586859'); #缺体重weight的值
ERROR 1136 (21S01): Column（列） count（数量） doesn't match（不匹配） value（值） count（数量) at row 1（2）添加一条记录到某个表中
insert into 表名称 (字段列表) values(值列表); #值列表中的值的顺序、类型、个数必须与(字段列表)一一对应
insert into teacher(tid,tname,salary,phone) values(3,'王五',16000,'15789546586');（3）添加多条记录到某个表中
insert into 表名称 values(值列表),(值列表),(值列表); #值列表中的值的顺序、类型、个数必须与表结构一一对应
insert into 表名称 (字段列表) values(值列表),(值列表),(值列表); #值列表中的值的顺序、类型、个数必须与(字段列表)一一对应
insert into teacher (tid,tname,salary,phone)
values(4,'赵六',16000,'15789546586'),
(5,'汪飞',18000,'15789548886'),
(6,'天琪',19000,'15909546586');（4）示例演示
#演示基本的，简单的DML语句
#基于tempdb数据库演示
create database tempdb;
use tempdb;
#创建teacher表
create table teacher(
id int,
name varchar(20),
gender enum('m','f'),
birthday date,
salary double,
tel varchar(11)
);
#查看teacher表结构
mysql> desc teacher;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| gender | enum('m','f') | YES | | NULL | |
| birthday | date | YES | | NULL | |
| salary | double | YES | | NULL | |
| tel | char(18) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
#添加数据
#（1）第一种情况，给所有字段赋值
insert into 表名称 values(值列表);
#这种情况要求(值列表)的每一个值的类型、顺序与表结构一一对应
#表中有几个字段，(值列表)必须有几个值，不能多也不能少
#值如果是字符串或日期类型，需要加单引号
#例如：添加一条记录到teacher表
insert into teacher values
(1,'张三','m','1998-7-8',15000.0,'15512341234');
13.2 修改语句
修改所有行
update 表名称 set 字段名 = 值, 字段名 = 值; #给所有行修改
#修改所有人的薪资，都涨了1000
update teacher set salary = salary + 1000 ; 修改部分行
update 表名称 set 字段名 = 值, 字段名 = 值 where 条件; #给满足条件的行修改
#修改张三的薪资降低5000
update teacher set salary = salary-5000 where tname = '张三';13.3 删除
删除部分行的数据
delete from 表名称 where 条件;
delete from teacher where tname = '张三';删除整张表的数据，但表结构留下
delete from 表名称;
delete from teacher;截断表，清空表中的数据，只有表结构
truncate 表名称;
truncate teacher;
truncate表和delete表的区别：
delete是一条一条删除记录的。如果在事务中，事务提交之前支持回滚。（后面会讲事务）
truncate是把整个表drop，新建一张，效率更高。就算在事务中，也无法回滚。
#同学问：是否可以删除salary字段的值，字段留着，值删掉
#可以实现，但是不是用delete，用update
#同学问：是否可以删除salary字段，连同字段和这个字段的数据都是删除
#可以实现，但是不是用delete，用alter table 表名称 drop column 字段名;
#同学问：只删除某个单元格的值
#可以实现，但是不是用delete，用update13.4 UPDATE中嵌套子查询
#子查询也可以嵌套在update语句中
#（1）修改“t_employee”表中部门编号（did）和
#“测试部”部门编号（did）相同的员工薪资为原来薪资的1.5倍。
UPDATE t_employee
SET salary = salary * 1.5
WHERE did = (SELECT did FROM t_department WHERE dname = '测试部');
#（2）修改“t_employee”表中did为NULL的员工信息，
#将他们的did值修改为“测试部”的部门编号。
#子查询select did from t_department where dname = '测试部'
#这种子查询必须是单个值，否则无法赋值
UPDATE t_employee
SET did = (SELECT did FROM t_department WHERE dname = '测试部')
WHERE did IS NULL;
#（3）修改“t_employee”表中“张三”的薪资值等于“李四”的薪资值。
#这里使用子查询先在“t_employee”表中查询出“李四”的薪资。
UPDATE t_employee
SET salary = (SELECT salary FROM t_employee WHERE ename = '李四')
WHERE ename = '张三';
#You can't specify target table 't_employee' for update in FROM clause'
UPDATE t_employee
SET salary = (SELECT salary FROM(SELECT salary FROM t_employee WHERE ename = '李四')temp)
WHERE ename = '张三';
#当update的表和子查询的表是同一个表时，需要将子查询的结果用临时表的方式表示
#即再套一层子查询，使得update和最外层的子查询不是同一张表
#（4）修改“t_employee”表“李四”的薪资与她所在部门的平均薪资一样。
#子查询，查询李冰冰的部门编号
#select did from t_employee where ename = '李四';
#子查询第二层，查询李四所在部门的平均薪资
#select avg(salary) from t_employee where did = (select did from t_employee where ename = '李四');
#子查询第三层，把第二层的子查询结果当成临时表再查一下结果
#目的使得和外层的update不是同一张表
SELECT pingjun FROM (SELECT AVG(salary) pingjun FROM t_employee WHERE did = (SELECT did FROM t_employee WHERE ename = '李四') temp)
#update更新
UPDATE t_employee
SET salary =
(SELECT pingjun FROM
(SELECT AVG(salary) pingjun FROM t_employee WHERE did =
(SELECT did FROM t_employee WHERE ename = '李四') ) temp)
WHERE ename = '李四';13.5 DELETE中嵌套子查询
#delete语句中也可以嵌套子查询
#（1）从“t_employee”表中删除“测试部”的员工记录。
DELETE FROM t_employee
WHERE did = (SELECT did FROM t_department WHERE dname = '测试部');
#（2）从“t_employee”表中删除和“李四”同一个部门的员工记录。
#子查询 “李四”的部门编号
#select did from t_employee where ename = '李四';
DELETE FROM t_employee WHERE did = (SELECT did FROM t_employee WHERE ename = '李四');
#You can't specify target table 't_employee' for update in FROM clause'
#删除和子查询是同一张表
DELETE FROM t_employee WHERE did = (SELECT did FROM (SELECT did FROM t_employee WHERE ename = '李四')temp);13.6 使用子查询复制表结构和数据
#演示通过子查询复制表，
#（1）复制表结构
#（2）复制一条或多条记录
#（3）同时复制表结构和记录
#仅仅是复制表结构，可以用create语句
CREATE TABLE department LIKE t_department;
#使用INSERT语句+子查询，复制数据，此时INSERT不用写values
INSERT INTO department (SELECT * FROM t_department WHERE did<=3);
#同时复制表结构+数据
CREATE TABLE d_department AS (SELECT * FROM t_department);
#如果select后面是部分字段，复制的新表就只有这一部分字段第14章 约束
14.1 约束的作用
约束是为了保证数据的完整性，数据完整性（Data Integrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。它是应防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。
数据的完整性要从以下四个方面考虑：
-
实体完整性（Entity Integrity）：例如，同一个表中，不能存在两条完全相同无法区分的记录
-
域完整性（Domain Integrity）：例如：年龄范围0-120，性别范围“男/女”
-
引用完整性（Referential Integrity）：例如：员工所在部门，在部门表中要能找到这个部门
-
用户自定义完整性（User-defined Integrity）：例如：用户名唯一、密码不能为空等，本部门经理的工资不得高于本部门职工的平均工资的5倍。
14.2 约束的类型
1、约束类型
-
键约束：主键约束、外键约束、唯一键约束
-
Not NULL约束：非空约束
-
Check约束：检查约束
-
Default约束：默认值约束
自增是键约束字段的一个额外的属性。
2、表级约束和列级约束
其中键约束和检查约束是表级约束，即不仅要看约束字段当前单元格的数据，还要看其他单元格的数据。
非空约束和默认值约束都是列级约束，即约束字段只看当前单元格的数据即可，和其他单元格无关。
所有的表级约束都可以在“information_schema.table_constraints”表中查看。
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';3、约束和索引
在MySQL中键约束会自动创建索引，提高查询效率。索引的详细讲解在高级部分。
MySQL高级会给大家讲解索引、存储引擎等，因为高级要给大家分析SQL性能。而基础阶段先不管效率，只要能查出来就行。
约束和索引不同：
约束是一个逻辑概念，它不会单独占用物理空间，
索引是一个物理概念，它是会占用物理空间。
例如：字典
字典里面有要求，不能有重复的字（字一样，读音也一样），这是约束。
字典里面有“目录”，它可以快速的查找某个字，目录需要占用单独的页。
14.3 非空约束
1、作用
限定某个字段/某列的值不允许为空
2、关键字：not null
3、特点
（1）只能某个列单独限定非空，不能组合非空
（2）一个表可以有很多列都分别限定了非空
4、如何指定非空约束
（1）建表时
create table 表名称(
字段名 数据类型 not null,
字段名 数据类型 not null,
字段名 数据类型
);（2）建表后（了解）
alter table 表名称 modify 【column】 字段名 数据类型 not null;
/*
如何该字段已经有值了，给该字段增加非空约束，要求该字段的值不能有NULL值，否则需要先处理NULL值才能加上非空约束
*/5、如何删除非空约束（了解）
alter table 表名称 modify 【column】 字段名 数据类型;
/*
说明：
如果某个字段有not null非空约束，使用modify修改字段的数据类型、位置、字符集和校对规则、默认值约束时，
想要保留非空约束，必须把not null带上，否则会在修改字段的数据类型、位置、字符集和校对规则时，会把非空约束给丢掉。
*/6、SQL示例演示
/*演示非空约束
1、作用：限定某个字段的值不允许为NULL。
在Java中只有引用数据类型才能赋值为null，基本数据类型不允许为null。
但是MySQL中所有的数据类型都可以赋值为NULL，包括int等。
2、关键字：not null
3、一个表可以有很多个字段限定非空约束，
一个字段加not null，和其他字段无关，它是列级约束。
*/
#创建一个表格，演示NULL值
create table not_null_demo(
id int,
name varchar(20),
birthday date
);
insert into not_null_demo values(null,null,null);
insert into not_null_demo(id) values(1);
select * from not_null_demo;
drop table not_null_demo;
#在创建表的时候，可以指定非空约束
create table 表名称(
字段名 数据类型 not null,
字段名 数据类型 not null,
字段名 数据类型
);
create table not_null_demo(
id int not null,
name varchar(20) not null,
birthday date #没有加not null的，允许为NULL
);
#添加NULL值
insert into not_null_demo values(null,null,null);
mysql> insert into not_null_demo values(null,null,null);
ERROR 1048 (23000): Column 'id' cannot be null
#指定了id字段的值，但是没有指定name字段和birthday字段的值
insert into not_null_demo(id) values(1);
mysql> insert into not_null_demo(id) values(1);
ERROR 1364 (HY000): Field 'name' doesn't have a default value'
#此时name设定了NOT NULL，但又没有提前指定默认值，
#如果没赋值只能处理为NULL，就违反非空约束
#总结：在insert添加记录时，必须给所有没有提前指定默认值的非空约束字段赋值。
insert into not_null_demo(id,name) values(1,'张三');
insert into not_null_demo(id,name,birthday) values(2,'李四','2000-1-1');
insert into not_null_demo values(3,'王五','2000-2-1');
insert into not_null_demo values(4,'赵六',null);
insert into not_null_demo values(4,'赵六'); #错误，值列表数量与字段数量不匹配
mysql> insert into not_null_demo values(4,'赵六');
ERROR 1136 (21S01): Column count doesn't match value count at row 1'
#在创建表之后，可以给某个字段增加非空约束
#alter table 表名称 modify 【column】 字段名 数据类型 not null;
#例如：给not_null_demo表格的birthday字段加not null约束
alter table not_null_demo modify column birthday date not null;
mysql> alter table not_null_demo modify column birthday date not null;
ERROR 1138 (22004): Invalid use of NULL value
#失败的原因是因为not_null_demo表中birthday字段现在已经有NULL值。
#所以添加不上非空约束。
#如果要加，必须先将原来的NULL值修改掉
mysql> select * from not_null_demo;
+----+------+------------+
| id | name | birthday |
+----+------+------------+
| 1 | 张三 | NULL |
| 2 | 李四 | 2000-01-01 |
| 3 | 王五 | 2000-02-01 |
| 4 | 赵六 | NULL |
+----+------+------------+
4 rows in set (0.01 sec)
update not_null_demo set birthday = '2000-5-1' where id=1 || id = 4;
mysql> select * from not_null_demo;
+----+------+------------+
| id | name | birthday |
+----+------+------------+
| 1 | 张三 | 2000-05-01 |
| 2 | 李四 | 2000-01-01 |
| 3 | 王五 | 2000-02-01 |
| 4 | 赵六 | 2000-05-01 |
+----+------+------------+
4 rows in set (0.00 sec)
mysql> alter table not_null_demo modify column birthday date not null;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc not_null_demo;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int | NO | | NULL | |
| name | varchar(20) | NO | | NULL | |
| birthday | date | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
#删除某个字段的非空约束
alter table 表名称 modify 【column】 字段名 数据类型;
#例如：去掉birthday字段的非空约束
alter table not_null_demo modify column birthday date;
mysql> desc not_null_demo;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int | NO | | NULL | |
| name | varchar(20) | NO | | NULL | |
| birthday | date | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
/*
说明：
如果某个字段有not null非空约束的话，
使用modify修改字段的数据类型、位置、字符集和校对规则时，
想要保留非空约束，必须把not null带上，
否则会在修改字段的数据类型、位置、字符集和校对规则时，
把非空约束给丢掉。
*/
#例如：修改name字段的数据类型为varchar(30)，本来想要保留not null非空约束的
alter table not_null_demo modify column name varchar(30);
#此时alter没有加not null，会把name字段的not null约束给丢掉。
mysql> desc not_null_demo;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int | NO | | NULL | |
| name | varchar(30) | YES | | NULL | |
| birthday | date | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
14.4 唯一键约束
1、唯一键约束的作用
单列唯一：用来限制某个字段/某列的值不能重复。
组合唯一：用来限定几个字段的值组合不能重复。
2、关键字：unique key
3、特点
（1）一个表可以有很多个唯一键约束， （2）每一个唯一键约束字段都会自动创建索引。 （3）唯一键约束允许为空 （4）唯一键约束也可以是复合唯一 （5）删除唯一键约束的索引来删除唯一键约束 索引名默认是字段名，复合唯一默认是第一个字段名。
4、如何指定唯一键约束
（1）建表时
#在建表时，可以指定唯一键约束
create table 表名称(
字段名 数据类型 unique key,
字段名 数据类型 unique key,
字段名 数据类型
);
create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
unique key(字段名),
unique key(字段名)
);（2）建表后指定唯一键约束（了解）
#如何在建表后添加唯一键约束
alter table 表名称 add unique 【key】(字段名);4、如何查看唯一键约束？（了解）
desc 表名称;
show create table 表名称;
show index from 表名称; #查看表的索引信息5、复合唯一
create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
unique key(字段列表) #字段列表中写的是多个字段名，多个字段名用逗号分隔，表示那么是复合唯一，即多个字段的组合是唯一的
);6、删除唯一键约束（了解）
alter table 表名称 drop index 索引名;
#删除唯一键约束需要手动删除对应的索引7、如何查看表的索引
show index from 表名称;8、SQL示例
/*演示唯一键约束
1、作用：限定某个字段的值不允许重复。
但是如果是NULL值可以重复。
2、关键字：unique key 其中key可以省略
3、特点
一个表可以有很多个唯一键约束，
每一个唯一键约束字段都会自动创建索引。
*/
#在建表时，可以指定唯一键约束
create table 表名称(
字段名 数据类型 unique key, #unique key可以直接加在字段后面
字段名 数据类型 unique key,
字段名 数据类型
);
create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
unique key(字段名), #unique key也可以在字段列表下面单独定义
unique key(字段名)
);
#创建一个表格，限定编号、身份证号码和手机号码唯一
create table emp(
id int unique key, #表示id字段值不能重复
name varchar(20),
cardid char(18),
tel char(11),
unique key(cardid), #表示cardid字段值不能重复
unique key(tel) #表示tel字段值不能重复
);
#查看表结构
desc emp;
mysql> desc emp;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int | YES | UNI | NULL | |
| name | varchar(20) | YES | | NULL | |
| cardid | char(18) | YES | UNI | NULL | |
| tel | char(11) | YES | UNI | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
#查看表的定义语句
show create table emp;
mysql> show create table emp\G
*************************** 1. row ***************************
Table: emp
Create Table: CREATE TABLE `emp` (
`id` int DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`cardid` char(18) DEFAULT NULL,
`tel` char(11) DEFAULT NULL,
UNIQUE KEY `id` (`id`),
UNIQUE KEY `cardid` (`cardid`),
UNIQUE KEY `tel` (`tel`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
#添加数据
insert into emp values(1,'张三','25678544522222','13589587585');
insert into emp values(2,'张三','25678544522211','13589587596');
#查询数据
select * from emp;
insert into emp values(3,'李四','25678544522233','13589587596');
#手机号码重复，报错
mysql> insert into emp values(3,'李四','25678544522233','13589587596');
ERROR 1062 (23000): Duplicate（复制、重复） entry（输入） '13589587596' for key 'emp.tel'
insert into emp values
(3,'李四',null,null),
(4,'王五',null,null);
mysql> select * from emp;
+------+------+----------------+-------------+
| id | name | cardid | tel |
+------+------+----------------+-------------+
| 1 | 张三 | 25678544522222 | 13589587585 |
| 2 | 张三 | 25678544522211 | 13589587596 |
| 3 | 李四 | NULL | NULL | #NULL可以重复
| 4 | 王五 | NULL | NULL |
+------+------+----------------+-------------+
4 rows in set (0.00 sec)
#如何查看一个表的索引
show index from 表名称;
#查看emp表的索引
show index from emp;
#删除唯一键约束
alter table 表名称 drop index 索引名;
#如果在指定唯一键约束时，没有手动定义名字，默认就是字段名;
#建议大家在删除时用show index语句查看一下索引名
#删除emp表的cardid的唯一键约束
alter table emp drop index cardid;
mysql> desc emp;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int | YES | UNI | NULL | |
| name | varchar(20) | YES | | NULL | |
| cardid | char(18) | YES | | NULL | |
| tel | char(11) | YES | UNI | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
#如何在建表后添加唯一键约束
alter table 表名称 add unique 【key】(字段名);
#给emp表的cardid增加唯一键约束
alter table emp add unique key(cardid);
mysql> desc emp;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int | YES | UNI | NULL | |
| name | varchar(20) | YES | | NULL | |
| cardid | char(18) | YES | UNI | NULL | |
| tel | char(11) | YES | UNI | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> show index from emp;
#有一种特殊的唯一键约束，复合唯一
#即表示两个或更多个字段值的组合唯一，单个字段看不唯一
create table 表名称(
字段名1 数据类型,
字段名2 数据类型,
字段名3 数据类型,
字段名4 数据类型,
unique key(字段名1,字段名2), #表示字段名1和字段名2组合不能重复
#字段1和字段2单独看是可以重复的
unique key(字段名3), #表示字段3单独不能重复
unique key(字段名4) #表示字段3单独不能重复
);
#学生表
create table stu(
id int,
name varchar(20)
);
#添加学生信息
insert into stu values(1,'张三');
insert into stu values(2,'李四');
mysql> select * from stu;
+------+------+
| id | name |
+------+------+
| 1 | 张三 |
| 2 | 李四 |
+------+------+
2 rows in set (0.00 sec)
#课程表
create table course(
id int,
title varchar(50)
);
#添加课程信息
insert into course values(1,'java');
insert into course values(2,'mysql');
mysql> select * from course;
+------+-------+
| id | title |
+------+-------+
| 1 | java |
| 2 | mysql |
+------+-------+
2 rows in set (0.00 sec)
#选课表
create table xuanke(
xid int unique key, #表示xid不能重复
sid int,
cid int,
score int,
unique key(sid,cid) #这么写表示sic和cid的组合不能重复，
#单独看sid和cid是可以重复的
);
#添加选课成绩信息
insert into xuanke values(1,1,1,89);
insert into xuanke values(2,1,2,96);
insert into xuanke values(3,2,1,75);
insert into xuanke values(4,2,2,96);
mysql> select * from xuanke;
+------+------+------+-------+
| xid | sid | cid | score |
+------+------+------+-------+
| 1 | 1 | 1 | 89 |
| 2 | 1 | 2 | 96 |
| 3 | 2 | 1 | 75 |
| 4 | 2 | 2 | 96 |
+------+------+------+-------+
4 rows in set (0.00 sec)
#单独看sid是可以重复的
#单独看cid是可以重复的
#组合看sid和cid是不可以重复的
insert into xuanke values(5,1,1,100); #sid为1和cid为1组合重复
mysql> insert into xuanke values(5,1,1,100);
ERROR 1062 (23000): Duplicate entry '1-1' for key 'xuanke.sid'
mysql> desc xuanke;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| xid | int | YES | UNI | NULL | |
| sid | int | YES | MUL | NULL | |
| cid | int | YES | | NULL | |
| score | int | YES | | NULL | |
+-------+------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql> show index from xuanke;
#组合唯一键索引名默认是unique key(字段1， 字段2)的第一个字段名。
#删除复合唯一键约束
alter table xuanke drop index sid;
14.5 主键约束（重要）
1、主键约束的作用
用来唯一的确定一条记录
2、关键字：primary key
3、特点
（1）唯一并且非空
（2）一个表最多只能有一个主键约束
（3）如果主键是由多列组成，可以使用复合主键
（4）主键列会自动创建索引（能够根据主键查询的，就根据主键查询，效率更高）
主键列的唯一并且非空是约束的概念，但是mysql会给每个表的主键列创建索引，会开辟单独的物理空间来存储每一个主键的目录表（Btree结构）。这样设计的意义，可以根据主键快速查询到某一行的记录。
（5）如果删除主键约束了，主键约束对应的索引就自动删除了。
4、唯一键约束和主键约束区别
4、唯一键约束和主键约束的区别 （1）唯一键约束一个表可以有好几个， 但是主键约束只有一个 （2）唯一键约束本身不带非空限制，如果需要非空，需要单独定义。 主键约束不用再定义NOT NULL，自身就带非空限制。
5、如何指定主键约束
（1）建表时指定主键约束
create table 表名称(
字段名 数据类型 primary key,
字段名 数据类型,
字段名 数据类型
);
create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
primary key(字段名)
);（2）建表后增加主键约束（了解）
alter table 表名称 add primary key(字段列表); #字段列表可以是一个字段，也可以是多个字段，如果是多个字段的话，是复合主键6、复合主键
create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
primary key(字段名1,字段名2) #表示字段1和字段2的组合是唯一的，也可以有更多个字段
);7、如何删除主键约束？（了解）
alter table 表名称 drop primary key;8、SQL示例
#演示主键约束
/*
1、作用：
主键约束是用来唯一的标识一行记录，
或者换句话说，表中不允许出现两条无法区分的记录。
问？唯一键约束不就可以实现吗？
答：唯一键约束因为没有限定非空，可能出现两个NULL。
学号 姓名 身份证号码（唯一键）
1 张三 524265198235684255
2 李四 524265198235684266
3 王五 NULL
3 王五 NULL
2、关键字：primary key
3、特点
（1）每一个表最多只能定义一个主键约束。
（2）主键约束，既要求唯一又要求非空
可以看成 主键约束 = 非空约束 + 唯一键约束
但不完全等价。
4、唯一键约束和主键约束的区别
（1）唯一键约束一个表可以有好几个，
但是主键约束只有一个
（2）唯一键约束本身不带非空限制，如果需要非空，需要单独定义。
主键约束不用再定义NOT NULL，自身就带非空限制。
*/
#建表时，指定主键约束
create table 表名称(
字段名 数据类型 primary key,
字段名 数据类型 not null,
字段名 数据类型 not null unique key,
字段名 数据类型 not null unique key,
字段名 数据类型 unique key,
字段名 数据类型 unique key
字段名 数据类型
);
create table 表名称(
字段名 数据类型 ,
字段名 数据类型 not null,
字段名 数据类型 not null unique key,
字段名 数据类型 not null unique key,
字段名 数据类型 unique key,
字段名 数据类型 unique key
字段名 数据类型,
primary key(字段名),
unique key(字段名)
);
mysql> drop database huohua;
Query OK, 19 rows affected (0.17 sec)
mysql> create database huohua;
Query OK, 1 row affected (0.01 sec)
mysql> use huohua;
Database changed
#创建员工表
create table emp(
id int primary key,
ename varchar(20) not null,
cardid char(18) unique key not null, #非空并且唯一
tel char(11) unique key, #只是唯一，可以为空
address varchar(100) #既可以为null，又可以重复
);
#查看表结构
desc emp;
mysql> desc emp;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| ename | varchar(20) | NO | | NULL | |
| cardid | char(18) | NO | UNI | NULL | |
| tel | char(11) | YES | UNI | NULL | |
| address | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
#添加数据
insert into emp
values(1,'张三','524265198235684255','18536955456',null);
#添加数据
insert into emp
values(2,'李四','524265198235685255',null,null);#tel可以为null
mysql> select * from emp;
+----+-------+--------------------+-------------+---------+
| id | ename | cardid | tel | address |
+----+-------+--------------------+-------------+---------+
| 1 | 张三 | 524265198235684255 | 18536955456 | NULL |
| 2 | 李四 | 524265198235685255 | NULL | NULL |
+----+-------+--------------------+-------------+---------+
2 rows in set (0.00 sec)
#添加数据
insert into emp
values(3,'李四','524265198235685895',null,null);
#添加数据
insert into emp
values(3,'王五','524265198235675895',null,null);#主键重复
mysql> #添加数据
mysql> insert into emp
-> values(3,'王五','524265198235675895',null,null);#主键重复
ERROR 1062 (23000): Duplicate entry '3' for key 'emp.PRIMARY'
#添加数据
insert into emp
values(null,'王五','524265198235675775',null,null);#主键为null
mysql> insert into emp
-> values(null,'王五','524265198235675775',null,null);#主键为null
ERROR 1048 (23000): Column 'id' cannot be null
#创建一个表，两个字段设置主键
create table xuanke(
sid int primary key,
cid int primary key,
score int
);
mysql> create table xuanke(
-> sid int primary key, #定义两个主键，不是复合主键的意思
-> cid int primary key, #定义两个主键，不是复合主键的意思
-> score int
->
-> );
ERROR 1068 (42000): Multiple primary key defined
#组合主键约束
#学生表
create table stu(
id int,
name varchar(20)
);
#添加学生信息
insert into stu values(1,'张三');
insert into stu values(2,'李四');
mysql> select * from stu;
+------+------+
| id | name |
+------+------+
| 1 | 张三 |
| 2 | 李四 |
+------+------+
2 rows in set (0.00 sec)
#课程表
create table course(
id int,
title varchar(50)
);
#添加课程信息
insert into course values(1,'java');
insert into course values(2,'mysql');
mysql> select * from course;
+------+-------+
| id | title |
+------+-------+
| 1 | java |
| 2 | mysql |
+------+-------+
2 rows in set (0.00 sec)
#定义选课表
create table xuanke(
sid int,
cid int,
score int,
primary key(sid,cid) #组合/复合主键
);
insert into xuanke values(1,1,96),(2,1,85),(1,2,75),(2,2,45);
#添加重复主键报错
insert into xuanke values(1,1,75);
mysql> insert into xuanke values(1,1,75);
ERROR 1062 (23000): Duplicate entry '1-1' for key 'xuanke.PRIMARY'
#查看一下表的定义
show create table emp;
show create table xuanke;
#查看emp表和xuanke表的索引
show index from emp;
show index from xuanke;
mysql> show index from emp;
mysql> show index from xuanke;
#是否可以删除主键约束
alter table 表名称 drop primary key;
#因为一个表只有一个唯一键约束，所以不用特意指定名字。
#删除xuanke表的主键约束
alter table xuanke drop primary key;
mysql> show index from xuanke;
Empty set (0.00 sec)
mysql> desc xuanke;
#删除主键约束时，对应的索引，键约束删除了，但是主键约束自带的非空约束没有删除。
#如果要去掉的话，需要用删除非空约束的方式，单独删除。
#建表后增加主键约束
alter table 表名称 add primary key(字段名); #表示给1个字段增加主键约束
alter table 表名称 add primary key(字段名1,字段名2); #表示增加的是复合主键
alter table xuanke add primary key(sid,cid);
mysql> desc xuanke;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| sid | int | NO | PRI | NULL | |
| cid | int | NO | PRI | NULL | |
| score | int | YES | | NULL | |
+-------+------+------+-----+---------+-------+
3 rows in set (0.01 sec)
#删除emp的主键约束
alter table emp drop primary key;
mysql> desc emp;
show index from emp;
mysql> show index from emp;
#给emp表的id字段增加主键约束
alter table emp add primary key(id);
mysql> show index from emp;
#学生问，删除主键约束，可以重复吗？答：是的
alter table emp drop primary key;
mysql> select * from emp;
+----+-------+--------------------+-------------+---------+
| id | ename | cardid | tel | address |
+----+-------+--------------------+-------------+---------+
| 1 | 张三 | 524265198235684255 | 18536955456 | NULL |
| 2 | 李四 | 524265198235685255 | NULL | NULL |
| 3 | 李四 | 524265198235685895 | NULL | NULL |
+----+-------+--------------------+-------------+---------+
3 rows in set (0.00 sec)
insert into emp values(3,'王五','524233198235685895',null,null);
mysql> select * from emp;
+----+-------+--------------------+-------------+---------+
| id | ename | cardid | tel | address |
+----+-------+--------------------+-------------+---------+
| 3 | 王五 | 524233198235685895 | NULL | NULL |
| 1 | 张三 | 524265198235684255 | 18536955456 | NULL |
| 2 | 李四 | 524265198235685255 | NULL | NULL |
| 3 | 李四 | 524265198235685895 | NULL | NULL |
+----+-------+--------------------+-------------+---------+
4 rows in set (0.00 sec)
14.6 默认值约束
1、作用
给某个字段/某列指定默认值，当添加时或修改时，可以使用默认值。
2、关键字：default
3、如何给字段加默认值
（1）建表时
create table 表名称(
字段名 数据类型 primary key,
字段名 数据类型 unique key not null,
字段名 数据类型 unique key,
字段名 数据类型 not null default 默认值,
);
create table 表名称(
字段名 数据类型 default 默认值 ,
字段名 数据类型 not null default 默认值,
字段名 数据类型 not null default 默认值,,
primary key(字段名),
unique key(字段名)
); 说明：默认值约束一般不在唯一键和主键列上加
（2）建表后（了解）
alter table 表名称 modify 字段名 数据类型 default 默认值;
#如果这个字段原来有非空约束，你还保留非空约束，那么在加默认值约束时，还得保留非空约束，否则非空约束就被删除了
#同理，在给某个字段加非空约束也一样，如果这个字段原来有默认值约束，你想保留，也要在modify语句中保留默认值约束，否则就删除了
alter table 表名称 modify 字段名 数据类型 default 默认值 not null;4、如何删除默认值约束（了解）
alter table 表名称 modify 字段名 数据类型 ;#删除默认值约束，也不保留非空约束
alter table 表名称 modify 字段名 数据类型 not null; #删除默认值约束，保留非空约束5、SQL示例
#演示默认值约束
/*
1、作用
如果在insert添加数据时，没有给这个字段赋值，它会选择默认值约束指定默认值。
如果字段没有定义默认值约束，那么默认就是NULL。
2、关键字：default
非空和默认值约束是列级约束，只影响某个字段，只看当前单元格，和其他行无关。
唯一键约束和主键约束是表级约束，会看该字段所有行的数据，整个表一起看。
*/
#建表时，可以在字段后面给字段指定默认值
create table emp(
eid int primary key,
ename varchar(20) not null,
gender enum('男','女') default '男' not null, #非空并且有默认值
address varchar(100) default '不详' #可以指定为null
);
insert into emp values(1,'张三');#错误，值的数量和字段的数量不匹配
mysql> insert into emp values(1,'张三');
ERROR 1136 (21S01): Column count doesn't match value count at row 1'
insert into emp(eid,ename) values(1,'张三');
insert into emp values(2,'李四',default,default);
insert into emp values(3,'王五',default,null);
mysql> select * from emp;
+-----+-------+--------+---------+
| eid | ename | gender | address |
+-----+-------+--------+---------+
| 1 | 张三 | 男 | 不详 |
| 2 | 李四 | 男 | 不详 |
| 3 | 王五 | 男 | NULL |
+-----+-------+--------+---------+
3 rows in set (0.00 sec)
insert into emp values(4,'翠花','女','郑州');
mysql> select * from emp;
+-----+-------+--------+---------+
| eid | ename | gender | address |
+-----+-------+--------+---------+
| 1 | 张三 | 男 | 不详 |
| 2 | 李四 | 男 | 不详 |
| 3 | 王五 | 男 | NULL |
| 4 | 翠花 | 女 | 北京 |
+-----+-------+--------+---------+
4 rows in set (0.00 sec)
#删除默认值约束
alter table 表名称 modify 【column】 字段名 数据类型;
#查看表结构
desc emp;
mysql> desc emp;
+---------+-----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-----------------+------+-----+---------+-------+
| eid | int | NO | PRI | NULL | |
| ename | varchar(20) | NO | | NULL | |
| gender | enum('男','女') | NO | | 男 | |
| address | varchar(100) | YES | | 不详 | |
+---------+-----------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
#删除emp表的address的默认值约束
alter table emp modify column address varchar(100);
mysql> desc emp;
+---------+-----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-----------------+------+-----+---------+-------+
| eid | int | NO | PRI | NULL | |
| ename | varchar(20) | NO | | NULL | |
| gender | enum('男','女') | NO | | 男 | |
| address | varchar(100) | YES | | NULL | |
+---------+-----------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
#删除emp表的gender的默认值约束
alter table emp modify column gender enum('男','女'); #同时删掉了非空约束和默认值约束
mysql> desc emp;
+---------+-----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-----------------+------+-----+---------+-------+
| eid | int | NO | PRI | NULL | |
| ename | varchar(20) | NO | | NULL | |
| gender | enum('男','女') | YES | | NULL | |
| address | varchar(100) | YES | | NULL | |
+---------+-----------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
#结论：当使用modify修改字段的非空约束、默认值约束、数据类型等信息时
#注意，如果要保留的，在modify语句中要体现。
#例如：要保留非空约束，就要加not null，否则会丢掉。
#建表给字段增加默认值约束
alter table 表名称 modify 【column】 字段名 数据类型 default 默认值;
#给emp表address增加“不详”默认值
alter table emp modify column address varchar(100) default '不详';
#给emp表gender增加“男”默认值，以及非空
alter table emp modify column gender enum('男','女') default '男' not null;
mysql> desc emp;
+---------+-----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-----------------+------+-----+---------+-------+
| eid | int | NO | PRI | NULL | |
| ename | varchar(20) | NO | | NULL | |
| gender | enum('男','女') | NO | | 男 | |
| address | varchar(100) | YES | | 不详 | |
+---------+-----------------+------+-----+---------+-------+
4 rows in set (0.01 sec)14.7 自增属性
1、作用
作用：给某个字段自动赋值，这个值是一直往上增加，如果没有特意干扰的，每次自增1.
2、关键字：auto_increment
3、特点和要求
（1）一个表只能有一个自增字段，因为一个表只有一个AUTO_INCREMENT属性记录自增字段值 （2）并且自增字段只能是key字段，即定义了主键、唯一键等键约束的字段。 一般都是给主键和唯一键加自增。 （3）自增字段应该是数值类型，一般都是整数类型。 （4）AUTO_INCREMENT属性值 必须 > 当前自增字段的最大值 （5）如果自增列指定了 0 和 null，会在当前最大值的基础上自增， 如果自增列手动指定了具体值，直接赋值为具体值。
4、如何指定自增约束
（1）建表时
create table 表名称(
字段名 数据类型 primary key auto_increment,
字段名 数据类型 unique key not null,
字段名 数据类型 unique key,
字段名 数据类型 not null default 默认值,
);
create table 表名称(
字段名 数据类型 default 默认值 ,
字段名 数据类型 unique key auto_increment,
字段名 数据类型 not null default 默认值,,
primary key(字段名)
);（2）建表后（了解）
alter table 表名称 modify 字段名 数据类型 auto_increment;5、如何删除自增约束（了解）
alter table 表名称 modify 字段名 数据类型 auto_increment;#给这个字段增加自增约束
alter table 表名称 modify 字段名 数据类型; #去掉auto_increment相当于删除6、演示错误
create table employee(
eid int auto_increment,
ename varchar(20)
);
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
create table employee(
eid int primary key,
ename varchar(20) unique key auto_increment
);
ERROR 1063 (42000): Incorrect column specifier for column 'ename' 因为ename不是整数类型7、SQL示例
#演示自增属性
/*
1、作用：给某个字段自动赋值，这个值是一直往上增加，
如果没有特意干扰的，每次自增1.
2、关键字：auto_increment
3、要求
（1）一个表只能有一个自增字段，因为一个表只有一个AUTO_INCREMENT属性记录自增字段值
（2）并且自增字段只能是key字段，即定义了主键、唯一键等键约束的字段。
一般都是给主键和唯一键加自增。
（3）自增字段应该是数值类型，一般都是整数类型。
（4）AUTO_INCREMENT属性值 必须 > 当前自增字段的最大值
*/
#创建表
create table emp(
eid int auto_increment,
ename varchar(20)
);
mysql> create table emp(
-> eid int auto_increment,
-> ename varchar(20)
-> );
ERROR 1075 (42000): Incorrect table definition;
there can be only one auto column and it must be defined as a key
create table emp(
eid int primary key auto_increment,
ename varchar(20)
);
#添加数据
insert into emp(ename) values('李四');#不给自增字段指定值，也是自增
insert into emp values(null,'张三'); #给自增字段赋值NULL，也是自增
mysql> select * from emp;
+-----+-------+
| eid | ename |
+-----+-------+
| 1 | 李四 |
| 2 | 张三 |
+-----+-------+
2 rows in set (0.00 sec)
insert into emp values(0,'王五'); #给自增字段赋值0，也是自增
mysql> select * from emp;
+-----+-------+
| eid | ename |
+-----+-------+
| 1 | 李四 |
| 2 | 张三 |
| 3 | 王五 |
+-----+-------+
3 rows in set (0.00 sec)
insert into emp values(-5,'王五'); #给自增字段赋值-5（<3)
mysql> select * from emp;
+-----+-------+
| eid | ename |
+-----+-------+
| -5 | 王五 |
| 1 | 李四 |
| 2 | 张三 |
| 3 | 王五 |
+-----+-------+
4 rows in set (0.00 sec)
insert into emp values(null,'赵六');
mysql> select * from emp;
+-----+-------+
| eid | ename |
+-----+-------+
| -5 | 王五 |
| 1 | 李四 |
| 2 | 张三 |
| 3 | 王五 |
| 4 | 赵六 |
+-----+-------+
5 rows in set (0.00 sec)
#查看表定义
show create table emp;
mysql> show create table emp\G
*************************** 1. row ***************************
Table: emp
Create Table: CREATE TABLE `emp` (
`eid` int NOT NULL AUTO_INCREMENT,
`ename` varchar(20) DEFAULT NULL,
PRIMARY KEY (`eid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
insert into emp values(15, '柴');#给自增字段赋值15（>5）
mysql> select * from emp;
+-----+-------+
| eid | ename |
+-----+-------+
| -5 | 王五 |
| 1 | 李四 |
| 2 | 张三 |
| 3 | 王五 |
| 4 | 赵六 |
| 15 | 柴 |
+-----+-------+
6 rows in set (0.00 sec)
mysql> show create table emp\G
*************************** 1. row ***************************
Table: emp
Create Table: CREATE TABLE `emp` (
`eid` int NOT NULL AUTO_INCREMENT,
`ename` varchar(20) DEFAULT NULL,
PRIMARY KEY (`eid`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
#结论：当手动给自增字段赋值时，
#如果这个值大于当前“AUTO_INCREMENT”属性记录的自增值时，会修改“AUTO_INCREMENT”属性值，
#下次就从这个值基础上自增
insert into emp values(7, '宋');#给自增字段赋值7（<16）
mysql> select * from emp;
+-----+-------+
| eid | ename |
+-----+-------+
| -5 | 王五 |
| 1 | 李四 |
| 2 | 张三 |
| 3 | 王五 |
| 4 | 赵六 |
| 7 | 宋 |
| 15 | 柴 |
+-----+-------+
7 rows in set (0.00 sec)
mysql> show create table emp\G
*************************** 1. row ***************************
Table: emp
Create Table: CREATE TABLE `emp` (
`eid` int NOT NULL AUTO_INCREMENT,
`ename` varchar(20) DEFAULT NULL,
PRIMARY KEY (`eid`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
#建议大家实际开发中，不要随意修改“AUTO_INCREMENT”的值，让他自动维护。
#修改“AUTO_INCREMENT”的值
alter table 表名称 AUTO_INCREMENT = 新值;
#修改emp表AUTO_INCREMENT值为18
alter table emp AUTO_INCREMENT = 18;
mysql> show create table emp\G
*************************** 1. row ***************************
Table: emp
Create Table: CREATE TABLE `emp` (
`eid` int NOT NULL AUTO_INCREMENT,
`ename` varchar(20) DEFAULT NULL,
PRIMARY KEY (`eid`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
#修改emp表AUTO_INCREMENT值为8
alter table emp AUTO_INCREMENT = 8;
mysql> show create table emp\G
*************************** 1. row ***************************
Table: emp
Create Table: CREATE TABLE `emp` (
`eid` int NOT NULL AUTO_INCREMENT,
`ename` varchar(20) DEFAULT NULL,
PRIMARY KEY (`eid`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
#结论，无论是insert语句还是alter语句，AUTO_INCREMENT值都不能修改为<当前自增字段最大的值。
#删除自增属性
alter table 表名称 modify 【column】 字段名 数据类型;
#查看表结构
desc emp;
mysql> desc emp;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| eid | int | NO | PRI | NULL | auto_increment |
| ename | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
#删除eid字段的自增属性
alter table emp modify column eid int;
mysql> desc emp;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| eid | int | NO | PRI | NULL | |
| ename | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
#modify会影响列级约束，默认值、非空等约束，表级约束（主键、唯一键）不受影响。
#建表后添加自增属性
alter table 表名称 modify 【column】 字段名 数据类型 auto_increment;
#给eid字段的添加自增属性
alter table emp modify column eid int auto_increment;
mysql> desc emp;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| eid | int | NO | PRI | NULL | auto_increment |
| ename | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)14.8 检查约束
1、作用
检查（CHECK） 约束用于限制字段中的值的范围。如果对单个字段定义 CHECK 约束，那么该字段只允许特定范围的值。如果对一个表定义 CHECK 约束，那么此约束会基于行中其他字段的值在特定的字段中对值进行限制。
在MySQL 8.0.16版本之前， CREATE TABLE语句支持给单个字段定义CHECK约束的语法，但是不起作用。
2、关键字：check
例如MySQL8.0之前，就算给表定义了检查约束，也不起作用。在MySQL8.0.16版本之后，CREATE TABLE语句既支持给单个字段定义列级CHECK约束的语法，还支持定义表级CHECK约束的语法。
3、如何定义检查约束
（1）建表时
#在建表时，可以指定检查约束
create table 表名称(
字段名1 数据类型 check(条件), #在字段后面直接加检查约束
字段名2 数据类型,
字段名3 数据类型,
check (条件) #可以限定两个字段之间的取值条件
);
#在建表时，可以指定检查约束
create table 表名称(
字段名1 数据类型 check(条件) enforced, #在字段后面直接加检查约束
字段名2 数据类型,
字段名3 数据类型,
check (条件) enforced #可以限定两个字段之间的取值条件
);如果省略或指定为ENFORCED，则会创建检查约束并强制执行约束，不满足约束的数据行不能插入成功。
如果写的是not ENFORCED，则不满足检查约束也没关系。
（2）建表后（了解）
#如何在建表后添加检查约束，使用add check
alter table 表名称 add check(条件);4、如何查看一个表的约束（了解）
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';
#information_schema数据库名（系统库）
#table_constraints表名称（专门存储各个表的约束）
#WHERE条件
#table_name = '表名称'条件是指定表名称5、如何删除检查约束（了解）
alter table 表名称 drop check 检查约束名;6、SQL示例
#演示检查约束
/*
1、作用：
限定某个字段值是否满足某个条件。
2、关键字：check
3、特点：
（1）mysql8.0之前，mysql支持检查约束的语法，但是不起作用。
（2）mysql8中检查约束可以定义在字段后面，通常这个只限定单个字段的取值范围，
也可以定义在字段下面，通常会限定两个字段之前的取值条件。
（3）就算是定义在字段后面，虽然此时和其他字段没关系，也是当成表级约束对象，
在系统information_schema.table_constraints表中可以看到约束信息。
*/
#创建表格，限定age>=18
create table emp(
id int primary key auto_increment,
name varchar(20) not null,
age int check(age>=18)
);
#mysql5.7上面运行
insert into emp values(null,'张三',8);
mysql> select * from emp;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | 张三 | 8 | #mysql5.7检查约束没有起作用
+----+------+------+
1 row in set (0.00 sec)
#mysql8.0.26上面运行
mysql> insert into emp values(null,'张三',8);
ERROR 3819 (HY000): Check constraint 'emp_chk_1' is violated.
#在建表时，可以指定检查约束
create table 表名称(
字段名1 数据类型 check(条件), #在字段后面直接加检查约束
字段名2 数据类型,
字段名3 数据类型,
check (条件) #可以限定两个字段之间的取值条件
);
create table emp(
id int primary key auto_increment,
name varchar(20) not null,
age int check(age>=18), #列级约束
birthday date not null, #出生日期
hiredate date not null, #入职日期
check( year(hiredate)-year(birthday)>=18) #year(hiredate)表示取出入职日期的年份值
);
#添加数据
insert into emp values(null,'张三',23,'2000-1-1','2021-11-30');#满足条件
mysql> select * from emp;
+----+------+------+------------+------------+
| id | name | age | birthday | hiredate |
+----+------+------+------------+------------+
| 1 | 张三 | 23 | 2000-01-01 | 2021-11-30 |
+----+------+------+------------+------------+
1 row in set (0.00 sec)
#添加数据
insert into emp values(null,'张三',8,'2013-1-1','2021-11-30');#不满足条件
mysql> insert into emp values(null,'张三',8,'2013-1-1','2021-11-30');
ERROR 3819 (HY000): Check constraint 'emp_chk_1' is violated.
insert into emp values(null,'张三',28,'2013-1-1','2021-11-30');#不满足条件
mysql> insert into emp values(null,'张三',28,'2013-1-1','2021-11-30');#不满足条件
ERROR 3819 (HY000): Check constraint 'emp_chk_2' is violated.
#查看表结构
desc emp;
#查看表的定义
show create table emp;
mysql> show create table emp\G
*************************** 1. row ***************************
Table: emp
Create Table: CREATE TABLE `emp` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`age` int DEFAULT NULL,
`birthday` date NOT NULL,
`hiredate` date NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `emp_chk_1` CHECK ((`age` >= 18)),
CONSTRAINT `emp_chk_2` CHECK (((year(`hiredate`) - year(`birthday`)) >= 18))
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
#查询系统库中约束表查看表的约束
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';
#查看emp表的约束
SELECT * FROM information_schema.table_constraints WHERE table_name = 'emp';
#只有主键、唯一键、外键、检查约束才能这个这个表中看到，默认值、非空这里是看不到。
mysql> SELECT * FROM information_schema.table_constraints WHERE table_name = 'emp';
#如何删除检查约束
alter table 表名称 drop check 检查约束名;
#删除emp表的 age字段的检查约束 ‘emp_chk_1 ’
#删除emp表的 hiredate和birthday字段的检查约束 ‘emp_chk_2’
alter table emp drop check emp_chk_1;
alter table emp drop check emp_chk_2;
mysql> SELECT * FROM information_schema.table_constraints WHERE table_name = 'emp';
#如何在建表后添加检查约束
#使用modify？不起作用
alter table 表名称 modify 【column】 字段名 数据类型 check(条件); #列级约束
#给age字段添加检查约束 age>=18
alter table emp modify age int check(age>=18);#这里语法上没问题，但是没有真正起作用
insert into emp values(null,'李四',6,'1998-5-1','2021-1-2');#添加成功，说明检查约束没有起作用
#如何在建表后添加检查约束，使用add check
alter table 表名称 add check(条件);
#给age字段添加检查约束 age>=18
alter table emp add check(age>=18);
mysql> alter table emp add check(age>=18);
ERROR 3819 (HY000): Check constraint 'emp_chk_1' is violated.
#因为表中有违反 (age>=18)的数据，必须先处理
mysql> select * from emp;
+----+------+------+------------+------------+
| id | name | age | birthday | hiredate |
+----+------+------+------------+------------+
| 1 | 张三 | 23 | 2000-01-01 | 2021-11-30 |
| 2 | 李四 | 6 | 1998-05-01 | 2021-01-02 |
+----+------+------+------------+------------+
2 rows in set (0.00 sec)
update emp set age = 26 where id = 2;
#给age字段添加检查约束 age>=18
alter table emp add check(age>=18);
insert into emp values(null,'李四',6,'1998-5-1','2021-1-2');14.9 外键约束（了解）
外键约束会影响性能，效率，所以很多人不愿意加外键约束。
学生问题：
（1）如果两个表之间有关系（一对一、一对多），比如：员工表和部门表（一对多），它们之间是否一定要建外键约束？
答：不是的
（2）建和不建外键约束有什么区别？
答：
建外键约束，你的操作（创建表、删除表、添加、修改、删除）会受到限制，从语法层面受到限制。例如：在员工表中不可能添加一个员工信息，它的部门的值在部门表中找不到。
不建外键约束，你的操作（创建表、删除表、添加、修改、删除）不受限制，要保证数据的引用完整性，只能依靠程序员的自觉，或者是在Java程序中进行限定。例如：在员工表中，可以添加一个员工的信息，它的部门指定为一个完全不存在的部门。
（3）那么建和不建外键约束和查询有没有关系？
答：没有
1、作用
限定某个表的某个字段的引用完整性，
比如：员工表的员工所在部门的选择，必须在部门表能找到对应的部分。
2、关键字：foreign key
3、主表和从表/父表和子表
主表（父表）：被引用的表，被参考的表
从表（子表）：引用别人的表，参考别人的表
例如：员工表的员工所在部门这个字段的值要参考部门表，
部门表是主表，员工表是从表。
例如：学生表、课程表、选课表
选课表的学生和课程要分别参考学生表和课程表， 学生表和课程表是主表，选课表是从表。
4、特点
（1）在“从表”中指定外键约束，并且一个表可以建立多个外键约束
（2）创建(create)表时就指定外键约束的话，先创建主表，再创建从表
（3）删表时，先删从表（或先删除外键约束），再删除主表
（4）从表的外键列，必须引用/参考主表的键列（主键或唯一键）
为什么？因为被依赖/被参考的值必须是唯一的
（5）从表的外键列的数据类型，要与主表被参考/被引用的列的数据类型一致，并且逻辑意义一致。
例如：都是表示部门编号，都是int类型。
（6）外键列也会自动建立索引（根据外键查询效率很高，很多）
（7）外键约束的删除，所以不会自动删除，如果要删除对应的索引，必须手动删除
5、如何指定外键约束
（1）建表时
create table 主表名称(
字段1 数据类型 primary key,
字段2 数据类型
);
create table 从表名称(
字段1 数据类型 primary key,
字段2 数据类型,
foreign key （从表的某个字段) references 主表名(被参考字段)
);
#(从表的某个字段)的数据类型必须与主表名(被参考字段)的数据类型一致，逻辑意义也一样
#(从表的某个字段)的字段名可以与主表名(被参考字段)的字段名一样，也可以不一样（2）建表后（了解）
alter table 从表名称 add foreign key (从表的字段) references 主表（被引用字段) 【on update xx】【on delete xx】;6、如何查看外键约束名
desc 从表名称; #可以看到外键约束，但看不到外键约束名
show create table 从表名称; #可以看到外键约束名
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';
#information_schema数据库名（系统库）
#table_constraints表名称（专门存储各个表的约束）
#WHERE条件
#table_name = '表名称'条件是指定表名称7、如何查看外键字段索引
show index from 表名称; #查看某个表的索引名8、如何删除外键约束（了解）
删除外键约束，不会自动删除外键约束列的索引，需要单独删除。
(1)第一步先查看约束名和删除外键约束
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';#查看某个表的约束名
alter table 从表名 drop foreign key 外键约束名;
（2）第二步查看索引名和删除索引
show index from 表名称; #查看某个表的索引名
alter table 从表名 drop index 索引名;第15章 事务(了解)
后面还会代码中详细讲解
15.1 事务的特点
1、事务处理（事务操作）：保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的事务都被提交(commit)，那么这些修改就永久地保存下来；要么数据库管理系统将放弃所作的所有修改，整个事务回滚(rollback)到最初状态。
2、事务的ACID属性：
（1）原子性（Automicity） 原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
（2）一致性（Consistency） 事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
（3）隔离性（Isolation） 事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
（4）持久性（Durability） 持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响
/*
原子性：
例如：
张三给李四转账500
张三转账之前余额是1000
李四转账之前余额是1000
成功：张三账号变为500，李四变为1500，
失败：张三账号是1000，李四还是1000.
#一致性
例如：
张三给李四转账500
张三转账之前余额是1000
李四转账之前余额是1000
要么他俩的余额不变， 还是1000，总和就是2000
要么他俩的余额有变化，张三500，李四1500，总和仍然是2000
错误：
张三500，李四1000，总和是1500，结果不对
张三1000，李四1500，总和是2500，结果不对
#隔离性
例如：张三要给李四转500，
王五要给李四转500，
张三转账是否成功，和王五是否转账成功无关。
#持久性：
例如：张三要给李四转500，一旦成功提交，就转账成功，撤不回来了。
*/15.2 事务的开启、提交、回滚
MySQL默认情况下是自动提交事务。 每一条语句都是一个独立的事务，一旦成功就提交了。一条语句失败，单独一条语句不生效，其他语句是生效的。
15.2.1 手动提交模式
#开启手动提交事务模式
set autocommit = false; 或 set autocommit = 0;
上面语句执行之后，它之后的所有sql，都需要手动提交才能生效，直到恢复自动提交模式。
#恢复自动提交模式
set autocommit = true; 或 set autocommit = 1;
例如：
SET autocommit = FALSE;#设置当前连接为手动提交模式
UPDATE t_employee SET salary = 15000
WHERE ename = '孙红雷';
COMMIT;#提交
例如：
SET autocommit = FALSE;#设置当前连接为手动提交模式
UPDATE t_employee SET salary = 15000
WHERE ename = '张三';
#后面没有提交，直接关了连接，那么这句修改没有生效15.2.2 自动提交模式下开启事务
/*
也可以在自动提交模式下，开启一个事务。
(1)start transaction;
....
(3)commit; 或 rollback;
在(1)和(3)之间的语句是属于手动提交模式，其他的仍然是自动提交模式。
*/
START TRANSACTION; #开始事务
UPDATE t_employee SET salary = 0
WHERE ename = '张三';
#下面没有写commit;那么上面这句update语句没有正式生效。
commit;#提交
START TRANSACTION;
DELETE FROM t_employee;
ROLLBACK; #回滚15.2.3 DDL语句不支持事务
#说明：DDL不支持事务
#DDL：create,drop,alter等创建库、创建表、删除库、删除表、修改库、修改表结构等这些语句不支持事务。
#换句话只对insert,update,delete语句支持事务。
TRUNCATE 表名称; 清空整个表的数据，不支持事务。 把表drop掉，新建一张表。
START TRANSACTION;
TRUNCATE t_employee;
ROLLBACK; #回滚 无效15.3 事务的隔离级别
/*
mysql支持四个隔离级别：
read-uncommitted：会出现脏读、不可重复读、幻读
read-committed：可以避免脏读，会出现不可重复读、幻读
repeatable-read：可以避免脏读、不可重复读、幻读。但是两个事务不能操作（写update,delete）同一个行。
serializable：可以避免脏读、不可重复读、幻读。但是两个事务不能操作（写update,delete）同一个表。
修改隔离级别：
set transaction_isolation='隔离级别';
#mysql8之前 transaction_isolation变量名是 tx_isolation
查看隔离级别：
select @@transaction_isolation;
*/数据库事务的隔离性：数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题。一个事务与其他事务隔离的程度称为隔离级别。数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱。
-
脏读：一个事务读取了另一个事务未提交数据；
-
不可重复读：同一个事务中前后两次读取同一条记录不一样。因为被其他事务修改了并且提交了。
-
幻读：一个事务读取了另一个事务新增、删除的记录情况，记录数不一样，像是出现幻觉。
数据库提供的 4 种事务隔离级别：
| 隔离级别 | 描述 |
|---|---|
| read-uncommitted | 允许A事务读取其他事务未提交和已提交的数据。会出现脏读、不可重复读、幻读问题 |
| read-committed | 只允许A事务读取其他事务已提交的数据。可以避免脏读，但仍然会出现不可重复读、幻读问题 |
| repeatable-read | 确保事务可以多次从一个字段中读取相同的值。在这个事务持续期间，禁止其他事务对这个字段进行更新。可以避免脏读和不可重复读。但是幻读问题仍然存在。注意：mysql中使用了MVCC多版本控制技术，在这个级别也可以避免幻读。 |
| serializable | 确保事务可以从一个表中读取相同的行，相同的记录。在这个事务持续期间，禁止其他事务对该表执行插入、更新、删除操作。所有并发问题都可以避免，但性能十分低下。 |
-
Oracle 支持的 2 种事务隔离级别：READ-COMMITED, SERIALIZABLE。 Oracle 默认的事务隔离级别为: READ COMMITED 。
-
Mysql 支持 4 种事务隔离级别。 Mysql 默认的事务隔离级别为: REPEATABLE-READ。在mysql中REPEATABLE READ的隔离级别也可以避免幻读了。
4535
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
