第4章数据操作语句(SQL server2005数据库系统概论PPT)

数据库原理与应用教程国家“十一五”规划教材第4章数据操作•4.1数据查询功能•4.2数据更改功能4.1数据查询功能•4.1.1查询语句的基本结构•4.1.2简单查询•4.1.3多表连接查询•4.1.4子查询查询语句基本格式SELECT目标列名序列--需要哪些列FROM数据源--来自于哪些表[WHERE检索条件]--根据什么条件[GROUPBY分组依据列][HAVING组提取条件][ORDERBY排序依据列]4.1.2简单查询1.选择表中若干列1.查询指定的列•查询表中用户感兴趣的部分属性列。•例1：查询全体学生的学号与姓名。SELECTSno,SnameFROMStudent例2.查询全体学生的姓名、学号、所在系SELECTSname,Sno,SdeptFROMStudent2.查询全部列•例3．查询全体学生的记录SELECTSno,Sname,Ssex,Sage,SdeptFROMStudent•等价于：SELECT*FROMStudent3.查询经过计算的列•例4．查询全体学生的姓名及其出生年份。SELECTSname,2010-SageFROMStudent常量列•例5．查询全体学生的姓名和出生年份所在系，并在出生年份列前加入一个列，此列的每行数据均为“出生年份”常量值。SELECTSname,'出生年份:',2010-SageFROMStudent改变列标题•语法：列名|表达式[AS]列标题•或：列标题＝列名|表达式•例：SELECTSname姓名,'YearofBirth'出生年份,2010-Sage年份，FROMStudent4.1.2简单查询2.选择表中若干元组1.消除取值相同的行例6．查询选修了课程的学生的学号SELECTSnoFROMSC有重复行！要去掉结果表中的重复行，可用DISTINCTSELECTDISTINCTSnoFROMSC2.查询满足条件的元组查询条件谓词比较运算符=,,=,,=,（或!=）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，表明此记录符合查询条件。示例•例10．查询年龄在20～23岁之间的学生的姓名、所在系和年龄。SELECTSname,Sdept,SageFROMStudentWHERESageBETWEEN20AND23•例11．查询年龄不在20～23之间的学生姓名、所在系和年龄。SELECTSname,Sdept,SageFROMStudentWHERESageNOTBETWEEN20AND23确定集合•使用IN运算符。•用来查找属性值属于指定集合的元组。•格式为：列名[NOT]IN(常量1,常量2,…常量n)•当列中的值与IN中的某个常量值相等时，则结果为True，表明此记录为符合查询条件的记录；•NOTIN：当列中的值与某个常量值相同时，则结果为False，表明此记录为不符合查询条件的记录示例•例12．查询信息系、数学系和计算机系学生的姓名和性别。SELECTSname,SsexFROMStudentWHERESdeptIN('信息系','数学系','计算机系')•例13．查询既不是信息系、数学系，也不是计算机系学生的姓名和性别。SELECTSname,SsexFROMStudentWHERESdeptNOTIN('信息系','数学系','计算机系')字符匹配•使用LIKE运算符•一般形式为：列名[NOT]LIKE匹配串•匹配串中可包含如下四种通配符：•_：匹配任意一个字符；•%：匹配0个或多个字符；•[]：匹配[]中的任意一个字符；对于连续字母的匹配，例如匹配[abcd]，可简写为[a-d]•[^]：不匹配[]中的任意一个字符示例•例14．查询姓‘张’的学生的详细信息。SELECT*FROMStudentWHERESnameLIKE'张%'•例15．查询学生表中姓‘张’、‘李’和‘刘’的学生的情况。SELECT*FROMStudentWHERESnameLIKE'[张李刘]%'•例16．查询名字中第2个字为‘小’或‘大’的学生的姓名和学号。SELECTSname,SnoFROMStudentWHERESnameLIKE'_[小大]%'示例（续）•例17．查询所有不姓“王”也不姓“张”的学生姓名SELECTSnameFROMStudentWHERESnameNOTLIKE‘[王张]%'•或者：SELECTSnameFROMStudentWHERESnameLIKE'[^王张]%'•或者：SELECTSnameFROMStudentWHERESnameNOTLIKE'王%'ANDSnameNOTLIKE'张%'示例（续）•例18．查询姓“王”且名字是2个字的学生姓名。SELECTSnameFROMStudentWHERESnameLIKE'王_'示例（续）•例19．查询姓王且名字是3个字的学生姓名SELECTSnameFROMStudentWHERESnameLIKE'王__'注意：尾随空格的处理。SELECTSnameFROMStudentWHERErtrim(Sname)LIKE'王__'涉及空值的查询•空值（NULL）在数据库中表示不确定的值。•例如，学生选修课程后还没有考试时，这些学生有选课记录，但没有考试成绩，因此考试成绩为空值。•判断某个值是否为NULL值，不能使用普通的比较运算符。•判断取值为空的语句格式为：列名ISNULL•判断取值不为空的语句格式为：列名ISNOTNULL示例•例20．查询没有考试成绩的学生的学号和相应的课程号。SELECTSno,CnoFROMSCWHEREGradeISNULL•例21．查询所有有考试成绩的学生的学号和课程号。SELECTSno,CnoFROMSCWHEREGradeISNOTNULL多重条件查询•在WHERE子句中可以使用逻辑运算符AND和OR来组成多条件查询。•用AND连接的条件表示必须全部满足所有的条件的结果才为True；•用OR连接的条件表示只要满足其中一个条件结果即为True。•例21．查询计算机系年龄在20岁以下的学生姓名。SELECTSnameFROMStudentWHERESdept=‘计算机系'ANDSage20示例（续）•例23．查询计算机系和信息系年龄大于等于20岁的学生姓名、所在系和年龄。SELECTSname,Sdept,SageFROMStudentWHERE(Sdept='计算机系'ORSdept='信息系')ANDSage=20或：SELECTSname,Sdept,SageFROMStudentWHERESdeptIN('计算机系','信息系')ANDSage=204.1.2简单查询##3.对查询结果进行排序对查询结果进行排序•可对查询结果进行排序。•排序子句为：ORDERBY列名[ASC|DESC][,列名…]•说明：按列名进行升序（ASC）或降序（DESC）排序。示例•例22．将学生按年龄的升序排序。SELECT*FROMStudentORDERBYSage•例23．查询选修了‘c02’号课程的学生的学号及其成绩，查询结果按成绩降序排列。SELECTSno,GradeFROMSCWHERECno='c02'ORDERBYGradeDESC•例24．查询全体学生的信息，查询结果按所在系的系名升序排列，同一系的学生按年龄降序排列。SELECT*FROMStudentORDERBYSdept,SageDESC4.1.2简单查询4.使用计算函数汇总数据使用计算函数汇总数据•SQL提供的计算函数有：•COUNT（*）：统计表中元组个数；•COUNT（[DISTINCT]列名）：统计本列列值个数；•SUM（[DISTINCT]列名）：计算列值总和；•AVG（[DISTINCT]列名）：计算列值平均值；•MAX（[DISTINCT]列名）：求列值最大值；•MIN（[DISTINCT]列名）：求列值最小值。•上述函数中除COUNT（*）外，其他函数在计算过程中均忽略NULL值。示例•例25．统计学生总人数。SELECTCOUNT(*)FROMStudent•例26．统计选修了课程的学生的人数。SELECTCOUNT(DISTINCTSno)FROMSC•例27．计算9512101号学生的考试总成绩之和。SELECTSUM(Grade)FROMSCWHERESno='9512101'示例（续）•例28．计算’C01’号课程学生的考试平均成绩。SELECTAVG(Grade)FROMSCWHERECno='C01'•例29．查询选修了’C01’号课程的学生的最高分和最低分。SELECTMAX(Grade),MIN(Grade)FROMSCWHERECno='C01'•注意：计算函数不能出现在WHERE子句中4.1.2简单查询5.对查询结果进行分组计算对查询结果进行分组计算•作用：可以控制计算的级别：对全表还是对一组。•目的：细化计算函数的作用对象。•分组语句的一般形式：[GROUPBY分组条件][HAVING组过滤条件]1.使用GROUPBY•例30．统计每门课程的选课人数，列出课程号和人数。SELECTCnoas课程号,COUNT(Sno)as选课人数FROMSCGROUPBYCno•对查询结果按Cno的值分组，所有具有相同Cno值的元组为一组，然后再对每一组使用COUNT计算，求得每组的学生人数。SnoCnoGrade951201C0180951201C0278951202C0190952103C0288952103C0185952103C0391952103C0474CnoCount(Sno)C013C022C031C041SnoCnoGrade951201C0180951202C0190952103C0185951201C0278952103C0288952103C0391952103C0474•例31．查询每名学生的选课门数和平均成绩。SELECTSnoas学号,COUNT(*)as选课门数,AVG(Grade)as平均成绩FROMSCGROUPBYSno2.使用HAVING•HAVING用于对分组自身进行限制，它有点象WHERE子句，但它用于组而不是对单个记录。•例32．查询修了3门以上课程的学生的学号SELECTSnoFROMSCGROUPBYSnoHAVINGCOUNT(*)3示例•例33．查询修课门数等于或大于4门的学生的平均成绩和选课门数。SELECTSno,AVG(Grade)平均成绩,COUNT(*)修课门数FROMSCGROUPBYSnoHAVINGCOUNT(*)=44.1.3多表连接查询•若一个查询同时涉及两个或两个以上的表，则称之为连接查询。•连接查询是关系数据库中最主要的查询•连接查询包括内连接、外连接和交叉连接等。连接基础知识•连接查询中用于连接两个表的条件称为连接条件或连接谓词。•一般格式为：[表名1.][列名1]