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是外码，被参照表是Massageconstraint 自命名 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 asselect year,semester,title,a.id i_id,d.namei_name,b.id s_id,c.name s_namefrom 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 takesselect id,course_id,sec_id,semester,year,nullfrom student a,section bwhere a.name='Aufr' and b.year = 2010 and b.semester= 'Fall';
删除信息
delete from 表名 where 条件;

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

delete from takes awhere exists(select 1 from student b where a.id=b.idand b.dept_name = 'Comp. Sci.' and b.name ='Ploski')and a.grade = 'C-';
更新信息
update 表名 set 字段=new字段 where 条件;

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

update instructorset salary =case when salary *1.1 > (select avg(salary) frominstructor) then(select avg(salary) frominstructor)else salary * 1.1endwhere dept_name = 'Comp. Sci.'and salary < (select avg(salary) from instructor);
查询常用函数
avg() :求平均值distinct : 去重max() : 求最大值min() : 求最小值sum() : 求和count() : 求记录的行数count(*) : 包括nullcount(字段) : 该字段中不为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 rank1from scores//over : 在什么条件之上partition by字段 : 按字段划分
idscorerow_number1dense_rank1rank1
0199111
0399211
0288323
例题：使用标量子查询，查询各院开设课程修课人数最多的前三门课程
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) rkfrom ta)select * from tbwhere rk a.cnt) rkfrom 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 cwhere 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 resultEND;

例题：查询各课程各级成绩人数，显示课程名称，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) totalfrom course natural left outer join takesgroup by title

范式

超码：超码能唯一确定一个元组
候选码：最小的超码、
1NF：非主属性部分依赖于R的候选码
2NF：非主属性完全函数依赖于R的候选码，存在传递依赖（即非主属性由另一个非主属性决定）
3NF：左边是超码或者右边是主属性
BCNF: 左边都是超码
无损分解

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

判断方法： 图示法

保持函数依赖

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

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

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

result:=α;while(result发生变化)dofor each 分解后的Rit=(result ∩ Ri)+ ∩ Riresult=result ∪ t如果result中包含了β的所有属性，则函数依赖α→β成立，这时分解是保持依赖的
分解为符合3NF标准

判断是否为3NF

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

去重

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

例题：

分解为符合BCNF标准

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

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

例题：

例题：

例题：

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

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

数据库设计