mySql笔记之基础篇（参看尚硅谷视频）

️楠小弟

已于 2023-02-23 11:57:56 修改

阅读量841

点赞数 1

分类专栏： java后端编程语言-基础篇文章标签： mysql 数据库

于 2022-02-16 23:03:21 首次发布

本文链接：https://blog.csdn.net/qq_45066822/article/details/122704732

版权

java后端编程语言-基础篇专栏收录该内容

3 篇文章 0 订阅

订阅专栏

文章目录

一、cmd操作mysql
- 1、使用命令进入MySQL
- 2、使用命令启动关闭服务
- 3、使用命令退出
- 4、使用命令查看数据库版本
- 5、使用命令显示MySQL中的数据库
- 6、查看MySQL使用的编码格式
- 7、使用命令操作mysql
- 8、导入现有的数据表、表的数据。
二、开启mysql之路
- 1、最基本的select语句
- 2、列的别名（as）
- 3、去除重复行（distinct）
- 4、空值参与运算
- 5、着重号 ``
- 6、查询常数
- 7、显示表结构（describe）
- 8、过滤数据（where关键字）
- 9、练习题：
三、运算符
- A：算术运算符
- B：比较运算符
- C、关键字的使用：
- 练习题：
四、排序与分页
- 1、排序规则
- 2、分页操作（limit）
- 3、练习题
五、多表查询
- 1、多表查询的分类
- 2、练习题：
六、单行函数
- 6.1、什么是函数
- 6.2、MYSQL的内置函数与分类
- 6.3、基本函数
- 6.4 、角度与弧度互换函数
- 6.5、三角函数
- 6.6、指数与对数
- 6.7、进制之间的转换
- 6.8、字符串函数
- 6.8、时间和日期函数
- 6.9、流程控制函数
- 6.10 、加密与解密函数
- 6.11、MYSQL信息函数
- 6.12、其他函数
- 6.13、练习题：
七、聚合函数
- 7.1、常见的几个聚合函数
- 7.2、GROUP BY的使用
- 7.3、HAVING的使用
- 7.4、SQL底层执行原理
- 7.5、练习题：
八、子查询
- 8.1、需求分析与问题解决
- 8.2、子查询的基本使用
- 8.3、子查询的分类
- 8.4、单行子查询
- 8.5、多行子查询
- 8.6、相关子查询
- 8.7、 EXISTS 与 NOT EXISTS关键字
- 8.8、相关更新
- 8.9、相关删除
- 8.10、抛一个问题
- 8.11、练习：
九、创建和管理表
- 9.1、标识符命名规则
- 9.2、MySQL中的数据类型
- 9.3、创建和管理数据库
- 9.4、创建表
- 9.5、修改表
- 9.6、重命名表
- 9.7、删除表
- 9.8、清空表
- 9.9、commit和rollback
- 9.10、DDL和DML的说明
- 9.11、MySQL8新特性—DDL的原子化
- 9.12、练习题：
十、数据处理之增删改
- 10.1、添加数据
- 10.2、更新数据（修改数据）
- 10.3、删除数据
- 10.4、MySQL8的新特性：计算列
- 10.5、综合案例：
- 10.6、本章练习
十一、约束
- 11.1、约束的分类
- 11.2、非空约束
- - 11.2.1、删除非空约束
- 11.3、唯一性约束
- - 11.3.1、关于复合唯一约束
  - 11.3.2、删除唯一约束
- 11.4、PRIMARY KEY 约束
- 11.5、自增列：AUTO_INCREMENT
- - 11.5.1、如何指定自增约束
  - 11.5.2、如何删除自增约束
- 11.6、 MySQL 8.0新特性—自增变量的持久化
- 11.7、FOREIGN KEY 约束
- 11.8、 CHECK 约束
- 11.9、DEFAULT约束
- - 11.9.1、如何给字段加默认值
  - 11.9.2、如何删除默认值约束
十二、视图
- 12.1、常见的数据库对象
- 12.2、视图的理解
- 12.3、创建视图
- 12.4、查看视图
- 12.5、. 更新视图的数据
- - 12.5.1、一般情况
  - 12.5.2、不可更新的视图
- 12.6、修改、删除视图
- - 12.6.1、修改视图
  - 12.6.2、删除视图
- 12.7、总结
- - 12.7.1、视图的优点
  - 12.7.2、视图的不足
- 12.8、练习
十三、存储过程与函数
十四、变量、流程控制与游标
十五、触发器
十六、mysql 8.0 新特性介绍

一、cmd操作mysql

1、使用命令进入MySQL

//cmd中输入：
mysql -u(用户名) -P(端口号)  -h(ip地址) -p(密码)
eg：mysql -uroot -P3306 -hlocalhost -p
#若在本机使用默认的，可省略 端口号、IP地址

2、使用命令启动关闭服务

#启动 mysql 服务命令：
net start mysql服务名
eg：net start mysql80

#停止 mysql 服务命令
net stop mysql服务名
eg：net stop mysql80

#注意：若显示“拒绝访问”，请使用“管理员window终端” 或 以 管理员身份 运行 cmd 。

3、使用命令退出

#方式一：
exit
#方式二：
quit
#方式三：
按下：ctrl + z

4、使用命令查看数据库版本

#方式一（在cmd未登进MySQL时）：
mysql -V(注意是大写的)

#方式二（在cmd未登进mysql时）：
mysql --version(注意有两个连接符)

#方式三（登进MySQL后）：
select version();（注意带上 分号）。

5、使用命令显示MySQL中的数据库

show databases; (注意带上 分号)

6、查看MySQL使用的编码格式

注意：5.x版本的mysql数据库需要手动配置编码格式！！

show variables like 'character_%';
show variables like 'collation_%';

7、使用命令操作mysql

创建数据库：

create database liu;

查看数据库的创建信息

show create database liu;

创建表：

#先确定在哪个数据库中创建表
use liu;
create table student(id int,name varchar(10));

查看表的创建信息

show create table student;

8、导入现有的数据表、表的数据。

方式一：在cmd命令行中执行

source 文件的全路径名
例子：source d:\liu.sql;

方式二：在图形工具中导入数据

比如：SQLyog中 选择 “工具”---“执行sql脚本”--选中xxx.sql即可。

二、开启mysql之路

1、最基本的select语句

-- ①
select 1+1 , 3*2 ;
相当于：
select 1+1 , 3*2 from dual; #dual是“伪表”。

-- ②
select *from employees;
#“*”表示 表中的所有的字段（或列）

2、列的别名（as）

#as的全称：alias（别名），可省略。
select userName name from student；

#不省略的情况
select userName as name from student；

#列的别名可以使用一对“”双引号引起来，不要使用‘’单引号 （遵守约定）
select userName as “name” from student；

3、去除重复行（distinct）

#例如查询信息部有哪些班级
#①错误的写法：没有去重的情况
select classId from emp;

#②正确的写法：去重的情况
select distinct classId from emp；

#③错误情况二：
select stuName , distinct classId from emp;

#④错误情况三：
#仅仅是没有报错，但没有实际意义，没能去掉重复的
select distinct classId,stuName;

#注意：distinct 关键字只能放在前面，若放后面则报错。

4、空值参与运算

空值：null
null 不等同于 0、’ '，‘null’
空值参与运算：结果一定也为空。

select employee_id,salary as "月工资" , salary * (1+commission_pct) * 12 as "年工资" ,commission_pct from employees;
#查询后可看到 与 空值 运算的 结果也为空。

实际问题的解决方案(针对上面的情况)：引入ifnull

select employee_id,salary as "月工资" , salary * (1+ ifnull(commission_pct,0)) * 12 as "年工资" ,commission_pct from employees;

5、着重号 ``

#使用场景：当字段、表名 和 mysql中 的保留字一样时，使用着重号标记就可以正常使用了。
select * from  `order`;

6、查询常数

select '刘',123,stuName,stuSex from stu;
#注意：“刘”，123都不是 stu表中的，这两个就是为常数了，
#此用法可显示一个学生对应的所有成绩（应该是这样）。

7、显示表结构（describe）

#显示表中字段的详细信息
#方式一：
describe employees;

#方式二：
desc employees；

8、过滤数据（where关键字）

#练习：查询90号部门的员工信息
select * from employees  where department_id=90;

#练习：查询last_name为’King‘的员工信息
select * from employees where last_name='King';

9、练习题：

# 1.查询员工12个月的工资总和，并起别名为ANNUAL SALARY
SELECT employee_id , last_name,salary * 12 "ANNUAL SALARY"
FROM employees;

SELECT employee_id,last_name,salary * 12 * (1 + IFNULL(commission_pct,0)) "ANNUAL
SALARY"
FROM employees;

# 2.查询employees表中去除重复的job_id以后的数据
SELECT DISTINCT job_id
FROM employees;

# 3.查询工资大于12000的员工姓名和工资
SELECT last_name, salary
FROM employees
WHERE salary > 12000;

# 4.查询员工号为176的员工的姓名和部门号
SELECT last_name, department_id
FROM employees
WHERE employee_id = 176;

# 5.显示表 departments 的结构，并查询其中的全部数据
show create table departments;

三、运算符

A：算术运算符

算术运算符有：

+（加法）、-（减法）、*（乘法）、/（div）除法、%（mod）求余。

1、加法：“+”

select 10+10,10+'a','a'+'b' ,10+null from dual;

#结果为：20、10、0，null。
#在mysql中，“+”只有运算作用，没有 连接作用。
#在mysql中，任何字符与 null 参与运算 ，结果都为 null

2、减法：“-”

select 10-5,10-'a','a'-10 ,10-null from dual;
#结果为：5、10、-10、null。
#在mysql中 不是数字的字符默认为0.
#在mysql中，任何字符与 null 参与运算 ，结果都为 null

3、乘法：“*”

select 10*2,10*'a' ,10*null from dual;
#结果为：20、0、null。

4、除法：“/ 或 div”

select 10/2,6 div 3,9 div -3,2/0 , 0/2,'a'/2 from dual;
结果为：5、2、-3、null、0、0。
#注意：当分母为0时，结果为 null，当分子为0时，结果为0。

5、取模运算：“% 或 mod”

SELECT 10 % 2,5 % 3,6 % -5,-6 % 5 ,-6 mod -5 FROM DUAL;
#结果为：0、2、1、-1、-1。
/*
注意：
当分子为正时，结果为正（分母为负也是），
当分子为负时，结果为负（分母为负也是）。
*/

B：比较运算符

比较运算符有：=、<=>、<>、!=、<、<=、>、>=

1、“=”：等于的使用

SELECT 1=2, 1 != 2,1=1,1='1',1='a','a'='b','a'='a' FROM DUAL;
#结果为：0、1、1、1、0、0、1。
#0为false，1为true。
#字符串存在隐式转换，若转换不了则默认为0
select 1=null，null=null，null='null' from dual；
#结果为：null、null、null

2、“<=>”：安全等于的使用

’<=>‘和’=‘的唯一区别：’<=>‘可以判断null。

SELECT 1<=>1,1<=>5,null<=>null,
null<=>'null',1<=>null,'a'<=>'a',
'a'<=>'null','a'<=>null 
FROM DUAL;
#结果：1、0、1、0、0、1、0、0

#练习：获取employees 表中commission_pct 为null的信息

#方式一：使用’<=>‘
SELECT last_name,salary,commission_pct 
FROM employees
WHERE commission_pct <=>NULL;

#方式二：使用 IS NULL
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct IS NULL;

#方式三：使用函数 ISNULL()
SELECT last_name,salary,commission_pct
FROM employees
WHERE ISNULL(commission_pct);

3、” != “或“<>”：不等于的使用


SELECT 3<>2,2<>'2',NULL<>NULL,NULL<>'NULL','a'<>'b' 
FROM DUAL;
#结果如下：1、0、null、null、1

4、“> ,>=,<,<=”的使用

select 2>1,2>=2,2<1,2<=2 from dual;
#结果：1，1，0，1

C、关键字的使用：

1、IS NULL、IS NOT NULL、ISNULL的使用

#练习一：获取employees 表中commission_pct 为null的信息

#方式一：使用关键字 IS NULL
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct IS NULL;

#方式二：使用函数 ISNULL()
SELECT last_name,salary,commission_pct
FROM employees
WHERE ISNULL(commission_pct);

#方式三：使用’<=>‘
SELECT last_name,salary,commission_pct 
FROM employees
WHERE commission_pct <=>NULL;

#练习二：查询表中commission_pct不为null的数据有哪些

方式一：使用关键字：IS NOT NULL
SELECT last_name,salary,commission_pct 
FROM employees
WHERE commission_pct IS NOT NULL;

方式二：对函数ISNULL()取反
SELECT last_name,salary,commission_pct 
FROM employees
WHERE NOT ISNULL(commission_pct);

方式三：对运算符 <=> 取反
SELECT last_name,salary,commission_pct 
FROM employees
WHERE NOT commission_pct <=>NULL;

2、LEAST()最小值、GREATEST()最大值

#练习一：使用函数 获取最小值、最大值
SELECT LEAST(5,9,1,'a'),GREATEST(5,9,1,'a') FROM DUAL;
结果：1、a

3、BETWEEN 条件1(下界) AND 条件2(上界)

查询条件1和条件2范围内的数据，包含边界

#练习一：查询工资在6000 到 8000的员工信息

SELECT last_name,salary,commission_pct
FROM employees
WHERE salary BETWEEN 6000 AND 8000;

