Oracle笔记.docx
《Oracle笔记.docx》由会员分享，可在线阅读，更多相关《Oracle笔记.docx（71页珍藏版）》请在冰点文库上搜索。
Oracle笔记
1、登录oracle的三种方式：
【1】、oracle的图形界面
【2】、命令行[输入sqlplus]
【3】、浏览器[如图]
【4】、如果自己机器装不上，可以访问其他装好的ip，使用isqlplus也行。
2、登录时候遇到的蛋疼问题【如图】
3、登录时候可以这样写
sqlplussys/Mm901401assysdba
注：
在sqlplus/assysdba执行后,SQL>selectnamefromv$database;可以查看sid查出来ORCL大写
selectINSTANCE_NAMEfromv$instance查出来结果是orcl小写—使用后面的连接字符串正确
4、alteruserscottaccountunlock;//用户解锁
5、oracle加快开机速度，修改三个自动为手动，使用时开启那三个即可
6、sql语言四大类（查询语句dml（数据操作）ddl（数据定义））
【1】******（select）查询语句（记住背过）：
7、descemp（表名）//用来描述emp表中有些什么字段
EMPNO//雇员编号
ENAME//雇员名字
JOB//雇员职位
MGR//雇员经理人
HIREDATE//入职日期
SAL//薪水
COMM//津贴
DEPINO//部门编号
Descdept//部门
Descdalgrade//薪水等级
补充：
varchar2表示可变字符串
8、select*fromsalgrade;
9、select*fromdual;//算一行数据
10、selectsysdatefromdual;//系统时间此处如果用emp表之类的，仍然显示14行。
11、selectename,sal*12“anuual_sal”fromdual;//把sal*12的字段重命名为anuual_sal,注意，此处如果anuual和sal中间是特殊字符。
例如空格的话，必须把anuual和sal俩边加双引号。
此处的双引号有俩个作用【1】、特殊字符必须俩边有双引号；【2】、保持字段原来的格式
试一下以下俩条命令：
selectename,sal*12“anuualsal”fromdual;//显示跟原来一样
selectename,sal*12anuual_salfromdual;//变成了ANUUAL_SAL,此处的anuual和sal之间的下划线不算特殊字符。
可以不需要引号
12、selectename,sal*12+comm.fromemp;//任何含有空值的数学表达式最后的值都是空值。
13、字符串连接符selectename||salfromemp;
Selectename||‘dsjlfkjaslfjlasjflasjflakjs’fromemp;//在oracle中使用单引号括起来的一段字符表示字符串。
14、selectename||‘asdfa’ldsjflsj’fromemp;//这里的写法是错误的，在需要连接的字符串中如果有单引号，应该使用俩个单引号代替，正确如下
selectename||‘asdfa’’ldsjflsj’fromemp;
15\selectdistinctdeptnofromemp;//去掉deptno中重复的数字
16、selectdistinctdeptno,jobfromemp;//去掉每一行他俩个都重复的
17、select*fromempwheredeptno=10;
Select*fromempwhereename=‘CLARK’;//此处clark必须为大写，否则会出现显示为找不到行
***Selectename,sal,deptnofromempwheredeptno<>10;******//<>在sql语句中表示不等于
Selectename,salfromempwhereename>‘CBA’;//此处比较字母的ascii吗
Selectename,salfromempwheresalbetween800and1500;这个sql语句和下面这个selectename,salfromempwheresal>=800andsal<=1500;//等价的。
And可以用来连接俩个条件。
18、selectename,sal,commfromempwherecomm.isnull;//找空值
19、selectename,sal,comm.fromenpwheresalin（800,1500,2000）;//谁的sal等于800或1500或2000。
selectename,sal,comm.fromenpwhereenamein（CLARK,KING,）;//找字符串也行
20、selectename,sal,hiredatefromempwherehiredate>’20-2月-81’;
等价于selectename,sal,hiredatefromempwherehiredate>’20-2月-1981’//日期的写的形式必须是这样子
注意：
不能省略月这个字，不能写成20-2-81
21、连接符andornot
Selectename,salfromempwheresalnotin<800,1500>;
selectename,salfromempwheresal>100ordeptno<1000;
22、selectenamefromempwhereenamelike‘%ALL%’;//%表示一个或多个字母
Selectenamefromempwhereenamelike‘_A%’;
23、正则表达式.代表一个字母*代表零个或多个字母？
代表零个或者一个字母
+代表一个或者多个字母%代表零个或多个
如果字符串中带有%号，那么使用转义字符，默认是\，也可以自定义成$如图
24、数据排序：
select*fromdeptorderbydeptno（默认是升序排列）;
也可以写成select*fromdeptorderbydeptnoasc;//生序排列
select*fromdeptorderbydeptnodesc;//降序排列
selectempno,enamefromempwheredeptno<>10orderbyempnoasc;//where和order的混合
25、如果有俩个排序，先按照前面的排列，如果前面数据相同，再按照后面的顺序排列
26、sql函数（包括单行函数和多行函数）
单行函数（26-29）
Loweruppersubstr（截子串）
Selectlower（ename）fromemp;
Selectsubstr（ename,1,3）fromemp;//从第一个开始截取三个
selectchr（65）fromdual;//吧一个数字转换成字母
selectascii（’A’）fromdual;//吧字符转换成ascii吗
selectround（23.652）fromdual;//四舍五入
selectround（23.652,2）fromdual;//四舍五入到小数点后俩位
selectround（23.652，-1）fromdual;//四舍五入到十位数，默认为0是个位
****selectto_char（sal,’$99,999.9999’）fromemp;*********//9代表一位数字，把sal转换为小数点后四位数字，十万位之前没有的形式。
selectto_char（sal,’L99,999.9999’）fromemp;\\本地货币
selectto_char（sal,’$00,000.0000’）fromemp;\\跟9一样也是表示数字，但是用0时如果该位没有数字强制变为0。
****selecthiredatefromemp;
Selectto_char（hiredate,’YYYY-MM-DDHH:
MI:
SS’）fromemp;
Selectto_char（sysdate,’YYYY-MM-DDHH:
MI:
SS’）fromemp;//系统当前时间
Selectto_char（sysdate,’YYYY-MM-DDHH24:
MI:
SS’）fromemp;//24进制
27、selectename,hiredatefromempwherehiredate>to_date（‘1981-2-2012:
34:
56’,’YYYY-MM-DDHH24:
MI:
SS’）;//将字符串转换成日期，第二个参数为了告诉怎么转换。
（这里使用系统默认格式，不转换怎么写即09-6月-81这种形式的？
？
）
28、selectsalfromempwheresal>to_number（‘$1,250.00’,’$9,999.99’）;//字符串转换数字
29、selectename,sal*12+nvl（comm.,0）fromemp;//这个函数意思是如果comm为null，用0代替，不为null就是原来的comm的值
*******多行函数（组函数）作用：
把多条记录作为输入，最后产生一个输出。
********重点
30、selectmax（sal）fromemp;selectmin（sal）fromemp;selectavg（sal）fromemp;//取出薪水的最大，最小，平均值
Selectto_char（avg（sal）,’99999.99’）fromemp;//取平均值到小数点后俩位
Selectround（avg（sal）,2）fromemp;//取平均值四舍五入小数点后俩位
Selectsum（sal）fromemp;//每个月总共的薪水
Selectcount（*）formemp;//求出这张表一共多少条记录
Selectconut（*）fromempwheredeptno=10;
Selectconut（ename）fromemp;
Selectcount（comm.）fromemp;//conut的作用是统计字段中非空数据个数
Selectcount（distinctdeptno）fromemp;//去掉deptno字段重复数据后数据的个数
******五个多行函数特别重要min、max、avg、sum、count牢牢记住*********
31、（牢牢掌握住）selectavg（sal）fromempgroupbydeptno;//按照deptno10，30，20的进行分组，算出不同分组sal的平均值。
Selectsal,avg（sal）fromempgroupbydeptno;
Selectdeptno,job,max（sal）fromempgroupbydeptno,job;//按照deptno和job都相同的分为一组。
分为了9组
Selectenamefromempwheresal=（selectmax（sal）fromemp）;//这里使用了子查询
注意：
这个语句是错误的selectename，max（sal）fromemp;（max是多行函数，与ename不匹配）
*****************************************************************
出现在select查询列表中的字段，没有出现在组函数里，必须出现在groupby函数字段里。
这就是为什么selectename,max（sal）fromempgroupbydeptno;错误
而selectdeptno,max（sal）fromempgroupbydeptno;正确的原因
**********************************************************************
求在所有人中，薪水值最高的这些人的名字以及他的薪水，编号
Selectename,deptno,salfromempwheresal=（selectmax（sal）fromemp）;
**********************************************************************************
如何求出，每个部门里面，薪水最高的这些人的名字？
？
（不严密的方法）可能想到利用19的in来求
Selectename,sal,deptnofromempwheresalin（selectmax（sal）fromempgroupbydeptno）;
（表面看正确，但是假如10这个部门再有一个3000，同样会取出来，它只要是等于2850,3000,5000都会取出来视频13讲）
正确的写法：
自己总结的几点：
1、子句中select语句中出现的字段在主句中不能再出现
（错误）
（正确）
2、括号位置不影响语句正确性
3、字据中join里面的max（xx）函数必须起别名（否者出现下面的错误）
（错误）
32selectavg（sal）,deptnofromempgroupbydeptnohavingavg（sal）>2000;//having用来限制groupby，where不能限制groupby
***************************************************************************
Sql语句的顺序：
（记住）
1Selectxxfromemp
2Wheresal>1000//数据过滤
3Groupbydeptno//数据分组
4Having//分组过滤
5Orderby//对最后结果进行排序
执行时候也是按照这个顺序进行，牢牢记住（having这一讲）
***************************************************************************
例题：
要求薪水大于一千二的雇员，按照部门编号进行分组，分组之后的平均薪水必须大于一千五，查询分组之内的平均工资，按照工资的倒序进行排列。
33、子查询：
那些人的工资位于平均薪水之上
Selectename,salfromempwheresal>=（selectavg（sal）fromemp）;
理解子查询的关键，把他当成一张表（理解：
如何求出，每个部门里面，薪水最高的这些人的名字？
？
）
例题：
求每个部门平均薪水等级（16讲）
34、自连接（自己和自己连接，同一张表，起不同的别名，当成俩张表用）
例题：
求一个人和其对应的经理人名字
35、1992,1999
（新的语法规则下，where只负责数据过滤，只写数据过滤条件，而连接条件放在joinon里）
Selectename,dnamefromemp,dept;1992
Selectename,dnamefromempcrossjoindept;1999（交叉连接）
Selectename,dnamefromemp,deptwhereemp.deptno=dept.deptno;（1992）
Selectename,dnamefromempjoindepton（emp.deptno=dept.deptno）;（1999）
一个简单的写法
Selectename,dnamefromempjoindeptusing（deptno）;//这句话意思是ename，和dname中都有一个deptno字段，并且ename的deptno等于dname的deptno。
等价于上面的写法，但是不推荐使用。
非等值连接：
Selectename,gradefromempejoinsalgradeson（e.salbetweens.losalands.hisal）;
Selectename,dname,gradefromempejoindeptdon（e.deptno=d.deptno）joinsalgradeson（e.salbetweens.losalands.hisal）whereenamenotlike‘_A%’;
经理人例题：
Selecte1.ename,e2.enamefromempe1joinempe2on（e1.mgr=e2.empno）;
想要把king显示出来
Selecte1.ename,e2.enamefromempe1leftouterjoinempe2on（e1.mgr=e2.empno）;
上面这个句子叫做左外连接，可以把左边这个表中多余的数据显示出来，即不能和右边产生链接的数据显示出来。
其中outer可以省略，效果如下：
右连接：
（outer可以省略）
全外连接：
Selectename,dnamefromempfullouterjoindepton（emp.deptno=dept.deptno）;
--求部门的平均的薪水等级
Ed命令：
--雇员中有哪些人是经理人
selectenamefromempwhereempnoin（selectdistinctmgrfromemp）;
--不准用组函数，求薪水的最高值（面试题）
自己写的语句如下：
1999年语法，自连接写法：
--求平均薪水最高的部门的部门编号
思路：
首先求各个部门的平均薪水
Selectdeptno,avg（sal）fromempgroupbydeptno;（表）
其次求出来薪水的最高值
Selectmax（avg_sal）from（selectdeptno,avg（sal）avg_salfromempgroupbydeptno）;
上面这个表当做值
最后通过第一个表，找到最高值的对应的部门
Selectdeptno,max_salfrom（selectdeptno,avg（sal）max_salfromempgroupbydeptno）wheremax_sal=（selectmax（avg_sal）from（selectdeptno,avg（sal）avg_salfromempgroupbydeptno））;效果如下图图：
也可以按照马士兵的格式如下：
组函数函数嵌套写法：
--求平均薪水最高的部门的部门名称
上面求出来薪水最高的部门编号，只要再把上面的结果中deptno当成值，当成下面的一个where条件，从dept表中找即可。
组函数嵌套写法：
--求平均薪水最低的部门的部门名称
四步：
类似上面求最大的，结果如下
组函数可以互相嵌套，但是最多嵌套俩层（21讲）实际效果见（1和2）
--求平均薪水等级最低的部门的部门名称
selectd.deptno,dname,avg_salfrom（selectdeptno,avg_sal,gradefrom
（
selectdeptno,avg（sal）avg_salfromempgroupbydeptno）tjoinsalgradeson（t.avg_salbetweens.losalands.hisal））t1joindeptdon（t1.deptno=d.deptno）
wheregrade=（selectmin（grade）from
（
selectdeptno,avg_sal,gradefrom
（
selectdeptno,avg（sal）avg_salfromempgroupbydeptno
）t
joinsalgradeson（t.avg_salbetweens.losalands.hisal）
）
）
简化的写法：
注意：
直接使用别名的方法不可以，例如
正确的方法是：
首先可以创建一个视图（就是一个虚表，实际的数据还在原来的表中）
提示scott权限不足
提升权限
Connsys/Mm901401assysdba;//进去管理员账户
Grantcreatetable,createviewtoscott;//赋权限
如图：
重新连接scott
Connscott/tiger;
创建视图
createviewv$_dept_avg_sal_infoas
selectdeptno,avg_sal,gradefrom
（
selectdeptno,avg（sal）avg_salfromempgroupbydeptno
）t
joinsalgradeson（t.avg_salbetweens.losalands.hisal）
/
效果如下：
简化的写法：
selectd.deptno,dname,avg_salfromv$_dept_avg_sal_infot1
joindeptdon（t1.deptno=d.deptno）
wheregrade=（selectmin（grade）from（selectdeptno,avg_sal,gradefrom
v$_dept_avg_sal_info））
/
视图以v$开头
视图就是一个子查询，或者说就是一张表，这张表示虚表，实际当中数据存在实际的表里面。
当成一张表用。
--求部门经理人中平均薪水最低的部门名称（思考题）
--比普通员工最高薪水还要高的经理人名称
求出普通员工最高薪水
经理人名称
--求薪水最高的前5名雇员
自
升级会员 