MySQL笔记（五）：多表查询

一、查询增强

对单表进行加强查询

#查询加强-- 1\使用WHERE语句  对1991.12.1后入职的员工进行查询-- 日期类型可以直接比较
SELECT * FROM empWHERE hiredate>'1991-12-01';-- 2\like 模糊查询-- %：表示0到任意字符 _:表示单个任意字符   '-- 显示首字符为s的员工姓名和工资  'S%'：S在前，以S开头，'%S'：S在后，以S结尾
SELECT ename,sal FROM empWHERE ename LIKE 'S%';-- 如何显示第三个字符为大写O的所有员工的姓名和工资
SELECT ename,sal FROM empWHERE ename LIKE '__O%';-- 如何显示没有上级的雇员的情况
SELECT * FROM empWHERE mgr IS NULL;/*不能写mgr = null*/-- 查询表结构
DESC emp;#order by 语句
-- 如何按照工资从低到高的顺序显示雇员信息
SELECT * FROM emp ORDER BY sal;-- 按照部门号升序而（部门内部）雇员工资降序排列，显示雇员信息
SELECT * FROM empORDER BY deptno ASC,sal DESC;

二、分页查询

#分页查询
-- 按雇员的id号升序取出，每页显示3条记录，请分别显示第1页，第2页，第3页
-- 第1页
SELECT * FROM empORDER BY empnoLIMIT 0,3;-- 第2页
SELECT * FROM empORDER BY empnoLIMIT 3,3;-- 第3页
SELECT * FROM empORDER BY empnoLIMIT 6,3;-- 第5页
SELECT * FROM empORDER BY empnoLIMIT 12,3;-- 公式：
-- 不能直接写公式 每页显示记录数*(第几页-1)，要算出结果
-- SELECT * FROM emp
-- 	ORDER BY empno
-- 	LIMIT 每页显示记录数*(第几页-1),每页显示记录数;-- 按雇员的empno号降序输出，每页显示5条记录，请分别显示第3页，第5页对应的sql语句
SELECT * FROM empORDER BY empno DESCLIMIT 10,5;SELECT * FROM empORDER BY empno DESCLIMIT 20,5;

三、分组增强

#分组增强
-- (1)显示每种岗位的雇员总数、平均工资
SELECT COUNT(*),AVG(sal),job FROM empGROUP BY job;SELECT * FROM emp;
-- (2) 显示雇员总数，以及获得补助的雇员数-- count（列）：如果列的值为NULL,是不会统计的	
SELECT COUNT(*),COUNT(comm) FROM empGROUP BY job;-- 统计没有获得补助的雇员数
SELECT COUNT(IF(comm IS NULL,1,NULL)) FROM empGROUP BY job;
SELECT COUNT(*),COUNT(*)-COUNT(comm) FROM empGROUP BY job;-- (3)显示管理者的总人数
SELECT COUNT(mgr) FROM emp; /*这样写会重复*/
SELECT COUNT(DISTINCT mgr) FROM emp;-- (4) 显示雇员工资的最大差额
SELECT MAX(sal)-MIN(sal) FROM emp;

四、多子句查询

-- 应用案例：估计各个部门group by 的平均工资avg,并且是大于1000的having,并且按照平均工资从高到低排序，order by 取出前两行激励limit
SELECT  deptno,AVG(sal) AS avg_sal FROM empGROUP BY deptnoHAVING avg_sal>1000ORDER BY avg_sal DESC /*只能在这里order by 在查询完了之后排序*/LIMIT 0,2;

五、多表查询

在默认情况下：当查询两个表时，规则：
1、从表1中，去除一行跟第二张表的每一条记录做拼接，返回这个结果，结果含有两张表的所有列
2、一共返回的记录数，表1的行数*表2的行数
3、这个默认的结果，为笛卡尔集
4、解决多表的关键是写出正确的过滤条件where，需要分析

