Mysql学习笔记第三章—复杂的查询语句

🕗 发布于 2024-01-29 23:36 mysql 学习笔记

复杂的查询语句

1.关于查询结果集的去重

去重方法：在查询字段前加distinct

#distinct只能出现在所有字段的最前方
mysql> select distinct job from emp;
+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
+-----------+
#distinct出现在最前方表示所有字段联合起来去重
mysql> select distinct ename,job from emp;
+--------+-----------+
| ename  | job       |
+--------+-----------+
| SMITH  | CLERK     |
| ALLEN  | SALESMAN  |
| WARD   | SALESMAN  |
| JONES  | MANAGER   |
| MARTIN | SALESMAN  |
| BLAKE  | MANAGER   |
| CLARK  | MANAGER   |
| SCOTT  | ANALYST   |
| KING   | PRESIDENT |
| TURNER | SALESMAN  |
| ADAMS  | CLERK     |
| JAMES  | CLERK     |
| FORD   | ANALYST   |
| MILLER | CLERK     |
+--------+-----------+
#统计job字段中的数量
mysql> select count(distinct job) from emp;
+---------------------+
| count(distinct job) |
+---------------------+
|                   5 |
+---------------------+

2.连接查询

在实际开发中，大部分情况不是从单标查询，而是多张表联合查询取出最终结果。
根据表的连接方式对连接查询分类：

内连接：等值连接、非等值连接、自连接

外连接：左(外)连接、右(外)连接

全连接
笛卡尔积(乘积)现象：两张表进行连接查询没有任何条件限制时，最终查询结果为两表记录条数的乘积。

#这种语句会发生笛卡尔积现象 
mysql> select ename,dname from emp,dept;
#关于表的别名语句
mysql>select e.ename,d.dname from emp e,dept d;

2.1内连接

内连接—等值连接：条件是等量关系。

常用语法：……A join B on 连接条件 where ……

避免笛卡尔积现象：加条件进行过滤。

#SQL92语法，一般少用。
mysql> select e.ename,d.dname from emp e,dept d where 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 |
+--------+------------+
#SQL99语法（常用）： ……A inner join B on 连接条件 where ……
#其中inner可以省略
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 |
+--------+------------+

内连接—非等值连接：连接条件中的关系是非等量关系。

#工资名称表
mysql> select ename,sal from emp;
+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| 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 |
+--------+---------+
#工资等级表
mysql> select * from salgrade;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
#要求查询每个人的工资等级
mysql> 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 |
+--------+---------+-------+

自连接：一张表看为两张表。

#一张表中的员工和对应的上级领导编号
mysql> 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 |
+-------+--------+------+
#要求：显示员工名和对应的领导名
mysql> select a.empno,a.ename as '员工名',b.ename as '领导名' from emp a join emp b on a.mgr = b.empno;
+-------+-----------+-----------+
| empno | 员工名    | 领导名    |
+-------+-----------+-----------+
|  7369 | SMITH     | FORD      |
|  7499 | ALLEN     | BLAKE     |
|  7521 | WARD      | BLAKE     |
|  7566 | JONES     | KING      |
|  7654 | MARTIN    | BLAKE     |
|  7698 | BLAKE     | KING      |
|  7782 | CLARK     | KING      |
|  7788 | SCOTT     | JONES     |
|  7844 | TURNER    | BLAKE     |
|  7876 | ADAMS     | SCOTT     |
|  7900 | JAMES     | BLAKE     |
|  7902 | FORD      | JONES     |
|  7934 | MILLER    | CLARK     |
+-------+-----------+-----------+

2.2外连接

内连接和外连接的区别：

内连接：A和B两张表使用内连接时，A和B能够匹配上的记录查询出来。两张表没有主副之分，是平等的。

外连接：A和B两张表使用外连接，A和B中有一张是主表，一张是副表，主要查询主表中的数据，顺便查询副表。当副表中数据没有和主表中数据匹配上，副表模拟出NULL与之匹配。
外连接分类：

左连接：表示左边的表是主表

右连接：表示右边的表是主表

