专升本数据库练习题及答案.doc

一．关系代数运算
1.选择 σ

（1）查询年龄小于20岁的学生
σsage<20(student)

（2）查询学号为201215122的学生
σsno=201215122(student)
2.投影
（1）查询学生姓名，和所在系
same,sdept(student)
（2）查询选修了2号课程的学生学号
sno（σcno=‘2’2(student)）
SQL语句
学生-课程数据库
学生表：学生（学号，姓名，性别，年龄，所在系）
课程表：课程（课程号，课程名，先行课，学分）
学生选课表：选课（学号，课程号，成绩）
二．创建数据库SQL
1. 创建数据库
Create database 数据库名
On
( name =逻辑文件名，
Filename=’物理文件名’，
Size=初始文件大小，
Maxsize=最大文件大小，
Filegrowth=文件自动增量 )
Log on
( name =逻辑文件名，
Filename=‘物理文件名’，
Size=初始文件大小，
Maxsize=最大文件大小，
Filegrowth=文件自动增量 )

例 创建‘教务管理数据库’
Create database 教务管理数据库
On
( name =教务管理_dat，
Filename=‘E:\SQL\教务管理_dat.mdf’，
Size=2，
Filegrowth=1 )
Log on
( name =教务管理.log，
Filename=‘E:\SQL\教务管理_log.ldf’，
Size=5M，
Filegrowth=10% )

1. 查看数据库属性
1) 使用sp_helpdb查看数据库信息 ：
sp_helpdb [数据库名]

（2）使用sp_databases 查看可使用的数据库信息：
sp_databases（显示所有可以使用的数据库名称和大小）

（3）使用sp_helpfile查看数据库文件信息
sp_helpfile [文件名]

1. 设置数据库选项
Alter database 数据库名
Set 选项

例 设置 教务管理数据库自动收缩
Alter database 教务管理数据库
Set auto_shrink on （自动收缩）

1. 修改数据库大小
Alter database 数据库名
Add file 增加数据文件
Add log file 增加日志文件
Remove file 删除文件
Modify file 修改文件（ ）
例 1.修改教务管理数据库，增加数据文件jwgl_dat1
Alter database 教务管理数据库
Add file （
Name=jwgl_dat1,
Filename=‘E:\SQL\jwgl_dat1.ndf’，
Size=2MB，
Filegrowth=1MB )
2. 修改教务管理数据库
Alter database 教务管理数据库
Modify file （
Name=jwgl_dat1,
Size=5MB ）

2. 删除教务管理数据库中次要数据文件jwgl_dat1
Alter database 教务管理数据库
Remove file jwgl_dat1

1. 数据库更名
1) 更改数据库名
Alter database 数据库名
Modify name = 新数据库名

例 将数据库test更名为mytest
Alter database test
Modify name = mytest

1) 更改数据库的逻辑文件名
Alter database 数据库名
Modify file（
Name=逻辑文件名，
Newname=新逻辑文件名 ）

例 将数据库逻辑文件test更名为mytest
Alter database mytest
Modify file（
Name = test，
Newname = mytest ）
Go
Alter database mytest
Modify file（
Name = test_log，
Newname = mytest_log ）

1) 更改数据库的物理文件名
Alter database 数据库名
Modify file（
Name=逻辑文件名，
Filename =‘新路径\操作系统文件名’ ）

例 将数据库物理文件test更名为mytest
Alter database mytest
Modify file（
Name=mytest，
Filename =‘E:\sql\mytest.mdf’ ）
GO
Alter database mytest
Modify file（
Name=mytest_log，
Filename =‘E:\sql\mytest_log.ldf’ ）

1. 删除数据库
Drop database 数据库名

例 删除数据库名为test和数据库sales
Drop database test，sales

1. 数据库分离
Sp_detach_db 数据库名

1. 数据库附加
Create database 数据库名
On （
Filename=‘主文件的物理文件名’
） For attach

三．基本表操作
1. 创建表
Create table 表名
（
）
例 创建一个学生表
Create table 学生表
（学号 char（6） primary key，
姓名 char（20） unique，
性别 char（2），
年龄 smallint，
所在系 char（20）
）

