数据库应用-VFP-60第5章-关系数据库标准语言SQL课件.pptx

1、Visual FoxPro程序设计教程 教师：郑州 E-mail:guan_V i s u a l F o x P r o 程序设计教程教师：郑州 了解SQL语言的作用和主要特点 掌握CREAT命令建立表结构、ALTER命令修改表结构、用DORP命令删除表 用INSERT插入记录、DELETE删除记录、UPDATE命令更新记录 使用SELECT命令进行查询补：换行符;第5章 关系数据库标准语言SQL了解S Q L 语言的作用和主要特点第5 章 关系数据库标准语言S QSQL语言是关系数据库的标准语言。它是结构化的查询语言。包括4个部分 定义语言（create，drop，alter）数据操作语言

2、（insert，update，delete）数据查询语言（select）数据控制语言（grant,revoke,commit,rollback）5.1 SQL语言概述S Q L 语言是关系数据库的标准语言。它是结构化的查询语言。5.2 SQL数据定义 5.2.1 定义表 1.创建表 格式：creat table free(字段名 类型(),primary key|unique)说明：(1)free 自由表标记(2)primary key|unique 定义主关键字|候选关键字5.2 S Q L 数据定义5.2.1 定义表将自由表添加到数据库中（1）用界面添加自由表 项目管理器（2）用命令添加自

