Oracle:SQL基础

1.1.1. FROM子句

SQL查询语句的语法如下：

1. SELECT <*, column [alias], …> FROM table;

其中：SELECT用于指定要查询的列，FROM指定要从哪个表中查询。如果要查询所有列，可以在SELECT后面使用*号，如果只查询特定的列，可以直接在SELECT后面指定列名，列名之间用逗号隔开。例句如下，查询dept表中的所有记录：

1. SELECT * FROM dept;

 

1.1.2. 使用别名

在SQL语句中可以通过使用列的别名改变标题的显示样式，或者表示计算结果的含义，使用语法是列的别名跟在列名后，中间可以加或不加一个“AS”关键字。例如：

1. SELECT empno AS id , ename "Name", sal * 12 "Annual Salary"  FROM emp;

 

别名可以直接写，不必用双引号引起来。但是如果希望别名中区分大小写字符，或者别名中包含字符或空格，则必须用双引号引起来。

1.1.3. WHERE子句

在SELECT语句中，可以在WHERE子句中使用比较操作符限制查询结果，是可选的。

当查询条件中和数字比较，可以使用单引号引起，也可以不用，当和字符及日期类型的数据比较，则必须用单引号引起。例如查询MGR=7698的员工信息：

1. SELECT * FROM emp WHERE MGR=7698;

 

查询职员表中职位是’SALESMAN’的职员：

1. SELECT ename, sal, job FROM emp WHERE job = 'SALESMAN';

 

图-1 用where子句定义查询条件

1.1.4. SELECT子句

如果只查询表的部分列，需要在SELECT后指定列名，例如：

1. SELECT empno, ename, sal, job FROM emp;

 

图-2查询指定的列

1.2. 查询条件

1.2.1. 使用>, <, >=, <=, !=, <>, =

在WHERE子句中的查询条件，可以使用比较运算符来做查询。比如：查询职员表中薪水低于2000元的职员信息：

1. SELECT ename, sal FROM emp WHERE sal < 2000;

 

查询职员表中不属于MGR=7698的员工信息 （!=等价于<>）：

1.     SELECT ename, sal, job FROM emp WHERE MGR !=7698;

 

查询职员表中在1981年1月1号以后入职的职员信息，比较日期类型数据：

1.    SELECT ename, sal, hiredate FROM emp 

2.     WHERE hiredate > to_date('1981-1-1','YYYY-MM-DD');

 

1.2.2. 使用AND，OR关键字

在SQL操作中，如果希望返回的结果必须满足多个条件，应该使用AND逻辑操作符连接这些条件，如果希望返回的结果满足多个条件之一即可，应该使用OR逻辑操作符连接这些条件。例如：查询薪水大于1000并且职位是’CLERK’的职员信息：AND的优先级比OR的高

1. SELECT EMPNO AS id,ENAME AS 姓名，

2. SAL AS 工资，COMM AS 奖金,MGR AS 管理

3. FROM EMP

4. WHERE MGR=7839 AND SAL > 2000;

 

查询薪水大于2000或者MGR是”7839”的职员信息：

1. SELECT EMPNO AS id,ENAME AS 姓名，

2. SAL AS 工资，COMM AS 奖金,MGR AS 管理

3. FROM EMP

4. WHERE MGR=7839 OR SAL > 2000;

 

1.2.3. 使用LIKE条件（模糊查询）

当用户在执行查询时，不能完全确定某些信息的查询条件，或者只知道信息的一部分，可以借助LIKE来实现模糊查询。LIKE需要借助两个通配符：

· %：表示0到多个字符 

· _：标识单个字符 

这两个通配符可以配合使用，构造灵活的匹配条件。例如查询职员姓名中第二个字符是‘L’的员工信息：

SELECT ENAME AS 姓名,JOB AS 职位,SAL AS 工资

FROM EMP

WHERE ENAME LIKE '_L%';

 

图-3 模糊查询的结果

1.2.4. 使用IN和NOT IN

