第4次 SQL.docx

1、第4次 SQLSQL概述考点:1.概念SQL是结构化查询语言Structured Query Language的缩写。是关系数据库的通用语言。包括数据查询(SELECT)、数据定义(数据库、表、视图的建立、修改和删除)、数据操纵（对数据记录的增加、删除和修改）和数据控制（用户权限的分配与回收）4部分。Visual FoxPro在SQL方面支持数据定义、数据查询和数据操纵功能。另外，由于Visual FoxPro自身在安全控制方面的缺陷，所以它没有提供数据控制功能。2 SQL语言的特点(1)一种一体化的语言。(2)一种高度非过程化的语言 /指不需要编写完整的程序(3)语言非常简洁。(4)可以直接

2、以命令方式交互使用，也可以以程序方式使用。2. 查询功能SQL的核心是查询，基本形式由SELECT-FROM-WHERE查询块组成，多个查询块可嵌套执行，如表5-1所示。表5-1 SQL命令动词 考点：简单查询1 简单查询无条件查询SELECT FROM或条件查询：SELECTFROM WHERE2 简单的连接查询连接是关系的基本操作之一，连接查询是一种基于多个关系的查询。SELECT ; FROM 表A INNER JOIN 表B ; ON A.字段名 = B.字段名3 嵌套查询嵌套查询所要求的结果出自一个关系，但关系的条件却涉及多个关系。其内层基本上也是一个SELECT-FROM-WHER

3、E查询语句。这种简单嵌套查询，可使用谓词IN或NOT IN来判断在外层的查询条件中是否包含内层查询的结果。SELECT 教师信息表.工号, 教师信息表.姓名, 教师信息表.所在系 ;FROM教师信息表;WHERE教师信息表.工号 IN (SELECT 选课成绩表.教师工号 FROM 选课成绩表) 功能 ：查询有代课教师的工号，姓名，所在系。考点：特殊运算符号1 BETWEENAND含义：该查询的条件是在范围之内，相当于用AND连接的一个逻辑表达式。比如： 成绩在80,90成绩 BETWEEN 80 AND 902 LIKE含义：它是一个字符串匹配运算符，与之搭配使用的通配符包括“%”和“_”。

4、“%” : 可以匹配任意多个任意的字符。“_”：只能匹配一个任意字符。 比如： 查找姓“李”的同学，字段为 姓名 姓名 LIKE “李%”考点：排序使用SQL SELECT可以将查询结果进行排序，排序的短语是ORDER BY。格式：ORDER BY Order_ItemASCIDESC，Order_ItemASC|DESC说明：可以按升序(ASC)或降序(DESC)排序，允许按一列或多列排序。考点：计算查询1 简单的计算查询SQL不仅具有一般的检索能力，而且还有计算方式的检索，用于计算检索的函数有：COUNT(计数)、SUM(求和)、AVG(计算平均值)、MAX(求最大值)及MIN(求最小值)

5、。比如：(1) 查学生基本情况表中的学生总人数 SELECT COUNT(*表示参数) AS 总人数 FROM 学生基本情况表(2) 查询选修同一门课程的学生的平均成绩在75分以上的课程号及平均成绩。SELECT 课程号,AVG(成绩) AS 平均成绩 FROM 学生成绩表 GROUP BY 课程号 HAVING平均成绩=75 在”学生选课成绩表”中， 包含学号、姓名、三门课的成绩：英语、高数、政治， 查询每个学生三门课的平均成绩SELECT学生选课成绩表.学号, 学生选课成绩表.姓名,（学生选课成绩表.英语 +学生选课成绩表.高数 + 学生选课成绩表.政治）/3 AS 平均成绩 ;FROM学

6、生选课成绩表2 分组与计算查询在SQL SELECT中可以利用GROUP BY子句进行分组计算查询。格式：GROUP BY GroupColumn，GroupColumnHAVING FilterCondition说明：可以按一列或多列分组，还可以用HAVING子句进一步限定分组的条件。它必须跟在GROUP BY子句之后，不可单独使用，与WHERE子句并不矛盾。考点: 别名和自连接查询(1)SQL语句允许在FROM短语中为关系定义别名，格式为： (2) SQL不仅可以对多个关系进行连接操作，也可以将同一关系与其自身进行连接，这种连接就称为自连接。在关系的自连接操作中，别名是必不可少的。比如：对

7、学生基本情况表做自连接后的查询考点：Visual FoxPro中SQL SELECT的几个特殊选项(l)显示部分结果，格式为： TOP nExprPERCENT(2)将查询结果存放到数组中，格式为： INTO ARRAY ArrayName(3)将查询结果存放到临时文件中，格式为： INTO CURSOR CursorName(4)将查询结果存放到永久表中，格式为： INTO DBF|TABLE TableName(5)将查询结果存放到文本文件中，格式为： TO FILE FileNameADDITIVE(6)将查询结果直接输出到打印机，格式为： TO PRINTERPROMPT3. 数据操纵

