Mysql-多表查询(!!!)
in PHP with 0 comment

Mysql-多表查询(!!!)

in PHP with 0 comment

Mysql-多表查询(!!!)

说明

Mysql 多表查询是我们日常工作中最长用到的技术点,需要我们熟练掌握,以部门员工两个表给大家讲一下这里边的相关技巧。


部门,员工表

-- 部门表
create table dept(
    deptno int primary key auto_increment, -- 部门编号
    dname varchar(14) ,   -- 部门名字
    loc varchar(13)   -- 地址
) ;
-- 员工表
create table emp(
    empno int primary key auto_increment,-- 员工编号
    ename varchar(10), -- 员工姓名                                      -
    job varchar(9), -- 岗位
    mgr int,     -- 直接领导编号
    hiredate date, -- 雇佣日期,入职日期
    sal int, -- 薪水
    comm int,  -- 提成
    deptno int not null, -- 部门编号
    foreign key (deptno) references dept(deptno)
);
insert into dept values(10,'财务部','北京');
insert into dept values(20,'研发部','上海');
insert into dept values(30,'销售部','广州');
insert into dept values(40,'行政部','深圳');
insert into emp values(7369,'刘一','职员',7902,'1980-12-17',800,null,20);
insert into emp values(7499,'陈二','推销员',7698,'1981-02-20',1600,300,30);
insert into emp values(7521,'张三','推销员',7698,'1981-02-22',1250,500,30);
insert into emp values(7566,'李四','经理',7839,'1981-04-02',2975,null,20);
insert into emp values(7654,'王五','推销员',7698,'1981-09-28',1250,1400,30);
insert into emp values(7698,'赵六','经理',7839,'1981-05-01',2850,null,30);
insert into emp values(7782,'孙七','经理',7839,'1981-06-09',2450,null,10);
insert into emp values(7788,'周八','分析师',7566,'1987-06-13',3000,null,20);
insert into emp values(7839,'吴九','总裁',null,'1981-11-17',5000,null,10);
insert into emp values(7844,'郑十','推销员',7698,'1981-09-08',1500,0,30);
insert into emp values(7876,'郭十一','职员',7788,'1987-06-13',1100,null,20);
insert into emp values(7900,'钱多多','职员',7698,'1981-12-03',950,null,30);
insert into emp values(7902,'大锦鲤','分析师',7566,'1981-12-03',3000,null,20);
insert into emp values(7934,'木有钱','职员',7782,'1983-01-23',1300,null,10);
mysql> select * from dept;
+--------+-----------+--------+
| deptno | dname     | loc    |
+--------+-----------+--------+
|     10 | 财务部    | 北京   |
|     20 | 研发部    | 上海   |
|     30 | 销售部    | 广州   |
|     40 | 行政部    | 深圳   |
+--------+-----------+--------+
4 rows in set (0.00 sec)

mysql> select * from emp;
+-------+-----------+-----------+------+------------+------+------+--------+
| empno | ename     | job       | mgr  | hiredate   | sal  | comm | deptno |
+-------+-----------+-----------+------+------------+------+------+--------+
|  7369 | 刘一      | 职员      | 7902 | 1980-12-17 |  800 | NULL |     20 |
|  7499 | 陈二      | 推销员    | 7698 | 1981-02-20 | 1600 |  300 |     30 |
|  7521 | 张三      | 推销员    | 7698 | 1981-02-22 | 1250 |  500 |     30 |
|  7566 | 李四      | 经理      | 7839 | 1981-04-02 | 2975 | NULL |     20 |
|  7654 | 王五      | 推销员    | 7698 | 1981-09-28 | 1250 | 1400 |     30 |
|  7698 | 赵六      | 经理      | 7839 | 1981-05-01 | 2850 | NULL |     30 |
|  7782 | 孙七      | 经理      | 7839 | 1981-06-09 | 2450 | NULL |     10 |
|  7788 | 周八      | 分析师    | 7566 | 1987-06-13 | 3000 | NULL |     20 |
|  7839 | 吴九      | 总裁      | NULL | 1981-11-17 | 5000 | NULL |     10 |
|  7844 | 郑十      | 推销员    | 7698 | 1981-09-08 | 1500 |    0 |     30 |
|  7876 | 郭十一    | 职员      | 7788 | 1987-06-13 | 1100 | NULL |     20 |
|  7900 | 钱多多    | 职员      | 7698 | 1981-12-03 |  950 | NULL |     30 |
|  7902 | 大锦鲤    | 分析师    | 7566 | 1981-12-03 | 3000 | NULL |     20 |
|  7934 | 木有钱    | 职员      | 7782 | 1983-01-23 | 1300 | NULL |     10 |
+-------+-----------+-----------+------+------------+------+------+--------+
14 rows in set (0.00 sec)

技术点介绍

介绍

多表查询是指基于两个和两个以上的表或是视图的查询.在实际应用中,查询单个表可能不能满足你的需求,就要用到多表联查。