在WHERE子句中可以用比较操作符IN(list)用来取出符合列表范围中的数据。其中的参数list表示值列表，当列或表达式匹配于列表中的任何一个值时，条件为TRUE，该条记录则被显示出来。

IN页可以理解为一个范围比较操作符，只不过这个范围是一个指定的值列表，NOT IN(list) 取出不符合此列表中的数据记录。例如查询工资SAL

1. SSELECT ENAME AS 姓名,JOB AS 职位,SAL AS 工资

1. FROM EMP

1. WHERE SAL IN (1600，3000，5000);

 

查询奖金不是0和1000：

SELECT ENAME AS 姓名，SAL AS 工资,COMM AS 奖金

FROM EMP

WHERE COMM NOT IN (0,1000);

 

1.2.5. BETWEEN…AND…

BETWEEN…AND…操作符用来查询符合某个值域范围条件的数据，最常见的是使用在数字类型的数据范围上，但对字符类型和日期类型数据也同样适用。例如查询薪水在1500-3000之间的职员信息：（AND左边是小的，AND右边是大的）

1.     SELECT ename, sal FROM emp  

2.     WHERE sal BETWEEN 1500 AND 3000;

 

1.2.6. 使用IS NULL和IS NOT NULL

空值NULL是一个特殊的值，比较的时候不能使用”=”号，必须使用IS NULL，否则不能得到正确的结果。例如查询哪些职员的奖金数据为NULL：

1. SELECT ename, sal, comm FROM emp 

2. WHERE comm IS NULL;

1.2.7. 使用ANY和ALL条件

在比较运算符中，可以出现ALL和ANY，表示“全部”和“任一”，但是ALL和ANY不能单独使用，需要配合单行比较操作符>、>=、<、<=一起使用。其中：

· > ANY : 大于最小 

· < ANY：小于最大 

· > ALL：大于最大 

· < ALL：小于最小 

例如，查询薪水比职位是“SALESMAN”的人高的员工信息，比任意一个SALESMAN高都行：

1. SELECT  EMPNO, ENAME, JOB, SAL

2. FROM  EMP

3. WHERE  SAL> ANY (

4. SELECT  SAL FROM  EMP  WHERE  JOB = 'SALESMAN');

 

列如，查询薪水比员工是“JONES”的人高的员工信息，比所有的JONES高都行

1. SELECT  EMPNO, ENAME, JOB, SAL

2. FROM  EMP

3. WHERE SAL >ALL(

4. SELECT SAL FROM EMP WHERE ENAME = 'JONES');    

 

1.2.8. 查询条件中使用表达式和函数

当查询需要对选出出的字段进行进一步计算，可以在数字列上使用算术表达式(+、-、*、/)。表达式符合四则运算的默认优先级，如果要改变优先级可以使用括号

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

查询条件中使用字符串函数UPPER，将条件中的字符串变大写后再参与比较：

5. SELECT EMPNO AS ID,ENAME AS 姓名,JOB AS 职务, SAL AS 工资

6. FROM EMP

7. WHERE ENAME = UPPER('king');

 

查询条件中使用算数表达式，查询年薪大于3w元的员工记录：

8. SELECT EMPNO AS ID,ENAME AS 姓名,JOB AS 职务, SAL AS 工资

9. FROM EMP

10. WHERE SAL*12 > 30000;

 

1.2.9. 使用DISTINCT过滤重复

数据表中有可能存储相同数据的行，当执行查询操作时，默认情况会显示所有行，不管查询结果是否有重复的数据。当重复数据没有实际意义，经常会需要去掉重复值，使用DISTINCT实现。例如查询员工的部门编码，包含所有重复值：

11. SELECT * FROM EMP;

 

查询员工的部门编码，去掉重复值：

12. SELECT  DISTINCT MGR

13. FROM EMP;

 

DISTINCT后面的列可以组合查询，下例查询每个部门的职位，去掉重复值。注意是deptno和job联合起来不重复：

