MySQL基础(下)

整数类型	TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT
浮点类型	FLOAT、DOUBLE
定点数类型	DECIMAL
位类型	BIT
日期时间类型	YEAR、TIME、DATE、DATETIME、TIMESTAMP
文本字符串类型	CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
枚举类型	ENUM
集合类型	SET
二进制字符串类型	BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB
JSON类型	JSON对象、JSON数组
空间数据类型	单值：GEOMETRY、POINT、LINESTRING、POLYGON；集合：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION

常用的数据类型

数据类型	描述
INT	从-2^31到2^31-1的整型数据。存储大小为 4个字节
CHAR(size)	定长字符数据。若未指定，默认为1个字符，最大长度255
VARCHAR(size)	可变长字符数据，根据字符串实际长度保存，必须指定长度
FLOAT(M,D)	单精度，占用4个字节，M=整数位+小数位，D=小数位。 D<=M<=255,0<=D<=30，默认M+D<=6
DOUBLE(M,D)	双精度，占用8个字节，D<=M<=255,0<=D<=30，默认M+D<=15
DECIMAL(M,D)	高精度小数，占用M+2个字节，D<=M<=65，0<=D<=30，最大取值范围与DOUBLE相同。
DATE	日期型数据，格式'YYYY-MM-DD'
BLOB	二进制形式的长文本数据，最大可达4G
TEXT	长文本数据，最大可达4G

2.创建和管理数据库

2.1创建数据库

# 1.创建和管理数据库

# 1.1如何创建数据库
#这里介绍的创建数据库 我们都是root用户 所以有这个权限

#方式1 :#默认的utf8字符集和比较规则
CREATE DATABASE mytest1;

SHOW CREATE DATABASE mytest1;#默认的utf8字符集和比较规则

SHOW DATABASES;

#方式2 :显示的指明了要创建的数据库使用的字符集和比较规则
CREATE DATABASE mytest2 CHARACTER SET 'gbk';

SHOW DATABASES;

#方式3 : 如果要创建的数据库已经存在, 则创建不成功但是不会报错 (推荐使用)
CREATE DATABASE IF NOT EXISTS mytest3 CHARACTER SET 'utf8';#不会报错 如果数据库名字重复 就不创建新的数据库
#如果数据库名字没有重复 才会创建新的数据库

2.2管理数据库

#1.2管理数据库

#查看当前连接中的数据库都有哪些
SHOW DATABASES;

#切换数据库
USE mytest2;

#查看当前数据库中保存的数据表
SHOW TABLES;

#查看当前使用的数据库
SELECT DATABASE() FROM DUAL;

#查看指定数据库下保存的数据表
SHOW TABLES FROM mysql;

2.3修改数据库

注意：DATABASE 不能改名。一些可视化工具可以改名，它是建新库，把所有表复制到新库，再删旧库完成的。

#1.3修改数据库

#查看创建数据库的结构
SHOW CREATE DATABASE mytest1;

#更改数据库的字符集
ALTER DATABASE mytest1 CHARACTER SET 'gbk';

2.4删除数据库

#1.4删除数据库

#方式1 :
DROP DATABASE mytest1;

SHOW DATABASES;

#方式2 : 推荐 如果要删除的数据库存在 则删除成功。如果不存在 则默默退出 不会报错
DROP DATABASE IF EXISTS mytest1;

#删库默认不能回滚 所以删库跑路慎重

3.创建和管理表

3.1创建表

#如何创建表
USE atguigudb;

SHOW CREATE DATABASE atguigudb;#默认使用的是utf8

#假设拥有创建表的权限

#方式1:
CREATE TABLE IF NOT EXISTS myemp1(
id INT,
emp_name VARCHAR(15),#使用varchar来定义字符串,必须在使用时指明其长度
hire_date DATE
);

#查看表结构
DESC myemp1;

#查看创建表语句的结构
SHOW CREATE TABLE myemp1;#如果创建表的时候没有显示的指明使用的字符集, 则默认使用表所在的数据库的字符集

#查看表数据
SELECT * 
FROM myemp1;

#方式2:基于现有的表
CREATE TABLE myemp2 
AS 
SELECT employee_id, last_name, salary
FROM employees;

DESC myemp2;
DESC employees;

SELECT *
FROM myemp2;#同时还可以导入数据

#说明1:查询语句中字段别名 可以作为新创建的表的字段名称。
#说明2:此时的查询语句可以结构比较丰富,使用前面章节讲过的各种select

CREATE TABLE myemp3
AS
SELECT employee_id AS 'emp_id', last_name AS 'lname', department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;

SELECT *
FROM myemp3;

#练习1:创建一个表叫employees_copy,实现对employees表的赋值 包括表数据
CREATE TABLE employees_copy
AS
SELECT *
FROM employees;


#练习1:创建一个表叫employees_blank,实现对employees表的赋值 不包括表数据

CREATE TABLE employees_blank
AS
SELECT *
FROM employees
WHERE 1 = 2;#加一个不可能的过滤条件

SELECT * 
FROM employees_blank;

3.2修改表

#3. 修改表 ----> ALTER TABLE
DESC myemp1;

# 3.1添加一个字段
ALTER TABLE myemp1
ADD salary DOUBLE(10, 2); #默认添加到表中最后一个字段的位置

ALTER TABLE myemp1
ADD phone VARCHAR(20) FIRST;#添加到表中的第一个字段

ALTER TABLE myemp1
ADD email VARCHAR(45) AFTER emp_name;

# 3.2修改一个字段 : 数据类型 长度 默认值(略)
ALTER TABLE myemp1
MODIFY emp_name VARCHAR(25);

ALTER TABLE myemp1
MODIFY emp_name VARCHAR(35) DEFAULT 'aaa';#默认值就是当往表里插入数据的时候
#如果没有指定字段的值 那么就为默认值

# 3.3重命名一个字段
ALTER TABLE myemp1
CHANGE salary monthly_salary DOUBLE(10, 2);

ALTER TABLE myemp1
CHANGE email my_email VARCHAR(50);#在重命名一个字段的同时, 也可以修改数据类型

# 3.4删除一个字段
ALTER TABLE myemp1
DROP COLUMN my_email;

3.3重命名表

#4 重命名表
USE atguigudb;
#方式一 : 
RENAME TABLE myemp1
TO myemp11;

DESC myemp11;

#方式二 : 
ALTER TABLE myemp11
RENAME TO myemp12;

DESC myemp12;

#推荐使用方式一

3.4删除表

#5 删除表
#不光将表结构删除 同时表中的数据也删除了

DROP TABLE IF EXISTS myemp2;

#表的删除也不能回滚

3.5清空表

#6 清空表
#清空表中的所有数据 但是表结构会保留

CREATE TABLE emp_copy
AS
SELECT *
FROM employees;

SELECT *
FROM emp_copy;

TRUNCATE TABLE emp_copy;

SELECT *
FROM emp_copy;

DESC emp_copy;

4.DCL中COMMIT与ROLLBACK的使用

