学习笔记day_05_连接查询

最新推荐文章于 2024-07-21 19:06:05 发布

HS..

最新推荐文章于 2024-07-21 19:06:05 发布

阅读量66

点赞数

本文链接：https://blog.csdn.net/ab0000888999/article/details/120925780

版权

数据库专栏收录该内容

9 篇文章 0 订阅

订阅专栏

distinct用法

distinct的作用：把查询结果去除重复记录

注意：原表的数据不会修改，只是查询结果去重

mysql> select job from emp;
+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| SALESMAN  |
| MANAGER   |
| SALESMAN  |
| MANAGER   |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
| SALESMAN  |
| CLERK     |
| CLERK     |
| ANALYST   |
| CLERK     |
+-----------+

mysql> select distinct job from emp;
+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
+-----------+

distinct只能出现在所有字段的最前方

distinct也能和分组函数结合来使用

统计一下工作岗位的数量？

mysql> select sum(distinct sal) from emp;
+-------------------+
| sum(distinct sal) |
+-------------------+
|          24775.00 |
+-------------------+
1 row in set (0.00 sec)

mysql> select sum(sal) from emp;
+----------+
| sum(sal) |
+----------+
| 29025.00 |
+----------+

连接查询

连接查询概念

连接查询的分类
- 根据语法的年代分类：
  - SQL92：1992年出现的语法
  - SQL99：1999年出现的语法
- 根据表连接的方式分类：
  - 内连接：
    - 等值连接
    - 非等值连接
    - 自连接
  - 外连接：
    - 左外连接（左连接）
    - 右外连接（右连接）
  - 全连接（了解内容）

连接查询的原理

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

+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | ACCOUNTING |
| SMITH  | RESEARCH   |
| SMITH  | SALES      |
| SMITH  | OPERATIONS |
| ALLEN  | ACCOUNTING |
| ALLEN  | RESEARCH   |
| ALLEN  | SALES      |
| ALLEN  | OPERATIONS |
| WARD   | ACCOUNTING |
| WARD   | RESEARCH   |
| WARD   | SALES      |
| WARD   | OPERATIONS |
| JONES  | ACCOUNTING |
| JONES  | RESEARCH   |
| JONES  | SALES      |
| JONES  | OPERATIONS |
| MARTIN | ACCOUNTING |
| MARTIN | RESEARCH   |
| MARTIN | SALES      |
| MARTIN | OPERATIONS |
| BLAKE  | ACCOUNTING |
| BLAKE  | RESEARCH   |
| BLAKE  | SALES      |
| BLAKE  | OPERATIONS |
| CLARK  | ACCOUNTING |
| CLARK  | RESEARCH   |
| CLARK  | SALES      |
| CLARK  | OPERATIONS |
| SCOTT  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| SCOTT  | SALES      |
| SCOTT  | OPERATIONS |
| KING   | ACCOUNTING |
| KING   | RESEARCH   |
| KING   | SALES      |
| KING   | OPERATIONS |
| TURNER | ACCOUNTING |
| TURNER | RESEARCH   |
| TURNER | SALES      |
| TURNER | OPERATIONS |
| ADAMS  | ACCOUNTING |
| ADAMS  | RESEARCH   |
| ADAMS  | SALES      |
| ADAMS  | OPERATIONS |
| JAMES  | ACCOUNTING |
| JAMES  | RESEARCH   |
| JAMES  | SALES      |
| JAMES  | OPERATIONS |
| FORD   | ACCOUNTING |
| FORD   | RESEARCH   |
| FORD   | SALES      |
| FORD   | OPERATIONS |
| MILLER | ACCOUNTING |
| MILLER | RESEARCH   |
| MILLER | SALES      |
| MILLER | OPERATIONS |
+--------+------------+

以上最终的查询结果条数，是两张表条数的乘积，这种现象被称作：笛卡尔积现象
表连接执行的操作是：emp表中的每一条记录都会和dept表每条记录进行匹配
如何避免笛卡尔积现象？
- 所以得在连接时加上条件，将满足这一个条件的记录被筛选出来

查询每个员工所在部门名称？
mysql> select ename,dname //注意在查找ename和dname，都会在emp和dept表中查询，而不是dname旨在dept表中查找，也会在emp表中查找
from emp,dept 
where emp.deptno = dept.deptno;//表连接的条件
// 表起别名。很重要。效率问题。
	select e.ename,d.dname 
	from  emp e, dept d
	where e.deptno = d.deptno; //SQL92语法。
+--------+------------+
| ename  | dname      |
+--------+------------+
| CLARK  | ACCOUNTING |
| KING   | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH  | RESEARCH   |
| JONES  | RESEARCH   |
| SCOTT  | RESEARCH   |
| ADAMS  | RESEARCH   |
| FORD   | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| TURNER | SALES      |
| JAMES  | SALES      |
+--------+------------+

