尚学堂马士兵Oracle教程笔记.docx

尚学堂马士兵Oracle教程笔记.docx

《尚学堂马士兵Oracle教程笔记.docx》由会员分享，可在线阅读，更多相关《尚学堂马士兵Oracle教程笔记.docx（27页珍藏版）》请在冰豆网上搜索。

尚学堂马士兵Oracle教程笔记

尚学堂马士兵Oracle教程笔记.txt大悲无泪，大悟无言，大笑无声。

我们手里的金钱是保持自由的一种工具。

女人在约会前，一定先去美容院；男人约会前，一定先去银行。

尚学堂马士兵Oracle教程笔记（2011-03-2809:

04:

04）转载标签：

oracle笔记分类：

Oracle

Java代码

检查Oracle安装

首先，以超级管理员的身份登录oracle

sqlplussys/bjsxtassysdba

然后，解除对scott用户的锁

alteruserscottaccountunlock;

那么这个用户名就能使用了。

（默认全局数据库名orcl）

1、selectename,sal*12fromemp;//计算年薪

2、select2*3fromdual;//计算一个比较纯的数据用dual表

3、selectsysdatefromdual;//查看当前的系统时间

4、selectename,sal*12anuual_salfromemp;//给搜索字段更改名称（双引号keepFormat别名有特殊字符，要加双引号）。

5、任何含有空值的数学表达式，最后的计算结果都是空值。

6、selectename||salfromemp;//（将sal的查询结果转化为字符串，与ename连接到一起，相当于Java中的字符串连接）

7、selectename||'afasjkj'fromemp;//字符串的连接

8、selectdistinctdeptnofromemp;//消除deptno字段重复的值

9、selectdistinctdeptno,jobfromemp;//将与这两个字段都重复的值去掉

10、select*fromempwheredeptno=10;//（条件过滤查询）

11、select*fromempwhereempno>10;//大于过滤判断

12、select*fromempwhereempno<>10//不等于过滤判断

13、select*fromempwhereename>'cba';//字符串比较，实际上比较的是每个字符的AscII值，与在Java中字符串的比较是一样的

14、selectename,salfromempwheresalbetween800and1500;//（betweenand过滤，包含8001500）

15、selectename,sal,commfromempwherecommisnull;//（选择comm字段为null的数据）

16、selectename,sal,commfromempwherecommisnotnull;//（选择comm字段不为null的数据）

17、selectename,sal,commfromempwheresalin（800,1500,2000）;//（in表范围）

18、selectename,sal,hiredatefromempwherehiredate>'02-2月-1981';//（只能按照规定的格式写）

19、selectename,salfromempwheredeptno=10orsal>1000;

20、selectename,salfromempwheredeptno=10andsal>1000;

21、selectename,sal,commfromempwheresalnotin（800,1500,2000）;//（可以对in指定的条件进行取反）

22、selectenamefromempwhereenamelike'%ALL%';//（模糊查询）

23、selectenamefromempwhereenamelike'_A%';//（取第二个字母是A的所有字段）

24、selectenamefromempwhereenamelike'%/%%';//（用转义字符/查询字段中本身就带%字段的）

25、selectenamefromempwhereenamelike'%$%%'escape'$';//（用转义字符/查询字段中本身就带%字段的）

26、select*fromdeptorderbydeptnodesc;（使用orderbydesc字段对数据进行降序排列默认为升序asc）;

27、select*fromdeptwheredeptno<>10orderbydeptnoasc;//（我们可以将过滤以后的数据再进行排序）

28、selectename,sal,deptnofromemporderbydeptnoasc,enamedesc;//（按照多个字段排序首先按照deptno升序排列,当detpno相同时，内部再按照ename的降序排列）

29、selectlower（ename）fromemp;//（函数lower（）将ename搜索出来后全部转化为小写）；

30、selectenamefromempwherelower（ename）like'_a%';//（首先将所搜索字段转化为小写，然后判断第二个字母是不是a）

31、selectsubstr（ename,2,3）fromemp;//（使用函数substr（）将搜素出来的ename字段从第二个字母开始截，一共截3个字符）

32、selectchr（65）fromdual;//（函数chr（）将数字转化为AscII中相对应的字符）

33、selectascii（'A'）fromdual;//（函数ascii（）与32中的chr（）函数是相反的将相应的字符转化为相应的Ascii编码））

34、selectround（23.232）fromdual;//（函数round（）进行四舍五入操作）

35、selectround（23.232,2）fromdual;//（四舍五入后保留的小数位数0个位-1十位）