#注意：交换6000 和 8000之后，查询不到数据
SELECT employee_id,last_name,salary
FROM employees
WHERE salary BETWEEN 8000 AND 6000;

#练习二：查询工资不在6000 到 8000的员工信息

方式一：使用 between...and
SELECT employee_id,last_name,salary
FROM employees
WHERE salary NOT BETWEEN 6000 AND 8000;

方式二：使用运算符
SELECT employee_id,last_name,salary
FROM employees
WHERE salary <6000 OR salary>8000;

4、IN()，NOT IN()的使用

#练习一：查询部门为10,20,30部门的员工信息

SELECT last_name,salary, department_id
FROM  employees
WHERE department_id IN(10,20,30);

#练习二：查询工资不是6000,7000,8000的员工信息

SELECT last_name,salary,commission_pct
FROM employees
WHERE salary NOT IN(6000,7000,8000);

5、like 模糊查询

# % : 代表不确定个数的字符 （0个，1个，或多个）

#练习：查询last_name中包含字符'a'的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%';

#练习：查询last_name中以字符'a'开头的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE 'a%';

#练习：查询last_name中包含字符'a'且包含字符'e'的员工信息
#写法1：
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%' AND last_name LIKE '%e%';
#写法2：
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%e%' OR last_name LIKE '%e%a%';

# _ ：代表一个不确定的字符

#练习：查询第3个字符是'a'的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE '__a%';

#练习：查询第2个字符是_且第3个字符是'a'的员工信息
#需要使用转义字符: \ 
SELECT last_name
FROM employees
WHERE last_name LIKE '_\_a%';

#或者  (了解)
SELECT last_name
FROM employees
WHERE last_name LIKE '_$_a%' ESCAPE '$';

6、REGEXP \ RLIKE :正则表达式

SELECT 'shkstart' REGEXP '^shk',
 'shkstart' REGEXP 't$', 'shkstart' REGEXP 'hk'
FROM DUAL;

SELECT 'atguigu' REGEXP 'gu.gu','atguigu' REGEXP '[ab]'
FROM DUAL;

D、逻辑运算符：

OR(或 ||)、AND(或&&)、NOT(或！)、XOR

# or  and 
SELECT last_name,salary,department_id
FROM employees
#where department_id = 10 or department_id = 20;
#where department_id = 10 and department_id = 20;
WHERE department_id = 50 AND salary > 6000;

# not 
SELECT last_name,salary,department_id
FROM employees
#where salary not between 6000 and 8000;
#where commission_pct is not null;
WHERE NOT commission_pct <=> NULL;

# XOR :追求的"异"
SELECT last_name,salary,department_id
FROM employees
WHERE department_id = 50 XOR salary > 6000;
#当满足=50时不满足>6000的，相反满足>6000不满足=50的。
#注意：AND的优先级高于OR

练习题：

# 1.选择工资不在5000到12000的员工的姓名和工资

SELECT last_name, salary
FROM employees
WHERE salary < 5000 OR salary > 12000;

SELECT last_name, salary
FROM employees
WHERE salary NOT BETWEEN 5000 AND 12000;

# 2.选择在20或50号部门工作的员工姓名和部门号

SELECT last_name, department_id
FROM employees
WHERE department_id = 20 OR department_id = 50;

SELECT last_name, department_id
FROM employees
WHERE department_id IN(20, 50);
# 3.选择公司中没有管理者的员工姓名及job_id

SELECT last_name, job_id
FROM employees
WHERE manager_id IS NULL;

# 4.选择公司中有奖金的员工姓名，工资和奖金级别

SELECT last_name, salary, commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;

# 5.选择员工姓名的第三个字母是a的员工姓名

SELECT last_name
FROM employees
WHERE last_name LIKE '__a%';

# 6.选择姓名中有字母a和k的员工姓名

SELECT last_name
FROM employees
WHERE last_name LIKE '%a%k%' OR last_name LIKE '%k%a%';

# 7.显示出表 employees 表中 first_name 以 'e'结尾的员工信息

SELECT employee_id,first_name,last_name
FROM employees
WHERE first_name LIKE '%e';

SELECT employee_id,first_name,last_name
FROM employees
WHERE first_name REGEXP 'e$';

# 8.显示出表 employees 部门编号在 80-100 之间的姓名、工种

SELECT last_name,job_id
FROM employees
#where department_id in (80,90,100);
WHERE department_id BETWEEN 80 AND 100;

# 9.显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、工资、管理者id

SELECT last_name,salary,manager_id
FROM employees
WHERE manager_id IN (100,101,110);

四、排序与分页

1、排序规则

使用order by 子句排序

ASC（ascend）：升序
DESC（descend）：降序

ORDER BY 子句在select语句的结尾。

-- 注意：
-- 如果没有使用排序操作，
-- 默认情况下查询返回的数据是按照添加数据的顺序显示的。

练习

# 练习一：按照salary从高到低的顺序显示员工信息

SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary DESC;

# 练习二：按照salary从低到高的顺序显示员工信息
方式一：
SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary ASC;

方式二：
SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary;
-- 如果在ORDER BY 后没有显式指名排序的方式的话，
-- 则默认按照升序排列。

#练习三：我们可以使用列的别名，进行排序
SELECT employee_id,salary,salary * 12 annual_sal
FROM employees
ORDER BY annual_sal;
/*
注意：
#列的别名只能在 ORDER BY 中使用，不能在WHERE中使用。
#如下操作报错！
SELECT employee_id,salary,salary * 12 annual_sal
FROM employees
WHERE annual_sal > 81600;
*/

强调格式：WHERE 需要声明在FROM后，ORDER BY之前。

SELECT employee_id,salary
FROM employees
WHERE department_id IN (50,60,70)
ORDER BY department_id DESC;

二级排序

#练习：显示员工信息，按照department_id的降序排列，
-- salary的升序排列
SELECT employee_id,salary,department_id
FROM employees
ORDER BY department_id DESC,salary ASC;

2、分页操作（limit）

mysql使用limit实现数据的分页显示

# 需求1：每页显示20条记录，此时显示第1页

SELECT employee_id,last_name
FROM employees
LIMIT 0,20;
#注：limit a，b；a表示开始位置，b表示一页要显示的条数。

# 需求2：每页显示20条记录，此时显示第2页

SELECT employee_id,last_name
FROM employees
LIMIT 20,20;

# 需求3：每页显示20条记录，此时显示第3页

SELECT employee_id,last_name
FROM employees
LIMIT 40,20;

分页公式

#需求：每页显示pageSize条记录，此时显示第pageNo页：
#公式：LIMIT (pageNo-1) * pageSize,pageSize;

WHERE … ORDER BY …LIMIT 声明顺序如下：

# LIMIT的格式： 严格来说：LIMIT 位置偏移量,条目数
# 结构"LIMIT 0,条目数" 等价于 "LIMIT 条目数"

SELECT employee_id,last_name,salary
FROM employees
WHERE salary > 6000
ORDER BY salary DESC
#limit 0,10;
LIMIT 10;


#练习：表里有107条数据，我们只想要显示第 32、33 条数据怎么办呢？

SELECT employee_id,last_name
FROM employees
LIMIT 31,2;

MySQL8.0新特性：LIMIT … OFFSET …

#练习：表里有107条数据，我们只想要显示第 32、33 条数据怎么办呢？

SELECT employee_id,last_name
FROM employees
LIMIT 2 OFFSET 31;

#练习：查询员工表中工资最高的员工信息
SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary DESC
#limit 0,1
LIMIT 1;

#2.4 LIMIT 可以使用在MySQL、PGSQL、MariaDB、SQLite 等-
#数据库中使用，表示分页。
# 不能使用在SQL Server、DB2、Oracle！

3、练习题

#1. 查询员工的姓名和部门号和年薪，按年薪降序,按姓名升序显示

SELECT last_name,department_id,salary * 12 annual_sal
FROM employees
ORDER BY annual_sal DESC,last_name ASC;

#2. 选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序，
#显示第21到40位置的数据

SELECT last_name,salary
FROM employees
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC
LIMIT 20,20;

#3. 查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，
#再按部门号升序

SELECT last_name,email,department_id
FROM employees
#where email like '%e%'
WHERE email REGEXP '[e]'
ORDER BY LENGTH(email) DESC,department_id ASC;

五、多表查询

笛卡尔积的错误：缺少了多表的连接条件

#错误的实现方式：每个员工都与每个部门匹配了一遍。
SELECT employee_id,department_name
FROM employees,departments;  #查询出2889条记录

#错误的方式
SELECT employee_id,department_name
FROM employees CROSS JOIN departments;#查询出2889条记录

. 多表查询的正确方式：需要有连接条件

SELECT employee_id,department_name
FROM employees,departments
#两个表的连接条件
WHERE employees.`department_id` = departments.department_id;

#如果查询语句中出现了多个表中都存在的字段，则必须指明此字段所在的表。
SELECT employees.employee_id,departments.department_name,
employees.department_id
FROM employees,departments
WHERE employees.`department_id` = departments.department_id;

从sql优化的角度，建议多表查询时，每个字段前都指明其所在的表。
可以给表起别名，在SELECT和WHERE中使用表的别名。

SELECT emp.employee_id,dept.department_name,emp.department_id
FROM employees emp,departments dept
WHERE emp.`department_id` = dept.department_id;

#如果给表起了别名，一旦在SELECT或WHERE中使用表名的话，则必须使用表的别名，而不能再使用表的原名。
#如下的操作是错误的：
SELECT emp.employee_id,departments.department_name,emp.department_id
FROM employees emp,departments dept
WHERE emp.`department_id` = departments.department_id;

结论：如果有n个表实现多表的查询，则需要至少n-1个连接条件

#练习：查询员工的employee_id,last_name,department_name,city
SELECT e.employee_id,e.last_name,d.department_name,
l.city,e.department_id,l.location_id
FROM employees e,departments d,locations l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`;

1、多表查询的分类

可分为：

1、角度一： 等值连接 vs 非等值连接
2、角度二： 自连接 vs 非自连接
3、角度三： 内连接 vs 外连接

A、等值连接 vs 非等值连接

1、等值连接
等值连接就是 表与表之间有相同的部分，
例如：department_id

2、非等值连接
例子：
SELECT *
FROM job_grades;

SELECT e.last_name,e.salary,j.grade_level
FROM employees e,job_grades j
#where e.`salary` between j.`lowest_sal` and j.`highest_sal`;
WHERE e.`salary` >= j.`lowest_sal` AND e.`salary` <= j.`highest_sal`;

B、自连接 vs 非自连接

1、自连接：自己连接自己
#练习：查询员工id,员工姓名及其管理者的id和姓名
SELECT emp.employee_id,emp.last_name,mgr.employee_id,mgr.last_name
FROM employees emp ,employees mgr
WHERE emp.`manager_id` = mgr.`employee_id`;

2、非自连接：不同表之间的连接。
例子：省略

C、内连接 vs 外连接

1、内连接：
合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行。

SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.`department_id` = d.department_id;  #只有106条记录

2、外连接：合并具有同一列的两个以上的表的行, 
结果集中除了包含一个表与另一个表匹配的行之外，
还查询到了左表 或 右表中不匹配的行。
2.1、 外连接的分类：左外连接、右外连接、满外连接
2.2、 左外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左表中不满足条件的行，这种连接称为左外连接。
2.3、右外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回右表中不满足条件的行，这种连接称为右外连接。

#练习：查询所有的员工的last_name,department_name信息 
SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.`department_id` = d.department_id; # 需要使用左外连接

2.4、注意：
外连接：MYSQL不支持SQL92的 外连接语法（+），支持使用SQL99语法
SQL99语法中使用 JOIN ...ON 的方式实现多表的查询。
这种方式也能解决外连接的问题。MySQL是支持此种方式的。

2.5、SQL99语法实现内连接：
SELECT last_name,department_name
FROM employees e INNER JOIN departments d
ON e.`department_id` = d.`department_id`;

SELECT last_name,department_name,city
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
JOIN locations l
ON d.`location_id` = l.`location_id`;

2.6、SQL99语法实现外连接：

#练习：查询所有的员工的last_name,department_name信息 
# 左外连接：
SELECT last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;

#右外连接：
SELECT last_name,department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;


#满外连接：mysql不支持FULL OUTER JOIN
SELECT last_name,department_name
FROM employees e FULL OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;

2.7、 UNION  和 UNION ALL的使用
# UNION：会执行去重操作
# UNION ALL:不会执行去重操作
#结论：如果明确知道合并数据后的结果数据不存在重复数据，
#或者不需要去除重复的数据，
#则尽量使用UNION ALL语句，以提高数据查询的效率。



2.8、 SQL99语法的新特性1:自然连接

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
AND e.`manager_id` = d.`manager_id`;

# NATURAL JOIN : 它会帮你自动查询两张连接表中`所有相同的字段`，然后进行`等值连接`。
SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;


2.9、 SQL99语法的新特性2:USING
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);


3.0、拓展：
SELECT last_name,job_title,department_name 
FROM employees INNER JOIN departments INNER JOIN jobs 
ON employees.department_id = departments.department_id 
AND employees.job_id = jobs.job_id;

2、练习题：


# 第06章_多表查询的课后练习

# 1.显示所有员工的姓名，部门号和部门名称。
SELECT e.last_name,e.department_id,d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;

# 2.查询90号部门员工的job_id和90号部门的location_id

SELECT e.job_id,d.location_id
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` = 90;