#一张表中的员工和对应的上级领导编号
mysql> 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 |
+-------+--------+------+
#左连接 left outer join
#outer可以省略
mysql> select a.empno,a.ename as'员工名',b.ename as '领导名' from emp a left join emp b on a.mgr = b.empno;
+-------+-----------+-----------+
| empno | 员工名    | 领导名    |
+-------+-----------+-----------+
|  7369 | SMITH     | FORD      |
|  7499 | ALLEN     | BLAKE     |
|  7521 | WARD      | BLAKE     |
|  7566 | JONES     | KING      |
|  7654 | MARTIN    | BLAKE     |
|  7698 | BLAKE     | KING      |
|  7782 | CLARK     | KING      |
|  7788 | SCOTT     | JONES     |
|  7839 | KING      | NULL      |
|  7844 | TURNER    | BLAKE     |
|  7876 | ADAMS     | SCOTT     |
|  7900 | JAMES     | BLAKE     |
|  7902 | FORD      | JONES     |
|  7934 | MILLER    | CLARK     |
+-------+-----------+-----------+
#右连接right outer join
#outer可以省略
mysql>  select a.empno,a.ename as'员工名',b.ename as '领导名' from emp b right join emp a on a.mgr = b.empno;
+-------+-----------+-----------+
| empno | 员工名    | 领导名    |
+-------+-----------+-----------+
|  7369 | SMITH     | FORD      |
|  7499 | ALLEN     | BLAKE     |
|  7521 | WARD      | BLAKE     |
|  7566 | JONES     | KING      |
|  7654 | MARTIN    | BLAKE     |
|  7698 | BLAKE     | KING      |
|  7782 | CLARK     | KING      |
|  7788 | SCOTT     | JONES     |
|  7839 | KING      | NULL      |
|  7844 | TURNER    | BLAKE     |
|  7876 | ADAMS     | SCOTT     |
|  7900 | JAMES     | BLAKE     |
|  7902 | FORD      | JONES     |
|  7934 | MILLER    | CLARK     |
+-------+-----------+-----------+
#找出哪个部门没有员工
mysql> select d.deptno,d.dname from emp e right join dept d on e.deptno = d.deptno where e.empno is null;
+--------+------------+
| deptno | dname      |
+--------+------------+
|     40 | OPERATIONS |
+--------+------------+

2.3三张表连接查询

需求：找出每个员工的部门名称以及工资等级。

#emp员工表
mysql> select empno,ename,deptno,sal from emp;
+-------+--------+--------+---------+
| empno | ename  | deptno | sal     |
+-------+--------+--------+---------+
|  7369 | SMITH  |     20 |  800.00 |
|  7499 | ALLEN  |     30 | 1600.00 |
|  7521 | WARD   |     30 | 1250.00 |
|  7566 | JONES  |     20 | 2975.00 |
|  7654 | MARTIN |     30 | 1250.00 |
|  7698 | BLAKE  |     30 | 2850.00 |
|  7782 | CLARK  |     10 | 2450.00 |
|  7788 | SCOTT  |     20 | 3000.00 |
|  7839 | KING   |     10 | 5000.00 |
|  7844 | TURNER |     30 | 1500.00 |
|  7876 | ADAMS  |     20 | 1100.00 |
|  7900 | JAMES  |     30 |  950.00 |
|  7902 | FORD   |     20 | 3000.00 |
|  7934 | MILLER |     10 | 1300.00 |
+-------+--------+--------+---------+
#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)
#salgrade工资等级表
mysql> select * from salgrade;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
#找出每个员工的部门名称以及工资等级。
mysql>  select e.ename,d.dname,s.grade from emp e join dept d on e.deptno = d.deptno join salgrade s on e.sal between s.losal and s.hisal;
+--------+------------+-------+
| ename  | dname      | grade |
+--------+------------+-------+
| SMITH  | RESEARCH   |     1 |
| ALLEN  | SALES      |     3 |
| WARD   | SALES      |     2 |
| JONES  | RESEARCH   |     4 |
| MARTIN | SALES      |     2 |
| BLAKE  | SALES      |     4 |
| CLARK  | ACCOUNTING |     4 |
| SCOTT  | RESEARCH   |     4 |
| KING   | ACCOUNTING |     5 |
| TURNER | SALES      |     3 |
| ADAMS  | RESEARCH   |     1 |
| JAMES  | SALES      |     1 |
| FORD   | RESEARCH   |     4 |
| MILLER | ACCOUNTING |     2 |
+--------+------------+-------+
#找出每个员工的部门名称以及工资等级以及上级领导。
mysql> select e.ename,d.dname,s.grade,eb.ename as 'leader' from emp e join dept d join salgrade s on e.deptno = d.deptno and e.sal between s.losal and s.hisal left join emp eb on e.mgr = eb.empno;
+--------+------------+-------+--------+
| ename  | dname      | grade | leader |
+--------+------------+-------+--------+
| SMITH  | RESEARCH   |     1 | FORD   |
| ALLEN  | SALES      |     3 | BLAKE  |
| WARD   | SALES      |     2 | BLAKE  |
| JONES  | RESEARCH   |     4 | KING   |
| MARTIN | SALES      |     2 | BLAKE  |
| BLAKE  | SALES      |     4 | KING   |
| CLARK  | ACCOUNTING |     4 | KING   |
| SCOTT  | RESEARCH   |     4 | JONES  |
| KING   | ACCOUNTING |     5 | NULL   |
| TURNER | SALES      |     3 | BLAKE  |
| ADAMS  | RESEARCH   |     1 | SCOTT  |
| JAMES  | SALES      |     1 | BLAKE  |
| FORD   | RESEARCH   |     4 | JONES  |
| MILLER | ACCOUNTING |     2 | CLARK  |
+--------+------------+-------+--------+

