智能网站建设哪家好,学做淘宝客网站,yw55523can优物入口4虎,高端购物网站目录
基本查询回顾
多表查询
自连接
子查询
单行子查询
多行子查询
多列子查询 在from子句中使用子查询 合并查询 前面我们讲解的mysql表的查询都是对一张表进行查询#xff0c;在实际开发中这远远不够。
基本查询回顾
【MySQL数据库】#xff1a;MySQL基本查…目录
基本查询回顾
多表查询
自连接
子查询
单行子查询
多行子查询
多列子查询 在from子句中使用子查询 合并查询 前面我们讲解的mysql表的查询都是对一张表进行查询在实际开发中这远远不够。
基本查询回顾
【MySQL数据库】MySQL基本查询-CSDN博客
我们借用上述文章分组查询的表 雇员信息表中包含三张表分别是员工表emp、部门表dept和工资等级表salgrade。 员工表emp中包含如下字段 雇员编号empno雇员姓名ename雇员职位job雇员领导编号mgr雇佣时间hiredate工资月薪sal奖金comm部门编号deptno 部门表dept中包含如下字段
部门编号deptno部门名称dname部门所在地点loc 工资等级表salgrade中包含如下字段
等级grade此等级最低工资losal此等级最高工资hisal 查询工资高于500或岗位为MANAGER的员工同时要求员工姓名的首字母为大写的J select ename,job,sal from emp where (sal500 or enameMANAGER) and ename like J%; 查询员工信息按部门号升序而员工工资降序显示 不同部门的员工按照部门号排升序而同一部门的员工按员工工资排降序。 查询员工信息按年薪降序显示 select ename, sal*12ifnull(comm,0) as 年薪 from emp order by 年薪 desc; 注意
由于NULL与任何值做计算得到的结果都是NULL因此在计算年薪时不能直接用月薪的12倍加上每个员工的奖金这样可能导致得到的年薪为NULL值。在计算每个员工的年薪时应该通过ifnull函数判断员工的奖金是否为NULL如果不为NULL则ifnull函数返回员工的奖金如果为NULL则ifnull函数返回0避免让NULL值参与计算。 查询工资最高的员工的姓名和岗位 select ename, job from EMP where sal (select max(sal) from emp); 查询工资高于平均工资的员工信息 查询每个部门的平均工资和最高工资 select deptno,format(avg(sal),2) 平均工资,max(sal) 最高工资 from emp group by deptno; 查询平均工资低于2000的部门号和它的平均工资 select deptno,avg(sal) 平均工资 from emp group by deptno having 平均工资2000; 查询每种岗位的雇员总数和平均工资 select job,count(*), format(avg(sal),2) from emp group by job; 多表查询
上面的基础查询都是在一张表的基础上进行的查询而实际开发中往往需要将多张表关联起来进行查询这就叫做多表查询。在进行多表查询时只需要将多张表的表名依次放到from子句之后用逗号隔开即可这时MySQL将会对给定的这多张表取笛卡尔积作为多表查询的初始数据源。多表查询的本质就是对给定的多张表取笛卡尔积然后在笛卡尔积中进行查询。 笛卡尔积的初步过滤 需要注意的是对多张表取笛卡尔积后得到的数据并不都是有意义的比如对员工表和部门表取笛卡尔积时员工表中的每一个员工信息都会和部门表中的每一个部门信息进行组合而实际一个员工只有和自己所在的部门信息进行组合才是有意义的因此需要从笛卡尔积中筛选出员工的部门号和部门的编号相等记录。
select * from emp,dept where emp.deptnodept.deptno; 显示部门号为10的部门名、员工名和员工工资 部门名只有部门表中才有而员工名和员工工资只有员工表中才有因此需要同时使用员工表和部门表进行多表查询
select dname,ename,sal from emp,dept where emp.deptnodept.deptno and emp.deptno10; 显示各个员工的姓名、工资和工资级别 员工名和工资只有员工表中才有而工资级别只有工资等级表中才有因此需要同时使用员工表和工资等级表进行多表查询
select ename,sal,grade from emp,salgrade where sal between losal and hisal; 自连接
自连接是指在同一张表进行连接查询也就是说我们不仅可以取不同表的笛卡尔积也可以对同一张表取笛卡尔积。如果一张表中的某个字段能够将表中的多条记录关联起来那么就可以通过自连接将表中通过该字段关联的记录组合起来。 显示员工FORD的上级领导的编号和姓名 我们可以使用子查询先对员工表进行查询得到FORD的领导的编号然后再根据领导的编号对员工表进行查询得到FORD领导的姓名
select empno,ename from emp where empno(select mgr from emp where enameFORD); 我们也可以使用自连接因为员工表中的mgr字段能够将表中员工的信息和员工领导的信息关联起来。
由于自连接是对同一张表取笛卡尔积因此在自连接时至少需要给一张表取别名否则无法区分这两张表中的列。
SELECT leader.* FROM emp AS leader; select leader.empno,leader.ename from emp leader, emp
where leader.empno emp.mgr and emp.enameFORD; 子查询
子查询是指嵌入在其他SQL语句中的查询语句也叫嵌套查询。子查询可分为单行子查询、多行子查询、多列子查询以及在from子句中使用的子查询。
单行子查询 显示SMITH同一部门的员工 select * from emp where deptno(select deptno from emp where enamesmith); 多行子查询 in关键字显示和10号部门的工作岗位相同的员工的名字、岗位、工资和部门号 select ename,job,sal,deptno from emp
where job in(select distinct job from emp where deptno10); all关键字显示工资比30号部门的所有员工的工资高的员工的姓名、工资和部门号 select ename, sal, deptno from emp
where sal all(select sal from emp where deptno30); any关键字显示工资比30号部门的任意员工的工资高的员工的姓名、工资和部门号包含30号部门的员工 select ename, sal, deptno from emp
where sal any(select sal from emp where deptno30); 多列子查询 显示和SMITH的部门和岗位完全相同的员工不包含SMITH本人 select * from emp
where (deptno, job)(select deptno, job from emp where enameSMITH)
and ename SMITH; 注意
多列子查询得到的结果是多列数据在比较多列数据时需要将待比较的多个列用圆括号括起来。多列子查询返回的如果是多行数据在筛选数据时也可以使用in、all和any关键字。 在from子句中使用子查询
子查询语句不仅可以出现在where子句中也可以出现在from子句中。子查询语句出现from子句中其查询结果将会被当作一个临时表使用。
注意 在from子句中使用子查询时必须给子查询得到的临时表取一个别名否则查询将会出错。 显示每个高于自己部门平均工资的员工的姓名、部门、工资和部门的平均工资 使用子查询
1. 对emp表首先查询每个部门的平均工资
select avg(sal) asal, deptno dt from emp group by deptno;
2. 将上面的表看作临时表
select ename, deptno, sal, format(asal,2)
from emp,(select avg(sal) asal, deptno dt from emp group by deptno) tmp
where emp.sal tmp.asal and emp.deptnotmp.dt; 显示每个部门工资最高的员工的姓名、工资、部门和部门的最高工资 使用子查询
1.先查询每个部门的最高工资
select max(sal) ms, deptno from emp group by deptno;
2. 将上面的表看作临时表
select emp.ename, emp.sal,emp.deptno, ms
from emp, (select max(sal) ms, deptno from emp group by deptno) tmp
where emp.deptnotmp.deptno and emp.saltmp.ms; 显示每个部门的部门名、部门编号、所在地址和人员数量 使用多表
select DEPT.dname, DEPT.deptno, DEPT.loc,count(*) 部门人数
from emp,DEPT
where emp.deptnoDEPT.deptno
group by DEPT.deptno,DEPT.dname,DEPT.loc;使用子查询
1. 对emp表进行人员统计
select count(*), deptno from emp group by deptno;
2. 将上面的表看作临时表
select dept.deptno, dname, mycnt, loc
from dept,(select count(*) mycnt, deptno from emp group by deptno) tmp
where dept.deptnotmp.deptno;合并查询
合并查询是指将多个查询结果进行合并可使用的操作符有union和union all。
union用于取得两个查询结果的并集union会自动去掉结果集中的重复行。union all也用于取得两个查询结果的并集但union all不会去掉结果集中的重复行。 显示工资大于2500或职位是MANAGER的员工 1、查询工资大于2500的员工
select ename,job,sal from emp where sal2500;2、查询职位是MANAGER的员工
select ename,job,sal from emp where jobMANAGER;3、查询工资大于2500或职位是MANAGER的员工可以使用or操作符将where子句中的两个条件关联起来。
select ename,job,sal from emp where sal2500 or jobMANAGER;4、我们可以使用union操作符将上述的两条查询SQL连接起来这时将会得到两次查询结果的并集并且会对合并后的结果进行去重。
select ename,job,sal from emp where sal2500
union
select ename,job,sal from emp where jobMANAGER;
1、查询工资大于2500的员工
2、查询职位是MANAGER的员工
3、查询工资大于2500或职位是MANAGER的员工可以使用or操作符将where子句中的两个条件关联起来。 4、我们可以使用union操作符将上述的两条查询SQL连接起来这时将会得到两次查询结果的并集并且会对合并后的结果进行去重。 注意
待合并的两个查询结果的列的数量必须一致否则无法合并。待合并的两个查询结果对应的列属性可以不一样但不建议这样做。