DESC departments;

# 3.选择所有有奖金的员工的 last_name , department_name , location_id , city
SELECT e.last_name ,e.`commission_pct`, d.department_name , d.location_id , l.city
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
LEFT JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE e.`commission_pct` IS NOT NULL; #也应该是35条记录


SELECT *
FROM employees
WHERE commission_pct IS NOT NULL; #35条记录


# 4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name 

SELECT e.last_name , e.job_id , e.department_id , d.department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE l.`city` = 'Toronto';

#sql92语法：
SELECT e.last_name , e.job_id , e.department_id , d.department_name
FROM employees e,departments d ,locations l
WHERE e.`department_id` = d.`department_id` 
AND d.`location_id` = l.`location_id`
AND l.`city` = 'Toronto';


# 5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’

SELECT d.department_name,l.street_address,e.last_name,e.job_id,e.salary
FROM departments d LEFT JOIN employees e
ON e.`department_id` = d.`department_id`
LEFT JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE d.`department_name` = 'Executive';


DESC departments;

DESC locations;

# 6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
employees	Emp#	manager	Mgr#
kochhar		101	king	100

SELECT emp.last_name "employees",emp.employee_id "Emp#",mgr.last_name "manager", mgr.employee_id "Mgr#"
FROM employees emp LEFT JOIN employees mgr
ON emp.manager_id = mgr.employee_id;


# 7.查询哪些部门没有员工

SELECT d.department_id
FROM departments d LEFT JOIN employees e
ON d.`department_id` = e.`department_id`
WHERE e.`department_id` IS NULL;

#本题也可以使用子查询：暂时不讲

# 8. 查询哪个城市没有部门 

SELECT l.location_id,l.city
FROM locations l LEFT JOIN departments d
ON l.`location_id` = d.`location_id`
WHERE d.`location_id` IS NULL;

SELECT department_id
FROM departments
WHERE department_id IN (1000,1100,1200,1300,1600);


# 9. 查询部门名为 Sales 或 IT 的员工信息