8、功能考点：插入Visual FoxPro支持两种SQL插入命令的格式，一种是标准格式，另一种是特殊格式。INSERT命令1标准格式 INSERT INTO dbf name(frame 1，fname2，) VALUES(eExpressionl，eExpression2，) 比如： 往学生选课成绩表中插入一条记录（”044138”,”02”,”0105”,”04051”,85）INSERT INTO 选课成绩表VALUES(”044138”,”02”,”0105”,”04051”,85)2 特殊格式 INSERT INTO dbf _name FROM ARRAY ArrayName|FRO

9、M MEMVAR考点：数据更新SQL的数据更新命令格式如下： UPDATE TableNameSET Column_Namel=eEpressionl，Column_Name2=eExpression2 WHERE Condition说明：利用WHERE字句指定条件，以更新满足条件的一些记录的字段值，并且一次可更新多个字段；如果不使用WHERE子句，则更新全部记录。比如： 将学生基本情况表中 学号为“044140”的学生改为入团，电话改为：6892222UPDATE 学生基本情况表;SET 是否入团=T,电话=”6892222” ;WHERE 学号=”044140”考点: 记录删除SQL从表中

10、删除数据的命令格式如下： DELETE FROM TableNameWHERE Condition说明：FROM指定从哪个表中删除记录，WHERE指定被删除的记录所满足的条件，如果不使用WHERE子句，则删除该表中的全部记录。小提示：当一个表定义一个主索引或候选索引后，由于相应的字段具有关键字的特性，即不能为空，所以只能用此命令插入记录、)APPEND和INSERT都是插入一条空白记录再输入各字段的值，故不能用它们来插入记录。5. 数据定义功能考点： 表的定义利用SQL命令建立的数据表同样可以完成在表设计器中设计表的所有功能。格式：CREATE TABLE I DBF TableNamelNA

11、ME LongTableNameFREE (FieldNamel FieldType(nFieldWidth，nPrecision)NULL |NOT NULL CHECK IExpressionl ERROR cMessageTextl DEFAULT eExpressionl PRIMARY KEY I UNIQUE PEFERENCES TableName2AG TagName1 NOCPTRANS ，FieldName2 ，PRIMARY KEY eEpression2 TAG TagName2|， UNIQUE eExpression3 TAG TagName3 ，FOREIGN K

12、EY eExpression4 TagName4NODUP REFERENCES TableName3 TAG TagName5 ，CHECK lExpression2ERROR cMessageText2) |FROM ARRAY ArravNasne 定义一个“student”表，字段情况如下： num 字符型，长度 8 name 字符型， 长度10 grade 数值型，长度为6，允许1位小数 CREATE TABLE student( num C(8), name C(10), grade N(6.1)说明：此命令除了建立表的基本功能外，还包括满足实体完整性的主关键字(主索引)PRIMA

13、RY KEY、定义域完整性的CHECK约束及出错提示信息ERROR、定义默认值DEFAULT等，另外还有描述表之间联系的FOREIGN KEY和REFERENCES等。考点：表的删除利用SQL命令删除表，可直接使用语句，格式为： DROP TABLE Table_name说明：如果删除的是自由表，则应该将当前打开的数据表先关闭，才能进行删除。如果删除数据库表，则要先打开数据库，在数据库中进行操作；否则，即使删除了数据库表，但记录在数据库中的信息并没有被删除，此后会出现错误提示。考点：表结构的修改修改表结构的命令是ALTER TABLE，该命令有3种格式。(1) 为某个表添加字段：ALTER T

14、ABLE TableNamel ADD| ALTERCOLUMNFieldNamel FieldTvpe(nFieldWidth，nPrecision)NULL |NOT NULL CHECK lExpressionlERROR cMessageTextl DEFAULT eExpressionl PRIMARY KEY |UNIQUE REFERENCES TableName2TAG TagNamel(2) 为某个表已存在的字段修改其相关设置：ALTER TABLE TableNamel ALTERCOLUMNFieldName2NULL|NOT NULL SET EFAULTeExpres

15、sion2SET CHECK lExpression2ERROR cMessageText2 DROP DEFAULTDROP CHECK(3)ALTER TABLE TableName1DROP COLUMNFieldName3 SET CHECK lExpression3ERROR cMessageText3 DROP CHECK ADD PRIMARY KEY eExpression3 TAG TagName2FOR lExpression4 DROP PRIMARY KEY ADD UNIQUE eExpression4TAG TagName3FOR lExpression5 DROP

16、 UNIQUE TAG TagName4 ADD FOREIGN KEYeExpression5 TAG TagName4FOR lExpression6 REFERENCES TableName2TAG TagName5 DROP FOREIGN KEY TAG TagName6SAVE RENAME COLUMN FieldName4 TO FieldName5考点：视图的定义1 视图的定义格式为： CREATE VIEW view_name(column_name，column_name) AS select_statement2从单个表派生出来的视图视图一经定义，就可以和基本表一样进行

17、各种查询，也可以进行一些修改操作。对于最终用户来说，有时并不需要知道操作的是基本表还是视图。3从多个表派生出来的视图视图一方面可以限定对数据的访问，另一方面又可以简化对数据的访问。4 视图的删除视图由于是从表派生出来的，所以不存在修改结构的问题，但是视图可以删除。格式：DROP VIEW 5关于视图的说明在关系数据库中，视图始终不真正含有数据，它总是原有表的一个窗口。所以，虽然视图可以像表一样进行各种查询，但是插入、更新和删除操作在视图上却有一定限制。在一般情况下，当一个视图是由单个表导出时可以进行插入和更新操作，但不能进行删除操作；当视图是从多个表导出时，插入、更新和删除操作都不允许进行。这

18、种限制是很有必要的，它可以避免一些潜在问题的发生。真题举例：2012.3：(19)查询“教师”表中“住址”字段中含有“望京”字样的教师信息，正确的SQL语句是 A)SELECT * FROM 教师 WHERE 住址 LIKE %望京% B)SELECT * FROM 教师 FOR 住址 LIKE %望京% C)SELECT * FROM 教师 FOR 住址 =%望京%)SELECT * FROM 教师 WHERE 住址 =%望京%(22)为“教师”表的职工号字段添加有效性规则：职工号的最左边三位字符是“110”，正确的SQL语句是 A)CHANGE TABLE 教师 ALTER 职工号 SET

