数据库概论chap02操纵功能

1、第2章 SQL操纵功能SQL数据操纵功能SQL数据查询功能SQL数据查询基本结构select子句from子句where子句重复元组的处理更名运算元组显示顺序字符串操作SQL数据查询功能全文检索关系的连接分组和聚集函数空值派生关系嵌套子查询集合操作递归查询SQL数据操纵功能插入删除更新SQL数据查询基本结构基本结构selectA1 , A2 , , Anfrom r1 , r2 , , rmwhere P A1 , A2 , , An(p(r1 r2 rm)锱铢必较：SQL返回结果是多集SQL数据查询基本结构select R.Afrom RwhereR.B 10select R.Afrom R,

2、 SwhereR.B =a1 and A=a2 ?not ( A between a1 and a2) Aa2优化小窍门：用between合并两个谓词比较重复元组的处理语法约束 SQL缺省为保留重复元组，也可用关键字all显式指明。若要去掉重复元组，可用关键字distinct指明示例 找出所有选修课程的学生select distinct S#from SCA(R) = (select A from R)?优化小窍门：只在必要时使用distinct重复元组的处理查询一：查询二：查询三：selectdistinct R.A, S.AfromR, S whereR.B = S.Cselectdist

3、inct R.AfromR, S whereR.B = S.Cselectdistinct R.AfromR, S whereR.B = S.A两个表R(A, B), S(A, C)，其中A是这两个表的主码,哪些查询中的distinct可以去掉？元组显示顺序命令order by 列名 asc | desc示例按年龄升序列出学生信息，相同年龄学生按姓名降序排列。 select * from S order by AGE asc，SNAME desc元组显示顺序示例：对教工按缴纳所得税的多少排序selectfname, sal * 0.2fromfacultyorder by2示例：按年龄顺序输出

4、学生姓名selectsnamefromstudentorder byage更名运算格式old_name as new_name为关系和属性重新命名，可出现在select和from子句中注：as可选更名运算属性更名 例：给出所有学生的姓名、性别、出生日期，并按出生日期升序排列 selectSNAME 姓名, SEX 性别,2013 - AGE 出生日期from Sorder by出生日期（或者order by3)更名运算关系更名找出比s1学生选修c1课程成绩高的学生号 select S2.S#from SC as S1，SC as S2 where S1.S# = s1and S1.C# = c

5、1and S2.C# = c1 and S1.GRADE P2.SAL 字符串操作命令格式列名 not like 字符串找出满足给定匹配条件的字符串匹配规则% ：匹配零个或多个字符：匹配任意单个字符 ：任何在指定范围内的字符a-f，abcdef：任何不在指定范围内的字符 a-f， abcdef字符串操作Escape定义转义字符，以去掉特殊字符的特定含义，使其被作为普通字符看待如escape ，定义 作为转义字符，则可用%去匹配%，用去匹配 %a_bx select * from tt where c1 like x%xx escape x思考：用什么去匹配 ？字符串操作示例列出姓名以“张”打头

6、的教师的所有信息 select * from PROF where PNAME like 张% 列出名称中含有3个以上字符，且倒数第三个是d，倒数第二个是_的课程select * from Cwhere CNAME LIKE %_d _ _ escape 字符串操作在CNAME上建有索引CNAME_idx，观察下面查询的执行计划，看谁用到了该索引。A.select * from C where CNAME like %dB.select * from C where CNAME like d%小巫见大巫Like全文索引 正则表达式正则表达式正则表达式（Regular Expression）记录

