MySQL知识点总结记录排序与分页、多表连接查询P22-P31

老霉儿

已于 2022-12-16 20:33:38 修改

阅读量121

点赞数

分类专栏： mysql 文章标签： mysql 数据库

于 2022-12-14 12:02:37 首次发布

本文链接：https://blog.csdn.net/weixin_43367551/article/details/128303655

版权

mysql 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

排序与分页

升序ASC 降序 DESC

别名为啥不能用在WHERE中：

因为数据库执行顺序是先FROM WHERE

然后在SELECT，ORDER BY，所以执行WHERE时别名还没产生，所以不能使用，只有在别名定义后的操作中才可使用。

LIMIT

多表查询

多表查询的分类

角度1：等值连接 vs 非等值连接

角度2：自连接 vs 非自连接

角度3：内连接 vs 外连接

内连接：合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.`department_id` = d.department_id; #只有106条记录

# 外连接：合并具有同一列的两个以上的表的行, 结果集中除了包含一个表与另一个表匹配的行之外，
# 还查询到了左表或右表中不匹配的行。

# 外连接的分类：左外连接、右外连接、满外连接

# 左外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左表中不满足条件的行，这种连接称为左外连接。
# 右外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回右表中不满足条件的行，这种连接称为右外连接。

#SQL92语法实现内连接：见上，略
#SQL92语法实现外连接：使用 + ----------MySQL不支持SQL92语法中外连接的写法！
#不支持：
SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.`department_id` = d.department_id(+);

#SQL99语法中使用 JOIN ...ON 的方式实现多表的查询。这种方式也能解决外连接的问题。MySQL是支持此种方式的。
#SQL99语法如何实现多表的查询。

#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`;

#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`;

UNION操作符

MySQL中实现满连接

# 左下图：满外连接
# 方式1：左上图 UNION ALL 右中图

SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;

# 方式2：左中图 UNION ALL 右上图

SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

# 右下图：左中图 UNION ALL 右中图
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;

SQL99新特性

多表查询练习题

#1.显示所有员工的姓名、部门号和部门名称
SELECT e.first_name, e.department_id,d.department_name
FROM employees e
LEFT JOIN departments d 
ON e.department_id = d.department_id
#查询90号部门员工的jobid和90号部门的locationid
SELECT e.job_id, d.location_id
FROM employees e 
JOIN departments d 
ON e.department_id = d.department_id
WHERE e.department_id = 90
#选择所有有奖金的员工的last name ，departmentid /department_name /city
SELECT e.last_name,e.department_id,d.department_name,l.city
FROM employees e 
LEFT JOIN departments d #有个哥们没有部门，但是有奖金
ON e.department_id = d.department_id
LEFT JOIN locations l 	#前两个表左外连接了，此时location表就腿短了，也需要左外连接
ON d.location_id = l.location_id
WHERE NOT e.commission_pct <=>NULL
#选择city在Toronto工作的员工的lastname 、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'
#查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive
SELECT d.department_name,l.street_address,e.last_name,job_title,salary
FROM employees e 
JOIN departments d 
ON e.department_id = d.department_id
JOIN locations l 
on d.location_id = l.location_id
join jobs j 
ON e.job_id = j.job_id
WHERE d.department_name = 'Executive'
#选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
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 
#查询哪些部门么有员工
SELECT d.department_name,e.last_name
FROM departments d 
LEFT JOIN employees e 
ON d.department_id = e.department_id 
WHERE e.department_id is NULL#此时左外连接，相当于d的数据是多的，而e只有部分，
													#要求的数据也就是d比e多的那些数据，也就是e为空的数据

SELECT d.department_name,e.last_name
FROM employees e 
RIGHT JOIN departments d 					#同样，此时用右外链接，还是d的数据是多的，e是部分
ON d.department_id = e.department_id
WHERE e.department_id is NULL			#要求的数据还是d比e多的那些数据，还是e为空的数据。

SELECT *												#观察两个表连接后的状态，也可看到上述d比e多的数据
FROM departments d 
LEFT JOIN employees e 
ON d.department_id = e.department_id  
#8. 查询哪个城市没有部门
SELECT l.city,d.department_id,l.location_id
FROM locations l 
LEFT JOIN departments d 
ON l.location_id = d.location_id
WHERE d.location_id IS NULL

#查询部门名为 Sales 或 IT 的员工信息
SELECT e.last_name
FROM employees e 
JOIN departments d 
ON e.department_id = d.department_id
WHERE d.department_name = 'Sales' OR d.department_name = 'IT' 

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