第4章-数据操作

第4章数据操作4.1数据查询功能4.2数据更改功能4.3视图4.1数据查询功能4.1.1查询语句的基本结构4.1.2简单查询4.1.3多表连接查询4.1.4使用TOP限制结果集4.1.5子查询查询语句基本格式SELECT目标列名序列--需要哪些列FROM数据源--来自于哪些表[WHERE检索条件]--根据什么条件[GROUPBY分组依据列][HAVING组提取条件][ORDERBY排序依据列]1.选择表中若干列查询表中用户感兴趣的部分属性列。例1：查询全体学生的学号与姓名。SELECTSno，SnameFROMStudent例2：查询全体学生的姓名、学号和所在系。SELECTSname，Sno，SdeptFROMStudent查询全部列例3．查询全体学生的记录SELECTSno，Sname，Ssex,Sage,SdeptFROMStudent等价于：SELECT*FROMStudent查询经过计算的列例4．查询全体学生的姓名及其出生年份。SELECTSname，2009-SageFROMStudent例5．含字符串常量的列：查询全体学生的姓名和出生年份，并在出生年份列前加一列，此列的每行数据均为“出生年份”常量值。SELECTSname，‘出生年份’,2009-SageFROMStudent消除取值相同的记录例6．在修课表中查询有哪些学生修了课程，要求列出学生的学号。SELECTSnoFROMSC结果中有重复的行。用DISTINCT关键字可以去掉结果中的重复行。DISTINCT关键字放在SELECT词的后边、目标列名序列的前边。SELECTDISTINCTSnoFROMSC2.查询满足条件的元组查询条件谓词比较运算符=,,=,,=,（或!=）NOT+比较运算符确定范围BETWEEN…AND,NOTBETWEEN…AND确定集合IN,NOTIN字符匹配LIKE,NOTLIKE空值ISNULL,ISNOTNULL逻辑谓词）AND,OR比较大小例7．查询计算机系全体学生的姓名。SELECTSnameFROMStudentWHERESdept='计算机系'例8．查询年龄在20岁以下的学生的姓名及年龄。SELECTSname,SageFROMStudentWHERESage20例9．查询考试成绩有不及格的学生的学号SELECTDISTINCTSnoFROMSCWHEREGrade60确定范围用BETWEEN…AND和NOTBETWEEN…AND是逻辑运算符，可以用来查找属性值在或不在指定范围内的元组，其中BETWEEN后边指定范围的下限，AND后边指定范围的上限。BETWEEN…AND…的格式为：列名|表达式[NOT]BETWEEN下限值AND上限值如果列或表达式的值在（或不在）下限值和上限值范围内，则结果为True，表明此记录符合查询条件。BETWEEN…AND…包括边界值。示例例10．查询年龄在20～23岁之间的学生的姓名、所在系和年龄。SELECTSname,Sdept,SageFROMStudentWHERESageBETWEEN20AND23等价于：SELECTSname,Sdept,SageFROMStudentWHERESage=20ANDSage=23示例例11．查询年龄不在20～23之间的学生姓名、所在系和年龄。SELECTSname,Sdept,SageFROMStudentWHERESageNOTBETWEEN20AND23等价于：SELECTSname,Sdept,SageFROMStudentWHERESage20ORSage23示例例12对于日期类型的数据也可以使用基于范围的查找。查询1991年6月出版的图书信息：SELECTtitle_id,type,price,pubdateFROMtitlesWHEREpubdateBETWEEN'1991/6/1'AND'1991/6/30'确定集合用来查找属性值属于指定集合的元组。格式为：列名[NOT]IN（常量1,常量2,…常量n）IN：当列中的值与IN中的某个常量值相等时，则结果为True，表明此记录为符合查询条件的记录。NOTIN：当列中的值与某个常量值相等时，结果为False，表明此记录为不符合查询条件的记录。示例例13查询信息管理系、通信工程系和计算机系学生的姓名和性别。SELECTSname,SsexFROMStudentWHERESdeptIN(‘信息管理系’,‘电子商务系','计算机系')等价于：SELECTSname,SsexFROMStudentWHERESdept='信息管理系'ORSdept=‘电子商务系'ORSdept='计算机系'示例（续）例14查询既不是信息管理系、通信工程系，也不是计算机系学生的姓名和性别。SELECTSname,SsexFROMStudentWHERESdeptNOTIN(‘信息管理系’,‘电子商务系','计算机系')等价于：SELECTSname,SsexFROMStudentWHERESdept!='信息管理系'ANDSdept!=‘电子商务系'ANDSdept!='计算机系'字符匹配一般形式为：列名[NOT]LIKE匹配串匹配串中可包含如下四种通配符：_：匹配任意一个字符；%：匹配0个或多个字符；[]：匹配[]中的任意一个字符(若要比较的字符是连续的，则可以用连字符“-”表达)；[^]：不匹配[]中的任意一个字符。示例例15．查询学生表中姓‘张’的学生的详细信息。SELECT*FROMStudentWHERESnameLIKE'张%'例16．查询学生表中姓‘张’、姓‘李’和姓‘刘’的学生的情况。SELECT*FROMStudentWHERESnameLIKE'[张李刘]%'示例（续）例17查询Student表中名字的第2个字为“小”或“大”的学生的姓名和学号。SELECTSname,SnoFROMStudentWHERESnameLIKE'_[小大]%'示例（续）例18查询Student表中所有不姓“刘”的学生。SELECTSnameFROMStudentWHERESnameNOTLIKE'刘%'例19从Student表中查询学号的最后一位不是2、3、5的学生信息。SELECT*FROMStudentWHERESnoLIKE'%[^235]'涉及空值的查询空值（NULL）在数据库中表示不确定的值。例如，学生选修课程后还没有考试时，这些学生有选课记录，但没有考试成绩，因此考试成绩为空值。判断某个值是否为NULL值，不能使用普通的比较运算符。判断取值为空的语句格式为：列名ISNULL判断取值不为空的语句格式为：列名ISNOTNULL示例例20查询还没有考试的学生的学号和相应的课程号。SELECTSno,CnoFROMSCWHEREGradeISNULL查询结果如图4-20所示。例21查询所有已经考试了的学生的学号和课程号。SELECTSno,CnoFROMSCWHEREGradeISNOTNULL多重条件查询在WHERE子句中可以使用逻辑运算符AND和OR来组成多条件查询。使用AND谓词的语法格式如下：布尔表达式1AND布尔表达式2AND…AND布尔表达式n只有当全部的布尔表达式均为真时，整个表达式的结果才为真，只要有一个布尔表达式的结果为假，则整个表达式结果即为假。多重条件查询（续）使用OR谓词的语法格式如下。布尔表达式1OR布尔表达式2OR…OR布尔表达式n表示只要其中一个布尔表达式为真，则整个表达式的结果即为真；只有当全部布尔表达式的结果均为假时，整个表达式结果才为假。示例例22查询计算机系年龄在20岁以下的学生姓名SELECTSnameFROMStudentWHERESdept='计算机系'ANDSage20示例（续）例23查询计算机系和信息管理系学生中年龄在18～20的学生的学号、姓名、所在系和年龄。SELECTSno,Sname,Sdept,SageFROMStudentWHERE(Sdept='计算机系'ORSdept='信息管理系')ANDSagebetween18and20也可写为：SELECTSno,Sname,Sdept,SageFROMStudentWHERESdeptin('计算机系','信息管理系')ANDSagebetween18and203.对查询结果集进行排序可对查询结果进行排序。排序子句为：ORDERBY列名[ASC|DESC][,列名…]说明：按列名进行升序（ASC）或降序（DESC）排序。示例例24将学生按年龄的升序排序。SELECT*FROMStudentORDERBYSage例25查询选修了“C002”号课程的学生的学号及其成绩，查询结果按成绩降序排列。SELECTSno,GradeFROMSCWHERECno='C002'ORDERBYGradeDESC示例（续）例26查询全体学生的信息，查询结果按所在系的系名升序排列，同一系的学生按年龄降序排列。SELECT*FROMStudentORDERBYSdept,SageDESC4.使用统计函数汇总数据SQL提供的统计函数有：COUNT(*)：统计表中元组个数；COUNT([DISTINCT]列名)：统计本列列值个数；SUM(列名)：计算列值总和；AVG(列名)：计算列值平均值；MAX(列名)：求列值最大值；MIN(列名)：求列值最小值。上述函数中除COUNT(*)外，其他函数在计算过程中均忽略NULL值。示例例27统计学生总人数。SELECTCOUNT(*)FROMStudent例28统计选修了课程的学生的人数。SELECTCOUNT(DISTINCTSno)FROMSC例29计算学号为“0611101”的学生的考试总成绩之和。SELECTSUM(Grade)FROMSCWHERESno=‘0611101'示例（续）例30计算“C001”课程的学生的考试平均成绩。图4-25例31查询结果SELECTAVG(Grade)FROMSCWHERECno='C001'例31查询选修了“C001”号课程的学生的最高分和最低分。SELECTMAX(Grade)最高分,MIN(Grade)最低分FROMSCWHERECno='C001'注意统计函数不能出现在WHERE子句中。例如，查询年龄最大的学生的姓名，如下写法是错误的：SELECTSnameFROMStudentWHERESage=MAX(Sage)5.对查询结果进行分组计算作用：可以控制计算的级别：对全表还是对一组。目的：细化计算函数的作用对象。分组语句的一般形式：[GROUPBY分组条件][HAVING组过滤条件]使用GROUPBY示例例32统计每门课程的选课人数，列出课程号和人数。SELECTCnoas课程号,COUNT(Sno)as选课人数FROMSCGROUPBYCno该语句首先对查询结果按Cno的值分组，所有具有相同Cno值的元组归为一组，然后再对每一组使用COUNT函数进行计算，求得每组的学生人数。示例（续）例33查询每名学生的选课门数和平均成绩。SELECTSno学号,COUNT(*)选课门数,AVG(Grade)平均成绩FROMSCGROUPBYSno注意GROUPBY子句中的分组依据列必须是表中存在的列名，不能使用AS子句指派的结果集列的别名。带有GROUPBY子句的SELECT语句的查询列表中只能出现分组依据列或统计函数，因为分组后每个组只返回一行结果。示例（续）例34统计每个系的学生人数和平均年龄。SELECTSdept,COUNT(*)AS学生人数,AVG(Sage)AS平均年龄FROMStudentGROUPBYSdept示例（续）例35带WHERE子