SELECT e.employee_id,e.last_name,e.department_id
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_name` IN ('Sales','IT');

六、单行函数

6.1、什么是函数

1、函数在计算机语言的使用中贯穿始终，函数的作用是什么呢？
它可以把我们经常使用的代码封装起来，
2、需要的时候直接调用即可。这样既 提高了代码效率 ，又提高了可维护性 。
在 SQL 中我们也可以使用函数
3、对检索出来的数据进行函数操作。使用这些函数，可以极大地 
提高用户对数据库的管理效率 。

6.2、MYSQL的内置函数与分类

1、MySQL提供了丰富的内置函数，这些函数使得数据的维护与管理更加方便，
能够更好地提供数据的分析
2、与统计功能，在一定程度上提高了开发人员进行数据分析与统计的效率。
3、MySQL提供的内置函数从 实现的功能角度 可以分为数值函数、
字符串函数、日期和时间函数、流程控制函数、加密与解密函数、
获取MySQL信息函数、聚合函数等。
这里，我将这些丰富的内置函数再分为两类： 单行函数 、 
聚合函数（或分组函数） 。

6.3、基本函数

基本函数

6.4 、角度与弧度互换函数

6.5、三角函数

三角函数

6.6、指数与对数

在这里插入图片描述

6.7、进制之间的转换

在这里插入图片描述

6.8、字符串函数

在这里插入图片描述

6.8、时间和日期函数

在这里插入图片描述

6.9、流程控制函数

在这里插入图片描述

6.10 、加密与解密函数

在这里插入图片描述

注意：

# PASSWORD()在mysql8.0中弃用。
SELECT MD5('mysql'),SHA('mysql'),MD5(MD5('mysql'))
FROM DUAL;

#ENCODE()\DECODE() 在mysql8.0中弃用。

6.11、MYSQL信息函数

在这里插入图片描述

6.12、其他函数

在这里插入图片描述

6.13、练习题：

#第07章_单行函数的课后练习


# 1.显示系统时间(注：日期+时间)
SELECT NOW(),SYSDATE(),CURRENT_TIMESTAMP(),LOCALTIME(),LOCALTIMESTAMP() #大家只需要掌握一个函数就可以了
FROM DUAL;

# 2.查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）
SELECT employee_id,last_name,salary,salary * 1.2 "new salary"
FROM employees;


# 3.将员工的姓名按首字母排序，并写出姓名的长度（length）
SELECT last_name,LENGTH(last_name) "name_length"
FROM employees
#order by last_name asc;
ORDER BY name_length ASC;


# 4.查询员工id,last_name,salary，并作为一个列输出，别名为OUT_PUT

SELECT CONCAT(employee_id,',',last_name,',',salary) "OUT_PUT"
FROM employees;


# 5.查询公司各员工工作的年数、工作的天数，并按工作年数的降序排序

SELECT employee_id,DATEDIFF(CURDATE(),hire_date)/365 "worked_years",DATEDIFF(CURDATE(),hire_date) "worked_days",
TO_DAYS(CURDATE()) - TO_DAYS(hire_date) "worked_days1"
FROM employees
ORDER BY worked_years DESC;

# 6.查询员工姓名，hire_date , department_id，满足以下条件：
#雇用时间在1997年之后，department_id 为80 或 90 或110, commission_pct不为空
SELECT last_name,hire_date,department_id
FROM employees
WHERE department_id IN (80,90,110)
AND commission_pct IS NOT NULL
#and hire_date >= '1997-01-01';  #存在着隐式转换
#and  date_format(hire_date,'%Y-%m-%d') >= '1997-01-01';  # 显式转换操作，格式化：日期---> 字符串
#and  date_format(hire_date,'%Y') >= '1997';   # 显式转换操作，格式化
AND hire_date >= STR_TO_DATE('1997-01-01','%Y-%m-%d');# 显式转换操作，解析：字符串 ----> 日期

# 7.查询公司中入职超过10000天的员工姓名、入职时间
SELECT last_name,hire_date
FROM employees
WHERE DATEDIFF(CURDATE(),hire_date) >= 10000;


# 8.做一个查询，产生下面的结果
#<last_name> earns <salary> monthly but wants <salary*3> 

SELECT CONCAT(last_name,' earns ',TRUNCATE(salary,0), ' monthly but wants ',TRUNCATE(salary * 3,0)) "Dream Salary"
FROM employees;


# 9.使用case-when，按照下面的条件：
/*job                  grade
AD_PRES              	A
ST_MAN               	B
IT_PROG              	C
SA_REP               	D
ST_CLERK             	E

产生下面的结果:
*/

SELECT last_name "Last_name",job_id "Job_id",CASE job_id WHEN 'AD_PRES' THEN 'A'
							 WHEN 'ST_MAN' THEN 'B'
							 WHEN 'IT_PROG' THEN 'C'
							 WHEN 'SA_REP' THEN 'D'
							 WHEN 'ST_CLERK' THEN 'E'
							 END "Grade"
FROM employees;


SELECT last_name "Last_name",job_id "Job_id",CASE job_id WHEN 'AD_PRES' THEN 'A'
							 WHEN 'ST_MAN' THEN 'B'
							 WHEN 'IT_PROG' THEN 'C'
							 WHEN 'SA_REP' THEN 'D'
							 WHEN 'ST_CLERK' THEN 'E'
							 ELSE "undefined" END "Grade"
FROM employees;

七、聚合函数

7.1、常见的几个聚合函数

AVG（平均数）/ SUM（求和）

只适用于数值类型的字段（或变量）

SELECT AVG(salary),SUM(salary),AVG(salary) * 107
FROM employees;

#如下的操作没有意义
SELECT SUM(last_name),AVG(last_name),SUM(hire_date)
FROM employees;

MAX（最大值）/ MIN（最小值）

适用于数值类型、字符串类型、日期时间类型的字段（或变量）

eg:
SELECT MAX(salary),MIN(salary)
FROM employees;

SELECT MAX(last_name),MIN(last_name),MAX(hire_date),MIN(hire_date)
FROM employees;

COUNT （查询个数）

① 作用：计算指定字段在查询结构中出现的个数（不包含NULL值的）
eg:
SELECT COUNT(employee_id),COUNT(salary),COUNT(2 * salary),COUNT(1),COUNT(2),COUNT(*)
FROM employees ;

#如果计算表中有多少条记录，如何实现？
#方式1：COUNT(*)
#方式2：COUNT(1)
#方式3：COUNT(具体字段) : 不一定对！

② 注意：计算指定字段出现的个数时，是不计算NULL值的。
SELECT COUNT(commission_pct)
FROM employees;

SELECT commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;


③ 公式：AVG = SUM / COUNT
SELECT AVG(salary),SUM(salary)/COUNT(salary),
AVG(commission_pct),SUM(commission_pct)/COUNT(commission_pct),
SUM(commission_pct) / 107
FROM employees;

#需求：查询公司中平均奖金率
#错误的！
SELECT AVG(commission_pct)
FROM employees;

#正确的：
SELECT SUM(commission_pct) / COUNT(IFNULL(commission_pct,0)),
AVG(IFNULL(commission_pct,0))
FROM employees;


# 如何需要统计表中的记录数，使用COUNT(*)、COUNT(1)、COUNT(具体字段) 哪个效率更高呢？
# 如果使用的是MyISAM 存储引擎，则三者效率相同，都是O(1)
# 如果使用的是InnoDB 存储引擎，则三者效率：COUNT(*) = COUNT(1)> COUNT(字段)

7.2、GROUP BY的使用

#需求：查询各个部门的平均工资，最高工资
SELECT department_id,AVG(salary),SUM(salary)
FROM employees
GROUP BY department_id

#需求：查询各个job_id的平均工资
SELECT job_id,AVG(salary)
FROM employees
GROUP BY job_id;

#需求：查询各个department_id,job_id的平均工资
#方式1：
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY  department_id,job_id;
#方式2：
SELECT job_id,department_id,AVG(salary)
FROM employees
GROUP BY job_id,department_id;

#错误的！
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id;


结论1：SELECT中出现的非组函数的字段必须声明在GROUP BY 中。
      反之，GROUP BY中声明的字段可以不出现在SELECT中。

结论2：GROUP BY 声明在FROM后面、WHERE后面，ORDER BY 前面、LIMIT前面

结论3：MySQL中GROUP BY中使用WITH ROLLUP

eg:
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id WITH ROLLUP;

#需求：查询各个部门的平均工资，按照平均工资升序排列
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal ASC;

说明：当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的。
#错误的：
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id WITH ROLLUP
ORDER BY avg_sal ASC;

7.3、HAVING的使用

作用：用来过滤数据的

#练习：查询各个部门中最高工资比10000高的部门信息
#错误的写法：
SELECT department_id,MAX(salary)
FROM employees
WHERE MAX(salary) > 10000
GROUP BY department_id;

要求1：如果过滤条件中使用了聚合函数，则必须使用HAVING来替换WHERE。否则，报错。
要求2：HAVING 必须声明在 GROUP BY 的后面。
要求3：开发中，我们使用HAVING的前提是SQL中使用了GROUP BY。

#正确的写法：
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000;

#练习：查询部门id为10,20,30,40这4个部门中最高工资比10000高的部门信息
#方式1：推荐，执行效率高于方式2.
SELECT department_id,MAX(salary)
FROM employees
WHERE department_id IN (10,20,30,40)
GROUP BY department_id
HAVING MAX(salary) > 10000;

#方式2：
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000 AND department_id IN (10,20,30,40);

结论：当过滤条件中有聚合函数时，则此过滤条件必须声明在HAVING中。
      当过滤条件中没有聚合函数时，则此过滤条件声明在WHERE中
      或HAVING中都可以。但是，建议大家声明在WHERE中。

WHERE 与 HAVING 的对比

1. 从适用范围上来讲，HAVING的适用范围更广。 
2. 如果过滤条件中没有聚合函数：
	这种情况下，WHERE的执行效率要高于HAVING

7.4、SQL底层执行原理

SELECT 语句的完整结构

#sql92语法：
SELECT ....,....,....(存在聚合函数)
FROM ...,....,....
WHERE 多表的连接条件 AND 不包含聚合函数的过滤条件
GROUP BY ...,....
HAVING 包含聚合函数的过滤条件
ORDER BY ....,...(ASC / DESC )
LIMIT ...,....


#sql99语法：
SELECT ....,....,....(存在聚合函数)
FROM ... (LEFT / RIGHT)JOIN ....ON 多表的连接条件 
(LEFT / RIGHT)JOIN ... ON ....
WHERE 不包含聚合函数的过滤条件
GROUP BY ...,....
HAVING 包含聚合函数的过滤条件
ORDER BY ....,...(ASC / DESC )
LIMIT ...,....

SQL语句的执行过程：

FROM ...,...-> ON -> (LEFT/RIGNT  JOIN) -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT ->  ORDER BY -> LIMIT

7.5、练习题：


# 第08章_聚合函数的课后练习

#1.where子句可否使用组函数进行过滤?  No!

#2.查询公司员工工资的最大值，最小值，平均值，总和
SELECT MAX(salary) max_sal ,MIN(salary) mim_sal,AVG(salary) avg_sal,SUM(salary) sum_sal
FROM employees;

#3.查询各job_id的员工工资的最大值，最小值，平均值，总和

SELECT job_id,MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees
GROUP BY job_id;

#4.选择具有各个job_id的员工人数
SELECT job_id,COUNT(*)
FROM employees
GROUP BY job_id;

# 5.查询员工最高工资和最低工资的差距（DIFFERENCE）  #DATEDIFF
SELECT MAX(salary) - MIN(salary) "DIFFERENCE"
FROM employees;


# 6.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内

SELECT manager_id,MIN(salary)
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary) >= 6000;


# 7.查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序 

SELECT d.department_name,d.location_id,COUNT(employee_id),AVG(salary)
FROM departments d LEFT JOIN employees e
ON d.`department_id` = e.`department_id`
GROUP BY department_name,location_id


# 8.查询每个工种、每个部门的部门名、工种名和最低工资 

SELECT d.department_name,e.job_id,MIN(salary)
FROM departments d LEFT JOIN employees e
ON d.`department_id` = e.`department_id`
GROUP BY department_name,job_id

八、子查询

1、子查询指一个查询语句嵌套在另一个查询语句内部的查询，
这个特性从MySQL 4.1开始引入。
2、SQL 中子查询的使用大大增强了 SELECT 查询的能力，
因为很多时候查询需要从结果集中获取数据，或者
3、需要从同一个表中先计算得出一个数据结果，
然后与这个数据结果（可能是某个标量，也可能是某个集合）进行比较。

8.1、需求分析与问题解决

谁的工资比Abel高

#方式一：
SELECT salary
FROM employees
WHERE last_name = 'Abel';
SELECT last_name,salary
FROM employees
WHERE salary > 11000;

#方式二：自连接
SELECT e2.last_name,e2.salary
FROM employees e1,employees e2
WHERE e1.last_name = 'Abel'
AND e1.`salary` < e2.`salary`

#方式三：子查询
SELECT last_name,salary
FROM employees
WHERE salary > (
SELECT salary
FROM employees
WHERE last_name = 'Abel'
);

8.2、子查询的基本使用

子查询的基本语法结构
子查询（内查询）在主查询之前一次执行完成。
子查询的结果被主查询（外查询）使用。
注意事项
- 子查询要包含在括号内
- 将子查询放在比较条件的右侧
- 单行操作符对应单行子查询，多行操作符对应多行子查询

#不推荐：
SELECT last_name,salary
FROM employees
WHERE  (
	SELECT salary
	FROM employees
	WHERE last_name = 'Abel'
		) < salary;

8.3、子查询的分类

角度1：从内查询返回的结果的条目数
- 单行子查询 vs 多行子查询
角度2：内查询是否被执行多次
- 相关子查询 vs 不相关子查询

 比如：相关子查询的需求：查询工资大于本部门平均工资的员工信息。
       不相关子查询的需求：查询工资大于本公司平均工资的员工信息。

子查询的编写技巧（或步骤）：① 从里往外写 ② 从外往里写

8.4、单行子查询

单行比较操作符
练习

#题目：查询工资大于149号员工工资的员工的信息

SELECT employee_id,last_name,salary
FROM employees
WHERE salary > (
		SELECT salary
		FROM employees
		WHERE employee_id = 149
		);

#题目：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资

SELECT last_name,job_id,salary
FROM employees
WHERE job_id = (
		SELECT job_id
		FROM employees
		WHERE employee_id = 141
		)
AND salary > (
		SELECT salary
		FROM employees
		WHERE employee_id = 143
		);


#题目：返回公司工资最少的员工的last_name,job_id和salary

SELECT last_name,job_id,salary
FROM employees
WHERE salary = (
		SELECT MIN(salary)
		FROM employees
		);

#题目：查询与141号员工的manager_id和department_id相同的其他员工
#的employee_id，manager_id，department_id。
#方式1：
SELECT employee_id,manager_id,department_id
FROM employees
WHERE manager_id = (
		    SELECT manager_id
		    FROM employees
		    WHERE employee_id = 141
		   )
AND department_id = (
		    SELECT department_id
		    FROM employees
		    WHERE employee_id = 141
		   )
AND employee_id <> 141;

#方式2：了解
SELECT employee_id,manager_id,department_id
FROM employees
WHERE (manager_id,department_id) = (
				    SELECT manager_id,department_id
			            FROM employees
				    WHERE employee_id = 141
				   )
AND employee_id <> 141;

#题目：查询最低工资大于110号部门最低工资的部门id和其最低工资

SELECT department_id,MIN(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING MIN(salary) > (
			SELECT MIN(salary)
			FROM employees
			WHERE department_id = 110
		     );

#题目：显式员工的employee_id,last_name和location。
#其中，若员工department_id与location_id为1800的department_id相同，
#则location为’Canada’，其余则为’USA’。

SELECT employee_id,last_name,CASE department_id WHEN (SELECT department_id FROM departments WHERE location_id = 1800) THEN 'Canada'
						ELSE 'USA' END "location"
FROM employees;

子查询中的空值问题

SELECT last_name, job_id
FROM   employees
WHERE  job_id =
                (SELECT job_id
                 FROM   employees
                 WHERE  last_name = 'Haas');
#为null

非法使用子查询

#错误：Subquery returns more than 1 row
SELECT employee_id, last_name
FROM   employees
WHERE  salary =
                (SELECT   MIN(salary)
                 FROM     employees
                 GROUP BY department_id);

8.5、多行子查询

1、也称为集合比较子查询
2、内查询返回多行
3、使用多行比较操作符
多行比较操作符
练习

# IN:
SELECT employee_id, last_name
FROM   employees
WHERE  salary IN
                (SELECT   MIN(salary)
                 FROM     employees
                 GROUP BY department_id); 
                 
# ANY / ALL:
#题目：返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、
#姓名、job_id 以及salary

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ANY (
		SELECT salary
		FROM employees
		WHERE job_id = 'IT_PROG'
		);

#题目：返回其它job_id中比job_id为‘IT_PROG’部门所有工资低的员工的员工号、
#姓名、job_id 以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ALL (
		SELECT salary
		FROM employees
		WHERE job_id = 'IT_PROG'
		);
		
#题目：查询平均工资最低的部门id
#MySQL中聚合函数是不能嵌套使用的。
#方式1：
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
			SELECT MIN(avg_sal)
			FROM(
				SELECT AVG(salary) avg_sal
				FROM employees
				GROUP BY department_id
				) t_dept_avg_sal
			);

#方式2：
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL(	
			SELECT AVG(salary) avg_sal
			FROM employees
			GROUP BY department_id
			)

空值问题

SELECT last_name
FROM employees
WHERE employee_id NOT IN (
			SELECT manager_id
			FROM employees
			);

8.6、相关子查询

相关子查询执行流程

如果子查询的执行依赖于外部查询，
通常情况下都是因为子查询中的表用到了外部的表
，并进行了条件关联，因此每执行一次外部查询，
子查询都要重新计算一次，这样的子查询就称之为 关联子查询 。

相关子查询按照一行接一行的顺序执行，
主查询的每一行都执行一次子查询。

说明：子查询中使用主查询中的列

#回顾：查询员工中工资大于公司平均工资的员工的last_name,salary和其department_id
SELECT last_name,salary,department_id
FROM employees
WHERE salary > (
		SELECT AVG(salary)
		FROM employees
		);
		
#题目：查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
#方式1：使用相关子查询
SELECT last_name,salary,department_id
FROM employees e1
WHERE salary > (
		SELECT AVG(salary)
		FROM employees e2
		WHERE department_id = e1.`department_id`
		);

#方式2：在FROM中声明子查询
SELECT e.last_name,e.salary,e.department_id
FROM employees e,(
		SELECT department_id,AVG(salary) avg_sal
		FROM employees
		GROUP BY department_id) t_dept_avg_sal
WHERE e.department_id = t_dept_avg_sal.department_id
AND e.salary > t_dept_avg_sal.avg_sal


#题目：查询员工的id,salary,按照department_name 排序

SELECT employee_id,salary
FROM employees e
ORDER BY (
	 SELECT department_name
	 FROM departments d
	 WHERE e.`department_id` = d.`department_id`
	) ASC;

结论：在SELECT中，除了GROUP BY 和 LIMIT之外，其他位置都可以声明子查询！

SELECT ....,....,....(存在聚合函数)
FROM ... (LEFT / RIGHT)JOIN ....ON 多表的连接条件 
(LEFT / RIGHT)JOIN ... ON ....
WHERE 不包含聚合函数的过滤条件
GROUP BY ...,....
HAVING 包含聚合函数的过滤条件
ORDER BY ....,...(ASC / DESC )
LIMIT ...,....

#题目：若employees表中employee_id与job_history表中employee_id相同的数目不小于2，
#输出这些相同id的员工的employee_id,last_name和其job_id

SELECT *
FROM job_history;

SELECT employee_id,last_name,job_id
FROM employees e
WHERE 2 <= (
	    SELECT COUNT(*)
	    FROM job_history j
	    WHERE e.`employee_id` = j.`employee_id`
		)

8.7、 EXISTS 与 NOT EXISTS关键字

关联子查询通常也会和 EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行。
如果在子查询中不存在满足条件的行：
- 条件返回 FALSE
- 继续在子查询中查找
如果在子查询中存在满足条件的行：
- 不在子查询中继续查找
- 条件返回 TRUE
NOT EXISTS关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。

#题目：查询公司管理者的employee_id，last_name，job_id，department_id信息
#方式1：自连接
SELECT DISTINCT mgr.employee_id,mgr.last_name,mgr.job_id,mgr.department_id
FROM employees emp JOIN employees mgr
ON emp.manager_id = mgr.employee_id;

#方式2：子查询

SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id IN (
			SELECT DISTINCT manager_id
			FROM employees
			);

#方式3：使用EXISTS
SELECT employee_id,last_name,job_id,department_id
FROM employees e1
WHERE EXISTS (
	       SELECT *
	       FROM employees e2
	       WHERE e1.`employee_id` = e2.`manager_id`
	     );

#题目：查询departments表中，不存在于employees表中的部门的department_id和department_name

#方式1：
SELECT d.department_id,d.department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;

#方式2：
SELECT department_id,department_name
FROM departments d
WHERE NOT EXISTS (
		SELECT *
		FROM employees e
		WHERE d.`department_id` = e.`department_id`
		);

SELECT COUNT(*)
FROM departments;

8.8、相关更新

UPDATE table1 alias1
SET column = (SELECT expression
FROM table2 alias2
WHERE alias1.column = alias2.column);

使用相关子查询依据一个表中的数据更新另一个表的数据。

#题目：在employees中增加一个department_name字段，数据为员工对应的部门名称

# 1）
ALTER TABLE employees
ADD(department_name VARCHAR2(14));

# 2）
UPDATE employees e
SET department_name = (SELECT department_name
FROM departments d
WHERE e.department_id = d.department_id);

8.9、相关删除

DELETE FROM table1 alias1
WHERE column operator (SELECT expression
FROM table2 alias2
WHERE alias1.column = alias2.column);

使用相关子查询依据一个表中的数据删除另一个表的数据。

#题目：删除表employees中，其与emp_history表皆有的数据
DELETE FROM employees e
WHERE employee_id in
(SELECT employee_id
FROM emp_history
WHERE employee_id = e.employee_id);

8.10、抛一个问题

问题：谁的工资比Abel的高？
解答：

#方式1：自连接
SELECT e2.last_name,e2.salary
FROM employees e1,employees e2
WHERE e1.last_name = 'Abel'
AND e1.`salary` < e2.`salary`

#方式2：子查询
SELECT last_name,salary
FROM employees
WHERE salary > (
SELECT salary
FROM employees
WHERE last_name = 'Abel'
);

问题：以上两种方式有好坏之分吗？
解答：自连接方式好！

1、题目中可以使用子查询，也可以使用自连接。
一般情况建议你使用自连接，因为在许多 DBMS 的处理过程中，
对于自连接的处理速度要比子查询快得多。

2、可以这样理解：子查询实际上是通过未知表进行查询后的条件判断，
而自连接是通过已知的自身数据表进行条件判断，
因此在大部分 DBMS 中都对自连接处理进行了优化。

8.11、练习：

#1.查询和Zlotkey相同部门的员工姓名和工资

SELECT last_name,salary
FROM employees
WHERE department_id IN (
			SELECT department_id
			FROM employees
			WHERE last_name = 'Zlotkey'
			);

#2.查询工资比公司平均工资高的员工的员工号，姓名和工资。

SELECT employee_id,last_name,salary
FROM employees
WHERE salary > (
		SELECT AVG(salary)
		FROM employees
		);

#3.选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary

SELECT last_name,job_id,salary
FROM employees
WHERE salary > ALL(
		SELECT salary
		FROM employees
		WHERE job_id = 'SA_MAN'
		);


#4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名

SELECT employee_id,last_name
FROM employees 
WHERE department_id IN (
			SELECT DISTINCT department_id
			FROM employees
			WHERE last_name LIKE '%u%'
			);


#5.查询在部门的location_id为1700的部门工作的员工的员工号

SELECT employee_id
FROM employees
WHERE department_id IN (
			SELECT department_id
			FROM departments
			WHERE location_id = 1700
			);


#6.查询管理者是King的员工姓名和工资

SELECT last_name,salary,manager_id
FROM employees
WHERE manager_id IN (
			SELECT employee_id
			FROM employees
			WHERE last_name = 'King'
			);



#7.查询工资最低的员工信息: last_name, salary

SELECT last_name,salary
FROM employees
WHERE salary = (
		SELECT MIN(salary)
		FROM employees
		);


#8.查询平均工资最低的部门信息
#方式1：
SELECT *
FROM departments
WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING AVG(salary ) = (
						SELECT MIN(avg_sal)
						FROM (
							SELECT AVG(salary) avg_sal
							FROM employees
							GROUP BY department_id
							) t_dept_avg_sal

						)
			);
#方式2：

SELECT *
FROM departments
WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING AVG(salary ) <= ALL(
						SELECT AVG(salary)
						FROM employees
						GROUP BY department_id
						)
			);

#方式3： LIMIT

SELECT *
FROM departments
WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING AVG(salary ) =(
						SELECT AVG(salary) avg_sal
						FROM employees
						GROUP BY department_id
						ORDER BY avg_sal ASC
						LIMIT 1		
						)
			);

#方式4：

SELECT d.*
FROM departments d,(
		SELECT department_id,AVG(salary) avg_sal
		FROM employees
		GROUP BY department_id
		ORDER BY avg_sal ASC
		LIMIT 0,1
		) t_dept_avg_sal
WHERE d.`department_id` = t_dept_avg_sal.department_id
		
#9.查询平均工资最低的部门信息和该部门的平均工资（相关子查询）
#方式1：
SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.`department_id`) avg_sal
FROM departments d
WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING AVG(salary ) = (
						SELECT MIN(avg_sal)
						FROM (
							SELECT AVG(salary) avg_sal
							FROM employees
							GROUP BY department_id
							) t_dept_avg_sal

						)
			);

#方式2：

SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.`department_id`) avg_sal
FROM departments d
WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING AVG(salary ) <= ALL(
						SELECT AVG(salary)
						FROM employees
						GROUP BY department_id
						)
			);

#方式3： LIMIT

SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.`department_id`) avg_sal
FROM departments d
WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING AVG(salary ) =(
						SELECT AVG(salary) avg_sal
						FROM employees
						GROUP BY department_id
						ORDER BY avg_sal ASC
						LIMIT 1		
						)
			);

#方式4：

SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.`department_id`) avg_sal
FROM departments d,(
		SELECT department_id,AVG(salary) avg_sal
		FROM employees
		GROUP BY department_id
		ORDER BY avg_sal ASC
		LIMIT 0,1
		) t_dept_avg_sal
WHERE d.`department_id` = t_dept_avg_sal.department_id

#10.查询平均工资最高的 job 信息

#方式1：
SELECT *
FROM jobs
WHERE job_id = (
		SELECT job_id
		FROM employees
		GROUP BY job_id
		HAVING AVG(salary) = (
					SELECT MAX(avg_sal)
					FROM (
						SELECT AVG(salary) avg_sal
						FROM employees
						GROUP BY job_id
						) t_job_avg_sal
					)
		);

#方式2：
SELECT *
FROM jobs
WHERE job_id = (
		SELECT job_id
		FROM employees
		GROUP BY job_id
		HAVING AVG(salary) >= ALL(
				     SELECT AVG(salary) 
				     FROM employees
				     GROUP BY job_id
				     )
		);

#方式3：
SELECT *
FROM jobs
WHERE job_id = (
		SELECT job_id
		FROM employees
		GROUP BY job_id
		HAVING AVG(salary) =(
				     SELECT AVG(salary) avg_sal
				     FROM employees
				     GROUP BY job_id
				     ORDER BY avg_sal DESC
				     LIMIT 0,1
				     )
		);

#方式4：
SELECT j.*
FROM jobs j,(
		SELECT job_id,AVG(salary) avg_sal
		FROM employees
		GROUP BY job_id
		ORDER BY avg_sal DESC
		LIMIT 0,1		
		) t_job_avg_sal
WHERE j.job_id = t_job_avg_sal.job_id
		
#11.查询平均工资高于公司平均工资的部门有哪些?

SELECT department_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING AVG(salary) > (
			SELECT AVG(salary)
			FROM employees
			);


#12.查询出公司中所有 manager 的详细信息

#方式1：自连接  xxx worked for yyy
SELECT DISTINCT mgr.employee_id,mgr.last_name,mgr.job_id,mgr.department_id
FROM employees emp JOIN employees mgr
ON emp.manager_id = mgr.employee_id;

#方式2：子查询

SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id IN (
			SELECT DISTINCT manager_id
			FROM employees
			);

#方式3：使用EXISTS
SELECT employee_id,last_name,job_id,department_id
FROM employees e1
WHERE EXISTS (
	       SELECT *
	       FROM employees e2
	       WHERE e1.`employee_id` = e2.`manager_id`
	     );

	
#13.各个部门中 最高工资中最低的那个部门的 最低工资是多少?

#方式1：
SELECT MIN(salary)
FROM employees
WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING MAX(salary) = (
						SELECT MIN(max_sal)
						FROM (
							SELECT MAX(salary) max_sal
							FROM employees
							GROUP BY department_id
							) t_dept_max_sal
						)
			);

SELECT *
FROM employees
WHERE department_id = 10;

#方式2：
SELECT MIN(salary)
FROM employees
WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING MAX(salary) <= ALL (
						SELECT MAX(salary)
						FROM employees
						GROUP BY department_id
						)
			);

#方式3：
SELECT MIN(salary)
FROM employees
WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING MAX(salary) = (
						SELECT MAX(salary) max_sal
						FROM employees
						GROUP BY department_id
						ORDER BY max_sal ASC
						LIMIT 0,1
						)
			);
			
#方式4：
SELECT MIN(salary)
FROM employees e,(
		SELECT department_id,MAX(salary) max_sal
		FROM employees
		GROUP BY department_id
		ORDER BY max_sal ASC
		LIMIT 0,1
		) t_dept_max_sal
WHERE e.department_id = t_dept_max_sal.department_id


#14.查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
#方式1：
SELECT last_name, department_id, email, salary
FROM employees
WHERE employee_id = ANY (
			SELECT DISTINCT manager_id
			FROM employees
			WHERE department_id = (
						SELECT department_id
						FROM employees
						GROUP BY department_id
						HAVING AVG(salary) = (
									SELECT MAX(avg_sal)
									FROM (
										SELECT AVG(salary) avg_sal
										FROM employees
										GROUP BY department_id
										) t_dept_avg_sal
									)
						)
			);

#方式2：
SELECT last_name, department_id, email, salary
FROM employees
WHERE employee_id = ANY (
			SELECT DISTINCT manager_id
			FROM employees
			WHERE department_id = (
						SELECT department_id
						FROM employees
						GROUP BY department_id
						HAVING AVG(salary) >= ALL (
								SELECT AVG(salary) avg_sal
								FROM employees
								GROUP BY department_id
								)
						)
			);

#方式3：
SELECT last_name, department_id, email, salary
FROM employees
WHERE employee_id IN (
			SELECT DISTINCT manager_id
			FROM employees e,(
					SELECT department_id,AVG(salary) avg_sal
					FROM employees
					GROUP BY department_id
					ORDER BY avg_sal DESC
					LIMIT 0,1
					) t_dept_avg_sal
			WHERE e.`department_id` = t_dept_avg_sal.department_id
			);


#15. 查询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号
#方式1：
SELECT department_id
FROM departments
WHERE department_id NOT IN (
			SELECT DISTINCT department_id
			FROM employees
			WHERE job_id = 'ST_CLERK'
			);

#方式2：
SELECT department_id
FROM departments d
WHERE NOT EXISTS (
		SELECT *
		FROM employees e
		WHERE d.`department_id` = e.`department_id`
		AND e.`job_id` = 'ST_CLERK'
		);



#16. 选择所有没有管理者的员工的last_name

SELECT last_name
FROM employees emp
WHERE NOT EXISTS (
		SELECT *
		FROM employees mgr
		WHERE emp.`manager_id` = mgr.`employee_id`
		);

#17．查询员工号、姓名、雇用时间、工资，其中员工的管理者为 'De Haan'
#方式1：
SELECT employee_id,last_name,hire_date,salary
FROM employees
WHERE manager_id IN (
		SELECT employee_id
		FROM employees
		WHERE last_name = 'De Haan'
		);

#方式2：
SELECT employee_id,last_name,hire_date,salary
FROM employees e1
WHERE EXISTS (
		SELECT *
		FROM employees e2
		WHERE e1.`manager_id` = e2.`employee_id`
		AND e2.last_name = 'De Haan'
		); 


#18.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资（相关子查询）

#方式1：使用相关子查询
SELECT last_name,salary,department_id
FROM employees e1
WHERE salary > (
		SELECT AVG(salary)
		FROM employees e2
		WHERE department_id = e1.`department_id`
		);

#方式2：在FROM中声明子查询
SELECT e.last_name,e.salary,e.department_id
FROM employees e,(
		SELECT department_id,AVG(salary) avg_sal
		FROM employees
		GROUP BY department_id) t_dept_avg_sal
WHERE e.department_id = t_dept_avg_sal.department_id
AND e.salary > t_dept_avg_sal.avg_sal


#19.查询每个部门下的部门人数大于 5 的部门名称（相关子查询）

SELECT department_name
FROM departments d
WHERE 5 < (
	   SELECT COUNT(*)
	   FROM employees e
	   WHERE d.department_id = e.`department_id`
	  );


#20.查询每个国家下的部门个数大于 2 的国家编号（相关子查询）

SELECT * FROM locations;

SELECT country_id
FROM locations l
WHERE 2 < (
	   SELECT COUNT(*)
	   FROM departments d
	   WHERE l.`location_id` = d.`location_id`
	 );

/* 
子查询的编写技巧（或步骤）：① 从里往外写  ② 从外往里写

如何选择？
① 如果子查询相对较简单，建议从外往里写。一旦子查询结构较复杂，则建议从里往外写
② 如果是相关子查询的话，通常都是从外往里写。

*/

九、创建和管理表

9.1、标识符命名规则

数据库名、表名不得超过30个字符，变量名限制为29个
必须只能包含 A–Z, a–z, 0–9, _共63个字符
数据库名、表名、字段名等对象名中间不要包含空格
同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名
必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使
用`（着重号）引起来
保持字段名和类型的一致性：在命名字段并为其指定数据类型的时候一定要保证一致性，假如数据
类型在一个表里是整数，那在另一个表里可就别变成字符型了