和事务相关的COMMIT和ROLLBACK :

COMMIT (提交) : 提交数据, 一旦执行COMMIT, 则数据就被永久的保存在了数据库中, 意味着数据不可以回滚。

ROLLBACK(回滚) : 回滚数据, 一旦执行ROLLBACK, 则可以实现数据的回滚, 回滚到最近的一次COMMIT之后。

#DCL中COMMIIT和ROLLBACK的使用
#对比TRUNCATE TABLE 和 DELETE FROM

#相同点 : 都可以实现对表中所有数据的删除, 同时保留表结构
#不同点 : 
#               TRUNCATE TABLE : 一旦执行此操作, 表数据全部清除, 同时数据是不可以回滚的
#               DELETE FROM : 一旦执行此操作, 表数据可以全部清除(不带WHERE), 同时数据可以实现回滚


/*
DDL 和 DML 的说明
1. DDL的操作一旦执行就不可以回滚
2. DML的操作默认情况下一旦执行也不可以回滚, 但是如果在执行DML之前执行了
     SET autocommit = FALSE, 则执行的DML操作就可以实现回滚了
原因 : DDL 在执行结束以后会自动COMMIT 不受 SET autocommit = FALSE 的影响

*/

#演示 : DELETE FROM
#(1)
COMMIT;#先COMMIT一下
#(2)
SELECT *
FROM myemp3;
#(3)
SET autocommit = FALSE;
#(4)
DELETE FROM myemp3;#数据清空
#(5)
SELECT *
FROM myemp3;
#(6)
ROLLBACK;#回滚
#(7)
SELECT *
FROM myemp3;#数据恢复

#演示 : TRUNCATE TABLE 
#(1)
COMMIT;#先COMMIT一下
#(2)
SELECT *
FROM myemp3;
#(3)
SET autocommit = FALSE;
#(4)
TRUNCATE TABLE myemp3;
#(5)
SELECT *
FROM myemp3;
#(6)
ROLLBACK;#回滚
#(7)
SELECT *
FROM myemp3;#数据没有恢复

阿里开发规范：

【参考】TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE 无事务且不触发 TRIGGER，有可能造成事故，故不建议在开发代码中使用此语句。

说明：TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。

5.MySQL8.0新特性 : DDL的原子化

在8.0中，执行一系列DDL语句的时候，如果出现错误，那么就会回滚到最开始一句也没有执行的时候。

在5.x中，执行一系列DDL语句的时候，如果出现错误，那么只在报错的语句停下，前面的会执行COMMIT。

# 测试MySQL 8.0新特性 : DDL的原子化
CREATE DATABASE IF NOT EXISTS mytest;

USE mytest;

CREATE TABLE book1(
book_id INT,
book_name VARCHAR(225)
);

SHOW TABLES;

DROP TABLE book1, book2;#在报错以后会回滚操作
#book1删除失败

# 测试MySQL5x 是否具有 DDL原子化特性
CREATE DATABASE IF NOT EXISTS mytest;

USE mytest;

CREATE TABLE book1(
book_id INT,
book_name VARCHAR(225)
);

SHOW TABLES;

DROP TABLE book1, book2;#分为两个操作 DROP book1, DROP book2, 先删了book1了已经
#book1删除成功

章节练习 :

#10创建和管理表章节练习

#练习一 :
#1. 创建数据库test01_office,指明字符集为utf8。并在此数据库下执行下述操作 
CREATE DATABASE IF NOT EXISTS test01_office CHARACTER SET 'utf8';

#2. 创建表dept01 
/*
字段 类型 
id INT(7) 
NAME VARCHAR(25) 
*/
USE test01_office;

CREATE TABLE IF NOT EXISTS dept01(
id INT(7),
`NAME` VARCHAR(25)
);

#3. 将表departments中的数据插入新表dept02中 
USE atguigudb;

CREATE TABLE IF NOT EXISTS dept02
AS
SELECT *
FROM departments;

SELECT *
FROM dept02;


#题目原意是
/*
CREATE TABLE IF NOT EXISTS dept02
AS
SELECT *
FROM atguigudb.departments;
*/


#4. 创建表emp01 
/*
字段 类型 
id INT(7)
first_name VARCHAR (25) 
last_name VARCHAR(25) 
dept_id INT(7) 
*/

CREATE TABLE IF NOT EXISTS emp01(
id INT(7),
first_name VARCHAR(25),
last_name VARCHAR(25),
dept_id INT(7)
);

DESC emp01;

#5. 将列last_name的长度增加到50 
ALTER TABLE emp01
MODIFY last_name VARCHAR(50);

DESC emp01;

#6. 根据表employees创建emp02 
CREATE TABLE IF NOT EXISTS emp02
AS
SELECT *
FROM employees

#7. 删除表emp01 
DESC emp01;

DROP TABLE IF EXISTS emp01;


#8. 将表emp02重命名为emp01 
DESC emp02;

RENAME TABLE emp02
TO emp01;

DESC emp01;

#9. 在表dept02和emp01中添加新列test_column，并检查所作的操作 
ALTER TABLE dept02
ADD test_column VARCHAR(10);

ALTER TABLE emp01
ADD test_column VARCHAR(10);

DESC dept02;
DESC emp01;

#10.直接删除表emp01中的列 department_id
ALTER TABLE emp01
DROP COLUMN department_id;

DESC emp01;


#练习二 : 
# 1、创建数据库 test02_market 
CREATE DATABASE IF NOT EXISTS test02_market;

SHOW CREATE DATABASE test02_market;

# 2、创建数据表 customers

USE test02_market;

CREATE TABLE IF NOT EXISTS customers(
c_num INT,
c_name VARCHAR(50),
c_contact VARCHAR(50),
c_city VARCHAR(50),
c_birth date
);

DESC customers;

# 3、将 c_contact 字段移动到 c_birth 字段后面 
ALTER TABLE customers
MODIFY c_contact VARCHAR(50) AFTER c_birth;

DESC customers;
# 4、将 c_name 字段数据类型改为 varchar(70) 
ALTER TABLE customers
MODIFY c_name VARCHAR(70);

DESC customers;

# 5、将c_contact字段改名为c_phone 
ALTER TABLE customers
CHANGE c_contact c_phone VARCHAR(50);

DESC customers;
# 6、增加c_gender字段到c_name后面，数据类型为char(1) 
ALTER TABLE customers
ADD c_gender CHAR(1) AFTER c_name;

DESC customers;

# 7、将表名改为customers_info
RENAME TABLE customers
TO customers_info;

DESC customers_info;

# 8、删除字段c_city
ALTER TABLE customers_info
DROP COLUMN c_city;

DESC customers_info;


#练习三 : 
# 1、创建数据库test03_company 
CREATE DATABASE IF NOT EXISTS test03_company;

# 2、创建表offices
USE test03_company;

CREATE TABLE IF NOT EXISTS offices(
officeCode INT,
city VARCHAR(30),
address VARCHAR(50),
country VARCHAR(50),
postalCode VARCHAR(25)
);

