八, 表查询 二
这里说下多表查询的基本概念:
在之前所使用的查询操作之中,都是从一张表之中查询出所需要的内容,那么如果现在一个查询语句需要显示多张表的数据,则就必须应用到多表查询的操作,而多表查询的语法如下:
复制代码
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语句自动给你加上一列排好顺序的序号列