数据库教案 项目二 数据库的查询.docx

《数据库教案 项目二 数据库的查询.docx》由会员分享，可在线阅读，更多相关《数据库教案 项目二 数据库的查询.docx（14页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、项目二数据库的查询一、教学目标1 .掌握学习SQL基本查询。2 .辨析排序order by与分组group by。3 .学会聚合数据查询与分组统计数据。4 .掌握连接查询、组合查询。5 .了解插入、更新。二、课时分配6 项目共6个任务，安排6课时。三、教学重点通过本项目的学习，让学生了解学习SQL基本查询，掌握通配符“*”的使 用，学习多个字段拼接，掌握WHERE子句使用方法；辨析排序order by与分组 group by,学习INTO子句和TOP子句；掌握并学会在数据库information09da2 中，利用排序ORDER BY和分组GROUP BY进行SQL查询操作。按学生的姓名拼音

2、排序，显示学生的序号、姓名、性别、户口所在地。分别统计男女在校住宿生人 数和住宿生总数。统计户口在同一城市的学生人数，并按统计的人数从少到多排 序；学会聚合数据查询以及分组统计数据；学会对学生成绩的聚合数据查询，利 用数据库informationl0da5da6,根据数据表“信息10大5”,对表中的数据进 行聚合数据查询；利用数据库information09da2中的数据表“户口和住宿表”， 进行分组统计查询；掌握什么是连接查询，分清等值连接与非等值连接、自身连 接、外连接、嵌套查询；掌握在SQL Server 2005中，将多个查询的结果放在一起，以 一个查询结果的形式显示出来，则可以使用U

3、NION关键字把多个SELECT连接起来。每 个SELECT查询语句应当有相同数量的字段，若字段个数不等，可以使用NULL来代替； 每个查询语句中相应的字段的类型必须相互兼容，若不兼容，可使用类型转换函数、强 制转换字段类型；学会INSERT语句、批量插入数据，将成绩不及格的学生信息批量插 入到新建的数据表中。四、教学难点1 .掌握SQL基本查询。2 .辨析排序order by与分组group by的区别。表10-8教师工资表namesexsalary孙莉女1995李强男2598王明军男2196张丰波男3098吴伟林男2880程序清单如下：SELECT x. name AS 姓名，x. sal

4、ary AS 工资，y. salary AS 李强工资FROM TeacherSalary as x, TeacherSalary as yWHERE x. salaryy. salary AND y. name=李强需要给表起别名以示区别，由于所有属性都是同名属性，因此必须使用别名前缀。 查询结要如表10-9所示。表10-9比“李强”教师高的教师工资表姓名资李强工资张丰波30982598吴伟林28802598三、外连接普通连接操作只输出满足连接条件的元组；外连接操作以指定表为连接主体，将主体表 中不满足连接条件的元组一起输出。可以分为左外连接和右外连接，左外连接的结果集 包括LEFT OUT

5、ER子句或LEFT OUTER JOIN中指定的左表的所有行，而不仅仅是连接列 所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所 有选择列表列均为空值。右外连接RIGHT JOIN或RIGHT OUTER JOIN将返回右表的所 有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。【例6】在学生表10-1中增加两个学生，分别为“刘艳”和“刘希妍”，如表10-10 所示。选课表sc如表10-2所示。表10 - 10 学生表studentsnosnamessexsagesdept2012010101陈路男24计算机2012010102张亮男二23计算机20120

6、11102张月女22创意2012011103刘艳女23创意2012011104刘希妍女22创意左外连接查询学生表student各选课表sc。SELECT student.sno, sname, ssex, sage, sdept, eno, score FROM student LEFT OUTER JION sc ON (student.sno=sc.sno) 执行结果如表10-11所示。表10-11 左外连接查询snosnamessexsagesdeptenoscore2012010101陈路男24计算机1952012010101陈路男24计算机2982012010102张亮男23计算机2