3.子查询

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

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

#找出高于平均薪资的员工信息
#注意where后面不能使用分组函数
#where后面嵌套子查询
mysql> select empno,ename,job,hiredate,sal,deptno from emp where sal>(select avg(sal) from emp);
+-------+-------+-----------+------------+---------+--------+
| empno | ename | job       | hiredate   | sal     | deptno |
+-------+-------+-----------+------------+---------+--------+
|  7566 | JONES | MANAGER   | 1981-04-02 | 2975.00 |     20 |
|  7698 | BLAKE | MANAGER   | 1981-05-01 | 2850.00 |     30 |
|  7782 | CLARK | MANAGER   | 1981-06-09 | 2450.00 |     10 |
|  7788 | SCOTT | ANALYST   | 1987-04-19 | 3000.00 |     20 |
|  7839 | KING  | PRESIDENT | 1981-11-17 | 5000.00 |     10 |
|  7902 | FORD  | ANALYST   | 1981-12-03 | 3000.00 |     20 |
+-------+-------+-----------+------------+---------+--------+
#from后面嵌套子查询
#找出每个部门平均薪水的薪资等级 
mysql> select t.*,s.grade,d.dname from (select deptno,avg(sal) as 'avgsal' from emp group by deptno) t join salgrade s on t.avgsal between s.losal and s.hisal left join dept d on t.deptno = d.deptno;
+--------+-------------+-------+------------+
| deptno | avgsal      | grade | dname      |
+--------+-------------+-------+------------+
|     20 | 2175.000000 |     4 | RESEARCH   |
|     30 | 1566.666667 |     3 | SALES      |
|     10 | 2916.666667 |     4 | ACCOUNTING |
+--------+-------------+-------+------------+
#在select后面嵌套子查询
#找出每个员工所在的部门名称，要求显示员工名和部门名
mysql> select e.ename,(select d.dname from dept d where e.deptno = d.deptno) as dname from emp e;
+--------+------------+
| 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 |
+--------+------------+

4.集相加

union：可以将查询结果集相加。

#找出job中是SALESMAN和MANAGER的ename
#第一种方式
mysql> select ename,job from emp where job='MANAGER' or job='SALESMAN';
#第二种方式
mysql> select ename,job from emp where job in('MANAGER','SALESMAN');
#第三种方式，union方式
mysql> select ename,job from emp where job='MANAGER'
    -> union
    -> select ename,job from emp where job='SALESMAN';
+--------+----------+
| ename  | job      |
    +--------+----------+
    | JONES  | MANAGER  |
    | BLAKE  | MANAGER  |
    | CLARK  | MANAGER  |
    | ALLEN  | SALESMAN |
    | WARD   | SALESMAN |
    | MARTIN | SALESMAN |
    | TURNER | SALESMAN |
    +--------+----------+

