4教学指南及上机指导第4章Word文档下载推荐.docx
《4教学指南及上机指导第4章Word文档下载推荐.docx》由会员分享,可在线阅读,更多相关《4教学指南及上机指导第4章Word文档下载推荐.docx(27页珍藏版)》请在冰点文库上搜索。
(1)定义表
由于CREATETABLE命令格式比较复杂,可以将命令格式按定义功能的不同进行分解。
①定义简单表
CREATETABLE<
表名>
[FREE](<
字段名1>
<
字段类型>
[(宽度[,小数位])]
[NULL|NOTNULL][,<
字段名2>
…])
②定义表并设置字段的主索引
[(宽度[,小数位])]
[PRIMARYKEY][,<
③定义表并设置字段的有效性规则
[CHECK<
表达式>
[ERROR<
提示信息>
]][,<
④定义表并设置字段的默认值
[DEFAULT<
][,<
⑤定义表并设置表之间的关联
表名1>
[FOREIGNKEY<
TAG<
标记1>
REFERENCES<
表名2>
[TAG<
标记2>
(2)修改表结构
①增加字段
ALTERTABLE<
ADD<
[,<
…]
②修改字段
ALTER<
在增加或修改字段的同时可以设置或修改字段的属性,如字段的默认值、有效性规则、表间的关联等。
③删除字段
[,<
(3)删除表
①移去数据库中的表
REMOVETABLE<
②删除表
DROPTABLE<
2.SQL的数据操纵是指对数据库中数据的操作功能,主要包括数据的插入、更新和删除操作。
(1)插入记录
INSERTINTO<
[(<
…])]
VALUES(<
表达式1>
表达式2>
…])
使用该命令一次只能插入一条记录,VALUES短语中各表达式的值要与对应字段的数据类型一致。
(2)更新记录
UPDATE<
SET<
=<
[,<
…]
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][[<
]…]
FROM<
WHERE<
在查询条件中可以使用下面的运算符。
①关系运算符:
=,<
>
(!
=,#),==,>
>
<
=。
②逻辑运算符:
NOT,AND,OR。
另外还有关系运算:
BETWEEN…AND…,IN()及LIKE。
(3)查询结果排序
使用ORDERBY短语可以通过一个或多个排序项来对查询结果进行排序。
]…]
ORDERBY<
排序项>
[ASC|DESC]
[ASC|DESC]…]
排序的方式可以是升序(ASC)或降序(DESC)。
如果ORDERBY短语中有多个排序项,查询结果进行嵌套排序。
(4)查询结果分组
GROUPBY短语用来对查询结果进行分组,分组结果中的每一行一般都要产生一个聚合值,因此可以使用系统提供的COUNT(),SUM(),AVG(),MIN()和MAX()聚合函数。
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
5
版次
2
书名
20
出版日期
8
作者
16
备注
4
单价
6
2.在“Book”表中增加一个“出版社ID”字段,其类型为字符型,宽度为2。
3.修改表结构,设置“单价”字段的有效性规则为“单价>
4.将“作者”字段修改为“作者ID”字段,宽度修改为4。
5.删除“备注”字段和“出版社ID”字段。
6.在“图书”数据库中使用SQLCREATE命令定义“JY”表,“图书ID”字段为普通索引,并与“Book”表建立一对多关系,“借阅”表结构如表4.3所示。
7.设置“JY”表中的“借书日期”字段的默认值为系统当前日期。
实习2SQL的数据操纵
∙能使用INSERT命令在表中插入记录。
∙能使用UPDATE命令修改记录。
∙能使用DELETE命令删除记录。
表4.4“部门”表中的记录
部门号
部门名称
40
家用电器部
10
电视录摄像机部
电话手机部
30
表4.3“JY”表的结构
类型
宽度
借书证号
借书日期
还书日期
实例1将表4.4中的数据逐条插入到“部门”表中。
插入记录使用SQL的INSERTINTO命令,在“命令”窗口中依次键入下列命令:
INSERTINTO部门VALUES("
40"
"
家用电器部"
)
10"
电视录摄像机部"
20"
电话手机部"
30"
由于“部门”表中只有“部门号”和“部门名称”字段,INSERTINTO命令中的VALUES短语提供的两个值分别插入到“部门号”和“部门名称”字段。
如果要将数据插入到指定的字段中,需要给出字段名。
上述最后一个命令可以改写为:
INSERTINTO部门(部门号)VALUES("
插入后的记录如图4.3所示。
图4.3“部门”表
在插入第1条记录时,能否连续使用下列命令:
INSERTINTO部门(部门名称)VALUES("
实例2将表4.5中的数据逐条插入到“商品”表中。
表4.5“商品”表中的记录
商品号
商品名称
数量
产地
0101
A牌电风扇
200.00
广东
0104
A牌微波炉
350.00
北京
1032
C牌传真机
1000.00
上海
0105
B牌微波炉
600.00
0110
A牌电话机
50
1041
C牌计算机
6000.00
在“命令”窗口中依次键入下列命令:
INSERTINTO商品VALUES("
0101"
A牌电风扇"
200,10,"
广东"
0104"
A牌微波炉"
350,10,"
北京"
1032"
C牌传真机"
1000,20,"
上海"
0105"
B牌微波炉"
600,10,"
0110"
A牌电话机"
200,50,"
1041"
C牌计算机"
6000,10,"
插入后的记录如图4.4所示。
图4.4“商品”表
实例3将“商品”表中“部门号”为40的商品单价全部下浮10%。
修改记录使用SQL的UPDATE…SET命令,在“命令”窗口键入命令:
UPDATE商品SET单价=单价*0.9WHERE部门号="
使用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)一条INSERTINTO命令只能插入条记录。
(2)SQL语言中更新记录的命令是。
(3)SQL语言中删除记录的命令是。
(1)“工资”表中有“职工编号”和“工资”两个字段,下列插入记录命令正确的是()。
A.INSERTINTO工资(工资,职工编号)VALUES("
A40"
2000)
B.INSERTINTO工资(职工编号,工资)VALUES("
"
2000"
C.INSERTINTO工资VALUES("
D.INSERTINTO工资VALUES("
2000)
(2)“工资”表中有“职工编号”和“工资”两个字段,只给“职工编号”字段输入数据,下列命令正确的是()。
A.INSERTINTO工资(职工编号)VALUES(A40)
(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工资<
(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("
D.INSERTINTOSTUDENT(SNO,SN)VALUES("
安琦"
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
(提示:
表示日期型常量时用花