7、文本规则的代码一个用来描述或者匹配一系列符合某个句法规则的字符串的单个字符串简单示例zo* 匹配“z”和“zoo”zo+与“zo”和“zoo”匹配，但与“z”不匹配o2与“fol” ,” foool” 不匹配，但与“fool” 匹配o2,不匹配”fol” ，匹配”fool”,”foooooool”z|food 匹配”z”或”food”(z|f)ood 匹配“zood”或”food”常见正则表达式用户名：/a-z0-9_-3,16$/密码：/a-z0-9_-6,18$/电子邮箱：/(a-z0-9_.-+)(da-z.-+).(a-z.2,6)$/IP 地址：/(?:(?:250-5|20-40-

8、9|01?0-90-9?).)3(?:250-5|20-40-9|01?0-90-9?)$/HTML 标签：/(a-z+)(.*)|s+/)$/常见正则表达式create table IP_Address( ip char(15) primary key)alter table IP_Address add constraint CHK_IP_Valid check( ip like _%._%._%._%and ip not like %.%.%.%.%and ip not like %0-9.% and ip not like %0-90-90-90-9% and ip not like

9、%3-90-90-9% and ip not like %26-90-9%and ip not like %256-9%)正则表达式：Oracle函数名说明REGEXP_LIKE类似于 LIKE 运算符，但执行正则表达式匹配而不是简单的模式匹配REGEXP_INSTR在给定字符串中搜索某个正则表达式模式，并返回匹配项的位置REGEXP_REPLACE搜索某个正则表达式模式并使用替换字符串替换它REGEXP_SUBSTR在给定字符串中搜索某个正则表达式模式并返回匹配的子字符串家族的第一人在树上被吊死，最后一人则被蚂蚁吃掉正则表达式：OracleALTER TABLE students ADD C

