数据库原理及应用(第四章-SQL示例)

1、查询指定列查询指定列例1 查询全体学生的学号与姓名。SELECT Sno,SnameFROM Student； 例2 查询全体学生的姓名、学号、所在系。SELECT Sname,Sno,SdeptFROM Student;查询全部列查询全部列例3 查询全体学生的详细记录。SELECT Sno,Sname,Ssex,Sage,Sdept FROM Student; 或SELECT *FROM Student; 查询经过计算的值查询经过计算的值 SELECT子句的为表达式n算术表达式n字符串常量n函数n列别名n等 查询经过计算的值查询经过计算的值例4 *查全体学生的姓名及其出生年份。select

2、Sname,2009-Sage from student; 输出结果： Sname 2009-Sage - - 李勇 1976 刘晨 1977 王名 1978 张立 1978 查询经过计算的值查询经过计算的值例5 *查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。SELECT Sname,Year of Birth: ,2009-Sage,LOWER(Sdept)FROM Student; 例题（续）例题（续）输出结果： Sname Year of Birth: 2009-Sage LOWER(Sdept) - - - - 李勇 Year of Birth: 1976 cs

3、刘晨 Year of Birth: 1977 is 王名 Year of Birth: 1978 ma 张立 Year of Birth: 1977 is 选择表中的若干元组选择表中的若干元组 n消除取值重复的行n查询满足条件的元组 1. 消除取值重复的行消除取值重复的行n在SELECT子句中使用DISTINCT短语假设SC表中有下列数据 Sno Cno Grade - - - 95001 1 92 95001 2 85 95001 3 88 95002 2 90 95002 3 80ALL 与与 DISTINCT 例6 查询选修了课程的学生学号。(1) SELECT Sno FROM SC;

4、 结果： Sno - 95001 95001 95001 95002 95002 例题（续）例题（续）(2) SELECT DISTINCT Sno FROM SC; 结果： Sno - 95001 95002 例题（续）例题（续）n注意 DISTINCT短语的作用范围是所有目标列例：查询选修课程的各种成绩错误的写法SELECT DISTINCT Cno,DISTINCT GradeFROM SC;正确的写法 SELECT DISTINCT Cno,Grade FROM SC; 查询满足条件的元组查询满足条件的元组WHERE子句常用的查询条件子句常用的查询条件(1) 比较大小比较大小在WHER

5、E子句的中使用比较运算符n=，=，=，!= 或 ，!，!， n逻辑运算符NOT + 比较运算符例7 查询所有年龄在20岁以下的学生姓名及其年龄。 SELECT Sname,Sage FROM Student WHERE Sage = 20; (2) 确定范围确定范围n使用谓词 BETWEEN AND NOT BETWEEN AND 例8 查询年龄在2023岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。 SELECT Sname,Sdept,SageFROM StudentWHERE Sage BETWEEN 20 AND 23; 例题（续）例题（续）例9 查询年龄不在2023岁之间（包

6、括20岁和23岁）的学生姓名、系别和年龄。SELECT Sname,Sdept,SageFROM StudentWHERE Sage NOT BETWEEN 20 AND 23; (3) 确定集合确定集合使用谓词 IN , NOT IN ：用逗号分隔的一组取值例10查询信息系（IS）、数学系（MA）和计 算机科学系（CS）学生的姓名和性别。SELECT Sname,SsexFROM StudentWHERE Sdept IN ( IS ,MA ,CS );(3) 确定集合确定集合例11查询既不是信息系、数学系，也不是计算 机科学系的学生的姓名和性别。SELECT Sname,SsexFROM

7、Student WHERE Sdept NOT IN ( IS,MA,CS );(4) 字符串匹配字符串匹配n NOT LIKE ESCAPE ：指定匹配模板：指定匹配模板 匹配模板：固定字符串或含匹配模板：固定字符串或含通配符通配符的字符串的字符串 当匹配模板为固定字符串时，当匹配模板为固定字符串时， 可以用可以用 = 运算符取代运算符取代 LIKE 谓词谓词 用用 != 或或 运算符取代运算符取代 NOT LIKE 谓词谓词通配符通配符w% (百分号百分号) 代表任意长度（长度可以为代表任意长度（长度可以为0）的字符串）的字符串n例：a%b表示以a开头，以b结尾的任意长度的字符串。如acb

