mysql联表查询 like mysql联表查询去重

转载

mob64ca140e76c8 2023-10-05 17:38:46

文章标签 mysql联表查询 like mysql 数据库 database 连接查询 文章分类 MySQL 数据库

文章目录

前言
一、去重
二、连接查询

内连接之等值连接
内连接之非等值连接
外连接
子查询
union合并查询结果集
limit
关于DQL语句的大总结

前言

视频教程：老杜带你学mysql-基础教程为了记录学习历程，方便以后查找，写下此博客。

本文所使用的数据：

链接：mysql_document 提取码：umui

一、去重

1、把查询结果去除重复记录

注意： distinct只能出现在所有字段的最前方。

mysql> select distinct job from emp;
+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
+-----------+
5 rows in set (0.01 sec)

mysql>

注意： distinct出现在job,deptno两个字段之前，表示两个字段联合起来去重。

mysql> select distinct job,deptno from emp;
+-----------+--------+
| job       | deptno |
+-----------+--------+
| CLERK     |     20 |
| SALESMAN  |     30 |
| MANAGER   |     20 |
| MANAGER   |     30 |
| MANAGER   |     10 |
| ANALYST   |     20 |
| PRESIDENT |     10 |
| CLERK     |     30 |
| CLERK     |     10 |
+-----------+--------+
9 rows in set (0.00 sec)

mysql>

二、连接查询

1、什么是连接查询？

从一张表中单独查询，称为单表查询。
从emp表和dept表联合起来查询数据，从emp表中取员工名字，从dept表中取部门名字。
这种跨表查询，多张表联合起来查询数据，被称为连接查询。

2、连接查询的分类

根据语法的年代分类：
		SQL92：1992年的时候出现的语法
		SQL99：1999年的时候出现的语法
		我们这里重点学习SQL99
	
	根据表连接的方式分类：
		内连接：
			等值连接
			非等值连接
			自连接

		外连接：
			左外连接（左连接）
			右外连接（右连接）

		全连接

3、当两张表进行连接查询时，没有任何条件的限制会发生什么现象

当两张表进行连接查询，没有任何条件限制的时候，最终查询结果条数，是
两张表条数的乘积，这种现象被称为：笛卡尔积现象。

4、查询每个员工所在部门名称

emp表

mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  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 | 1987-04-19 | 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 | 1987-05-23 | 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 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)

dept表

mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

查询：

mysql> select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno; //SQL92语法。
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| CLARK  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.01 sec)

内连接之等值连接

5.1、查询每个员工所在部门名称，显示员工名和部门名

mysql> select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| CLARK  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)

mysql>

还可以加上inner

mysql> select e.ename,d.dname from emp e inner join dept d on e.deptno = d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| CLARK  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)

mysql>

//inner可以省略（带着inner可读性更好！！！一眼就能看出来是内连接）

5.2、查询每个员工所在部门名称，显示部门名为SALES的员工名和部门名

mysql> select e.ename,d.dname from emp e inner join dept d on e.deptno = d.deptno where d.dname = 'sales';
+--------+-------+
| ename  | dname |
+--------+-------+
| ALLEN  | SALES |
| WARD   | SALES |
| MARTIN | SALES |
| BLAKE  | SALES |
| TURNER | SALES |
| JAMES  | SALES |
+--------+-------+
6 rows in set (0.00 sec)

内连接之非等值连接

6.1、找出每个员工的薪资等级，要求显示员工名、薪资、薪资等级

mysql> select e.ename,e.sal,s.grade from emp e inner join salgrade s on e.sal between s.losal and s.hisal;
+--------+---------+-------+
| ename  | sal     | grade |
+--------+---------+-------+
| SMITH  |  800.00 |     1 |
| ALLEN  | 1600.00 |     3 |
| WARD   | 1250.00 |     2 |
| JONES  | 2975.00 |     4 |
| MARTIN | 1250.00 |     2 |
| BLAKE  | 2850.00 |     4 |
| CLARK  | 2450.00 |     4 |
| SCOTT  | 3000.00 |     4 |
| KING   | 5000.00 |     5 |
| TURNER | 1500.00 |     3 |
| ADAMS  | 1100.00 |     1 |
| JAMES  |  950.00 |     1 |
| FORD   | 3000.00 |     4 |
| MILLER | 1300.00 |     2 |
+--------+---------+-------+
14 rows in set (0.00 sec)

mysql>

7、内连接之自连接
7.1、查询员工的上级领导，要求显示员工名和对应的领导名

mysql> select e.ename , m.ename from emp e join (select empno,ename from emp) m on e.mgr = m.empno;
+--------+-------+
| ename  | ename |
+--------+-------+
| SMITH  | FORD  |
| ALLEN  | BLAKE |
| WARD   | BLAKE |
| JONES  | KING  |
| MARTIN | BLAKE |
| BLAKE  | KING  |
| CLARK  | KING  |
| SCOTT  | JONES |
| TURNER | BLAKE |
| ADAMS  | SCOTT |
| JAMES  | BLAKE |
| FORD   | JONES |
| MILLER | CLARK |
+--------+-------+
13 rows in set (0.00 sec)

mysql>

外连接

8.1、外连接（右外连接）

select 
	e.ename,d.dname
from
	emp e 
right join 
	dept d
on
	e.deptno = d.deptno;

// outer是可以省略的，带着可读性强。
select 
	e.ename,d.dname
