(SQL 单表，多表关联查询案例)2020.11.18

SQL 单表查询

-- 需求4：聚合函数的使用：
--          聚合函数 count() 的使用  
-- 4.1:统计 gjp_ledger表*有多少条数据。
select 
  COUNT(*) 
from
  gjp_ledger ;

-- 提示：使用count(*)
-- 4.2：count 统计所有工商银行的内容，共有多少条
select  COUNT(*) from  gjp_ledger g where g.`account` ='工商银行';
-- 提示：使用 LIKE + '%内容%'
			
-- 4.3：统计账务表中金额大于3000的有多少条记录
-- 提示：使用比较运算符 >
select  COUNT(*)  from gjp_ledger g  where g.`money`>3000
		
-- 聚合函数 sum() 的使用  
-- 询账务表，求出money字段的和
select  SUM(g.`money`)  from gjp_ledger g
-- 提示：使用sum(字段名)
-- 4.5：查询账务表，统计所有支出的金额总和
select SUM(g.`money`) '支出总和'  from gjp_ledger g  where g.`parent` ='支出'
-- 提示：查询条件为 '支出'
-- 聚合函数：max,min 求出最大和最小值
-- 4.6：查询账务表，统计出 支出中的最大值 和 收入中的最小值
select  MAX(g.`money`) from gjp_ledger g    where g.`parent`='支出'
select  MIN(g.`money`) from gjp_ledger g    where g.`parent`='收入'
-- 	聚合函数：avg():-- 
-- 		4.7:查询账务表，计算出，所有支出的平均数
select AVG(g.`money`)   from gjp_ledger g  where g.`parent`='支出'
-- 			提示：avg(字段) 计算这个字段的平均数
-- 	排序order by:
-- 		4.8 :查询账务表，使用money字段，进行升序排序 
select  * from  gjp_ledger g  order by g.money asc
-- 		4.9 ：查询账务表，使用createtime字段，进行降序排序 
select  *   from gjp_ledger g order by g.`createtime` desc 
-- 需求2：分组 group by的使用
	-- 查询出，支出的共计多少钱，收入的共计多少钱
select  SUM(g.`money`) '总计',g.`parent`  from gjp_ledger g  group by g.`parent`
-- 提示：需要对支出和收入进行分组
		
	-- 查询出，所有的金额总和，先按照支出和收入分类，再按照银行的类型分类。
select  SUM(g.`money`) '总计',g.`parent`,g.`account`  from gjp_ledger g  group by g.`parent`,account 
having  g.`account`!='现金'	
-- 提示：需要对 支出分组，然后对银行类型分
		
	-- 查询出，支出的共计多少钱，收入的共计多少钱， 只显示大于20000元的   
select  SUM(g.`money`),g.`parent`  from gjp_ledger  g  group by g.`parent` having SUM(g.`money`)>20000
-- 提示：使用 having 子句，跟随分组，分组后，对结果再次过滤

SQL 关联查询

-- 1.返回员工信息以及员工所在的部门名称和部门地址。
select 
 e.*,d.dname,d.`LOC`  
   from emp e,dept d
     where e.deptno =d.deptno 
-- 2.工资水平多于smith的员工信息。
-- 1-1查询smith工资多少
select  e.sal   from emp e where e.ename ='smith'
-- 1-2 拿着所有员工的薪资和smith进行比较
select  * from emp e where e.sal >(select  e.sal   from emp e where e.ename ='smith')
-- 3.返回员工和所属经理的姓名。人员的经理编号 =  经理的普通人员编号
select   
   e.ename,m.ename
   from emp e,emp m 
     where e.mgr =m.empno 
     
select    
     e.ename,m.ename
     from emp e left join emp m
      on e.mgr =m.empno
-- 4.返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名

select  
  e.ename,e.hiredate '员工入职日期', m.ename,m.hiredate '经理入职日期'
  from  
    emp e,emp m
     where e.mgr=m.empno 
       and e.hiredate < m.hiredate
-- 5.返回员工姓名及其所在的部门名称。
select  
     e.ename,d.dname
     from  emp e inner join dept d
      on e.deptno =d.deptno 
-- 6.返回从事clerk工作的员工姓名和所在部门名称。
select  
      e.ename,d.dname,e.job
      from  emp e,dept d
        where e.deptno = d.deptno 
         and e.job ='clerk'
-- 7.返回部门号及其本部门的最低工资。
select  MIN(e.sal),e.deptno  from emp e group by e.deptno 
-- 8.返回销售部(sales)所有员工的姓名。

select
    e.ename,d.dname
    from emp e,dept d
      where e.deptno =d.deptno 
       and d.dname ='sales'
--  9.返回工资水平多于平均工资的员工。
-- 1-1 先查询平均薪资是多少
select  AVG(e.sal) from emp e 
-- 1-2 拿所有员工的薪资和平均薪资进行比较
select  *  from emp e where e.sal >(select  AVG(e.sal) from emp e )
-- 10.返回与30部门员工工资水平相同的员工姓名与工资。(850,900,1200...)
-- 1-1 查询30部门所有员工的薪资
select  e.sal     from emp e where e.deptno =30 
-- 1-2 拿所有员工的薪资和这个薪资进行对比如果相同就符合条件 
-- in 表示小括号中其中一个值符合就行 类似于or
select  * from emp e where e.sal in (950,1500,5000);

