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

4 结构化查询语言SQL4.5 常用函数4.5.1 数学函数 RAND()返回0到1之间的随机值。例如:Print rand()declare i Floatset i=rand()print iPRINT CONVERT(INT,RAND()*26) - 0 to 25PRINT CONVERT(INT,RAND()*26)+1 - 1 to 26规则：PRINT CONVERT(INT,RAND()*(N-M+1)+M - M to N (MN)ROUND(78.518, 2)按照指定精度四舍五入。78.520 FLOOR(-123.45)返回=参数的最小整数。-123PI()3.14159其他函数：弧度、角度DEGREES、RADIANS指数函数EXP、POWER对数函数LOG10、LOG三角函数COS、SIN、TAN、ATAN4.5.2 字符串函数LEN(abcd)计算串长SPACE(10) 返回由重复的空格组成的字符串。REPLICATE(abc,3)返回由重复的字符串组成的字符串。REVERSE(abcdef,3)字符串逆序declare s char(100)set s=REVERSE(abcdef)print sdeclare s char(100)set s=REVERSE(abcdef)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)字符串转换为实数（也可用于其他类型转换）子串操作LEFT(abcdefg,2) 取左子串RIGHT(abcdefg,2)取右子串SUBSTRING(abcdefg,2,4)取子串REPLACE(old is old!,old,new)子串替换STUFF(abcdef, 2, 3, 12345)位置替换子串/模式匹配CHARINDEX(Data,What is DataBase)子串匹配PATINDEX(%Data%,What is DataBase)模式匹配 字符串相似度SOUNDEX返回由四个字符的代码DIFFERENCE返回0,4，表示两串的相似性SOUNDEX(hello)H120SOUNDEX(what)0000SOUNDEX(water)0000DIFFERENCE(hello,what)0DIFFERENCE(what,water)4 4.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() DATEDIFF(minute,1990-1-1,GETDATE()DATEDIFF(second,1990-1-1,GETDATE()日期减法4.6 数据查询 SELECT语句有太多的命令选项（子句）：SELECT子句指定要显示的属性列 （相当于投影运算）FROM子句指定查询表WHERE子句指定查询条件（相当于选择运算）GROUP BY子句对查询结果按指定列的值分组。一般会在每组的若干记录上使用“集函数”。HAVING短语筛选出满足指定条件的组ORDER BY子句对查询结果按指定列值的排序4.6.1 单表查询1、选择表中的若干列(投影)SELECT Sname,Sno,Sdept FROM S;SELECT * FROM S;SELECT Sname as 姓名,year(GETDATE()-year(Sbirthday) as 年龄 FROM S;2、选择满足条件的元组(选择)查询1989-9-1以前出生的学生记录SELECT * FROM S WHERE Sbirthday90或SELECT S.*,Cno,SCscore FROM S INNER JOIN SCON S.Sno = SC.SnoWHERE Cno=1 AND SCscore90Cf:查询每个学生每门课的姓名、课程名、成绩Select s.sname,ame,scscore from scInner join s on sc.sno=s.snoInner join c on o=o2 一个表的连接查询自身连接：一个表与其自己进行连接。由于所有属性名都是同名属性，因此必须使用别名。（/*例如一行查阅一位同学的几门课成绩*/）自身连接SELECT a.*, b.* FROM SC as a, SC as bWHERE a.sno = b.sno 查询选修了1号课程和3号课程的学生信息SELECT a.*, b.* FROM SC as a, SC as bWHERE a.sno = b.sno AND o = 1 AND o = 33 外连接查询外连接：以一个表为主体，将另一个表中“满足”和“不满足”连接条件的元组一起输出。左外连接：查询所有学生的选修情况（含未选修情况）SELECT S.*, SCscore FROM S LEFT OUTER JOIN SC ON S.SNO=SC.SNO;左外连接：查询所有课程的成绩（含未考试科目）SELECT C.*, SCscore FROM CFULL OUTER JOIN SC ON C.Cno = SC.Cno;右外连接：SELECT S.*, SCscore FROM S RIGHT OUTER JOIN SC ON S.SNO=SC.SNO;外连接：SELECT S.*, SCscore FROM S FULL OUTER JOIN SC ON S.SNO=SC.SNO;4.6.3 嵌套查询查询块：SELECT-FROM-WHERE语句嵌套查询：将一个查询块嵌套在另一个查询块的WHERE子句中1、不相关子查询子查询的查询条件不依赖于父查询。IN运算符查询与“刘晨”在同一个系学习的学生SELECT * FROM S WHERE Sdept IN (SELECT Sdept FROM S WHERE Sname=刘晨)同上（使用单表连接的方法）SELECT S1.* FROM S as S1,S as S2WHERE S1.Sdept = S2.Sdept AND S2.Sname = 刘晨查询选择了2号课程的所有学生的姓名SELECT Sname FROM SWHERE Sno IN (SELECT Sno FROM SC WHERE Cno=2);-如果直接用连接查询？查询选修了“数学”的学生学号和姓名SELECT * FROM S WHERE Sno IN( SELECT Sno FROM SC WHERE Cno IN (SELECT Cno FROM C WHERE Cname=数学)查询有一门课程分数高于90的学生姓名SELECT Sname FROM SWHERE Sno IN (SELECT distinct Sno FROM SC WHERE SCscore90 )将信科系所有学生的成绩置100。UPDATE SC set SCscore=100 where Sno IN (SELECT Sno FROM S WHERE Sdept=信科系)删除信科系所有学生的选课记录。DELETE FROM SC WHERE Sno IN (SELECT Sno from S WHERE Sdept=信科系)ANY或ALL运算符查询有一门课程分数高于90的学生姓名SELECT Sname FROM Swhere Sno =ANY (SELECT distinct Sno FROM SC WHERE SCscore90 )查询其他系中比信科系任意一个学生年龄大的学生SELECT * FROM S WHERE Sdept信科系 AND Sbirthday ANY (SELECT Sbirthday FROM S WHERE Sdept= 信科系)同上SELECT * FROM S WHERE Sdept信科系 AND Sbirthday (SELECT MAX(Sbirthday) FROM S WHERE Sdept=信科系);查询其他系中比信科系所有学生年龄大的学生SELECT * FROM S WHERE Sdept信科系 And Sbirthday ALL (SELECT Sbirthday FROM S WHERE Sdept= 信科系);同上SELECT * FROM S WHERE Sdept信科系 AND Sbirthday = ALL(SELECT SCscore FROM SC)同上SELECT Sname FROM Swhere Sno in( SELECT Sno FROM SC where SCscore = (SELECT MAX(SCscore) FROM SC)Cf:任课教师的信息 招生的专业2、相关子查询子查询的查询条件依赖于父查询：依次取外层查询中每个元组，将其置于内层查询中，将使WHERE子句为真的所有元组放入结果表中。EXISTS运算符查询所有选修2号课程的学生。SELECT * FROM S WHERE EXISTS (SELECT * FROM SC WHERE Sno=S.Sno AND Cno=2) /*如果用连接查询，含义一样吗？ 是查询没有选修2号课程的学生。SELECT * FROM S WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno=S.Sno AND Cno=2)/*如果用连接查询，含义一样吗？ SELECT * FROM S,sc WHERE s.Sno=Sc.Sno AND Cno2 否查询与“李勇”在同一个系的学生。SELECT * FROM S as A WHERE EXISTS (SELECT * FROM S as B WHERE B.Sdept=A.Sdept AND B.Sname=李勇 )4.6.4 对查询结果分组GROUP BY子句：将查询结果表按某一列或多列值分组，值相等的为一组。（集函数常配合使用，在输出字段中并且不是集函数）HAVING子句：只有满足指定条件的组才输出。求各课程的选课人数和均分。SELECT Cno,COUNT(Sno) as 选课人数,AVG(SCscore) as 均分 FROM SCGROUP BY Cno查询选修了2门以上课程的学生学号。SELECT Sno,COUNT(sno) FROM SC GROUP BY Sno HAVING COUNT(sno)2查询有2门以上课程是90分以上的学生的学号及（90分以上的）课程数SELECT Sno, COUNT(Cno) as 优秀课程数 FROM SC WHERE SCscore=90GROUP BY Sno HAVING COUNT(Cno)=2WHERE子句：作用于表或视图，从中选择满足条件的元组。（不可以是集函数）HAVING短语：作用于组，从中选择满足条件的组。（一定在输出字段中）4.6.5 查询的并操作查询物理系的学生及年龄小于20岁的学生。SELECT * FROM S WHERE Sdept=物理系UNIONSELECT * FROM S where DATEDIFF(YEAR,Sbirthday,GETDATE()20;查询选修了001号或者003号课程的学生学号。SELECT Sno FROM SC WHERE Cno=1 UNIONSELECT Sno FROM SC WHERE Cno=3同上SELEC