推荐下载OracleSQL语句大全尚学堂马士兵讲义 精品.docx

推荐下载OracleSQL语句大全尚学堂马士兵讲义 精品.docx

《推荐下载OracleSQL语句大全尚学堂马士兵讲义 精品.docx》由会员分享，可在线阅读，更多相关《推荐下载OracleSQL语句大全尚学堂马士兵讲义 精品.docx（25页珍藏版）》请在冰豆网上搜索。

推荐下载OracleSQL语句大全尚学堂马士兵讲义精品

OracleSQL语句大全

20XX-04-1315:

39

1.desc（描述）emp描述emp这张表

2.descdept部门表

3.descsalgrade薪水等级

4.select*fromtable查找表中的元素

5.dual是系统中的一张空表（可以用于计算各种数学表达式）

6.select*fromdual

7.selectsysdatefromdual取出系统时间

8.selectename,sal*12"annul_sal"（取的别名）fromemp;查找用户姓名和用户的年薪

9.任何含有空值的数学表达式的值都是空值

selectename,sal*12+mfromemp;

10.selectename||salfromemp其中的||（字符串连接符）相当于将sal全部转化为字符串

11.表示字符串的方法

selectename||'ajjf'fromemp;

12.如果其中有一个单引号就用2个单引号来代替他

selectename||'sakj''lds'fromemp;

13.selectdistinctdeptnofromemp（去除部门字段中重复的部分,关键字distinct）

14.selectdistinctdeptno,jobfromemp;（去除这2个字段中重复的组合）

15.select*fromdeptwheredeptno=10;取出条件（取出部门编号为10的记录）

16.select*fromempwhereename='CLIRK';取出部门中姓名为clirk的记录（注意取出过程中ename用单引号隔开）

17.selectename,salfromempwheresal>1500;取出部门中薪水大于1500的人的姓名

18.selectename,sal,deptnofromempwheredeptno<>10取出部门中的部门号不等于10的

19.selectename,sal,deptnofromempwhereename>'CBA'取出部门中员工名字大于CBA的员工（实际比较的是ACIIS码）

20.selectename,salfromempwheresalbetween800and1500

selectename,salfromempwheresal>=800andsal<=1500;（取出800和1500之间的数）

21.selectename,sal,mfromempwheremisnull（选出其中的空值）

selectenmae,sal,mfromempwheremisnotnull（选出其中的非空值）

22.selectename,sal,mfromempwheresalin（800,1500,2000）;取出这3者之中的

selectename,sal,mfromempwhereenamein（'simth'）;

23.selectename,sal,hiredatefromempwherehiredata>'3-04月-81';宣传符合条件的日期

24.selectename,sal,fromempwheresal>1000ordeptno=10;找出工资薪水大于1000或者部门号等于10的员工

25.selectename,salfromempwheresalnotin（500,1000）;查找薪水不在500到1000的员工姓名和月薪

26.selectename,salfromempwhereenamelike'%ALL%';查找姓名中含有ALL的客户信息%表示0个或多个字母

selectename,salfromempwhereenamelike'_A%';查找第二个字母是A的客户信息

27.selectename,salfromempwhereenamelike'_%$%%'escape'$';自己指定转义字符

selectename,salfromempwhereenamelike'_%\%%';查找中间含有%相匹配的客户信息，运用转易字符

28.select*fromdeptorderbydeptno对表中元素按部门号排序

select*fromdeptorderbydeptnodesc默认为升序asc，可以用desc按降序

29.selectename,salfromempwheresal<>1000orderbysaldesc,enamedesc按照查询条件来查询，并排序（asc升序排列）

30.selectename,sal*12fromempwhereenamenotlike'_%A%'andsal>800orderbysaldesc

31.selectlower（ename）fromemp将ename都转化为小写lower是函数能将字母转化为小写，upper（ename）

32.selectenamefromempwherelower（ename）like'_%a%';找出ename中所有的含有a的字符

33.selectsubstr（ename,2,3）formemp从第2个字符开始截取3个字符

34.selectchr（65）fromdual;将65转化为字符

35.selectascii（'A'）fromdual将ACSII码转化为字符串

36.selectround（23.565）fromdual四舍五入

36.selectround（23,4565,2）fromdual四舍五入到第二位（第二个参数默认为0）

37.selectto_char（sal,'$99,999.9999'）fromemp按指定格式输出（9代表一位数字）

selectto_char（sal,'L99,999.9999'）formempL代表本地字符（￥）0代表占位符

38.selecthiredatefromemp

selectto_char（hiredate,'YYYY-MM-DDHH:

MI:

SS）fromemp;时间格式的显示

selectto_char（sysdate,'YYYY-MM-DDHH:

MI:

ss）fromdual;十二小时制显示系统时间

selectto_char（sysdate,'YYYY-MM-DDHH24:

MI:

SS）fromdual二四小时制显示系统时间

