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:是在分组统计的结果之上,再次统计的结果)
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子句:执行顺序为从左到右排序,消耗资源。