1. 修改表
Alter table 表名
Add 列名 数据类型（）//增加列
（alter column 列名 数据类型（）//对原有的基础上修改列）
（drop column 列名 //对原有的基础上删除列，可一次删除多列）

例1. 添加一新列电子邮箱 char型 长度20
Alter table 学生表
Add 电子邮箱 char(20)

例2. 修改学生表列 电子邮箱数据类型长度为22
Alter table 学生表
alter column 电子邮箱 varchar(22)

例3.删除学生表的电子邮箱列
Alter table 学生表
drop column 电子邮箱

3.删除表
Drop table 表名

例 删除学生表
Drop table 学生表

四．数据更新
1. 插入数据
Insert
Into 表名 （）
Values 值（）

例1 插入一行数据到学生表的学生
Insert
Into 学生表（学号，姓名，性别，年龄，所在系）
Values （‘201215126’，‘张成民’，‘男’，18，‘计算机系’）

1. 修改数据
Update 表名
Set 列名=表达式
Where 条件

1) 修改某元祖的值

例 将学生201215121的年龄改为22
Update 学生表
Set 年龄=22
Where 学号=‘201215121‘

1) 修改多个元祖的值

例 将所有学生年龄增加一岁
Update 学生表
Set 年龄=年龄+1

1) 带子查询的修改语句

例 将计算机科学系全体学生成绩置零
Update 选课表
Set 成绩=0
Where 学号 in
（select 学号
From 学生表
Where 所在系=‘计算机科学系’）

1) 修改表名
Sp_rename 旧对象名，新对象名
例 课程表更名为课程信息表
Sp_rename 课程表，课程信息表

3. 删除数据
Delete
from 表名
Where 条件

例 将课程信息表中c程序设计课程信息删除
Delete
From 课程信息表
Where 课程名=‘c程序设计’

五．使用约束
1. 非空约束（NOT NULL）

例 创建一个学生表 使姓名，性别 为非空约束 学号为主键
Create table 学生表
（学号 char（6） primary key，
姓名 char（20） NOT NULL，
性别 char（2）NOT NULL，
年龄 smallint，
所在系 char（20）
）
1. 唯一约束（UNIQUE）

例 创建一个学生表 使姓名，性别 为唯一约束 学号为主键
Create table 学生表
（学号 char（6） primary key，
姓名 char（20） UNIQUE，
性别 char（2）UNIQUE，
年龄 smallint，
所在系 char（20）
）
1. Check约束（检查列值是否满足一个条件表达式）

1. 完整性约束命令子句
Constraint 完整性约束条件名（包括非空 主键 唯一 外键 check约束等） 约束条件

例 1创建一个学生表 使性别 只允许为‘男’或‘女’,年龄在20到30之间
Create table 学生表
（学号 char（6） primary key，
姓名 char（20） UNIQUE，
性别 char（2） CHECK（（性别=‘男’）or （性别=‘女’）），
年龄 smallint CHECK（年龄between 20 and 30），
所在系 char（20）
）
2 利用T-SQL命令设置课程表中的考核方式只能取值‘考试’或‘考查’，学分的值为1-6之间
Alter table 课程表 with check
Add constraint CK_课程表_1 check（考核方式 in （‘考查’，‘考试’））
Alter table 课程表 with nocheck
Add constraint CK_课程表_2 check（学分 between 1 and 6）

1. 主键约束（PRIMARY KEY）

例 创建一个学生表 使姓名，性别 为唯一约束 学号为主键
Create table 学生表
（学号 char（6） primary key，（在列定义后设置主键）
姓名 char（20） UNIQUE，
性别 char（2）UNIQUE，
年龄 smallint，
所在系 char（20）
）

1. 外键约束（FOREIGN KEY）
Constraint 约束名 FOREIGN KEY（列名）
Reference 被引用表（列名）
[on delete cascade（级联删除）|on update cascade（级联更新）]

