SQL学习笔记

最新推荐文章于 2023-05-05 15:02:54 发布

Kopparberg4%

最新推荐文章于 2023-05-05 15:02:54 发布

阅读量326

点赞数

文章标签： sql mysql

本文链接：https://blog.csdn.net/weixin_58429642/article/details/120375504

版权

学习内容来自：https://www.bilibili.com/video/BV12b411K7Zu?p=28&spm_id_from=pageDriver

文章目录

数据库
- 1. 数据库的概念
- 2. 数据库的特点
MySQL
DQL语言的学习
DML语言的学习
DDL语言的学习
TCL语言的学习

数据库

1. 数据库的概念

DB
数据库（Database）：存储数据的“仓库”，保存了一系列有组织的数据。
DBMS
数据库管理系统（数据库软件/数据库产品）（Database Management System）。数据库是通过DBMS创建和操作的容器。
常见的数据库管理系统（分两类）：
- 基于客户机 —— 服务器的DBMS
  - MySQL、
  - Oracle（收费且贵）、
  - SqlServer（微软，只能用在windows系统）等
- 基于共享文件系统的DBMS（Access）
- 不知道：
  - DB2（IBM公司，适合于处理海量数据）、
SQL
结构化查询语言（Structure Query Language）专门用来与DBMS通信的语言。

2. 数据库的特点

1. 将数据放到表中，表再放到库中
2. 一个数据库中可以由多个表，每个表都有一个名字，用来标识自己。**表名具有唯一性**。
3. 表具有一些特性，定义了数据在表中如何存储，= 属性。
4. 表由列组成（字段）。列 = 属性。

表中数据按行存储。行 = 对象。

MySQL

1. MySQL的安装和配置

https://baijiahao.baidu.com/s?id=1710300910479113488&wfr=spider&for=pc

之后在环境变量里加入安装的bin目录。默认安装在了C盘，添加的路径为“C:\Program Files\MySQL\MySQL Server 8.0\bin”。

2. MySQL服务的启动和停止

CMD以管理员身份运行

启动：net start 服务名
停止：net stop 服务名
比如，net start mysql80

3. MySQL服务的登录与退出

法一：MySQL自带的客户端
法二：CMD
- 登录：mysql -h 主机名 -P 端口号 -u 用户名 -p密码
  比如，mysql -h localhost -P3306 -u root -p，回车输密码
  简单版：mysql -u root -p
- 退出：exit或ctrl+c

4. MySQL的常见命令

查看当前所有的数据库：show databases;

前三个不能动。
查看当前所在库：select database();
打开指定的库：use 库名;
查看当前库的所有表：show tables;
查看其它库的所有表：show tables from 库名;
此时所在数据库不变
创建表：
create table 表名(
列名列类型,
列名列类型
);
比如，
create table stuinfo(
id int,
name varchar(20));
注：varchar是字符串
查看表结构：desc 表名;
查看服务器版本：
- 登录到mysql服务端：select version();
- 未登录到mysql服务端：mysql --version

5. MySQL语法规范

不区分大小写，但建议关键字大写，表名、列名小写
每条命令最好用分号结尾
每条命令根据需要可以进行缩进或换行。建议，例如：

SELECT
*
FROM
表名
注释：

单行注释：#注释文字或-- 注释文字
多行注释：/* 注释文字 */

6. SQLyog常用快捷键

执行选中的代码：选中代码按F9；
标准化格式：按F12；

DQL语言的学习

D: Data, Q: Query, L: Language

数据查询语言，涉及select。

1. 基础查询

语法：

select 查询列表 from 表名;

查询列表可以是：表中的字段、常量值、表达式、函数。
查询的结果是一个虚拟的表格。

每次做操作前，先打开制定的库：

USE `myemployees`;
#有着重号是因为直接双击得到的值，需要区分关键字和字段的时候可以加上着重号。
USE myemployees;

1.1 查询列表的四种类型

1. 查询字段

a. 查询表中的单个字段

SELECT last_name FROM employees;

b. 查询表中的多个字段

SELECT last_name, salary, email FROM employees;
SELECT 
    `first_name`,
    `last_name`,
    `email` 
FROM
    `employees` ;

c. 查询表中的所有字段

SELECT * FROM employees; #表中顺序不变

2. 查询常量值

SELECT 100;
SELECT 'john';

3. 查询表达式

SELECT 100%98

4. 查询函数

SELECT VERSION();

1.2 起别名