笛卡尔积

当我们对多表进行联查时,我们就会得到的 表1记录数 * 表2的记录数

mysql> select * from emp,dept;
+-------+-----------+-----------+------+------------+------+------+--------+--------+-----------+--------+
| empno | ename     | job       | mgr  | hiredate   | sal  | comm | deptno | deptno | dname     | loc    |
+-------+-----------+-----------+------+------------+------+------+--------+--------+-----------+--------+
|  7369 | 刘一      | 职员      | 7902 | 1980-12-17 |  800 | NULL |     20 |     10 | 财务部    | 北京   |
|  7369 | 刘一      | 职员      | 7902 | 1980-12-17 |  800 | NULL |     20 |     20 | 研发部    | 上海   |
|  7369 | 刘一      | 职员      | 7902 | 1980-12-17 |  800 | NULL |     20 |     30 | 销售部    | 广州   |
....
56 rows in set (0.00 sec)
自连接

自连接是指在同一张表的连接查询

例1:查询刘一的上司的信息
分析:所有的员工都在emp这张表里,领导编号存在于mgr字段中
mysql> select * from emp where empno=(select mgr from emp where ename="刘一");
+-------+-----------+-----------+------+------------+------+------+--------+
| empno | ename     | job       | mgr  | hiredate   | sal  | comm | deptno |
+-------+-----------+-----------+------+------------+------+------+--------+
|  7902 | 大锦鲤    | 分析师    | 7566 | 1981-12-03 | 3000 | NULL |     20 |
+-------+-----------+-----------+------+------------+------+------+--------+
1 row in set (0.00 sec)

例2:自己跟上司在同一张表时,如何显示自己的信息及上司的名字呢?
分析:显然这种情况下一张表是无法处理的,可以将emp看成两张表,那我们的语句就可以这么写。
mysql> select a.*,b.ename from emp a,emp b where a.mgr=b.empno;
+-------+-----------+-----------+------+------------+------+------+--------+-----------+
| empno | ename     | job       | mgr  | hiredate   | sal  | comm | deptno | ename     |
+-------+-----------+-----------+------+------------+------+------+--------+-----------+
|  7369 | 刘一      | 职员      | 7902 | 1980-12-17 |  800 | NULL |     20 | 大锦鲤    |
|  7499 | 陈二      | 推销员    | 7698 | 1981-02-20 | 1600 |  300 |     30 | 赵六      |
|  7521 | 张三      | 推销员    | 7698 | 1981-02-22 | 1250 |  500 |     30 | 赵六      |
|  7566 | 李四      | 经理      | 7839 | 1981-04-02 | 2975 | NULL |     20 | 吴九      |
|  7654 | 王五      | 推销员    | 7698 | 1981-09-28 | 1250 | 1400 |     30 | 赵六      |
|  7698 | 赵六      | 经理      | 7839 | 1981-05-01 | 2850 | NULL |     30 | 吴九      |
|  7782 | 孙七      | 经理      | 7839 | 1981-06-09 | 2450 | NULL |     10 | 吴九      |
|  7788 | 周八      | 分析师    | 7566 | 1987-06-13 | 3000 | NULL |     20 | 李四      |
|  7844 | 郑十      | 推销员    | 7698 | 1981-09-08 | 1500 |    0 |     30 | 赵六      |
|  7876 | 郭十一    | 职员      | 7788 | 1987-06-13 | 1100 | NULL |     20 | 周八      |
|  7900 | 钱多多    | 职员      | 7698 | 1981-12-03 |  950 | NULL |     30 | 赵六      |
|  7902 | 大锦鲤    | 分析师    | 7566 | 1981-12-03 | 3000 | NULL |     20 | 李四      |
|  7934 | 木有钱    | 职员      | 7782 | 1983-01-23 | 1300 | NULL |     10 | 孙七      |
+-------+-----------+-----------+------+------------+------+------+--------+-----------+
13 rows in set (0.00 sec)
注:总裁是没有在这个表里的,因为总裁是没有领导的,所以要注意。
子查询

子查询是指嵌入在其它sql查询语句中的select语句,也叫嵌套查询
1.返回条件是单数据(子查询出来是单数据)

 例:查询跟刘一同样职位的人
 mysql>  select ename,job from emp where job =(select job from emp where ename='刘一');
+-----------+--------+
| ename     | job    |
+-----------+--------+
| 刘一      | 职员   |
| 郭十一    | 职员   |
| 钱多多    | 职员   |
| 木有钱    | 职员   |
+-----------+--------+
4 rows in set (0.01 sec)

2.返回条件是多数据(子查询出来是单数据)

