07第七章 SQL语言的应用

1、SQL语言的应用第七章16.1 SQL语言概述6.2 数据定义6.3 数据查询6.4 数据操纵 2 SQL被作为关系数据库的标准语言。SQL语句可以用来执行各种各样的操作。目前流行的关系数据库管理系统，如Oracle、Sybase、SQL Server、Visual FoxPro等都采用了SQL语言标准。 SQL语言具有如下特点：（1）SQL是一种一体化的语言,它集数据定义、数据查询、数据操纵和数据控制功能于一体，可以独立完成数据库的全部操作。（2）SQL语言是一种高度非过程化的语言。它没有必要一步步地告诉计算机“如何”去做，而只需要描述清楚用户要“做什么” 。6.1 SQL语言概述3（3）S

2、QL语言非常简洁。虽然SQL语言功能很强，但它只有为数不多的命令：CREATE、DROP、ALTER、SELECT、INSERT、UPDATE、DELETE。另外SQL的语法也非常简单，它很接近英语自然语言，因此容易学习和掌握。 （4）SQL语言可以直接以命令方式交互使用，也可以嵌入到程序设计语言中以程序方式使用4 Visual FoxPro在SQL方面支持数据定义、数据查询和数据操纵功能，由于Visual FoxPro自身在安全控制方面的缺陷，所以它没有提供数据控制功能。 本章讲述Visual FoxPro 中SQL的语法、功能与应用。56.2.1 建立表结构命令格式是：CREATE TAB

