llinu启动oracle数据库,约束达内学习心得：oracle数据库笔记

达内学员 oracl e数据库笔记

获奖学员王梓祺

所获奖项三等奖

内容:

/*前言这份资料是结合老师笔记课堂案例TTS6.0课件以及个人的理解整理时间仓促如有纰漏敬请原谅。 */

Oracl e数据库

语法顺序selec tfrom on where group byhavin gorder by

执行顺序from onwhere group by havin gselec torder by

一、关键字语法介绍

1、 from on from后面接的是需要查询的表格on后面接的是表的连接条件和过滤条件

单表查询直接跟上源表的名字

多表查询跟上的是源表的名字和两表之间的连接种类

2、where where语句实现的是对查询表的结果集的筛选where语句后面跟的是条件表达式(可以是列名、常量 比较运算符文字值)betwe en..and..语句表示一个范围是两边的闭区间范围in运算符是表示范围是一个由离散值组成的集合like运算符配合通配符进行相关字符性的信息查找

%表示0或多个字符_表示任意单个字符is null表示值是空值null的行信息

3、 group by根据gro up by子句指定的表达式将要处理的数据分组

4、 havin g根据统计结果添加条件对分组后的组进行过滤

只有符合h aving条件的组被保留

{where和havi ng的区别}where过滤的是行 havin g过滤的是分组where可以跟任意列名、单行函数 havin g只能包含group by的表达式和组函数where执行在前 havin g执行在后where和havi ng都不允许用列别名

5、 selec tselec t语句把需要显示的列的名称或者是表达式进行设定(查询的核心步骤)通过sel ect语句得到需要的信息进行显示disti nct(去重)跟在sel ect后面用于对显示的记录进行去重操作

6、 order by用于对se lect语句的结果集进行排序的语句

后面跟上需要依赖进行排序的列名以及asc(升序默认)或者des c(降序)

7、 inser tinto„用于把记录添加到表格当中inser t into tabna me(col1,col2/*不写括号默认添加行的所有列书写可以写进去相应列的记录其他保持空值*/)value s(val1,val2);

8、 drop用于删除表格drop table tabna mecasca deconst raint spurge;

/*casca deconst raint s用在删除表格前先中断与其他表格的外键约束关系*/

9、 alter table修改表格中的列的信息alter table tabna me modif y(colna me null);/*not null列改为nul l列*/alter table tabna me modif y(colna me defau lt 1 not null);

/*null列改为not null列*/alter table tabna me add(建表时列定义方法);/*添加列*/alter table tabna me drop(colna me);/*删除列*/

10、 synon ym同义词creat esynon ymaccou ntfor taren a.accou nt;

/*设置acc ount与taren a.accou nt一致*/

二、数据类型

1、 numbe r类型numbe r(int a,int b)表示该数值有a位有效数字 b位小数位ex:numbe r(6);第二个参数不写默认为0表示从-999999~999999的整数numbe r(4,3);4位有效数字 3位小数位表示从-9.999~9.999的三位小数数字numbe r(3,-3);3位有效数字 -3位小数位表示+- 1~(+-)999*103的数字

2、字符类型a、 char类型按定义的字符长度存

可以不定义长度缺省为1字节最大长度2000字节b、 varch ar2类型按字符串的实际长度存

必须定义长度最大值为4000字节

/*列的取值是定长定义为ch ar类型列的取值长度不固定定义为va rchar 2类型*/

3、 日期函数

缺省日期格式为DD-MON-RRalter sessi on set nls_d ate_f ormat = “yyyy-mm-dd hh24:mi:ss” ;

/*调整系统date函数缺省格式函数*/

三、 比较和逻辑运算符

比较运算符:=<=

SQL比较运算符:betw e en and, in, like, is null

逻辑运算符:and,or,not

否定形式:比较运算符:<>, !=,^=

SQL比较运算符:not betwe en and,not in,not like, is not null

四、函数

1、字符函数low er把字符中的字母降为小写upper把字符中的字母升为大写initc ap把字符中的首字符转为大写concat拼接函数的内容subst r求子串的函数lengt h字符串的长度lp ad右对齐函数位数不够的左边补指定字符lp ad('a',5,'b')在a前方补4个b到5位rp ad左对齐函数位数不够的右边补指定字符tr im去掉字符串两边的空格和制表符

2、数值函数round(num,x);对传进的n um数值进行四舍五入保留x位小数trunc(num,x);对传进的n um数值进行去尾操作保留x位小数

3、 日期函数add month s(Date date,numbe r a)一个日期加a各月month sbetwe en两个日期之间相差几个月next day下一天last day前一天

4、转换函数to date(str);把字符串转为时间函数to char(Date date,Strin greg);根据reg传入的格式把date转换为字符串to numbe r(str);把数字的字符串转为n umber数据类型

