sql第四天

2023-12-09 20:00:39

排序分组

排序

语法：

SELECT COLUMN1,COLUMN2,COLUMN3 FROM TABLE_NAME (WHERE COMDITION) ORDER BY COLUMN1 ASC/DESC;

应用：将员工信息按照工资升序排列
→

SELECT * FROM EMP ORDER BY SAL ASC;

应用：

SELECT EMPNO,ENAME,SAL,SAL*12,JOB FROM EMP ORDER BY 1 ;--按照MPNO,ENAME,SAL,SAL*12,JOB这几列中的第1列排序

SELECT EMPNO,ENAME,SAL,SAL*12,JOB FROM EMP ORDER BY 8 ;--报错，因为前面查询的列中没有第8列

SELECT EMPNO,ENAME,SAL,SAL*12,JOB FROM EMP ORDER BY DEPTNO,SAL ;--可以跟多列排序，首先按照第一列排序，如果第一列的值相等，再按第二列排序

SELECT * FROM EMP ORDER BY DEPTNO DESC,SAL ASC;--规则上一个排序列对应一个排序方式

SELECT EMPNO,ENAME,SAL,JOB FROM EMP ORDER BY 'A';--没有意义

SELECT * FROM EMPORDER BY COMM ASC;--有NULL值时默认视为最大

SELECT * FROM EMP ORDER BY COMM ASC NULLS LAST;
SELECT * FROM EMP ORDER BY COMM ASC NULLS FIRST;

←将NULL值自定义前后排序顺序

ROWNUM

ROWNUM:性质是伪列 /可以查询的/查询结果为一组递增的数字

ROWNUM的应用:

SELECT EMPNO,ENAME,SAL,JOB,ROWNUM FROM EMP;

→
sql第四天
sql第四天
→
sql第四天
应用：查询EMP 表中ROWNUM为1,2,3的员工信息
→

SELECT EMP.*,ROWNUM FROM EMP WHERE ROWNUM<=3;--每次有多个表名出现时，应当在通配符 *前注明表名

注意：
1.ROWNUM只能用于小于等于查询(有破解方法，给rownum起别名)
2.条件语句中不能使用别名但是排序时可以使用
3.排序可以跟运算使用;比如先运算求日薪再排序

分组查询

SELECT COLUMN1 FROM EMP (WHERE CONDITION) GROUP BY COLUMN1 HAVING CONDITION;

sql第四天
应用：查询每个部门各自的工资总和
→

SELECT DEPTNO,SUM(SAL) FROM EMP GROUP BY DEPTNO;

查询每个部门的员工人数
→

SELECT DEPTNO,COUNT(*) FROM EMP GROUP BY DEPTNO;

思考下列语句是查询什么的？

SELECT DEPTNO,JOB,COUNT(*) FROM EMP GROUP BY DEPTNO,JOB;

←
统计各自部门的各自职业的人数
→

sql第四天

应用：按照部门和职业分组并且排序统计各自部门的各自职业的人数
→

SELECT DEPTNO,JOB,COUNT(*) FROM EMP GROUP BY DEPTNO,JOB ORDER BY DEPTNO;

应用：查询平均工资大于2000的每个部门的员工人数，和每个部门平均工资
→

SELECT DEPTNO,COUNT(*),AVG(SAL) FROM EMP GROUP BY DEPTNO  HAVING AVG(SAL)>2000；

–为什么这个地方不用WHERE而是用HAVING？因为where后不允许使用聚合函数，而且注意where的位置和having的位置不一样

注意：WHERE 和HAVING 的联系：
1.WHERE不必和 GROUP BY连用，不能写在 GROUP BY后，HAVING和 GROUP BY连用
2.WHERE筛选的是针对FROM后表的数据， HAVING是针对 GROUP BY分组之后的数据进行筛选
3.WHERE中的部分条件可以写在 HAVING中，只有在 GROUP BY子句中出现的字段，才能够由WHERE子句中转写到HAVING子句中