最终查询的结果条数是14条，但是匹配的过程中，匹配的次数减少了吗？还是56次，只不过进行了四选一。次数没有减少

内连接

内连接之等值连接

查询每个员工所在部门名称，显示员工名和部门名？emp e和dept d表进行连接。条件是：e.deptno = d.deptno

//sql92语法
select e.ename,d.dname 
from emp e,dep d 
where e.deptno=e.deptno;
//sql92的缺点：结构不清晰，表的连接条件，和后期进一步筛选的条件，都放到了where后面

//sql99语法
//sql99有优点：表连接的条件是独立的，连接之后，如果还需要进一步筛选，在往后面添加where
//inner可以省略
select e.name,d.deptno
from emp e
inner join emp d
on e.deptno=d.deptno //e和d的连接条件

运行结果：
+--------+------------+
| ename  | dname      |
+--------+------------+
| CLARK  | ACCOUNTING |
| KING   | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH  | RESEARCH   |
| JONES  | RESEARCH   |
| SCOTT  | RESEARCH   |
| ADAMS  | RESEARCH   |
| FORD   | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| TURNER | SALES      |
| JAMES  | SALES      |
+--------+------------+

内连接之非等值连接

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

select e.ename,e.sal,s.grade 
from emp e 
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 |
+--------+---------+-------+

内连接之自连接

查询员工的上级领导，要求显示员工名和对象的领导名

select empno,ename,mgr from emp;
+-------+--------+------+
| empno | ename  | mgr  |
+-------+--------+------+
|  7369 | SMITH  | 7902 |
|  7499 | ALLEN  | 7698 |
|  7521 | WARD   | 7698 |
|  7566 | JONES  | 7839 |
|  7654 | MARTIN | 7698 |
|  7698 | BLAKE  | 7839 |
|  7782 | CLARK  | 7839 |
|  7788 | SCOTT  | 7566 |
|  7839 | KING   | NULL |
|  7844 | TURNER | 7698 |
|  7876 | ADAMS  | 7788 |
|  7900 | JAMES  | 7698 |
|  7902 | FORD   | 7566 |
|  7934 | MILLER | 7782 |
+-------+--------+------+
//把一张表看成两张表
+-------+--------+------+
| empno | ename  | mgr  |
+-------+--------+------+
|  7369 | SMITH  | 7902 |
|  7499 | ALLEN  | 7698 |
|  7521 | WARD   | 7698 |
|  7566 | JONES  | 7839 |
|  7654 | MARTIN | 7698 |
|  7698 | BLAKE  | 7839 |
|  7782 | CLARK  | 7839 |
|  7788 | SCOTT  | 7566 |
|  7839 | KING   | NULL |
|  7844 | TURNER | 7698 |
|  7876 | ADAMS  | 7788 |
|  7900 | JAMES  | 7698 |
|  7902 | FORD   | 7566 |
|  7934 | MILLER | 7782 |
+-------+--------+------+//emp a

+-------+--------+------+
| empno | ename  | mgr  |
+-------+--------+------+
|  7369 | SMITH  | 7902 |
|  7499 | ALLEN  | 7698 |
|  7521 | WARD   | 7698 |
|  7566 | JONES  | 7839 |
|  7654 | MARTIN | 7698 |
|  7698 | BLAKE  | 7839 |
|  7782 | CLARK  | 7839 |
|  7788 | SCOTT  | 7566 |
|  7839 | KING   | NULL |
|  7844 | TURNER | 7698 |
|  7876 | ADAMS  | 7788 |
|  7900 | JAMES  | 7698 |
|  7902 | FORD   | 7566 |
|  7934 | MILLER | 7782 |
+-------+--------+------+//emp a
select a.ename,b.ename 
from emp a join emp b 
on a.empno=b.mgr;
+-------+--------+
| ename | ename  |
+-------+--------+
| FORD  | SMITH  |
| BLAKE | ALLEN  |
| BLAKE | WARD   |
| KING  | JONES  |
| BLAKE | MARTIN |
| KING  | BLAKE  |
| KING  | CLARK  |
| JONES | SCOTT  |
| BLAKE | TURNER |
| SCOTT | ADAMS  |
| BLAKE | JAMES  |
| JONES | FORD   |
| CLARK | MILLER |
+-------+--------+外连接

外连接

A表和B表内连接，AB两张表没有主次之分，平等的；内连接的特点是：将能够满足连接条件的记录数据查询出来。
外连接分位左外连接和右外连接

左外连接