39.selectename,hiredatefromempwherehiredate>to_date（'20XX-2-312:

32:

23','YYYY-MM-DDHH:

MI:

SS'）;把特定格式字符串转换成日期

40selectsalfromempwheresal>to_number（'$1,250.00','$9,999.99'）;取出比它大的一切字符串（把特定格式的数字转化成字符）

41selectename,sal+nvl（m,0）fromemp;讲m值为空的用0来替换，单行函数（以一条记录为条件）一条对一条

组函数

42.selectMax（sal）fromemp;

selectMin（sal）fromemp;

selectavg（sal）fromemp;

selectsum（sal）fromemp;

selectcount（*）fromemp;查看表中一共有多少条记录

selectcount（*）fromempwheredeptno=10;查找部门10一共有多少人；

43.selectavg（sal）,deptnofromempgroupbydeptno;按部门号进行分组

selectdeptno,job,max（sal）fromempgroupbyjob,deptno;按工作和部门号进行分组；

44.selectenamefromempwheresal=（selectmax（sal）fromemp）;子查询，查找部门中薪水最高的员工姓名

45.groupby注意：

出现在select列表中的字段，如果没有出现在组函数中必须出现在groupby子句中

where是过滤单条语句,having可以过滤分组。

46.selectavg（sal）,deptnofromempgroupbydeptnohavingavg（sal）>2000;选出部门中平均薪水大于2000的部门，

47.select*fromempwheresal>100groupbydeptnohaving..........orderby........

先取数据--过滤数据------分组----对分组限制-------排序

48.selectavg（sal）fromempwheresal>1200groupbydeptnohavingavg（sal）>1500orderbyavg（sal）desc;

查找部门中平均薪水大于1200的员工，并按部门号进行排序，查询分组后的平均薪水必须大于1500，查询结果按平均薪水从低到高排列

子查询（把它当成一张表）

49.selectenamefromempwheresal>（selectavg（sal）fromemp）;

查找出员工中薪水位于部门平均薪水之上的所有员工

50.selectename,salfromempjoin（selectmax（sal）max_salfromempgroupbydeptno）ton（emp.sal=t,max_salandemp.deptno=t.deptno）;

查找每个部门中薪水最高的

51.selecte1.ename,e2.enamefromempe1,empe2wheree1.mgr=e2.empno;表的自连接（当成两张表来用）

1999SQL标准

where语句里不写“连接条件”，只写数据过滤条件。

joinon（表间连接条件），where写过滤条件，思路清晰。

52.selectdname,enamefromempcrossjoindept交叉连接，笛卡尔

SQL99中的新语法

53.selectename,dnamefromempjoindepton（emp.deptno=dept.deptno）;//on（连接条件）

54.selectename,dnamefromempjoindeptusing（deptno）;不推荐查找emp和dept表中deptno相同的部分。

55.selectename,dname,gradefromempejoindeptdon（e.deptno=d.depno）

joinsalgradeson（e.salbetweens.losalands.hisal）

whereenamenotlike'_%A%';（三表查找）

56.selecte1.ename,e2.enamefromempe1joinempe2on（e1.mgr=e2.deptno）;表的自连接

57.selecte1.ename,e2.enamefromempe1leftjoinempe2on（e1.mgr=e2.empno）左外表连接//可以将左边表多余的数据拿出来，右外表连接：

right（outer）joinon

selectename,dnamefromemperightjoindeptdon（e.deptno=d.deptno）右外连接

selectename,dnamefromempefulljoindeptdon（e.deptno=d.deptno）全连接

58.求部门中薪水最高的

selectename,salfromempjoin（selectmax（sal）max_sal,deptnofromempgroupbydeptno）ton（emp.sal=t.max_salandemp.deptno=t.deptno）;

---求部门平均薪水的等级

selectdeptno,avg_sal,gradefrom

（selectdeptno,avg（sal）avg_salfromempgroupbydeptno）t

joinsalgradeson（t.avg_salbetweens.losalands.hisal）;

----求部门中各成员的平均薪水等级

selectdeptno,ename,gradefromemp

joinsalgradeson（emp.salbetweens.losalands.hisal）;

59.求部门中薪水等级的平均值

selectdeptno,avg（grade）from（selectdeptno,ename,gradefromempjoinsalgradeson（emp.salbetweens.losalands.hisal））tgroupbydeptno;

60.查找雇员中哪些是经理人

selectenamefromempwhereempnoin（selectdistinctmgrfromemp）;

61.selectdistincte1.salfromempe1joinempe2on（e1.sal

selectdistinctsalfromempwheresalnotin（selectename,e1.salfromempe1joinempe2on（e1.sal

62.求平均薪水最大的部门的部门编号

selectdeptno,avg_salfrom

（selectavg（sal）avg_sal,deptnofromempgroupbydeptno）t

whereavg_sal=

（selectmax（avg_sal）from

（selectavg（sal）avg_sal,deptnofromempgroupbydeptno））;

