第6章 SQL语言
6.1 SQL概述
SQL是结构化查询语言,由四部分构成,分别为数据定义,数据查询,数据操纵和数据控制。
6.2 SQL的数据定义功能
6.2.1 创建数据表
?命令:CREATE TABLE<表名>[FREE]
(<字段名1><字段类型>[<字段宽度>[,小数位数]]
[NULL/NOT NULL]
[CHECK <表达式>][ERROR<提示信息>] [DEFAULT<表达式>]
[PRIMARY KEY/UNIQUE]
[,<字段名2>…]
?功能:创建数据表结构
create table 学生;
(学号 c(8) primary key not null,;
姓名 c(8) not null,;
性别 c(2) default '男' check 性别='男' or 性别='女' error '性别必须是男或女',; 出生年月 d check (出生年月<={^1990-01-01}) and (出生年月>={^1960-01-01}),;
入校总分 n(3),;
三好生 l,;
特长 m,;
照片 g)
?注意:create table 是一条命令,分号(;)是续行号,字段之间用逗号(,)隔开。
6.2.2 修改数据表
?命令:ALTER TABLE<表名>
[ADD/ALTER/drop/rename [COLUMN]
<字段名><字段类型>[<字段宽度>[,小数位]
[NULL/NOT NULL]
[CHECK<表达式>][ERROR<提示信息>]
[DEFAULT<表达式>]
[PRIMARY KEY/UNIQUE]
?功能:修改基本表结构
?例:在“学生”表中增加“电话”字段,并设为候选索引。
ALTER TABLE 学生;
ADD COLUMN 电话 C(8)
6.2.3 删除数据表
?当某个数据表无用时,可将其删除,删除后,该表中的数据和在此表上所建的索引都被删除。
?命令:DROP TABLE<表名>
?功能:删除数据表。
?例:删除表“学生”
DROP TABLE 学生
6.2 SQL的数据查询功能
?SQL的查询功能实际上是数据操纵语言(Data Manipulation Language 简称DML)的范畴。?数据查询是对数据库中的数据按指定内容和顺序进行检索输出
?数据查询是数据库的核心操作。
?SQL语言的数据查询只有一条SELECT语句,但用途最广泛,具有灵活的使用方法和丰富的功能。
6.2.1 SELCET命令的格式
?命令:SELECT[ALL/DISTINCT]
[TOP<表达式>[PERCENT]][<别名>.]<列表达式>
[AS<栏名>][,[<别名>.]<列表达式>[AS<栏名>]…]
FROM[<数据库名!>]<表名>[,[<数据库名!>]]<表名>…]
[INNER/LEFT/RIGHT/FULL JOIN[<数据库名!>]<表名>
[ON<连接条件>…]]
[[INTO<新表名>]/TO FILE<文件名>/TO PRINTER/TO SCREEN]]
[WHERE<连接条件>[AND<连接条件>…]
[AND/OR<筛选条件>[AND/OR<筛选条件>…]]]
[GROUP BY<列名>[,<列名>..]]
[ORDER BY<列名>[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:查询入校总分最低的五名学生基本情况,按入校总分由低到高进行排序,同时指定表中的字段在查询结果中的列标题。
SELECT TOP 5 学号 AS 学生编号,
姓名 AS 学生姓名,;
性别 AS 学生性别,;
入校总分 AS 高考分数;
FROM 学生 ORDER BY 入校总分
注:top 必须和order by 一起使用
?例6-9:查询选课学生的学号。
select distinct学号 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 分组查询
?GROPU BY子句可以将查询结果按属性列或属性列组合在行的方向上进行分组,每组在属性列或属性列组合上具有相同的值。
?例6-25:查询各位教师的教师号及其任课门数。
select 教师号,count(*) as 任课门数;
from 授课;
group by 教师号
注意:GROUP BY子句按教师号的值分组,所有具有相同教师号的元组为一组,对每一组使用函数COUNT进行计算,统计出各位教师任课的门数。
6.3.6 查询的排序
?对查询结果使用ORDER BY子句对一个或多个属性进行排序,默认为升序。?ORDER BY子句必须放在其他子句之后。
?例6-28:查询选修了课程“C140”的学生学号和成绩,并按成绩降序排列。
select 学号,成绩;
from 选课;
where 课程号='c140';
order by 成绩 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:查询所有比“陈静”工资高的教师姓名、职称、工资和“陈静”的工资。
select x.姓名,x.工资,y.工资 as 陈静工资;
from 教师 as x,教师 as y;
where x.工资>y.工资 and y.姓名='陈静'
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 姓名;
from 教师;
where 教师号=any;
(select 教师号;
from 授课;
where 课程号=‘C140')
6.3.9 查询结果输出
使用INTO子句,可以将查询结果输出到一个新建的数据表或一个临时表。
INTO TABLE<表名>:输出到数据表
INTO CURSOR:输出到临时表
例6-43:将每个同学选修的课程名及成绩输出到数据表(表名为学生成绩)
select 姓名,课程名,成绩;
from 学生信息,选课,课程;
where 学生信息.学号=选课.学号 and 选课.课程号=课程.课程号;
into table 学生成绩
还有一些输出选项:
TO FILE<文件名>:将查询结果输出到文件
TO PRINT:将查询结果输出到打印机
TO SCREEN:将查询结果输出到VF主窗口
6.4 SQL的数据操纵功能
6.4.1 插入数据
1. 插入数据记录
?命令:INSERT INTO <表名>[(<字段名1>[,<字段名2>…])]
VALUES (<表达式1>[,<表达式2>…])
?功能:将新记录插入到指定的表中,分别用表达式1、表达式2等为字段名1、字段名2等赋值。
?说明:<表名是指要插入新记录的表;<字段名>是可选项,指定添加数据的列;VALUES
子句指定待添加数据的具体值。
例6-44:在学生表中插入一条学生记录
insert into 学生 (学号,姓名,性别,出生年月,入校总分,三好生);
values(‘s0201111’,‘徐畅’,‘女’,{^1984-06-25},588,.T.)
2、插入多行记录
?插入多行记录用于表间的拷贝,将一个表中的数据抽取数行插入另一表中,可以通过子查询来实现。
?命令:INSERT INTO <表名>[(<字段名1>[,<字段名2>…])]
?功能:插入数据
1、先建立新表学生平均成绩,用来存放学生姓名和学生选修课的平均成绩。
CREATE TABLE 学生平均成绩(姓名CHAR(8),平均成绩INT(3))
2、利用子查询求出各学生的平均成绩,把结果存放在新表学生平均成绩中。
INSERT INTO 学生平均成绩;
SELECT 姓名,AVG(成绩);
FROM 学生,选课;
WHERE 学生.学号=选课.学号;
GROUP BY 学号
6.4.2 修改数据记录
?SQL语言可以使用UPDATE语句对表中的一行或多行记录的某些列值进行修改。
?命令:UPDATE <表名>
SET <字段名>=<表达式>[,<字段名>=<表达式>]…[WHERE<条件>]
?功能:对表中一行或多行记录的某些列值进行修改
?说明:<表名>指要修改的表,SET子句给出要修改的列及改后的值,WHERE子句指定待
修改的记录应当满足的条件,WHERE子句省略时,则修改表中的所有记录。
1、修改一行记录
2、修改多行记录
3、用子查询修改记录
6.4.3 删除数据记录
?命令:DELETE FROM<表名>[WHERE<条件>]
1、删除一行记录
例6-49:删除刘伟教师的记录
DELETE FROM 学生WHERE 姓名=‘刘伟’
2、删除多行记录
例6-51:删除所有学生的成绩
DELETE FROM 选课
3、利用子查询选择要删除的行
例6-51:删除陈静教师授课的记录
DELETE FROM 授课;
WHERE 教师号=;
(SELECT 教师号;
FROM 教师;
WHERE 姓名=‘陈静’)
第七章小结
1.SQL语言的四大功能包括数据定义功能,数据查询功能,数据更新功能,数据控制功能.以
及各种语句的使用.
2.查询语句的多种形式的使用
历届等级考试题选讲
11、(33次单选第11题)从学生选课表XK中(其中有学号、课程、成绩3个字段)查
询选修了6门以上课程的学生学号,实现该功能的SELECT语句是(26)
A. SELECT 学号FROM XK GROUP BY 学号HA VING
B. SELECT 学号FROM XK GROUP BY 学号HA VING count(*)>5
C. SELECT 学号FROM XK GROUP BY 学号HA VING sum(*)>5
D. SELECT 学号FROM XK HA VING课程>5 GROUP BY 学号
2、(33次读程序第2题)有如下的“学生”表
(1)执行“SELECT班级号,COUNT(*) FROM 学生GROUP BY 班级号ORDER BY 2
DESC”后,查询结果中共有()条记录
A 7
B 2 C. 3 D. 4
(2)第一条记录是()
A. 11 3
B. 22 1
C. 33 2
D. 44 1
(3)执行“SELECT MIN(年龄) FROM 学生INTO ARRAY TMP”后,下列说法正确的是()
A. TMP中共有2个元素
B. TMP(1)的值为18
C. TMP(0)的值为18
D. 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 教师ADD UNIQUE 教师号TAG JSH
(2)将所有教师的工资上调20%
_________________教师SET 工资=工资*1.2
(3)查询所授课程的课时>=80的教师的教师号、姓名和课程名
SELECT 教师.教师号,课程名FROM 教师INNER JOIN 授课INNER JOIN 课程;
ON__________ ON教师.教师号=授课.教师号;
______________课时>=80
1. (32次单选第11题)在SQL的查询语句中,(26)短语用于实现关系的投影操作
A. WHERE
B.SELECT
C. FROM
D. GROUP BY
2.(32次填空第4题)利用结构化查询语言SQL
(1)在给定的数据库表RS中有数值字段“工资”,将工资在原来基础上增加20%.
_____________RS_______________工资=工资*1.2
(2)在教工数据表JG中,查询职称为“助教”或“讲师”人员的姓名,工资和部门,并按工资降序排序。
SELECT 姓名,工资,部门,FORM JG WHERE职称_______________________;
________________BY 工资________________
3. (31次单选第11题)将教师表中所有职称为“教授”的教师工资增加300元的SQL语句是()
(A)replace all 工资with 工资+300 where 职称=“教授”
(B)update 教师set工资=工资+300 where 职称=“教授”
(C)update 教师set工资=工资+300 where 职称=教授
(D)update 教师工资with 工资+300 where 职称=“教授”
4. (31次读程序第4题)阅读下面程序
数据库“等级考试.DBC”中有“学生”和“成绩”两个数据表:
学生(考号C(6),姓名C(2),性别C(2),党团员L)
成绩(考号C(6) ,笔试成绩N(3),设计成绩N(3),总分N(5,1))
程序如下:
SELECT TOP 3 学生.考号,学生.姓名,学生.性别,成绩.总分;
FROM 等级考试!学生,成绩;
ORDER BY 总分;
WHERE 学生.考号=成绩.考号
(1)程序功能是
(A)查询学生信息,并按部分升序排序(B)查询学生信息,并按部分降序排序
(C)查询总分前3名的学生信息,并按部分降序排序(D)查询总分前3名的学生信息,并按部分升序排序
(2)去掉程序中的语句ORDER BY 总分,程序运行结果()
(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.into table <表文件名>
B.to file<文件名>
C.to screen
D.into cursor<表名>
8.(30次读程序第4题)现有数据“部门表”和“商品表”文件,执行SQL查询命令后请选择正确的结果()
第(33)——(35)题使用如下表的数据:
(1)SQL语句
SELECT 部门号,MAX(单价*数量) FROM 商品表GROUP BY 部门号查询结果有几条记录()
A.1
B.4
C. 3
D.10
(2)SQL语句
SELECT 产地,COUNT(*) 提供的商品种类数;
FROM 商品表;
WHERE 单价>200;
GROUP BY 产地HA VING COUNT(*)>2
查询结果的第一条记录的产地和提供商品种类数是()
A.北京,1
B.上海,2
C. 广东,4
D.广东,6
(3)SQL语句
SELECT 部门表.部门号,部门名称,sum(单价*数量);
FROM 部门表,商品表;
WHERE 部门表.部门号=商品表.部门号;
GROUP BY 部门表.部门号
查询结果是()
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 班级.班级编号=教材订购.班级编号;
GROUP BY________2_________
(2)在“教材证购“数据库中查询订购了5种以上教材的班级编号和订购的教材种类数量。
SELECT 班级编号,COUNT(*) FROM 教材订购;
GROUP BY _______3__________ HA VING______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题)阅读下面程序:
SELECT SB.名称AS 设备名,SB.启用日期FROM SB WHERE YEAR(SB.启用日期)>=1995 (1) 程序完成的功能是()
(A)查询SB.DBF中1995年启用的设备与日期
(B)查询SB.DBF中1995年以前启用的设备与日期
(C)查询SB.DBF中1995年以后启用的设备与日期
(D)以上命令有错,不能实现查询
(2)命令中的AS设备名,表示()
(A)当前表文件的别名(B)保存设备名
(C)为查询结果命名(D)为查询结果的列名重新命名
(3)如果将SQL命令改写为:SELECT SB.名称,SB.启用日期 FROM SB WHERE SB.启用日期=1995,程序完成的功能是()
(A)查询SB.DBF中1995年启用的设备名称与日期
(B)查询SB.DBF中1995年以前启用的设备名称与日期
(C)查询SB.DBF中1995年以后启用的设备名称与日期
(D)以上命令有错,不能实现查询
14(28次填空第3题)有一人才管理数据库中有3个表:其表名和结构分别如下:
基本情况(编号C(6),姓名C(8),性别C(8),出生年月D, 工资N(5))
专业(编号C(6),专业名称C(10),职称C(10))
成果(编号C(6),成果类别C(8),成果名称C(20))
(1)列出年龄在40岁以下的教授的姓名:
SELECT姓名FROM 基本情况,________;
WHERE基本情况.编号=专业.编号;
AND_____________<=40 AND职称=“教授”
(2)查询成果在3项以上的人员姓名和成果数,并按成果数降序排列:
SELECT 姓名,COUNT(*) AS成果数FROM 基本情况,成果WHERE基本情况.编号=成果.编号;
GROUP BY 成果.编号HA VING___________________;
ORDER BY ___________________.