练习：
1.查询每个部门的平均工资，并统计其员工人数
→

SELECT DEPTNO,AVG(SAL),COUNT(*) FROM EMP GROUP BY DEPTNO;

2.查询每个部门的平均工资，最大工资，最小工资
→

SELECT DEPTNO,MAX(SAL),MIN(SAL),AVG(SAL) FROM EMP GROUP BY DEPTNO;

3.查询每个平均工资大于2000部门编号/最大最小及平均工资
→

SELECT DEPTNO,MAX(SAL),MIN(SAL),AVG(SAL) FROM EMP GROUP BY DEPTNO HAVING AVG(SAL)>2000;

习题：
1.查询emp表，将职位为 ANALYST, CLERK和 SALESMAN的显示为工人， MANAGER和 PRESIDEN显示为管理员
→

SELECT JOB,CASE WHEN JOB='ANALYST' THEN '工人'
WHEN JOB='CLERK' THEN '工人'
WHEN JOB='SALESMAN' THEN '工人'
WHEN JOB='MANAGER' THEN '管理员'
WHEN JOB='PRESIDENT' THEN '管理员'
END FROM EMP;
  • 2.列出所有员工的年工资，按年薪从低到高排序（年薪=sal*12+ comm）；
    →
SELECT EMP.*,SAL*12+NVL(COMM,0) AS "年薪" FROM EMP ORDER BY 年薪;

3.查询emp表，若SAL大于2500，则显示为高收入，1000-2500显示为中等收入，低于1000显示为贫农
→

SELECT SAL,CASE WHEN SAL>2500 THEN '高收入' WHEN SAL BETWEEN 1000 AND 2500 THEN '中等收入' WHEN SAL<1000 THEN '贫农' END FROM EMP;

4.查询emp表，查询出SAL<1000，SAL在1000-3000，和SAL大于3000的人数
→

SELECT SUM(CASE WHEN SAL<1000 THEN 1 ELSE 0 END),SUM(CASE WHEN SAL BETWEEN 1000 AND 3000 THEN 1 ELSE 0 END),SUM(CASE WHEN SAL>3000 THEN 1 ELSE 0 END) FROM EMP;

5.查询emp表，要求按照 hiredate和 ename升序排列；
→

SELECT * FROM EMP  ORDER BY HIREDATE,ENAME;

6.查询emp表，要求先按照JOB降序，再按照SAL升序排列；
→

SELECT * FROM EMP ORDER BY JOB DESC,SAL;
  • 7.按员工岗位升序排列且除经理外其他工种小写，经理排在第一位
    →
SELECT * FROM (SELECT CASE WHEN JOB='MANAGER' THEN  'MANAGER' ELSE LOWER(JOB) END "JOB" FROM EMP) ORDER BY JOB ASC;

↑↓

SELECT CASE WHEN JOB='MANAGER' THEN 'MANAGER' ELSE LOWER(JOB) END FROM EMP ORDER BY JOB ASC;
  • 8.查询工资超过1500的部门平均工资
    →
SELECT DEPTNO,AVG(SAL) FROM EMP WHERE SAL>1500 GROUP BY DEPTNO;

9.查询30号部门和10号部门的平均工资及人数
→

SELECT DEPTNO,AVG(SAL),COUNT(*) FROM EMP GROUP BY DEPTNO HAVING DEPTNO=10 OR DEPTNO=30 IN (10,30);

↑↓

SELECT DEPTNO,AVG(SAL),COUNT(*) FROM EMP WHERE DEPTNO IN(10,30) GROUP BY DEPTNO;

10.查询平均工资超过1500的部门的平均工资及最高最低工资差
→

SELECT DEPTNO,AVG(SAL),MAX(SAL)-MIN(SAL) FROM EMP GROUP BY DEPTNO HAVING AVG(SAL)>1500;

11.査出平均薪资超过800的部门和岗位类型及其平均薪资和最高薪资
→

