使用工具：SQLyog
使用语言：SQL
学习日期：02
学习内容：
Day02
多表查询Day02
-- 多表查询（继Day01）
/*
演绎式：提出问题1 --> 解决问题1 -->提出问题2 --> 解决问题2
归纳式：总-->分
*/
-- 7：多表查询的分类
/*
1：等值连接 AND 非等值连接
2：自连接 AND 非自连接
3：内连接 AND 外连接
*/
-- 7.1 等值连接 AND 非等值连接
-- 非等值连接例子
SELECT e.last_name,e.salary,j.grade_level
FROM employees AS e,job_grades AS j
WHERE e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`
-- 7.2 自连接 AND 非自连接
-- 练习：查询员工ID，员工姓名及其管理者的ID和姓名
-- 自连接的例子
SELECT emp.last_name,emp.employee_id,mgr.last_name,mgr.employee_id
FROM employees emp,employees mgr
WHERE emp.manager_id = mgr.employee_id
-- 7.3 内连接 AND 外连接
-- 内连接例子(内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行)
SELECT employee_id,department_name
FROM employees e, departments p
WHERE e.`department_id` = p.department_id;
-- 外连接：合并具有同一列的两个以上的表的行，结果集中除了包含一个表与另一个表匹配的行之外，还查询到了左表或者
-- 右表中不匹配的行
-- 外连接的分类：左外连接 右外连接 满外连接
-- 左外连接：两个表在连接过程中除了返回满足连接条件的行之外，还返回了左表中不满足条件的行
-- 右外连接：两个表在连接过程中除了返回满足连接条件的行之外，还返回了右表中不满足条件的行
-- 练习：查询所有员工的last_name,department_name
-- SQL92语法实现外连接：使用 +
SELECT employee_id,department_name
FROM employees e, departments p
WHERE e.`department_id` = p.department_id(+); -- MySQl不支持SQL92语法写外连接
-- SQL99语法使用JOIN....ON的方式实现多表的查询，这种方式也可以解决外连接问题
-- 左外连接
SELECT last_name,department_name
FROM employees e LEFT OUTER JOIN departments p
ON e.`department_id` = p.department_id;
-- 右外连接
SELECT last_name,department_name
FROM employees e RIGHT OUTER JOIN departments p
ON e.`department_id` = p.department_id;
-- 满外连接:MySQl不支持 FULL OUTER JOIN 这样的写法
SELECT last_name,department_name
FROM employees e FULL OUTER JOIN departments p
ON e.`department_id` = p.department_id;
-- 8：UNION 和 UNIONALL的使用
-- UNION：会执行去重的操作，UNIONALL不会执行去重
-- 9：7种JOIN的实现（图片在下面）
-- 中图：内连接
SELECT employee_id,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;
-- 左上图：左外连接
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;
-- 右上图：右外连接
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
-- 左中图：
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
-- 右中图：
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
-- 左下图：满外连接
-- 方式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
图片资料：
多表查询Day02练习
# 1.显示所有员工的姓名，部门号和部门名称。
SELECT last_name,d.department_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
# 2.查询90号部门员工的job_id和90号部门的location_id
SELECT job_id ,location_id
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id`=90
# 3.选择所有有奖金的员工的 last_name , department_name , location_id , city
SELECT e.last_name,d.department_name,l.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;
# 4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name
SELECT last_name , job_id , d.department_id , 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';
# 5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’
SELECT d.department_name,l.street_address,e.last_name,e.job_id ,e.salary
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE d.`department_name`='Executive'
# 6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
/*]
employees Emp# manager Mgr#
kochhar 101 king 100
*/
SELECT emp.last_name AS 'employees',emp.employee_id AS 'EMP',mgr.last_name AS 'manager',mgr.employee_id AS '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 d.location_id , city
FROM locations l LEFT JOIN departments d
ON l.location_id = d.`location_id`
WHERE d.`location_id` IS NULL
# 9. 查询部门名为 Sales 或 IT 的员工信息
SELECT e.employee_id,e.last_name,e.department_id,d.`department_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
ABS(-123),ABS(32),SIGN(-23),SIGN(43),PI(),CEIL(32.32),CEILING(-43.23),FLOOR(32.32),
FLOOR(-43.23),MOD(12,5)
FROM DUAL;
-- 取随机数
SELECT RAND(),RAND(),RAND(10),RAND(10),RAND(-1),RAND(-1)
FROM DUAL;
-- 四舍五入，截断
SELECT
ROUND(12.33),ROUND(12.343,2),ROUND(12.324,-1),TRUNCATE(12.66,1),TRUNCATE(12.66,-1)
FROM DUAL;
-- 指数和对数
SELECT POW(2,5),POWER(2,4),EXP(2),LN(10),LOG10(10),LOG2(4)
FROM DUAL;
-- 进制之间的转换
SELECT BIN(10),HEX(10),OCT(10),CONV(10,2,8)
FROM DUAL;
总结
关键字
JOIN…ON ：
说明：创建多表连接，可以使用 ON 子句指定额外的连接条件。
这个连接条件是与其它条件分开的。
关键字 JOIN、INNER JOIN、CROSS JOIN 的含义是一样的，都表示内连接
UNION
UNION ALL：
说明：合并查询结果
区别：UNION有去除重复的操作
UNION ALL没有去除重复的操作
USING：
说明：它会自动查询两个连接表中所有的相同的字段，然后进行等值连接
单行函数关键字：
重点
多表查询的练习，多表查询看了两遍了