Oracle常用语句练习范文.docx
《Oracle常用语句练习范文.docx》由会员分享，可在线阅读，更多相关《Oracle常用语句练习范文.docx（18页珍藏版）》请在冰豆网上搜索。
Oracle常用语句练习范文
/**
创建表空间
DATAFILE指定该表空间创建的位置.文件的后缀为*.DBF
SIZE初始大小
AUTOEXTENDONNEXT指定自动增长大的增量
MAXSIZE最大上限UNLIMITED无限大
LOGGING记录日志
**/
CREATETABLESPACEspaces
DATAFILE'E:
\database\spaces.DBF'
SIZE5M
AUTOEXTENDONNEXT1M
MAXSIZEUNLIMITED
LOGGING;
--删除表空间
DROPTABLESPACEspacesINCLUDINGCONTENTSANDDATAFILES;
--查看当前用户所有的表空间对象
SELECT*FROMuser_tablespaces;
--创建用户
CREATEUSERtestIDENTIFIEDBYtestDEFAULTTABLESPACEspaces;
--删除用户
DROPUSERtestCASCADE;
--给用户赋予权限（角色）
GRANTCONNECT,RESOURCE,CREATEVIEWTOtestWITHADMINOPTION;
GRANTALlONscott.empTOtestWITHGRANTOPTION;
GRANTALlONscott.deptTOtestWITHGRANTOPTION;
GRANTALlONscott.salgradeTOtestWITHGRANTOPTION;
--撤销权限
REVOKECONNECT,RESOURCEFROMtest;
--创建表
CREATETABLEtb
（
idINTPRIMARYKEYNOTNULL,
nameVARCHAR2（10）UNIQUENOTNULL,
sexNCHAR
（1）CHECK（sex='男'ORsex='女'），
scoreNUMBER（10,2）DEFAULT（100）
）
CREATETABLEsub_tb
（
idINTPRIMARYKEYNOTNULL,
fINTREFERENCEStb（id）
）
DROPTABLEtb;
--序列（自动增长序列）
CREATESEQUENCEmysequ
STARTWITH1000
INCREMENTBY2
NOMAXVALUE
CACHE100;
--插入信息,DEFAULT可以为默认关键字
INSERTINTOtb（id,name,sex,score）VALUES（1,'测试','女',99）;
INSERTINTOtb（id,name,sex,score）VALUES（2,'测试2','男',DEFAULT）;
INSERTINTOtbVALUES（mysequ.nextval,'测试3','男',DEFAULT）;
SELECTmysequ.currval当前序列,mysequ.nextval下一个序列FROMdual;
--添加列
ALTERTABLEtbADDtimesDATEDEFAULT（SYSDATE）;
--修改列
ALTERTABLEtbMODIFYsexNCHAR
（1）DEFAULT（'男'）;
--删除列
ALTERTABLEtbDROPCOLUMNscore;
--修改表的名称
RENAMEnew_tbTOtb;
--查询当前用户所有的表信息
SELECT*FROMuser_tables;
--查询并创建
CREATETABLEmyempASSELECT*FROMscott.empWHERE1=2;
CREATETABLEmydeptASSELECT*FROMscott.dept;
CREATETABLEmysalgradeASSELECT*FROMscott.salgrade;
--查询并插入
INSERTINTOmyempSELECT*FROMscott.emp;
/**
事务的处理
ROLLBACK回滚事务
COMMIT提交事务,真正的向数据库提交信息
如果直接COMMIT或者ROLLBACK则中途所有的保存点都会消失
DELETE该删除属于DML数据操作语言,在删除数据的时候会记录日志.
是为了恢复而准备.所以速度慢.
TRUNCATETABLE该删除属于DDL语言.没有记录日志信息.速度快.无法恢复
**/
TRUNCATETABLEmyemp;
--创建保存点
SAVEPOINTa;
--回滚事务
ROLLBACK;
ROLLBACKTOa;
--提交事物
COMMIT;
DELETEFROMmyempWHEREename='SMITH';
--查询SMITH的薪水,工作,所在部门
SELECTsal,job,deptnoFROMmyempWHEREename='SMITH';
--显示每个雇员的年工资
SELECTsal*12+NVL（comm,0）别名FROMmyemp;
--如何显示工资高于3000的员工
SELECT*FROMmyempWHEREsal>3000;
--如何查找1982.1.1后入职的员工
SELECT*FROMmyempWHEREhiredate>'01-1月-1982';
--如何显示工资在2000到2500的员工情况
SELECT*FROMmyempWHEREsalBETWEEN2000AND2500;
SELECT*FROmmyempWHEREsal>=2000ANDsal<=2500;
--如何显示首字符为S的员工姓名和工资
SELECTename,salFROMmyempWHEREenameLIKE'S%';
--如何显示第三个字符为大写O的所有员工的姓名和工资
SELECTename,salFROMmyempWHEREenameLIKE'__O%';
--如何显示empno为123,345,800...的雇员情况
SELECT*FROMmyempWHEREempnoIN（123,345,800,7788）;
--如何显示没有上级的雇员的情况
SELECT*FROMmyempWHEREmgrISnull;
SELECT*FROMmyempWHEREmgrISNOTNULL;
--查询工资高于500或是岗位为MANAGER的雇员，同时还要满足他们的姓名首写字母为大写的J
SELECT*FROMmyempWHERE（sal>500ORjob='MANAGER'）ANDenameLIKE'J%';
--如何按照工资的从低到高的顺序显示雇员的信息
SELECT*FROMmyempORDERBYsalASC;
--按照部门号升序而雇员的入职时间降序排列
SELECT*FROMmyempORDERBYdeptnoASC,hiredateDESC;
--如何显示所有员工中最高工资和最低工资
SELECTMAX（sal）,MIN（sal）FROMmyemp;
--显示所有员工的平均工资和工资总和
SELECTAVG（sal）,SUM（sal）FROMmyemp;
--计算共有多少员工
SELECTCOUNT（*）FROMmyemp;
--请显示工资最高的员工的名字，工作岗位
SELECTename,job,salFROMmyempWHEREsal=（SELECTMAX（sal）FROMmyemp）;
--请显示工资高于平均工资的员工信息
SELECTename,job,salFROMmyempWHEREsal>（SELECTAVG（sal）FROMmyemp）;
--如何显示每个部门的平均工资和最高工资
SELECTdeptno,AVG（sal）,MAX（sal）FROMmyempGROUPBYdeptno;
--显示每个部门的每种岗位的平均工资和最低工资
SELECTdeptno,job,AVG（sal）,MAX（sal）FROMmyempGROUPBYdeptno,job;
--显示平均工资低于2000的部门号和它的平均工资
SELECTdeptno,AVG（sal）FROMmyempGROUPBYdeptnoHAVINGAVG（sal）<2000;
/***
SQL协议标准有三个版本,SQL89,SQL92,SQL99
其中内连接92标准是SELECT*FROMA,B,CWHERE连接条件AND筛选条件
99标准是SELECT*FROMAINNERJOINBON连接条件WHERE筛选条件
推荐使用新标准,思路更清晰
多表查询一定要起别名,方便些
**/
--显示雇员名,雇员工资及所在部门的名字
SELECTe.ename,e.sal,d.dnameFROMmyempe,mydeptdWHEREe.deptno=d.deptno;
SELECTe.ename,e.sal,d.dnameFROMmyempeINNERJOINmydeptdONe.deptno=d.deptno;
--如何显示部门号为10的部门名、员工名和工资
SELECTe.ename,e.sal,d.dnameFROMmyempe,mydeptdWHEREe.deptno=d.deptnoANDd.deptno=10;
SELECTe.ename,e.sal,d.dnameFROMmyempeJOINmydeptdONe.deptno=d.deptnoWHEREe.deptno=10;
--显示各个员工的姓名，工资，及其工资的级别
SELECTe.ename,e.sal,e.job,g.gradeFROMmyempeJOINmysalgradegONe.salBETWEENg.losalANDg.hisal;
--显示雇员名,雇员工资及所在部门的名字,并按部门排序.
SELECTe.ename,e.sal,d.dname,d.deptnoFROMmyempe,mydeptdWHEREe.deptno=d.deptnoORDERBYe.deptno;
--显示员工的上级领导的姓名
SELECTe1.empno,e1.ename上级,e2.empno,e2.ename下属FROMmyempe1,myempe2WHEREe1.empno=e2.mgr;
--如何显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
SELECT*FROMmyempWHEREsal>（SELECTMAX（sal）FROMmyempWHEREdeptno=30）;
SELECT*FROMmyempWHEREsal>ALL（SELECTsalFROMmyempWHEREdeptno=30）;
--如何显示工资比部门30的任意一个员工的工资高的员工的姓名、工资和部门号
SELECT*FROMmyempWHEREsal>ANY（SELECTsalFROMmyempWHEREdeptno=30）;
--查找每个部门工资最高的人的详细资料
SELECT*FROMmyempWHEREsalIN（SELECTMAX（sal）FROMmyempGROUPBYdeptno）;
--显示每个部门的信息和人员数量
SELECT*FROMmydeptd,（SELECTdeptno,COUNT（*）FROMmyempGROUPBYdeptno）tWHEREd.deptno=t.deptno;
--分页,伪列都是查询结果集数据的行信息,只是执行先后顺序问题而已
SELECT*FROM（
SELECTROWNUMr,t.*FROM（
SELECTROWNUM,myemp.*FROMmyempORDERBYsal）tWHEREROWNUM<=（2*5）
）t2WHEREt2.r>（2-1）*5;
--UNION联合查询
SELECT*FROMscott.empWHEREsal>2500UNIONALL
SELECT*FROMmyempWHEREjob='MANAGER';
SELECT*FROMscott.empWHEREsal>2500MINUS
SELECT*FROMmyempWHEREjob='MANAGER';
SELECT*FROMscott.empWHEREsal>2500INTERSECT
SELECT*FROMmyempWHEREjob='MANAGER';
--外连接
SELECT*FROMmyempeLEFTOUTERJOINmydeptdONe.deptno=d.deptno;
SELECT*FROMmyempeRIGHTJOINmydeptdONe.deptno=d.deptno;
SELECT*FROMmyempeFULLJOINmydeptdONe.deptno=d.deptno;
SELECT*FROMmyempe,mydeptdWHEREe.deptno=d.deptno（+）;
SELECT*FROMmyempe,mydeptdWHEREe.deptno（+）=d.deptno;
--字符替换函数
SELECTename,REPLACE（ename,'TA','ta'）FROMmyemp;
--连接字符
SELECT'姓名:
'||enameFROMmyemp;
SELECTCONCAT（'姓名:
',ename）FROMmyemp;
--将字符变成小写
SELECTename,LOWER（ename）FROMmyemp;
--将字符变成大写
SELECTename,UPPER（ename）FROMmyemp;
SELECTLOWER（'你好'）FROMmyemp;
--将首字母大写剩余字母全部小写
SELECTename,INITCAP（ename）FROMmyemp;
--字符截取，Oracle函数设计灵活
SELECTename,SUBSTR（ename,2）FROMmyemp;
SELECTename,SUBSTR（ename,2,5）FROMmyemp;
SELECTename,SUBSTR（ename,-2,1）FROMmyemp;
--判断该字符是否存在,如果存在则返回首次出现的位置数否则为0
SELECTename,INSTR（ename,'A'）FROMmyemp;
--去除指定的字符
SELECTename,TRIM（'A'FROM'AABBAA'）FROMmyemp;
--替换多个字母
SELECTename,TRANSLATE（ename,'SM','<>'）FROMmyemp;
--该字母ASCII编码
SELECTASCII（'w'）FROMmyemp;
SELECTASCII（'w'）FROMdual;
--四舍五入如果不写精确的位数则默认针对于小数点后一位.如果有位数则针对于该位数.如果该位数为负数
--精确到小数点左边的位数值进行四舍五入.同时如果没有进位则为0.舍弃小数点位.
SELECTsal,ROUND（15.64,-2）FROMmyemp;
--数字截取.如果截取的位数为负数则截取整数部分.截取的值直接归
SELECTTRUNC（15.64,-2）FROMmyemp;
--取模10%3==110/==3
SELECTMOD（10,3）FROMmyemp;
--向下取整
SELECTFLOOR（-11.5）FROMmyemp;
--向上取整
SELECTCEIL（-11）FROMmyemp;
--绝对值
SELECTABS（-1.1）FROMmyemp;
--系统当前时间
SELECTSYSDATEFROMdual;
--添加月份
SELECThiredate,ADD_MONTHS（hiredate,-4）FROMmyemp;
--返回当前时间按月份的最后一天
SELECThiredate,LAST_DAY（hiredate）FROMmyemp;
--返回两个时间差集月份,日期-日期==天数格式
SELECTename,hiredate,MONTHS_BETWEEN（SYSDATE,hiredate）FROMmyemp;
SELECTSYSDATE-hiredateFROMmyemp;
--显示下一个指定的时间
SELECTNEXT_DAY（hiredate,'星期日'）FROMmyemp;
--转换字符类型
SELECTsal,hiredate,TO_CHAR（sal,'$000,999,999.99'）,TO_CHAR（hiredate,'yyyy/mm/dd/hh24:
mi:
ss'）FROMmyemp;
--转换日期类型TO_DATE（转换的数据yymmdd）不要写汉字
SELECTTO_DATE（'87-5-2','yyyy/mm/dd'）FROMdual;
--显示所有员工的姓名,用”我是A”替换所有"A“
SELECTename,REPLACE（ename,'A','我是A'）FROMmyemp;
--显示在一个月为30天的情况所有员工的日薪金,忽略余数.
SELECTsal,TRUNC（sal/30）FROMmyemp;
--查找已经入职8个月多的员工
SELECTmyemp.*,MONTHS_BETWEEN（SYSDATE,hiredate）FROMmyempWHEREMONTHS_BETWEEN（SYSDATE,hiredate）>8;
--显示满10年服务年限的员工的姓名和受雇日期.
SELECTmyemp.*,MONTHS_BETWEEN（SYSDATE,hiredate）/12FROMmyempWHEREMONTHS_BETWEEN（SYSDATE,hiredate）/12>10;
--对于每个员工,显示其加入公司的天数.
SELECTmyemp.*,CEIL（SYSDATE-hiredate）FROMmyemp;
--找出各月倒数第3天受雇的所有员工.
SELECTmyemp.*,LAST_DAY（hiredate）FROMmyempWHEREhiredate=LAST_DAY（hiredate）-2;
--日期是否可以显示时/分/秒
SELECThiredate,TO_CHAR（hiredate,'yyyy-mm-ddhh24:
mi:
ss'）FROMmyemp;
--薪水是否可以显示指定的货币符号
SELECTsal,TO_CHAR（sal,'L000,999,999.99'）FROMmyemp;
--显示1980年入职的所有员工
SELECTmyemp.*,hiredateFROMmyempWHERE'1980'=TO_CHAR（hiredate,'yyyy'）;
--显示所有12月份入职的员工
SELECTmyemp.*,hiredateFROMmyempWHERE'12'=TO_CHAR（hiredate,'mm'）;
--备份其他用户表信息
expuserid=system/systemtables=（scott.emp,scott.dept,scott.salgrade）file=F:
\scott.dmp
--导入其他用户备份信息
impuserid=system/systemfile=F:
\scott.dmpfromuser=scotttouser=test
--导出自身方案
expuserid=scott/scottowner=scottfile=F:
\scott.dmp
impuserid=scott/scottfile=F:
\scott.dmp
--导入导出别人方案
expuserid=system/systemowner=scottfile=F:
\scott.dmp
impuserid=system/systemfile=F:
\scott.dmpfromuser=文件备份方案名touser=需要恢复的对象方案
--创建索引,可以提高检索速度
CREATEINDEXemp_indexONemp（ename）;
DROPINDEXemp_index;
--创建视图
CREATEORREPLACEVIEWpaging_viewAS
SELECT*FROM（
SELECTROWNUMr,t.*FROM
（SELECT*FROMempORDERBYsal）t
WHEREROWNUM<=2*5
）t2WHEREt2.r>（2-1）*5
SELECT*FROMpaging_view;
DROPVIEWpaging_view;
--创建同义词
CREATESYNONYMeFORscott.emp;
SELECT*FROMscott.emp;
SELECT*FROMe;
DROPSYNONYMe;
---------------------PL/SQL编程,基于SQL之上的一种SQL编程语言------------------------------------------------------
DECLARE
--声明变量
v_namesVARCHAR2（100）:
='默认值,写法比较奇怪.';
v_jobemp.job%TYPE;--job变量的类
升级会员 