数据库基本select

1、u熟悉熟悉SELECT语法格式语法格式u掌握掌握WHERE,ORDER BY,GROUP BY,HAVING子句子句u掌握基本的多表查询掌握基本的多表查询u掌握内连接、外连接、交叉连接和联合查询掌握内连接、外连接、交叉连接和联合查询u了解自连接的使用了解自连接的使用u掌握多行和单值子查询的使用掌握多行和单值子查询的使用u了解了解嵌套子查询嵌套子查询的使用的使用第第4章章 获取数据获取数据4.1 基本基本SELECT查询语句查询语句 SELECT语句的执行窗口语句的执行窗口:使用使用SQL编辑器编辑器 假定已建好假定已建好Students、Courses、Enrollment三三个表，并已向各个

2、表添加了数据（见下页表）。个表，并已向各个表添加了数据（见下页表）。讨论一下数据查询问题：讨论一下数据查询问题：数据查询用来描述怎样从数据库中获取所需的数据。数据查询用来描述怎样从数据库中获取所需的数据。数据查询用到的语句就是查询语句，即数据查询用到的语句就是查询语句，即SELECT语语句，它是数据库操作中最基本、最重要的语句之一。句，它是数据库操作中最基本、最重要的语句之一。SELECT语句的功能就是从一个或多个表或视图语句的功能就是从一个或多个表或视图（一种虚拟表）中查到满足条件的数据。（一种虚拟表）中查到满足条件的数据。它的数据源是表或视图，而结果是另一个表。它的数据源是表或视图，而结果

3、是另一个表。 查询语句基本结构查询语句基本结构 SELECT FROM WHERE GROUP BY HAVING ORDER BY (1)SELECT ，称为，称为SELECT子句子句,用于用于指定整个查询结果表中包含的列指定整个查询结果表中包含的列。 假定已经执行完假定已经执行完FROM、WHERE、GROUP BY、HAVING子句，从概念上来说得子句，从概念上来说得到了一个表，若将该表称为到了一个表，若将该表称为T，从，从T表中选择表中选择SELECT子句指定的目标列组成表就为整个查子句指定的目标列组成表就为整个查询的结果表。询的结果表。(2) FROM ，称为，称为FROM子句。子句

4、。 用于指定整个查询语句用到的一个或多个基本用于指定整个查询语句用到的一个或多个基本表或视图，是整个查询语句的表或视图，是整个查询语句的数据来源数据来源，通常，通常称为数据源表。称为数据源表。 (3) WHERE ，称为，称为WHERE子句。子句。用于指定多个数据源表的用于指定多个数据源表的连接条件连接条件和单个源表和单个源表中中行行的的筛选条件或选择条件筛选条件或选择条件。如果只有一个源。如果只有一个源表，则没有表间的连接条件，只有行的筛选条表，则没有表间的连接条件，只有行的筛选条件。件。(4) GROUP BY ，称为，称为GROUP BY子句。假子句。假定已经执行完定已经执行完FROM、

5、WHERE子句，则从概念上子句，则从概念上来说得到了一个表，若将该表称为来说得到了一个表，若将该表称为T1表，则表，则GROUP BY用于指定用于指定T1表按哪些列表按哪些列(称为分组列称为分组列)进行分组，进行分组，所有这些行组成一个表，不妨把它称为所有这些行组成一个表，不妨把它称为T2表，表，T2表表实际上是一个组表。实际上是一个组表。(5) HAVING ，称为，称为HAVING子句。子句。与与GROUP BY子句一起使用子句一起使用。 用于指定组表用于指定组表T2表的选择条件，即选择表的选择条件，即选择T2表中表中满足满足的行，组成一个表就是的行，组成一个表就是SELECT子句中提到的

6、表子句中提到的表T。(6) ORDER BY ，称为，称为ORDER BY子句。子句。若有若有ORDER BY子句，则用于指定查询结果表子句，则用于指定查询结果表T中按指定中按指定列列进行进行升序或降序排序升序或降序排序，得到整个查询，得到整个查询的结果表。的结果表。4.1.1 单表查询单表查询 【例例4.1】查询全体学生的学号与姓名查询全体学生的学号与姓名 SELECT Sno,Sname FROM Students 【例例4.2】查询数据库查询数据库studentcourse学生基本信息学生基本信息表表s中学生的学号，姓名，所在系。中学生的学号，姓名，所在系。 SELECT 学号学号,姓名

