第4章2结构化查询语言SQL

4-14结构化查询语言SQL4.5常用函数4.5.1数学函数RAND()返回0到1之间的随机值。例如:Printrand()declare@iFloatset@i=rand()print@iPRINTCONVERT(INT,RAND()*26)--0to25PRINTCONVERT(INT,RAND()*26)+1--1to26规则：PRINTCONVERT(INT,RAND()*(N-M+1))+M--MtoN(MN)ROUND(78.518,2)按照指定精度四舍五入。78.520FLOOR(-123.45)返回=参数的最大整数。-124CEILING(-123.45)返回=参数的最小整数。-123PI()3.14159其他函数：弧度、角度DEGREES、RADIANS指数函数EXP、POWER对数函数LOG10、LOG三角函数COS、SIN、TAN、ATAN4.5.2字符串函数LEN('abcd')计算串长SPACE(10)返回由重复的空格组成的字符串。4-2REPLICATE('abc',3)返回由重复的字符串组成的字符串。REVERSE('abcdef')字符串逆序declare@schar(100)set@s=REVERSE('abc')print@sRTRIM('abc')缩减右空格类型转换ASCII('abc')首字符转换为ASCII值。CHAR(97)ASCII值转换为字符STR(123.45,8,3)STR(float_expression[,length[,decimal]])数值转换为字符串float_expression带小数点的近似数字(float)数据类型的表达式。length总长度。它包括小数点、符号、数字以及空格。默认值为10。decimal小数点后的位数。decimal必须小于或等于16。如果decimal大于16，则会截断结果，使其保持为小数点后具有十六位。CONVERT(real,'12.34')CONVERT(decimal(9,4),'12.34')字符串转换为实数（也可用于其他类型转换）4-3子串操作LEFT('abcdefg',2)取左子串RIGHT('abcdefg',2)取右子串SUBSTRING('abcdefg',2,4)取子串REPLACE('oldisold!','old','new')子串替换STUFF('abcdef',2,3,'12345')位置替换子串/模式匹配CHARINDEX('Data','WhatisDataBase')子串匹配PATINDEX('%Data%','WhatisDataBase')模式匹配字符串相似度SOUNDEX返回有四个字符的代码DIFFERENCE返回[0,4]，表示两串的相似性SOUNDEX('hello')H120SOUNDEX('what')0000SOUNDEX('water')0000DIFFERENCE('hello','what')0DIFFERENCE('what','water')44.5.3日期函数GETDATE()返回当前时间YEAR(GETDATE())取时间中的年MONTH(GETDATE())取时间中的月DAY(GETDATE())取时间中的日DATENAME(year,GETDATE())DATENAME(month,GETDATE())DATENAME(day,GETDATE())取部分日期（字符串）DATEPART(hour,GETDATE())DATEPART(minute,GETDATE())DATEPART(second,GETDATE())取部分日期（整数）DATEADD(day,2,'2008-9-1')DATEADD(month,2,'2008-9-1')DATEADD(year,2,'2008-9-1')日期加法DATEDIFF(hour,'1990-1-1',GETDATE())日期减法4-4DATEDIFF(minute,'1990-1-1',GETDATE())DATEDIFF(second,'1990-1-1',GETDATE())4.6数据查询SELECT语句有太多的命令选项（子句）：SELECT子句指定要显示的属性列（相当于投影运算）FROM子句指定查询表WHERE子句指定查询条件（相当于选择运算）GROUPBY子句对查询结果按指定列的值分组。一般会在每组的若干记录上使用“集函数”。HAVING短语筛选出满足指定条件的组ORDERBY子句对查询结果按指定列值的排序4.6.1单表查询1、选择表中的若干列(投影)SELECTSname,Sno,SdeptFROMS;SELECT*FROMS;SELECTSnameas[姓名],year(GETDATE())-year(Sbirthday)as[年龄]FROMS;2、选择满足条件的元组(选择)查询1989-9-1以前出生的学生记录SELECT*FROMSWHERESbirthday'1989-9-1'查询没有参加考试学生的信息查询信科系大三的学生姓名SELECTSnameFROMSWHERESdept='信科系'ANDSgrade=3;查询大二、大三、大四的学生记录SELECT*FROMSWHERESgradeBETWEEN2AND4;查询大一的学生记录SELECT*FROMSWHERESgradeNOTBETWEEN2AND4;4-5查询信科系和物理系的学生记录SELECT*FROMSWHERESdeptIN('信科系','物理系');SELECT*FROMSWHERESdept='信科系'ORSdept='物理系';字符串的模式匹配：%代表任意长度的字符串_代表任意单个字符SELECT*FROMSWHERESnoLIKE'95%';查询所有的刘姓学生SELECT*FROMSWHERESnameLIKE'刘_';查询所有的刘姓（双字）学生SELECTSnameFROMSWHERESnameLIKE'刘_'查询所有的姓名中第2个字是勇的学生SELECTSname,SnoFROMSWHERESnameLIKE'_勇%'出现重复行的情形：查询所有选过课的学生学号（含重复行）SELECTSnoFROMSC;查询所有选过课的学生学号（无重复行）SELECTDISTINCTSnoFROMSC;3、对查询结果排序所有记录按成绩降序排列SELECT*FROMSCORDERBYSCscoreDESC3号课程的所有记录按成绩降序排列SELECT*FROMSCWHERECno='3'ORDERBYSCscoreDESC所有记录按学号升序、成绩降序排列SELECT*FROMSCORDERBYSnoASC,SCscoreDESC4、使用集函数计数COUNT()4-6总和SUM()平均值AVG()最大值MAX()最小值MIN()方差STDEV()查询学生总数。SELECTCOUNT(*)FROMS查询选修了课程的学生人数。(计算时取消重复值)SELECTCOUNT(DISTINCTSno)FROMSC计算2号课程的学生平均成绩。SELECTAVG(SCscore),MAX(SCscore),Min(SCscore)FROMSCWHERECno='2'计算学生成绩的均值和方差。SELECTAVG(SCscore),STDEV(SCscore)fromSC4.6.2多表（连接）查询1两表之间的连接查询广义笛卡尔积SELECTS.*,SC.*FROMS,SC等值连接：查询每个学生及其选修课程的情况。SELECTS.*,SC.*FROMS,SCWHERES.Sno=SC.Sno自然连接：查询每个学生及其选修课程的情况。SELECTS.Sno,Sname,Ssex,Sbirthday,Sdept,Sgrade,Cno,SCscoreFROMS,SCWHERES.Sno=SC.Sno或SELECTS.*,Cno,SCscoreFROMS,SCWHERES.Sno=SC.Sno或SELECTS.*,Cno,SCscoreFROMSINNERJOINSCONS.Sno=SC.Sno内连接INNERJOIN：第一表与第二表在联接列上满足条件而连接。更多内涵的连接：查询选修1号课程且成绩大于90分所有的学生4-7SELECTS.*,Cno,SCscoreFROMS,SCWHERES.Sno=SC.SnoANDCno='1'ANDSCscore90或SELECTS.*,Cno,SCscoreFROMSINNERJOINSCONS.Sno=SC.SnoWHERECno='1'ANDSCscore90Cf:查询每个学生每门课的姓名、课程名、成绩Selects.sname,c.cname,scscorefromscInnerjoinsonsc.sno=s.snoInnerjoinconsc.sno=c.cno2一个表的连接查询自身连接：一个表与其自己进行连接。由于所有属性名都是同名属性，因此必须使用别名。（/*例如一行查阅一位同学的几门课成绩*/）自身连接SELECTa.*,b.*FROMSCasa,SCasbWHEREa.sno=b.sno查询选修了1号课程和3号课程的学生信息SELECTa.*,b.*FROMSCasa,SCasbWHEREa.sno=b.snoANDa.cno='1'ANDb.cno='3'3外连接查询外连接：以一个表为主体，将另一个表中“满足”和“不满足”连接条件的元组一起输出。左外连接：查询所有学生的选修情况（含未选修情况）SELECTS.*,SCscoreFROMSLEFTOUTERJOINSCONS.SNO=SC.SNO;外连接：查询所有课程的成绩（含未考试科目）SELECTC.*,SCscoreFROMCFULLOUTERJOINSCONC.Cno=SC.Cno;右外连接：SELECTS.*,SCscoreFROMSRIGHTOUTERJOINSCONS.SNO=SC.SNO;外连接：4-8SELECTS.*,SCscoreFROMSFULLOUTERJOINSCONS.SNO=SC.SNO;4.6.3嵌套查询查询块：SELECT-FROM-WHERE语句嵌套查询：将一个查询块嵌套在另一个查询块的WHERE子句中1、不相关子查询子查询的查询条件不依赖于父查询。IN运算符查询与“刘晨”在同一个系学习的学生SELECT*FROMSWHERESdeptIN(SELECTSdeptFROMSWHERESname='刘晨')同上（使用单表连接的方法）SELECTS1.*FROMSasS1,SasS2WHERES1.Sdept=S2.SdeptANDS2.Sname='刘晨'查询选择了2号课程的所有学生的姓名SELECTSnameFROMSWHERESnoIN(SELECTSnoFROMSCWHERECno='2');--如果直接用连接查询？查询选修了“数学”的学生学号和姓名SELECT*FROMSWHERESnoIN(SELECTSnoFROMSCWHERECnoIN(SELECTCnoFROMCWHERECname='数学'))查询有一门课程分数高于90的学生姓名SELECTSnameFROMSWHERESnoIN(SELECTdistinctSnoFROMSCWHERESCscore90)将信科系所有学生的成绩置100。UPDATESCsetSCscore=100whereSnoIN(SELECTSnoFROMSWHERESdept='信科系')删除信科系所有学生的选课记录。4-9DELETEFROMSCWHERESnoIN(SELECTSnofromSWHERESdept='信科系')ANY或ALL运算符查询有一门课程分数高于90的学生姓名SELECTSnameFROMSwhereSno=ANY(SELECTdistinctSnoFROMSCWHERESCscore90)查询其他系中比信科系任意一个学生年龄大的学生SELE