10.基本DQL

DQL

• 基本语法：SELECT 字段1,字段2… FROM 表名 WHERE 查询条件 其他子句

• 其他子句：就是后面所讲的排序、聚合函数等

• 使用别名

  • 在SQL语句中可以通过使用列的别名改变标题的显示样式，或者表示计算结果的含义

  • 使用语法是列的别名跟在列名后，可以加或不加一个“AS”关键字

  • 如果希望别名中区分大小写字符，或者别名中包含字符或空格，别名则必须用双引号引起来

• SELECT语句中除了使用表达式，还可以使用函数

查询条件：> , < , >= , <= , != , <> , =

• “!=”和“<>”等价的，都用于判断不等于条件，建议使用<>，标准数据库都支持

查询条件：AND,OR 关键字

• 如果希望返回的结果必须满足多个条件，使用AND逻辑操作符连接这些条件

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

• 如果同时使用AND和OR，需要注意AND优先级高于OR，可以通过括号来提高OR的优先级

查询条件：LIKE 关键字

• 比较操作符 LIKE 用来做模糊查询

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

• LIKE 需要借助两个通配符：

  • % ：表示0到多个字符

  • — ：表示单个字符

  • 这两个通配符可以配合使用

查询条件：IN、NOT IN

• 比较操作符 IN(list) 用来去除符合列表范围中的数据

• list 表示值列表，当列或表达式匹配与列表中的任何一个值时，条件为TRUE,该条记录被显示出来

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

• NOT IN(list) 取出不符合此列表中的数据记录

• IN 和 NOT IN 常用来判断子查询的结果，子查询后面会介绍

查询条件：BETWEEN…AND…

• 该操作符用来查询符合某个值域范围条件的数据

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

• 范围小的必须在 BETWEEN 后面，大的在 AND 后面

查询条件：IS NULL 和 IS NOT NULL：前面已经介绍

查询条件：ANY、ALL

• ALL和ANY不能单独使用，需要配合比较操作符 >,<,>=,<= 一起使用

• ANY(list)：大于最小 (大于list其中之一)

• <ANY(list)：小于最大 (小于list其中之一)

• ALL(list)：大于最大 (大于list中所有值)

• <ALL(list)：小于最小 (小于list中所有值)

DISTINC 关键字

• 作用：过滤掉重复的数据

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

• 当重复数据没有实际意义，经常会需要去掉重复值，使用DISTINCT实现

• 使用该关键字去除多个字段时，是去除他们的组合的重复数据

• 语法：SELECT DISTINC 字段1,字段2… …

  • 只能去除后面紧跟的字段中重复的数据，也就是字段1，字段2和其他字段数据不会受到影响

查询-排序：ORDER BY 子句

• 对数据按一定规则进行排序操作，使用该子句

• ASC：升序，默认就是升序，DESC：降序

• 必须出现在SELECT中的最后一个子句

• 当给多个字段排序时，先给第一个字段排序，有重复的时候再按照第二字段排序，依次类推，每个字段可以单独指定是降序还是升序

• 如果排序的字段中含有NULL值，NULL被认作最大值

查询-统计：聚合函数

• 查询时有时需要做一些数据统计，需要统计的数据不能在表里直观列出，而是根据现有的数据计算得到结果

• 该函数的作用是：将表的全部数据划分为几组数据，每组数据统计出一个结果

• WHERE中不能使用聚合函数作为过滤条件，原因是过滤时机不对，WHERE是在数据检索表中数据时，对数据逐条过滤以决定是否查询出该数据时使用的，所以WHERE用来确定结果集的数据

• 因为多行数据参与运算返回一行结果，也称作分组函数，多行函数，集合函数

• 所有的聚合函数都忽略NULL值统计

聚合函数：MAX、MIN

• 求给定字段的最大值于最小值

聚合函数：AVG、SUM

• 求给定字段的平均值和总和

聚合函数：COUNT

• 统计给定字段中数据不为NULL值的记录数

• 特殊情况：SELECT COUNT(*) FROM 表名，可以查看表中所有记录的条数

查询-分组：GROUP BY 子句

• 作用：可以将结果集按照其后指定的字段值相同的记录看作一组，然后配合聚合函数进行更细分的统计工作

• GROUP BY 可以根据多个字段分组，分组原则为这几个字段值都相同的记录看作一组，划分的小组有多少，最终的结果集行数就有多少

• 当SELECT语句中含有聚合函数，那么不在聚合函数内的字段，必须存在于GROUP BY子句中

  • 如：SELECT AVG(sal),deptno FROM emp GROUP BY deptno，deptno必须在分组子句中

HAVING 子句

• 该子句用来对分组后的结果进一步限制，对分组后的结果进行过滤

• 要使用它，那么它必须跟在GROUP BY后面，不能单独存在

查询语句执行顺序

1. FROM 子句：执行顺序为从后往前，从右到左

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

