MySQL约束、设计和多表查询

约束

约束的作用：

对表中的数据进行限制，保证数据的正确性、有效性和完整性。一个表如果添加了约束，不正确的数据将无法插入到表中。约束在创建表的时候添加比较合适。

约束的种类：

1.主键约束：primary key

/*
	主键约束:在添加数据时，约束重复数据的行为
	需要一个字段保证数据是唯一的，所以这个字段的值是不能为null的
	特点：非空并且唯一
	分类：
		自然主键：数据中本身存在一个非空并且唯一的字段 - 不建议使用
		代理主键：字段和数据没有直接联系 id
			int(6) - 自动增长（mysql自带特性） max(id) + 1
				   - 序列（oracle特性 sequence）
            varchar(32) - UUID（）[Java中也有UUID.randomUUID()]
*/
select replace(UUID(),'-','');
-- 表存在时添加主键：前提是这个字段本身的数据是非空且唯一
alter table user2 add primary key (no);
-- 表存在，删除主键
alter table user2 drop primary key;

-- 自动增长：auto_increment
create table user(
	id int(6) primary key auto_increment,
    username varchar(20),
    password varchar(20)
)auto_increment = 1000;
insert into user values(null,'lucy','1234'),
(null,'jack','12345'),
(null,'rose','123456');

2.唯一约束：unique

-- 唯一性约束:unique 自动在这一列中生成索引 - index
-- 在已有的表中添加唯一约束 user-username
alter table user add unique(username);
-- 在已有的表中删除唯一约束（删除对应的索引）
-- 先查询表中的索引
show index from user;
-- username在这里表示索引名
alter table user drop index username;
-- 建表时添加唯一索引
create table user1(
	username varchar(20) unique
);

3.非空约束：not null

-- 非空约束
-- 在已有的表中添加非空约束
alter table user modify password varchar(20) not null;
-- 删除非空约束
alter table user modify password varchar(20);
-- 添加非空约束，同时设置默认值
alter table user modify password varchar(20) not null default '123456';
insert into user(id,username) values(null,'tom');

4.外键约束：foreign key

-- 外键约束:多个表间的关系
-- 一对多
create table stu(
	name varchar(20),
    num int(6),
    sex char(1),
    no int(6) primary key auto_increment
);
insert into stu values('jack',100010,'男',1),
('lucy',100015,'女',2),
('rose',100020,'男',3);
create table course(
	id int(6) primary key auto_increment,
    name varchar(20),
    msg varchar(20),
    stuId int(6)
);
-- 添加外键 constraint(约束) ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名);
alter table course add constraint stu_course_fk foreign key (stuId) references stu(no);

alter table course add foreign key (stuId) references stu(no);
-- 错误，不能在从表存在的情况下删除主表
drop table stu;
-- 错误，不能引用一个不hotel存在的外键值
insert into course values(null,'通信原理','通信原理（I）',4);
-- 正确
insert into course values(null,'高等数学','高等数学（上）',1);
-- 删除外键
show index from course;
alter table course drop foreign key stuId;

alter table emp add constraint emp_dept_fk foreign key (deptno) references dept(deptno);
alter table emp drop foreign key emp_dept_fk;
-- 多对多
create table stu1(
	name varchar(20),
    num int(6),
    sex char(1),
    id int(6) primary key auto_increment
);
insert into stu1 values('jack',100010,'男',1),
('lucy',100015,'女',2),
('rose',100020,'男',3);
create table course1(
	id int(6) primary key auto_increment,
    name varchar(20),
    msg varchar(20)
);
insert into course1 values(null,'高等数学','11223214'),
(null,'大学物理','3214213'),
(null,'大学英语','2785728375');
-- 新建表时添加外键 [CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)
create table sel(
	id int(6) primary key auto_increment,
    s_id int(6),
    c_id int(6),
	constraint sel_stu1_fk foreign key (s_id) references stu1(id),
    constraint sel_course1_fk foreign key (c_id) references course1(id)
);
insert into sel values(null,1,1);
insert into sel values(null,4,1);
insert into sel values(null,1,4);
insert into sel values(null,4,4);