7、姓名,系系 FROM s【例例4.1】查询所有学生可选的课程信息。查询所有学生可选的课程信息。SELECT * FROM c *表示选择当前表的所有属性列【例4.2】查询全体学生的学号、姓名、性别、年龄、所在系查询全体学生的学号、姓名、性别、年龄、所在系 SELECT Sno，Sname, Ssex，Sage，Sdept FROM Students 等价于：SELECT * FROM Students 【例例4.3.3】查询全体学生的选课情查询全体学生的选课情况，即学号、课程号、成绩，对况，即学号、课程号、成绩，对成绩值都加成绩值都加5。 SELECT Sno,Cno,Grade+5 FROM

8、 Enrollment 结果中表结果中表达式列达式列Grade5无列名。实际上无列名。实际上所有表达式列都所有表达式列都没有列名。没有列名。 o 【例4.3.4】查询全体学生的学号、姓名，并为原来的英文列名设置中文别名。以下三种方法等价：oSELECT Sno 学号 , Sname 姓名 FROM StudentsoSELECT Sno AS 学号 ,Sname AS 姓名 FROM Studentso SELECT 学号=Sno , 姓名=Sname FROM Students【例例4.3.54.3.5】查询全体学生的选课情况，其成绩列值查询全体学生的选课情况，其成绩列值都加都加5 5，并为

9、各列设置中文的别名，并为各列设置中文的别名 SELECT Sno 学号学号,Cno 课程号课程号,Grade+5 成绩成绩 FROM Enrollment 结果为：结果为： 【例例4.44.4】查询表查询表s s的学号和姓名信息，其中设置学号的学号和姓名信息，其中设置学号的别名为的别名为xhxh，设置姓名的别名为，设置姓名的别名为xmxmSELECT SELECT 学号学号 AS AS xhxh, , 姓名姓名 AS AS xmxm FROM S FROM S或或SELECT SELECT 学号学号 AS AS xhxh , , 姓名姓名 AS AS xmxm FROM S FROM S或或S

10、ELECT SELECT 学号学号 AS AS xhxh , , 姓名姓名 AS AS xmxm FROM S FROM S或或SELECT SELECT xhxh=学号学号, , xmxm=姓名姓名 FROM SFROM So 例如：SELECT 职称 from 教师 职称职称 副教授副教授 讲师讲师 教授教授 教授教授 select distinct 职称 from 教师 【例例3.3.63.3.6】显示所有选课学生的学号。显示所有选课学生的学号。SELECT Sno 学号学号 FROM Enrollment【例例4.3.74.3.7】显示所有选课学生的学号，并去掉重复显示所有选课学生的学

11、号，并去掉重复行。行。SELECT DISTINCT Sno 学号学号 FROM Enrollment 与与DISTINCTDISTINCT相反的是相反的是ALLALL，ALLALL表示保留结果表中表示保留结果表中的重复行。默认情况下是的重复行。默认情况下是ALLALL，表示保留重复行，表示保留重复行2. FROM子句子句 单表查询中，源表只有一个：单表查询中，源表只有一个： FROM 例如，要查找学生有关的信息，用到例如，要查找学生有关的信息，用到Students表，表，则则FROM子句为：子句为：FROM Students 例如，要查找课程有关的信息，用到例如，要查找课程有关的信息，用到C

12、ourses表，表，则则FROM子句为：子句为：FROM Courses 例如，要查找选课有关的信息，用到例如，要查找选课有关的信息，用到Enrollment表表，则，则FROM子句为：子句为：FROM Enrollment 3、通过、通过TOP子句限制结果集输出的行数子句限制结果集输出的行数（1）命令格式）命令格式SELECT TOP n PERCENT 属性列表属性列表 （2）功能）功能 n是一个正整数，如果指定是一个正整数，如果指定PERCENT关键字，则返回前百关键字，则返回前百分之分之n条记录，条记录，n必须介于必须介于0到到100之间的正整数。比如之间的正整数。比如“SELECT

