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

查询指定列查询指定列例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 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 刘晨 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; 结果： 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) 比较大小比较大小在WHERE子句的中使用比较运算符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岁之间（包括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 Student WHERE Sdept NOT IN ( IS,MA,CS );(4) 字符串匹配字符串匹配n NOT LIKE ESCAPE ：指定匹配模板：指定匹配模板 匹配模板：固定字符串或含匹配模板：固定字符串或含通配符通配符的字符串的字符串 当匹配模板为固定字符串时，当匹配模板为固定字符串时， 可以用可以用 = 运算符取代运算符取代 LIKE 谓词谓词 用用 != 或或 运算符取代运算符取代 NOT LIKE 谓词谓词通配符通配符w% (百分号百分号) 代表任意长度（长度可以为代表任意长度（长度可以为0）的字符串）的字符串n例：a%b表示以a开头，以b结尾的任意长度的字符串。如acb，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) 匹配模板为含通配符的字符串例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 * 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课程的课程号和学分。 SELECT 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 某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。 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= CS 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 95007 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 ）使用集函数（续）使用集函数（续） 求最大值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 ; 例29 查询选修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对查询结果分组对查询结果分组 （续）（续）nGROUP 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 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短语作用于组，从中选择满足条件的组。 插入数据插入数据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 将学生95001的年龄改为22岁。 UPDATE Student SET Sage=22 WHERE Sno= 95001 ； 2. 修改多个元组的值修改多个元组的值例2 将所有学生的年龄增加1岁。 UPDATE Student SET Sage= Sage+1；删除数据删除数据DELETE FROM WHERE ；功能w删除指定表中满足WHERE子句条件的元组WHERE子句w指定要删除的元组w缺省表示要修改表中的所有元组1. 删除某一个元组的值删除某一个元组的值例1 删除学号为95019的学生记录。 DELETE FROM Student WHERE Sno=95019;2. 删除多个元组的值删除多个元组的值例2 删除2号课程的所有选课记录。 DELETE FROM SC WHERE Cno=2；例3 删除所有的学生选课记录。 DELETE FROM SC；注意注意:做删除操作时需谨慎做删除操作时需谨慎,避免误删避免误删!视视 图图视图的特点视图的特点n虚表虚表，是从一个或几个基本表（或视图）导出的表n只存放视图的定义，不会出现数据冗余n基表中的数据发生变化，从视图中查询出的数据也随之改变建立视图建立视图n语句格式 CREATE VIEW ( ，) AS WITH CHECK OPTION；例例建立信息系学生的视图。 CREATE VIEW IS_Student AS SELECT Sno,Sname,Sage FROM Student WHERE Sdept= IS;