第四章关系数据库标准语言SQL - 图文

第4章 关系数据库标准语言SQL

4.1 SQL概述

SQL是Structured Query Language(结构化查询语言)的缩写。查询是SQL的重要组成部分，但不是全部，SQL还包含数据定义、数据操纵和数据控制功能。SQL语句已经成为关系数据库的标准语言，所以现在所有的关系数据库管理系统都支持SQL。 SQL语言的特点

SQL语言具有如下主要特点：

（1） SQL是一种一体化的语言，它包含了数据定义、数据查询、数据操纵和数据控制等方面的功能，可以完成数据库活动中的全部工作。

（2） SQL语言是一种高度非工程化的语言，它没有必要告诉计算机“如何”去做，而只需要描述清楚用户要“做什么”，SQL语言就可以将要求给系统，自动完成全部工作。

（3） SQL语言非常简洁。虽然SQL功能很强，但它只有为数不多的几条命令，而且SQL语句的语法也非常简单，它很接近自然语言，因此容易学习和掌握。

（4） SQL语句可以直接以命令方式交互使用，也可以嵌入到程序语言中以程序方式使用。

4.2查询功能

SQL语言的核心是查询。SQL语言的查询命令也称为select命令，它的基本形式由select-from-where查询块组成，多个查询块可以嵌套执行。

命令格式：

Select 字段名表 from 表名 where 条件 group by 分组字段 order by 排序字段。

为了方便大家对照和验证查询结果，这里依然把第三章使用的cj11.dbf表和da11.DBF表作为数据源使用。 注：null是空值，这里的意思是没有确定的值。

4.2.1简单查询

先从几个最简单的查询语句开始，而且这些查询基于单个表，查询由SELECT和FROM短语构成。 【例4-1】从cj11表中检索所有学生的总分 Select 总分 from cj11

【例4-2】查询da11表中所有学生的信息 Select * from da11 注：”*”是通配符，表示所有属性（字段） 【例4-3】查询da11表中学生报考的专业名称 Select distinct 报考专业 from da11

注：distinct 短语的作用是去掉查询结果的重复值。 【例4-4】查询cj11表中总分高于或等于680分学生的学号 Select 学号 from cj11 where 总分>=680

4.2.2简单的链接查询

SQL语句可以实现多表查询但表之间必须有公共字段。

【例4-5】查询总分大于或等于680分的学生的姓名、报考专业、邮政编码、及家庭地址 Select da11.姓名,报考专业,邮政编码.家庭地址 from cj11,da11;

Where cj11.学号=da11.学号 and 总分>=680

注1：当from之后的多个表如果有相同的字段名（属性）时,必须用表名作为前缀指明字段所属的表如”cj11.学号”其中”cj11”为表名”学号”为字段名。

注2：分号是续行符，即如果命令过长可以使用分号续行命令在执行时作用一条指令。

4.2.3嵌套查询

嵌套查询也是基于多表查询的SQL语句嵌套查询由两个SELECT语句块组成即内层查询和外层查询，内层查询的结果作为外层查询的条件使用。

【例4-6】查询总分大于或等于680分的学生的姓名、报考专业、邮政编码、及家庭地址 Select 学号,姓名,邮政编码,家庭地址 where 学号 in; (select 学号 from cj11 where 总分>=680) 注：这里IN相当于集合运算符∈。

【例4-7】查询与学号为10003的学生总分相同的学生的信息 Select * from cj11 where 总分=;

(select 总分 from cj11 where 学号=’10003’)

4.2.4超链接查询

SQL还支持更为广泛的关系连接运算，称为超联接。原来的连接只有满足连接条件，相应的结果才会出现在结果表中；而超联接是首先保证一个表中满足条件的元组都在结果表中，然后将满足联接条件的元组与另一个表中的元组进行联接，不满足联接条件的则将应来自另一个表的属性值置为空值。 其语法格式为：

SELECT ?? FROM 表A LEFT|RIGHT|JULL JOIN 表B on 关联条件 WHERE

* INNSER JOIN 等价于JOIN为内联接两个表取交集当且仅当表A与表B的关联条件完全相当时将查询的信息显示在结果中。

【例4-8】查询表da11.dbf和表cj11.dbf中总分超过670的学生的学号、姓名、班级、总分、邮政编码、家庭地址等信息

Select cj11.学号,cj11.姓名,班级,总分,邮政编码,家庭地址 from cj11 inner join da11 on cj11.学号=da11.学号

