关系数据库标准语言-SQL课件

1、第3章 关系数据库标准语言-SQL第3章 关系数据库标准语言-SQL参考书籍曹军生等.SQL Server 2000实用教程.北京：北京理工大学出版社，2003.教育部考试中心.全国计算机等级考试二级教程： Visual FoxPro程序设计（2011年版） M.北京：高等教育出版社，2011.刘丽.Visual FoxPro程序设计（第三版）M.北京：中国铁道出版社，2011.参考书籍曹军生等.SQL Server 2000实用教程.北3.1 SQL概述（1）SQL 发展：SQL(Structured Query Language):结构化查询语言1974年，Boyce 和Chamberli

2、n 最早提出；1975-1979年，IBM公司通过System R实现了商业用途；版本：SQL86(ANSI)-89SQL92SQL99同样 Visual FoxPro也引入了SQL语言3.1 SQL概述（1）SQL 发展：3.1 SQL概述（2）一、SQL语言的特点：SQL是结构化查询语言，其功能不仅仅是查询，他是一个通用的功能极强的关系数据库语言。1、综合统一：SQL语言集数据定义语言（DDL）、数据操纵语言（DML）、数据控制语言(DCL)功能于一体；2、高度非过程化：3.1 SQL概述（2）一、SQL语言的特点：3、面向集合的操作方式：可以同时对多个记录进行查询、插入、更新、删除等操作

3、；4、以同一种语法结构提供两种使用方式：即可独立使用，也可嵌入其它语言程序中；5、功能丰富，语言简练：SQL功能命令动词数据查询SELECT数据定义CREATE ，DROP ， ALTER数据操纵INSERT ，UPDATE ， DELETE3、面向集合的操作方式：可以同时对多个记录进行查询、插入、更3.1 SQL概述（3）二、VFP中SQL的功能：数据查询：数据定义：数据操纵：三、视图的定义：视图是从一个或几个数据库表中导出的“表”，它本身不独立存储在数据库中，是虚表。3.1 SQL概述（3）二、VFP中SQL的功能：3.2 数据定义功能(1) 3.2.1 表的定义一般格式如下：CREATE

4、 TABLE | DBF ; （字段1的完整性约束条件; ，字段2的完整性约束条件; ，表的完整性约束条件（表间关系） )3.2 数据定义功能(1) 3.2.1 表的定义3.2 表数据的定义功能(2)说明：1.Table与DBF是等价的，Table是标准SQL的关键字，DBF是VFP的关键字；2.是基本表的名称，它可包含多个字段；3.字段的完整约束条件包括：1）满足实体完整性的主关键字（主索引）可用PRIMARY KEY | UNIQUE 说明，UNIQUE也可指定关键字内容的唯一性。2）定义字段值（字段内容）的有效性用CHECK约束，出错提示信息用ERROR说明（说明的内容为字符型数据）,定

5、义默认值用 DEFAULT3）用NULL或NOT NULL 说明字段值是否可以为空值。 3.2 表数据的定义功能(2)说明：3.2 表数据的定义功能(3)4此外还有描述表之间联系的 FOREIGN KEY和 REFERENCES（用FOREIGN KEY指定普通索引，用REFERENCES限定索引与表建立关系）等。5字段类型用字母表示（见下页）有些类型需要用户给定字段宽度和小数位。3.2 表数据的定义功能(3)4此外还有描述表之间联系的 3.2 表数据的定义功能(4)字段类型表示字段宽度小数位数说明Cm-字符型（character），宽度位mD-日期型（Date）T-日期时间型（DateTim