SELECT ename,sal,dname,emp.deptno  /*对于两个表中都有的列，需要标注是哪个表的列*/FROM emp,deptWHERE emp.deptno=dept.deptno;

#多表查询
-- 1、显示雇员民，雇员工资（emp表）及所在部门的名字（dept表）（笛卡尔集）
SELECT * FROM emp,dept;SELECT ename,sal,dname,emp.deptno  /*对于两个表中都有的列，需要标注是哪个表的列*/FROM emp,deptWHERE emp.deptno=dept.deptno;-- 2、显示部门为10的部门名、员工名和工资
SELECT dname,ename,sal,emp.deptno FROM emp,deptWHERE emp.deptno=dept.deptno AND emp.deptno=10;SELECT * FROM salgrade;
-- 3、显示各个员工的姓名，工资、工资级别
SELECT *FROM emp,salgrade /*只有sal落在对应级别的losal和hisal之间的那一行才有效*/
SELECT ename,sal,gradeFROM emp,salgradeWHERE sal BETWEEN losal AND hisal;-- 4\显示雇员名，工资和所在部门名字，并按部门排序
SELECT ename,sal,dname,emp.deptnoFROM emp,deptWHERE emp.deptno=dept.deptnoORDER BY emp.deptno DESC;

六、自连接

自连接：指在同一张表的连接查询【将同一张表看作两张表】

#多表查询的自连接

– 显示公司员工名字和他的上级的名字
– 员工和上级名字在ename，通过mgr连接

#自连接的特点：1.一张表当程两张表使用

2.需要给表取别名 表明表别名

3.列名不明确，需要取别名

SELECT worker.ename AS ‘职员名’,boss.ename AS ‘上级名’
FROM emp worker,emp boss
WHERE worker.mgr=boss.empno;

SELECT * FROM emp;

七、子查询

(1)基础嵌套子查询

什么是子查询
子查询是指嵌入在其他aql语句中的select语句，也叫嵌套查询
单行子查询
单行子查询是指只返回一行数据的子查询语句
多行子查询
指返回多行数据的子查询，使用关键字in

#多行子查询
SELECT * FROM emp;
-- 显示与smith同一部门的所有员工
SELECT * FROM empWHERE deptno = (SELECT deptno FROM emp WHERE ename='SMITH');-- 查询和部门10的工作相同的雇员的名字、岗位、工资、部门号，但是不包含10自己
SELECT ename,job,sal,deptnoFROM empWHERE job IN (SELECT DISTINCT job FROM empWHERE deptno=10) ANDdeptno != 10; /*!= 也可以写成<>*/

（2）子查询临时表
这一部分我没有表，直接看第47集

（3）mysql_all和any

#all和any操作符
-- 显示工资比30部门的所有员工的工资高的员工的姓名、工资和部门号
SELECT ename,sal,deptnoFROM empWHERE sal>ALL(SELECT sal FROM empWHERE deptno=30);SELECT ename,sal,deptnoFROM empWHERE sal> (SELECT MAX(sal) FROM empWHERE deptno=30);-- 显示工资比30部门的其中一个员工的工资高的员工的姓名、工资和部门号SELECT ename,sal,deptnoFROM empWHERE sal> ANY(SELECT salFROM empWHERE deptno=30);SELECT ename,sal,deptnoFROM empWHERE sal> (SELECT MIN(sal)FROM empWHERE deptno=30);

(4)多列子查询
多列子查询：查询返回多个列数据的子查询语句

#多列子查询
#(字段1，字段2，...)=select 字段1，字段2，... from 。。。
-- 查询与SMITH的部门和岗位完全相同的所有雇员（不含simth)
SELECT deptno,jobFROM empWHERE ename = 'ALLEN'; 
SELECT * FROM empWHERE (deptno,job)=(SELECT deptno,jobFROM empWHERE ename = 'ALLEN')AND ename <> 'ALLEN';

八、表复制

（1）自我复制数据（蠕虫复制）
为了对某个sql语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据

