select * from deptselect * from emp1 where job='SALESMAN'AND ename like '李%'select e.sal,e.ename from emp e where to_char(e.hiredate,'YYYY')<'2011'select * from emp e where e.ename like '李%' and e.job='clerk' or e.job='MAG'select * from emp e where e.ename like '李%' and (e.job='clerk' or e.job='MAG')select * from emp e where (e.ename like '王%' or e.ename like '李%') --复制表结构及数据create table dept1 asselect rownum as num,t.* from dept t--只复制表结构CREATE TABLE dept1 AS SELECT t.* FROM dept t WHERE 1=2--删除表数据truncate table dept1select * from dept1--删除表drop table dept1--删除表数据，需要提交delete dept1--修改数据SELECT t.*,ROWID FROM dept1 tSELECT * FROM dept FOR UPDAT--两表合并数据，去除重复行SELECT e.* FROM emp1 eUNIONSELECT a.* FROM emp a--两表合并数据SELECT e.* FROM emp1 eUNION ALLSELECT a.* FROM emp a--EMP1表数据减去EMP表数据，差集SELECT e.* FROM emp1 eMINUSSELECT a.* FROM emp a--EMP1表数据与EMP表数据的交集SELECT e.* FROM emp1 eINTERSECTSELECT a.* FROM emp a--去重SELECT DISTINCT e.ename FROM emp1 eSELECT DISTINCT t.cdate,t.upperdeptno FROM dept tSELECT DISTINCT e.ename,e.empno FROM emp1 e--新建DEPT3,直插入dept表中内码大于5的CREATE TABLE dept3 ASSELECT t.* FROM dept t WHERE t.deptno>5--rownumSELECT ROWNUM,t.* FROM dept t WHERE ROWNUM<4--查询部门创建时间最早的前3条记录select * from (select * from dept t order by t.cdate) where rownum<4 SELECT * FROM dept WHERE ROWNUM<4 ORDER BY cdate 错误 --查询当前用户下用户表名包含DEPT字母的所有表SELECT * FROM user_tables where table_name like '%DEPT%'--查询201或202部门下的所有人SELECT * FROM EMP E WHERE E.deptno IN (201,202)SELECT * FROM EMP E WHERE e.deptno=201 OR e.deptno=202--查询不在201或202部门下的所有人SELECT * FROM EMP E WHERE E.deptno NOT IN (201,202)SELECT * FROM EMP E WHERE E.deptno!=201 AND E.deptno!=202--工资在500~1500SELECT * FROM EMP E WHERE sal BETWEEN 500 AND 1500--查询工资最高的2~5条记录SELECT ename,sal,r FROM(SELECT ename,sal,ROWNUM r FROM(SELECT ename,sal FROM emp ORDER BY NVL(sal,0) DESC)) /* nvl  没有数据的也统计上*/WHERE r>=2 AND r<=5

SELECT ename,sal,r FROM(SELECT ename,sal,ROWNUM r       FROM emp ORDER BY NVL(sal,0) desc)       WHERE r>=2 AND r<=5  错--查询工作岗位是办事员的工资最高的人员名称

