第5章结构化查询语言SQL

第5章结构化查询语言SQL 主编 赖申江 本章学习导航 结构化查询语言SQL是一种功能齐全的数据库语言 包含数据定义功能 数据查询功能 数据操纵功能和数据控制功能 其中最主要的功能就是查询 通过本章学习 学生应掌握SQL语言的使用方法和技巧 提高知识综合运用能力 写出高质量的SQL查询命令 本章学习要点 SQL的数据定义 熟练运用CREATETABLE ALTERTABLET和DROPTABLE语句完成表的创建 修改和删除 SQL的数据操纵 熟练运用INSERT DELETE和UPDATE语句添加 删除和更新记录 SQL的数据查询 熟练运用SELECT语句完成简单查询 嵌套查询 连接查询 分组与计算查询 教学课时 4课时 第5章结构化查询语言SQL 5 1SQL概述结构化查询语言 StructuredQueryLanguage 简称SQL 是一种介于关系代数和关系演算之间的语言 它于1974年由Boyce和Chamberlin提出 由于具有语言简洁 方便实用 功能齐全等优点 很快得到推广和应用 随着关系数据库的流行 SQL语言最终发展成为关系数据库的标准语言和数据库领域中一个主流语言 SQL已经被ANSI 美国国家标准化组织 确定为数据库系统的工业标准 它是数据库系统的通用语言 利用SQL 用户可以用几乎相同的语句在不同的数据库系统上执行同样的操作 第5章结构化查询语言SQL 5 1 1SQL语言的特点SQL语言之所以能够成为国际标准 是因为它是一个综合的 通用的 功能极强 同时又简单易学的语言 1 功能强大SQL语言集数据查询 数据操纵 数据定义和数据控制功能于一体 2 高度非过程化用户只要提出 做什么 而无需指明 怎么做 存取路径的选择以及SQL语言的操作过程由系统自动完成 不但大大减轻了用户负担 而且有利于提高数据的独立性 3 简单易用SQL语言十分简洁 实现核心功能 一般只要用到下面9个命令动词 如表5 1所示 因此容易学习和掌握 4 有联机交互和嵌入两种使用方式SQL语言支持两种不同的使用方式 一种是联机交互使用 即用户能在终端直接输入SQL命令对数据库进行操作 另一种是嵌入式 即将SQL语句嵌入到其他高级语言中 以便程序员在设计时使用 第5章结构化查询语言SQL 5 1 2SQL数据定义功能关系数据库的基本对象是表 视图和索引 因此SQL的数据定义功能包括定义数据库 表 视图和索引 如表5 2所示 由于视图是基于表的虚表 索引是依附于表的 因此SQL通常不提供修改视图和索引的操作 1 数据库的创建和删除关系数据库被定义为关系 表 索引和视图的集合 在创建表 视图和索引前必须首先创建数据库 例如 创建学生选课数据库stsc 可以用CREATEDATABASEstsc语句来实现 第5章结构化查询语言SQL 2 基本表的创建 修改和删除用CREATEDATABASE建立的数据库是一个空的数据库 接下来应该在这个数据库中建立表 1 创建基本表表的创建既可以通过表设计器或VisualFoxPro的新建表命令实现 也可以使用SQL中的相应命令创建 命令格式 CREATETABLE DBF FREE 功能 建立一个由指定的表结构 说明 指定要创建的表的名称 FREE 指定创建的表是一个自由表 不被添加到数据库中 指定相应字段的名称 类型 宽度及小数位数 第5章结构化查询语言SQL 例5 1在学生选课数据库stsc中创建一个学生表student CREATETABLEstudent 学号c 8 姓名c 6 性别c 2 院系c 8 出生日期D 入学成绩N 4 1 功能 用CREATETABLE语句创建数据库表student 该表中包含了学号 姓名 性别 院系 出生日期和入学成绩6个字段 第5章结构化查询语言SQL 2 修改表结构修改表结构命令以ALTERTABLE开头 后面根据不同的操作使用不同的命令动词 增加字段格式 ALTERTABLEADD功能 对指定的表增加一个字段 例5 为学生student表增加一个照片字段 ALTERTABLEstudentADD照片G 删除字段格式 ALTERTABLEDROP功能 在指定的表中删除指定字段 例5 删除学生student表中的照片字段 ALTERTABLEstudentDROP照片 第5章结构化查询语言SQL 修改字段格式 ALTERTABLEALTER功能 在指定的表中修改指定字段的类型和宽度 不能修改字段名 例5 把学生student表中的姓名字段宽度由6改为10 ALTERTABLEstudentALTER姓名c 10 修改字段名格式 ALTERTABLERENAMETO功能 在指定表中将字段名 更名为字段名 例5 把课程表course中的课名改为课程名称 ALTERTABLEcourseRENAME课名TO课程名称说明 执行一条命令只能对一个字段进行一种操作 第5章结构化查询语言SQL 3 删除表DROPTABLE语句用于删除表的定义和表中的数据 建立在表上的索引 视图等 格式 DROPTABLE RECYCLE 功能 删除指定的表 说明 将显示 移去 对话框 从中可删除指定的表 RECYCLE 将删除的表文件放到回收站 以后可以恢复 第5章结构化查询语言SQL 3 视图的创建和删除视图是一个虚表 是由一个或者几个基本表导出的表 它本身不独立存储在数据库中 即数据库中只存放视图的定义而不存放视图对应的数据 这些数据仍存放在对应的表中 当基本表中的数据发生了变化 视图中对应的数据也随之改变 第5章结构化查询语言SQL 1 创建视图视图一经创建 就可以通过它来查询数据库中数据 又可以用来定义新的视图 格式 CREATEVIEW AS功能 创建一个由视图名指定的视图 用视图可以简化基本表的浏览 例如只想了解学生表中的学号 姓名和院系 可以从学生表student创建一个视图 CREATEVIEWstuyxASSELECT学号 姓名 院系FROMstudent其中 stuyx是视图名 省略了视图的列名 隐含是SELECT查询语句中的列名 例如 可以通过创建stu jsj视图查看金融系的学生内容 语句如下 CREATEVIEWstu jsjASSELECT FROMstudentWHERE院系 金融 第5章结构化查询语言SQL 2 查询视图视图定义后 用户就可以像基本表一样对视图进行查询 格式 SELECT FROM 3 删除视图格式 DROPVIEW视图删除后 其定义的以及在它基础上再建立的其他视图将自动删除 由于视图是从基本表中衍生出来的 所以不能进行结构修改 若要改变视图结构 则只能删除后重新定义视图 第5章结构化查询语言SQL 5 1 3SQL数据查询功能数据查询是数据库的核心操作 数据查询是指根据用户的需要 从数据库中提取所需的数据 SQL提供了SELECT语句实现查询 该语句具有灵活的使用方式和丰富的功能 既可以完成相对简单的单表查询 又可以完成复杂的多表连接查询和嵌套查询 第5章结构化查询语言SQL 1 SQL查询命令一般格式 SELECT ALL DISTINCT FROM WHERE GROUPBY HAVING ORDERBY ASC DESC 功能 根据WHERE子句的条件表达式 从FROM子句指定的基本表或视图中 找出满足条件的记录 再按SELECT子句中的目标表达式 选出记录中的属性值 形成结果表 如果有GROUPBY子句 则将结果按分组列名的值进行分组 该属性列值相等的记录为一个组 如果GROUPBY子句还带有HAVING短语 则只有满足指定条件的组才能输出 如果有ORDERBY子句 则结果还要按排序字段的值进行升序或降序排列 第5章结构化查询语言SQL 说明 1 在使用SQL查询语句时 无论数据库是否打开 FROM子句中指定的表所在数据库将自动打开 2 由于FoxPro系统中将Enter键解释为一条语句的结束 在整个语句结束前只能连续写 不能用Enter键来换行 在SQL中 为了使查询结构清晰 往往把SELECT子句 FROM子句及各层嵌套以分号作为分隔符 表示下一行为本行的后续 为了便于理解 还是以学生选课数据库stsc为例说明SELECT语句的基本用法 学生选课数据库stsc由三个基本表组成 学生表student 学号 姓名 性别 出生日期 院系 入学成绩 成绩表score 学号 课程编号 成绩 课程表course 课程编号 课程名称 学分 第5章结构化查询语言SQL 2 简单查询简单查询是基于单表的查询 由SELECT和FROM短语构成无条件查询或者由SELECT FROM和WHERE短语构成有条件查询 1 选择表中的若干列例5 6查询学生表student中所有院系 SELECT院系FROMstudent该结果中有重复值 若要去掉重复值只需加上DISTINCT短语 SELECTDISTINCT院系FROMstudent例5 7查询全体学生的详细记录 SELECT FROMstudent其中的 表示要查询所有的列 例5 8查询全体学生情况 并将结果按入学成绩升序排序 SELECT FROMstudentORDERBY入学成绩ASC结果表中的记录自动按入学成绩的升序排序 第5章结构化查询语言SQL 2 用WHERE子句选择满足条件的记录例5 9查找中文系全体学生的名单 SELECT姓名FROMstudentWHERE院系 中文 例5 10查找缺少成绩的学生学号和课程号 SELECT学号 课程编号FROMscoreWHERE成绩ISNULL例5 11查找姓 王 的所有学生姓名和院系 SELECT姓名 院系FROMstudentWHERE姓名LIKE 王 第5章结构化查询语言SQL 3 连接查询若查询涉及到两个或两个以上的表 就要用到连接查询 1 连接由于SQL的高度非过程化 用户只需要在FROM子句中指出要用到的表名 在WHERE中指出连接条件 连接过程将由系统自动完成 连接条件用来连接两个表的条件称为连接条件 其一般格式为 此外 连接条件还可以用下面的形式 BETWEEN AND 连接条件中的列名称为连接字段 条件中的各连接字段必须是可比的 等值连接和非等值连接当连接运算符为 时 称为等值连接 使用其他运算符称为非等值连接 第5章结构化查询语言SQL 例5 12查询选修课程编号为 9001 的学生学号和姓名 学生的姓名在student表中 学生选课信息在score成绩表中 所以本查询实际上同时student和score两个表中的数据 这两个表之间的联系是通过两个表中都有 学号 字段实现的 要查询学生及其选修课程的情况 就必须将这两个表中学号相同的记录连接起来 这是一个等值连接 SQL语句为 SELECTstudent 学号 姓名FROMstudent score WHEREstudent 学号 score 学号AND课程编号 9001 注意 student表与score表中均包含学号字段 访问时要加前缀 第5章结构化查询语言SQL 4 嵌套查询在SQL语言中 一个SELECT FROM WHERE语句称为一个查询块 将一个查询块嵌套在另一个查询块的WHERE子句或HANVIG短语中的查询称为嵌套查询 嵌套查询使用户可以用多个简单查询构造复杂的查询 从而增强SQL语言的查询能力 例5 13用嵌套查询实现例5 12的语句是 SELECT学号 姓名FROMstudent WHERE学号IN SELECT学号FROMscoreWHERE课程编号 9001 在本例中 下层查询块SELECT学号FROMscoreWHERE课程编号 9001 是嵌套在查询块SELECT学号 姓名FROMstudentWHERE学号IN中的 上层查询块又称为 外层查询 或 父查询 下层查询块称为 内层查询 或 子查询 一个子查询还可以嵌套其他子查询 第5章结构化查询语言SQL 5 计算查询SQL提供了称为库函数的常用统计函数 这些库函数增强了查询功能 进一步方便了用户 这些库函数及其功能如下 COUNT 对指定字段进行计数SUM 求指定字段值的总和 该字段必须为数值 AVG 求指定字段值的平均值 该字段必须为数值 MAX 求指定字段中的最大值MIN 求指定字段中的最小值以上函数不可以嵌套使用 在使用库函数查询时 常用AS来指定列名 第5章结构化查询语言SQL 例5 14查询课程总数 SELECTCOUNT AS课程总数FROMcourse在course表中课程编号的值唯一 所以统计课程总数就是统计表中的记录数 例5 15查询选修了课程学生人数 SELECTCOUNT DISTINCT学号 AS选课人数FROMscore由于每个学生选课不止一门课 score表中一个学生可能有多个记录 因此在COUNT函数中用DISTINCT去掉重复记录 例5 16统计各课程选修的学生人数 SELECT课程编号 COUNT 学号 AS学生人数FROMscoreGROUPBY课程编号本例对score按课程编号分组 对每一组用函数COUNT求出学生人数 第5章结构化查询语言SQL 6 查询结果输出 输出部分结果在SELECT语句中常使用TOPn PERCENT 短语来显示满足条件的前几条记录 不带PERCENT参数时 n是1 32767之间的整数 说明显示前n条记录 使用PERCENT参数时 n是0 01 99 99之间的实数 说明显示查询结果中前百分之多少的记录 TOP短语要与ORDERBY短语同时使用才有效 例5 17查询输出入学成绩在前3名的学生SELECT TOP3FROMstudentORDERBY入学成绩DESC例5 18查询输出入学成绩在前3 的学生SELECT TOP3PERCENTFROMstudentORDERBY入学成绩DESC 输出去向在SELECT语句中INTO或TO短语 可以指定查询结果的输出去向 第5章结构化查询语言SQL 例5 19将所有女同学的信息保存于临时表TEMP中 SELECT FROMstudentWHERE性别 女 INTOCURSORTEMP例5 20将入学成绩大于450分的学生信息保存于高分 DBF表中 SELECT FROMstudentWHERE入学成绩 450INTODBF高分例5 21将中文系的学生信息存放到文件名为 中文 的文本文件中 SELECT FROMstudentWHERE院系 中文 TOFILE中文 第5章结构化查询语言SQL 5 1 4SQL操纵功能SQL的数据操作功能是指对已经存在的数据表进行记录的插入 删除和修改操作 SQL的数据操作包括三个语句 INSERT 插入记录 DELETE 删除记录 和UPDATE 修改记录 第5章结构化查询语言SQL 1 插入数据 1 命令格式 INSERTINTO 字段名 字段名 VALUES 表达式1 表达式2 功能 在表尾追加一条包含指定字段值的记录 例5 22给成绩表添加一条选课记录 INSERTINTOscore 学号 课程编号 成绩 VALUES 03036003 001 89 说明 命令中的各表达式和各字段之间相对应的 其数据类型应相同 若插入命令中表的每一个字段都有具体的值 那么字段名列表可以省略 若只给出部分字段的值 那么必须在命令中列出对应的字段名 如上列的命令可改为 INSERTINTOscoreVALUES 03036003 001 89 2 命令格式2 INSERTINTOFROMARRAY 功能 从数组或内存变量中导入记录 第5章结构化查询语言SQL 2 删除数据命令格式 DELETEFROM WHERE 功能 从指定的表中对满足条件的那些记录作删除标记 说明 该命令执行的是逻辑删除 若要彻底删除记录 还需要使用PACK命令 如果省略条件子句 表示删除表中所有记录 但是该表的结构仍然存在 例5 23从课程表中删除课程号为 001 的记录 DELETEFROMcourseWHERE课程编号 001 第5章结构化查询语言SQL 3 修改数据修改数据是指修改指定表中满足条件的记录 命令格式 UPDATESET WHERE 功能 按SET子句中的表达式修改记录的相应的字段值 如果省略条件 表示表中所有记录都要修改 否则仅修改满足条件的部分记录 例5 24将学生表中学号为 03036003 的学生姓名改为 王平 UPDATEstudentSET姓名 王平 WHERE学号 03036003 例5 25将所有学生的入学成绩加上5分 UPDATEstudentSET入学成绩 入学成绩 5 第5章结构化查询语言SQL 5 2用SQL进行表的基本操作案例案例说明本案例要求运用SQL语句的定义功能 创建 学生 成绩 课程 数据库Stsc 然后在该数据库中创建表 并建立表间联系 基本要求如下 1 用SQL语句创建数据库stsc 2 在该数据库下创建3张数据库表 分别为 学生表student 成绩表score 课程表course 3 设置学生表的学号为主索引 成绩表的学号和课程编号为候选索引 课程表的课程编号为主索引 4 建立学生表和成绩表 课程表和成绩表一对多关联 5 设置数据库表的完整约束性条件 6 向数据库表中添加记录 第5章结构化查询语言SQL 5 2 1操作步骤 1 创建数据库stsc 2 创建数据库表在命令窗口中输入CREATETABLE命令 分别建立学生表student 成绩表score 课程表couse 并要求对学生表中的 学号 和课程表中的 课程编号 字段建立主索引 对成绩表中的 学号 和 课程编号 字段建立候选索引 在新建成绩表 score 命令中用REFERENCES子句建立了该表与其他两个表的关联 3 修改表结构 4 给成绩表添加一条记录或从数组中导入记录 第5章结构化查询语言SQL 5 2 2本节知识点1 完整性约束条件在案例中用到了SQL数据定义功能中的完整性约束条件的使用 2 使用SQL插入命令当表中定义了主索引或候选索引 相应的字段设置成不能为空时 VisuaiFoxPro的INSERT或者APPEND命令就无法使用了 因此这里使用SQL的插入命令 第5章结构化查询语言SQL 5 3用SQL查询命令查询数据案例案例说明本案例要求在 学生 成绩 课程 数据库基础上 运用SQL查询命令 对stsc数据库中数据实现单表的简单查询 多表的连接查询和复杂的嵌套查询 灵活地运用数据库内的数据为实际应用服务 基本要求如下 1 用SQL的查询命令有选择地显示表中的部分列 2 利用SQL查询功能实现计算查询和分组计算查询 3 用SELECT命令进行多表的连接查询 4 用嵌套查询构造复杂的查询 第5章结构化查询语言SQL 5 3 1操作步骤1 用简单查询实现表中部分列和记录的显示 1 查询全体学生的学号和姓名 在命令窗口中键入下列命令 SELECT学号 姓名FROMstudent 2 查询全体学生的姓名和年龄 由于表中没有直接的年龄字段 因此可以在SQL查询命令中运用表达式来实现 在命令窗口中键入下列命令 SELECT姓名 year 2003 09 01 year 出生日期 as年龄FROMstudent 第5章结构化查询语言SQL 3 查询学校的院系名称 由于通过学生表可以看到学校的院系名单 但是会出现院系名重复 因此用DISTINCT子句去掉重复 命令如下 SELECT院系DISTINCTFROMstudent 4 查找入学成绩在460分至500分之间的学生信息 并按入学成绩降序排序 在命令窗口中输入下列命令 SELECT FROMstudent WHERE入学成绩BETWEEN460AND500 ORDERBY入学成绩DESC 5 查找金融系和建筑系学生的姓名和出生日期 命令如下 SELECT姓名 出生日期 院系FROMstudent WHERE院系in 金融 建筑 第5章结构化查询语言SQL 2 利用SQL查询功能实现实现计算查询和分组计算查询 1 求女生的人数 平均入学成绩 往常入学成绩最高分和入学成绩最低分 在SQL中通过计算查询可以直接对查询的结果进行计算 命令如下 SELECTCOUNT AS女生人数 AVG 入学成绩 AS平均入学成绩 MAX 入学成绩 AS最高分入学成绩最高分 MIN 入学成绩 AS入学成绩最低分 FROMstudentWHERE性别 女 2 查询各门课程的平均成绩 要统计课程的平均分 需要用查询命令中GROUP子句将课程分组 然后计算平均分 命令如下 SELECT课程编号 AVG 成绩 AS课程平均分FROMscoreGROUPBY课程编号 3 查询课程的平均成绩高于70分的课程号和平均成绩 在分组计算的结果上再进行筛选 可以用HAVING子句实现 HAVING子句总是跟在GROUPBY后面的 不可单独使用 查询命令如下 SELECT课程编号 AVG 成绩 AS课程平均分 FROMscoreGROUPBY课程编号HAVINGAVG 成绩 70 第5章结构化查询语言SQL 3 用SELECT命令进行多表查询 在实际应用中 经常碰到同时要用到两个表或更多表的数据的问题 用SELECT的连接查询就能解决这样的问题 1 查询每个学生的选课情况 学生信息在student表中 选课信息在score表中 所以本查询涉及student与score两个表中的数据 这两个表的联系是通过两个表中都具有的字段 学号 实现的 SQL语句为 SELECTstudent score 课程编号 score 成绩 FROMstudent scoreWHEREstudent 学号 score 学号 2 查询所有学生的平均成绩 本题需要从score表中查询并统计各个学生的平均成绩 然后按学号进行分组 查询命令 SELECTstudent 学号 student 姓名 AVG score 成绩 AS 平均成绩 FROMstudent scoreWHEREstudent 学号 score 学号GROUPBYscore 学号 第5章结构化查询语言SQL 3 查询平均成绩在前三名的学生的成绩 本题不仅要查询出所有学生的平均成绩 而且还要按平均成绩进行降序排序 最后只输出前三名的学生的信息 查询命令为 SELECTstudent 学号 student 姓名 AVG score 成绩 AS 平均成绩 TOP3FROMstudent scoreWHEREstudent 学号 score 学号 GROUPBYscore 学号ORDERBY平均成绩DESC 第5章结构化查询语言SQL 4 用嵌套查询构造新的数据关