2. WHERE 子句：执行顺序为自下而上，从右到左

   • 将过滤掉最大数量记录的条件写在WHERE子句的最右边

3. GROUP BY 子句：执行顺序从左往右分组

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

4. HAVING 子句：消耗资源

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

5. SELECT 子句：少用*号，尽量取字段名称

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

6. ORDER BY 子句：执行顺序从左到右排序，消耗资源

演示：基本DQL

• 使用别名

  SELECT ename AS “员工” FROM emp;

  SELECT ename,sal*12 “年薪” FROM emp;

• 、< 、>= 、<= 、!= 、<> 、=

  • 查询职员表中薪水低于2000元的职员信息
    SELECT * FROM emp WHERE sal<2000;

  • 查询职员表中不属于部门10的员工信息
    SELECT * FROM emp WHERE deptno<>10;

  • 查询职员表中在1982年1月1号以后入职的职员信息，比较日期类型的数据
    SELECT * FROM emp WHERE hiredate > TO_DATE(‘1982-01-01’,‘YY-MM-DD’);

• AND、OR

  • 查询薪水大于1000并且职位是’CLERK’的职员信息
    SELECT sal,job,sal FROM emp WHERE sal>1000 AND job=‘CLERK’;

  • 查询薪水大于1000或者职位是’CLERK’的职员信息
    SELECT sal,job,sal FROM emp WHERE sal>1000 OR job=‘CLERK’;

  • 查看工资高于1000且职位是SALESMAN或CLERK
    SELECT sal,job,sal FROM emp WHERE sal>1000 AND (job=‘SALESMAN’ OR job=‘CLERK’);

• LIKE

  • 查询名字第二个字符为A最后一个字符是N的员工姓名
    SELECT ename FROM emp WHERE ename LIKE ‘_A%N’;

  SELECT * FROM myemp WHERE name LIKE ‘_强’;

• IN、NOT IN

  • 查询工作是’MANAGER’或’CLERK’的员工
    SELECT ename,job FROM emp WHERE job IN(‘MANAGER’,‘CLERK’);

  • 查询部门不是10或20的员工
    SELECT ename,deptno FROM emp WHERE deptno NOT IN(10,20);

• BETWEEN…AND…

  • 查询薪水在1500和3000的员工
    SELECT ename,sal FROM emp WHERE sal BETWEEN 1500 AND 3000;

• ANY、ALL

  SELECT ename,sal FROM emp WHERE sal >ANY(3500,4000,4500);

  SELECT ename,sal FROM emp WHERE sal <ANY(3500,4000,4500);

  SELECT ename,sal FROM emp WHERE sal <ALL(3500,4000,4500);

  SELECT ename,sal FROM emp WHERE sal >ALL(3500,4000,4500);

• 使用表达式和函数

  SELECT ename,sal,job FROM emp WHERE ename=UPPER(‘scott’);

  SELECT ename,sal12,job FROM emp WHERE sal12 >50000;

• DISTINCT

  • 查看公司有哪些职位
    SELECT DISTINCT job FROM emp;

  SELECT DISTINCT job,deptno FROM emp;

• ORDER BY

  SELECT ename,sal FROM emp ORDER BY sal;

  SELECT ename,sal FROM emp ORDER BY sal DESC;

  SELECT ename,deptno,sal FROM emp ORDER BY deptno,sal;

  SELECT ename,deptno,sal FROM emp ORDER BY deptno DESC,sal ASC;

  SELECT ename,comm FROM emp ORDER BY comm DESC;

• 聚合函数：MAX、MIN

  • 查看公司的最高工资和最低工资
    SELECT MAX(sal),MIN(sal) FROM emp;

• 聚合函数：AVG,SUM

  • 查看公司工资的平均值和总和
    SELECT AVG(sal),SUM(sal) FROM emp;

  • 查看平均绩效
    SELECT AVG(NVL(comm,0)),SUM(sal) FROM emp;

• 聚合函数：COUNT

  • 查看公司中有绩效的人有多少
    SELECT COUNT(comm) FROM emp;

  • 查看表有多少条记录
    SELECT COUNT(*) FROM emp;

• GROUP BY

  • 查看每个部门的平均工资
    SELECT AVG(sal),deptno FROM emp GROUP BY deptno ORDER BY deptno;

  • 查看同部门同职位的平均工资
    SELECT AVG(sal),deptno,job FROM emp GROUP BY deptno,job ORDER BY deptno;

• HAVING

  • 查看部门的平均工资，前提是该部门的平均工资高于2000，该查询是错误查询
    SELECT AVG(sal),deptno FROM emp WHERE AVG(sal)>2000 GROUP BY deptno;

  • 正确查询
    SELECT AVG(sal),deptno FROM emp GROUP BY deptno HAVING AVG(sal)>2000;

• 查看平均工资高于2000的部门的最高工资和最低工资分别是多少

  SELECT MAX(sal),MIN(sal),deptno FROM emp GROUP BY deptno HAVING AVG(sal)>2000;