SELECT * FROM EMP E WHERE E.SAL=(SELECT MAX(E.SAL) FROM EMP E WHERE E.JOB='CLERK')--查询工作岗位是办事员的姓名和年薪SELECT e.ename as 姓名, nvl(e.sal,0)*12+nvl(e.comm,0) AS 年薪 FROM EMP EWHERE E.JOB='CLERK'--复制表结构及数据create table emp1 asselect rownum as num,t.* from emp t--等于SELECT * FROM EMP E WHERE E.sal=500--不等于SELECT * FROM EMP E WHERE E.sal!=500SELECT * FROM EMP E WHERE E.sal<>500SELECT * FROM EMP E WHERE E.sal^=500--小于等于500SELECT * FROM EMP E WHERE E.sal<=500--sal为空SELECT * FROM EMP E WHERE nvl(E.sal,0)<=500--查询存在于DEPT表中所有人员SELECT * FROM emp1 eWHERE EXISTS(SELECT 1 FROM dept d WHERE d.deptno=e.deptno)SELECT e.* FROM emp1 e,dept dWHERE e.deptno=d.deptno--查询不存在于DEPT表中所有人员select * from emp1 e where not exists (select 1 from dept1 d where d.deptno=e.deptno)--复制表结构create table emp2 asselect * from emp where 1=2--使用日期函数--转成字符串SELECT t.deptno,t.dname,to_char(t.cdate,'YYYY-mm-dd hh24:mi:ss') cdateyearFROM dept t--转成日期SELECT to_date('2013-10-13','YYYY-MM-DD')FROM dualselect to_date('2013-10-15','YYYY-MM-DD') from dual--查询当前时间SELECT SYSDATE FROM dualSELECT SYSDATE FROM deptSELECT to_char(SYSDATE,'YYYY-MM-DD') FROM dual--列别名时，可以加as，也可以不加asSELECT NVL(e.sal,0)*12+NVL(e.comm,0) AS 年薪FROM emp1 eSELECT NVL(e.ename,'无名氏') FROM emp1 e--如果雇佣日期为空赋值为明天SELECT to_char(nvl(e.hiredate,SYSDATE+1),'YYYY-MM-DD') FROM emp1 eselect nvl(e.hiredate,to_date(to_char(sysdate,'yyyy-mm-dd'),'YYYY-MM-DD')) from emp1 e--连接字符串SELECT '此员工的姓名为 :['|| e.ename || ']' FROM emp1 eSELECT '['|| e.ename || '的岗位是:]' || e.job FROM emp1 e--where语句使用 查询姓刘的经理信息SELECT e.ename FROM emp1 e WHERE e.job='MANAGER' AND e.ename LIKE '刘%'--查询姓名中包含飞的经理SELECT e.ename FROM emp1 e WHERE e.job='MANAGER' AND e.ename LIKE '%飞%'--查询雇用日期在2011年前的所有人员信息SELECT * FROM emp1 e WHERE to_char(hiredate,'yyyy')<'2011'--查询姓刘或姓李的人员信息SELECT * FROM emp1 e WHERE e.ename LIKE '刘%' OR e.ename LIKE '李%'--查询所有办事员和经理的信息SELECT * FROM emp1 e WHERE e.job IN ('MANAGER','CLERK')--为空select * from emp1 e where e.ename is null--不为空select * from emp1 e where e.ename is not null--查询人员表当中姓王或者姓李的，并且雇佣日期在2011年之前的人员编号、姓名及岗位select e.empno,e.ename,e.jobfrom emp1 ewhere (e.ename like '王%' or e.ename like '李%')and to_char(e.hiredate,'yyyy')<'2011'   --只保留年份yyyy--工资降序，奖金将序select * from emp1 e order by nvl(e.sal,0) desc,e.comm desc--根据别名进行排序select nvl(e.sal,0)*12+nvl(e.comm,0) as 年薪 from emp1 e order by 年薪 desc--根据列位置进行排序SELECT to_char(deptno) as temp,dname FROM deptUNIONSELECT empno,ename FROM empORDER BY 1--UNION的SQL也可以根据别名进行排序SELECT to_char(deptno) as temp,dname FROM deptUNIONSELECT empno,ename FROM empORDER BY tempselect max(e.sal),min(e.sal),avg(e.sal),sum(e.sal),count(*) from emp1 eselect e.job,count(*),min(e.empno)from emp1 egroup by e.job 　　　　　　　　Group by --Group by语句中，select后只能跟聚合函数和分组变量（group by中） --　　group by.......having..... --查询人员表中姓李的存在重名的人员名称及人数 --select...from...where...group by...having...order by... select e.ename,count(*)from emp1 ewhere e.ename like '李%'group by e.enamehaving count(*)>1 --统计每个部门最高工资，单列分组统计select e.deptno,max(nvl(e.sal,0))from emp1 e group by e.deptnoorder by max(nvl(e.sal,0)) desc,e.deptno asc --根据最高工资倒序和部门编号升序排序，--统计每个部门每个岗位最高工资 ，多列分组统计select e.deptno,e.job,max(nvl(e.sal,0))from emp1 e group by e.deptno,e.jobhaving max(nvl(e.sal,0))>2000 --只显示最高工资大于2000的分组数据　　　　　　　　连接--1.相等连接、内连接，查询人员姓名及所属部门名称select e.ename,d.dnamefrom emp1 e ,dept1 dwhere e.deptno=d.deptno--2. 内连接 查询人员姓名及上级领导姓名select e.ename,a.enamefrom emp1 e ,emp1 awhere e.mgr=a.empno--3.左外连接，以左表为主，显示人员表的所有内容，当没有部门时候显示无所属部门select e.ename,nvl(d.dname,'无所属部门')from emp1 e LEFT JOIN dept1 dON e.deptno=d.deptno--4.左外连接，以左表为主，显示部门表的所有内容select e.ename,d.dname,d.deptnofrom dept1 d LEFT JOIN emp1 eON d.deptno=e.deptno--5.右外连接，以右表为主，显示部门表的所有内容select e.ename,nvl(d.dname,'无所属部门')from emp1 e right JOIN dept1 dON e.deptno=d.deptno--6.（+）以部门表为主，显示部门表的所有内容，看+号对面select e.ename,d.dnamefrom emp1 e ,dept1 dwhere e.deptno(+)=d.deptno--7.（+）以人员表为主，显示人员表的所有内容select e.ename,d.dnamefrom emp1 e ,dept1 dwhere e.deptno=d.deptno(+)--8.完全外连接，将两个表都不满足条件的数据都查询出来select e.ename,d.dname,d.deptnofrom dept1 d full JOIN emp1 eON d.deptno=e.deptno　　　　　　　　子查询--1.子查询，在WHERE语句当中使用子查询select d.dname from dept1 dwhere d.deptno=(select deptno from emp1 e where e.ename ='刘鹏飞')=select e.ename,d.dnamefrom dept1 d ,emp1 e where d.deptno=e.deptno and e.ename ='刘鹏飞'--2.子查询，在WHERE语句使用返回多条记录使用INselect d.dname from dept1 dwhere d.deptno in (select deptno from emp1 e where e.ename like '王%')--3.子查询，返回多列数据SELECT ename,job,sal,deptno FROM empWHERE (deptno,job)=(SELECT deptno,job FROM emp WHERE ename='刘鹏飞')=SELECT ename,job,sal,deptno FROM emp WHERE ename='刘鹏飞'--4.在INSERT语句里使用子查询insert into emp(empno,ename,deptno,job)select e.empno,e.ename,203,e.job from emp1 ewhere e.ename='小王'--5.在UPDATE语句里使用子查询，通过查询EMP表里小王的所属部门，修改EMP1表里小王的所属部门update emp1 eset e.deptno=(select a.deptno from emp a where a.empno=7950)where e.empno=7950;commit; --提交--6.在CREATE里使用子查询create table emp3asselect * from emp1　　　　　　　　函数--SQL常用函数--ASCII 返回指定字符对应的十进制数SELECT ASCII('A') A,ASCII('a') a,ASCII('0') zero,ASCII(' ') SPACE FROM dual--char和ascii相反，给出整数，返回对应的字符SELECT CHR(65),CHR(97) FROM dual--连接字符串SELECT '此员工的姓名为：【' || e.ename || '】' FROM emp1 e--连接两个字符串SELECT CONCAT('此员工的姓名为:',e.ename) FROM emp1 e--返回第二个字符串在第一个字符串中出现的位置SELECT INSTR('AABBASCAB','AB') FROM dual--如果没有返回0SELECT INSTR('AABBASCAB','ABC') FROM dualSELECT e.ename,INSTR(e.ename,'李') FROM emp1 e--从第二个位置查找SELECT INSTR('AABBASCAB','ABC'，2) FROM dual--从第二个位置查找，出现的第二次的位置SELECT INSTR('AABBASCAB','ABC'，2，2) FROM dual--返回字符串的长度，字节的长度SELECT LENGTH(e.ename),LENGTHB(e.ename) FROM emp1 e--返回字符串的小写SELECT LOWER('ABCdddd') FROM dual--返回字符串的大写SELECT UPPER('ABCdddd') FROM dual--去空格SELECT LTRIM(' ABCdddd ') FROM dualSELECT RTRIM(' ABCdddd ') FROM dualSELECT TRIM(' ABCdddd ') FROM dual--去左侧字符SELECT LTRIM('ABCdddd','AB') FROM dual--去右侧字符SELECT RTRIM('ABCdddd','d') FROM dualSELECT LTRIM(e.ename,'李') FROM emp1 e--从第3个位置取8个字符select substr('13088888888',3,8) from dualSELECT SUBSTR(e.ename,2,LENGTH(e.ename)-1) FROM emp1 e--将JOB字段当中的CL字符修改为ALselect replace(e.job,'CL','AL') from emp1 e--四舍五入，将sal字段四舍五入，保留2位小数select round(e.sal,2)from emp1 e where e.empno=7369SELECT round('13.5562',2) FROM dual--截取数字SELECT TRUNC('13.5562',2) FROM dual   13.55