36、selectto_char（sal,'$99,999.9999'）fromemp;//（加$符号加入千位分隔符，保留四位小数，没有的补零）

37、selectto_char（sal,'L99,999.9999'）fromemp;//（L将货币转化为本地币种此处将显示￥人民币）

38、selectto_char（sal,'L00,000.0000'）fromemp;//（补零位数不一样，可到数据库执行查看）

39、selectto_char（hiredate,'yyyy-MM-DDHH:

MI:

SS'）fromemp;//（改变日期默认的显示格式）

40、selectto_char（sysdate,'yyyy-MM-DDHH:

MI:

SS'）fromdual;//（用12小时制显示当前的系统时间）

41、selectto_char（sysdate,'yyyy-MM-DDHH24:

MI:

SS'）fromdual;//（用24小时制显示当前的系统时间）

42、selectename,hiredatefromempwherehiredate>to_date（'1981-2-2012:

24:

45','YYYY-MM-DDHH24:

MI:

SS'）;//（函数to-date查询公司在所给时间以后入职的人员）

43、selectsalfromempwheresal>to_number（'$1,250.00','$9,999.99'）;//（函数to_number（）求出这种薪水里带有特殊符号的）

44、selectename,sal*12+nvl（comm,0）fromemp;//（函数nvl（）求出员工的"年薪+提成（或奖金）问题"）

45、selectmax（sal）fromemp;//（函数max（）求出emp表中sal字段的最大值）

46、selectmin（sal）fromemp;//（函数max（）求出emp表中sal字段的最小值）

47、selectavg（sal）fromemp;//（avg（）求平均薪水）;

48、selectto_char（avg（sal）,'999999.99'）fromemp;//（将求出来的平均薪水只保留2位小数）

49、selectround（avg（sal）,2）fromemp;//（将平均薪水四舍五入到小数点后2位）

50、selectsum（sal）fromemp;//（求出每个月要支付的总薪水）

/////////////////////////组函数（共5个）：

将多个条件组合到一起最后只产生一个数据//////min（）max（）avg（）sum（）count（）/////////////////////////////

51、selectcount（*）fromemp;//求出表中一共有多少条记录

52、selectcount（*）fromempwheredeptno=10;//再要求一共有多少条记录的时候，还可以在后面跟上限定条件

53、selectcount（distinctdeptno）fromemp;//统计部门编号前提是去掉重复的值

////////////////////////聚组函数groupby（）//////////////////////////////////////

54、selectdeptno,avg（sal）fromempgroupbydeptno;//按照deptno分组，查看每个部门的平均工资

55、selectmax（sal）fromempgroupbydeptno,job;//分组的时候，还可以按照多个字段进行分组，两个字段不相同的为一组

56、selectenamefromempwheresal=（selectmax（sal）fromemp）;//求出

57、selectdeptno,max（sal）fromempgroupbydeptno;//搜素这个部门中薪水最高的的值

//////////////////////////////////////////////////having函数对于groupby函数的过滤不能用where//////////////////////////////////////

58、selectdeptno,avg（sal）fromempgroupbydeptnohavingavg（sal）>2000;（orderby）//求出每个部门的平均值，并且要>2000

59、selectavg（sal）fromempwheresal>1200groupbydeptnohavingavg（sal）>1500orderbyavg（sal）desc;//求出sal>1200的平均值按照deptno分组，平均值要>1500最后按照sal的倒序排列

60、selectename,salfromempwheresal>（selectavg（sal）fromemp）;//求那些人的薪水是在平均薪水之上的。

61、selectename,salfromempjoin（selectmax（sal）max_sal,deptnofromempgroupbydeptno）ton（emp.sal=t.max_salandemp.deptno=t.deptno）;//查询每个部门中工资最高的那个人

///////////////////////////////等值连接//////////////////////////////////////

62、selecte1.ename,e2.enamefromempe1,empe2wheree1.mgr=e2.empno;//自连接，把一张表当成两张表来用

63、selectename,dnamefromemp,dept;//92年语法两张表的连接笛卡尔积。

64、selectename,dnamefromempcrossjoindept;//99年语法两张表的连接用crossjoin

65、selectename,dnamefromemp,deptwhereemp.deptno=dept.deptno;//92年语法表连接+条件连接

66、selectename,dnamefromempjoindepton（emp.deptno=dept.deptno）;//新语法

67、selectename,dnamefromempjoindeptusing（deptno）;//与66题的写法是一样的，但是不推荐使用using：

假设条件太多

///////////////////////////////////////非等值连接///////////////////////////////////////////