19、 CHECK LEFT (职工号,3)=110 B)CHANGE TABLE 教师 ALTER 职工号 SET CHECK OCCURS (职工号,3)=110 C)ALTER TABLE 教师 ALTER 职工号 SET CHECK LEFT (职工号,3)=110D)ALTER TABLE 教师 ALTER 职工号 CHECK LEFT (职工号,3)=110(24)删除视图salary的命令是 A)DROP VIEW salary B)DROP salary VIEW C)DELECT salary D)DELECT salary VIEW 3135题使用如下三个数据库表： 图书(索书号

20、，书名，出版社，定价，ISBN) 借书证(借书证号，姓名，性别，专业，所在单位) 借书记录(借阅号，索书号，借书证号，借书日期，还书日期) 其中：定价是货币型，借书日期和还书日期是日期型，其他是字符型。 (31)查询借书证上专业为“计算机”的所有信息，正确的SQL语句是 A)SELECT ALL FROM 借书证 WHERE 专业=计算机 B)SELECT 借书证号 FROM 借书证 WHERE 专业=计算机 C)SELECT ALL FROM 借书记录 WHERE 专业=计算机 D)SELECT * FROM 借书证 WHERE 专业=计算机 (32)查询2011年被借过图书的书名、出版社和

21、借书日期，正确的SQL语句是A)SELECT 书名,出版社,借书日期 FROM 图书,借书记录WHERE 借书日期=2011 AND 图书.索书号=借书记录.索书号 B)SELECT 书名,出版社,借书日期 FROM 图书,借书记录 WHERE 借书日期=YEAR(2011) AND 图书.索书号=借书记录.索书号C)SELECT 书名,出版社,借书日期 FROM 图书,借书记录 WHERE图书.索书号=借书记录.索书号 AND YEAR(借书日期)=2011 D) SELECT 书名,出版社,借书日期 FROM 图书,借书记录 图书.索书号=借书记录.索书号 AND WHERE YEAR(借

22、书日期)=YEAR(2011) (33)查询所有借阅过“中国出版社”图书的读者的姓名和所在单位A)SELECT 姓名,所在单位 FROM 借书证,图书,借书记录 WHERE 图书.索书号=借书记录.索书号 AND 借书证.借书证号=借书记录.借书证号 AND 出版社=中国出版社 B)SELECT 姓名,所在单位 FROM图书,借书证WHERE 图书.索书号=借书证.借书证号 AND 出版社=中国出版社 C)SELECT 姓名,所在单位 FROM图书,借书记录 WHERE 图书.索书号=借书记录.索书号 AND 出版社=中国出版社 D)SELECT 姓名,所在单位 FROM 借书证,借书记录 W

