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

一．关系代数运算1.选择σ（1）查询年龄小于20岁的学生σsage<20(student)（2）查询学号为201215122的学生σsno=201215122(student)2.投影（1）查询学生姓名，和所在系same,sdept(student)（2）查询选修了2号课程的学生学号sno（σcno=‘2’2(student)）SQL语句学生-课程数据库学生表：学生（学号，姓名，性别，年龄，所在系）课程表：课程（课程号，课程名，先行课，学分）学生选课表：选课（学号，课程号，成绩）二．创建数据库SQL1.创建数据库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_dat1Alter 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_dat1Alter database 教务管理数据库Remove file jwgl_dat11.数据库更名1)更改数据库名Alter database 数据库名Modify name = 新数据库名例将数据库test更名为mytestAlter database testModify name = mytest1)更改数据库的逻辑文件名Alter database 数据库名Modify file（Name=逻辑文件名，Newname=新逻辑文件名）例将数据库逻辑文件test更名为mytestAlter database mytestModify file（Name = test，Newname = mytest ）GoAlter database mytestModify file（Name = test_log，Newname = mytest_log ）1)更改数据库的物理文件名Alter database 数据库名Modify file（Name=逻辑文件名，Filename =‘新路径\操作系统文件名’）例将数据库物理文件test更名为mytestAlter database mytestModify file（Name=mytest，Filename =‘E:\sql\mytest.mdf’）GOAlter database mytestModify file（Name=mytest_log，Filename =‘E:\sql\mytest_log.ldf’）1.删除数据库Drop database 数据库名例删除数据库名为test和数据库salesDrop database test，sales1.数据库分离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型长度20Alter table 学生表Add 电子邮箱char(20)例2. 修改学生表列电子邮箱数据类型长度为22Alter table 学生表alter column 电子邮箱varchar(22)例3.删除学生表的电子邮箱列Alter table 学生表drop column 电子邮箱3.删除表Drop table 表名例删除学生表Drop table 学生表四．数据更新1.插入数据InsertInto 表名（）Values 值（）例1 插入一行数据到学生表的学生InsertInto 学生表（学号，姓名，性别，年龄，所在系）Values （‘201215126’，‘张成民’，‘男’，18，‘计算机系’）1.修改数据Update 表名Set 列名=表达式Where 条件1)修改某元祖的值例将学生201215121的年龄改为22Update 学生表Set 年龄=22Where 学号=‘201215121‘1)修改多个元祖的值例将所有学生年龄增加一岁Update 学生表Set 年龄=年龄+11)带子查询的修改语句例将计算机科学系全体学生成绩置零Update 选课表Set 成绩=0Where 学号in（select 学号From 学生表Where 所在系=‘计算机科学系’）1)修改表名Sp_rename 旧对象名，新对象名例课程表更名为课程信息表Sp_rename 课程表，课程信息表3.删除数据Deletefrom 表名Where 条件例将课程信息表中c程序设计课程信息删除DeleteFrom 课程信息表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 checkAdd constraint CK_课程表_1 check（考核方式in （‘考查’，‘考试’））Alter table 课程表with nocheckAdd 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成绩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 表一.列名(比较运算符>,=,<)表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 nonclusteredIndex XS XMOn 学生表（姓名asc）With fillfactor=502.删除索引Drop index 表名.索引名九．视图1.定义视图1)建立视图Create view 视图名列名AsSelect（在视图后加上with check option子句以后对该视图进行增删改时则自动添加）例建立信息系学生的视图Create view 信息系学生AsSelect 学号，姓名，年龄From 学生表Where 系别=‘信息系’1)删除视图Drop view 视图名（如选择级联删除视图名后加cascade）例删除视图BT_S和视图IS_S1Drop view BT_SDrop 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 TABLEASRETURN(select班名,学生表.学号,姓名,课程名,成绩from学生表,课程表,班级表,选课表where课程表.课程号=选课表.课程号and学生表.学号=选课表.学号and学生表.班号=班级表.班号and班名=@班名and课程名=@课程名)GO执行select*from dbo.某班某课成绩单('嵌入式班','java程序设计')3、CREATE FUNCTION某班平均函数(@班号char(5))RETURNS TABLEASRETURN(SELECt课程号,avg(成绩)平均成绩from选课表,学生表where学生表.学号=选课表.学号and班号=@班号group by课程号)GO调用select*from dbo.某班平均函数('1001')十．存储过程1.存储过程创建与执行（1）创建存储过程Create proc[edure] 存储过程名[ @参数数据类型[output（指明返回参数）] ]AsBeginSql语句[ ]End说明：存储过程可以带参数也可不带参数可以输入也可输出例1. 创建“学生名单”存储过程功能为输出所有学生名单Create procedure 学生名单AsBeginSelect 学号，姓名From 学生表End例2.创建“某班学生名单”存储过程其功能查看某个班级学生名单结果按学号排序Create procedure 某班学生名单@班名varchar （20）AsBeginSelect 学号，姓名From 学生表，班级表Where 学生表.班号= 班级表.班号and班名=@班名Order by 学号End例3.创建“查询学生”存储过程功能根据学号查询学生姓名年龄Create procedure 查询学生@学号char（11），@姓名nchar（5）output，@年龄int outputAsBeginSelect @姓名= 姓名，@年龄=datediff（year，出生日期，getdate（））From 学生表Where 学号=@学号End（2）执行存储过程Execute 存储过程名@参数=[值]例1 查看学校学生名单Execute 学生名单例2 查看计算机应用071班学生名单Execute 某班学生名单@班级=‘计算机应用071班’例3 查看学号为“20070101101”的学生姓名年龄declare@姓名nchar(5)declare@年龄intexec查询学生'15999015',@姓名output,@年龄outputprint@姓名print@年龄查看存储过程（1）查看存储过程的定义Exec sp_helptext “”（2）查看存储过程的参数和数据类型信息Exec sp_help “ ”（3）查看存储过程的依赖关系Exec sp_depends “ ”2.修改存储过程Alter proc[edure] 存储过程名[ @参数数据类型[output（指明返回参数）] ]AsBeginSql语句[ ]End例修改“学生名单”存储过程结果按学号排序alter procedure 学生名单AsBeginSelect 学号，姓名From 学生表Order by 学号End2.删除存储过程Drop procedure 存储过程名例将存储过程“学生名单”删除Drop procedure 学生名单十一．触发器1.创建触发器Create trigger 触发器名On 表名/视图名{ for / after / instead of }{ [ insert ] [ update ] [ delete ] 指定表/视图执行哪条语句时激活触发器关键字} AsSql 语句例1.对学生表创建插入触发器当有新纪录插入表时更新班级表中相应班级人数Create trigger 增加学生On 学生表after insertAsBeginSet nocount onIf update（班级）BeginUpdate 班级表Set 学生人数=学生人数+1From insertedWhere 班级表.班号=inserted.班级EndEnd例2.对学生表创建删除触发器当删除一条纪录时减少班级表中相应班级人数Create trigger 删除学生On 学生表after deleteAsBeginSet nocount onBeginUpdate 班级表Set 学生人数=学生人数-1From deletedWhere 班级表.班号=deleted.班级EndEnd例3.对学生表创建更新触发器当一条纪录被更新时修改班级表中相应班级人数Create trigger 更新学生On 学生表after updateAsBeginSet nocount onIf update（班级）BeginUpdate 班级表Set 学生人数=学生人数-1From deletedWhere 班级表.班号=deleted.班级EndEnd1.查看触发器（1）查看触发器的定义Exec sp_helptext “”（2）查看触发器的参数和数据类型信息Exec sp_help “ ”（3）查看触发器的依赖关系Exec sp_depends “ ”1.修改触发器Alter trigger 触发器名On 表名/视图名{ for / after / instead of }{ [ insert ] [ update ] [ delete ] 指定表/视图执行哪条语句时激活触发器关键字}AsSql 语句4.删除触发器Drop trigger 触发器名例将触发器“更新学生”删除Drop trigger 更新学生。