作用：

便于理解；
如果查询的字段有重名的情况，使用别名可以区分

使用as或空格
如果别名有空格，注意加双引号。

SELECT 100%98 AS 结果;
SELECT last_name AS 姓, first_name AS 名 FROM employees;
SELECT last_name 姓, first_name 名 FROM employees;

1.3 去重

# 比如查询员工表中涉及到的所有部门编号
SELECT `department_id` FROM `employees`;
SELECT DISTINCT `department_id` FROM `employees`; #去重部门编号

1.4 +号的作用

只有一个功能：运算符

SELECT 100+90; # 两个操作数都为数值型，则做加法运算。
# 只要其中一方为字符型，则会试图将字符型数值转换为数值型。
SELECT '123'+90; #（转换成功）
SELECT 'john'+90;#（转换失败）
SELECT NULL+10;
/*
如果转换成功，则继续做加法运算；
如果转换失败，则将字符型数值转换成0。
只要其中一方为null，则结果肯定为null。
*/

1.5 concat

# 查询员工名和姓连接成一个字段，并显示为 姓名
# 错误的例子：
SELECT 
    `last_name` + `first_name` 
FROM
    `employees`; #（失败）

SELECT CONCAT('a', 'b', 'c') AS 结果;
SELECT 
    CONCAT(`last_name`, `first_name`) AS 姓名 
FROM
    `employees`;

1.6 判断是否为null

# 如果为null则返回一个值。例子中返回的是0.
SELECT 
    IFNULL(`commission_pct`, 0) AS 奖金率 
FROM
    `employees` ;

2. 条件查询

语法：

SELECT 
    查询列表 
FROM
    表名 
WHERE 筛选条件;

分类：

2.1 按条件表达式筛选

简单条件运算符：> < = != <> >= <=
注意：不是双等号；不等于可以是!=，也可以是<>

# 查询工资>12000的员工信息
select * from employees where salary > 12000;

# 查询部门编号不等于90号的员工名和部门编号
SELECT 
    first_name, department_id
FROM
    employees
WHERE
    department_id != 90;

2.2 按逻辑运算符筛选

逻辑运算符——与或非：&& || ! 推荐使用and or not

# 工资在10000到20000之间的员工名、工资及奖金
SELECT 
    first_name, salary, commission_pct
FROM
    employees
WHERE
    salary>=10000 and salary<=20000;

#查询部门编号不是在90到110之间，或者工资高于15000的员工信息
SELECT 
    *
FROM
    employees
WHERE
    NOT (department_id >= 90 AND department_id <= 110) OR salary > 15000;

2.3 模糊查询

模糊查询
like
between and
in
is null 或 is not null

like
一般和通配符搭配使用

通配符	意义（不区分大小写）
%	任意0个或多个字符
_	任意单个字符

# 查询员工名中包含字符a的员工信息
SELECT 
    *
FROM
    employees
WHERE
    first_name LIKE '%a%';

# 查询员工名中第三个字符为e，第五个字符为a的员工名和工资
SELECT 
    first_name, salary
FROM
    employees
WHERE
    first_name LIKE '__e_a%';

# 查询员工名中第二个字符为_的员工名
# 可以用转义 \，也可以：
SELECT 
    first_name
FROM
    employees
WHERE
    first_name LIKE '_$_%' ESCAPE '$';
# 其中，$可以换成其他字符，表示转义。

between and

等价于>=和<=，即包含临界值。

# 查询员工编号在100到120之间的员工信息
SELECT 
    *
FROM
    employees
WHERE
    employee_id BETWEEN 100 AND 120;

in列表的值类型必须一致
不能使用通配符代表匹配的列表值

# 查询员工的工种编号是IT_PROG、AD_VP中的一个员工名和工种编号
SELECT 
    first_name, job_id
FROM
    employees
WHERE
    job_id IN ('IT_PROT' , 'AD_VP');

is null 或 is not null

# 查询没有奖金的员工名和奖金率
SELECT 
    first_name, commission_pct
FROM
    employees
WHERE
    commission_pct IS NULL;

安全等于：<=>
用的较少。

# 查询没有奖金的员工名和奖金率
SELECT 
    first_name, commission_pct
FROM
    employees
WHERE
    commission_pct <=> NULL;

判断是否安全等于，如果等于则返回True。

3. 排序查询

# 引入：
select * from employees;
# 语法：
select 查询列表
from 表
[where 筛选条件]
order by 排序列表 [asc|desc]
# 特点：
## 默认升序：
SELECT 
    *