查询员工的上级领导，要求显示全部员工名和对象的领导名
select a.ename '员工名',b.ename '领导名' 
from emp a 
outer left join emp b //outer可以省略，来说明该连接位外连接
on  a.mgr=b.empno;
+--------+--------+
| 员工名      | 领导名     |
+--------+--------+
| SMITH  | FORD   |
| ALLEN  | BLAKE  |
| WARD   | BLAKE  |
| JONES  | KING   |
| MARTIN | BLAKE  |
| BLAKE  | KING   |
| CLARK  | KING   |
| SCOTT  | JONES  |
| KING   | NULL   |
| TURNER | BLAKE  |
| ADAMS  | SCOTT  |
| JAMES  | BLAKE  |
| FORD   | JONES  |
| MILLER | CLARK  |
+--------+--------+

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

多表连接

三张表，四张表怎么连接？
	语法：
		select 
			...
		from
			a
		join
			b
		on
			a和b的连接条件
		join
			c
		on
			a和c的连接条件
		right join
			d
		on
			a和d的连接条件

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

select e.ename,e.sal,s.grade,d.dname 
from emp e 
join dept d 
on e.deptno=d.deptno 
join salgrade s 
on e.sal between s.losal and s.hisal;
+--------+---------+-------+------------+
| ename  | sal     | grade | dname      |
+--------+---------+-------+------------+
| SMITH  |  800.00 |     1 | RESEARCH   |
| ALLEN  | 1600.00 |     3 | SALES      |
| WARD   | 1250.00 |     2 | SALES      |
| JONES  | 2975.00 |     4 | RESEARCH   |
| MARTIN | 1250.00 |     2 | SALES      |
| BLAKE  | 2850.00 |     4 | SALES      |
| CLARK  | 2450.00 |     4 | ACCOUNTING |
| SCOTT  | 3000.00 |     4 | RESEARCH   |
| KING   | 5000.00 |     5 | ACCOUNTING |
| TURNER | 1500.00 |     3 | SALES      |
| ADAMS  | 1100.00 |     1 | RESEARCH   |
| JAMES  |  950.00 |     1 | SALES      |
| FORD   | 3000.00 |     4 | RESEARCH   |
| MILLER | 1300.00 |     2 | ACCOUNTING |
+--------+---------+-------+------------+

子查询

什么是子查询

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

子查询可以出现在哪些位置？

select
	..(select).（不需要掌握）
from
	..(select).
where
	..(select).

注意子查询，不能用order by 语句，order by 只能对最终结果排序
where子句中的子查询

案例：找出比最低工资高的员工姓名和工资

 select ename,sal 
 from emp 
 where 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 |
+--------+---------+

from子句中的子查询

注意：from后面的子查询，可以将子查询的查询结果当做一张临时表

案列：找出每个岗位的平均工资的薪资等级。

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

union合并查询结果集

案例：查询工作岗位是MANAGER和SALESMAN的员工？

 select ename from emp where job='manager'
 union
 select ename from emp where job='salesman';
+--------+
| ename  |
+--------+
| JONES  |
| BLAKE  |
| CLARK  |
| ALLEN  |
| WARD   |
| MARTIN |
| TURNER |
+--------+

union在使用的时候有注意事项吗？

//错误的：union在进行结果集合并的时候，要求两个结果集的列数相同。
select ename,job from emp where job = ‘MANAGER’
union
select ename from emp where job = ‘SALESMAN’;

// MYSQL可以，oracle语法严格，不可以，报错。要求：结果集合并时列和列的数据类型也要一致。
select ename,job from emp where job = ‘MANAGER’
union
select ename,sal from emp where job = ‘SALESMAN’;

limit

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

默认的起始下标为0，limit 5 表示取前五

按照薪资降序，取出排名在前5名的员工？
select ename from emp order by sal desc limit 5;
+-------+
| ename |
+-------+
| KING  |
| SCOTT |
| FORD  |
| JONES |
| BLAKE |
+-------+
取出工资排名在[3-5]名的员工？
select ename, sal from emp order by sal desc limit 2,3;
+-------+---------+
| ename | sal     |
+-------+---------+
| FORD  | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+

注意：mysql中limit在在order by 之后执行！！！！
limit的应用———分页：控制数据的输出，每次指输出指定条数的数据

关于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..

HS..

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
学习笔记day_05_连接查询

distinct用法distinct的作用：把查询结果去除重复记录注意：原表的数据不会修改，只是查询结果去重mysql> select job from emp;+-----------+| job |+-----------+| CLERK || SALESMAN || SALESMAN || MANAGER || SALESMAN || MANAGER || MANAGER || ANALYST || PRESIDEN
复制链接

扫一扫