第6章SQL查询的操作

1、本章要点SQL与SQL查询SQL 数据查询SQL数据定义SQL数据操纵第6章 SQL查询的操作6.1 SQL与SQL查询6.1.1 SQL的发展与功能SQL最早是在20世纪70年代由IBM公司开发出来的，并被应用在DB2关系数据库系统中，主要用于关系数据库中的信息检索。1986年10月，美国国家标准协会(ANSI)的数据库委员会批准了SQL作为关系数据库语言的美国标准。1987年6月，国际标准化组织(ISO)将其采纳为国际标准，这个标准也称为SQL86。SQL标准的出台使SQL作为标准关系数据库语言的地位得到了加强。随后，SQL标准几经修改和完善，其间经历了SQL89，SQL92，SQL99，

2、SQL2003等多个版本，每个新版本都较前面的版本有重大改进。随着数据库技术的发展，还会有更新的SQL标准。 按照其实现的功能可以将SQL语句划分为4类。数据查询语言(Data Query Language，DQL)：按一定的查询条件从数据库对象中检索符合条件的数据，如SELECT语句。数据定义语言(Data Definition Language，DDL)：用于定义数据的逻辑结构及数据项之间的关系，如CREATE，DROP，ALTER语句等。数据操纵语言(Data Manipulation Language，DML)：用于增加、删除、修改数据等，如INSERT，UPDATE，DELETE语句

3、等。数据控制语言(Data Control Language，DCL)：在数据库系统中，具有不同角色的用户执行不同的任务，并且应该被给予不同的权限。数据控制语言用于设置或更改用户的数据库操作权限，如GRANT，REVOKE语句等。6.1.2 SQL 视图实际上，在使用查询设计视图创建查询时，Access会自动将操作步骤转化为一条条等价的SQL语句，只要打开查询，并进入该查询的SQL 视图就可以看到系统生成的SQL语句。6.1.3 创建SQL查询SQL查询包括联合查询、传递查询和数据定义查询。创建SQL查询的步骤如下。打开“教学管理”数据库，单击“创建”选项卡，再在“查询”命令组中单击“查询设计

4、”命令按钮，打开查询设计视图窗口，再在“显示表”对话框中单击“关闭”按钮，不添加任何表或查询，进入空白的查询设计视图。在“查询工具/设计”选项卡的“结果”命令组中单击“视图”命令按钮，在下拉菜单中选择“SQL视图”命令，进入SQL视图并输入SQL语句。也可以在“查询工具/设计”选项卡的“查询类型”命令组中选择“联合”、“传递”或“数据定义”命令，即打开相应的特定查询窗口，在窗口中输入合适的SQL语句。将创建的查询存盘并运行查询。6.2 SQL数据查询SQL数据查询通过SELECT语句实现。SELECT语句中包含的子句很多，其语法格式为SELECT ALL|DISTINCT|TOP n.AS ，

5、.AS FROM ， WHERE GROUP BY ，HAVING UNIONALL SELECT语句ORDER BY ASC|DESC，ASC|DESC以上格式中“”中的内容是必选的，“”中的内容是可选的，“|”表示多个选项中只能选择其中之一。6.2.1 基本查询1简单的查询语句简单的SELECT语句只包含SELECT子句和FROM子句，其格式为SELECT ALL|DISTINCT|TOP n.AS ，.AS FROM ， 例6-1 对“学生”表进行如下操作，写出操作步骤和SQL语句。列出全部学生信息。列出前5个学生的姓名和年龄。操作1的SELECT语句：SELECT * FROM 学生操

