多表查询

合并结果集(了解)

注意：被合并的表中，列数必须相同（类型可以不用相同）
语法：
UNION，去除重复行
UNION ALL，不去除重复行
注意：这里合并的是查询的结果集而不是合并表本身！！！！

CREATE TABLE a(
	id INT,
	`name` VARCHAR(20),
	age INT
);

CREATE TABLE b(
	id INT,
	`name` VARCHAR(20)
);

SELECT * FROM a  -- 3列
UNION
SELECT * FROM b  -- 2列

SELECT id,NAME FROM a  -- 2列
UNION  -- 去除重复的列
SELECT id,NAME FROM b  -- 2列

SELECT id,NAME FROM a  -- 2列
UNION ALL  -- 不去除重复的列，重复的也显示出来
SELECT id,NAME FROM b  -- 2列

连接查询（横向上的连接查询）

分类
内连接（符合连接条件的查询）
外连接（部分或全部不符合连接条件及符合连接条件的查询）
> 左外连接
> 右外连接
> 全外连接(MySQL不支持,但有解决方案)
自然连接(属于内连接)

内连接

注意：内连接查询出的所有记录都满足条件

• 方言：SELECT * FROM 表1 别名1, 表2 别名2 WHERE 别名1.xx=别名2.xx
• 标准：SELECT * FROM 表1 别名1 INNER JOIN 表2 别名2 ON 别名1.xx=别名2.xx
• 自然：SELECT * FROM 表1 别名1 NATURAL JOIN 表2 别名2
  自然连接会自动的在两张表中找列名一样的列做条件匹配

-- 下面的这种内连接的写法是mysql的方言
-- oracle数据库是用不了的
SELECT *  FROM emp e,dept d WHERE e.deptno=d.deptno

-- sql标准有标准的内连接写法
SELECT * FROM emp e INNER JOIN dept d ON e.deptno=d.deptno

外连接

外连接（部分或全部不符合连接条件及符合连接条件的查询）

左外：
SELECT * FROM 表1 别名1 LEFT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx
左表记录无论是否满足条件都会查询出来，而右表只有满足条件才能出来。左表中不满足条件的记录，右表部分都为NULL

-- 左外连接
SELECT * FROM emp LEFT OUTER JOIN dept ON emp.deptno=dept.deptno
-- outer是可以省略掉的
SELECT * FROM emp LEFT JOIN dept ON emp.deptno=dept.deptno

左外自然：
SELECT * FROM 表1 别名1 NATURAL LEFT OUTER JOIN 表2 别名2

右外：
SELECT * FROM 表1 别名1 RIGHT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx
右表记录无论是否满足条件都会查询出来，而左表只有满足条件才能出来。右表不满足条件的记录，其左表部分都为NULL

SELECT * FROM emp RIGHT OUTER JOIN dept ON emp.deptno=dept.deptno
-- outer是可以省略掉的
SELECT * FROM emp RIGHT JOIN dept ON emp.deptno=dept.deptno

右外自然：
SELECT * FROM 表1 别名1 NATURAL RIGHT OUTER JOIN 表2 别名2

全外链接（两张表都是主表）： – Mysql不支持
SELECT * FROM 表1 别名1 FULL OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx

SELECT * FROM emp FULL OUTER JOIN dept ON emp.deptno=dept.deptno

可以使用UNION来完成全链接（左外和右外合并）

SELECT * FROM emp LEFT OUTER JOIN dept ON emp.deptno=dept.deptno
UNION
SELECT * FROM emp RIGHT OUTER JOIN dept ON emp.deptno=dept.deptno

子查询

SELECT …SELECT…
嵌套查询！！！就是查询里面还有查询！！！（select语句中包含select语句）

子查询出现的位置：
from后作为表存在 必须给表起别名
where后作为条件存在

子查询作为表存在

select * from （select ……）别名;

SELECT * FROM (SELECT empno FROM emp) e

子查询的结果作为条件(where)

• 单行单列：SELECT * FROM 表1 别名1 WHERE 列1 [=、>、<、>=、<=、!=] (SELECT 列 FROM 表2 别名2 WHERE 条件)
  例子：查询工资最低员工的详细信息

  --SELECT * FROM emp WHERE sal=MIN(sal)  -- Invalid use of group function
  -- MIN(sal) 只能在select后面用
  SELECT * FROM emp WHERE sal = (SELECT MIN(sal) FROM emp)
  

• 多行单列：SELECT * FROM 表1 别名1 WHERE 列1 [IN, ALL, ANY] (SELECT 列 FROM 表2 别名2 WHERE 条件)
  例子1：查询工种和30部门员工工种一样的员工信息

  SELECT * FROM emp WHERE job IN(SELECT DISTINCT job FROM emp WHERE deptno=30) AND deptno!=30
  

  例子2：查询工资大于20部门所有员工工资的员工的详细信息

  SELECT * FROM emp WHERE sal > ALL(SELECT sal FROM emp WHERE deptno=20)
  

  例子3：查询工资大于20部门任意员工工资的员工的详细信息

  SELECT * FROM emp WHERE sal > ANY(SELECT sal FROM emp WHERE deptno=20)
  

• 单行多列：SELECT * FROM 表1 别名1 WHERE (列1,列2) IN (SELECT 列1, 列2 FROM 表2 别名2 WHERE 条件)
  例子：查询工种及部门和MARTIN完全一样的人。

  SELECT * FROM emp WHERE (job,deptno) IN (SELECT job,deptno FROM emp WHERE ename='martin')
  

• 多行多列：SELECT * FROM 表1 别名1 , (SELECT …) 别名2 WHERE 条件
  例子：查询工种及部门和MARTIN、SMITH完全一样的人。

  WHERE (job,deptno) IN (SELECT job,deptno FROM emp WHERE ename='martin' OR ename='smith')
  

案例

部门表
CREATE TABLE dept(
	deptno		INT primary key,
	dname		varchar(14),
	loc		varchar(13)
);

INSERT INTO dept values(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept values(20, 'RESEARCH', 'DALLAS');
INSERT INTO dept values(30, 'SALES', 'CHICAGO');
INSERT INTO dept values(40, 'OPERATIONS', 'BOSTON');

员工表
CREATE TABLE emp(
	empno		INT primary key,
	ename		VARCHAR(50),
	job		VARCHAR(50),
	mgr		INT,
	hiredate	DATE,
	sal		DECIMAL(7,2),
	comm		DECIMAL(7,2),
	deptno		INT,
    -- 添加外键约束
	CONSTRAINT fk_dept_emp FOREIGN KEY(deptno) REFERENCES dept(deptno)
) ;
INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

– 1.查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。
SELECT dept.deptno,dept.dname,dept.loc,COUNT(*) 人数
FROM emp,dept
WHERE emp.deptno=dept.deptno GROUP BY emp.deptno

– 列出薪资比SMITH高的所有员工。
SELECT * FROM emp WHERE sal >= (SELECT sal FROM emp WHERE ename = ‘smith’)

– 列出所有员工的姓名及其直接上级的姓名。
SELECT * FROM emp e,(SELECT * FROM emp) m WHERE e.mgr=m.empno