3、LE ( (,)NULL|NOT NULLCHECK ERROR DEFAULT PRIMARY KEY | UNIQUE TAG ，(,)6.2 数据定义6参数说明：表名1：要建立的表的名称。字段名1、字段名2、：所要建立的新表的字段名。宽度及小数位数：字段宽度及小数位数。NULL、NOT NULL：该字段是否允许“空值”，其默认值为NULL，即允许“空”值。CHECK 条件表达式用来检测字段的值是否有效，这是实行数据库的一种完整性检查。7ERROR 出错显示信息输入数据时，系统会自动检测所输入的字段值不满足条件表达式时，系统自动显示这里所提示的出错信息。DEFAULT 表达式为一个字段指定

4、的默认值。PRIMARY KEY指定该字段为关键字段，非数据库表不能使用该参数。UNIQUE指定该字段为一个侯选关键字段。8 从以上命令格式可以看出，用CREATE TABLE命令建立表可以完成用表设计器完成的所有功能。除了建立表的基本功能外，它还包括满足实体完整性的主关键字（主索引）PRIMARY KEY、定义域完整性的CHECK约束及出错提示信息ERROR、定义默认值的DEFAULT等。9例1 利用SQL命令建立3个表：学生表、选课表和课程表。 建立学生表 CREATE DATABASE 学生管理 create table 学生(学号 c(11); primary key,; 姓名 c(8

5、),; 入学成绩 n(5,1); check(入学成绩0); error成绩应该大于零)10 建立课程表。 CREATE TABLE 课程(课程号 C(5) ; PRIMARY KEY,课程名 C(20),学分 N(1) 建立选课表。 CREATE TABLE 选课(学号 C(5),; 课程号 C(5),; 成绩 I CHECK(成绩=0 and 成绩=100); ERROR成绩值的范围0-100！ ; DEFAULT 60)116.2.2 删除表删除表的SQL命令是： DROP TABLE 直接从磁盘上删除所指定的表文件。 要删除数据库中的表时，最好应使数据库是当前打开的数据库，在数据库中进

6、行操作。126.2.3 修改表结构修改表结构的命令是ALTER TABLE，该命令有2种格式。格式1 （向已存在的表结构中增加字段）ALTER TABLE ADD COLUMN (,)NULL | NOT NULLCHECK ERRORDEFAULT PRIMARY KEY|UNIQUE13例2 为课程表增加一个整数类型的学时字段。 alter table 课程 add 学时 I ; check 学时1614格式2 （修改已有表中字段的规则）ALTER TABLE ALTER COLUMN NULL|NOT NULLSET DEFAULT SET CHECK ERROR DROP DEFAUL

7、TDROP CHECK该格式命令主要用于定义、修改和删除有效性规则以及默认值定义。例3 删除学时字段的有效性规则。 ALTER TABLE 课程 ; ALTER 学时 DROP CHECK15格式3（删除表中字段，更改字段名等）ALTER TABLE DROP COLUMN SET CHECK ERROR 出错显示信息DROP CHECKADD PRIMARY KEY TAG DROP PRIMARY KEY .RENAME COLUMN TO 该格式命令主要用于删除字段, 修改字段名;定义、修改和删除表一级的有效性规则（主键、外键等）例3 删除“学时”字段。 ALTER TABLE 课程 ;

8、 DROP COLUMN 学时 例4 改“单位”为“开课系别”ALTER TABLE 课程 ；Renam column 单位 to 开课系别16Visual FoxPro的SQL SELECT命令的语法格式是： SELECT AS , AS FROM JOIN ON WHERE AND GROUP BY HAVING ORDER BY 排序选项1，ASC|DESC INTO |TO FILE6.3 数据查询176.3.1 基本查询所谓基本查询是指无条件查询，其格式是： SELECT DISTINCT , FROM 其中DISTINCT表示输出无重复结果的记录。 选项可以是字段名、表达式或函数。

9、 表名代表要查询的表。18例4 列出学生名单。 SELECT * FROM STUD 命令中的*表示输出所有字段，数据来源是学生表，所有内容以浏览方式显示。例5 列出所有选课的学生，去掉重复的学号。 SELECT DISTINCT 学号 AS 选课学生; from SC SELECT 命令中的选项，不仅可以是字段名，还可以是表达式，也可以是一些函数。19例6 将所有的学生入学成绩四舍五入，只显示学号、姓名和入学成绩。 SELECT 学号, 姓名, ； ROUND(入学成绩,0) AS 入学成绩 ； FROM STUD例7 求出所有学生的入学成绩平均分。 SELECT AVG(入学成绩) AS

10、平均分； FROM STUD 由以上两例可见，直接使用Visual FoxPro提供的各种SQL函数在输出时进行计算，便可得到相应的输出结果。206.3.2 带条件查询WHERE是条件语句关键字，是可选项，其格式是： WHERE 其中条件表达式可以是单表的条件表达式，也可以是多表之间的条件表达式。例8 求出男同学的入学成绩平均分。 SELECT AVG(入学成绩) ; AS 平均分 FROM stud; WHERE 性别=男21条件表达式是指查询的结果集合应满足的条件，如果某行条件为真就包括该行记录。下面是可用于条件表达式中几个特殊运算符的意义和使用方法。 BETWEEN AND IN 或者

11、IN () LIKE 22例9 列出系别代号为“01”和“03”的学生名单。 SELECT 学号,姓名,系别代号; FROM stud; WHERE 系别代号 IN (“01,“03)以上命令的功能等同于： SELECT 学号,姓名,系别代号; FROM stud ; WHERE 系别代号=“01 OR 系别代号=“0323例10 列出入学成绩在560分到650分之间的学生名单。 SELECT 学号,姓名,入学成绩 ; FROM stud ; WHERE 入学成绩 BETWEEN 560 AND 650在以上命令的功能等同于： SELECT 学号,姓名,入学成绩; FROM stud; WHE

12、RE 入学成绩=560 ； AND 入学成绩=65024例11 列出所有的姓刘的学生名单。 SELECT 学号,姓名 ; FROM stud; WHERE 姓名 LIKE 刘%以上命令的功能等同于： SELECT 学号,姓名; FROM stud ; WHERE 姓名=刘256.3.3 嵌套查询 有时候一个SELECT命令无法完成查询任务，需要一个子SELECT的结果作为条件语句的条件，即需要在一个SELECT命令的WHERE子句中出现另一个SELECT命令，这种查询称为嵌套查询。261. 返回单值的子查询例12 列出选修“计算机应用 ”的学生的学号。 SELECT 学号 FROM SC ；

13、WHERE 课程号=; (SELECT 课程号 FROM KC ； WHERE 课程名=计算机应用) 上述SQL语句首先在KC表中找出“计算机应用”的课程号（比如“KC0003”），然后再在SC表中找出课程号等于“KC0003”的记录，列出这些记录的学号。272. 返回一组值的子查询若某个子查询返回值不止一个，则必须指明在WHERE子句中应怎样使用这些返回值。通常使用条件IN。 例13 列出选修“计算机应用”或“法律概论 ”的所有学生的学号。select 学号 from sc;where 课程号 in ;(select 课程号 from kc; where 课程名=计算机应用 or ;课程名=

14、法律概论)286.3.4 多表查询 在一个表中进行查询，一般说来是比较简单的，而在多表之间查询就比较复杂，必须处理表和表间的联接关系。使用SELECT命令进行多表查询是很方便的。 等值联接 等值联接是按对应字段的共同值将一个表中的记录与另一个表中的记录相联接。29例14 输出所有学生的成绩单，要求给出学号、姓名、课程号和成绩。 SELECT stud.学号, 姓名, 课程号, 成绩; FROM STUD,SC ; WHERE STUD.学号=SC.学号30例15 列出女生的选课情况，要求列出学号、姓名、课程名和学分。 select a.学号,姓名,课程名,学分; From stud a,sc

15、b,kc c; where a.学号=b.学号; and b.课程号=c.课程号; and 性别=女316.3.5 联接查询 Visual FoxPro提供的SELECT命令，在FROM子句中提供一种称之为联接的子句。联接分为内部联接和外部联接。外部联接又分为左外联接、右外联接和全外联接。 1. 内部联接（Inner Join） 实际上，上面例子全部都是内部联接。所谓内部联接是指包括符合条件的每个表格中的记录。也就是说是所有满足联接条件的记录都包含在查询结果中。32例16 输出所有学生的成绩单，要求给出学号、姓名、课程号和成绩。（连接格式1） SELECT a.学号, 姓名, 课程号, 成绩;

16、 FROM STUD a,SC b; WHERE a.学号=b.学号可以用内部联接： （连接格式2） select a.学号,姓名,课程号,成绩; from stud a inner join sc b; on a.学号=b.学号336.3.6 查询结果处理1. 排序输出（ORDER） SELECT的查询结果是按查询过程中的自然顺序给出的，因此查询结果通常无序，如果希望查询结果有序输出，需要ORDER BY子句配合。34例17 按性别顺序列出学号、姓名、性别、课程 名及成绩，性别相同的按成绩由高到低排序。 SELECT STUD.学号, 姓名,性别,课程名, 成绩 ; FROM STUD JO

17、IN SC JOIN KC ; ON SC.课程号= KC.课程号 ; ON STUD.学号=SC.学号 ; ORDER BY 性别 ASC, 成绩 DESC352. 重定向输出（INTO或TO ） INTO是可选项，表示查询结果可以重定输出方向，其格式如下 INTO 其中： “”为有如下3种形式： 1. ARRAY ：将查询结果存到指定数组中。 2. CURSOR ：将输出结果存到一个临时表，这个表的操作与其他表一样，不同的是，一旦被关闭就被删除 3. DBF |TABLE ：将结果存到一个表。 TO 1. TO FILE ：将结果输出到指定文本文件 2. TO PRINTER ：将结果送打

18、印机输出36例18 输出所有学生的成绩单，要求给出学号、姓名、课程号和成绩，并将查询结果存入testtable表中。 SELECT a.学号, 姓名,课程号,成绩; from stud a inner join sc b ; ON a.学号=b.学号; INTO TABLE testtable373. 分组统计（GROUP）与筛选（HAVING）查询结果可以分组，其格式是： GROUP BY 其中可以是字段名，SQL函数表达式，也可以是列序号（最左边为1）。筛选条件格式是： HAVING HAVING子句与WHERE功能一样（都是筛选），只不过是与GROUP BY子句连用，用来指定每一分组内应

19、满足的条件（凡不满足的分组将被筛选出去）。38例19 分别统计男女人数。 SELECT 性别,COUNT(性别) ; FROM STUD; GROUP BY 性别例20 分别统计男女中团员的学生人数。 SELECT 性别,COUNT(性别); FROM STUD; WHERE 是否团员; GROUP BY 性别如果把命令写成如下形式，统计的结果就是错误的 。SELECT 性别,COUNT(性别) FROM STUD ； GROUP BY 性别 HAVING 是否团员 396.4.1 插入记录Visual FoxPro支持两种SQL插入命令，其格式是：格式1： INSERT INTO (字段名1，字段名2 ) VALUES(表达式1，表达式2 )该命令在指定的表尾添加一条新记录，其值为VALUES后面表达式的值。 只需要插入表中某些字段的数据，需要列出插入数据的字段名，当然相应表达式的数据位置应与之对应。6.4 数据操纵40例21 向STUD表中添加记录。 INSERT INTO STUD(学号,姓名,入学成绩); VALUES(010503,李成功,620.0) 当需要插入表中所有字段的数据时，表名后面的字段名可以缺省，但插入数据的格