写法2：

selectdeptno,avg_salfrom

（selectavg（sal）avg_sal,deptnofromempgroupbydeptno）t

whereavg_sal=

（selectmax（avg（sal））fromempgroupbydeptno）;max（avg（sal））组函数嵌套，最多两层

63.求平均薪水最大的部门的部门名称

selectdnamefromdeptwheredeptno=

（

selectdeptnofrom

（selectavg（sal）avg_sal,deptnofromempgroupbydeptno）

whereavg_sal=

（selectmax（avg_sal）from

（selectavg（sal）avg_sal,deptnofromempgroupbydeptno）

）

）;

64,求平均薪水等级最低的部门的部门名称

selectdname,t1.deptno,grade,avg_salfrom

（

selectdeptno,grade,avg_salfrom

（selectdeptno,avg（sal）avg_salfromempgroupbydeptno）t

joinsalgradeson（t.avg_salbetweens.losalands.hisal）

）t1

joindepton（t1.deptno=dept.deptno）

wheret1.grade=

（

selectmin（grade）from

（

selectdeptno,grade,avg_salfrom

（selectdeptno,avg（sal）avg_salfromempgroupbydeptno）t

joinsalgradeson（t.avg_salbetweens.losalands.hisal）

）

）

;

创建一个视图（一张表,虚表），简化SQL语句

createviewv$_dept_avg_sal_infoas

selectdeptno,grade,avg_salfrom

（selectdeptno,avg（sal）avg_salfromempgroupbydeptno）t

joinsalgradeson（t.avg_salbetweens.losalands.hisal）;

简化后代码

selectdname,t1.deptno,grade,avg_salfrom

v$_dept_avg_sal_infot1

joindepton（t1.deptno=dept.deptno）

wheret1.grade=

（

selectmin（grade）from

v$_dept_avg_sal_info

）;

65、求比普通员工最高薪水还高的经理人

selectenamefromemp

whereempnoin（selectdistinctmgrfromempwheremgrisnotnull）

and

sal>

（

selectmax（sal）fromempwhereempnonotin

（selectdistinctmgrfromempwheremgrisnotnull）

）

;

DML（数据操作语言）语句：

更、删、改、查

创建权限，connsys/adminassysdba

grantcreatetable,createviewtoscott;

首先在C:

下面建个文件夹备份文件

一.createNewUser方法

1.--backupscott---备份scott

exp---执行exp.exe文件///c:

\temp>exp,,.DMP文件。

在c:

\temp下

2.createuser（创建用户）用超级管理员模式进入

createuser用户名identifiedby密码defaulttablespaceusersquota10Monusers;

grantcreatesession,createtable,createviewto用户名---授予权限

dropuser用户名---删除用户

3.importthedata（导入备份数据）

imp

二.insert

insertintodeptvalues（50,'game','bj'）插入一条记录

insertintodept2（deptno,dname）values（78,'games'）;插入指定的几条记录

insertintodept2select*fromdept插入指定的表（表结构要一样）

rollback;回退（取消上一步操作）

createtableemp2asselect*fromemp;备份emp这张表

三.updateemp2setsal=sal*12wheredeptno=10;update的用法

四.deletefromdept2wheredeptno<25;删除语句的用法

rownumr只能和<和<=使用,不能用>或者>=或者=

transaction事务:

一个事务起始于一个DML语句

DDL（数据定义）语言

1.createtablet（avarchar2（10））;创建表:

2.droptablet删除表

3.mit所有的（上一条事务的修改）提交，所有修改都结束了。

对于rollback（rollback会回到原始状态）无效，一个事务开始于第1条DML语句

碰到执行DDLDCL语句事务自动提交（自动执行mit）对于rollback无效，

正常断开连接，自动提交。

非正常提交，自动回滚rollback

DCL：

数据控制语句----如grant授权

建表语句

建学生信息表：

createtablestu

（idnumber（6）,

namevarchar2（20）constraintstu_name_nnnotnull,

sexnumber

（1）,

agenumber（3）,

sdatedate,

gradenumber

（2）default1,

classnumber（4）,

emailvarchar2（50）unique（唯一约束）

）;

5个约束条件

非空notnull

唯一unique（不能插入重复值），

主键primarykey（可以唯一标识整条记录）：

非空，唯一，数字，可以使用多个字段的组合作为主键

外键foreignkey（class）referencesclass（id）,references参考class中的id，被参考的字段必须是主键。

被参考的记录是无法删除的

：

两张表，在一张表插入数据的时候，不允许插入另一张表中一个字段没有的数据。

默认添加。

check：

检查约束

createtablestu

（

idnumber（6）primarykey,

namevarchar2（20）constraintstu_name_nnnotnull,约束名constraint

sexnumber

（1）,

agenumber（3）,

sdatedate,

gradenumber

（2）default1,

classnumber（4）,

emailvarchar2（50）,

constraints