DQL 练习

单表查询

• 找出奖金高于工资60%的员工

  SELECT * 
  FROM emp
  WHERE COMM>SAL*0.6;
  

• 找出部门编号10所有经理，部门编号20中所有销售员，即不是经理又不是销售员但其工资大或等于20000的...

  select * 
  from emp
  where (DEPTNO=10 AND JOB='经理') OR (DEPTNO=20 AND JOB='销售员') OR (JOB NOT IN('经理','销售员') AND SAL>=20000);
  

• 查询2000年入职的员工

  select * 
  from emp
  WHERE HIREDATE LIKE '2002%';
  

• 查询所有员工详细信息，用工资降序排序，如果工资相同使用入职日期升序排序

  select * 
  from emp
  order by SAL desc,HIREDATE ASC;
  

• 查询每种工作的最高工资、最低工资、平均工资、人数

  select JOB,AVG(SAL) 平均工资,MAX(SAL) AS 最高工资,MIN(SAL)最低工资,COUNT(*) 人数 
  from emp
  group by JOB;
  

• 有奖金的工种

  select JOB 
  FROM emp
  where COMM IS NOT NULL
  group by JOB;
  

• 显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计大于50000，输出结果按月工资的合计升序排列

  select JOB,SUM(SAL) 
  FROM EMP
  GROUP BY JOB
  HAVING SUM(SAL)>50000 AND JOB<>'销售员'
  ORDER BY SUM(SAL) ASC;
  

• 🔥 如下表，将num处于 [20,29] 之间的数改为20，[30,39] 之间的数改为30。变量计算

  demo 表
  +-----+
  | num |
  +-----+
  |  3  |
  |  12 |
  |  15 |
  |  23 |
  |  28 |
  |  32 |
  |  39 |
  |  42 |
  |  49 |
  +-----+
  

  update table demo set num = floor(num/10)*10 where num between 20 and 39
  

• 🔥 把 GOOD 表中商品名为 “红米xxxx“ 的商品改为 ”小米加步枪xxxx“

  update good set name = CONCAT('h红米', SUBSTRING(name ,7)) where name like 'x小米加步枪%';
  

  注意，如果使用 length 时，需要记清楚它计算的是字节长度，汉字会占用2个字节哦。SUBSTRING 的 index 从 1开始，取长度（不写则到最后），好像不存在字节长度问题！！！

• 🔥 如下表，查询2门及2门以上不及格者的平均成绩

  Name	Subject	Score
  张三	数学	90
  张三	语文	50
  张三	地理	40
  李四	语文	55
  李四	政治	45
  王五	政治	30

  -- 1 查询最终结果
  select name, avg(score)
  from score
  group by name;
  
  -- 2 如何处理不及格（不能在聚合函数中写表达式，没有效果）
  -- score < 60 true 为 1， false 为 0，据此可以使用 having 来筛选
  select name, score, score < 60
  from score;
  
  -- 3 只能用 sum 哦，count 会把返回的 0 或 1 都统计进去
  select name, sum(score < 60) gk, avg(score)
  from score group by name having gk >= 2;
  
  -- 简写
  select name, avg(score)
  from score group by name having sum(score < 60) >= 2;
  

多表查询

• 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数

SELECT d.deptno,d.dname,d.loc,t.cnt
FROM dept d 
    JOIN (SELECT emp.deptno, COUNT(*) cnt FROM emp GROUP BY emp.deptno HAVING COUNT(*)>1) t 
    ON d.deptno = t.deptno;

• 列出薪金比关羽高的所有员工

SELECT * 
FROM emp
WHERE sal>(SELECT sal FROM emp WHERE ename='关羽')

• 列出所有员工的姓名及其直接上级的姓名

SELECT e.ename, IFNULL(m.ename, 'BOSS') 领导
FROM emp e LEFT JOIN emp m
ON e.mgr=m.empno;

• 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称(三张表)

SELECT e.empno, e.ename, d.dname
FROM emp e 
    JOIN emp m ON e.mgr=m.empno 
    JOIN dept d ON e.deptno=d.deptno
WHERE e.hiredate<m.hiredate;

• 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级(四张表)

SELECT emp.*,dept.dname,e2.ename,salgrade.grade
FROM emp  
    LEFT JOIN dept ON emp.deptno = dept.deptno 
    LEFT JOIN emp e2  ON emp.mgr = e2.empno
    LEFT JOIN salgrade ON emp.sal BETWEEN salgrade.losal AND salgrade.hisal 
WHERE emp.sal>(SELECT AVG(emp.sal) FROM emp);

• 查出年份、利润、年度增长比

SELECT E1.*,IFNULL(CONCAT((E1.zz-E2.zz)/E2.zz*100,'%'),0)
FROM lirun E1 
    JOIN lirun E2 ON E1.`year`=E2.`year`+1
ORDER BY E1.`year` ASC;

• 🔥 union。如下 a，b 表，求出 c 表

  +----+-----+
  |name|value|
  +----+-----+
  |a   |5    |
  |b   |10   |
  |c   |15   |
  |d   |10   |
  +----+-----+
  

  +----+-----+
  |name|value|
  +----+-----+
  |b   |5    |
  |c   |15   |
  |d   |20   |
  |e   |99   |
  +----+-----+
  

  +----+----------+
  |name|sum(value)|
  +----+----------+
  |a   |5         |
  |b   |15        |
  |c   |30        |
  |d   |30        |
  |e   |99        |
  +----+----------+
  

  解法一：

  select name, sum(value)
  from (select name,value from a
  union all
  select name,value from b) t group by name;
  

  解法二（连接查询）：

  select a.name, coalesce(a.value, 0) + coalesce(b.value, 0)  sum
  from a left join b on a.name = b.name;
  

  但是该方式可能会造成a表没有的结果集中也没有