SELECT DEPTNO,JOB,AVG(SAL),MAX(SAL) FROM EMP GROUP BY DEPTNO,JOB HAVING AVG(SAL)>800;
    • 12.查询工资在1000到3000之间，按有奖金和没有奖金分组，求薪资合计以及平均薪资，按平均薪资升序排列，加一列数据：字段名 奖金分类 内容：有奖金 无奖金
      →
SELECT SUM(SAL),
       AVG(SAL),
       CASE
         WHEN COMM IS NOT NULL THEN
          '有奖金'
         ELSE
          '无奖金'
       END AS "奖金分类"
  FROM EMP
 WHERE SAL BETWEEN 1000 AND 3000
 GROUP BY CASE
            WHEN COMM IS NOT NULL THEN
             '有奖金'
            ELSE
             '无奖金'
          END
 ORDER BY 2;

集合

对于数据集合进行操作，一般包含 UNION/ UNION ALL INTERSECT MINUS

并集

两个sql查询语句，将两个数据聚合并到一个表中，宇段名以第一个查询语句为主

应用：

SELECT * FROM EMP 
UNION
SELECT * FROM DEPT;

→
sql第四天
←会去重

SELECT * FROM EMP 
UNION ALL
SELECT * FROM DEPT;

→
sql第四天
←不会去重

思考：什么叫做以第一个查询语句为主？

SELECT EMPNO FROM EMP
UNION 
SELECT MGR FROM EMP;

→
sql第四天

SELECT EMPNO FROM EMP
UNION 
SELECT ENAME FROM EMP;

——报错：表达式必须具有与对应表达式相同的数据类型

总结：两个查询语句的显示列数应当一致，且字段的数据类型应当一致，且查询的表的列应当从数量上和数据类型上一致

注意:UNION默认按照第一列排序
而union all不会默认排序

交集

两个sql查询语句，求两个集合*有的部分，宇段名以第一个查询语句为主

语法：

SELECT EMPNO FROM EMP WHERE SAL=800 OR SAL=1600
INTERSECT
SELECT EMPNO FROM EMP WHERE SAL=800;

↑↓

SELECT EMPNO FROM EMP WHERE SAL IN(800,1600) AND SAL=1600;

注意:交集默认按照第一列排序

差集

两个sql查询语句，将第一个数据聚合中含有第二个数据集合的数据去除，字段名以第一个查询语句为主

SELECT EMPNO FROM EMP WHERE SAL=800 OR SAL=1600
MINUS
SELECT EMPNO FROM EMP WHERE SAL=800;

注意：'减数’与’被减数’是有区别的

习题：
1.查询部门编号是10,20的员工信息
→
SELECT * FROM EMP WHERE DEPTNO IN(10,20);
SELECT * FROM EMP WHERE DEPTNO=10 OR DEPTNO=20;
SELECT * FROM EMP WHERE DEPTNO=10 UNION SELECT * FROM EMP WHERE DEPTNO=20;

2.查询员工信息，职位是 CLERK或者 SALESMAN或者 ANALYST
→
SELECT * FROM EMP WHERE JOB=‘CLERK’ OR JOB=‘SALESMAN’ OR JOB=‘ANALYST’;
SELECT * FROM EMP WHERE JOB IN(‘CLERKL’,‘SALESMAN’,‘ANALYST’);
SELECT * FROM EMP WHERE JOB=‘CLERK’
UNION SELECT * FROM EMP WHERE JOB IN(‘SALESMAN’)
UNION SELECT * FROM EMP WHERE JOB=‘ANALYST’;

3.查询薪资是800，950的员工信息
→
SELECT * FROM EMP WHERE SAL= 800 OR SAL =950;
SELECT * FROM EMP WHERE SAL IN(800,950);
SELECT * FROM EMP WHERE SAL=800 UNION SELECT * FROM EMP WHERE SAL=950;

4.查询上级编号7902、7698的员工信息
→
SELECT * FROM EMP WHERE MGR=7902 OR MGR=7698;
SELECT * FROM EMP WHERE MGR IN(7902,7698);
SELECT * FROM EMP WHERE MGR=7902 UNION SELECT * FROM EMP WHERE MGR=7698;

