oracle数据库笔记剖析.docx
《oracle数据库笔记剖析.docx》由会员分享，可在线阅读，更多相关《oracle数据库笔记剖析.docx（23页珍藏版）》请在冰点文库上搜索。
oracle数据库笔记剖析
达内学员：
oracle数据库笔记
Oracle数据库
语法顺序
selectfromonwheregroupbyhavingorderby
执行顺序
fromonwheregroupbyhavingselectorderby
一、关键字语法介绍
1、fromonfrom后面接的是需要查询的表格
on后面接的是表的连接条件和过滤条件
单表查询直接跟上源表的名字
多表查询跟上的是源表的名字和两表之间的连接种类
2、wherewhere语句实现的是对查询表的结果集的筛选
where语句后面跟的是条件表达式（可以是列名、常量，比较运算符，文字值）
between..and..语句表示一个范围，是两边的闭区间范围
in运算符是表示范围是一个由离散值组成的集合
like运算符配合通配符进行相关字符性的信息查找
%表示0或多个字符_表示任意单个字符
isnull表示值是空值null的行信息
3、groupby根据groupby子句指定的表达式将要处理的数据分组
4、having根据统计结果添加条件对分组后的组进行过滤
只有符合having条件的组被保留
{where和having的区别}
where过滤的是行，having过滤的是分组
where可以跟任意列名、单行函数，having只能包含groupby的表达式和组函数
where执行在前，having执行在后
where和having都不允许用列别名
5、selectselect语句把需要显示的列的名称或者是表达式进行设定（查询的核心步骤）
通过select语句得到需要的信息进行显示
distinct（去重）跟在select后面，用于对显示的记录进行去重操作
6、orderby用于对select语句的结果集进行排序的语句
后面跟上需要依赖进行排序的列名以及asc（升序，默认）或者desc（降序）
7、insertinto…用于把记录添加到表格当中
insertintotabname（col1,col2/*不写括号默认添加行的所有列，书写可以写进去相应列的记录，其他保持空值*/）values（val1,val2）;
8、drop用于删除表格
droptabletabnamecascadeconstraintspurge;
/*cascadeconstraints用在删除表格前先中断与其他表格的外键约束关系*/
9、altertable修改表格中的列的信息
altertabletabnamemodify（colnamenull）;/*notnull列改为null列*/
altertabletabnamemodify（colnamedefault1notnull）;
/*null列改为notnull列*/
altertabletabnameadd（建表时列定义方法）;/*添加列*/
altertabletabnamedrop（colname）;/*删除列*/
10、synonym同义词
createsynonymaccountfortarena.account;
/*设置account与tarena.account一致*/
二、数据类型
1、number类型number（inta,intb）表示该数值有a位有效数字，b位小数位
ex:
number（6）;第二个参数不写默认为0，表示从-999999~999999的整数
number（4,3）;4位有效数字，3位小数位，表示从-9.999~9.999的三位小数数字
number（3,-3）;3位有效数字，-3位小数位，表示（+-）1~（+-）999*103的数字
2、字符类型
a、char类型按定义的字符长度存
可以不定义长度，缺省为1字节，最大长度2000字节
b、varchar2类型按字符串的实际长度存
必须定义长度，最大值为4000字节
/*列的取值是定长，定义为char类型；列的取值长度不固定，定义为varchar2类型*/
3、日期函数
缺省日期格式为DD-MON-RR
altersessionsetnls_date_format=“yyyy-mm-ddhh24:
mi:
ss”;
/*调整系统date函数缺省格式函数*/
三、比较和逻辑运算符
比较运算符:
=,>,>=,<,<=
SQL比较运算符:
betweenand,in,like,isnull
逻辑运算符:
and,or,not
否定形式:
比较运算符:
<>,!
=,^=
SQL比较运算符:
notbetweenand,notin,notlike,isnotnull
四、函数
1、字符函数
lower把字符中的字母降为小写
upper把字符中的字母升为大写
initcap把字符中的首字符转为大写
concat拼接函数的内容
substr求子串的函数
length字符串的长度
lpad右对齐函数，位数不够的，左边补指定字符
lpad（'a',5,'b'）在a前方补4个b到5位
rpad左对齐函数，位数不够的，右边补指定字符
trim去掉字符串两边的空格和制表符
2、数值函数
round（num,x）;对传进的num数值进行四舍五入，保留x位小数
trunc（num,x）;对传进的num数值进行去尾操作，保留x位小数
3、日期函数
addmonths（Datedate,numbera）一个日期加a各月
monthsbetween两个日期之间相差几个月
nextday下一天
lastday前一天
4、转换函数
todate（str）;把字符串转为时间函数
tochar（Datedate,Stringreg）;根据reg传入的格式把date转换为字符串
tonumber（str）;把数字的字符串转为number数据类型
5、一般函数
nvl（col1,Stringstr）;遍历col1列所有元素，有空值的会置换为str字符串
五、SQL语句中的分支
1、case...then...对where语句进行优先级筛选，从上到下，有一个满足便马上退出case
selectbase_duration,base_cost,
casewhenbase_duration=20thenunit_cost+0.05
whenbase_duration=40thenunit_cost+0.03
else
unit_cost
end
new_unit_costfromcost;
当base_duration找到符合的值便马上套用值退出；
如果不添加else语句，不符合case中的base_duration的值此时会输出null
2、decode用法与case...when接近
SELECTa2.real_namecunstomer,
DECODE（a1.real_name,a2.real_name,'Norecommender',a1.real_name）recommender
FROMaccounta1JOINaccounta2
ONa1.id=NVL（a2.recommender_id,a2.id）;
后面的参数分别是:
需要检查的列名，条件1，执行语句1，条件2，执行语句2，...不符合所有条件时返回的执行语句
语句执行的方法是:
找到当前表格的列，检查列中的每一个值是否符合各项的条件，一有符合的条件，马上跳转到执行语句上并执行后退出decode;若列中的值没有符合条件，则会执行”不符合所有条件时返回的执行语句”后结束decode。
六、子查询
子查询的概念是在一个查询里面包含另外一个select语句（一般是where条件语句中）
1、非关联子查询
非关联子查询，先执行子查询，子查询的返回结果作为主查询的条件，再执行主查询。
子查询只执行一遍，子查询结果若返回多值，会自动去重。
2、关联子查询
关联子查询，采用循环的方式，查询先从外部查询，获得一条记录后，并将其传入内部查询，内部查询从其结果中把值传回外部查询，若符合条件，就放入结果集，否则放弃。
重复执行以上步骤。
3、in函数和exist函数
in函数
多用在非关联子查询中，后面跟值或者是表达式
exist函数多用在关联子查询中
采用的是循环（loop）方式，判断outer表中是否存在记录只要在inner表中找到一条匹配的记录即可
in和extst的比较
exist使用循环方式，由outer表的记录决定循环的次数，所以外表的记录数要少
in先执行子查询，返回结果去重后再执行主查询，子查询结果越少越适合用in函数
/*哪些客户申请远程登录服务*/
/*关联子查询*/SELECTreal_name,idFROMaccounto
WHEREEXISTS
（SELECT1FROMserviceiWHEREi.account_id=o.id）;
/*非关联子查询*/SELECTreal_nameFROMaccount
WHEREidIN（SELECTaccount_idFROMservice）;
七、多表连接和多表查询
1、交叉连接tabname1crossjointabname2
交叉连接得到的是表1和表2的笛卡尔积（两个表里的元素一一对应）
2、内连接tabname1jointabname2ontabname.c1=tabname2.c2and
内连接所达到的效果是两表内相关的信息保留，不会显示没有交叉的信息
多个条件的情况下，可以加上and继续添加
执行顺序:
先根据on后面的过滤条件进行过滤，筛选出符合条件的行，
再根据on的连接条件将两个表进行连接。
{自连接}指的是驱动表和匹配表都是同一张表，方法同内连接一样，给表起不同表别名
/*哪些os帐号的开通时间比同一台机器上os帐号的平均开通时间长*/
/*关联子查询方法*/SELECTunix_host,os_username,
ROUND（sysdate-create_date）days
FROMserviceo
WHEREROUND（sysdate-create_date）>
（SELECTROUND（AVG（sysdate-create_date））
FROMservicei
WHEREi.unix_host=o.unix_host）;
/*多表查询方法*/SELECTs.unix_host,s.os_username,
ROUND（sysdate-create_date）day,d.days
FROMservices
JOIN（SELECTunix_host,ROUND（AVG（sysdate-create_date））days
FROMservice
GROUPBYunix_host）d
ONs.unix_host=d.unix_host
ANDROUND（sysdate-s.create_date）>d.days;
3、外连接
（1）leftjoin驱动表是左边的表，匹配表是右边的表
（2）rightjoin驱动表是右边的表，匹配表是左边的表
（3）fulljoin
执行顺序:
先根据on和and条件对要连接的表进行过滤，将过滤后的结果集进行外连接操作（joinon），再对外连接的结果集用where子句进行过滤，最后用select语句生成最终结果集。
/*列出客户姓名以及他的推荐人*/
selectt2.real_namecunstomer,nvl（t1.real_name,'Norecommender'）recommender
fromaccountt1rightjoinaccountt2
ont1.id=t2.recommender_id;
4、内连接和外连接的区别
简单来说，内连接的结果集只显示两张关联表中关联值的交集记录，驱动表和匹配表交换对结果集影响不大；外连接的结果集与驱动表和匹配表的设置有密切关系，驱动表会显示其所有的记录，没有跟匹配表匹配的也会显示出来。
{表连接，子查询共同解决问题}
1.匹配问题（结果集出自一张表）inexist、表连接（innerjoin）
匹配问题（结果集出自多张表）表连接（innerjoin）
2.不匹配问题（结果集出自一张表）notinnotexist表连接（outerjoin+where...isnull）
3.匹配+不匹配表连接（outerjoin）
八、集合
数据库中的集合的概念与数学上集合的概念基本一致，同样有union/unionall（并运算），intersert（交运算），minus（减运算）。
1、union/unionall
unionall实现的是集合（结果集）的并集，简单做并集，不去重
selectname,base_duration,unit_cost+0.05new_unit_costfromcost
wherebase_duration=20
unionall
selectname,base_duration,unit_cost+0.03fromcost
wherebase_duration=40
unionall
selectname,base_duration,unit_costfromcost
wherebase_durationnotin（20,40）
orbase_durationisnull
/*这里要注意，要加上空值的保留，否则会忽略了计时和包月两种套餐*/;
union实现的是结果的并集，得到的结果集会去掉重复的记录
2、intersect实现的是对结果集取交集的运算
/*sun280和sun-server上的远程登录业务使用了哪些相同的资费标准*/
selectname,idfromcost
whereidin
（/*加上selectfromcostwhereidin（）语句用以获取相应套餐的名字*/
selectcost_idfromservicesjoinhosth
ons.unix_host=h.id
andh.name='sun280'/*利用on的过滤条件把相应的名字筛选出来*/
intersect
selectcost_idfromservicesjoinhosth
ons.unix_host=h.id
andh.name='sun-server'）;
3、minus实现的是集合A减去A和B的交集，结果集是A中与B不一样的记录
selectname,id,locationfromhost
minus
selecth.name,s.unix_host,h.locationfromservicesjoinhosth
ons.unix_host=h.id;
九、排名分页
rownum是一个伪列，对查询返回的行编号即行好，由1开始一次递增
=>oracle的rownum数值是在获取每行之后才赋予的，rownum是不能作为表的一列存在，无法通过rownum=2得到第二行的数据
要利用小于等于伪列某个值之后再利用where确定范围
/*最晚开通netCTOSS系统的第四到第六名客户*/
selectrn,real_name,create_date
from（
selectrownumrn,real_name,create_date
from（
selectreal_name,create_date
fromaccount
orderbycreate_datedesc）
whererownum<=6）
wherernbetween4and6;
/*经典例题，这里的做法是先通过对account里的记录按照开通时间的早晚降序排列，
再引入rownum进行编号（如果在排序前引入，则会依照读取account记录的顺序并添上rownum，再做降序，此时的结果并不能达到预想的效果），并通过whererownum<=a抽出前a个记录后在嵌套多一个select语句把处在靠后位置的第四到第六的记录显示*/
十、约束
1、约束的类型
a、根据约束的性质分类
主键约束（primarykey,pk）特性是非空且唯一
唯一约束（uniquekey,uk）特性是唯一
检查约束（checkkey,ck）特性是填入的记录的范围
非空约束（notnull,nn）特性是非空
外键约束（foreignkey,fk）特性是表与表之间的联系列
b、根据约束的范围分类
列级约束:
约束直接在列中声明，只约束一列
表级约束:
约束在列以外表中声明，可以只约束一列，也可以多列同时约束
2、建立数据库表格时约束的写法
createtabletestmt（
c1number（3）constrainttestmt_c1_pkprimarykey,/*主键约束，列级约束*/
c2varchar2（10）constrainttestmt_c2_ukunique,/*唯一性约束，列级约束*/
c3charnotnull,/*非空约束，列级约束*/
c4number
（1）constrainttestmt_c4_ckcheck（c4in（0,1,2,3））,/*检查约束，列级约束*/
c5number（4）constrainttestmt_c5_fkreferencestarena.account（id）,
/*外键约束，列级约束*/
constrainttestmt_c2_c3_ukunique（c2,c3）/*唯一性约束，表级约束*/）;
3、外键约束
a、表格的一对多关系的实现
表和表之间的关系:
外键约束1:
n
childtable（fk）->parenttable（pk/uk）
account1[idpk]
servicem[id（服务），account_id（客户）
account_idfk-->account（id）]
createtabletestchild（/*列级约束外键约束写法*/
c1number
（2）constrainttestchild_c1_pkprimarykey,
c2number（3）constrainttestchild_c2_fkreferenceswzqparent（c1））;
createtabletestchild（/*表级约束外键约束写法*/
c1number
（2）constrainttestchild_c1_pkprimarykey,
c2number（3）,
constrainttestchild_c2_fkforeignkey（c2）referencestestparent（c1））;
b、表格的多对多关系的实现
表格之间的多对多关系
m:
n通过中间表（（t1_pk列名）-->t1.pk列，（t2_pk列名）-->t2.pk列）
表达两张表（t1,t2）的关系
c、表格的一对一关系的实现
表格之间的一对一关系
把一对一信息的两列信息各定义成一张表，每张表的id列定义成pk列，其中一张表的pk列同时定义成fk列，实现一一对应关系
d、合表问题
=>合表问题的三个范式
第一范式有pk，每一列不可再分。
第二范式每个非主属性必须完全依赖pk列（多对多关系的合表）
第三范式每个非主属性不能依赖于另一个非主属性（非主属性之间不能有依赖关系）
=>表格的一对多关系进行合表--数据冗余
一对多的两张表要合表会导致数据冗余，违反了第三范式
占用空间，insert每一条服务信息，同时写account信息。
同一用户信息出现多条记录，很可能数据不一致。
=>表格的多对多关系进行合表—违反第二范式
e、外键约束定义时的两种形式
=>ondeletecascade--级联删除，删除父表的记录前，先删除子表里的相关记录
createtabletestchild1（
c1number
（2）constrainttestchild1_c1_pkprimarykey,
c2number（3）constrainttestchild1_c2_fkreferencestestparent（c1）
ondeletecascade）;
/*在级联删除的情况下，删除父表的值，子表的同一值下的行也会被自动删除*/
=>ondeletesetnull--删除父表的记录前，先将子表中外键列的相关值置空
createtabletestchild1（
c1number
（2）constrainttestchild1_c1_pkprimarykey,
c2number（3）constrainttestchild1_c2_fkreferencestestparent（c1）
ondeletesetnull
/*updatewzqchild2setc2=nullwherec1=1;*/
）;
4、检查约束
检查约束也可以在列级和表级之间实现。
/*列级*/createtabletestmt（
c1number（3）constrainttestmt_c1_pkprimarykey,
c2number（3）constrainttestmt_c2_ckcheck（c2>100）
）;
/*表级*/createtabletestmt（
c1number（3）constrainttestmt_c1_pkprimarykey,
c2number
（2）,
c3number
（2）,
constrainttestmt_c2_c3_ckcheck（（c2+c3）>100）
/*check的表达式可以是in，可以是=，也可以是算术表达式*/
）;
5、约束的暂停和恢复
在为表格增加记录的时候，有些记录在表格所有记录完全写入之前受制于表格创建时所设下的约束条件，导致无法向表格中填入记录。
在这种情况下，除了在创建表格时候不添加相应的约束，等到记录全部填入后再写，或者可以选择暂时暂停约束的条件，待记录录入完成后恢复。
具体的操作如下:
altertabletestmtdisableconstrainttestmt_c5_fk;
/*关键字disable可以暂时暂停命名为testmt_c5_fk的constraint（约束
升级会员 