Oracle常用语句练习范文Word文件下载.docx

资源描述

Oracle常用语句练习范文Word文件下载.docx

《Oracle常用语句练习范文Word文件下载.docx》由会员分享，可在线阅读，更多相关《Oracle常用语句练习范文Word文件下载.docx（18页珍藏版）》请在冰豆网上搜索。

Oracle常用语句练习范文Word文件下载.docx

（

idINTPRIMARYKEYNOTNULL,

nameVARCHAR2（10）UNIQUENOTNULL,

sexNCHAR

（1）CHECK（sex='

男'

ORsex='

女'

），

scoreNUMBER（10,2）DEFAULT（100）

）

CREATETABLEsub_tb

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'

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='

--显示每个雇员的年工资

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的所有员工的姓名和工资

__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的所有员工的工资高的员工的姓名、工资和部门号

（SELECTMAX（sal）FROMmyempWHEREdeptno=30）;

ALL（SELECTsalFROMmyempWHEREdeptno=30）;

--如何显示工资比部门30的任意一个员工的工资高的员工的姓名、工资和部门号

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='

2500MINUS

2500INTERSECT

--外连接

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（'

你好'

--将首字母大写剩余字母全部小写

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,'

--去除指定的字符

SELECTename,TRIM（'

FROM'

AABBAA'

--替换多个字母

SELECTename,TRANSLATE（ename,'

SM'

--该字母ASCII编码

SELECTASCII（'

）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,'

星期日'

--转换字符类型

SELECTsal,hiredate,TO_CHAR（sal,'

$000,999,999.99'

）,TO_CHAR（hiredate,'

yyyy/mm/dd/hh24:

mi:

ss'

--转换日期类型TO_DATE（转换的数据yymmdd）不要写汉字

SELECTTO_DATE（'

87-5-2'

yyyy/mm/dd'

--显示所有员工的姓名,用”我是A”替换所有"

A“

我是A'

--显示在一个月为30天的情况所有员工的日薪金,忽略余数.

SELECTsal,TRUNC（sal/30）FROMmyemp;

--查找已经入职8个月多的员工

SELECTmyemp.*,MONTHS_BETWEEN（SYSDATE,hiredate）FROMmyempWHEREMONTHS_BETWEEN（SYSDATE,hiredate）>

--显示满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:

--薪水是否可以显示指定的货币符号

SELECTsal,TO_CHAR（sal,'

L000,999,999.99'

--显示1980年入职的所有员工

SELECTmyemp.*,hiredateFROMmyempWHERE'

1980'

=TO_CHAR（hiredate,'

yyyy'

--显示所有12月份入职的员工

12'

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:

impuserid=scott/scottfile=F:

--导入导出别人方案

expuserid=system/systemowner=scottfile=F:

\scott.dmpfromuser=文件备份方案名touser=需要恢复的对象方案

--创建索引,可以提高检索速度

CREATEINDEXemp_indexONemp（ename）;

DROPINDEXemp_index;

--创建视图

CREATEORREPLACEVIEWpaging_viewAS

SELECT*FROM（

SELECTROWNUMr,t.*FROM

（SELECT*FROMempORDERBYsal）t

WHEREROWNUM<

=2*5

（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变量的类

展开阅读全文