9.2、MySQL中的数据类型

在这里插入图片描述
其中，常用的几类类型介绍如下：

9.3、创建和管理数据库

9.3.1、创建数据库

方式一：创建数据库

CREATE DATABASE 数据库名;

方式2：创建数据库并指定字符集

CREATE DATABASE 数据库名 CHARACTER SET 字符集;

方式3：判断数据库是否已经存在，不存在则创建数据库（推荐）

CREATE DATABASE IF NOT EXISTS 数据库名;

如果MySQL中已经存在相关的数据库，则忽略创建语句，不再创建数据库。

注意：

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

9.3.2、使用数据库

查看当前所有的数据库

SHOW DATABASES; #有一个S，代表多个数据库

查看当前正在使用的数据库

SELECT DATABASE(); #使用的一个 mysql 中的全局函数

查看指定库下所有的表

SHOW TABLES FROM 数据库名;

查看数据库的创建信息

SHOW CREATE DATABASE 数据库名;
或者：
SHOW CREATE DATABASE 数据库名\G

使用/切换数据库

USE 数据库名;

注意：

注意：要操作表格和数据之前必须先说明是对哪个数据库进行操作，
否则就要对所有对象加上“数据库名.”。

9.3.3、修改数据库

更改数据库字符集

ALTER DATABASE 数据库名 CHARACTER SET 字符集; #比如：gbk、utf8等

9.3.4、删除数据库

方式1：删除指定的数据库

DROP DATABASE 数据库名;

方式2：删除指定的数据库（推荐）

DROP DATABASE IF EXISTS 数据库名;

9.4、创建表

9.4.1、创建方式1

必须具备：
- CREATE TABLE权限
- 存储空间
语法格式

CREATE TABLE [IF NOT EXISTS] 表名(
字段1, 数据类型 [约束条件] [默认值],
字段2, 数据类型 [约束条件] [默认值],
字段3, 数据类型 [约束条件] [默认值],
……
[表约束条件]
);

加上了IF NOT EXISTS关键字，则表示：如果当前数据库中不存在要创建的数据表，则创建数据表；
如果当前数据库中已经存在要创建的数据表，则忽略建表语句，不再创建数据表。

必须指定：
- 表名
- 列名(或字段名)，数据类型，长度
可选指定：
- 约束条件
- 默认值
创建表例子 1：

-- 创建表
CREATE TABLE emp (
-- int类型
emp_id INT,
-- 最多保存20个中英文字符
emp_name VARCHAR(20),
-- 总位数不超过15位
salary DOUBLE,
-- 日期类型
birthday DATE

//查看表的描述
DESC emp;
);

注意：

MySQL在执行建表语句时，将id字段的类型设置为int(11)，
这里的11实际上是int类型指定的显示宽度，默认的显示宽度为11。
也可以在创建数据表的时候指定数据的显示宽度。

创建表举例2：

CREATE TABLE dept(
-- int类型，自增
deptno INT(2) AUTO_INCREMENT,
dname VARCHAR(14),
loc VARCHAR(13),
-- 主键
PRIMARY KEY (deptno)
);

注意：

在MySQL 8.x版本中，不再推荐为INT类型指定显示长度，并在未来的版本中可能去掉这样的语法。

9.4.2、创建方式2：