6、作2的SELECT语句：SELECT TOP 5 姓名，Year(Date()-Year(出生日期) AS 年龄 FROM 学生例6-2 求出所有学生的平均入学成绩。SELECT语句如下。SELECT Avg(入学成绩) AS 入学成绩平均分 FROM 学生2带条件查询WHERE子句用于指定查询条件，其格式为WHERE 例6-3 写出对“教学管理”数据库进行如下操作的语句。列出入学成绩在580分以上的学生记录。求出湖南学生入学成绩平均值。操作1：SELECT * FROM 学生 WHERE 入学成绩580操作2：SELECT Avg(入学成绩) AS 入学成绩平均分 FROM 学生 WHERE

7、 籍贯=湖南例6-4 写出对“教学管理”数据库进行如下操作的语句。列出江苏籍和贵州籍的学生名单。列出入学成绩在560650分之间的学生名单。列出所有姓张的学生名单。列出所有成绩为“空值”的学生学号和课程编号。操作1：SELECT 学号,姓名,籍贯 FROM 学生 WHERE 籍贯 In(江苏“,贵州)语句中的WHERE子句还有如下等价的形式。WHERE 籍贯江苏 Or 籍贯贵州操作2：SELECT 学号,姓名,入学成绩 FROM 学生 WHERE 入学成绩 Between 560 And 650语句中的WHERE子句还有如下等价的形式。WHERE 入学成绩=560 And 入学成绩=650操作

8、3：SELECT 学号，姓名 FROM 学生 WHERE 姓名 Like张*语句中的WHERE子句还有如下等价的形式。WHERE Left(姓名,1)=张或WHERE Mid(姓名,1,1)=张或WHERE InStr(姓名,张)=1操作4：SELECT 学号，课程编号 FROM 选课 WHERE 成绩 Is Null注意：在查询时用“字段名Is Null”的形式，而不能写成“字段名=Null”。3查询结果处理1)排序输出(ORDER BY)其格式为ORDER BY ASC|DESC，ASC|DESC例6-5 对“教学管理”数据库，按性别顺序列出学生的学号、姓名、性别、年龄及籍贯，性别相同的再

9、按年龄由大到小排序。SELECT 学号,姓名,性别,Year(Date()-Year(出生日期) AS 年龄,籍贯 FROM 学生 ORDER BY 性别,Year(Date()-Year(出生日期) DESC要注意语句中“年龄”的表达方法。在该语句中，由于两个排序选项是第3、第4个输出选项，所以ORDER BY子句也可以写成ORDER BY 3,4 DESC2)分组统计(GROUP BY)与筛选(HAVING)使用GROUP BY子句可以对查询结果进行分组，其格式为GROUP BY ，其中，是作为分组依据的字段名。GROUP BY子句可以将查询结果按指定列进行分组，每组在列上具有相同的值。要

10、注意的是，如果使用了GROUP BY子句，则查询输出选项要么是分组选项，要么是统计函数，因为分组后每个组只返回一行结果。若在分组后还要按照一定的条件进行筛选，则需使用HAVING子句，其格式为HAVING HAVING子句与WHERE子句一样，也可以起到按条件选择记录的功能，但两个子句作用的对象不同。WHERE子句作用于表，而HAVING子句作用于组，必须与GROUP BY子句连用，用来指定每一分组内应满足的条件。HAVING子句与WHERE子句不矛盾，在查询中先用WHERE子句选择记录，然后进行分组，最后再用HAVING子句选择记录。当然，GROUP BY子句也可单独出现。例6-6 写出对“

11、教学管理”数据库进行如下操作的语句。分别统计男女生人数。分别统计男女生中少数民族学生人数。列出平均成绩大于80分的课程编号，并按平均成绩升序排序。统计每个学生选修课程的门数(超过一门的学生才统计)，要求输出学生学号和选修门数，查询结果按选修门数降序排序，若门数相同，按学号升序排序。操作1：SELECT 性别,Count(*) AS 人数 FROM 学生 GROUP BY 性别操作2：SELECT 性别,Count(*) AS 人数 FROM 学生 WHERE 是否少数民族 GROUP BY 性别操作3：SELECT 课程编号,Avg(考试成绩) AS 平均考试成绩 FROM 选课 GROUP

12、BY 课程编号 HAVING Avg(考试成绩)=80 ORDER BY Avg(考试成绩) ASC操作4：SELECT 学号, Count(课程编号) AS 选课门数 FROM 选课 GROUP BY 学号 HAVING Count(课程编号)1 ORDER BY 2 DESC,16.2.2 嵌套查询1返回单值的子查询例6-7 对“教学管理”数据库，列出选修“数据库技术与应用”的所有学生的学号。SELECT 学号 FROM 选课 WHERE 课程编号= (SELECT 课程编号 FROM 课程 WHERE 课程名称=“数据库技术与应用)2返回一组值的子查询1)Any运算符的用法Any运算符可

13、以找出满足子查询中任意一个值的记录，使用格式为 Any()例6-8 对“教学管理”数据库，列出选修“J0015”课的学生中成绩比选修“K1002”课的最低成绩高的学生的学号和成绩。SELECT 学号,成绩 FROM 选课 WHERE 课程编号=“J1005 And 成绩Any (SELECT 成绩 FROM 选课 WHERE 课程编号=“K1002)2)All运算符的用法All运算符可以找出满足子查询中所有值的记录，使用格式为 All()例6-9 对“教学管理”数据库，列出选修“J1005”课的学生中成绩比选修“K1002”课的最高成绩还要高的学生的学号和考试成绩。SELECT 学号,成绩 F

