一、MySQL介绍
1.1 引言
之前在学习JavaSE基础时，存储数据的方式有哪些？
- Java程序存储数据（变量，对象，数组，集合），数据都会被存储在Java内存中，属于瞬时状态存储。
- 基于IO的方式，将Java内存中的数据，持久化到本地的一个文件中，保存到硬盘上，属于持久状态存储。
上述的存储方式存在什么问题呢？
- 基于内存存储数据，明显不安全，JVM一停，数据没了，非常不安全。
- 如果采用IO的形式，存储到本地文件
- 没有数据类型的区分，会导致操作持久化的数据很麻烦。
- 存储的数据量级比较小，如果将1个G的数据存到一个文件里，很难维护。
- 没有访问的安全限制。
- 没有做备份，和误操恢复的能力。
- 想查询某一个数据，但是很难找。
1.2 数据库
数据库是按照数据结构来组织，存储和管理数据的仓库。是一个长期存储在计算机内的，有组织的，有共享的统一管理的数据集合。
数据库的分类其他非常多，咱们现在只需要关注两种即可：
- 关系型数据库：Oracle、MySQL、SQL Server、PostgreSQL、DB2，这些都是以表格Table的形式存储，多表格之间建立关联关系，通过分类，合并，连接，选取以及一些运算进行访问。
- 非关系型数据库：Redis、Elasticsearch、MongoDB、HBase等等都属于非关系型数据库，他们的存储方式各有各的道。
1.3 MySQL介绍
MySQL属于 关系型数据库 ，由瑞典的MySQL AB公司开发，属于Oracle旗下的产品。MySQL依然是最主流的关系型数据库之一，在WEB应用方面，MySQL是最好的关系型数据库之一。
MySQL官网：https://www.mysql.com/
现在关系型数据库的种类还是比较多的，每种数据库的操作方式会有一些不同，但是大方向上基本都是一样的，关系型数据库，后期都基于SQL语句做基本交互，每个数据库都是基于SQL99规范。但是一些细节内容可能会有一些不同，但是大方向基本没有特别大的区别。
二、MySQL下载&安装
MySQL现在的主流版本就是5.7和8.0，如果你电脑上已经有这两个版本的任意版本，不要卸载再安装，成本蛮高的。
2.1 安装MySQL 5.7（别跳过）
首先去官网下载MySQL 5.7的安装包。
下载好一个Windows环境下的安装包，不需要做太多的额外操作，基本就是傻瓜式安装，不停的下一步下一步下一步下一步…………
到这，MySQL5.7就安装好了。同时可以测试一下链接效果。
也可以配置一下环境变量，找一下MySQL的默认安装地址
我没指定安装地址，默认就是这：C:\Program Files\MySQL\MySQL Server 5.7\bin
将这个路径配置到环境变量的Path里。
配置好之后， 重新打开一个cmd窗口！！！！！ 链接
2.2 安装MySQL 8.0
看8.0之前，看一下5.7的安装，流程基本都是一致的。
跟5.7一样，去逛网下载MySQL的安装包，这次下载的是8.0的版本
下载好之后，直接打开即可。
这次安装流程一致，只是安装的服务是8.0的，没贴图的，就是和MySQL5.7一样的操作
安装成功后，测试一下链接。
环境变量和MySQL5.7的方式是一样的。
重新打开一个cmd窗口！！！！！链接
三、SQL语言
3.1 概念
SQL（Structured Query Language）结构化查询语言。SQL用于对存储数据，更新，查询和管理关系型数据库的程序设计语言。
通常执行对数据库的增删改查，简称C（Create）R（Read）U（Update）D（Delete）
在MySQL中有一点：
对于数据库的操作，需要进入的MySQL环境下进行指令输入，属于完一个语句，需要使用 ; 进行结尾。
3.2 基本操作
在做基本操作之前，先要对MySQL的整体结构有一个了解。
一个MySQL服务下会有很多个库，其中MySQL中会自带4个库，下面绿色的标识。
这四个不要动。要玩的话，需要自己主动的去创建数据库。
开始操作，首先用doc窗口，或者用MySQL提供的命令窗口去进入到MySQL环境。
查看MySQL中所有的数据库
mysql> show databases; # 显示当前MySQL中包含的所有数据库
| 数据库名称 | 描述 |
|---|---|
| information_schema | 信息数据库，保存着关乎所有数据库的信息（元数据）。 |
| mysql | 核心数据库，主要负责存储数据库的用户，权限设置，关键字等等。 |
| performance_schema | 性能优化的数据库，性能优化的引擎等等都在里面。 |
| sys | 系统数据库存储元数据信息的库，可以了解系统瓶颈的问题。 |
创建自定义的数据库
mysql> create database 数据库名称;
mysql> create database 数据库名称 character set utf8mb4;
mysql> create database if not exists 数据库名称;
查看数据库创建信息
mysql> show create database 数据库名称; # 查看数据库创建时的基本信息
修改数据库（了解）
mysql> alter database 数据库名称 character set gbk; # 修改数据库的字符集
删除数据库
mysql> drop database 数据库名称;
使用数据库
mysql> use 数据库名称;
查看当前所使用的数据库
mysql> select database(); # 查看当前使用的是哪个数据库
四、客户端工具
这里就带领大家安装一个Navicat工具。
类似其他的客户端工具就不带领大家去玩了，咱们就接触Navicat就足够了。
安装OK之后，直接基于Navicat对MySQL做一些基本的操作
指定上各种连接信息后，会发现Navicat无法正常连接，原因是因为MySQL升级到8.0版本之后，采用的加密方式是caching_sha2_password，这种加密导致8.0的版本用远程工具无法正常的链接，需要将现在用的root用户的密码加密方式，修改为mysql_native_password的方式。
用黑窗口，连接上MySQL服务，在内部执行下面的指令
mysql> alter user 'root'@'localhost' identified with mysql_native_password by '密码';
链接数据库成功后，会看到这个界面
五、DQL操作（重要）
5.1 数据库表的基本结构
关系结构数据库的是以表格（Table）进行数据的存储，表格由 行 和 列 组成。
这里提供了一套test库的表和测试数据，后面操作都基于这几张表来玩。
构建好test库，将课程资料中提供的test.sql文件，直接拖拽到表的位置
导入之后，关闭，需要在表的位置按F5，刷新一波。
5.2 基本查询
语法：SELECT 列名 FROM 表名
| 关键字 | 描述 |
|---|---|
| SELECT | 指定要查询的列 |
| FROM | 指定要查询的表 |
5.2.1 查询部分列
-- 查询员工表中的员工id，名称，邮件。
-- MySQL中可以忽略列名的大小写，表名也可以忽略。
SELECT
employee_id,first_name,last_name,email
FROM
t_employees;
5.2.2 查询所有列
在公司中开发的过程中，哪怕你有200个列，需要查询200个，你也必须写200个列。
在自己玩的时候，为了提升学习的速度，可以通过 * 来代表所有的列。*这个关键字，可以放在SELECT的后面，代表所有的列。
-- 查询全部的列
SELECT
*
FROM
t_employees;
5.2.3 对列中的数据进行运算
查询员工表中，员工的名称，以及所有员工的年薪是多少（12个月的薪资）
-- 查询员工表中，员工的名称，以及所有员工的年薪是多少（12个月的薪资）
SELECT
first_name,last_name,salary * 12
FROM
t_employees;
| 算术运算符 | 描述 |
|---|---|
| + | 两列做加法运算 |
| - | 两列做减法运算 |
| * | 两列做乘法运算 |
| / | 两列做除法运算 |
Ps：%符号，不是取模，在MySQL中，%属于一个通配符。
5.2.4 列的别名
查询员工表中，员工的名称，以及所有员工的年薪是多少（12个月的薪资），同时给返回的虚拟表的列设置中文名称
可以在查询的列后面，追加上as关键字，然后指定返回的列的别名
也可以简写，不写as，直接在查询的列后面追加列的别名
SELECT
first_name as "名", last_name as "姓", salary * 12 年薪
FROM
t_employees;
5.2.5 查询结果去重
通过员工表，查询部门ID。
-- 通过员工表，查询部门ID
SELECT
department_id
FROM
t_employees
通过员工表，查询部门ID,不查看重复数据，去重。
DISTINCT放在所有列名的最前面，会帮你把数据重复的行，去掉。
-- 通过员工表，查询部门ID,不查看重复数据，去重.
SELECT
DISTINCT department_id
FROM
t_employees
5.3 排序查询
在执行SELECT查询后，查询到的结果一般是跟表中的结果顺序一致的。
如果需要基于一些列做排序，可以使用MySQL提供的order by 操作
语法： SELECT 列名 FROM 表名 ORDER BY 排序列 [排序规则] , 排序列 [排序规则] …………
| 排序规则 | 描述 |
|---|---|
| ASC（默认规则） | 对前面排序列做升序排序 |
| DESC | 对前面排序列做降序排序 |
查询员工的编号，姓，薪资。 按照薪资的高低做降序排序
# 查询员工的编号，姓，薪资。 按照薪资的高低做降序排序
SELECT
employee_id,last_name, salary
FROM
t_employees
ORDER BY
salary DESC
查询员工的姓，工资，入职时间。 优先按照工资做降序，再根据入职时间做升序
-- 查询员工的姓，工资，入职时间。 优先按照工资做降序，再根据入职时间做升序
-- 运行选中的SQL的快捷键 ，Ctrl + Shift + R
SELECT
last_name 姓, salary 工资, hire_date 入职时间
FROM
t_employees
ORDER BY
salary DESC,
hire_date
5.4 条件查询
语法：select 列明 from 表名 where 条件
| 关键字 | 描述 |
|---|---|
| where | 在查询结果中，筛选符合条件的查询结果，条件为布尔表达式 |
5.4.1 等值判断（=）
注意：与Java不中，Java中是==，而在MySQL中是=
查询薪资是11000的员工信息。（查询员工的编号，名称，薪资）
select
employee_id,first_name ,salary
from
t_employees
where
salary = 11000;
5.4.2 不等值判断（<、>、<=、>=、!=、<>）
其中，前四个没啥说的，常规的大于，小于之类的判断。
其中 != 和 <> 都代表不等于的意思。
查询薪资是大于11000员工信息。（查询员工的编号，名称，薪资）
select
employee_id,first_name ,salary
from
t_employees
WHERE
salary > 11000;
5.4.3 逻辑判断（and，or，not）
类似Java中的 && ，|| ，!
其中 and 左右的条件都需要满足才可以。
其中 or 左右的条件，满足其一就可以查询到。
其中 not 会将条件取反
查询薪资是11000大洋，并且提成是0.3的员工信息（查询员工的编号，名称，薪资，提成）
select
employee_id,first_name ,salary ,commission_pct
from
t_employees
where
salary = 11000 and commission_pct = 0.3;
5.4.4 区间查询（between 数值 and 数值）
这个between and其实就相当于 用大于等于和小于等于的组合。
包含边界的。
需要指定好字段值的边界。并且需要左侧的数值小，右侧的数值大。不然无法查询到结果。
查询员工的薪资在5000~10000之间的员工信息。（查询员工的编号，名称，薪资）
select
employee_id,first_name ,salary
from
t_employees
where
salary between 5000 and 10000;
5.4.5 NULL 值判断（is null、is not null）
当对某个列判断是否是NULL值时，不能使用 = 或者 != 之类的方式。
必须采用 字段 is null 、 字段 is not null 的方式
查询没有提成的员工信息（查询员工的编号，名称，薪资，提成）
select
employee_id,first_name ,salary,commission_pct
from
t_employees
where
commission_pct is not null;
5.4.6 字段多值判断（in）
正常如果涉及到了某一个字段，可以为多个值的匹配条件时。
正常可能需要 字段 = xxx or 字段 = yyy or 字段 = zzz。 写着成本太高。
可以采用in来实现。
字段 in (xxx,yyy,zzz)
查询员工属于60，70，80，90号部门的员工信息。（查询员工的编号，名称，薪资，部门编号）
select
employee_id,first_name ,salary,department_id
from
t_employees
where
department_id in (60,70,80,90);
5.4.7 模糊查询（like）
模糊查询一般是来匹配字符串的。
其中有两个关键字。 % _
字段 like ‘s%’ ：查询出这个字段中以s开头的数据。
字段 like ‘s_’ ：查询出s开头，并且后面只有一个字符
%：代表任意长度的任意字符
_：代表单个任意字符
Ps：这两个特殊字符只能配合like使用
查询名字以L开头的员工信息（查询员工的编号，名称，薪资）
select
employee_id,first_name ,salary
from
t_employees
where
first_name like 'L%'
查询名字以L开头，但是名字长度为4个字符的员工信息（查询员工的编号，名称，薪资）
select
employee_id,first_name ,salary
from
t_employees
where
first_name like 'L___'
5.4.8 分支结构查询（case when then else end）
语法：这里的分支结构查询一般是放在select后面的特殊操作。
case
when 条件1 then 结果1
when 条件2 then 结果2
when 条件3 then 结果3
………………
else 结果
end
可以认为是case开头，end结尾，中间写when then的内容
就是类似Java中的switch
查询员工信息，并根据薪资范围，体现出员工的薪资等级
salary 0 ~ 4000 = E
salary 4000 ~ 6000 = D
salary 6000 ~ 8000 = C
salary 8000 ~ 10000 = B
salary 10000 ~ …… = A
select
employee_id,first_name ,salary ,
case
when salary >= 0 and salary < 4000 then 'E'
when salary >= 4000 and salary < 6000 then 'D'
when salary >= 6000 and salary < 8000 then 'C'
when salary >= 8000 and salary < 10000 then 'B'
else 'A'
end as salary_level
from
t_employees;
5.4.9 逻辑判断（if、ifnull）
这里的if，和ifnull类似于Java中调用方法实现某个功能。
if(条件表达式,结果1,结果2)：条件表达式为true，返回结果1，否则返回结果2
ifnull(值,结果)：如果值为NULL，返回结果，如果值不为NULL，返回值本身。
查询员工信息，如果薪资大于10000，返回小康生活，否则，返回勉强生存（查询员工的编号，名称，薪资）
select
employee_id,first_name ,salary,if(salary > 10000,'小康生活','勉强生存')
from
t_employees;
查询员工号信息，如果有提成，返回提升的比例，如果没有，返回吃底薪。（查询员工的编号，名称，薪资）
select
employee_id,first_name ,salary,ifnull(commission_pct,'吃底薪')
from
t_employees;
5.5 时间函数查询
这里是针对MySQL中的时间类型提供的一些函数以及操作。
下面会玩一些关于时间函数的操作。
| 时间函数 | 描述 |
|---|---|
| sysdate() | 返回当前系统时间，返回年-月-日 时:分:秒 |
| now() | 返回当前系统时间，返回年-月-日 时:分:秒 |
| curdate() | 返回当前系统的年月日 |
| curtime() | 返回当前系统的时分秒 |
| week(date) | 返回指定日期是今年的第几周 |
| year(date) | 返回指定日期的年 |
| month(date) | 返回指定日期的月 |
| day(date) | 返回指定日期的日 |
| hour、minute、second(date) | 返回指定日期的时、分、秒 |
5.6 字符串函数查询
针对字符串的一些函数。
| 函数 | 描述 |
|---|---|
| concat(str1,str2,str…) | 将多个字符串拼接到一起…… |
| upper、lower(str) | upper是将字符串切换为大写、lower将字符串切换为小写 |
| substring(str,num,len) | 将字符串从num位置开启截取len个内容，num从1开始 |
| insert(str,pos,len,newstr) | 将str中指定的pos位置开始len长度的内容替换为newStr |
| replace(str,from_str,to_str) | 将str中所有的from_str，替换为to_str |
| date_format(date,format) | 将时间类型date，根据format的格式转换为varchar |
| str_to_date(str,format) | 将字符串类varchar，根据format的格式转换为date |
format：%Y-%m-%d %H:%i:%s 表示 yyyy-MM-dd HH:mm:ss
5.7 聚合函数查询
针对多行数据做的一个统计。
一般有五种需要咱们掌握
语法： select 聚合函数(列名) from 表名;
| 函数名 | 描述 |
|---|---|
| sum(列名) | 统计当前列所有数据的总和 |
| max(列名) | 获取到当前列中的最大值 |
| min(列名) | 获取到当前列中的最小值 |
| avg(列名) | 获取到当前列中所有数据的平均值 |
| count(列名) | 获取当前数据的总条数 |
基于上述的聚合函数，针对薪资做一个统计，分别统计薪资总和，薪资最大值，薪资最小值，薪资的平均值，当前员工的个数。
一般都是针对数值类型的字段做具体的统计，几乎不会对字符串做什么统计操作，count除外。
select
sum(salary) 薪资总和, max(salary) 薪资最大值, min(salary) 薪资最小值,avg(salary) 薪资平均值, count(*) 员工的个数
from
t_employees;
5.8 分组查询
语法：select 列名 from 表名 where 条件 group by 列名
查询各部门的人数（查询部门编号，部门对应的人数）
select
department_id 部门编号, count(*) 部门人数
from
t_employees
group by department_id;
查询各个部门的平均工资（查询部门编号，部门的平均工资）
select
department_id 部门编号, avg(salary) 平均工资
from
t_employees
group by department_id;
查询各个部门、各个岗位的人数（部门编号，岗位信息，人数）
1、根据department_id分组。
2、根据job_id分组
3、做count统计查询
select
department_id 部门编号, job_id 岗位信息, count(*) 人数
from
t_employees
group by department_id,job_id;
# 查询各个部门、各个岗位，薪资大于5000的人数（部门编号，岗位信息，人数）
select
department_id 部门编号, job_id 岗位信息, count(*) 人数
from
t_employees
where
salary > 5000
group by department_id,job_id;
5.9 分组过滤查询
如果需要在做过分组之后，再次对接过做二次筛选，需要使用having的方式编写条件
语法： select 列名 from 表名 where 条件 group by 列名 having 条件 order by 列名
查询各个部门、各个岗位的人数（部门编号，岗位信息，人数）,只查看人数大于10个的
select
department_id 部门编号, job_id 岗位信息, count(*) 人数
from
t_employees
group by department_id,job_id
having 人数 > 10;
查询各个部门中，平均薪资大于10000的部门信息（部门编号，平均薪资）
select
department_id 部门编号,avg(salary) avg_salary
from
t_employees
group by department_id
having avg_salary > 10000;
# 查询各个部门中，所有员工平均薪资大于10000的部门信息（部门编号，平均薪资）,在根据平均薪资做排序
select
department_id 部门编号,avg(salary) avg_salary
from
t_employees
group by department_id
having avg_salary > 10000
order by avg_salary asc;
5.10 限定查询
比如查询时，查询到了上千条的数据，但是暂时只需要前5条，可以基于limit只获取前5条数据返回。
采用limit帮助咱们实现。 limit是MySQL特有的一个关键字。
语法：select 列名 from 表名 where 条件 group by 列名 having 条件 order by 列名 limit 起始行,行数
起始行（offset）：你返回的数据，从第几行开始， 从第0行开始。
行数（size）：一共返回几行数据
limit 0,5：从第一行数据开始，往下一共返回5条。
limit的第一个offset可以省略不写，不写代表写的是0。
查询表中薪资最高的前5名员工的所有信息。
- 薪资最高的话，需要基于salary做降序排序。
- 基于limit，只返回前5条数据。
select
*
from
t_employees
order by salary desc
limit 0,5
查询表中薪资最高的6~10名员工的所有信息。
select
*
from
t_employees
order by salary desc
limit 5,5
limit很多时候可以同于分页操作，比如需要分页时
可以采用limit，在数据库中查询到不同页数的数据，给页面返回，让页面展示数据即可。
5.11 子查询
查询语句返回的结果，可以再次看成一张表去操作。
select 列名 from 表名 where 条件（子查询操作替代一些值）
select 列名 from （子查询的结果集） where 条件
5.11.1 子查询作为查询字段某个值
查询薪资和Bruce工资一致的员工信息。
通过查询可以得到Bruce的工资信息
再基于另一个查询，将前面的Bruce的薪资结果作为条件判断的一环，从而实现利用子查询得到结果
1、查询Bruce的工资信息，返回 单列单行
select salary from t_employees where first_name = ‘Bruce’;
2、基于Bruce的工资信息查询与其薪资一致的员工信息
select * from t_employees where salary = (select salary from t_employees where first_name = 'Bruce');
5.11.2 子查询作为 字段多值判断的值
因为字段多值判断是采用in的方式去做条件筛选。 一个字段需要多个值。
在利用子查询时，应当返回 单列多行 数据。
查询last_name为King同一部门的员工信息
1、查询last_name为King的部门信息。
select department_id from t_employees where last_name = ‘King’;
2、基于上述查询返回的结果，查看同部门的员工信息
select * from t_employees where department_id in (select department_id from t_employees where last_name = 'King');
5.11.3 子查询作为 一张表操作。
可以直接基于子查询返回的结果集做二次筛选。
查询员工表中，工资排名前5名的员工。
这个查询可以直接使用order by 排序，然后基于limit做筛选。
就为了使用，搞两个SQL实现，利用子查询来玩。
1、查询员工的信息，基于salary做降序排序。
select * from t_employees order by salary desc;
2、将上述的结果基于limit筛选出前5条。
select * from (select * from t_employees order by salary desc) as temp limit 5
5.11.4 子查询的ALL、ANY（了解）
之前在给字段做=判断时，子查询必须返回单列单行的数据，不然报错。
其实在做=判断时，即便返回了单列多行数据，也可以采用ALL、ANY关键字解决问题。
查询工资高于60部门员工的，所有人的信息。
select salary from t_employees where department_id = 60;
1、查询比60部门所有员工薪资都高的员工信息。
select * from t_employees where salary > ALL (select salary from t_employees where department_id = 60);2、查询比60部门任意一名员工薪资高的员工信息。
select * from t_employees where salary > ANY (select salary from t_employees where department_id = 60);
5.12 合并查询（了解）
将两个select操作的结果整合为一个结果集返回。一般是为了规避掉一些查询方式导致的效率较低，可以采用合并查询来提升效率。
语法：select 列名 from 表名1 union select 列名 from 表名2; （会去重）
语法：select 列名 from 表名1 union all select 列名 from 表名2; （不会去重）
在使用union 或者 union all时，需要保证两个select的列的个数是一致的。
哪怕两次Select的列不是一个东西，但是个数一致，那就基于第一个select的列，作为返回的基准，将结果合并到一起。
5.13 表连接查询
表连接可以将多张表关联到一起，返回需要的内容
语法：select 列名 from 表1 连接方式 表2 on 连接条件 连接方式 表3 on 连接条件 …………
内连接语法： select 列名 from 表1 inner join 表2 on 连接条件
外连接：
- 左外连接：select 列名 from 表1 left [outer] join 表2 on 连接条件
- 右外连接：select 列名 from 表1 right [outer] join 表2 on 连接条件
内连接查询操作。
内连接查询，针对哪些 连接条件 无法满足的数据，会直接筛选掉。
查询当前员工名称以及对应的部门名称。
# 显示内连接 select e.first_name , e.last_name , d.department_name from t_employees as e inner join t_departments as d on e.department_id = d.department_id; # 隐示内连接 select e.first_name , e.last_name , d.department_name from t_employees e, t_departments d where e.department_id = d.department_id;查询员工的名称，部门的名称，部门所在国家的信息（三张表联查）
# 查询员工的名称，部门的名称，部门所在国家的信息（三张表联查) # 显示内连接 select e.first_name , e.last_name ,d.department_name,l.city from t_employees e inner join t_departments d on e.department_id = d.department_id inner join t_locations l on l.location_id = d.location_id; # 隐示内连接 select e.first_name , e.last_name ,d.department_name,l.city from t_employees e , t_departments d ,t_locations l where e.department_id = d.department_id and d.location_id = l.location_id;
外连接查询
还是基于前面玩的查询，查询当前员工名称以及对应的部门名称。
因为之前用的内连接的方式，导致一个员工
发现，最后的部门Id是一个NULL，导致后续查询时，并没有这个员工的信息返回。
现在可以采用外链接的方式，来解决这个问题。
外链接前面说过左外的语法，和右外的语法。
其实就是将左边的表，或者是右边的表作为基准表，基准表回返回全部的数据，无论连接条件是否满足。
# 查询当前 所有 员工名称以及对应的部门名称。 select e.first_name , e.last_name ,d.department_name from t_employees e left outer join t_departments d on e.department_id = d.department_id;注意，左外和右外是指定哪张表是基准表，基准表的数据会全部返回，匹配不到内容的位置，会用NULL填充上。
5.14 常见错误
六、DML操作（重要）
关于DML，只需要保证可以完成三个操作即可。
6.1 新增（insert）
语法1：insert into 表名 (列1,列2,列3,列4…) values (值1,值2,值3,值4…);
语法2：insert into 表名 values (值1,值2,值3,值4…);
语法3：insert into 表名 (列1,列2,列3,列4…) values (值1,值2,值3,值4…),(值1,值2,值3,值4…)……;（批量添加）
MySQL中最长见的三个数据类型，数值，字符串，时间。
给t_jobs表添加一条数据。 记得确保前面给的列数，和后面的值的个数需要保持一致。
# 给t_jobs表添加一条数据 insert into t_jobs (job_id,job_title,min_salary,max_salary) values ('运营','运营',500,50000);
给t_employees表添加一条数据，采用语法2的方式。 需要确保表中的所有字段的值都要给上。
针对时间类型数据的添加，在MySQL端可以直接输入字符串的方式，MySQL可以帮助咱们做一些数据类型的转换。
# 给t_employees表添加一条数据，采用语法2的方式 insert into t_employees values (100001,'张','三','123@123.com','18888888888','1999-11-11','程序员',5000,0.20,100,90);
给t_jobs表中基于一条SQL语句追加多行数据。而且可以看到受影响行数的信息。
insert into t_jobs (job_id,job_title,min_salary,max_salary) values ('A','做A',5000,8000), ('B','做B',5000,8000), ('C','做C',5000,8000);
6.2 修改（update）
语法：update 表名 set 列1 = 新值1,列2 = 新值2 …… [where 条件];
正常修改语句从语法的角度讲，可以不追加where条件，但是咱们再实际操作时， 一定要追加上条件，不然整张表都要被修改成一模一样的数据。
修改job_id标识为A的数据，将薪资最小和最大调整为3000，5000
update t_jobs set min_salary = 3000, max_salary = 5000 where job_id = 'A';如果返回的受影响行数为1，说明当前标识为A的这行数据发生了变化。
如果返回的受影响行数为0，不代表SQL执行失败，只是单纯的这行数据没变化。
6.3 删除（delete）
语法：delete from 表名 [where 条件];
从删除语句的语法来说，where条件可以不写，但是如果不写，就相当于清空了整张表。正常业务情况下是需要追加where。
将之前添加进去的测试数据，全部都删除
我这里是针对t_jobs和t_employees表追加了数据，现在都干掉~
# 删除t_jobs表中的，标识为A，B，C，程序员，运营这5条数据 delete from t_jobs where job_id in ('A','B','C','程序员','运营');# 删除t_employees表中标识为100001，100002，100003，100004的员工信息干掉 delete from t_employees where employee_id in (100001,100002,100003,'100004');
6.4 常见问题
七、数据表操作DDL操作
7.1 MySQL中的数据类型
7.1.1 数值类型
跟Java中的几乎是一致的，但是比Java会更丰富一些，不过大类别依然是整形和浮点型
浮点型的数值中，所有的M，D，都必须保证M >= D
| 类型 | 大小 | 范围 | 描述 |
|---|---|---|---|
| tinyint | 1byte | （-128~127） | 小整数，映射Java中的Byte |
| smallint | 2byte | ………… | 小整数，映射Java中的Short |
| int | 4byte | ………… | 默认整数，映射Java中的Integer |
| bigint | 8byte | ………… | 长整数，映射Java中的Long |
| float(10,2) | 4byte | ………… | 单精度浮点类型，映射Java中的Float |
| double(10,2) | 8byte | ………… | 双精度浮点类型，映射Java中的Double |
| decimal(M,D) | 看下面~ | ………… | 映射Java中的BigDecimal |
decimal存储的空间大小：
每9位数最多占用4字节，整数和小数要分开算，如果小于9位数，基于下述表格计算
位数 大小 0 0 1–2 1 3–4 2 5–6 3 7–9 4 如果指定的
DECIMAL(18,9)，这个数值是小数点前，占用4字节，小数点后，也占用4字节。一共占用8字节。如果指定的
DECIMAL(20,6)，这个数值是小数点前有14位，9位占用4子节，剩下5位占用3字节。小数点后的6位，占用3字节。一共占用10字节。
7.1.2 时间类型
MySQL中的时间类型比Java中要丰富一些。Java中有Date，LocalDate之类的，MySQL相对更多一些。
| 类型 | 大小 | 范围 | 格式 | 描述 |
|---|---|---|---|---|
| date | 3byte | 用到死！ | yyyy-MM-dd | 存储日期 |
| time | 3byte | 够用！ | HH:mm:ss | 存储时间 |
| datetime | 8byte | 用到死！ | yyyy-MM-dd HH:mm:ss | 存储日期和时间 |
| timestamp | 4byte | 1970-01-01 00:00:00~ 2038-01-19 03:14:07 | yyyy-MM-dd HH:mm:ss | 存储日期和时间 |
datetime和timestamp的区别：
存储的大小不一样。
- datetime占用8字节。
- timestamp占用4字节。
存储的范围不一样。
- datetime可以持续到9999年。
- timestamp是从1970年~2038年。
存储的时区问题。
- datetime存储时，不考虑时区，存储什么就是什么，取的时候也就是什么。
- timestamp存储时，会将时间从当前时区转换为UTC进行存储，查询时，将其再转换为当前客户端的时区。
其次，在5.6.5版本后，datetime和timestamp对于行级数据变化，自动更新为当前系统时间，都是支持的。（后面创建表会演示这个操作）
7.1.3 字符串类型
MySQL中的字符串类型那就多了，Java中就String。
| 类型 | 描述 |
|---|---|
| char(长度) | 定长字符串 |
| varchar(长度) | 变长字符串 |
| text | 大文本类型 |
| blob | 以二进制的形式存储大文本数据 |
char和varchar核心区别：
- char指定好长度后，无论存储多少数据，就占这个长度的大小。
- varchar指定好长度后，你写入了多大的数据，他就占用多少大小。
7.2 创建表
创建表的语法：
create table 表名( 列名 数据类型 [约束], 列名 数据类型 [约束], ………… 列名 数据类型 [约束] -- 最后一行不要在后面追加逗号 );
创建一张学生表，student。表名和列名如果存在多个单词，用_分开，不要出现大写字母。
| 列名 | 数据类型 | 约束 | 描述 |
|---|---|---|---|
| student_id | bigint | 无 | 学员编码 |
| student_name | varchar(32) | 无 | 学员名称 |
| student_gender | char(1) | 无 | 学员性别 |
| student_birthday | date | 无 | 学员生日 |
创建学生表的SQL语句
create table student( student_id bigint comment '学员编号', student_name varchar(32) comment '学员名称', student_gender char(1) comment '学员性别', student_birthday date comment '学员生日' )comment '学生表';
Ps：如果构建完之后，通过Navicat查看注释信息时，发现有乱码，需要设置系统的一些内容，如果没乱码，你不需要做任何事情！！！！
1、打开cmd，输入chcp，如果是936，那就开始设置当前操作系统的语言
2、更改系统区域设置
3、修改完毕后，需要重启电脑才可以生效！！ 可以在重启后，再次打开cmd，查看chcp的返回内容
7.3 表的其他操作（了解）
语法：alter table 表名 操作
7.3.1 添加一个列
语法：alter table 表名 add 列名 类型;
给student表追加一个student_phone的列。类型是varchar(16）
# 添加一列 alter table student add student_phone varchar(16);
7.3.2 修改表中的列
语法：alter table 表名 modify 列名 类型;
将student表中的student_phone的列中的varchar长度设置为11。
# 修改一列 alter table student modify student_phone varchar(11) comment '学生手机号';
7.3.3 删除表中的列
语法：alter table 表名 drop 列名;
将前面用navicat随便添加的列删掉。删除ssss这个列。
# 删除一列 alter table student drop ssss;
7.3.4 修改表名
语法：alter table 表名 rename 新表名;
将student表修改为table_student
# 修改表名 alter table student rename table_student;
7.3.5 修改列名和类型
语法：alter table 表名 change 旧列名 新列明 类型;
将table_student表中的student_gender修改为student_sex，并且类型指定为tinyint
# 修改列的名称和类型 alter table table_student change student_gender student_sex tinyint;
7.3.6 清空表数据
这个操作的语法跟前面完全不同。单独的语法。
这个清空表不是单独的删除数据，而是直接将表摧毁掉，再重新创建一张一模一样的表。
语法：truncate 表名;
直接将table_student中的数据直接干掉。
# 摧毁表,重新创建 truncate table_student;
7.3.7 删除表
这个跟前面的操作也不一样。
语法：drop table 表名;
删除table_student表。
drop table table_student;
7.4 常见问题
八、约束
问题：往一张表中插入完全一致的数据，可行不。
从功能的维度来说，完全没有问题。但是从业务方向来考虑，两个完全一模一样的数据没有什么意义。
从行数据的维度来考虑，需要保证 实体完整性约束 。需要确保每行数据不重复，数据唯一。
8.1 主键约束（重要）
primary key 主键的约束，标识表中的一行数据，当前指定的主键列的 值不可以重复，并且不能为NULL
主键在表中只能值有一个，指定多个主键，就会报错。
create table student( student_id bigint primary key comment '学员编号' , student_name varchar(32) comment '学员名称', student_gender char(1) comment '学员性别', student_birthday date comment '学员生日' )comment '学生表'; insert into student values (1,'李四','男','2001-11-11');Ps：一般咱们推荐给表添加一个主键约束，一般情况下，可以针对有意义数据设置主键约束，也可以单独搞一个列来作为主键（推荐是有序自增的最好）
这里推荐主键最好是自增的，这样后期在做一些范围查询的时候效率会更好。
现在student_id是主键，每次咱们需要自己指定1，2，3，4，5，6，7…………很麻烦。
可以给主键设置一个自动增长的效果。
auto_increment 放在约束后面即可，就配合主键使用，其他列不用。
create table student( student_id bigint primary key auto_increment comment '学员编号' , student_name varchar(32) comment '学员名称', student_gender char(1) comment '学员性别', student_birthday date comment '学员生日', student_phone varchar(11) unique comment '学员手机号' )comment '学生表'; insert into student values (NULL,'李四','男','2001-11-12','18888888855');
8.2 唯一约束
unique 唯一约束。标识表中的一行数据，当前指定的唯一约束的列的 值不允许重复，可以为NULL
唯一约束可以在一张表中指定多个列。
允许为NULL，并且，多个NULL可以同时存在。
create table student( student_id bigint primary key comment '学员编号' , student_name varchar(32) comment '学员名称', student_gender char(1) comment '学员性别', student_birthday date comment '学员生日', student_phone varchar(11) unique comment '学员手机号' )comment '学生表'; insert into student values (1,'李四','男','2001-11-12','18888888888');
域完整性约束：限制列的数据完整性。
8.3 非空约束
not null ，在列的后面指定上这个约束即可。
给学生生日指定为非空！
create table student( student_id bigint primary key auto_increment comment '学员编号' , student_name varchar(32) comment '学员名称', student_gender char(1) comment '学员性别', student_birthday date not null comment '学员生日', student_phone varchar(11) unique comment '学员手机号' )comment '学生表'; insert into student values (NULL,'王五','男',NULL,'18888888434');
8.4 默认值约束
default 默认值 ，当前列如果没有指定任何的值，会采用这个默认值填充。
给学员名称列设置默认值，如果没指定姓名就叫 ‘张三’。
测试默认值时，需要采用下述方式使用insert语句，跳过student_name字段的赋值。
create table student( student_id bigint primary key auto_increment comment '学员编号' , student_name varchar(32) default '张三' comment '学员名称', student_gender char(1) comment '学员性别', student_birthday date not null comment '学员生日', student_phone varchar(11) unique comment '学员手机号' )comment '学生表'; drop table student; insert into student (student_gender,student_birthday,student_phone) values ('男','2011-11-11','18888888433');
8.5 检查约束
check (检查约束要求) ，这个是MySQL8.x提供的约束功能，约束某一个列的值满足一定的条件要求
给student表中的性别字段，指定一个检查约束，要求添加的值只能是男、女。不允许添加其他内容。
-- 是单独指定约束信息 create table student( student_id bigint primary key auto_increment comment '学员编号' , student_name varchar(32) default '张三' comment '学员名称', student_gender char(1) comment '学员性别', student_birthday date not null comment '学员生日', student_phone varchar(11) unique comment '学员手机号', constraint student_gender_check check (student_gender = '男' or student_gender = '女') )comment '学生表'; -- 在字段后直接编写。 create table student( student_id bigint primary key auto_increment comment '学员编号' , student_name varchar(32) default '张三' comment '学员名称', student_gender char(1) check (student_gender = '男' or student_gender = '女') comment '学员性别', student_birthday date not null comment '学员生日', student_phone varchar(11) unique comment '学员手机号' )comment '学生表'; -- 测试 insert into student (student_gender,student_birthday,student_phone) values ('弯','2011-11-11','18888888435');
8.6 数值的约束
UNSIGNED，数值的约束，保证这个数值必须是正数，不允许存储负数。
同时取值范围还可以增加接近一倍。
比如tinyint，存储范围是-128到127，如果追加上了UNSIGNED，就可以标识0到255。
# 添加unsigned约束后，数值就不允许存储负数了。 create table yyy( id tinyint unsigned ); insert into yyy values (-1);
zerofill，根据指定的数值长度，如果当前数值的长度不满足指定的长度，会自动在前面填充0。
可以在数值类型后面追加这个约束。
8.7 常见错误
九、事务（重要）
9.1 转账操作
生活当众，转账是从一方扣钱，另一方加钱，采用数据库来模拟一下这个操作。
优先准备好转账的环境。
# 创建一个账户表 create table account( id bigint primary key auto_increment comment '账户标识', name varchar(16) not null comment '账户名称', money bigint not null comment '账户余额' )comment '账户表'; # 添加两个账户信息，张三、李四，一人1000大洋 insert into account (name,money) values ('张三',1000),('李四',1000); # 模拟张三给李四转500大洋 update account set money = money - 500 where id = 1; update account set money = money + 500 where id = 2;转账分为两个操作，需要先给一个用户扣钱，再给一个用户加钱。
第一个扣钱的操作成功了，但是因为一些其他的原因，比如服务器断点，或者是一些异常情况，导致第二个加钱的SQL执行失败。整个转账的业务其实是失败的，但是第一个的钱扣了，没了。。。。。
正常应当是，这 两个操作要么都执行成功，要么都执行失败。
上述的转账问题，咱们就可以基于事务来解决。
事务可以看做是一个最小的执行单位，一个事务可以由一条或者多条SQL语句组成。 一个事务操作，所有的SQL语句要么都执行成功，要么都执行失败。
9.2 事务操作
首先MySQL他模式就是开启事务的，但是这个事务每执行一个DML语句，都会自动的提交结束。
通过几个操作来实现自己对事务的控制。
开启事务：
1、因为MySQL默认自动结束事务的，咱们可以自己关闭这种自动结束的操作。
2、也可以通过单独的指令，来指定事务开始和结束的位置。
set AutoCommit = 0; -- 默认情况，值是1，代表每次自动结束事务，设置为0，代表需要咱们手动的结束事务。 begin; -- 开启手动结束事务操作，需要在执行完SQL语句后，自己去执行结束事务的指令 start transaction; -- 跟begin是一样的。。。。结束事务：
1、如果开启事务的所有SQL语句，执行没有问题，可以直接提交事务。
2、如果开启事务后执行的SQL语句存在问题，可以执行回滚事务操作。
commit; -- 提交事务，事务中的操作都会落到磁盘当众 rollback; -- 回滚事务，事务中的操作全部都会恢复到事务开启之前的状态。将前面的转账操作，基于事务控制，解决前面的问题。
# 模拟张三给李四转500大洋 set AutoCommit = 1; -- 关闭自动结束事务，需要遇到commit或者rollback才会结束这个事务 begin; -- 手动开启了一个事务，需要遇到commit或者rollback才会结束这个事务。 start transaction; -- 也是手动开启一个事务。 update account set money = money - 500 where id = 1; update account set money = money + 500 where id = 2; commit; rollback; -- 事务结束有两种方式。commit，提交。 rollback，回滚。
9.3 事务的基本实现原理
9.4 事务的特性
- 原子性（Atomicity）
事务是一个最小的执行单位，一次事务的操作要么都成功，要么都失败。- 一致性（Consistency）
表示一个事务内有一个操作失败时，所有的更改过的数据都必须回滚到修改前的状态。- 隔离性（Isolation）
事务查看数据操作时数据所处的状态，要么是另一个并发事务修改之前的状态，要么是另一个事务修改它之后的状态，事务不会查看中间状态的数据。- 持久性（Durablility）
事务正常提交后，会将数据落到磁盘中，影响是永久了。
9.5 事务并发问题
- 脏读：一个事务中，读取到了另一个事务未提交的数据。（这个问题必须要解决）
- 不可重复读：一个事务中，多次查询同一个数据，结果不一致。原因是其他事务中对这个数据修改了，并且提交事务了。
- 幻读：一个事务中，多次查询同一个数据，结果不一致。原因是其他事务中对某个数据进行了增删，并且提交事务了。
为了复现上述的问题，优先将MySQL的事务隔离级别调低
-- 为了查看到所有问题的效果，这里优先将事务的隔离级别设置为最低等级。READ-UNCOMMITTED。
set global transaction_isolation = 'READ-UNCOMMITTED';
-- 因为设置的是全局的事务隔离级别，设置完毕后，关闭连接，重新打开，才会生效。
select @@transaction_isolation;
复现脏读效果：
复现不可重复读效果：
复现幻读的效果：
9.6 事务的隔离级别
事务的隔离级别就是用来解决前面9.5聊到的事务并发的三个问题的。
- READ-UNCOMMITTED（读未提交）：可以读取到未提交事务的数据。
- （一个问题都不能解决）
- READ-COMMITTED（读已提交）：可以读取到已经提交事务的数据。
- （只能解决脏读）
- Oracle默认隔离级别是READ-COMMITTED
- REPEATABLE-READ（可重复读）：会让一次事务多次查询同一数据结果一致（修改导致）。
- （可以解决脏读和不可重读）
- MySQL默认隔离级别是可重复读REPEATABLE-READ
- SERIALIZABLE（串行化）：上锁，所有问题都能解决。
- （可以解决所有问题）
为了解决上面说道的各种问题，这里可以设置事务的隔离级别，然后查看效果
查看事务的隔离级别的方式：
select @@transaction_isolation;设置事务的隔离级别，可以设置全局的，也可以针对当前连接设置。
-- 全局的事务隔离级别设置。（设置完，需要关闭连接，重新打开） set global transaction_isolation = 'SERIALIZABLE'; -- 当前会话的事务隔离级别设置。 set session transaction_isolation = 'SERIALIZABLE';
十、权限控制DCL操作（了解）
DCL就是Data Control Language，一般就是对于用户的权限做一些授权操作之类的内容。
直接构建用户，基于用户操作对应库表的权限。
10.1 用户的操作
创建用户： create user 用户名@IP地址 identified by 密码;
这里的IP地址，是指定Host列，也就是当前用户可以基于哪个IP地址连接当前MySQL
创建一个用户，用户名是zheng，密码是zheng，采用默认的IP，%。
#创建一个用户，用户名是zheng，密码是zheng。 create user 'zheng' identified by 'zheng'; create user 'zhang'@'%' identified by 'zhang';
删除用户：drop user 用户名;
删除上面的用户zhang。
# 删除上面的用户zhang。 drop user 'zhang';
Ps：创建完毕的用户，密码插件默认是caching_sha2_password，正常黑窗口是可以连接的。我提供的Navicat版本比较低，所以无法连接，需要将密码插件修改为mysql_native_password。
10.2 授权
给用户赋予操作指定库和表的权限。
语法：grant all on 库名.表名 to 用户名;
all代表赋予这个库和表的所有操作权限。
库名和表名如果想赋予全部的，使用*代替。
赋予zheng用户，可以操作test库下所有表的所有操作权限。
# 赋予zheng用户，可以操作test库下所有表的所有操作权限。 grant all on test.* to 'zheng';发现赋予权限后，依然无法连接，原因是zheng用户默认的密码插件是caching_sha2_password。咱们的Navicat版本低，无法识别，通过alter语句修改一下插件信息
alter user 'zheng'@'%' identified with mysql_native_password by 'zheng';正常连接后，可以看到具体的赋予权限的库表信息。
10.3 撤销授权
将前面赋予的权限撤销掉。
语法：revoke all on 库名.表名 from 用户名;
撤销掉对zheng用户赋予的test库中所有表的所有权限
# 撤销掉对zheng用户赋予的test库中所有表的所有权限 revoke all on test.* from 'zheng';
十一、视图（了解）
11.1 视图介绍
视图（View），是一张虚拟表，在本地磁盘是没有存储的。
视图是从一张表或者多张表中查询出来的结果，作用和真实的表是一样的，包含一系列带有行和列的数据。
在视图中，用户可以通过select语句查询视图里的数据，也可以基于insert，update，delete按修改视图的数据，但是修改视图的本质就是在修改原始表。一般不允许做修改视图的操作。
视图的核心作用是可以将一些非常复杂的查询逻辑封装到一个视图里，同时也可以将一些敏感数据规避。
视图的本质，就是一个SQL。
11.2 视图语法
构建视图语法：create view 视图名 as (查询语句);
视图的构建很简单，只要有对应的查询语句即可。
但是记住，视图无法提升你的查询效率，视图的本质就是一个查询语句。
视图的出现是为了刚方便咱们的操作。。
11.3 视图构建
将之前玩的employee表中薪资大于5000的信息数据封装为一个视图
薪资大于5000，就是where salary > 5000。
查询SQL搞定，直接封装视图即可。
# 将之前玩的employee表中薪资大于5000的信息数据封装为一个视图 select * from t_employees where salary > 5000; # 构建为视图 create view v_emp_salary_gt_fivethousand as (select * from t_employees where salary > 5000);
11.4 视图使用
视图的使用和正常操作表是一样的。
前面构建好的v_emp_salary_gt_fivethousand就可以直接查询
# 查询视图 select first_name,last_name from v_emp_salary_gt_fivethousand;视图是可以修改的，但是修改的不是视图，修改的是视图映射的原表数据。
Ps：能改，但是别改。记住，视图就是用来查询的，别用于写操作。
11.5 删除视图
删除视图跟删除表一样。
语法：drop view 视图名;
# 删除视图 drop view v_emp_salary_gt_fivethousand;
十二、同义词（了解）
MySQL不支持同义词synonym这个关键字。
Oracle中支持synonym的构建。
在Oracle中是给对应的表，视图，存储过程起个别名，访问起来更方便一些。
因为在MySQL8.0中，提供了一个构建同义词数据库的存储函数。
将一些名字比较恶心的数据库，起个别名，用于做一些查询操作。
语法：call sys.create_synonym_db(‘原库’,‘同义词库’);
# 给test库搞一个同义词 call sys.create_synonym_db('test','t');构建完毕之后，会出现一个t数据库，在t数据库中，会将test库中的所有表和视图，全部生成为视图存户到t库中。
构建完毕同义词库中的视图，如果你做了修改和删除数据的操作，那修改和删除的是原库中的数据。
不过删除视图无所谓，视图删除了，和原表没关系。
十三、索引
索引可以提升查询是的效率。（合理的运用）
索引是给表中具体的列追加一个索引。
索引并不是什么列都适合添加的。
- 一般需要对经常被查询的列添加索引，而且这个列的值不能过于重复。
- 如果列的长度特别大的就不太适合添加索引。
查看表中的索引信息。
语法：show index from 表名;
通过上述语句，查看一下t_employees表中的索引信息
添加索引信息
语法：create [索引类型] index 索引名称 on 表名(列);
给员工表中的手机号字段，追加上一个普通索引。
# 给员工表中的手机号字段，追加上一个普通索引。 create index index_emp_phone on t_employees(phone_number);
给员工表中的邮箱字段追加一个唯一索引。
# 给员工表中的邮箱字段追加一个唯一索引。 create unique index index_unique_emp_email on t_employees(email);
经常有一种操作，在查询某张表时，经常用这种条件where a = ？ and b = ？ ……
这种查询可以做一个优化，可以将a列和b列创建一个联合（复合、多列）索引。
语法：create [索引类型] index 索引名称 on 表名(列1,列2);
这种联合索引的查询效率，比起单独给列1和列2分别构建索引的查询效率要快。
将员工表中的first_name和last_name组合一个联合索引。
# 将员工表中的first_name和last_name组合一个联合索引。 create index index_emp_name on t_employees(first_name,last_name);
删除索引操作。
语法：drop index 索引名称 on 表;
将员工表中的email的唯一索引删除掉。
# 将员工表中的email的唯一索引删除掉。 drop index index_unique_emp_email on t_employees;
创建表的时候，也可以指定索引信息（了解一小下）
语法：
create table 表名( 列1 数据类型…………, index(列名) # 构建索引的方式 );
十四、综合练习
后期针对MySQL的操作，其实90%以上都是做Select查询操作。
现在就用Oracle提供的三张经典表来做练习
emp（员工表）、dept（部门表）、salgrade（工资等级表）
14.1 外键约束的补充（了解）
在做真正的练习之前，优先搞定一下之前一致没聊的外键约束。
外键其实就是做表与表之前关联的一个约束。
现在有员工表，也有部门表。
- 一个部门下可以有多个员工。
- 一个员工只能属于一个部门。
就需要在一得一方，也就是员工表中，有一个外键，也就是deptno这个列，他的作用是和dept部门表做一个关联。当然，也可以给这个员工表中的deptno追加一个外键约束（不推荐）。
也给emp额外追加上外键约束。
语法：alter table 表名1 add constraint 外键名称 foreign key (列1) references 表名2(列2);
# 给员工表追加上deptno的外键约束 alter table emp add constraint fk_emp_dept foreign key (deptno) references dept(deptno);
14.2 查询操作练习
14.2.1 查询每个部门最高薪水的人员名称
需要查询的内容都处于员工表中。
需要查询员工的名称，部门，薪资。
首先先完成查询每个部门的最高薪资。 需要利用聚合函数的max，并且对部门编号做一个分组。
然后将部门最高信息查询返回的虚拟表与emp表做表连接，查询出最终结果
# 首先先完成查询每个部门的最高薪资。 需要利用聚合函数的max，并且对部门编号做一个分组。 select deptno,max(sal) as maxsal from emp group by deptno; # 将前面查询到的部门最高薪资，和当前的emp表做一个表连接操作，查询出每个部门薪资最高的员工名称 select e.ename,e.deptno,m.maxsal from emp e inner join (select deptno,max(sal) as maxsal from emp group by deptno) m on e.deptno = m.deptno and e.sal = m.maxsal;
14.2.2 查询哪些员工薪资在部门的平均薪资之上
首先，依然还是在员工表中查询。
需要查询员工名称，员工薪资，部门编号，部门平均薪资
首先需要先将各个部门的平均薪资查询出来，根据聚合函数avg以及对部门编号分组查询。
然后将查询的平均薪资和员工表做一个关联。
-- 查询哪些员工薪资在部门的平均薪资之上 -- 需要查询员工名称，员工薪资，部门编号，部门平均薪资 # 首先需要先将各个部门的平均薪资查询出来，根据聚合函数avg以及对部门编号分组查询。 select deptno,avg(sal) avgsal from emp group by deptno; # 将员工表和上述查询部门平均薪资的表关联到一起，条件是部门编号一致 and 员工薪资大于平均薪资 select e.ename,e.sal,e.deptno,a.avgsal from emp e inner join (select deptno,avg(sal) avgsal from emp group by deptno) a on e.deptno = a.deptno and e.sal > a.avgsal;
14.2.3 查询每个部门的平均薪资等级
首先这里需要查询员工表和薪资等级表。
需要查询部门编号以及部门的平均薪资等级
首先需要查询所有员工的薪资等级是多少。
直接将上述的查询结果作为一个from的虚拟表，直接对部门做分组，针对薪资等级做avg平均值即可
-- 查询每个部门的平均薪资等级 -- 需要查询部门编号以及部门的平均薪资等级 # 首先需要查询所有员工的薪资等级是多少。 select e.deptno,s.grade from emp e inner join salgrade s on e.sal between s.losal and hisal # 可以将上述的查询结果，基于deptno做分组，然后将grade做一个avg算平均数。 select es.deptno,avg(es.grade) from (select e.deptno,s.grade from emp e inner join salgrade s on e.sal between s.losal and hisal) es group by deptno;
14.2.4 查询平均薪资最高的部门名称
首先需要查询员工表和部门表
需要查询出来部门的名称和平均薪资
首先平均薪资单独的emp表就可以查询出来，再基于排序和limit，就可以只查询出平均薪资最高的部门
然后将上述查询结果的内容，和dept表做表连接，查询出薪资最高的部门信息
-- 查询平均薪资最高的部门名称 -- 需要查询出来部门的名称和平均薪资 # 首先平均薪资单独的emp表就可以查询出来，再基于排序和limit，就可以只查询出平均薪资最高的部门 select deptno,avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1 # 基于上述查询的结果，和dept表做一个表连接操作 select d.dname,da.avgsal from dept d inner join (select deptno,avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1) da on d.deptno = da.deptno;上述SQL可以查询出emp，dept表中的正确结构，但是如果多个部门的平均薪资一致，并且都是最高薪资，那上述查询就有问题了。
上述的limit方式不太合适了。
第一步：查询出平均薪资的最大值。
第二步：查询出和平均薪资最大值一致的部门编号。
第三部：基于部门编号查询出部门的名称。
# 上述的limit看着不错，但是存在问题。 # 第一步：查询出平均薪资的最大值。 select avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1; # 第二步：查询出和平均薪资最大值一致的部门编号。 select deptno,avg(sal) as maxavgsal from emp group by deptno having maxavgsal = (select avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1); # 第三部：基于部门编号查询出部门的名称。 select d.dname,avg(sal) as maxavgsal from emp e inner join dept d on e.deptno = d.deptno group by d.dname having maxavgsal = (select avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1);
14.2.5 查询薪水比自己领导还高的员工信息。
首先需要查询emp表，但是需要两张emp一个作为普通员工信息，一个作为领导信息
查询员工名称和员工薪资，领导名称和领导薪资
直接将两张emp表做连接，连接的条件是员工表中的mgr，与领导表中的empno作为条件。
-- 查询薪水比自己领导还高的员工信息。 -- 查询员工名称和员工薪资，领导名称和领导薪资 # 直接将两张emp表做连接，连接的条件是员工表中的mgr，与领导表中的empno作为条件。 select e.ename,e.sal,me.ename,me.sal from emp e inner join emp me on e.mgr = me.empno where e.sal > me.sal;
14.2.6 查询比普通员工的最高薪资还要高的领导名称
首先查询的依然是emp表。
查询出领导的名称和薪资即可。
要先分出来哪些是普通员工，哪些是领导。 领导的empno都在mgr字段上。
分成三步查询。
1、先查询出所有的领导的empno，只需要查询mgr字段即可，做个去重。
2、再基于上面查询出来的领导的empno，筛选出普通员工，查询出普通员工中的最高薪资。
3、再查询领导信息，薪资大于普通员工的最高薪资的领导信息查询出来。
-- 查询比普通员工的最高薪资还要高的领导名称 -- 1、先查询出所有的领导的empno，只需要查询mgr字段即可，做个去重。 select distinct mgr from emp where mgr is not null; -- 2、再基于上面查询出来的领导的empno，筛选出普通员工，查询出普通员工中的最高薪资。 select max(sal) as maxsal from emp where empno not in (select distinct mgr from emp where mgr is not null); -- 3、再查询领导信息，薪资大于普通员工的最高薪资的领导信息查询出来。 select ename,sal from emp where empno in (select distinct mgr from emp where mgr is not null) and sal > (select max(sal) as maxsal from emp where empno not in (select distinct mgr from emp where mgr is not null));
14.2.7 查询每个薪资等级有多少个员工
首先必然需要查询员工表以及薪资等级表的关联操作。
查询出薪资等级和对应的员工个数即可。
分成两步操作：
1、先基于emp和salgrade表查询出每位员工的薪资等级
2、在上述的基础上，再根据grade字段进行分组，查询count即可。
-- 查询每个薪资等级有多少个员工 # 1、先基于emp和salgrade表查询出每位员工的薪资等级 select e.ename,s.grade from emp e inner join salgrade s on e.sal between s.losal and s.hisal; # 2、在上述的基础上，再根据grade字段进行分组，查询count即可。 select s.grade,count(1) from emp e inner join salgrade s on e.sal between s.losal and s.hisal group by s.grade order by s.grade;
14.2.8 查询出入职时间早于其领导的员工信息和部门信息
首先需要查询emp表两张，同时还要查询出对应的部门信息，还要关联部门表。
需要查询出员工名称，部门名称，领导名称，领导部门
分成两步操作：
1、查询出员工及其领导信息，并且追加上一个判断，员工的入职时间，要早于领导的入职时间
2、再上述的基础上，再额外关联两张部门表，查询出对应的部门名称即可。
-- 查询出入职时间早于其领导的员工信息 # 需要查询出员工名称，部门名称，领导名称，领导部门 # 1、查询出员工及其领导信息，并且追加上一个判断，员工的入职时间，要早于领导的入职时间 select e.ename 员工名称,m.ename 领导名称 from emp e inner join emp m on e.mgr = m.empno where e.hiredate < m.hiredate; # 2、再上述的基础上，再额外关联两张部门表，查询出对应的部门名称即可。 select e.ename 员工名称,d.dname 员工部门,m.ename 领导名称 ,md.dname 领导部门 from emp e inner join emp m on e.mgr = m.empno inner join dept d on e.deptno = d.deptno inner join dept md on m.deptno = md.deptno where e.hiredate < m.hiredate;
14.2.9 查询出至少有5位员工的部门信息
首先需要查询emp和dept表，需要两张表做一个关联。
需要查询部门的编号，部门的名称，部门的员工人数。
分成两步走：
1、关联员工和部门表，查询出部门信息和部门的员工人数。
2、在上述的基础上，筛选出员工人数大于5个的部门信息。
-- 查询出至少有5位员工的部门信息 # 需要查询部门的编号，部门的名称，部门的员工人数。 # 分成两步走： # 1、关联员工和部门表，查询出部门信息和部门的员工人数。 select d.deptno,d.dname,count(1) from emp e inner join dept d on e.deptno = d.deptno group by d.deptno; # 2、在上述的基础上，筛选出员工人数大于5个的部门信息。 select d.deptno,d.dname,count(1) as empcount from emp e inner join dept d on e.deptno = d.deptno group by d.deptno having empcount >= 5
14.2.10 查询出薪资高于公司薪资的平均水平的员工名称，所在部门，上级领导名称，员工名称的薪资等级
首先这里需要所有表都参与。emp表查询出员工和领导的信息，所在部门需要dept表，员工薪资水平需要salgrade表。
需要查询员工名称，所在部门，上级领导名称，员工名称的薪资水平
分成四步走：
1、查询出员工信息和所在部门的信息。
2、在上述的基础上筛选出薪资高于公司平均水平的员工。
3、在上述的基础上再追加查询员工的领导名称。
4、在上述的基础上，再追加查询员工的薪资水平。
-- 查询出薪资高于公司薪资的平均水平的员工名称，所在部门，上级领导名称，员工名称的薪资水平 # 需要查询员工名称，所在部门，上级领导名称，员工名称的薪资水平 # 分成四步走： # 1、查询出员工信息和所在部门的信息。 select e.ename,d.dname from emp e inner join dept d on e.deptno = d.deptno; # 2、在上述的基础上筛选出薪资高于公司平均水平的员工。 select e.ename,d.dname from emp e inner join dept d on e.deptno = d.deptno where e.sal > (select avg(sal) from emp); # 3、在上述的基础上再追加查询员工的领导名称。 select e.ename 员工名称,d.dname 员工部门, m.ename 领导名称 from emp e inner join dept d on e.deptno = d.deptno left join emp m on e.mgr = m.empno where e.sal > (select avg(sal) from emp); # 4、在上述的基础上，再追加查询员工的薪资水平。 select e.ename 员工名称,d.dname 员工部门, m.ename 领导名称,s.grade 员工薪资等级 from emp e inner join dept d on e.deptno = d.deptno left join emp m on e.mgr = m.empno inner join salgrade s on e.sal between s.losal and s.hisal where e.sal > (select avg(sal) from emp);
14.2.11 查询与’SCOTT’从事相同工作的员工名称和部门名称
首先需要查询emp和dept的关联查询，同时需要子查询来找到’SCOTT’的工作作为条件筛选的值
需要查询员工名称和部门名称
分成两步走
1、正常的查询出员工的名称和所在的部门
2、基于’SCOTT’从事的工作筛选出对应的员工信息
-- 查询与'SCOTT'从事相同工作的员工名称和部门名称
# 需要查询员工名称和部门名称
# 分成两步走
# 1、正常的查询出员工的名称和所在的部门
select
e.ename,d.dname
from
emp e inner join dept d on e.deptno = d.deptno;
# 2、基于'SCOTT'从事的工作筛选出对应的员工信息
select
e.ename,d.dname
from
emp e inner join dept d on e.deptno = d.deptno
where
e.job = (select job from emp where ename = 'SCOTT') and ename != 'SCOTT';
14.2.12 查询没有员工的部门信息（exists）
首先，查询时其实依然是emp和dept表之间的一个联系。需要查询出员工表中不存在的deptno，但是dept表存在的部门信息。
exists是可以追加到条件里的。
语法：where exists (查询)
上述语法中，如果 (查询) 有结果，相当于条件满足，会返回当前数据，如果 (查询) 没有结果，相当于不满足条件，不会返回当前结果。
在exists前，可以追加not，效果就是取反的效果了。
可以在查询部门时，基于emp表的查询结合exists来做到，如果查询有员工信息，返回true，不返回当前结果，如果返回false，希望返回结果。
select d.deptno,d.dname,d.loc from dept d where not exists (select empno from emp e where e.deptno = d.deptno);
14.2.13 查询部门的平均薪资，以2000作为点，返回’大于2000’或者’小于2000’或者’等于2000’的结果
首先查询平均薪资，必然会用到员工表和部门表两个信息。
查询部门编号，以及部门的薪资导致是大于，小于，等于2000的结果。
需要使用到case when then end这种操作。
分成两步操作
1、查询每个部门的平均薪资。
2、基于上述查询，将平均薪资的返回结果替换为需求中的大于，小于，等于2000的结果。
-- 查询部门的平均薪资，以2000作为点，返回'大于2000'或者'小于2000'或者'等于2000'的结果 # 查询部门编号，以及部门的薪资导致是大于，小于，等于2000的结果。 # 分成两步操作 # 1、查询每个部门的平均薪资。 select d.deptno, avg(e.sal) from emp e inner join dept d on e.deptno = d.deptno group by d.deptno; # 2、基于上述查询，将平均薪资的返回结果替换为需求中的大于，小于，等于2000的结果。 select d.deptno, avg(e.sal) 平均薪资, case when avg(e.sal) = 2000 then '等于2000' when avg(e.sal) > 2000 then '大于2000' else '小于2000' end as 是否大于2000 from emp e inner join dept d on e.deptno = d.deptno group by d.deptno;
14.3 常见错误
扫一扫
676
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
