数据库层次结构.ppt

第6章关系数据库标准语言SQL 国家级 十一五 规划教材配套电子教案 第1节SQL概述第2节数据查询第3节数据操纵第4节数据定义 本章内容 第1节SQL概述 一 SQL语言简介SQL StructuredQueryLanguage 结构化查询语言 其主要功能是同各种数据库建立联系 进行沟通 目前 绝大多数流行的关系型数据库管理系统 如Oracle Sybase MicrosoftSQLServer Access等都采用了SQL语言标准 二 SQL语言的特点 1 是一种一体化的语言 包括数据定义 数据查询 数据操纵和数据控制功能 2 是一种高度非过程化的语言 SQL语言非常简洁 3 SQL既是自含式语言 又是嵌入式语言 可以用同一种语法结构提供两种使用方式 4 SQL语言采用面向集合的操作方式 核心是查询 第2节数据查询 一 SELECT语句的语法 SELECT命令的常用格式如下 SELECT FROM WHERE GROUPBY ORDERBY INTO SELECT子句 说明要查询的数据 对应于 查询设计器 中的 字段 选项卡 FROM子句 说明要查询的数据来自哪个表或哪些表 对应于 查询设计器 中 添加表或视图 操作 WHERE子句 说明查询条件 对应于 查询设计器 中的 筛选 选项卡 GROUPBY子句 用于对查询结果进行分组 对应于 查询设计器 中的 分组 选项卡 第2节数据查询 查询结果输出选项 来指定查询结果的输出去向 对应于 查询设计器 中 查询去向 对话框 查询结果显示选项 ALL DISTINCT TOPn PERCENT 关键词用来控制查询结果的显示情况 对应于 查询设计器 中的 杂项 选项卡 一 SELECT语句的语法 续 第2节数据查询 二 简单查询 例6 1 检索公寓表中的所有记录 命令 select from公寓说明 通配符 来设定返回表中的所有列 例6 2 从学生表中检索所有系 命令1 select系from学生命令2 selectDISTINCT系AS系部名称from学生说明 DISTINCT关键词的作用是去掉重复值 AS指定了输出字段的标题 例6 3 检索学费不低于4000元的专业简称及其收费情况 命令 select专业简称 学费from专业where学费 4000 第2节数据查询 二 简单查询 例6 4 检索出经贸系和信息系贫困学生的学号 姓名和所在班级 命令 select学号 姓名 班级from学生 where家庭情况 贫困 AND 系 经贸系 OR系 信息系 说明 这个SQL语句较长 分为两行来写 为续行符 这个查询的条件很简单 但初学者很容易出错 往往把查询要求中的 和 理解成逻辑运算符中的 AND 最后的查询条件写为 where系 信息系 AND系 经贸系 从而查不到符合条件的记录 三 简单的联接查询 例6 5 检索欠费学生的基本情况和所欠金额 分析 查询需要输出的信息 学生情况和欠费金额 分别来源于学生表和交费表 分析得知两表之间存在一对一的联系 可通过公共字段 学号 建立 这样的查询一般用联接查询来实现 命令 select学生 欠交金额from学生 交费 where学生 学号 交费 学号AND欠交金额 0说明 学生 代表学生表中所有的字段 当from之后有多个表时 含有公共字段的表达式 必须用表名前缀直接指明公共字段所属的表 如 学生 学号 交费 学号 而非公共字段则可省略表前缀 如 欠交金额 第2节数据查询 三 简单的联接查询 例6 6 检索每个学生所在专业与应交的学费 所住公寓与住宿费的情况 命令 select学号 姓名 专业 专业简称 学费 公寓 公寓类别 住宿费 from学生 专业 公寓 where学生 专业 专业 专业简称AND学生 公寓类别 公寓 公寓类别 第2节数据查询 所谓嵌套查询就是 查询所输出的信息来自一个表 而查询的条件却涉及到一个或多个表 四 嵌套查询 例6 7 检索至少有一个学生欠费的系 命令 selectDISTINCT系from学生where学号IN select学号from交费where欠交金额 0 说明 这个检索命令由两个SELECT子句构成 即内层查询 子查询 和外层查询 子查询查到的结果是欠费学生的学号 外层查询在利用这个学号在学生表中查找欠费学生所在的系 因为查询的结果有重复 而我们只对欠费的系感兴趣 所以 用DISTINCT关键词去掉了重复值 注意 子查询用一对圆括号括起来 且子查询不能嵌套 第2节数据查询 四 嵌套查询 例6 8 检索和会计学专业收费相同的专业 命令 select专业简称from专业 where专业简称 会本 AND学费 select学费from专业where专业简称 会本 说明 命令用专业简称 会本 条件来去掉 会计学 专业本身 第2节数据查询 第2节数据查询 在查询中 还可以加入运算符 如算术运算符和函数运算符 对原始表中的数据进行计算 常用的统计函数有 计数函数COUNT 求和函数SUM 求平均值函数AVG 求最大值函数MAX 求最小值函数MIN 五 简单的计算查询 例6 9 输出每个学生的学号 姓名和年龄 分析 表中并没有年龄字段 需要根据出生日期计算每个人的年龄 命令 select学号 姓名 YEAR DATE YEAR 出生日期 AS年龄from学生 例6 10 算出生源所在地的数目 命令 selectCOUNT DISTINCT籍贯 from学生 第2节数据查询 五 简单的计算查询 COUNT 函数的使用要点 如要对表中记录个数进行计数 一般用COUNT 而对其他字段的统计则用COUNT DISTINCT列名 例6 11 计算所有学生的人数 命令1 selectCOUNT from学生命令2 selectCOUNT AS总人数from学生说明 第二条命令修改了查询结果的列名 这样方便于对查询结果的浏览 第2节数据查询 例6 12 计算学生所欠的总金额数 命令 selectsum 欠交金额 from交费 SUM 函数 例6 13 计算信息系和经贸系学生所欠的金额数 命令 selectsum 欠交金额 from交费where学号IN select学号from学生where系 信息系 OR系 经贸系 例6 14 计算人均欠费金额 命令 selectAVG 欠交金额 from交费 AVG 函数 MAX 函数 第2节数据查询 例6 15 查找信息系欠费金额最高的学生学号 姓名及所欠金额 命令 select学生 学号 姓名 欠交金额from学生 交费 where学生 学号 交费 学号AND系 信息系 AND欠交金额 selectMAX 欠交金额 from学生 交费where学生 学号 交费 学号AND系 信息系 例6 16 查找学费最低的专业 命令 select专业简称 学费from专业where学费 selectMIN 学费 from专业 MIN 函数 第2节数据查询 六 分组与计算查询 有时需要对查询结果进行分类统计 这就要用到GROUPBY子句 GROUPBY子句可对数据进行分组 若要限定分组的条件 则需启用HAVING子句 例6 17 统计各系欠费学生的人数 并按欠费人数降序排序 命令 select系AS系部名称 COUNT AS欠费人数from学生groupby系 where学号IN select学号from交费where欠交金额 0 ORDERBY欠费人数 第2节数据查询 六 分组与计算查询 例6 18 列出欠费学生人数超过2人的系部 命令 select系AS系部名称 COUNT AS人数from学生 groupby系having COUNT 2 where学号IN select学号from交费where欠交金额 0 说明 HAVING子句总是跟在GROUPBY子句之后 不可以单独使用 也不可以在其后使用子查询 第2节数据查询 为了方便浏览 常常要把查询结果按一定的标准排序 在SQL语言中 可以用ORDERBY子句按一列或多列对查询结果进行升序 ASC 或降序 DESC 排列 七 排序 例6 19 按学费从低到高的顺序列出全部专业信息 命令 select from专业orderby学费说明 如省略ASC DESC关键字 查询结果默认按升序 ASC 进行排列 第2节数据查询 例6 20 输出学生的学号 姓名 性别 籍贯 班级信息 查询结果按籍贯排序 籍贯相同的再按性别排序 命令 select学号 姓名 性别 班级 籍贯from学生orderby5 3说明 排序关键字可以是一个字段或字段表达式 也可以是一个数值表达式 这个数值是表或查询结果中列的位置 其中最左边的列的编号为1 例6 21 按系列出欠费学生的学号 姓名 系 班级 欠交金额 同一个系的按欠交金额降序排列 命令 select学生 学号 姓名 系 班级 欠交金额from学生 交费where学生 学号 交费 学号AND学生 学号IN select学号from交费where欠交金额 0 orderby系 欠交金额DESC 第2节数据查询 八 查询结果的显示和输出 1 显示部分查询结果 TOPn Percent 使用TOPn PERCENT 短语限制返回的记录行数 TOPn说明返回n行 而TOPnPERCENT时 说明n是一个百分数 指定返回的行数等于总行数的百分之几 例6 22 显示学费最高的前5个专业的信息 命令1 select TOP5from专业orderby学费DESC 专业简称说明 如果排序关键字存在重复值 则TOP关键字将失效 记录并没有按指定的数目显示 为了避免这种情况出现 可以在其后加入一个没有重复值的主键字段 第2节数据查询 八 查询结果的显示和输出 例6 23 列出84年以后出生的学生信息 只显示其中50 的记录即可 命令 select TOP50percentfrom学生 where出生日期 1983 12 31 orderby出生日期 2 将查询结果存放到永久表文件 DBF TABLE 中在SELECT语句中使用短语INTODBF TABLE 可将查询结果存放到永久表 例如 将 例6 21 的查询结果按降序排列存放到 各系学生欠费金额一览表 中 命令为 select学生 学号 姓名 系 班级 欠交金额from学生 交费 where学生 学号 交费 学号AND学生 学号IN select学号from交费where欠交金额 0 orderby系 欠交金额DESC intotable各系学生欠费金额一览表 第2节数据查询 3 将查询结果存放到临时表文件 CURSOR 中使用短语INTOCURSOR 可以将查询结果存放到临时表文件中 当查询结束后 该临时表是当前文件 但仅是只读的dbf文件 关闭文件时该临时表将自动删除 临时表常在程序设计中作为表格 列表框或组合框的数据源 例如 将查询到的专业信息存放到临时表文件ZYtemp中 命令为 select from专业intocursorZYtemp 4 将查询结果存放到数组 ARRAY 中VisualFoxPro程序设计中经常将表中的数据和数组数据进行交换 我们可以用INTOARRAY将查询结果存放到一个二维数组中 数组的每一行对应一条记录 每一列对应查询结果中的一列 第2节数据查询 5 将查询结果存放到文本文件 FILE 中可以用TO FILE 短语将查询结果存放到文本文件中 同时显示在屏幕上 默认是 TXT文件 例如 将公寓表中的信息存放到文本文件gy中 命令如下 select from公寓tofilegy 例如 将公寓表中的记录存放到数组GYarray中 命令如下 select from公寓intoarrayGYarray 第2节数据查询 6 将查询结果输出到屏幕 SCREEN 例如 将公寓表中的信息显示在屏幕上 命令如下 select from公寓toscreen 7 将查询结果输出到打印机 PRINTER 使用短语TOPRINTER PROMPT 可以将查询结果输出到打印机 如果使用了PROMPT 在开始之前会打开 打印设置 对话框 查询去向一览表 第2节数据查询 九 超联接查询 格式 FROM DatabaseName Table AS Local Alias INNER LEFT OUTER RIGHT OUTER FULL OUTER JOIN DatabaseName Table AS Local Alias ONJoinCondition 从上面的格式可看出 超联接分为内联接和外联接 外联接又分为左联接 右联接和全联接 且联接的条件在FROM子句中由JOINON短语来指定 在 查询设计器 中的操作见第5章中 图5 15设定联接条件 第2节数据查询 九 超联接查询 1 内联接使用 INNER JOIN短语的联接称为内联接 与以前的自然联接效果等同 即只有满足联接条件的记录才出现在查询结果中 命令1 select学生 学号 姓名 班级 应交金额 已交金额 欠交金额 from学生INNERJOIN交费ON学生 学号 交费 学号命令2 select学生 学号 姓名 班级 应交金额 已交金额 欠交金额 from学生JOIN交费ON学生 学号 交费 学号命令3 select学生 学号 姓名 班级 应交金额 已交金额 欠交金额 from学生 交费where学生 学号 交费 学号以上3种格式的命令是等价的 第2节数据查询 2 左联接使用LEFT OUTER JOIN短语的联接称为左联接 即除满足联接条件的记录出现在查询结果中外 第一个表 JOIN左侧的表 中不满足条件的记录也出现在查询结果中 命令 select学生 学号 姓名 班级 应交金额 已交金额 欠交金额 from学生LEFTJOIN交费ON学生 学号 交费 学号 3 右联接使用RIGHT OUTER JOIN短语的联接称为右联接 即除满足联接条件的记录出现在查询结果中外 第二个表 JOIN右侧的表 中不满足条件的记录也出现在查询结果中 第2节数据查询 命令 select学生 学号 姓名 班级 交费 学号 应交金额 已交金额 欠交金额 from学生RIGHTJOIN交费ON学生 学号 交费 学号 4 全联接使用FULL OUTER JOIN短语的联接称为全联接 即除满足联接条件的记录出现在查询结果中外 两个表中不满足条件的记录也全部出现在查询结果中 命令 select学生 学号 姓名 班级 交费 学号 应交金额 已交金额 欠交金额 from学生FULLJOIN交费ON学生 学号 交费 学号 说明 出现在查询结果中不满足联接条件的记录将另一表中的字段值设置为空值 NULL 第2节数据查询 十 别名与自联接查询 多表联接查询中 我们经常用表名作前缀来区分公共字段 但是如果表名较长 联接的表比较多 就会很麻烦 为此 SQL提供了在FROM子句中为表定义别名的功能 格式为 AS 例如 要查询每个学生的学号 姓名 所学课程名称 该门课的得分 就要涉及到3个表 2个联接 命令1 selectstudent 学号 姓名 课程名称 成绩 fromstudent score course wherestudent 学号 score 学号ANDscore 课程号 course 课程号如果使用别名 查询命令就会简单一些 第2节数据查询 十 别名与自联接查询 命令2 selectA 学号 姓名 课程名称 成绩fromstudentA scoreB courseC whereA 学号 B 学号ANDB 课程号 C 课程号上述例子中 别名并不是必须的 但是在表的自联接查询中 别名就是必不可少的 SQL不仅可以对多个表进行联接查询 也可以将同一个表与其自身进行联接 这种联接就称为自联接查询 也称递归查询 例6 24 根据课程关系列出每门课程以及其先修课程名单 命令 selectA 课程名称 的先修课程是 B 课程名称fromcourseA courseB whereB 课程号 A 先修课号 toscreenPLAIN 第2节数据查询 十一 内外层相关嵌套查询 自联接是一种出自同一个表同一值域的两个不同字段联接的递归查询 其实同一个表中的同一字段在特殊情况下也需要进行联接 另外 在6 2 4讲述的嵌套查询都是外层查询依赖于内层查询的结果 而内层查询与外层查询无关 事实上 有时也需要内 外层相关的查询 这时内层查询的条件需要外层查询提供值 而外层查询的条件需要内层查询的结果 例6 25 检索出每个学生所考课程中得分最高的成绩信息 命令 selectout fromscoreoutwhere成绩 selectMAX 成绩 fromscoreinner1 whereout 学号 inner1 学号 第2节数据查询 十一 内外层相关嵌套查询 说明 在这个查询中 外层查询和内层查询使用同一个表 成绩 dbf 外层查询给成绩表指定别名out 内层查询给成绩表指定别名inner1 外层查询提供out表中每个记录的学号给内层查询使用 内层查询利用这个学号 确定该学生所考课程中的最高成绩 随后外层查询再根据out表中的成绩与该成绩进行比较 如果相等 则该记录被选择 第2节数据查询 十二 集合并运算 有时需要把两个或两个以上的查询结果合并到一个结果中 这就要用到UNION操作 也即集合并运算 进行并运算 要求两个查询结果具有相同的字段个 并且对应字段应具有相同的数据类型和取值范围 第2节数据查询 十二 集合并运算 例6 26 把score表和new score表的数据合并存放到临时表temp中 命令 select fromscore UNION select fromnew score intocursorhebing 第2节数据查询 十三 特殊运算符的使用 SQL支持的运算符如下 逻辑运算符 用于多条件的逻辑连接 NOT 非 AND 与 OR 或 比较运算符 大小比较 范围运算符 BETWEEN AND 和NOTBETWEEN AND 列表运算符 IN 项1 项 NOTIN 项1 项2 模式匹配符 LIKE NOTLIKE空值判断符 ISNULL ISNOTNULL 第2节数据查询 十三 特殊运算符的使用 1 范围运算符范围运算符BETWEENAND用于判断表达式值是否在指定的范围 例6 27 在成绩管理数据库的student表中检索出年龄在19岁到20岁之间的学生信息 命令 select fromstudentwhere年龄BETWEEN19AND20等价于 select fromstudentwhere年龄 19AND年龄 20如果要检索年龄在在19岁以下20岁之上的学生信息 则用命令 select fromstudentwhere年龄NOTBETWEEN19AND20 第2节数据查询 十三 特殊运算符的使用 2 列表运算符列表运算符IN用于判断表达式是否为列表中的指定项 例6 28 在GXSF数据库的学生表中检索出晋东南地区的学生信息 命令 select from学生whereright 籍贯 4 in 晋城 长治 第2节数据查询 3 模式匹配符模式匹配符LIKE常用于模糊查找 可使用以下通配字符 百分号 可匹配任意类型和长度的字符 下划线 可匹配单个任意字符 例6 29 从GXSF数据库的学生表中检索出姓 李 的学生信息 命令1 select from学生where姓名LIKE 李 命令2 select from学生where姓名LIKE 李 命令3 select from学生where姓名LIKE 李 4 空值判断符空值判断符ISNULL用于判断表达式是否为空值 例6 30 从销售记录数据库的商品信息表中检索价格尚未确定的商品信息 命令 select from商品信息where单价ISNULL 第2节数据查询 5 谓词查询谓词EXISTS用来检查子查询中是否有结果返回 例6 31 检索销量至少有一台的商品的所有信息 命令 select from商品信息whereEXISTS select from购买信息where商品号 商品信息 商品号 等价于 select from商品信息where商品号IN select商品号from购买信息 相应的 如果要检索还没有销量的商品信息 命令如下 select from商品信息whereNOTEXISTS select from购买信息where商品号 商品信息 商品号 一 插入 SQL的数据操纵主要是指对表中的记录进行插入 更新和删除的操作 第3节数据操纵 1 插入完整记录格式 INSERTINTO表名VALUES value1 value2 例如 向成绩管理数据库的STUDENT表中插入一条记录 命令为 INSERTINTOSTUDENTVALUES 20020001 刘然 19 女 01 2 插入部分数据格式 INSERTINTO表名 字段名1 字段名2 VALUES value1 value2 例如 向GXSF数据库的学生表中插入一条包含学号 姓名 性别 出生日期 系 籍贯和备注信息的记录 命令为 INSERTINTO学生 学号 姓名 性别 出生日期 系 籍贯 备注 VALUES 20035002 刘凌 男 1986 10 29 经贸系 云南丽江 纳西玉水寨 注意 插入项的顺序和数据类型必须要与表中字段的顺序和数据类型相对应 插入整条记录时每一个字段的值都必须输入 不能省略 二 更新 第3节数据操纵 SQL语言用UNPDATE语句来修改表中数据 格式 UPDATE表名SET字段名1 新的字段值 字段名2 新的字段值 WHERE条件 SET指定要更新的字段及其更新后的值 一次可以更新多个字段 WHERE子句指定要更新的记录应满足的条件 如果没有WHERE 则更新全部记录 例如 算出GXSF数据库的交费表中欠交金额的滞纳金 滞纳金率为0 5 命令为 UPDATE交费SET滞纳金 欠交金额 0 005WHERE欠交金额 0注意 我们在第3章中学习过的ReplaceWith命令与UNPDATE语句的功能是一样的 都用于更新表中数据 所以初学者很容易混淆 读者要熟记各自的语法以免出错 三 删除 第3节数据操纵 SQL语言同样用Delete命令来删除表中的记录 不过语法稍有不同 格式 DELETEFROM表名 WHERE条件 FROM指定要删除的表名 WHERE指定要删除的记录应满足的条件 如果没有WHERE子句 则删除全部记录 例如 删除公寓表中所有记录 命令为 DELETEFROM公寓注意 同第3章学习的DELETE命令一样 SQL的DELETEFROM命令也只是逻辑删除记录 要彻底删除记录还需继续用PACK命令 一 表的定义 第4节数据定义 例如 用SQL命令建立销售记录数据库 1 创建数据库命令 CREATEDATABASE销售记录2 创建商品信息表命令 CREATETABLE商品信息 商品号C 3 PRIMARYKEY 商品名C 15 单价N 8 2 NULL 数量I 厂名C 20 产地C 8 DEFAULT 中国北京 说明 上述命令创建了 商品信息 表 商品号 字段为字符型 宽度为3 PRIMARYKEY指定 商品号 为主关键字 单价 字段为数值型 宽度为8位 小数位数2位 并且允许其值为空值 数量 字段为整型I 产地 字段用DEFAULT设置了默认值 3 创建购买信息表 并建立和商品信息表的联系命令 CREATETABLE购买信息 会员号C 2 商品号C 3 单价N 8 2 数量ICHECK 数量 0 ERROR 数量不能为0 日期D FOREIGNKEY商品号TAG商品号REFERENCES商品信息 说明 上述命令创建了 购买信息 表 用CHECK为 数量 字段设置了有效性规则 用ERROR为规则设置提示信息 用 FOREIGNKEY商品号TAG商品号 在该表的 商品号 字段上建立了一个普通索引 同时说明该字段是联接字段 通过引用商品信息表的主索引 商品号 与商品信息表建立了联系 第4节数据定义 二 表的删除 在第3章中介绍用DELETEFILE来删除表及其他类型文件 用REMOVETABLE命令来移去数据库表 在SQL中删除表的命令为 DROPTABLE表名这种删除是直接删除 所以读者在操作时要慎重 且如果要删除的表属于某个数据库 应先打开该数据库再进行删除 以避免数据库出现不必要的错误提示 例如 删除销售记录数据库中的商品信息表 命令序列为 OPENDATABASE销售记录DROPTABLE商品信息 1 增加字段或修改已有字段类型或宽度的命令格式 ALTERTABLE表名ADD ALTER字段名其中ADD用于增加新的字段 ALTER用于修改现有字段 例如 为购买信息表增加一个折扣字段 折扣范围在 0 1 之间 命令为 ALTERTABLE购买信息ADD折扣N 4 2 CHECK 折扣 0AND折扣 1 ERROR 折扣范围应在 0 1 之间 例如 将商品信息表中厂名字段的宽度由原来的