5.员工工资不是800和1600的员工信息
→
SELECT * FROM EMP WHERE SAL <>800 AND SAL<>1600;
SELECT * FROM EMP WHERE SAL NOT IN (800,1600);
SELECT * FROM EMP
MINUS
SELECT * FROM EMP WHERE SAL IN(800,1600);

6.查询员工薪资为1000到3000的员工信息
→
SELECT * FROM EMP WHERE SAL BETWEEN 1000 AND 3000;
SELECT * FROM EMP WHERE 1000<=SAL AND SAL<=3000;
SELECT * FROM EMP
MINUS
SELECT * FROM EMP WHERE SAL<1000 OR SAL>3000;

7.查询员工编号在7500到7700之间的员工
→
SELECT * FROM EMP WHERE EMPNO BETWEEN 7500 AND 7700;
SELECT * FROM EMP WHERE EMPNO>=7500 AND EMPNO<=7700;
SELECT * FROM EMP
MINUS
SELECT * FROM EMP WHERE EMPNO<7500 OR EMPNO>7700;

8.查询姓名是以A开头的、薪资在1000到1500之间的员工
→
SELECT * FROM EMP WHERE ENAME LIKE ‘A%’ AND SAL BETWEEN 1000 AND 1500;
SELECT * FROM EMP WHERE ENAME LIKE ‘A%’ AND SAL<=1500 AND SAL>=1000;

9.查询薪资在1000-3000之间，并且部门编号是10或者20
→
SELECT * FROM EMP WHERE DEPTNO IN(10, 20) AND SAL BETWEEN 1000 AND 3000;

10.查询（部门编号是20并且工作岗位是 CLERK的员工），或者薪资大于3000的员工
→
SELECT * FROM EMP WHERE (DEPTNO=20 AND JOB=‘CLERK’) OR SAL>3000;

11.查询没有员工的部门编号
→

SELECT DISTINCT DEPTNO FROM DEPT WHERE DEPTNO NOT IN (SELECT DISTINCT DEPTNO FROM EMP);

↑↓

SELECT DISTINCT DEPTNO FROM DEPT
MINUS 
SELECT DISTINCT DEPTNO FROM EMP;

12.查询30号部门中有，10部门没有的岗位名称
→

SELECT DISTINCT  JOB FROM EMP WHERE DEPTNO=30 AND JOB NOT IN(SELECT JOB FROM EMP WHERE DEPTNO=10);

↑↓

SELECT DISTINCT JOB FROM EMP WHERE DEPTNO=30
MINUS 
SELECT DISTINCT JOB FROM EMP WHERE DEPTNO=10;
  1. 如何将M表sql第四天
    转化成N表 ？sql第四天

→

SELECT ENAME "姓名",
       SUM(CASE
             WHEN COURSE = '语文' THEN
              SCORE
             ELSE
              NULL(默认的，可以不写)
           END) _ "语文",
       SUM(CASE
             WHEN COURSE = '数学' THEN
              SCORE
             ELSE
              NULL
           END) "数学",
       SUM(CASE
             WHEN COURSE = '英语' THEN
              SCORE
             ELSE
              NULL
           END) "英语"
  FROM M
 GROUP BY ENAME
 ORDER BY 姓名DESC;

14.如何将N表
sql第四天

转化成M表？
sql第四天

→

SELECT 姓名 "ENAME",'语文' "COURSE",语文 "SCORE" FROM N
UNION ALL
SELECT 姓名 ,'数学' "COURSE",数学 "SCORE" FROM N
UNION ALL
SELECT 姓名 ,'英语' "COURSE",英语 "SCORE" FROM N
ORDER BY 姓名 DESC;

↑利用了查询常量
SELECT ‘A’ FROM EMP;
→
sql第四天
和union all的结合

上一篇：MySQL基础(六)——MySQL之开窗函数


下一篇：数据库从入门到精通