FROM
    employees
ORDER BY salary;
## order by 子句中可以支持单个字段、多个字段、表达式、函数、别名
## order by 子句一般是放在查询语句的最后面，limit子句除外

# 案例
## 案例1：查询部门编号>=90的员工信息，按入职时间的先后进行排序
SELECT 
    *
FROM
    employees
WHERE
    department_id >= 90
ORDER BY hiredate ASC;

## 案例2：按年薪的高低显示员工的信息和年薪【按表达式排序】
SELECT 
    *, salary * 12 * (1 + IFNULL(commission_pct, 0)) 年薪
FROM
    employees
ORDER BY salary * 12 * (1 + IFNULL(commission_pct, 0)) DESC;
### 或order by使用别名
SELECT 
    *, salary * 12 * (1 + IFNULL(commission_pct, 0)) 年薪
FROM
    employees
ORDER BY 年薪 DESC;

## 案例3：按姓名的长度显示员工的姓名和工资【按函数排序】
SELECT 
    LENGTH(last_name) 字节长度, last_name, salary
FROM
    employees
ORDER BY LENGTH(last_name) DESC;

## 案例4：查询员工信息，要求先按工资升序，再按员工编号降序【按多个字段排序】
SELECT 
    *
FROM
    employees
ORDER BY salary ASC , employee_id DESC;

4. 常见函数

调用：

select 函数名（实参列表） 【from 表】;

分类：

单行函数，如concat、length、ifnull等
分组函数，做统计使用，又称为统计函数、聚合函数、组函数

4.1 单行函数

字符函数	数学函数	日期函数	其他函数	控制函数
length	round	now	version	if
concat	ceil	curdate	database	case
substr	floor	curtime	user
instr	truncate	year
trim	mod	month
upper		monthname
lower		day
lpad		hour
rpad		minute
replace		second
		str_to_date
		date_format

4.1.1 字符函数

length
获取参数值的字节个数。
一个汉字3个字节。
目前来讲，指字节的只有length，其他是字符。

SELECT LENGTH('join');

concat

SELECT 
    CONCAT(last_name, '_', first_name) 姓名
FROM
    employees;

upper、lower

SELECT UPPER('john');
SELECT LOWER('joHn');
SELECT 
    CONCAT(UPPER(last_name), LOWER(first_name)) 姓名
FROM
    employees;

substr、substring

# 截取从指定索引处后面所有字符（包括该索引）select substr('我想带一人回云深不知处', 7) out_put; #返回：云深不知处# 截取从指定索引处指定字符长度的字符（字符串索引从1开始，选择包含该索引）select substr('我想带一人回云深不知处', 1, 3) out_put; #返回：我想带# 案例：姓名中首字符大写，其他字符小写然后用_拼凑，显示出来SELECT     CONCAT(UPPER(SUBSTR(last_name, 1, 1)),            '_',            LOWER(SUBSTR(last_name, 2))) out_putFROM    employees;

instr
返回子串第一次出现的索引，如果找不到，返回0

select instr('我想带一人回云深不知处', '云深不知处') as out_put; #返回：7

trim
去前后空格或指定字符。
只前后，中间的去不掉。

select trim('     我想带一人回云深不知处    ') as out_put; #返回：我想带一人回云深不知处# select length(trim('     我想带一人回云深不知处    ')) as out_put; #返回：33select trim('a' from 'aaaaaa我想带一人回云深不知处aaaa') as out_put;

lpad
用指定字符实现左填充指定长度

SELECT LPAD('云深不知处', 10, '*') AS out_put;#返回：*****云深不知处# 字符串长度大于指定长度，则会被截断。

rpad
用指定字符实现右填充指定长度

SELECT RPAD('云深不知处', 10, '*') AS out_put;#返回：云深不知处*****

replace
替换
如果有重复，则都替换

select replace('我想带一人回云深不知处', '一人', '阿羡') as out_put;

4.1.2 数学函数

round
四舍五入

SELECT ROUND(1.65); #返回：2SELECT ROUND(-1.65); #返回：-2SELECT ROUND(1.65, 1); #返回：1.7

ceil
向上取整，返回大于等于该参数的最小整数。

SELECT CEIL(1.65); #返回：2
SELECT CEIL(-1.65); #返回：-1

floor
向上取整，返回小于等于该参数的最大整数。

