7. 排序
排序采用 ORDER BY 子句,包括升序(ASC)或降序(DESC)的排序方式。
SELECT * FROM JNUser ORDER BY Salary; --ASC 升序排序(默认排序方式)
SELECT * FROM JNUser ORDER BY Salary DESC; --DESC 降序排序
SELECT * FROM JNUser ORDER BY Sex ASC, Salary DESC; --首先按性别升序排序,再按薪资降序排序
SELECT * FROM JNUser WHERE Sex = 1 ORDER BY 4 ASC; --查出男性用户所有用户,并按照第4列(AGE)升序排序
SELECT Salary AS Salary2, t.* FROM JNUser t ORDER BY Salary2; --注意:排序的字段可以使用别名,也可以使用原有字段名(Salary)
n NULL 值排序处理
在 Oracle 中排序,NULL 默认为是最大值。所以如果降序排序,为 NULL 的行始终排在前面。如果想改变这个现象,可以使用以下关键字。例如:
--将 NULL 置于最后
SELECT * FROM "G_Brand" ORDER BY "UpdateTime" DESC NULLS LAST;
--将 NULL 置于最前(默认处理)
SELECT * FROM "G_Brand" ORDER BY "UpdateTime" DESC NULLS FIRST;
8. 分组与过滤
分组采用 GROUP BY 子句,注意:包含分组的 SELECT 输出列中,只能包含分组的列和聚合计算的列。GRUOUP BY 分组后还可以跟 HAVING 子句,HAVING 通常用于聚合计算并过滤。
1) 查出工资大于8000的用户,每个性别各占的数量,并按性别倒序排序
SELECT Sex, COUNT(1) AS Count FROM JNUser
WHERE Salary > 8000
GROUP BY Sex
ORDER BY Sex DESC;
2) 查出工资大于8000的用户,每个性别的平均工资大于13000,并按平均工资倒序排序
SELECT Sex, AVG(Salary) AS Avg FROM JNUser
WHERE Salary > 8000
GROUP BY Sex
HAVING AVG(Salary) > 13000
ORDER BY AVG(Salary) DESC;
或者
SELECT * FROM (
SELECT Sex, AVG(Salary) AS Avg FROM JNUser
WHERE Salary > 8000
GROUP BY Sex
HAVING AVG(Salary) > 13000
) t ORDER BY Avg DESC;
3) 查出每个城市里工资大于800的每个性别的平均工资,和最高工资,和人数,并按城市和性别排序
SELECT City, Sex, AVG(Salary) Avg, MAX(Salary) Max, COUNT(1) Count FROM JNUser
WHERE Salary > 800
GROUP BY City, Sex --多个字段同时分组
ORDER BY City, Sex;
9. 多表查询
Ø 注意
1) 在后续的查询示例中,将使用 scott 用户下的系统自带表,作为演示数据。
SELECT * FROM dept; --部门表 4条
SELECT * FROM emp; --员工表 14条
SELECT * FROM salgrade; --工资级别表 5条
SELECT * FROM bonus; --工资表 0条
2) 在讨论多表查询前,我们先搞清楚一件事。就是当我们在查询时,大于一张表的情况下,在 WHERE 子句中没有跟第二张表或后面的其他表做“关联”,将产生笛卡尔积。例如:
SELECT * FROM dept, emp, salgrade WHERE dept.deptno = emp.deptno;
以上这条语句中,WHERE 只对 dept 和 emp 表进行了关联,没有对 salgrade 的关联(或处理),所以结果会产生70(14*5)条数据;如果三张表都不关联,将产生280(4*14*5)条数据。
1) 查出员工部门编号为20的部门、姓名、和薪资
SELECT t1.dname, t2.ename, t2.sal FROM dept t1, emp t2
WHERE t1.deptno = 20 AND t1.deptno = t2.deptno;
或者(使用内连接)
SELECT t1.dname, t2.ename, t2.sal FROM dept t1
INNER JOIN emp t2 ON(t1.deptno = t2.deptno);
提示:提示:在使用多表连接时,选择显示的列尽量指明是属于哪个一个表(比如:t1.dname),一是可读性更好,二是当多张表中有相同列时,不指定表名会报错。
2) 查出员工姓名、薪资和薪资级别
SELECT t1.ename, t1.sal, t2.grade FROM emp t1, salgrade t2
WHERE t1.sal BETWEEN t2.losal AND t2.hisal;
注意:这里的关联方式,实际上并不是关联查询,只是使用到了 salgrade 表进行取值,这样同样不会产生笛卡尔积。
或者(使用子查询)
SELECT ename, sal, (SELECT grade FROM salgrade WHERE sal BETWEEN losal AND hisal) grade FROM emp t1;
3) 查出 SMITH 的上级领导
SELECT t2.* FROM emp t1, emp t2
WHERE t1.ename = 'SMITH' AND t1.mgr = t2.empno;
或者
SELECT t1.* FROM emp t1
WHERE t1.empno = (SELECT mgr FROM emp WHERE ename = 'SMITH');
提示:两种方案区别在于:当有两个人叫 SMITH 时,第一个 SQL 会出现两条记录;而第二个 SQL 会直接报错(因为子查询比允许返回两条相同的记录)。
4) 查出各个员工姓名和他上级领导的姓名
SELECT t1.ename, t2.ename FROM emp t1, emp t2
WHERE t1.mgr = t2.empno;
10. 子查询
子查询是指在一个 SELECT 查询语句中再嵌入另一个 SELECT 查询,或者被成为嵌套查询。子查询可以位于 SELECT 选择列中,也可位于 FROM 之后(派生表子查询),同时还可以为 WHERE 条件子查询,而子查询又分为单行单列和多行多列子查询,下面分别用示例演示:
1) 查出 SMITH 同部门的所有员工
SELECT * FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename = 'SMITH');
2) 查询与10号部门所有员工工作岗位相同的其他员工姓名、工资、部门号
SELECT ename, sal, deptno FROM emp
WHERE job IN(SELECT job FROM emp t1 WHERE t1.deptno = 10);
3) 查询平均工资比部门平均工资高的员工(派生表子查询)
SELECT t1.* FROM emp t1, (
SELECT deptno, AVG(sal) avgsal FROM emp GROUP BY deptno
) t2 WHERE t1.deptno = t2.deptno AND t1.sal > t2.avgsal;
4) 查询与 SMITH 部门和工作岗位相同的员工(多列子查询)
SELECT * FROM emp t1 WHERE 1=1
AND t1.deptno = (SELECT deptno FROM emp WHERE ename = 'SMITH')
AND t1.job = (SELECT job FROM emp WHERE ename = 'SMITH');
--或者(注意:这个语法在 MSSQL 中是不支持的)
SELECT * FROM emp t1 WHERE 1=1
AND (t1.deptno, t1.job) = (SELECT deptno, job FROM emp WHERE ename = 'SMITH');
--或者(使用派生表 + 多表查询)
SELECT * FROM emp t1, (
SELECT deptno, job FROM emp WHERE ename = 'SMITH'
) t2 WHERE t1.deptno = t2.deptno AND t1.job = t2.job;
提示:由于第一种方式根据部门和工作岗位执行了两个子查询;而后面两个只查询了一个子查询,所以建议使用后面两种方式。
5) 查询部门信息和员工数量
SELECT t1.*, (SELECT COUNT(*) FROM emp t2 WHERE t2.deptno = t1.deptno) Count FROM dept t1;
--或者
SELECT t1.*, t2.Count FROM dept t1, (
SELECT deptno, COUNT(1) Count FROM emp GROUP BY deptno
) t2 WHERE t1.deptno = t2.deptno(+);
注意:第二种方式,当 dept 中的部门编号中在 emp 中没有时,该部门不会统计出来。此时可以使用 t2.deptno(+) 处理,表示左连接。
11. 连接查询
连接查询中分为内连接和外连接。
Ø 内连接
内连接很类似我们之前讨论的多表连接,例如:
SELECT t1.ename, t2.dname FROM emp t1, dept t2
WHERE t1.deptno = t2.deptno;
而内连接只是在语法形式上存在差别,其实现功能都是一样的,下面使用内连接:
SELECT t1.ename, t2.dname FROM emp t1
INNER JOIN dept t2 ON(t1.deptno = t2.deptno); --由于内连接是默认的连接方式,所以 INNER 关键字可以省略
可以看到,内连接只是语法结构变了,将之前的 WHERE 条件部分,放在了 ON() 中了,同时使用了 INNER JOIN 关键字。
Ø 外连接
外连接又分为左外连接、右外连接和全连接,三责存在什么区别呢?看完示例就明白了。
提示:由于在 scott 用户下,dept、emp、salgrade 这三张表不满测试外连接条件,但可以借助第四张表 bonus(工资表),但是该表中默认没有数据,所以需要先准备测试数据:
INSERT ALL
INTO bonus VALUES('SMITH', 'CLERK', 800.00, 200)
INTO bonus VALUES('WARD', 'SALESMAN', 1250.00, 500)
INTO bonus VALUES('JONES', 'MANAGER', 2975.00, 300)
INTO bonus VALUES('张三丰', '武当', 10000.00, 6000)
SELECT * FROM dual;
COMMIT;
说明:
1. bonus 表一共插入了4条数据,其中前三条可以跟 emp 表关联起来,最后一条(张三丰)是别的公司的员工,所以不再公司的员工表里;
2. emp 表与 bonus 表关联字段为 ename(员工姓名);
3. 有没有发现 bonus 的建表结构似乎不是很合理,为什么需要用 ename 和 job 去关联吗?不知道 Oracle 是买的什么关子!当然这个不是重点。
1) 左外连接
左外连接又称“左连接”,当根据指定的条件连接两张表时,始终显示左表的记录,右表没有连接上的记录,相关字段显示为 NULL。示例:
SELECT t1.empno, t1.ename, t1.job, t2.sal, t2.comm FROM emp t1
LEFT OUTER JOIN bonus t2 ON(t1.ename = t2.ename); --OUTER 关键字可以省略
从结果可以看到,左表显示了全部,右表没关联上的字段显示为 NULL 了。
另外,除了以上语法,左外连接还可以这样写,使用(+)代替:
SELECT t1.empno, t1.ename, t1.job, t2.sal, t2.comm FROM emp t1, bonus t2
WHERE t1.ename = t2.ename(+);
(+)表示该表未连接上时显示为 NULL,结果与前面的语法是一样的。
2) 右外连接
右外连接又称“右连接”,与左连接是相反的,始终显示右表的记录,左表没有连接上的记录,相关字段显示为 NULL。示例:
SELECT t1.empno, t1.ename, t1.job, t2.sal, t2.comm FROM emp t1
RIGHT OUTER JOIN bonus t2 ON(t1.ename = t2.ename); --OUTER 关键字可以省略
从结果可以看到,右表显示了全部,左表没关联上的字段显示为 NULL 了。
同样,右连接也可以使用(+)代替,例如:
SELECT t1.empno, t1.ename, t1.job, t2.sal, t2.comm FROM emp t1, bonus t2
WHERE t1.ename(+) = t2.ename;
与左连接相比,将(+)放在了左表的字段上。
3) 全连接
全连接是左外连接和右外连接结合版本,表示两张表都显示全部,未连接上的记录显示 NULL。示例:
SELECT t1.empno, t1.ename, t1.job, t2.sal, t2.comm FROM emp t1
FULL OUTER JOIN bonus t2 ON(t1.ename = t2.ename)
WHERE t1.ename IN('SMITH', 'WARD', 'JONES', 'BLAKE') OR t2.ename = '张三丰'; --OUTER 关键字可以省略
n 总结
Ø 内连接:只显示两张表连接上的记录;
Ø 左外连接:左表显示所有,右表没连接上的记录显示为 NULL,可以使用右表字段(+)的方式代替;
Ø 右外连接:右表显示所有,左表没连接上的记录显示为 NULL,可以使用左表字段(+)的方式代替;
Ø 全连接:两张表都显示所有,未连接上的记录各显示为 NULL。