数据库期末（上机）

 

练习老师给的期末上机试题并总结相关知识点

调试方法：绿色按钮；逐语句调试。调试在编程中是一个重要方法，通过调试观察可以节省很多找错误的时间。

当数据库中表较多时，需要画E-R图理清彼此之间的关系。

 

目录

一、系统自带函数

二、数据库

三、表

四、从表中查询数据

五、视图

六、用户自定义函数

七、存储过程

八、触发器

九、权限

十、备份

---

一、系统自带函数

1、已知字符串‘   ABCDEFG’，请用查询指令删除前导空（3个空格），并使得查询结果为CDE。

• 删除前导空：ltrim(' ')   补：删除尾导空：rtrim(' ')；删除前尾导空：trim(' ')
• substring( '字符串'，指明为第几个字符，指明需要多少位)

select substring(ltrim('   ABCDEFG'),3,3)

结果：

• 常见系统标量函数

1. abs（数学表达式）—— 数字表达式的绝对值                                                                            eg：select ABS(-5.0), ABS(0.0), ABS(8.0)                  
2. rand([种子]) —— 返回0～1之间的一个随机值。                                                                    参数“种子”是指定种子值的整型表达式，返回值类型为float。如果未指定“种子”，则随机分配种子值。对于指定的种子值，返回的结果始终相同。
3. left(字符表达式 , 整型表达式) —— 返回从字符串左边开始指定个数的字符，返回值为varchar型。
4. replace('字符串表达式1' , '字符串表达式2' , '字符串表达式3' ) —— 用“字符串表达式3”替换“字符串表达式1”中包含的“字符串表达式2”，并返回替换后的表达式（返回值为字符型）。
5. cast、convert这两个函数的功能都是实现数据类型的转换，但convert
   的功能更强一些。常用的类型转换有以下几种情况：日期型→字符型、字符型→日
   期型、数值型→字符型。
   语法格式：
   cast ( 表达式 AS 数据类型[(长度)])
   convert(数据类型[(长度)], 表达式 [, 类型])
6. isnumbric函数 —— 用于判断一个表达式是否为数值类型。
   语法格式：
   isnumbric(表达式)
   如果输入表达式的计算值为有效的整数、浮点数、money或decimal类型，则isnumbric返回1；否则返回 0。

 

二、数据库

2、使用T-SQL语句建立数据库school，它包含3个文件组：主文件组、sch1文件组、sch2文件组。主文件组包含10MB的schmgt1和10MB的schmgt2两个文件，两个文件的最大尺寸为40MB，增长长度为2MB；sch1文件组包含10MB的schmgt3和10MB的schmgt4两个文件，两个文件的最大尺寸为默认，增长长度为1MB；sch2文件组包含10MB的schmgt5一个文件，这个文件的最大尺寸为20MB，增长长度为10%。该数据库同时还包含一个日志文件schmgtlog，文件大小为20MB，增长长度为10％。（存储路径都为d:\school）

• 创建数据库：

create database [数据库名]
on   //文件组
primary  //主文件组
(
    name=文件组名 
    filename='保存地址' //扩展名为.mdf
    maxsize //最大尺寸
    size  //文件尺寸
    filegrowth //增长长度
),
filegroup [次文件组名] //次文件组
(
     name=文件组名
    filename='保存地址' //扩展名为.ndf
    maxsize //最大尺寸
    size  //文件尺寸
    filegrowth //增长长度
)
log on //日志文件
(
    name=文件组名
    filename='保存地址' //扩展名为.ldf
    maxsize //最大尺寸
    size  //文件尺寸
    filegrowth //增长长度
)

• 对于文件组主文件组后缀为.mdf，其他文件组后缀为.ndf，日志文件后缀为.ldf；主文件组与次文件组间要用 ，隔开；一个文件组中包含多个文件也要用 ， 隔开  
• 文件组与日志文件之间不需要 ，隔开
• 最大尺寸未指出或为默认值时可以省略不写，无限情况为=unlimited
• name=文件名，文件名不需要加引号

create database school
on
primary
(
	name=schmgt1,
	filename='E:\0-大三下课程\数据库\期末\sql\schmgt1.mdf',
	maxsize=40mb,
	size=10mb,
	filegrowth=2mb),
(
	name=schmgt2,
	filename='E:\0-大三下课程\数据库\期末\sql\schmgt2.ndf',
	maxsize=40mb,
	size=10mb,
	filegrowth=2mb),