SELECT FLOOR(1.65); #返回：1
SELECT FLOOR(-1.65); #返回：-2

truncate
截断

SELECT TRUNCATE(1.699999, 1); #返回：1.6

mod
取余
mod(a, b): a-a/b*b
mod(-10, -3): -10 - (-10)/(-3) * (-3)

SELECT MOD(10, 3); #返回：1，相当于10%3
SELECT 10 % 3;
# 负数怎么看：看被除数，被除数为负，结果为负。
SELECT MOD(-10, -3); #返回：-1
SELECT MOD(-10, 3); #返回：-1
SELECT MOD(10, -3); #返回：1

4.1.3 日期函数

在这里插入图片描述

now
返回当前系统日期+时间
curdate
返回当前系统日期，不包含时间
curtime
返回当前时间，不包含日期

SELECT NOW();
SELECT CURDATE();
SELECT CURTIME();

可以获取指定的部分，年、月、日、小时、分钟、秒

# 年SELECT YEAR(NOW()) 年;SELECT YEAR('1998-1-1') 年;SELECT     YEAR(hiredate) 年FROM    employees;# 月SELECT MONTH(NOW()) 月;SELECT MONTHNAME(NOW()) 月;

str_to_date
将日期格式的字符转换成指定格式的日期。

SELECT STR_TO_DATE('8-18-1988', '%m-%d-%Y'); #返回：1988-08-18

SELECT 
    *
FROM
    employees
WHERE
    hiredate = '1988-8-18';

SELECT 
    *
FROM
    employees
WHERE
    hiredate = STR_TO_DATE('8-18 1988', '%c-%d %Y');

date_format
将日期转换成字符

SELECT DATE_FORMAT('1988/8/8', '%Y年%m月%d日');#返回：1988年08月08日
SELECT DATE_FORMAT(NOW(), '%y年%月%d日') AS out_put;

# 案例：查询有奖金的员工名和入职日期（xx月/xx日 xx年）
SELECT 
    last_name, DATE_FORMAT(hiredate, '%m月/%d日 %y年') 入职日期
FROM
    employees
WHERE
    commission_pct IS NOT NULL;

4.1.4 其它函数

SELECT VERSION();
SELECT DATABASE();
SELECT USER();

4.1.5 流程控制函数

if 函数
if else 的效果

SELECT IF(10 > 5, '大', '小');
select last_name, commission_pct, if (commission_pct is null, '无奖金', '有奖金') as 备注 from employees;
# 注意：if 前有逗号

case

case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
…
else 要显示的值n或语句n;
end

when后面的判断句相当于==

/*
案例：查询员工的工资，要求：
部门号=30，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
部门号=50，显示的工资为1.3倍
其他部门，显示原工资
*/
select salary 原始工资, department_id, 
case department_id
when 30 then salary*1.1
when 40 then salary*1.2
when 50 then salary*1.3
else salary
end as 新工资
from employees;

多重if
case
when 条件1 then 要显示的值1或语句1;
when 条件2 then 要显示的值2或语句2;
…
else 要显示的值n或语句n
end

when相当于elif

/*案例：查询员工的工资情况如果工资大于20000，显示A级别如果工资大于15000，显示B级别如果工资大于10000，显示C级别否则，显示D级别*/select salary 原始工资, casewhen salary>20000 then 'A'when salary>15000 then 'B'when salary>10000 then 'C'else 'D'end as 工资级别from employees;

4.2 分组函数

用作统计使用，又称为聚合函数或统计函数或组函数

函数：
sum()，avg()，max()，min()，count()

参数类型：

sum和avg一般用于处理数值型，
max，min和count可以处理任何类型，比如数值型，字符型和日期型。

是否忽略Null值：

null加上任何数都是null
都忽略了null值
count计算不为null的个数

可以和distinct搭配实现去重的运算
和分组函数一同查询的字段要求是group by后的字段

SELECT     SUM(salary)FROM    employees;    SELECT     sum(salary) 总和, round(avg(salary), 2) 平均值, max(salary) 最高, min(salary) 最小, count(salary) 计数FROM    employees;SELECT     SUM(DISTINCT salary), SUM(salary)FROM    employees;

4.2.1 count

# 统计行数（*号是统计所有列）
SELECT 
    COUNT(*)
FROM
    employees; # 只要一行中有一个不为null，则该行计入总数

SELECT 
    COUNT(1)
FROM
    employees; # 相当于加入一列都为1，统计1的个数

## 一般用*

5. 分组查询