数据规范化

1.什么是范式
好的数据库设计对数据的存储性能和后期的程序开发，都会产生重要的影响。建立科学的，规范的数据库就需要满足一些规则来优化数据的设计和存储，这些规则就称为范式。
2.三大范式
目前关系数据库有六种范式：第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF，又称完美范式)。

满足最低要求的范式是第一范式。在第一范式的基础上进一步满足更多规范要求的称为第二范式，其余范式以次类推。一般来说，数据库只需满足第三范式就行了。

数据库范式:
1NF: 在符合第一范式（1NF）表中的每个域值只能是实体的一个属性或一个属性的一部分。简而言之，第一范式就是无重复的域。
2NF: 在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）
3NF: 在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

函数依赖: 通过一个属性(组)A, 能确定唯一的B属性的值, 称B函数依赖于A
例如: 学号 -> 姓名 学号+课程名称 -> 分数
部分函数依赖: 通过属性组A中的部分属性, 就能确定唯一的B属性的值, 称B部分函数依赖于A
例如: 学号+课程名称 -> 姓名
完全函数依赖: 通过属性组A中的每一个属性一起确定唯一的B属性的值, 称B完全函数依赖于A
例如: 学号+课程名称 -> 分数
传递依赖: 通过属性(组)A确定唯一的B属性的值, 再通过B属性, 确定唯一C属性的值, 称C 传递依赖于A
例如: 学号 -> 系名 -> 系主任

主码: 也叫主属性, 表中其他所有属性的值, 都能通过一个属性(组)来确定, 这个属性(组)就是主码
例如: 学号+课程名称

多表关联

-- emp dept 多表关联
-- 查询所有员工 以及 他们所在的部门名称
select * from emp, dept ; -- 笛卡尔积, 两表数据相乘
-- 乘积筛选后的结果, 两边能匹配上的数据
select * from emp, dept where emp.deptno = dept.deptno;-- 隐式内连接
select * from emp e, dept d where e.deptno = d.deptno;
select e.ename, d.dname from emp e, dept d where e.deptno = d.deptno;

-- 表连接  join
/**
	内连接  [inner] join
    外连接 
	 左外连接  left [outer] join
     右外连接  right [outer] join
     oracle - 全外连接 full [outer] join
     
    连接的选择,主要取决于查询结果,需要哪张表的全部数据 
    需要显示全部数据的表, 称为"驱动表"
 */
-- 内连接
select * from emp e inner join dept d on e.deptno = d.deptno;
-- 左外连接
select * from emp e left outer join dept d on e.deptno = d.deptno;
select * from dept d left outer join emp e on e.deptno = d.deptno;

-- 右外连接
select * from emp e right outer join dept d on e.deptno = d.deptno;

子查询

子查询的概念：
1.一个查询的结果作为另一个查询的条件
2.有查询的嵌套，内部的查询称为子查询
3.子查询要使用括号

-- 子查询，查询中嵌套查询，使用另一个查询的结果
-- 1.子查询的结果是单行单列的结果，值可以直接比较
-- 查询公司中最高薪资的员工姓名和所在部门编号
select max(sal) from emp;
select ename,deptno from emp where sal = (select max(sal) from emp);

-- 2.子查询的结果是单列多行的，集合 in、not in、> any、< all
-- 查询部门平均薪资高于1800的部门中所有员工的信息
select deptno from emp group by deptno having avg(sal) > 1800;
select * from emp where deptno in (select deptno from emp group by deptno having avg(sal) > 1800);

-- 3.子查询的结果是多列多行的，作为一张临时表使用
-- 查询薪资大于所在部门平均薪资的所有员工信息
select deptno,avg(sal) as s_avg from emp group by deptno;
select * from emp e join (select deptno,avg(sal) as s_avg from emp group by deptno) d on e.deptno = d.deptno where e.sal > d.s_avg;

-- 可以使用子查询的子句：from where having select