使用 AS subquery 选项，将创建表和插入数据结合起来
指定的列和子查询中的列要一一对应
通过列名和默认值定义列

CREATE TABLE emp1 AS SELECT * FROM employees;
CREATE TABLE emp2 AS SELECT * FROM employees WHERE 1=2; -- 创建的emp2是空表


CREATE TABLE dept80
AS
SELECT employee_id, last_name, salary*12 ANNSAL, hire_date
FROM employees
WHERE department_id = 80;

9.4.3、

查看数据表结构

在MySQL中创建好数据表之后，可以查看数据表的结构。
MySQL支持使用 DESCRIBE/DESC 语句查看数据表结构，
也支持使用 SHOW CREATE TABLE 语句查看数据表结构。

语法格式：

SHOW CREATE TABLE 表名\G

使用SHOW CREATE TABLE语句不仅可以查看表创建时的详细语句，还可以查看存储引擎和字符编码。

9.5、修改表

修改表指的是修改数据库中已经存在的数据表的结构。

使用 ALTER TABLE 语句可以实现：
- 向已有的表中添加列
- 修改现有表中的列
- 删除现有表中的列
- 重命名现有表中的列

9.5.1、追加一个列

语法格式：
ALTER TABLE 表名 ADD 【COLUMN】 字段名 字段类型 【FIRST|AFTER 字段名】;

举例：
ALTER TABLE dept80
ADD job_id varchar(15);

9.5.2、修改一个列

可以修改列的数据类型，长度、默认值和位置
修改字段数据类型、长度、默认值、位置的语法格式如下：

ALTER TABLE 表名 MODIFY 【COLUMN】 字段名1 字段类型 【DEFAULT 默认值】【FIRST|AFTER 字段名
2】;

举例：

ALTER TABLE dept80
MODIFY last_name VARCHAR(30);


ALTER TABLE dept80
MODIFY salary double(9,2) default 1000;

对默认值的修改只影响今后对表的修改
此外，还可以通过此种方式修改列的约束。

9.5.3、重命名一个列

使用 CHANGE old_column new_column dataType子句重命名列。语法格式如下：

ALTER TABLE 表名 CHANGE 【column】 列名 新列名 新数据类型;

举例：

ALTER TABLE dept80
CHANGE department_name dept_name varchar(15);

9.5.4、删除一个列

删除表中某个字段的语法格式如下：

ALTER TABLE 表名 DROP 【COLUMN】字段名

举例

ALTER TABLE dept80
DROP COLUMN job_id;

9.6、重命名表

方式一：使用RENAME

RENAME TABLE emp
TO myemp;

方式二：

ALTER table dept
RENAME [TO] detail_dept; -- [TO]可以省略

9.7、删除表

不光将表结构删除掉，同时表中的数据也删除掉，释放表空间
在MySQL中，当一张数据表没有与其他任何数据表形成关联关系时，可以将当前数据表直接删除。
数据和结构都被删除
所有正在运行的相关事务被提交
所有相关索引被删除
语法格式：

DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];

IF EXISTS 的含义为：如果当前数据库中存在相应的数据表，则删除数据表；
如果当前数据库中不存在相应的数据表，则忽略删除语句，不再执行删除数据表的操作。

DROP TABLE dept80;

DROP TABLE 语句不能回滚

9.8、清空表

清空表，表示清空表中的所有数据，但是表结构保留。
TRUNCATE TABLE语句：
- 删除表中所有的数据
- 释放表的存储空间
例子：

TRUNCATE TABLE detail_dept;

TRUNCATE语句不能回滚，而使用 DELETE 语句删除数据，可以回滚
对比 TRUNCATE TABLE 和 DELETE FROM

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

# 演示：DELETE FROM 
#1)
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;
#2)
SELECT *
FROM myemp3;
#3)
SET autocommit = FALSE;
#4)
TRUNCATE TABLE myemp3;
#5)
SELECT *
FROM myemp3;
#6)
ROLLBACK;
#7)
SELECT *
FROM myemp3;

9.9、commit和rollback

COMMIT:提交数据。一旦执行COMMIT，则数据就被永久的保存在了数据库中，意味着数据不可以回滚。
ROLLBACK:回滚数据。一旦执行ROLLBACK,则可以实现数据的回滚。回滚到最近的一次COMMIT之后。

9.10、DDL和DML的说明

① DDL的操作一旦执行，就不可回滚。指令SET autocommit = FALSE对DDL操作失效。
(因为在执行完DDL操作之后，一定会执行一次COMMIT。
而此COMMIT操作不受SET autocommit = FALSE影响的。)
  
② DML的操作默认情况，一旦执行，也是不可回滚的。
  但是，如果在执行DML之前，执行了 SET autocommit = FALSE，
  则执行的DML操作就可以实现回滚。

9.11、MySQL8新特性—DDL的原子化

在MySQL 8.0版本中，InnoDB表的DDL支持事务完整性，即 DDL操作要么成功要么回滚 。
DDL操作回滚日志写入到data dictionary数据字典表mysql.innodb_ddl_log
（该表是隐藏的表，通过show tables无法看到）中，用于回滚操作。
通过设置参数，可将DDL操作日志打印输出到MySQL错误日志中。

mysql8.0 中，当执行出错时会回滚，而MySQL5.x不行。

CREATE DATABASE mytest;

USE mytest;

CREATE TABLE book1(
book_id INT ,rr
book_name VARCHAR(255)
);

SHOW TABLES;

DROP TABLE book1,book2;

SHOW TABLES;

9.12、练习题：

# 第10章_创建和管理表的课后练习

#练习1：
#1. 创建数据库test01_office,指明字符集为utf8。并在此数据库下执行下述操作

CREATE DATABASE IF NOT EXISTS test01_office CHARACTER SET 'utf8';

USE test01_office;

#2.	创建表dept01
/*
字段      类型
id	 INT(7)
NAME	 VARCHAR(25)
*/
CREATE TABLE IF NOT EXISTS dept01(
id INT(7),
`name` VARCHAR(25)
);


#3.将表departments中的数据插入新表dept02中
CREATE TABLE 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 emp01(
id INT(7),
first_name VARCHAR(25),
last_name VARCHAR(25),
dept_id INT(7)
);

#5.将列last_name的长度增加到50
DESC emp01;

ALTER TABLE emp01
MODIFY last_name VARCHAR(50);

#6.根据表employees创建emp02
CREATE TABLE emp02
AS
SELECT *
FROM atguigudb.`employees`;

SHOW TABLES FROM test01_office;

#7.删除表emp01
DROP TABLE emp01;


#8.将表emp02重命名为emp01
#alter table emp02 rename to emp01;
RENAME TABLE emp02 TO emp01;

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

DESC emp01;

ALTER TABLE dept02 ADD test_column VARCHAR(10);

DESC dept02;


#10.直接删除表emp01中的列 department_id

ALTER TABLE emp01
DROP COLUMN department_id;

#练习2：
# 1、创建数据库 test02_market

CREATE DATABASE IF NOT EXISTS test02_market CHARACTER SET 'utf8';

USE test02_market;

SHOW CREATE DATABASE test02_market;

# 2、创建数据表 customers
CREATE TABLE IF NOT EXISTS customers(
c_num INT,
c_name VARCHAR(50),
c_contact VARCHAR(50),
c_city VARCHAR(50),
c_birth DATE
);

SHOW TABLES;

# 3、将 c_contact 字段移动到 c_birth 字段后面
DESC customers;

ALTER TABLE customers
MODIFY c_contact VARCHAR(50) AFTER c_birth;

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

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

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

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

DESC customers_info;

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

#练习3：
# 1、创建数据库test03_company
CREATE DATABASE IF NOT EXISTS test03_company CHARACTER SET 'utf8';

USE test03_company;

# 2、创建表offices
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(255),
sex VARCHAR(5)

);

DESC employees;

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

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

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

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

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

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

DESC employees_info;

十、数据处理之增删改

10.1、添加数据

方式1：一条一条的添加数据

# ① 没有指明添加的字段
#正确的
INSERT INTO emp1
VALUES (1,'Tom','2000-12-21',3400); #注意：一定要按照声明的字段的先后顺序添加
#错误的
INSERT INTO emp1
VALUES (2,3400,'2000-12-21','Jerry');

# ② 指明要添加的字段 （推荐）
INSERT INTO emp1(id,hire_date,salary,`name`)
VALUES(2,'1999-09-09',4000,'Jerry');
# 说明：没有进行赋值的hire_date 的值为 null
INSERT INTO emp1(id,salary,`name`)
VALUES(3,4500,'shk');

# ③ 同时插入多条记录 （推荐）
INSERT INTO emp1(id,NAME,salary)
VALUES
(4,'Jim',5000),
(5,'张俊杰',5500);

方式2：将查询结果插入到表中

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

说明：
1、emp1表中要添加数据的字段的长度不能低于employees表中查询的字段的长度。
2、如果emp1表中要添加数据的字段的长度低于employees表中查询的字段的长度的话，就有添加不成功的风险。

10.2、更新数据（修改数据）

# UPDATE .... SET .... WHERE ...
# 可以实现批量修改数据的。

同时修改一条数据的多个字段

UPDATE emp1
SET hire_date = CURDATE(),salary = 6000
WHERE id = 4;



#题目：将表中姓名中包含字符a的提薪20%
UPDATE emp1
SET salary = salary * 1.2
WHERE NAME LIKE '%a%';

修改数据时，是可能存在不成功的情况的。（可能是由于约束的影响造成的）

UPDATE employees
SET department_id = 10000
WHERE employee_id = 102;

10.3、删除数据

DELETE FROM … WHERE…

DELETE FROM emp1
WHERE id = 1;

在删除数据时，也有可能因为约束的影响，导致删除失败

DELETE FROM departments
WHERE department_id = 50;

DML操作默认情况下，执行完以后都会自动提交数据。
若希望执行完后不自动提交数据，则需要使用 SET autocommit = FALSE.

10.4、MySQL8的新特性：计算列

#使用数据库
USE atguigudb;
#创建表
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因a、b的变化而变化。

10.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,
`name` VARCHAR(50),
`authors` VARCHAR(100),
price FLOAT,
pubdate YEAR,
note VARCHAR(100),
num INT
);

DESC books;

SELECT * FROM books;
# 3、向books表中插入记录

# 1）不指定字段名称，插入第一条记录
INSERT INTO books
VALUES(1,'Tal of AAA','Dickes',23,'1995','novel',11);
# 2）指定所有字段名称，插入第二记录
INSERT INTO books(id,NAME,AUTHORS,price,pubdate,note,num)
VALUES(2,'EmmaT','Jane lura',35,'1993','joke',22);
# 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);


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

# 5、将名称为EmmaT的书的价格改为40，并将说明改为drama。
UPDATE books
SET price = 40,note = 'drama'
WHERE NAME = 'EmmaT';

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

# 7、统计书名中包含a字母的书
SELECT NAME
FROM books
WHERE NAME LIKE '%a%';

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

# 9、找出“novel”类型的书，按照价格降序排列

SELECT NAME,note,price
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) sum_num
FROM books
GROUP BY note
ORDER BY sum_num DESC
LIMIT 0,1;

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

SELECT NAME
FROM books
WHERE CHAR_LENGTH(REPLACE(NAME,' ','')) >= 10;

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


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

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

# 19、统计每一种note的数量，并合计总量
SELECT IFNULL(note,'合计总量') AS 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 0,1;

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

# 23、找出书名中字数最多的一本书，不含空格

SELECT *
FROM books
ORDER BY CHAR_LENGTH(REPLACE(NAME,' ','')) DESC
LIMIT 0,1;

10.6、本章练习


#第11章_数据处理之增删改的课后练习
#练习1：
#1. 创建数据库dbtest11
CREATE DATABASE IF NOT EXISTS dbtest11 CHARACTER SET 'utf8';

#2. 运行以下脚本创建表my_employees
USE dbtest11;

CREATE TABLE my_employees(
	id INT(10),
	first_name VARCHAR(10),
	last_name VARCHAR(10),
	userid VARCHAR(10),
	salary DOUBLE(10,2)
);

CREATE TABLE users(
	id INT,
	userid VARCHAR(10),
	department_id INT
);
#3.显示表my_employees的结构

DESC my_employees;

DESC users;

#4.向my_employees表中插入下列数据
ID	FIRST_NAME	LAST_NAME	USERID		SALARY
1	patel		Ralph		Rpatel		895
2	Dancs		Betty		Bdancs		860
3	Biri		Ben		Bbiri		1100
4	Newman		Chad		Cnewman		750
5	Ropeburn	Audrey		Aropebur	1550

INSERT INTO my_employees
VALUES(1,'patel','Ralph','Rpatel',895);

INSERT INTO my_employees VALUES
(2,'Dancs','Betty','Bdancs',860),
(3,'Biri','Ben','Bbiri',1100),
(4,'Newman','Chad','Cnewman',750),
(5,'Ropeburn','Audrey','Aropebur',1550);

SELECT * FROM my_employees;

DELETE FROM my_employees;

#方式2：
INSERT INTO my_employees
SELECT 1,'patel','Ralph','Rpatel',895 UNION ALL
SELECT 2,'Dancs','Betty','Bdancs',860 UNION ALL
SELECT 3,'Biri','Ben','Bbiri',1100 UNION ALL
SELECT 4,'Newman','Chad','Cnewman',750 UNION ALL
SELECT 5,'Ropeburn','Audrey','Aropebur',1550;
			