语法：
select 分组函数, 列（要求出现在group by的后面）
from 表
[where 筛选条件]
[group by 分组的列表]
[order by 子句];

注意：

查询列表必须特殊，要求是分组函数和group by 后出现的字段
注意where，group by等的先后顺序

分组查询中的筛选条件分为两类：

-	数据源	位置	关键字
分组前筛选	原始表	group by子句的前面	where
分组后筛选	分组后的结果集	group by子句的后面	having

分组函数做条件肯定是放在having子句中，比如“最低工资大于5000”。
能用分组前筛选的先用分组前筛选。
group by 子句支持单个字段和多个字段分组（多个字段之间用逗号隔开没有顺序），表达式或函数（用得较少）。
可以添加排序（排序放在整个分组查询的最后）。

5.1 简单的分组查询

# 案例：查询每个工种的最高工资SELECT     MAX(salary), job_idFROM    employeesGROUP BY job_id;# 案例：查询每个位置上的部门个数SELECT     COUNT(*), location_idFROM    departmentsGROUP BY location_id;

5.2 添加分组前的筛选条件

# 案例：查询邮箱中包含a字符的，每个部门的平均工资SELECT     AVG(salary), department_idFROM    employeesWHERE    email LIKE '%a%'GROUP BY department_id;# 案例：查询有奖金的每个领导手下员工的最高工资SELECT     MAX(salary), manager_idFROM    employeesWHERE    commission_pct IS NOT NULLGROUP BY manager_id;

5.3 添加分组后的筛选条件

# 案例：查询哪个部门的员工个数大于2## 思路：## 1.查询哪个部门的员工个数大于2；## 2.根据1的结果进行筛选，查询哪个部门的员工个数大于2SELECT     COUNT(*), department_idFROM    employeesGROUP BY department_idHAVING COUNT(*) > 2;# 案例：查询每个工种有奖金的员工的最高工资大于12000的工种编号和最高工资SELECT     MAX(salary), job_idFROM    employeesWHERE    commission_pct IS NOT NULLGROUP BY job_idHAVING MAX(salary) > 12000;# 案例：查询领导编号大于102的每个领导手下的最低工资大于5000的领导编号是哪个，以及其最低工资SELECT     MIN(salary), manager_idFROM    employeesWHERE    manager_id > 102GROUP BY manager_idHAVING MIN(salary) > 5000;

5.4 按表达式或函数分组

# 案例：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数大于5的有哪些SELECT     COUNT(*), LENGTH(last_name) len_nameFROM    employeesGROUP BY LENGTH(last_name)HAVING COUNT(*) > 5;

5.5 按多个字段分组

# 案例：查询每个部门每个工种的员工的平均工资SELECT     AVG(salary), department_id, job_idFROM    employeesGROUP BY department_id , job_id;

5.6 添加排序

# 案例：查询每个部门每个工种的员工的平均工资，并且按平均工资的高低显示
SELECT 
    AVG(salary) a, department_id, job_id
FROM
    employees
#WHERE department_id IS NOT NULL
GROUP BY department_id , job_id
#HAVING a > 10000
ORDER BY a DESC;

6. 连接查询

又称多表查询，当查询的字段来自于多个表时会用到。

笛卡尔乘积现象：表1 有m行，表2有n行，结果 = m*n行

发生原因：没有有效的连接条件

如何避免：添加有效的连接条件

分类：

按年代分类：
- sql92标准：仅仅支持内连接
- sql99标准【推荐】：除了全外连接都支持（好像oracle可以全外连接）
按功能分类：
- 内连接
  - 等值连接
  - 非等值连接
  - 自连接
- 外连接
  - 左外连接
  - 右外连接
  - 全外连接
- 交叉连接

6.1 sql92标准

6.1.1 等值连接

多表等值连接的结果为多表的交集部分

n表连接，至少需要n-1个连接条件

多表的顺序没有要求

一般需要为表起别名

可以搭配前面介绍的所有子句使用，比如排序、分组、筛选。

SELECT     `name`,    `boyName` FROM    boys,    beauty WHERE beauty.boyfriend_id = boys.id ;# 案例：查询员工名和对应的部门名SELECT     last_name,    department_name FROM    employees,    departments WHERE employees.`department_id` = departments.`department_id` ;

为表起别名

可以提高语句的简洁度
区分多个重名的字段

注意：如果起了别名，则查询的字段就不能使用原来的表名去限定。

