SQL语言操作示例练习1范文.docx
《SQL语言操作示例练习1范文.docx》由会员分享，可在线阅读，更多相关《SQL语言操作示例练习1范文.docx（14页珍藏版）》请在冰点文库上搜索。
SQL语言操作示例练习1范文
SQL语言操作示例练习
设要建立学生选课数据库，库中包括学生、课程和选课3个表，其表结构为：
学生（学号，姓名，性别，年龄，所在系）；
课程（课程号，课程名，先行课）；
选课（学号，课程号，成绩）。
一、数据库创建操作
【练习之1-1】：
使用SQL命令创建一个包含一个数据文件和一个事务日志文件的数据库。
库文件名和数据文件逻辑名均为教学管理数据库，物理文件名为教学管理数据库.mdf，存储在D盘根目录中，该数据库主数据文件是教学管理数据库，初始大小8MB，最大尺寸80MB，以10%的速度增长。
事务日志文件逻辑名为Book_log，物理文件名为教学管理数据库_log.ldf，初始大小1MB，最大尺寸20MB，以20%的速度增长。
操作示例如下：
CREATEDATABASE教学管理数据库
ON
PRIMARY（NAME=教学管理数据库,
FILENAME='D:
\教学管理数据库.mdf',
SIZE=8MB,
MAXSIZE=80MB,
FILEGROWTH=10%）
LOGON
（NAME=教学管理数据库_log,
FILENAME='D:
\教学管理数据库_log.ldf',
SIZE=1MB,
MAXSIZE=20MB,
FILEGROWTH=20%）
【练习之1-2】修改数据库
alterdatabase教学管理数据库
addfile
（name=教学管理数据库_data2,
filename='d:
\教学管理数据库2.mdf',
size=10mb,
maxsize=20mb,
filegrowth=20%）
【练习之1-3】删除数据库
usemaster
dropdatabase\教学管理数据库
go
二、数据表的创建操作
createtable
【练习之2-1】：
使用SQL命令，在“教学管理数据库”下面创建三个数据表。
“学生”表结构
字段名及说明
数据类型
宽度
说明
学号
字符型
7
主键
姓名
字符型
8
非空
性别
字符型
2
年龄
整形
班级
字符型
10
专业
字符型
10
“课程”表结构
字段名及说明
数据类型
宽度
说明
课程号
字符型
10
主键
课程名
字符型
20
非空
学时数
整形
“选课”表结构
字段名及说明
数据类型
宽度
说明
学号
字符型
7
主键，引用Student的外键
课程号
字符型
10
主键，引用Course的外键
成绩
整形
“学生”表
use教学管理数据库
Createtable学生
（
学号char（7）notnull,
姓名char（8）notnull,
性别char
（2）,
年龄int,
班级char（10）,
专业char（10）
PRIMARYKEY（学号）
）
“课程”表
use教学管理数据库
Createtable课程
（
课程号char（10）notnull,
课程名char（20）notnull,
学时数int,
PRIMARYKEY（课程号）
）
“选课”表，
use教学管理数据库
createtable选课
（
学号char（7）notnull,
课程号char（10）notnull,
成绩int,PRIMARYKEY（学号,课程号），
CHECK（成绩>=0AND成绩<=100）
）
【练习之2-1】给课程表增加“先修课程”字段。
use教学管理数据库
altertable课程add先修课程char（20）
【练习之2-2】在“选课表”的“成绩”列中添加一个未经验证的CHECK约束（0<=成绩<=100）
use教学管理数据库
ALTERTABLE选课WITHNOCHECK
ADDCONSTRAINT选课_checkCHECK（成绩>=0and成绩<=100）
【练习之2-3】删除表：
Droptable<表名>
三、数据记录添加
【练习之3】：
使用SQL语句添加数据。
（1）ＳＱＬ命令格式：
INSERTINTO[教学管理数据库].[dbo].[学生]
（[学号]
[姓名]
[性别]
[年龄]
[班级]
[专业]）
VALUES
（<学号,char（7）,>
<姓名,nchar（10）,>
<性别,char
（2）,>
<年龄,int,>
<班级,nchar（10）,>
<专业,nchar（10）,>）
（2）操作示例
插入学生数据记录
use教学管理数据库
insertinto学生（学号,姓名,性别,年龄,班级,专业）values（'','沈红兵','男',22,'计算机','软件工程'）
insertinto学生（学号,姓名,性别,年龄,班级,专业）values（'','何雪娟','女',21,'经济','市场营销'）
insertinto学生（学号,姓名,性别,年龄,班级,专业）values（'','狄晓雷','女',22,'计算机','计算机应用'）
insertinto学生（学号,姓名,性别,年龄,班级,专业）values（'','刘军','男',20,'计算机','计算机应用'）
insertinto学生（学号,姓名,性别,年龄,班级,专业）values（'','徐丽','女',20,'计算机','计算机应用'）
插入课程数据记录
use学生管理数据库
insertinto课程（课程号,课程名,学时数）values（'1','数据结构',54）
insertinto课程（课程号,课程名,学时数）values（'2','操作系统',51）
insertinto课程（课程号,课程名,学时数）values（'3','软件工程',33）
insertinto课程（课程号,课程名,学时数）values（'4','接口与通讯',51）
插入选课数据记录
use学生管理数据库
insertinto选课（学号,课程号,成绩）values（'','1',84）
insertinto选课（学号,课程号,成绩）values（'','4',76）
insertinto选课（学号,课程号,成绩）values（'','2',91）
insertinto选课（学号,课程号,成绩）values（'','2',81）
insertinto选课（学号,课程号,成绩）values（'','3',69）
insertinto选课（学号,课程号,成绩）values（'','1',73）
insertinto选课（学号,课程号,成绩）values（'','4',80）
insertinto选课（学号,课程号,成绩）values（'','1',74）
insertinto选课（学号,课程号,成绩）values（'','3',77）
【练习之4】：
在将“学生”表中的所有学生的年龄增加1岁。
打开查询分析器，选择“学生管理数据库”，输入如下SQL语句，并执行：
UPDATE学生set年龄=年龄+1
四、数据查询操作
【练习之5】查询学生的全部信息。
SELECT*FROM学生
用‘*’表示学生表的全部列名，而不必逐一列出。
【练习之6】：
查询全体学生的学号、姓名和年龄。
SELECT学号,姓名,年龄FROM学生
【练习之7】查询选修了课程的学生号。
SELECTDISTINCT学号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.05as提高后成绩from选课
将上述操作生成的视图产生一个表
select学号,课程号,成绩as考试成绩,成绩*1.05as提高后成绩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】求学号为学生的总分和平均分。
SELECTSUM（成绩）AS总成绩,AVG（成绩）AS平均成绩
FROM选课WHERE（学号=''）
注意：
函数SUM和AVG只能对数值型字段进行计算。
【练习之9】多表联合查询，学生、课程、选课三表联合查询
按学生班级和学科查询学生的成绩；
use教学管理数据库
select学生.学号,学生.姓名,课程.课程名,选课.成绩from学生,课程,选课where学生.学号=选课.学号and选课.课程号=课程.课程号and课程.课程名='软件工程'
【练习之10】统计学生所有的总成绩和平均成绩；
use教学管理数据库
select学号，姓名,sum（选课.成绩）as总成绩,avg（选课.成绩）as平均成绩from学生,选课，课程where学生.学号=选课.学号and选课.课程号=课程.课程号groupby学生.学号,学生.姓名
Sum（）求和函数；avg（）求平均值函数
数据库操作练习题
一、设要建立学生选课数据库，库中包括学生、课程和选课3个表，其表结构为：
学生（学号，姓名，性别，年龄，所在系）；
课程（课程号，课程名，先行课）；
选课（学号，课程号，成绩）。
用Transact—SQL完成下列操作。
（1）建立学生选课库。
创建数据库：
Createdatabase学生选课库
相关命令：
修改数据库：
alterdatabase<数据库名>
删除数据库：
dropdatabase<数据库>
（2）分别要求学生表以学号为主键，课程表以课程号为主键建立学生表和课程表。
创建学生表操作：
Use学生选课库
Createtable学生
（
学号char（8）notnull，
姓名char（10）notnull,
性别char
（2），
年龄int，
所在系char（20）,
Primarykey（学号）
）
创建课程表操作：
Use学生选课库
Createtable课程
（
课程号char（10）notnull,
课程名char（20）notnull,
先行课char（20）,
primarykey（课程号）
）
（3）建立选课表，定义“成绩”的取值范围为0~100；
Use学生选课库
Createtable选课
（
学号char（7）notnull,
课程号char（10）notnull,
成绩int,
Primarykey（学号,课程号），
Check（成绩>=0and成绩<=100）
）
（4）将“选课”数据表中增加一个“备注”列，类型为text；
Use学生选课库
Altertable选课add备注text
将“选课”数据表中“成绩”的类型改为浮点型；
Use学生选课库
Altertable选课altercolumn成绩float
（5）查询各系及学生数，最后求出共有多少系和多少学生。
Use学生选课库
Selectcount（所在系）from学生where所在系=’系名’
use学生管理数据库
selectcount（*）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选课.课程号=课程.课程号groupby学生.学号,学生.姓名
go
（10）给学生表中插入一条学生信息（0007，徐立，男，23，计算机），并选课表中插入其成绩，即：
“0007000189”，“0007000270”，“0007000365”的考试成绩
Use 学生管理数据库
Insertinto学生（学号,姓名,性别,年龄,所在系别）values（‘0007’，’徐立’，’男’，23，’计算机’）
Insertinto选课（学号,课程号,成绩）values（‘0007’‘0001’89）
Insertinto选课（学号,课程号,成绩）values（‘0007’‘0002’70）
Insertinto选课（学号,课程号,成绩）values（‘0007’‘0003’65）
二、数据库设计题
1．现在要建立关于系、学生、班级、学会诸信息的一个关系数据库。
语义为：
一个系有若干专业，每个专业每年只招一个班，每个班有若干学生，一个系的学生住在同一个宿舍区，每个学生可参加若干学会，每个学会有若干学生。
描述学生的属性有：
学号、姓名、出生日期、系名、班号、宿舍区；
描述班级的属性有：
班号、专业名、系名、人数、入校年份；
描述系的属性有：
系名、系号、系办地点、人数；
描述学会的属性有：
学会名、成立年份、地点、人数、学生参加某回有一个入会年份。
1）请写出关系模式。
2）写出每个关系模式的最小函数依赖集，指出是否存在传递依赖。
在函数依赖左部是多属性的情况下，讨论函数依赖是完全依赖，还是部分函数依赖。
3）指出各个关系模式的侯选关键字、外部关键字，以及有没有全关键字.
参考答案
（1）学生（学号，姓名，出生日期，班号）
班级（班级编码，专业名，系号，人数，入校年份）；
教学系（系名，系号，办公室地点，人数，宿舍区）
学会（学会名，成立年份，地点，人数）
参加（学号，学会名，入会年份）。
（2）F（班级）＝｛班级编码→专业名，班级→系号，班级→人数，班级→入校年份｝；
F（学生）＝｛学号→姓名，学号→出生日期，学号→班号｝
F（教学系）＝｛系号→系名，系号→办公室地点，系号→人数，系号→宿舍区｝
F（参加）＝｛学会名→成立年份，学会名→地点，学会名→人数｝；
F（学会）＝｛（学号，学会名）→入会年份｝
（3）学生表中，码为学号。
班级表中，码为班级编码。
教学系表中，码为系号。
学会表中，码为学会名。
参加表中，码为（学号，学会名）；外码为学号，参照属性为学生（学号）；外码为学会名，参照属性为学会（学会名）。
升级会员 