1 / 79

第 5 章 结构化查询语言 SQL

第 5 章 结构化查询语言 SQL. 计算机教研室. 5.1 SQL 概述. SQL—Structured Query Language 结构化查询语言，是关系数据库的标准语言。 多数关系型数据库管理系统都采用了 SQL 标准。（如 Oracle 、 Sybase 、 Access 等） VFP 作为一种关系数据库管理系统，除本身的 VFP 命令外，也支持 SQL 命令。 一条 SQL 命令可以代替多条 VFP 命令。. 5.1.1 SQL 语言的主要功能. 语言简洁、形象、易于理解。 综合统一，可以独立完成数据库生命周期中的全部活动。

frayne
Download Presentation

第 5 章 结构化查询语言 SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 第5章 结构化查询语言SQL 计算机教研室

  2. 5.1 SQL 概述 • SQL—Structured Query Language 结构化查询语言，是关系数据库的标准语言。 • 多数关系型数据库管理系统都采用了SQL标准。（如Oracle、Sybase、Access等） • VFP作为一种关系数据库管理系统，除本身的VFP命令外，也支持SQL命令。 • 一条SQL命令可以代替多条VFP命令。

  3. 5.1.1 SQL语言的主要功能

  4. 语言简洁、形象、易于理解。 综合统一，可以独立完成数据库生命周期中的全部活动。 高度非过程化，用户只需利用SQL语言说明自己需要什么数据，而不需关心如何获得数据的过程，系统会自动完成。 一种语法结构，两种使用方式，既能用于命令方式，又可内嵌到程序设计语言中以程序方式使用。 5.1.2 SQL语言的主要特点

  5. 5.2 SQL的定义功能 5.2.1 表结构的定义 • 定义表的名字 • 确定是建立自由表还是数据库表 • 确定每个字段的字段名、类型、宽度 • 确定字段是否允许空值 • 定义索引 • 定义字段的有效性规则，违反规则的提示信息 • 定义字段的默认值 • 定义记录的有效性规则 • 定义所建立表与其它表的永久关系

  6. 5.2.1 表结构的定义 命令格式： CREATE TABLE | DBF <表名1> [FREE] ( <字段名1> 字段类型 [(长度 [，小数位数]) [ null | not null]] [CHECK 字段规则1 [ERROR 信息1]] [DEFAULT 默认值l] [PRIMARY KEY | UNIQUE] [REFERENCES <表名2> [TAG 索引名l]] [, <字段名2>……] [, PRIMARY KEY 表达式2 TAG 索引名2 |UNIQUE 表达式3 TAG 索引名3] [, FOREIGN KEY 表达式4 TAG 索引名4] [REFERENCES 表名3 [TAG 索引名5]] [, CHECK 逻辑表达式2 [ERROR字符表达式2]])

  7. 5.2.1 表结构的定义 【例5-2】创建一个自由表,并为学生证号建立候选索引 （打开学生管理数据库） Create Table 学生表2 ; Free; ( ; 学生证号 C(10) Unique, ; 姓名 C(8) , ; 出生日期 D, ; 党员 L , 家庭住址 M, ; 照片 G ; ) 【例5-1】不打开数据库，创建一个自由表, 包括学生证号、姓名、出生日期、党员、家庭住址、照片 Create dbf 学生表1.dbf ( 学生证号 C(10), 姓名 C(8), 出生日期 D, 党员 L, 家庭住址 M, 照片 G )

  8. 【例5-3】创建数据库表学生表3 , 为学生证号建立主索引,为性别字段建立字段的有效性规则及默认值。 create database 学生管理 Create table 学生表3 ; ( 学生证号 C(10) primary key , ; 姓名C(8) , ; 性别 C(2) check 性别='女' or 性别='男' ; error [性别只能是男或女] default "女",; 出生日期 D, ; 党员 L , ; 家庭住址 M, ; 照片 G )

  9. 【例5-4】创建数据库表:成绩表1(在学生管理数据库中)【例5-4】创建数据库表:成绩表1(在学生管理数据库中) 并建立与学生表3的永久关系 Create table 成绩表1 ; ( 学生证号 C(10) references 学生表3, ; 课程编号 C(5), 期末成绩 N(6,2), 学分 N(1))

  10. 【例5-5】创建课程表1，再创建成绩表2，并建立与学生表3、课程表1的永久关系,同时设置成绩表2的主索引。【例5-5】创建课程表1，再创建成绩表2，并建立与学生表3、课程表1的永久关系,同时设置成绩表2的主索引。 create table 课程表1(课程编号 c(10) unique, 课程名称 C(12)) create table 成绩表2; (学生证号 C(10) references 学生表3 tag 学生证号, ; 课程编号 C(5), 期末成绩 N(6, 2), 学分 N(1), ; fore key 课程编号 tag 课程编号 refe 课程表1 tag 课程编号, ; primary key 学生证号+课程编号 tag xhkh)

  11. 5.2.2 表结构的修改 1．格式1 • 格式： ALTER TABLE 表名1 ADD | ALTER [COLUMN] 字段名1 字段类型[(字段宽度 [，小数位数])] [NULL | NOT NULL]] [CHECK 逻辑表达式1 [ERROR 字符表达式1]] [DEFAULT表达式1]； [PRIMARY KEY | UNIQUE]； [REFERENCES 表名2 [TAG 索引名1]] • 功能：此命令可分成两种形式，各有各的功能。 • 选用ADD关键字是为表增加新字段。 • 选用Alter关键字，对表中的某字段的类型、宽度、有效性规则等进行修改。

  12. 5.2.2 表结构的修改 【例5-6】为学生表3.dbf增加两个新字段，第一个新字段名为“血型”，字段宽度为1，第二个新字段名为“身高”，宽度为4，同时为“身高”字段增加有效性规则、默认值。 ALTER TABLE 学生表3 ; ADD 血型 C(1) ; ADD 身高 N(4,2) &&多个add不用“;”分隔 ; CHECK 身高>1.00 AND 身高<3.00 ; ERROR "身高值不在正确的范围内！"; DEFAULT 1.60

  13. 5.2.2 表结构的修改 【例5-7】修改学生表3中的字段，把“血型”的字段宽度改为2，同时修改“身高”字段的有效果性规则、错误信息、默认值。 ALTER TABLE 学生表3 ; ALTER 血型 C(2) ; ALTER 身高 N(4,2) ; CHECK 身高>1.50 AND 身高<=2.00 ; ERROR “身高值不正确！”; DEFAULT 1.65 说明：多个ALTER之间不用分隔符； 自由表只能修改字段名、类型、宽度

  14. 5.2.2 表结构的修改 2．格式2： • 功能：定义、修改和删除字段的有效性规则和默认值 • 命令格式： ALTER TABLE 表名1 ALTER [COLUMN] 字段名2 [SET DEFAULT 表达式2] [SET CHECK 逻辑表达式2 [ERROR字符表达式2]] [DROP DEFAULT] [DROP CHECK]

  15. 5.2.2 表结构的修改 • 应用举例 【例5-8】修改学生表3 的“身高”字段，把“身高”字段的默认值改为1.50。 ALTER TABLE 学生表3.dbf ALTER 身高; SET DEFAULT 1.50 【例5-9】删除学生表3 的“身高”字段的默认值。 ALTER TABLE 学生表3 ALTER 身高 ; DROP DEFAULT

  16. 5.2.2 表结构的修改 3．格式3 • 命令 ALTER TABLE 表名1 [DROP [COLUMN] 字段名3] [SET CHECK 逻辑表达式3 [ERROR字符表达式3]] [DROP CHECK] [ADD primary key 表达式3 TAG 索引名2 [FOR 逻辑表达式4]] [DROP PRIMARY KEY] [ADD UNIQUE 表达式4 [TAG索引名3 [FOR 逻辑表达式5]]] [DROP UNIQUE TAG 索引名4] [ADD foreign key[表达式5] tag TagName4 [FOR 逻辑表达式6] [REFERENCES 表名2 [TAG索引名5]] [DROP FOREIGN KEY TAG TagName6 ] [RENAME COLUMN字段名4 TO字段名5]

  17. 格式3功能 • 删除字段 • 增加或删除记录的有效果性规则 • 建立或删除主关键字 • 建立或删除候选索引 • 建立或删除永久关系 • 字段名改名 • 应用举例 【例5-10】删除学生表.dbf的“身高”字段。 ALTER TABLE 学生表 DROP 身高 【例5-11】把学生表.dbf的“血型”字段改名为“学生血型”。 ALTER TABLE 学生表 RENAME 血型 TO 学生血型

  18. 5.2.3 表的删除 • 格式 DROP TABLE <数据库表名> | <自由表名> | ?[recycle] • 功能：把一个表（从数据库中移出）从磁盘中删除。 • 举例： 【例5-13】删除自由表学生表1。 Drop Table 学生表1 recycle &&删除并放入回收站 Drop Table ? &&显示移去对话框，选择表删除 Drop Table 学生表1 【例5-14】删除数据库表:成绩表1。 Open Data 学生管理 Drop Table 成绩表1 recycle Drop Table 成绩表1

  19. 5.2.4 视图的定义及删除 • 视图的概念 • 视图是从一个或多个表或视图中导出的表，其结构和数据是建立在对表查询的基础上。 • 视图是一个虚拟表，本身并不存储表数据，但通过它可以浏览和修改表中的数据。 • 视图的定义、浏览、修改、删除等操作与表一样。 • 建立视图必须新建或打开一个数据库，因为视图是数据库所特有的，它依赖于数据库，并存储在数据库中。

  20. 5.2.4 视图的定义及删除 • 建立视图的命令格式： CREATE VIEW <视图名> [(字段名 1[,字段名2]...)] AS SELECT_STATEMENT • 命令说明： • SELECT_STATEMENT可以是任意的SELECT 查询语句，它说明和限定了视图中的数据； • 当没有为视图指定字段名时，视图的字段名将与SELECT_STATEMENT中指定的字段名或表中的字段名同名。

  21. 5.2.4 视图的定义及删除 • 应用举例 【例5-15】建立“学生信息”视图，视图中包括：学生表中的学生证号和姓名，成绩表中的期末成绩和学分。 OPEN DATABASE 学生管理 CREATE VIEW 学生信息 AS； SELECT 学生表.学生证号,姓名,期末成绩,学分 FROM 学生表，成绩表 WHERE 学生表.学生证号=成绩表.学生证号

  22. 5.2.4 视图的定义及删除 2．视图的删除 • 格式： DROP VIEW <视图名> • 功能： 删除一个视图。 • 应用举例 【例5-16】删除例5-15中建立的“学生信息”视图。 DROP VIEW学生信息

  23. 5.3 SQL的数据操纵 5.3.1 数据的插入——INSERT 5.3.2 数据的更新——UPDATE 5.3.3 数据的删除——DELETE

  24. 5.3.1 数据的插入 1．命令格式1(向表中插入一条记录数据) • INSERT INTO <表名> [ (<字段名1> [,<字段名2>……])] VALUES (<表达式1> [，<表达式2>……]) • 功能: 向表中插入一个记录数据，数据值由Values指定。 • 例5-17 向成绩表2中插入一条记录， 学生证号、课程编号、期末成绩、学分值分别为："2024061809"，"30601"，90，6。 INSERT INTO 成绩表2 (学生证号,课程编号,期末成绩,学分); VALUES ("2024061809", "30601", 90, 6) insert into 成绩表 values ("2024061809", "30601", 90, 6) • 例5-18向成绩表2中插入记录，已知学生证号为：2024061811 课程编号为：10320, 学分为3 INSERT INTO 成绩表(课程编号, 学生证号, 学分) ; VALUES ("10320" ,"2024061811", 3)

  25. 5.3.1 数据的插入 1．命令格式2（两种形式） • 格式：INSERT INTO <表名> FROM ARRAY 数组名 INSERT INTO <表名> FROM MEMVAR • 功能：成批插入数据，数据通过数组或内存变量来指定 【例5-19】从数组向学生表3中成批输入数据 dimension c(2,3) c(1,1)="2024061811" c(1,2)="李勇" c(1,3)="男" c(2,1)="2024061812" c(2,2)="刘静" c(2,3)="女" insert into 学生表3 from array c 【例5-20】从内存变量向学生表3中输入数据 学生证号= " 2024061813" 姓名="陈鹏" 性别="男" insert into 学生表3 from memvar

  26. 5.3.2 数据更新 • 格式 Update <表名> Set <字段名1>= <表达式1> [，<字段名2>=<表达式2>……] [Where <条件表达式>] • 功能： 修改数据表中的已有记录数据。 • 说明 （1）如果指定WHERE子句，则更新满足条件的某 些记录的一个或多个字段值； （2）如不使用WHERE子句，则更新全部记录。 （3） Update命令一次只能为一个表更新记录。 （4） Update命令一次可以修改多个字段的值

  27. 应用举例 例5-21 把课程表中 “课程名称” 为 “人工智能” 的 “课程性质”变为“必修”。 命令为：Update 课程表 Set 课程性质="必修" Where 课程名称='人工智能' 例5-22 把学生表中的“学生证号”为“2024061811”的学生 的“姓名”改为“方平”，“性别”改为“男”。 命令为：Update 学生表 Set 姓名=[方平]，性别="男" Where 学生证号='2024061811'

  28. 5.3.3 数据删除 • 格式：DELETE FROM <表名> [WHERE <条件表达式>] • 功能：从指定的表中逻辑删除满足WHERE条件的记录 如果命令中不含WHERE子句，则逻辑删除该 表中的全部记录。 • 注意：使用DELETE只是逻辑删除表中的记录，使用 RECALL命令可以撤消删除，只有执行PACK 命令后，记录才会从表中物理删除。 • 例5-23 逻辑删除课程表中“课程编号”为“10411”的记 录，再使用Recall撤消删除。 DELETE FROM 课程表 WHERE 课程编号="10411" RECALL

  29. 5.4 SQL的数据查询 格式(完整) : SELECT [All | Distinct][Top <数值表达式1>[Percent ]]［Alias］ [<Alias>．[<字段名1> | <函数> [AS 列名称]； [,[<Alias>．]<字段名2> | <函数> [AS 列名称] …… ]； FROM [数据库名称！] <表１或视图１> [[INNER| Left [OUTER] | Right [OUTER] |FULL[OUTER] JOIN[数据库名称!] <表2或视图2> [ On <连接条件表达式>] [[INTO ＜目标＞]｜［TO FILE ＜文件名＞］； [ADDITIVE]] |TO PRINTER|TO SCREEN] ]； [WHERE<筛选条件表达式１> AND|OR <筛选条件表达式2> ]； [Order By<排序选项1> [Asc][Desc] [,<排序选项2> [Asc] [Desc],……]]； [Group By <分组字段名> [Having <过滤条件表达式>]]； [Union [All] Select 命令]

  30. 5.4.1 无条件查询 • 格式 Select [All | Distinct] [*] ｜ <字段名1> | <函数> [AS 列名称]; [,［<Alias>.］<字段名2> | <函数> [AS 列名称] …]; From <表或视图> • 功能：从表或视图中查询记录数据。 • 说明 • Distinct： 指定输出结果时无重复记录。 • ALL ： 代表全部记录, ALL也是默认值。 • * : 表示显示表中全部字段信息。 • AS 列名称：可为某字段指定显示的别名。 • Alias. : 表的别名，当有重名字段时，要用表名区别。

  31. 5.4.1 无条件查询 • 应用举例 例5-24查询学生表中所有记录及所有字段。 SELECT all * FROM 学生表 例5-25查询成绩表中所有学生的学分。 SELECT 学生证号 as 学号, 学分 FROM 成绩表 例5-26从成绩表中查询无重复值的学分。 SELECT distinct 学分 FROM 成绩表 例5-27从成绩表中查询期末成绩最高的学生。 SELECT 学生证号, max(期末成绩) as 最高成绩; FROM 成绩表

  32. 5.4.2 条件查询 在查询过程中，如果要在数据表中找出满足某些条件的记录，则要使用WHERE子句来指定查询条件，这种查询就是条件查询。 • 条件查询的命令格式： SELECT [All | Distinct] <字段名1> | <函数> [AS 列名称]; [,[<Alias>.]<字段名2> | <函数> [AS 列名称] …]; FROM <表或视图>； WHERE <条件表达式>

  33. WHERE <条件表达式>的说明: • 格式：Where <表达式１> 比较运算符 <表达式2> • 比较运算符用于连接和运算，常用的比较运算符表所示：

  34. 应用举例 例5-28查询学生表中所有“男”同学的记录。 SELECT * FROM 学生表 WHERE 性别=”男” 例5-29查询成绩表中“期末成绩”大于70分并且学分不为4的记录。 SELECT * FROM 成绩表 WHERE 期末成绩>70 AND NOT 学分 = 4 或者： SELECT * FROM 成绩表 WHERE 期末成绩>70 AND 学分!=4

  35. 应用举例 例5-30查询学生表中“身高”在1.65到1.75之间的所有学生。 SELECT * FROM 学生表 WHERE 身高>=1.65 AND 身高<=1.75 此命令等同于： SELECT * FROM 学生表 WHERE 身高 BETWEEN 1.65 AND 1.75

  36. 应用举例 例5-31查询学生表中民族是汉族或苗族的学生记录。 SELECT * FROM 学生表 WHERE 民族="汉族" or 民族= "苗族" 此命令等同于： SELECT * FROM 学生表 WHERE 民族 IN {"汉族", "苗族"}

  37. 应用举例 例5-32查询学生表中所有姓名“王”的学生记录。 SELECT * FROM 学生表 WHERE substr(姓名,1,2)= "王" 此命令等同于： SELECT * FROM 学生表 WHERE 姓名 LIKE "王%"

  38. SQL的查询功能回顾 5.4.1 无条件查询 • select 学生证号,姓名 from 学生表 • select * from 学生表 • select all 民族 from 学生表 • select distinct 民族 from 学生表 • select 学生证号 as 学号, 姓名 from 成绩表 5.4.2 条件查询 • select ……； from ……； where <条件表达式>

  39. 5.4.3 多表查询 前面介绍的数据查询都是从一个表中进行的，这种查询相对简单；多表查询就是要从多个表中查询数据，多表查询要将多个表连接起来，根据连接条件，将连接分为： 1. 等值连接 2. 内部连接 3. 外部连接 (1) 左外连接 (2) 右外连接 (3) 全 连 接

  40. 5.4.3 多表查询 1．等值连接 • 概念：两个表有类型相同、取值范围相同的共有字段， 根据该字段值相等进行记录的连接。 • 等值连接的命令格式： select <表名1.字段名1, 字段名m… ,表名2.字段名1 … > from <表名1> , <表名2> … where <表的连接条件> and <记录筛选条件> • 例5-33输出所有学生的成绩，输出的字段包括： 学生证号、姓名、期末成绩和学分。 SELECT 学生表.学生证号, 姓名, 期末成绩, 成绩表.学分 FROM 学生表，成绩表 WHERE 学生表. 学生证号=成绩表. 学生证号

  41. 等值连接的查询过程 将学生表中每个记录依次与成绩表中每个记录比较一次，若学生证号相等，则连接成一个新记录，得到一个查询结果，再处理学生表下一个记录，直到学生表中所有记录处理完为止。 SELECT 学生表.学生证号, 姓名, 期末成绩, 学分 FROM 学生表，成绩表 WHERE 学生表. 学生证号=成绩表. 学生证号

  42. 5.4.3 多表查询 1．等值连接 例5-33-1 输出所有女学生的成绩，输出的字段包括： 学生证号、姓名、期末成绩和学分。 SELECT 成绩表.学生证号, 姓名, 期末成绩, 学分 FROM 学生表，成绩表 WHERE 学生表. 学生证号=成绩表. 学生证号 and 性别= "女" 说明：记录的筛选条件和表的等值连接条件要同时满足

  43. 5.4.3 多表查询 1．等值连接 例5-33-2 输出所有女学生考试成绩，包括学生表中所有 信息及课程名称、期末成绩、学分 SELECT 学生表.* , 课程名称, 期末成绩, 学分 FROM 学生表, 成绩表 , 课程表 WHERE 学生表. 学生证号=成绩表. 学生证号 and 成绩表.课程编号=课程表.课程编号 and 性别= "女" 说明：记录的筛选条件和表的等值连接条件要同时满足. 三个表的连接，要分别说明连接条件。

  44. 5.4.3 多表查询 2．内部连接（Inner Join） 所谓内部连接是查询出满足条件的每个表中的记录。事实上等值连接也是内部连接，只是命令格式不同。 例5-34查询出所有男同学的学生证号、姓名、课程名称和期末成绩。 等值连接： SELECT 学生表.学生证号 , 课程名称, 期末成绩, 学分 FROM 学生表, 成绩表 , 课程表 WHERE 学生表. 学生证号=成绩表.学生证号 and 成绩表.课程编号=课程表.课程编号 and 性别= “男"

  45. 5.4.3 多表查询 等值连接： SELECT 学生表.学生证号 , 课程名称, 期末成绩, 学分 FROM 学生表, 成绩表 , 课程表 WHERE 学生表. 学生证号=成绩表.学生证号 and 成绩表.课程编号=课程表.课程编号 and 性别= “男” 内部连接： SELECT 学生表.学生证号 , 课程名称, 期末成绩, 学分 FROM 学生表 inner join成绩表 inner join课程表 ON 成绩表. 课程编号=课程表.课程编号 ON 学生表.学生证号=成绩表.学生证号 WHERE 性别= “男”

  46. 5.4.3 多表查询 等值连接： SELECT 学生表.学生证号 , 课程名称, 期末成绩, 学分 FROM 学生表, 成绩表 , 课程表 WHERE 学生表. 学生证号=成绩表.学生证号 and 成绩表.课程编号=课程表.课程编号 and 性别= “男” 内部连接： SELECT 学生表.学生证号 , 课程名称, 期末成绩, 学分 FROM 学生表 inner join成绩表 inner join课程表 ON 成绩表. 课程编号=课程表.课程编号 ON 学生表.学生证号=成绩表.学生证号 WHERE 性别= “男”

  47. 5.4.3 多表查询 3．外部连接（Outer Join） (1) 左外连接（Left Outer Join） 将左表中每条记录与右表中所有记录依次比较，若有满足连接条件的记录，则筛选出来，否则产生一个对应右表段值均为NULL值的记录。 例5-35查询所有学生的“学生证号”、“姓名”、“期末成绩” SELECT 学生表.学生证号 , 姓名, 期末成绩 FROM 学生表 left outer join 成绩表 ON学生表.学生证号=成绩表.学生证号

  48. 5.4.3 多表查询 3．外部连接（Outer Join） (2) 右外连接（Right Outer Join） 与左外连接正好相反，将右表中每条记录与左表中所有记录依次比较，若有满足连接条件的记录，则筛选出来，否则产生一个对应左表段值均为NULL值的记录。 例5-36查询所有学生的“学生证号”、“姓名”、“期末成绩” SELECT 学生表.学生证号 , 姓名, 期末成绩 FROM 学生表 ritht outer join 成绩表 ON学生表.学生证号=成绩表.学生证号

  49. 5.4.3 多表查询 3．外部连接（Outer Join） (3) 全连接（Fullr Join） VFP先按右连接进行比较，然后再按左连接进行比较，查询结果中去掉重复值，原则上结果记录数是左表与右表之和。 例5-36查询所有学生的“学生证号”、“姓名”、“期末成绩” SELECT 学生表.学生证号 , 姓名, 期末成绩 FROM 学生表 full outer join 成绩表 ON学生表.学生证号=成绩表.学生证号

More Related