SELECT e.`last_name`, e.`job_id`, j.`job_title`FROM `employees` e, `jobs` jWHERE e.`job_id` = j.`job_id`;

可以调换两个表的顺序
可以加筛选

# 案例：查询有奖金的员工名，部门名
SELECT 
    last_name,
    department_name,
    commission_pct 
FROM
    `employees` e,
    departments d 
WHERE e.`department_id` = d.`department_id` 
    AND e.`commission_pct` IS NOT NULL ;
    
# 案例：查询城市名中第二个字符为o的部门名和城市名
SELECT 
    `department_name`,
    `city` 
FROM
    `departments` d,
    `locations` l 
WHERE d.`location_id` = l.`location_id` 
    AND l.`city` LIKE '_o%' ;

可以加分组

# 案例：查询每个城市的部门个数
SELECT 
    COUNT(*) 个数,
    `city` 
FROM
    `locations` l,
    `departments` d 
WHERE l.`location_id` = d.`location_id` 
GROUP BY l.`city` ;

# 案例：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT 
    d.`department_name`,
    d.`manager_id`,
    MIN(e.`salary`) 
FROM
    `departments` d,
    `employees` e 
WHERE d.`department_id` = e.`department_id` 
    AND e.`commission_pct` IS NOT NULL 
GROUP BY d.`department_name`,
    d.`manager_id` ;

可以加排序

# 案例：查询每个工种的工种名和员工个数，并且按员工个数降序
SELECT 
    job_title,
    COUNT(*) 
FROM
    employees e,
    jobs j 
WHERE e.`job_id` = j.`job_id` 
GROUP BY job_title 
ORDER BY COUNT(*) DESC ;

三表连接

# 案例：
SELECT 
    last_name,
    department_name,
    city 
FROM
    employees e,
    departments d,
    locations l 
WHERE e.`department_id` = d.`department_id` 
    AND d.`location_id` = l.`location_id` ;

6.1.2 非等值连接

# 案例中所用到的表(不必查看本段代码)
CREATE TABLE job_grades
(grade_level VARCHAR(3),
 lowest_sal  INT,
 highest_sal INT);

INSERT INTO job_grades
VALUES ('A', 1000, 2999);

INSERT INTO job_grades
VALUES ('B', 3000, 5999);

INSERT INTO job_grades
VALUES('C', 6000, 9999);

INSERT INTO job_grades
VALUES('D', 10000, 14999);

INSERT INTO job_grades
VALUES('E', 15000, 24999);

INSERT INTO job_grades
VALUES('F', 25000, 40000);

SELECT salary, employee_id FROM employees;
SELECT * FROM job_grades;

employees	job_grades

本案例是用employees里的salary对应于job_grades里的值区间进行非等值比较：

# 案例：查询员工的工资和工资级别
SELECT 
    salary,
    grade_level 
FROM
    employees e,
    job_grades g 
WHERE e.`salary` BETWEEN g.`lowest_sal` 
    AND g.`highest_sal` ;

6.1.3 自连接

# 案例：查询员工名和上级的名称
SELECT e.employee_id, e.last_name, m.employee_id, m.last_name
FROM employees e, employees m
WHERE e.`manager_id` = m.`employee_id`;

6.2 sql99标准

语法：

select 查询列表

from 表1 别名【连接类型】

join 表2 别名

on 连接条件

【where 筛选条件】

【group by 分组】

【having 筛选条件】

【order by 排序条件】

分类：

内连接：inner
外连接：
- 左外：left 【outer】
- 右外：right 【outer】
- 全外：full 【outer】
交叉连接：cross

6.2.1 内连接

语法：

select 查询列表

from 表1 别名

inner join 表2 别名

on 连接条件；

分类：

等值连接
非等值连接
自连接

特点：

添加排序、分组、筛选
inner可以省略
筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
inner join 连接和sql92语法中的等值连接效果一样，都是查询多表的***交集部分***。
三表连接注意连接顺序，有可能两个表之间没有连接条件，需要借助第三个表。

等值连接