8、，addgb，ab 等都满足该匹配串_ (下横线下横线) 代表任意单个字符代表任意单个字符n例：a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配串ESCAPE 短语：短语：n*当用户要查询的字符串本身就含有 % 或 _ 时，要使用ESCAPE 短语对通配符进行转义。例题例题1) 匹配模板为固定字符串 例12 查询学号为95001的学生的详细情况。 SELECT * FROM Student WHERE Sno LIKE 95001;等价于： SELECT * FROM Student WHERE Sno = 95001;例题（续）例题（续）2) 匹配模板为含通

9、配符的字符串例13 查询所有姓刘学生的姓名、学号和性别。 SELECT Sname,Sno,Ssex FROM Student WHERE Sname LIKE 刘%;SELECT *FROM 信息表WHERE 姓名 like %红%;SELECT * from 信息表 where 籍贯 like %山%;SELECT * from 信息表 where 民族 not like 汉族;例题（续）例题（续）匹配模板为含通配符的字符串（续）例14 查询姓欧阳且全名为三个汉字的学生的姓名。 SELECT Sname FROM Student WHERE Sname LIKE 欧阳_ ; SELECT

10、* FROM 信息表 WHERE 姓名 LIKE _阳%;例题（续）例题（续）匹配模板为含通配符的字符串（续）例15 查询名字中第2个字为阳字的学生的姓名和学号。 SELECT Sname,Sno FROM Student WHERE Sname LIKE _阳%;例题（续）例题（续）匹配模板为含通配符的字符串（续）例16 查询所有不姓刘的学生姓名。 SELECT Sname,Sno,Ssex FROM Student WHERE Sname NOT LIKE 刘%;例题（续）例题（续）3) 使用换码字符将通配符转义为普通字符 例17 * 查询DB_Design课程的课程号和学分。 SELEC

11、T Cno,Ccredit FROM Course WHERE Cname LIKE DB_Design ESCAPE ;例题（续）例题（续）使用换码字符将通配符转义为普通字符(续)例18 * 查询以DB_开头，且倒数第3个字符为 i的课程的详细情况。 SELECT *FROM Course WHERE Cname LIKE DB_%i_ _ ESCAPE ;(5) 涉及空值的查询涉及空值的查询n 使用谓词 IS NULL 或 IS NOT NULLn “IS NULL” 不能用 “= NULL” 代替例19 某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的

12、学号和相应的课程号。 SELECT Sno,Cno FROM SC WHERE Grade IS NULL;例题例题(续续) 例20 查所有有成绩的学生学号和课程号。 SELECT Sno,Cno FROM SC WHERE Grade IS NOT NULL;(6) 多重条件查询多重条件查询用逻辑运算符AND和 OR来联结多个查询条件n AND的优先级高于ORn 可以用括号改变优先级可用来实现多种其他谓词n NOT INn NOT BETWEEN AND 例题例题例21 查询计算机系年龄在20岁以下的学生姓名。 SELECT Sname FROM Student WHERE Sdept= C

13、S AND Sage=20 AND Sage=23;三、对查询结果排序三、对查询结果排序 使用ORDER BY子句n 可以按一个或多个属性列排序n 升序：ASC；降序：DESC；缺省值为升序缺省值为升序当排序列含空值时nASC：排序列为空值的元组最后显示nDESC：排序列为空值的元组最先显示 对查询结果排序（续）对查询结果排序（续） 例24 查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。 SELECT Sno,Grade FROM SC WHERE Cno= 3 ORDER BY Grade DESC; 查询结果查询结果 Sno Grade - - 95010 95024 9