14、ROM 选课 WHERE 课程编号 =J1005 And 成绩All (SELECT 成绩 FROM 选课 WHERE 课程编号=K1002)3)In运算符的用法In是属于的意思，等价于“Any”，即等于子查询中任何一个值。例5-10 写出对“教学管理”数据库进行如下操作的语句。列出选修“数据库技术与应用”或“微积分”的所有学生的学号。显示“选课”表的第610号记录。操作1：SELECT 学号 FROM 选课 WHERE 课程编号 In (SELECT 课程编号 FROM 课程 WHERE 课程名称=“数据库技术与应用 Or 课程名称=微积分)操作2：SELECT TOP 5 * FROM 选

15、课 WHERE 学号 Not In (SELECT TOP 5 学号 FROM 选课)6.2.3 多表查询例6-11 写出对“教学管理”数据库进行如下操作的语句。输出所有学生的成绩单，要求给出学号、姓名、课程编号、课程名称和成绩。列出少数民族学生的选课情况，要求列出学号、姓名、课程编号、课程名称和成绩。求选修“J1005”课的女生的平均年龄。操作1：SELECT a.学号,姓名,b.课程编号,课程名称,平时成绩,考试成绩,总评成绩 FROM 学生 a,选课 b,课程 c WHERE a.学号=b.学号 And b.课程编号=c.课程编号操作2：SELECT a.学号,a.姓名,b.课程编号,课

16、程名称,平时成绩,考试成绩,总评成绩 FROM 学生 a,选课 b,课程 c WHERE a.学号=b.学号 And b.课程编号=c.课程编号 And 是否少数民族操作3：SELECT Avg(Year(Date()-Year(出生日期) AS 平均年龄 FROM 学生,选课 WHERE 学生.学号=选课.学号 And 课程编号=J0015 And 性别=男6.2.4 联合查询联合查询实际是将两个或更多个表或查询中的记录纵向合并成为一个查询结果。数据合并(UNION)子句的格式为UNION ALL 其中，ALL表示结果全部合并。若没有ALL，则重复的记录将被自动去掉。合并的规则如下。不能合并

17、子查询的结果。两个SELECT语句必须输出同样的列数。两个表各相应列的数据类型必须相同，数字和字符不能合并。仅最后一个SELECT语句中可以用ORDER BY子句，且排序选项必须用数字说明。例6-12 对“教学管理”数据库，列出选修“J1005”课或“K1002”课的所有学生的学号和姓名，要求建立联合查询。SELECT 学生.学号, 学生.姓名 FROM 选课,学生 WHERE 课程编号=“J1005 And 选课.学号=学生.学号UNION SELECT 学生.学号, 学生.姓名 FROM 选课,学生 WHERE 课程编号=“K1002 And 选课.学号=学生.学号6.3 SQL数据定义6

18、.3.1 建立表结构在SQL中可以通过CREATE TABLE语句建立表结构，其语句格式为CREATE TABLE ( 字段级完整性约束1 ， 字段级完整性约束2 ， ， 字段级完整性约束n ， )Microsoft Access SQL常用数据类型数据类型字段宽度说明Smallint短整型，按2个字节存储Integer长整型，按4个字节存储Real单精度浮点型，按4个字节存储Float双精度浮点型，按8个字节存储Money货币型，按8个字节存储Char(n)n字符型(存储0255个字符)Text(n)n备注型Bit是/否型，按1个字节存储Datetime日期/时间型，按8个字节存储Image

19、用于OLE对象例6-13 在“教学管理”数据库中建立“教师”表：教师(编号，姓名，性别，基本工资，出生年月，研究方向)，其中允许“出生年月”字段为“空值”。CREATE TABLE 教师 ( 编号 Char(7)， 姓名 Char(8)， 性别 Char(2)， 基本工资 Money， 出生年月 Datetime Null， 研究方向 Text(50) )6.3.2 修改表结构如果表不满足要求，就需要进行修改。可以使用ALTER TABLE语句修改已建表的结抅，其语句格式为ALTER TABLE ADD 字段级完整性约束条件DROP ALTER 例6-14 对“课程”表的结构进行修改，写出操作语句。为“课程”表增加一个整数类型的“学时”字段。删除“课程”表中的“学时”字段。操作1：ALTER TABLE 课程 ADD 学时 Smallint操作2：ALTER TABLE 课程 DROP 学时6.3.3 删除表如果希望删除某个不需要的表