(完整版)专升本数据库练习题及答案

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 更新学生