* LEFT JOIN 左关联(即除满足条件的记录出现在结果中外，第一个表即左表中不满足联接条件的记录也出现在查询结果中)。

【例4-9】左关联

Select cj11.学号,cj11.姓名,班级,总分,邮政编码,家庭地址 from cj11 left join da11 on cj11.学号=da11.学号

* RIGHT JOIN 右关联(即除满足条件的记录出现在结果中外，第二个表即右表中不满足联接条件的记录也出现在查询结果中)。 【例4-10】右关联

Select da11.学号,da11.姓名,班级,总分,邮政编码,家庭地址 from cj11l right join da11 on cj11.学号=da11.学号

FULL JOIN 全关联(即除满足条件的记录出现在结果中外，两个表中不满足联接条件的记录也出现在查询结果中)。

【例4-11】全关联

Select cj11.学号,cj11.姓名,班级,总分,邮政编码,家庭地址 from cj11 full join da11 on cj11.学号=da11.学号

4.2.5内外层关嵌套查询

内外层相互关嵌套查询是指内层的条件需要外层查询提供值，而外层查询的条件需要内层查询的结果。 【例4-12】

select out.姓名,out.班级,out.总分 from cj11 out where 总分=;

(select max(总分) from cj11 inner1 where out.班级=inner1.班级)

注：JOIN连接格式在连接多个表时的书写方法要特别注意，在这种格式中JOIN的顺序和ON的顺序（相应的连接条件）正好相反。

【例4-13】有一个数据库sdb，其中有数据库表student2、sc和course2。3个表如下所示：

student2（学号，姓名，年龄，性别，院系编号） sc（学号，课程号，成绩，备注）

course2（课程号，课程名，先修课号，学分）

用SQL语句查询\数据库\课程的考试成绩在85分以上（含85分）的学生的全部信息要求输出字段为学号，

姓名，年龄，性别，院系编号，成绩等字段)。

SELECT STUDENT2.* ,成绩 FROM COURSE2 INNER JOIN SC INNER JOIN STUDENT2;

ON STUDENT2.学号=SC.学号 ON SC.课程号=COURSE2.课程号 WHERE 课程名='数据库' AND 成绩>=85 等价于SELECT STUDENT2.* ,成绩 FROM COURSE2, SC , STUDENT2;

WHERE STUDENT2.学号=SC.学号 AND SC.课程号=COURSE2.课程号 AND 课程名='数据库' AND 成绩>=85

4.2.6查询中的特殊运算符

在进行更复杂、涉及更多表的查询之前，先介绍以下可以在SELECT中使用的几个特殊运算符。 【例4-13】查询总分在650分到680分之间的学生的成绩 Select * from cj11 where 总分 between 650 and 680 这里，between ??and意思是在“??和??之间”，这个查询的条件等价于语句： Select * from cj11 where 总分>=650 and 总分<=680 【例4-14】查询da11.dbf表家庭地址为江苏省的学生信息 Select * from da11 where 家庭地址 like “江苏%” 或 select * from da11 where 家庭地址 like '江苏_ _'

注：LIKE是字符串匹配运算符，通配符”%”表示一个或多个字符，而通配符_(下划线)则表示位置相同的任一字符。

4.2.7排序

在SQL SELECT 中使用ORDERY BY 短语对查询结果排序，并可以用ASC指出升序排序（默认），用DESC指出按降序排序，可以按一列或多列排序。

【例4-15】先按总分降序排列，再按英语升序排序查询所有成绩表的信息 Select * from cj11 order by 总分 desc ,英语

4.2.6简单的计算查询

SQL不仅有一般的查询能力，而且还可以对查询结果进行计算，比如查询学生的平均成绩、某个班级的最高（第）分某个班级的人数等用于查询的函数有： 1COUNT（）计数；（2） SUM（）求和 (3)AVG()求平均值；（4）MAX()求最大值；（5）MIN（）求最小值。 【例4-16】查询CJ11.DBF表中所有学生的平均分 Select avg(总分) from cj11 也可以使用语句

Select avg(总分) as 平均分 from cj11 或 select avg(总分) 平均分 from cj11 注：AS 短语表示虚字段但有时可以省略。格式为：表达式 as 虚字段 或表达式 虚字段 【例4-17】查询每个学生的主科成绩

select 语文+数学+英语 as 主科分数 from cj11 select 语文+数学+英语 主科分数 from cj11