5、一般函数nvl(col1,Strin gstr);遍历col 1列所有元素有空值的会置换为st r字符串

五、 SQL语句中的分支

1、 c ase. .. then.. .对wher e语句进行优先级筛选从上到下有一个满足便马上退出caseselec t base_durat ion,base_cost,case when base_durat ion=20 then unit_cost+0.05

when base_durat ion=40 then unit_cost+0.03

els eunit_cost

endnew_u nit_c ost from cost;

当base_dura tion找到符合的值便马上套用值退出

如果不添加else语句不符合ca se中的b ase_durati on的值此时会输出n ull

2、 decod e用法与ca se...when接近

SELEC Ta2.real_name cunst omer,

DECOD E(a1.real_name,a2.real_name,'No recom mende r',a1.real_name)recom mende rFROM account a1 JOIN accounta2

ON a 1.id=NVL(a2.rec om mende r_id,a2.id);

后面的参数分别是:需要检查的列名条件1 执行语句1 条件2执行语句2 ...不符合所有条件时返回的执行语句

语句执行的方法是:找到当前表格的列检查列中的每一个值是否符合各项的条件 一有符合的条件马上跳转到执行语句上并执行后退出decode;若列中的值没有符合条件则会执行” 不符合所有条件时返回的执行语句”后结束de code。

六、子查询

子查询的概念是在一个查询里面包含另外一个selec t语句(一般是where条件语句中)

1、非关联子查询

非关联子查询先执行子查询子查询的返回结果作为主查询的条件再执行主查询。子查询只执行一遍子查询结果若返回多值会自动去重。

2、关联子查询

关联子查询采用循环的方式查询先从外部查询获得一条记录后并将其传入内部查询 内部查询从其结果中把值传回外部查询若符合条件就放入结果集否则放弃。重复执行以上步骤。

3、 in函数和exist函数in函数

多用在非关联子查询中后面跟值或者是表达式exis t函数多用在关联子查询中

采用的是循环(loop)方式判断out er表中是否存在记录只要在in ner表中找到一条匹配的记录即可in和ex tst的比较exist使用循环方式 由oute r表的记录决定循环的次数所以外表的记录数要少in先执行子查询返回结果去重后再执行主查询子查询结果越少越适合用in函数/*哪些客户申请远程登录服务*/

/*关联子查询*/SELEC Treal_name,id FROM accou nt o

WHERE EXIST S

(SELEC T 1 FROM servi ce i WHERE i.accou nt_id =o.id);

/*非关联子查询*/SELEC Treal_name FROM accou nt

WHERE id IN(SELEC T accou nt_id FROM servi ce);

七、多表连接和多表查询

1、交叉连接tabna me1 cross join tabna me2

交叉连接得到的是表1和表2的笛卡尔积(两个表里的元素一一对应)

2、 内连接tabna me1 join tabna me2 on tabna me.c1=tabna me2.c2 and

内连接所达到的效果是两表内相关的信息保留不会显示没有交叉的信息

多个条件的情况下可以加上and继续添加

执行顺序:先根据on后面的过滤条件进行过滤筛选出符合条件的行

再根据on的连接条件将两个表进行连接。

{自连接}指的是驱动表和匹配表都是同一张表方法同内连接一样给表起不同表别名/*哪些os帐号的开通时间比同一台机器上os帐号的平均开通时间长*/

/*关联子查询方法*/SELEC T unix_host,os_us ernam e,

ROUND(sysda te-creat e_dat e)days

FROM servi ceo

WHERE ROUND(sysda te-creat e_dat e)>

(SELEC TROUND(AVG(sysda te-creat e_dat e))

FROM servi cei

WHERE i.unix_host=o.unix_host);

/*多表查询方法*/SELEC Ts.unix_host,s.os_us ernam e,

ROUND(sysda te-creat e_dat e)day,d.days

FROM servi ces

JOIN(SELEC Tunix_host,ROUND(AVG(sysda te-creat e_dat e))days

FROM servi ce

GROUP BY unix_host)d

ON s.unix_host=d.unix_hos t

AND ROUND(sysda te-s.creat e_dat e)>d.days;

3、外连接

(1)left join驱动表是左边的表匹配表是右边的表

(2)right join驱动表是右边的表匹配表是左边的表

(3)full join

执行顺序:先根据on和and条件对要连接的表进行过滤将过滤后的结果集进行外连接操作(join on)再对外连接的结果集用where子句进行过滤最后用se lect语句生成最终结果集。/*列出客户姓名以及他的推荐人*/selec tt2.real_name cunst omer,nvl(t1.real_name,'No recom mende r')recom mende rfrom accou nt t1 right join accou ntt2on t1.id=t2.recom mende r_id;

4、 内连接和外连接的区别

