测试数据
CREATE TABLE dept( /*部门表*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
dname VARCHAR(20) NOT NULL DEFAULT "",
loc VARCHAR(13) NOT NULL DEFAULT ""
);
INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK'), (20, 'RESEARCH', 'DALLAS'), (30, 'SALES', 'CHICAGO'), (40, 'OPERATIONS', 'BOSTON');
#创建表EMP雇员
CREATE TABLE emp
(empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
mgr MEDIUMINT UNSIGNED ,/*上级编号*/
hiredate DATE NOT NULL,/*入职时间*/
sal DECIMAL(7,2) NOT NULL,/*薪水*/
comm DECIMAL(7,2) ,/*红利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
);
INSERT INTO emp VALUES(7369, 'SMITH', 'CLERK', 7902, '1990-12-17', 800.00,NULL , 20),
(7499, 'ALLEN', 'SALESMAN', 7698, '1991-2-20', 1600.00, 300.00, 30),
(7521, 'WARD', 'SALESMAN', 7698, '1991-2-22', 1250.00, 500.00, 30),
(7566, 'JONES', 'MANAGER', 7839, '1991-4-2', 2975.00,NULL,20),
(7654, 'MARTIN', 'SALESMAN', 7698, '1991-9-28',1250.00,1400.00,30),
(7698, 'BLAKE','MANAGER', 7839,'1991-5-1', 2850.00,NULL,30),
(7782, 'CLARK','MANAGER', 7839, '1991-6-9',2450.00,NULL,10),
(7788, 'SCOTT','ANALYST',7566, '1997-4-19',3000.00,NULL,20),
(7839, 'KING','PRESIDENT',NULL,'1991-11-17',5000.00,NULL,10),
(7844, 'TURNER', 'SALESMAN',7698, '1991-9-8', 1500.00, NULL,30),
(7900, 'JAMES','CLERK',7698, '1991-12-3',950.00,NULL,30),
(7902, 'FORD', 'ANALYST',7566,'1991-12-3',3000.00, NULL,20),
(7934,'MILLER','CLERK',7782,'1992-1-23', 1300.00, NULL,10);
链接查询
- 链接查询：查询每个员工和其部门名 （笛卡尔积现象）
select *
from emp,dept
order by emp.empno;
查询结果图示：
emp表有14条记录，dept表有14条记录，我们是查出，每个员工的信息和部门名，而一个员工只能属于一个部门，所以正常来说，应该返回14条记录。而这里返回了56条记录，是因为我们没有加链接条件，发生了笛卡尔积现象，就是说，将第一张表的每一条记录都与第二章表的每一条记录链接了一遍，如果第一条记录，smith的部门号是20，而这条记录却与部门号10，20，30，40的都链接了一遍。
- 通过等值链接条件过滤记录 （同条记录根据条件过滤）
此时可以通过添加链接条件来解决，多余的记录问题。
select *
from emp,dept
where emp.deptno =dept.deptno
order by emp.empno;
此时的结果集就正常了。该链接条件表示 emp表的deptno字段的值要等于dept表的deptno字段的值，才会被筛选出来。
例如未加筛选条件时的第一条记录emp的deptno是20，而dept表的deptno是40，那此时这条记录就没了，再看第二条记录，emp表的deptno是20，dept表的deptno是30，此时这条记录也没有，再看第三条，此时emp表的deptno是20，而dept表的deptno也是20，满足我们的条件，那么该条记录就会被返回，以此类推，就会将emp.deptno = dept.deptno的返回。
union 与 union all
- union all 将多条sql语句的结果合并
比如查询员工号大于7600或者工资大于2000的
一条sql可以这样写
select *
from emp
where sal > 2000 or empno > 7600
共有17条记录
如果使用union all 就可以将这个 or 条件分成两条sql
select *
from emp
where empno > 7600
union
select *
from emp
where sal > 2000
此时 查询效果和上面的sql是一样的。
- union all
union all 和 union不同之处就是union all不会对结果集去重。
外连接查询
- 为什么会有外连接？
拿上面员工和部门案例来说，当我查询每个员工的部门的时候，此时只能查出有部门的员工信息和部门信息。如果某个员工的deptno为null，那此时就不会查出这条记录。如果此时想查询每个员工和他的部门信息，同时也将没有员工的信息查询出来。那就需要外连接。
select e.*,d.*
from emp e
left join dept d on e.deptno = d.deptno
使用外连接，就可以将有部门的员工信息和没有部门的员工信息都能查询出来。那些没有部门的员工的部门表的字段都用null来填充。
- 基于此，还可以查询出，哪些部门没有员工？
此时部门表成了主表，员工表成为从表
select d.dname
from dept d
left join emp e on e.deptno = d.deptno
where e.empno is null
当联查后，条件是从表的主键为null，就可以查询出没有员工的部门，因为如果从表没有和主表的条件连接，所有字段用null来填充。