DESC offices;
# 3、创建表employees
CREATE TABLE IF NOT EXISTS employees(
empNum INT,
lastName VARCHAR(50),
firstName VARCHAR(50),
mobile VARCHAR(25),
`code` INT,
jobTitle VARCHAR(50),
birth date,
note VARCHAR(225),
sex VARCHAR(5)
);

DESC employees;


# 4、将表employees的mobile字段修改到code字段后面 
ALTER TABLE employees 
MODIFY mobile VARCHAR(25) AFTER `code`;

DESC employees;
# 5、将表employees的birth字段改名为birthday 
ALTER TABLE employees
CHANGE birth birthday DATE;

DESC employees;
# 6、修改sex字段，数据类型为char(1) 
ALTER TABLE employees
MODIFY sex CHAR(1);

DESC employees;
# 7、删除字段note 
ALTER TABLE employees
DROP COLUMN note;

DESC employees;

# 8、增加字段名favoriate_activity，数据类型为varchar(100) 
ALTER TABLE employees
ADD favoriate_activity VARCHAR(100);

DESC employees;
# 9、将表employees的名称修改为 employees_info
RENAME TABLE employees
TO employees_info;

DESC employees_info;

十一、数据处理之增删改

DML 操作

1.插入数据

使用 INSERT 语句向表中插入数据

1.1 VALUES的方式添加

使用这种语法一次只能往表中插入一条数据

#0 储备工作
USE atguigudb;

CREATE TABLE IF NOT EXISTS emp1(
id INT,
`name` VARCHAR(15),
hire_date DATE,
salary DOUBLE(10, 2)
);

DESC emp1;

SELECT *
FROM emp1;

#1. 添加数据

#方式一 : 一条一条添加
# ① 没有指明添加的字段
# 一定要按照声明的字段的先后顺序去添加
INSERT INTO emp1
VALUES (1, 'Tom', '2000-12-21', 3400);

#错误的
INSERT INTO emp1
VALUES (1, 'Tom', '2000-12-21', 'Jerry');# Data truncated for column 'salary' at row 1

SELECT *
FROM emp1;

# ② 指明添加的字段(推荐)
INSERT INTO emp1(id, hire_date, salary, `name`)
VALUES (2, '1999-09-09', 4000, 'Jerry');

SELECT *
FROM emp1;

INSERT INTO emp1(id, salary, `name`)
VALUES (3, 4500, 'shk');#hire_date 为 NULL 如果添加了约束 要求非空 那么就会添加失败

# ③ 同时插入多条记录(推荐)
INSERT INTO emp1(id, `name`, salary)
VALUES
(4, 'Jim', 5000),
(5, '侯德阳', 5500);

SELECT *
FROM emp1;
#注意 一个同时插入多行数据的INSERT语句等同于多个插入单行数据的INSERT语句, 但是多行的INSERT语句执行效率高

1.2 将查询结果插入表中

#方式二 : 将查询结果插入到表中
SELECT * 
FROM emp1;

INSERT INTO emp1(id, NAME, salary, hire_date)
#查询语句 查询的字段一定要与添加的字段的类型和顺序一一对应
SELECT employee_id, last_name, salary, hire_date
FROM employees
WHERE department_id IN (60, 70);

SELECT *
FROM emp1;

DESC emp1;
DESC employees;

#在使用查询语句添加的使用要提前查看添加的字段的数据类型是否一样 范围是否合适
#emp1表中要添加数据的字段的长度不能低于employees表中查询的字段的长度
#如果低于了 就有添加不成功的风险

2.更新数据

使用 UPDATE 语句更新数据 ( 修改数据 )

#2. 更新数据 (或修改数据)
#UPDATE ... SET ... WHERE ...
#可以实现批量的修改数据的

UPDATE emp1
SET hire_date = CURRENT_DATE()
WHERE id = 5;

SELECT *
FROM emp1;

#同时修改一条数据的多个字段
UPDATE emp1
SET hire_date = CURRENT_DATE(), salary = 6000
WHERE id = 4;

#题目 : 将表中包含字符是a的提薪 20%
UPDATE emp1
SET salary = salary + 0.2 * salary
WHERE `name` LIKE '%a%';

SELECT * FROM emp1;

#修改不成功例子 : 
UPDATE employees
SET department_id = 1000000
WHERE employee_id = 102;#Cannot add or update a child row: a foreign key constraint fails
#修改数据时是可能存在不成功的情况的, 可能是由于约束的影响造成的
#这里的约束是 修改的部门必须得是部门表中有的一个部门才可以修改

3.删除数据

使用 DELETE FROM WHERE 语句

#3. 删除数据
#DELETE FROM 

DELETE FROM emp1
WHERE id = 1;

SELECT * FROM emp1;

#在删除数据时 也有可能因为约束的影响 导致删除失败
DELETE FROM departments
WHERE department_id = 50;#Cannot delete or update a parent row


#小结 : DML 操作默认情况下 执行完以后都会自动提交数据
# 如果希望执行完以后不自动提交数据 则需要使用 SET autocommit = FALSE.

4.MySQL8的新特性 : 计算列

什么叫计算列呢？简单来说就是某一列的值是通过别的列计算得来的。例如，a列值为1、b列值为2，c列不需要手动插入，定义a+b的结果为c的值，那么c就是计算列，是通过别的列计算得来的。

在MySQL 8.0中，CREATE TABLE 和 ALTER TABLE 中都支持增加计算列。下面以CREATE TABLE为例进行讲解。

#MySQL8的新特性 : 计算列

USE dbtest2;

CREATE TABLE test1(
a INT,
b INT,
c INT GENERATED ALWAYS AS (a + b) VIRTUAL#字段c 作为计算列
);

INSERT INTO test1(a, b)
VALUES (10, 20);

SELECT *
FROM test1;

UPDATE test1
SET a = 100;

#c作为计算列 自动生成数据

5.综合案例

# 1、创建数据库test01_library 
CREATE DATABASE IF NOT EXISTS test01_library CHARACTER SET 'utf8';

USE test01_library;
# 2、创建表 books，表结构如下：课件
CREATE TABLE IF NOT EXISTS books(
id INT COMMENT '书编号',
`name` VARCHAR(50) COMMENT '书名',
`authors` VARCHAR(100) COMMENT '作者',
price FLOAT COMMENT '价格',
pubdate YEAR COMMENT '出版日期',
note VARCHAR(100) COMMENT '说明',
num INT COMMENT '库存'
);

DESC books;
# 3、向books表中插入记录 
# 1）不指定字段名称，插入第一条记录 
INSERT INTO books
VALUES (1, 'Tal of AAA', 'Dickes', 23, '1995', 'novel', 11);

SELECT * FROM books;
# 2）指定所有字段名称，插入第二记录 
INSERT INTO books(id, `name`, `authors`, price, pubdate, note, num)
VALUES (2, 'EmmaT', 'Jane lura', 35, '1993', 'joke', 22);

SELECT * FROM books;

