第四章 SQL语言
关系数据库标准语言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、UPDA TE、DELETE）
4.3.1插入数据：INSERT
VF支持两种SQL插入命令：标准格式和特殊格式
（1）标准格式插入：部分插入和完全插入
语法格式：
Insert INTO 表名(字段名1,字段名2,……) VALUE (字段值1,字段值2,……..)
Insert INTO 表名V ALUE(字段值1,字段值2,…….)
EG:
INSERT INTO 职工(职工号,仓库号,工资) V ALUE("E9","WH2",1850)
说明:插入记录时,主关键字不允许为空.
INSERT into 职工V ALUE("E2","WH3",1580,"女","武汉")
INSERT INTO 职工(职工号,婚否,出生日期) V ALUE("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 职工
insert INTO 职工FROM ARRAY M
4.3.2更新数据(UPDA TE):修改表中记录值
语法格式:
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发过订购单的职工的职工号和仓库号？
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( ) 求和A VG( ) 平均值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 字段名
对分组的条件进行限制：HA VING
例题：求每个仓库的职工的平均工资？
SELECT 仓库号,A VG(工资) FROM 职工group BY 仓库号+
例题：求至少有两个职工的每个仓库的平均工资？
SELECT 仓库号,A VG(工资) FROM 职工group BY 仓库号HA VING 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 订购单.职工号HA VING COUNT(*)>=2
检索出每个仓库中工资多于1220元的职工人数？
SELECT 仓库号,COUNT(职工号) from 职工where 工资>1220 group by 仓库号