DB（四）：SELECT查询、查询条件、排序、分组、集合操作_select条件查询

---

ORACLE SQL

包括SELECT语句、AS别名、WHILE子句；AND和OR关键字、LIKE条件、IN和NOT IN、BETWEEN…AND…操作符、IS NULL和IS NOT NULL、ANY和ALL条件、DISTINCT去重；使用ORDER BY子句排序；使用GROUP BY子句分组、HAVING子句、查询语句的执行顺序；集合操作UNION、UNION ALL、MINUS。

---

一、DQL查询语句

1、SELECT语句

（1）、用于查询表中数据

（2）、SELECT子句后面跟的是要查询的字段，可以包括表中的具体字段，函数或者表达式

（3）、FROM子句用来指定数据来源的表

（4）、WHERE子句用来添加过滤条件，这样做的结果是只将满足条件的记录查询出来

（5）、SELECT语句的处理过程：

• 分析语句：搜索是否有相同语句；检查语法、表名、权限；在分析过程中给对象加锁；生成执行计划

• 绑定变量——给变量赋值

• 执行语句

• 获取数据——将数据返回给用户进程

SELECT id,name,salary,job FROM myemployee_liu WHERE deptno = '10';
1

2、AS别名

（1）、SELECT子句中可以使用函数或表达式，那么结果集中对应的该字段名就是这个函数或表达式，可读性差，为此可以为这样的字段添加别名，那么结果集会以这个别名作为该字段的名字

（2）、使用语法是列的别名跟在列名后，中间可以加或不加一个“AS”关键字

（3）、别名本身不区分大小写，而且不能含有空格，若希望别名区分大小写或含有空格，那么可以在别名上使用双引号括起来

SELECT name,salary*12 AS "sal" FROM myemployee_liu;
1

3、WHERE子句

（1）、在SELECT语句中，可以在WHERE子句中使用比较操作符限制查询结果

（2）、如果和数字比较，可以使用单引号引起，也可以不用

（3）、如何和字符及日期类型的数据比较，则必须用单引号引起

（4）、如果只查询表的部分列，需要在SELECT后制定列名

-- 查询ID为7369的员工信息
SELECT * FROM myemployee_liu WHERE ID = '7369';
-- 查询工作为'CLERK'的员工
SELECT id,name,salary,job FROM myemployee_liu WHERE JOB = 'CLERK';
1
2
3
4

注：WHERE子句后不能跟列别名；过滤列中字符串必须用单引号括起来；字符串是大小写敏感的

二、查询条件

1、使用>、<、>=、<=、!=、<>、=

-- 查询薪资低于2000元的员工信息
SELECT name,salary FROM myemployee_liu WHERE salary<2000;
-- 查询不属于CLERK工作的员工信息（!=等价于<>）
SELECT * FROM myemployee_liu WHERE JOB<>'CLERK';
-- 查询在2002年1月1日以后入职的员工信息，比较日期类型数据
SELECT name,salary,birth FROM myemployee_liu WHERE birth > to_date('1982-1-1','YYYY-MM-DD');
1
2
3
4
5
6

2、使用AND、OR关键字

（1）、在SQL操作中，如果希望返回的结果必须满足多个条件，应该使用AND逻辑操作符连接这些条件

（2）、在SQL操作中，如果希望返回的结果满足多个条件之一即可，应该使用OR逻辑操作符连接这些条件

-- 查询薪水大于1000并且工作为“SALESMAN或者CLERK”的员工信息
SELECT name,salary,job FROM myemployee_liu WHERE salary>1000 AND (job='SALESMAN' OR job='CLERK');
-- 查询薪水大于1000或者职位为“SALESMAN”的员工信息
SELECT name,salary,job FROM myemployee_liu WHERE salary>1000 OR job='SALESMAN';
1
2
3
4

**注：AND的优先级高于OR，可以通过括号来提高OR的优先级**

3、使用LIKE条件（模糊查询）

（1）、比较操作符LIKE用来做模糊查询

（2）、当用户在执行查询时，不能完全确定某些信息的查询条件，或者只知道信息的一部分，可以借助LIKE来实现

（3）、LIKE需要借助两个通配符：

• %：表示0到多个字符

• _：表示单个字符

（4）、这两个通配符可以配合使用，构造灵活的匹配条件

-- 查看名字第二个字母是A最后一个字母是N的员工信息
SELECT name,salary,job FROM myemployee_liu WHERE name LIKE '_A%D';
1
2

（5）、若查找%和_本身，需要使用ESCAPE进行转义

SELECT name,salary FROM myemployee_liu WHERE name LIKE 'S\_%' ESCAPE '\';
1

4、使用IN和NOT IN

（1）、比较操作符IN(list)用来取出符合列表范围中的数据，含义为等于其中任意一个值

（2）、List表示值列表，当列或表达式匹配与列表中的任何一个值时，条件为TRUE，该条记录则被显示出来

（3）、IN也可以理解为一个范围比较操作符，只不过这个范围是一个指定的值列表