# 3）同时插入多条记录（剩下的所有记录）
INSERT INTO books(id, `name`, `authors`, price, pubdate, note, num)
VALUES 
(3, 'Story of Jane', 'Jane Tim', 40, '2001', 'novel', 0),
(4, 'Lovey Day', 'George Byron', 20, '2005', 'novel', 30),
(5, 'Old land', 'Honore Blade', 30, '2010', 'law', 0),
(6, 'The Battle', 'Upton Sara', 30, '1999', 'medicine', 40),
(7, 'Rose Hood', 'Richard haggard', 28, '2008', 'cartoon', 28);

SELECT * FROM books;

# 4、将小说类型(novel)的书的价格都增加5。 
UPDATE books
SET price = price + 5
WHERE note = 'novel';

# 5、将名称为EmmaT的书的价格改为40，并将说明改为drama。 
USE test01_library;

UPDATE books
SET price = 40, note = 'drama'
WHERE name = 'EmmaT';

SELECT * FROM books;

# 6、删除库存为0的记录。
DELETE FROM books
WHERE num = 0;

SELECT * FROM books;

# 7、统计书名中包含a字母的书 
SELECT `name` 
FROM books
WHERE `name` REGEXP '[a]';

# 8、统计书名中包含a字母的书的数量和库存总量 
SELECT COUNT(*), SUM(num)
FROM books
WHERE `name` REGEXP '[a]';

# 9、找出“novel”类型的书，按照价格降序排列 
SELECT *
FROM books
WHERE note = 'novel'
ORDER BY price DESC;


# 10、查询图书信息，按照库存量降序排列，如果库存量相同的按照note升序排列 
SELECT *
FROM books
ORDER BY num DESC, note ASC;


# 11、按照note分类统计书的数量 (不是库存量)
SELECT `note`, COUNT(*)
FROM books
GROUP BY note;


# 12、按照note分类统计书的库存量，显示库存量超过30本的 
SELECT `note`, SUM(num)
FROM books
GROUP BY note
HAVING SUM(num) > 30;

# 13、查询所有图书，每页显示5本，显示第二页 
SELECT *
FROM books
LIMIT 5, 5;

# 14、按照note分类统计书的库存量，显示库存量最多的 
SELECT note, SUM(num)
FROM books
GROUP BY note
ORDER BY SUM(num) DESC
LIMIT 0, 1;


# 15、查询书名达到10个字符的书，不包括里面的空格 
SELECT NAME
FROM books
WHERE LENGTH(REPLACE(`name`, ' ', '')) > 10;

# 16、查询书名和类型，其中note值为novel显示小说，law显示法律，medicine显示医药，cartoon显示卡通， joke显示笑话 
SELECT `name`, CASE note WHEN 'novel' THEN '小说'
                         WHEN 'law'   THEN '法律'
                         WHEN 'medicine' THEN '医药'
                         WHEN 'cartoon' THEN '卡通'
                         WHEN 'joke' THEN '笑话'
                         ELSE '其他'END AS "type"
FROM books;


# 17、查询书名、库存，其中num值超过30本的，显示滞销，大于0并低于10的，显示畅销，为0的显示需要无货 
SELECT `name`, num, CASE WHEN num > 30 THEN '滞销'
                         WHEN num BETWEEN 0 AND 10 THEN '畅销'
                         WHEN num = 0 THEN '无货' 
                         ELSE '正常' END AS "说明"
FROM books;


# 18、统计每一种note的库存量，并合计总量 
SELECT IFNULL(note, '合计库存总量'), SUM(num)
FROM books
GROUP BY note WITH ROLLUP;#在最后一行显示总量


# 19、统计每一种note的数量，并合计总量 
SELECT IFNULL(note, '合计总量'), COUNT(*)
FROM books
GROUP BY note WITH ROLLUP;

# 20、统计库存量前三名的图书 
SELECT *
FROM books
ORDER BY num DESC
LIMIT 0, 3;

# 21、找出最早出版的一本书 
SELECT *
FROM books
ORDER BY pubdate ASC
LIMIT 1;

# 22、找出novel中价格最高的一本书 
SELECT *
FROM books
WHERE note = 'novel'
ORDER BY price DESC
LIMIT 1;

SELECT *
FROM books;

# 23、找出书名中字数最多的一本书，不含空格
SELECT *
FROM books
ORDER BY CHAR_LENGTH(REPLACE(NAME, ' ', '')) DESC
LIMIT 1;

章节练习

详见MySQL文件

十二、MySQL数据类型精讲

#第 12 章 MySQL数据类型精讲

#本章的内容测试建议使用 MySQL5.7进行测试

#关于属性 : character set name

#创建数据库的时候指明字符集
CREATE DATABASE IF NOT EXISTS dbtest12 CHARACTER SET 'utf8';

SHOW CREATE DATABASE dbtest12;

USE dbtest12;

#创建表的时候指明表的字符集
CREATE TABLE temp(
id INT
)CHARACTER SET 'utf8';

SHOW CREATE TABLE temp;

#创建表中字段的时候, 可以指定字段的字符集
CREATE TABLE temp1(
id INT,
NAME VARCHAR(15) CHARACTER SET 'gbk'
);

SHOW CREATE TABLE temp1;

#如果字段没有指明字符集, 默认使用创建表时指定的字符集, 如果创建表的时候
#也没有指明字符集, 那么就会默认使用创建数据库时指明的字符集, 如果创建
#数据库时也没有指定字符集, 那么就会使用MySQL的默认字符集(在my.ini配置文件中)。

SHOW VARIABLES LIKE 'character_%';

#整数数据类型
USE dbtest12;

CREATE TABLE test_int1(
f1 TINYINT,
f2 SMALLINT,
f3 MEDIUMINT,
f4 INTEGER,
f5 BIGINT
);

DESC test_int1;

INSERT INTO test_int1(f1)
VALUES (12), (-12), (-128), (127);

SELECT *
FROM test_int1;

INSERT INTO test_int1(f1)
VALUES (128);#Out of range value for column 'f1' at row 1
#超出范围了

INSERT INTO test_int1(f2)
VALUES (1231), (-1231), (-12323), (13132), (0);

SELECT *
FROM test_int1;

#5.7的整型的数据类型会更清晰的显明范围(数据的宽度)

#在没有指明显示宽度的时候会默认为下面最大的宽度

# TINYINT(4) -128 ~ 127 加上负号一共占四位
# SMALLINT(6)-32768 ~ 32767 加上负号一共占六位
# MEDIUMINT(9) -8388608 ~ 8388607 加上负号一共占九位
# INT(11) 加上负号一共占11位
# BIGINT(20) 加上负号一共占20位

DESC test_int1;

CREATE TABLE test_int2(
f1 INT,
f2 INT(5),
f3 INT(5) ZEROFILL# ① 显示宽度为 5 , 不足 5 位, 用 0 填充
);	              # ② 当使用zerofill时 会自动添加 unsigned 关键字

DESC test_int2;
 
