MySQL笔记04

最新推荐文章于 2024-08-23 17:37:15 发布

没事刷两道

最新推荐文章于 2024-08-23 17:37:15 发布

阅读量64

点赞数

分类专栏：笔记文章标签： mysql

本文链接：https://blog.csdn.net/weixin_43820452/article/details/116642742

版权

笔记专栏收录该内容

23 篇文章 1 订阅

订阅专栏

MySQL笔记04

进阶6：连接查询

#二、SQL99语法
/*
语法：
	select 查询列表
	from 表1 别名 【连接类型】
	join 表2 别名
	on 连接条件
	【where 筛选条件】
	【group by 分组】
	【having 筛选条件】
	【order by 排序列表】

分类：
内连接（*）：inner
外连接
	左外（*）：left 【outer】
	右外（*）：right 【outer】
	全外：full 【outer】
交叉连接
*/

#内连接
/*
语法：
	select 查询列表
	from 表1 别名 
	inner join 表2 别名
	on 连接条件
	【where 筛选条件】
	【group by 分组】
	【having 筛选条件】
	【order by 排序列表】
分类：
	等值
	非等值
	自连接
特点：
	添加排序、分组、筛选
	inner 可省略
	筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
*/

#一、内连接
#等值连接
#1、查询员工名、部门名（调换位置）
SELECT last_name, department_name
from employees e
INNER JOIN departments d
ON e.department_id = d.department_id;

#2、查询名字中包含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、查询部门个数>3的城市名和部门个数（分组+筛选）
SELECT city, COUNT(*)
FROM locations l
INNER JOIN departments d
ON l.location_id = d.location_id
GROUP BY city
HAVING COUNT(*) > 3;

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

#5、查询员工名、部门名、工种名，并按部门名降序
SELECT last_name, department_name, job_title
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN jobs j ON e.job_id = j.job_id
ORDER BY d.department_id DESC;

#非等值连接
#查询员工的工资和工资级别
SELECT salary, grade_level
FROM employees e
INNER JOIN job_grades j
ON e.salary BETWEEN j.lowest_sal AND j.highest_sal;

#自连接
#查询员工名中包含k的员工名、上级名
SELECT e.last_name, m.last_name
FROM employees e
INNER JOIN employees m
ON e.manager_id = m.employee_id
WHERE e.last_name LIKE '%k%';

#二、外连接
/*
应用场景：用于查询一个表中有，另一个表中没有的

特点：
	外连接的查询结果为主表中的所有记录
		如果从表中有和它匹配的，则显示匹配值
		如果从表中没有和它匹配的，显示null
		外连接查询结果=内连接结果+主表中有从表中没有的记录
	左外连接，left join左边的是主表
	右外连接，right join右边的是主表
*/
SELECT * FROM beauty;
SELECT * FROM boys;

#查询男朋友不在男神表中的女神名
#左外连接
SELECT b.name, bo.*
FROM beauty b
LEFT JOIN boys bo
ON b.boyfriend_id = bo.id;
#右外连接
SELECT b.name, bo.*
FROM boys bo
RIGHT JOIN beauty b
ON b.boyfriend_id = bo.id;

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

#三、交叉连接 笛卡尔乘积（相当于全连接）
SELECT b.*, bo.*
FROM beauty b
CROSS JOIN boys bo;

#练习
#1、查询编号>3 的女神的男朋友信息，如果有则列出详细，如果没有，用 null 填充
SELECT b.*, bo.*
FROM beauty b
LEFT JOIN boys bo
ON b.boyfriend_id = bo.id
WHERE b.id > 3;

#2、查询哪个城市没有部门
SELECT city, department_name
FROM locations l
LEFT JOIN departments d
ON l.location_id = d.location_id
WHERE d.department_name IS NULL;

#3、查询部门名为 SAL 或 IT 的员工信息
SELECT department_name, e.*
FROM departments d
LEFT JOIN employees e
ON d.department_id = e.department_id
#WHERE d.department_name = 'SAL' OR d.department_name = 'IT';
WHERE d.department_name IN('SAL','IT');

进阶7：子查询

#进阶7：子查询
/*
含义：出现在其他语句中的select语句，称为子查询或内查询
			外部的查询语句称为主查询或外查询
			
分类：
	按子查询出现的位置：
		select后面：
			标量子查询
		from后面：
			表子查询
		where或having后面：（*）
			标量子查询（单行） （*）
			列子查询 （多行）（*）
			行子查询
		exists后面：
			表子查询
	按结果集的行列数不同：
		标量子查询（结果集只有一行一列）
		列子查询（结果集只有一列多行）
		行子查询（结果集只有一行多列）
		表子查询（结果集有多行多列）
*/