filegroup sch1
(
	name=schmgt3,
	filename='E:\0-大三下课程\数据库\期末\sql\schmgt3.ndf',
	size=10mb,
	filegrowth=1mb
),
(
	name=schmgt4,
	filename='E:\0-大三下课程\数据库\期末\sql\schmgt4.ndf',
	size=10mb,
	filegrowth=1mb
),
filegroup sch2
(
	name=schmgt5,
	filename='E:\0-大三下课程\数据库\期末\sql\schmgt5.ndf',
	size=10mb,
	maxsize=20mb,
	filegrowth=10%
)
log on
(
	name=schmgtlog,
	filename='E:\0-大三下课程\数据库\期末\sql\schmgtlog.ldf',
	size=20mb,
	filegrowth=10%
)

 

三、表

3.在school数据库中包含有学生信息表（student）、课程信息表（course）和成绩表（score），它们的定义分别为：

student (学号 char(6)  not null，姓名 char(8)，性别 char(2)，出生时间 datetime，专业 char(12)，总学分int 0到100学分之间，备注varchar(500))

course (课程号 char(3)  not null，课程名 char(16)not null，开课学期tinyint default 1，学时tinyint default 0，学分tinyint default 0)

score (学号 char(6)  not null，课程号 char(3)  not null，成绩int default 0, 主键为学号与课程号的组合)

（1）用T-SQL语句分别创建表student 表，course表，score表，并插入相关数据，相关数据见附录。

• 创建表

use [数据库名]
go
create table [表名]
(
    //表列名与列的参数设定
)

• 代码写创建数据库是注意：

1、类型包含长度的，在类型后面加(括号)，括号里面写长度

2、上一列写完加逗号

3、最后一列不要写逗号

• 约束

1、primary key:主键

2、foreign key:外键

3、unique:唯一性——指定的列在表中的数据不能相同，一个表中可以有多个

4、check:取值范围

5、default:默认值——当插入数据该列没有指定值时，程序定义一个值默认输入

6、null (空) not null (非空)

• 创建约束

use [数据库名]
go
alter table [表名]
--主键
add constraint [键名] primaty key ([列名])
--外键 
add constraint [键名] foreign key ([列名]) references [外键参照表名]([外键参照列名])
on update cascade --级联更新
on delete cascade --级联删除
--唯一性约束
add constraint [键名] unique ([列名])
--检查约束
add constraint [键名] check ([列名] in ([取值]))

• 销毁约束

use [数据库名]
go
alter table [表名]
drop constraint [键名]

 

use school
go
create table student (
学号 char(6)  not null,
姓名 char(8),
性别 char(2),
出生时间 datetime,
专业 char(12),
总学分 int check (总学分>=0 and 总学分<=100) ,
备注 varchar(500)
) 
go
create table course (
课程号 char(3)  not null,
课程名 char(16)not null,
开课学期 tinyint default 1,
学时 tinyint default 0,
学分 tinyint default 0
)
go
create table score (
学号 char(6)  not null,
课程号 char(3)  not null,
成绩 int default 0, 
primary key(学号,课程号) )

• 指定列中内容的范围：【总学分 int 0到100学分之间】总学分 int check （总学分>=0 and 总学分<=100）
• 设置主键：为组合：【主键为学号与课程号的组合】 primary key(学号，课程号) primary key位于前面；为单一列：primary key位于后面。

 

• 插入数据

use [数据库名]
go
insert into [表名] values('','')

 

四、从表中查询数据

（2）—（6）

• 查找数据

use [数据库名]  
go

select [输出列1] , [输出列2] , …
//select distinct[输出列] --消除重复列
//select all[输出列] --符合条件的所有行
//select TOP [数字或百分比] [输出列] --限制返回行数
/* 用case替换查询结果中的数据
[列名]=
case
    where [条件1] then [表达式1]
    where [条件2] then [表达式2]
    ……     --其余查询条件
    else [表达式]
end  */  

into [新表名]
from [查找的表名1],[查找的表名2],…

where [列名]=/>/</!=/……[要求]  -- 不可以出现聚合函数
and [列名] (not) like [转义符]  --like用于指出字符串是否与指定的字符串相匹配
and [列名] (not) between [表达式1] and [表达式2]  --查询的条件是某个值的范围
and [列名] (not) in ()  /*使用IN关键字可以指定一个值表，值表中列出所有可能的值，当与值表中的任一个匹配时，即返回TRUE，否则返回FALSE。*/
and [列名] is (not) null

group by [列名] 
//group by rollup/cube/grouping sets([列名1],[列名2]，……)

having [分组统计条件]
--使用group by子句和聚合函数对数据进行分组后，用having子句对分组数据做进一步的筛选。
--可以出现聚合函数，可以与group by中列名不一致

order by [排序条件]  --升序（小数在前）
//order by [排序条件] desc --降序（大数在前）

•  聚合函数 ([all/distinct] 列名)

• 模式匹配——like

eg:姓“王”且单名——LIKE '王_ '       

倒数第5个数字为9，且倒数第1个数在1～5之间—— LIKE '%9_ _ _[1-5]'