SELECT TRUNC('13.5562',1) FROM dual   13.5

SELECT TRUNC('13.5562',-1) FROM dual   10

--给当前日期加2个月 select ADD_MONTHS(sysdate,2) from dual --给指定的日期加2个月  select ADD_MONTHS(to_date('2013-5-25','yyyy-mm-dd'),2) from dual SELECT to_char(nvl(ADD_MONTHS(e.hiredate,-1),ADD_MONTHS(SYSDATE,-1)),'yyyy-mm-dd') FROM emp1 e --求当前月份最后一天 select last_day(sysdate) from dual; --查询今年二月份的最后一天 select last_day(To_date('2017-02','yyyy-mm')) from dual select last_day(To_date('2017-02-01','yyyy-mm-dd')) from dual --查询明年当前月份的最后一天 SELECT last_day(ADD_MONTHS(SYSDATE,12)) FROM dual --返回特定日期之后的第一个工作日所对应的日期 select NEXT_DAY(sysdate,'星期一') from dual --返回当前用户 select user from dual --查询人员表中最高工资 select max(e.sal) 最高工资 from emp1 e --非常好用，相当于IF elsif ，如果JOB字段的值为clerk则显示办事员，如果为SALESMAN则显示销售员，否则全部显示领导 select decode(e.job,'CLERK','办事员','SALESMAN','销售员','领导') from emp1 e 　　 　　 　　　　　　　　DDL（创建、插入、更新） --1.最基本的插入语句，建议大家要把列名列出来 insert into emp1(num,empno,ename,deptno,job) values(18,8888,'小李',203,'CLERK') --2.插入数据采用默认值  insert into emp1(num,empno,ename,deptno,job,hiredate) 　　values(18,8888,'小李',203,default,sysdate) --3.查询所有人员姓名及所属部门名称，还有上级领导的姓名及所属部门名称 select e.ename,d.dname,a.ename,b.dname from dept1 d, emp1 e ,emp1 a,dept1 b where d.deptno=e.deptno and e.mgr=a.empno and a.deptno=b.deptno --分解上面4个表的SQL --1查询所有人员姓名及所属部门名称 select e.ename,d.dname from dept1 d, emp1 e where d.deptno=e.deptno --2查询所有人员姓名及所属部门名称及所属领导姓名  select e.ename,d.dname,a.ename from dept1 d, emp1 e,emp1 a where d.deptno=e.deptno and e.mgr=a.empno --3查询所有人员姓名及所属部门名称及所属领导姓名和部门名称 select e.ename,d.dname,a.ename,b.dname from dept1 d, emp1 e,emp1 a,dept1 b where d.deptno=e.deptno and e.mgr=a.empno and a.deptno=b.deptno 　　　　　　　　更新update select * from emp1 --更新王刚1的工资为2200  update emp1 e set e.sal = 2200 where e.ename='王刚1' update emp1 e set e.sal=e.sal + e.sal * 0.2 where e.ename='王刚'

