第四章 SQL语言.doc

关系数据库标准语言SQL
结构化查询语言： STRUCTURED QUERY LANGUAGE(SQL)

4.1SQL介绍-----特点
（1）SQL是一种一体化语言
（2）SQL语言是一种高度非过程化的语言
（3）SQL 语言非常简洁
（4）SQL语言可以直接以命令的方式使用，也可以嵌入到程序设计语言中以程序的方式使用

SQL的功能：数据的定义、数据的操纵、数据的查询、数据的控制。

说明：VF数据库不支持数据的控制功能。

数据的定义功能：对表结构的操作（CREATE 创建 、DROP 删除 、ALTER 修改）
数据的操纵功能：对表记录的操作（INSERT 插入、update 修改、DELETE 删除）
数据的查询：SELECT

4.4定义功能：CREATE、DROP 、ALTER

4.4.1表的定义CREATE
创建一个仓库管理数据库（仓库、职工、订购单、供应商）
定义表的结构：字段名、类型、宽度、有效性规则和索引等等。

语法格式：
CREATE TABLE 表名(字段名1 类型(宽度) PRIMARY KEY,字段名2 类型(宽度),;
字段名3 类型(宽度) CHECK……. ERROR…….. DEFAULT,;
FOREIGN KEY 字段名 TAG 索引名 REFERENCES 表名,;
FOREIGN KEY 字段名 TAG 索引名 REFERENCES 表名)

说明:PRIMARY KEY 建立一个主索引,CHECK…..ERROR….DEFAULT 字段的有效性规则.
FOREIGN KEY 外部关键字建立普通索引, REFERENCES 为表建立联系

EG:仓库表建立
create database 仓库管理
modify database
create TABLE 仓库(仓库号 C(4) PRIMARY KEY,;
城市 C(10),面积 N(6,2)CHECK 面积>=120 AND 面积<=580 ;
error "面积在120-580之间" DEFAULT 120)

职工表建立：
CREATE TABLE 职工(职工号 C(4) PRIMARY KEY,;
仓库号 C(4),工资 Y,;
FOREIGN KEY 仓库号 TAG 仓库号 REFERENCE 仓库)

供应商表建立:
CREATE TABLE 供应商(供应商号 C(4) PRIMARY KEY,;
供应商名 C(8))
订购单表建立:
CREATE TABLE 订购单(订购单号 C(4) PRIMARY KEY,;
职工号 C(4),供应商号 C(4),订购日期 D NULL,
FOREIGN KEY 职工号 TAG 职工号 REFERENCE 职工,;
FOREIGN KEY 供应商号 TAG 供应商号 REFERENCE 供应商)

4.4.2表的删除：DROP 直接从磁盘上删除表所对应的DBF文件。
语法格式：
DROP TABLE 表名

4.4.3表结构的修改:ALTER
（1）添加字段、修改已有字段名，修改字段的类型、宽度。
语法格式：
ALTER TABLE 表名 ADD 新字段名 类型（宽度）
ALTER TABLE 表名 RENAME 字段名 TO 新字段名
ALTER TABLE 表名 ALTER 字段名 新类型（新宽度）

EG:
ALTER TABLE 职工 ADD 邮箱 C(11)
ALTER TABLE 订购单 RENAME 订购日期 TO 订购时间
ALTER TABLE 职工 ALTER 电话号码 N(7)

(2)为已有字段（添加或者修改 ）有效性规则，添加字段的同时添加有效性规则。删除有效性规则,删除字段。

语法格式：
Alter table 表名 ALTER 字段名 SET CHECK ………ERROR.........
Alter table 表名 ALTER 字段名 SET DEFULT……….
说明：对已有字段设置有效性规则时，默认值必须单独设置

ALTER TABLE 表名 ADD 字段名 类型（宽度） CHECK…… ERROR…. DEFAULT…..
ALTER TABLE 表名 ALTER 字段名 DROP CHECK
ALTER TABLE 表名 ALTER 字段名 DROP DEFAULT
ALTER TABLE 表名 DROP 字段名

EG:
ALTER TABLE 职工 alter 工资 SET check 工资>=1000 AND 工资<=5000;
ERROR "工资在1000-5000中间"
ALTER TABLE 职工 alter 工资 SET DEFAULT 1000

ALTER TABLE 供应商 ADD 资产 N(8,2) CHECK 资产>=8000 AND 资产<=18500;
ERROR "资产的范围在8000---18500" DEFAULT 8000

ALTER TABLE 职工 ALTER 工资 DROP check
ALTER TABLE 职工 ALTER 工资 DROP DEFAULT

