26.多表查询

1.笛卡尔集

创建俩表：

-- 创建部门表（dept）
use mysql_learn
CREATE TABLE dept (
    deptno INT PRIMARY KEY,       
    dname VARCHAR(50) NOT NULL,   
    loc VARCHAR(50)      
);

-- 创建员工表（emp）
CREATE TABLE emp (
    empno INT PRIMARY KEY,        
    ename VARCHAR(50) NOT NULL,  
    job VARCHAR(50),             
    mgr INT,                       
    hiredate DATE,                
    sal DECIMAL(10,2) NOT NULL,   
    comm DECIMAL(10,2),           
    deptno INT
);

插入一些示例数据：

-- 向部门表插入数据
INSERT INTO dept (deptno, dname, loc) VALUES
(10, 'ACCOUNTING', 'NEW YORK'),
(20, 'RESEARCH', 'DALLAS'),
(30, 'SALES', 'CHICAGO'),
(40, 'OPERATIONS', 'BOSTON');

-- 向员工表插入数据
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES
(7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800.00, NULL, 20),
(7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600.00, 300.00, 30),
(7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250.00, 500.00, 30),
(7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975.00, NULL, 20),
(7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250.00, 1400.00, 30),
(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850.00, NULL, 30),
(7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450.00, NULL, 10),
(7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09', 3000.00, NULL, 20),
(7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000.00, NULL, 10),
(7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500.00, 0.00, 30),
(7876, 'ADAMS', 'CLERK', 7788, '1983-01-12', 1100.00, NULL, 20),
(7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950.00, NULL, 30),
(7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000.00, NULL, 20),
(7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300.00, NULL, 10);  

看看结果：

部门表，里面有4条记录。

雇员表，里面有14条记录。

假设现在有个需求：打印某个员工的工资条。

我们有两张表：1️⃣雇员表，2️⃣部门表。

当我们根据前面所学习的知识直接对两个表同时查询时会出现以下情况：

SELECT * from dept,emp

可以看到，我们直接将俩表连起来一起查出来的记录有56条之多。

仔细探查我们会发现，这些结果实际上就是第一个表中的四条记录和第二张表的一条记录进行拼接而成的。也就是4*14=56条记录。这就是笛卡尔集。

所以这些直接进行查询获得的信息是有冗余的信息，为了得到真正有意义的信息，我们需要做的就是过滤。

2.笛卡尔集的解决

为了过滤出有意义的信息，我们直接在where子句中做即可。

SELECT * from dept,emp
	where dept.deptno = emp.deptno

这样我们就解决了笛卡尔集，得到了真正有意义的14条记录。

对sql语句稍作修改，也就可以得到每个员工的工资条了。

SELECT ename as '姓名',job as '工作',sal as '薪水',dname as '部门',deptno as '部门号'
	from dept,emp
	where dept.deptno = emp.deptno

我们会发现这样会报错。

SQL 错误 [1052] [23000]: Column 'deptno' in field list is ambiguous

因为我们进行的是多表查询，deptno这个字段在俩表中同时存在，直接写会有歧义的。

我们对sql再次进行修改：

SELECT ename as '姓名',job as '工作',sal as '薪水',dname as '部门',emp.deptno as '部门号'
	from dept,emp
	where dept.deptno = emp.deptno

得到了我们想要的结果了。

注意：多表查询的条件不能少于表的个数减一，否则就会出现笛卡尔集。

3.练习

再创建一个工资等级表。

CREATE TABLE grade_salary (
    grade INT,
    losal DECIMAL(10, 2),
    hisal DECIMAL(10, 2)
);

INSERT INTO grade_salary (grade, losal, hisal)
VALUES 
(1, 1000.00, 1200.00),
(2, 1201.00, 2000.00),
(3, 2001.00, 3000.00),
(4, 3001.00, 9999.00),
(5, 3001.00, 9999.00);

（1）显示各个员工的姓名，工资，工资等级

SELECT ename,sal,grade
	from emp,grade_salary

很显然，直接查俩个表会出现笛卡尔集。

我们做一下过滤：可以直观的分析出雇员表emp和工资级别表grade_salary之间的联系就是员工工资在工资级别表中的那一个级别区间，也即员工工资和工资级别区间相对应的记录就是我们要查询的结果。

基于此，可以使用between and 语句来判断。

SELECT ename,sal,grade
	from emp,grade_salary gs
	where sal between losal and hisal;

（2）显示员工名字，工资，所在部门名字，并按部门号降序排列

显然我们这次需要查询，emp和dept这两张表，为了防止出现笛卡尔集，做一下过滤，最后用order by做一下排序。

SELECT ename,sal,dname
	from emp e,dept d
	where e.deptno = d.deptno
	order by d.deptno DESC ;

、

再看一眼，按部门号降序排序也做好了。