《数据库层次结构》PPT课件.ppt

第6章 关系数据库标准语言SQL,国家级“十一五”规划教材配套电子教案,第1节 SQL 概述 第2节 数据查询 第3节 数据操纵 第4节 数据定义,本章内容,第1节 SQL 概述,一、SQL语言简介 SQL（Structured Query Language）结构化查询语言，其主要功能是同各种数据库建立联系，进行沟通。目前，绝大多数流行的关系型数据库管理系统，如Oracle，Sybase，Microsoft SQL Server，Access等都采用了SQL语言标准。 二、SQL语言的特点： （1）是一种一体化的语言，包括数据定义、数据查询、数据操纵和数据控制功能。 （2）是一种高度非过程化的语言； SQL语言非常简洁 。 （3） SQL既是自含式语言，又是嵌入式语言，可以用同一种语法结构提供两种使用方式。 （4） SQL语言采用面向集合的操作方式；核心是查询。,第2节 数据查询,一、SELECT语句的语法,SELECT命令的常用格式如下： SELECT-FROM-WHERE-GROUP BY-ORDER BY-INTO,SELECT子句：说明要查询的数据，对应于【查询设计器】中的【字段】选项卡。 FROM 子句 ：说明要查询的数据来自哪个表或哪些表，对应于【查询设计器】中【添加表或视图】操作。 WHERE 子句 ：说明查询条件，对应于【查询设计器】中的【筛选】选项卡。 GROUP BY 子句 ：用于对查询结果进行分组，对应于【查询设计器】中的【分组】选项卡。,第2节 数据查询,查询结果输出选项 ：来指定查询结果的输出去向，对应于【查询设计器】中【查询去向】对话框。 查询结果显示选项 ：ALL|DISTINCT TOP nPERCENT关键词用来控制查询结果的显示情况，对应于【查询设计器】中的【杂项】选项卡。,一、SELECT语句的语法（续）,第2节 数据查询,二、简单查询,【例6-1】检索公寓表中的所有记录。 命令：select * from 公寓 说明：通配符“*”来设定返回表中的所有列,【例6-2】从学生表中检索所有系。 命令1：select 系 from 学生 命令2：select DISTINCT 系 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】检索至少有一个学生欠费的系。 命令：select DISTINCT 系 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】算出生源所在地的数目。 命令：select COUNT(DISTINCT 籍贯) from 学生,第2节 数据查询,五、简单的计算查询,COUNT（）函数的使用要点： 如要对表中记录个数进行计数，一般用COUNT（*），而对其他字段的统计则用COUNT（DISTINCT 列名）。,【例6-11】计算所有学生的人数。 命令1：select COUNT(*) from 学生 命令2： select COUNT(*) AS 总人数 from 学生 说明：第二条命令修改了查询结果的列名，这样方便于对查询结果的浏览。,第2节 数据查询,【例6-12】计算学生所欠的总金额数。 命令：select sum(欠交金额) from 交费,SUM（）函数,【例6-13】计算信息系和经贸系学生所欠的金额数。 命令：select sum(欠交金额) from 交费 where 学号 IN; (select 学号 from 学生 where 系=“信息系“ OR 系=“经贸系“),【例6-14】计算人均欠费金额。 命令：select AVG(欠交金额) from 交费,AVG（）函数,MAX（）函数,第2节 数据查询,【例6-15】查找信息系欠费金额最高的学生学号、姓名及所欠金额。 命令： select 学生.学号,姓名,欠交金额 from 学生,交费; where 学生.学号=交费.学号 AND 系=“信息系“ AND 欠交金额=; (select MAX(欠交金额)from 学生,交费where 学生.学号=交费.学号 AND 系=“信息系“),【例6-16】查找学费最低的专业。 命令：select 专业简称 ,学费 from 专业 where 学费=(select MIN(学费) from 专业),MIN（）函数,第2节 数据查询,六、分组与计算查询,有时需要对查询结果进行分类统计，这就要用到GROUP BY 子句。 GROUP BY子句可对数据进行分组，若要限定分组的条件，则需启用HAVING子句。,【例6-17】统计各系欠费学生的人数，并按欠费人数降序排序。 命令：select 系 AS 系部名称, COUNT(*) AS 欠费人数 from 学生 group by 系; where 学号 IN (select 学号 from 交费 where 欠交金额 0) ; ORDER BY 欠费人数,第2节 数据查询,六、分组与计算查询,【例6-18】列出欠费学生人数超过2人的系部。 命令：select 系 AS 系部名称, COUNT(*) AS 人数 from 学生; group by 系 having (COUNT(*)2) ; where 学号 IN; (select 学号 from 交费 where 欠交金额0),说明：HAVING子句总是跟在GROUP BY子句之后，不可以单独使用，也不可以在其后使用子查询,第2节 数据查询,为了方便浏览，常常要把查询结果按一定的标准排序，在SQL语言中，可以用ORDER BY子句按一列或多列对查询结果进行升序（ASC）或降序（DESC）排列。,七、排序,【例6-19】按学费从低到高的顺序列出全部专业信息。 命令：select * from 专业 order by 学费 说明：如省略ASC|DESC关键字，查询结果默认按升序（ASC）进行排列,第2节 数据查询,【例6-20】输出学生的学号，姓名，性别，籍贯，班级信息，查询结果按籍贯排 序，籍贯相同的再按性别排序。 命令：select 学号,姓名,性别,班级,籍贯 from 学生 order by 5,3 说明：排序关键字可以是一个字段或字段表达式，也可以是一个数值表达式，这个数值是表或查询结果中列的位置，其中最左边的列的编号为1。,【例6-21】按系列出欠费学生的学号，姓名，系，班级，欠交金额，同一个系的按欠交金额降序排列。 命令：select 学生.学号,姓名,系,班级,欠交金额from 学生,交费where 学生.学号=交费.学号 AND 学生.学号 IN; (select 学号 from 交费 where 欠交金额0); order by 系,欠交金额 DESC,第2节 数据查询,八、查询结果的显示和输出,1.显示部分查询结果（TOP nPercent） 使用TOP n PERCENT短语限制返回的记录行数，TOP n说明返回n行，而TOP n PERCENT时，说明n是一个百分数，指定返回的行数等于总行数的百分之几。 【例6-22】显示学费最高的前5个专业的信息。 命令1： select * TOP 5 from 专业 order by 学费 DESC,专业简称 说明：如果排序关键字存在重复值，则TOP关键字将失效，记录并没有按指定的数目显示。为了避免这种情况出现，可以在其后加入一个没有重复值的主键字段。,第2节 数据查询,八、查询结果的显示和输出,【例6-23】列出84年以后出生的学生信息，只显示其中50%的记录即可。 命令：select * TOP 50 percent from 学生; where 出生日期1983-12-31order by 出生日期,2.将查询结果存放到永久表文件（DBF|TABLE）中 在SELECT语句中使用短语 INTO DBF|TABLE ，可将查询结果存放到永久表。例如：将【例6-21】的查询结果按降序排列存放到“各系学生欠费金额一览表”中，命令为： select 学生.学号,姓名,系,班级,欠交金额 from 学生,交费; where 学生.学号=交费.学号 AND 学生.学号 IN; (select 学号 from 交费 where 欠交金额0); order by 系,欠交金额 DESC; into table 各系学生欠费金额一览表,第2节 数据查询,3.将查询结果存放到临时表文件（CURSOR）中 使用短语INTO CURSOR ，可以将查询结果存放到临时表文件中。当查询结束后，该临时表是当前文件，但仅是只读的dbf文件，关闭文件时该临时表将自动删除。临时表常在程序设计中作为表格、列表框或组合框的数据源。 例如：将查询到的专业信息存放到临时表文件ZYtemp中，命令为： select * from 专业 into cursor ZYtemp,4.将查询结果存放到数组（ARRAY）中 Visual FoxPro程序设计中经常将表中的数据和数组数据进行交换。我们可以用INTO ARRAY 将查询结果存放到一个二维数组中，数组的每一行对应一条记录，每一列对应查询结果中的一列。,第2节 数据查询,5.将查询结果存放到文本文件（FILE）中 可以用 TO FILE 短语将查询结果存放到文本文件中（同时显示在屏幕上），默认是.TXT文件。 例如：将公寓表中的信息存放到文本文件gy中，命令如下： select * from 公寓 to file gy,例如：将公寓表中的记录存放到数组GYarray中，命令如下： select * from 公寓 into array GYarray,第2节 数据查询,6.将查询结果输出到屏幕（SCREEN） 例如：将公寓表中的信息显示在屏幕上，命令如下： select * from 公寓 to screen,7.将查询结果输出到打印机（PRINTER） 使用短语TO PRINTER PROMPT 可以将查询结果输出到打印机，如果使用了PROMPT，在开始之前会打开“打印设置”对话框。,查询去向一览表,第2节 数据查询,九、超联接查询,格式：FROM DatabaseName!TableASLocal_Alias INNER|LEFTOUTER|RIGHTOUTER|FULLOUTERJOIN DatabaseName!TableASLocal_Alias ON JoinCondition,从上面的格式可看出，超联接分为内联接和外联接，外联接又分为左联接、右联 接和全联接，且联接的条件在FROM子句中由JOIN ON 短语来指定。在【查询设 计器】中的操作见第5章中“图5.15 设定联接条件”。,第2节 数据查询,九、超联接查询,1.内联接 使用INNER JOIN 短语的联接称为内联接，与以前的自然联接效果等同，即只有满足 联接条件的记录才出现在查询结果中。 命令1：select 学生.学号,姓名,班级,应交金额,已交金额,欠交金额 ; from 学生 INNER JOIN 交费 ON 学生.学号=交费.学号 命令2：select 学生.学号,姓名,班级,应交金额,已交金额,欠交金额 ; from 学生 JOIN 交费 ON 学生.学号=交费.学号 命令3：select 学生.学号,姓名,班级,应交金额,已交金额,欠交金额 ; from 学生,交费 where 学生.学号=交费.学号 以上3种格式的命令是等价的。,第2节 数据查询,2.左联接使用LEFTOUTER JOIN 短语的联接称为左联接，即除满足联接条件的记录出现在查询结果中外，第一个表（JOIN 左侧的表）中不满足条件的记录也出现在查询结果中。 命令：select 学生.学号,姓名,班级,应交金额,已交金额,欠交金额 ; from 学生 LEFT JOIN 交费 ON 学生.学号=交费.学号,3.右联接 使用RIGHT OUTER JOIN 短语的联接称为右联接，即除满足联接条件的记录出现在查询结果中外，第二个表（JOIN 右侧的表）中不满足条件的记录也出现在查询结果中。,第2节 数据查询,命令：select 学生.学号,姓名,班级,交费.学号,应交金额,已交金额,欠交金额 ; from 学生 RIGHT JOIN 交费 ON 学生.学号=交费.学号,4.全联接 使用FULL OUTER JOIN 短语的联接称为全联接，即除满足联接条件的记录出现在查询结果中外，两个表中不满足条件的记录也全部出现在查询结果中。 命令：select 学生.学号,姓名,班级,交费.学号,应交金额,已交金额,欠交金额 ; from 学生 FULL JOIN 交费 ON 学生.学号=交费.学号,说明：出现在查询结果中不满足联接条件的记录将另一表中的字段值设置为空值.NULL.。,第2节 数据查询,十、别名与自联接查询,多表联接查询中，我们经常用表名作前缀来区分公共字段。但是如果表名较长，联接的表比较多，就会很麻烦。为此，SQL提供了在FROM子句中为表定义别名的功能，格式为： AS 例如，要查询每个学生的学号，姓名，所学课程名称，该门课的得分，就要涉及 到3个表，2个联接： 命令1：select student.学号,姓名,课程名称,成绩; from student,score,course; where student.学号=score.学号 AND score.课程号=course.课程号 如果使用别名，查询命令就会简单一些。,第2节 数据查询,十、别名与自联接查询,命令2：select A.学号,姓名,课程名称,成绩from student A,score B,course C; where A.学号=B.学号 AND B.课程号=C.课程号 上述例子中，别名并不是必须的，但是在表的自联接查询中，别名就是必不可少的。SQL不仅可以对多个表进行联接查询，也可以将同一个表与其自身进行联接，这种联接就称为自联接查询，也称递归查询。 【例6-24】根据课程关系列出每门课程以及其先修课程名单。 命令：select A.课程名称,“的先修课程是“,B.课程名称from course A,course B; where B.课程号=A.先修课号; to screen PLAIN,第2节 数据查询,十一、内外层相关嵌套查询,自联接是一种出自同一个表同一值域的两个不同字段联接的递归查询，其实同一个表中的同一字段在特殊情况下也需要进行联接。 另外，在6.2.4讲述的嵌套查询都是外层查询依赖于内层查询的结果，而内层查询与外层查询无关。事实上，有时也需要内、外层相关的查询，这时内层查询的条件需要外层查询提供值，而外层查询的条件需要内层查询的结果。 【例6-25】检索出每个学生所考课程中得分最高的成绩信息。 命令：select out.* from score out where 成绩=; (select MAX(成绩) from score inner1; where out.学号=inner1.学号),第2节 数据查询,十一、内外层相关嵌套查询,说明：在这个查询中，外层查询和内层查询使用同一个表“成绩.dbf”。外层查询给成绩表指定别名out，内层查询给成绩表指定别名inner1。外层查询提供out表中每个记录的学号给内层查询使用；内层查询利用这个学号，确定该学生所考课程中的最高成绩；随后外层查询再根据out表中的成绩与该成绩进行比较，如果相等，则该记录被选择。,第2节 数据查询,十二、集合并运算,有时需要把两个或两个以上的查询结果合并到一个结果中，这就要用到UNION操作，也即集合并运算。进行并运算，要求两个查询结果具有相同的字段个，并且对应字段应具有相同的数据类型和取值范围。,第2节 数据查询,十二、集合并运算,【例6-26】把score表和new_score表的数据合并存放到临时表temp中。 命令：select * from score; UNION; select * from new_score; into cursor hebing,第2节 数据查询,十三、特殊运算符的使用,SQL支持的运算符如下： 逻辑运算符(用于多条件的逻辑连接)：NOT（非）、AND（与）、OR（或） 比较运算符(大小比较)：、=、=、!、! 范围运算符：BETWEENAND 和 NOT BETWEENAND 列表运算符：IN(项1,项)、NOT IN(项1,项2) 模式匹配符：LIKE、NOT LIKE 空值判断符：IS NULL、IS NOT NULL,第2节 数据查询,十三、特殊运算符的使用,1范围运算符 范围运算符BETWEEN AND 用于判断表达式值是否在指定的范围。 【例6-27】在成绩管理数据库的student表中检索出年龄在19岁到20岁之间的学生信息。 命令： select * from student where 年龄 BETWEEN 19 AND 20 等价于： select * from student where 年龄=19 AND 年龄=20 如果要检索年龄在在19岁以下20岁之上的学生信息，则用命令： select * from student where 年龄 NOT BETWEEN 19 AND 20,第2节 数据查询,十三、特殊运算符的使用,2列表运算符 列表运算符IN 用于判断表达式是否为列表中的指定项。 【例6-28】在GXSF数据库的学生表中检索出晋东南地区的学生信息。 命令： select * from 学生 where right(籍贯,4) in(“晋城“,“长治“),第2节 数据查询,3模式匹配符 模式匹配符LIKE常用于模糊查找，可使用以下通配字符：百分号“%”，可匹配任意类型和长度的字符；下划线“_”，可匹配单个任意字符 【例6-29】从GXSF数据库的学生表中检索出姓“李”的学生信息。 命令1：select * from 学生 where 姓名 LIKE “李%“ 命令2：select * from 学生 where 姓名 LIKE “李_“ 命令3：select * from 学生 where 姓名 LIKE “李_“,4空值判断符 空值判断符IS NULL用于判断表达式是否为空值。 【例6-30】从销售记录数据库的商品信息表中检索价格尚未确定的商品信息。 命令：select * from 商品信息 where 单价 IS NULL,第2节 数据查询,5谓词查询 谓词EXISTS用来检查子查询中是否有结果返回。 【例6-31】检索销量至少有一台的商品的所有信息。 命令： select * from 商品信息 where EXISTS; (select * from 购买信息 where 商品号=商品信息.商品号),等价于： select * from 商品信息 where 商品号 IN(select 商品号 from 购买信息),相应的，如果要检索还没有销量的商品信息，命令如下： select * from 商品信息 where NOT EXISTS; (select * from 购买信息 where 商品号=商品信息.商品号),一、插入,SQL的数据操纵主要是指对表中的记录进行插入、更新和删除的操作。,第3节 数据操纵,1.插入完整记录 格式：INSERT INTO 表名 VALUES（value1,value2） 例如：向成绩管理数据库的STUDENT表中插入一条记录，命令为： INSERT INTO STUDENT VALUES(“20020001“,“刘然“,19,“女“,“01“) 2.插入部分数据 格式：INSERT INTO 表名(字段名1,字段名2) VALUES（value1,value2） 例如：向GXSF数据库的学生表中插入一条包含学号、姓名、性别、出生日期、 系、籍贯和备注信息的记录，命令为： INSERT INTO 学生(学号,姓名,性别,出生日期,系,籍贯,备注) ; VALUES(“20035002“,“刘凌“,“男“,1986-10-29,“经贸系“,“云南丽江“,“纳西玉水寨“) 注意：插入项的顺序和数据类型必须要与表中字段的顺序和数据类型相对应，插入整条记录时每一个字段的值都必须输入，不能省略。,二、更新,第3节 数据操纵,SQL语言用UNPDATE语句来修改表中数据。 格式：UPDATE 表名 SET 字段名1=新的字段值 ，字段名2=新的字段值 WHERE 条件 SET指定要更新的字段及其更新后的值，一次可以更新多个字段； WHERE子句指定要更新的记录应满足的条件，如果没有WHERE，则更新全部记录。 例如：算出GXSF数据库的交费表中欠交金额的滞纳金，滞纳金率为0.5%，命令为： UPDATE 交费 SET 滞纳金=欠交金额*0.005 WHERE 欠交金额0 注意：我们在第3章中学习过的Replace With命令与UNPDATE语句的功能是一样的， 都用于更新表中数据，所以初学者很容易混淆，读者要熟记各自的语法以免出错。,三、删除,第3节 数据操纵,SQL语言同样用Delete命令来删除表中的记录，不过语法稍有不同。 格式：DELETE FROM 表名 WHERE 条件 FROM 指定要删除的表名，WHERE指定要删除的记录应满足的条件，如果没有 WHERE子句，则删除全部记录。 例如：删除公寓表中所有记录，命令为： DELETE FROM 公寓 注意：同第3章学习的DELETE命令一样，SQL的 DELETE FROM 命令也只是逻辑删除记录，要彻底删除记录还需继续用PACK命令。,一、表的定义,第4节 数据定义,例如：用SQL命令建立销售记录数据库。 1.创建数据库 命令：CREATE DATABASE 销售记录 2.创建商品信息表 命令：CREATE TABLE 商品信息(商品号 C(3) PRIMARY KEY,商品名 C(15),; 单价 N(8,2) NULL,数量 I ,厂名 C(20),产地 C(8) DEFAULT “中国北京“) 说明：上述命令创建了“商品信息”表，“商品号”字段为字符型，宽度为3，PRIMARY KEY 指定“商品号” 为主关键字；“单价”字段为数值型，宽度为8位，小数位数2位，并且允许其值为空值；“数量”字段为整型I；“产 地”字段用 DEFAULT 设置了默认值。 3.创建购买信息表，并建立和商品信息表的联系 命令：CREATE TABLE 购买信息(会员号 C(2),商品号 C(3)单价 N(8,2) , ; 数量 I CHECK(数量0) ERROR “数量不能为0!“,日期 D,; FOREIGN KEY 商品号 TAG 商品号 REFERENCES 商品信息) 说明：上述命令创建了“购买信息”表，用CHECK为“数量”字段设置了有效性规则，用ERROR 为规则设置 提示信息；用“FOREIGN KEY 商品号 TAG 商品号 ”在该表的“商品号”字段上建立了一个普通索引，同时说明该 字段是联接字段，通过引用商品信息表的主索引“商品号”与商品信息表建立了联系。,第4节 数据定义,二、表的删除,在第3章中介绍用DELETE FILE 来删除表及其他类型文件，用REMOVE TABLE 命令来移去数据库表。在SQL中删除表的命令为： DROP TABLE 表名 这种删除是直接删除，所以读者在操作时要慎重。且如果要删除的表属于某个数据 库，应先打开该数据库再进行删除，以避免数据库出现不必要的错误提示。 例如：删除销售记录数据库中的商品信息表，命令序列为： OPEN DATABASE 销售记录 DROP TABLE 商品信息,1增加字段或修改已有字段类型或宽度的命令 格式：ALTER TABLE 表名 ADD|ALTER 字段名 其中ADD用于增加新的字段，ALTER用于修改现有字段。 例如：为购买信息表增加一个折扣字段，折扣范围在（0，1之间，命令为： ALTER TABLE 购买信息 ADD 折扣 N(4,2); CHECK(折扣0 AND 折扣=1) ERROR “折扣范围应在(0,1之间“ 例如：将商品信息表中厂名字段的宽度由原来的20改为10，命令为： ALTER TA