4教学指南及上机指导第4章.docx
《4教学指南及上机指导第4章.docx》由会员分享，可在线阅读，更多相关《4教学指南及上机指导第4章.docx（27页珍藏版）》请在冰点文库上搜索。
4教学指南及上机指导第4章
第4章结构化查询语言SQL
（一）教学提要
1．掌握SQL的数据定义
2．掌握SQL的数据操纵方法
3．掌握SQL的数据查询方法
（二）课程内容
1．SQL是一种综合的、通用的、功能极强的关系数据库语言，它包括数据定义、数据操纵、数据管理、存取保护、处理控制等多种功能。
SQL的数据定义包括数据库的定义、数据表的定义、视图的定义、规则的定义等。
在VisualFoxPro6.0中创建数据库可以使用选单方式，也可以使用CREATEDATABASE命令。
创建数据库后，就可以创建存储在数据库中的表。
除了使用VisualFoxPro6.0的选单或命令创建数据表外，还可以使用SQL的CREATETABLE命令来创建表。
在使用CREATETABLE命令来创建表时，必须首先确定表所包含的字段、各字段的数据类型（如果必要的话，还可以定义数据类型的长度）以及这些字段是否允许存放空值等。
在创建表时设置的表属性越详尽，那么表的整个定义过程的效率也就越高，也可以先创建基本表，以后再逐一定义表的其他附属属性。
表4.1列出了VisualFoxPro6.0定义字段的类型、宽度及小数位。
表4.1字段类型
字段类型
代表字符
宽度
小数位
字段类型
代表字符
宽度
小数位
字符型
C
n
–
双精度型
B
–
d
货币型
Y
–
–
整型
I
–
–
数值型
N
n
d
逻辑型
L
–
–
浮动型
F
n
d
备注型
M
–
–
日期型
D
–
–
通用型
G
–
–
日期时间型
T
–
–
使用CREATETABLE命令定义表时，字段的数据类型必须使用代表字符。
表4.1中宽度和小数位栏中为“–”，表示不需要指定长度，系统自动设置默认的宽度和小数位。
（1）定义表
由于CREATETABLE命令格式比较复杂，可以将命令格式按定义功能的不同进行分解。
①定义简单表
CREATETABLE<表名>[FREE]（<字段名1><字段类型>[（宽度[,小数位]）]
[NULL|NOTNULL][,<字段名2>…]）
②定义表并设置字段的主索引
CREATETABLE<表名>[FREE]（<字段名1><字段类型>[（宽度[,小数位]）]
[PRIMARYKEY][,<字段名2>…]）
③定义表并设置字段的有效性规则
CREATETABLE<表名>[FREE]（<字段名1><字段类型>[（宽度[,小数位]）]
[CHECK<表达式>[ERROR<提示信息>]][,<字段名2>…]）
④定义表并设置字段的默认值
CREATETABLE<表名>[FREE]（<字段名1><字段类型>[（宽度[,小数位]）]
[DEFAULT<表达式>][,<字段名2>…]）
⑤定义表并设置表之间的关联
CREATETABLE<表名1>[FREE]（<字段名1><字段类型>[（宽度[,小数位]）]
[FOREIGNKEY<表达式>TAG<标记1>REFERENCES<表名2>
[TAG<标记2>]][,<字段名2>…]）
（2）修改表结构
①增加字段
ALTERTABLE<表名>ADD<字段名1><字段类型>[（宽度[,小数位]）]
[,<字段名2>…]
②修改字段
ALTERTABLE<表名>ALTER<字段名1><字段类型>[（宽度[,小数位]）]
[,<字段名2>…]
在增加或修改字段的同时可以设置或修改字段的属性，如字段的默认值、有效性规则、表间的关联等。
③删除字段
ALTERTABLE<表名>ALTER<字段名1>[,<字段名2>…]
（3）删除表
①移去数据库中的表
REMOVETABLE<表名>
②删除表
DROPTABLE<表名>
2．SQL的数据操纵是指对数据库中数据的操作功能，主要包括数据的插入、更新和删除操作。
（1）插入记录
INSERTINTO<表名>[（<字段名1>[,<字段名2>,…]）]
VALUES（<表达式1>[,<表达式2>,…]）
使用该命令一次只能插入一条记录，VALUES短语中各表达式的值要与对应字段的数据类型一致。
（2）更新记录
UPDATE<表名>SET<字段名1>=<表达式1>[,<字段名2>=<表达式2>…]
WHERE<条件>
该命令一次可以更新多个字段；使用WHERE<条件>短语可以成批修改符合条件的记录；省略WHERE<条件>短语，则更新全部记录。
（3）删除记录
DELETEFROM<表名>[WHERE<条件>]
该命令是逻辑删除记录，如果要物理删除记录，还需要使用PACK命令。
如果省略WHERE<条件>短语，则删除全部记录。
3．数据查询是SQL的核心。
SQL的查询命令也称做SELECT命令，它的基本形式是SELECT…FROM…WHERE。
SELECT查询命令包含FROM短语、WHERE短语、GROUPBY短语、HAVING短语、ORDERBY短语和INTO短语组成。
FROM短语是以逗号隔开的一系列数据表名、视图名。
WHERE短语、GROUPBY短语、HAVING短语等用来根据一定的条件对数据进行过滤。
使用INTO短语可以将查询创建一个新的数据表。
（1）简单查询
简单查询中不需要指定查询条件，可以查询部分或全部记录。
命令格式为：
SELECT[DISTINCT][<别名>.]<查询项>[AS<列标题>]FROM<表名>
DISTINCT选项的作用是去掉查询结果中的重复值。
在查询中可以使用COUNT（）,SUM（）,AVG（）,MIN（）和MAX（）等函数返回计算值。
①COUNT（）：
返回符合条件的记录数。
②SUM（）：
求和。
③AVG（）：
求平均值。
④MIN（）：
求最小值。
⑤MAX（）：
求最大值。
（2）条件查询记录
通过条件查询记录时使用WHERE短语。
命令格式为：
SELECT[DISTINCT][[<别名>.]<查询项>[AS<列标题>]…]
FROM<表名>WHERE<条件>
在查询条件中可以使用下面的运算符。
①关系运算符：
=,<>（!
=,#）,==,>,>=,<,<=。
②逻辑运算符：
NOT,AND,OR。
另外还有关系运算：
BETWEEN…AND…，IN（）及LIKE。
（3）查询结果排序
使用ORDERBY短语可以通过一个或多个排序项来对查询结果进行排序。
命令格式为：
SELECT[DISTINCT][[<别名>.]<查询项>[AS<列标题>]…]
FROM<表名>ORDERBY<排序项>[ASC|DESC]
[,<排序项>[ASC|DESC]…]
排序的方式可以是升序（ASC）或降序（DESC）。
如果ORDERBY短语中有多个排序项，查询结果进行嵌套排序。
（4）查询结果分组
GROUPBY短语用来对查询结果进行分组，分组结果中的每一行一般都要产生一个聚合值，因此可以使用系统提供的COUNT（）,SUM（）,AVG（）,MIN（）和MAX（）聚合函数。
SELECT[DISTINCT][[<别名>.]<查询项>[AS<列标题>]…]
FROM<表名>GROUPBY<分组项>[,<分组项>]
[HAVING<条件>]
HAVING<条件>短语表示在分组结果中，筛选满足条件的组。
HAVING短语通常和GROUPBY短语一起使用。
（5）嵌套查询
嵌套查询是指在SELECT查询条件中包含另一个或多个SELECT语句。
通常在具有关联关系的多个表中使用嵌套查询。
（6）查询结果输出
用户根据需要可以将查询结果直接输出到屏幕、打印机、数组、文本文件、临时表或表中。
使用INTO或TO短语来设置输出选项，如表4.2所示。
表4.2INTO或TO短语选项的含义
输出选项
含义
INTO
ARRAY<数组名>
将结果存储到一个二维数组中
CURSOR<表名>
将结果保存到临时表中
DBF<表名>
将结果保存到表中
TABLE<表名>
TO
FILE<文本文件名>
[ADDITIVE]
将结果保存到一个文本文件中。
选择ADDITIVE选项，不覆盖原文件内容，将结果追加到原文件的末尾
PRINTER
将结果从打印机上打印出来
SCREEN
将结果显示在屏幕上
（三）教学要求
本章节内容较深，命令较复杂，建议教学过程中有层次、有选择的讲解，以让学生最终掌握为准，可以参考SQLserver2000数据库应用系统进行讲解。
（四）实验指导
实习1SQL的数据定义
跟我做
∙掌握使用SQLCREATE命令建立表的方法。
∙掌握使用SQL命令修改表结构的方法。
实例1使用CREATE命令创建一个“商品管理”数据库，再用SQLCREATE命令建立“部门”表（字段名为：
部门号/C/2，部门名称/C/16）。
操作步骤：
①建立“商品管理”数据库，在“命令”窗口键入命令：
CREATEDATABASE商品管理
②建立“部门”表，在“命令”窗口键入命令：
CREATETABLE部门（部门号C
（2）,部门名称C（16））
这时创建的表是自由表，打开“部门”表的设计器，可以观察创建的表结构，如图4.1所示。
图4.1“部门”表的结构
可以创建一个项目文件或打开一个已经存在的项目文件，再将“商品管理”数据库添加到项目文件中，如“图书管理”，然后将“部门”表添加到“商品管理”数据库中。
想一想
在表设计器中的自由表和数据库表结构有什么不同？
实例2在“商品管理”数据库中建立“商品”表（字段名为：
部门号/C/2，商品号/C/4，商品名称/C/12，单价/N6/2，数量/N/3，产地/C/8），并对“单价”字段设置有效性规则“单价>0”。
操作步骤：
①打开项目文件及“商品管理”数据库。
②在“命令”窗口键入命令：
CREATETABLE商品（部门号C
（2）,商品号C（4）,商品名称C（12）,单价N（6,2）；
CHECK（单价>0）ERROR"单价必须大于零!
",数量N（3）,产地C（8））
想一想
在上例中如果设计产地的默认值为“广东”，CREATETABLE应如何改写？
实例3修改“部门”表，增加一个“地址”字段，数据类型为C，宽度为10。
在“命令”窗口键入命令：
ALTERTABLE部门ADD地址C（10）
则在“部门”表中增加了一个“地址”字段。
实例4设置“部门”表中的“部门号”字段为NOTNULL且为主索引。
在“命令”窗口键入命令：
ALTERTABLE部门ALTER部门号C
（2）NOTNULLPRIMARYKEY
实例5通过“部门号”关键字建立“部门”表与“商品”表的关联。
由于“部门”表已按“部门号”主关键字建立主索引，而“商品”表中可以有多条“部门号”相同的记录，因此，两表可以建立一对多的关系。
在“命令”窗口键入命令：
ALTERTABLE商品ADDFOREIGNKEY部门号TAG部门号；
REFERENCES部门
上述命令说明在“商品”表中通过“FOREIGNKEY部门号TAG部门号REFERENCES部门”命令短语设置了一个一对多的关系。
用“FOREIGNKEY部门号”在“商品”表的“仓库号”上建立一个普通索引，同时说明该字段是连接字段。
打开数据库设计器可以查看表间建立的关系，如图4.2所示。
图4.2表间的关联
实例6删除“部门”表中的“地址”字段。
在“命令”窗口键入命令：
ALTERTABLE部门DROP地址
使用DROPTABLE命令可以直接删除数据库中的表。
练一练
1．填空题
（1）SQL按其功能可以分为、和3部分。
（2）SQL的数据定义包括定义、定义、定义、定义等。
（3）使用CREATETABLE命令定义表结构时，要设置字段的有效性规则，使用短语，设置字段的默认值使用短语，设置主关键字段使用短语，设置候选索引使用短语。
（4）使用CREATETABLE命令定义表结构时不需要定义字段宽度的字段是货币型、日期型、、、、、和。
2．选择题
（1）关于CREATETABLE课程（课程号C（4）PRIMARYKEY,课程名C（8））命令，下列说法错误的是（）。
A.该命令定义的“课程”表中包含“课程号”和“课程名”两个字段
B.该命令定义“课程”表时自动设置“课程号”字段为主索引
C.该命令定义“课程”表时自动设置“课程号”字段为主索引，“课程名”字段为候选索引
D.“课程”表中的“课程号”字段和“课程名”字段都是字符型
（2）要在“考生”表中增加一个“职业”字段（C/8），正确的SQL命令是（）。
A.ALTERTABLE考生DROP职业B.CREATETABLE考生职业（C（8））
C.ALTERTABLE考生ALTER职业C（8）D.ALTERTABLE考生ADD职业C（8）
动手做
1．在“图书管理”项目中创建一个“图书”数据库，再在“图书”数据库中使用SQLCREATE命令定义如表4.2所示的“Book”表，并设置“图书ID”字段为主索引。
表4.2“Book”表的结构
字段名
类型
宽度
小数位数
字段名
类型
宽度
小数位数
图书ID
C
5
版次
C
2
书名
C
20
出版日期
D
8
作者
C
16
备注
M
4
单价
N
6
2
2．在“Book”表中增加一个“出版社ID”字段，其类型为字符型，宽度为2。
3．修改表结构，设置“单价”字段的有效性规则为“单价>0”。
4．将“作者”字段修改为“作者ID”字段，宽度修改为4。
5．删除“备注”字段和“出版社ID”字段。
6．在“图书”数据库中使用SQLCREATE命令定义“JY”表，“图书ID”字段为普通索引，并与“Book”表建立一对多关系，“借阅”表结构如表4.3所示。
7．设置“JY”表中的“借书日期”字段的默认值为系统当前日期。
实习2SQL的数据操纵
跟我做
∙能使用INSERT命令在表中插入记录。
∙能使用UPDATE命令修改记录。
∙能使用DELETE命令删除记录。
表4.4“部门”表中的记录
部门号
部门名称
40
家用电器部
10
电视录摄像机部
20
电话手机部
30
表4.3“JY”表的结构
字段名
类型
宽度
借书证号
C
4
图书ID
C
5
借书日期
D
8
还书日期
D
8
实例1将表4.4中的数据逐条插入到“部门”表中。
插入记录使用SQL的INSERTINTO命令，在“命令”窗口中依次键入下列命令：
INSERTINTO部门VALUES（"40","家用电器部"）
INSERTINTO部门VALUES（"10","电视录摄像机部"）
INSERTINTO部门VALUES（"20","电话手机部"）
INSERTINTO部门VALUES（"30",""）
由于“部门”表中只有“部门号”和“部门名称”字段，INSERTINTO命令中的VALUES短语提供的两个值分别插入到“部门号”和“部门名称”字段。
如果要将数据插入到指定的字段中，需要给出字段名。
上述最后一个命令可以改写为：
INSERTINTO部门（部门号）VALUES（"30"）
插入后的记录如图4.3所示。
图4.3“部门”表
想一想
在插入第1条记录时，能否连续使用下列命令：
INSERTINTO部门（部门号）VALUES（"40"）
INSERTINTO部门（部门名称）VALUES（"家用电器部"）
实例2将表4.5中的数据逐条插入到“商品”表中。
表4.5“商品”表中的记录
部门号
商品号
商品名称
单价
数量
产地
40
0101
A牌电风扇
200.00
10
广东
40
0104
A牌微波炉
350.00
10
北京
20
1032
C牌传真机
1000.00
20
上海
40
0105
B牌微波炉
600.00
10
上海
20
0110
A牌电话机
200.00
50
广东
30
1041
C牌计算机
6000.00
10
北京
在“命令”窗口中依次键入下列命令：
INSERTINTO商品VALUES（"40","0101","A牌电风扇",200,10,"广东"）
INSERTINTO商品VALUES（"40","0104","A牌微波炉",350,10,"北京"）
INSERTINTO商品VALUES（"20","1032","C牌传真机",1000,20,"上海"）
INSERTINTO商品VALUES（"40","0105","B牌微波炉",600,10,"上海"）
INSERTINTO商品VALUES（"20","0110","A牌电话机",200,50,"广东"）
INSERTINTO商品VALUES（"30","1041","C牌计算机",6000,10,"北京"）
插入后的记录如图4.4所示。
图4.4“商品”表
实例3将“商品”表中“部门号”为40的商品单价全部下浮10%。
修改记录使用SQL的UPDATE…SET命令，在“命令”窗口键入命令：
UPDATE商品SET单价=单价*0.9WHERE部门号="40"
使用BROWSE命令浏览记录的修改情况如图4.5所示。
图4.5单价下浮后的记录
从浏览结果中可以观察到单价已经被修改。
实例4将“商品”表中全部记录的数量增加2。
使用UPDATE…SET…WHERE命令修改全部记录时，可以省略WHERE短语。
在“命令”窗口键入命令：
UPDATE商品SET数量=数量+2
使用BROWSE命令浏览记录的修改情况如图4.6所示。
图4.6数量增加后的记录
从浏览结果中可以观察到数量都在原来的基础上增加了2。
实例5删除“商品”表中产地是“上海”的记录。
删除记录使用SQL的DELETEFROM命令，在“命令”窗口键入命令：
DELETEFROM商品WHERE产地="上海"
上述删除操作只是逻辑删除记录，如果要从表中删除记录，还需要键入PACK命令，对记录进行物理删除。
删除记录后的“商品”表记录如图4.7所示。
图4.7删除记录后的“商品”表
使用DELETEFROM命令删除记录时，省略WHERE短语则逻辑删除全部记录。
练一练
1．填空题
（1）一条INSERTINTO命令只能插入条记录。
（2）SQL语言中更新记录的命令是。
（3）SQL语言中删除记录的命令是。
2．选择题
（1）“工资”表中有“职工编号”和“工资”两个字段，下列插入记录命令正确的是（）。
A.INSERTINTO工资（工资,职工编号）VALUES（"A40",2000）
B.INSERTINTO工资（职工编号,工资）VALUES（"A40","2000"）
C.INSERTINTO工资VALUES（"A40","2000"）
D.INSERTINTO工资VALUES（"A40",2000）
（2）“工资”表中有“职工编号”和“工资”两个字段，只给“职工编号”字段输入数据，下列命令正确的是（）。
A.INSERTINTO工资（职工编号）VALUES（A40）
B.INSERTINTO工资（职工编号,工资）VALUES（"A40",""）
C.INSERTINTO工资VALUES（"A40"）
D.INSERTINTO工资VALUES（"A40",2000）
（3）将“工资”表中全部记录的工资在原来的基础上提高20%，下列命令正确的是（）。
A.UPDATE工资SET工资*1.2B.UPDATE工资SET工资*1.2=工资
C.UPDATE工资SET工资=工资*1.2D.UPDATE工资SET工资WITH工资*1.2
（4）逻辑删除“工资”表中工资小于1000的记录，下列命令正确的是（）。
A.DELETEFROM工资FOR工资<1000B.DELETE工资WHERE工资<1000
C.DELETEFROM工资<1000D.DELETEFROM工资WHERE工资<1000
（5）如果学生表“STUDENT”是使用下面的SQL语句创建的：
CREATETABLESTUDENT（SNOC（4）PRIMARYKEYNOTNULL,SNC（8）,;
SEXC
（2）,AGEN
（2）CHECK（AGE>15ANDAGE<30）
则下面的INSERT语句中可以正确执行的是（）。
A.INSERTINTOSTUDENT（SNO,SEX,AGE）VALUES（"S9","男",17）
B.INSERTINTOSTUDENT（SNO,SEX,AGE）VALUES（"李安琦","男",20）
C.INSERTINTOSTUDENT（SEX,AGE）VALUES（"男",20）
D.INSERTINTOSTUDENT（SNO,SN）VALUES（"S9","安琦",16）
动手做
1．使用INSERTINTO命令在“图书”数据库“Book”表中插入以下3条记录。
记录号图书ID书名作者ID单价版次出版日期
1B2350东方之子R98012.0011998/12/23
2W3271天外来客R00219.5032001/01/12
3C7612世界之窗R32121.2021999/07/24
2．使用INSERTINTO命令在“JY”表中插入以下4条记录。
记录号借书证号图书ID借书日期还书日期
1J006W32712003/10/182003/12/10
2J721C76122004/01/072004/03/25
3J006B23502004/05/11
4J123W32712004/02/192004/04/09
（提示：
表示日期型常量时用花
升级会员 