7、962012011102张月女22创意3982012011102张月女22创意2802012011103刘艳女23创意NULLNULL2012011104刘希妍女22创意NULLNULL四、嵌套查询一个SELECT FROM WHERE语句称为一个查询块，将一个查询块嵌套在另一个查询块的 WHERE子句或HAVING短语的条件中的查询为嵌套查询。1 .带有IN的子查询子查询在上一级查询处理之前求解，子查询的结果用于建立父查询的查找条件。【例7】运用表10-1和表10-2,查询选修课程号为2的学生名单。SELECT snameFROM studentWHERE sno IN(SELECT sno

8、FROM scWHERE cno-2)【例8】查询与“陈路”同系的学生名单若用分步完成：(1)确定“陈路”所在的系名：SELECT sdeptFROM studentWHERE sname二陈路结果为“计算机”(2)查找所有在“计算机”学生名单：SELECT sno, sname, sdeptFROM studentWHERE sdept二计算机结果如表10-12所示。表10-12计算机系的学生snosnamesdept2012010101陈路计算机2012010102张亮计算机把前面两步合并在一起的查询为: SELECT sno, sname, sdept FROM studentWHERE

9、 sdept IN(SELECT sdeptFROM studentWHERE sname二陈路)本例的IN可以用“二”代替。2 .带有比较运算符的子查询【例9】查询超出课程平均成绩的学生号和课程号。SELECT sno, enoFROM SC xWHERE score = (SELECT avg (score)FROM SC yWHERE y. sno=x. sno)3 .带有ANY、SOME或ALL的子查询【例10】查询其他系中比计算机系所有学生年龄都要小的学生姓名和年龄。SELECT sname, sageFROM studentWHERE sage ALL (SELECT sageFR

10、OM studentWHERE sdept二计算机)AND sdept计算机运用上述的表101,可以查询出两位“张月”和“刘希妍”的符合条件，她们的年龄都 比计算机系所有学生的年龄都要小。用聚合函数改写为：SELECT sname, sageFROM studentWHERE sage ALL (SELECT MIN (sage)FROM studentWHERE sdept二计算机)AND sdept计算机4 .带EXISTS的子查询不需要返回具体的查询数据，而只关心是否有返回值，即返回True或False。【例11】运用表107和10-2,查询没有选修1号课程的学生姓名。SELECT sn

11、ameFROM studentWHERE NOT EXISTS (SELECT *FROM scWHERE sno=student, sno AND cno=l)任务十一、组合查询在SQL Server 2005中，将多个查询的结果放在一起，以一个查询结果的形式显示出来, 则可以使用UNION关键字把多个SELECT连接起来。每个SELECT查询语句应当有相同数 量的字段，若字段个数不等，可以使用NULL来代替；每个查询语句中相应的字段的类 型必须相互兼容，若不兼容，可使用类型转换函数强制转换字段类型。UNION缺省在合 并结果集后消除重复项，UNION ALL指定在合并结果集后保留重复项。U

12、NION结果集中 的列名总是等于UNION中第一个SELECT语句中的列名。【例1】 网络售书情况表Internetinfo如表11-1所示，实体店面售书表Storeinfo 如表11-2所示，现在需要查询所有售书的订单日期。表11-1网络售书情况网络名 InternetName）书名（BookName）订单日期(OrderDate)发货口期(OrderDate)当当网蛙2012-10-112012-10-12京东商城丰乳肥臀2012-10-122012-10-13表11-2实体店面情况实体店面名称(StoreName)书名（BookName）订单日期(OrderDate)发货日期(Order

13、Date)鼓楼新华书店蛀2012- 10-122012-10-14观前街新华书店丰乳肥臀2012-10-132012-10-15SELECT OrderDate FROM InternetinfoUNIONSELECT OrderDate FROM Storeinfo 操作结果如表11-3所示。表11 -3 售书UNION结果SELECT OrderDate FROM InternetinfoUNION ALLSELECT OrderDate FROM Storeinfo操作结果如表11-4所示。表11 -4 售书UNION ALL结果订单日期(OrderDate)2012-10-11 201

