SQL语言操作示例练习
设要建立学生选课数据库，库中包括学生、课程和选课3个表，其表结构为：
学生（学号，姓名，性别，年龄，所在系）； 课程（课程号，课程名，先行课）； 选课（学号，课程号，成绩）。
一、数据库创建操作
【练习之1-1】：使用SQL命令创建一个包含一个数据文件和一个事务日志文件的数据库。库文件名和数据文件逻辑名均为教学管理数据库，物理文件名为教学管理数据库.mdf，存储在D盘根目录中，该数据库主数据文件是教学管理数据库，初始大小8MB，最大尺寸80MB，以10%的速度增长。事务日志文件逻辑名为Book_log，物理文件名为教学管理数据库 _log.ldf，初始大小1MB，最大尺寸20MB，以20%的速度增长。操作示例如下：
CREATE DATABASE 教学管理数据库 ON
PRIMARY (NAME= 教学管理数据库, FILENAME='D:\\ 教学管理数据库.mdf', SIZE=8MB, MAXSIZE=80MB, FILEGROWTH=10%) LOG ON
(NAME=教学管理数据库_log,
FILENAME='D:\\ 教学管理数据库_log.ldf', SIZE=1MB, MAXSIZE=20MB, FILEGROWTH=20%)
【练习之1-2】修改数据库
alter database 教学管理数据库
add file
(name=教学管理数据库_data2, filename='d: \\ size=10mb,
教学管理数据库2.mdf',
maxsize=20mb, filegrowth=20%)
【练习之1-3】删除数据库
use master drop database \\ go
教学管理数据库
二、数据表的创建操作
create table
【练习之2-1】：使用SQL命令，在“教学管理数据库”下面创建三个数据表。
“学生”表结构
字段名及说明 学号 姓名 性别 年龄 班级 专业
字符型 字符型 字符型 整形 字符型 字符型
数据类型
宽度 7 8 2 10 10
主键 非空
说明
“课程”表结构
字段名及说明 课程号 课程名 学时数
数据类型 字符型 字符型 整形
宽度 10 20
说明 主键 非空
“选课”表结构
字段名及说明 学号 课程号 成绩
数据类型 字符型 字符型 整形
宽度 7 10
说明
主键，引用Student的外键 主键，引用Course的外键
“学生”表
use 教学管理数据库 Create table 学生 (
学号 char(7) not null, 姓名 char(8) not null, 性别 char(2), 年龄 int,
班级 char(10), 专业 char(10)
PRIMARY KEY(学号) )
“课程”表
use 教学管理数据库 Create table 课程 (
课程号 char (10) not null, 课程名 char(20) not null, 学时数 int,
PRIMARY KEY(课程号) )
“选课”表，
use 教学管理数据库 create table 选课 (
学号 char(7) not null, 课程号 char(10) not null,
成绩 int, PRIMARY KEY(学号,课程号)， CHECK(成绩>=0 AND 成绩<=100) )
【练习之2-1】给课程表增加“先修课程”字段。
use 教学管理数据库
alter table 课程add 先修课程char(20)
【练习之2-2】在“选课表”的“成绩”列中添加一个未经验证的 CHECK 约束（0<=成绩<=100）
use 教学管理数据库
ALTER TABLE 选课WITH NOCHECK
ADD CONSTRAINT 选课_check CHECK (成绩>=0 and 成绩<=100)
【练习之2-3】删除表：
Drop table <表名>
三、数据记录添加
【练习之3】：使用SQL语句添加数据。
（1） ＳＱＬ命令格式：
INSERT INTO [教学管理数据库].[dbo].[学生] ([学号] ,[姓名] ,[性别] ,[年龄] ,[班级] ,[专业])
VALUES
(<学号, char(7),> ,<姓名, nchar(10),> ,<性别, char(2),> ,<年龄, int,> ,<班级, nchar(10),> ,<专业, nchar(10),>)
（2） 操作示例
插入学生数据记录
use 教学管理数据库
insert into 学生(学号,姓名,性别,年龄,班级,专业) values('2310109','沈红兵','男',22,'计算机','软件工程')
insert into 学生(学号,姓名,性别,年龄,班级,专业) values('2320108','何雪娟','女',21,'经济','市场营销')
insert into 学生(学号,姓名,性别,年龄,班级,专业) values('2310111','狄晓雷','女',22,'计算机','计算机应用')
insert into 学生(学号,姓名,性别,年龄,班级,专业) values('2310112','刘军','男',20,'计算机','计算机应用')
insert into 学生(学号,姓名,性别,年龄,班级,专业) values('2310113','徐丽','女',20,'计算机','计算机应用')
插入课程数据记录 use 学生管理数据库
insert into 课程(课程号,课程名,学时数) values('1','数据结构',54) insert into 课程(课程号,课程名,学时数) values('2','操作系统',51) insert into 课程(课程号,课程名,学时数) values('3','软件工程',33) insert into 课程(课程号,课程名,学时数) values('4','接口与通讯',51)
插入选课数据记录
use 学生管理数据库
insert into 选课(学号,课程号,成绩) values('2310109','1',84) insert into 选课(学号,课程号,成绩) values('2310109','4',76) insert into 选课(学号,课程号,成绩) values('2320108','2',91) insert into 选课(学号,课程号,成绩) values('2310111','2',81) insert into 选课(学号,课程号,成绩) values('2310111','3',69) insert into 选课(学号,课程号,成绩) values('2310112','1',73) insert into 选课(学号,课程号,成绩) values('2310112','4',80) insert into 选课(学号,课程号,成绩) values('2310113','1',74)
insert into 选课(学号,课程号,成绩) values('2310113','3',77)
【练习之4】：在将“学生”表中的所有学生的年龄增加1岁。
打开查询分析器，选择“学生管理数据库”，输入如下SQL语句，并执行：
UPDATE 学生 set 年龄=年龄+1
四、数据查询操作
【练习之5】 查询学生的全部信息。 SELECT * FROM 学生
用‘ * ’表示学生表的全部列名，而不必逐一列出。
【练习之6】：查询全体学生的学号、姓名和年龄。 SELECT 学号, 姓名, 年龄 FROM 学生
【练习之7】 查询选修了课程的学生号。 SELECT DISTINCT 学号 FROM 选课
? 查询结果中的重复行被去掉——DISTINCT
? 上述查询均为不使用WHERE子句的无条件查询，也称作投影查询。
? 另外，利用投影查询可控制列名的顺序，并可通过指定别名改变查询结果的列标题的名字。 ?
【练习之8】 查询全体学生的姓名、学号和年龄。 SELECT 姓名 学生姓名, 学号, 年龄 FROM 学生 ? 其中，学生姓名为姓名的别名
? use 学生管理数据库
? select 姓名 学生姓名,学号,年龄from 学生
【练习之6】在“学生”表中查找计算机班的学生的学号、姓名、班级信息。
use 教学管理数据库
select 学号,姓名,班级 from 学生 where 班级='计算机'
【练习之7】将“选课”表中的“成绩”字段名改为“考试成绩”，将所有学
生成绩提高5%，并显示提高后的成绩；
Use 教学管理数据库
select 学号,课程号,考试成绩=成绩 ,成绩*1.05 =提高后成绩from 选课 或者：
use 教学管理数据库
select 学号,课程号,成绩 as 考试成绩,成绩*1.05 as 提高后成绩 from 选课
将上述操作生成的视图产生一个表
select 学号,课程号,成绩 as 考试成绩,成绩*1.05 as 提高后成绩 into 学生成绩表 from 选课
【练习之7】 查询学生表中性别为“男”，并且年龄大于等于22岁的所有学生记录 use 教学管理数据库 select * from 学生 where 性别=’男’ and 年龄>22
【练习之8】 检索成绩在90分（含）以上的学生学号、课程号和成绩。
Use 教学管理数据库
select 学号,课程号,成绩 from 选课 WHERE 成绩>=90
【练习之9】在“学生”表中检索姓名为“何”姓的学生信息。模糊查询
use 教学管理数据库
select * from 学生 where 姓名 LIKE '何%'
五、常用库函数及统计汇总查询
? SQL提供了许多库函数，增强了基本检索能力。 ? 常用的库函数，如表3.2所示
【练习之5-1】求学号为2310111学生的总分和平均分。
SELECT SUM(成绩) AS 总成绩, AVG(成绩) AS 平均成绩 FROM 选课 WHERE (学号 = '2310111')
注意：函数SUM和AVG只能对数值型字段进行计算。
【练习之9】多表联合查询，学生、课程、选课三表联合查询
按学生班级和学科查询学生的成绩；
use 教学管理数据库
select 学生.学号,学生.姓名,课程.课程名,选课.成绩 from 学生,课程,选课 where 学生.学号=选课.学号 and 选课.课程号=课程.课程号 and 课程.课程名='软件工程'
【练习之10】统计学生所有的总成绩和平均成绩；
use 教学管理数据库
select 学号，姓名,sum(选课.成绩) as 总成绩,avg(选课.成绩) as 平均成绩 from 学生,选课，课程 where 学生.学号=选课.学号 and 选课.课程号=课程.课程号 group by 学生.学号,学生.姓名
Sum（）求和函数；avg（）求平均值函数
数据库操作练习题
一、设要建立学生选课数据库，库中包括学生、课程和选课3个表，其表结构为：
学生（学号，姓名，性别，年龄，所在系）； 课程（课程号，课程名，先行课）； 选课（学号，课程号，成绩）。
用Transact—SQL完成下列操作。 （1） 建立学生选课库。
创建数据库：Create database 学生选课库
相关命令：
修改数据库：alter database <数据库名> 删除数据库：drop database <数据库>
（2） 分别要求学生表以学号为主键，课程表以课程号为主键建立学生表和课程
表。 创建学生表操作：
Use 学生选课库
Create table 学生 (
学号 char(8) not null， 姓名 char(10) not null, 性别 char(2)， 年龄 int， 所在系 char(20), Primary key(学号) )
创建课程表操作：
Use 学生选课库 Create table 课程 (
)
课程号 char(10) not null, 课程名 char(20) not null, 先行课 char(20),
primary key(课程号)
（3） 建立选课表，定义“成绩”的取值范围为0~100； Use 学生选课库 Create table 选课 ( )
（4） 将“选课”数据表中增加一个“备注”列，类型为text；
Use 学生选课库 学号 char(7) not null, 课程号 char(10) not null, 成绩 int,
Primary key(学号,课程号)， Check(成绩>=0 and 成绩<=100)
Alter table 选课 add 备注 text
将“选课”数据表中 “成绩”的类型改为浮点型； Use 学生选课库
Alter table 选课 alter column 成绩 float
（5） 查询各系及学生数，最后求出共有多少系和多少学生。
Use 学生选课库
Select count( 所在系 ) from 学生 where 所在系=’ 系名’
use 学生管理数据库
select count(*) as 计算机专业的学生人数 from 学生 where 班级='计算机' go
（6） 查询学生表中性别为“男”，并且年龄大于等于22岁的所有学生记录
Select * from 学生 where 性别=’男’ and 年龄>=22 （7） 查询学生表中姓名中包含“王”的所有数据记录；
Select * from 学生 where 姓名 like ‘%王%’
（8） 按学生的学号、姓名、所在系别、课程名、成绩等字段查询所有信息（学
生表、课程表、选课表三库联查）；
Select 学生.学号,学生.姓名,学生.所在系别,课程.课程名,选课.成绩 into 成绩统计from 学生,课程,选课 where 选课.学号=学生.学号 and 选课.课程号=课程.课程号
（9） 统计学生所有的总成绩和平均成绩
use 教学管理数据库
select 学生.学号,学生.姓名,sum(选课.成绩) as 总成绩,avg(选课.成绩) as 平均成绩from 学生,选课,课程where 学生.学号=选课.学号and 选课.课程号=课程.课程号 group by 学生.学号,学生.姓名
go
（10）给学生表中插入一条学生信息（0007，徐立，男， 23，计算机），并选课表中插入其成绩，即：“0007 0001 89”，“0007 0002 70”，“0007 0003 65”的考试成绩
Use 学生管理数据库
Insert into 学生(学号,姓名,性别,年龄,所在系别) values(‘0007’，’徐立’，’男’， 23，’计算机’)
Insert into 选课（学号,课程号,成绩） values(‘0007’ ‘0001’ 89) Insert into 选课（学号,课程号,成绩） values(‘0007’ ‘0002’ 70)
Insert into 选课（学号,课程号,成绩） values(‘0007’ ‘0003’ 65)
二、数据库设计题
1．现在要建立关于系、学生、班级、学会诸信息的一个关系数据库。语义为：一个系有若干专业，每个专业每年只招一个班，每个班有若干学生，一个系的学生住在同一个宿舍区，每个学生可参加若干学会，每个学会有若干学生。
描述学生的属性有：学号、姓名、出生日期、系名、班号、宿舍区； 描述班级的属性有：班号、专业名、系名、人数、入校年份； 描述系的属性有：系名、系号、系办地点、人数；
描述学会的属性有：学会名、成立年份、地点、人数、学生参加某回有一个入会年份。
1）请写出关系模式。
2）写出每个关系模式的最小函数依赖集，指出是否存在传递依赖。在函数依赖左部是多属性的情况下，讨论函数依赖是完全依赖，还是部分函数依赖。 3）指出各个关系模式的侯选关键字、外部关键字，以及有没有全关键字.
参考答案
（1）学生（学号，姓名，出生日期，班号）
班级（班级编码，专业名，系号，人数，入校年份）； 教学系（系名，系号，办公室地点，人数，宿舍区） 学会（学会名，成立年份，地点，人数） 参加（学号，学会名，入会年份）。
（2）F（班级）＝｛班级编码→专业名，班级→系号，班级→人数，班级→入校年份｝；
F（学生）＝｛学号→姓名，学号→出生日期，学号→班号｝
F（教学系)＝｛系号→系名，系号→办公室地点，系号→人数，系号→宿舍区｝ F（参加）＝｛学会名→成立年份，学会名→地点，学会名→人数｝； F（学会）＝｛（学号，学会名）→入会年份｝
（3）学生表中，码为学号。班级表中，码为班级编码。教学系表中，码为系号。学会表中，码为学会名。参加表中，码为（学号，学会名）；外码为学号，参照属性为学生（学号）；外码为学会名，参照属性为学会（学会名）。
Insert into 选课（学号,课程号,成绩） values(‘0007’ ‘0003’ 65)
二、数据库设计题
1．现在要建立关于系、学生、班级、学会诸信息的一个关系数据库。语义为：一个系有若干专业，每个专业每年只招一个班，每个班有若干学生，一个系的学生住在同一个宿舍区，每个学生可参加若干学会，每个学会有若干学生。
描述学生的属性有：学号、姓名、出生日期、系名、班号、宿舍区； 描述班级的属性有：班号、专业名、系名、人数、入校年份； 描述系的属性有：系名、系号、系办地点、人数；
描述学会的属性有：学会名、成立年份、地点、人数、学生参加某回有一个入会年份。
1）请写出关系模式。
2）写出每个关系模式的最小函数依赖集，指出是否存在传递依赖。在函数依赖左部是多属性的情况下，讨论函数依赖是完全依赖，还是部分函数依赖。 3）指出各个关系模式的侯选关键字、外部关键字，以及有没有全关键字.
参考答案
（1）学生（学号，姓名，出生日期，班号）
班级（班级编码，专业名，系号，人数，入校年份）； 教学系（系名，系号，办公室地点，人数，宿舍区） 学会（学会名，成立年份，地点，人数） 参加（学号，学会名，入会年份）。
（2）F（班级）＝｛班级编码→专业名，班级→系号，班级→人数，班级→入校年份｝；
F（学生）＝｛学号→姓名，学号→出生日期，学号→班号｝
F（教学系)＝｛系号→系名，系号→办公室地点，系号→人数，系号→宿舍区｝ F（参加）＝｛学会名→成立年份，学会名→地点，学会名→人数｝； F（学会）＝｛（学号，学会名）→入会年份｝
（3）学生表中，码为学号。班级表中，码为班级编码。教学系表中，码为系号。学会表中，码为学会名。参加表中，码为（学号，学会名）；外码为学号，参照属性为学生（学号）；外码为学会名，参照属性为学会（学会名）。
