精通Oracle10编程SQL(4)使用SQL语句

数据库  /  管理员 发布于 4年前   270

--工资级别表create table SALGRADE(  GRADE    NUMBER(10),  LOSAL    NUMBER(10,2),  HISAL    NUMBER(10,2))insert into SALGRADE values(1,0,100);insert into SALGRADE values(2,100,200);insert into SALGRADE values(3,200,500);insert into SALGRADE values(4,500,1000);insert into SALGRADE values(5,1000,2000);insert into SALGRADE values(6,2000,3000);insert into SALGRADE values(6,3000,5000);insert into SALGRADE values(6,5000,8000);insert into SALGRADE values(6,8000,10000);--给emp添加MGR（管理者号）列alter table emp add MGR NUMBER(10);select * from emp for update;select ename,sal*12 from emp;select ename as 姓名,sal*12 as 年收入 from emp;select ename as "姓名",sal*12 as "年收入" from emp;select ename,sal,comm,sal+comm from emp;--使用NVL函数处理NULL值select ename,sal,comm,sal+nvl(comm,0) as "月收入" from emp;--使用NVL2函数处理NULL值select ename,nvl2(comm,sal+comm,sal) from emp;select * from emp;--连接字符串select ename||' is a '||job as "Employee Detail" from emp;--使用where子句select ename,sal from emp where sal>100;select job,sal from emp where ename='SCOTT';select job,sal from emp where lower(ename) = 'scott';select ename,sal,hiredate from emp where hiredate>'01-1月-82';select ename,sal,hiredate from emp where hiredate>to_date('1982-01-01','YYYY-MM-DD');select ename,sal,hiredate,job from emp where sal between 100 and 200;--显示首字符为S的所有雇员名及其工资select ename,sal from emp where ename like 'S%';--显示第三个字符为大写A的所有雇员名及其工资select ename,sal from emp where ename like '__A%';--显示雇员名包含"_"的雇员信息（其中ESCAPE后的字符a为转义符select ename,sal from emp where ename like '%a_%' ESCAPE 'a';select * from emp for update;select ename,sal from emp where sal in(800,1250);select ename,sal from emp where job is null;--显示补助非空的雇员信息select ename,sal,comm from emp where comm is not null;--当执行升序排序时，如果被排序列包含NULL值，那么NULL会显示在最后面。select ename,sal,comm from emp where deptno=3 order by comm;--当执行降序排序时，如果被排序列存在NULL值，那么NULL会显示在最前面。select ename,sal,comm from emp where deptno=3 order by comm desc;--使用非选择列表列进行排序select ename from emp order by sal desc;--使用别名排序select ename,sal*12 as "全年工资" from emp where deptno=3 order by "全年工资" desc;--使用列位置编号排序--另外当使用UNION,UNION ALL,INTERSECT,MINUS等集合操作符合并查询结查时，如果选择列表中的列名不同，并且希望进行排序，那么必须使用列位置。select ename,sal*12 "全年工资" from emp where deptno=3 order by 2 desc;select * from emp;--使用特定格式插入日期值insert into emp(empno,ename,sal,hiredate,comm,job,deptno)values(1356,'MARY',30.2,to_date('1983-10-20','YYYY-MM-DD'),10.2,'CLERK',5);--使用DEFAULT提供数据select * from dept;insert into dept values(60,'MARKET',DEFAULT);SELECT * FROM dept WHERE deptno=60;--使用子查询插入数据select * from employee;select * from emp;create table employee as select * from emp where 1=2;insert into employee(empno,ename,sal,deptno)select empno,ename,sal,deptno from empwhere deptno=3;--使用子查询执行直接装载--当要装载大批量数据时，采用第二种方法装载数据的速度远远优于第一种方法。insert /*++APPEND*/ into employee(empno,ename,sal,deptno)select empno,ename,sal,deptno from empwhere deptno=3;--使用多表插入数据create table dept10 as select * from emp where 1=2;create table dept20 as select * from emp where 1=2;create table dept30 as select * from emp where 1=2;create table clerk as select * from emp where 1=2;create table other as select * from emp where 1=2;select * from dept10;select * from dept20;select * from dept30;select * from clerk;select * from other;delete dept10;delete dept20;delete dept30;delete clerk;delete other;select * from emp for update;--使用ALL操作符执行多表插入insert allwhen deptno=1 then into dept10when deptno=2 then into dept20when deptno=3 then into dept30when job='CLERK' then into clerkelse into otherselect * from emp;--使用FIRST操作符执行多表插入--当使用FIRST操作符执行多表插入时，如果数据已经满足了先前条件，并且已经被插入到某表，那么该行数据在后续插入中将不会被再次使用。insert firstwhen deptno=1 then into dept10when deptno=2 then into dept20when deptno=3 then into dept30when job='CLERK' then into clerkelse into otherselect * from emp;select * from emp;--更新日期列数据update emp set hiredate=to_date('1984/01/01','YYYY/MM/DD') where empno=7788;--使用DEFAULT选项更新数据select job from emp where ename='SCOTT';update emp set job=DEFAULT where ename='SCOTT';--修改表字段，设置默认值alter table emp modify (job NVARCHAR2(255) default 'JOB'); --使用子查询更新数据--更新关联数据update emp set(job,sal,comm) = (select job,sal,comm from emp where ename='FAT')where ename='SCOTT';--使用子查询更新数据--复制表数据select * from employee;update employee set deptno =    (select deptno from emp where empno=7788)where job = (select job from emp where empno=7788);--删除数据delete from emp where ename='SMITH';select * from emp;--使用TRUNCATE TABLE截断表--使用TRUNCATE TABLE语句不仅会删除表的所有数据，而且还会释放表段所占用的空间。--注意，DELETE语句的操作可以回退，但TRUNCATE TABLE语句的操作不能回退。truncate table employee;--使用子查询删除数据delete from emp where deptno = (select deptno from dept where dname='SALES');select * from dept;select * from emp;--删除主表数据的注意事项delete from dept where deptno=10;--设置保存点savepint a;exec dbms_transaction.savepoint('a');--取消部分事务rollback to a;exec dbms_transaction.rollback_savepoint('a');--取消全部事务rollback;exec dbms_transaction.rollback;--只读事务--设置只读事务--当使用只读事务时，可以确保用户取得特定时间点的数据。--只读事务是指只允许执行查询操作，而不允许执行任何DML操作的事务。set TRANSACTION READ ONLY;exec dbms_transaction.read_only;--顺序事务--设置顺序事务--顺序事务使得用户可以取得特定时间点的数据，并且允许DML操作，可以使用顺序事务。set TRANSACTION ISOLATION LEVEL SERIALIZABLE;--取得总计行数select count(*) from emp;--在count函数中还可以引用表达式，因为分组函数会忽略NULL行，所以使用count(表达式)会显示NOT NULL的总计行数。select count(comm) from emp;--取得方差和标准偏差select variance(sal),stddev(sal) from emp;--取消重得值select count(distinct deptno) AS distinct_dept from emp;select count(deptno) AS dept from emp;select * from emp;select distinct deptno AS distinct_dept from emp;select deptno AS distinct_dept from emp;--使用GROUP BY进行单列分组select deptno,avg(sal),max(sal) from emp group by deptno;--使用GROUP BY进行多列分组select deptno,job,avg(sal),max(sal) from empgroup by deptno,job;--使用HAVING子句限制分组显示结果select deptno,avg(sal),max(sal) from empgroup by deptnohaving avg(sal)<100;--使用ROLLUP操作符--下面显示每部门每岗位的平均工资、每部门的平均工资、所有雇员平均工资select deptno,job,avg(sal) from empgroup by rollup(deptno,job);--使用CUBE操作符--下面以显示每部门每岗位平均工资、部门平均工资、岗位平均工资、所有雇员平均工资为例select deptno,job,avg(sal) from empgroup by cube(deptno,job);--使用GROUPING函数--GROUPING函数用于确定统计结果是否用到了特定列。--如果函数返回0，则表示统计结果使用了该列，如果函数返回1，则表示统计结果未使用该列。select deptno,job,avg(sal),grouping(deptno),grouping(job)from emp group by cube(deptno,job);--GROUPING SETS--使用GROUPING SETS操作符可以合并多个分组的结果。--显示部门平均工资select deptno,avg(sal) from emp group by deptno;--显示岗位平均工资select job,avg(sal) from emp group by job;--显示部门平均工资和岗位平均工资select deptno,job,avg(sal) from emp group by grouping sets(deptno,job);--使用相等连接执行主从查询--下面显示所有雇员的名称、工资及其所在的部门名称为例，说明使用相等连接的方法。select e.ename,e.sal,d.dname from emp e,dept dwhere e.deptno = d.deptno;--使用AND指定其他条件select d.dname,e.ename,e.sal from emp e,dept dwhere e.deptno=d.deptno and d.deptno=1;--不等连接select * from salgrade;--显示所有雇员的名称、工资及其工资级别select a.ename,a.sal,b.grade from emp a,salgrade bwhere a.sal between b.losal and b.hisal;--连接查询-自连接select * from emp for update;--显示EMPP雇员的上级领导select manager.ename from emp manager,emp workerwhere worker.mgr = manager.empnoand worker.ename = 'EMPP';select * from dept;--内连接select a.dname,b.ename from dept a,emp bwhere a.deptno=b.deptno and a.deptno=1;select a.dname,b.ename from dept ainner join emp bon a.deptno = b.deptnoand a.deptno=1;--如果主表的主键列和从表的外部键列名称相同，那么还可以使用NATURAL JOIN关键字自动执行内连接操作select dname,ename from dept natural join emp;--左外连接select a.dname,b.ename from dept a left join emp bon a.deptno = b.deptno and a.deptno = 1;select a.dname,b.ename from dept a right join emp bon a.deptno = b.deptno and a.deptno=1;--完全外连接select a.dname,b.ename from dept a full join emp bon a.deptno = b.deptno and a.deptno=1;--使用（+）操作符--左外连接select a.dname,b.ename from dept a,emp bwhere a.deptno = b.deptno(+) and b.deptno(+) = 1;--右外连接select a.dname,b.ename from dept a,emp bwhere a.deptno(+) = b.deptno and a.deptno(+) = 1;--单行子查询select ename,sal,deptno from emp where deptno=(select deptno from emp where ename = 'SCOTT');select ename,sal,deptno from emp where deptno in(select deptno from emp);--在多行子查询中使用IN操作符select ename,job,sal,deptno from emp where job in(select distinct job from emp where deptno = 1);--在多行子查询中使用ALL操作符select ename,sal,deptno from emp where sal>all(select sal from emp where deptno = 5);--在多行子查询中使用ANY操作符select ename,sal,deptno from emp where sal>any(select sal from emp where deptno = 5);select * from emp;--多列子查询select ename,job,sal,deptno from emp where (deptno,job)=(select deptno,job from emp where ename='EMPP');--成对比较select ename,sal,comm,deptno from empwhere (sal,nvl(comm,-1)) in (select sal,nvl(comm,-1)from emp where deptno=3);--非成对比较示例select ename,sal,comm,deptno from empwhere sal in(select sal from emp where deptno=3)and nvl(comm,-1) in (select nvl(comm,-1) from emp where deptno=3);--相关子查询--相关子查询是指需要引用主查询表列的子查询语句，相关子查询是通过EXISTS谓词来实现的select ename,job,sal,deptno from emp where exists(select 1 from dept where dept.deptno = emp.deptnoand dept.loc = 'NEW YORK');select * from dept for update;--在FROM子句中使用子查询--当在FROM子句中使用子查询时，该子查询会被作为视图对待，因此也被称为内嵌视图--显示高于部门平均工资的雇员为例select ename,job,sal from emp,(select deptno,avg(sal) avgsal from emp group by deptno) deptwhere emp.deptno = dept.deptno and sal > dept.avgsal;--在DML语句中使用子查询--在INSERT语句中使用子查询create table EMPL(  id       NUMBER(10),  name     varchar2(20),  title    varchar2(100),  salary   number(10,2),  EMPNO    NUMBER(10),  HIREDATE TIMESTAMP(6),  COMM     NUMBER(10,2),  JOB      NVARCHAR2(255),  DEPTNO   NUMBER(10))insert into empl(id,name,title,salary) select empno,ename,job,sal from emp;select * from empl;--在UPDATE语句中使用子查询--当在UPDATE语句中使用子查询时，既可以在WHERE子句中引用子查询（返回未知条件值），也可以在SET子句中使用子查询（修改列数据）--将SCOTT同岗位的雇员工资和补助更新为与SCOTT的工资和补助完全相同为例update emp set(sal,comm) =(select sal,comm from emp where ename='SCOTT')where job=(select job from emp where ename='SCOTT');select * from emp for update;--在DELETE语句中使用子查询delete from emp where deptno=(select deptno from dept where dname='SALES');select * from dept;--在DDL语句中使用子查询--在CREATE TABLE语句中使用子查询create table new_emp(id,name,sal,job,deptno) as select empno,ename,sal,job,deptno from emp;select * from new_emp;--在CREATE VIEW语句中使用子查询create or replace view dept_10 as select empno,ename,job,sal,deptno from empwhere deptno=1 order by empno;select * from dept_10;--在CREATE MATERIALIZED VIEW语句中使用子查询create MATERIALIZED VIEW summary_emp as select deptno,job,avg(sal) avgsal,sum(sal) sumsalfrom emp group by cube(deptno,job);--UNION--用于获取两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行，并且会以第一列的结果进行排序。select ename,sal,job from emp where sal>100unionselect ename,sal,job from emp where job='JOB';select * from emp;--UNION ALL--UNION ALL操作符用于获取两个结果集的并集。--但与UNION操作符不同，该操作符不会取消重复值，而且也不会以任何列进行排序。select ename,sal,job from emp where sal>100union allselect ename,sal,job from emp where job='JOB';--INTERSECT--INTERSECT操作符用于获取两个结果集的交集。--当使用该操作符时，只会显示同时存在于两个结果集中的数据，并且会以第一列进行排序。select ename,sal,job from emp where sal>100intersectselect ename,sal,job from emp where job='JOB';--MINUS--MINUS操作符用于获取两个结果集的差集。--当使用该操作符时，只会显示在第一个结果集中存在，在第二个结果集中不存在的数据，并且会以第一列进行排序。select ename,sal,job from emp where sal>100minusselect ename,sal,job from emp where job='JOB';select * from emp for update;--其他复杂查询--层次查询--下面以显示除"CLERK"外所有其他雇员的上下级关系为例，说明使用层次查询的方法。select LPAD(' ',3*(LEVEL-1))||ename ename,LPAD(' ',3*(LEVEL-1))||job job from empwhere job<>'CLERK' start with mgr is NULLconnect by mgr=PRIOR empno;--使用CASE表达式select ename,sal,CASE when sal>100 then 3when sal>20 then 2else 1 end gradefrom emp where deptno=10;update emp set sal=1000 where empno=8;--查看当前数据select ename,sal from emp where ename='FAT';--查看历史数据select ename,sal from emp as of timestamp to_timestamp('2010-10-26 10:48:00','YYYY-MM-DD HH24:MI:SS')where ename='FAT'--使用WITH子句重用子查询--显示部门工资总和高于雇员工资总和三分之一的部门名及工资总和（两次使用相同子查询）select dname,sum(sal) as dept_total from emp,deptwhere emp.deptno=dept.deptno group by dnamehaving sum(sal) >(select sum(sal)*1/3 from emp,deptwhere emp.deptno = dept.deptno);--显示部门工资总和高于雇员工资总和三分之一的部门名及工资总和（使用WITH子句重用子查询）with summary as (select dname,sum(sal) as dept_total from emp,deptwhere emp.deptno=dept.deptno group by dname)select dname,dept_total from summary where dept_total>(select sum(dept_total)*1/3 from summary);