1.查询出所有是领导的人的信息
mysql> select * from emp where empno in(select mgr from emp where mgr is not null group by mgr);
+-------+-----------+-----------+------+------------+------+------+--------+
| empno | ename     | job       | mgr  | hiredate   | sal  | comm | deptno |
+-------+-----------+-----------+------+------------+------+------+--------+
|  7902 | 大锦鲤    | 分析师    | 7566 | 1981-12-03 | 3000 | NULL |     20 |
|  7698 | 赵六      | 经理      | 7839 | 1981-05-01 | 2850 | NULL |     30 |
|  7839 | 吴九      | 总裁      | NULL | 1981-11-17 | 5000 | NULL |     10 |
|  7566 | 李四      | 经理      | 7839 | 1981-04-02 | 2975 | NULL |     20 |
|  7788 | 周八      | 分析师    | 7566 | 1987-06-13 | 3000 | NULL |     20 |
|  7782 | 孙七      | 经理      | 7839 | 1981-06-09 | 2450 | NULL |     10 |
+-------+-----------+-----------+------+------------+------+------+--------+
6 rows in set (0.00 sec)
注:最高领导是没有领导的,所以group by的时候会查询出null,要用is not null 处理一下。
多列子查询

如果我们的一个子查询,返回的结果是多列的,我们就把这个子查询叫做多列子查询.

1.查询跟刘一同一部门统一领导的人
mysql> select ename,job from emp where(job,mgr)=(select job,mgr from emp where ename='陈二');
+--------+-----------+
| ename  | job       |
+--------+-----------+
| 陈二   | 推销员    |
| 张三   | 推销员    |
| 王五   | 推销员    |
| 郑十   | 推销员    |
+--------+-----------+
4 rows in set (0.00 sec)
多列子查询

如果我们的子查询他是在,from子句中出现的,则我们就叫from子句的子查询.

1.查询出比自己部门平均工资高的员工
select job,avg(sal) from emp group by job;
mysql> select EA.ename,EA.job,EA.sal,EB.bsal from emp EA,(select job,avg(sal) as bsal from emp group by job) EB where EA.job=EB.job and EA.sal<EB.bsal;
+-----------+-----------+------+-----------+
| ename     | job       | sal  | bsal      |
+-----------+-----------+------+-----------+
| 刘一      | 职员      |  800 | 1037.5000 |
| 张三      | 推销员    | 1250 | 1400.0000 |
| 王五      | 推销员    | 1250 | 1400.0000 |
| 孙七      | 经理      | 2450 | 2758.3333 |
| 钱多多    | 职员      |  950 | 1037.5000 |
+-----------+-----------+------+-----------+
5 rows in set (0.00 sec)
自我复制数据

有时,为了对某个sql语句进行效率测试,我们需要海量数据时,可以使用此法为表创建海量数据. 如快速的创建一张海量表.1000w

(1)创建一张结构相同的表
mysql> create table temp_emp like emp;
Query OK, 0 rows affected (0.06 sec)
(2)将emp的数据导入的temp_emp
mysql> insert into temp_emp select * from emp;
Query OK, 14 rows affected (0.01 sec)
Records: 14  Duplicates: 0  Warnings: 0
(3)自我复制
mysql> insert into temp_emp select * from temp_emp;
Query OK, 14 rows affected (0.01 sec)
Records: 14  Duplicates: 0  Warnings: 0    
数据去重复

1.单列去重

mysql> select distinct job from emp;
+-----------+
| job       |
+-----------+
| 职员      |
| 推销员    |
| 经理      |
| 分析师    |
| 总裁      |
+-----------+

2.多列去重

mysql> select distinct job,sal from emp;
+-----------+------+
| job       | sal  |
+-----------+------+
| 职员      |  800 |
合并查询

合并查询有两种,union / union all 会将几个sql语句的结果合并并返回
语法:select 语句1 union select语句2 ....;
注意点:union 合并时会去重复,union all 不会去重复

mysql> select * from dept union select * from dept;
+--------+-----------+--------+
| deptno | dname     | loc    |
+--------+-----------+--------+
|     10 | 财务部    | 北京   |
|     20 | 研发部    | 上海   |
|     30 | 销售部    | 广州   |
|     40 | 行政部    | 深圳   |
+--------+-----------+--------+
4 rows in set (0.00 sec)

mysql> select * from dept union all select * from dept;
+--------+-----------+--------+
| deptno | dname     | loc    |
+--------+-----------+--------+
|     10 | 财务部    | 北京   |
|     20 | 研发部    | 上海   |
|     30 | 销售部    | 广州   |
|     40 | 行政部    | 深圳   |
|     10 | 财务部    | 北京   |
|     20 | 研发部    | 上海   |
|     30 | 销售部    | 广州   |
|     40 | 行政部    | 深圳   |
+--------+-----------+--------+
8 rows in set (0.00 sec)

结论:union 和 union 都是纵向合并,且返回的字段以第一个语句查询的为准。

总结

以上就是对Mysql-多表查询的相关介绍,大家有什么问题可以给博主留言。

结尾

这一个黑夜的孩子,沉浸于冬天,倾心死亡不能自拔,热爱着空虚而寒冷的乡村--《春天,十个海子》

Responses