目录
正文
1.连接查询
笛卡尔乘积现象：表1有m行，表2有n行，结果 m*n行
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件----使用连接查询。
含义
又称多表查询，当查询的字段来自于多个表时，就会用到连接查询。
分类
按年代分类：
sql92标准：支持内连接，也支持一部分外连接（用于oracle、sqlserver，mysql不支持）
sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接
按功能分类：
内连接：
等值连接
非等值连接
自连接
外连接：
左外连接
右外连接
全外连接
交叉连接
详解
表格集
表 locations
表 jobs
表 employees
表 departments
表 beauty
表boys
一、sql92标准
1.等值连接
特点：
① 多表等值连接的结果为多表的交集部分
② n表连接，至少需要n-1个连接条件
③ 多表的顺序没有要求
④ 一般需要为表起别名
⑤可以搭配前面介绍的所有子句使用，比如排序分组、筛选
案例：
#案例一：查询女神名和对应的男神名
SELECT `name` ,boyName FROM beauty ,boys
WHERE beauty.boyfriend_id=boys.id;#案例二：查询员工名和对应的部门名
SELECT last_name ,department_name FROM
employees,departments
WHERE employees.department_id=departments.department_id;
2.为表起别名
/*
①提高语句的简洁度
②区分多个重名的字段
注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定
*/
#查询员工名、工种号、工种名SELECT last_name,e.job_id,job_title FROM employees AS e,jobs as j WHERE e.job_id=j.job_id;
3.两个表的顺序可以调换
SELECT last_name,e.job_id,job_title FROM jobs as j ,employees AS e WHERE e.job_id=j.job_id;
4.可以加筛选
案例：查询有奖金的员工名、 部门名
SELECT last_name,department_name ,commission_pct FROM employees e,departments d
WHERE commission_pct IS NOT NULL AND e.department_id=d.department_id;
案例2：查询城市名中第二个字符为o的部门名和城市名SELECT department_name,city FROM departments d,locations l
WHERE l.city LIKE '_o%' AND d.location_id=l.location_id;
5.可以加分组案例1：查询每个城市的部门个数
SELECT COUNT(*),city FROM departments d,locations l
WHERE l.location_id=d.location_id
GROUP BY city;案例2：查询出有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT department_name,d.manager_id ,MIN(salary)
FROM employees e,departments d
WHERE e.department_id=d.department_id AND commission_pct IS NOT NULL
GROUP BY d.department_id,d.manager_id;
6.可以排序案例：查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT COUNT(*),e.job_id FROM employees e,jobs j WHERE e.job_id=j.job_id
GROUP BY e.job_id
ORDER BY COUNT(*) DESC;
7.可以实现三表连接案例：查询员工名、部门名和所在城市
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
AND city LIKE 's%'
;
2.非等值连接
和等值的区别是where后的筛选条件是非等值的连接条件案例1：查询员工的工资和工资级别
SELECT salary,salary_grade FROM employees,
job_grade
WHERE employees.salary BETWEEN lower AND higher;
3.自连接
和等值连接的区别是 FROM 表 别名1，表 别名2 （两个同样的表 起不一样的别名作区分）
#案例： 查询员工名及上级的名称
SELECT e.employee_id,e.last_name,e.manager_id,m.employee_id,m.last_name
FROM employees e,employees m
WHERE e.manager_id=m.employee_id;
二、sql99标准
sql99标准
语法：
select 查询列表
from 表1 别名 [连接类型]
JOIN 表2 别名 on 连接条件
[WHERE 筛选条件]
[GROUP BY 分组]
[HAVING 筛选条件]
[ORDER BY 排序列表]
内连接（*） inner
外连接
左外（*） left[outer]
右外（*） right[outer]
全外 full [outer]
交叉连接 cross
1.内连接语法：
SELECT 查询列表
FROM 表1 别名
INNER JOIN 表2 别名
ON 连接条件；分类：
等值
非等值
自连接
特点：
①添加排序、分组、筛选
②inner可以省略
③筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
④inner join连接和sql92语法中的等值连接效果是一样的
⑤表的顺序可以调换
⑥内连接的结果=多表的交集
⑦n表连接至少需要n-1个连接条件
1.等值连接
案例
1.查询员工名、部门名（调换位置）SELECT last_name,department_name FROM employees e
INNER JOIN departments d ON e.department_id =d.department_id;上面是sql99标准的SQL语句，用sql92语句是
SELECT last_name,department_name FROM employees e，departments d
Where e.department_id =d.department_id;可以看出sql92的数据源都是放在from后面的，而sql99只放一个表格在那里，另外
sql92的筛选条件和连接条件都是在where后面，分离性不够好，
sql99将连接条件放在 ON 后面，筛选条件放在where后面。
两个都可以用，不过我更推荐sql99.
2.查询名字中包含e的员工名和工种名（筛选）
SELECT last_name,job_title FROM employees e
INNER JOIN jobs j ON e.job_id=j.job_id
WHERE last_name LIKE '%e%';
3.查询部门个数>3的城市名和部门个数（分组+筛选）
SELECT city ,COUNT(*) FROM locations l
INNER JOIN departments d ON d.location_id=l.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 department_name DESC;
2.非等值连接查询员工的工资级别
SELECT salary,salary_grade FROM employees JOIN job_grade
ON employees.salary BETWEEN lower AND higher;
3.自连接
案例：查询姓名中包含字符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%';
二.外连接
应用场景：用于查询一个表中有，另一个表没有的记录特点：
1.外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示null
外连接查询结果=内连接结果+主表中有而从表没有的记录
2.左外连接，left join左边的是主表
右外连接，right join 右边的是主表
全外连接=内连接+表1有表二没有+表2有表一没有
3.左外和右外交换两个表的顺序，可以实现同样的效果。
4.一般用于查询除了交集部分的剩余的不匹配的行注意：一定要分清主从表
引入：查询男朋友不在男神表的女神名
左外连接
SELECT `name`,boyName FROM beauty
LEFT JOIN boys ON beauty.boyfriend_id=boys.id
WHERE boys.id IS NULL;右外连接
SELECT `name`,boyName FROM boys
RIGHT JOIN beauty ON boys.id=beauty.boyfriend_id
WHERE boys.id IS NULL;
-- 如果boys用左外连接
-- SELECT `name` ,boyName FROM boys
-- LEFT JOIN beauty ON boys.id=beauty.boyfriend_id
-- WHERE boys.id IS NULL;结果将是 null 因为boys是主表，id是主键，因此不可能为空
案例1：查询哪个部门没有员工
SELECT department_name ,employee_id FROM employees e
RIGHT JOIN departments d ON d.department_id=e.department_id
WHERE employee_id IS NULL;
三、全外连接
不分主从表，结果为内连接+左外+右外
交叉连接
使用99语法标准实现笛卡尔积
SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;
sql92 和sql99pk
功能：sql99支持的较多
可读性：sql99实现连接条件和筛选条件的分离，可读性较高
多表连接案例：
1.查询编号>3的女神的男朋友的信息，如果有则列出详细信息，如果没有用NULL 填充
SELECT b.`name`,bo.* FROM beauty b
LEFT JOIN boys bo ON bo.id =b.boyfriend_id
WHERE b.id>3;读题可知男朋友的信息可为Null，因此boys为从表。
2.查询哪个城市没有部门
SELECT city,department_name,d.department_id FROM locations l
LEFT JOIN departments d
ON l.location_id=d.location_id
WHERE d.department_id IS NULL;读题可知城市必须不为null，而部门可以为null，所以部门表是从表。
3.查询部门名为SAL或IT的员工信息
如果employees表为主表的话将会显示出所有员工的信息，而有些部门是没有员工的，
departments表会显示出所有部门的信息，符合题意。
SELECT * FROM employees e
RIGHT JOIN departments d ON e.department_id=d.department_id WHERE department_name IN ('SAL','IT');
2.子查询
进阶7：子查询
含义：
出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询。分类：
按子查询出现的位置：
SELECT后面：
仅仅支持标量子查询
from后面：
支持表子查询
where或having后面： ☆
标量子查询 ☆
、列子查询 ☆
、行子查询
exists后面（相关子查询） 表子查询
按结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）
表子查询（结果集一般为多行多列）
一、where或having后面：1.标量子查询（单行子查询）
2.列子查询（多行）
3.行子查询（多列多行）特点：
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询，一般搭配着单行操作符使用
>< >= <= = <>
列子查询：一般搭配着多行操作符使用
in 、any/some、all
1.标量子查询
案例一：谁的工资比abel高？
如这种简单逻辑的可以一下子写出来，但是如果遇到很绕的问题我们最好能分步骤实现。
SELECT salary,last_name FROM employees
WHERE salary>(SELECT salary FROM employees WHERE last_name='Abel');
案例二：返回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);案例三 返回公司工资最少的员工的last_name，job_id 和salary
SELECT last_name,job_id,salary FROM employees
WHERE salary=(SELECT MIN(salary) FROM employees);案例四 查询最低工资大于50号部门最低工资的部门id和其最低工资SELECT department_id,MIN(salary) FROM
employees GROUP BY department_id
HAVING MIN(salary)>(SELECT MIN(salary) FROM employees WHERE department_id=50);
2.列子查询 （多行子查询）
案例一： 返回location_id 是1400或1700的部门中的所有员工姓名
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));
案例二：返回其他部门中比job_id 为'IT_PROG'部门任一工资低的员工的员工号、姓名、job_id 以及salarySELECT employee_id,last_name,job_id,salary
FROM employees e
WHERE salary < ANY(SELECT salary
FROM employees WHERE job_id='IT_PROG')
AND job_id <>'IT_PROG';案例三：返回其他部门中比job_id 为'IT_PROG'部门所有工资都低的员工的员工号、姓名、job_id以及salary
SELECT employee_id,last_name,job_id,salary FROM employees
WHERE salary <ALL(
SELECT salary FROM employees WHERE 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 department_id,COUNT(*) FROM employees GROUP BY department_id;
#上面的查询语句是错误的，因为你从employees表中查到的是有员工的部门，有些部门没有员工是null，这里不会显示用等值连接试一下 还是不会显示出null的部门 感觉比较麻烦
SELECT d.*,COUNT(e.employee_id)
FROM departments d JOIN employees e
on d.department_id=e.department_id
GROUP BY d.department_id
;我们发现我们需要的department_id在departments表中存在但是employees表不存在 因此需要用的是
外连接！
SELECT d.*,COUNT(*) FROM departments d LEFT JOIN employees e
ON d.department_id=e.department_id
GROUP BY d.department_id;
但是这里发现有些部门没有人却计数为1 这是为什么? 经过和老师的探讨我发现这里的count(*)是对departments表的，而departments作为主表是不可能为0的，因此我们需要count的是employess表中的字段所以左外连接查询语句这样就可以
SELECT d.*,COUNT(employee_id) FROM departments d LEFT JOIN employees e
ON d.department_id=e.department_id
GROUP BY d.department_id;
用子查询SELECT d.*,(SELECT COUNT(*) FROM employees e
WHERE e.department_id=d.department_id)
FROM departments d;这类似于java中的嵌套循环 ，主查询语句是外层循环，子查询语句是内层循环，在做完每一条主查询语句后执行子查询语句，这样理解会容易些
SELECT d.*,(SELECT COUNT(*)
FROM employees e
WHERE e.department_id=d.department_id
)
FROM departments d;
查询员工号为102的部门名，部门号
SELECT e.department_id,(SELECT department_name FROM departments d
WHERE d.department_id=e.department_id
)
FROM employees e
WHERE employee_id =102
三、from后面案例一：查询每个部门的平均工资的工资等级
SELECT
ag_dep.*
FROM
(
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) ag_dep
INNER JOIN job_grade g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;
四、exits后面（相关子查询）
语法：
EXISTS (完整的查询语句)
结果：1或0
SELECT EXISTS(SELECT employee_id FROM employees WHERE salary=30000);案例一：查询有员工的部门名
SELECT department_name FROM
departments
WHERE EXISTS (
SELECT * FROM employees
WHERE departments.department_id=employees.department_id
)
案例二、 查询没有女朋友的男生信息用in
SELECT * FROM boys
WHERE boys.id NOT IN(
SELECT boyfriend_id FROM beauty
)用exists
SELECT * FROM boys
WHERE NOT EXISTS(
SELECT boyfriend_id FROM beauty
WHERE boys.id=beauty.boyfriend_id
)
子查询案例
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 查询各部门中工资比本部门平均工资高的员工的员工号、姓名和工资
方法① 嵌套循环
SELECT employee_id ,last_name,salary,department_id FROM employees e
WHERE salary >(
SELECT AVG(salary) FROM employees e2 WHERE e2.department_id=e.department_id
)
方法二② from后接表查询
SELECT employee_id ,last_name,salary,e.department_id FROM employees e
JOIN (SELECT AVG(salary) aq ,department_id FROM employees GROUP BY department_id) ag
ON ag.department_id=e.department_id
WHERE e.salary>ag.aq;4 查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
SELECT employee_id,last_name FROM employees
WHERE department_id IN(SELECT department_id FROM employees
WHERE last_name LIKE "%u%")；
5 查询在部门的location_id 为1700的部门工作的员工的员工号
SELECT employee_id,d.department_id,location_id FROM employees e
JOIN departments d ON e.department_id=d.department_id
WHERE location_id =1700；
6 查询管理者是K_ing的员工姓名和工资
SELECT e.last_name ,e.salary,e.manager_id FROM employees e
JOIN employees m ON e.manager_id=m.employee_id
WHERE m.last_name='K_ing';
7 查询工资最高的员工的姓名。要求first_name和last_name显示为一列，列名为姓.名
SELECT CONCAT(first_name,".",last_name) '姓.名' FROM employees WHERE salary=(SELECT MAX(m.salary) FROM employees m);
3.分页查询
进阶8：分页查询
应用场景：当要显示的数据一页显示不全需要分页提交sql请求语法：
SELECT 查询列表 ⑥
FROM 表 ①
[join type JOIN 表2 ②
on 连接条件
WHERE 筛选条件 ③
GROUP BY 分组字段 ④
HAVING 分组后的筛选 ⑤
ORDER BY 排序的字段 ⑦
LIMIT OFFSET,size; ⑧
OFFSET 要显示条目的起始索引（起始索引从0开始）
size 要显示的条目个数
特点：
①limit语句放在查询语句的最后
②公式：
要显示的页数 page,每页的条目数size
SELECT 查询列表 FROM 表
LIMIT (page-1)*size,size
案例一：查询前五条员工信息SELECT * FROM employees LIMIT 0,5;
SELECT * FROM employees LIMIT 5;案例二： 查询第11条--第25条
SELECT * FROM employees LIMIT 10,15;#案例三：查询有奖金的员工 信息，并且工资较高的前十名
SELECT
*
FROM
employees
WHERE
commission_pct IS NOT NULL
ORDER BY
salary DESC
LIMIT 10;
4.联合查询
进阶9：联合查询
union 联合、 合并： 将多条查询语句的结果合并成一个结果语法：
查询语句1
union
查询语句2
union
，，.（此处省略若干个union及查询语句）应用场景：
要查询的结果来自于多个表且多个表没有直接的连接关系，但查询的信息一致
特点：
1.要求多条查询语句的查询列数是一致的
2.要求多条查询语句查询每一列的类型和顺序最好一致
3.使用union时会自动去重 我们可以用 UNION ALL包含重复项
引入的案例：查询部门编号>90或邮箱包含a的员工信息
SELECT * FROM employees WHERE department_id >90 OR email LIKE '%a%'SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id>90;
429
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