--多列更新 update emp1 e set e.sal=e.sal + e.sal * 0.2,e.comm=e.comm+ e.comm * 0.2 where e.ename='王刚1' insert into emp1(num,empno,ename,deptno) values(22,8899,'小刘1',203) --给小刘1的岗位设成默认值 update emp1 e set e.job=default where e.ename='小刘' --重点记忆，在更新数据或删除数据时，一定要先查询数据，确认返回行是否正确。 select * from emp1 e where e.ename= '小刘' --通过子查询修改字段值 update emp1 e set e.sal=(select a.sal from emp1 a where a.ename='王刚2') where e.ename='小刘' --删除指定的数据 delete from emp1 e where e.ename='小刘1' --删除整个表的数据 delete from emp3 --免提交删除整个表的数据 truncate table emp3 　 　　 　　　　　　　　视图 --1.创建人员表视图 查询人员和部门名称以及上级领导的名字和部门名 create view v_emp1asselect e.empno,e.ename,d.dname,d.upperdeptno,e.mgr,a.ename mgrnamefrom emp1 e,dept1 d,emp1 awhere e.deptno=d.deptno and e.mgr=a.empno--视图的应用跟表的应用类似，也可以进行连接select r.*,d.dnamefrom v_emp1 r ,dept1 dwhere r.upperdeptno=d.deptno--2.创建一个复杂视图--注意：修改表结构会对引用它的视图、触发器有影响。create or replace view v_emp1_salasselect f.*,d.dname from dept1 d,(select e.deptno,max(e.sal) as hsal,min(e.sal) msal,avg(e.sal) asalfrom emp1 egroup by e.deptno) fwhere d.deptno=f.deptno--从当前用户视图字典表查询新建的视图，视图名是大写，所以本SQL语句查询不到数据SELECT * FROM user_views u where u.view_name like '%emp1%'--将视图名称转为小写，可以查询出数据SELECT * FROM user_views u where lower(u.view_name) like '%emp1%'--可以显示是否允许在特定视图列上执行DML操作 　　　　　　　　序列SELECT *FROM user_updatable_columns swhere s.table_name like '%EMP1%'--创建序列CREATE SEQUENCE deptno_seq START WITH 1 INCREMENT BY 1 MAXVALUE 99 CACHE 10;--查询deptno_seq的下一个值select deptno_seq.nextval from dual;--查询deptno_seq的当前值select deptno_seq.currval from dual--在插入语句中使用序列3insert into emp1(num,empno)values(99,deptno_seq.nextval)--创建一个同义词create or replace synonym dlv3emp for dlv3.emp--使用同义词select * from dlv3emp　　　　　　　　 　　　　　　　　游标 --游标的定义、使用、关闭declarecursor zb_cursor isselect ZB_INCODE from TWZ_CGMX_ZBLYwhere xh=v_xh;zb_record zb_cursor%rowtype;beginopen zb_cursor;fetch zb_cursor into zb_record;while zb_cursor%found loopv_bill_incode:=zb_record.ZB_INCODE;p_zb_liyong_delete(v_bill_incode);fetch zb_cursor into zb_record;end loop;close zb_cursor;end;--创建一个函数--函数的意义：根据输入参数的部门内码获得部门名称create or replace function f_get_dept_name(in_deptno number) return varchar2 isv_dname dept.dname%type;beginselect t.dname into v_dname from dept t where t.deptno=in_deptno;return(v_dname);end f_get_dept_name;--使用自定义的函数select e.ename, f_get_dept_name(e.deptno)from emp e--与下面这句SQL返回值相同select e.ename,d.dnamefrom emp e ,dept d where e.deptno=d.deptno　　　　　　　　 　　　　　　　　存储过程 --创建一个存储过程--该存储过程的含义：--将人员表中经理职位并且年薪大于7万的人员信息插入到MANAGER表中，输出参数是插入的条数create or replace procedure p_manager_update(o_ename_num out number) isbeginselect to_number(count(1)) into o_ename_numfrom emp ewhere nvl(e.sal,0)*12+nvl(e.comm,0) > 70000and e.job='MANAGER';insert into MANAGER(INCODE,ENAME,CREATE_DATE)select deptno_seq.nextval,e.ename,sysdatefrom emp ewhere nvl(e.sal,0)*12+nvl(e.comm,0) > 70000and e.job='MANAGER'; commit;end p_manager_update;--在COMMONAD WINDOWS里执行exec p_manager_update;--如何使用游标--如果MANAGER表里已经存在员工，不插入，如果不存在，再插入create or replace procedure p_manager_update2(o_ename_num out number) isbegindeclare--向MANAGER插入一条记录就+1，最终值就是输出参数v_num number(3);v_empno varchar2(20);v_ename varchar2(20);v_emp_num number(3);begin v_num:=0;declarecursor zb_cursor isselect e.empno,e.enamefrom emp ewhere nvl(e.sal,0)*12+nvl(e.comm,0) > 70000and e.job='MANAGER';zb_record zb_cursor%rowtype;beginopen zb_cursor;fetch zb_cursor into zb_record;while zb_cursor%found loop--从record中取出员工编号及员工姓名v_empno:=zb_record.empno;v_ename:=zb_record.ename;select count(*) into v_emp_numfrom manager m where m.empno=v_empno;if v_emp_num=0 theninsert into MANAGER(INCODE,ENAME,CREATE_DATE,empno)values(deptno_seq.nextval,v_ename,sysdate,v_empno);v_num := v_num+1;end if;fetch zb_cursor into zb_record;end loop;close zb_cursor;end;o_ename_num :=v_num;commit;end;end p_manager_update2;--新生成一张表emp_his 只复制表结构CREATE TABLE emp_his AS SELECT * FROM EMP WHERE 1=2;　　　　　　　　 　　　　　　　　触发器 --创新一个触发器，当删除EMP表前将删除的记录插入到emp_his表中set serveroutput on;CREATE OR REPLACE TRIGGER tr_del_empBEFORE DELETE --指定触发时机为删除操作前触发ON empFOR EACH ROW --说明创建的是行级触发器BEGIN--将修改前数据插入到日志记录表 del_emp ,以供监督使用。INSERT INTO emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate )VALUES( :old.deptno, :old.empno, :old.ename , :old.job,:old.mgr, :old.sal, :old.comm, :old.hiredate );END; DELETE emp WHERE empno=7788; DROP TABLE emp_his; DROP TRIGGER del_emp;

