专升本数据库练习题及答案(共21页).doc

精选优质文档-倾情为你奉上一关系代数运算1.选择 （1）查询年龄小于20岁的学生 sage<20(student) （2）查询学号为的学生 sno=(student)2.投影 （1）查询学生姓名，和所在系 same,sdept(student)（2）查询选修了2号课程的学生学号 sno（cno=22(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_dat1 Alter database 教务管理数据库 Add file （ Name=jwgl_dat1, Filename=E:SQLjwgl_dat1.ndf， Size=2MB， Filegrowth=1MB )2. 修改教务管理数据库 Alter database 教务管理数据库 Modify file （ Name=jwgl_dat1, Size=5MB ）2. 删除教务管理数据库中次要数据文件jwgl_dat1 Alter database 教务管理数据库 Remove file jwgl_dat11. 数据库更名1) 更改数据库名Alter database 数据库名Modify name = 新数据库名例 将数据库test更名为mytest Alter database test Modify name = mytest1) 更改数据库的逻辑文件名 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:sqlmytest.mdf ） GO Alter database mytest Modify file（ Name=mytest_log， Filename =E:sqlmytest_log.ldf ）1. 删除数据库 Drop database 数据库名例 删除数据库名为test和数据库sales Drop 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型 长度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 （，张成民，男，18，计算机系）1. 修改数据 Update 表名 Set 列名=表达式 Where 条件1) 修改某元祖的值 例 将学生的年龄改为22 Update 学生表 Set 年龄=22 Where 学号=1) 修改多个元祖的值 例 将所有学生年龄增加一岁 Update 学生表 Set 年龄=年龄+11) 带子查询的修改语句 例 将计算机科学系全体学生成绩置零 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成绩 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_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 查看学号为“”的学生 姓名 年龄 declare 姓名 nchar(5)declare 年龄 intexec 查询学生 '',姓名 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 更新学生专心-专注-专业