14、2 -10-12 2012-10-12 2012-10-13任务十二.插入.更新在SQL Server 2005中，有时为了方便，需要将查询结果保存起来。在SELECT子句的 后面，FROM子句的前面加上了一个INTO关键字，关键字的后面紧跟用于保存查询结果 的新表的名字。SELECT * (或字段列表)INTO新表名FROM表名一、INSERT 语句INSERT语句用于向数据库表或者视图中加入一行数据。INSERT语句的语法形式如下： INSERT INTO table_or_view (column_list) VALUES(data_values) 其中，table_or_view是指要

15、插入新记录的表或视图，column_list是可选项，指定待 添加数据的列，VALUES子句指定待添加数据的具体值。列名的排列顺序不一定要和表定 义时的顺序一致。但当指定列名表时，VALUES子句值的排列顺序必须和列名表中的列名 排列顺序一致，个数相等，数据类型一一对应。在进行数据插入操作时须注意以下2点：(1)在VALUES子句中，必须用逗号将各个数据分开，字符型数据要用单引号括起来。(2)有时并不需要向表中插入完整的行，而需要将数据只插入到几个指定的字段内， 则在表名后加上字段列表，且VALUES子句中值的排列顺序要和表中各属性列的排列顺 序一致。【例1】将姓名为夏总的相关信息插入到表中。

16、INSERT INTO student (sno, sname, sex, birth, depart)VALUES(0025,夏总，男，1958-10-01,信息工程系)(3)将VALUES子句中的值按照INTO子句中指定列名的顺序插入到表中。(4)对于INTO子句中没有出现的列，则插入的新记录在这些列上将取空值。但在表 定义时有NOT NULL约束的属性列不能取空值，例如学号不能为空。二、UPDATE 语句UPDATE语句用于修改数据库表或视图中特定记录或者字段的数据，其语法形式如下：UPDATE table_or_viewSET = , column二expression WHERE 其

17、中：table_or_view是指要修改的表或视图；SET子句给出要修改的列及其修改后的 值，其中column为要修改的列名，expression为其修改后的值；WHERE子句指定待修 改的记录应当满足的条件，WHERE子句省略时，则修改表中的所有记录。【例2带有WHERE条件的修改语句。UPDATE student, addressSET city=SuzhouWHERE student, address =1【例3】将所有学生年龄增加1岁。UPDATE S SET age=age+l【例4】把讲授C2课程的教师的工资增加500元。教师基本情况表是T表，其结构为T (tno, tn, sex

18、, age, prof, sal, dept),分别表示教师 的编号、姓名、性别、年龄、职称、工资和系别。教师授课表是TC表，其结构为TC(tno, eno) 分别表示教师的编号，课程编号。UPDATE TSET sal=sal+500WHERE tno IN(SELECT T. tnoFROM T, TCWHERE T.tno=TC. tno AND TC.cno=C2)例5在ssts数据库中，把课程“数据库”的学生成绩每人增加1分。 UPDATE选课表SET成绩=成绩+1WHERE课程号IN (SELECT选课表.课程号FROM选课表,课程表WHERE选课表,课程号二课程表.课程号AND课

19、程表.课程名二数据库)3 .掌握聚合数据查询与分组统计数据。4 .掌握连接查询、组合查询、插入与更新。五、教学内容任务七SQL基本查询在SQL Server 2005中，maste数据库是默认的当前数据库，所以在编写操作SQL语句 时，先使用USE语句打开要使用的表所在的数据库，否则就会出现“对象名无效”提示。 一、单表查询1 .通配符“*”的使用在SELECT语句中，可以使用通配符来显示所有的字段。【例1】查询学生信息表中所有学生的信息。USE information09da2SELECT *FROM学生信息表SELECT后面列出要查询的字段名，字段之间要用英文逗号”隔开。如果要去掉重复 的

20、查询结果，要加上DISTINCT。用关键字AS可以命名新列，也可以给现有字段取别名。2 .用AS重新指定返回字段的名字【例2】查询学生信息表中所有学生的姓名和性别，其中sname用“姓名”显示，sex 用“性别”显示。SELECT sname AS 姓名，sex AS 性别FROM学生信息表3 .多个字段拼接如果需要将多个字段拼接成一个字段，可以使用“ + ”连接，使用函数rtrim去掉字段 右侧的空格。【例3】查询学生信息表中所有学生的姓名和系别，并生成拼接成一个字段。SELECT RTRIM(sname) + ，( +RTRIM(depart) + ，) AS 姓名和系别FROM学生信息表