CREATE OR REPLACE TRIGGER tr_emp_sal_comm

BEFORE UPDATE OF salary, commission_pct

OR DELETE

ON HR.employees

FOR EACH ROW

WHEN (old.department_id = 80)

BEGIN

CASE

WHEN UPDATING ('salary') THEN

IF :NEW.salary < :old.salary THEN

RAISE_APPLICATION_ERROR(-20001, '部门80的人员的工资不能降');

END IF;

WHEN UPDATING ('commission_pct') THEN

IF :NEW.commission_pct < :old.commission_pct THEN

RAISE_APPLICATION_ERROR(-20002, '部门80的人员的奖金不能降');

END IF;

WHEN DELETING THEN

RAISE_APPLICATION_ERROR(-20003, '不能删除部门80的人员记录');

END CASE;

END;

　　　　　　导出导入 --在cmd下输入如下命令，导出全库数据库--exp 用户名/密码@网络服务名 file=导出文件存放路径及名称 full=y (表示全库导出)exp dlv3/dlv3@ydme file=e:\20131106full.dmp full=y--将数据库中的表table1和table2导出exp dlv3/dlv3@ydme file=e:\20131106dlv3table.dmp tables=(table1,table2)--将数据库中dlv3用户与dlv2用户的所有对象导出exp dlv3/dlv3@ydme file=e:\20131106dlv3dlv2.dmp owner=(dlv3,dlv2)--将E:\20131106dlv3.dmp 备份文件导入，将dlv3用户下的emp1和dept1表导入到DLV2用户下imp dlv2/ffffff@ydme fromuser=dlv3 touser=dlv2tables=(emp1,dept1) log=e:\20131106.log file=E:\20131106dlv3.dmp--将E:\20131106dlv3.dmp 备份文件导入，将dlv3用户下所有对象导入到DLV2用户下imp dlv2/ffffff@ydme fromuser=dlv3 touser=dlv2 log=e:\20131106.log file=E:\20131106dlv3.dmp

 

本文转载于 https://www.cnblogs.com/bhy-1116/p/8314297.html