INSERT INTO test_int2(f1, f2)
VALUES (123, 123),
(123, 123456);

SELECT *
FROM test_int2;#结果正常输出 没有限制只有五位 
#指明的是显示宽度 不会影响INT本身的11位的表示范围 只要是INT 范围就是11位 
#只有配合 ZEROFILL 才有意义

INSERT INTO test_int2(f3)
VALUES
(123),
(123456);

SELECT *
FROM test_int2;

#在 8.0 之后的版本不支持显示宽度设置了

# UNSIGNED 的使用
# 无符号的意思

# 浮点类型
# DOUBLE FLOAT 这两个数据类型在 UNSIGNED修饰以后范围不会变大 只是把负数取消了

CREATE TABLE test_double1(
f1 FLOAT,
f2 FLOAT(5, 2),
f3 DOUBLE,
f4 DOUBLE(5, 2)
);

DESC test_double1;

INSERT INTO test_double1(f1, f2)
VALUES (123.45, 123.45);

SELECT * FROM test_double1;

INSERT INTO test_double1(f3, f4)
VALUES (123.45, 123.456);#小数位做了四舍五入 f4 = 123.46

INSERT INTO test_double1(f3, f4)
VALUES (123.45, 1234.56);#整数位超出 报错

#定点数类型
# DECIMAL (M, D) M 称作精度 D称作标度 当DECIMAL没有指定精度和标度时 默认为 (10, 0)
# (5, 2) 的意思是一共五位 两个小数位 剩下的三个是整数位
# 总共占用的存储空间为 M + 2 个字节
#高精度表示类型 

CREATE TABLE test_decimal1(
f1 DECIMAL,
f2 DECIMAL(5, 2)
)

DESC test_decimal1;

INSERT INTO test_decimal1(f1)# (10, 0)
VALUES 
(123), (123.45);#存在四舍五入  123 123

SELECT * FROM test_decimal1;

INSERT INTO test_decimal1(f2)
VALUES
(999.99);#(5, 2)能显示的最大的数

INSERT INTO test_decimal1(f2)
VALUES
(12.132);#存在四舍五入

INSERT INTO test_decimal1(f2)
VALUES
(1234.67);#超出范围 报错  Out of range value for column 'f2' at row 1

#DECIMAL 实现对数据精准的保存

# 金额 等对数据精度要求特别高的使用DECIMAL

# 位类型
CREATE TABLE test_bit1(
f1 BIT,
f2 BIT(5),
f3 BIT(65)#[Err] 1439 - Display width out of range for column 'f3' (max = 64)
)

CREATE TABLE test_bit1(
f1 BIT,#默认是 BIT(1)
f2 BIT(5),
f3 BIT(64)
)

DESC test_bit1;

INSERT INTO test_bit1(f1)
VALUES
(0), (1);

SELECT * FROM test_bit1;

INSERT INTO test_bit1(f1)# BIT(1)
VALUES
(2);#默认十进制的  [Err] 1406 - Data too long for column 'f1' at row 1


INSERT INTO test_bit1(f2)
VALUES (30);

SELECT * FROM test_bit1;#result : 11110

SELECT BIN(f1), BIN(f2)#转换为二进制
FROM test_bit1;

SELECT HEX(f1), HEX(f2)#转换为十六进制
FROM test_bit1;

SELECT f1 + 0, f2 + 0 #加上一个 0 可以变成十进制
FROM test_bit1;

十三、约束

1.约束(constraint)概述

为什么需要约束？

数据完整性是指数据的精确性和可靠性。它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息提出的。

为了保证数据的完整性，SQL规范以约束的方式对表数据进行额外的条件限制。从以下四个方面考虑：

实体完整性(Entity Integrity) : 例如，同一个表中，不能存在两条完全相同无法区分的记录
域完整性(Domain Integrity) : 例如，年龄范围 0 - 120 ，性别范围男/女
引用完整性(Referential Integrity) : 例如，员工所在部门，在部门表中要能找到这个部门
用户自定义完整性(User-defined Integrity) : 例如，用户名唯一，密码不能为空等，本部门的经理工资不得高于本部门职工的平均工资的五倍。

什么是约束？

对表中字段的限制，约束是表级的强制规定。

约束的分类：

角度一 : 约束的字段个数单列约束 VS 多列约束

角度二 : 约束的作用范围列级约束 : 声明此约束时, 将此约束声明在对应字段的后面。表级约束: 在表中所有字段声明完以后, 在所有字段的后面声明的约束。

角度三 : 约束的功能

NOT NULL(非空约束)
UNIQUE(唯一性约束)
PRIMARY KEY(主键约束)
FOREIGN KEY(外键约束)
CHECK(检查约束)
DEFAULT(默认值约束)

如何添加/删除约束

CREATE TABLE 时添加约束

ALTER TABLE 时增加约束删除约束

如何查看表中的约束

SELECT * FROM information_schema.TABLE_CONSTRAINTS
WHERE table_name = '表名称';

2.非空约束

作用 : 限定某个字段/列的值不允许为空

关键字 : NOT NULL

特点 :

默认，所有类型的值都可以是NULL，包括INT、FLOAT等数据类型。
非空约束只能出现在表对象的列上，只能是某个列单独限定非空，不能组合非空。
一个表可以有很多列都分别限定了非空
空字符串''不等于 NULL，0也不等于NULL

#2. NOT NULL非空约束
CREATE DATABASE dbtest13;
USE dbtest13;
#2.1 在创建CREATE TABLE时 添加约束
CREATE TABLE test1(
id INT NOT NULL, 
last_name VARCHAR(15) NOT NULL,
email VARCHAR(25),
salary DECIMAL(10, 2)
);

DESC test1;

INSERT INTO test1(id, last_name, email, salary)
VALUES (1, 'Tom', 'tom@126.com', 3400);

SELECT * FROM test1;

INSERT INTO test1(id, last_name, email, salary)
VALUES (2, NULL, 'tom1@126.com', 3450);#Column 'last_name' cannot be null

UPDATE test1
SET id = NULL
WHERE id = 1;#Column 'id' cannot be null

#在ALTER TABLE的时候添加约束
DESC test1;

ALTER TABLE test1
MODIFY email VARCHAR(25) NOT NULL;

#在ALTER TABLE的时候删除约束
ALTER TABLE test1
MODIFY email VARCHAR(25) NULL;

3.唯一性约束

作用 : 来限制某个字段/某列的值不能重复

关键字 : UNIQUE

特点 :

同一个表可以有多个唯一的约束
唯一约束可以是某一个列的值唯一, 也可以是多个列组合的值唯一
唯一性约束允许列值为空
在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同。
MySQL会给唯一约束的列上默认创建一个唯一索引

#3. UNIQUE(唯一性)约束
#3.1 在创建CREATE TABLE时 添加约束
CREATE TABLE test2(
id INT UNIQUE,#列级约束
last_name VARCHAR(15),
email VARCHAR(25) UNIQUE,
salary DECIMAL(10, 2),

#表级约束
CONSTRAINT uk_test2_email UNIQUE(email)
);

