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

项目二数据库的查询一、教学目标1.掌握学习SQL基本查询。2.辨析排序orderby与分组groupby。3.学会聚合数据查询与分组统计数据。4.掌握连接查询、组合查询。5.了解插入、更新。二、课时分配本项目共6个任务，安排6课时。三、教学重点通过本项目的学习，让学生了解学习SQL基本查询，掌握通配符“*”的使用，学习多个字段拼接，掌握WHERE子句使用方法；辨析排序orderby与分组groupby，学习INTO子句和TOP子句；掌握并学会在数据库information09da2中，利用排序ORDERBY和分组GROUPBY进行SQL查询操作。按学生的姓名拼音排序，显示学生的序号、姓名、性别、户口所在地。分别统计男女在校住宿生人数和住宿生总数。统计户口在同一城市的学生人数，并按统计的人数从少到多排序；学会聚合数据查询以及分组统计数据；学会对学生成绩的聚合数据查询，利用数据库information10da5da6，根据数据表“信息10大5”，对表中的数据进行聚合数据查询；利用数据库information09da2中的数据表“户口和住宿表”，进行分组统计查询；掌握什么是连接查询，分清等值连接与非等值连接、自身连接、外连接、嵌套查询；掌握在SQLServer2005中，将多个查询的结果放在一起，以一个查询结果的形式显示出来，则可以使用UNION关键字把多个SELECT连接起来。每个SELECT查询语句应当有相同数量的字段，若字段个数不等，可以使用NULL来代替；每个查询语句中相应的字段的类型必须相互兼容，若不兼容，可使用类型转换函数、强制转换字段类型；学会INSERT语句、批量插入数据，将成绩不及格的学生信息批量插入到新建的数据表中。四、教学难点1.掌握SQL基本查询。2.辨析排序orderby与分组groupby的区别。3.掌握聚合数据查询与分组统计数据。4.掌握连接查询、组合查询、插入与更新。五、教学内容任务七SQL基本查询在SQLServer2005中，master数据库是默认的当前数据库，所以在编写操作SQL语句时，先使用USE语句打开要使用的表所在的数据库，否则就会出现“对象名无效”提示。一、单表查询1.通配符“*”的使用在SELECT语句中，可以使用通配符“*”来显示所有的字段。【例1】查询学生信息表中所有学生的信息。USEinformation09da2SELECT*FROM学生信息表SELECT后面列出要查询的字段名，字段之间要用英文逗号“,”隔开。如果要去掉重复的查询结果，要加上DISTINCT。用关键字AS可以命名新列，也可以给现有字段取别名。2.用AS重新指定返回字段的名字【例2】查询学生信息表中所有学生的姓名和性别，其中sname用“姓名”显示，sex用“性别”显示。SELECTsnameAS姓名,sexAS性别FROM学生信息表3.多个字段拼接如果需要将多个字段拼接成一个字段，可以使用“+”连接，使用函数rtrim去掉字段右侧的空格。【例3】查询学生信息表中所有学生的姓名和系别，并生成拼接成一个字段。SELECTRTRIM(sname)+‘(’+RTRIM(depart)+‘)’AS姓名和系别FROM学生信息表运行的结果如：张三(信息工程系)4.WHERE子句用关键字WHERE子句来说明查询条件，紧跟在FROM子句的后面。条件表达式中有如下条件运算符，关系运算符：=,<,>,<=,>=,<>,!=；逻辑运算符NOT,AND,OR；其他运算符：IN,NOTIN,BETWEEN,NOTBETWEEN,LIKE,NOTLIKE,ISNULL,ISNOTNULL,EXISTS,ANY,SOME。【例4】查询学生信息表中所有学生的年龄不小于18岁的女生的姓名和性别。SELECT姓名,性别FROM学生信息表WHERE性别=‘女’AND年龄>=185.查询常用的通配符常用的通配符见表7-1。在sqlsever中nchar，nvarchar使用的是unicode字符集。而char，varchar使用的是ASCII字符集。规定：当unicode数据（nchar或nvarchar）与like一起使用时，尾随空格有意义。但对非unicode数据，尾随空格无意义。而ASCII中char字符串后面的空格没有意义，即char和varchar尾部空格无意义。数据库中“姓名”列的数据类型，若数据类型为nchar(8)，所以要查询姓“王”且单名的学生例如“王石”情况，则查询语句应该为：SELECT*FROM学生信息表WHERE姓名LIKE王_——王下划线后面是6个空格，要与nchar的长度一致。任务八、排序orderby与分组groupby一、排序ORDERBY在SQLServer2005中，为了方便查看查询结果，按某种规律排序，可使用ORDERBY子句来排序数据，还可以进行多字段排序。【例1】从学生表student中按照成绩grade升序排序。SELECT*FROMstudentORDERBYgrade也就是按照成绩grade升序排序，默认排序方式是升序，功能等于在字段名后加上升序关键字ASC，ORDERBY后的字段名可以不出现在SELECT字段列表中，如果要降序排列，在字段名后加上DESC。例如：ORDERBYgradeDESC二、分组GROUPBY数据分组经常运用在统计汇总中，运用GROUPBY可以进行分组，如果要将满足条件的分组查询出来，还需要使用HAVING子句。需要注意的是，使用GROUPBY时，不要使用SELECT*语句，否则有“列表中的列无效，因为该列没有包含在聚合函数或GROUPBY子句中”错误提示。如果SELECT子句后是字段名列表，而这些字段名又不在聚合函数中，则应当在GROUPBY子句中列出所有这些字段名，这时分组就是这些字段的组合，而并非单个字段分组。【例2】从学生表student中按照性别sex分组，统计男女生人数。SELECTsex,COUNT(*)As人数FROMstudentGROUPBYsex三、INTO子句和TOP子句对已经存在的表，运用INTO子句可以创建一张新表，此新表存储在数据库中，不会显示在屏幕上，因此，建立后可以用SELECT显示全部记录。若INTO后的表名前有“＃”号，表示该新表为临时表，退出SQLServer后，不保存该新表。【例3】从学生表student中按照系别，把信息工程系的学生记录单独生成一张新表“信息工程系学生表”。SELECT*INTO信息工程系学生表FROMstudentWHERE系别=‘信息工程系’SELECT*FROM信息工程系学生表TOP子句可以限定显示前n条记录。【例4】显示学生表student中前10条记录。SELECTTOP3*FROMstudent任务九、聚合数据查询与分组统计数据一、聚合数据查询GROUPBY子句可以将查询结果按属性列或属性列组合在行的方向上进行分组，每组在属性列或属性列组合上具有相同的聚合值。常用的聚合函数如表9-1所示。【例1】查询求学号为001学生的总分和平均分。程序清单如下：SELECTSUM(score)AS总分,AVG(score)AS平均分FROMSCWHEREsno=001注意：函数SUM和AVG只能对数值型字段进行计算。【例2】查询求信息工程系学生的总数。SELECTCOUNT(sno)FROMSWHEREdept=信息工程系【例3】查询求学校中共有多少个系。程序清单如下：SELECTCOUNT(DISTINCTdept)AS系部个数FROMS注意：加入关键字DISTINCT后表示消去重复行，可计算字段“dept”不同值的数目。COUNT函数对空值不计算，但对零进行计算。二、分组统计数据在分组查询中，只要表达式中不包括聚合函数，就可以按该表达式分组。【例4】查询每位学生的学号及其选课的门数。程序清单如下：SELECTcno,COUNT(*)AS选课门数FROMSCGROUPBYcnoGROUPBY子句按cno的值分组，所有具有相同cno的元组为一组，对每一组使用函数COUNT进行计算，统计出各位学生选课的门数。GROUPBY子句还可以与WHERE子句配合使用，WHERE子句先于GROUPBY子句执行，将满足条件的记录保留下来，然后，再按照GROUPBY子句分成小组。若在分组后还要按照一定的条件进行筛选，则需要使用HAVING子句。【例5】在分组查询中使用WHERE条件，查询计算机系的学生学号及平均成绩。程序清单如下：SELECTsno,AVG(score)ASaveragescoreFROMSCWHEREsno=(SELECTsnoFROMSWHEREdept=’计算机’)GROUPBYsnoORDERBYsno【例6】在分组查询中使用HAVING条件，查询平均成绩大于85分的学生学号及平均成绩。程序清单如下：SELECTsno,AVG(score)ASaveragescoreFROMSCGROUPBYsnoHAVINGAVG(score)>85【例7】查询选课在三门以上且各门课程均及格的学生的学号及其总成绩，查询结果按总成绩降序列出。程序清单如下：SELECTsno,SUM(score)AStotalscoreFROMSCWHEREscore>=60GROUPBYsnoHAVINGCOUNT(*)>=3ORDERBYSUM(score)DESC三、反转查询结果当查询的结果不是理想的行列显示时，可以使用反转查询转换。可以利用COUNT函数忽略NULL值的规则，借助于CASEEND表达式实现。【例8】查询每系学生的男生和女生人数得到结果如表9-2所示。要想得到如表9-3所示的表格。查询语句如下：SELECTdept,COUNT(CASEWHENsex=’男’THEN1ELSENULLEND)AS男生人数,COUNT(CASEWHENsex=’女’THEN1ELSENULLEND)AS女生人数FROMstudentGROUPBYdept四、HAVING子句在SQL中增加HAVING子句原因是，WHERE关键字无法与统计函数一起使用。如果只想要看到分组的统计结果，不需要全部的统计信息。【例9】查询信息工程系和机电工程系的学生数。SELECTdept,COUNT(*)AS人数FORMstudentGROUPBYdeptHAVINGdeptIN(‘信息工程’,’机电工程’)【例10】订单表如表9-4所示，有商品编号Id、订单日期OderDate、订单价格OrderPrice和订单的顾客姓名。若希望查找订单总金额少于2000的客户。则使用如下SQL语句：SELECTCustomer,SUM(OrderPrice)FROMOrdersGROUPBYCustomerHAVINGSUM(OrderPrice)<2000若希望查找客户“李其福”或“孔平俊”拥有超过2500的订单总金额。则在SQL语句中增加了WHERE子句：SELECTCustomer,SUM(OrderPrice)FROMOrdersWHERECustomer=李其福ORCustomer=孔平俊GROUPBYCustomerHAVINGSUM(OrderPrice)>2500对于WHERE和HAVING，HAVING子句主要用于筛选组，而WHERE子句用于筛选记录；HAVING子句中可以运用聚合函数，而WHERE子句不能运用聚合函数；HAVING子句不能出现既不被GROUPBY子句包含的字段，又不被聚合函数包含的字段，而WHERE子句中可出现所需的任意字段。通过上例可以知道使用的顺序一般为先WHRER，再GROUPBY分组，最后是HAVING子句分组条件。任务十、连接查询一、等值连接与非等值连接连接条件的一般格式为：［<表名1>.］<列名1><比较运算符>［<表名2>.］<列名2>其中，比较运算符主要有：＝、>、<、>＝、<＝、！＝。当比较运算符为“＝”时，称为等值连接，其他情况为非等值连接。【例1】现有学生表student和选课表sc，分别如表10-1和表10-2所示。查询每个学生及其选修课程的情况。SELECTstudent.*,sc.*FROMstudent,scWHEREstudent.sno=sc.sno查询结果如表10-3所示。自然连接是一种特殊的等值连接，它要求两个关系中进行比较的分量必须是相同的属性组，并且在结果中把重复的属性列去掉，而等值连接并不去掉重复的属性列。【例2】对上例用自然连接完成查询。SELECTstudent.sno,sname,ssex,sage,sdept,cno,scoreFROMstudent,scWHEREstudent.sno=sc.sno查询结果如表10-4所示。【例3】查询张亮同学所选修的课程号为2的课程成绩。SELECTstudent.sno,sname,o,scoreFROMstudent,scWHERE(student.sno=sc.sno)AND(student.sname=张亮)AND(o=2)查询结果为表10-5所示。【例4】现有学生表student和选课表sc，如表10-1和10-2所示，课程表c如表10-6所示。查询所有选课学生的学号、姓名、选课名称及成绩。SELECTstudent.sno,sname,cname,scoreFROMstudent,c,scWHEREstudent.sno=sc.snoANDo=o查询结果为表10-7所示。本例涉及三个表，WHERE子句中有两个连接条件。当有两个以上的表进行连接时，称为多表连接。二、自身连接当一个表与其自已进行连接操作时，称为表的自身连接。要查询的内容均在同一表中，可以将表分别取两个别名，一个是x，一个是y。将x，y中满足查询条件的行连接起来。这实际上是同一表的自身连接。【例5】现有教师工资表TeacherSalary如表10-8所示，查询所有比教师“李强”工资高的教师姓名、性别、工资和李四的工资。程序清单如下：SELECTAS姓名,x.salaryAS工资,y.salaryAS李强工资FROMTeacherSalaryasx，TeacherSalaryasyWHEREx.salary>y.salaryAND=李强需要给表起别名以示区别，由于所有属性都是同名属性，因此必须使用别名前缀。查询结要如表10-9所示。三、外连接普通连接操作只输出满足连接条件的元组；外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一起输出。可以分为左外连接和右外连接，左外连接的结果集包括LEFTOUTER子句或LEFTOUTERJOIN中指定的左表的所有行，而不仅仅是连接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。右外连接RIGHTJOIN或RIGHTOUTERJOIN将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。【例6】在学生表10-1中增加两个学生，分别为“刘艳”和“刘希妍”，如表10-10所示。选课表sc如表10-2所示。左外连接查询学生表student各选课表sc。SELECTstudent.sno,sname,ssex,sage,sdept,cno,scoreFROMstudentLEFTOUTERJIONscON(student.sno=sc.sno)执行结果如表10-11所示。四、嵌套查询一个SELECTFROMWHERE语句称为一个查询块，将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询为嵌套查询。1.带有IN的子查询子查询在上一级查询处理之前求解，子查询的结果用于建立父查询的查找条件。【例7】运用表10-1和表10-2，查询选修课程号为2的学生名单。SELECTsnameFROMstudentWHEREsnoIN（SELECTsnoFROMscWHEREcno=2)【例8】查询与“陈路”同系的学生名单若用分步完成：（1）确定“陈路”所在的系名：SELECTsdeptFROMstudentWHEREsname=陈路结果为“计算机”（2）查找所有在“计算机”学生名单：SELECTsno,sname,sdeptFROMstudentWHEREsdept=计算机结果如表10-12所示。把前面两步合并在一起的查询为：SELECTsno,sname,sdeptFROMstudentWHEREsdeptIN(SELECTsdeptFROMstudentWHEREsname=陈路)本例的IN可以用“=”代替。2.带有比较运算符的子查询【例9】查询超出课程平均成绩的学生号和课程号。SELECTsno,cnoFROMSCxWHEREscore>=(SELECTavg(score)FROMSCyWHEREy.sno=x.sno)3.带有ANY、SOME或ALL的子查询【例10】查询其他系中比计算机系所有学生年龄都要小的学生姓名和年龄。SELECTsname,sageFROMstudentWHEREsage<ALL(SELECTsageFROMstudentWHEREsdept=计算机)ANDsdept<>计算机运用上述的表101，可以查询出两位“张月”和“刘希妍”的符合条件，她们的年龄都比计算机系所有学生的年龄都要小。用聚合函数改写为：SELECTsname,sageFROMstudentWHEREsage<ALL(SELECTMIN(sage)FROMstudentWHEREsdept=计算机)ANDsdept<>计算机4.带EXISTS的子查询不需要返回具体的查询数据，而只关心是否有返回值，即返回True或False。【例11】运用表10-1和10-2，查询没有选修1号课程的学生姓名。SELECTsnameFROMstudentWHERENOTEXISTS(SELECT*FROMscWHEREsno=student.snoANDcno=1)任务十一、组合查询在SQLServer2005中，将多个查询的结果放在一起，以一个查询结果的形式显示出来，则可以使用UNION关键字把多个SELECT连接起来。每个SELECT查询语句应当有相同数量的字段，若字段个数不等，可以使用NULL来代替；每个查询语句中相应的字段的类型必须相互兼容，若不兼容，可使用类型转换函数强制转换字段类型。UNION缺省在合并结果集后消除重复项，UNIONALL指定在合并结果集后保留重复项。UNION结果集中的列名总是等于UNION中第一个SELECT语句中的列名。【例1】网络售书情况表InternetInfo如表11-1所示，实体店面售书表StoreInfo如表11-2所示，现在需要查询所有售书的订单日期。SELECTOrderDateFROMInternetInfoUNIONSELECTOrderDateFROMStoreInfo操作结果如表11-3所示。SELECTOrderDateFROMInternetInfoUNIONALLSELECTOrderDateFROMStoreInfo操作结果如表11-4所示。任务十二、插入、更新在SQLServer2005中，有时为了方便，需要将查询结果保存起来。在SELECT子句的后面，FROM子句的前面加上了一个INTO关键字，关键字的后面紧跟用于保存查询结果的新表的名字。SELECT*(或字段列表)INTO新表名FROM表名一、INSERT语句INSERT语句用于向数据库表或者视图中加入一行数据。INSERT语句的语法形式如下：INSERT［INTO］table_or_view［(column_list)］VALUES(data_values)其中，table_or_view是指要插入新记录的表或视图，column_list是可选项，指定待添加数据的列，VALUES子句指定待添加数据的具体值。列名的排列顺序不一定要和表定义时的顺序一致。但当指定列名表时，VALUES子句值的排列顺序必须和列名表中的列名排列顺序一致，个数相等，数据类型一一对应。在进行数据插入操作时须注意以下2点：（1）在VALUES子句中，必须用逗号将各个数据分开，字符型数据要用单引号括起来。（2）有时并不需要向表中插入完整的行，而需要将数据只插入到几个指定的字段内，则在表名后加上字段列表，且VALUES子句中值的排列顺序要和表中各属性