六关系数据库标准语言PPT学习教案

1、会计学1 六关系数据库标准语言六关系数据库标准语言 第1节 SQL 概述 一、SQL语言简介 SQL（Structured Query Language）结构化查询语言， 其主要功能是同各种数据库建立联系，进行沟通。目前，绝大 多数流行的关系型数据库管理系统，如Oracle，Sybase， Microsoft SQL Server，Access等都采用了SQL语言标准。 二、SQL语言的特点： （1）是一种一体化的语言，包括数据定义、数据查询、数 据操纵和数据控制功能。 （2）是一种高度非过程化的语言； SQL语言非常简洁 。 （3） SQL既是自含式语言，又是嵌入式语言，可以用同一 种语法结

2、构提供两种使用方式。 （4） SQL语言采用面向集合的操作方式；核心是查询。 第1页/共59页 第1节 SQL 概述 SQL语言提供的功能： （1）数据定义 （2）数据操作 （3） 数据检索（数据查询） （4） 存储控制 （5）数据完整性检验 本章介绍前三种功能 第2页/共59页 一、表的定义 第2节 数据定义 例如：用SQL命令建立销售记录数据库。 1.创建数据库 命令：CREATE DATABASE 销售记录 2.创建商品信息表 命令：CREATE TABLE 商品信息(商品号 C(3) PRIMARY KEY,商品名 C(15),; 单价 N(8,2) NULL,数量 I ,厂名 C(2

3、0),产地 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 商品号 REFERENC

4、ES 商品信息) 说明：上述命令创建了“购买信息”表，用CHECK为“数量”字段设置了有效性规则，用ERROR 为规则设置 提示信息；用“FOREIGN KEY 商品号 TAG 商品号 ”在该表的“商品号”字段上建立了一个普通索引，同时说明该 字段是联接字段，通过引用商品信息表的主索引“商品号”与商品信息表建立了联系。 第3页/共59页 第2节 数据定义 P128 三、上机操作题 1.建立数据库STUDENT.DBC CREATE DATABASE STUDENT 2.建立学生（学号C4，姓名C8，出生日期 D）学号主索引 CREATE TABLE 学生(学号 C(4) PRIMARY KEY

5、 ,姓名 C(8),出生日期 D) 3.建立课程（课程号C6，课程名C16）课程号为主索引 CREATE TABLE 课程(课程号 C(6) PRIMARY KEY ,课程名 C(16) 4.建立成绩（学号C4，课程号C6，成绩I）学号、课程号的候选索引 CREATE TABLE 成绩(学号 C(4) UNIQUE,课程号 C(6) UNIQUE,成绩 I,; Foreign key 学号 tag 学号 references 学生) 第4页/共59页 第2节 数据定义 二、表的删除 在第4章中介绍用DELETE FILE 来删除表及其他类型文件，用REMOVE TABLE 命令来移去数据库表。

6、在SQL中删除表的命令为： DROP TABLE 表名 这种删除是直接删除，所以读者在操作时要慎重。且如果要删除的表属于某个数据 库，应先打开该数据库再进行删除，以避免数据库出现不必要的错误提示。 例如：删除销售记录数据库中的商品信息表，命令序列为： OPEN DATABASE 销售记录 DROP TABLE 商品信息 1增加字段或修改已有字段类型或宽度的命令 格式：ALTER TABLE 表名 ADD|ALTER 字段名 其中ADD用于增加新的字段，ALTER用于修改现有字段。 例如：为购买信息表增加一个折扣字段，折扣范围在（0，1之间，命令为： ALTER TABLE 购买信息 ADD 折

7、扣 N(4,2); CHECK(折扣0 AND 折扣=0.3 AND 折扣0 注意：我们在第3章中学习过的Replace With命令与UNPDATE语句的功能是一样的， 都用于更新表中数据，所以初学者很容易混淆，读者要熟记各自的语法以免出错。 第12页/共59页 第4节 数据查询 一、SELECT语句的语法 SELECT命令的常用格式如下： SELECT-FROM-WHERE-GROUP BY-ORDER BY-INTO SELECT子句：说明要查询的数据，对应于【查询设计器】中的 【字段】选项卡。 FROM 子句 ：说明要查询的数据来自哪个表或哪些表，对应于 【查询设计器】中【添加表或视图

8、】操作。 WHERE 子句 ：说明查询条件，对应于【查询设计器】中的【筛 选】选项卡。 GROUP BY 子句 ：用于对查询结果进行分组，对应于【查询设 计器】中的【分组】选项卡。 第13页/共59页 第4节 数据查询 查询结果输出选项 ：来指定查询结果的输出去向，对应于【 查询设计器】中【查询去向】对话框。 查询结果显示选项 ：ALL|DISTINCT TOP nPERCENT关键 词用来控制查询结果的显示情况，对应于【查询设计器】中的 【杂项】选项卡。 一、SELECT语句的语法（续） 第14页/共59页 第4节 数据查询 二、简单查询 【例6-1】检索公寓表中的所有记录。 命令：sele

9、ct * from 公寓 说明：通配符“*”来设定返回表中的所 有列 【例6-2】从学生表中检索所有系。 命令1：select 系 from 学生 命令2：select DISTINCT 系 AS 系部名称 from 学生 说明：DISTINCT关键词的作用是去掉重复值，AS指定了输 出字段的标题。 【例6-3】检索学费不低于4000元的专业简称及其收费情况。 命令：select 专业简称,学费 from 专业 where 学费=4000 第15页/共59页 第4节 数据查询 二、简单查询 【例6-4】检索出国贸系和信息系贫困学生的学号、姓名和所在班 级。 命令：select 学号,姓名,班级

10、 from 学生; where 家庭情况=“贫困” AND (系=“国贸系 OR 系=信息系 ) 说明：这个SQL语句较长，分为两行来写，“;”为续行符；这 个查询的条件很简单，但初学者很容易出错，往往把查询要求 中的“和”理解成逻辑运算符中的“AND”，最后的查询条件 写为“where 系=”信息系“ AND 系=”国贸系”，从而查不到 符合条件的记录。 第16页/共59页 三、简单的联接查询 【例6-5】检索欠费学生的基本情况和所欠金额。 分析：查询需要输出的信息：学生情况和欠费金额，分别 来源于学生表和交费表，分析得知两表之间存在一对一的联系 ，可通过公共字段“学号”建立。这样的查询一般

11、用联接查询 来实现。 命令：select 学生.* ,欠交金额 from 学生,交费; where 学生.学号=交费.学号 AND 欠交金额0 说明：“学生.*” 代表学生表中所有的字段；当from 之后 有多个表时，含有公共字段的表达式，必须用表名前缀直接指 明公共字段所属的表，如“学生.学号=交费.学号”，而非公共 字段则可省略表前缀，如“欠交金额”。 第4节 数据查询 第17页/共59页 三、简单的联接查询 【例6-6】检索每个学生所在专业与应交的学费，所住公寓与住 宿费的情况。 命令：select 学号,姓名,专业.专业简称,学费,公寓.公寓类别,住 宿费 ; from 学生,专业,公

12、寓; where 学生.专业=专业.专业简称 AND 学生.公寓类别= 公寓.公寓类别 第4节 数据查询 第18页/共59页 所谓嵌套查询就是，查询所输出的信息来自一个表， 而查询的条件却涉及到一个或多个表。 四、嵌套查询 【例6-7】检索至少有一个学生欠费的系。 命令：select DISTINCT 系 from 学生 where 学号 IN; (select 学号 from 交费 where 欠交金额0) 说明：这个检索命令由两个SELECT子句构成，即内层 查询（子查询）和外层查询，子查询查到的结果是欠费学生 的学号，外层查询在利用这个学号在学生表中查找欠费学生 所在的系，因为查询的结果

13、有重复，而我们只对欠费的系感 兴趣，所以，用DISTINCT关键词去掉了重复值。 注意：子查询用一对圆括号括起来，且子查询不能嵌套 。 第2节 数据查询 第19页/共59页 第2节 数据查 询 在查询中，还可以加入运算符，如算术运算符和函数运 算符，对原始表中的数据进行计算。常用的统计函数有：计 数函数COUNT（）、求和函数SUM（）、求平均值函数 AVG（）、求最大值函数MAX（）、求最小值函数MIN（） 。 五、简单的计算查询 【例6-9】输出每个学生的学号，姓名和年龄。 分析：表中并没有年龄字段，需要根据出生日期计算 每个人的年龄。 命令：select 学号,姓名,YEAR(DATE(

14、)-YEAR(出生 日期) AS 年龄 from 学生 【例6-10】算出生源所在地的数目。 命令：select COUNT(DISTINCT 籍贯) from 学生 第20页/共59页 第4节 数据查 询 五、简单的计算查询 COUNT（）函数的使用要点： 如要对表中记录个数进行计数，一般用COUNT（*）， 而对其他字段的统计则用COUNT（DISTINCT 列名）。 【例6-11】计算所有学生的人数。 命令1：select COUNT(*) from 学生 命令2： select COUNT(*) AS 总人数 from 学生 说明：第二条命令修改了查询结果的列名，这样方便于对查 询结果

15、的浏览。 第21页/共59页 第4节 数据查 询 【例6-12】计算学生所欠的总金额数。 命令：select sum(欠交金额) from 交费 SUM（）函数 【例6-13】计算信息系和经贸系学生所欠的金额数。 命令：select sum(欠交金额) from 交费 where 学号 IN; (select 学号 from 学生 where 系=信息系 OR 系= 经贸系) 【例6-14】计算人均欠费金额。 命令：select AVG(欠交金额) from 交费 AVG（）函数 MAX（）函数 第22页/共59页 第4节 数据查 询 【例6-15】查找信息系欠费金额最高的学生学号、姓名及所

16、欠金额。 命令： select 学生.学号,姓名,欠交金额 from 学生,交费; where 学生.学号=交费.学号 AND 系=信息系 AND 欠交 金额=; (select MAX(欠交金额)from 学生,交费where 学生.学号=交 费.学号 AND 系=信息系) 【例6-16】查找学费最低的专业。 命令：select 专业简称 ,学费 from 专业 where 学费=(select MIN(学费) from 专业) MIN（）函数 第23页/共59页 第4节 数据查询 六、分组与计算查询 有时需要对查询结果进行分类统计，这就要用到GROUP BY 子句。 GROUP BY子句可

17、对数据进行分组，若要限定分组的条 件，则需启用HAVING子句。 【例6-17】统计各系欠费学生的人数，并按欠费人数降序排序。 命令：select 系 AS 系部名称, COUNT(*) AS 欠费人数 from 学 生 group by 系; where 学号 IN (select 学号 from 交费 where 欠交金额 0) ; ORDER BY 欠费人数 DESC 第24页/共59页 第4节 数据查询 六、分组与计算查询 【例6-18】列出欠费学生人数超过2人的系部。 命令：select 系 AS 系部名称, COUNT(*) AS 人数 from 学生; group by 系 ha

18、ving (COUNT(*)2) ; where 学号 IN; (select 学号 from 交费 where 欠交金额0) 说明：HAVING子句总是跟在GROUP BY子句之后，不可以单 独使用，也不可以在其后使用子查询 第25页/共59页 第4节 数据查 询 为了方便浏览，常常要把查询结果按一定的标准排序，在 SQL语言中，可以用ORDER BY子句按一列或多列对查询结果 进行升序（ASC）或降序（DESC）排列。 七、排序 【例6-19】按学费从低到高的顺序列出全部专业信息。 命令：select * from 专业 order by 学费 说明：如省略ASC|DESC关键字，查询结果

19、默认按升序（ ASC）进行排列 第26页/共59页 第4节 数据查 询 【例6-20】输出学生的学号，姓名，性别，籍贯，班级信息，查 询结果按籍贯排 序，籍贯相同的再按性别排序。 命令：select 学号,姓名,性别,班级,籍贯 from 学生 order by 5,3 说明：排序关键字可以是一个字段或字段表达式，也可以是一 个数值表达式，这个数值是表或查询结果中列的位置，其中最左边 的列的编号为1。 【例6-21】按系列出欠费学生的学号，姓名，系，班级，欠 交金额，同一个系的按欠交金额降序排列。 命令：select 学生.学号,姓名,系,班级,欠交金额from 学生,交 费where 学生.

20、学号=交费.学号 AND 学生.学号 IN; (select 学号 from 交费 where 欠交金额0); order by 系,欠交金额 DESC 第27页/共59页 第4节 数据查询 八、查询结果的显示和输出 1.显示部分查询结果（TOP nPercent） 使用TOP n PERCENT短语限制返回的记录行数，TOP n说 明返回n行，而TOP n PERCENT时，说明n是一个百分数，指定返 回的行数等于总行数的百分之几。 【例6-22】显示学费最高的前5个专业的信息。 命令1： select * TOP 5 from 专业 order by 学费 DESC,专业简称 说明：如果排

21、序关键字存在重复值，则TOP关键字将失效， 记录并没有按指定的数目显示。为了避免这种情况出现，可以在其 后加入一个没有重复值的主键字段。 第28页/共59页 第4节 数据查询 八、查询结果的显示和输出 【例6-23】列出84年以后出生的学生信息，只显示其中50% 的记录即可。 命令：select * TOP 50 percent from 学生; where 出生日期1983-12-31order by 出生日期 2.将查询结果存放到永久表文件（DBF|TABLE）中 在SELECT语句中使用短语 INTO DBF|TABLE ，可 将查询结果存放到永久表。例如：将【例6-21】的查询结果按降

22、 序排列存放到“各系学生欠费金额一览表”中，命令为： select 学生.学号,姓名,系,班级,欠交金额 from 学生,交费 ; where 学生.学号=交费.学号 AND 学生.学号 IN; (select 学号 from 交费 where 欠交金额0); order by 系,欠交金额 DESC; into table 各系学生欠费金额一览表 第29页/共59页 第4节 数据查 询 3.将查询结果存放到临时表文件（CURSOR）中 使用短语INTO CURSOR ，可以将查询结果 存放到临时表文件中。当查询结束后，该临时表是当前文件， 但仅是只读的dbf文件，关闭文件时该临时表将自动删除

23、。临 时表常在程序设计中作为表格、列表框或组合框的数据源。 例如：将查询到的专业信息存放到临时表文件ZYtemp中 ，命令为： select * from 专业 into cursor ZYtemp 4.将查询结果存放到数组（ARRAY）中 Visual FoxPro程序设计中经常将表中的数据和数组数据进 行交换。我们可以用INTO ARRAY 将查询结果存放到 一个二维数组中，数组的每一行对应一条记录，每一列对应查 询结果中的一列。 第30页/共59页 第4节 数据查 询 5.将查询结果存放到文本文件（FILE）中 可以用 TO FILE 短语将查询结果存放到文本 文件中（同时显示在屏幕上）

24、，默认是.TXT文件。 例如：将公寓表中的信息存放到文本文件gy中，命令如下： select * from 公寓 to file gy 例如：将公寓表中的记录存放到数组GYarray中，命令如下： select * from 公寓 into array GYarray 第31页/共59页 第4节 数据查询 6.将查询结果输出到屏幕（SCREEN） 例如：将公寓表中的信息显示在屏幕上，命令如下： select * from 公寓 to screen 7.将查询结果输出到打印机（PRINTER） 使用短语TO PRINTER PROMPT 可以将查询结果输出 到打印机，如果使用了PROMPT，在开

25、始之前会打开“打印设 置”对话框。 查询去向一览表 去向INTO短语去向TO短语 永久表TABLE|DBF 文本文件TO FILE 临时表INTO CURSOR 屏幕TO SCREEN 数组INTO ARRAY 打印机TO PRINTER 默认：显示在浏览窗口中 如果TO短语和INTO短语同时使用，则忽略TO短语 第32页/共59页 第4节 数据查询 十、集合并运算 有时需要把两个或两个以上的查询结果合并到一个结果中 ，这就要用到UNION操作，也即集合并运算。进行并运算， 要求两个查询结果具有相同的字段个，并且对应字段应具有 相同的数据类型和取值范围。 第33页/共59页 第4节 数据查询

26、十、集合并运算 【例6-26】把score表和new_score表的数据合并存放到 临时表temp中。 命令：select * from score; UNION; select * from new_score; into cursor hebing 第34页/共59页 第4节 数据查询 十一、特殊运算符的使用 SQL支持的运算符如下： 逻辑运算符(用于多条件的逻辑连接)：NOT（非）、AND（与） 、OR（或） 比较运算符(大小比较)：、=、=、=、!、!=19 AND 年龄=20 如果要检索年龄在在19岁以下20岁之上的学生信息，则用命令 ： select * from student where 年龄 NOT BETWEEN 19 AND 20 第36页/共59页 第4节 数据查询 十一、特殊运算符的使用 2列表运算符 列表运算符IN 用于判断表达式是否为列表中的指定项。 【例6-28】在GXSF数据库的学生表中检索出晋东南地 区的学生信息。 命令： select * from 学生 where right(籍贯,4) in(