高级查询
分组函数多表连接查询:
SELECT wm_concat(s.sname) FROM student1 s GROUP BY s.cid CREATE TABLE emp AS SELECT * FROM scott.emp; //复制scott表下面的表 CREATE TABLE dept AS SELECT * FROM scott.dept; CREATE TABLE salgrade AS SELECT * FROM scott.salgrade; SELECT AVG(e.sal), SUM(e.sal)/COUNT(e.sal), SUM(e.sal)/COUNT(1) FROM emp e //三种算平均数的方式, SELECT AVG(e.comm), SUM(e.comm)/COUNT(e.comm), SUM(e.comm)/COUNT(1) FROM emp e SELECT e.job, e.deptno, AVG(e.sal) FROM emp e GROUP BY e.job, e.deptno ORDER BY 表的列名, 索引, 表达式, 别名 SELECT e.ename, e.sal 工资 FROM emp e ORDER BY 工资 --员工表中10号部门的平均工资 SELECT e.deptno, AVG(e.sal) FROM emp e WHERE e.deptno=10 GROUP BY e.deptno //此方法效率更高一些,数据较多时,先把10号部门选出 再进行平均数的计算 SELECT e.deptno, AVG(e.sal) FROM emp e GROUP BY e.deptno HAVING e.deptno=10 //此方法先找出全部的,再过滤的
1. 等值连接 SELECT e.empno,e.ename,d.dname FROM emp e, dept d WHERE e.deptno=d.deptno 在没有条件满足的时候,是不会有数据显示的
2.不等值连接
使用内连接只有满足连接条件的才会显示,如果想要使内容显示完整才用到了下面的外连接
1.1 内连接 SELECT * FROM STUDENT T, SCORE SO 啥也不写就是内连接, 直接写两个表名
3.外连接 ---左外连接, 右外连接
(+)修饰符的使用 d.student(+)= e.student ,左边连接的那个表里的内容不会全显示,
4. 外连接(其中一个表一定显示全部的内容)
SELECT d.deptno, d.dname, COUNT(e.empno) FROM emp e, dept d
WHERE e.deptno(+)=d.deptno GROUP BY d.deptno, d.dname
SELECT * FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno 一般情况下会用这个方法,右外连接 ,右边的那个表里的内容全部显示出来
WHERE d.deptno=10
5.自连接
--不适合操作大表
SELECT e.ename, b.ename FROM emp e, emp b, emp c WHERE e.empno=b.mgr (雇员的领导编号 = 领导的信息)
6.连接查询必要的条件:
必须有(表的个数-1)个条件
7.层次查询
connect by prior 连接条件 start with 开始条件 :
SELECT * FROM emp e CONNECT BY PRIOR e.empno=e.mgr
START WITH e.mgr=7698
8.level (伪列) ,select * ,level from 层级
9.rownum (伪列,做分页),作为条件的时必须包含第一条,第一行只能使用 < ,<=, = SELECT m.*, LEVEL, ROWNUM FROM menu m CONNECT BY PRIOR m.id=m.parentid
START WITH m.mname='主食'
select t.*, t.rowid from MENU t
-- page: 2, rows: 5
-- rownum作为条件时必须包含第一行记录, rownum只能使用< <= =
SELECT * FROM menu WHERE ROWNUM<=10 AND ROWNUM > 5
SELECT * FROM (SELECT m.*, ROWNUM rnum FROM menu m WHERE ROWNUM<=10) r
WHERE r.rnum>5
10. SELECT * FROM emp e WHERE e.sal>1000--12
UNION
SELECT * FROM emp e WHERE e.sal<3000--11
11.-- EXISTS
--查询部门下面有人的部门名称和编号
SELECT * FROM dept d WHERE EXISTS(SELECT 1 FROM emp e WHERE e.deptno=d.deptno)
12.子查询(sql语句的执行顺序)
(where ,from ,having,select 后面可以使用子查询的位置)
SELECT * FROM emp e WHERE e.sal<=ALL(SELECT sal FROM emp)
SELECT * FROM emp e WHERE e.sal=(SELECT MIN(sal) FROM emp)
--员工的编号, 姓名, 部门名称
SELECT e.empno "员工编号", e.ename "姓名", (SELECT d.dname FROM dept d WHERE d.deptno=e.deptno) "部门名称" FROM emp e
-- 部门平均工资比所有员工平均工资高的部门
SELECT AVG(e.sal) FROM emp e GROUP BY deptno HAVING AVG(e.sal)>(SELECT AVG(sal) FROM emp)
-- 查询SALES部门下的所有员工
SELECT * FROM emp e WHERE e.deptno=(SELECT d.deptno FROM dept d WHERE d.dname='SALES')
单行子查询
多行子查询
exists关键字的使用