二级培训讲义SQL查询查询设计器SQL数据表的操作视图设计器

1、第三部分 关系数据库标准语言SQL一、 SQL概述1.1 SQL的概念：SQL(Structured Query Language)是结构化查询语言的简称，它引领着关系数据库的发展方向，是未来关系数据库发展的标准。1.2 SQL的特点：u 一体化u 高度非过程化u 非常简洁u 直接以命令方式交互使用1.3 SQL的主要命令：u 数据查询：SELECTu 数据定义：CREATE、DROP、ALTERu 数据操纵：INSERT、UPDATE、DELETE二、 使用命令建立SQL查询2.1查询的概念：查询是从指定的表或视图中提取满足条件的记录，然后按照想得到的输出类型定向输出查询结果。2.2 SQL

2、查询语句格式：u SELECT : 要查询的数据（字段）u FROM ：数据来源(要查询的数据来自哪个或哪些表)u WHERE ：查询需要满足的条件u GROUP BY ：对查询的数据进行分组u ORDER BY ：对查询结果进行排序2.3 SQL简单查询：1. 在当前数据库当前表中查询某一数据基本格式：SELECT Select_Item FROM Table 其中：Select_Item ：字段名列表 Table ：数据表名称例题2.1：从职工关系中检索所有工资值。实现语句：SELECT 工资 FROM 职工2. 在指定数据库指定表中查询某一数据基本格式：SELECT Alias.Sele

3、ct_Item FROM DatabaseName!Table例题2.2：从订货管理数据库中的职工数据表中检索所有工资值。实现语句：SELECT 职工.工资 FROM 订货管理数据库!职工3. 在当前数据库当前表中查询所有数据基本格式：SELECT * FROM Table例题2.3：从当前数据库中当前数据表中检索所有数据。实现语句：SELECT * FROM 职工4. 在当前数据库当前表中查询所有数据（不允许内容重复）基本格式：SELECT ALL | DISTINCT Select_Item FROM Table例题2.4：从当前数据库中当前数据表中检索所有不重复的工资数据。实现语句：SE

4、LECT DISTINCT 工资 FROM 职工5. 在当前数据库当前表中进行条件查询基本格式：SELECT Select_Item FROM Table WHERE JoinCondition例题2.5：在当前数据库当前表中，查询工资大于1210元职工的仓库号Select 仓库号 from 职工 where 工资>1210实现语句：SELECT 仓库号 FROM 职工 WHERE 工资>1210例题2.6：查询在仓库“WH1”或“WH2”工作，并且工资少于1250元的职工工号实现语句：SELECT 职工号 FROM 职工 WHERE 工资<1250 AND (仓库号=”WH

5、1” OR 仓库号=”WH2”)完整格式：SELECT ALL|DISTINCT Alias.Select_Item |* FROM DatabaseName!Table WHERE JoinCondition2.4 SQL联接查询：联接查询的含义：联接查询是一种基于多个关系的查询，即将多个数据表中数据构成一个查询集合，并在其中查询出符合要求的记录。例题2.7：找出工资多于1230元的职工号和他们所在的城市。实现语句：SELECT 职工号,城市 FROM 职工,仓库 WHERE 工资>1230 AND (职工.仓库号=仓库.仓库号)特别注意：u 职工.仓库号=仓库.仓库号 是连接条件u

6、如果在FROM之后有两个关系，则这两个关系之间肯定有一种联系，否则无法构成检索表达式例题2.8：找出工作在面积大于400的仓库的职工号以及这些职工工作所在的城市。实现语句：SELECT 职工号,城市 FROM 职工,仓库 WHERE 面积>400 AND (职工.仓库号=仓库.仓库号)2.5 SQL嵌套查询：例题2.9：哪些城市至少有一个仓库的职工工资为1250元？实现语句：SELECT 城市 FROM 仓库 WHERE 仓库号 in (SELECT 仓库号 FROM 职工 WHERE 工资=1250)例题2.10：查询所有职工的工资都多于1210元的仓库信息。实现语句：SELECT *

