一、填空题(每空1分,共10分)
Select的各子句的作用
Select表示仅仅读取信息而不修改数据信息
From指定要读取数据所在的一个表或几个表达名称
Where用来设定返回记录的限制条件
Order by用来定义返回数据从排列顺序
Group by用于聚合信息
Having用于带有group by子句的查询中,应用于分组的聚合值
For xml输出xml
Option优化运行sql查询的方法
Distinct和all用于返回无重复的行或所有行
Create table语句中各类约束条件的实现关键字
CONSTRAINT约束的开始
PRIMARY KEY主键约束
UNIQUE唯一约束
FOREIGN KEY REFERENCES外键约束
CHECK完整性约束
在DML触发器中,DML命令操作的数据保存在哪里
在updated和deleted表中
怎么判断查询结果是否为空
使用exist()函数
怎么判断某值X是否为空
使用isnull函数
如isnull(value tested,0)
怎么实现表连接
内部连接Inner join
外部连接outer join
全连接full join
交叉连接cross join
存储过程形参的定义形式
名称数据类型默认值方向
@parameter datatype=default output
默认值和方向可以省略,但对于声明输出参数是必须使用output 数据完整性有几种
实体完整性
域完整性
引用完整性
用户定义完整性
怎么实现级联修改
在外键中使用on update no action
on delete cascade
使用DML触发器
在函数定义中怎么定义返回值。
使用return子句
return标量返回标量值
return table返回表
二、名词解释(每小题2分,3小题,共6分)
数据库为特定目的(例如搜索、排序和重新组合数据)而组织和表示的信息、表和其他对象的集合
外键其值与同一个表或另一个表中的主键(PK)或唯一键相匹配的列或列的组合,也称为引用键。
死锁两个用户各拥有一个数据块的锁,并尝试获取对方的数据块锁时出现
的情况。
事务组成一个逻辑工作单元的一组数据库操作,这些操作要么全部提交,要么全部回滚
主键唯一地标识表中所有行的一列或一组列。
实体完整性一种状态,在该状态下,每个表的每一行都能唯一标识。
三、简答题(每小题5分,3小题,共15分)
索引的设计原则
1.了解数据库本身的特征。
2.了解最常用的查询的特征。
3.了解查询中使用的列的特征
4.确定哪些索引选项可在创建或维护索引时提高性能.
5.确定索引的最佳存储位置。
事务的属性
原子性、一致性、隔离性和持久性
简述各类数据完整性及其对应的约束实现
实体完整性:规定表的每一行在表中是惟一的实体。用主键约束实现
域完整性:是指表中的列必须满足某种特定的数据类型约束。用check约束实现
参照完整性:是指两个表的主关键字和外关键字的数据应一致。用外键约束实现
用户定义的完整性:针对某个特定关系数据库的约束条件,它反映某一具体应用必须满足的语义要求。用非空约束,check约束和触发器
来实现
索引的作用
索引能大大加快数据的检索速度,加速表和表之间的连接。在使用分组
和排序子句进行数据检索时,可以显著减少查询中分组和排序的时间。
创建唯一性索引,保证数据库表中每一行数据的唯一性;
DML触发器的用途
可通过数据库中的相关表实现级联更改。
防止恶意或错误的INSERT、UPDATE以及DELETE操作。
引用其他表中的列。
评估数据修改前后表的状态,并根据该差异采取措施
SQL分为几类,每类常用的命令有哪些?
数据定义语言(DDL),例如:CREATE、DROP、ALTER等语句。
数据操作语言(DML),例如:SELECT、INSERT、UPDATE、DELETE语句。
数据控制语言(DCL),例如:GRANT、REVOKE、COMMIT、ROLLBACK等存储过程的优点
提高数据库执行速度。
可以重复使用。
安全性高。
当对数据库进行复杂操作时,可将此复杂操作用存储过程封装起来与数
据库提供的事务处理结合一起使用。
四、计算题(4分)
一个数据表使用单一扩展盘区或混合扩展盘区存储时占用磁盘空间的估算。五、操作题(共45分)
所有操作假设由sa账号用SQL语句完成。
1、创建一个数据库
create database教学
on
(name='教学_data',
filename='e:\sqltest\data\教学_data.mdf',
size=10MB,
maxsize=200MB,
filegrowth=10MB
)
log on
(name='教学_log',
filename='e:\sqltest\data\教学_log.ldf', size=1MB,
maxsize=20MB,
filegrowth=1MB)
2、在SQL Serve中添加一个登录名
Windows域帐户创建登录名
create login sasa
from windows;
创建带密码的登录名
create login dafei
with password='111111'
3、在“教学”数据库中添加数据库用户
use教学
create user fei without login
或
use教学
create user fei for login dafei
4、在“教学”数据库中创建三个数据表:
create table学生
(学号char(10)primary key
constraint xs_xh_ck check(学号like'0808__ _[1-2]__'),check语句是按软件班上面的要求来写的姓名nvarchar(20)not null,
生日datetime
)
create table课程
(课程号char(4)primary key,
课程名nvarchar(20)not null
)
create table成绩
(
学号char(10)constraint xs_cj_xh foreign key references学生(学号),
课程号char(4)constraint xs_cj_kch foreign key references课程(课程号),
分数decimal(4,1)default(0)
check(分数between0and100),
primary key(学号,课程号)
)
考核内容重点:数据类型、主键、外键、空/非空、数据校验、默认等约束。5、修改表:添加一列“性别”(只能取值“男”或“女”),并在“生日”上添加不对已有数据进行验证的数据校验约束“bircheck”,要求输入的数据需满足的条件是学生的年龄必须不小于14岁。(7分)
考核内容重点:添加列、添加约束条件(对已有数据是否校验)、开关约束条件alter table学生
add性别char(1)check(性别like'男女');
alter table学生
add constraint bircheck
check(year(getdate())-left(生日,4)>=14);
6、权限管理
use教学
grant select on学生to fei
deny select on学生to fei
revoke select on学生from fei
grant create table to fei
必须加use教学因为前面的是为教学添加的fei用户
7、创建一个视图,用以显示各班的SQL成绩统计(包括班级,最高分,最低分,平均分)。
use教学
go
create view SQL成绩_view
as
select left(学生.学号,8)as班级,max(分数)as最高分,min(分数)as最低分,avg(分数)as平均分
from学生
left join成绩
on学生.学号=成绩.学号
inner join课程
on成绩.课程号=课程.课程号
where学生.学号like'__08_______'
and课程.课程名='SQL'
group by班级
创建一个显示所有XX级计算机系学生的《YY》课程的成绩的视图,其中包括学号,姓名,课程名,分数四列。
use教学
go
create view某课程的成绩_view
as
select学生.学号,学生.姓名,课程.课程号,成绩.分数from学生
left join成绩
on学生.学号=成绩.学号
inner join课程
on成绩.课程号=课程.课程号
where subtring(学生.学号,2,2)='08'
and课程.课程名='某课程'
按班级、性别统计XX级计算机系各班的YY课程的平均成绩,结果按照班级的升序,性别的降序显示。
use教学
select left(学生.学号,8)as班级,学生.性别, avg(成绩.分数)as分数
from学生
left join成绩
on学生.学号=成绩.学号
inner join课程
on成绩.课程号=课程.课程号
where subtring(学生.学号,2,2)='08'
and课程.课程名='YY课程'
group by班级,学生.性别
order by班级asc,性别desc
8创建简单/复合索引(升/降序)。
use教学
create index xh_index
on学生(学号asc)
go
use教学
create index xh_fs_index
on成绩(学号desc,分数desc)
9、定义触发器
use教学
go
create trigger xst
on学生
after delete
as
delete from成绩
where成绩.学号in
(select学号from deleted)
在“教学”数据库中添加一个角色。
use教学
create role xs_role authorization fei
5、将安全账户添加到角色中。
use教学
exec sp_addrolemember'xs_role','fei'
六、编程题(20分)
在“教学”数据库中创建一个存储过程cj_proc,实现按照输入的课程名称打印此门课程的成绩报表(如不给定课程名称,则输出“SQL”的成绩),输出结果按照分数降序排列。
use教学
go
if exists
(select*from sysobjects
where name='cj_proc'and xtype='p') drop proc cj_proc
go
create proc cj_proc@KCM nvarchar(40)='SQL' as
SET NOCOUNT ON
DECLARE
@XH char(10),--学号
@XM nvarchar(10),--姓名
@kch nvarchar(6),--课程号
@CJ numeric(4,1),--成绩
@message nvarchar(80),--信息
@mc int--名次
if not exists
(select课程.课程号
from课程
inner join成绩
on课程.课程号=成绩.课程号
and课程.课程名=@kcm)
print'无此课程成绩'
else
begin
select@kch=课程.课程号
from课程where课程.课程名=@kcm
print'《'+@kcm+'》成绩表' print REPLICATE('*',48)--重复个*制成一种表格形式print'名次学号姓名成绩' declare xhcj_cursor cursor scroll
for select学生.学号,姓名,分数from学生,成绩where成绩.学号=学生.学号and课程号=@kch
order by分数desc
open xhcj_cursor
fetch next from xhcj_cursor into@xh,@xm,@cj set@mc=1
while@@fetch_status=0
begin
set
@message=ltrim(str(@mc))+space(10)+@xh+space(7) +@xm+space(12)+ltrim(str(@CJ))
PRINT@message
fetch next from xhcj_cursor into@xh,@xm,@CJ
set@mc=@mc+1
end
print REPLICATE('*',48) CLOSE xhcj_cursor DEALLOCATE xhcj_cursor end
go