13、TOP 3 ”表示输出查询结果集的前表示输出查询结果集的前3行，行，“SELECT TOP 3 PERCENT”表示输出查询结果集的表示输出查询结果集的前前3%记录行。记录行。【例例4.84.8】将课程成绩将课程成绩7070分以下的同分以下的同学的成绩提高学的成绩提高5 5分，然后把结果存储分，然后把结果存储到（到（另存另存）新数据表新数据表“最新成绩最新成绩”中中。SELECT SELECT 成绩成绩+5 AS +5 AS 最新成绩最新成绩 INTO INTO 最新成绩最新成绩 FROM sc WHERE FROM sc WHERE 成绩成绩70704、 INTO子句的使用子句的使用4.1.

14、2 WHERE子句子句 WHERE ：只获得满足条件的数据。：只获得满足条件的数据。l 查询体条件中没有多表连接条件，只有一个表的行筛选条件查询体条件中没有多表连接条件，只有一个表的行筛选条件 。l 中常用的运算符：比较运算符和逻辑中常用的运算符：比较运算符和逻辑运算符。（运算符。（P101表表4-1）l 常用的比较运算符有常用的比较运算符有：=(等于等于)、(大于大于)、=(大于等于大于等于)、=(小小于等于于等于)、!=或或(不不等于等于)、!( (不大不大于于)、!( (不小不小于于)共共9种。种。 范围比较运算符：范围比较运算符：BETWEEN AND ，NOT BETWEEN AND

15、 集合比较运算符：集合比较运算符：IN ，NOT IN 字符匹配运算符：字符匹配运算符：LIKE，NOT LIKE 空值比较运算符：空值比较运算符：IS NULL，IS NOT NULL 条件连接运算符：条件连接运算符：AND，OR，NOT 【例例4.104.10】在基本表在基本表S S中检索中检索1978-01-061978-01-06出生出生的学生情况。的学生情况。SELECT SELECT * * FROM s FROM s WHERE WHERE 出生日期出生日期= =1978-01-061978-01-06【例例4.114.11】在基本表在基本表S S中检索中检索19781978年及

16、以后出年及以后出生的学生情况。生的学生情况。SELECT SELECT * * FROM s FROM s WHERE year(WHERE year(出生日期出生日期)=1978)=1978或或SELECT SELECT * * FROM s FROM s WHERE year(WHERE year(出生日期出生日期)!1978)!1978【例例4.14】列出表列出表S中管理信息系年龄小于中管理信息系年龄小于28岁的学生的学号和出生年份情况。岁的学生的学号和出生年份情况。SELECT 学号学号, year(出生日期出生日期) as 出生年份出生年份FROM s WHERE 系系 =管理信息系

17、管理信息系 and (year(getdate()-year(出生日期出生日期)=下限值下限值 AND列名列名= 80 AND Grade =90 【例例4.3.11】查询学生年龄不在查询学生年龄不在2030之间的学之间的学生学号、姓名、所在系。生学号、姓名、所在系。SELECT Sno ,Sname,Sdept FROM Students WHERE Sage NOT BETWEEN 20 AND 30SELECT Sno ,Sname,Sdept FROM Students WHERE Sage30IN用于测试一个列值是否与用于测试一个列值是否与常量表常量表中的任何一个值相等。中的任何一个

18、值相等。IN条件表示格式为：条件表示格式为：列名列名 IN (常量常量1, 常量常量2, 常量常量n)当列值与当列值与IN中的任一常量值相等时，则条件为中的任一常量值相等时，则条件为TRUE，否则为否则为FALSE。NOT IN与与IN的含义正好相反，当列值与的含义正好相反，当列值与IN中的任一常中的任一常量值都不相等时，则结果为量值都不相等时，则结果为TRUE，否则为，否则为FALSE。 【例例4.3.12】查询数学系、计算机系、艺术系学生的学号、查询数学系、计算机系、艺术系学生的学号、姓名。姓名。SELECT Sno,Sname FROM Students WHERE Sdept IN (

19、Math, Computer, Art)此句等价于：此句等价于：SELECT Sno,Sname FROM Students WHERE Sdept =Math OR Sdept = Computer OR Sdept = Art【例例4.3.13】查询既不是数学系、计算机系，也不是查询既不是数学系、计算机系，也不是艺术系学生的学号、姓名。艺术系学生的学号、姓名。SELECT Sno,Sname FROM Students WHERE Sdept NOT IN (Math, Computer, Art)此句等价于：此句等价于：SELECT Sno,Sname FROM Students WHE

20、RE Sdept! =Math AND Sdept! = Computer AND Sdept! = ArtlLIKE用于测试一个字符串用于测试一个字符串是否与给定的模式匹配是否与给定的模式匹配。所谓模。所谓模式是一种特殊的字符串，其中可以包含普通字符，也可以包式是一种特殊的字符串，其中可以包含普通字符，也可以包含特殊意义的字符含特殊意义的字符(通常叫通配符通常叫通配符)。l一般形式为：列名一般形式为：列名 LIKE l模式串中可包含四种通配符：模式串中可包含四种通配符：o _：匹配任意：匹配任意一个字符一个字符。注意，在这里一个汉字或一个。注意，在这里一个汉字或一个全角字符也算一个字符。如全

21、角字符也算一个字符。如 _u_表示第二个字符为表示第二个字符为u或或U，o%：匹配任意：匹配任意0个或多个个或多个字符。如字符。如S%表示以表示以S开头的字开头的字符串。符串。o ：匹配：匹配 中的任意中的任意一个一个字符。如字符。如SDJ 。o ：不匹配不匹配 中的任意一个字符。如中的任意一个字符。如SDJ o 可以用可以用LIKE来实现模糊查询来实现模糊查询【例例4.3.144.3.14】查找姓名的第二个字符是查找姓名的第二个字符是u u并且只有三个并且只有三个字符的学生的学号、姓名。字符的学生的学号、姓名。SELECT Sno,Sname FROM Students WHERE Snam

22、e LIKE _u_【例例4.3.154.3.15】查找姓名以查找姓名以S S开头的所有学生的学号、姓开头的所有学生的学号、姓名。名。SELECT Sno,Sname FROM Students WHERE Sname LIKE S%【例例4.3.164.3.16】查找姓名以查找姓名以S S、D D或或J J开头的所有学生的学开头的所有学生的学号、姓名。号、姓名。SELECT Sno,Sname FROM Students WHERE Sname LIKE SDJ%【例例4.3.17】查找姓名不是以查找姓名不是以S、D或或J开头的所有开头的所有学生的学号、姓名。学生的学号、姓名。SELECT

23、Sno,Sname FROM Students WHERE Sname LIKE SDJ% 等价于：等价于：SELECT Sno,Sname FROM Students WHERE Sname NOT LIKE SDJ%【例例4.15】在表在表S中查询管理信息系学生的姓中查询管理信息系学生的姓名。名。SELECT 姓名 as 姓名,系 as 所在院系 FROM S WHERE 系 LIKE 管理信息系【例例4.164.16】查询陈老师所教的课程信息。查询陈老师所教的课程信息。SELECT SELECT * * FROM c FROM c WHERE WHERE 教师教师 LIKE LIKE 陈

24、陈% %【例例4.174.17】在基本表在基本表S S中检索出生日期包含中检索出生日期包含1313的学生情况。的学生情况。SELECT SELECT * * FROM s FROM s WHERE WHERE 出生日期出生日期 like %13%like %13%【例例4.18】如果要找出其姓名中不含有如果要找出其姓名中不含有“红红”的学生情况，则在的学生情况，则在LIKE前增加前增加NOT 。SELECT * FROM s WHERE 姓名 NOT LIKE %红% 【例例4.19】在基本表在基本表S中检索以中检索以“王王”姓打头，姓打头，名字由名字由1个汉字组成的学生姓名。个汉字组成的学生

25、姓名。SELECT 姓名 FROM s WHERE 姓名 LIKE 王_o 空值是尚未确定或不确定的值。空值是尚未确定或不确定的值。o 判断某列值是否为判断某列值是否为NULLNULL值，不能使用比较运算符等值，不能使用比较运算符等于和不等于，而只能使用专门的判断空值的子句，于和不等于，而只能使用专门的判断空值的子句，如下：如下： 判断列值为空的语句格式为判断列值为空的语句格式为：列名列名IS NULL 判断列值不为空的语句格式为判断列值不为空的语句格式为：列名列名 IS NOT NULL 【例例4.29】查询缺少成绩的学生的学号和相应的查询缺少成绩的学生的学号和相应的课程号。（某些学生选修课

26、程后没有参加考试，课程号。（某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。）所以有选课记录，但没有考试成绩。） SELECT 学号,课程号 FROM sc WHERE 成绩 IS NULL【例例4.30】查询有选修成绩的学生的学号和相应查询有选修成绩的学生的学号和相应的课程号。的课程号。 SELECT 学号,课程号 FROM sc WHERE 成绩 IS NOT NULL【例例4.18】查询无考试成绩的学生的学号和相应的课查询无考试成绩的学生的学号和相应的课程号。程号。SELECT Sno, Cno FROM Enrollment WHERE Grade IS NULL不等价

27、于不等价于：SELECT Sno, Cno FROM Enrollment WHERE Grade =0 【例例4.19】查询有考试成绩查询有考试成绩(即成绩不为空值即成绩不为空值)的学生的学生的学号、课程号。的学号、课程号。SELECT Sno, Cno FROM Enrollment WHERE Grade IS NOT NULL 【例例4.214.21】在基本表在基本表S S中检索中检索19751975年出生或者姓王年出生或者姓王的学生情况。的学生情况。SELECT SELECT * * FROM s FROM s WHERE year(WHERE year(出生日期出生日期)=1975

28、 OR )=1975 OR 姓名姓名 LIKE LIKE 王王% % 【例例4.224.22】在基本表在基本表S S中检索在中检索在19701970年到年到19801980年之年之间出生的学生情况。间出生的学生情况。SELECT SELECT * * FROM s FROM s WHERE year(WHERE year(出生日期出生日期)1970 and year()1970 and year(出生日出生日期期)1980)1980【例例4.23】在基本表在基本表S中检索不在管理信息系中检索不在管理信息系的学生情况。的学生情况。SELECT * FROM s WHERE not (系=管理信息

29、系)或SELECT * FROM s WHERE 系!=管理信息系 统计函数也称为集合函数或聚集函数，其作用是对一组值进行计算并统计函数也称为集合函数或聚集函数，其作用是对一组值进行计算并返回一个值。返回一个值。【例例4.22】求学生的总人数求学生的总人数SELECT COUNT(*) AS 学生的总人数学生的总人数 FROM Students 【例例4.23】求选修了课程的学生人数求选修了课程的学生人数SELECT COUNT(DISTINCT Sno) AS 选修课程的总人选修课程的总人数数 FROM Enrollment【例例4.24】求选修了求选修了C1课程的学生的平均成绩。课程的学生

30、的平均成绩。SELECT AVG(Grade) AS 平均成绩平均成绩 FROM Enrollment WHERE Cno=C1【例例4.25】求求20010102号学生的考试总成绩之和。号学生的考试总成绩之和。SELECT SUM(Grade) AS 20010102考试总成绩考试总成绩 FROM Enrollment WHERE Sno = 20010102【例例4.26】选修了选修了C1课程的学生的最高分和最低分课程的学生的最高分和最低分SELECT MAX(Grade) AS 最高分最高分, MIN(Grade) AS 最低分最低分 FROM Enrollment WHERE Cno=

31、C1 指定整个指定整个SELECT语句的输出结果中记录的排序依据。语句的输出结果中记录的排序依据。ORDER BY ASC | DESC ， n ASC表示按列值升序方式排序，表示按列值升序方式排序，DESC表示按列值降表示按列值降序方式排序。序方式排序。 如果没有指定排序方式，则默认的排序方式为升序排如果没有指定排序方式，则默认的排序方式为升序排序。序。 在在ORDER BY子句中，可以指定多个用逗号分隔的子句中，可以指定多个用逗号分隔的列名。这些列出现的顺序决定了查询结果排序的顺序。列名。这些列出现的顺序决定了查询结果排序的顺序。 当指定多个列时，首先按最前面的列进行排序，如果排序后当指定

32、多个列时，首先按最前面的列进行排序，如果排序后存在两个或两个以上列值相同的行，则对这些值相同的行再存在两个或两个以上列值相同的行，则对这些值相同的行再依据第二列进行排序，依此类推。依据第二列进行排序，依此类推。 4.1.3 ORDER BY子句子句【例例4.314.31】将所有女生按年龄升序排序。将所有女生按年龄升序排序。SELECT * FROM s WHERE 性别=女 ORDER BY 出生日期 ASC 【例例4.324.32】将将SCSC数据表中的信息按学生学号升序，数据表中的信息按学生学号升序，课程号降序排列。课程号降序排列。【例例4.30】查询所有学生的行，并按学生的年龄值从小查询

33、所有学生的行，并按学生的年龄值从小到大排序。到大排序。SELECT * FROM Students ORDER BY Sage【例例4.31】查询选修了查询选修了C1课程的学生的学号和成绩，查课程的学生的学号和成绩，查询结果按成绩降序排列。询结果按成绩降序排列。SELECT Sno, Grade FROM Enrollment WHERE Cno=C1 ORDER BY Grade DESC【例例4.32】查询全体学生信息，查询结果按所在系的系名查询全体学生信息，查询结果按所在系的系名升序排列，同一系的学生按年龄降序排列。升序排列，同一系的学生按年龄降序排列。 SELECT * FROM St

34、udents ORDER BY Sdept ASC, Sage DESC【例例4.33】求选修课程大于等于求选修课程大于等于2 2门课的学生的学号、平门课的学生的学号、平均成绩和选课门数，并按平均成绩降序排列。均成绩和选课门数，并按平均成绩降序排列。SELECT Sno AS 学号学号, AVG(Grade) AS 平均成绩平均成绩, COUNT(*)AS 修课门数修课门数FROM Enrollment GROUP BY Sno HAVING COUNT(*) = 2 ORDER BY AVG (Grade) DESC 【例例】在在SC表中，查询学号为表中，查询学号为1的同学获得最的同学获得最

35、高成绩的课程号。高成绩的课程号。【例例】在在SC表中，找出选修了课程号为表中，找出选修了课程号为2的课的课程，而且课程成绩最高的前两位同学。程，而且课程成绩最高的前两位同学。4.1.4 GROUP BY 子句子句 有时我们需要把有时我们需要把FROM、WHERE子句子句产生的表产生的表按某种按某种原则原则分成若干组分成若干组，然后，然后再对每个组进行统计再对每个组进行统计，一组形，一组形成一行，最后把所有这些行组成一个表，称为组表。成一行，最后把所有这些行组成一个表，称为组表。GROUP BY子句在子句在WHERE子句后边子句后边。一般形式为一般形式为：GROUP BY ， n 其中其中 是分

36、组的依据。是分组的依据。分组原则分组原则是是 的的列值相同，就为同一组列值相同，就为同一组。当有多个当有多个 时，则先按第一个列值分组，然后对每一时，则先按第一个列值分组，然后对每一组再按第二个列值进行分组，依此类推。组再按第二个列值进行分组，依此类推。 【例例4.27】求选修每门课程的求选修每门课程的学生人数学生人数。SELECT Cno AS 课程号课程号, COUNT(Sno) AS 选修人数选修人数 FROM Enrollment GROUP BY Cno SELECT Sno 学号学号, Sum(grade) 总成绩总成绩 FROM Enrollment GROUP BY Sno 包含包含GROUP BY子句的查询语句中，子句的查询语句中，SELECT子句指定的子句指定的列名列名，要么是统计函数要么是统计函数，如上，如上例中的例中的COUNT(Sno)，要么是包含在要么是包含在GROUP BY子子句中的列名句中的列名，如上例中的，如上例中的Cno，否则将出错否则将出错。u如下列语句是错误的如下列语句是错误的：SELECT Sno AS 学号学号,Cno AS 课程号课程号, COUNT(Sno) AS 选修人数选修人数 FROM Enrollment GROUP BY Cno 因为因为SELECT子句中的子句中的Sno列，既不是统计函数，列，既不是统计函数，也不是