MySql之多表
一、多表关系
1.1 多表关系概述
现实生活中,实体与实体之间肯定是有关系的,所以设计表时也应该体现出表与表之间的关系。
表与表之间的关系:
- 一对多,如一个部门对应多个员工
- 多对一,如多个订单对应一个用户
- 多对多,如一个老师对应多个学生,一个学生对应多个老师
- 一对一,如一个公民对应一张身份证
1.2 多表关系的实现
1.2.1 一对多&多对一
在一对多中,把一的一方称之为一表或者主表, 把多的一方称之为多表或者从表。
实现一对多的方式是在从表上添加外键。
外键:从表中有一列,该列的取值只能来源于主表的主键或者是null。
如商品和分类:
代码实现:
#一对多: 商品和分类 商品表product
CREATE TABLE product(
pid INT PRIMARY KEY ,
pname VARCHAR(32),
cid INT
);
#category 分类表
CREATE TABLE category(
cid INT PRIMARY KEY ,
cname VARCHAR(32)
);
#分类表
INSERT INTO category VALUES(1, '生活类');
INSERT INTO category VALUES(2, '科技类');
#商品表
INSERT INTO product VALUES(1, '牙膏' , 1);
INSERT INTO product VALUES(2, '牙刷' , 1);
INSERT INTO product VALUES(3, '手机' , 2);
INSERT INTO product VALUES(4, '电脑' , 2);
SELECT * FROM category;
SELECT * FROM product;
1.2.2 一对一
在开发中一对一不常见。
常见的解决方案:
- 字段不多的话,两个实体可以放到一个表中
- 唯一外键对应:从表中添加外键字段,给外键字段添加非空和唯一约束
- 主键对应:把主键字段同时设置为外键
1.2.3 多对多
通过第三张中间表,中间表至少包含两列,这两列作为中间表的外键,分别关联两张表的主键。
多对多可以看成两个一对多,任何一方和中间表的关系都是一对多,如果没有中间表,则两张表没有关系。
代码实现:
#学生表
CREATE TABLE student(
sid INT PRIMARY KEY ,
sname VARCHAR(32)
);
#课程表 class
CREATE TABLE class(
cid INT PRIMARY KEY ,
cname VARCHAR(32)
);
#中间表
CREATE TABLE student_class(
sid INT ,
cid INT
);
INSERT INTO student VALUES(1, 'jack');
INSERT INTO student VALUES(2, 'rose');
INSERT INTO class VALUES(1, 'java');
INSERT INTO class VALUES(2, 'ui');
INSERT INTO student_class VALUES(1,1);
INSERT INTO student_class VALUES(1,2);
INSERT INTO student_class VALUES(2,1);
INSERT INTO student_class VALUES(2,2);
1.3 外键约束
外键约束: 用来约束表中字段书写的内容
约束的作用: 规范数据的编写, 保证数据的有效性和完整性
加入外键约束的方式:
- 通过修改表结构添加外键约束
alter table 多表名字 add foreign key(外键字段名) references 一表名字(主键名字);
alter table emp add foreign key(dept_id) references dept(id);
- 在建表的时候在约束区域中添加外键约束(几乎不用)
create table 表名(
字段名称 类型,
字段名称 类型,
constraint FK_ORDERS_USERID foreign key (user_id) references user(id)
关键字 外键名称(自己起) 外键(关键字) 外键字段名称 关联(关键字) 主表名称 主表主键
);
-- 学生课程中间表
create table stu_course(
sc_id int primary key auto_increment,
s_id int,
c_id int,
constraint FK_STU_COURSEID foreign key (s_id) references stu(id),
constraint FK_STU_COURSEID foreign key (c_id) references course(id)
);
外键约束的特点:
- 主表中不能删除已被子表引用的数据
- 从表中不能添加主表中不存在的数据
- 外键的值可以为null
二、多表操作
2.1 增操作&改操作
-
主表:单表操作,不考虑从表
-
从表:可以提供主表信息
如学生和班级,一个班级对应多个学生,一个学生对应一个班级;添加班级时不会考虑学生信息,但学生入学或转班需要添加班级信息。
2.2 删除操作
-
主表:
有从表引用: - 从表数据在主表删除之后还有保留的价值:先把从表中引用的数据解除引用,再删除主表 - 从表数据在主表删除之后没有保留的价值:先删除从表数据,再删除主表数据 没有从表引用:单表操作
-
从表:单表操作
2.3 多表查询
多表查询:同时查询多张表,返回需要的数据
2.3.1 笛卡尔积
多表查询时左表的每条数据和右表的每条数据组合,这种效果称为笛卡尔积,是多表查询的基础。
格式:
select * from 表1,表2
select * from emp,dept
结果:
dept表中有4条记录,emp表中有6条记录,笛卡尔积的结果是 4*6=24 条记录
2.3.2 内连接
内连接是在在笛卡尔积的基础上进行筛选,找到满足条件的数据。
显示内连接:
select * | 字段
from a [inner] join b
on a和b关联条件 [inner] join c on c和上面结果(ab的结果)的关联条件
where 其他条件
-- 查询张三的 id,姓名,性别,工资和所在部门名称
SELECT emp.id,emp.name,emp.gender,emp.salary,dept.name
FROM emp
JOIN dept
ON emp.dept_id = dept.id
WHERE emp.name = '张三' ;
-- 为了方便写sql语句,可以给表起别名 : 表名 [as] 别名
SELECT e.id,e.name,gender,salary,d.name
FROM emp e
JOIN dept d
ON e.dept_id = d.id
WHERE e.name = '张三';
隐式内连接:
select * | 字段
from a,b,c
where 关联条件和其他查询条件
SELECT e.id,e.name,gender,salary,d.name
FROM emp e,dept d
WHERE e.dept_id = d.id AND e.name = '张三';
2.3.3 外连接
外连接分为左外连接和右外连接。
左外连接:先展示左边表中的所有数据,然后根据关联条件去查询右表中的数据,右表中的数据如果满足条件就会展示,否则就会显示null。
select * | 字段
from a
left [outer] join b
on a和b的关联条件
where 其他查询条件
-- 查询所有员工信息及对应的部门名称
SELECT e.*,d.name
FROM emp e
LEFT JOIN dept d
ON e.dept_id = d.id
右外连接 : 先展示右边表中的所有数据,然后根据关联条件去查询左表中的数据,左表中的数据如果满足条件就会展示,否则就会显示null。
select * | 字段
from a
right [outer] join b
on a和b的关联条件
where 其他查询条件
-- 查询所有部门及对应的员工信息
SELECT d.*,e.*
FROM emp e
RIGHT JOIN dept d
ON e.dept_id = d.id
-- 右外转换成左外
SELECT d.*,e.*
FROM dept d
LEFT JOIN emp e
ON e.dept_id = d.id
2.3.4 外链接和内连接的区别
2.3.5 子查询
当一个查询需要依赖于另外一个查询的结果的时候,我们就把另外的这个查询称之为子查询。
即一个子查询的结果可以作为另一个查询语句的一部分。
子查询的查询结果:
- 单行单列的值:一般作为条件使用
- 多行单列的值:一般作为条件使用
- 多行多列的值:作为临时表使用
-- 1 查询工资最高的员工是谁?
#子查询:将一条sql语句的执行结果 作为了另一条sql语句的一部分(作为了条件)
#1.1 查询最高工资
INT money = SELECT MAX(salary) FROM emp;
#1.2 查询员工
SELECT * FROM emp WHERE salary = money;
SELECT * FROM emp WHERE salary = (SELECT MAX(salary) FROM emp);
#作为表存在
#查询出2011年以后入职的员工信息,包括部门名称
SELECT * FROM emp e
INNER JOIN dept d ON e.dept_id = d.id
WHERE e.join_date > '2011-01-01';
SELECT * FROM (
SELECT em.name,em.salary,em.join_date,em.dept_id
FROM emp em
WHERE em.join_date>'2011-01-01'
) e
INNER JOIN dept d ON e.dept_id = d.id;
2.3.6 自关联
关联查询的表还是本身,这种情况我们称之为自关联
查询当前员工和上级领导的名字:
# emp e 普通员工 emp em 领导
SELECT e.id, e.ename , e.mgr , em.id , em.ename FROM emp e
LEFT JOIN emp em ON e.mgr=em.id;
2.4 SQL执行顺序
select [distinct] 字段1,字段2 | [聚合函数]
from 表1
[join 表2 on 连接条件]
[join 表3 on 连接条件]
where 查询条件
[group by 分组字段 having[分组后条件筛选]]
[order by 排序字段 排序方式]
[limit m,n]
执行顺序:
- 先执行from,确定从那些表中查询数据
- join操作
- 执行where筛选
- group by 分组 ,分组函数执行,聚合函数就执行了
- having 筛选
- select 决定要显示那些字段
- distinct操作
- order by 排序操作
- limit 分页