目录
第一章 多表查询
1.为什么要多表查询
减少冗余字段,提高IO交互效率,并发查询
2.多表查询
1.出现笛卡尔积错误:由于省略了多个表的连接条件(关联条件);连接条件无效;表中所有行互相连接
2.使用where 加入有效的连接条件select … from … where table1.column =table2.column
SELECT emp_id,dept_name
FROM emps1,emps2
#两个表的查询条件
WHERE emps1.dept_id = emps2.dept_id
3.如果查询语句中出现多个表都存在的字段,必须指明此字段所在的表;
4.建议多表查询时,每个字段前都指明其所在的表
SELECT emps1.emp_id,emps2.dept_name
FROM emps1,emps2
#两个表的查询条件
WHERE emps1.dept_id = emps2.dept_id
5.在where中给表起别名,在select和where中使用表的别名;一旦使用别名必须都使用别名
SELECT emps.emp_id,emps2.dept_id
FROM emps1 e1,emps2 e2
WHERE e1.dept_id = e2.dept_id;
6.如果有n个表要实现多表查询,则需要至少n-1个连接条件
3.多表查询的分类
1.等值连接:非等值连接
SELECT emp_id
FROM emps
WHERE salay >= 6000 AND salary <= 8000;
2.自连接:非自连接
SELECT emp_id
FROM emps emps1,emps emps2
WHERE emps1.manager_id = emps2.manager_id;
3.内连接:外连接
内连接:结果集中不包含不匹配连接关系的行(无特殊要求,使用的都是内连接)
只返回两个表中匹配的行,其他不匹配的行将被排除
外连接:结果集中包含不匹配连接关系的行
左外连接(LEFT JOIN)返回左表中的所有行以及右表中满足连接条件的行,如果右表中没有匹配的行,则右表的结果为NULL。右外连接(RIGHT JOIN)则相反,返回右表中的所有行以及左表中满足连接条件的行,如果左表中没有匹配的行,则左表的结果为NULL
代码示例:
# 3.选择所有有奖金的员工的 last_name , department_name , location_id , city
SELECT e.last_name,d.department_name,d.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.SQL92实现内连接:之前都是SQL99实现内连接
5.外连接分类:左外连接,右外连接,满外连接
6.所有的…信息 都是外连接
4.常用的sql标准
最重要的SQL标准就是SQL92和SQL99
5.外连接
1.SQL92使用+做外连接,mysql不支持
SQL99使用join on,mysql支持
2.SQL99语法实现外连接
#查询所有员工的name信息
#左外连接
SELECT name
FROM emps e LEFT JOIN depts d
ON e.dept_id = d.dept_id
#右外连接
SELECT name
FROM emps e RIGHT JOIN depts d
ON e.dept_id = d.dept_id
6.SQL七种joins实现
1.七图例
实现代码:
#七种Joins实现
SELECT * FROM employees;
SELECT * FROM departments;
# 内连接
SELECT employee_id,department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;
# 左上图:左外连接
SELECT last_name,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 e.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.employee_id IS NULL;
# 左下图:满外连接
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.employee_id IS NULL;
SELECT employee_id,department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE e.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;
# 右下图
SELECT employee_id,department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE e.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.employee_id IS NULL;
7.union关键字
1.union操作符:返回两个结果并集,去除重复记录
2.union all操作符:返回两个查询结果的并集,对于两个结果集的重复部分,不去重;
3.开发中通常使用union all,不需要去重,效率更高
9.注意
要控制表的数量,多表连接就像嵌套for循环一样,非常消耗资源,因此不要连接不必要的表。超过三个表禁止join
第二章 子查询
1.子查询是什么
概念:可以理解为嵌套查询,查询中可以分为主查询和子查询
代码示例:
SELECT employee_id,last_name
FROM employees
WHERE department_id IN (
SELECT department_id
FROM employees
WHERE last_name LIKE '%u%'
);
2.基本使用
1.若分为两层:外查询(或主查询)、内查询(或子查询)
2.子查询在主查询之前执行完成,子查询的结果被主查询使用
3.注意:
1. 子查询要包含在括号内;
2. 将子查询放在比较条件的右侧;
3. 单行操作符对应单行子查询,多行操作符对应多行子查询
3.子查询的分类
1.单行子查询-多行子查询:看子查询返回多少记录决定
2.相关子查询-不相关子查询:子查询是否被执行多次
4.单行子查询
0.子查询只返回一条记录
1.单行操作符:
2.子查询中的空值情况:不报错,无值出现
3.非法使用子查询:使用单行操作符和多行输出记录相操作
5.多行子查询
1.子查询返回了多条记录
2.多行操作比较符
3.mysql中聚合函数不能嵌套使用
4.查询为空值:返回空
6.相关子查询
1.不包含子查询时的顺序
2.包含子查询时的顺序在select中,除了group by和limit之外,其他位置都可以声明子查询
3.exist 和 not exist关键字
exist:
not exist:与exist相反
注意:一般用 IN 都可以改写为exists ;或者改为 = any
7.子查询的编写技巧
1.从里往外写
2.从外往里写如何选择?
如果子查询相对较简单,建议从外往里写; 如果子查询结构较复杂,建议从里往外写
如果是相关子查询,通常是从外往里写
8.可以使用自连接,也可以使用子查询,用哪种方式好?
答:自连接方式好;因为在DBMS的处理过程中,对于自连接的处理速度要比子查询快得多,而大部分DBMS都对自连接处理进行了优化。