alter TABLE 供应商 DROP 资产

（3）添加各种类型的索引，删除索引，通过索引建立表之间的联系。
语法格式：
ALTER TABLE 表名 ADD 索引类型（PRIMARY KEY /CANDIDATE） 字段名 TAG 索引名
ALTER TABLE 表名2 ADD 索引类型（FOREIGN KEY） 字段名 TAG 索引名；
REFERENCE 表名1
ALTER TABLE 表名 DROP 索引类型 TAG 索引名

说明：索引类型：主索引（PRIMARY KEY） 候选索引（CANDIDATE/UNIQUE）
普通索引（FOREIGN KEY ）

EG:
ALTER TABLE 仓库 ADD PRIMARY KEY 仓库号 TAG 仓库号
alter TABLE 职工 ADD primary KEY 职工号 TAG ZGH
ALTER TABLE 仓库 ADD unique 所在地 TAG SZD
ALTER TABLE 职工 ADD foreign KEY 仓库号 TAG 仓库号 REFERENCE 仓库

alter TABLE 供应商 DROP primary KEY
ALTER TABLE 仓库 DROP CANDIDATE TAG SZD
ALTER TABLE 职工 DROP FOREIGN KEY TAG 仓库号

4.3数据的操作功能：对表记录的操作（INERT、UPDATE、DELETE）

4.3.1插入数据：INSERT
VF支持两种SQL插入命令：标准格式和特殊格式

（1）标准格式插入：部分插入和完全插入
语法格式：
Insert INTO 表名(字段名1,字段名2,……) VALUE (字段值1,字段值2,……..)
Insert INTO 表名 VALUE(字段值1,字段值2,…….)

EG:
INSERT INTO 职工(职工号,仓库号,工资) VALUE("E9","WH2",1850)
说明:插入记录时,主关键字不允许为空.
INSERT into 职工 VALUE("E2","WH3",1580,"女","武汉")
INSERT INTO 职工(职工号,婚否,出生日期) VALUE("E5",.T.,{^1985.12.02})

(2)特殊格式:向表中插入的数据来自于某个数组(ARRAY)
EG:
CLOSE ALL
DIME M(5) &&&&&&定义一维数组M
M(1)="E8"
M(2)="WH4"
M(3)=8500
M(4)="男"
M(5)="天津"
Use 职工
BROWSE
insert INTO 职工 FROM ARRAY M

4.3.2更新数据(UPDATE):修改表中记录值
语法格式:
UPDATE 表名 SET 字段名=新的值 (功能:修改表中指定字段的所有值)

UPDATE 表名 SET 字段名=字段名+新的值 WHERE 条件表达式

EG:
将男职工的工资增加10%?
UPDATE 职工 SET 工资=工资+工资*0.1 where 性别="男"
将家在上海居住的女职工的工资修改为1950？
update 职工 SET 工资=1950 WHERE 性别="女" AND 城市="上海"

4.3.3删除数据（DELETE）：逻辑删除表中的记录，如果要实现物理删除，使用命令PACK
语法格式：
DELETE FROM 表名 （功能：逻辑删除指定表的所有记录）
DELETE FROM 表名 WHERE 条件表达式

EG:将工资大于1550以上的职工信息删除？
DELETE FROM 职工 WHERE 工资>=1550

4.2查询功能SELECT :SQL语言的核心内容。
查询的分类：简单查询、简单连接查询、嵌套查询、超连接查询。
查询的基本格式：SELECT ……. FROM………. WHERE……… 多个查询可以进行嵌套。

4.2.1简单查询：基于一个表的数据查询。
语法格式： 查找的对象（字段名），字段名来自于那个表，条件来自于 那个表。
SELECT 字段名1,字段名2,….. FROM 表名 where 条件

EG1:找出男职工的职工号和工资?
select 职工号,工资 FROM 职工 WHERE 性别="男"

EG2:检索出女职工的全部信息?
SELECT 职工号,仓库号,工资,性别,城市 FROM 职工 WHERE 性别="女"
SELECT * FROM 职工 WHERE 性别="女"
说明:通配符星号*:表示通配查询的所有字段.

EG3:找出E3职工的所在地是哪儿?
SELECT 城市 AS 所在地 FROM 职工 WHERE 职工号="E3"
SELECT 城市 所在地 FROM 职工 WHERE 职工号="E3"
说明: AS (也可以用空格表示):表示将字段作为新的字段输出

EG4:检索出家在上海和遵义居住的男职工信息？
SELECT * FROM 职工 where 性别="男" AND (城市="上海" OR 城市="遵义")