23、HERE 借书证.借书证号=借书记录.借书证号 AND 出版社=中国出版社 (34)从借书证表中删除借书证号为“1001”的记录，正确的SQL语句是 A)DELETE FROM 借书证 WHERE 借书证号=1001 B)DELETE FROM 借书证 FOR 借书证号=1001 C)DROP FROM 借书证 WHERE 借书证号=1001 D)DROP FROM 借书证 FOR 借书证号=1001 (35)将选项为“锦上计划研究所”的所在单位字段值重设为“不详”，正确的SQL语句是 A)UPDATE 借书证 SET 所在单位=锦上计划研究所 WHERE 所在单位=不详 B)UPDATE 借

24、书证 SET 所在单位=不详 WITH所在单位=锦上计划研究所 C)UPDATE 借书证 SET 所在单位=不详 WHERE所在单位=锦上计划研究所D)UPDATE 借书证 SET 所在单位=锦上计划研究所 WITH 所在单位=不详2011.9:第(32)-(35)题使用如下3个表：商店 (商店号, 商店名, 区域名, 经理姓名)商品 (商品号, 商品名, 单价)销售 (商店号, 商品号, 销售日期, 销售数量)(32)查询在“北京”和“上海”区域的商店信息的正确命令是A)SELECT * FROM 商店 WHERE 区域名=北京 AND 域名名=上海B)SELECT * FROM 商店 WH

25、ERE 区域名=北京 OR 域名名=上海C)SELECT * FROM 商店 WHERE 区域名=北京 AND 上海D)SELECT * FROM 商店 WHERE 区域名=北京 OR 上海(33)查询单价最高的商品销售情况，查询结果包括商品号、商品名、销售日期、销售数量和销售金额。正确命令是A)SELECT 商品.商品号, 商品名, 销售日期, 销售数量, 销售数量*单价 AS 销售金额 FROM 商品 JOIN 销售 ON 商品.商品号=销售.商品号 WHERE 单价=(SELECT MAX(单价) FROM 商品)B)SELECT 商品.商品号, 商品名, 销售日期, 销售数量, 销售数

26、量*单价 AS 销售金额 FROM 商品 JOIN 销售 ON 商品.商品号=销售.商品号 WHERE 单价=MAX(单价)C)SELECT 商品.商品号, 商品名, 销售日期, 销售数量, 销售数量*单价 AS 销售金额 FROM 商品 JOIN 销售 WHERE 单价=(SELECT MAX(单价) FROM 商品)D)SELECT 商品.商品号, 商品名, 销售日期, 销售数量, 销售数量*单价 AS 销售金额 FROM 商品 JOIN 销售 WHERE 单价=MAX(单价)(34)查询商品单价在10到50之间、并且日销售数量高于20的商品名、单价、销售日期和销售数量，查询结果按单价降序

27、。正确命令是A)SELECT 商品名, 单价, 销售日期, 销售数量 FROM 商品 JOIN 销售 WHERE (单价 BETWEEN 10 AND 50) AND 销售数量20 ORDER BY 单价 DESCB)SELECT 商品名, 单价, 销售日期, 销售数量 FROM 商品 JOIN 销售 WHERE (单价 BETWEEN 10 AND 50) AND 销售数量20 ORDER BY 单价C)SELECT 商品名, 单价, 销售日期, 销售数量 FROM 商品,销售 WHERE (单价 BETWEEN 10 AND 50) AND 销售数量20 ON 商品.商品号=销售.商品号

28、ORDER BY 单价D)SELECT 商品名, 单价, 销售日期, 销售数量 FROM 商品,销售 WHERE (单价 BETWEEN 10 AND 50) AND 销售数量20 AND 商品.商品号=销售.商品号 ORDER BY 单价 DESC(35)查询销售金额合计超过20000的商店，查询结果包括商店名和销售金额合计。正确命令是A)SELECT 商店名, SUM(销售数量*单价) AS 销售金额合计 FROM 商店, 商品, 销售 WHERE 销售金额合计 20000B)SELECT 商店名, SUM(销售数量*单价) AS 销售金额合计20000 FROM 商店, 商品, 销售 WHERE 商品.商品号=销售.商品号 AND 商店.商店号=销售.商店号C)SELECT 商店名, SUM(销售数量*单价) AS 销售金额合计 FROM 商店, 商品, 销售 WHERE 商品.商品号=销售.商品号 AND 商店.商店号=销售.商店号 AND SUM(销售数量*单价)20000 GROUP BY 商店名D)SELECT 商店名, SUM(销售数量*单价) AS 销售金额合计 FROM 商店, 商品, 销售 WHERE 商品.商品号=销售.商品号 AND 商店.商店号=销售.商店号 GROUP BY 商店名 HAVING SUM(销售数量*单价)20000