数据库期末复习（SQL,范式，数据库设计例题）

SQL语句

创表

create table 表名(
    id number(10) primary key not null, //列名 类型 主键 不为空
    name varchar(20) not null, //varchar (可变长度，指定最大长度20字节) 不为空
    mobile varchar(11) check(length(mobile)=11) unique //约束长度等于11 取唯一值
    constraint 自命名 foreign key(address) references Massage(address) //address是外码，被参照表是Massage
    constraint 自命名 primary key(mobile)
)
//常用数据类型
// varchar(size) : 存储可变长度字符串， size 规定字符串最大长度
// number(m,n)  : m 表示总长度，n表示小数位的精度，只有m表示可以存入最大为m位的整数
// date : 表示日期和时间，7个字节固定宽度，有7个属性，分别为世纪-年-月-日-小时-分-秒

视图

create view 视图名 as select ....;
drop view 视图名;
view 和 with as 的区别：view 创建后不删除就一直都还在，with as 执行后就不存在了

例题：建立一个视图V1，显示老师与学生的授课关系，包括年份，学期，课程名称，老师ID，老师姓名，学生ID，学生姓名

create view v1 as
select year,semester,title,a.id i_id,d.name
i_name,b.id s_id,c.name s_name
from takes a join teaches b 
using(course_id,sec_id,year,semester)
join student c on(a.id = c.id)
join instructor d on (b.id = d.id)
join course using(course_id)

向表中添加或删除约束

// 添加主键约束
alter table 表名 add constraint 自定义主键名 primary key(字段)
//添加外键约束
alter table 表名 add constraint 自定义外键名 foreign key(外键字段) 
references 表名(字段)
//删除主键约束
alter table 表名 drop constraint 主键名
//删除外键约束
alter table 表名 drop constraint 外键名

添加信息

insert into 表名 values(值1,值2,....);
insert into 表名(字段1，字段3) values(值1，值3);
insert into 表名 select ...;

例题：给“Aufr”同学选上2010年秋季学期的所有课程

insert into takes
select id,course_id,sec_id,semester,year,null
from student a,section b
where a.name='Aufr' and b.year = 2010 and b.semester
= 'Fall';

删除信息

delete from 表名 where 条件;

例题：删除“Comp. Sci.”学院“Ploski”同学，所有成绩为’C-’的选课记录

delete from takes a
where exists(select 1 from student b where a.id=b.id
and b.dept_name = 'Comp. Sci.' and b.name ='Ploski')
and a.grade = 'C-';

更新信息

update 表名 set 字段=new字段 where 条件;

例题： 将“Comp. Sci.” 学院所有低于学校平均工资老师的涨薪10%，但是最高不能超过学校平均工资

update instructor
set salary =
case when salary *1.1 > (select avg(salary) from
instructor) then
(select avg(salary) from
instructor)
else salary * 1.1
end
where dept_name = 'Comp. Sci.'
and salary < (select avg(salary) from instructor);

查询常用函数

avg() :求平均值
distinct : 去重
max() : 求最大值
min() : 求最小值
sum() : 求和
count() : 求记录的行数
count(*) : 包括null
count(字段) : 该字段中不为null 的行数
group by 字段 : 按字段分组
order by 字段,字段 : 按字段排序，desc 降序，默认为升序
union all 合并不去重
//窗口函数
//排序rank(),dense_rank(),row_number()
// row_number 不存在并列，不会有相同的数字
//dense_rank 存在并列，不会跳数字
//rank() 存在并列，会出现数字的中断
select id,score,row_number() over (order by score desc) as row_number,
dense_rank() over (order by score desc) as dense_rank1,
rank() over (order by score desc) as rank1
from scores
//
over : 在什么条件之上
partition by  字段 : 按字段划分

idscorerow_number1dense_rank1rank1019911103992110288323

例题：使用标量子查询，查询各院开设课程修课人数最多的前三门课程

with ta as
(select dept_name,course_id,title,count (distinct id) cnt
 from course natural join takes
 group by dept_name,course_id,title),
tb as(
select dept_name,course_id,title,cnt,rank() over(partition 
by dept_name order by cnt desc) rk
from ta)
select * from tb
where rk<=3;
//不使用窗口函数
with ta as
(select dept_name,course_id,title,count(distinct id) cnt
from course natural join takes
group by dept_name,course_id,title),
tb as
(select dept_name,course_id,title,cnt,
(select count(*)+1 from ta b
where a.dept_name = b.dept_name and b.cnt > a.cnt) rk
from ta a)
select * from tb where rk <= 3 order by dept_name,rk

exists 和 not exists 的使用

eixsts()会返回具体的查询到的数据，只是会返回true或者false，如果外层sql的字段在子查询中存在则返回true，不存在则返回false

双not exists 的使用

例题：查询修了ID=‘82402‘同学所有选修课程的同学的ID，姓名

select id, name
//同学
from student a
//不存在
where not exists(
//这样一门课程
    select 1 from takes b
//这门课程82402选了，但她没选
where id='82402' and not exists(
    select 1 from takes c
where a.id=c.id and b.course_id=c.course_id)
);

case 的使用

CASE语句遍历条件并在满足第一个条件时返回一个值（如IF-THEN-ELSE语句）。因此，一旦条件为真，它将停止读取并返回结果。如果没有条件为 true，则返回 ELSE 子句中的值。

如果没有其他部分，并且没有条件为 true，则返回 NULL。

CASE
 WHEN condition1 THEN result1
 WHEN condition2 THEN result2
 WHEN conditionN THEN resultN
 ELSE result
END;

例题：查询各课程各级成绩人数，显示课程名称，A级人数，B级人数，C级人数，总人数

select title,
count(case when grade like 'A%' then 1 else null end) A,
count(case when grade like 'B%' then 1 else null end) B,
count(case when grade like 'C%' then 1 else null end) C,
count(grade) total
from course natural left outer join takes
group by title

范式

超码：超码能唯一确定一个元组

候选码：最小的超码、

1NF：非主属性部分依赖于R的候选码

2NF：非主属性完全函数依赖于R的候选码，存在传递依赖（即非主属性由另一个非主属性决定）

3NF：左边是超码或者右边是主属性

BCNF: 左边都是超码

无损分解

定义：无损连接是指分解后的关系通过自然连接可以恢复成原来的关系，即通过自然连接得到的关系与原来的关系相比，既不多出信息、又不丢失信息。

判断方法： 图示法

保持函数依赖

如果F上的每一个函数依赖都在其分解后的某一个关系上成立，则这个分解是保持依赖的（充分条件）。

如果上述判断失败，并不能断言分解不是保持依赖的，因为上面只是充分条件，还要使用下面的算法来做进一步判断。

对F上的每一个α→β使用下面的过程:

result:=α;
while(result发生变化)do
    for each 分解后的Ri
        t=(result ∩ Ri)+ ∩ Ri
        result=result ∪ t
如果result中包含了β的所有属性，则函数依赖α→β成立，
这时分解是保持依赖的

分解为符合3NF标准

判断是否为3NF

求正则覆盖，求候选码，进行分解

去重

判断有无候选码，无则加上

例题：

分解为符合BCNF标准

函数依赖中非平凡函数依赖的左边都是超码

先判断左边是否为超码，不是则分解为符合超码的集合

例题：

例题：

例题：

正则覆盖和最小函数依赖的区别：最小覆盖的右端必然只有一个属性

推荐课程: 录课｜数据库系统概念-范式3NF BCNF分解习题

数据库设计