数据库上课第五讲SQL语言_2(简单查询与连接)

机械自动化学院2014主讲：顾曦电话：[phone]Email：[e-mail]主要内容SQL概述数据定义数据更新1简单查询连接查询嵌套子查询聚合查询集合运算SQL查询一般格式数据更新2视图13:4221.1投影运算1.2选择运算1.3排序运算1.4查询表1.1投影运算SQL查询的基本结构包括3个子句：SELECT子句对应投影运算，指定查询结果中所需要的属性或表达式FROM子句对应笛卡尔积，给出查询所涉及的表，表可以是基本表、视图或查询表WHERE子句对应选择运算，指定查询结果元组所需要满足的选择条件注意：SELECT是必须的，其他是可选的。13:424基本语法为SELECTA1,A2,...,An/*需要查找的属性或表达式*/FROMR1,R2,...,Rm/*查询所涉及的表*/WHEREP/*代表谓词(即选择条件)，如果省略WHERE子句，表示P为真*/SQL的查询结果中允许包含重复元组一般理解的执行过程：首先对表R1,R2,...,Rm执行笛卡尔积然后在笛卡尔积中选择使得谓词P为真的记录再在A1,A2,...,An属性列中进行投影运算，不消除重复元组13:4251.1.1查询指定列选取表中的全部列或指定列，通过SELECT确定要查询的属性[例3.1]查询所有班级的班级编号、班级名称和所属学院SELECTclassNo,className,instituteFROMClass该查询的执行过程是：从Class表中依次取出每个元组对每个元组仅选取classNo、className和institute三个属性的值，形成一个新元组最后将这些新元组组织为一个结果关系输出13:4261.1.2消除重复元组需要消除重复元组，使用DISTINCT关键字[例3.2]查询所有学院的名称。SELECTinstituteFROMClass上述查询不消除重复元组消除重复元组SELECTDISTINCTinstituteFROMClass13:4271.1.3查询所有列两种方法：将所有的列在SELECT子句中列出(可以改变列的显示顺序)；使用*符号，*表示所有属性，按照表定义时的顺序显示所有属性[例3.3]查询所有班级的全部信息。SELECTclassNo,className,classNum,grade,instituteFROMClass或SELECT*FROMClass13:4281.1.4给属性列取别名可为属性列取一个便于理解的列名，如用中文来显示列名为属性列取别名特别适合经过计算的列[例3.4]查询所有班级的所属学院、班级编号和班级名称，要求用中文显示列名可使用AS关键字取别名：SELECTinstituteAS所属学院,classNoAS班级编号,classNameAS班级名称FROMClass13:4291.1.5查询经过计算的列可使用属性、常数、函数和表达式[例3.5]查询每个班级编号、班级名称以及该班级现在为几年级，并将班级编号中大写字母改为小写字母输出SELECTlower(classNo)班级编号,className,year(curdate())-gradeAS年级FROMClass函数lower()将大写字母改为小写字母函数curdate()获取当前系统的日期其他函数：now（），getdate()函数year()提取日期中的年份13:42101.2选择运算（WHERE子句）WHERE子句可实现关系代数中的选择运算WHERE常用的查询条件有：比较运算：、=、、=、=、(或!=)范围查询：BETWEEN...AND集合查询：IN空值查询：ISnull字符匹配查询：LIKE逻辑查询：AND、OR、NOT13:42111.2.1比较运算比较运算符、=、、=、=、(或!=)[例3.6]查询2007级的班级编号、班级名称和所属学院。SELECTclassNo,className,instituteFROMClassWHEREgrade=200713:4212该查询的执行过程可能有多种方法：全表扫描法依次取出Class表中的每个元组判断该元组的grade属性值是否等于2007若是则将该元组的班级编号、班级名称和所属学院属性取出，形成一个新元组最后将所有新元组组织为一个结果关系输出该方法适用于小表，或者该表未在grade属性列上建索引索引搜索法如果该表在grade属性列上建有索引，且满足条件的记录不多，则可使用索引搜索法来检索数据具体使用何种方法由数据库管理系统的查询优化器来选择，详见第8章内容13:4213在学生Student表中查询年龄大于或等于19岁的同学学号、姓名和出生日期。SELECTstudentNo,studentName,birthdayFROMStudentWHEREyear(curdate())-year(birthday)=19[例3.7]13:4214.1.2.2范围查询BETWEEN...AND用于查询属性值在某一个范围内的元组NOTBETWEEN...AND用于查询属性值不在某一个范围内的元组BETWEEN后是属性的下限值，AND后是属性的上限值[例3.8]在选课Score表中查询成绩在80～90分之间的同学学号、课程号和相应成绩SELECTstudentNo,courseNo,scoreFROMScoreWHEREscoreBETWEEN80AND90该查询也可以使用逻辑运算AND实现，见例3.2213:4215[例3.9]在选课Score表中查询成绩不在80～90分之间的同学学号、课程号和相应成绩。SELECTstudentNo,courseNo,scoreFROMScoreWHEREscoreNOTBETWEEN80AND90该查询也可以使用逻辑运算OR实现，见例3.2313:42161.2.3集合查询IN用于查询属性值在某个集合内的元组NOTIN用于查询属性值不在某个集合内的元组IN后面是集合，可以是具体的集合，也可以是查询出来的元组集合(该部分内容详见3.4节的内容)。[例3.10]在选课Score表中查询选修了“001”、“005”或“003”课程的同学学号、课程号和相应成绩。SELECTstudentNo,courseNo,scoreFROMScoreWHEREcourseNoIN('001','005','003')该查询也可以使用逻辑运算OR实现，见例3.1913:4217[例3.11]在学生Student表中查询籍贯不是“南昌”或“上海”的同学姓名、籍贯和所属班级编号。SELECTstudentName,native,classNoFROMStudentWHEREnativeNOTIN('南昌','上海')该查询也可以使用逻辑运算AND实现，见例3.2113:42181.2.4空值查询空值表示未知或不确定的值，空值表示为nullISnull用于查询属性值为空值ISNOTnull用于查询属性值不为空值IS不能用“=”替代[例3.12]在课程Course表中查询先修课程为空值的课程信息。SELECT*FROMCourseWHEREpriorCourseISNULL13:4219[例3.13]在课程Course表中查询有先修课程的课程信息。SELECT*FROMCourseWHEREpriorCourseISNOTNULL13:42201.2.5字符匹配查询LIKE用于字符匹配查询，语法格式为：[NOT]LIKE匹配字符串[ESCAPE换码字符]查询的含义是：如果在LIKE前没有NOT，则查询指定的属性列值与匹配字符串相匹配的元组；如果在LIKE前有NOT，则查询指定的属性列值不与匹配字符串相匹配的元组。13:4221匹配字符串可以是一个具体的字符串，也可以包括通配符%和_%表示任意长度的字符串ab%，表示所有以ab开头的任意长度的字符串；zhang%ab，表示以zhang开头，以ab结束，中间可以是任意个字符的字符串。符号_(下划线)表示任意一个字符ab_，表示所有以ab开头的3个字符的字符串，其中第3个字符为任意字符；a__b表示所有以a开头，以b结束的4个字符的字符串，且第2、3个字符为任意字符。13:4222[例3.14]在班级Class表中查询班级名称中含有会计的班级信息SELECT*FROMClassWHEREclassNameLIKE'%会计%'注意：字符串必须用一对引号括起来可单可双，小心匹配。13:4223[例3.15]在学生Student表中查询所有姓王且全名为3个汉字的同学学号和姓名SELECTstudentNo,studentNameFROMStudentWHEREstudentNameLIKE'王__'注意：在中文SQL-Server中，如果匹配字符串为汉字，则一个下划线代表一个汉字；如果是西文，则一个下划线代表一个字符。13:4224[例3.16]在学生Student表中查询名字中不含有“福”的同学学号和姓名。SELECTstudentNo,studentNameFROMStudentWHEREstudentNameNOTLIKE'%福%'13:4225[例3.17]在学生Student表中查询蒙古族的同学学号和姓名SELECTstudentNo,studentNameFROMStudentWHEREnationLIKE'蒙古族'注意：如果匹配字符串中不含有%和_，则LIKE与比较运算符“=”的查询结果一样SELECTstudentNo,studentNameFROMStudentWHEREnation='蒙古族'13:4226转义如果查询字串中本身要包含%和_，必须对通配符进行转义处理。mysql转义字符是‘\’。[例3.18]在班级Class表中查询班级名称中含有“08_”符号的班级名称SELECTclassNameFROMClassWHEREclassNameLIKE'%08\%%'紧跟在\符号后的%不是通配符，而是普通的用户要查询的符号13:42271.2.6逻辑查询SQL提供AND、OR和NOT逻辑运算符分别实现逻辑与、逻辑或和逻辑非运算[例3.19]在选课Score表中查询选修了“001”、“005”或“003”课程的同学学号、课程号和相应成绩SELECTstudentNo,courseNo,scoreFROMScoreWHEREcourseNo='001'ORcourseNo='005'ORcourseNo='003'在例3.10中使用的是集合运算，本例中采用逻辑“或”运算13:4228[例3.20]在Student表中查询1991年出生且籍贯为“汉族”的同学学号、姓名、出生日期。SELECTstudentNo,studentName,birthdayFROMStudentWHEREyear(birthday)=1991ANDnation='汉族'13:4229注意：在逻辑运算中，不可以对同一个属性进行逻辑“与”的等值运算例：在选课Score表中查询同时选修了“001”和“002”课程的同学的选课信息，如下查询是错误的，得不到结果：SELECT*FROMScoreWHEREcourseNo='001'ANDcourseNo='002'要实现该查询，需要使用连接运算或嵌套子查询通过连接运算表示该查询，参见例3.29、例3.31通过嵌套子查询，参见例3.39、例3.4013:4230[例3.21]在Student表中查询籍贯不是“南昌”或“上海”的同学姓名、籍贯和所属班级编号。SELECTstudentName,native,classNoFROMStudentWHEREnative!='南昌'ANDnative!='上海'[例3.