关系数据库标准语言.ppt

第5章 关系数据库标准语言,5.1 SQL概述 5.2 SQL的数据查询功能 5.3 SQL的定义功能 5.4 SQL的数据操作功能,5.1 SQL概述,SQL是Structured Query Language的缩写，意思为“结构化查询语言” 。 SQL特点： 语言简洁、规范 非过程化 高度灵活化 平台无关性,SQL具有以下功能： 查询数据库中的数据 更新数据库中的数据 设置数据库用户访问的权限 修改数据库表的结构 增加、删除数据库中的表、视图,返回目录,5.2 SQL的数据查询功能,SELECT语句是数据查询语言组中唯一的语句，它包括单表查询、多表连接查询、嵌套查询和集合查询等。 5.2.1 SELECT语法格式 SELECT ALL|DISTINCT TOP|PERCENT , AS FROM , WHERE GROUP BY HAVING ORDER BY ASC|DESC INTO ARRAY |INTO CURSOR |INTO DBF|TABLE |TO FILE ADDITIVE|TO PRINTERPROMPT,【说明】整个语句的含义是，根据WHERE子句的条件表达式，从FROM子句指定的基本表或视图中查找出满足条件的记录，再按SELECT子句中的目标表达式选出记录中的字段值形成结果表。GROUP限定结果分组，ORDER限定结果的排序。,SELECT子句对应关系代数中的投影运算，其后面列出的属性名集合就是需要进行查询的数据集合； FROM说明要查询的数据来自哪个或哪些表，可以对单个表或多个表进行查询； WHERE说明查询条件，即选择元组的条件； GROUP BY短语用于对查询结果进行分组，可以利用它进行分组汇总； HAVING短语必须跟随GROUP BY使用，它用来限定分组必须满足的条件； ORDER BY短语用来对查询的结果进行排序。,5.2.2 简单查询,1. SELECT语句 SELECT语句从数据库中检索数据并将查询结果以表格的形式返回，其结果必是一个关系。 在简单查询中，可以从一个数据表中查询部分或者全部的字段。 【例5.1】查询“学生”表中所有学生的姓名。 SELECT 姓名 FROM 学生 【例5.2】查询“课程”表中所有的信息。 SELECT * FROM 课程,查询结果也可以是通过某一字段或者几个字段计算得到的虚拟字段。 【例5.3】查询“入学成绩”表中，学生的学号、姓名，以及语文、外语、数学的平均成绩。 SELECT 学号,姓名,(语文+外语+数学)/3 FROM 入学成绩 使用“AS”重新为新字段命名的SELECT语句如下。 SELECT 学号,姓名,(语文+外语+数学)/3 AS 平均分 FROM 入学成绩,在查询中，可能产生完全相同的两个元组，用“DISTINCT”关键字可以去掉重复记录。 【例5.4】查询“学生”表中，所有的专业名称。 SELECT DISTINCT(专业) FROM 学生,2.WHERE子句 带上WHERE子句的查询就是条件查询。查询的条件主要包括比较、范围设定、匹配测试、NULL值测试以及组合条件。 （1）比较条件 比较是SQL中最经常使用的查询条件，运算符有=、=、。 【例5.5】查询“学生”表中，计算机专业学生的姓名、学号以及入学成绩。 SELECT 姓名,学号,入学成绩 FROM 学生 WHERE 专业=”计算机”,（2）范围设定 测试表达式的值取自某个范围时，使用NOTBETWEEN.AND.来限定。 【例5.6】查询外语成绩在110分到130分之间的学生的姓名。 SELECT 姓名 FROM 入学成绩 WHERE 外语 BETWEEN 110 AND 130,（3）匹配测试 当查询需要进行字符串匹配时，使用“LIKE”。在匹配测试表达式中使用的通配符百分号（%）代表任意个任意字符，下划线（_）代表一个任意字符。 【例5.7】查询所有姓“王”的同学的信息。 SELECT * FROM 学生 WHERE 姓名 LIKE “王%”,（4）NULL值测试 NULL表示空，意思是还没有确定，与数值“0”不一样，0表示一个确切的数。 在判断NULL值时要使用“IS”语句 【例5.8】查询没有安排上课教师的课程。 SELECT * FROM 课程 WHERE 教师号 IS NULL,（5）组合条件 搜索条件中还可使用AND、OR、NOT将简单的查询条件进行组合，进行复杂的搜索。 【例5.9】查询计算机专业的女生信息。 SELECT * FROM 学生 WHERE 性别=”女” AND 专业=”计算机”,5.2.3 嵌套查询,1.带有比较运算的子查询和使用量词的查询 当子查询的返回结果是个单列时，可以使用、=、!=等或使用带有量词ANY、SOME、ALL的比较运算符进行查询。 其中ANY和SOME是同义词，在进行比较运算时只要子查询中有一行能使结果为真，则结果为真。而ALL则要求子查询中的所有行都使结果为真时，结果才为真。 【例5.10】查询和刘云飞同一个专业的学生的姓名、性别以及出生日期。 SELECT 姓名,性别,出生日期 FROM 学生 WHERE 专业=(SELECT 专业 FROM 学生 WHERE 姓名=”刘云飞”),【例5.11】查询课程成绩高于学号为“110102”的所有科成绩的学生学号（这里并不是每一门课程对应比较）。 SELECT 学号 FROM 选课 WHERE 成绩ALL (SELECT 成绩 FROM 选课 WHERE 学号=”110102”) 【例5.12】查询课程成绩至少高于学号为“110102”的一门课成绩的学生学号以及成绩。 SELECT 学号,成绩 FROM 选课 WHERE 成绩SOME (SELECT 成绩 FROM 选课; WHERE 学号=”110102”),2.使用谓词的查询 子查询中使用最多的是谓词IN，EXISTS和NOT IN，NOT EXISTS，含义为当查询的指定字段值包含（或不包含）在子查询结果字段值表中时条件为真。 IN用来测试集合中的成员，该集合是由SELECT子句产生的一组值的集合。 EXISTS或NOT EXISTS是用来检查在子查询中是否有结果返回。,【例5.13】查询计算机专业学生的学号、所选的课程号以及该门课的成绩。 SELECT 学号,课程号,成绩 FROM 选课 WHERE 学号 IN (SELECT 学号 FROM 学生; WHERE 专业=”计算机”) EXISTS可测试一个子查询的结果是否有元组，当作为子查询结果的集合为非空时则主查询返回结果，否则不返回。NOT EXISTS则与之相反。 【例5.14】查询没有选修任何一门课的学生信息。 SELECT * FROM 学生 WHERE NOT EXISTS (SELECT * FROM 选课; WHERE 学号=学生.学号),5.2.4 联接查询,有一些查询同时涉及到两个或两个以上的表时，这种查询称之为联接查询（也称为多表查询）。 1.简单联接查询 【例5.15】查询所有学生的姓名、学号以及入学时该学生的语文、数学、外语、综合的成绩。 SELECT 学生.姓名,学生.学号,语文,数学,外语,综合 FROM 学生,入学成绩; WHERE 学生.学号=入学成绩.学号 SQL允许在FROM子句中为关系名定义别名，格式为： . 例5.15的查询语句可以写做： SELECT S.姓名,S.学号,语文,数学,外语,综合 FROM 学生 S,入学成绩 R; WHERE S.学号=R.学号,联接条件,2.超联接查询 如果想要将不符合联接条件的记录也查询出来，就要利用超联接查询，超联接分为内联接（也称为等值联接）、左联接、右联接和全联接。其语法结构如下： 【格式】 SELECTFROM INNER|LEFT|RIGHT|FULL JOIN ON WHERE,【说明】 INNER JOIN 或JOIN为内联接，也称为等值联接，按照联接条件进行联接，不满足条件的记录不会出现在查询结果中，是常用的一种联接形式。 LEFT JOIN 为左联接，除满足连接条件的记录出现在查询结果中外，第一个表不满足条件的记录也会出现在查询结果中。 RIGHT JOIN 为右联接，除满足连接条件的记录出现在查询结果中外，第二个表不满足条件的记录也会出现在查询结果中。 FULL JOIN 为全联接，除满足连接条件的记录出现在查询结果中外，两个表中不满足条件的记录也会出现在查询结果中。 ON 指明联接条件，而不能在WHERE后面给出联接条件。,【例5.16】内联接查询。 SELECT 学生.学号, 学生.姓名,入学成绩,数学 FROM 学生 JOIN 入学成绩 ; ON 学生.学号=入学成绩.学号 注意：多个表用“JOIN”语句联接的顺序要与联接条件“ON”的顺序恰好相反。 等价于简单联接查询方式。 SELECT 学生.学号, 学生.姓名,入学成绩,数学 FROM 学生,入学成绩; WHERE 学生.学号=入学成绩.学号,【例5.17】左联接查询。 SELECT 学生.学号,姓名,课程名,成绩 FROM (学生 LEFT JOIN 选课; ON 学生.学号=选课.学号)LEFT JOIN 课程 ON 选课.课程号=课程.课程号 因为是左联接查询，其中学生表中有四个学生记录没有选课，不符合联接条件，同样也被查询出来，该学生没有选课，因此相应的课程名和成绩为空。,【例5.18】右联接查询。 SELECT 学生.学号,姓名,课程名,成绩 FROM (学生 RIGHT JOIN 选课; ON 学生.学号=选课.学号)RIGHT JOIN 课程 ON 选课.课程号=课程.课程号 因为是右联接查询，其中课程表中有的课程记录没有被学生所选，不符合联接条件，同样也被查询出来，该课程没有被学生所选，因此相应的学号和姓名为空。,【例5.19】全联接查询。 SELECT 学生.学号,姓名,课程名,成绩 FROM (学生 FULL JOIN 选课; ON 学生.学号=选课.学号) FULL JOIN 课程 ON 选课.课程号=课程.课程号 全联接查询的结果是左联接查询和右联接查询结果的并集。,5.2.5 排序（ORDER BY子句）,使用ORDER BY子句对按照一个或多个字段对查询结果进行升序（ASC）或降序（DESC）排列，默认为升序排列。 ORDER BY语句只能对最终的查询结果进行排序，在子查询中不能使用。 ORDER BY语句后不能接任何运算表达式，不能按某个表达式的运算结果进行排序。 对于空值排序，升序时空值记录将最后显示，降序时将最先显示。 【例5.20】查询计算机专业的学生信息，结果按入学成绩降序排列。 SELECT * FROM 学生 WHERE 专业=”计算机” ORDER BY 入学成绩 DESC,使用TOP PERCENT语句可以只显示排序之后的前几条记录或前百分之多少条记录。 【例5.21】查询入学成绩前五名的学生信息。 SELECT * TOP 5 FROM 学生 ORDER BY 入学成绩 DESC 【例5.22】查询1001这门课成绩在前50%的学生的学号、成绩。查询结果如图5.21所示。 SELECT * TOP 50 PERCENT FROM 选课 WHERE 课程号=”1001” ORDER BY 成绩 DESC 注意： TOP语句要与ORDER BY语句同时使用才有效。,5.2.6 计算查询与分组查询（GROUP BY 子句）,1.计算查询 有时用户的请求需要对查询的内容进行计算才能得到结果。常用的计算函数如下： COUNT( ) 统计元组个数 COUNT(DISTINCT|ALL ) 统计一列中值的个数。 SUM(DISTINCT|ALL ) 计算某一列值的总和(此列必须是数值型)。 AVG(DISTINCT|ALL ) 计算某一列值的平均值(此列必须是数值型)。 MAX(DISTINCT|ALL ) 计算某一列值的最大值。 MIN(DISTINCT|ALL ) 计算某一列值的最小值。 注意：如果指定DISTINCT短语，则表示在计算时要取消指定列中的重复值。,【例5.23】查询选修了课程的学生的人数。 SELECT COUNT(DISTINCT 学号) FROM 选课 【例5.24】查询“入学成绩”表中，学生外语的平均成绩。 SELECT AVG(外语) AS 外语平均成绩 FROM 入学成绩 其中，使用“AS”关键字对查询结果的属性重新命名。,2.分组查询 GROUP BY子句将查询结果按某一列或多列的值分组，值相等的为一组。同时还可以用HAVING语句对分组的记录加以限定再计算。 HAVING语句不能单独使用只能跟在GROUP BY语句之后，在查询过程中与WHERE语句并不矛盾，WHERE语句限定所有记录，HAVING语句只限定分组。,【例5.25】查询各专业学生的入学成绩平均值。 SELECT 专业,AVG(入学成绩) FROM 学生 GROUP BY 专业 在分组查询时，有时要求分组需要满足某个条件时才可以检索，这时可以用HAVING子句来限定分组。 【例5.26】查询选修了两门（含两门）以上课程的学生的学号以及平均成绩。 SELECT 学号,AVG(成绩) AS 平均成绩 FROM 选课 GROUP BY 学号; HAVING COUNT(*)=2,【说明】HAVING子句和WHERE子句的区别： （1）WHERE子句是用来指定表中各行所应满足的条件，用于基表或视图，而HAVING子句是用来指定每一分组所满足的条件，作用于组，只有满足HAVING条件的那些组才能在结果中被显示。 （2）HAVING子句总是跟在GROUP BY子句之后，不可以单独使用。HAVING子句和WHERE子句不矛盾，在查询中是先用WHERE子句限定元组，然后进行分组，最后再用HAVING子句限定分组。,5.2.7 集合的并运算,使用UNION子句可以进行集合的并运算，即可以将两个SELECT语句的查询结果合并成一个查询结果。 【例5.27】查询“教师”表中教授和副教授的教师号、姓名、性别和职称。查询结果如图5.26所示。 SELECT 教师号,姓名,性别,职称 FROM 教师 WHERE 职称=“教授“; UNION SELECT 教师号,姓名,性别,职称 FROM 教师 ; WHERE 职称=“副教授“,5.2.8 查询去向,1.在查询窗口中显示查询结果 使用SELECT语句查询默认情况下，是将结果显示在查询窗口中。 2.利用临时表文件存放查询结果 使用INTO CURSOR语句可以将查询结果存放在临时表文件中，该表是一个只读的表文件，可以像一般的表文件一样在程序中使用，程序结束关闭文件时该表文件将自动删除。 【例5.28】将所有的课程信息放到临时表tmp中。 SELECT * FROM 课程 INTO CURSOR tmp,3.利用永久表文件存放查询结果 使用INTO DBFTABLE 语句可以将查询结果存放在永久表文件中，该SELECT语句执行完查询之后，该表将以自由表保存并作为当前文件自动打开。 【例5.29】将所有女生的信息放入永久表girls中。 SELECT * FROM 学生 WHERE 性别=”女” INTO TABLE girls,4.利用文本文件存放查询结果 使用TO FILEADDITIVE语句可以将查询结果存放在文本文件中。 如果使用ADDITIVE语句，查询结果将追加到该文本文件尾部，否则将覆盖原有文件。 【例5.30】将每位老师所教授的课程情况存入文本文件course中。 SELECT 教师.姓名,课程.课程名 FROM 教师,课程 WHERE 教师.教师号=课程.教师号; TO FILE course,5.利用数组存放查询结果 使用INTO ARRAY语句将查询结果存放在变量数组中，如果查询结果是多列多条记录，将其自动存在二维数组中，数组的行列与记录的行列元素对应。 在涉及到计算查询时，查询结果一般是一个数值，如平均值、最值等，可以将其存放到一维数组的第一元素中，然后在程序中再进行处理。 【例5.31】计算学生表中有多少位男学生将其存放在变量x中。 SELECT COUNT(*) FROM 学生 WHERE 性别=”男” INTO ARRAY x 【例5.32】将学生的信息存放到二维数组s中。 SELECT * FROM 学生 INTO ARRAY s 二维数组s是一个8行8列的数组，每一行存放某个学生的所有信息字段。例如s(1,1)存放的是“110101”，s(1,2)存放是“王芳”，以此类推。,返回目录,5.3 SQL的定义功能,5.3.1 表的创建 创建表命令： 【格式】 CREATE TABLE ( ( ,)NULL|NOT NULL CHECK 域完整性约束条件 ERROR 出错信息 DEFAULT 默认值 PRIMARY KEY，）,【例5.33】建立“教室管理”数据库，在此数据库中建立“教室”数据库表。 CREATE DATABASE 教室管理 CREATE TABLE 教室(教室编号 C(4) PRIMARY KEY,楼名 C(12),面积 I CHECK(面积0) ERROR “面积应为非负！“ DEFAULT 100) 【说明】其中“教室编号”是主关键字（主索引，用PRIMARY KEY说明），用CHECK为“面积”字段值说明了有效性规则（面积0），用ERROR为该有效性规则说明了出错提示信息“面积应为非负！”，同时为面积设置了一个默认值为100。,建立“自习教室”数据库表，使用FOREIGN KEYTAGREFERENCES 短语，将两个表进行永久性联接。 CREATE TABLE 自习教室 (教室编号 C(4),使用者 C(12),使用时间 C(10), FOREIGN KEY 教室编号 TAG 教室编号 REFERENCES 教室) 其中，“FOREIGN KEY 教室编号”是为“自习教室”表建立普通索引，“TAG 教室编号REFERENCES 教室”是通过“教室编号”字段与“教室”表进行永久性连接。,5.3.2 表的删除,删除表命令： 【格式】DROP TABLE 【说明】该命令将表从数据库中物理性的删除，在执行该命令时最好将数据库打开，再删除其中的表，否则表可以删除，但是表在数据库中的信息将不能删除，此后打开数据库之后将会出现错误。,5.3.3 表结构的修改,修改表结构的命令是ALTER TABLE，该命令有三种格式。 【格式1】ALTER TABLE ADD|ALTER COLUMN 数据类型 (宽度,小数位数) NULL|NOT NULL CHECK域完整性约束条件 ERROR 出错信息DEFAULT 默认值 PRIMARY KEY|UNIQUE REFERENCES TAG 标识名 【说明】该格式可以添加（ADD）新的字段或修改（ALTER）已有的字段。 从命令格式可以看出，该格式可以修改字段的类型、宽度、有效性规则、错误信息、默认值，定义主关键字和联系等。但是不能修改字段名，不能删除字段，也不能删除已经定义的规则等。,【例5.34】向“教室”表中添加一个“楼层”字段。 ALTER TABLE 教室 ADD楼层 N (3) I CHECK楼层0 ERROR “楼层应为非负”; DEFAULT 100 【例5.35】将“教室”表中的“教室编号”字段的宽度由4改为6。 ALTER TABLE教室ALTER教室编号 C(6),【格式2】ALTER TABLE ALTERCOLUMN NULL|NOT NULL SET DEFAULT 默认值 SET CHECK 域完整性约束条件 ERROR 出错信息 DROP DEFAULT DROP CHECK 说明：该格式主要用于定义、修改和删除有效性规则和默认定义。 【例5.36】修改“教室”表中的“面积”字段的有效性规则。 ALTER TABLE 教室 ALTER面积SET CHECK面积30 ERROR “面积应大于30平方米“ ALTER面积SET DEFAULT 40 【例5.37】删除“教室”表中的“面积”字段的域完整性约束条件。 ALTER TABLE 教室 ALTER 面积 DROP CHECK 【例5.38】删除“教室”表中的“面积”字段的默认值。 ALTER TABLE 教室 ALTER 面积 DROP DEFAULT,【格式3】ALTER TABLE DROPCOLUMN SET CHECK 域完整性约束条件 ERROR 出错信息 DROP CHECK ADD PRIMARY KEY 字段名 TAG 标示名 DROP PRIMARY KEY RENAME COLUMN TO 说明：该格式可以删除字段（DROPCOLUMN）、修改字段名（RENAME COLUMN）等。 【例5.39】修改教室表中“面积”字段的名称为“使用面积”。 ALTER TABLE 教室 RENAME COLUMN 面积 TO使用面积 【例5.40】删除教室表中“使用面积”字段。 ALTER TABLE 教室 DROP COLUMN 使用面积,5.3.4 视图的定义,视图是一个虚拟的表，在存储介质中找不到相应的存储文件。 视图类似SELECT 语句的查询窗口，视图保存了这个查询窗口，保存了查询的数据是满足什么样的条件来自于哪几个表，真正的数据还是分别保存在各自的表中。 视图可引用一个或多个表，或者引用其他视图，视图是可以更新的，它可引用远程表。 视图最大的特点是，依赖于表，不独立存在，虽不保存真正的数据，但是可以像一般的表一样进行建立查询、表单、报表等。,1.定义视图命令 【格式】CREATE VIEW AS 其中SELECT语句可以是任意的SELECT查询语句，它说明和限定了视图中的数据；视图的字段名与SELECT语句中指定的字段名相同。 （1）从单个表派生出来的视图 【例5.41】建立商品的商品号、商品名以及产地的视图。 CREATE VIEW 商品产地信息 AS SELECT 商品号,商品名,产地 FROM 商品 其中“商品产地信息”是视图的名称。,（2）从多个表派生出来的视图 【例5.42】建立学生的学号、姓名、所选的课程的课程名以及该门课的成绩的视图。 CREATE VIEW 成绩单 AS SELECT 学生.学号,姓名,课程名,成绩 FROM 学生,选课,课程 WHERE 学生.学号=选课.学号 AND 课程.课程号=选课.课程号 此时，如果要查询王芳所选的课程以及成绩，则使用如下语句： SELECT 学号,姓名,课程名,成绩 FROM 成绩单 WHERE 姓名=”王芳”,（3）视图中的虚字段 用一个查询来建立一个视图的SELECT字句可以包含算术表达式或函数，这些表达式或函数与视图的其