3、由表格式：ADD TABLE|?功能：向当前数据库添加一个由表名指定的自由表说明：？显示打开对话框，选定一个自由表 需要打开数据库，才可添加将自由表添加到数据库中（1）用界面添加自由表 项目管理器 例1：创建“图书管理”数据库 创建“图书”表(书号(c,10),姓名(c,10),书名(c,10),作者(c,10),定价(N,6,1)，并将书号字段设置成主关键字。例2 创建 自由表 教工 (教工号 c(10),姓名 c(10),所教专业 c(10),所属系别 c(10),入职时间 D)并将教工表添加到 图书管理 数据库例1：创建“图书管理”数据库2.定义字段有效性规则及默认值 格式：creat

4、table free(字段名 类型()checkerror 错误信息 default,)说明(1)checkerror 错误信息:有效性规则及提示信息(2)default：设置字段默认值(3)error|default 后要根据类型加定界符2.定义字段有效性规则及默认值格式：c r e a t t a b l e 例2 在图书管理数据库中创建 读者 表(借书证号(c,6),姓名(c,6),性别(c,2),民族(c,4),所在系(c,10),出生日期D,入学成绩(N,5,1)要求：1.借书证号为主关键字 2.民族字段默认为汉族 3.入学成绩取值范围602-750例2 在图书管理数据库中创建 读者

5、 表3.建立表间关系 格式：creat table(字段名 类型()refrencestag,forign key tag refrences tag)说明：(1)refrencestag:与父表建立永久联系 (2)forign key tag：创建外部关键字与父表建立关系，refrencestag:定义建立永久联系的父表3.建立表间关系格式：c r e a t t a b l e (字段 例3 在“图书管理”数据库中创建“借阅”表(借书证号(c,6),借阅书号(c,10),借阅日期 D,应还日期 D,是否归还 L,罚款金额(N,5,1)，并将其与“图书”、“读者”二表建立联系例3 在“图书管

6、理”数据库中创建“借阅”表5.2.2修改表结构 格式：alter table add|alter|drop|renamecolumnnull|not nullsetcheckerror 错误信息setdefaultdrop checkdrop default5.2.2 修改表结构功能：修改表的结构说明：1、ADD子句增加新字段2、ALTER子句修改原有字段3、DROP子句删除字段4、RENAME子句将字段重命名为字段2 5、check 用在add后，set check 用在 alter 后 6、default 用在add后，set default 用在 alter 后 7、drop check

7、 删除有效性规则 8、drop default 删除默认值 9、null|not null 此字段是否接受空值功能：修改表的结构 例4 对教工表增加职称字段C(20),设置默认值“讲师”将教工表中姓名字段的类型改为C(15)修改读者表中的入学成绩字段，在402-750之间 删除读者表中关于入学成绩的有效性规则 删除教工表中的入职时间字段 将教工表中的所教专业改为所教课程例4 5.2.3 删除表 格式：drop table|?RECYCLE 功能：删除数据库中的表或自由表 说明：？将打开“移去”对话框，让用户选择要删除的表 若选RECYCLE子句，将删除表放入回收站 例 5 删除“图书管理”数据

8、库中的教工表5.2.3 删除表5.3 操作功能（插入、更新、删除）1、插入格式1：INSERT INTO 字段1,字段2,VALUES(表达式1,表达式2)格式2：INSERT INTO FROM ARRAY 数组名 功能：在表尾添加一个指定字段的记录说明：（1）格式1中字段名1，字段名2是用于指定添加记录的字段名，如省略，说明所有字段，表达式1，表达式2是添加字段1，字段2的具体值，也可以只插入几个字段的值。（2）格式2 ARRAY数组名是从指定的数组中添加记录的值5.3 操作功能（插入、更新、删除）1、插入 例13 在图书管理数据库的图书表中插入一条记录(001,操作系统,严一平,清华大学

9、,35.2)要求：使用格式1，格式2 各操作一遍例1 3 在图书管理数据库的图书表中插入一条记录2、更新格式：UPDATE SET 字段名1=,字段名2=WHERE 功能：更新满足条件的记录的值，若省略WHERE，更新全部记录的值例14 将 图书 表中出版社为 电子工业 的图书的 定价增加10%2、更新格式：U P D A T E S E T 字段名1=表3、删除与恢复（1）删除格式：DELETE FROM WHERE 功能：逻辑删除符合条件的记录，若省略WHERE将删除全部记录（2）恢复格式：RECALL范围 for WHILE 功能：恢复符合条件的记录，若省略for 条件，则只恢复一条记录

10、例 15 删除 图书 表中出版社为 清华大学 的记录恢复 图书 表中出版社为 清华大学 的记录3、删除与恢复（1）删除格式：附加 数据库表的移出与删除（1）数据库表的移出1）用界面移出2）用命令移出格式：REMOVE TABLE|?DELETE RECYCLE功能：从当前数据库中移去由表名指定的表，若选DELETE子句在将表移出的同时从磁盘上删除，若选RECYCLE子句，将表放入回收站附加 数据库表的移出与删除（1）数据库表的移出例6 建一个学生专业数据库，将学生专业、校专业、课程、选修表添加到库中CREATE DATABASE 学生专业ADD TABLE 学生专业ADD TABLE 校专业A

11、DD TABLE 课程ADD TABLE 选修CLOSE DATABASE ALL例6 建一个学生专业数据库，将学生专业、校专业、课程、选修表例7（1）将学生成绩F表分别复制成学生成绩1、学生成绩2表。建一个学生数据库。（2）将学生表、学生成绩表、学生成绩F、学生成绩1、学生成绩2添加到数据库中。（3）将学生成绩1移出库，学生成绩2移出且删除COPY FILE 学生成绩F.*TO 学生成绩1.*COPY FILE 学生成绩F.*TO 学生成绩2.*CREATE DATABASE 学生ADD TABLE 学生ADD TABLE 学生成绩ADD TABLE 学生成绩FADD TABLE 学生成绩1

12、ADD TABLE 学生成绩2REMOVE TABLE 学生成绩1REMOVE TABLE 学生成绩2 DELETECLOSE DATABASE ALL例7（1）将学生成绩F 表分别复制成学生成绩1、学生成绩2 表。5.4 SELECT数据查询5.4.1简单查询1、基本语句格式：SELECT INTO DBF|TABLE FROM WHERE 功能：从表名列表的表中查询符合条件的，按的形式显示。若选INTO DBF|TABLE 子句，查询结果存入新表。slect.from.where5.4 S E L E C T 数据查询5.4.1 简单查询说明：格式：ALL|DISTINCTTOP PERC

13、ENT别名.列名 AS 栏目名,别名.列名 AS 栏目名(1)ALL|DISTINCT子句：ALL输出结果有重复记录，是子句默认值。DISTINCT输出结果无重复记录。(2)TOP PERCENT子句：TOP 是符合条件的内容中取前个记录。PERCENT是取前百分之个记录。top n*|top n percent*(3)别名.列名 AS 栏目名,别名.列名 AS 栏目名子句：列名可以是字段、含字段的表达式。指定输出结果中的字段，此子句也可用*代替此时显示表中所有字段。说明：格式：例5.1 SELECT基本语句应用open database 学生(1)选择学生表的全部记录 select*from

14、 学生(2)从学生表中选择入学成绩不低于500分的记录 select 姓名,入学成绩 from 学生 where 入学成绩=500(3)不重复显示学生表的性别记录 select distinct 性别 from 学生例5.1 S E L E C T 基本语句应用o p e n d a t a b a s e2、SELECT中常用的系统函数在实际应用中经常有统计、求平均值、汇总等操作。SQL提供一些常用的系统函数。位置在 select.from函数说明AVG()求的平均值COUNT(*)统计记录个数MIN()求的最小值MAX()求的最大值SUM()求的和2、S E L E C T 中常用的系统函

15、数在实际应用中经常有统计、例16 从 图书 表中检索出共包含哪些出版社,并以出版商为列名显示 例17 从 图书 中输出 出版社 书名 单价，其中单价即为定价 例18 从 图书 表中查询表中图书的种类 最高单价 最低单价 平均价例1 6 从 图书 表中检索出共包含哪些出版社,并以出版商为列5.4.2 条件查询 select fromwhere 查询条件：比较运算符、逻辑运算符、字符匹配、区间、包含、空值(1)notBETWEEN AND运算符格式：50-60:not between 50 an 60 NOTBETWEEN AND 说明：表示在一个区间范围内(2)LIKE后含有%或_通配符的字符型

16、表达式。%表示0个或多个字符，_表示一个字符。5.4.2 条件查询s e l e c t f r o m w h e r例 19 查询 图书 表中定价在45元以下的图书的书名、出版社、定价信息 查询 图书 表中定价在45元到55元之间的图书的书名、出版社、定价信息(不在此范围)例 20 查询 图书 表中刘姓作者的图书信息 使用比较运算符和字符匹配两种方式例 21 查询 图书 表中清华大学出版社的价格在 50元以下的图书信息例 1 9 查询 图书 表中定价在4 5 元以下的图书的书名、出版例5.8 查询入学成绩480-600分学生情况，与不在500-600之间的学生open database 学

17、生select*from 学生 where 入学成绩 between 490 and 600select*from 学生 where 入学成绩 not between 490 and 600close database all例5.8 查询入学成绩4 8 0-6 0 0 分学生情况，与不在5 0 0例4.9 查询姓王的所有学生情况，查询姓名前两个字为王丽的学生情况open database 学生select*from 学生 where 姓名 like“王%”select*from 学生 where 姓名 like“王丽_”close database all例4.9 查询姓王的所有学生情况，查

18、询姓名前两个字为王丽的学5.4.3 多表查询当要查询的字段项包含在多个表中时，就需要用到多表查询。这些表必须具有1:1或1:n 的关系，where条件要选取需要连接两个表中名称相同或含义相同的字段名。引用方式：表名.字段名 若字段名只出现一次，不用加表名例22 查询“周正正”同学所借图书的书名及作者信息 5.4.3 多表查询当要查询的字段项包含在多个表中时，就需要5.4.4 嵌套查询嵌套查询就是在SELECT语句中套用SELECT语句，被套在内的SELECT子句通常称为子查询select distinct as from where in|not in(select distinct as f

19、rom where)例23 查询所有已借图书的信息查询所有未借图书的信息5.4.4 嵌套查询嵌套查询就是在S E L E C T 语句中套用 IN|NOT IN谓词及比较运算符结合使用例5.4 IN与NOT IN在嵌套查询中的应用open database 学生select*from 学生 where 学号 in(select 学号 from 学生成绩 where 英语=90)select*from 学生 where 学号 not in(select 学号 from 学生成绩 where 英语90)close database all I N|N O T I N 谓词及比较运算符结合5.4.5

20、 排序查询格式：ORDER BY ASC|DESC,ASC|DESC TOP N PERCENT功能：查询结果按关键字排序，ASC升序为默认值，DESC为降序例24 查询 图书 表中的所有信息，结果按 定价 降序排列 （显示前4个记录，显示前30%记录）例25 查询 图书 表中的所有信息，按 出版社 升序，按 定价 降序5.4.5 排序查询格式：5.4.6 SELECT分组查询格式：GROUP BY,HAVING 功能：对查询结果进行分组。HAVING 为指定分组必须满足的条件分组项中不能使用表达式分组项可以是字段名,列标题，序号having：在分组结果中应用条件，去除掉不满足条件的记录。且必

21、须在group by 之后，不能单独使用，可以包含聚合函数，where用在group by 之前，不能使用聚合函数。例 26 查询各出版社中图书种类以及最高单价5.4.6 S E L E C T 分组查询格式：例5.2 分组查询应用open database 学生select avg(入学成绩)from 学生select count(*)from 学生select sum(入学成绩)from 学生 group by 性别 having 性别=女例5.2 分组查询应用o p e n d a t a b a s e 学生5.4.7 查询去向（1）用INTO DBF|TABLE 可将查询结果存放表中

22、（2）用INTO ARRAY 将查询结果存入数组（3）用INTO CURSOR 将查询结果存入临时表（4）用TO FILE ADDITIVE将结果存入文本文件，用ADDITIVE将结果追加到由指定的文本文件尾部。否则将覆盖原有文件（5）TO PRINTERPROMPT：将结果从打印机输出（6）TO SCREEN：将结束输出到屏幕（7）1-3属于数据库系统内，4-6属于数据库系统外5.4.7 查询去向（1）用I N T O D B F|T A B L E 例 27 查询 刘晓 所借图书的书名及应还日期并保存到表LXJSXX中例 28 查询清华大学出版社的图书信息，并保存到tsh.txt中例 2

23、7 查询 刘晓 所借图书的书名及应还日期5.5 连接查询5.5.1内连接查询使用比较运算符对表之间的某些数据进行比较，列出表中与连接条件相匹配的数据行，将两张或多张表显示在一起格式：INNER JOIN ON 例29 使用内连接查询读者和借阅表5.5 连接查询5.5.1 内连接查询5.5.2左连接查询LEFT JOIN为左连接，在满足连接条件的记录出现在结果中基础上，第一个表不满足条件的记录也出现在结果中。LEFT JOIN ON 例30 使用左连接查询读者和借阅表5.5.2 左连接查询L E F T J O I N 为左连接，在满足连接5.5.3右连接查询RIGHT JOIN为右连接，在满足

24、连接条件的记录出现在结果中基础上，第二个表不满足条件的记录也出现在结果中RIGHT JOIN ON 例31 使用右连接查询读者和借阅表5.5.3 右连接查询R I G H T J O I N 为右连接，在5.5.4全连接查询 FULL JOIN为全连接。即满足连接条件的记录出现在结果上，两表不满足连接条件的记录也出现在结果上 FULL JOIN ON 例32 使用全连接查询读者和借阅表5.5.4 全连接查询 5.6 合并查询用此子句可以将多个表的查询结果，合并成一个结果，要求查询结果具有相同字段与个数，且对应字段类型要兼容。例如：select*from 学生 where 性别=女 union all select*from 学生 where 性别=男