from
	emp e 
right outer join 
	dept d
on
	e.deptno = d.deptno;

right代表什么：表示将join关键字右边的这张表看成主表，主要是为了将
这张表的数据全部查询出来，捎带着关联查询左边的表。
在外连接当中，两张表连接，产生了主次关系。

8.2、外连接（左外连接）：

select 
	e.ename,d.dname
from
	dept d 
left join 
	emp e
on
	e.deptno = d.deptno;

// outer是可以省略的，带着可读性强。
select 
	e.ename,d.dname
from
	dept d 
left outer join 
	emp e
on
	e.deptno = d.deptno;

9、三张表，四张表怎么连接

select 
	...
from
	a
join
	b
on
	a和b的连接条件
join
	c
on
	a和c的连接条件
right join
	d
on
	a和d的连接条件

9.1 、找出每个员工的部门名称以及工资等级，要求显示员工名、部门名、薪资、薪资等级

mysql> select e.ename  as '员工名',m.ename as '领导名' , d.dname as '部门名', e.sal as '薪资', s.grade as '薪资等级' from emp e left outer join (select empno,ename from emp) m on e.mgr = m.empno join dept d on e.deptno = d.deptno join salgrade s on e.sal between s.losal and hisal;
+--------+--------+------------+---------+----------+
| 员工名 | 领导名 | 部门名     | 薪资    | 薪资等级 |
+--------+--------+------------+---------+----------+
| SMITH  | FORD   | RESEARCH   |  800.00 |        1 |
| ALLEN  | BLAKE  | SALES      | 1600.00 |        3 |
| WARD   | BLAKE  | SALES      | 1250.00 |        2 |
| JONES  | KING   | RESEARCH   | 2975.00 |        4 |
| MARTIN | BLAKE  | SALES      | 1250.00 |        2 |
| BLAKE  | KING   | SALES      | 2850.00 |        4 |
| CLARK  | KING   | ACCOUNTING | 2450.00 |        4 |
| SCOTT  | JONES  | RESEARCH   | 3000.00 |        4 |
| KING   | NULL   | ACCOUNTING | 5000.00 |        5 |
| TURNER | BLAKE  | SALES      | 1500.00 |        3 |
| ADAMS  | SCOTT  | RESEARCH   | 1100.00 |        1 |
| JAMES  | BLAKE  | SALES      |  950.00 |        1 |
| FORD   | JONES  | RESEARCH   | 3000.00 |        4 |
| MILLER | CLARK  | ACCOUNTING | 1300.00 |        2 |
+--------+--------+------------+---------+----------+
14 rows in set (0.01 sec)

mysql>

子查询

10.1、什么是子查询

select语句中嵌套select语句，被嵌套的select语句称为子查询。

10.2、子查询都可以出现在哪里呢

select
		..(select).
	from
		..(select).
	where
		..(select).

10.3、找出比最低工资高的员工姓名和工资

mysql> select e.ename,e.sal from emp e where e.sal > (select min(sal) from emp);
+--------+---------+
| ename  | sal     |
+--------+---------+
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
13 rows in set (0.00 sec)

10.4 、找出每个岗位的平均工资的薪资等级

mysql> select a.job,a.avgsal,s.grade from (select job,avg(sal) as avgsal from emp group by job) a join salgrade s on a.avgsal between s.losal and s.hisal;
+-----------+-------------+-------+
| job       | avgsal      | grade |
+-----------+-------------+-------+
| CLERK     | 1037.500000 |     1 |
| SALESMAN  | 1400.000000 |     2 |
| MANAGER   | 2758.333333 |     4 |
| ANALYST   | 3000.000000 |     4 |
| PRESIDENT | 5000.000000 |     5 |
+-----------+-------------+-------+
5 rows in set (0.00 sec)

union合并查询结果集

11.1、查询工作岗位是MANAGER和SALESMAN的员工

mysql> select ename from emp where job = 'salesman' union select ename from emp where job = 'manager';
+--------+
| ename  |
+--------+
| ALLEN  |
| WARD   |
| MARTIN |
| TURNER |
| JONES  |
| BLAKE  |
| CLARK  |
+--------+
7 rows in set (0.00 sec)

limit

12.1、limit作用

将查询结果集的一部分取出来。通常使用在分页查询当中。
百度默认：一页显示10条记录。
分页的作用是为了提高用户的体验，因为一次全部都查出来，用户体验差。
可以一页一页翻页看。

12.2、limit怎么用呢

完整用法：limit startIndex, length
	startIndex是起始下标，length是长度。
	起始下标从0开始。

缺省用法：limit 5; 这是取前5.

12.2.1、按照薪资降序，取出排名在前5名的员工？

select 
		ename,sal
	from
		emp
	order by 
		sal desc
	limit 5; //取前5

	select 
		ename,sal
	from
		emp
	order by 
		sal desc
	limit 0,5;

12.2.2、取出工资排名在[3-5]名的员工

mysql> select ename,sal from emp order by sal desc limit 2,3;
+-------+---------+
| ename | sal     |
+-------+---------+
| FORD  | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
3 rows in set (0.00 sec)

mysql>

关于DQL语句的大总结

select 
		...
	from
		...
	where
		...
	group by
		...
	having
		...
	order by
		...
	limit
		...
	
	执行顺序？
		1.from
		2.where
		3.group by
		4.having
		5.select
		6.order by
		7.limit..

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。