21、运行的结果如：张三(信息工程系)4 . WHERE 子句用关键字WHERE子句来说明查询条件，紧跟在FROM子句的后面。条件表达式中有如下 条件运算符，关系运算符：=,=,!=；逻辑运算符NOT, AND, OR； 其他运算符：IN, NOT IN, BETWEEN, NOT BETWEEN, LIKE, NOT LIKE, IS NULL, IS NOT NULL, EXISTS, ANY, SOMEo例4查询学生信息表中所有学生的年龄不小于18岁的女生的姓名和性别。SELECT姓名，性别FROM学生信息表WHERE性别二一女，AND年龄=185 .查询常用的通配符常用的通配符见表7-lo表

22、7-1常用的通配符通配符描 述举例%匹配。个或n个字符WHERE sname LIKE 张%查找姓张的下划线匹配1个 字符WHERE学生编号like2012查询学生编号开头为2012后面一个字符n指定范围内的任何 单个字符WHERE 年龄 LIKE”3 8查询年龄在2030岁之 间,但只显示2328岁口不在指定范围内的 任何单个字符WHERE 年龄 LIKEH2110-9查询年龄不在2030岁 之间在 sqlsever 中 nchar, nvarchar 使用的是 unicode 字符集。而 char, varchar 使用的 是ASCH字符集。规定：当unicode数据(nchar或nvar

23、char)与like 一起使用时, 尾随空格有意义。但对非unicode数据，尾随空格无意义。而ASCH中char字符串后 面的空格没有意义，即char和varchar尾部空格无意义。数据库中“姓名”列的数据类型，若数据类型为nchar(8),所以要查询姓“王”且单名 的学生例如“王石”情况，则查询语句应该为：SELECT *FROM学生信息表WHERE姓名LIKE王一一王下划线后面是6个空格，要与nchar的长度一致。任务八.排序order by与分组group by一、排序 ORDER BY在SQL Server 2005中，为了方便查看查询结果，按某种规律排序，可使用ORDER BY 子

24、句来排序数据，还可以进行多字段排序。【例1】从学生表student中按照成绩grade升序排序。SELECT *FROM studentORDER BY grade也就是按照成绩grade升序排序，默认排序方式是升序，功能等于在字段名后加上升序 关键字ASC, ORDER BY后的字段名可以不出现在SELECT字段列表中，如果要降序排列, 在字段名后加上DESC。例如：ORDER BY grade DESC二、分组 GROUP BY数据分组经常运用在统计汇总中，运用GROUP BY可以进行分组，如果要将满足条件的 分组查询出来，还需要使用HAVING子句。需要注意的是，使用GROUP BY时，

25、不要使用 SELECT *语句，否则有“列表中的列无效，因为该列没有包含在聚合函数或GROUP BY 子句中”错误提示。如果SELECT子句后是字段名列表，而这些字段名又不在聚合函数 中，则应当在GROUP BY子句中列出所有这些字段名，这时分组就是这些字段的组合， 而并非单个字段分组。【例2】从学生表student中按照性别sex分组，统计男女生人数。SELECT sex, COUNT (*) As 人数FROM studentGROUP BY sex三、INTO子句和TOP子句对已经存在的表，运用INTO子句可以创建一张新表，此新表存储在数据库中，不会显 示在屏幕上，因此，建立后可以用SE

26、LECT显示全部记录。若INTO后的表名前有 号，表示该新表为临时表，退出SQL Server后，不保存该新表。【例3】从学生表student中按照系别，把信息工程系的学生记录单独生成一张新表 “信息工程系学生表”。SELECT * INTO信息工程系学生表FROM studentWHERE系别二信息工程系SELECT *FROM信息工程系学生表TOP子句可以限定显示前n条记录。【例4】显示学生表student中前10条记录。SELECT TOP 3 *FROM student任务九、聚合数据查询与分组统计数据一、聚合数据查询GROUP BY子句可以将查询结果按属性列或属性列组合在行的方向上进

