1. 约束：唯一，非空，默认值
唯一约束
指定某一列不能出现相同的值
语法
在建表的时候创建
create table 表名 (
字段名 字段类型 unique
)
非空约束
设置某列数据不能为空，必须要输入
语法
create table 表名 (
字段名 字段类型 not null
)
默认值
如果一个字段没有设置它的值，将使用默认值
语法
create table 表名 (
字段名 字段类型 default 默认值
)
2. 外键约束
外键的概念：在一张表建立一个字段关联另一张表的主键，这个字段称为外键。
- 什么是外键：一定是出现在从表中。被主表中主键约束的。
- 创建表的时候，先创建主键，再创建从表。删除表的时候先删除从表，再删除主表。
创建外键约束
语法
新建表时增加外键：
create table 表名 (
外键字段名 字段类型,
foreign key(外键字段名) references 主表(主键)
)
已有表增加外键：
alter table 表名 add constraint 约束名 foreign key(外键字段名) references 主表(主键)
-- 约束名用于后期删除外键约束
删除外键
-- 删除employee表的employee_ibfk_1外键
alter table employee drop foreign key employee_ibfk_1;-- 在employee表情存在的情况下添加外键
alter table employee add foreign key (dept_id) references department(id);
3. 外键约束：级联更新和删除
什么是级联操作
修改主表中主键，或者删除主表中记录的时候，从表中外键也被同时修改，或者删除。
语法
写在外键约束的后面，在创建外键约束的时候创建级联操作
级联操作 | 语法 |
级联更新 | on update cascade |
级联删除 | on delete cascade |
-- 删除外键约束
alter table employee drop foreign key employee_ibfk_1;-- 添加外键约束，级联更新和级联删除
alter table employee add constraint fk_emp_dept
foreign key (dept_id) references department(id) on update cascade on delete cascade;-- delete from employee where id > 6;
select * from employee;-- 把部门表中id等于1的部门改成id等于9
select * from department;
update department set id=9 where id=1;-- 删除部门号是2的部门
delete from department where id=2;
4. 数据库范式：第一范式
什么是范式
一种规则，指导程序员创建表的规则。数据库的范式有6种范式，程序员在设计表的时候，只需要符合三大范式就可以了。
三大范式
分成几个等级，一级比一级要求得严格。满足这些规范的数据库是简洁的、结构明晰的。
满足最低要求的范式是第一范式（1NF），在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般说来，数据库只需满足第三范式(3NF）就行了。
第一范式
概念
原子性：表中每一列都不可以再分割成更小的列。如果一列中包含的数据可以再拆分，不符合第一范式。
第一范式：满足列不可再分。
案例
班级表
学号 | 姓名 | 班级 |
1000 | 张三 | 一年级3班 |
1001 | 李四 | 一年级2班 |
2001 | 王五 | 二年级1班 |
解决方案
学号 | 姓名 | 年级 | 班号 |
1000 | 张三 | 一年级 | 3班 |
5.数据库范式：第二范式
概念
在满足第一范式的基础上，有更多的要求，才是第二范式。表中每一列都必须完全依赖于主键，
示例
借书证表
主键 | 学生证号 | 学生证名称 | 学生证办理时间 | 借书证号 | 借书证名称 | 借书证办理时间 |
1 | A2349U | 张三学生证 | 2018-01-23 | 29374 | 张三借书证 | 2019-03-10 |
6. 数据库范式：第三范式
概念
在满足第二范式的基础上，有更多的要求。 表中每列都直接依赖于主键，而不是通过其它列间接依赖于主键。
依赖关系
所谓传递依赖，指的是如果存在"A → B → C"的决定关系，则C传递依赖于A。
满足第三范式的数据库表应该不存在如下依赖关系：主键列 → 非主键列x → 非主键列y
示例
学生信息表
学号 | 姓名 | 年龄 | 所在学院 | 学院地点 |
1000 | 张三 | 20 | 传智专修学院 | 江苏 |
2000 | 李四 | 19 | 广州美术学院 | 广州 |
存在传递的决定关系：学号 -> 学院 -> 地点
解决方案
从表：有外键 (多方)
主键 | 姓名 | 年龄 | 所在学院ID |
从表 | | | 外键 |
学院ID | 学院名字 | 学院地点 |
主表(1方) | | |
三范式：降低数据的冗余。提高表的科学性设计！指导思想！！
反三范式：为了提高性能。有时候会反三范式设计！
7. 表与表之间的关系概述
一对一
-- 主表
CREATE TABLE stu(
id INT PRIMARY KEY AUTO_INCREMENT, -- 主键
NAME VARCHAR(20)
);
-- 从表
CREATE TABLE info(
id INT PRIMARY KEY AUTO_INCREMENT, -- 主键
address VARCHAR(20),
use_name VARCHAR(10),
weight DOUBLE,
-- 直接把主键约束成外键。 主键也可以是外键。
FOREIGN KEY(id) REFERENCES stu(id)
);
一对多
/*
一对多，一个分类对应多条线路
因为sql中不区分大小写：如果有多个单词，中间使用_分隔
*/
/*
创建旅游线路分类表tab_category
cid旅游线路分类主键，自动增长
cname旅游线路分类名称非空，唯一，字符串100
*/
create table tab_category(
cid int primary key auto_increment,
cname varchar(100) not null unique
);/*
创建旅游线路表tab_route
rid旅游线路主键，自动增长
rname旅游线路名称非空，唯一，字符串100
price价格
rdate 上架时间，日期类型
cid 外键，所属分类
*/
create table tab_route(
rid int primary key auto_increment,
rname varchar(100) not null unique,
price double,
rdate date,
cid int, -- 外键
foreign key(cid) references tab_category(cid)
);
多对多
-- 多对多的关系
/*
创建用户表tab_user
uid用户主键，自增长
username用户名长度100，唯一，非空
password密码长度30，非空
name真实姓名长度100
birthday生日
sex性别，定长字符串1
telephone手机号，字符串11
email邮箱，字符串长度100
*/
create table tab_user(
uid int primary key auto_increment,
username varchar(100) unique not null,
password varchar(30) not null,
name varchar(100),
birthday date,
sex char(1),
telephone varchar(11),
email varchar(100)
);/*
创建收藏表tab_favorite
rid 旅游线路id，外键
date 收藏时间
uid用户id，外键
rid和uid不能重复，设置复合主键，同一个用户不能收藏同一个线路两次
*/
create table tab_favorite (
rid int, -- 线路id的外键
`date` datetime,
uid int , -- 用户id的外键
-- 创建复合主键
primary key(rid, uid),
foreign key(rid) references tab_route(rid), -- 关联了线路的主键
foreign key(uid) references tab_user(uid) -- 关联了用户的主键
);
8.表连接：笛卡尔积和内连接
-- 需求：查询所有的员工和所有的部门
-- 查询孙悟空在哪个部门名字
select * from emp;select * from dept;
-- 需求：查询所有的员工和所有的部门
-- 查询2张表结果是，是2张表记录的乘积，称为笛卡尔积
select * from emp,dept;-- 如何消除笛卡尔积：条件是从表.外键=主表.主键
select * from emp,dept where emp.dept_id = dept.id;
-- 这就是隐式内连接，使用where，没有用到join...on-- 给表起别名
select * from emp e ,dept d where e.dept_id = d.id;
-- 查询孙悟空在哪个部门名字
select * from emp e ,dept d where e.dept_id = d.id and e.id=1;
-- 只显示2列
select e.name 员工名,d.name 部门名 from emp e ,dept d where e.dept_id = d.id and e.id=1;
隐式内连接语法
select 列名 from 左表,右表 where 从表.外键=主表.主键
显式内连接语法
-- 显示内连接, on后面就是表连接的条件
select 列名 from 左表 inner join 右表 on 从表.外键=主表.主键
9. 外连接：左连接和右连接
左连接
语法
select 列名 from 左表 left join 右表 on 从表.外键=主表.主键
概念
左连接：保证左表中所有的数据都出现，如果右表没有对应的记录，使用NULL填充
右连接
语法
select 列名 from 左表 right join 右表 on 从表.外键=主表.主键
概念
右连接：保证右表中所有的数据都出现，如果左表没有对应的记录，使用NULL填充
10.子查询：子查询引入
子查询的概念
- 将一个查询的结果做为另一个查询的条件
- 这是一种查询语句的嵌套，嵌套的SQL查询称为子查询。
- 如果使用子查询必须要使用括号
-- 需求：查询开发部中有哪些员工
select * from emp;select id from dept where name='开发部';
select * from emp where dept_id=1;
-- 写成一句：使用子查询
select * from emp where dept_id=(select id from dept where name='开发部');select * from emp where dept_id=(select id from dept where name='市场部');
子查询: 单行单列的情况
- 如果子查询是单行单列，父查询使用比较运算符：> < =
-- 案例：查询工资最高的员工是谁？
-- 1. 查询最高工资是多少
select max(salary) from emp;
-- 2. 根据最高工资到员工表查询到对应的员工信息
select * from emp where salary=(select max(salary) from emp);-- 查询工资大于"蜘蛛精"的员工
-- 1. 查询蜘蛛精的工资是多少
select salary from emp where name='蜘蛛精';
-- 2. 查询大于这个工资的员工
select * from emp where salary > (select salary from emp where name='蜘蛛精');
子查询: 多行单列的情况
- 多行单列认为是一个数组，父查询使用in /any /all
-- 查询工资大于5000的员工，来自于哪些部门，得到部门的名字
-- 1. 先查询大于5000的员工所在的部门id
select dept_id from emp where salary > 5000;-- 2. 再查询在这些部门id中部门的名字
-- Subquery returns more than 1 row
select * from dept where id=(select dept_id from emp where salary > 5000);
select * from dept where id in(select dept_id from emp where salary > 5000);-- 列出工资高于在1号部门工作的所有员工，显示员工姓名和工资、部门名称。
-- 1. 查询1号部门所有员工的工资，得到多行单列
select salary from emp where dept_id=1;-- 2. 使用大于号不能计算，怎么办
select * from emp where salary > all (select salary from emp where dept_id=1);
-- any表示任何一个，all所有
select * from emp where salary > any (select salary from emp where dept_id=1);
子查询: 多行多列的情况
- 认为它是一张虚拟表，可以使用表连接再次进行多表查询
-- 查询出2011年以后入职的员工信息，包括部门名称
-- 1. 在员工表中查询2011-1-1以后入职的员工
select * from emp where join_date > '2011-1-1';-- 2. 查询所有的部门信息，与上面的虚拟表中的信息组合，找出所有部门id等于的dept_id
select * from dept d inner join (select * from emp where join_date > '2011-1-1') e on d.id = e.dept_id;select * from dept d right join (select * from emp where join_date > '2011-1-1') e on d.id = e.dept_id;