5.limit

limit是mysql特有的，其他数据库没有。(Oracle中有一个相同的机制，叫做rownum)
limit取结果集中的部分数据，可以用来分页查询。

语法机制：limit startIndex,length

startIndex表示起始位置，length表示取个数。

#取出工资前5名的员工（思路降序取前5个）
mysql> select ename,sal from emp order by sal desc limit 0,5;
#前五个可以直接省略第一个数字。
#mysql> select ename,sal from emp order by sal desc limit 5;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| SCOTT | 3000.00 |
| FORD  | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
#找出工资在第4到第9名员工（下标从0开始，第4名是3）
mysql> select ename,sal from emp order by sal desc limit 3,6;
+--------+---------+
| ename  | sal     |
+--------+---------+
| JONES  | 2975.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
+--------+---------+

小结：

select … from … where … group by …having … order by … limit…;

学习视频：动力节点MySQL基础入门到精通

原文地址：https://blog.csdn.net/weixin_46016581/article/details/135893156

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

上一篇：VitePress-05-文档中标题表toc的使用
下一篇：安装Anaconda所遇到的一些问题

AutoDL上进行tensorboard可视化
3.在实例中执行：tensorboard --port 6006 --logdir work_dirs。2.在实例中执行：export https_proxy=http://127.0.0.1:108
阅读更多2024-11-16
莱特币转型MEME币：背后隐含的加密市场现象
MEME币的兴起标志着加密市场的一种转型，传统的技术创新已经不再是吸引投资者的唯一因素，社区文化和社交媒体的炒作正在成为市场推动力之一。这种趋势的背后，反映了加密市场的一种深层次变化：许多项目原本依靠
阅读更多2024-11-16
STM32仿真proteus位带操作和keil增加头文件C文件
在学习 51 单片机的时候就使用过位操作，通过关键字 sbit 对单片机 IO 口进行位定义。但是 STM32 没有这样的关键字，而是通过访问位带别名区来实现。即将每个比特位膨胀成一个 32 位字，当
阅读更多2024-11-16
【机器学习】机器学习中用到的高等数学知识-5. 函数空间和泛函分析 (Functional Analysis)
函数的连续性和可微性是分析和优化模型的重要数学性质，在机器学习中，这些概念帮助我们评估模型的学习能力和泛化能力。希尔伯特空间和巴拿赫空间是泛函分析中的两个重要概念，它们描述了不同的向量空
阅读更多2024-11-16
苍穹外卖学习-day11
Apache ECharts 是一款基于 Javascript 的数据可视化图表库，提供直观，生动，可交互，可个性化定制的数据可视化图表。常见的统计图形有：柱状图，条形图，折线图不管是哪种形式的图形，
阅读更多2024-11-16
DHTMLX-gantt组件显示不同的颜色
important;important;important;
阅读更多2024-11-16
计算机毕业设计Python+大模型中医养生问答系统知识图谱医疗大数据中医可视化机器学习深度学习人工智能大数据毕业设计
计算机毕业设计Python+大模型中医养生问答系统知识图谱医疗大数据中医可视化机器学习深度学习人工智能大数据毕业设计
阅读更多2024-11-16
使用Markmap从链接生成脑图并下载为交互式HTML文件
在这篇博客中，我们将探讨如何从指定链接中提取内容，生成Markdown格式的脑图，然后使用Markmap将其可视化，并最终下载为交互式HTML文件。通过以上步骤，我们可以轻松从链接中提取内容，生成Ma
阅读更多2024-11-16
SAP+Internet主题HTML样式选择
SAP生成HTML前端界面
阅读更多2024-11-16
关于php Datetime 时区转换因为timezone_version(时区版本)问题造成的时区转换问题
php时区转换 timezone_version(时区版本)问题造成的时区转换问题
阅读更多2024-11-16

Mysql学习笔记第三章—复杂的查询语句

复杂的查询语句

1.关于查询结果集的去重

2.连接查询

2.1内连接

2.2外连接

2.3三张表连接查询

3.子查询

4.集相加

5.limit

相关文章