27、行分组，每组在属 性列或属性列组合上具有相同的聚合值。常用的聚合函数如表9-1所示。表9-1常用的聚合函数函数名称MINMAXSUMAVGCOUNTC()UNT( * )功能求一列中 的最小值求一列中 的最大值按列计算 值的总和按列计算 平均值按列值计 个数返回表中的所 用行数【例1】查询求学号为001学生的总分和平均分。程序清单如下：SELECT SUM (score) AS 总分，AVG (score) AS 平均分FROM SCWHERE sno = 001注意：函数SUM和AVG只能对数值型字段进行计算。【例2】查询求信息工程系学生的总数。SELECT COUNT (sno)FROM

28、SWHERE dept二信息工程系【例3】查询求学校中共有多少个系。程序清单如下：SELECT COUNT (DISTINCT dept) AS 系部个数FROM S注意：加入关键字DISTINCT后表示消去重复行，可计算字段“dept”不同值的数目。COUNT函数对空值不计算，但对零进行计算。二、分组统计数据在分组查询中，只要表达式中不包括聚合函数，就可以按该表达式分组。【例4】查询每位学生的学号及其选课的门数。程序清单如下：SELECT eno, COUNT (*) AS 选课门数FROM SCGROUP BY enoGROUP BY子句按cn。的值分组，所有具有相同eno的元组为一组，对

29、每一组使用函数 COUNT进行计算，统计出各位学生选课的门数。GROUP BY子句还可以与WHERE子句配合使用，WHERE子句先于GROUP BY子句执行，将 满足条件的记录保留下来，然后，再按照GROUP BY子句分成小组。若在分组后还要按 照一定的条件进行筛选，则需要使用HAVING子句。【例5】在分组查询中使用WHERE条件，查询计算机系的学生学号及平均成绩。程序清单如下：SELECT sno, AVG (score) AS averagescoreFROM SCWHERE sno= (SELECT sno FROM S WHERE dept=计算机)GROUP BY snoORDER

30、 BY sno例6在分组查询中使用HAVING条件，查询平均成绩大于85分的学生学号及平均成 绩。程序清单如下：SELECT sno, AVG (score) AS averagescoreFROM SCGROUP BY snoHAVING AVG (score) 85【例7】查询选课在三门以上且各门课程均及格的学生的学号及其总成绩，查询结果按 总成绩降序列出。程序清单如下：SELECT sno, SUM(score) AS totalscoreFROM SCWHERE score=60GROUP BY snoHAVING COUNT(*)=3ORDER BY SUM (score) DESC

31、三、反转查询结果当查询的结果不是理想的行列显示时，可以使用反转查询转换。可以利用COUNT函数忽 略NULL值的规则，借助于CASE END表达式实现。例8查询每系学生的男生和女生人数得到结果如表9-2所示。表9-2各系男生和女生人数deptsextotal信息工程4 H口有一男 He266信息工程女220机电工程男350机电工程女112电气工程男342电气工程女123要想得到如表9-3所示的表格。表9-3转换后各系男女生数dept男生人数女生人数信息工程266220机电工程350112电气工程342123查询语句如下：SELECTdept, COUNT (CASE WHEN sex=男THE

32、N1ELSENULLEND) AS男生人数，COUNT (CASE WHEN sex=女THEN1ELSENULLEND) AS女生人数FROMstudentGROUP BY dept四、HAVING子句在SQL中增加HAVING子句原因是，WHERE关键字无法与统计函数一起使用。如果只想要看到分组的统计结果，不需要全部的统计信息。【例9】查询信息工程系和机电工程系的学生数。SELECT dept, COUNT (*) AS 人数FORM studentGROUP BY deptHAVING dept IN(信息工程，机电工程)【例10】订单表如表9-4所示，有商品编号Id、订单日期OderD