68、selectename,gradefromempejoinsalgradeson（e.salbetweens.losalands.hisal）;//两张表的连接此种写法比用where更清晰

69、selectename,dname,gradefromempe

joindeptdon（e.deptno=d.deptno）

joinsalgradeson（e.salbetweens.losalands.hisal）

whereenamenotlike'_A%';//三张表的连接

70、selecte1.ename,e2.enamefromempe1joinempe2on（e1.mgr=e2.empno）;//自连接第二种写法，同62

71、selecte1.ename,e2.enamefromempe1leftjoinempe2on（e1.mgr=e2.empno）;//左外连接把左边没有满足条件的数据也取出来

72、selectename,dnamefromemperightjoindeptdon（e.deptno=d.deptno）;//右外连接

73、selectdeptno,avg_sal,gradefrom（selectdeptno,avg（sal）avg_salfromempgroupbydeptno）tjoinsalgradeson（t.avg_salbetweens.losalands.hisal）;//求每个部门平均薪水的等级

74、selectenamefromempwhereempnoin（selectmgrfromemp）;//在表中搜索那些人是经理

75、selectsalfromempwheresalnotin（selectdistincte1.salfromempe1joinempe2on（e1.sal

76、selectdeptno,max_salfrom

（selectavg（sal）max_sal,deptnofromempgroupbydeptno）

wheremax_sal=

（selectmax（max_sal）from

（selectavg（sal）max_sal,deptnofromempgroupbydeptno）

）;//求平均薪水最高的部门名称和编号。

77、selectt1.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）

）

）//求平均薪水等级最低的部门的名称哈哈确实比较麻烦

78、createviewv$_dept_avg_sal_infoas

selectdeptno,grade,avg_salfrom

（selectdeptno,avg（sal）avg_salfromempgroupbydeptno）t

joinsalgradeson（t.avg_salbetweens.losalands.hisal）;

//视图的创建，一般以v$开头，但不是固定的

79、selectt1.deptno,grade,avg_salfromv$_dept_avg_sal_infot1

joindepton（t1.deptno=dept.deptno）

wheret1.grade=

（

selectmin（grade）from

v$_dept_avg_sal_infot1

）

）//求平均薪水等级最低的部门的名称用视图，能简单一些，相当于Java中方法的封装

80、---创建视图出现权限不足时候的解决办法：

connsys/adminassysdba;

显示：

连接成功Connected

grantcreatetable,createviewtoscott;

显示:

授权成功Grantsucceeded

81、-------求比普通员工最高薪水还要高的经理人的名称-------

selectename,salfromempwhereempnoin

（selectdistinctmgrfromempwheremgrisnotnull）

andsal>

（

selectmax（sal）fromempwhereempnonotin

（selectdistinctmgrfromempwheremgrisnotnull）

）

82、---面试题：

比较效率

select*fromempwheredeptno=10andenamelike'%A%';//好，将过滤力度大的放在前面

select*fromempwhereenamelike'%A%anddeptno=10;

83、-----表的备份

createtabledept2asselect*fromdept;

84、-----插入数据

insertintodept2values（50,'game','beijing'）;

----只对某个字段插入数据

insertintodept2（deptno,dname）values（60,'game2'）;

85、-----将一个表中的数据完全插入另一个表中（表结构必须一样）

insertintodept2select*fromdept;

86、-----求前五名员工的编号和名称（使用虚字段rownum只能使用<或=要使用>必须使用子查询）

selectempno,enamefromempwhererownum<=5;

86、----求10名雇员以后的雇员名称--------

selectenamefrom（selectrownumr,enamefromemp）wherer>10;

87、----求薪水最高的前5个人的薪水和名字---------

selectename,salfrom（selectename,salfromemporderbysaldesc）whererownum<=5;

88、----求按薪水倒序排列后的第6名到第10名的员工的名字和薪水--------

selectename,salfrom

（selectename,sal,rownumrfrom

（selectename,salfromemporderbysaldesc）

）

wherer>=6andr<=10

89、----------------创建新用户---------------

1、backupscott//备份

exp//导出

2、createuser

createuserguohailongidentified（认证）byguohailongdefaulttablespaceusersquota（配额）10Monusers

grantcreatesession（给它登录到服务器的权限）,createtable,createviewtoguohailong

3、importdata

imp

90、-----------事务回退语句--------

rollback;

91、-----------事务确认语句--------

commit;//此时再执行rollback无效

92、当正常断开连接的时候例如exit,事务自动提交。

当非正常断