Oracle的基本查询知识

基本语法
SELECT [DISTINCT] {*, column [alias],...}
FROM table;
参数说明
SELECT 标识出所需的数据列、函数、常量和表达式。
Distinct 删除查询结果中相同的值。
星号“*” 表示显示所有的列。
COLUMN指定的列名，表中相应列的数据显示在结果集中。
Alias 为列指定一个别名。
FROM 来自哪个表。

 

查询所有列
SQL> SELECT * FROM emp;
查询指定列
SQL> SELECT deptno, dname FROM dept;
使用算术表达式
SQL> SELECT empno, sal*0.8 FROM emp;
使用字符常量
SQ> SELECT empno, 'Name is: ', ename FROM emp;
使用函数
SQL> SELECT empno, LOWER(ename) FROM emp;

无条件查询：

定义列的别名
重新命名列标题
用于计算是很有用处的
紧跟列名；在列名和别名之间加入可选关键字AS
如果别名包含空格或特殊关键字符或区分大小写则需要使用双引号
连接操作符
用于连接列与列、列和字符之间的连接
形式上是以两个竖杠||
用于创建字符表达式的结果列

改变列标题
SQL> SELECT ename employeename,sal AS salary
FROM emp;
如果别名包含空格或特殊关键字符或区分大小写则需要使用双引号。例如：
SQL> SELECT ename "Emname",sal AS Salary
FROM emp;
使用连接字符串
SQL> SELECT '员工号：'||empno, '员工名：'||ename
FROM emp;
消除重复行
SQL> SELECT ALL deptno FROM emp;
SQL> SELECT DISTINCT deptno FROM emp;

有条件查询

查询满足条件的元组通过WHERE子句实现。

WHERE条件中常用的运算符号

 

关系运算

SQL> SELECT empno,ename,sal FROM emp
WHERE deptno != 10;
SQL> SELECT empno,ename,sal FROM emp
WHERE sal>1500

确定范围
使用谓词BETWEEN AND与NOT BETWEEN AND

SQL> SELECT * FROM emp
WHERE deptno BETWEEN 10 AND 20;
SQL> SELECT * FROM emp
WHERE sal NOT BETWEEN 1000 AND 2000;

注意：BETWEEN...AND...指定的范围包括上限值和下限值！

确定集合

使用谓词IN或NOT IN用来查找属性值属于/不属于指定集合的元组。

SQL> SELECT empno,ename,sal FROM emp
WHERE deptno IN(10,30);

字符匹配
使用LIKE或NOT LIKE进行模糊查询。通配符：
%(百分号)代表任意长(长度为0)字符串。
_(下划线)代表任意单个字符。
ESCAPE：定义一个表示转义的字符。

SQL> SELECT * FROM empWHERE ename LIKE '%S%'；
SQL> SELECT * FROM emp WHERE ename LIKE '_A%';
SQL> SELECT * FROM emp WHERE ename LIKE '%x_%' ESCAPE 'x';

空值操作
涉及空值查询时使用IS NULL或 IS NOT NULL。
注意：这里的IS不能用=替代！

SQL> SELECT * FROM emp WHERE deptno IS NULL;
SQL> SELECT * FROM emp WHERE comm IS NOT NULL;

逻辑操作
用逻辑运算符NOT、AND和OR来联结多个查询条件。
优先级：NOT、AND、OR(用户可以用括号改变优先级)。
IN谓词实际上是多个OR运算的缩写。

SQL> SELECT * FROM emp
WHERE deptno=10 AND sal >1500;
SQL> SELECT * FROM emp
WHERE (deptno=10 OR deptno=20) AND sal>1500;
注意：
使用BETWEEN…AND，NOT BETWEEN…AND，IN，NOT IN运算符的查询条件都可以转换为NOT，AND，OR的逻辑运算。例如，下面两个语句是等价的：

SQL> SELECT * FROM emp
WHERE sal>=1000 AND sal<=2000;
SQL> SELECT * FROM emp
WHERE sal BETWEEN 1000 AND 2000;

查询排序：

使用ORDER BY子句对查询结果进行排序。
升序、降序排序
ASC: 升序 (缺省)；DESC: 降序

SQL> SELECT empno,ename,sal FROM emp ORDER BY sal;

SQL> SELECT empno,ename,sal FROM emp
ORDER BY sal DESC;

 

多列排序
首先按照第一个列或表达式进行排序；当第一个列或表达式的数据相同时，以第二个列或表达式进行排序，以此类推。

 

SQL> SELECT * FROM emp ORDER BY deptno,sal DESC;

 

按表达式排序
可以按特定的表达式进行排序。
SQL> SELECT empno,ename,sal FROM emp
ORDER BY sal*12;

使用别名排序
可以使用目标列或表达式的别名进行排序。