#表复制CREATE TABLE my_tab01(id INT,ename VARCHAR(32),sal DOUBLE,job CHAR(32),deptno INT);DESC my_tab01
SELECT * FROM my_tab01;-- 演示如何自我复制
-- 1、先把emp表的记录复制到my_tab01
INSERT INTO my_tab01(id,ename,sal,job,deptno)SELECT empno,ename,sal,job,deptno FROM emp;-- 2\自我复制
INSERT INTO my_tab01SELECT * FROM my_tab01;SELECT COUNT(*) FROM my_tab01;-- 删除表的重复记录
-- 1、先创建一张表my_tab02
-- 2、让my_tab02有重复的记录
CREATE TABLE my_tab02 LIKE emp;  -- 这个语句 把 emp 表的结构（列），复制到my_tab02;
DESC my_tab02
SELECT * FROM my_tab02;
INSERT INTO my_tab02SELECT * FROM emp;
-- 3\去重
-- 创建临时表my_tmp,该表的结构与my_tab02相同->把my_tab02的记录通过distinct关键字处理后把记录复制到my_tmp->清除my_tab02的记录->把my_tmp复制到my_tab02->drop my_tmp
CREATE TABLE my_tmp LIKE my_tab02;
INSERT INTO my_tmpSELECT	DISTINCT * FROM my_tab02;
DELETE FROM my_tab02;
INSERT INTO my_tab02 SELECT * FROM my_tmp;
DROP TABLE my_tmp;
SELECT * FROM my_tab02;

九、合并查询

合并多个select语句
union all
：取得两个结果集的并集，不会取消重复行

#合并语句
SELECT ename,sal,job FROM emp WHERE sal>2500;SELECT ename,sal,job FROM emp WHERE job='manager'#union all 合并不去重
SELECT ename,sal,job FROM emp WHERE sal>2500 UNION ALLSELECT ename,sal,job FROM emp WHERE job='manager'#union :合并去重
SELECT ename,sal,job FROM emp WHERE sal>2500 UNION SELECT ename,sal,job FROM emp WHERE job='manager'

十、表外连接

#表外连接SELECT	dname,ename,jobFROM emp,deptWHERE emp.deptno=dept.deptnoORDER BY dname;-- 创建stuCREATE TABLE stu3(id INT,tname VARCHAR(32));INSERT INTO stu3 VALUES(1,'jack'),(2,'tom'),(3,'kitty'),(4,'nono');SELECT * FROM stu3;-- 创建exam
CREATE TABLE exam(id INT,grade INT);
INSERT INTO exam VALUES(1,56),(2,76),(11,8);-- 不小心添加了两次，联系一下去重
CREATE TABLE tmpex LIKE exam;
INSERT INTO tmpex  SELECT DISTINCT * FROM exam;
DELETE FROM exam;
INSERT INTO exam SELECT * FROM tmpex;
DROP TABLE tmpex;-- 使用左连接-- 显示所有人的成绩，如果没有成绩，也要显示id和姓名
SELECT tname,stu.id,gradeFROM stu3,examWHERE stu3.id=exam.id;-- 改成左外连接 
-- select .. from 表1 left join 表2 on 条件【表1：左表，表2：右表】
SELECT tname,stu3.id,gradeFROM stu3 LEFT JOIN examON stu3.id=exam.id;-- 右外连接
-- select .. from 表1 right join 表2 on 条件【表1：左表，表2：右表】
SELECT tname,stu3.id,grade 
SELECT tname,stu3.id,gradeFROM stu3 RIGHT JOIN examON stu3.id=exam.id;-- 列出部门名称和这些部门的员工信息（名字，和工作），同时列出没有员工共的部门名
-- 1、使用左外连接
SELECT ename,job,dnameFROM dept LEFT JOIN empON dept.deptno=emp.deptno;-- 2、使用右外连接
SELECT ename,job,dnameFROM emp RIGHT JOIN deptON emp.deptno=dept.deptno;