Oracle 查询(SELECT)语句(二)

2024年 3月 11日 158.5k 0

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。

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论