EG5：检索男职工来自于那些城市？
SELECT DISTINCT(城市) AS 城市 FROM 职工 WHERE 性别="男"
说明：DISTINCT短语：去掉查询结果的重复值

EG6:检索出男职工来自于几个城市？
SELECT COUNT(DISTINCT(城市)) AS 城市数量 from 职工 WHERE 性别="男"
说明：COUNT( ) 函数表示计数统计。

4.2.2简单连接查询：是一种基于多个关系（表）的查询。
语法格式：SELECT 字段1，字段2，…… FROM 表1，表2，…… WHERE 表之间的联系 AND 查询条件（用AND/OR连接多个条件）
说明：表之间的联系是一个表的主关键字和另外一个表外部关键字
表1.主关键字=表2.外部关键字
查询条件：是对查询结果进行限制。

EG1：检索出男职工的仓库的所在地？
SELECT 所在地 FROM 仓库,职工 where 仓库.仓库号=职工.仓库号 AND 性别="男"

EG2：检索出工资高于1220元的男职工的订购单信息？
SELECT 订购单.* FROM 职工,订购单 WHERE 职工.职工号订购单.职工号;
AND 工资>1220 AND 性别="男"

EG:找出仓库的仓库号、所在地以及他们的职工？
SELECT 仓库.仓库号,所在地,职工号 FROM 仓库,职工 WHERE 仓库.仓库号=职工.仓库号

说明：查询的对象不唯一时，必须加表名限制。

课堂练习：
检索出补考学生的人数?
SELECT count(DISTINCT(学号)) AS 补考人数 FROM 成绩 WHERE 分数<60
找出男党员所选修的课程名？
SELECT 课程名 FROM 学生,成绩,课程 WHERE 学生.学号=成绩.学号;
AND 成绩.课程号=课程.课程号 AND 性别="男" AND 政治面貌="党员"
检索出选修了外语课程的学生信息?
SELECT 学生.* FROM 学生,成绩,课程 where 学生.学号=成绩.学号;
AND 成绩.课程号=课程.课程号 AND 课程名="外语"

课后练习：
检索出由北京的供应商提供的订购单信息？
select 订购单.* from 订购单,供应商 where 供应商.供应商号=订购单.供应商号 and 地址="北京"

找出向S4供应商发出订购单的仓库所在的城市？
SELECT DISTINCT(所在地) AS 城市 FROM 仓库,职工,订购单;
WHERE 仓库.仓库号=职工.仓库号 AND 职工.职工号=订购单.职工号;
AND 供应商号="S4"

检索出向供应商S3发过订购单的职工的职工号和仓库号？
SELECT 职工.职工号,仓库号 FROM 职工,订购单 where 职工.职工号=订购单.职工号;
AND 供应商号="S3"

4.2.3嵌套查询：基于多个表的查询。查询结果来自于一个表，然而查询的条件来自于另一个表或者多个表。
嵌套查询特点：分为外层和内层，外层查询结果，内层查询的结果将作为外层查询的条件。

语法格式：
SELECT 表1字段 FROM 表1 where ？ IN(SELECT ？ FROM 表2,表3….. WHERE 表2和表3的联系 AND 查询条件) AND 条件表达式
EG1：
检索出那些仓库所在地有职工?
SELECT 所在地 FROM 仓库 WHERE 仓库号 IN(SELECT 仓库号 FROM 职工)
EG2：
检索出女职工所拥有的订购单？
SELECT 订购单号 FROM 订购单 WHERE 职工号 IN(;
SELECT 职工号 FROM 职工 WHERE 性别="女")
EG3
SELECT 订购单号 FROM 职工,订购单 WHERE 职工.职工号=订购单.职工号;
AND 性别="女"
EG4
检索出那些男职工有订购单？
SELECT 职工号 FROM 职工 WHERE 性别="男";
AND 职工号 IN(SELECT 职工号 FROM 订购单)

说明：查询的条件如果和查询的结果来自于同一个表，查询条件一定书写在外层，反之书写在内层。

课堂练习：
检索出由北京的供应商提供的订购单信息？
SELECT * FROM 订购单 WHERE 供应商号 IN(SELECT 供应商号 FROM 供应商 where 地址="北京")

找出向S4供应商发出订购单的仓库所在的城市？
SELECT 所在地 FROM 仓库 where 仓库号 IN(;
SELECT 仓库号 FROM 职工,订购单 WHERE 职工职工号=订购单.职工号 AND 供应商号="S4")