（4）、NOT IN(list)取出不符合此列表中的数据记录

（5）、IN和NOT IN常用来判断子查询的结果

-- 查询职位是MANAGER或者CLERK的员工信息
SELECT name,salary,job FROM myemployee_liu WHERE job IN ('MANAGER','CLERK');
-- 查询不是部门10或者20的员工信息
SELECT name,salary,job FROM myemployee_liu WHERE deptno NOT IN ('10','20');
1
2
3
4

5、BETWEEN…AND…

（1）、BETWEEN…AND…操作符用来查询符合某个值域范围条件的数据，含义为大于等于并且小于等于

（2）、最常见的是使用在数字类型的数据范围上，但对字符类型和日期类型数据也同样适用

-- 查询薪水在1500~3000之间的员工信息
SELECT  name,salary,job FROM myemployee_liu WHERE salary BETWEEN 1500 AND 3000;
1
2

6、使用IS NULL和IS NOT NULL

空值NULL是一个特殊的值，比较的时候不能使用“=”或“<>”号，必须使用IS NULL，否则不能得到正确的结果

-- 查询那些员工的部门为NULL
SELECT  name,salary,job FROM myemployee_liu WHERE deptno IS NULL;
1
2

7、使用ANY和ALL条件

（1）、ALL和ANY不能单独使用，需要配合单行比较操作符>、>=、<、<=一起使用

> ANY：大于最小（大于列表中最小的）
< ANY：小于最大（小于列表中最大的）
> ALL：大于最大（大于列表中最大的）
< ALL：小于最小（小于列表中最小的）
1
2
3
4

（2）、ANY和ALL常用与子查询

-- 查询薪资大于(1500,2000,3000)其中之一的员工信息
SELECT  name,salary,job FROM myemployee_liu WHERE salary > ANY(1500,2000,3000);
1
2

8、查询条件中使用表达式和函数

（1）、当查询需要对选出的字段进行进一步计算，可以在数字列上使用算术表达式（+、-、*、/）

（2）、表达式符合四则运算的默认优先级，如果要改变优先级可以使用括号

（3）、算术运算主要是针对数字类型的数据，对日期类型的数据可以做加减操作，表示咋一个日期值上加或减一个天数

-- 查询条件中使用函数
SELECT  name,salary,job FROM myemployee_liu WHERE name=UPPER('rose');
SELECT  name,salary,job FROM myemployee_liu WHERE salary*12>10000;
1
2
3

9、使用DISTINCT过滤重复

（1）、数据表中有可能存储相同数据的行，当执行查询操作时，默认情况会显示所有行，不管查询结果是否有重复数据

（2）、当重复数据没有实际意义，经常会需要去掉重复值，使用DISTINCT实现；若后面为多列，所有列联合起来唯一

-- 查询公司有哪些工作
SELECT DISTINCT job FROM myemployee_liu;
-- 查询每个部门的工作，去掉重复值（多字段去重，是对这些字段值的组合进行去重）
SELECT DISTINCT job,deptno FROM myemployee_liu;
1
2
3
4

三、排序

（1）、对数据按一定规则进行排序操作，使用ORDER BY子句

SELECT <*, column [alias], ...>
FROM table
[WHERE condition(S)]
[ORDER BY coiumn [ASC|DESC]];
--ASC：升序，不写默认就是升序
--DESC：降序
1
2
3
4
5
6

（2）、必须出现在SELECT中的最后一个子句

-- 按部门进行降序排序
SELECT job,deptno FROM myemployee_liu ORDER BY deptno DESC;
1
2

（3）、多字段排序：ORDER BY首先按照第一个字段的排序方式对结果集进行排序，当第一个字段有重复值时才会按照第二个字段排序方式进行排序，以此类推，每个字段都可以单独指定排序方式

-- 按薪资和部门进行排序
SELECT name,job,deptno FROM myemployee_liu ORDER BY salary, deptno DESC;
1
2

（4）、排序的字段中含有NULL值，NULL被认作为最大值

四、分组

1、GROUP BY子句

（1）、基本语法：

SELECT <*, column [alias], ...>
FROM table [WHERE condition(S)]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column [ASC|DESC]];
1
2
3
4
5

（2）、GROUP BY可以将结果集按照其后指定的字段值相同的记录看做一组，然后配合聚合函数进行更细分的统计工作

-- 查看每个部门的平均工资
SELECT  AVG(salary),deptno FROM myemployee_liu GROUP BY deptno;
1
2

（3）、GROUP BY也可以根据多个字段分组，分组原则为这几个字段值都相同的记录看做一组

-- 查看同部门同职位的平均工资
SELECT  AVG(salary),job,deptno FROM myemployee_liu GROUP BY job,deptno;
1
2

（4）、常见错误

a、若没有GROUP BY子句，SELECT后面有一个是组函数，其它都必须是组函数

SELECT deptno,COUNT(name) FROM myemployee_liu
1