SELECT * FROM information_schema.TABLE_CONSTRAINTS
WHERE table_name = 'test2';#在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同。

INSERT INTO test2(id, last_name, email, salary)
VALUES
(1, 'Tom', 'tom@126.com', 4500);

SELECT *
FROM test2;

INSERT INTO test2(id, last_name, email, salary)
VALUES
(1, 'Tom', 'tom@126.com', 4500);#Duplicate entry '1' for key 'id'

INSERT INTO test2(id, last_name, email, salary)
VALUES
(2, 'Jerry', NULL, 4560);#可以添加NULL NULL约束和UNIQUE约束是分开的
#而且可以多次添加NULL值

SELECT * FROM test2;

#在ALTER TABLE中添加/删除UNIQUE

DESC test2;

#方式一
ALTER TABLE test2
ADD CONSTRAINT uk_test2_sal UNIQUE(salary);


#方式二
ALTER TABLE test2
MODIFY last_name VARCHAR(15) UNIQUE;

#多列约束(复合约束)

CREATE TABLE USER(
id INT,
NAME VARCHAR(15),
PASSWORD VARCHAR(25),

#表级约束
CONSTRAINT uk_user_name_pwd UNIQUE(NAME, PASSWORD)
);

DESC USER;

INSERT INTO USER
VALUES
(1, 'Tom', 'abc');

#可以添加成功
INSERT INTO USER
VALUES 
(1, 'Tom1', 'abc');

SELECT * FROM USER;

INSERT INTO USER
VALUES
(1, 'Tom', 'abc');#Duplicate entry 'Tom-abc' for key 'uk_user_name_pwd'

/*
复合唯一性约束案例 : 
实际应用场景
*/

#学生表
CREATE TABLE student(
sid INT,
sname VARCHAR(20),
tel CHAR(11) UNIQUE KEY,
cardid CHAR(18) UNIQUE KEY
);

#课程表
CREATE TABLE course(
cid INT,
cname VARCHAR(20)
);

#选课表
CREATE TABLE student_course(
id INT,
sid INT,
cid INT,
score INT,
UNIQUE KEY (sid, cid) #复合唯一
);

insert into student values(1,'张三','13710011002','101223199012015623');#成功 
insert into student values(2,'李四','13710011003','101223199012015624');#成功 
insert into course values(1001,'Java'),(1002,'MySQL');#成功

SELECT * FROM student;

SELECT * FROM course;

insert into student_course 
values 
(1, 1, 1001, 89), 
(2, 1, 1002, 90), 
(3, 2, 1001, 88), 
(4, 2, 1002, 56);#成功

SELECT * FROM student_course;

#错误 Duplicate entry '2-1002' for key 'sid'
INSERT INTO student_course VALUES
(5, 2, 1002, 67);

#删除唯一性约束
/*
添加唯一性约束的列上也会自动创建唯一索引。
删除唯一约束只能通过删除唯一索引的方式删除。
删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样。
如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同；如果是组合列，那么默认和()
中排在第一个的列名相同。也可以自定义唯一性约束名。
*/

#删除唯一性索引
DESC test2;

ALTER TABLE test2
DROP INDEX uk_test2_email;

#注意 : 可以通过 SHOW INDEX FROM 表名; 查看表的索引

SHOW INDEX FROM test2;

4.主键约束

作用 : 用来唯一标识表中的一条记录

关键字 : primary key

特点 :

主键约束相当于唯一约束 + 非空约束的组合，主键约束不允许重复，也不允许出现空值
使用主键的概念保证实体完整性 (一张表中不能同时出现两条相同的记录)
一个表最多只能有一个主键约束，建立主键约束可以在列级别创建，也可以在表级别创建
主键约束对应表中的一列或者多列(复合主键)
如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复
MySQL的主键名总是PRIMARY，就算自己命名了主键约束名也没用
当创建主键约束时，系统默认会在所在的列组合上建立对应的主键索引(能够根据主键查询的，就根据主键查询，效率更高)，如果删除主键约束了，主键约束对应的索引就自动删除了
注意：不要修改主键字段的值，因为主键是数据记录的唯一标识，如果修改了主键字段的值，就有可能破坏数据的完整性

#PRIMARY KEY(主键约束)
#在CREATE TABLE时添加约束
USE dbtest13;

CREATE TABLE test3(
id INT PRIMARY KEY,#Error : Multiple primary key defined
#一个表中不能有两个主键
last_name VARCHAR(15) PRIMARY KEY,
salary DECIMAL(10, 2),
email VARCHAR(25)
);

#主键约束的特征 : 非空且唯一, 用于唯一的标识表中的一条记录
CREATE TABLE test3(
id INT PRIMARY KEY,#列级约束
last_name VARCHAR(15),
salary DECIMAL(10, 2),
email VARCHAR(25)
);

CREATE TABLE test4(
id INT,
last_name VARCHAR(15),
salary DECIMAL(10, 2),
email VARCHAR(25),
#表级约束
CONSTRAINT pk_test5_id PRIMARY KEY(id)
#没有必要起名字
);

SELECT * FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_NAME = 'test4';#虽然给约束重命名了 但是名字还是PRIMARY 
#这也证明一个表中只能有一个主键约束

INSERT INTO test3(id, last_name, salary, email)
VALUES(1, 'Tom', 4500, 'tom@126.com');

SELECT * FROM test3;

#满足唯一性约束
INSERT INTO test3(id, last_name, salary, email)
VALUES(1, 'Tom', 4500, 'tom@126.com');#[Err] 1062 - Duplicate entry '1' for key 'PRIMARY'

#满足非空约束
UPDATE test3
SET id = NULL
WHERE id = 1;#[Er r] 1048 - Column 'id' cannot be null

#复合主键
CREATE TABLE USER1(
id INT,
NAME VARCHAR(15),
PASSWORD VARCHAR(25),

PRIMARY KEY(NAME, PASSWORD)
);

DESC USER1;

INSERT INTO USER1
VALUES#复合主键 联合在一起唯一就可以
(1, 'Tom', 'abc'),
(1, 'Tom1', 'abc'),
(1, 'Tom2', 'abc');

SELECT * FROM USER1;

#在ALTER TABLE的时候添加主键约束
CREATE TABLE test5(
id INT,
last_name VARCHAR(15),
salary DECIMAL(10, 2),
email VARCHAR(25)
);

DESC test5;

ALTER TABLE test5
ADD PRIMARY KEY(id);

#如何删除主键约束(在实际开发中不会使用)
ALTER TABLE test5
DROP PRIMARY KEY;#删除了以后 NOT NULL会保留

5.自增长列

AUTO_INCREMENT

作用 : 某个字段的值自增

关键字 : AUTO_INCREMENT

特点和要求 :

一个表最多只能有一个自增长列
当需要产生唯一标识符或顺序值时, 可设置自增长
自增长列约束的列必须是键列(主键列, 唯一键列)
自增约束的列的数据类型必须是整数型