【例4-18】查询DA11.DBF表中学生报考专业的数目 select count(distinct 报考专业) as 专业数目 from da11 注：distinct短语的作用是去掉查询结果中的重复值。 【例4-19】查询cj11.dbf表中班级为1的人数

select count(*) as 人数 from cj11 where 班级=’1’ 【例4-20】查询1班和2班的平均成绩

select avg(总分) as 平均分 from cj11 where 班级='1' or 班级='2' 等价于

select avg(总分) as 平均分 from cj11 where 班级 in; (select 班级 from cj11 where 班级='1' or 班级='2') 【例4-21】所有女生的总分平均分

select avg(总分) 女生平均分 from cj11 where 学号 in; (select 学号 from da11 where 性别='女') 【例4-22】查询所有学生数学的总分

Select sum(数学) as 数学总分 from cj11 【例4-23】查询1班学生的最高分

Select max(总分) from cj11 where 班级=’1’ 【例4-24】查询2班学生的最低分

Select min(总分) from cj11 where 班级=’2’

【例4-25】查询总分大于平均分的学生的学号、姓名、总分等信息 select 学号,姓名,总分 from cj11 where 总分>=; (select avg(总分) from cj11)

注：在sql语句中单独使用以上函数（不分组）查询结果中只能有一条查询信息。

4.2.8分组与计算查询

利用GROUP BY 字句进行计算查询使用得非常广泛，可以按一列或多列分组，还可以用HAVING进一步限定分组的条件。

【例4-26】查询每个班的平均成绩

Select 班级,avg(总分) from cj11 group by 班级

【例4-27】查询每个班男女生的平均成绩

Select 班级,性别,avg(总分) from cj11 inner join da11 on cj11.学号=da11.学号 Group by 班级,性别

【例4-28】查询每个班男女生的平均成绩

Select 班级,性别,avg(总分) as 平均分 from cj11 inner join da11 on cj11.学号=da11.学号 Group by 班级,性别

【例4-29】查询每个班男女生的平均成绩大于650分的信息

Select 班级,性别,avg(总分) as 平均分 from cj11 inner join da11 on cj11.学号=da11.学号 Group by 班级,性别 having 平均分>=650

注：having 总是跟在group by 子句之后，不可单独使用。Having子句和where子句不矛盾，先用where限定元组，然后分组，最后再使用having 子句限定分组。

4.2.9利用空值查询

在前面章节介绍过空值的概念，SQL支持空值，而且也可以利用空值进行查询。 【例4-30】查询家庭地址不确定的学生的信息 select * from da11 where 家庭地址 is null 注：查询空值时要使用 is null 而=null是无效的 【例4-31】查询家庭地址已经确定的学生的信息 select * from da11 where 家庭地址 is not null 4.2.10利用别名与自链接进行查询

在连接操作中，经常要使用关系别名作为前缀，有时这样显得麻烦，因此SQL允许在FROM短语中为表名定义别名，格式为： <表名> <别名>

【例4-32】查询表cj11.dbf和表da11.dbf中学生的学号、姓名、性别、班级、总分、邮政编码、家庭地址

select c.学号,c.姓名,性别,班级,总分,邮政编码,家庭地址 from cj11 c,da11 d where; c.学号=d.学号

4.2.11使用量词和谓词进行查询

【例4-33】查询哪些学生在da11表中没有档案信息。 select * from cj11 where not exists;

(select * from da11 where 学号=cj11.学号) 以上查询等价于：

select * from cj11 where 学号 not in; (select 学号 from da11)

【例4-34】查询哪些学生已经在档案表da11.dbf有信息 Select * from cj11 where exists;

(select * from da11 where 学号=cj11.学号) 它等价于：

select * from cj11 where 学号 in; (select 学号 from da11)

【例4-35】

select * from cj11 where 总分>=any ; (select 总分 from cj11 where 班级='3') 它等价于：

select * from cj11 where 总分>=;

(select min(总分) from cj11 where 班级='3')

【例4-36】查询总分大于或等于班级为3中所有学生的信息。 select * from cj11 where 总分>=all ; (select 总分 from cj11 where 班级='3') 它等价于

elect * from cj11 where 总分>=;

(select max(总分) from cj11 where 班级='3')

4.3 SQL语句查询去向

SQL语句的查询去向默认的方式是浏览，除此之外还可以以有多种不同的方式存储查询结果。

4.3.1显示前几项记录