#一、where或having后面
/*
1、标量子查询（单行子查询）
2、列子查询（多行子查询）
3、行子查询（多行多行）
特点：
	子查询放在小括号内
	子查询一般放在条件右侧
	标量子查询一般搭配单行操作符使用
		> < >= <= = <>
	列子查询一般搭配多行操作符使用
		in、any、some、all
	子查询优先于主查询
*/

#1、标量子查询
#案例1：谁的工资比Abel高
SELECT * 
FROM employees
WHERE salary > (
	SELECT salary
	FROM employees
	WHERE last_name = 'Abel'
);
#案例2：查询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
);
#案例3：返回公司工资最少的员工的last_name,job_id和salary
SELECT last_name, job_id, salary
FROM employees
WHERE salary = (
	SELECT min(salary)
	FROM employees
);
#案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
	SELECT MIN(salary)
	FROM employees
	WHERE department_id = 50
);


#2、列子查询
#案例1：查询location_id是1400或1700的部门中的所有员工姓名
SELECT last_name
FROM employees
WHERE department_id IN(
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id IN(1400, 1700)
);
#或
SELECT last_name
FROM employees
WHERE department_id = ANY(
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id IN(1400, 1700)
);
#案例2：返回其它工种中比job_id为'IT_PROG'工种任一工资低的员工的工号、姓名、job_id以及salary
#a、job_id为'IT_PROG'部门任一工资
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG';
#b、工号、姓名、job_id以及salary，salary<(a)中的任意一个
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ANY(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id = 'IT_PROG'
) AND job_id <> 'IT_PROG'
#或
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < (
	SELECT MAX(salary)
	FROM employees
	WHERE job_id = 'IT_PROG'
) AND job_id <> 'IT_PROG'
##案例3：返回其它工种中比job_id为'IT_PROG'工种所有工资都低的员工的工号、姓名、job_id以及salary
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ALL(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id = 'IT_PROG'
) AND job_id <> 'IT_PROG'
#或
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < (
	SELECT MIN(salary)
	FROM employees
	WHERE job_id = 'IT_PROG'
) AND job_id <> 'IT_PROG'

#3、行子查询（结果一行多列或多行多列）
#案例：查询员工编号最小并且工资最高的员工信息
SELECT *
FROM employees
WHERE employee_id = (
	SELECT MIN(employee_id)
	FROM employees
) AND salary = (
	SELECT MAX(salary)
	FROM employees
);
#使用行子查询
SELECT *
FROM employees
WHERE (employee_id, salary) = (
	SELECT MIN(employee_id), MAX(salary)
	FROM employees
);

#二、select后面
#案例：查询每个部门的员工个数
SELECT d.*, (
		SELECT COUNT(*)
		FROM employees e
		WHERE e.department_id = d.department_id
	) 个数
FROM departments d;
#案列2：查询员工号=102的部门名
SELECT (
	SELECT department_name
	FROM departments d
	INNER JOIN employees e
	ON d.department_id = e.department_id
	WHERE e.employee_id = 102
);

#三、from后面（表连表）
/*
将子查询结果充当一张表，要求必须起别名
*/
#案例：查询每个部门的平均工资的工资等级
#a、查询每个部门的平均工资

SELECT * FROM job_grades;
#b、连接a的结果集和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
	) AS ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

#四、exists后面（相关子查询）
/*
语法：
	exists(完整的查询语句)
结果：
	1或0
*/
SELECT EXISTS(SELECT employee_id FROM employees);

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

子查询练习

#练习
#1. 查询和 Zlotkey 相同部门的员工姓名和工资
SELECT last_name, salary
FROM employees
WHERE department_id = (
	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. 查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
#from后的子查询(表连表)
SELECT employee_id, last_name, salary
FROM employees e
INNER JOIN (
	SELECT AVG(salary) ag, department_id
	FROM employees
	GROUP BY department_id
) ag_dep
ON e.department_id = ag_dep.department_id
WHERE salary > ag;

#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 DISTINCT department_id
	FROM departments
	WHERE location_id = 1700
);

#6. 查询管理者是 King 的员工姓名和工资
SELECT last_name, salary
FROM employees
WHERE manager_id IN (
	SELECT employee_id
	FROM employees
	WHERE last_name = 'K_ing'
);