#自增列 AUTO_INCREMENT

#在CREATE TABLE时添加

CREATE TABLE test7(
id INT AUTO_INCREMENT,
last_name VARCHAR(15)#必须加在键列上 且数据类型为整数类型
);#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

CREATE TABLE test7(
id INT PRIMARY KEY AUTO_INCREMENT,
last_name VARCHAR(15)#必须加在键列上
);

INSERT INTO test7(last_name)
VALUES('Tom');#自动赋值id

SELECT * FROM test7;

#当我们向包含了自增的主键上添加 0 或者 NULL 时 会自动往上添加指定字段的数值
INSERT INTO test7(id, last_name)
VALUES (0, 'Tom'),(NULL, 'Tom');

INSERT INTO test7(id, last_name)
VALUES (-1, 'Jerry');#填入非0或NULL的数值时, 会按照填入的数据写 并且主键会给表中记录自动排序

#开发中 一旦主键作用的字段上声明自增 AUTO_INCREMENT
#则在添加数据的时候就不要给主键对应的字段赋值了
INSERT INTO test7(last_name)
VALUES ('HELLO');

#在ALTER TABLE时添加
CREATE TABLE test8(
id INT, 
last_name VARCHAR(15),
CONSTRAINT PRIMARY KEY(id)
);

DESC test8;

ALTER TABLE test8
MODIFY id INT AUTO_INCREMENT;
 
#删除自增约束

ALTER TABLE test8
MODIFY id INT;


#自增列出现裂缝问题
CREATE TABLE test9(
id INT PRIMARY KEY AUTO_INCREMENT
);

INSERT INTO test9
VALUES (0), (0), (0), (0);

SELECT * FROM test9;

DELETE FROM test9
WHERE id = 4;#删除4号id

INSERT INTO test9
VALUES (0);

SELECT * FROM test9;#从5开始自增

DELETE FROM test9
WHERE id = 5;

#重启MySQL5.7服务器后
USE dbtest13;
SELECT * FROM test9;

INSERT INTO test9
VALUES (0);#会从4开始自增

/*
原因 : MySQL5.7中AUTO_INCREMENT在内存中维护一个值 保持自增
重启的时候 AUTO_INCREMENT会重新在表中获取这个值 重新维护
*/

6.外键约束

FOREIGN KEY

作用 : 限定某个表的某个字段的引用完整性

比如 : 员工表的员工所在的部门的选择，必须在部门表能找到对应的部门。

通过外键关联到另外一个表中的主键

关键字 : FOREIGN KEY

主表和从表/父表和子表 :

主表(父表) : 被引用的表，被参考的表

从表(子表) : 引用别人的表，参考别人的表

特点 :

（1）从表的外键列，必须引用/参考主表的主键或唯一约束的列。为什么？因为被依赖/被参考的值必须是唯一的

（2）在创建外键约束时，如果不给外键约束命名，默认名不是列名，而是自动产生一个外键名（例如student_ibfk_1;），也可以指定外键约束名

（3）创建(CREATE)表时就指定外键约束的话，先创建主表，再创建从表

（4）删表时，先删从表（或先删除外键约束），再删除主表

（5）当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据

（6）在“从表”中指定外键约束，并且一个表可以建立多个外键约束

（7）从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样，逻辑意义一致。如果类型不一样，创建子表时，就会出现错误“ERROR 1005 (HY000): Can't create table'database.tablename'(errno: 150)”。例如：都是表示部门编号，都是int类型。

（8）当创建外键约束时，系统默认会在所在的列上建立对应的普通索引。但是索引名是列名，不是外键约束名(根据外键查询效率很高)

（9）删除外键约束后，必须手动删除对应的索引

(10) 外键约束不能跨存储引擎使用

#FOREIGN KEY约束

#在CREATE TABLE 时添加

#主表和从表 : 父表和子表

#先创建主表
USE dbtest13;

SHOW TABLES;

CREATE TABLE dept1(
dept_id INT,
dept_name VARCHAR(15)
);

#再创建从表
CREATE TABLE emp1(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(15),
department_id INT,

#表级约束
CONSTRAINT fk_emp1_dept_id FOREIGN KEY(department_id) REFERENCES dept1(dept_id)
);#报错 : 主表没有主键约束

#给主表添加主键约束
ALTER TABLE dept1
ADD PRIMARY KEY (dept_id);

#再创建从表
CREATE TABLE emp1(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(15),
department_id INT,

#表级约束
CONSTRAINT fk_emp1_dept_id FOREIGN KEY(department_id) REFERENCES dept1(dept_id)
);#成功

DESC emp1;

#演示外键的效果 
INSERT INTO emp1
VALUES (1001, 'Tom', 10);#Cannot add or update a child row: a foreign key constraint fails
#主表没有十号部门 添加失败

INSERT INTO dept1
VALUES (10, 'IT');

#在主表中添加了10号部门以后就可以成功了
INSERT INTO emp1
VALUES (1001, 'Tom', 10);#添加成功

DELETE FROM dept1
WHERE dept_id = 10;# Cannot delete or update a parent row: a foreign key constraint fails 
#删除失败 从表存在10号部门

#在ALTER TABLE 时添加外键约束
CREATE TABLE dept2(
dept_id INT PRIMARY KEY,
dept_name VARCHAR(15)
);

DESC dept2;

CREATE TABLE emp2(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(15),
department_id INT
);

ALTER TABLE emp2
ADD CONSTRAINT fk_emp2_dept_id FOREIGN KEY(department_id) REFERENCES dept2(dept_id);

SELECT * FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_NAME = 'emp2';

约束等级 :

Cascade方式：在父表上update/delete记录时，同步update/delete掉子表的匹配记录

Set null方式：在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子表的外键列不能为not null

No action方式：如果子表中有匹配的记录，则不允许对父表对应候选键进行update/delete操作

Restrict方式：同no action，都是立即检查外键约束

Set default方式（在可视化工具SQLyog中可能显示空白）：父表有变更时，子表将外键列设置成一个默认的值，但Innodb不能识别

如果没有指定等级，就相当于Restrict方式。

对于外键约束，最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式。

#约束等级 
#演示 : 
#ON UPDATE CASCADE ON DELETE SET NULL;

CREATE TABLE dept( 
did INT PRIMARY KEY, #部门编号 
dname VARCHAR(50) #部门名称 
);

CREATE TABLE emp( 
eid INT PRIMARY KEY, #员工编号 
ename VARCHAR(5), #员工姓名 
deptid INT, #员工所在的部门
FOREIGN KEY (deptid) REFERENCES dept(did) ON UPDATE CASCADE ON DELETE SET NULL
#把修改操作设置为级联修改等级，把删除操作设置为set null等级 
);

#这样在更新和删除的时候会有对应的效果
#结论 : 对于外键约束，最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式。

删除外键约束

#删除外键约束

#一个表可以声明多个外键约束
USE atguigudb;

SELECT * FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_NAME = 'employees';