SQL> SELECT empno,sal*12 salary FROM emp
ORDER BY salary;

使用列位置编号排序
如果列名或表达式名称很长，那么使用位置排序可以缩短排序语句的长度。

SQL> SELECT empno,sal*12 salary FROM emp ORDER BY 2;

使用统计函数

例：统计30号部门的总人数、人均工资、最高工资和最低工资。

SQL> SELECT count(*),avg(sal),max(sal),min(sal)
FROM emp WHERE deptno=30;

 

例7：从员工信息表中查询所有的部门个数

SQL> SELECT count(DISTINCT deptno) FROM emp;

说明：COUNT(DISTINCT expr)返回对于表达式expr非空并且值不相同的行数！

：统计所有员工的平均工资和奖金总和。
SQL> SELECT avg(comm),sum(comm) FROM emp;

注意：结果的计算情况！

例：统计员工工资的方差和标准差。
SQL> SELECT variance(sal),stddev(sal) FROM emp;

注意
除了COUNT(*)函数外，其他的统计函数都不考虑返回值或表达式为NULL的情况。
分组函数只能出现在目标列表达式、ORDER BY子句、HAVING子句中，不能出现在WHERE子句和GROUP BY子句中。
默认对所有的返回行进行统计，包括重复的行；如果要统计不重复的行信息，则可以使用DISTINCT选项。
如果对查询结果进行了分组，则分组函数的作用范围为各个组，否则分组函数作用于整个查询结果。

分组查询：

基本语法
单列分组查询
多列分组查询
使用HAVING子句限制返回组
使用ROLLUP 和CUBE
合并分组查询

Oracle DB中，分组查询由GROUP BY子句、分组函数和
HAVING子句共同实现

 

SELECT column, group_function, …
FROM table
[WHERE condition]
[GROUP BY [ROOLUP|CUBE|GROUPING SETS]
group_by_expression]
[HAVING group_condition]
[ORDER BY column[ASC|DESC]];

 

单列分组查询
将查询出来的记录按照某一个指定的列进行分组。
SQL> SELECT deptno,count(*),avg(sal)
FROM emp GROUP BY deptno;

多列分组查询
在GROUP BY子句中指定了两个或多个分组列。
例如，查询各个部门中不同工种的员工人数和平均工资。

SQL> SELECT deptno,job,count(*),avg(sal)
FROM emp GROUP BY deptno,job;

使用HAVING子句限制返回组
可以使用HAVING子句, 只有满足条件的组才会返回。
例如，查询部门平均工资高于1500的部门号、部门人数和部门平均工资。

SQL> SELECT deptno,count(*),avg(sal)
FROM emp WHERE avg(sal)>1500
GROUP BY deptno

注意：HAVING子句作用于组，WHERE子句作用于记录。先根据WHERE条件
查询记录，最后根据HAVING条件决定哪些组返回。

例如，统计20号部门中各个工种的员工人数和平均工资，并返回平均工资高于1000的工种人数和平均工资。

SQL> SELECT job,count(*),avg(sal)
FROM emp WHERE deptno=20
GROUP BY job HAVING avg(sal)>1000;

使用ROLLUP 和CUBE
如果在GROUP BY子句中使用ROLLUP选项，则还可以生成横向统计和不分组统计；
如果在GROUP BY子句中使用CUBE选项，则还可以生成横向统计、纵向统计和不分组统计。
例10：查询各个部门各个工种的平均工资、每个部门的平均工资和所有员工的平均工资。

SQL> SELECT deptno,job,avg(sal)
FROM emp
GROUP BY ROLLUP(deptno,job);

例11：查询各个部门中各个工种的平均工资、每个部门的平均工资、每个工种的平均工资和所有员工的平均工资。

SQL> SELECT deptno,job,avg(sal)
FROM emp
GROUP BY CUBE(deptno,job);

合并分组查询
使用GROUPING SETS可以将几个单独的分组查询合并成一个分组查询。
例如，查询各个部门的平均工资和各个工种的平均工资。
SQL> SELECT deptno,job,avg(sal)
FROM emp
GROUP BY GROUPING SETS(deptno,job);
注意：
GROUP BY子句用于指定分组列或分组表达式。
分组函数用于对分组进行统计。如果未对查询分组，则分组函数将作用于整个查询结果；如果对查询结果分组，则分组函数将作用于每一个组，即每一个分组都有一个集合函数。
HAVING子句用于限制分组的返回结果。
WHERE子句对表中的记录进行过滤，而HAVING子句对分组后形成的组进行过滤。
在分组查询中，SELECT子句后面的所有目标列或目标表达式要么是分组列，要么是分组表达式，要么是分组函数。
目标列、目标表达式指出现在GROUP BY子句中的列或表达式