尚学堂马士兵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.sal76、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,事务自动提交。
当非正常断
升级会员 