经典Oracle的sql语句百例训练汇总.docx
《经典Oracle的sql语句百例训练汇总.docx》由会员分享，可在线阅读，更多相关《经典Oracle的sql语句百例训练汇总.docx（33页珍藏版）》请在冰豆网上搜索。
经典Oracle的sql语句百例训练汇总
Oracle系列《一》：
简单SQL与单行函数
使用s
cott/tiger用户下的emp表和dept表完成下列练习，表的结构说明如下
emp员工表（empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号）
dept部门表（deptno部门编号/dname部门名称/loc地点）
工资＝薪金＋佣金
登录Oracle数据库
1、sqlplusscott/tiger
2、sqlplus/nolog
SQL>connscott/tiger
若是使用SYS的账号进行登录的话，则使用以下语句
SQL>conn/assysdba
【1】EMP表内容查询
SQL>SELECT*FROMemp;
出错，原因是没有找到该表，因为该表时SCOTT用户的表，所以查询时应该加上scott.emp就可以了
【2】显示当前用户
SQL>showuser
【3】查看当前用户的所有表
SQL>SELECT*FROMtab;
【4】若想重复执行上一条SQL语句，则在sqlplus命令行下输入"/"即可
【5】查询一张表的结构，例如dept表
SQL>descdept
【6】在雇员表中查询雇员的编号、姓名、工作
SQL>SELECTempno,ename,jobFROMemp;
【7】可以为列名取别名，在Linux下Oracle如果英文别名不加上双引号则会变成大写
SQL>SELECTempno编号,ename姓名,job工作FROMemp;
【8】查询所有的工作
SQL>SELECTDISTINCTjobFROMemp;
工作可能会重复，加上DISTINCT关键字
【9】若要求按照以下的格式进行结果输出，如NO:
7469,Name:
SMITH,Job:
CLERK
SQL>SELECT'NO:
'||empno||',Name:
'||ename||',Job:
'||jobFROMemp;
【10】要求列出每个雇员的姓名及年薪
SQL>SELECTename,sal*12incomeFROMemp;
这里年薪最好用别名进行标识，可以一眼就能明白
【11】查看每月可以得到奖金的雇员信息
SQL>SELECT*FROMempWHEREcommisNOTNULL;
【12】要求基本工资大于1500，同时可以领取奖金的雇员信息
SQL>SELECT*FROMempWHEREsal>1500ANDcommisNOTNULL;
如果是或的是关系，则使用OR
【13】查询基本工资不大于1500，同时不可以领取奖金的雇员信息
SQL>SELECT*FROMempWHERENOT（sal>1500ANDcommisNOTNULL）;
【14】查询在1981年雇佣的全部雇员信息，BETWEEN..AND包含等于的情况
SQL>SELECT*FROMemp
WHEREhiredateBETWEEN'01-JAN-81'AND'31-DEC-81';
【15】Oracle对大小敏感，所以查询时名字要区分大小写
【16】要求查询出雇员编号不是7369、7499的雇员信息
SQL>SELECT*FROMemp
WHEREempnoNOTIN（7369,7499）;
【17】SQL中LIKE语句要注意通配符%和_
SQL>SELECT*FROMemp
WHEREhiredateLIKE'%81%';
【18】查看雇员编号不是7369的雇员信息，使用<>或!
=
SQL>SELECT*FROMemp
WHEREempno<>7369;
【19】要求对雇员的工资由低到高进行排序，升序为默认（ASC），降序（DESC）
SQL>SELECT*FROMemp
GROUPBYsal;
【20】查看出部门号为10的雇员信息，查询的信息按照工资从高到低，若工资相等则按雇用日期从早到晚排列
SQL>SELECT*FROMemp
WHEREdeptno=10
GROUPBYsalDESC,hiredateASC;
数据库系统中，每个数据库之间区别最大的就是在函数的支持上，单行函数是最简单的函数，单行函数分为
1、字符函数：
接受字符输入并且返回字符或数值
2、数值函数：
接受数值输入并返回数值
3、日期函数：
对日期型数据进行操作
4、转换函数：
将一种数据类型转换为另一种数据类型
5、通用函数：
NVL、DECODE函数
字符函数：
【1】大小写转换UPPER和LOWER
SQL>SELECTUPPER（'smith'）FROMdual;
【2】将雇员姓名变为开头字母大写，INITCAP
SQL>SELECTINITCAP（ename）FROMemp;
字符函数中有连接函数CONCAT，但不如||好用，还有字符串处理的一些函数
字符串截取：
substr（）
字符串长度：
length（）
内容替换：
replace（）
SQL>SELECTsubstr（'hello',1,3）,length（'hello'）,replace（'hello','l','x'）FROMdual;
这里注意的是Oracle中字符串截取从0和从1开始都是一样的，谨防面试提问
【3】要求显示所有雇员的姓名及姓名的后3个字符
SQL>SELECTename,SUBSTR（ename,LENGTH（ename）-2）FROMemp;
以上操作显得较为麻烦，substr（）函数是可以倒着截取
SQL>SELECTename,SUBSTR（ename,-3,3）FROMemp;
数值函数：
1、四舍五入：
ROUND（）
2、截断小数位：
TRUNC（）
3、取余（取模）：
MOD
SQL>SELECTROUND（789.536）FROMdual;
【1】保留2位小数,（如果是-2则对整数进行四舍五入，变为800了）
SQL>SELECTROUND（783.56,2）FROMdual;
【2】使用MOD（）函数进行取余操作
SQL>SELECTMOD（10,3）FROMdual;
日期函数：
1、日期-数字=日期
2、日期+数字=日期
3、日期-日期=数字（天数）
【1】求出当前日期
SQL>SELECTSYSDATEFROMdual;
Oracle提供了以下的日期函数支持：
MONTHS_BETWEEN（）:
求出给定日期范围的月数
ADD_MONTHS（）:
在指定日期上加上指定的月数，求出之后的日期
NEXT_DAY（）:
下一个的今天的日期
LAST_DAY（）:
求出给定日期的最后一天日期
【2】求出从雇用日期到今天所有雇员的雇员编号、姓名和月数
SQL>SELECTempno,ename,ROUND（MONTHS_BETWEEN（SYSDATE,hiredate））FROMemp;
【3】验证ADD_MONTHS（）、NEXT_DAY（）、LAST_DAY（）
SQL>SELECTADD_MONTHS（SYSDATE,4）FROMDUAL;
SQL>SELECTNEXT_DAY（SYSDATE,'MON'）FROMDUAL;
SQL>SELECTLAST_DAY（SYSDATE）FROMDUAL;
转换函数：
1、TO_CHAR（）：
将日期或数值转换成字符串
2、TO_NUMBER（）：
将字符串转换成数字
3、TO_DATE（）：
将字符串转换成日期
【1】将年月日进行分开，要指定拆分的通配符,yyyy-mm-dd
SQL>SELECTempno,ename,TO_CHAR（hiredate,'yyyy'）datetimeFROMemp;
【2】将薪水的数字进行格式化，'$99,999'表示美元，'L99,999'表示当地货币
SQL>SELECTempno,ename,TO_CHAR（sal,'99,999）salaryFROMemp;
【3】TO_NUMBER（）验证
SQL>SELECTTO_NUMBER（'123'）+TO_NUMBER（'123'）FROMDUAL;
【4】TO_DATE（）验证，如下例子执行后显示为11-JUL-11
SQL>SELECTTO_DATE（'2011-7-11','yyyy-mm-dd'）FROMDUAL;
通用函数：
【1】求出每个雇员的年薪（应算上奖金）
SQL>SELECTempno,ename,（sal+comm）*12FROMemp;
由于comm中有NULL,NULL值计算后还是NULL,正确如下：
SQL>SELECTempno,ename,NVL（comm,0）,（sal+NVL（comm,0））*12incomeFROMemp;
NVL可以理解为将NULL值转换为具体的内容，这里是0
【2】DECODE（）函数，该函数类似于IF...ELSEIF...ELSE
语法如下：
DECODE（col/expression,选择1,结果1[，选择2，结果2，...,默认]）
验证DECODE（）函数
SQL>SELECTempno,ename,hiredate,
DECODE（job,'CLERK','业务员','SALESMAN','销售人员',’MANAGER','经理','ANALYST','分析员','PRESIDENT','总裁'）职位
FROMemp;
SQL简单语句练习：
【1】找出佣金高于薪金的60%的员工
SQL>SELECT*FROMempWHEREcomm>sal*0.6
【2】找出部门10中所有经理（MANAGER）和部门20中所有办事员（CLERK）的详细资料
SQL>SELECT*FROMemp
WHERE（deptno=20ANDjob='MANAGER'）
OR（deptno=10ANDjob='CLERK'）;
【3】找出既不是经理又不是办事员但其薪金大于或等于2000的所有员工的资料
SQL>SELECT*FROMemp
WHEREjobNOTIN（'MANAGER','CLERK'）ANDsal>=2000;
【4】找出有奖金的员工的不同国祚
SQL>SELECTDISTINCTjobFROMemp
WHEREcommISNOTNULL;
【5】找出各月倒数第3天受雇的所有员工
SQL>SELECT*FROMemp
WHERELAST_DAY（hiredate）-2=hiredate;
【6】找出早于12年前受雇的员工
SQL>SELECT*FROMemp
WHEREMONTHS_BETWEEN（sysdate,hiredate）/12>12;
【7】显示刚好为5个字符的员工的姓名
SQL>SELECTenameFROMemp
WHERElength（ename）=5;
【8】显示不带有"R"的员工的姓名
SQL>SELECTenameFROMemp
WHEREenameNOTLIKE'%R%';
【9】显示员工的姓名和受雇日期，将最老的员工排在最前
SQL>SELECT*FROMemp
GROUPBYhiredate;
【10】显示所有员工的姓名，加入公司的年份和月份，按受雇日期所在月排序，若月份相同则按年份排序
SQL>SELECTename,TO_CHAR（hiredate,'yyyy'）year,TO_CHAR（hiredate,'mm'）monthFROMemp
ORDERBYmonth,year;
【11】找出在2月受聘的员工
SQL>SELECT*FROMemp
WHERETO_CHAR（hiredate,'mm'）=2;
【12】以年月日方式显示所有员工服务年限
SQL>SELECTename,TRUNC（MONTHS_BETWEEN（sysdate,hiredate）/12）year,
TRUNC（MOD（MONTHS_BETWEEN（sysdate,hiredate）,12））month,
TRUNC（MOD（sysdate-hiredate,30））day
FROMemp;
Oracle系列《二》：
多表复杂查询和事务处理
多表查询应该注意去除笛卡尔积，一般多个表时会为表起个别名
【1】要求查询雇员的编号、姓名、部门编号、部门名称及部门位置
SQL>SELECTe.empno,e.ename,d.deptno,d.dname,d.loc
FROMempe,deptd
WHEREe.deptno=d.deptno;
【2】要求查询每个雇员的姓名、工作、雇员的直接上级领导的姓名（表自关联）
SQL>SELECTe.ename,e.job,m.ename
FROMempe,empm
WHEREe.mgr=m.empno;
【3】对【2】进行扩充，将雇员所在部门名称同时列出
SQL>SELECTe.ename,e.job,m.ename,d.dname
FROMempe,empm,deptd
WHEREe.mgr=m.empnoANDe.deptno=d.deptno;
【4】查询每个雇员的姓名、工资、部门名称，工资在公司的等级（salgrade），及其领导的姓名所在公司的等级
<1>先确定工资等级表的内容
SQL>SELECT*FROMsalgrade;
<2>查询每个雇员的姓名、工资、部门名称和工资在公司的等级
SQL>SELECTe.ename,e.sal,d.dname,s.grade
FROMempe,deptd,salgrades
WHEREe.deptno=d.deptnoANDe.salBETWEENs.losalANDs.hisal;
<3>查询其领导姓名及工资所在公司的等级
SQL>SELECTe.ename,e.sal,d.dname,s.grade,m.ename,m.sal,ms.grade
FROMempe,deptd,salgrades,empm,salgradems
WHEREe.deptno=d.deptnoANDe.salBETWEENs.losalANDs.hisal
ANDe.mgr=m.empnoANDm.salBETWEENms.losalANDms.hisal;
【5】左连接与右连接的概念，"+"在等号左边表示右连接，反之，左连接
查询雇员的编号、姓名及其领导的编号、姓名
SQL>SELECTe.empno,e.ename,m.empno,m.ename
FROMempe,empm
WHEREe.mgr=m.empno（+）;
就发现将KING的那条记录也连过来了
SQL1999语法中有如下几种连接（了解）
1、交叉连接CROSSJOIN，产生笛卡尔积
SQL>SELECT*FROMempCROSSJOINdept;
2、自然连接NATURALJOIN,自动进行关联字段的匹配
SQL>SELECT*FROMempNATURALJOINdept;
3、使用USING子句，直接关联操作列
SQL>SELECT*FROMempJOINdeptUSING（deptno）
WHEREdeptno=30;
4、使用ON子句，用户自己编写的条件
SQL>SELECT*FORMempJOINdeptON（emp.deptno=dept.deptno）
WHEREdeptno=30;
5、左连接（左外连接、LEFT（OUTER）JOIN）、右连接（右外连接、RIGHT（OUTER）JOIN）
组函数及分组统计
1、COUNT（）:
求出全部记录数
2、MAX（）：
求出一组中最大值
3、MIN（）:
求出最小值
4、AVG（）：
求出平均值
5、SUM（）：
求和
【1】求出每个部门的雇员数量
SQL>SELECTdeptno,count（empno）
FROMemp
GROUPBYdeptno;
【2】按部门分组，并显示部门的名称，及每个部门的员工数
SQL>SELECTd.dname,COUNT（e.empno）
FROMempe,deptd
WHEREe.deptno=d.deptno
GROUPBYd.dname;
【3】要求显示平均工资大于2000的部门编号和平均工资
SQL>SELECTdeptno,AVG（sal）
FROMemp
WHEREAVG（sal）>2000
GROUPBYdeptno;
出错，WHERE子句中不能出现分组函数的条件，要使用HAVING子句
上述语句应该改为如下
SQL>SELECTdeptno,AVG（sal）
FROMemp
GROUPBYdeptno
HAVINGAVG（sal）>2000
【4】显示非销售人员工作名称以及从事同一工作雇员的月工资总和，并且要求从事同一工作的雇员月工资合计大于$5000,
输出结果按月工资的合计升序排序
<1>按工作分组，求出非销售人员的月工资总和
SQL>SELECTjob,SUM（sal）
FROMemp
WHEREjob<>'SALESMAN'
GROUPBYjob;
<2>对分组条件进行限制，然后进行排序,HAVING子句不能使用别名
SQL>SELECTjob,SUM（sal）totalSal
FROMemp
WHEREjob<>'SALESMAN'
GROUPBYjob
HAVINGSUM（sal）>5000
ORDERBYtotalSal;
【3】分组函数可以嵌套使用，但是在SELECT列中就不能再出现该分组条件的列名了
SQL>SELECTdeptno,MAX（AVG（sal））
FROMemp
GROUPBYdeptno;
出错！
修改如下
SQL>SELECTMAX（AVG（sal））
FROMemp
GROUPBYdeptno;
【4】查询出比7654工资要高的全部雇员的信息
<1>首先要查询雇员编号7654的工资
SQL>SELECTsalFROMempWHEREempno=7654;
<2>以上述条件的结果最后后续查询的依据
SQL>SELECT*FROMemp
WHEREsal>（SELECTsalFROMempWHEREempno=7654）;
子查询在操作中分为以下三类：
1、单列子查询：
返回的结果是一列的内容
2、单行子查询：
返回多个列，也可能是一条记录
3、多行子查询：
返回多个记录
【1】要求查询工资比7654高，同时与7788从事相同工作的全部雇员
SQL>SELECT*FROMemp
WHEREsal>（SELECTsalFROMempWHEREempno=7654）
ANDjob=（SELECTjobFROMempWHEREempno=7788）;
【2】要求查询部门名称、部门员工数、部门平均工资，部门的最低收入雇员的姓名
<1>查询部门员工数、部门平均工资
SQL>SELECTdeptno,COUNT（empno）,AVG（sal）
FROMemp
GROUPBYdeptno;
<2>查询部门的名称，及最低收入雇员姓名，要进行表关联（子查询）
SQL>SELECTd.dname,ed.c,ed.a,e.ename
FROMdeptd,（
SELECTdeptno,COUNT（empno）c,AVG（sal）a,MIN（sal）min
FROMemp
GROUPBYdeptno）ed,empe
WHEREd.deptno=ed.deptnoANDe.sal=ed.min;
若上述存在两个最低工资的情况，则会出错，在子查询中存在以下3种查询的操作符号
IN：
指定一个查询范围，例如查询每个部门的最低工资（返回值有多个）
SQL>SELECT*FROMemp
WHEREsalIN（SELECTMIN（sal）FROMempGROUPBYdeptno）;
ANY：
=ANY（与IN操作一样）、>ANY（比最小大）、SQL>SELECT*FROMemp
WHEREsalALL:
>ALL（比最大要大）、SQL多列子查询示例如下
SQL>SELECT*FROMemp
WHERE（sal,NVL（comm,-1））IN
（SELECTsal,NVL（comm,-1）FROMempWHEREdeptno=20）;
数据库更新操作INSERT、UPDATE、DELETE
【1】复制一张表，例如复制EMP表为MYEMP
SQL>CREATETABLEMYTEMPASSELECT*FROMemp;
【2】将编号为7899的雇员的领导取消
SQL>UPDATEmyempSETmgr=nullWHEREempno=7899;
【3】更新时，一定要注意不能批量更新（加上WHERE子句），多列更新例子如下
SQL>UPDATEmyempSETmgr=null,comm=nullWHEREempnoIN（7369,8899）;
【4】删除掉全部领取奖金的雇员
SQL>DELECTFROMempWHEREcommisNOTNULL;
事务处理ACID
A：
Atomicity 原子性：
事务中的操作或者都完成，或者都取消
C：
Consistency一致性：
事务中的操作保证数据库中的数据不会出现逻辑上不一致的情况
I：
Isolation 隔离性：
当前的事务与其他未完成的事务是隔离的
D：
Durability 持久性：
在COMMIT之后，数据永久保存在数据库中，在此之前，事务的操作都可以回滚
验证事务过程：
<1>创建一张临时表，只包含部门10
SQL>CREATETABLEemp10ASSELECT*FROMempWHEREempno=10;
<2>删除emp10中的7782雇员
SQL>DELETEFROMemp10WHEREempno=7782;
再打开另
升级会员 