约束，表关系，多表查询

1 约束

1.1 常见约束

• 非空约束：关键字是 NOT NULL
• 唯一约束：关键字是 UNIQUE
• 主键约束：关键字是 PRIMARY KEY
• 默认约束：关键字是 DEFAULT
• 外键约束：关键字是 FOREIGN KEY

1.1.1 举例

#常见约束
create table emp(
    id int primary key auto_increment, -- 主键约束且自动增长
    ename varchar(50) not null unique ,-- 非空约束，唯一约束
    joindate date not null , -- 非空约束
    salary double(7,2) not null , -- 非空约束
    bonus double(7,2) default 0 -- 默认约束
);

1.2 外键约束

外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性。
语法

1.2.1 创建表时添加外键约束

create table 表名(
    列名 数据类型,
	...
	[constraint] [外键名称] foreign key(外键列名) references 主表(主表列名)
)

1.2.2 建完表后添加外键约束

alter table 表名 add constraint 外键名称 foreign key(外键字段名称) references 主表名称(主表列名称)

1.2.3 删除外键约束

alter table 表名 drop foreign key 外键名称

1.2.4 举例

#创建员工表和部门表，并添加上外键约束
#删除表
drop table if exists emp;
drop table if exists dept;
#部门表
create table dept(
    id int primary key auto_increment,
    dep_name varchar(20),
    addr varchar(20)
);
#员工表
create table emp(
    id int primary key auto_increment,
    name varchar(20),
    age int,
    dep_id int
    #添加外键 dep_id,关联 dept 表的id主键
#     constraint fk_emp_dept foreign key(dep_id) references dept(id)
);
# 建完表后，添加外键
alter table emp add constraint fk_emp_dept foreign key (dep_id) references dept(id);
#添加数据
#添加2个部门
insert into dept(dep_name, addr) values ('研发部','广州'),('销售部','深圳');
#添加员工，dep_id 表示员工所在的部门
insert into emp(name,age,dep_id) values
                                     ('张三', 20, 1),
                                     ('李四', 20, 1),
                                     ('王五', 20, 1),
                                     ('赵六', 20, 2),
                                     ('孙七', 22, 2),
                                     ('周八', 18, 2);
#此时删除研发部这条数据，会发现无法删除
delete from dept where dep_name='研发部';
#删除外键
alter table emp drop foreign key fk_emp_dept;

2 数据库设计

表关系

2.1 一对多

• 如：部门和员工
• 一个部门对应多个员工，一个员工对应一个部门
  实现方式
  在多的一方建立外键，指向一的一方的主键

2.1.1 举例

# 删除表
drop table if exists tb_emp;
drop table if exists tb_dept;
# 部门表
create table tb_dept(
    id int primary key auto_increment,
    dep_name varchar(20),
    addr varchar(20)
);
# 员工表
create table tb_emp(
    id int primary key auto_increment,
    name varchar(20),
    age int,
    dep_id int,
    # 添加外键 dep_id，关联 dept表的id主键
    constraint fk_emp_dept foreign key (dep_id) references tb_dept(id)
);

2.1.2 关系图

2.2 多对多

• 如：商品和订单
• 一个商品对应多个订单，一个订单包含多个商品
  实现方式
  建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

2.2.1 举例

# 删除表
drop table if exists tb_order_goods;
drop table if exists bt_order;
drop table if exists tb_goods;
# 订单表
create table tb_order(
    id int primary key auto_increment,
    payment double(10, 2),
    payment_type tinyint,
    status tinyint
);
# 商品表
create table tb_goods(
    id int primary key auto_increment,
    title varchar(100),
    price double(10, 2)
);
# 订单商品中间表
create table tb_order_goods(
    id int primary key auto_increment,
    order_id int,
    goods_id int,
    count int
);
# 建完表后，添加外键
alter table tb_order_goods add constraint fk_order_id foreign key (order_id) references tb_order(id);
alter table tb_order_goods add constraint fk_goods_id foreign key (goods_id) references tb_goods(id);

2.2.2 关系图

2.3 一对一

• 如：用户和用户详情
• 一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用户提升查询性能
  实现方式
  在任意一方加入外键，关联另一方主键，并且设置外键为唯一(UNIQUE)

2.3.1 举例

# 删除表
drop table if exists tb_user_desc;
drop table if exists tb_user;
#创建用户详情表
create table tb_user_desc(
    id int primary key auto_increment,
    city varchar(20),
    edu varchar(10),
    income int,
    status char(2),
    des varchar(100)
);
# 创建用户表
create table tb_user(
    id int primary key auto_increment,
    photo varchar(100),
    nickname varchar(50),
    age int,
    gender char(1),
    desc_id int unique,
    -- 添加外键
    constraint fk_user_desc foreign key (desc_id) references tb_user_desc(id)
);

2.3.2 关系图

3 多表查询

3.1 内连接查询

• 语法
  – 隐式内连接

select 字段列表 from 表1,表2... where 条件;

– 显示内连接

select 字段列表 from 表1 [inner] join 表2 on 条件;

• 案例

# 隐式内连接
select * from emp e,dept d where e.dep_id = d.did;

执行结果如下：

3.2 外连接查询

• 语法
  – 左外连接

select 字段列表 from 表1 left [outer] join 表2 on 条件;

• 案例

# 查询emp表所有数据和对应的部门信息(左外连接)
select * from emp e left join dept d on e.dep_id = d.did;

执行结果如下：

结果显示查询到了左表(emp)中所有的数据及两张表能关联的数据。
– 右外连接

select 字段列表 from 表1 right [outer] join 表2 on 条件;

• 案例

# 查询dept表所有数据和对应的员工信息(右外连接)
select * from emp e right join dept d on e.dep_id = d.did;

执行语句结果如下：

结果显示查询到了右表(dept)中所有的数据及两张表能关联的数据。
要查询出部门表中所有的数据，也可以通过左外连接实现，只需要将两个表的位置进行互换

select * from dept d letf join emp e on e.dep_id = d.did;

3.3 子查询

• 概念
  查询中嵌套查询，称嵌套查询为子查询
• 案例

# 需求：查询工资高于猪八戒的员工信息
# 第一步：先查询出来猪八戒的工资
select salary from emp where name = '猪八戒';
# 第二步：查询工资高于猪八戒的员工信息
select * from emp where salary > (select salary from emp where name = '猪八戒');

• 子查询根据查询结果不同，作用不同
  – 子查询语句结果是单行单列，子查询语句作为条件值，使用 = != > < 等进行条件判断
  – 子查询语句结果是多行单列，子查询语句作为条件值，使用 in 等关键字进行条件判断
  – 子查询语句结果是多行多列，子查询语句作为虚拟表
• 案例

# 查询 '财务部' 和 '市场部'所有的员工信息
# 第一步：查询 '财务部' 或者 '市场部' 所有的员工的部门did
select did from dept where dname = '财务部' or dname = '市场部';
# 第二步：查询部门id为'财务部' 或者 '市场部' 的员工信息
select * from emp where dep_id in (select did from dept where dname = '财务部' or dname = '市场部');
 
# 查询入职日期是'2011-11-11' 之后的员工信息和部门信息
# 第一步：查询入职日期是 '2011-11-11' 之后的员工信息和部门信息
select * from emp where join_date > '2011-11-11';
# 第二步：将第一步的结果作为虚拟表和dept表进行内连接查询
select * from (select * from emp where join_date > '2011-11-11') t1,dept where t1.dep_id = dept.did;