检索出向供应商S3发过订购单的职工的职工号和仓库号？
SELECT 职工号,仓库号 FROM 职工 where 职工号 in(;
SELECT 职工号 FROM 订购单 WHERE 供应商号="S3")

检索出由工资多于1230元的职工向北京的供应商发出的订购单号？
SELECT 订购单号 FROM 职工,订购单,供应商 where 职工.职工号=订购单.职工号;
AND 订购单.供应商号=供应商.供应商号 AND 工资>1230 AND 地址="北京"

SELECT 订购单号 FROM 订购单 where 职工号 IN(SELECT 职工号 FROM 职工 WHERE 工资>1230);
AND 供应商号 IN(SELECT 供应商号 FROM 供应商 WHERE 地址="北京")

4.2.4几个特殊的运算符：
(1) BETWEEN…… AND……. 表示在两者之间（闭区间））

例题：检索出工资在1220到1240之间的职工信息？
SELECT * from 职工 WHERE 工资>=1220 AND 工资<=1240
SELECT * from 职工 WHERE 工资 BETWEEN 1220 AND 1240

(2)LIKE:像什么一样，是字符串匹配运算符，通配符%表示0个或者多个字符，另外下划线_表示一个字符。
例题：从供应商关系中检索出全部公司的信息？
SELECT * FROM 供应商 where 供应商名 LIKE "%公司"

检索出姓张学生的信息？
SELECT * FROM 学生 WHERE 姓名 LIKE "张%"

（3）不等于: != # NOT <>
例题：检索出没有在北京居住的职工信息？
SELECT * FROM 职工 WHERE 城市!="北京"
SELECT * FROM 职工 WHERE 城市<>"北京"
SELECT * FROM 职工 WHERE 城市#"北京"
SELECT * FROM 职工 WHERE NOT(城市="北京")

4.2.5排序：对查询结果按照某个字段值的升序或者降序来排列查询结果
排序的短语：ORDER BY 字段名1,字段名2,……

说明:升序ASC 降序 DESC, 系统默认是升序

例题1:按职工的工资值升序检索出全部职工的信息?
SELECT * from 职工 ORDER BY 工资 ASC

例题2:检索出全部职工的信息,并且先按照仓库号的升序排列,再按工资的降序输出。
SELECT 职工号,仓库号,工资,性别,城市 FROM 职工;
ORDER BY 仓库号 ASC,工资 DESC

SELECT 职工号,仓库号,工资,性别,城市 FROM 职工;
ORDER BY 2 ASC,3 DESC

4.2.6简单的计算查询
计算检索的函数： COUNT( ) 计数
SUM( ) 求和 AVG( ) 平均值 MAX( ) 最大值 MIN( ) 最小值

EG1：检索出供应商所在地的数目？
SELECT COUNT(DISTINCT(地址)) AS 数目 FROM 供应商

EG2：检索出所有职工的工资总和？
SELECT SUM(工资) AS 总工资 FROM 职工

4.2.8利用空值（NULL）查询:不确定的值
IS NULL 不确定的值 IS NOT NULL 确定了的值

例题：找出没有确定供应商的订购单信息？
SELECT * FROM 订购单 WHERE 供应商号 IS NULL

4.2.7分组与计算查询
分组的短语： GROUP BY 字段名
对分组的条件进行限制：HAVING

例题：求每个仓库的职工的平均工资？
SELECT 仓库号,AVG(工资) FROM 职工 group BY 仓库号+

例题：求至少有两个职工的每个仓库的平均工资？
SELECT 仓库号,AVG(工资) FROM 职工 group BY 仓库号 HAVING COUNT(职工号)>=2

课堂练习：
找出每个城市的男职工人数？
SELECT 城市,COUNT(职工号) FROM 职工 WHERE 性别="男" group BY 城市

检索出面积大于300的每个仓库的职工人数？
SELECT 职工.仓库号,COUNT(职工号) FROM 仓库,职工 WHERE 仓库.仓库号=职工.仓库号;
AND 面积>300 GROUP BY 职工.仓库号

SELECT 仓库号,COUNT(职工号) FROM 职工 WHERE 仓库号 in(;
SELECT 仓库号 FROM 仓库 WHERE 面积>300) GROUP BY 仓库号

检索出最少有两个订购单的职工的信息？
SELECT 职工.* FROM 职工,订购单 WHERE 职工.职工号=订购单.职工号;
GROUP BY 订购单.职工号 HAVING COUNT(*)>=2

检索出每个仓库中工资多于1220元的职工人数？
SELECT 仓库号,COUNT(职工号) from 职工 where 工资>1220 group by 仓库号