• 子查询：子查询可用于select、from、where中
• exists子查询：用于测试子查询的结果是否为空表，若子查询的结果集不为空，则EXISTS返回TRUE，否则返回FALSE。EXISTS还可与NOT结合使用，即NOTEXISTS，其返回值与EXISTS刚好相反。格式为：[ NOT ] EXISTS ( 子查询 )

• 相关子查询：子查询中的条件语句依赖于父查询的某个属性值，即子查询的条件需要父查询提供。

• 不相关子查询：嵌套部分的select能单独运行。

• 嵌套查询是由内而外的查询，链接查询是多表间的查询

 

（2）找出“计算机基础”课程成绩在90分以上的学生姓名和专业。

注：90分以上不包括90分。

use school
go
select 姓名,专业
from student
where 学号 in (
select 学号
from score
where 成绩>90
and 课程号 =(
select 课程号
from course
where 课程名='计算机基础'))

or

use school
go
select 姓名,专业
from student,score,course 
where student.学号=score.学号
and score.课程号=course.课程号
and 成绩>90 and 课程名='计算机基础'

 

（3）查找比所有计算机系学生年龄都小的学生。

use school
go
select *
from student 
where 出生时间 > all (
select 出生时间
from student
where 专业='计算机')

or

use school
go
select *
from student
where 出生时间>(
select max(出生时间)
from student
where 专业='计算机')

 

（4）检索选修2门以上课程的每个学生的平均成绩，并要求按平均成绩的降序排列。

use school
go
select 姓名,score.学号,avg(成绩)as'平均成绩'
from student,score
where student.学号=score.学号
group by student.学号,score.学号,student.姓名
having count(课程号)>2
order by avg(成绩) desc

 

（5）从student表中查找所有男学生的姓名、学号以及其与张蔚同学的年龄差距。

注：计算年龄用到函数 year() —取时间中的年份

use school
go
select 姓名,学号,(year(出生时间)-year(
(	select 出生时间
	from student
	where 姓名='张蔚'))) as '年龄差'
from student
where 性别='男'

•  该题注意年龄差的计算，一开始有点错误，没想对。计算年纪才getdate( ) —获取当前时间。年龄差是两者相减，与当前无关。

 

（6）查找不同课程成绩相同的学生的姓名、课程名和成绩。 

分析题目：不同课程成绩相同的学生翻译为同一个学生的不同课程有相同成绩，查找该学生。

• 将一个表命名为其他名字后，就无法通过其本名调用列，如果想让两个相同的表建立联系，必须为原来的表命两个名字。

use school
go
select 姓名,c1.课程名,sc1.成绩
from student as s,course as c1,course as c2,score as sc1,score as sc2
where sc1.成绩=sc2.成绩
and c1.课程号!=c2.课程号
and sc1.学号=s.学号
and sc2.学号=s.学号
and c1.课程号=sc1.课程号
and c2.课程号=sc2.课程号

 

！！！这个题目想不通 

 

五、视图

（7）创建计算机专业学生总成绩视图CE_SUM，包括学号（在视图中列名为num）和总成绩（在视图中列名为score_sum）。

注：题目一开始提到了学生专业为计算机

• 创建视图

use [数据库名]
go
create view [视图名]([视图列名1],[视图列名2],……)
as
    select [视图中需要的表的列名]
    from [表的列名对应的表]
    where [条件]
    group by [条件]
    having [条件]
/*union all --分区视图
select
from
*/

• 查看视图中的数据

use [数据库名]
go
select [列名]
from [视图名]

use school
go
--创建
create view CE_SUM (num,score_sum)
as
	select student.学号,sum(成绩)
	from student,score
	where student.学号=score.学号
    and 专业='计算机'
	group by student.学号
go
--查看
select *
from CE_SUM

 

六、用户自定义函数

（8）创建用户定义函数，查询全体学生某门功课最高分、平均分和最低分，并调用该函数查询课程号为101课程的最高分、平均分、最低分。（函数名为MAM_FUN）

• 创建函数

use [数据库名]
go
//返回数字
create function [函数名] ([@参数名1] [参数类型1],[@参数名2] [参数类型2]) --形参
returns [返回值类型] 
as
begin
    declare [@变量名] [变量类型]  --定义变量
    select [@变量名]=       --函数具体功能
    (
        select
        from
        where
        group by
        having
    )
    return [@变量名]
end

//返回表
create function [函数名] ([@参数名1] [参数类型1],[@参数名2] [参数类型2]) --形参
returns table
as return
(
    select
    from
    where
    group by
    having
)

• 调用函数

use [数据库名]
go
//数值
declear [@变量名1] [变量类型1] --定义变量
declear [@变量名2] [变量类型2] 
select [@变量名1] = ''  --为变量赋值
select [@变量名2] =dbo.[函数名]([形参变量名])  --调用函数
select [@变量名2]  --显示结果