如果显示满足条件的前几项记录可以使用 top n (1<=n<=32767)或top percent n(<0.01=n<=99.99)表示显示结果中的前百分之几的记录。

注：top 短语必须与order by 短语同时使用才有效。 【例4-37】查询cj11.dbf表中总分为前三名学生的信息 Select * top 3 from cj11 order by 总分 desc 查询cj11.dbf表中最高分30%的学生的信息

【例4-38】Select * top 30 percent from cj11 order by 总分 desc

4.3.2将查询结果存储到数组中

可以使用INTO ARRAY <数组名>短语将查询结果存放到数组中，<数组名>可以是任意的数组名。一般将查询的结果作为二维数值来使用，每行一条记录。 【例4-39】将查询结果存放在数组tmp中 Select * from cj11 into array tmp

4.3.3将查询结果存放在临时文件中

使用INTO CURSOR <临时表文件名>可以将查询结果存放到临时数据库文件中，该短语产生的临时文件是一个只读的.DBF文件，可以像一般的.DBF表文件一样使用（但是只读的），一旦关闭该文件则自动删除。 【例4-40】将查询的学生的档案信息存储到临时表文件temp中。 Select * from da11 where 家庭地址=’江苏%’ into cursor temp

4.3.4将查询结果存放到永久表中

使用INTO DBF<表名>或INTO TABLE<表名>可以将查询结果存放到永久表中，（.DBF）。 【例4-41】将查询的学生的档案信息存储到表文件stu.dbf中。 Select * from da11 into dbf stu 它等价于

Select * from da11 into table stu

4.3.5将查询结果存储到文本文件中

使用TO FILE <文件名>可以将查询结果存放到文本文件中（默认扩展名为.TXT）， 【例4-42】将总分超过680分的学生的信息存储到文本文件tmp.txt中。 Select * from cj11 where 总分>=680 to file tmp

4.3.6将查询结果直接输出到打印机

使用TO PRINT[PROMPT]可以直接将查询结果输出到打印机如果使用PROMPT选项，在开始打印之前会打开打印机设置对话框 【例4-43】

4.5操作功能

SQL的操作功能是指对数据库中数据的操作功能，主要包括数据的插入、更新和删除等功能。

4.5.1插入数据

格式一：