USE dbtest13;

ALTER TABLE emp1
DROP FOREIGN KEY fk_emp1_dept_id;

DESC emp1;

#再手动的删除外键约束对应的普通索引

#查看索引 : 
SHOW INDEX FROM emp1;

ALTER TABLE emp1
DROP INDEX fk_emp1_dept_id;#按照外键约束名字来删

说明 : 阿里巴巴开发规范在 MySQL 里，外键约束是有成本的，需要消耗系统资源。对于大并发的 SQL 操作，有可能会不适合。比如大型网站的中央数据库，可能会因为外键约束的系统开销而变得非常慢。所以， MySQL 允许你不使用系统自带的外键约束，在应用层面完成检查数据一致性的逻辑。也就是说，即使你不用外键约束，也要想办法通过应用层面的附加逻辑，来实现外键约束的功能，确保数据的一致性。

7.检查约束

作用 : 检查某个字段的值是否符号xx要求，一般指的是值的范围

关键字 : CHECK

注意 : MySQL5.7不支持，MySQL8.0支持

# CHECK约束
USE dbtest2;

CREATE TABLE test10(
id INT,
last_name VARCHAR(15),
salary DECIMAL(10, 2) CHECK (salary > 2000)
);

INSERT INTO test10
VALUES (1, 'Tom', 2500);

INSERT INTO test10
VALUES (1, 'Tom', 1500);#[Err] 3819 - Check constraint 'test10_chk_1' is violated.
#添加失败

8.默认约束

作用 : 给某个字段/某列指定默认值，一旦设置默认值，在插入数据时，如果此字段没有显式赋值，则赋值为默认值。

关键字 : DEFAULT

#DEFAULT 约束

#在CREATE TABLE 时添加DEFAULT约束
CREATE TABLE test11(
id INT,
last_name VARCHAR(15),
salary DECIMAL(10, 2) DEFAULT 2000
);

DESC test11;

INSERT INTO test11
VALUES (1, 'Tom', 3000);

SELECT * FROM test11;

INSERT INTO test11(id, last_name)
VALUES (2, 'Jerry');#默认工资为2000

#在ALTER TABLE 添加约束
CREATE TABLE test12(
id INT,
last_name VARCHAR(15),
salary DECIMAL(10, 2)
);

ALTER TABLE test12
MODIFY salary DECIMAL(10, 2) DEFAULT 2500;

#在ALTER TABLE 时删除默认值约束
ALTER TABLE test12
MODIFY salary DECIMAL(10, 2);#不加DEFAULT就行

章节练习

#第13章 约束章节练习

#练习一 : 
CREATE DATABASE test04_emp; 

use test04_emp; 

CREATE TABLE emp2( 
id INT, 
emp_name VARCHAR(15) 
);

CREATE TABLE dept2( 
id INT, 
dept_name VARCHAR(15) 
);

#1.向表emp2的id列中添加PRIMARY KEY约束 
ALTER TABLE emp2
ADD PRIMARY KEY(id);

DESC emp2;
#2. 向表dept2的id列中添加PRIMARY KEY约束 
ALTER TABLE dept2
ADD PRIMARY KEY(id);

DESC emp2;
#3. 向表emp2中添加列dept_id，并在其中定义FOREIGN KEY约束，与之相关联的列是dept2表中的id列。
ALTER TABLE emp2
ADD dept_id INT;

ALTER TABLE emp2
ADD CONSTRAINT fk_emp2_dept_id FOREIGN KEY(dept_id) REFERENCES dept2(id);

DESC emp2;

#练习二 : 
# 1、创建数据库test01_library 
CREATE DATABASE IF NOT EXISTS test01_library CHARACTER SET 'utf8';

USE test01_library;
# 2、创建表 books，表结构如下：课件
CREATE TABLE IF NOT EXISTS books(
id INT COMMENT '书编号',
`name` VARCHAR(50) COMMENT '书名',
`authors` VARCHAR(100) COMMENT '作者',
price FLOAT COMMENT '价格',
pubdate YEAR COMMENT '出版日期',
note VARCHAR(100) COMMENT '说明',
num INT COMMENT '库存'
);

DESC books;

# 3、使用ALTER语句给books按如下要求增加相应的约束
#方式一 : 
ALTER TABLE books
ADD PRIMARY KEY(id);

ALTER TABLE books
MODIFY id INT AUTO_INCREMENT;
#方式二 : 
ALTER TABLE books
MODIFY id INT PRIMARY KEY AUTO_INCREMENT;

ALTER TABLE books
MODIFY name VARCHAR(50) NOT NULL;

ALTER TABLE books
MODIFY authors VARCHAR(100) NOT NULL;

ALTER TABLE books
MODIFY price FLOAT NOT NULL;

ALTER TABLE books
MODIFY pubdate YEAR NOT NULL;

ALTER TABLE books
MODIFY num INT NOT NULL;

DESC books;

#练习三 : 
#1. 创建数据库test04_company 
CREATE DATABASE IF NOT EXISTS test04_company CHARACTER SET 'utf8';

SHOW CREATE DATABASE test04_company;

#2. 按照下表给出的表结构在test04_company数据库中创建两个数据表offices和employees
CREATE TABLE IF NOT EXISTS offices(
officeCode INT PRIMARY KEY AUTO_INCREMENT,
city VARCHAR(50) NOT NULL,
address VARCHAR(50),
country VARCHAR(50) NOT NULL,
postalCode VARCHAR(15) UNIQUE
);

DESC offices;

CREATE TABLE IF NOT EXISTS employees(
employeeNumber INT PRIMARY KEY AUTO_INCREMENT,
lastName VARCHAR(50) NOT NULL,
firstName VARCHAR(50) NOT NULL,
mobile VARCHAR(25) UNIQUE,
officeCode INT NOT NULL,
jobTitle VARCHAR(50),
birth DATETIME NOT NULL,
note VARCHAR(255),
sex VARCHAR(5),
CONSTRAINT fk_emp_offCode FOREIGN KEY(officeCode) REFERENCES offices(officeCode)
);

DESC employees;

#3. 将表employees的mobile字段修改到officeCode字段后面 
ALTER TABLE employees
MODIFY mobile VARCHAR(25) AFTER officeCode;#不用管唯一约束也不受影响

DESC employees;

#4. 将表employees的birth字段改名为employee_birth 
ALTER TABLE employees 
CHANGE birth employee_birth DATETIME NOT NULL;#非空约束得加上

DESC employees;
#5. 修改sex字段，数据类型为CHAR(1)，非空约束 
ALTER TABLE employees
MODIFY sex CHAR(1) NOT NULL;

DESC employees;
#6. 删除字段note 
ALTER TABLE employees
DROP COLUMN note;

#7. 增加字段名favoriate_activity，数据类型为VARCHAR(100) 
ALTER TABLE employees
ADD favoriate_activity VARCHAR(100);

DESC employees;
#8. 将表employees名称修改为employees_info
RENAME TABLE employees
TO employees_info;

DESC employees_info;