例 创建选课表 设置选课表的课程号为外键 引用课程表 课程号
Create table 选课表
（ 学号 char（6） primary key，
课程号 char（11） Reference 课程表（课程号）on update cascade，
成绩 numeric（5,1）
）

7.默认约束（DEFAULT）
Constraint 约束名 DEFAULT 常量表达式 可在创建表中 数据类型后直接加

例 修改课程表 设置考核方式默认值为‘考试’
Alter table 课程表
Add constraint df_课程表_考核 DEFAULT‘考试’）for 考核方式

六．数据查询
1. 单表查询
1) 选择列查询

例 查询全体学生姓名 学号
Select 姓名，学号
From 学生表

1) 查询全部列

例 查询全体学生姓名 学号
Select *
From 学生表

1) 查询经过计算的值

例 查询全体学生姓名 及出生年份
Select 姓名，2017-年龄
From 学生表

1) 消除取值重复的行 （distinct）

例 查询选修了课程的学生学号 结果取消重复行
Select distinct 学号
From 学生表

1) 查询满足条件的元祖 （where子句）
· 比较大小 （> = < 等）

例 查询选修了课程为‘计算机’的学生学号 结果取消重复行
Select distinct 学号
From 学生表
Where 课程=‘计算机’

· 确定查找范围（between and）

例 查询成绩为80-90的学生学号 结果取消重复行
Select distinct 学号
From 选课表
Where 成绩between 80 and 90

· 确定集合（in）

例 查询成绩为80，90，100的学生学号 结果取消重复行
Select distinct 学号
From 选课表
Where 成绩in（80,90,100）

· 字符匹配（like ）
%：任意长度的字符串 _（下划线）：任意单个字符

例 1查询所有姓刘的学生姓名 学号 结果取消重复行
Select distinct 姓名，学号
From 学生表
Where 姓名 like‘刘%’

2查询名字中第二个字是阳 学生姓名 学号 结果取消重复行
Select distinct 姓名，学号
From 学生表
Where 姓名 like‘_阳%’

· 空值查询（is null/not null）

例 查询成绩为空的中第二个字是阳 学生姓名 学号 结果取消重复行
Select distinct 姓名，学号
From 学生表
Where 姓名 like‘_阳%’

· 多重条件查询（and or in）

例 查询成绩为80以上的女同学的姓名 学号 结果取消重复行
Select distinct 姓名，学号
From 学生表，选课表
Where 学生表.学号=选课表.学号
And 成绩>=80 AND 性别=‘女’

6) 设置字段别名（as）
表达式 as 别名 或 字段别名 = 表达式

例 查询学生姓名 性别 年龄
Select 姓名as学生姓名,性别,datediff（year，出生日期，getdate（））as年龄
From 学生表
或
Select 学生姓名=姓名,性别,年龄=datediff（year，出生日期，getdate（））
From 学生表

2. Order by子句（对查询结果默认升序（asc）降序（desc））

例 查询选修了3号课程的学生学号 成绩 结果按分数降序排列
Select 成绩，学号
From 选课表
Where 课程号=‘3’
Order by成绩 desc

2. 聚集函数（count 统计个数，sum 求和，avg 求平均数，max 最大数，min 最小数）
只能用于select，group by，having 子句中 不能用于where条件句中

例 查询选修课程号为1的学生最高成绩
Select max（成绩）
From 选课表
Where 课程号=‘1’

2. Group by ...... having子句（功能为分组）
例 1.求各个课程号及相应的选课人数
Select 课程号，count（学号）
From 选课表
Group by 课程号

2.查询选修了三门以上课程的学生学号
Select 学号
From 选课表
Group by 学号 having count（*）> 3（having子句作用范围为Group by后）

3.查询平均成绩大于等于90分的学生学号 和平均成绩
Select 学号avg（成绩）
From 选课表
Group by 学号
having avg（成绩）>=90

七．多表查询
1. 表连接
用where子句连接两个表
Where 表一.列名(比较运算符>,=,<)表2.列名

例 查询每个学生及选修课程的情况（用到学生表，选课表）
Select 学生表.*，选课表.*（如查询的属性在两个表不唯一则不用加表前缀）
From 学生表，选课表
where 学生表.学号=选课表.学号