简单来说 内连接的结果集只显示两张关联表中关联值的交集记录驱动表和匹配表交换对结果集影响不大外连接的结果集与驱动表和匹配表的设置有密切关系驱动表会显示其所有的记录没有跟匹配表匹配的也会显示出来。

{表连接子查询共同解决问题}

1.匹配问题(结果集出自一张表)in exist 、表连接(inner join)

匹配问题(结果集出自多张表)表连接(inner join)

2.不匹配问题(结果集出自一张表)not in not exist表连接(outer join+where ...is null)

3.匹配+不匹配表连接(outer join)

八、集合

数据库中的集合的概念与数学上集合的概念基本一致同样有un ion/union all(并运算) intersert(交运算)m inu s (减运算)。

1、 union/union all

union all实现的是集合(结果集)的并集简单做并集不去重selec tname,base_durat ion,unit_cost+0.05 new_u nit_c ost from costwhere base_durat ion=20

union allselec tname,base_durat ion,unit_cost+0.03 fromcostwhere base_durat ion=40

union allselec tname,base_durat ion,unit_cost from costwhere base_durat ion not in(20,40)or base_durat ion is null

/*这里要注意要加上空值的保留否则会忽略了计时和包月两种套餐*/;un io n实现的是结果的并集得到的结果集会去掉重复的记录

2、 inter sect实现的是对结果集取交集的运算

/*sun280和sun-serve r上的远程登录业务使用了哪些相同的资费标准*/selec tname,id from costwhere id in

( /*加上sel ectfrom cost where id in()语句用以获取相应套餐的名字*/selec tcost_idfrom servi ces join host hon s.unix_hos t=h.id

and h.name='sun280'/*利用on的过滤条件把相应的名字筛选出来*/inter sectselec tcost_idfrom servi ces join host hon s.unix_hos t=h.id

and h.name='sun-serve r');

3、m inu s实现的是集合A减去A和B的交集结果集是A中与B不一样的记录selec tname,id,locat ion from hostm inu sselec th.name,s.unix_host,h.locat ion from servi ce s join host hon s.unix_hos t=h.id;

九、排名分页rownu m是一个伪列对查询返回的行编号即行好 由1开始一次递增

=>oracl e的row num数值是在获取每行之后才赋予的 rownu m是不能作为表的一列存在无法通过r ownum=2得到第二行的数据

要利用小于等于伪列某个值之后再利用where确定范围

/*最晚开通n etCTOSS系统的第四到第六名客户*/selec trn,real_name,creat e_dat efrom(selec trownu mrn,real_name,creat e_dat efrom(selec treal_name,creat e_dat efrom accou ntorder by creat e_dat edesc)

where rownu m<=6)where rnbetwe en4 and 6;

/*经典例题这里的做法是先通过对accou nt里的记录按照开通时间的早晚降序排列

再引入ro wnum进行编号(如果在排序前引入则会依照读取acco unt记录的顺序并添上rown um再做降序此时的结果并不能达到预想的效果)并通过wh ere rownu m<=a抽出前a个记录后在嵌套多一个select语句把处在靠后位置的第四到第六的记录显示*/

十、约束

1、约束的类型a、根据约束的性质分类

主键约束(prima ry key,pk)特性是非空且唯一

唯一约束(uniqu e key,uk)特性是唯一

检查约束(check key,ck)特性是填入的记录的范围

非空约束(not null,nn)特性是非空

外键约束(forei gn key,fk)特性是表与表之间的联系列b、根据约束的范围分类

列级约束:约束直接在列中声明只约束一列

表级约束:约束在列以外表中声明可以只约束一列也可以多列同时约束

2、建立数据库表格时约束的写法creat etable testm t(c 1 numbe r(3)const raint testm t_c1_pk prima ry key,/*主键约束列级约束*/c2 varch ar2(10)const raint testm t_c2_ukuniqu e,/*唯一性约束列级约束*/c3 char not null,/*非空约束列级约束*/c4 numbe r(1)const raint testm t_c4_ck check(c4 in(0,1,2,3)),/*检查约束列级约束*/c5 numbe r(4)const raint testm t_c5_fkrefer ences taren a.accou nt(id),

/*外键约束列级约束*/const raint testm t_c2_c3_uk uniqu e(c2,c3)/*唯一性约束表级约束*/);

3、外键约束a、表格的一对多关系的实现

表和表之间的关系:外键约束1:nchild table(fk) ->paren ttable(pk/uk)account1[idpk]servi cem[id(服务) accou nt_id(客户)account_id fk-->account(id)]creat etable testc hild(/*列级约束外键约束写法*/c 1 numbe r(2)const raint testc hild_c1_pk prima rykey,c2 numbe r(3)const raint testc hild_c2_fk refer ences wzqpa rent(c 1));creat etable testc hild(/*表级约束外键约束写法*/c 1 numbe r(2)const raint testc hild_c1_pk prima rykey,c2 numbe r(3) ,const raint testc hild_c2_fk forei gnkey(c2)refer ences testp arent (c1));b、表格的多对多关系的实现