#5.向users表中插入数据
1	Rpatel		10
2	Bdancs		10
3	Bbiri		20
4	Cnewman		30
5	Aropebur	40
INSERT INTO users VALUES
(1,'Rpatel',10),
(2,'Bdancs',10),
(3,'Bbiri',20),
(4,'Cnewman',30),
(5,'Aropebur',40)

SELECT * FROM users;



#6. 将3号员工的last_name修改为“drelxer”
UPDATE my_employees
SET last_name = 'drelxer'
WHERE id = 3;

#7. 将所有工资少于900的员工的工资修改为1000
UPDATE my_employees
SET salary = 1000
WHERE salary < 900;

#8. 将userid为Bbiri的users表和my_employees表的记录全部删除

#方式1：
DELETE FROM my_employees
WHERE userid = 'Bbiri';

DELETE FROM users
WHERE userid = 'Bbiri';

#方式2：

DELETE m,u
FROM my_employees m
JOIN users u
ON m.userid = u.userid
WHERE m.userid = 'Bbiri';

SELECT * FROM my_employees;
SELECT * FROM users;

#9. 删除my_employees、users表所有数据
DELETE FROM my_employees;
DELETE FROM users;

#10. 检查所作的修正
SELECT * FROM my_employees;
SELECT * FROM users;

#11. 清空表my_employees
TRUNCATE TABLE my_employees;
##########################################
#练习2：
# 1. 使用现有数据库dbtest11
USE dbtest11;

# 2. 创建表格pet
CREATE TABLE pet(
NAME VARCHAR(20),
OWNER VARCHAR(20),
species VARCHAR(20),
sex CHAR(1),
birth YEAR,
death YEAR
);

DESC pet;

# 3. 添加记录
INSERT INTO pet VALUES
('Fluffy','harold','Cat','f','2003','2010'),
('Claws','gwen','Cat','m','2004',NULL),
('Buffy',NULL,'Dog','f','2009',NULL),
('Fang','benny','Dog','m','2000',NULL),
('bowser','diane','Dog','m','2003','2009'),
('Chirpy',NULL,'Bird','f','2008',NULL);

SELECT *
FROM pet;

# 4. 添加字段:主人的生日owner_birth DATE类型。
ALTER TABLE pet
ADD owner_birth DATE;

# 5. 将名称为Claws的猫的主人改为kevin
UPDATE pet
SET OWNER = 'kevin'
WHERE NAME = 'Claws' AND species = 'Cat';

# 6. 将没有死的狗的主人改为duck
UPDATE pet
SET OWNER = 'duck'
WHERE death IS NULL AND species = 'Dog';

# 7. 查询没有主人的宠物的名字；
SELECT NAME
FROM pet
WHERE OWNER IS NULL;

# 8. 查询已经死了的cat的姓名，主人，以及去世时间；
SELECT NAME,OWNER,death
FROM pet
WHERE death IS NOT NULL;

# 9. 删除已经死亡的狗
DELETE FROM pet
WHERE death IS NOT NULL 
AND species = 'Dog';

# 10. 查询所有宠物信息
SELECT * 
FROM pet;

##################################
#练习3：
# 1. 使用已有的数据库dbtest11
USE dbtest11;
# 2. 创建表employee，并添加记录
CREATE TABLE employee(
id INT,
NAME VARCHAR(15),
sex CHAR(1),
tel VARCHAR(25),
addr VARCHAR(35),
salary DOUBLE(10,2)

);

INSERT INTO employee VALUES
(10001,'张一一','男','13456789000','山东青岛',1001.58),
(10002,'刘小红','女','13454319000','河北保定',1201.21),
(10003,'李四','男','0751-1234567','广东佛山',1004.11),
(10004,'刘小强','男','0755-5555555','广东深圳',1501.23),
(10005,'王艳','男','020-1232133','广东广州',1405.16);

SELECT * FROM employee;

# 3. 查询出薪资在1200~1300之间的员工信息。
SELECT *
FROM employee
WHERE salary BETWEEN 1200 AND 1300;

# 4. 查询出姓“刘”的员工的工号，姓名，家庭住址。
SELECT id,NAME,addr
FROM employee
WHERE NAME LIKE '刘%';

# 5. 将“李四”的家庭住址改为“广东韶关”
UPDATE employee
SET addr = '广东韶关'
WHERE NAME = '李四';

# 6. 查询出名字中带“小”的员工
SELECT *
FROM employee
WHERE NAME LIKE '%小%';

注意：例子答案不一定都正确，请谨慎！！！

十一、约束

为什么要约束

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

什么是约束

- 约束是表级的强制规定。
可以在创建表时规定约束（通过 CREATE TABLE 语句），
或者在表创建之后通过 ALTER TABLE 语句规定约束。

11.1、约束的分类

根据约束数据列的限制，约束可分为：
	1、单列约束：每个约束只约束一列
	2、多列约束：每个约束可约束多列数据
	
根据约束的作用范围，约束可分为：
	1、列级约束：只能作用在一个列上，跟在列的定义后面
	2、表级约束：可以作用在多个列上，不与列一起，而是单独定义

根据约束起的作用，约束可分为：

NOT NULL 非空约束，规定某个字段不能为空

UNIQUE 唯一约束，规定某个字段在整个表中是唯一的

PRIMARY KEY 主键(非空且唯一)约束

FOREIGN KEY 外键约束

CHECK 检查约束

DEFAULT 默认值约束

注意： MySQL不支持check约束，但可以使用check约束，而没有任何效果

11.2、非空约束

作用：限定某个字段/某列的值不允许为空
关键字：NOT NULL
特点：

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

方式一：
举例

CREATE TABLE emp(
id INT(10) NOT NULL,
NAME VARCHAR(20) NOT NULL,
sex CHAR NULL
);

非空约束只能在列级添加，不能在表级添加。
方式二：建表时没添加则用此法

alter table 表名称 modify 字段名 数据类型 not null;

例子：

ALTER TABLE emp
MODIFY sex VARCHAR(30) NOT NULL;

11.2.1、删除非空约束

alter table 表名称 modify 字段名 数据类型 NULL;
#去掉not null，相当于修改某个非注解字段，该字段允
许为空
或
alter table 表名称 modify 字段名 数据类型;
#去掉not null，相当于修改某个非注解字段，该字段允许为空

例子：

ALTER TABLE emp
MODIFY sex VARCHAR(30) NULL;

11.3、唯一性约束

作用：用来限制某个字段/某列的值不能重复。
关键字：UNIQUE
特点：

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

方式一：建表时

create table 表名称(
字段名 数据类型,
字段名 数据类型 unique,
字段名 数据类型 unique key,
字段名 数据类型
);
create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
[constraint 约束名] unique key(字段名)
);

例子

CREATE TABLE USER(
id INT NOT NULL,
NAME VARCHAR(25),
PASSWORD VARCHAR(16),
-- 使用表级约束语法
CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD)
#表示用户名和密码组合不能重复
);

方式二：建表后指定唯一键约束

#字段列表中如果是一个字段，表示该列的值唯一。
#如果是两个或更多个字段，那么复合唯一，即多个字段的组合是唯一的

#方式1：
alter table 表名称 add unique key(字段列表);

#方式2：
alter table 表名称 modify 字段名 字段类型 unique;

例子：

ALTER TABLE USER
ADD UNIQUE(NAME,PASSWORD);

ALTER TABLE USER
ADD CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD);

ALTER TABLE USER
MODIFY NAME VARCHAR(20) UNIQUE;

11.3.1、关于复合唯一约束

create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
unique key(字段列表) 
#字段列表中写的是多个字段名，多个字段名用逗号分隔，
#表示那么是复合唯一，即多个字段的组合是唯一的.
);

11.3.2、删除唯一约束

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

SELECT * FROM information_schema.table_constraints WHERE table_name = '表名'; 
#查看都有哪些约束

注意：可以通过 show index from 表名称; 查看表的索引

11.4、PRIMARY KEY 约束

作用：用来唯一标识表中的一行记录。
关键字：primary key
特点：

1、主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值。
2、一个表最多只能有一个主键约束，建立主键约束可以在列级别创建，也可以在表级别上创建。
3、主键约束对应着表中的一列或者多列（复合主键）
4、如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。
5、MySQL的主键名总是PRIMARY，就算自己命名了主键约束名也没用。

6、当创建主键约束时，系统默认会在所在的列或列组合上建立对应的主键索引（能够根据主键查询的，就根据主键查询，效率更高）。
如果删除主键约束了，主键约束对应的索引就自动删除了。
7、需要注意的一点是，不要修改主键字段的值。因为主键是数据记录的唯一标识，如果修改了主键的值，就有可能会破坏数据的完整性。

11.4.1、添加主键约束

创建表时：指定主键约束

create table 表名称(
字段名 数据类型 primary key, #列级模式
字段名 数据类型,
字段名 数据类型
);
create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
[constraint 约束名] primary key(字段名) #表级模式
);

方式二：建表后增加主键约束

ALTER TABLE 表名称 ADD PRIMARY KEY(字段列表);
 #字段列表可以是一个字段，也可以是多个字段，如果是多个字段的话，
 #是复合主键

例子

ALTER TABLE student ADD PRIMARY KEY (sid);

ALTER TABLE emp5 ADD PRIMARY KEY(NAME,pwd);

11.4.2、关于复合主键

create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
primary key(字段名1,字段名2) 
#表示字段1和字段2的组合是唯一的，也可以有更多个字段
);

11.4.3、删除主键约束

alter table 表名称 drop primary key;

说明：删除主键约束，不需要指定主键名，因为一个表只有一个主键，
删除主键约束后，非空还存在。

eg:
ALTER TABLE student DROP PRIMARY KEY;
ALTER TABLE emp5 DROP PRIMARY KEY;

11.5、自增列：AUTO_INCREMENT

作用：某个字段的值自增
关键字：auto_increment
特点与要求：

（1）一个表最多只能有一个自增长列
（2）当需要产生唯一标识符或顺序值时，可设置自增长
（3）自增长列约束的列必须是键列（主键列，唯一键列）
（4）自增约束的列的数据类型必须是整数类型
（5）如果自增列指定了 0 和 null，会在当前最大值的基础上自增；
如果自增列手动指定了具体值，直接赋值为具体值。

11.5.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(字段名)
);

方式二：建表后

alter table 表名称 modify 字段名 数据类型 auto_increment;

11.5.2、如何删除自增约束

#alter table 表名称 modify 字段名 数据类型 auto_increment;
#给这个字段增加自增约束


alter table 表名称 modify 字段名 数据类型; 
#去掉auto_increment相当于删除

11.6、 MySQL 8.0新特性—自增变量的持久化

在MySQL 8.0之前，自增主键AUTO_INCREMENT的值如果大于max(primary key)+1，
在MySQL重启后，会重置AUTO_INCREMENT=max(primary key)+1，
这种现象在某些情况下会导致业务主键冲突或者其他难以发现的问题。


在MySQL 5.7系统中，对于自增主键的分配规则，
是由InnoDB数据字典内部一个 计数器 来决定的，
而该计数器只在 内存中维护 ，并不会持久化到磁盘中
。当数据库重启时，该计数器会被初始化。

MySQL 8.0将自增主键的计数器持久化到 重做日志 中。
每次计数器发生改变，都会将其写入重做日志中。
如果数据库重启，InnoDB会根据重做日志中的信息来初始化计数器的内存值。

11.7、FOREIGN KEY 约束

作用：

限定某个表的某个字段的引用完整性。
比如：员工表的员工所在部门的选择，必须在部门表能找到对应的部分。

关键字：FOREIGN KEY
特点：

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

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

（3）创建(CREATE)表时就指定外键约束的话，先创建主表，再创建从表
（4）删表时，先删从表（或先删除外键约束），再删除主表
（5）当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据
（6）在“从表”中指定外键约束，并且一个表可以建立多个外键约束

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

主表和从表/父表和子表

主表（父表）：被引用的表，被参考的表
从表（子表）：引用别人的表，参考别人的表
例如：员工表的员工所在部门这个字段的值要参考部门表：部门表是主表，员工表是从表。
例如：学生表、课程表、选课表：选课表的学生和课程要分别参考学生表和课程表，学生表和课程表是主表，选课表是从表。

11.7.1、添加外键约束

方式一：建表时

create table 主表名称(
字段1 数据类型 primary key,
字段2 数据类型
);

create table 从表名称(
字段1 数据类型 primary key,
字段2 数据类型,
[CONSTRAINT <外键约束名称>] FOREIGN KEY（从表的某个字段) references 主表名(被参考字段)
);
#(从表的某个字段)的数据类型必须与主表名(被参考字段)的数据类型一致，逻辑意义也一样
#(从表的某个字段)的字段名可以与主表名(被参考字段)的字段名一样，也可以不一样
-- FOREIGN KEY: 在表级指定子表中的列
-- REFERENCES: 标示在父表中的列

方式二：建表后

一般情况下，表与表的关联都是提前设计好了的，因此，
会在创建表的时候就把外键约束定义好。
不过，如果需要修改表的设计（比如添加新的字段，增加新的关联关系），
但没有预先定义外键约束，那么，就要用修改表的方式来补充定义。

-- 格式：
ALTER TABLE 从表名 ADD [CONSTRAINT 约束名] FOREIGN KEY (从表的字段) REFERENCES 主表名(被引用字段) [on update xx][on delete xx];