1. 多表连接

例 查询每个学生的学号，姓名，选修的课程名及成绩（用到学生表，课程表选课表）
Select 学生表.学号，姓名，课程名，成绩（查询的属性在两个表不唯一则不用加表前缀）
From 学生表，课程表，选课表
where 学生表.学号=选课表.学号and 课程表.课程号=选课表.课程号
1. 带in的子查询（嵌套查询）

例 查询与‘刘晨’在同一个系学习的学生的学号，姓名
Select 学号，姓名，系名
From 学生表
Where系名 in
（ Select 系名
From 学生表
Where姓名=‘刘晨’）

1. 带比较运算符的子查询（>,<,=,>=,<=,!=）

例 查询与‘刘晨’在同一个系学习的学生的学号，姓名
Select 学号，姓名，系名
From 学生表
Where系名 in
（ Select 系名
From 学生表
Where姓名=‘刘晨’）

八．建立索引
1.建立索引
Create [unique（唯一）][clustered ( 聚集 ) ][nonclustered ( 非聚集 )]
Index 索引名
On （表/视图）（列名[asc（升序）][desc（降序）]）
With [pad_index][fillfactor=填充因子][drop_existing]

例 在学生表上创建一个名为XS XM的非唯一性非聚簇索引 索引关键字为姓名 升序 填充因子为50%

Create nonclustered
Index XS XM
On 学生表（姓名 asc）
With fillfactor=50

2. 删除索引
Drop index 表名.索引名

九．视图
1. 定义视图
1) 建立视图
Create view 视图名 列名
As
Select
（在视图后加上with check option子句 以后对该视图进行增删改时则自动添加）

例 建立信息系学生的视图
Create view 信息系学生
As
Select 学号，姓名，年龄
From 学生表
Where 系别=‘信息系’

1) 删除视图
Drop view 视图名（如选择级联删除 视图名后加cascade）

例 删除视图BT_S和视图IS_S1
Drop view BT_S
Drop view IS_S1 （拒绝执行 因为与另一个视图有连接所以 后面加cascade）

2. 查询视图
！同表查询 唯一区别在于from中添加的为视图名！

2. 更新视图（指对视图进行增删改，由于对视图修改最后都归结于对表的修改所以修改视图数据=修改表数据）

函数

1、 标量值
create function 函数名
(@变量 类型)
returns 返回值类型
begin
return
end

例：给专业名求男女比例
CREATE FUNCTION 某专业男女比例
(
@专业名 varchar(20)
)
RETURNS numeric(6,2)
AS
BEGIN
declare @man int
declare @woman int
select @man=count(*)
from 学生表,专业表,班级表
where 学生表.学号=班级表.班号 and 班级表.专业=专业表.专业号 and 性别='男'and 专业名=@专业名
select @woman=count(*)
from 学生表,专业表,班级表
where 学生表.学号=班级表.班号 and 班级表.专业=专业表.专业号 and 性别='女'and 专业名=@专业名
return cast(@man as float)/cast(@woman as float)

END

执行
select dbo.某专业男女比例('数据库')

2、表值函数 给班名和课程名 求某班某课成绩单
CREATE FUNCTION 某班某课成绩单
(
@班名 char(20),@课程名 varchar(20)
)
RETURNS TABLE
AS
RETURN
(
select 班名,学生表.学号,姓名,课程名,成绩
from 学生表,课程表,班级表,选课表
where 课程表.课程号=选课表.课程号 and 学生表.学号=选课表.学号 and 学生表.班号=班级表.班号 and 班名=@班名 and 课程名=@课程名
)
GO
执行
select * from dbo.某班某课成绩单('嵌入式班','java程序设计')

3、
CREATE FUNCTION 某班平均函数
(
@班号 char(5)
)
RETURNS TABLE
AS
RETURN

(
SELECt 课程号,avg(成绩) 平均成绩
from 选课表,学生表
where 学生表.学号=选课表.学号 and 班号 =@班号
group by 课程号
)
GO
调用
select * from dbo.某班平均函数('1001')

