数据库基本select.ppt

熟悉SELECT语法格式 掌握WHERE,ORDER BY,GROUP BY,HAVING子句 掌握基本的多表查询 掌握内连接、外连接、交叉连接和联合查询 了解自连接的使用 掌握多行和单值子查询的使用 了解嵌套子查询的使用,第4章 获取数据,4.1 基本SELECT查询语句,SELECT语句的执行窗口:使用SQL编辑器,数据查询,假定已建好Students、Courses、Enrollment三个表，并已向各个表添加了数据（见下页表）。,讨论一下数据查询问题： 数据查询用来描述怎样从数据库中获取所需的数据。 数据查询用到的语句就是查询语句，即SELECT语句，它是数据库操作中最基本、最重要的语句之一。 SELECT语句的功能就是从一个或多个表或视图（一种虚拟表）中查到满足条件的数据。 它的数据源是表或视图，而结果是另一个表。,查询语句基本结构,语法： SELECT FROM WHERE GROUP BY HAVING ORDER BY ,SELECT语句说明 SELECT语句包括：SELECT 子句，FROM子句，可选的WHERE子句、GROUP BY子句、HAVING子句和ORDER BY子句。 SELECT ，称为SELECT子句,用于指定整个查询结果表中包含的列。 假定已经执行完FROM、WHERE、GROUP BY、HAVING子句，从概念上来说得到了一个表，若将该表称为T，从T表中选择SELECT子句指定的目标列组成表就为整个查询的结果表。,(2) FROM ，称为FROM子句。 用于指定整个查询语句用到的一个或多个基本表或视图，是整个查询语句的数据来源，通常称为数据源表。 (3) WHERE ，称为WHERE子句。用于指定多个数据源表的连接条件和单个源表中行的筛选条件或选择条件。如果只有一个源表，则没有表间的连接条件，只有行的筛选条件。,(4) GROUP BY ，称为GROUP BY子句。假定已经执行完FROM、WHERE子句，则从概念上来说得到了一个表，若将该表称为T1表，则GROUP BY用于指定T1表按哪些列(称为分组列)进行分组，所有这些行组成一个表，不妨把它称为T2表，T2表实际上是一个组表。 (5) HAVING ，称为HAVING子句。与GROUP BY子句一起使用。 用于指定组表T2表的选择条件，即选择T2表中满足的行，组成一个表就是SELECT子句中提到的表T。,(6) ORDER BY ，称为ORDER BY子句。若有ORDER BY子句，则用于指定查询结果表T中按指定列进行升序或降序排序，得到整个查询的结果表。 SELECT语句包含了关系代数中的选择、投影、连接、笛卡儿积等运算。 下面首先学习单表查询SELECT语句中的各个子句,4.1.1 单表查询,单表查询指的是在一个源表中查找所需的数据。 因此，单表查询时，FROM子句中的 只要给出一个源表表名。 1. SELECT子句 （1）选择表中若干列 在SELECT子句的中指定整个查询结果表中出现的若干个列名，各列名之间用逗号分隔,【例41】查询全体学生的学号与姓名 SELECT Sno,Sname FROM Students 【例4.2】查询数据库studentcourse学生基本信息表s中学生的学号，姓名，所在系。 SELECT 学号,姓名,系 FROM s,（2）选择表中所有列(无条件查询) 可以在中指定表中所有列的列名，也可以用*来代替，或用.*代表指定表的所有列。* 代表所有列；输出显示列的次序由SELECT指定 【例4.1】查询所有学生可选的课程信息。 SELECT * FROM c *表示选择当前表的所有属性列 【例4.2】查询全体学生的学号、姓名、性别、年龄、所在系 SELECT Sno，Sname, Ssex，Sage，Sdept FROM Students 等价于：SELECT * FROM Students,（3）查询的数据可以使用表达式 表达式可以是列名、常量、函数、或用列名、常量、函数等经过+(加)、-(减)、*(乘)、/(除)等组成的公式。 【例4.3.3】查询全体学生的选课情况，即学号、课程号、成绩，对成绩值都加5。 SELECT Sno,Cno,Grade+5 FROM Enrollment,注意：结果中表达式列Grade5无列名。实际上所有表达式列都没有列名。,（ 4）设置列的别名 所谓别名，就是另一个名字。有时需要显示特殊的输出列名，主要是为了方便阅读。 例如： select 职称 高校教师职称 from 教师,设置列别名的方法有： 原列名 AS 列别名 或者： 列别名原列名 【例4.3.4】查询全体学生的学号、姓名，并为原来的英文列名设置中文别名。以下三种方法等价： SELECT Sno 学号 , Sname 姓名 FROM Students SELECT Sno AS 学号 ,Sname AS 姓名 FROM Students SELECT 学号=Sno , 姓名=Sname FROM Students,【例4.3.5】查询全体学生的选课情况，其成绩列值都加5，并为各列设置中文的别名 SELECT Sno 学号,Cno 课程号,Grade+5 成绩 FROM Enrollment 结果为：,【例4.4】查询表s的学号和姓名信息，其中设置学号的别名为xh，设置姓名的别名为xm SELECT 学号 AS xh, 姓名 AS xm FROM S 或 SELECT 学号 AS xh , 姓名 AS xm FROM S 或 SELECT 学号 AS xh , 姓名 AS xm FROM S 或 SELECT xh=学号, xm=姓名 FROM S,（ 5）使用DISTINCT消除结果表中完全重复的行,例如：SELECT 职称 from 教师 职称 副教授 讲师 教授 教授 select distinct 职称 from 教师,【例3.3.6】显示所有选课学生的学号。 SELECT Sno 学号 FROM Enrollment 【例4.3.7】显示所有选课学生的学号，并去掉重复行。 SELECT DISTINCT Sno 学号 FROM Enrollment 与DISTINCT相反的是ALL，ALL表示保留结果表中的重复行。默认情况下是ALL，表示保留重复行。,2. FROM子句,单表查询中，源表只有一个： FROM 例如，要查找学生有关的信息，用到Students表，则FROM子句为：FROM Students 例如，要查找课程有关的信息，用到Courses表，则FROM子句为：FROM Courses 例如，要查找选课有关的信息，用到Enrollment表，则FROM子句为：FROM Enrollment,3、通过TOP子句限制结果集输出的行数,（1）命令格式 SELECT TOP n PERCENT 属性列表 （2）功能 n是一个正整数，如果指定PERCENT关键字，则返回前百分之n条记录，n必须介于0到100之间的正整数。比如“SELECT TOP 3 ”表示输出查询结果集的前3行，“SELECT TOP 3 PERCENT”表示输出查询结果集的前3%记录行。 例如： select distinct top 8 籍贯 from 学生信息,【例4.8】将课程成绩70分以下的同学的成绩提高5分，然后把结果存储到（另存）新数据表“最新成绩”中。 SELECT 成绩+5 AS 最新成绩 INTO 最新成绩 FROM sc WHERE 成绩70,4、 INTO子句的使用,4.1.2 WHERE子句,WHERE ：只获得满足条件的数据。 查询体条件中没有多表连接条件，只有一个表的行筛选条件 。 中常用的运算符：比较运算符和逻辑运算符。（P101表4-1） 常用的比较运算符有：=(等于)、(大于)、=(大于等于)、(不等于)、!( (不大于)、!( (不小于)共9种。,范围比较运算符：BETWEEN AND ，NOT BETWEEN AND 集合比较运算符：IN ，NOT IN 字符匹配运算符：LIKE，NOT LIKE 空值比较运算符：IS NULL，IS NOT NULL 条件连接运算符：AND，OR，NOT,基于其它运算符的查询,【例4.10】在基本表S中检索1978-01-06出生的学生情况。 SELECT * FROM s WHERE 出生日期=1978-01-06 【例4.11】在基本表S中检索1978年及以后出生的学生情况。 SELECT * FROM s WHERE year(出生日期)=1978 或 SELECT * FROM s WHERE year(出生日期)!1978,【例4.14】列出表S中管理信息系年龄小于28岁的学生的学号和出生年份情况。 SELECT 学号, year(出生日期) as 出生年份FROM s WHERE 系 =管理信息系 and (year(getdate()-year(出生日期)28,基于BETWEENAND的查询 基本形式：列名 BETWEEN 下限值 AND 上限值 等价于：列名=下限值 AND列名=上限值 列名可以是表达式列的别名。 BETWEENAND一般用于数值型范围的比较。表示当列值在指定的下限值和上限值范围内时，条件为TRUE，否则，为FALSE。 NOT BETWEENAND与BETWEENAND正好相反，表示列值不在指定的下限值和上限值范围内时，条件为TRUE，否则为FALSE。 注意：列名类型要与下限值或上限值的类型一致。,【例4.3.10】查询学生选课成绩在8090分之间的学生学号、课程号、成绩。 SELECT * FROM Enrollment WHERE Grade BETWEEN 80 AND 90 SELECT * FROM Enrollment WHERE Grade = 80 AND Grade 30,基于IN的查询 IN用于测试一个列值是否与常量表中的任何一个值相等。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 (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 WHERE Sdept! =Math AND Sdept! = Computer AND Sdept! = Art,基于LIKE的查询 LIKE用于测试一个字符串是否与给定的模式匹配。所谓模式是一种特殊的字符串，其中可以包含普通字符，也可以包含特殊意义的字符(通常叫通配符)。 一般形式为：列名 LIKE 模式串中可包含四种通配符： _：匹配任意一个字符。注意，在这里一个汉字或一个全角字符也算一个字符。如 _u_表示第二个字符为u或U， %：匹配任意0个或多个字符。如S%表示以S开头的字符串。 ：匹配 中的任意一个字符。如SDJ 。 ：不匹配 中的任意一个字符。如SDJ ,可以用LIKE来实现模糊查询 【例4.3.14】查找姓名的第二个字符是u并且只有三个字符的学生的学号、姓名。 SELECT Sno,Sname FROM Students WHERE Sname LIKE _u_ 【例4.3.15】查找姓名以S开头的所有学生的学号、姓名。 SELECT Sno,Sname FROM Students WHERE Sname LIKE S% 【例4.3.16】查找姓名以S、D或J开头的所有学生的学号、姓名。 SELECT Sno,Sname FROM Students WHERE Sname LIKE SDJ%,【例4.3.17】查找姓名不是以S、D或J开头的所有学生的学号、姓名。 SELECT 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.16】查询陈老师所教的课程信息。 SELECT * FROM c WHERE 教师 LIKE 陈% 【例4.17】在基本表S中检索出生日期包含13的学生情况。 SELECT * FROM s WHERE 出生日期 like %13%,【例4.18】如果要找出其姓名中不含有“红”的学生情况，则在LIKE前增加NOT 。 SELECT * FROM s WHERE 姓名 NOT LIKE %红% 【例4.19】在基本表S中检索以“王”姓打头，名字由1个汉字组成的学生姓名。 SELECT 姓名 FROM s WHERE 姓名 LIKE 王_,空值是尚未确定或不确定的值。 判断某列值是否为NULL值，不能使用比较运算符等于和不等于，而只能使用专门的判断空值的子句，如下： 判断列值为空的语句格式为：列名IS NULL 判断列值不为空的语句格式为：列名 IS NOT NULL,基于NULL空值的查询,【例4.29】查询缺少成绩的学生的学号和相应的课程号。（某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。） 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 不等价于： SELECT Sno, Cno FROM Enrollment WHERE Grade =0 【例4.19】查询有考试成绩(即成绩不为空值)的学生的学号、课程号。 SELECT Sno, Cno FROM Enrollment WHERE Grade IS NOT NULL,【例4.21】在基本表S中检索1975年出生或者姓王的学生情况。 SELECT * FROM s WHERE year(出生日期)=1975 OR 姓名 LIKE 王% 【例4.22】在基本表S中检索在1970年到1980年之间出生的学生情况。 SELECT * FROM s WHERE year(出生日期)1970 and year(出生日期)1980,基于多个条件的查询,【例4.23】在基本表S中检索不在管理信息系的学生情况。 SELECT * FROM s WHERE not (系=管理信息系) 或 SELECT * FROM s WHERE 系!=管理信息系,使用统计函数的查询 统计函数也称为集合函数或聚集函数，其作用是对一组值进行计算并返回一个值。,【例4.22】求学生的总人数 SELECT COUNT(*) AS 学生的总人数 FROM Students 【例4.23】求选修了课程的学生人数 SELECT COUNT(DISTINCT Sno) AS 选修课程的总人数 FROM Enrollment 【例4.24】求选修了C1课程的学生的平均成绩。 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=C1,指定整个SELECT语句的输出结果中记录的排序依据。 ORDER BY ASC | DESC ， n ASC表示按列值升序方式排序，DESC表示按列值降序方式排序。 如果没有指定排序方式，则默认的排序方式为升序排序。 在ORDER BY子句中，可以指定多个用逗号分隔的列名。这些列出现的顺序决定了查询结果排序的顺序。 当指定多个列时，首先按最前面的列进行排序，如果排序后存在两个或两个以上列值相同的行，则对这些值相同的行再依据第二列进行排序，依此类推。,4.1.3 ORDER BY子句,【例4.31】将所有女生按年龄升序排序。 SELECT * FROM s WHERE 性别=女 ORDER BY 出生日期 ASC 【例4.32】将SC数据表中的信息按学生学号升序，课程号降序排列。,【例4.30】查询所有学生的行，并按学生的年龄值从小到大排序。 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 Students ORDER BY Sdept ASC, Sage DESC 【例4.33】求选修课程大于等于2门课的学生的学号、平均成绩和选课门数，并按平均成绩降序排列。 SELECT Sno AS 学号, AVG(Grade) AS 平均成绩, COUNT(*)AS 修课门数 FROM Enrollment GROUP BY Sno HAVING COUNT(*) = 2 ORDER BY AVG (Grade) DESC,【例】在SC表中，查询学号为1的同学获得最高成绩的课程号。 【例】在SC表中，找出选修了课程号为2的课程，而且课程成绩最高的前两位同学。,4.1.4 GROUP BY 子句 有时我们需要把FROM、WHERE子句产生的表按某种原则分成若干组，然后再对每个组进行统计，一组形成一行，最后把所有这些行组成一个表，称为组表。 GROUP BY子句在WHERE子句后边。 一般形式为： GROUP BY ， n 其中是分组的依据。 分组原则是的列值相同，就为同一组。 当有多个时，则先按第一个列值分组，然后对每一组再按第二个列值进行分组，依此类推。,【例4.27】求选修每门课程的学生人数。 SELECT Cno AS 课程号, COUNT(Sno) AS 选修人数 FROM Enrollment GROUP BY Cno,【例4.28】输出每个学生的学号和他/她的各门课程的总成绩。 SELECT Sno 学号, Sum(grade) 总成绩 FROM Enrollment GROUP BY Sno,注意：包含GROUP BY子句的查询语句中，SELECT子句指定的列名，要么是统计函数，如上例中的COUNT(Sno)，要么是包含在GROUP BY子句中的列名，如上例中的Cno，否则将出错。 如下列语句是错误的： SELECT Sno AS 学号,Cno AS 课程号, COUNT(Sno) AS 选修人数 FROM Enrollment GROUP BY Cno 因为SELECT子句中的Sno列，既不是统计函数，也不是GROUP BY子句中的列名。,4.1.5 HAVING子句 HAVING子句指定GROUP BY生成的组表的选择条件,在GROUP BY子句之后，并且必须与GROUP BY子句一起使用。 一般形式为：HAVING 例4.29：求选修课程大于等于2门课的学生的学号、