数据库和mysql小记

---

基本语法：

建表和完整性约束:

create table student( 	 -- 建表
    sno int unique, -- 属性名 类型 列级（属性上的）完整性约束...
    cno int,
	sname char(6),
    class char(4),
    Foreign key(cno) references class(cno),
    Primary key(sno, cno) -- 最后两行是表级（元组上的）完整性约束，主键有多个时，必须作为表级完整性来写
);
drop table student [cascade|restrict]; -- 删除，其他东西的删除往往也是这个格式

这样创建的是一个表的结构，还没有具体内容

列级（属性级）完整性约束包括NOT NULL UNIQUE CHECK(条件)

CHECK 也可以定义表级完整性约束，对元组各个属性之间的关系作出要求

完整性约束命名：就是在约束前面加上 constraint 名字

断言是一种更高级别的约束，可以定义表与表之间的约束关系：

create assertion 名字
	check(...);

任何操作都需要检查所有和它有关的完整性约束，若不符合则进行违约处理：拒绝，级联更新或置空

查询：

select sno, student.sname			-- 投影，保留了sno和sname两属性列
from student, class					-- 连接
where student.sname = class.sname;	-- 选择

多表查询时，属性名唯一的可以直接写，有冲突的需要 表名.属性名 这样；

上面是一个典型的自然连接，在做等值连接的同时，仅select了其中一个同名属性列，生成的表将含 sname 而非 student.sname 或 sname(1)

select sno, sum(score)
from student;

使用聚集函数时，如果没有跟上 group by 分组，则会把所有东西拼成一组，像上面那样得到的结果就没什么实际含义；

select class, sum(score) as total -- as total 造了一个别名，输出时将名为total，as可略
from student
group by class
having avg(score) > 80;
-- group by紧跟着一个having，表示仅筛选满足条件的组
-- 这里面，当然，也可以用聚集函数

这样即可对 class 属性列分组，相同的为一组，聚集函数对每个组求一次值。
这里便能得到各个班级的总分；

查询聚集函数得到的结果，可以直接当成数值来用：

-- 嵌套查询，查询平均分之上的同学
select *
from student
where score >= (
		select avg(score)
		from student
	);

修改：

修改表结构：

alter table class ...; -- 注意alter只能根一个修改语句，不是语句块

add teacher char(8) unique;
drop teacher;
change 列名 新列名 新类型; 	-- 这里和书上不一样
add constraint 表级完整性约束;
drop constraint 表级完整性约束名;

修改数据：

-- 插入
insert into student(a, b, c)
values(1, 2001, "tyj", ), (..), (..);
-- 值的顺序和上面的列a, b, c列一一对应
-- 可不提供(a, b, c)这样的顺序，按照默认顺序
-- 可以一次插入多个值

-- 删除
delete
from student
where score < 60; -- 删除60分以下的学生

-- 修改
update student
set cno = 2
where cno = 1;

授权：

Grant update(sno) -- 权限名: select(查询),insert(插入),update(修改),all privileges等
on table student
to u1; -- 用户/角色，public表示所有用户
[with grant option] -- 加上表示允许该用户传递此权限

Revoke update(sno)
on table student
from u1 [cascade|restrict];	-- 和删除类似，级联即同时回收u1传递出去的权限，默认cascade

【角色】是权限的集合——比如职工，经理等角色；可以在给角色分配好权限后，给多个用户直接分配这一角色的权限。

Grant 角色1,角色2...
to 角色/用户
[with grant option]

范式：

好东西：https://blog.csdn.net/yangbodong22011/article/details/51619590

• 候选码：能唯一标识一个元组，且其子集不能的属性组，一个表可以有多组候选码
• 选择其中的一组候选码，即作为主码
• 所有候选码的属性称为主属性（个人感觉定义最奇怪的地方，也是导致BCNF理解困难的原因）

简单概括：

• 1NF：数据项不可分割
• 2NF：非主属性对主属性不能存在部分函数依赖
• 3NF：非主属性对主属性不能存在传递函数依赖
• BCNF：任一主属性对其他候选码不存在部分和传递依赖
  • 上面那句话不好理解，个人有另一种理解：
  • BCNF是3NF的扩展：如果有多个候选码，则把其中任一组看成主属性，其他看成非主属性（按定义，本来应是主属性）后，仍然满足3NFs
• 4NF：属性间不存在非平凡且非函数的多值依赖（我暂且记个定义）

一些细节：

代码不加分号也能过：分号有类似分区的作用，出现错误时，其他加了分号的部分仍然正常执行

空值的判断是 is [not] null