八, 表查询 二

发布于 2018-03-22  1 次阅读



这里说下多表查询的基本概念:

在之前所使用的查询操作之中,都是从一张表之中查询出所需要的内容,那么如果现在一个查询语句需要显示多张表的数据,则就必须应用到多表查询的操作,而多表查询的语法如下:
复制代码

SELECT [DISTINCT] * | 字段 [别名] [,字段 [别名] ,…]

FROM 表名称 [别名], [表名称 [别名] ,…]

[WHERE 条件(S)]

[ORDER BY 排序字段 [ASC|DESC] [,排序字段 [ASC|DESC] ,…]];

使用连接字符串(||)

select ename || ' is a ' || job from emp;

使用逻辑操作符号
查询工资高于500或者是岗位为manager的雇员,同时他们的姓名首字母为大写的J

select * from emp where (sal > 500 or job = 'MANAGER') and ename like 'J%';

多表查询
规定:多表查询的条件是至少不能少于表的个数N-1才能排除笛卡尔集(如果有N张表联合查询,必须得有N-1个条件,才能避免笛卡尔集合)

显示部门为10的部门名称,雇员,工资,

SELECT d.dname, e.ename, e.sal FROM emp e, dept d WHERE e.deptno = d.deptno and e.deptno = 10;

显示雇员名,雇员工资及所在部门的名字,并按部门排序

SELECT e.ename, e.sal, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno ORDER by e.deptno;

注意:如果用group by,一定要把e.deptno 放到查询列里面

显示各个员工的姓名,工资及工资的级别

SELECT e.ename, e.sal, s.grade FROM emp e, salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal;

自连接
显示员工上司

SELECT worker.ename, boss.ename FROM emp worker, emp boss WHERE worker.mgr = boss.empno AND worker.ename = 'FORD';

嵌套查询,子查询
单行子查询
单行子查询是指只返回一行数据的子查询语句

SELECT * FROM emp WHERE deptno = (select deptno from emp WHERE ename = 'SMITH');

多行子查询
多行子查询指返回多行数据的子查询

SELECT * FROM emp WHERE job IN (SELECT DISTINCT job FROM emp WHERE deptno = 10);

在多行子查询中使用all操作符

SELECT ename, sal, deptno FROM emp WHERE sal > all(SELECT sal FROM emp WHERE deptno = 30);

SELECT ename, sal, deptno FROM emp WHERE sal > (SELECT max(sal) FROM emp WHERE deptno = 30);

在多行子查询中使用any操作符

SELECT ename, sal, deptno FROM emp WHERE sal > ANY (SELECT sal FROM emp WHERE deptno = 30);

SELECT ename, sal, deptno FROM emp WHERE sal > (SELECT min(sal) FROM emp WHERE deptno = 30);

多列子查询
单行子查询是指子查询只返回单列、单行数据,多行子查询是指返回单列多行数据,都是针对单列而言的,而多列子查询是指查询返回多个列数据的子查询语句

SELECT * FROM emp WHERE (deptno, job) = (SELECT deptno, job FROM emp WHERE ename = 'SMITH');

使用查询结果创建新表

create table mytable(classid,name,match,comm) as select classid,name,match,comm from student;

在from子句中使用子查询

SELECT e.ename, e.deptno, e.sal, ds.mysal FROM emp e, (SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno) ds WHERE e.deptno = ds.deptno AND e.sal > ds.mysal;

合并查询
有时在实际应用中,为了合并多个select语句的结果,可以使用集合操作符号
union,union all,intersect,minus多用于数据量比较大的数据局库,运行速度快
1.union
该操作符用于取得两个结果集的并集。当使用该操作符时,会自动去掉结果集中重复行

SELECT ename, sal, job FROM emp WHERE sal >2500
UNION
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';

2.union all
该操作符与union相似,但是它不会取消重复行,而且不会排序

SELECT ename, sal, job FROM emp WHERE sal >2500
UNION ALL
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';

3.Intersect
使用该操作符用于取得两个结果集的交集。

SELECT ename, sal, job FROM emp WHERE sal >2500
INTERSECT
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';

4.minus
使用该操作符用于取得两个结果集的差集,他只会显示存在第一个集合中,而不存在第二个集合中的数据

SELECT ename, sal, job FROM emp WHERE sal >2500
MINUS
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';

分页查询=MySQL的limit
Oracle中的表,除了我们建表时设计的各个字段,其实还有两个字段(此处只介绍2个),分别是ROWID(行标示符)和ROWNUM(行号)

SELECT classid,NAME,MATCH FROM (
SELECT ROWNUM rn,S.* FROM student S WHERE ROWNUM <=4 ORDER BY MATCH DESC) ua
WHERE ua.rn >=2;

说明:
ROWNUM是一个序列,会根据sql语句自动给你加上一列排好顺序的序号列


富强 民主 文明 和谐 自由 平等 公正 法治 爱国 敬业 诚信 友善