慈溪网站设计,关于茶叶网站模板,国内做的好看的网站,微信公众号开发文档官方「前言」文章内容大致是对MySQL复合查询的学习。 「归属专栏」MySQL 「主页链接」个人主页 「笔者」枫叶先生(fy) 目录 一、基本查询回顾二、多表查询三、自连接四、子查询4.1 单行子查询4.2 多行子查询4.3 多列子查询4.4 在from子句中使用子查询 五、合并查询 一、基本查询回顾… 「前言」文章内容大致是对MySQL复合查询的学习。 「归属专栏」MySQL 「主页链接」个人主页 「笔者」枫叶先生(fy) 目录 一、基本查询回顾二、多表查询三、自连接四、子查询4.1 单行子查询4.2 多行子查询4.3 多列子查询4.4 在from子句中使用子查询 五、合并查询 一、基本查询回顾
前面篇章讲解的mysql表的查询都是对一张表进行查询在实际开发中这远远不够下面将讲解复合查询首先回顾一下基本的查询。
使用的数据库是之前篇章的雇员信息表员工表emp、部门表dept和工资等级表salgrade 查询工资高于500或岗位为MANAGER的雇员同时还要满足他们的姓名首字母为大写的J mysql select * from emp where (sal 500 or job MANAGER) and ename like J%;按照部门号升序而雇员的工资降序排序 mysql select * from emp order by deptno asc, sal desc;使用年薪进行降序排序 mysql select ename, sal*12ifnull(comm, 0) as 年薪 from emp order by 年薪 desc;注
由于NULL与任何值做计算得到的结果都是NULL因此在计算年薪时不能直接用月薪的12倍加上每个员工的奖金这样可能导致得到的年薪为NULL值。在计算每个员工的年薪时应该通过ifnull函数判断员工的奖金是否为NULL如果不为NULL则ifnull函数返回员工的奖金如果为NULL则ifnull函数返回0避免让NULL值参与计算 显示工资最高的员工的名字和工作岗位 解决该问题需要进行两次查询 此外这种问题还可以使用子查询将两句查询语句合并起来需要将第一次查询的SQL语句用括号括起来。
mysql select ename, job from emp where sal (select max(sal) from emp);显示工资高于平均工资的员工信息 也是使用子查询解决
mysql select * from emp where sal (select avg(sal) from emp);显示每个部门的平均工资和最高工资 在group by子句中指明按照部门号进行分组在select语句中使用avg函数和max函数分别查询每个部门的平均工资和最高工资
mysql select deptno, format(avg(sal), 2) 平均, max(sal) 最高 from emp group by deptno;显示平均工资低于2000的部门号和它的平均工资 在group by子句中指明按照部门号进行分组在select语句中使用avg函数查询每个部门的平均工资在having子句中指明筛选条件为平均工资小于2000
mysql select deptno, avg(sal) 平均工资 from emp group by deptno having 平均工资 2000;显示每种岗位的雇员总数平均工资 mysql select job, count(*) 人数, format(avg(sal), 2) 平均工资 from emp group by job;二、多表查询
上面的基础查询都是在一张表的基础上进行的查询实际开发中往往数据来自不同的表所以需要多表查询。
在进行多表查询时只需要将多张表的表名依次放到from子句之后用逗号隔开即可这时MySQL将会对给定的这多张表取笛卡尔积作为多表查询的初始数据源多表查询的本质就是对给定的多张表取笛卡尔积然后在产生的新表进行查询
笛卡尔积是指给定两个集合A和B其中A中的每个元素和B中的每个元素都可以组成一个有序对这些有序对的集合就是A和B的笛卡尔积。
例如员工表和部门表进行笛卡尔积
员工表 部门表 两张表进行笛卡尔积
mysql select * from emp, dept;员工表和部门表的笛卡尔积由两部分组成前半部分是员工表的列信息后半部分是部门表的列信息 对员工表和部门表取笛卡尔积时会先从员工表中选出一条记录与部门表中的所有记录进行组合然后再从员工表中选出一条记录与部门表中的所有记录进行组合以此类推最终得到一张新表 对多张表取笛卡尔积后得到的数据并不都是有意义的。
比如对员工表和部门表取笛卡尔积时员工表中的每一个员工信息都会和部门表中的每一个部门信息进行组合而实际一个员工只有和自己所在的部门信息进行组合才是有意义的因此需要从笛卡尔积产生的新表筛选出员工的部门号和部门的编号相等记录。 注意进行笛卡尔积的多张表中可能会存在相同的列名这时在选中列名时需要通过表名.列名的方式进行指明如果有重复的不指明确切一列就会报错。 显示雇员名、雇员工资以及所在部门的名字 从题意可以看出部门名只有dept表中才有其他数据来源于emp表即数据来自EMP和DEPT表因此要联合查询即多表查询
mysql select emp.ename, emp.sal, dept.deptno from emp, dept where emp.deptno dept.deptno;显示部门号为10的部门名员工名和工资 部门名只有部门表中才有员工名和员工工资只有员工表中才有因此需要同时使用员工表和部门表进行多表查询在where子句中指明筛选条件为员工的部门号等于部门编号筛选符合条件的信息
mysql select ename, sal, emp.deptno, dname from emp, dept where emp.deptno dept.deptno and dept.deptno 10;注意在筛选部门号等于10的部门时可以使用员工表中的部门号也可以使用部门表中的部门编号因为两列都是一样的。 显示各个员工的姓名工资及工资级别 员工名和工资只有员工表中才有而工资级别只有工资等级表中才有因此需要同时使用员工表和工资等级表进行多表查询在where子句中指明筛选条件为员工的工资在losal和hisal之间的记录
mysql select ename, sal, grade from emp, salgrade where sal between losal and hisal;三、自连接
自连接是指在同一张表进行连接查询也就是说我们不仅可以对不同表进行取笛卡尔积也可以对同一张表取笛卡尔积。 显示员工FORD的上级领导的编号和姓名 可以使用子查询先对员工表进行查询得到FORD的领导的编号然后再根据领导的编号对员工表进行查询得到FORD领导的姓名
mysql select empno, ename from emp where empno (select mgr from emp where ename FORD);也可以使用多表查询自查询因为员工表中的mgr字段能够将表中员工的信息和员工领导的信息关联起来。
mysql select leader.empno, leader.ename from emp leader, emp worder where leader.empno worder.mgr and worder.ename FORD;注 由于自连接是对同一张表取笛卡尔积因此在自连接时至少需要给一张表取别名否则无法区分这两张表中的列。
四、子查询
子查询是指嵌入在其他SQL语句中的查询语句也叫嵌套查询子查询可分为单行子查询、多行子查询、多列子查询以及在from子句中使用的子查询
4.1 单行子查询
单行子查询是指返回单行单列数据的子查询 显示SMITH同一部门的员工 在子查询中查询SMITH所在的部门号在where子句中指明筛选条件为员工部门号等于子查询返回的部门号
mysql select * from emp where deptno (select deptno from emp where ename SMITH);此外解决该问题也可以使用自连接
4.2 多行子查询
多行子查询是指返回多行单列数据的子查询 使用in关键字查询和10号部门的工作岗位相同的雇员的名字岗位工资部门号但是不包含10自己的 先查询10号部门有哪些工作岗位在查询时要对结果进行去重因为10号部门的某些员工的工作岗位可能是相同的 然后将上述查询作为子查询在查询员工表时在where子句中使用in关键字in关键字用于判断员工的工作岗位是子查询得到的若干岗位中的一个 mysql select ename, job, deptno from emp - where job in (select distinct job from emp where deptno10) and deptno10;实用all关键字显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号 先查询30号部门员工的工资进行去重
将上述查询作为子查询在查询员工表时在where子句中使用all关键字all关键字用于判断员工的工资是否高于子查询得到的所有工资
mysql select ename, sal, deptno from emp where sal all(select distinct sal from emp where deptno20);使用any关键字显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号包含自己部门的员工 先查询30号部门员工的工资然后在查询员工表时在where子句中使用any关键字判断员工的工资是否高于子查询的得到的工资中的某一个
mysql select ename, sal, deptno from emp where sal any(select distinct sal from emp where deptno30);4.3 多列子查询
单行子查询是指子查询只返回单列单行数据多行子查询是指返回单列多行数据都是针对单列而言的而多列子查询则是指查询返回多个列数据的子查询语句 查询和SMITH的部门和岗位完全相同的所有雇员不含SMITH本人 先查询SMITH所在部门的部门号和他的岗位然后将上述查询作为子查询
mysql select * from emp where (deptno,job) (select deptno, job from emp where ename SMITH) and ename SMITH;注
多列子查询得到的结果是多列数据在比较多列数据时需要将待比较的多个列用圆括号括起来多列子查询返回的如果是多行数据在筛选数据时也可以使用in、all和any关键字
4.4 在from子句中使用子查询
子查询语句不仅可以出现在where子句中也可以出现在from子句中子查询语句出现from子句中其查询结果将会被当作一个临时表使用 显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资 先查询每个部门的平均工资这张表当做临时表使用 然后对员工表和上述的查询结果进行多表查询在where子句中指明筛选条件为员工的部门号等于临时表中的部门号并且员工的工资大于临时表中的平均工资
mysql select ename, emp.deptno, sal, 平均工资 from emp, (select deptno, avg(sal) 平均工资 from emp group by deptno) tmp - where emp.deptnotmp.deptno and sal 平均工资;注意在from子句中使用子查询时必须给子查询得到的临时表取一个别名否则查询将会出错 查找每个部门工资最高的人的姓名、工资、部门、最高工资 先查询每个部门的最高工资 然后对员工表和上述的查询结果进行取笛卡尔积在where子句中指明筛选条件为员工的部门号等于临时表中的部门号并且员工的工资等于临时表中的最高工资
mysql select ename, sal, emp.deptno, 最高工资 from emp, (select max(sal) 最高工资, deptno from emp group by deptno) tmp - where emp.deptnotmp.deptno and sal最高工资;显示每个部门的信息部门名编号地址和人员数量 按照部门号进行分组分别查询每个部门的人员数量 述查询作为子查询放在from子句中然后对员工表和临时表取笛卡尔积在where子句中指明筛选条件为员工的部门号等于临时表中的部门号即可
mysql select dname, dept.deptno, loc, 部门人数 from dept, (select deptno, count(*) 部门人数 from emp group by deptno) - tmp where dept.deptno tmp.deptno;上述也可以只使用多表查询解决
mysql select dname, dept.deptno, loc, count(*) 人数 from emp, dept - where emp.deptno dept.deptno - group by dept.deptno, dname, loc;五、合并查询
合并查询是指将多个查询结果进行合并关键字union和union all
union用于取得两个查询结果的并集union会自动去掉结果集中的重复行union all也用于取得两个查询结果的并集但union all不会去掉结果集中的重复行 将工资大于2500或职位是MANAGER的人找出来 查询工资大于2500的员工查询职位是MANAGER的员工 可以使用or操作符将where子句中的两个条件关联起来 也可以使用union将上述的两条查询SQL连接起来这时将会得到两次查询结果的并集并且会对合并后的结果进行去重
mysql select ename, job, sal from emp where sal 2500 union- select ename, job, sal from emp where sal 2500 or job MANAGER;可以使用union all结果是不去重 注意待合并的两个查询结果的列的数量必须一致否则无法合并 --------------------- END ----------------------
「 作者 」 枫叶先生
「 更新 」 2023.8.25
「 声明 」 余之才疏学浅故所撰文疏漏难免或有谬误或不准确之处敬请读者批评指正。
