数据库语言SQL――数据查询1

第4章数据查询4.1基本查询4.2嵌套查询4.3连接查询在“学生学籍数据库”中创建了以下三个关系模式：学生信息表（学号，姓名，性别，年龄，系别）课程信息表(课程号，课程名，学分)选修信息表（学号，课程号，成绩）SQL数据查询语句是SELECT语句语法：SELECT目标列名FROM数据源表[WHERE查询条件][GROUPBY分组列][HAVING组选择条件][ORDERBY排序列]SELECT语句说明SELECT语句中必须有：SELECT子句、FROM子句其余子句可选：WHERE子句、GROUPBY子句、HAVING子句、ORDERBY子句SELECT语句说明(1)SELECT目标列名，称为SELECT子句。用于指定整个查询结果表中包含的列。假定已经执行完FROM、WHERE、GROUPBY、HAVING子句，SELECT子句指定的目标列组成表就为整个查询的结果表。(2)FROM数据源表，称为FROM子句。是整个查询语句的数据来源，通常称为数据源表。单表查询单表查询指的是在一个源表中查找所需的数据。下面首先说明单表查询SELECT语句中的各个子句3.3.2单表查询1.SELECT子句（1）查询表中若干列在SELECT子句的目标列名表中指定整个查询结果表中出现的若干个列名，各列名之间用逗号分隔。【例3.3.1】查询全体学生的学号与姓名SELECT学号,姓名FROMStudents（2）查询表中所有的列可以用*来代替表的所有列。【例3.3.2】查询全部课程的基本信息Select*from表名Select*from课程信息表（2）选择表中所有列可以用*来代替表的所有列。【例】查询全体学生的学号、姓名、性别、年龄、所在系。SELECT*FROM学生信息表（3）查询经过计算的值例:查询全体学生的姓名及其出生年份。SELECT姓名，2011-年龄FROM学生信息表输出结果：……（4）设置列的别名所谓别名，就是给另一个名字，主要是为了方便阅读。设置列别名的方法有：原列名AS列别名【例】查询全体学生的学号、姓名，并为原来的中文列名设置英文别名：Select学号assno,姓名assname,年龄assagefrom学生信息表例:查询全体学生的姓名及其出生年份。输出结果：SELECT姓名,2010-年龄as出生年份FROM学生信息表……（4）使用DISTINCT消除结果表中完全重复的行【例】显示所有选课学生的学号，并去掉重复行。解：SELECTDISTINCT学号FROM选修信息表与DISTINCT相反的是ALL，ALL表示保留结果表中的重复行。默认情况下是ALL，表示保留重复行。练习1：1、查询全体课程的课程号、课程名，并为其设置别名CNO,CNAME。3、查询选修了课程的学生的学号。（去掉结果中重复的行）3.WHERE子句WHERE查询条件查询条件中常用的运算符：比较运算符和逻辑运算符。比较运算符用于比较两个数值之间的大小是否相等。常用的比较运算符有：=、、、=、=、!=或、!、!共9种。逻辑运算符主要有:范围比较运算符：BETWEEN…AND…，NOTBETWEEN…AND集合比较运算符：IN，NOTIN字符匹配运算符：LIKE，NOTLIKE空值比较运算符：ISNULL，ISNOTNULL条件连接运算符：AND，OR，NOT（1）基于比较运算符的查询【例】查询学生选课成绩大于80分的学生学号、课程号、成绩。解：SELECT*FROM选修信息表WHERE成绩80练习2：1、查询“系别”不是“英语”的所有学生的基本信息。2、查询所有年龄在20岁以上的学生姓名、系别及其年龄。（2）基于BETWEEN…AND的查询基本格式：列名BETWEEN下限值AND上限值等价于：列名=下限值AND列名=上限值【例】查询学生选课成绩在80～90分之间的学生学号、课程号、成绩。解：SELECT*FROM选修信息表WHERE成绩BETWEEN80AND90等价于：SELECT*FROM选修信息表WHERE成绩=80AND成绩=90（3）基于IN的查询IN用于测试一个列值是否与常量表中的任何一个值相等。IN条件表示格式为：列名IN(常量1,常量2,…常量n)当列值与IN中的任一常量值相等时，则条件为TRUE，否则为FALSE。【例】查询数学系、计算机系、艺术系学生的学号、姓名。解：SELECT姓名,学号FROM学生信息表WHERE系别IN(‘数学’,‘计算机’,‘艺术')等价于：SELECT学号,姓名FROM学生信息表WHERE系别='数学'OR系别='计算机'OR系别='艺术'（4）基于LIKE的查询LIKE用于测试一个字符串是否与给定的模式匹配。所谓模式是一种特殊的字符串，其中可以包含普通字符，也可以包含特殊意义的字符，通常叫通配符。LIKE运算符的一般形式为：列名LIKE模式串模式串中可包含如下四种通配符：（1）_：匹配任意一个字符。如'_u_'表示第二个字符为u，第一、第三个字符为任意字符的字符串。（2）%：匹配多个字符。如‘S%’表示以S开头的字符串。（3）[]：匹配[]中的任意一个字符，如[SDJ]。（4）[^]：不匹配[]中的任意一个字符，如[^SDJ]。可以用LIKE来实现模糊查询【例】查找姓名的第二个字符是晓并且只有三个字符的学生的学号、姓名。解：SELECT学号,姓名FROM学生信息表WHERE姓名LIKE‘_晓_'【例】查找姓名以李开头的所有学生的学号、姓名。解：SELECT学号,姓名FROM学生信息表WHERE姓名LIKE‘李%'【例】查找姓名以李、张或王开头的所有学生的学号、姓名。解：SELECT学号,姓名FROM学生信息表WHERE姓名LIKE‘[李张王]%'（5）基于NULL空值的查询空值是尚未确定或不确定的值。判断某列值是否为NULL值只能使用专门判断空值的子句，语句格式如下：判断列值为空的语句格式为：列名ISNULL判断列值不为空的语句格式为：列名ISNOTNULL【例】查询无考试成绩的学生的学号和相应的课程号。解：SELECT学号,课程号FROM选修信息表WHERE成绩ISNULL不等价于：SELECT学号,课程号FROM选修信息表WHERE成绩=0（6）基于多个条件的查询可以使用AND、OR逻辑谓词来连接多个条件，构成一个复杂的查询条件。使用格式为：条件1AND条件2AND…条件n或条件1OR条件2OR…条件n【例】查询计算机系年龄在18岁以上的学生学号、姓名解：SELECT学号,姓名FROM学生信息表WHERE系别=‘计算机’AND年龄18【例】求选修了C1课程或C2课程的学生学号、成绩。解：SELECT学号,成绩FROM选修信息表WHERE课程号=‘C1’OR课程号='C2'练习3：1、查询既不是艺术系、数学系，也不是旅游系的学生的姓名和性别。2、查询所有不姓张学生的姓名、学号、和性别。3、查询年龄在17~19岁（包括17岁和19岁）之间的学生的姓名、系别和年龄。4、查询选修’1’号或’2’号课程的学生的学号、课程号、成绩。对查询结果排序●使用ORDERBY子句可以按一个或多个属性列排序升序：ASC；降序：DESC；默认值为升序注：DESC是descend降序意思;ASC是ascend升序的意思对查询结果排序（续）按一个属性列排序查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。SELECT学号,成绩FROM选修信息表WHERE课程号='3'ORDERBY成绩DESC对查询结果排序（续）按多个属性列排序查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。SELECT*FROM学生信息表ORDERBY系别,年龄DESC练习4⑴查询选修信息表的基本信息,结果按学号升序排列,同一学号按课程号降序排列.⑵查询学生信息表的学号、姓名、年龄，结果按年龄升序排列，同一年龄学生按学号升序排列。（7）使用统计函数的查询统计函数也称为集合函数或聚集函数，其作用是对一组值进行计算并返回一个值。函数表达式MAX（列名）求列中的最大值MIN（列名）求列中的最小值AVG（列名）求一列数据的平均值SUM（列名）求一列数据的和COUNT（〈*〉）统计查询的行数常用函数【例】求选修了1号课程的学生的平均成绩。SELECTAVG(成绩)AS’平均成绩’FROM选修信息表WHERE课程号='1’【例】选修了1号课程的学生的最高分SELECTMAX(成绩)AS’最高分’FROM选修信息表WHERE课程号='1’【例】求学生的总人数SELECTCOUNT(*)AS‘学生的总人数’FROM学生信息表等价于：SELECTCOUNT(学号)AS‘学生的总人数’FROM学生信息表【例】查询选修了课程的学生人数。SELECTCOUNT(DISTINCT学号)FROM选修信息表注：用DISTINCT以避免重复计算学生人数4.GROUPBY子句有时我们需要把FROM、WHERE子句产生的表按某种原则分成若干组，然后再对每个组进行统计，一组形成一行，最后把所有这些行组成一个表GROUPBY子句在WHERE子句后边。一般形式为：GROUPBY列名[，…n]【例】求选修每门课程的学生人数。解：SELECT课程号,COUNT(学号)AS‘选修人数’FROM选修信息表GROUPBY课程号注意：1、select字句中如果有统计函数，则不允许出现其他的列名，除非是groupby字句的列名总之：包含GROUPBY子句的查询语句中，SELECT子句的列名，要么是统计函数，要么是包含在GROUPBY子句中的列名下列语句是错误的：Select学号，avg（成绩）from选修信息表SELECT学号,课程号,COUNT(学号)AS'选修人数'FROM选修信息表GROUPBY课程号5.HAVING子句HAVING子句指定GROUPBY生成的组表的选择条件。它的一般形式为：HAVING组选择条件HAVING子句必须与GROUPBY子句一起使用。WHERE子句用来筛选FROM子句中指定的操作所产生的行。GROUPBY子句用来分组WHERE子句的输出。HAVING子句用来指定选择组的条件，满足条件的组被选。【例】查询选修课程大于等于2门课的学生的学号、平均成绩、选修的门数。解：SELECT学号,AVG(成绩)AS‘平均成绩’,COUNT(*)AS‘选修门数’FROM选修信息表GROUPBY学号HAVINGCOUNT(*)=2【例】查询学生的学号、平均成绩、选修的门数。解：SELECT学号,AVG(成绩)AS‘平均成绩’,COUNT(*)AS‘选修门数’FROM选修信息表GROUPBY学号注意：where子句作用于基本表，从中选出满足条件的行，having字句作用于组，从中选出满足条件的组例题查询有3门以上课程是90分以上的学生的学号及（90分以上的）课程数SELECT学号,COUNT(课程号)FROM选修信息表WHERE成绩=90GROUPBY学号HAVINGCOUNT(课程号)=3练习5：1.列出选修人数小于5的课程号和实际选修人数2.平均成绩大于80的课程号和实际平均成绩综合练习1.查询学生信息表的基本信息,结果按年龄降序排列,同一年龄按学号升序排列.2.统计每位学生的选课情况，列出学号及其选课的门数3.查询选修了最高分大于87的学生的学号及其最高分