7、 FROM 仓库 WHERE 仓库号 NOT IN (SELECT 仓库号 FROM 职工 WHERE 工资<=1210)例题2.11：找出和职工E4挣同样工资的所有职工。实现语句：SELECT 职工号 FROM 职工 WHERE 工资=(SELECT 工资 FROM 职工 WHERE 职工号=”E4”)2.6 SQL几个特殊运算符：u BETWEEN AND ：和之间u LIKE：字符串匹配运算符例题2.12：检索出工资在1220元到1240元范围内的职工信息。实现语句：SELECT * FROM 职工 WHERE 工资 BETWEEN 1220 AND 1240例题2.13：从供应商

8、关系中检索出全部公司的信息，不要工厂或其他供应商的信息。实现语句：SELECT * FROM 供应商 WHERE 供应商名 LIKE “%公司”通配符：u % ：表示任意多个字符（含0个）u _ ：表示一个字符2.7 SQL排序：基本格式：ORDER BY Order_Iten ASC|DESC ,Order_Iten ASC|DESC例题2.14：按职工的工资值升序检索出全部职工信息。实现语句：SELECT * FROM 职工 ORDER BY 工资 注意：默认为升序例题2.15：先按仓库号排序，再按工资排序并输出全部职工信息。实现语句：SELECT * FROM 职工 ORDER BY 仓

9、库号,工资 2.8 SQL计算查询：1.用于检索的函数：COUNT()计数 SUM()求和 AVG()计算平均值 MAX()求最大值 MIN()求最小值2.例子：例题2.16：找出供应商所在地的数目。实现语句：SELECT COUNT(DISTINCT 地址) FROM 供应商例题2.17：求出供应商数据表中记录数目。实现语句：SELECT COUNT(*) FROM 供应商例题2.18：求支付的工资总数。实现语句：SELECT SUM(工资) FROM 职工例题2.19：求北京和上海的仓库职工的工资总和。实现语句：SELECT SUM(工资) FROM 职工 WHERE 仓库号 IN (SE

10、LECT 仓库号 FROM 仓库 WHERE 城市=”北京” OR 城市=”上海”)例题2.20：求所有职工的工资都多于1210元的仓库的平均面积。实现语句：SELECT AVG(面积) FROM 仓库 WHERE 仓库号 NOT IN (SELECT 仓库号 FROM 职工 WHERE 工资<=1210)例题2.21：求在WH2仓库工作的职工的最高工资值。实现语句：SELECT MAX(工资) FROM 职工 WHERE 仓库号=”WH2”2.9 SQL分组与计算查询：基本格式：GROUP BY GroupColumn ,GroupColumn 例题2.22：求每个仓库的职工的平均工资

11、。实现语句：SELECT 仓库号,AVG(工资) FROM 职工 GROUP BY 仓库号2.10 SQL的几个特殊选项：1. 将查询结果存放到数组中：语句格式：INTO ARRAY ArrayName例题：将查询到的职工放到数组TEMP中。 SELECT * FROM 职工 INTO ARRAY temp2. 将查询结果存放到临时文件中语句格式：INTO CURSOR CursorName临时文件是一只读的DBF文件，可以像一般的DBF文件一样使用，但只读。当关闭该文件时该文件会自动删除。例题：将查询到的职工放到DBF文件TEMP中。 SELECT * FROM 职工 INTO CURSOR

12、 temp3. 将查询结果存放到永久表中语句格式：INTO DBF|TABLE TableName 例题：将查询到的职工放到DBF文件TEMP中。 SELECT * FROM 职工 INTO DBF temp4. 将查询结果存放到文本文件中语句格式：TO FILE FileName例题：将查询到的职工放到文本文件TEMP中。 SELECT * FROM 职工 TO FILE temp5. 将查询结果直接输出到打印机中语句格式：TO PRINT PROMPT例题：SELECT * FROM 职工 TO PRINT三、 利用查询设计器查询3.1 查询设计器：1. 建立查询的方法：u 使用“项目管理