14、5007 92 95003 82 95010 82 95009 75 95014 61 95002 55对查询结果排序（续）对查询结果排序（续） 例25 查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。 SELECT * FROM Student ORDER BY Sdept,Sage DESC;四、使用集函数四、使用集函数 5类主要集函数n计数COUNT（DISTINCT|ALL *）COUNT（DISTINCT|ALL ）n计算总和SUM（DISTINCT|ALL ）n 计算平均值AVG（DISTINCT|ALL ）使用集函数（续）使用集函数（续） 求最大值

15、MAX（DISTINCT|ALL ） 求最小值MIN（DISTINCT|ALL ）DISTINCT短语：在计算时要取消指定列中的重复值ALL短语：不取消重复值ALL为缺省值使用集函数使用集函数 （续）（续）例26 查询学生总人数。 SELECT COUNT(*) FROM Student; 例27 查询选修了课程的学生人数。 SELECT COUNT(DISTINCT Sno) FROM SC;注：用DISTINCT以避免重复计算学生人数使用集函数使用集函数 （续）（续）例28 计算1号课程的学生平均成绩。 SELECT AVG(Grade) FROM SC WHERE Cno= 1 ; 例2

16、9 查询选修1号课程的学生最高分数。 SELECT MAX(Grade) FROM SC WHERE Cno= 1 ; 五、对查询结果分组五、对查询结果分组 使用GROUP BY子句分组 细化集函数的作用对象n 未对查询结果分组，集函数将作用于整个查询结果n 对查询结果分组后，集函数将分别作用于每个组 使用使用GROUP BY子句分组子句分组例30 求各个课程号及相应的选课人数。 SELECT Cno,COUNT(Sno) FROM SC GROUP BY Cno; 结果 Cno COUNT(Sno) 1 22 2 34 3 44 4 33 5 48对查询结果分组对查询结果分组 （续）（续）n

17、GROUP BY子句的作用对象是查询的中间结果表n分组方法：按指定的一列或多列值分组，值相等的为一组n使用GROUP BY子句后，SELECT子句的列名列表中只能出现分组属性和集函数 使用使用HAVING短语筛选最终输出结果短语筛选最终输出结果例31 查询选修了3门以上课程的学生学号。 SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) 3; 思考题思考题:查询选修了查询选修了3门以上（包括门以上（包括3门）课程及平均分在门）课程及平均分在80分以上的学生学号分以上的学生学号及平均成绩。及平均成绩。SELECT Sno,avg(Grade) FROM

18、 SC GROUP BY Sno HAVING COUNT(*) =3 and avg(Grade)80; 例题例题例32 *查询有3门以上课程是90分以上的 学生的学号及（90分以上的）课程数 SELECT Sno, COUNT(*) FROM SC WHERE Grade=90 GROUP BY Sno HAVING COUNT(*)=3; 使用使用HAVING短语筛选最终输出结果短语筛选最终输出结果n只有满足HAVING短语指定条件的组才输出nHAVING短语与WHERE子句的区别：作用对象不同nWHERE子句作用于基表或视图，从中选择满足条件的元组。nHAVING短语作用于组，从中选择

19、满足条件的组。 插入数据插入数据n语句格式INSERTINTO (，)VALUES ( ， )n功能 将新元组插入指定表中。插入数据插入数据例1 将一个新学生记录（学号：95020；姓名：陈冬；性别：男；所在系：IS；年龄：18岁）插入到Student表中。 INSERT INTO Student(Sno,Sname,Ssex,Sdept,Sage) VALUES (95020,陈冬,男,IS,18);注意注意:若未列出属性名若未列出属性名,则数据顺序必须与表中属性顺序一则数据顺序必须与表中属性顺序一致致nINSERT INTO Studentn VALUES (95020,陈冬,男,18,IS)修改数据修改数据n语句格式 UPDATE SET =，= WHERE ；n功能修改指定表中满足WHERE子句条件的元组1. 修改某一个元组的值修改某一个元组的值例1 将学生