第6章 SQL语言 讲课用Word格式文档下载.docx
《第6章 SQL语言 讲课用Word格式文档下载.docx》由会员分享，可在线阅读，更多相关《第6章 SQL语言 讲课用Word格式文档下载.docx（22页珍藏版）》请在冰点文库上搜索。
）是续行号，字段之间用逗号（，）隔开。
6.2.2修改数据表
ALTERTABLE<
[ADD/ALTER/drop/rename[COLUMN]
<
字段名>
[,小数位]
[NULL/NOTNULL]
[CHECK<
[DEFAULT<
[PRIMARYKEY/UNIQUE]
修改基本表结构
•例：
在“学生”表中增加“电话”字段，并设为候选索引。
ALTERTABLE学生;
ADDCOLUMN电话C（8）
6.2.3 删除数据表
•当某个数据表无用时，可将其删除，删除后，该表中的数据和在此表上所建的索引都被删除。
DROP TABLE＜表名＞
删除数据表。
删除表“学生”
DROP TABLE 学生
6.2SQL的数据查询功能
•SQL的查询功能实际上是数据操纵语言（DataManipulationLanguage简称DML）的范畴。
•数据查询是对数据库中的数据按指定内容和顺序进行检索输出
•数据查询是数据库的核心操作。
•SQL语言的数据查询只有一条SELECT语句，但用途最广泛，具有灵活的使用方法和丰富的功能。
6.2.1SELCET命令的格式
SELECT[ALL/DISTINCT]
[TOP<
[PERCENT]][<
别名>
.]<
列表达式>
[AS<
栏名>
][,[<
[AS<
]…]
FROM[<
数据库名!
>
]<
[,[<
]]<
[INNER/LEFT/RIGHT/FULLJOIN[<
[ON<
连接条件>
…]]
[[INTO<
新表名>
]/TOFILE<
文件名>
/TOPRINTER/TOSCREEN]]
[WHERE<
[AND<
[AND/OR<
筛选条件>
[AND/OR<
…]]]
[GROUPBY<
列名>
[,<
..]]<
HAVING<
[ORDERBY<
[ASC/DESC][,<
[ASC/DESC]…];
实现数据查询。
SELECT语句的执行过程：
根据WHERE子句的连接和检索条件，从FROM子句指定的基本表或视图中选取满足条件的元组
再按SELECT子句中指定的列表达式，选出元组中的属性值形成结果表
如有GROUP子句，则将查询结果按照指定＜列名＞相同的值进行分组
如果GROUP子句后有HAVING短语，则只输出满足HAVING条件的元组
如果有ORDER子句，查询结果还要按照指定＜列名＞的值进行排序。
•SELECT语句中各子句的使用分为：
投影查询、条件查询、分组查询、查询排序、连接查询、子查询。
6.3.2投影查询
•例6－6：
查询学生的情况。
SELECT 学号，姓名，入校总分，性别，；
出生年月 FROM 学生
注：
可以重新指定列的顺序。
•例6－7：
查询学生的全部情况。
SELECT * FROM 学生
•例6－8：
查询入校总分最低的五名学生基本情况，按入校总分由低到高进行排序，同时指定表中的字段在查询结果中的列标题。
SELECTTOP5学号AS学生编号,
姓名AS学生姓名,;
性别AS学生性别,;
入校总分AS高考分数;
FROM学生ORDERBY入校总分
top必须和orderby一起使用
•例6－9：
查询选课学生的学号。
selectdistinct学号from选课
•例6－10：
查询学生的学号并改名显示为学生编号。
select学号as学生编号from学生
6.3.3条件查询
•当要在数据表中找出满足某些条件的行时，则需使用WHERE子句指定查询条件
•WHERE子句中，条件通常由三部份来描述：
列名比较运算符列名、常数
•比较大小
•多重条件查询
•确定范围
•确定集合：
•部份匹配查询
6.3.4统计查询
在应用中，有的需要在原有数据上通过计算输出统计结果。
SQL提供了许多库函数，增强了检索的功能
•例6－20：
求学号为‘s0201109’学生的总分和平均分（显示学号）。
select学号,sum（成绩）as总分,avg（成绩）as平均分;
from选课;
where（学号='
s0201109'
）
6.3.5分组查询
•GROPUBY子句可以将查询结果按属性列或属性列组合在行的方向上进行分组，每组在属性列或属性列组合上具有相同的值。
•例6－25：
查询各位教师的教师号及其任课门数。
select教师号,count（*）as任课门数;
from授课;
groupby教师号
注意：
GROUP BY子句按教师号的值分组，所有具有相同教师号的元组为一组，对每一组使用函数COUNT进行计算，统计出各位教师任课的门数。
6.3.6查询的排序
•对查询结果使用ORDERBY子句对一个或多个属性进行排序，默认为升序。
•ORDER BY子句必须放在其他子句之后。
•例6－28：
查询选修了课程“C140”的学生学号和成绩，并按成绩降序排列。
select学号,成绩;
where课程号='
c140'
;
orderby成绩desc
6.3.7连接查询
•连接操作的目的：
通过加在连接字段上的条件将多个表连接起来，以便从多个表中查询数据。
•连接查询包括：
等值与非等值连接查询、自身连接查询、外连接查询。
•表的连接方法：
–方法1：
表之间满足一定条件的行进行连接，此时FROM子句中指明进行连接的表名，WHERE子句指明连接的列名及连接条件。
–方法2：
利用关键字JOIN进行连接。
方法2具体分为：
INNER JOIN（内部连接），LEFT JION（左连接），RIGHT JOIN（右连接），FULL JOIN（全连接）
1、等值连接与非等值连接
例6－31：
查询陈静教师所讲授的课程号
select教师.教师号,姓名,课程号;
from教师，授课；
where教师.教师号=授课.教师号and姓名=‘陈静’
一般的连接格式：
表名1>
列名1>
比较运算符>
表名2>
.][列名2]
比较运算符主要有：
=,>
<
>
=,<
=,!
=
取‘＝’时称为等值，其他为非等值连接。
注意：
上例中对于引用‘教师号’要指明属于哪个表。
2、自身连接
表与其自己进行连接称为表的自身连接。
主要用于同一个表中数据的比较。
例6－33：
查询所有比“陈静”工资高的教师姓名、职称、工资和“陈静”的工资。
selectx.姓名,x.工资,y.工资as陈静工资;
from教师asx,教师asy;
wherex.工资>
y.工资andy.姓名='
陈静'
3、外连接
•例6－35：
查询教师姓名、职称及课程名。
select姓名,职称,课程名;
from教师,课程,授课;
where教师.教师号=授课.教师号and;
授课.课程号=课程.课程号
6.2.8嵌套查询
•嵌套查询可将一系列简单查询构成复杂查询，增强查询功能。
子查询嵌套层次最多可为255层。
嵌套查询执行时由里向外处理。
1、返回一个值的子查询
•当子查询返回一个值时，可以使用比较运算符将父查询和了查询连接起来。
•例6－36：
查询与“陈静”老师职称相同的老师姓名和职称。
select姓名,职称;
from教师;
where职称=;
（select职称;
from教师;
where姓名='
2、返回一组值的子查询
•如果返回一组值，不能直接使用比较运算符，可以在比较运算符和子查询之间插入ANY/IN或ALL。
•例6－38：
查询讲授课程号为“C140”的教师姓名。
select姓名;
where教师号=any;
（select教师号;
from授课;
where课程号=‘C140'
6.3.9查询结果输出
使用INTO子句，可以将查询结果输出到一个新建的数据表或一个临时表。
INTO TABLE＜表名＞：
输出到数据表
INTO CURSOR：
输出到临时表
例6－43：
将每个同学选修的课程名及成绩输出到数据表（表名为学生成绩）
select姓名,课程名,成绩;
from学生信息,选课,课程;
where学生信息.学号=选课.学号and选课.课程号=课程.课程号;
intotable学生成绩
还有一些输出选项：
TO FILE＜文件名＞：
将查询结果输出到文件
TO PRINT：
将查询结果输出到打印机
TO SCREEN：
将查询结果输出到VF主窗口
6.4SQL的数据操纵功能
6.4.1插入数据
1.插入数据记录
INSERTINTO<
[（<
…]）]
VALUES （<
表达式1>
表达式2>
…]）
将新记录插入到指定的表中，分别用表达式1、表达式2等为字段名1、字段名2等赋值。
•说明：
＜表名是指要插入新记录的表；
＜字段名＞是可选项，指定添加数据的列；
VALUES子句指定待添加数据的具体值。
例6－44：
在学生表中插入一条学生记录
insertinto学生（学号,姓名,性别,出生年月,入校总分,三好生）;
values（‘s0201111’,‘徐 畅’,‘女’,{^1984-06-25},588,.T.）
2、插入多行记录
•插入多行记录用于表间的拷贝，将一个表中的数据抽取数行插入另一表中，可以通过子查询来实现。
插入数据
1、先建立新表学生平均成绩，用来存放学生姓名和学生选修课的平均成绩。
CREATE TABLE 学生平均成绩（姓名 CHAR（8），平均成绩 INT（3））
2、利用子查询求出各学生的平均成绩，把结果存放在新表学生平均成绩中。
INSERTINTO学生平均成绩；
SELECT姓名，AVG（成绩）；
FROM 学生，选课；
WHERE 学生.学号＝选课.学号；
GROUP BY 学号
6.4.2修改数据记录
•SQL语言可以使用UPDATE语句对表中的一行或多行记录的某些列值进行修改。
UPDATE ＜表名＞
SET<
=<
]…[WHERE<
条件>
对表中一行或多行记录的某些列值进行修改
＜表名＞指要修改的表，SET子句给出要修改的列及改后的值，WHERE子句指定待修改的记录应当满足的条件，WHERE子句省略时，则修改表中的所有记录。
1、修改一行记录
2、修改多行记录
3、用子查询修改记录
6.4.3删除数据记录
DELETEFROM<
[WHERE<
1、删除一行记录
例6－49：
删除刘伟教师的记录
DELETE FROM 学生 WHERE 姓名＝‘刘伟’
2、删除多行记录
例6－51：
删除所有学生的成绩
DELETE FROM 选课
3、利用子查询选择要删除的行
删除陈静教师授课的记录
DELETE FROM 授课；
WHERE 教师号＝；
（SELECT 教师号；
FROM 教师；
WHERE 姓名＝‘陈静’）
第七章小结
1.SQL语言的四大功能包括数据定义功能,数据查询功能,数据更新功能,数据控制功能.以及各种语句的使用.
2.查询语句的多种形式的使用
历届等级考试题选讲
11、（33次单选第11题）从学生选课表XK中（其中有学号、课程、成绩3个字段）查询选修了6门以上课程的学生学号，实现该功能的SELECT语句是（26）
A.SELECT学号FROMXKGROUPBY学号HAVING
B.SELECT学号FROMXKGROUPBY学号HAVINGcount（*）>
5
C.SELECT学号FROMXKGROUPBY学号HAVINGsum（*）>
D.SELECT学号FROMXKHAVING课程>
5GROUPBY学号
2、（33次读程序第2题）有如下的“学生”表
（1）执行“SELECT班级号，COUNT（*）FROM学生GROUPBY班级号ORDERBY2DESC”后，查询结果中共有（）条记录
A7B2C.3D.4
（2）第一条记录是（）
A.113B.221C.332D.441
（3）执行“SELECTMIN（年龄）FROM学生INTOARRAYTMP”后，下列说法正确的是（）
A.TMP中共有2个元素B.TMP
（1）的值为18
C.TMP（0）的值为18D.TMP
（1）的值为22
4、（33次）在教师管理数据库中有3个表：
其表名和结构分别如下：
教师（教师号C（6）,姓名C（8），职称C（10），年龄N（3），工资N（4））
授课（教师号C（6），课程号C（4））
课程（课程号C（4），课程名C（10），课时N（3））
（1）为教师表的“教师号”建立候选索引，索引名为JSH_
_____________TABLE教师ADDUNIQUE教师号TAGJSH
（2）将所有教师的工资上调20%
_________________教师SET工资=工资*1.2
（3）查询所授课程的课时>
=80的教师的教师号、姓名和课程名
SELECT教师.教师号,课程名FROM教师INNERJOIN授课INNERJOIN课程；
ON__________ON教师.教师号=授课.教师号；
______________课时>
=80
1.（32次单选第11题）在SQL的查询语句中，（26）短语用于实现关系的投影操作
A.WHEREB.SELECTC.FROMD.GROUPBY
2.（32次填空第4题）利用结构化查询语言SQL
（1）在给定的数据库表RS中有数值字段“工资”，将工资在原来基础上增加20%.
_____________RS_______________工资=工资*1.2
（2）在教工数据表JG中，查询职称为“助教”或“讲师”人员的姓名，工资和部门，并按工资降序排序。
SELECT姓名，工资，部门，FORMJGWHERE职称_______________________；
________________BY工资________________
3.（31次单选第11题）将教师表中所有职称为“教授”的教师工资增加300元的SQL语句是（）
（A）replaceall工资with工资+300where职称=“教授”
（B）update教师set工资=工资+300where职称=“教授”
（C）update教师set工资=工资+300where职称=教授
（D）update教师工资with工资+300where职称=“教授”
4.（31次读程序第4题）阅读下面程序
数据库“等级考试.DBC”中有“学生”和“成绩”两个数据表：
学生（考号C（6），姓名C
（2），性别C
（2），党团员L）
成绩（考号C（6），笔试成绩N（3），设计成绩N（3），总分N（5，1））
程序如下：
SELECTTOP3学生.考号，学生.姓名，学生.性别，成绩.总分；
FROM等级考试!
学生，成绩;
ORDERBY总分;
WHERE学生.考号=成绩.考号
（1）程序功能是
（A）查询学生信息，并按部分升序排序
（B）查询学生信息，并按部分降序排序
（C）查询总分前3名的学生信息，并按部分降序排序
（D）查询总分前3名的学生信息，并按部分升序排序
（2）去掉程序中的语句ORDERBY总分，程序运行结果（）
（A）不变
（B）改变
（C）不可运行
（D）查询结果不排序
（3）SELECT后的学生.考号，学生.姓名，学生.性别，成绩.总分所对应的关系操作是（）
（A）投影
（B）连接
（C）选择
（D）合并
5.（31次填空第6题）有数据表文件学生（学号C（8），姓名c（6），性别L）下面的SQL语句是在学生表中插入一条新记录，请完善：
INSERT学生（学号，姓名，性别）（“2008002345”，“李林”，.F.）
6.（31次填空第7题）向学生表中增加一个字段“入校成绩N（5，1）”的SQL语句如下，请完善：
TABLE学生入校成绩N（5，1）
7.（30次单选第8题）在SELECT-SQL中，要将查询结果保存在数据表中的选项是（）
A.intotable<
表文件名>
B.tofile<
C.toscreen
D.intocursor<
8.（30次读程序第4题）现有数据“部门表”和“商品表”文件，执行SQL查询命令后请选择正确的结果（）
第（33）——（35）题使用如下表的数据：
（1）SQL语句
SELECT部门号,MAX（单价*数量）FROM商品表GROUPBY部门号查询结果有几条记录（）
A.1
B.4
C.3
D.10
（2）SQL语句
SELECT产地，COUNT（*）提供的商品种类数；
FROM商品表；
WHERE单价>
200；
GROUPBY产地HAVINGCOUNT（*）>
2
查询结果的第一条记录的产地和提供商品种类数是（）
A.北京，1
B.上海，2
C.广东，4
D.广东，6
（3）SQL语句
SELECT部门表.部门号,部门名称,sum（单价*数量）；
FROM部门表，商品表；
WHERE部门表.部门号=商品表.部门号；
GROUPBY部门表.部门号
查询结果是（）
A.各部门商品数量合计
B.各部门商品金额合计
C.所有商品金额合计
D.各部门商品金额平均值
9.有以下两表
学生（学号，姓名，性别，入校成绩，出生年月）
成绩（学号，课程号，成绩）
一个学生可以选多门课程；
（1）按入校成绩降序查询，并将结果放入文本文件TEMP.TEXT中的SELECT-SQL语句如下，请完善：
SELECT*FROM学生
______________BY入校成绩DESC
________________________
（2）查询并输出每个学生的课程平均成绩的SELECT-SQL语句如下，请完善；
SELECT姓名，_________________AS平均成绩；
FROM学生，成绩；
WHERE学生.学号=__________________
____________________BY学生.学号
10.（29次单选第5题）在SQL查询命令中，要去掉查询结果中某字段的重得值，应该在SELECT后面该字段名前面使用（）
A.DISTINCT
B.WHERE
C.HAVING
D.WHILE
11.（29次填空第2题）在“教材订购“数据库中有3个表：
其中表名和结构分别如下：
教材（书号C（6），书名C（20），作者C（8），出版社C（20），价格N（3））
班级（班级编号C
（2），班名C（10））
教材订购（书号C（6），班级编号C
（2），数量N（3））
（1）在“教材证购“数据库中查询每个班级的平均订书量：
SELECT班名，____1_____AS平均订书量FROM班级，教材订购；
WHERE班级.班级编号=教材订购.班级编号；
GROUPBY________2_________
（2）在“教材证购“数据库中查询订购了5种以上教材的班级编号和订购的教材种类数量。
SELECT班级编号，COUNT（*）FROM教材订购；
GROUPBY_______3__________HAVING______4______
12.（28次单选第7题）为了在选课表中查询选修了“C140”和“C160”课程的学号，SELECT－SQL语句的WHERE子句的格式为（）
（A）WHERE课程号BETWEEN“C140”AND“C160”
（B）WHERE课程号=“C140”AND“C160”
（C）WHERE课程号IN（“C140”,“C160”）
（D）WHERE课程号LIKE（“C140”，“C160”）
13．（28次读程序第6题）阅读下面程序：
SELECTSB.名称AS设备名,SB.启用日期FROMSBWHEREYEAR（SB.启用日期）>
=1995
（1）程序完成的功能是（）
（A）查询SB.DBF中1995年启用的设备与日期
（B）查询SB.DBF中1995年以前启用的设备与日期
（C）查询SB.DBF中1995年以后启用的设备与日期
（D）以上命令有错，不能实现查询
（2）命令中的AS设备名，表示（）
（A）当前表文件的别名（B）保存设备名
（C）为查询结果命名（D）为查询结果的列名重新命名
（3）如果将SQL命令改写为：
SELECTSB.名称,SB.启用
升级会员 