1、SQL 分类查询攻略建立数据库”成绩管理DBC”，完成以下数据表文件：学生DBF(学号(C/6)、姓名(C/8)、性别(C/2)、出生日期(D)、年龄(N/2)、党员否(L)、入学分数(N/3) 、院系号(C/8) 、籍贯(C/10)、备注(M) ，以学号建立了主索引，以院系建立了普通索引；课程DBF(课程号(C/2)、课程名称(C/12)、先行课(C/2)、学分(N/1)、任课教师(C/6) ，以课程号建立了主索引。成绩DBF(学号(C/6)、课程号(C/2)、成绩(N/2)，学号和课程号分别建立了普通索引；学生DBF学号 姓名 性别 出生日期 年龄 党员否 入学分数 院系 籍贯 备注201
2、001 刘芳菲 女 1989-12-03 T 512 计算机系 武汉201002 刘丹 男 1989-06-14 F 536 计算机系 黄石201003 高菲 女 1991-11-07 T 528 信息系 武汉201004 李丽 女 1990-01-23 F 496 信息系 随州201005 欧阳志飞 男 1989-06-28 T 469 数学系 孝感201006 胡艳 女 1988-02-11 F 531 计算机系 荆州201007 张阳 男 1990-03-28 F 520 数学系 武汉201008 刘俊 男 1989-07-08 F 509 计算机系 天门201009 李向阳 男 199
3、0-11-13 F 486 数学系 汉川课程DBF 成绩DBFSQL 分类查询大全第 2 页 共 7 页 一、条件查询1. 查询学生表中计算机系或信息系男生的姓名及性别。SELECT 姓名，性别 FROM 学生 WHERE (院系 =”计算机系”OR 院系=”信息系”) AND 性别=”男”2. 查询”信息系” 的学生来自哪些城区。SELECT 籍贯 FROM 学生 WHERE 院系=”信息系”3. 查询所有 90 年以前出生的女生姓名与实际年龄。SELECT 姓名，2011-YEAR( 出生日期 ) AS 实际年龄 FROM 学生 WHERE 性别=” 女” AND YEAR(出生日期)=9
4、07. 查询考试成绩有不及格的学生的学号。SELECT 学号 FROM 成绩 WHERE 成绩=20 AND 年龄21 三、集合查询学号 课程号 成绩201001 C1 85201001 C2 96201001 C3 48201002 C1 84201002 C2 95201002 C4 75201003 C1 93201003 C5 NULL201003 C3 48201004 C1 89201004 C2 42201004 C3 78201005 C1 86201005 C2 95201005 C3 NULL201006 C1 87201006 C4 77201007 C2 9420100
5、8 C2 83课程号 课程名称 先行课 学分 任课教师C1 数据库 C5 4 晓寒C2 数学 2 王江C3 信息系统 C1 4 蒋发C4 操作系统 C6 3 刘海军C5 数据结构 C7 4 赵丽C6 数据处理 2 苏小朋SQL 分类查询大全第 3 页 共 7 页13. 查询信息系、计算机系、外语系学生的姓名和性别。SELECT 姓名，性别 FROM 学生 WHERE 院系 IN (”信息系”， ”计算机系”， ”外语系”)条件等价于：WHERE 院系=”信息系”or 院系=”计算机系”or 院系=”外语系”14. 查询即不是信息系、数学系，也不是外语系的学生姓名和性别。SELECT 姓名，性别
6、 FROM 学生 WHERE 院系 NOT IN (”信息系”， ”计算机系”， ”外语系”)条件等价于：WHERE 院系!=” 信息系”and 院系!=” 计算机系”and 院系! =”外语系” 四、字符匹配查询15. 查询学号为 200701 的学生详细情况。SELECT * FROM 学生 WHERE 学号 =”200701”条件等价于：WHERE 学号 IN (”200701”) 或 WHERE 学号 LIKE “200701”16. 查询所有姓刘的学生的姓名、学号和性别。SELECT 姓名，学号，性别 FROM 学生 WHERE 姓名 LIKE “刘%”比较：SELECT 姓名，学号
7、，性别 FROM 学生 WHERE 姓名 LIKE “刘_”17. 查询名字中第 2 个字为”阳”字的学生姓名和学号。SELECT 姓名，学号 FROM 学生 WHERE 姓名 LIKE “_阳%”比较：SELECT 姓名，学号 FROM 学生 WHERE 姓名 LIKE “%阳_”18. 查询名字中包含”阳” 字的学生姓名和学号。SELECT 姓名，学号 FROM 学生 WHERE 姓名 LIKE “%阳%”19. 查询所有不姓刘的学生姓名。SELECT 姓名 FROM 学生 WHERE 姓名 NOT LIKE “刘%” 五、空值查询20. 查询缺少成绩的学生学号和相应课程号。SELECT
8、学号，课程号 FROM 成绩 WHERE 成绩 IS NULL21. 查询全部有成绩的学生学号和相应课程号。SELECT 学号，课程号 FROM 成绩 WHERE 成绩 IS NOT NULL22. 查询有成绩的学生学号及姓名。(嵌套或连接)嵌套：SELECT 学号，姓名 FROM 学生 WHERE 学号 IN (SELECT DISTINCT 学号 FROM 成绩 WHERE 成绩 IS NOT NULL)连接：SELECT 学生学号，姓名 FROM 学生，成绩WHERE (成绩 IS NOT NULL) AND (学生学号=成绩学号)23. 查询没有成绩的学生姓名。(不包括没有参加选修的学
9、生)嵌套：SELECT 学号，姓名 FROM 学生 WHERE 学号 IN (SELECT DISTINCT 学号 FROM 成绩 WHERE 成绩 IS NULL)连接：SELECT 学生学号，姓名 FROM 学生，成绩WHERE (成绩 IS NULL) AND (学生学号=成绩学号) 六、多条件查询24. 查询计算机系年龄在 20 岁以下的学生以及信息系的学生姓名。SELECT 姓名 FROM 学生WHERE (院系=”计算机系” AND 年龄=9026. 查询选修 C2 课程且成绩在 90 分以上的所有学生的姓名。嵌套：SELECT 学号，姓名 FROM 学生 WHERE 学号 IN(
10、SELECT 学号 FROM 成绩 WHERE 课程号=”C2” AND 成绩=90)连接：SELECT 学生学号，姓名 FROM 学生，成绩 WHERE 学生学号=成绩学号AND 课程号=”C2” AND 成绩=90 七、排序查询27. 查询选修了 C1 课程的学生的学号及其成绩，查询结果按分数的降序排列。SELECT 学号，成绩 FROM 成绩 WHERE 课程号=”C1” ORDER BY 成绩 DE 成绩28. 查询全体学生情况，查询结果按所在院系降序排列，同一系中的学生按学号升序排列。SELECT * FROM 学生 ORDER BY 院系 DESC，学号29. 查询选修了 C1 课
11、程的学生的姓名及其成绩，查询结果按分数的降序排列。(只能使用联接查询)SELECT 姓名，成绩 FROM 学生，成绩WHERE 课程号 =”C1” AND 学生学号=成绩学号 ORDER BY 成绩 DESC 八、函数查询30. 查询参加了选修课程的学生人数。SELECT COUNT(DIST 学号) FROM 成绩31. 查询成绩表中选修” 数学” 课程的学生人数。嵌套：SELECT COUNT(学号 ) FROM 成绩WHERE 课程号=(SELECT 课程号 FROM 课程 WHERE 课程名称=”数学”)连接：SELECT COUNT(学号 ) FROM 成绩，课程WHERE 成绩课程
12、号=课程课程号 AND 课程名称=”数学”)32. 查询选修课程号为”C4”的学生的平均年龄。( 此题先求出年龄后再做)SELECT AVG(年龄) FROM 学生 WHERE学号 IN (SELECT 学号 FROM 成绩 WHERE 课程号=”C4”)33. 查询选修课程名为”数据库”课程的学生的平均年龄。SELECT AVG(年龄) FROM 学生 WHERE 学号 IN(SELECT 学号 FROM 课程，成绩 WHERE 课程名=”数据库” AND 课程课程号=成绩课程号)34. 统计每门课程的平均成绩。SELECT 课程号，AVG( 成绩 ) FROM 成绩 GROUP BY 课程
13、号35. 查询学生表中男女生人数。SELECT 性别，COUNT(性别 ) FROM 学生 GROUP BY 性别36. 按课程号统计每门成绩的总分、平均分、最高分及最低分。SELECT 课程号，SUM(成绩)，AVG(成绩)，MAX(成绩) ，MIN(成绩)FROM 成绩 GROUP BY 课程号37. 查找并显示低于总平均分的学生姓名和平均成绩。错误：SELECT 姓名，AVG(成绩) FROM 学生，成绩 WHERE 成绩=242. 检索选修 3 门以上(含 3 门)课程学生的学号和姓名。SELECT 学号，姓名 FROM 学生 WHERE 学号 IN(SELECT 学号 FROM 成绩
14、 GROUP BY 课程号 HAVING COUNT(课程号)=3) 联接与嵌套查询43. 查询每个学生及其选修课程的情况，包括学号、姓名、课程名称及成绩。联接：SELECT 学生学号，姓名，课程名称，成绩 FROM 学生，课程，成绩 WHERE 学生学号=成绩学号 AND 课程课程号=成绩课程号 44. 查询成绩不及格的学生姓名、课程名称及成绩。联接：SELECT 姓名，课程名称，成绩 FROM 学生，课程，成绩 WHERE 学生学号=成绩学号AND 课程课程号=成绩课程号 AND 成绩6045. 查询没有参加选修课程的学生姓名。嵌套：SELECT 姓名 FROM 学生 WHERE 学号 N
15、OT IN(SELECT DISTINCT 学号 FROM 成绩)46. 查询与”王芳” 在同一个系学习的学生学号、姓名、院系。嵌套：SELECT 学号，姓名，院系 FROM 学生 WHERE 院系=(SELECT 院系 FROM 学生 WHERE 姓名=”王芳”)47. 查询”王芳” 同学没有选修课程的课程号。嵌套：SELECT 课程号 FROM 课程 WHERE 课程号 NOT IN (SELECT 课程号 FROM 学生，成绩 WHERE 姓名=”王芳” AND 学生学号=成绩学号)48. 查询”王芳” 同学没有选修课程的课程名称。嵌套：SELECT 课程名称 FROM 课程 WHERE
16、 课程号 NOT IN (SELECT 课程号 FROM 学生，成绩 WHERE 姓名=”王芳” AND 学生学号=成绩学号)49. 查询刘海军老师所授课的学生的学号。联接：SELECT 学号 FROM 成绩，课程 WHERE 成绩课程号=课程课程号 AND 任课教师=”刘海军”)嵌套：SELECT 学号 FROM 成绩 WHERE 课程号 IN(SELECT 课程号 FROM 课程 WHERE 任课教师=”刘海军”)50. 查询选修”数学” 课程的学生学号；嵌套 1：Select 学号 From 成绩 Where 课程号 In (Select 课程号 From 课程 Where 课程名称=”
17、数学”)嵌套 2：Select 学号 From 成绩 Where 课程号 =(Select 课程号 From 课程 Where 课程名称=”数学”)联接：Select 学号 From 成绩，课程 Where 成绩课程号=课程课程号 And 课程名称=”数学”SQL 分类查询大全第 6 页 共 7 页51. 查询选修”数学” 课程的学生姓名；嵌套：Select 姓名 From 学生 Where 学号 In ；(Select 学号 From 成绩，课程 Where 成绩课程号 =课程课程号 And 课程名称=” 数学”)52. 查询选修 C1 课程的学生姓名、成绩，结果按成绩从高到低排序。嵌套：S
18、ELECT 姓名，成绩 FROM 学生，成绩WHERE 课程号 =”C1” AND 学生学号=成绩学号) ORDER 成绩 DESE53. 查询没有选修 C1 课程的学生姓名。嵌套：SELECT 姓名 FROM 学生 WHERE 学号 NOT IN (SELECT 学号 FROM 成绩 WHERE 课程号=”C1”)54. 按成绩降序输出”计算机系”学生选修了” 数据库”课程的学生姓名和成绩。嵌套：SELECT 姓名，成绩 FROM 学生，成绩 WHERE (学生学号=成绩学号) AND (课程号 IN(SELECT 课程号 FROM 课程 WHERE 课程名=”数据库” AND 院系=” 计
19、算机”) ORDER BY 成绩 DESC联接：SELECT 姓名，成绩 FROM 学生，课程，成绩 WHERE 学生学号=成绩学号 AND 课程课程号=成绩课程号 AND 课程名=”数据库” AND 院系=”计算机” ORDER BY 成绩 DESC55. 查询女生选修课程的课程名称和任课教师。嵌套：SELECT 课程名称，任课教师 FROM 课程 WHERE 课程号 IN(SELECT 课程号 FROM 学生，成绩 WHERE 性别=”女” AND 学生学号=成绩学号)联接：SELECT 课程名称，任课教师 FROM 学生，成绩，课程WHERE 性别 =”女” AND 学生学号=成绩学号
20、AND 成绩课程号=课程课程号56. 查询先行课为”数据库” 的课程名称；内嵌套：Select 课程名称 From 课程 Where 课程号=(Select 课程号 From 课程 Where 课程名称=”数据库”)；57. 查询”数据库” 先行课的课程名称；内联接：Select S课程名称 From 课程 S，课程 T Where S课程号=T 课程号 And T课程名称=”数据库”; 并操作查询58. 查询选修了 C1 课程或者选修 C2 课程的学生学号。并操作：(SELECT 学号 FROM 成绩 WHERE 课程号=”C1”) UNION(SELECT 学号 FROM 成绩 WHERE
21、 课程号=”C2”)条件操作：SELECT 学号 FROM 成绩 WHERE 课程号=”C1” OR 课程号=”C2” 数据更新59. 计算学生表中每个学生的年龄UPDATE 学生 SET 年龄=2011-YEAR(出生日期)60. 将成绩表中所有 55-59 分的学生成绩增加 5 分。UPDATE 成绩 SET 成绩=成绩+5 WHERE 成绩 BETWEEN 55 AND 5961. 把女生成绩提高 5。UPDATE 成绩 SET 成绩=成绩*1.05 WHERE 学号 IN (SELECT 学号 FROM 学生 WHERE 性别=”女”)62. 将成绩表中所有 55-59 分的女生成绩增
22、加 5 分。UPDATE 成绩 SET 成绩=成绩+5 WHERE (成绩 BETWEEN 55 AND 59) AND 学号 IN(SELECT 学号 FROM 学生 WHERE 性别=”女”)63. 把低于总平均成绩的学生成绩提高 5。UPDATE 成绩 SET 成绩=成绩*1.05 WHERE 成绩(SELECT AVG(成绩) FROM 成绩) 视图操作SQL 分类查询大全第 7 页 共 7 页64. 建立视图 FFS(课程号，最高分，最低分)，要求按课程号分组，求出成绩表中选修 C1 课程的最高分和最低分；CREATE VIEW FFS AS SELECT 学号，MAX(成绩) AS
23、 最高分，MIN(成绩) AS 最低分 FROM 成绩 WHERE 课程号=”C1” GROUP BY 课程号65. 利用视图”FFS”，查询最高分学生的姓名。SELECT 姓名 FROM 学生，成绩，FFS WHERE 学生学号=成绩学号 AND FFS最高分=成绩成绩 操作查询66. 创建数据表 WWS.DBF，结构为：学号 (C，6)、姓名(C ，10)、出生年月(D) 、总分(N ，5，1)，并将学号设置为主索引。 （建表前需先建立数据库文件）CREATE TABLE WWS(学号 C(6) PRIMARY KEY，姓名 C(6)，出生日期 D，总分 N(5，1)67. 在 WWS 表中再增加一条记录为：”090101”、 ”刘玉芳”、1990-01-21、516；INSERT INTO WWS VALUES(”090201”， ”刘玉芳”，1990-01-21，516)68. 将 WWS 表中的姓名字段的宽度由原来的 10 个字符改为 8 个字符；ALTER TABLE WWS ALTER 姓名 C(8)69. 在 WWS 表中的增加院系号(C，2)；ALTER TABLE WWS ADD 院系号 (C，2)70. 将 WWS 表中刘玉芳的记录删除。DELETE FROM WWS WHERE 姓名=” 刘玉芳”