13、器”建立查询；u 使用“新建”对话框建立查询；u 使用命令CREATE QUERY命令建立查询；u 通过直接编辑.qpr文件建立。2. 查询设计器：u 字段：选择需要显示的字段；u 联接：编辑联接条件；u 筛选：指定查询条件；u 排序依据：用于指定排序字段；u 分组依据：用于分组；u 杂项：指定是否要重复记录及列在前面的记录，与DISTINCT及TOP对应。3.2 查询设计器的局限性：利用查询设计器不能够设计出所有的查询，如：嵌套查询、查询去向到数组等。3.3 运行查询：1.查询运行方法：u 使用“项目管理器”运行；u 使用命令运行：格式：DO QueryFile注意：使用命令运行查询时，必须

14、给出查询文件的扩展名qpr，如：DO abc.qpr如果不给出扩展名(DO abc)，VFP将把命令解释为 DO abc.prg，即执行程序文件。2查询去向：u 浏览：在“浏览(BROWSE)”窗口显示；u 临时表：命名的临时文件；u 表：命名表；u 图形：统计图；u 屏幕：屏幕直接输出（类似LIST）；u 报表：报表文件.frxu 标签：标签文件.lbx四、 数据表记录的SQL操作：4.1 插入：标准格式：INSERT INTO dbf_name(fname1,fname2,) VALUES(eExoression1,eExoression2,)VFP特殊格式：INSERT INTO dbf

15、_name FROM ARRAY ArrayName|FROM MEMVAR例题4.1：往订购单关系中插入元组（”E7”,”S4”,”OR01”,2001/05/25）。实现语句：INSERT INTO 订购单 VALUE(”E7”,”S4”,”OR01”,2001-05-25)例题4.2：往订购单关系中插入元组（”E7”,”OR01”）。实现语句：INSERT INTO 订购单(职工号,订购单号) VALUE(”E7”,”OR01”)注意：u 用INSERT命令在数据表中插入数据时，该数据表事先可以不必打开；u 当数据表设置了“主索引”或“候选索引”时，不能用以前的INSERT与APPEND

16、来添加记录，只能使用INSERT INTO添加。(实际有出入)4.2 更新：格式：UPDATE TableName SET Column_Name1=eExoression1,Column_Name2=eExoression2 WHERE Condition例题3.4：给WH1仓库的职工提高10%的工资。实现语句：UPDATE 职工SET 工资=工资+工资*0.1 WHERE 仓库号=”WH1”等价语句：replace工资 with工资*1.1 for仓库号=”WH1”4.3 删除：格式：DELETE FROM TableName WHERE Condition例题3.5：删除仓库关系中仓库号

