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. 主键对应:把主键字段同时设置为外键

在这里插入图片描述

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 外键约束

外键约束: 用来约束表中字段书写的内容

约束的作用: 规范数据的编写, 保证数据的有效性和完整性

加入外键约束的方式:

  1. 通过修改表结构添加外键约束
alter table 多表名字 add foreign key(外键字段名) references 一表名字(主键名字);

alter table emp add foreign key(dept_id) references dept(id);
  1. 在建表的时候在约束区域中添加外键约束(几乎不用)
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 * from1,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 | [聚合函数]
from1
[join2 on 连接条件]
[join3 on 连接条件]
where 查询条件
[group by 分组字段 having[分组后条件筛选]]
[order by 排序字段 排序方式]
[limit m,n]

执行顺序:

  1. 先执行from,确定从那些表中查询数据
  2. join操作
  3. 执行where筛选
  4. group by 分组 ,分组函数执行,聚合函数就执行了
  5. having 筛选
  6. select 决定要显示那些字段
  7. distinct操作
  8. order by 排序操作
  9. limit 分页
  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值