6、e）Nmn数字型（Numeric），宽度位m，小数位数位nL-逻辑型（Logical）M-备注型（Memo）G-通用型（General）Y-货币型（Currency）I-整数型（Integer）B-m双精度型（Double），小数位数为 mFmn浮点型（Float），宽度为m，小数位数为n3.2 表数据的定义功能(4)字段类型表示字段宽度小数位数说3.2 表数据的定义功能()【例3.1】用命令建立一个名为“YYGL”的医院科室数据库，再用SQL新建一个名为“BUMEN”的科室信息表(要求有：科室编号，科室名，床位数，并对床位数进行有效性规则检查）。Create Database YYGL CR

7、EATE TABLE BUMEN(科室编号 C(3) PRIMARY KEY,科室名 C(10),床位数 I CHECK(床位数0)ERROR 床位数应该大于0！)3.2 表数据的定义功能()【例3.1】用命令建立一个名为3.2 表数据的定义功能(6)【例3.2】用SQL的命令建立医生信息表（YSXX）于医院管理库。 create table ysxx(编号 c(6) primary key,姓名 c(6),性别 c(2),职称 c(10),年龄 I check(年龄=15 and 年龄0 error 年龄值不合法！,科室编号 c(3),主治大夫编号 c(6),foreign key 科室编号

8、 tag 科室编号 references bumen,foreign key 主治大夫编号 tag 编号 references ysxx)通过3个例题，共建立3个关系：BUMEN 科室编号-YSXX 科室编号BUMEN 科室编号-BRXX 科室编号YSXX 编号BRXX 主治大夫编号【例3.3】用SQL的命令建立病人信息表（BRXX）。3.2 表数据的定义功能(7)3.2.2修改基本表的结构【格式一】ALTER TABLE ADD | ALTERCOLUMN 完整性约束该用法可以添加（ADD）新的字段或修改（ALTER）己有的字段，它的句法基本可以与 CREATE TABLE 的句法相对应。注

9、意：该用法用于增加字段或修改字段的类型、宽度。以及重新定义有效性规则、错误信息、默认值，主关键字和联系等；但是不能修改字段名，不能删除字段等。 3.2 表数据的定义功能(7)3.2.2修改基本表的结构3.2 表数据的定义功能(8)【例3.4】为科室信息表（BUMEN）增加一个“职工数”字段，并且要求该字段值不能为零。【例3.5】 将医生信息表（YSXX）的“姓名”字段的宽度改为8。 3.2 表数据的定义功能(8)【例3.4】为科室信息表（BU3.2 表数据的定义功能(9)【格式二】ALTER TABBLE ALTERCOLUMNNULL | NOT NULLSET DEFAULT SET CH

10、ECK 有效性规则 ERROR 提示信息DROP DEFAULTDROP CHECK注意：该格式主要用于修改和删除有效性规则和默认值定义。 3.2 表数据的定义功能(9)【格式二】ALTER TABB3.2 表数据的定义功能(10)【例3.6】 修改或定义科室信息表（BUMEN）“职工数”字段的有效性规则。 【例3.7】 删除科室信息表（BUMEN）的有效性规则。 3.2 表数据的定义功能(10)【例3.6】 修改或定义科室3.2 表数据的定义功能(11)【格式三】ALTER TABLE DROPCOLUMN字段名 ADD PRIMARY KEY 关键字 TAG 索引名 FOR 条件 DROP

11、 PRIMARY KEY ADD UNIQUE 关键字 TAG索引名FOR 条件 DROP UNIQUE TAG索引名 ADD FOREIGN KEY关键字TAG索引名FOR 条件 REFERENCES 表名TAG 索引名 DROP FOREIGN KEY TAG索引名SAVE RENAME COLUMN 字段名 TO 新字段名 该用法可以删除字段（DROPCOLUMN）、可以修改字段名（RENAME COLUMN）。可以定义、修改和删除表一级的有效性规则等。3.2 表数据的定义功能(11)【格式三】ALTER TAB3.2 表数据的定义功能(12)【例3.8】将科室信息表（BUMEN）的“职

12、工数”字段名改为“职工人数”。 【例3.9】删除科室信息表（BUMEN）中的“职工人数”字段。 【例3.10】 将科室信息表（BUMEN）的“床位数”为候选索引（候选关键字），索引名是TEMP_CWS 。【例3.11】删除科室信息表（BUMEN）的候选索引TEMP_CWS。 alter table bumen drop unique tag Temp_cws3.2 表数据的定义功能(12)【例3.8】将科室信息表（B3.2 表数据的定义功能(13)3.2.3 表的删除Drop Table 注意：删除数据库中表时，数据库要打开。3.2 表数据的定义功能(13)3.2.3 表的删除3.3 数据查询

13、功能(1)数据库查询是SQL的核心操作Select一般格式： SELECT ALL | DISTINCT , FROM表名1 ，表名2 WHERE GROUP BY HAVING ORDER BY ASC | DESC3.3 数据查询功能(1)数据库查询是SQL的核心操作Se3.3 数据查询功能(2)3.3.1 简单查询（单表查询）：一、选择表中的任意列(字段)：1、查询指定列：【例3.12】查询全体医生的姓名与学历。select 姓名,学历 from ysxx 【例3.13】查询全体医生的姓名、学历和编号。 select 姓名,学历,编号 from ysxx 2查询全部列 ：【例3.14】查

14、询全体医生的详细记录。Select * from ysxx3.3 数据查询功能(2)3.3.1 简单查询（单表查询）：3.3 数据查询功能(3) 3、查询经过计算的值 ：SELECT子句的不仅可以是表中的字段，也可以是表达式。 【例3.15】查询全体医生的姓名及其出生年份。 Select 姓名,2012-年龄 from ysxxSelect 姓名,year(date()-年龄 as 出生年份 from ysxx3.3 数据查询功能(3) 3、查询经过计算的值 ：3.3 数据查询功能(4)二、选择表中的若干记录（行）：1、消除取值重复的行：Distinct2、查询满足条件的记录：where查 询

15、 条 件谓词比 较=，=，=， ，!，!确定范围BETWEEN AND，NOT BETWEEN AND确定集合IN，NOT IN字符匹配LIKE，NOT LIKE空 值IS NULL，IS NOT NULL多重条件AND，OR3.3 数据查询功能(4)二、选择表中的若干记录（行）：查 3.3 数据查询功能(5)【例3.17】查询所有年龄在30岁以下的医生姓名及其年龄。【例3.18】查询年龄在30-40岁(包括30岁和40岁)之间的医生的姓名和年龄。 【例3.19】查询科室编号为“101”、“102”，“103”的医生的全部信息。 【例3.20】查询编号为“101001”的医生的详细情况。 SE

16、LECT * FROM YSXX WHERE 编号 LIKE “101001”等价于：SELECT * FROM YSXX WHERE 编号 =“101001”3.3 数据查询功能(5)【例3.17】查询所有年龄在30岁3.3 数据查询功能(6)【例3.21】查询所有姓“雷”的医生的全部信息。 SELECT * FROM YSXX WHERE 姓名 LIKE “雷%”【例3.22】查询姓名中姓“刘”，且姓名只有两个字的医生的姓名。 SELECT 姓名 FROM YSXX WHERE 姓名 LIKE “刘_” 【例3.23】查询所有不姓刘的医生的姓名。 SELECT 姓名 FROM YSXX W

17、HERE NOT 姓名 LIKE “刘” 3.3 数据查询功能(6)【例3.21】查询所有姓“雷”的医3.3 数据查询功能(7)【例3.24】某些医生有来医院工作意向但没有报到上班，所以有个人信息而没有参加工作日期。查询缺少参加工作日期的医生的姓名。 SELECT 姓名 FROM YSXX WHERE 参加工作日期 IS NULL 3.3 数据查询功能(7)【例3.24】某些医生有来医院工作3.3 数据查询功能(8)【例3.25】查询科室编号为“101”的年龄在40岁以下的医生姓名。SELECT 姓名FROM YSXX WHERE 科室编号=“101”AND 年龄403.3 数据查询功能(8)

18、【例3.25】查询科室编号为“103.3 数据查询功能(9)三.对查询结果排序： 用户可以用ORDER BY子句对查询结果按照一个或多个字段的升序(ASC)或降序(DESC)排列，缺省值为升序。 【例3.26】查询所有医生的姓名和年龄，查询结果按年龄的降序排列。select 姓名,年龄 from ysxx order by 年龄 desc注：SELECT命令要显示的行数可以是 TOP n Percent（TOP 必须与Order by 同时使用 ） 3.3 数据查询功能(9)三.对查询结果排序：3.3 数据查询功能(10)四.使用集函数：集函数（使用格式）函数功能COUNT(* )统计记录个数

19、COUNT(DISTINCT | ALL )统计一列中值的个数SUM(DISTINCT | ALL )计算一列值的总和(此列必须是数值型)AVG(DISTINCT | ALL )计算一列值的平均值(此列必须是数值型)MAX(DISTINCT | ALL )求一列值中的最大值MIN(DISTINCT | ALL )求一列值中的最小值3.3 数据查询功能(10)四.使用集函数：集函数（使用格式3.3 数据查询功能(11)【例3.27】查询医生的总人数。Select count(*) from ysxx 【例3.28】查询病人信息表中医生的人数。Select count(distinct 主治大夫编

20、号) from brxx 【例3.29】查询医生中年龄最大的年龄。Select max(年龄) from ysxx 3.3 数据查询功能(11)【例3.27】查询医生的总人数。3.3 数据查询功能(12)五.对查询结果进行分组：GROUP BY子句将查询结果表按某一列或多列的值分组，值相等的为一组。对查询结果分组的目的是为了细化集函数的作用对象。如果未对查询结果分组，集函数将作用于整个查询结果，如上面的例3.27、例3.28、例3.29。分组后集函数将作用于每一个组，即每一组都有一个函数值。3.3 数据查询功能(12)五.对查询结果进行分组：3.3 数据查询功能(13)【例3.30】求各个科室

21、的医生相应人数。 SELECT 科室编号，COUNT(科室编号) FROM YSXX GROUP BY 科室编号 如果分组后还要求按一定的条件对这些组进行筛选，最终只输出满足指定条件的组，则可以使用HAVING短语指定筛选条件。 【例3.31】查询医生人数超过1人的科室编号和科室的医生人数。3.3 数据查询功能(13)【例3.30】求各个科室的医生相3.3.2 联接查询（1）若一个查询同时涉及两个或两个以上的表，则称之为联接查询。 分类：等值、非等值、自身联接查询、超联接查询。 3.3.2 联接查询（1）若一个查询同时涉及两个或两个以上的3.3.2 联接查询（2）1.等值与非等值联接查询：其一

22、般格式为：表名1 比较运算符 表名2 (联接谓词)当联接运算符为=时，称为等值联接。使用其他运算符称为非等值联接。 联接谓词中的字段名称为联接字段。联接条件中的各联接字段类型必须是可比的。但不必是相同的。3.3.2 联接查询（2）1.等值与非等值联接查询：3.3.2 联接查询（3）【例3.32】查询每个医生及其对应科室情况。 医生情况存放在YSXX表中，科室情况存放在BUMEN表中，所以本查询实际上涉及两个表。这两个表之间的联系是通过公共字段科室编号实现的 若在等值联接中把目标字段中重复的字段去掉则为自然联接。 SELECT YSXX.科室编号,姓名,性别,职称,年龄,参加工作日期,学历,毕业

23、学校,职务,科室名,床位数 FROM YSXX,BUMEN WHERE YSXX.科室编号=BUMEN.科室编号3.3.2 联接查询（3）【例3.32】查询每个医生及其对应3.3.2 联接查询（4）2.自身联接查询(命名别名来实现)：联接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行联接，称为表的自身联接。【例3.34】查询医生信息表中是科室主任的医生是哪些医生的科室主任。SELECT ZW.姓名,是,YS.姓名,的主任 FROM YSXX ZW,YSXX YS WHERE ZW.科室编号=YS.科室编号 AND ZW.职务=主任 AND YS.职务主任3.3.2 联接查询（4）2.

24、自身联接查询(命名别名来实现)3.3.2 联接查询（5）3.超联接查询：首先保证一个表中满足条件的记录都在结果表中，然后将满足联接条件的记录与另一个表的记录进行联接，不满足联接条件的则将应来自另一表的属性值置为空值（NULL）。 【格式】SELECT FROM INNER | LEFT | RIGHT | FULL JOIN ON WHERE 3.3.2 联接查询（5）3.超联接查询：3.3.2 联接查询（6）说明：1 INNER JOIN 等价于 JOIN，为普通的联接（等值联接），在 Visual FoxPro 中称为内部联接。2 LEFT JOIN 为左联接。3 RIGHT JOIN 为

25、右联接。4 FULL JOIN 可以称为完全联接，即两个表中的记录不管是否满足联接条件将都在目标表或查询结果中出现，不满足联接条件的记录对应内容部分为 NULL。 5 ON 联接条件 ：指定联接的条件 3.3.2 联接查询（6）说明：3.3.2 联接查询（7）【例3.35】查询医生与病人间的诊治关系，按医生编号的升序排列。SELECT 编号,Ysxx.姓名,Ysxx.性别,参加工作日期,Brxx.床位号,Brxx.姓名 FROM Ysxx inner JOIN Brxx ON Ysxx. 编号=BRxx. 主治大夫编号 ORDER BY Ysxx.编号3.3.2 联接查询（7）【例3.35】查

26、询医生与病人间的诊3.3.3 嵌套查询（1）在SQL语言中，一个SELECTFROMWHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询嵌套查询是基于多个表的查询，这类查询所要求的结果出自一个表，但相关的条件却涉及多个表。 上层的查询块称为外层查询或父查询，WHERE后的下层查询块称为内层查询或子查询。SQL语言允许多层嵌套查询。 3.3.3 嵌套查询（1）在SQL语言中，一个SELECT3.3.3 嵌套查询（2）1.带有IN谓词的子查询：在嵌套查询中，子查询的结果往往是一个集合，所以谓词IN是嵌套查询中最经常使用的谓词。【例

27、3.36】查询与“肖冰”同在一个科室住院治疗的病人。 SELECT * FROM BRXX WHERE 科室编号 IN (SELECT 科室编号 FROM BRXX WHERE 姓名=“肖冰”)3.3.3 嵌套查询（2）1.带有IN谓词的子查询：3.3.3嵌套查询（3）【例3.37】 哪些科室有45岁以上的职工？ SELECT DISTINCT 科室名,有45岁以上职工 From Bumen WHERE 科室编号 IN (SELEC 科室编号 FROM Ysxx WHERE 年龄45)3.3.3嵌套查询（3）【例3.37】 哪些科室有45岁以上3.3.3 嵌套查询（4）2、带有比较运算符的子查

28、询：返回是单值3、带有ANY（SOME）和ALL谓词的子查询：【例3.38】 查询出只要年龄小于或等于“内科”（科室编号为102）中某一名医师年龄的医师编号。这个查询可以使用ANY或SOME量词。 SELECT 编号 FROM Ysxx WHERE 年龄 = ANY； (SELECT 年龄 FROM Ysxx WHERE 科室编号=102) and 科室编号1023.3.3 嵌套查询（4）2、带有比较运算符的子查询：返回是3.3.3 嵌套查询（5）【例3.39】 查询出年龄大于或等于“内科”（科室编号为102）中所有医师年龄的医师编号。这个查询使用ALL量词。SELECT DISTINCT 编

29、号 FROM Ysxx WHERE 年龄= ALL ;(SELECT 年龄 FROM Ysxx WHERE 科室编号= 102) and 科室编号1023.3.3 嵌套查询（5）【例3.39】 查询出年龄大于或等3.3.3 嵌套查询（）或!=ANYALLIN-NOT INMAXMIN=MAXMINMAX=MIN=MAX3.3.3 嵌套查询（）或!=ANYIN-3.3.3 嵌套查询（）、带有EXISTS谓词的子查询：EXISTS（NOT EXISTS）代表存在（不存在）量词。带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“Ture“或逻辑假值“False”, 用来检查在子查询中是否有

30、结果返回，即存在记录或不存在记录。3.3.3 嵌套查询（）、带有EXISTS谓词的子查询：3.3.3嵌套查询（8）【例3.40】 在Brxx表检索那些在ysxx表中无主治大夫编号的病号记录。使用谓词 NOT EXISTS： SELECT * FROM Brxx WHERE NOT EXISTS (SELECT * FROM Ysxx WHERE 编号 = Brxx.主治大夫编号) SELECT * FROM Brxx WHERE 主治大夫编号 NOT IN (SELECT 编号 FROM Ysxx ) 3.3.3嵌套查询（8）【例3.40】 在Brxx表检索那些3.3.3嵌套查询（9）所有带I

31、N谓词、比较运算符、ANY（SOME）和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换 3.3.3嵌套查询（9）所有带IN谓词、比较运算符、ANY（3.3.4 集合查询(1) SELECT语句查询的结果是记录的集合，所以多个SELECT语句的结果可再进行集合操作。集合操作主要包括”并”操作(UNION)。【例3.41】查询“内科”（科室编号为102）的医生以及年龄不大于30岁的医生的信息。SELECT * FROM YSXX WHERE 科室编号=102 UNION SELECT * FROM YSXX WHERE 年龄=303.3.4 集合查询(2)注意:参加UNION操作的各结

32、果表3.3.5 设定输出目标1.输出到自由表：INTO TABLE 2.输出到数组：INTO ARRAY 3.输出到临时表：INTO CURSOR 4.输出到文本文件：TO FILE 3.3.5 设定输出目标1.输出到自由表：INTO TABL3.3.6 其他子句(1) NOCONSOLE:禁止将查询结果发送到 文件、打印机和Visual FoxPro主窗口。(2)PLAIN:防止列标题出现在查询结果的主窗口显示中。(3)NOWAIT:打开浏览窗口，并将查询结果输出到这个窗口后继续程序的执行；程序并不等待关闭浏览窗口，而是立即执行紧接在SELECT语句后面的程序行。 3.3.6 其他子句(1)

33、 NOCONSOLE:禁止将查询结3.4 数据更新功能(1)3.4.1 插入数据:SQL的数据插入语句INSERT通常有两种形式。一种是插入一条记录，另一种通过数组或变量等插入多条记录。 1.插入单条记录:INSERT INTO (字段名1,字段名2，) VALUES (常量1,常量2,)3.4 数据更新功能(1)3.4.1 插入数据:3.4 数据更新功能(2)例3.47向表YSXX中插入记录，记录内容如下：（106002 王明 男 主任医师 55 106 1980.10.1 本科 滨州医学院 教师）INSERT INTO YSXX VALUES(106002,王明,男,主任医师,55,106

34、,1980-10-1,本科,滨州医学院,教师,null)INSERT INTO YSXX(编号,姓名,性别,职称,年龄,科室编号,参加工作日期,学历,毕业学校,职务) VALUES(106002,王明,男,主任医师,55,106,1980-10-1,本科,滨州医学院,教师)3.4 数据更新功能(2)例3.47向表YSXX中插入记3.4 数据更新功能(3)2. 对批量生成的数据操作:INSERT INTO FROM ARRAY |MEMVAR说明：（）FROMARRAY 从指定的数组中插入记录值；（）FROM MEMVAR 从同名的内存变量中插入记录值。3.4 数据更新功能(3)2. 对批量生成的数据操作:3.4 数据更新功能(4)【例3.48】 USE Ysxx SELE * FROM ysxx INTO ARRAY arr1COPY STRU TO Ord1 INSERT INTO Ord1 FROM ARRAY arr1SELEC