#7. 查询工资最高的员工的姓名，要求 first_name 和 last_name 显示为一列，列名为 姓.名
SELECT CONCAT(first_name,last_name) '姓.名'
FROM employees
WHERE salary = (
	SELECT MAX(salary)
	FROM employees
);

子查询经典案例练习

#子查询经典案例练习
#1. 查询工资最低的员工信息: last_name, salary
SELECT last_name, salary
FROM employees
WHERE salary = (
		SELECT MIN(salary)
		FROM employees
);

#2. 查询平均工资最低的部门信息
#a、各部门的平均工资
SELECT AVG(salary), department_id
FROM employees
GROUP BY department_id
#b、查询a结果中平均工资最低的部门编号
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) ASC
LIMIT 1;
#c、查询部门信息
SELECT *
FROM departments 
WHERE department_id = (
		SELECT department_id
		FROM employees
		GROUP BY department_id
		ORDER BY AVG(salary) ASC
		LIMIT 1
);

#3. 查询平均工资最低的部门信息和该部门的平均工资
SELECT d.*, ag
FROM departments d
INNER JOIN (
		SELECT AVG(salary) ag,department_id
		FROM employees
		GROUP BY department_id
		ORDER BY AVG(salary) ASC
		LIMIT 1
) ag_dep
ON d.department_id = ag_dep.department_id

#4. 查询平均工资最高的 job 信息
SELECT j.*
FROM jobs j
WHERE job_id = (
		SELECT job_id
		FROM employees
		GROUP BY job_id
		ORDER BY AVG(salary) DESC
		LIMIT 1
);

#5. 查询平均工资高于公司平均工资的部门有哪些?
SELECT AVG(salary), department_id
FROM employees
GROUP BY department_id
HAVING avg(salary) > (
		SELECT AVG(salary)
		FROM employees
);

#6. 查询出公司中所有 manager 的详细信息.
SELECT *
FROM employees
WHERE employee_id IN (
		SELECT DISTINCT manager_id
		FROM employees
);

#7. 各个部门中 最高工资中最低的那个部门的 最低工资是多少
#a、各个部门中的最高工资
SELECT MAX(salary), department_id
FROM employees
GROUP BY department_id
#b、各个部门中的最高工资中最低的部门编号
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY MAX(salary) ASC
LIMIT 1
#c、
SELECT MIN(salary), department_id
FROM employees
WHERE department_id = (
		SELECT department_id
		FROM employees
		GROUP BY department_id
		ORDER BY MAX(salary)
		LIMIT 1
);
SELECT MIN(salary),department_id
FROM employees
WHERE department_id = 10

#8. 查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
#查询平均工资最高的部门
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 1
#查询平均工资最高的部门的 manager_id
SELECT manager_id
FROM employees
WHERE department_id = (
	SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 1
);
#
SELECT last_name, department_id, email, salary
FROM employees
WHERE employee_id IN (
		SELECT manager_id
		FROM employees
		WHERE department_id = (
				SELECT department_id
				FROM employees
				GROUP BY department_id
				ORDER BY AVG(salary) DESC
				LIMIT 1
		)
);

进阶8：分页查询

#进阶8：分页查询 重点
/*
应用场景：要显示的数据一页显示不全，需要分页提交SQL请求
语法：
	select 查询列表
	from 表
	【join type join 表2
	on 连接条件
	where 筛选条件
	group by 分组字段
	having分组后的筛选 
	order by 排序的字段】
	limit offset, size;
	
	offset 要显示条目的起始索引（此处起始索引从0开始）
	size 要显示的条目个数
	
特点：
	a、limit语句放在查询语句最后
	b、公式 limit（page-1）*size，size
*/

#案例1：查询前五条员工信息
SELECT * FROM employees LIMIT 0,5;
SELECT * FROM employees LIMIT 5;

#案例2：查询第11条到第15条
SELECT * FROM employees LIMIT 10, 15;

#案例3：有奖金的员工信息，并且工资较高的前十名
SELECT * 
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10;

进阶9：联合查询

#进阶9：联合查询
/*
union联合 合并：将多条查询语句的结果合并成一个结果
语法：
	查询语句1
	union
	查询语句2
	union
	...
	
应用场景：从多个不同的表中查询，并且这些表之间没有连接关系
特点：
	a、要求多条查询语句的查询列数是一致的
	b、要求多条查询语句的每一列的类型和顺序一致
	c、union关键字默认去重，使用union all可以包含重复项
*/

#引入：查询部门编号>90或邮箱包含a的员工信息
SELECT *
FROM employees
WHERE email LIKE '%a%'
OR department_id > 90;

SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id > 90;