33、ate、订单价格OrderPrice 和订单的顾客姓名。表9-4订单信息表IdOrder DateOrderPriceCustomer12012/10/292500李其福22012/10/231800孔平俊32012/10/05850祁华广42012/09/20350张泉成52012/07/061850方道萍62012/05/21500卢喜72012/05/122000李其福82012/05/081200孔平俊若希望查找订单总金额少于2000的客户。则使用如下SQL语句：SELECT Customer, SUM(OrderPrice) FROM OrdersGROUP BY CustomerH

34、AVING SUM(OrderPrice)2500对于WHERE和HAVING,HAVING子句主要用于筛选组，而WHERE子句用于筛选记录;HAVING 子句中可以运用聚合函数，而WHERE子句不能运用聚合函数；HAVING子句不能出现既不 被GROUP BY子句包含的字段，又不被聚合函数包含的字段，而WHERE子句中可出现所 需的任意字段。通过上例可以知道使用的顺序一般为先WHRER,再GROUP BY分组，最后 是HAVING子句分组条件。任务十,连接查询一、等值连接与非等值连接连接条件的一般格式为：表名1.列名1比较运算符表名2.其中，比较运算符主要有：=、=、 =、！=。当比较运算符

35、为“=”时, 称为等值连接，其他情况为非等值连接。【例1】 现有学生表student和选课表sc,分别如表10-1和表10-2所示。表10 -1 学生表studentsnosnamessexsagesdept2012010101陈路男24计算机2012010102张亮男23计算机2012011102张月女22创意表10-2选课表scsnoenoscore2012010101195201201010129820120101022962012011102982012011102280查询每个学生及其选修课程的情况。SELECT student. *, sc.*FROM student, scWHE

36、RE student. sno=sc. sno查询结果如表10-3所示。表10-3学生选修课程的情况student, snosnamessexsagesdeptsc. snoenoscore2012010101陈路男24计算机20120101011952012010101陈路男24计算机20120101012982012010102张亮男23计算机20120101022962012011102张月女22创意20120111023982012011102张月女22创意2012011102280自然连接是一种特殊的等值连接，它要求两个关系中进行比较的分量必须是相同的属性 组，并且在结果中把重复的属

37、性列去掉，而等值连接并不去掉重复的属性列。【例2】对上例用自然连接完成查询。SELECT student, sno, sname, ssex, sage, sdept,eno, scoreFROM student,scWHERE student.sno=sc. sno查询结果如表10-4所示。表10-4学生选修课程的情况snosnamessexsagesdeptCDOscore2012010101陈路男24计算机1952012010101陈路24计算机2982012010102张亮男23计算机2962012011102张月女22创意3982012011102张月女22创意280【例3】查询张亮

38、同学所选修的课程号为2的课程成绩。SELECT student. sno, sname, sc. eno, scoreFROM student,scWHERE (student. sno = sc. sno) AND (student, sname二张亮)AND (sc. cno=2) 查询结果为表10-5所示。表10-5学生张亮选修课程的情况snosname _ enoscore2012010102张亮* 296【例4现有学生表student和选课表sc,如表10-1和10-2所示，课程表c如表10-6 所示。表10 - 6课程表enocname1ASP. NET程序设计2大学计算机基础3服

39、装设计查询所有选课学生的学号、姓名、选课名称及成绩。 SELECT student, sno, sname, cname, scoreFROM student,c,scWHERE student. sno=sc. sno AND o=c. eno 查询结果为表10-7所示。表10-7所有学生选课情况snosnamessexscore2012010101陈路ASP. NET程序设计952012010101陈路大学计算机基础982012010102张亮大学计算机基础962012011102张月服装设计982012011102张月大学计算机基础80本例涉及三个表，WHERE子句中有两个连接条件。当有两个以上的表进行连接时，称为 多表连接。二、自身连接当一个表与其自己进行连接操作时，称为表的自身连接。要查询的内容均在同一表中， 可以将表分别取两个别名，一个是x, 一个是y。将x, y中满足查询条件的行连接起来。 这实际上是同一表的自身连接。【例5现有教师工资表TeacherSalary如表10-8所示，查询所有比教师“李强”工 资高的教师姓名、性别、工资和李四的工资。