INSERT INTO 表名(字段名1，字段名2??) VALUES(达式2??）

【例4-44】将以下数据插入到新复制的da66表中 学号 use da11 copy structure to cj66

Insert into cj66(学号,姓名,性别,出生日期,邮政编码,家庭地址) values(‘100066’,’张小强’,’男’,{^1989-12-11,’210036’,’江苏徐州’}) Select * from cj66

注：当插入数据时要保证三个对应一致即：字段名的个数与表达式的个数一致；字段的顺序与表达式的顺序保持一致；表达式值的数据类型应与字段名的数据类型保持一致。 格式二:

INSERT INTO 表名 FROM ARRAY <数组名> 将数组中的数据追加到指定的表中

【例4-45】将da11.dbf表中家庭地址为江苏省的学生信息的查询结果存储到数组temp中并将数组temp中的数据追加到表da66中。

Select * from da11 where 家庭地址 like ‘江苏%’ into array temp &&将数据存储到数组 Insert into cj66 from array temp &&将数组中的数据追加到cj66表中 Select * from cj66 &&显示cj66表中数据

姓名 性别 出生日期 邮政编码 家庭地址 江苏徐州 100066 张小强 男 1989-12-11 210036 4.5.2数据更新

SLQ数据更新的命令格式为：

UPDATE <表名> SET 字段名1=表达式,字段名2=表达式2??WHERE <条件> 【例4-46】

Copy file cj11.dbf to cj66.dbf

Update cj66 set 总分=总分+20 where 班级=’1’

4.5.3删除数据

SQL从表中删除数据的命令格式如下： 【例4-47】

DELETE FROM <表名> WHERE <条件>

注:VFP 中SQL DELETE 命令同样是逻辑删除记录，如果要物理删除需要继续使用PACK命令。

4.6表的定义

标准SQL的数据定义功能非常广泛，一般包括数据库的定义、表的定义、视图的定义、存储过程的定义、规则定义等，本节主要介绍VFP支持的表的定义和视图的定义。

4.6.1表的定义

格式1：

CREATE TABLE|DBF <表名>（字段名1 数据类型（宽度） , 字段名2 数据类型（宽度）??） 格式2：

CREATE TABLE|DBF <表名>（字段名1 数据类型（宽度） PRIMARY KEY , 字段名2 数据类型（宽度） CHECK <规则> ERROR <信息> DEFAULT <默认值>??）

在创建表的同时设置一个索引名同表达式名相同的主索引以及为某字段设置规则、信息、和默认值。 Modify database stu11 【例4-48】

Create da81(学号 c(8),姓名 c(8),性别 c(2),出生日期d(8),邮政编码 i(4)) 【例4-49】

Create da82(学号 c(8) primary key ,姓名 c(8),性别 c(2),出生日期d(8),邮政编码 i(4)) 【例4-50】

Create da82(学号 c(8) primary key ,姓名 c(8),性别 c(2) check 性别=’男’ or 性别=’女’ error ‘性别只能是男或女’ default ‘男’,出生日期d(8),邮政编码 i(4))

4.6.2表结构的修改

ATLERT TABLE <表名> ADD|DROP|ALTER|RENAME

ATLERT TABLE <表名> ALTER <字段名> SET CHECK|DROP CHECK SET DEFAULT|DROP DEFAULT 【例4-51】

Alter table da81 add column 家庭地址 c(20) 【例4-52】

Alter table da81 drop column 家庭地址 【例4-52】

Alter table da81 alter 性别 l 【例4-52】

Alter table da81 alter 出生日期 t(8)

【例4-52】

Alter table da81 rename 出生日期 to 出生年月

【例4-52】

Alter table da81 alter column 性别 set check 性别=’男’ or 性别=’女’

【例4-52】

Alter table da81 alter column 性别 drop check 【例4-52】

Alter table da81 alter column 性别 set default ‘女’ 【例4-52】

Alter table da81 alter column 性别 drop default 【例4-52】

4.6.3表的删除

DROP TABLE <表名> 【例4-52】

4.6.4视图的定义

在VFP中，视图是一个定制的虚拟表，可以是本地的、远程的或带参数的。视图可以引用一个或多个表，或者引用其他视图。视图可以更新原始表中的数据。视图是根据对表的查询定义的其命令格式如下： CREATE VIEW 视图名as SQL语句

第5章 关系数据库标准语言SQL 5．1．2历届最新考题汇编 一、选择题

（1）SQL是哪几个英文单词的缩写？ A）Standard Query Language B）Structured Query Language C）Select Query Language D）以上都不是 （2）在SQL SELECT 语句中为了将查询结查存储到临时表应该使用短语 A）TO CURSOR B）INTO CURSOR C）INTO DBF 以上4题使用如下数据表： 学生.DBF：学号（C，8），姓名（C，6），性别（C，2），出生日期（D） 选课.DBF：学号（C，8），课程号（C，3），成绩（N，5，1）

（3）查询所有1982年3月20号以后（含）出生、性别为男的学生，正确的是SQL语句是 A）SELECT*FROM学生WHERE出生日期>={^1982-03-20}AND性别=“男” B）SELECT*FROM学生WHERE出生日期<={^1982-03-20}AND性别=“男” C）SELECT*FROM学生 WHERE出生日期>={^1982-03-20}OR性别=“男” D）SELECT*FROM学生 WHERE出生日期<={^1982-03-20}OR性别=“男” （4）计算刘明同学选修的所有课程的平均成绩，成绩的SQL语句是 A）SELECT AVG（成绩）FROM选课WHERE姓名=“刘明”

B）SELECT AVG（成绩）FROM 学生，选课WHERE姓名=“刘明” C）SELECT AVG（成绩）FROM 学生，选课WHERE学生. 姓名=“刘明”

D）SELECT AVG（成绩）FROM 学生，选课WHERE学生.学号=选课.学号AND姓名=“刘明”

（5）假定学号的第3、4位为专业代码。要计算各专业学和选修课程号为“101”课程的平均成绩，正确的SQL语句是

A）SELECT专业 AS SUBS（学号，3，2），平均分AS AVG（成绩）FROM选课 WHERE课程号=“101” GROUP BY专业

B）SELECT SUBS（学号，3，2）AS专业 AVG（成绩）AS平均分 FROM选课 WHERE 课程号=“101”成绩 GROUP BY1

C）SELECT SUBS（学号，3，2） AS专业 ，AVG（成绩） AS 平均分 FROM选课

D）TO DBF

本文来源：https://www.bwwdw.com/article/s47t.html