-- 正确写法：
SELECT deptno,COUNT(name) FROM myemployee_liu GROUP BY deptno;
SELECT COUNT(deptno),COUNT(name) FROM myemployee_liu;
1
2
3

b、若有GROUP BY子句，SELECT后面可跟GROUP BY后面跟的表达式及组函数，其它会报错

SELECT deptno,COUNT(deptno) FROM myemployee_liu GROUP BY name;
1

-- 正确写法：
SELECT deptno,COUNT(deptno) FROM myemployee_liu GROUP BY deptno;
1
2

注：组函数包括AVG()、SUM()、COUNT()、MAX()、MIN()

2、HAVING子句

-- 基本语法：
SELECT colname, group_function
FROM tabname
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];
1
2
3
4
5
6
7

（1）、当SELECT子句中含有聚合函数时，那么凡不在聚合函数中的其他单独字段都必须出现在GROUP BY子句中，反过来则不是必须的

（2）、WHWEW中不能使用聚合函数作为过滤条件，原因是过滤时机不对

（3）、WHERE是在数据库检索表中数据时，对数据逐条过滤以决定是否查询出该数据时使用的，所以WHERE用来确定结果集的数据

（4）、使用聚合函数的结果作为过滤条件，那么一定是数据从表中查询完毕（WHERE在查询过程中发挥作用）得到结果集，并且分组完毕才进行聚合函数统计结果，得到后才可以对分组进行过滤，由此可见，这个过滤时机是在WHERE之后进行的

（5）、聚合函数的过滤条件要在HAVING子句中使用，HAVING必须跟在GROUP BY子句之后，HAVING是用来过滤分组的

-- 查看部门的平均工资，前提是该部分的平均工资高于2000
SELECT AVG(salary),deptno FROM myemployee_liu  GROUP BY deptno HAVING AVG(salary)>2000;
-- 查看平均工资高于2000的部门的最高工资和最低工资分别是多少
SELECT MAX(salary),MIN(salary),deptno FROM myemployee_liu  GROUP BY deptno HAVING AVG(salary)>2000;
1
2
3
4

3、查询语句执行顺序

查询语句的执行顺序依下例子句次序：

（1）、from子句：执行顺序为从后往前、从右到左

• 数据量较少的表尽量放在后面

（2）、where子句：执行顺序为自下而上、从右到左

• 将能过滤掉最大记录的条件写在Where子句的最右

（3）、group by：执行顺序从左往右分组

• 最好在GROUP BY前使用WHERE将不需要的记录在GROUP BY之前过滤掉

（4）、having子句：消耗资源

• 尽量避免使用，HAVING会在检索出所有记录之后才对结果集进行过滤，需要排序等操作

（5）、select子句：少用*号，尽量取字段名称

• ORACLE在解析的过程中，通过查询数据字典将*号一次转换成所有的列名，消耗时间

（6）、order by子句：执行顺序为从左到右排序，消耗资源

4、WHERE子句和HAVING子句的区别

（1）、WHERE子句过滤的是行（记录）

（2）、HAVING子句过滤的是分组（组标识、每组数据的聚合结果）

（3）、WHERE子句后面可以跟任意列名、单行函数，不能跟组函数

（4）、HAVING子句只能包含GROUP BY后面的表达式和组函数

（5）、WHERE子句执行在前，HAVING子句执行在后

（6）、WHERE子句和HAVING子句都不允许用列别名

五、集合操作

1、UNION、UNION ALL

（1）、为了合并多个SELECT语句的结果，可以使用集合操作符，实现集合的并、交、差

（2）、集合操作符包括NUION、NUION ALL、INTERSECT和MINUS

（3）、多条作集合操作的SELECT语句的列的个数和数据类型必须匹配

（4）、ORDER BY子句只能放在最后的一个查询语句中

（5）、集合操作的语法如下：

SELECT statement1
[UNION | UNION ALL | INTERSECT | MINUS]
SELECT statement2;
1
2
3

-- 合并职位是‘MANAGER’的员工和薪水大于2500的员工集合
SELECT name,job,salary FROM myemployee_liu WHERE job='MANAGER' UNION SELECT name,job,salary FROM myemployee_liu WHERE salary>2500;
SELECT name,job,salary FROM myemployee_liu WHERE job='MANAGER' UNION ALL SELECT name,job,salary FROM myemployee_liu WHERE salary>2500;
1
2
3

注：使用集合，前后查询SQL的字段必须一致；全并集（UNION ALL）会出现重复数据，即同时满足前后俩个SQL

2、MINUS

（1）、获取两个结果集的差集

（2）、只有在第一个结果集中存在，在第二个结果集中不存在的数据，才能够被显示出来。也就是结果集一减去结果集二的结果，不包含重复值

-- 列出职位是MANAGER但薪水低于2500的员工信息
SELECT name,job,salary FROM myemployee_liu WHERE job='MANAGER' MINUS SELECT name,job,salary FROM myemployee_liu WHERE salary>2500
1
2

---

---