17、值是WH2的元组。实现语句：DELETE FROM 仓库 WHERE 仓库号=”WH2”注意：在VFP中，SQL DELETE命令同样是逻辑删除，如果要物理删除记录同样需要使用PACK命令。五、 数据表结构的SQL操作：5.1 表定义：1.建立数据表简单操作基本格式：CREATE TABLE|DBF TableName Name LongTableName | FREE(FieldName1 FieldType(nFieldWidth,nPrecision)其中：TableName：新建数据表的表名称FieldName ：字段名FieldType ：用字母表示的字段类型NFieldWidth：

18、字段总宽度NPrecision：小数位数FREE：建立自由表例题5.1：用SQL命令建立“订货管理1”数据库，然后建立“仓库1”数据表。实现语句：CREATE DATABASE 订货管理1CREATE TABLE 仓库1 (仓库号 C(5),城市 C(10),面积 I)2.数据表字段有效性设置方法基本格式：CREATE TABLE|DBF TableName1 Name LongTableName FREE(FieldName1 FieldType(nFieldWidth,nPrecision) CHECK lExpression ERROR cMessageText1 )例题5.2：用SQL

19、命令建立“订货管理1”数据库，然后建立“仓库1”数据表，并定义面积字段的有效性规则为面积必须大于0。实现语句：CREATE DATABASE 订货管理1 CREATE TABLE 仓库1 (仓库号 C(5),城市 C(10),面积 I ；CHECK(面积>0) ERROR “面积必须大于0”)3.数据表字段默认值设置方法基本格式：CREATE TABLE|DBF TableName1 Name LongTableName FREE(FieldName1 FieldType(nFieldWidth,nPrecision) DEFAULT eExpression )例题5.3：建立数据表“仓

20、库1”，并将“仓库1”数据表的“面积”字段的默认值设置为200。实现语句：CREATE TABLE 仓库1(仓库号 C(5),城市 C(10),面积 I DEFAULT 200)4.数据表字段主关键字或候选索引设置方法基本格式：CREATE TABLE|DBF TableName1 Name LongTableName FREE(FieldName1 FieldType(nFieldWidth,nPrecision) PRIMARY KEY | UNIQUE ) 注意：UNIQUE在此处是候选索引例题5.4：建立数据表“仓库1”，并将“仓库号”字段设置为主关键字。实现语句：CREATE TAB

21、LE 仓库1(仓库号 C(5) PRIMARY KEY,城市 C(10),面积 I)5.2 表删除：基本格式：DROP TABLE | DBF TableName 例题5.5：删除数据表“仓库1”。实现语句：DROP TABLE 仓库15.3 表结构修改：1.添加新字段、修改已有字段并设置字段级相关规则：基本格式：ALTER TABLE TableName ADD | ALTER COLUMN FieldName FieldType(nFieldWidth,nPrecision)NULL | NOT NULL&&字段是否允许为空CHECK lExpression ERROR c

22、MessageText&&设置字段的有效性规则DEFAULT eExpression&&设置字段的有效性规则PRIMARY KEY | UNIQUE&&设置字段索引类型例题5.6：为订购单1表增加一个货币类型的总金额字段。实现语句：ALTER TABLE 订购单1 ADD 总金额 Y CHECK 总金额>0 ERROR “总金额应该大于0”例题5.7：将订购单1表的订购单号字段的宽度由原来的5改为6。实现语句：ALTER TABLE 订购单1 ALTER 订购单号 C(6)2.定义、修改、删除字段级有效性规则和默认值定义：基本格式：ALTE

23、R TABLE TableName ALTER COLUMN FieldName NULL | NOT NULL&&字段是否允许为空ALTER TABLE TableName ALTER COLUMN FieldName SET CHECK lExpression ERROR cMessageText&&设置字段的有效性规则ALTER TABLE TableName ALTER COLUMN FieldName DROP CHECK&&删除字段的有效性规则ALTER TABLE TableName ALTER COLUMN FieldName S

24、ET DEFAULT eExpression&&设置字段的默认值ALTER TABLE TableName ALTER COLUMN FieldName DROP DEFAULT&&删除字段的默认值例题5.8：修改(定义)总金额字段的有效性规则。实现语句：ALTER TABLE 订购单1 ALTER 总金额 SET CHECK 总金额>100 ERROR “总金额应该大于100”例题5.9：删除总金额字段的有效性规则。实现语句：ALTER TABLE 订购单1 ALTER 总金额 DROP CHECK3.删除字段、修改字段名，定义、修改、删除表一级的有效性

25、规则：基本格式：ALTER TABLE TableName DROP COLUMN FieldName&&删除字段ALTER TABLE TableName RENAME COLUMN FieldName1 TO FieldName2&&修改字段名ALTER TABLE TableName SET CHECK lExpression ERROR cMessageText&&设置表的有效性规则ALTER TABLE TableName DROP CHECK&&删除表的有效性规则例题5.10：将订购单1表的总金额字段名改为金额。实现语句：ALTER TABLE 订购单1 RENAME 总金额 TO金额例题5.11：删除订购单1金额字段。实现语句：ALTER TABLE 订购单1 DROP 金额六、 视图：6.1 视图的命令操作：1.视图的定义格式：格式：CREATE VIEW ViewName AS SelectStatement其中：SelectStatemen