怎么做刷会员网站,百度竞价被点击软件盯上,设计方案怎么写格式,软件外包公司如何找客源文章目录 MySQL复合查询1. 基本查询回顾(1) 查询工资高于500或岗位为MANAGER的雇员#xff0c;同时还要满足他们的姓名首字母为大写的J(2) 按照部门号升序而雇员的工资降序排序(3) 使用年薪进行降序排序(4) 显示工资最高的员工的名字和工作岗位(5) 显示工资高于平均工资的员工… 文章目录 MySQL复合查询1. 基本查询回顾(1) 查询工资高于500或岗位为MANAGER的雇员同时还要满足他们的姓名首字母为大写的J(2) 按照部门号升序而雇员的工资降序排序(3) 使用年薪进行降序排序(4) 显示工资最高的员工的名字和工作岗位(5) 显示工资高于平均工资的员工信息(6) 显示每个部门的平均工资和最高工资(7) 显示平均工资低于2000的部门号和它的平均工资(8) 显示每种岗位的雇员总数平均工资 2. 多表查询(1) 笛卡尔积(2) 显示雇员名、雇员工资以及所在部门的名字(3) 显示部门号为10的部门名员工名和工资(4) 显示各个员工的姓名工资及工资级别 3. 自连接(1) 显示员工FORD的上级领导的编号和姓名 4. 子查询4.1 在where子句中充当判断条件4.1.1 单行子查询(1) 显示SMITH同一部门的员工 4.1.2 多行子查询(1) 查询和10号部门的工作岗位相同的雇员的名字岗位工资部门号但是不包含10自己的(2) 查询和10号部门的工作岗位相同的雇员的名字岗位工资部门号所在的部门名称但是不包含10自己的(3) 显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号(4) 显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号(包含自己部门的员工) 4.1.3 多列子查询(1) 查询和SMITH的部门和岗位完全相同的所有雇员不含SMITH本人 4.2 在from子句中使用子查询(1) 显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资(2) 显示每个高于自己部门平均工资的员工的姓名、工作地点、部门(3) 查找每个部门工资最高的人的姓名、工资、部门、最高工资(4) 显示每个部门的信息部门名编号地址和人员数量 5. 合并查询5.1 union(1) 将工资大于2500或职位是MANAGER的人找出来 5.2 union all(1) 将工资大于25000或职位是MANAGER的人找出来 MySQL复合查询
1. 基本查询回顾
(1) 查询工资高于500或岗位为MANAGER的雇员同时还要满足他们的姓名首字母为大写的J
mysql select * from emp where (sal 500 or job MANAGER) and ename like J%;也可以使用内置函数来查询首字母为大写的J
mysql select * from emp where (sal 500 or job MANAGER) and - substring(ename, 1, 1) J;(2) 按照部门号升序而雇员的工资降序排序
mysql select * from emp order by deptno asc, sal desc;(3) 使用年薪进行降序排序
年薪 月薪(sal) * 12 奖金(comm)
这里有的comm为NULLNULL与任何值做计算得到的结果都是NULL所以若是直接用NULL来计算得到结果还是NULL。comm的运算可以使用ifnull函数来comm判断是否为NULL若不为NULL则ifnull函数返回员工的comm如果为NULL则ifnull函数返回0避免让NULL值参与计算
直接用comm的NULL参与运算 正确写法
mysql select ename, sal*12ifnull(comm,0) as 年薪 from emp order by 年薪 desc;(4) 显示工资最高的员工的名字和工作岗位
解决该问题有两种方案:
使用两次查询使用一次子查询(推荐)
两次查询
mysql select max(sal) from emp;
mysql select ename, job from emp where sal 5000;一次子查询将两条查询语句合并起来where条件筛选中再使用select进行筛选此语句执行的顺序是由内向外先执行where中的select再执行整句中的select
mysql select ename, job from emp where sal (select max(sal) from emp);(5) 显示工资高于平均工资的员工信息
同样使用子查询
mysql select * from emp where sal (select avg(sal) from emp);(6) 显示每个部门的平均工资和最高工资
在group by子句中指明按照部门号进行分组在select语句中使用avg函数和max函数分别查询每个部门的平均工资和最高工资
mysql select deptno, format(avg(sal),2), max(sal) from emp group by deptno;(7) 显示平均工资低于2000的部门号和它的平均工资
在group by子句中指明按照部门号进行分组在select语句中使用avg函数查询每个部门的平均工资在having子句中指明筛选条件为平均工资小于2000
mysql select deptno, avg(sal) as 平均工资 from emp group by deptno having 平均工资 2000;(8) 显示每种岗位的雇员总数平均工资
mysql select job, count(*), format(avg(sal),2) from emp group by job;2. 多表查询
实际开发中往往数据来自不同的表所以需要多表查询。本节我们用一个简单的公司管理系统有三张表EMP, DEPT, SALGRADE 来演示如何进行多表查询。
(1) 笛卡尔积
员工表 部门表 我们想让两个表联合显示为一张表
mysql select * from emp, dept;我们会发现上面的表是这样显示的
整张表由两个表构成前半部分是员工表的列信息后半部分是部门表的列信息。 同时员工表中每条记录与部门表进行穷举组合员工表中的每一个员工信息都会和部门表中的每一个部门信息进行组合。 我们这种从第一张表中选取一条记录与第二张表中的所有记录进行穷举组合且不加过滤条件得到的结果称为笛卡尔积。 但是在实际开发中一个员工只有和自己所在的部门信息进行组合才是有意义的因此需要我们在笛卡尔积产生的新表中筛选出真正需要的数据
(2) 显示雇员名、雇员工资以及所在部门的名字
笛卡尔积的表中可能会存在相同的列名这时在选中列名时需要通过表名.列名的方式进行指明若有重复的不指明确切的一列就会报错
mysql select ename,sal,dname from emp, dept where emp.deptno dept.deptno;(3) 显示部门号为10的部门名员工名和工资
mysql select ename, sal, emp.deptno, dname from emp, dept where dept.deptnoemp.deptno and dept.deptno 10;(4) 显示各个员工的姓名工资及工资级别
mysql select ename, sal, grade from emp, salgrade where sal between losal and hisal;3. 自连接
自连接是指在同一张表连接查询即我们可以对同一张表取笛卡尔积
(1) 显示员工FORD的上级领导的编号和姓名
可以分为2步
先找到FORD的领导的编号根据领导的编号找领导信息
这2步合并为1步使用子查询
使用子查询
mysql select empno, ename from emp where empno (select mgr from emp where ename FORD);使用多表查询(自查询)
对同一张表做笛卡尔积要给自己的表起别名
mysql select * from emp e1, emp e2;图只放了一部分
mysql select e2.empno, e2.ename from emp e1, emp e2 where e1.enameFORD - and e1.mgr e2.empno;4. 子查询
子查询是指嵌入在其他sql语句中的select语句也叫嵌套查询
4.1 在where子句中充当判断条件
4.1.1 单行子查询
返回一行记录的子查询
(1) 显示SMITH同一部门的员工
在子查询中查询SMITH所在的部门号在where子句中指明筛选条件为员工部门号等于子查询返回的部门号
mysql select * from emp where deptno (select deptno from emp where ename SMITH);4.1.2 多行子查询
返回多行记录的子查询
(1) 查询和10号部门的工作岗位相同的雇员的名字岗位工资部门号但是不包含10自己的
in关键字: 判断某个对应列值是否在集合当中
可以先查询10号部门有哪些岗位查询时要对结果进行去重因为10号部门的某些员工的工作岗位可能是相同的 再将上述查询作为子查询在查询员工表时在where子句中使用in关键字判断员工的工作岗位是子查询得到的若干岗位中的一个
mysql select ename,job,sal, deptno from emp where job in(select distinct job from emp where deptno 10) and deptno10;(2) 查询和10号部门的工作岗位相同的雇员的名字岗位工资部门号所在的部门名称但是不包含10自己的
拿着查询出来的表结构和其他查询结果做笛卡尔积
mysql select ename, job,sal,dname from(select ename,job,sal, deptno from emp where job in(select distinct job from emp where deptno 10) and deptno100) as tmp, dept where dept.deptnotmp.deptno;(3) 显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
all关键字
mysql select ename,sal,deptno from emp where sal all(select distinct sal from emp where deptno 30);(4) 显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号(包含自己部门的员工)
any关键字
mysql select ename,sal,deptno from emp where sal any(select distinct sal from emp where deptno 30);4.1.3 多列子查询
单行子查询是指子查询只返回单列单行数据多行子查询是指返回单列多行数据都是针对单列而言的而多列子查询则是指查询返回多个列数据的子查询语句
(1) 查询和SMITH的部门和岗位完全相同的所有雇员不含SMITH本人
mysql select * from emp where (deptno, job) (select deptno, job from emp where ename SMITH) and enameSMITH;#这里也可以把改成in
mysql select * from emp where (deptno, job) in (select deptno, job from emp where ename SMITH) and enameSMITH;上面除(2)外所有的子查询全部在where子句中充当判断条件 任何时候查询出来的临时结构本质在逻辑上也是表结构即MySQL中一切皆表
4.2 在from子句中使用子查询
子查询语句出现在from子句中。这里要用到数据查询的技巧把一个子查询当做一个临时表使用。
多表查询的指导思想
解决多表问题的本质想办法将多表转化成单表所以MySQL中所有select的问题全部都可以转成单表问题
(1) 显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资
# 显示每个部门的平均工资,将其看作临时表
mysql select deptno, avg(sal) mysal from emp group by deptno;# 多表查询
mysql select * from emp, (select deptno, avg(sal) mysal from emp group by deptno) tmp - where sal tmp.mysal and emp.deptnotmp.deptno;# 单表查询
mysql select ename,emp.deptno,sal,mysal from emp, (select deptno, avg(sal) mysal from emp group by deptno) tmp where sal tmp.mysal and emp.deptnotmp.deptno; (2) 显示每个高于自己部门平均工资的员工的姓名、工作地点、部门
mysql select * from dept;# 多表查询
mysql select * from dept, (select ename,emp.deptno,sal,mysal from emp, (select deptno, - avg(sal) mysal from emp group by deptno) tmp where sal tmp.mysal and - emp.deptnotmp.deptno) t1;mysql select * from dept, (select ename,emp.deptno,sal,mysal from emp, (select deptno, - avg(sal) mysal from emp group by deptno) tmp where sal tmp.mysal and - emp.deptnotmp.deptno) t1 where t1.deptnodept.deptno;# 单表查询
mysql select t1.ename,dept.loc,t1.deptno from dept, (select ename,emp.deptno,sal,mysal - from emp, (select deptno, avg(sal) mysal from emp group by deptnoo) tmp where sal - tmp.mysal and emp.deptnotmp.deptno) t1 where t1.deptnodept.deptno;(3) 查找每个部门工资最高的人的姓名、工资、部门、最高工资
# 每个部门的最高工资
mysql select deptno, max(sal) from emp group by deptno;# 多表查询
mysql select * from emp e1, (select deptno, max(sal) from emp group by deptno) e2;mysql select * from emp e1, (select deptno, max(sal) mysal from emp group by deptno) e2 where e1.deptnoe2.deptno and e1.sale2.mysal;# 单表查询
mysql select ename, sal, e1.deptno, mysal from emp e1, (select deptno, max(sal) mysal - from emp group by deptno) e2 where e1.deptnoe2.deptno and e1.sale2.mysal;(4) 显示每个部门的信息部门名编号地址和人员数量
使用子查询多表
# 每个部门的人数
mysql select deptno, count(*) dept_num from emp group by deptno;# 多表查询
mysql select * from dept t1, (select deptno, count(*) dept_num from emp group by deptno) - t2 where t1.deptnot2.deptno;# 单表查询
mysql select dname,t1.deptno,loc, dept_num from dept t1, (select deptno, count(*) - dept_num from emp group by deptno) t2 where t1.deptnot2.deptno;5. 合并查询
在实际应用中为了合并多个select的执行结果可以使用集合操作符 unionunion all
5.1 union
该操作符用于取得两个结果集的并集。当使用该操作符时会自动去掉结果集中的重复行。
(1) 将工资大于2500或职位是MANAGER的人找出来
mysql select * from emp where jobMANAGER;
mysql select * from emp where sal 2500 union select * from emp where jobMANAGER;
mysql select * from emp where sal 2500 union select * from emp where jobMANAGER;5.2 union all
该操作符用于取得两个结果集的并集。当使用该操作符时不会去掉结果集中的重复行。
(1) 将工资大于25000或职位是MANAGER的人找出来
mysql select * from emp where sal 2500 union all select * from emp where job MANAGER;
