子查询***************
SELECT * FROM emp
WHERE sal>(SELECT sal FROM emp WHERE empno=7654);
显示比7654工资高的员工所有信息
SELECT * FROM emp
WHERE sal>(SELECT sal FROM emp WHERE empno=7654)
AND job=(SELECT job FROM emp WHERE empno=7788);
显示工资大于7654，并且和7788相同职位的员工的所有信息
SELECT ename,job,sal FROM emp WHERE sal=(SELECT MIN(sal) FROM emp);
显示工资最低的员工工作，姓名，工资 信息
思考：
查询部门名称，部门员工数，部门平均工资，部门最低收入员工姓名，
SELECT d.dname 人数,平均工资,e.ename
FROM dept d,(SElECT deptno,COUNT(ename) 人数,AVG(sal) 平均工资,MIN(sal) min FROM emp GROUP BY deptno) ed,emp e
WHERE d.deptno=ed.deptno AND e.sal=ed.min;
如果此时在同一部门有2个一样的最低工资的员工，则此语句无效。
在子查询中
1 IN 指定查询范围
不用IN 实现方法，显示各个部门中工资最低的员工信息
SELECT * FROM emp e,(SELECT deptno, MIN(sal) sal FROM emp GROUP BY deptno) ea WHERE e.sal=ea.sal;
等同于SELECT * FROM emp WHERE sal IN(SELECT MIN(sal) FROM emp GROUP BY deptno);
意思就是从后面的结果中查询出所有有关这个结果的信息。
2 ANY 操作有3种
“=ANY”操作，(和功能IN完全一样，例如上面的例子可以写成：SELECT * FROM emp WHERE sal=ANY(SELECT MIN(sal) FROM emp GROUP BY deptno);)
“>ANY”操作,SELECT * FROM emp WHERE sal>ANY(SELECT MIN(sal) FROM emp GROUP BY deptno);
拿后面sal结果做比较，显示大于结果中的最小值[800]，显示13行
“
拿后面的sal结果做比较，显示小结果中的最大值[1300]3 ALL 操作
“>ALL”操作，SELECT * FROM emp WHERE sal>ALL(SELECT MIN(sal) FROM emp GROUP BY deptno);
比结果中最大的值大的数值，并显示
“
比结果中最小的值小的数值，并显示(无结果，显示未指定，因为没有比最小值更小的了)
数据库的更新操作
回顾之前的，之前所学为查询操作，(SELECT)
更新操作，INSERT(增加),UPDATA(更新，修改),DELETE(删除)
在对我的数据操作之前，现在进行复制一份：CREATE TABLE myemp as SELECT * FROM emp ;(myemp 副本名字)
CREATE TABLE mydept AS SELECT * FROM dept;CREATE TABLE mysalgrade AS SELECT * FROM salgrade;
CREATE TABLE mybonus AS SELECT * fROM bonus;
CREATE TABLE 复制方法只在ORACLE 中适用，其他数据库不能操作
一 添加数据
INSERT INTO 表名称(字段1，字段2，...)VALUES(字段1值，字段2值，...)
INSERT INTO myemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES(7899,'张三','清洁工'7369,'14-2月-1995',9000,300,40);
简略写法
INSERT INTO myemp VALUES(8899,'李四','清洁工',7369,'14-2月-95',9000,300,40)；
添加王五没有领导和奖金
INSERT INTO myemp(empno,ename,job,hiredate,sal,deptno) VALUES(7889,'王五','清洁工','14-2月-95',9000,40);
等同于INSERT INTO myemp VALUES(7889,'王五','清洁工',NULL,'14-2月-95',9000,NULL,40);
插入不同格式的时间 TO_DATE()函数，将一个字符类型的数据变为DATE类型的数据。
INSERT INTO myemp VALUES(8888,'赵六','保洁工',NULL,TO_DATE('2009-01-06','yyyy-mm-dd'),9000,NULL,40);
更新，修改表单
被分为修改全部，和修改局部
UPDATE 表名称 SET 字段=数值
UPDATE 表名称 SET 字段=数值 。。。。WHERE 字段=数值
UPDATE myemp SET comm=1000;
UPDATE myemp SET sal=5000 WHERE empno=8888;
例子：7369,8899,7788的领导，奖金取消(就是为空)
UPDATE myemp SET mgr=NULL,comm=NULL WHERE empno IN(7369,8899,7788);
删除数据
被分为删除全部，删除局部
DELETE
DELETE FROM 表名称
DELETE FROM 表名称 WHERE ....
DELETE FROM myemp WHERE empno=7899;
DELETE FROM myemp WHERE hiredate LIKE '%-87';
DELETE FROM myemp;
新建一张表只包含10部门雇佣人员。
CREATE TABLE emp10 AS SELECT * FROM emp WHERE deptno=10;
事务处理
commit 无悔提交
rollback 删除后 后悔还原
保证数据操作完整性，数据操作要么同时成功，要么同时失败。
死锁现象：同时几个控制台一起操作，一个或者几个控制台提交commit,则此刻无法真正完成操作，要等待其他控制台提交或操作，此刻的等待现象被称为死锁。
1 求出部门的员工数
SELECT deptno,COUNT(empno) FROM emp GROUP BY deptno;(GROUP BY 分组统计)
2 列出部门人数大于1的所有部门编号
SELECT deptno,COUNT(empno) FROM emp GROUP BY deptno HAVING COUNT(empno)>1;
3 通过部门表查出部门的信息即可(部门的名字，人数，员工姓名)
SELECT d.deptno,d.dname,de.con FROM dept d,(SELECT deptno,COUNT(empno) con from emp GROUP BY deptno) de WHERE d.deptno=de.deptno;
4 列出工资比"SMITH"多的所有员工
分析：首先列出SMITH的工资， SELECT sal FROM emp WHERE ename='SMITH';
SELECT ename FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename='SMITH');
5 列出所有员工的姓名及其直接上司的姓名
SELECT e.ename,em.ename FROM emp e,emp em WHERE e.mgr=em.empno ;
分析关联，
6 列出雇佣时间早与其直接领导的员工编号，姓名，部门名称
分析：mgr=empno 然后比较时间
SELECT e.empno,e.ename,d.dname FROM dept d,emp e,emp em WHERE e.deptno=d.deptno AND e.mgr=em.empno AND e.hiredate
7 列出部门名称和和谐部门的员工信息，同时列出那些没有员工的部门
SELECT e.deptno,d.dname,e.ename,e.job,e.sal,e.mgr,e.hiredate,e.deptno FROM emp e,dept d WHERE e.deptno(+)=d.deptno ;
认真观察两行有什么不同，为什么？
SELECT d.deptno,d.dname,e.ename,e.job,e.sal,e.mgr,e.hiredate,e.deptno FROM emp e,dept d WHERE e.deptno(+)=d.deptno ;
上面的信息中2个结果不同，因为所显示的东西是不同表格中的信息，emp中不包括40部门信息，所以即使显示15行信息也不能提取40部门编号，下面则显示的是d.deptno则能够提取部门编号！*********
8 列出所有"CLERK"的姓名及部门名称，部门的人数。
SELECT e.ename,d.dname,dn.CON FROM (SELECT deptno,COUNT(empno) CON FROM emp GROUP BY deptno) dn, emp e,dept d WHERE e.deptno=d.deptno AND job='CLERK' AND d.deptno=dn.deptno;
总结： 多表查询，清除笛卡儿积
分组统计，分组函数只能在分组语句中使用。
子查询，子查询可以在任意位置查询，例如，FROM 或作为条件使用
事务处理，commit,rollback.
作业
1
SELECT job,MIN(sal),COUNT(empno) MIN FROM emp GROUP BY job HAVING MIN(sal)>1500 ;
2 查看SALES部门的员工的姓名，假设不知道部门编号。(deptno不知道)
SELECT e.ename FROM emp e,(SELECT deptno FROM dept WHERE dname LIKE 'SALES') d WHERE e.deptno=d.deptno;
3 列出工资高于公司平均工资的所有员工，所在部门，上级领导，所在的工资等级
SElECT e.ename,d.dname,em.ename,s.grade
FROM salgrade s,emp em,emp e, dept d
WHERE e.sal>(SELECT AVG(sal) FROM emp)
AND e.deptno=d.deptno
AND e.mgr=em.empno(+) AND e.sal BETWEEN s.LOSAL AND s.HISAL;
4 列出与scott相同部门的员工姓名， 部门 ，工作的名称
SELECT e.ename,e.job,d.dname FROM emp e,dept d WHERE e.deptno=(SELECT deptno FROM emp WHERE ename=UPPER('scott')) AND e.deptno=d.deptno;
7 列出与scott相同工作的的员工的姓名，部门，工作名称
SELECT e.*,d.dname FROM emp e,dept d WHERE job=(SELECT job FROM emp WHERE ename LIKE 'SCOTT') AND e.ename!='SCOTT' AND e.deptno=d.deptno;
其中e.ename!='SCOTT'意思是不包括scott,因为分析题意明确说明不应该显示SCOTT的信息。
SELECT ename,job FROM emp WHERE sal IN(SELECT sal FROM emp WHERE deptno=30) AND DEPTNO!=30;
观看结果，发现未指定行，**********说明语句正确，表中没有搜索的结果。
8 列出工资高于在部门30人员的其他员工的姓名，工资，部门信息
SELECT e.ename,e.job,e.sal,d.dname FROM emp e,dept d WHERE sal>ALL(SELECT sal FROM emp WHERE deptno=30) AND e.deptno=d.deptno;
9列出每个部门员工数量，平均工资和平均服务期限。
SELECT deptno,COUNT(empno), AVG(sal),AVG(TO_CHAR(hiredate)) FROM emp GROUP BY deptno;
阅读(1057) | 评论(0) | 转发(0) |