# 案例：查询员工名、部门名
SELECT last_name, department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id` = d.`department_id`;

# 案例：（添加筛选）查询名字中包含e的员工名和工种名
SELECT last_name, job_title
FROM employees e
INNER JOIN jobs j
ON e.`job_id` = j.`job_id`
WHERE e.`last_name` LIKE '%e%';

# 案例：（添加分组和筛选）查询部门个数大于3的城市名和部门个数
SELECT city, COUNT(*)
FROM departments d
INNER JOIN locations l
ON d.`location_id` = l.`location_id`
GROUP BY city
HAVING COUNT(*) > 3;

# 案例：（添加排序）查询哪个部门的部门员工个数大于3的部门名和员工个数，并按个数降序
SELECT department_name, COUNT(*) 员工个数
FROM employees e
INNER JOIN departments d
ON e.`department_id` = d.`department_id`
GROUP BY d.`department_id`
HAVING 员工个数 > 3
ORDER BY 员工个数 DESC;

# 案例：（三表连接）查询员工名、部门名、工种名，并按员工名降序
SELECT last_name, department_name, job_title
FROM employees e
INNER JOIN departments d ON d.`department_id` = e.`department_id`
INNER JOIN jobs j ON e.`job_id` = j.`job_id`
ORDER BY e.`last_name` DESC;

非等值连接

# 案例：查询员工的工资级别
SELECT salary, grade_level
FROM employees e
INNER JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;

# 案例：查询每个工资级别个数大于20的个数，并且按工资级别降序
SELECT COUNT(*), grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
GROUP BY g.`grade_level`
HAVING COUNT(*) > 20
ORDER BY g.`grade_level` DESC;

自连接

# 案例：查询姓名中包含字母k的员工的名字、上级的名字
SELECT e.`last_name`, m.`last_name`
FROM employees e
JOIN employees m
ON e.`manager_id` = m.`employee_id`
WHERE e.`last_name` LIKE '%k%';

6.2.2 外连接

应用场景：

用于查询一个表中有，另一个表没有的记录

语法：

特点：

外连接的查询结果为主表中的所有记录
- 如果从表中有和它匹配的，则显示匹配的值
- 否则，显示Null
- 外连接查询结果 = 内连接结果 + 主表中有而从表没有的记录
怎么区分主从表：
- 左外连接：left join 左边的是主表
- 右外连接：right join 右边的是主表
左外和右外交换两个表的顺序，可以实现同样的效果
全外连接 = 内连接结果 + 表1中有但表2没有的 + 表2中有但表1中没有的

# 案例：查询男朋友不在男生表的女生名
## 左外连接
SELECT b.name
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.`boyfriend_id` = bo.`id`
WHERE bo.`id` IS NULL;

### 换主从表，注意where条件有改变
SELECT b.*, bo.*
FROM boys bo
LEFT OUTER JOIN beauty b
ON b.`boyfriend_id` = bo.`id`
WHERE b.`id` IS NULL;

## 右外连接
SELECT b.name, bo.*
FROM boys bo
RIGHT OUTER JOIN beauty b
ON b.`boyfriend_id` = bo.`id`
WHERE bo.`id` IS NULL;

# 案例：查询哪个部门没有员工
SELECT d.*, e.`employee_id`
FROM departments d
LEFT OUTER JOIN employees e
ON e.`department_id` = d.`department_id`
WHERE e.`employee_id` IS NULL;

6.2.3 交叉连接

笛卡尔乘积

USE girls;
SELECT b.*, bo.*
FROM beauty b
CROSS JOIN boys bo;

6.3 总结

内连接：

左外连接：

查询表A中所有的部分查询表A去掉交集部分
右外连接：

查询表B中所有的部分	查询表B去掉交集部分

7. 子查询

含义：

出现在其他语句中的select语句，称为子查询或内查询。
外部的查询语句，称为主查询或外查询

分类：

按子查询出现的位置：
- select 后面：仅仅支持标量子查询
- from 后面：支持表子查询
- where 或 having 后面（重点）：标量子查询（单行），列子查询（多行），行子查询（用的较少）
- exists 后面（相关子查询）：表子查询
按结果集的行列数不同：
- 标量子查询（结果集只有一行一列）
- 列子查询（结果集只有一列多行）
- 行子查询（结果集有一行多列）
- 表子查询（结果集一般为多行多列）

7.1 where 或 having 后面

特点：

子查询放在小括号内
子查询一般放在条件的右侧
标量子查询，一般搭配着单行操作符使用

> < >= <= = <>

列子查询，一般搭配着多行操作符使用

in、all/some、all
子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

7.1.1 标量子查询

（单行子查询）

# 案例：谁的工资比Abel高？
## 1. 查询Abel的工资
SELECT salary
FROM employees e
WHERE e.`last_name` = 'Abel';
## 2. 查询员工信息，满足 salary 大于第一步的结果
SELECT *
FROM employees e
WHERE e.`salary` > (
	SELECT salary
	FROM employees e
	WHERE e.`last_name` = 'Abel'
	);

# 案例：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资
SELECT last_name, job_id, salary
FROM employees e
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
	);

# 案例：查询最低工资大于50号部门最低工资的部门id和其最低工资
## 1. 查询50号部门的最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id = 50;
## 2. 查询每个部门的最低工资
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id;
## 3. 在2的基础上筛选，满足min(salary)大于第一步的结果
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
	SELECT MIN(salary)
	FROM employees
	WHERE department_id = 50
);

7.1.2 列子查询

（多行子查询）

多行比较操作符

操作符	含义
IN/ NOT IN	等于列表中的任意一个
ANY \| SOME	和子查询返回的某一个值比较
ALL +	和子查询返回的所有值比较

# 案例(IN)：返回Location_id是1400或1700的部门中的所有员工姓名
## 1. 查询location_id是1400或1700的部门编号
SELECT DISTINCT department_id
FROM departments d
WHERE d.`location_id` IN(1400, 1700);
## 2. 查询员工姓名，要求部门号是1列表中的某一个
SELECT last_name
FROM employees e
WHERE e.`department_id` IN (
	SELECT DISTINCT department_id
	FROM departments d
	WHERE d.`location_id` IN(1400, 1700)
);
### IN 等价于 = ANY
### NOT IN 等价于 != ANY


# 案例(ANY)：返回其他工种中比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 DISTINCT salary
	FROM employees
	WHERE job_id = 'IT_PROG'
);
## 或
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE job_id != 'IT_PROG'
AND salary < (
	SELECT MAX(salary)
	FROM employees
	WHERE job_id = 'IT_PROG'
);

# 案例(ALL)：返回其他工种中比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 DISTINCT salary
	FROM employees
	WHERE job_id = 'IT_PROG'
);
## 或
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE job_id != 'IT_PROG'
AND salary < (
	SELECT MIN(salary)
	FROM employees
	WHERE job_id = 'IT_PROG'
);

7.1.3 行子查询

（结果集一行多列或多行多列）

# 案例：查询员工编号最小并且工资最高的员工信息
SELECT *
FROM employees
WHERE (employee_id, salary) = (
	SELECT MIN(employee_id), MAX(salary)
	FROM employees
);
## 相当于：
SELECT *
FROM employees
WHERE employee_id = (
	SELECT MIN(employee_id)
	FROM employees
)
AND salary = (
	SELECT MAX(salary)
	FROM employees
);

7.2 select 后面

# 案例：查询每个部门的员工个数
SELECT d.*, (
	SELECT COUNT(*)
	FROM employees e
	WHERE e.`department_id` = d.`department_id`
) 个数
FROM departments d;

# 案例：查询员工号=102的部门名
SELECT (
	SELECT department_name
	FROM departments d
	INNER JOIN employees e
	ON e.`department_id` = d.`department_id`
	WHERE e.`employee_id` = 102
) 部门名;

7.3 from 后面

将子查询结果充当一张表，要求***必须起别名***。

# 案例：查询每个部门的平均工资的工资等级
## 1. 查询每个部门的平均工资
SELECT AVG(salary), department_id
FROM employees
GROUP BY department_id
## 2. 连接1的结果集和job_grades表，筛选条件平均工资 between lowest_sal and highest_sal
SELECT ag_dep.*, g.`grade_level`
FROM (
	SELECT AVG(salary) ag, department_id
	FROM employees
	GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN g.`lowest_sal` AND g.`highest_sal`;

7.4 exists 后面

（相关子查询）

语法：

exists （完整的查询语句）

**结果：**1或0

特点：

先外查询

# 案例：查询有员工的部门名
SELECT department_name
FROM departments d
WHERE EXISTS(
	SELECT *
	FROM employees e
	WHERE d.`department_id` = e.`department_id`
);
## 或者用IN：
SELECT department_name
FROM departments d
WHERE d.`department_id` IN (
	SELECT department_id
	FROM employees
);

# 案例：查询没有女朋友的男生信息
SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(
	SELECT b.`boyfriend_id`
	FROM beauty b
	WHERE bo.`id` = b.`boyfriend_id`
);
## 或者用IN：
SELECT bo.*
FROM boys bo
WHERE bo.id NOT IN (
	SELECT b.`boyfriend_id`
	FROM beauty b
)