select  *  from emp e where e.sal in(select  e.sal  from emp e where e.deptno =30)
-- 11.返回工资高于30部门所有员工工资水平的员工信息。	
-- 1-1 先查询30部门最高人的薪资
select  MAX(e.sal)  from emp e where e.deptno =30;
-- 1-2 拿所有人的薪资和最高的进行比较
select *   from emp e where e.sal > (select  MAX(e.sal)  from emp e where e.deptno =30)
-- 12.返回部门号、部门名、部门所在位置及其每个部门的员工总数。
select   
     COUNT(*),d.*
     from emp e,dept d 
      where e.deptno =d.deptno 
         group by e.deptno 
-- 13.返回员工的姓名、所在部门名及其工资。
select 
     e.ename,d.dname,e.sal
    from emp e,dept d
     where e.deptno =d.deptno 

SQL 多表关联查询 进阶案例

-- 1、取出每个部门薪水最高的员工的名称
-- 1-1 查询每个部门最高的薪资
select  MAX(e.sal),e.deptno  from emp e group by e.deptno 
-- 1-2 在第一步的基础和员工表进行关联求出最高薪资人员
select
 e.ename,e.sal,e.deptno  
   from 
     emp e,(select  MAX(e.sal)maxsal,e.deptno  from emp e group by e.deptno)m
       where e.deptno =m.deptno  
          and e.sal =m.maxsal
-- 2、哪些人的薪水在各自部门平均薪水之上
-- 1-1 查询每个部门的平均薪资
select  AVG(e.sal),e.deptno  from emp e group by e.deptno 
-- 1-2 在第一步的基础上和员工表进行关联求出高于平均薪资的人
select  
     e.ename,e.sal,m.avgsal
     from 
       emp e,(select  AVG(e.sal)avgsal,e.deptno  from emp e group by e.deptno ) m
         where e.deptno=m.deptno 
           and e.sal >m.avgsal 
-- 3、不准使用MAX，求出最高薪水 
select  * from emp e order by e.sal desc  limit 0,1
-- 4、获得平均薪水最高的部门的部门编号
-- 1-1 查询每个部门平均薪资
select  e.deptno,AVG(e.sal)   from  emp e group by e.deptno 
-- 1-2 在第一步的基础上求平均值的最大值
select MAX(m.avgsal)  from (select  e.deptno,AVG(e.sal)avgsal   from  emp e group by e.deptno)m
-- 1-3 在第二步的基础上求平均薪资为第二步结果的部门号
select  e.deptno,AVG(e.sal) from emp e group by e.deptno having AVG(e.sal) =(select MAX(m.avgsal)  from (select  e.deptno,AVG(e.sal)avgsal   from  emp e group by e.deptno)m)
-- 5、取得平均薪水最高的部门的部门名称
-- 1-1 查询每个部门的平均薪资
select  e.deptno,AVG(e.sal)   from emp e group by e.deptno 
-- 1-2 求最高的平均薪资
select  MAX(m.avgsal)  from (select  e.deptno,AVG(e.sal)avgsal  from emp e group by e.deptno)m
-- 1-3 在第二步基础上求平均薪资最高的部门名称
select 
      AVG(e.sal),e.deptno,d.dname
       from 
          emp e,dept d
             where  e.deptno =d.deptno        
                group by e.deptno 
                   having  AVG(e.sal) =(select  MAX(m.avgsal)  from (select  e.deptno,AVG(e.sal)avgsal  from emp e group by e.deptno)m)
-- 6、获取每个薪水等级对应多少员工
select  
   s.grade,COUNT(*)
   from emp e,salgrade s 
      where e.sal between s.losal and hisal 
        group by s.grade 
-- 7、列出至少有5个员工的部门
select   COUNT(*),e.deptno  from emp e group by e.deptno having COUNT(*)>=5
-- 8、列出所有 “CLERK”（办事员）部门名称，部门人数
select 
    d.dname,COUNT(*)
    from 
       emp e,dept d
         where e.deptno =d.deptno 
          and e.job ='CLERK'
            group by e.deptno 
-- 9、列出薪资最低大于1500的各种工作，以及从事此工作雇员的全部人数
select e.job,MIN(e.sal),COUNT(*)    from emp e group by e.job having MIN(e.sal)>1500
-- 10、列出各种工作最低工资，以及对应雇员的姓名
-- 1-1 求出各种工作的最低工资
select  e.job,MIN(e.sal) from  emp e group by e.job 
-- 1-2 在第一步的基础上查询最低工资从事雇员名称
select    
    e.ename,e.job,m.minsal
    from 
      emp e,(select  e.job,MIN(e.sal)minsal from  emp e group by e.job) m
        where e.sal = m.minsal