1. SELECT DISTINCT deptno, job FROM emp;

图-4用distinct去掉重复的列

1.3. 排序

1.3.1. 使用ORDER BY字句

对查询出的数据按一定规则进行排序操作，使用ORDER BY子句。语法如下：

1. SELECT <*, column [alias], …> 

2. FROM table 

3. [WHERE condition(s)] 

4. [ORDER BY column [ASC | DESC]] ;

注意，ORDER BY必须出现在SELECT中的最后一个子句。下例对职员表按薪水排序：

1. SELECT ENAME AS 姓名,SAL AS 工资

2. FROM EMP

3. ORDER BY SAL(ASC);(表示升序，可以默认（不用写ASC）是升序)

 

图-5用ORDER BY将查询结果排序

1.3.2. ASC和DESC

排序时默认按升序排列，即由小及大，用ASC用来指定升序排序，DESC用来指定降序排序。

因为NULL值视作最大，则升序排列时，排在最后，降序排列时，排在最前。如果不写ASC或DESC，默认是ASC，升序排列。例如，按员工的经理升序排序：

1.     SELECT empno, ename, mgr FROM emp 

2.     WHERE deptno = 10 ORDER BY mgr;

降序排列，必须指明，按员工的薪水倒序排序：

4. SELECT ENAME AS 姓名,SAL AS 工资

5. FROM EMP

6. ORDER BY SAL DESC;(降序DESC)

 

1.3.3. 多个列排序

当以多列作为排序标准时，首先按照第一列进行排序，如果第一列数据相同，再以第二列排序，以此类推。多列排序时，不管正序还是倒序，每个列需要单独设置排序方式。

下例对职员表中的职员排序，先按照奖金降序排列，再按照薪水升序排列：

1. SELECT ENAME AS 姓名,SAL AS 工资,COMM AS 奖金

2. FROM EMP

3. ORDER BY COMM DESC,SAL;

 

1.4. 聚合函数

1.4.1. 什么是聚合函数

查询时需要做一些数据统计，比如：查询职员表中各部门职员的平均薪水，各部门的员工人数。当需要统计的数据并不能在职员表里直观列出，而是需要根据现有的数据计算得到结果，这种功能可以使用聚合函数来实现，即：将表的全部数据划分为几组数据，每组数据统计出一个结果。

因为是多行数据参与运算返回一行结果，也称作分组函数、多行函数、集合函数。用到的关键字：

· GOURP BY 按什么分组 

· HAVING 进一步限制分组结果 

1.4.2. MAX和MIN

用来取得列或表达式的最大、最小值，可以用来统计任何数据类型，包括数字、字符和日期。例如获取机构下的最高薪水和最低薪水，参数是数字：

1. SELECT MAX(sal) max_sal, MIN(sal) min_sal 

2. FROM emp;

 

计算最早和最晚的入职时间，参数是日期：

1. SELECT MAX(hiredate) max_hire, MIN(hiredate) min_hire 

2. FROM emp;

 

1.4.3. AVG和SUM

AVG(average 平均)和SUM(总数)函数用来统计列或表达式的平均值和和值，这两个函数只能操作数字类型，并忽略NULL值。例如获得机构下全部职员的平均薪水和薪水总和：

1. SELECT AVG(SAL)平均工资,SUM(SAL)总薪水

2. FROM EMP;

 

1.4.4. COUNT

COUNT函数用来计算表中的记录条数，同样忽略NULL值。例如获取职员表中一共有多少名职员记录：

1.     SELECT COUNT(*) 公司人数  FROM emp;

 

获得职员表中有多少人是有职位的（忽略没有职位的员工记录）

1.     SELECT COUNT(job) total_job  FROM emp;

1.4.5. 聚合函数对空值的处理

聚合函数忽略NULL值。即当emp表中的某列有NULL值，比如某新入职员工没有薪水，比较两条语句的结果：(凡是在分组函数中使用了聚合函数，就不能单独出现函数)(聚合函数有：AVG 、SUM、MAX、MIN、COUNT)