10、ONSTRAINT stud_ssn_ck CHECK (REGEXP_LIKE(ssn, (:digit:3-:digit:2-:digit:4|:digit:9)$) REGEXP_SUBSTR(cust_email, +)REGEXP_REPLACE (catalog_url, http:/(/+).*, 1)REGEXP_REPLACE(Ellen Hildi Smith,(.*) (.*) (.*), 3, 1 2) 标准SQL：similar to全文检索dialogueAre you kidding?No, Im serious.你是凯丁吗？不，我是史瑞斯。create inde

11、x idx1 on film(dialogus)select * from film where dialogue like %serious%全文检索正文倒排索引全文检索：创建create fulltext catalog catalog_namecreate fulltext index on table_name (column_name key index index_nameon catalog_namekey index指定table_name上唯一键索引的名称，最好是聚簇索引全文检索：查询Contains(属性列|*，查找条件)FREETEXT(属性列|* ，查找文本)使用FRE

12、ETEXT时，全文查询引擎内部将查找文本拆分为若干个搜索词，并赋予每个词以不同的加权，然后查找匹配全文检索示例：全文索引的创建及使用演示doc(doc_id, title, author, abstract, content)create unique clustered index doc_idx on doc(doc_id)create fulltext catalog doc_fulltext_ catalogcreate fulltext index on doc (title, author, abstract, content) key index doc_idxon doc_fu

13、lltext_catalog全文检索select*fromdocwherecontains (*, database and dataspace)select*fromdocwherecontains (author, Jim Gray and not Jeff Ullman)全文检索select*fromdocwherecontains (title, abstract), graph mining)select*fromdocwherefreetext (content, Adaptive Query Processing)数据库查询和信息检索的区别关系的连接基本分类连接成分包括两个输入关

14、系、连接条件、连接类型连接条件决定两个关系中哪些元组相互匹配，以及连接结果中出现哪些属性连接类型决定如何处理与连接条件不匹配的元组关系的连接连接类型连接条件inner joinleft outer joinright outer joinfull outer joinon (R cross join S) as T两个关系的笛卡儿积关系的连接RSABBC122323253436R inner join S on R.B = S.BAR.BS.BC122312252336R left outer join S on R.B = S.BAR.BS.BC12231225233634nullnullR

15、 full outer join S on R.B S.BAR.BS.BC123623Nullnull34Nullnullnullnull23nullnull25关系的连接列出老师的教工号、姓名、工资、所教课程号select P#，PNAME，SAL，C#from (PROF left outer join PC on PROF.P#=PC.P#)selectP#，PNAME，SAL，C#fromPROF, PCwherePROF.P# = PC.P#unionselectP#，PNAME，SAL，nullfromPROFwhereP# not in (select P# from PC)空值

16、No cat has 12 tailsA cat has one more tail than no catTherefore, a cat has 13 tailsC.J Date：null是标识，不是值 。包含null违反了关系定义Codd提出了两类nullA-Mark null：未知的，墨镜人眼睛的颜色T-Mark null：不适用的，汽车眼睛的颜色空值SELECT S.SNO , P.PNOFROM S , PWHERE S.CITY P.CITYOR P.CITY Paris SNOCITYS1LondonPNOCITYP1Get SNO-PNO pairs where either

17、 the supplier and part cities are different or the part city isnt Paris (or both)Disjunctive databaseQ(Sno, Pno) Suppliers(Sno,City1), Parts(Pno,City2), City1 City2Q(Sno, Pno) Suppliers(Sno,City1), Parts(Pno,City2),City2 paris空值ANDTRUEFALSEUNKNOWNTRUETRUEFALSEUNKNOWNFALSEFALSEFALSEFALSEUNKNOWNUNKNOW

18、NFALSEUNKNOWNORTRUEFALSEUNKNOWNTRUETRUETRUETRUEFALSETRUEFALSEUNKNOWNUNKNOWNTRUEUNKNOWNUNKNOWN空值空值测试is not null测试指定列的值是否为空值注意事项除is not null之外，空值不满足任何查找条件如果null参与算术运算，则该算术表达式的值为null如果null参与比较运算，则结果可视为false。在SQL-92中可看成unknown表中存在两行(1, 2, null), (1, 2, null)，select distinct * ?空值示例找出成绩值为空的学生号 select S#

19、from SC where GRADE is null不可写为where GRADE = null 空值isnullisnull(check_expression, replacement_value)如果check_expression值为空,则返回replacement_value, 否则返回check_expressionselect S#, C#, isnull ( GRADE, 0 )from SC空值coalesce (expression1, expression2, )，返回第一个不为null的expressionselects#, c#, coalesce(grade, 0)

20、fromscnullif (expression1, expression2)如果两个表达式相等，则返回空值，否则返回第一个表达式select nullif(1, 2), nullif(1, 1)空值缺省情况下空值是最后输出的。当指定order by时，降序情况下首先输出空值，升序情况下最后输出空值示例：首先由小到大输出非空sal，然后是空值salselect fname, salfrom facultyorder by 2首先输出空值sal，然后由大到小输出非空salselect fname, salfrom facultyorder by 2 desc空值首先输出空值sal，然后由小到大输

21、出非空sal。select fname, salfrom (selectfname, sal, case when sal is null then 0 else 1 as is_null fromfaculty) temp_facultyorder by is_null, salfnamesalis_nullbob10001tomnull0cat12001聚集函数聚集函数将一列中所有的值聚集为单个值平均值：avg最小值：min最大值：max总和：sum记数：count火眼金睛之一select S#fromSCwhereGRADE = max( GRADE )select S#fromSCwh

22、ereGRADE = (select max( GRADE )from SC)火眼金睛之一count（*）VS count（列名）S#C#Gs1c180s1c290s1c395s2c185s2c2nulls3c2null4select count(G)from SC6select count(*)from SC分组分组命令group by 列名 having 条件表达式 group by将表中的元组按指定列上值相等的原则分组，然后在每一分组上使用聚集函数，得到单一值having则对分组进行选择，只将聚集函数作用到满足条件的分组上分组S#C#Gs1c184s1c290s1c396s2c180s2

23、c290s3c296s3c388S#C#Gs1c184s1c290s1c396s2c180s2c290s3c296s3c388列出每个学生的平均成绩列出每门课程的平均成绩group by S#group by C#928590929290分组和聚集函数在关系的子集上运用聚集函数，得到一个新的关系火眼金睛之二R(A, B, C) select Afrom Rgroup by Bselect A, Bfrom Rgroup by Aselect A, Cfrom Rgroup by A, Bselect Afrom Rgroup by A, Cselect Afrom Rgroup by Asel

24、ect *from Rgroup by A, B纲举目张：目标列必须是分组属性分组和聚集函数示例列出每个学生的最高、最低、平均成绩select S#，max(GRADE)，min(GRADE), avg(GRADE) from SC group by S#白马非马 select S#，avg(GRADE) from SC group by S# having min(GRADE) = 60select S#，avg(GRADE) from SCwhere GRADE =60 group by S#物莫非指 而指非指天下无指 物不可谓天下无物 谁径谓指分组和聚集函数列出每一年龄组中男学生（超过5

25、0人）的人数select AGE，count(S#)from Swhere SEX = Mgroup by AGEhaving count(*) 50从group by到cube所有可能的分析需求每种车型：Group by model每个年份：Group by year每种颜色：Group by color每个年份、每种车型：Group by model, year每个年份、每种颜色：Group by color, year每种颜色、每种车型：Group by model, colorn个属性的所有group by共有2n个cubeCUBEcubeselect Model, Year, Col

26、or, sum(Sales)from car_salesgroupby Model, Year, Color with cube总行数= (model个数+1) * (year个数+1) * (color个数+1) = (2 + 1) * (3 + 1) * (3 + 1) = 48 cubeselectTotle Sold = sum(sales),case when(grouping(model) =1) then ALL else isnull(model, ? )end model ,case when(grouping(year) =1 ) then ALL else isnull(

27、year, ? )end year ,case when(grouping(color) =1) then ALL else isnull(color, ? )end colorfrom my_cube group bymodel, theyear, color with cubegrouping是一个聚合函数,它产生一个附加的列，当用cube或rollup运算符添加行时，附加的列输出值为1，否则为0rollupgroupby Model, Year, Color with rollupALLChevyFord199019911992199019911992RedBlueWhite分组属性集s

28、elect model, year, null as color, sum( sales)from car_salesgroup bymodel, yearunion allselect model, null as year, color, sum(sales)from car_salesgroup bymodel, colorunion allselect null as model, year, color, sum(sales)from car_salesgroup byyear, colorunion allselect null as model, null as year, nu

29、ll as color, sum(sales)from car_sales分组属性集group by grouping sets( (分组属性集1), (分组属性集2), .(分组属性集n) )select model, year, color, sum( sales)from car_salesgroup bygrouping sets(model, theyear),(model, color),(theyear, color),()分组属性集借助grouping_id()函数，可以标示每一行到底和哪个group by相关联，这是通过为不同的分组分配不同的整数来做到的例如grouping_

30、id(A, B, C, D)分组(A, B, C, D)的标识为8*0+4*0+2*0+1*00分组(A, B, C)的标识为8*0+4*0+2*0+1*11分组(A, B)的标识为8*0+4*0+2*1+1*13分组(A, C)的标识为8*0+4*1+2*0+1*15分组(C)的标识为8*1+4*1+2*0+1*113分组属性集cube(x, y, z) = grouping sets ( (a, b), (a), (b), () )rollup(x, y, z) = grouping sets( (x, y, z), (x, y), (x), () )group by cube(a, b)

31、, rollup(x, y, z)= group by grouping sets( (a, b), (a), (b), () ), grouping sets( (x, y, z), (x, y), (x), () )= group by grouping sets (a, b, x, y, z), (a, b, x, y), (a, b, x), (a, b),(a, x, y, z), (a, x, y), (a, x), (a),(b, x, y, z), (b, x, y), (b, x), (b),(x, y, z), (x, y), (x), () )嵌套子查询集合成员资格集合之

32、间的比较集合基数的测试测试集合是否为空集合成员资格：in子查询in 子查询表达式 not in （子查询）判断表达式的值是否在子查询的结果中示例列出张军和王红同学的所有信息 select * from S where SNAME in (张军, 王红)集合成员资格：in子查询选修了c1号课程的学生的姓名 select SNAMEfrom S, SCwhere S.S# = SC.S#and C# = c1)select SNAMEfrom Swhere S# in(select S# from SC where C# = c1)问题1：等价否？问题2：谁更有效？集合成员资格：in子查询列出选修

33、了c1号和c2号课程的学生的学号 select S# from SC where SC.C# = c1 and S# in (select S# from SC where C# = c2)集合之间的比较：some/all子查询some/all子查询表达式 比较运算符 some （子查询） 表达式的值至少与子查询结果中的一个值相比满足比较运算符 表达式 比较运算符 all （子查询） 表达式的值与子查询结果中的所有的值相比都满足比较运算符集合之间的比较：some/all子查询056(5 some) = true05(5 some) = true05) = false(5 some05) = t

34、rue(5 = some056(5 all) = false46(5 all) = true610) = true(5= all(select avg(GRADE)from SCgroup by S#)集合之间的比较：some/all子查询select D#, X.S#from S X, SCwhereX.S# = SC.S#group byD#, X.S#having avg(GRADE) = all(select avg(GRADE)from S, SCwhereS.S# = SC.S#andS.D# = X.D#group by S.S#)找出每个系平均成绩最高的学生集合基数的测试：ex

35、ists 子查询测试集合是否为空not exists （子查询）判断子查询的结果集合中是否有任何元组存在in后的子查询与外层查询无关，每个子查询执行一次，而exists后的子查询与外层查询有关，需要执行多次，称之为相关子查询集合基数的测试：exists 子查询列出选修了c1号课程的学生姓名 select SNAME from S where exists (select*from SC where C# = c1 and S# = S.S#)S#SNAMES1AS2BS3CS#C#S1c1S2c2S3c1(s1,c1)(s2,c1)(s3,c1)集合基数的测试：exists 子查询列出选修了c

36、1号和c2号课程的学生的学号 select S# from SC SC1 where SC1.C# = c1and exists (select S#from SC where C# = c2andS# = SC1.S#)反半连接：not in, not exists列出没有选修c1号课程的学生的姓名 何时not exists优于not in?select SNAMEfrom Swhere S# not in(select S#from SCwhere C# = c1)select SNAMEfrom Swhere not exists(select S#from SCwhere S#=S.S

37、# and C# = c1)反半连接：not in, not existsselect SNAMEfrom Swhere S# in(select S#from S)except(select S#from SC)select SNAMEfrom (S left outer join SC on S.S# = SC.S#)where C# is null子查询中的属性解析匹配select snofrom MySwhere sno not in ( select sno from SC where C# = c01 )select snofrom MySwhere not exits ( sel

38、ect * from SC where C# = c01 )select snofrom MySwhere sno not in ( select MyS.sno from SC where C# = c01 )S#C#s1c1s2c2snos1SCMyS子查询与nullselect * from t1 where not exists (select * from t2 where a =b )a12bnullt1t2select * from t1 where a not in (select * from t2 )返回1, 2返回空集北京无处不飞沙除法在SQL中的表达You cannot

39、 be too careful三年来，我独自一人，无时无刻思念着你Negation Plus Negation Means AffirmationNothing is nothing at allThere is no success without hardships否定之否定 vs 双重否定You cannot make egg rolls with out breaking eggs满城尽带黄金甲列出选修了全部课程的学生姓名selectSNAMEfrom S S1where not exists(select C#from C C1where not exists(select*from

40、 SCwhere C# = C1.C#andS# = S1.S# )学生s1选修了所有课程不存在 (任何一门课程c1 (所求学生s1 没有选C1) )用having count(*)=select count(*) from C实现除法列出至少选修了s1号学生选修的所有课程的学生名select SNAMEfrom S S2where not exists(selectC#from C C1where exists(select *from SC whereC# = C1.C# andS# = s1)and not exists(select *from SC where C# = C1.C#a

41、nd S# = S2.S#)s2选修了S1所修的所有课程不存在 (任何一门课程c1 (S1选修了C1) 但 (S2没有选修C1) )派生关系命令（子查询） as 关系名（列名，列名，） SQL-92中，允许在from子句中使用子查询表达式，这时可将该子查询的结果命名为一个临时关系加以引用派生关系 Vs 视图？派生关系找出平均成绩及格的学生(不用having子句) 先求每个学生的平均成绩，再从中找出及格的学生 select S#, AVG_GRADEfrom (selectS# , avg（GRADE）from SCgroup byS#) as result(S# ， AVG_GRADE )wh

42、ereAVG_GRADE = 60临时视图：DB2with max-GRADE(value) as select max (GRADE) from SCselectS#from SC, max-GRADEwhereSC.GRADE = max-GRADE.value公用表表达式CTE：SQL Server with S-total (S#, value) as selectS#, sum (GRADE) from SC group by S# S-total-avg(value) as select avg (value) from S-total select S# from S-total

43、, S-total-avg where S-total.value = S-total-avg.value集合操作命令集合并：union (all)集合交：intersect (all)集合差： except (all)集合操作缺省去除重复元组intersect的优先级高于其他集合操作的优先级集合操作S1=1, 1, 2, 2, 2, S2=2, 2, 4, S3=2, 4, 4S1 intersect S2 = 2, S1 intersect all S2 = 2, 2；S1 except S2 = 1, S1 except all S2 = 1, 1, 2, S2 except S1 =

44、S2 except all S1 = 4。S1 except all S2 intersect all S3 = 1, 1, 2, 2(S1 except all S2) intersect all S3 = 2集合操作示例求工资大于1000或者年龄大于60的教工(select P#from PROFwhere SAL 1000)union all (select P#from PROFwhere AGE 60)(select P#from PROFwhere SAL 1000 orAGE 60问题1：等价否？问题2：谁更有效？用集合运算实现除法select s#fromS S1where (

45、select c# from Cexceptselect c#from SCwhere S1.s# = SC.s#) is null集合操作R except SselectAfrom(select1 as flag, AfromRunion allselect0, AfromS) RSgroup byAhavingcount(distinct flag) = 1 andmax(flag) = 1(1，a1)(1，a1)(0，a1)集合操作R except all SselectAfrom(selectA, max(Rcnt) max(Scnt) as Cntfrom(select 1 as f

46、lag, A, count(*) Rcnt, 0from Rgroup by Aunion allselect 0, A, 0, count(*) Scntfrom Sgroup by A) RS_1group by A) RS_2join SeqNums on s_number 90select S# , avg(GRADE)into EXCELLENT ( S#, GRADE)from SCgroup by (S#)having avg(GRADE) 90插入操作复制一个数据文件至数据库表中 bulk insert 表名 from 数据文件with(batchsize = 指定批处理中的行

47、数,check_constraints,datafiletype = 数据文件类型,fieldterminator = 字段终止符, maxerrors = 所容忍的最大错误数目, rowterminator = 行终止符)删除操作命令delete from 表名 where 条件表达式从表中删除符合条件的元组，如果没有where语句，则删除所有元组示例清除所有选课记录delete from SC删除操作删除王明老师所有的任课记录delete from PCwhere P# in (select P# from PROF where PNAME = “王明”) 删除操作删除低于平均工资的老师记

48、录delete from PROFwhere SAL (select avg(SAL) from PROF) 思考：是先找到所有符合条件的元组，一并删除，还是找到一个删除一个？删除操作truncate table删除表中的所有行，而不记录单个行删除操作truncate table在功能上与不带where子句的 delete语句相同。但truncate table比delete 速度快，且使用的系统和事务日志资源少identity计数器重置为种子值 更新操作命令update 表名 set 列名 = 表达式 | 子查询 列名 = ，表达式 | 子查询where 条件表达式指定对哪些列进行更新，以及

49、更新后的值是什么示例老师工资上调5%update PROFset SAL = SAL * 1.05更新操作将D01系系主任的工资改为该系的平均工资update PROFset SAL =(select avg(SAL)from PROFwhere D# = D01)where P# = (select DEANfrom DEPTwhere D# = D01)更新操作当C1课程的成绩小于该课程的平均成绩时，将该成绩提高5%update SCset GRADE = GRADE * 1.05 where C# = C1and GRADE 2000update PROFset SAL = SAL * 0.95where SAL 2000 then SAL * 0.9when SAL = 2000 then SAL * 0.95墨菲定律：执行顺序是，还是，？1-(1-p)noutp