十．存储过程
1.存储过程创建与执行
（1）创建存储过程
Create proc[edure] 存储过程名
[ @参数 数据类型 [output（指明返回参数）] ]
As
Begin
Sql语句[ ]
End
说明：存储过程可以带参数 也可不带 参数可以输入 也可输出

例1. 创建“学生名单”存储过程 功能为输出所有学生名单
Create procedure 学生名单
As
Begin
Select 学号，姓名
From 学生表
End

例2.创建“某班学生名单”存储过程 其功能查看某个班级学生名单 结果按学号排序
Create procedure 某班学生名单
@班名 varchar （20）
As
Begin
Select 学号，姓名
From 学生表，班级表
Where 学生表.班号 = 班级表.班号 and班名=@班名
Order by 学号
End

例3.创建“查询学生”存储过程 功能根据学号查询学生姓名 年龄
Create procedure 查询学生
@学号 char（11），@姓名 nchar（5）output，@年龄 int output
As
Begin
Select @姓名 = 姓名，@年龄=datediff（year，出生日期，getdate（））
From 学生表
Where 学号=@学号
End

（2）执行存储过程
Execute 存储过程名 @参数=[值]

例1 查看学校学生名单
Execute 学生名单

例2 查看计算机应用071班学生名单
Execute 某班学生名单 @班级=‘计算机应用071班’

例3 查看学号为“20070101101”的学生 姓名 年龄
declare @姓名 nchar(5)
declare @年龄 int
exec 查询学生 '15999015',@姓名 output, @年龄 output
print @姓名
print @年龄查看存储过程
（1）查看存储过程的定义
Exec sp_helptext “ ”
（2）查看存储过程的参数和数据类型信息
Exec sp_help “ ”
（3）查看存储过程的依赖关系
Exec sp_depends “ ”

2. 修改存储过程
Alter proc[edure] 存储过程名
[ @参数 数据类型 [output（指明返回参数）] ]
As
Begin
Sql语句[ ]
End

例 修改“学生名单”存储过程 结果按学号排序
alter procedure 学生名单
As
Begin
Select 学号，姓名
From 学生表
Order by 学号
End

2. 删除存储过程
Drop procedure 存储过程名

例 将存储过程“学生名单”删除
Drop procedure 学生名单

十一．触发器
1. 创建触发器
Create trigger 触发器名
On 表名/视图名
{ for / after / instead of }
{ [ insert ] [ update ] [ delete ] 指定表/视图执行哪条语句时激活触发器关键字}
As
Sql 语句

例1.对学生表创建插入触发器 当有新纪录插入表时 更新班级表中相应班级人数
Create trigger 增加学生
On 学生表
after insert
As
Begin
Set nocount on
If update（班级）
Begin
Update 班级表
Set 学生人数=学生人数+1
From inserted
Where 班级表.班号=inserted.班级
End
End

例2.对学生表创建删除触发器 当删除一条纪录时 减少班级表中相应班级人数
Create trigger 删除学生
On 学生表
after delete
As
Begin
Set nocount on
Begin
Update 班级表
Set 学生人数=学生人数-1
From deleted
Where 班级表.班号=deleted.班级
End
End

例3.对学生表创建更新触发器 当一条纪录被更新时 修改班级表中相应班级人数
Create trigger 更新学生
On 学生表
after update
As
Begin
Set nocount on
If update（班级）
Begin
Update 班级表
Set 学生人数=学生人数-1
From deleted
Where 班级表.班号=deleted.班级
End
End

1. 查看触发器
（1）查看触发器的定义
Exec sp_helptext “ ”
（2）查看触发器的参数和数据类型信息
Exec sp_help “ ”
（3）查看触发器的依赖关系
Exec sp_depends “ ”
1. 修改触发器
Alter trigger 触发器名
On 表名/视图名
{ for / after / instead of }
{ [ insert ] [ update ] [ delete ] 指定表/视图执行哪条语句时激活触发器关键字}
As
Sql 语句

4.删除触发器
Drop trigger 触发器名

例 将触发器“更新学生”删除
Drop trigger 更新学生