1. SELECT AVG(sal)  avg_sal FROM emp;

 

2. SELECT AVG(NVL(sal,0)) avg_sal FROM emp;

 

3. DELECT AVG(SAL),COMM FROM EMP(该行是错误的 ，不能使用COMM)

1.5. 分组

1.5.1. GROUP BY子句

上面的例子都是以整个表作为一组。如果希望得到每个部门的平均薪水，而不是整个机构的平均薪水，需要把整个数据表按部门划分成一个个小组，每个小组中包含一行或多行数据，在每个小组中再使用分组函数进行计算，每组返回一个结果。语法如下：(将某些相同特性的分成一组，group by出现在where或from的后面,)

1. SELECT <*, column [alias], …> 

2. FROM table [WHERE condition(s)] 

3. [GROUP BY group_by_expression]

4. [HAVING group_condition]

5. [ORDER BY column [ASC | DESC]] ;

其中划分的小组有多少，最终的结果集行数就有多少。

查询各部门的工资情况（最大，最小，平均，总和）

SELECT语句中出现聚合函数，同时又想出现在此语句中是，必须在group by出现

6. SELECT DEPTNO，MAX(SAL),MIN(SAL),SUM(SAL),AVG(SAL)

7. FROM EMP

8. GROUP BY DEPTNO

9. ORDER BY DEPTNO;

 

当使用GROUP BY 分组时，SELECT字段出现的单独字段，必须在GROUP BY 中出现

WHERE 中不能使用聚合函数

1.5.2. 分组查询

图-6 分组查询

1.5.3. HAVING字句

HAVING子句用来对分组后的结果进一步限制，比如按部门分组后，得到每个部门的最高薪水，可以继续限制输出结果。必须跟在GROUP BY后面，不能单独存在。

例如：查询每个部门的最高薪水，只有最高薪水大于4000的记录才被输出显示：(HAVING:是在分组统计的结果之上，再次统计的结果)

1. SELECT DEPTNO,MAX(SAL)

2. FROM EMP

3. GROUP BY DEPTNO

4. HAVING MAX(SAL)>4000;

 

例如：查询每个部门的最高薪水，要求该部门的平均工资在2500以上

5. SELECT DEPTNO AS 部门编号,MAX(SAL) AS 最高工资,AVG(SAL) AS 平均工资

6. FROM EMP

7. GROUP BY DEPTNO

8. HAVING AVG(SAL)>2500;

 

列如：查询部门编号，该部门的最高工资，要求平均工资高于2000，

      职位是PRESIDENT的不参

    WHERE是在第一次查询是过滤 的

9. SELECT DEPTNO AS 部门编号,MAX(SAL) AS 最高工资,

    AVG(SAL) AS 平均工资,JOB AS 职位

10. FROM EMP

11. WHERE JOB<>'PRESIDENT'

12. GROUP BY DEPTNO,JOB

13. HAVING AVG(SAL)>2500 ;

14. SELECT *FROM EMP;

 

1.6. 查询语句的执行顺序

当一条查询语句中包含所有的子句，执行顺序依下列子句次序：

1. FROM 子句：执行顺序为从后往前、从右到左。数据量较少的表尽量放在后面。 

2. WHERE子句：执行顺序为自下而上、从右到左。将能过滤掉最大数量记录的条件写在WHERE 子句的最右。 

3. GROUP BY：执行顺序从左往右分组，最好在GROUP BY前使用WHERE将不需要的记录在GROUP BY之前过滤掉。 

4. HAVING 子句：消耗资源。尽量避免使用，HAVING 会在检索出所有记录之后才对结果集进行过滤，需要排序等操作。 

5. SELECT子句：少用*号，尽量取字段名称。ORACLE 在解析的过程中, 通过查询数据字典将*号依次转换成所有的列名, 消耗时间。 

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