//表
select 
from dbo.[函数名]([要求的值])

use school
go
--创建
create function MAM_FUN(@course char(3)) returns table
as return
(
	select max(成绩) as '最高分',avg(成绩) as '平均分',min(成绩) as '最低分'
	from score
	where 课程号=@course
	group by 课程号
)
go
--查询
select *
from dbo.MAM_FUN('101')

 

七、存储过程

（9）创建一个存储过程CPA，比较两个学生(用学号表示)的平均成绩，若前者比后者高就输出1，否则输出0。

• 创建存储过程

use [数据库名]
go
//不使用参数 
create procedure [存储过程名]
as                 
    select       --执行的操作
    from
    where
go
execute [存储过程名]  --执行
//接收与传递参数
create produce [存储过程名] [@参数名1] [参数类型1],[@参数名2] [参数类型2]
as
    select
    from 
    where
go
execute [存储过程名] [参数1要求值] [参数2要求值]
//带output参数
create produce [存储过程名] [@参数名1] [参数类型1],[@参数名2] [参数类型2] output
as
begin

end

use school
go
create procedure CPA @st1 char(6),@st2 char(6)
as
	declare @num1 int
	declare @num2 int
	select @num1=(
	select avg(成绩)
	from score
	where 学号=@st1)
	select @num2=(
	select avg(成绩)
	from score
	where 学号=@st2)
	if(@num1>@num2) 
		select 1
	else
		select 0
go
execute CPA '081103','081101'

 or

use school
go
create procedure CPA @st1 char(6),@st2 char(6),@out int output
as
	declare @num1 int
	declare @num2 int
	select @num1=(
	select avg(成绩)
	from score
	where 学号=@st1)
	select @num2=(
	select avg(成绩)
	from score
	where 学号=@st2)
	if(@num1>@num2) 
		set @out=1
	else
		set @out=0
go
declare @out int
execute CPA '081102','081101',@out output
select @out

• 使用变量时不要加单引号

 

八、触发器

（10）创建触发器score_insert，当向score表中插入数据时，检查学号字段的值在student表中是否存在，若存在，则允许插入，并提示“插入数据成功”；若不存在，则取消插入操作，并提示“该学号不存在于student表中，不能插入记录，插入将终止！”。

• 创建触发器

use [数据库名]
go
//DML触发器
create trigger [触发器名]
on [表名/视图名]
for/after/instead of --常用after
insert/update/delete --可以有一个及以上，用 ，隔开
as
{
    /*SQL语句*/
   begin
        declare 
        select …… from inserted/deleted
            from
            where
        update
        delete
        set
        if……else
        print
        rollback transaction --回滚语句
   end
}
//DDL触发器
create trigger [触发器名]
on datasbase/all server
for/after [事件名 such as:DROP_DATABASE]
as
{
    /*SQL语句*/
}

• 触发器是在事件触发的条件下发生作用，验证是需要触发设定条件，查看结果。

use school
go
create trigger score_insert
on score
after insert
as
	declare @st_num char(6)
	select @st_num = 学号 from inserted
	if(@st_num in (select 学号 from student))
		print '插入数据成功'
	else
		print '该学号不存在于student表中，不能插入记录，插入将终止！'
		rollback transaction

• 比较AFTER（FOR）和INSTEAD OF两种触发器的不同：

触发器	应用于	执行次序	原来SQL	适用于
AFTER (FOR)	表	事件发生之后	执行	记录变更后的处理或检查
INDSTEAD OF	表、视图	事件发生之前	不执行	数据禁止修改；有可能要回滚修改；控制数据的修改方式和流程

 

九、权限

4. 在数据库school上给用户Tim和Lintao授予创建表的权限，并使他们有对student表的所有操作权限。

注：注意所有操作的SQL语句

• 授予权限

use [数据库名]
go
grant [权限名 eg：create table]
to [用户名1],[用户名2]

use school
go
grant create table to Tim,Lintao
grant all on student to Tim,Lintao

 

5. 以命令方式撤销用户Tim在score表中的SELECT、UPDATE权限。

• 撤销权限

use [数据库名]
go
revoke [权限操作1],[权限操作2]
on [表名]
from [用户名1],[用户名2]

use school
go
revoke select,update
on score
from Tim

 

十、备份

6. 采用文件备份的方式将school数据库备份一份。（备份目录为d:\school_BP）

• 数据备份

//数据库文件备份
exec sp_addumpdevice 'DISK','[备份设备名]','[路径名]\[数据库名].bak'
backup database [数据库名]
to [备份设备名]
/*
    with init --覆盖设备中原有的内容
    with noinit --保留设备中原有的内容
    with name='设备中数据库的名称'
*/

exec sp_addumpdevice 'DISK','BP1','d:\school_BP\school.bak'
backup database school
to BP1