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

一关系代数运算1.选择 （1）查询年龄小于20岁的学生 sage = =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成绩 desc2. 聚集函数（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 表一.列名(比较运算符,=,=,=,!=） 例 查询与刘晨在同一个系学习的学生的学号，姓名 Select 学号，姓名，系名 From 学生表 Where系名 in （ Select 系名 From 学生表 Where姓名=刘晨）八建立索引1.建立索引 Create unique（唯一）clustered ( 聚集 ) nonclustered ( 非聚集 ) Index 索引名 On （表/视图）（列名asc（升序）desc（降序） With pad_indexfillfactor=填充因子drop_existing 例 在学生表上创建一个名为XS XM的非唯一性非聚簇索引 索引关键字为姓名 升序 填充因子为50% Create nonclustered Index XS XM On 学生表（姓名 asc） With fillfactor=502. 删除索引 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 返回值类型beginreturnend例：给专业名求男女比例CREATE FUNCTION 某专业男女比例(专业名 varchar(20)RETURNS numeric(6,2)ASBEGINdeclare man intdeclare woman intselect 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 ASRETURN (select 班名,学生表.学号,姓名,课程名,成绩from 学生表,课程表,班级表,选课表where 课程表.课程号=选课表.课程号 and 学生表.学号=选课表.学号 and 学生表.班号=班级表.班号 and 班名=班名 and 课程名=课程名)GO 执行select * from dbo.某班某课成绩单(嵌入式班,java程序设计)3、CREATE FUNCTION 某班平均函数(班号 char(5)RETURNS TABLE ASRETURN (SELECt 课程号,avg(成绩) 平均成绩from 选课表,学生表where 学生表.学号=选课表.学号 and 班号 =班号group by 课程号)GO调用select * from dbo.某班平均函数(1001)十存储过程1.存储过程创建与执行 （1）创建存储过程 Create procedure 存储过程名 参数 数据类型 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 年龄 intexec 查询学生 15999015,姓名 output, 年龄 output print 姓名print 年龄查看存储过程 （1）查看存储过程的定义 Exec sp_helptext “ ” （2）查看存储过程的参数和数据类型信息 Exec sp_help “ ” （3）查看存储过程的依赖关系 Exec sp_depends “ ”2. 修改存储过程 Alter procedure 存储过程名 参数 数据类型 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 更新学生