表格之间的多对多关系m:n 通过中间表((t1_pk列名)-->t1.pk列 (t2_pk列名)-->t2.pk列)

表达两张表(t1,t2)的关系c、表格的一对一关系的实现

表格之间的一对一关系

把一对一信息的两列信息各定义成一张表每张表的i d列定义成pk列其中一张表的pk列同时定义成fk列实现一一对应关系d、合表问题

=>合表问题的三个范式

第一范式有pk每一列不可再分。

第二范式每个非主属性必须完全依赖pk列(多对多关系的合表)

第三范式每个非主属性不能依赖于另一个非主属性(非主属性之间不能有依赖关系)=>表格的一对多关系进行合表--数据冗余

一对多的两张表要合表会导致数据冗余违反了第三范式

占用空间 inser t每一条服务信息 同时写ac count信息。

同一用户信息出现多条记录很可能数据不一致。

=>表格的多对多关系进行合表—违反第二范式e、外键约束定义时的两种形式

=>on delet ecasca de--级联删除删除父表的记录前先删除子表里的相关记录creat etable testc hild1 (c 1 numbe r(2)const raint testc hild1_c1_p kprima rykey,c2 numbe r(3)const raint testc hild1_c2_f krefer ences testp arent (c1)on delet ecasca de);

/*在级联删除的情况下删除父表的值子表的同一值下的行也会被自动删除*/

=>on delet eset null--删除父表的记录前先将子表中外键列的相关值置空creat etable testc hild1 (c 1 numbe r(2)const raint testc hild1_c1_p kprima rykey,c2 numbe r(3)const raint testc hild1_c2_f krefer ences testp arent (c1)on delet eset null

/*updat ewzqch ild2 set c2=null where c1=1;*/

);

4、检查约束

检查约束也可以在列级和表级之间实现。

/*列级*/creat etable testm t(c 1 numbe r(3)const raint testm t_c1_pk prima ry key,c2 numbe r(3)const raint testm t_c2_ck check(c2>100)

);

/*表级*/creat etable testm t(c 1 numbe r(3)const raint testm t_c1_pk prima ry key,c2 numbe r(2),c 3 numbe r(2),const raint testm t_c2_c3_ck check((c2+c3)>100)

/*check的表达式可以是in可以是= 也可以是算术表达式*/

);

5、约束的暂停和恢复

在为表格增加记录的时候有些记录在表格所有记录完全写入之前受制于表格创建时所设下的约束条件导致无法向表格中填入记录。在这种情况下除了在创建表格时候不添加相应的约束等到记录全部填入后再写或者可以选择暂时暂停约束的条件待记录录入完成后恢复。具体的操作如下:alter table testm tdisab leconst raint testm t_c5_fk;

/*关键字di sable可以暂时暂停命名为t estmt_c5_f k的con strai nt(约束)*/alter table testm tenabl econst raint testm t_c5_fk;

/*关键字en able可以恢复命名为test mt_c5_fk的c onstr aint(约束) */

注意:约束的存在不仅仅是为了保持表格于表格之间的联系(forei gn key) 同时还有其他例如唯一性(uniqu e key)和主键(prima ry key)等用于限定表格中的记录以符合业务逻辑的。因此在暂停约束的时候不能够随便暂停一旦在暂停过程中输入违反约束条件的记录就不能正常恢复相应的约束导致出现工作的延误和不合理。所以要根据记录的输入和现实中的业务逻辑考虑使用约束的暂停。

6、约束的查询selec tc.const raint_name,c.const raint_type,cc.colum n_nam e posit ionfrom user_const raint sc join user_cons_colum ns ccon c.table_name =cc.table_name

and c.table_name = “ACCOU NT” ;const raint:约束名table_name :表名colum n_nam e:被约束的列posit ion:单列约束值为1联合约束时表示该列在约束定义中的位置

7、约束的操作alter table tabna me drop const raint_name;/*根据约束名删除约束*/alter table tabna me drop prima rykey;/*删除主键约束*/alter table tabna me add表级约束语法;/*添加约束*/

十一、 updat e和del ete语法

1、 updat e的语法

--用于更新表中已经存在的记录 即修改记录的某列/某些列的值updat etabna me set colna me1=value 1[,colna me2=value 2]where condi tion;

2、 delet e的语法

--删除已经存在的记录delet efrom tabna me where condi tion;

如果要删除表格里面的所有记录可以简写成delet etabna me;

{delet e和tru ncate的区别}delet efrom tabna me时间长(是否写ro llbac k/undo segme nt)trunc ate table tabna me把表所有数据删除(删除的速度比dele te from tabna me块)delet e不释放表占用的空间trunc ate释放表占用的空间