数据库复习知识点

数据库命令的基本考法

设有一个教师人员管理系统局部数据库，包括教师、职称两个关系模式。其中教师（Teacher）表结构如下：

字段名称	数据类型·	是否为空值	约束	说明
TNo	char(5)	x	主键	教师编号
TName	varchar(10)	x	唯一值	教师姓名
TAge	int	√	默认值为20	教师年龄
TSex	char(2)	√	默认为男	教师编号
Title	varchar(10)	x	外键	教师职称

职称（Position）表的结构如下：

字段名称	数据类型·	是否为空值	约束	说明
Title	varchar(10)	x	主键	职称名称
Salary	int	x	唯一值	基本工资
Allowance	int	√	岗位津贴

1.用SQL语言定义教师（Teacher）表

create table teacher(
	TNo char(5) primary key,
	TName varchar(10) not null unique,
	TAge int default 20,
	TSex char(2) default '男',
	Title varchar(10),
	Foreign key (Title) References Position(Title)
);

2.利用关系代数实现查询：求王华老师的基本工资、岗位津贴。
$${\Pi }_{Salary,Allowance}({\sigma }_{Tname{=}^{\prime }王{华}^{\prime }}(Teacher\infty Position))$$
3.用SQL语言实现查询：
（1）求各种职称教师的平均年龄。

Select Titile ,  Avg(TAge) 
from Teacher
Group by Title;

（2）查询工资总额超过5000元的教师姓名。

select TName 
from Teacher t , Position  p
where  t.Title = p.Title and (Salary + Allowance) > 5000

4.插入操作：将编号为10130，姓名为刘畅，职称为讲师的记录插入到教师表中。

insert into teacher(TNo, TName,Title) values ('10130','刘畅','讲师');

5.修改操作：将职称为“副教授”的岗位津贴改为1000。

update Position set Allowance = 1000 where Title = '副教授';

6.删除操作：将编号为10130的教师记录删除。

delete from Teacher where TNo = '101130';

7.授权操作：授予用户U1对教师表具有查询权限、对年龄属性有更新权限。

grant select,update(TAge) on table Teacher to U1;

8.收回权限操作：收回用户U1对教师表的年龄属性更新权限

revoke update(TAge) on table Teacher from U1;

9.创建存储过程实现计算全体学生某门功课的平均成绩的功能

命令：（包括存储定义及调用）
delimiter ||
<u> ________</u> p_avgscore(in cna varchar(30),out avgs decimal(3,1) ) -- 定义存储过程
begin 
	select avg(score)  ________ -- 存储平时成绩
 	from sc b,course c
  	where b.cno = c.cno and  ________; -- 指定某门课程
end ||
delimiter ;
 ________p_avgscore('C语言',@avgs);   --调用存储过程
 ________@avgs '平均分';             -- 显示平均成绩
>

答案：

delimiter ||
create procedure p_avgscore(in cna varchar(30),out avgs decimal(3,1) ) -- 定义存储过程
begin 
	select avg(score) into avgs 
	from sc b,course c
	where b.cno = c.cno and cname = cna;
end ||
delimiter ;
call P_avgscore('C语言',@avgs);

10.创建带参数的存储的函数，实现计算全体学生某门功课的平均成绩的功能

命令:包括存储函数的定义以及调用
delimiter ||
 ________ f_avgscore(in cna varchar(30) )    ________decimal(3,1)-- 定义存储过程
begin 
  	 ________ avgs decimal(3,1) --定义变量
  	select  ________  into avgs-- 存储平时成绩
	from sc b,course c   
 	where   ________ and cname = cna; 
    ________ avs;   -- 返回平时成绩
end ||
delimiter ;
——————f_avgscore('C语言');

答案：

delimiter ||
create function f_avgscore(in cna varchar(30) )   returns decimal(3,1)-- 定义存储过程
begin 
  	declare avgs decimal(3,1)
 	select  avg(score) into avgs-- 存储平时成绩
	from sc b,course c
	where   b.cno = c.cno and cname = cna; -- 指定某门课程
    return  avs;
end ||
delimiter ;
select f_avgscore('C语言');

11.创建一个触发器实现更新分数时保留最高分

delimiter ||
create trigger update_score before update on sc  -- 定义触发器
for each row 
begin 
	if(new.score < old.score) then    
		set new.score = old.score;
	end if;
end ||
delimiter ;

12.备份数据库

mysqldump -u user_name -p password db_name > D:\\beifen\\beifen2.sql;

13.恢复数据库

 1.在登录mysql的情况下：  
  source  备份文件 
 2. 在不登录的情况下 
  mysql -u用户名 -p密码 库名 < 备份文件

查询树的启发式优化

（1）选择运算尽可能先做
（2）把投影运算和选择运算同时进行
（3）把投影与其前后的双目运算符结合起来
（4）把某些选择同在它前面执行的笛卡尔积结合起来成为一个连接运算
（5）找出公共子表达式