Mysql的链接查询、union查询、外连接查询

左手的ㄟ右手 2023-10-11 16:23 45阅读 0赞

### 测试数据 ###

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;

查询结果图示：

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBARGFuY2VEb25rZXk_size_20_color_FFFFFF_t_70_g_se_x_16]

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;

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBARGFuY2VEb25rZXk_size_20_color_FFFFFF_t_70_g_se_x_16 1]  
此时的结果集就正常了。该链接条件表示 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的返回。

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBARGFuY2VEb25rZXk_size_20_color_FFFFFF_t_70_g_se_x_16 2]

### union 与 union all ###

*  union all 将多条sql语句的结果合并  
    比如查询员工号大于7600或者工资大于2000的  
    一条sql可以这样写

select *
    from emp
    where sal > 2000 or empno > 7600

共有17条记录  
![在这里插入图片描述][055a92f0b58c4829a543b03da50be6b8.png]如果使用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来填充。

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBARGFuY2VEb25rZXk_size_20_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/48e18291ef1a4e3a8b385eb7d13193f3.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBARGFuY2VEb25rZXk=,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBARGFuY2VEb25rZXk_size_20_color_FFFFFF_t_70_g_se_x_16 1]: https://img-blog.csdnimg.cn/22ca05b41b6c4009adb58d1f69bfe8af.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBARGFuY2VEb25rZXk=,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBARGFuY2VEb25rZXk_size_20_color_FFFFFF_t_70_g_se_x_16 2]: https://img-blog.csdnimg.cn/847d21298822490c910c691060d90582.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBARGFuY2VEb25rZXk=,size_20,color_FFFFFF,t_70,g_se,x_16
[055a92f0b58c4829a543b03da50be6b8.png]: https://img-blog.csdnimg.cn/055a92f0b58c4829a543b03da50be6b8.png