eg：
ALTER TABLE emp1
ADD [CONSTRAINT emp_dept_id_fk] FOREIGN KEY(dept_id) REFERENCES dept(dept_id);

注意：方式二若添加失败，请检查表中数据是否有主表没有的数据。
总结：约束关系是针对双方的
- 添加了外键约束后，主表的修改和删除数据受约束
- 添加了外键约束后，从表的添加和修改数据受约束
- 在从表上建立外键，要求主表必须存在
- 删除主表时，要求从表从表先删除，或将从表中外键引用该主表的关系先删除

11.7.2、约束等级

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 的方式。

11.7.3、删除外键约束

(1)第一步先查看约束名和删除外键约束

SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';#查看某个表的约束名

ALTER TABLE 从表名 DROP FOREIGN KEY 外键约束名;

（2）第二步查看索引名和删除索引。（注意，只能手动删除）
SHOW INDEX FROM 表名称; #查看某个表的索引名
ALTER TABLE 从表名 DROP INDEX 索引名;

11.8、 CHECK 约束

作用：检查某个字段的值是否符号xx要求，一般指的是值的范围
关键字：check
说明：MySQL 5.7 不支持

MySQL5.7 可以使用check约束，但check约束对数据验证没有任何作用。
添加数据时，没有任何错误或警告.

但是MySQL 8.0中可以使用check约束了。(但我的8.01版本没起作用)。

例子：

create table employee(
eid int primary key,
ename varchar(5),
gender char check ('男' or '女')
);

11.9、DEFAULT约束

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

11.9.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(字段名)
);
说明：默认值约束一般不在唯一键和主键列上加

方式二：建表后

alter table 表名称 modify 字段名 数据类型 default 默认值;

#如果这个字段原来有非空约束，你还保留非空约束，那么在加默认值约束时，
#还得保留非空约束，否则非空约束就被删除了
#同理，在给某个字段加非空约束也一样，如果这个字段原来有默认值约束，
#你想保留，也要在modify语句中保留默认值约束，否则就删除了

alter table 表名称 modify 字段名 数据类型 default 默认值 not null;

11.9.2、如何删除默认值约束

alter table 表名称 modify 字段名 数据类型 ;#删除默认值约束，也不保留非空约束
alter table 表名称 modify 字段名 数据类型 not null; #删除默认值约束，保留非空约束

eg:
alter table employee modify gender char; 
#删除gender字段默认值约束，如果有非空约束，也一并删除

alter table employee modify tel char(11) not null;
#删除tel字段默认值约束，保留非空约束

十二、视图

12.1、常见的数据库对象

在这里插入图片描述

为什么使用视图？

视图一方面可以帮我们使用表的一部分而不是所有的表，
另一方面也可以针对不同的用户制定不同的查询视图。

比如，针对一个公司的销售人员，我们只想给他看部分数据，
而某些特殊的数据，比如采购的价格，则不会提供给他。

再比如，人员薪酬是个敏感的字段，那么只给某个级别以上的人员开放，
其他人的查询视图中则不提供这个字段。

12.2、视图的理解

① 视图，可以看做是一个虚拟表，本身是不存储数据的。
  视图的本质，就可以看做是存储起来的SELECT语句
  
② 视图中SELECT语句中涉及到的表，称为基表

③ 针对视图做DML操作，会影响到对应的基表中的数据。反之亦然。

④ 视图本身的删除，不会导致基表中数据的删除。

⑤ 视图的应用场景：针对于小型项目，不推荐使用视图。针对于大型项目，
可以考虑使用视图。

⑥ 视图的优点：简化查询; 控制数据的访问

12.3、创建视图

在 CREATE VIEW 语句中嵌入子查询

CREATE [OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW 视图名称 [(字段列表)]
AS 查询语句
[WITH [CASCADED|LOCAL] CHECK OPTION]

精简版

CREATE VIEW 视图名称
AS 查询语句

12.3.1、创建单表视图

CREATE VIEW empvu80
AS
SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 80;

#查询视图
SELECT * FROM salvu80;

说明1：实际上就是我们在 SQL 查询语句的基础上封装了视图 VIEW，这样就会基于 SQL 语句的结果集形
成一张虚拟表。
说明2：在创建视图时，没有在视图名后面指定字段列表，则视图中字段列表默认和SELECT语句中的字
段列表一致。如果SELECT语句中给字段取了别名，那么视图中的字段名和别名相同。

12.3.2、创建多表视图

例子1：
CREATE VIEW empview
AS
SELECT employee_id emp_id,last_name NAME,department_name
FROM employees e,departments d
WHERE e.department_id = d.department_id;

例子2：
CREATE VIEW emp_dept
AS
SELECT ename,dname
FROM t_employee LEFT JOIN t_department
ON t_employee.did = t_department.did;

例子3：
CREATE VIEW dept_sum_vu
(name, minsal, maxsal, avgsal)
AS
SELECT d.department_name, MIN(e.salary), MAX(e.salary),AVG(e.salary)
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name;

12.3.3、基于视图创建视图

当我们创建好一张视图之后，还可以在它的基础上继续创建视图。

举例：联合“emp_dept”视图和“emp_year_salary”视图查询
员工姓名、部门名称、年薪信息创建“emp_dept_ysalary”视图。

CREATE VIEW emp_dept_ysalary
AS
SELECT emp_dept.ename,dname,year_salary
FROM emp_dept INNER JOIN emp_year_salary
ON emp_dept.ename = emp_year_salary.ename;

12.4、查看视图

方式一：查看数据库的表对象、视图对象

SHOW TABLES;

方式二：查看视图的结构

DESC / DESCRIBE 视图名称;

方式三：查看视图的属性信息

# 查看视图信息（显示数据表的存储引擎、版本、数据行数和数据大小等）
SHOW TABLE STATUS LIKE '视图名称'\G

执行结果显示，注释Comment为VIEW，说明该表为视图，其他的信息为NULL，
说明这是一个虚表。

方式四：查看视图的详细定义信息

SHOW CREATE VIEW 视图名称;

12.5、. 更新视图的数据

12.5.1、一般情况

MySQL支持使用INSERT、UPDATE和DELETE语句对视图中的数据进行插入、更新和删除操作。当视图中的数据发生变化时，数据表中的数据也会发生变化，反之亦然。

12.5.2、不可更新的视图

要使视图可更新，视图中的行和底层基本表中的行之间必须存在一对一的关系。另外当视图定义出现如下情况时，视图不支持更新操作：

1、在定义视图的时候指定了“ALGORITHM = TEMPTABLE”，
视图将不支持INSERT和DELETE操作；

2、视图中不包含基表中所有被定义为非空又未指定默认值的列，
视图将不支持INSERT操作；

3、在定义视图的SELECT语句中使用了 JOIN联合查询 ，
视图将不支持INSERT和DELETE操作；

4、在定义视图的SELECT语句后的字段列表中使用了 数学表达式 或 子查询 ，
视图将不支持INSERT，也不支持UPDATE使用了数学表达式、子查询的字段值；

5、在定义视图的SELECT语句后的字段列表中使用 DISTINCT 、 聚合函数 、
 GROUP BY 、 HAVING 、UNION 等，视图将不支持INSERT、UPDATE、DELETE；
 
6、在定义视图的SELECT语句中包含了子查询，而子查询中引用了FROM后面的表，
视图将不支持INSERT、UPDATE、DELETE；

7、视图定义基于一个 不可更新视图 ；

8、常量视图。

虽然可以更新视图数据，但总的来说，视图作为虚拟表，主要用于方便查询，不建议更新视图的数据。对视图数据的更改，都是通过对实际数据表里数据的操作来完成的。

12.6、修改、删除视图

12.6.1、修改视图

方式1：使用CREATE OR REPLACE VIEW 子句修改视图

CREATE OR REPLACE VIEW empvu80
(id_number, name, sal, department_id)
AS
SELECT employee_id, first_name || ' ' || last_name, salary, department_id
FROM employees
WHERE department_id = 80;

说明：CREATE VIEW 子句中各列的别名应和子查询中各列相对应。

方式2：ALTER VIEW

ALTER VIEW 视图名称
AS
查询语句

12.6.2、删除视图

删除视图只是删除视图的定义，并不会删除基表的数据。
删除视图的语法是：

DROP VIEW IF EXISTS 视图名称;

#删除多个
DROP VIEW IF EXISTS 视图名称1,视图名称2,视图名称3,...;

说明：基于视图a、b创建了新的视图c，如果将视图a或者视图b删除，会导致视图c的查询失败。这样的视图c需要手动删除或修改，否则影响使用。

12.7、总结

12.7.1、视图的优点

1、操作简单

将经常使用的查询操作定义为视图，
可以使开发人员不需要关心视图对应的数据表的结构、表与表之间的关联关系，
也不需要关心数据表之间的业务逻辑和查询条件，
而只需要简单地操作视图即可，极大简化了开发人员对数据库的操作。

2、减少数据冗余

视图跟实际数据表不一样，它存储的是查询语句。
所以，在使用的时候，我们要通过定义视图的查询语句来获取结果集。
而视图本身不存储数据，不占用数据存储的资源，减少了数据冗余。

3、数据安全

MySQL将用户对数据的 访问限制 在某些数据的结果集上，
而这些数据的结果集可以使用视图来实现。
用户不必直接查询或操作数据表。
这也可以理解为视图具有隔离性 。
视图相当于在用户和实际的数据表之间加了一层虚拟表。

同时，MySQL可以根据权限将用户对数据的访问限制在某些视图上，
用户不需要查询数据表，可以直接通过视图获取数据表中的信息。
这在一定程度上保障了数据表中数据的安全性。

4、适应灵活多变的需求

当业务系统的需求发生变化后，如果需要改动数据表的结构，
则工作量相对较大，可以使用视图来减少改动的工作量。
这种方式在实际工作中使用得比较多。

5、能够分解复杂的查询逻辑

数据库中如果存在复杂的查询逻辑，则可以将问题进行分解，
创建多个视图获取数据，再将创建的多个视图结合起来，
完成复杂的查询逻辑。

12.7.2、视图的不足

如果我们在实际数据表的基础上创建了视图，那么，
如果实际数据表的结构变更了，我们就需要及时对相关的视图进行相应的维护。

特别是嵌套的视图（就是在视图的基础上创建视图），维护会变得比较复杂， 
可读性不好 ，容易变成系统的潜在隐患。

因为创建视图的 SQL 查询可能会对字段重命名，也可能包含复杂的逻辑，
这些都会增加维护的成本。

实际项目中，如果视图过多，会导致数据库维护成本的问题。

所以，在创建视图的时候，你要结合实际项目需求，
综合考虑视图的优点和不足，这样才能正确使用视图，使系统整体达到最优。

12.8、练习

#练习1：
#1. 使用表emps创建视图employee_vu，(emp 数据复制与employee，使用AS方式创建)
#其中包括姓名（LAST_NAME），员工号（EMPLOYEE_ID），部门号(DEPARTMENT_ID)

CREATE OR REPLACE VIEW employee_vu(lname,emp_id,dept_id)
AS
SELECT last_name,employee_id,department_id
FROM emps;


#2. 显示视图的结构
DESC employee_vu;

#3. 查询视图中的全部内容
SELECT * FROM employee_vu;

#4. 将视图中的数据限定在部门号是80的范围内
CREATE OR REPLACE VIEW employee_vu(lname,emp_id,dept_id)
AS
SELECT last_name,employee_id,department_id
FROM emps
WHERE department_id = 80;

#练习2：
CREATE TABLE emps
AS
SELECT * FROM atguigudb.employees;

DESC emps;

#1. 创建视图emp_v1,要求查询电话号码以‘011’开头的员工姓名和工资、邮箱

CREATE OR REPLACE VIEW emp_v1
AS
SELECT last_name,salary,email
FROM emps
WHERE phone_number LIKE '011%';


#2. 要求将视图 emp_v1 修改为查询电话号码以‘011’开头的并且邮箱中包含 e 字符
#的员工姓名和邮箱、电话号码

CREATE OR REPLACE VIEW emp_v1
AS
SELECT last_name,email,phone_number,salary
FROM emps
WHERE phone_number LIKE '011%'
AND email LIKE '%e%';

SELECT * FROM emp_v1;

#3. 向 emp_v1 插入一条记录，是否可以？

DESC emps;

# 实测：失败了
INSERT INTO emp_v1
VALUES('Tom','tom@126.com','01012345');


#4. 修改emp_v1中员工的工资，每人涨薪1000
SELECT *
FROM emp_v1;

UPDATE emp_v1
SET salary = salary + 1000;

#5. 删除emp_v1中姓名为Olsen的员工
DELETE FROM emp_v1
WHERE last_name = 'Olsen';

#6. 创建视图emp_v2，要求查询部门的最高工资高于 12000 的部门id和其最高工资

CREATE OR REPLACE VIEW emp_v2(dept_id,max_sal)
AS
SELECT department_id,MAX(salary)
FROM emps
GROUP BY department_id
HAVING MAX(salary) > 12000;


SELECT * FROM emp_v2;

#7. 向 emp_v2 中插入一条记录，是否可以？

不可以！
#错误：The target table emp_v2 of the INSERT is not insertable-into
INSERT INTO emp_v2(dept_id,max_sal)
VALUES(4000,20000);


#8. 删除刚才的emp_v2 和 emp_v1
DROP VIEW IF EXISTS emp_v1,emp_v2;

SHOW TABLES;