Mysql之表与表之间关系与多表查询

表与表之间的关系

概念

*一对一关系

1、A表的每一行对应B表的每一行，同理B表的每一行也对应A表的每一行
2、由于该模型下两张表可以合成一张表，所以不需要过多关注

*一对多关系

1、A表的每一行对应B表的多行，反之不可以，一对多一般描述的是包含关系
2、一对多关系是数据分析遇到最多的关系之一

*多对多关系

1、A表的每一行对应B表的多行，反之B表的每一行都对应A的多行
2、多对多关系是数据分析最复杂的关系，这种关系表必须有中间表

部门表和员工表:
演员表 和 角色表：

表关系实现

 一对多关系

######################表关系-一对多关系##############
# 1、创建分类表 -主表
drop table if exists category;
CREATE TABLE if not exists category(
    cid   VARCHAR(32) PRIMARY KEY,
    cname VARCHAR(100)
);

# 2、创建商品表 -从表

-- 创建外键约束-方式1-在建表时直接指定
drop  table if exists product;
CREATE TABLE if not exists product
(
    pid         INT PRIMARY KEY,
    pname       VARCHAR(20),
    price       DOUBLE,
    category_id VARCHAR(32),
    constraint fk_tb_product2 foreign key (category_id) references category(cid)
);

# 创建外键约束-方式2-在建表之后指定
-- 大小写转换 ： Ctrl + Shift  + u
/*
 alter table 从表 add constraint 外键的名字
    foreign key(从表外键列)  references 主表(主表主键列);
 */
alter table product add constraint fk_tb_product
    foreign key(category_id)  references category(cid);

-- 验证有约束的数据操作

-- 添加数据
/*
  1、先要添加主表数据，才能添加从表数据
 */
insert into category values ('c001','电器');
insert into category values ('c002','服装');

INSERT INTO bigdata_db.product(pid,pname,price,category_id) VALUES(1,'联想',5000,'c001');
INSERT INTO bigdata_db.product(pid,pname,price,category_id) VALUES(2,'海尔',3000,'c001');
INSERT INTO bigdata_db.product(pid,pname,price,category_id) VALUES(3,'雷神',5000,'c001');

INSERT INTO bigdata_db.product(pid,pname,price,category_id) VALUES(4,'杰克琼斯',800,'c002');
INSERT INTO bigdata_db.product(pid,pname,price,category_id) VALUES(5,'真维斯',200,'c002');
INSERT INTO bigdata_db.product(pid,pname,price,category_id) VALUES(6,'花花公子',440,'c002');
INSERT INTO bigdata_db.product(pid,pname,price,category_id) VALUES(7,'劲霸',2000,'c002');
INSERT INTO bigdata_db.product(pid,pname,price,category_id) VALUES(8,'海澜之家',1,'c002');

-- 删除数据
/*
  1、必须先删除从表数据，才能删除主表数据
 */

delete from product where pname = '联想';
delete from product where pname = '雷神';
delete from product where pname = '海尔';

delete from product where category_id = 'c001';

delete  from category where cid = 'c001';

-- 删除外键约束
-- alter  table 从表 drop foreign key  外键约束名;
alter  table product drop foreign key  fk_tb_product2;

-- 结论
/*
对于外键约束而言，只是来约束你插入数据和删除数据的行为，对查询不会造成任何影响
 */

*多对多关系

 -- 1、创建左侧主表
 drop table if exists actor;
 create table actor(
     actor_id int primary key ,
     actor_name varchar(20)
 );
 -- 2、创建右侧主表
 drop table if exists role;
 create table role(
     role_id int primary key ,
     role_name varchar(20)
 );
 -- 3、创建中间从表
 create table actor_role(
     actor_id int,
     role_id int
 );
 
 -- 4、创建左侧外键约束1
 alter table actor_role add constraint fk_1 foreign key(actor_id)
 references actor(actor_id);
 -- 5、创建右侧外键约束2
 
 alter table actor_role add constraint fk_2 foreign key(role_id)
 references role(role_id);
 
 
 
 -- 6、验证-数据的添加
 /*
    1、左侧主表和右侧主表的数据可以随便添加
  */
 
 -- 添加左侧主表数据
 insert into actor values
                       (1,'王宝强'),
                       (2,'杨幂'),
                       (3,'贾乃亮'),
                       (4,'迪丽热巴'),
                       (5,'胡歌');
 
 -- 添加右侧主表数据
 insert into role values
                       (1,'皇帝'),
                       (2,'太监'),
                       (3,'宫女'),
                       (4,'大臣'),
                       (5,'皇后');
 
 -- 添加中间表
 insert into actor_role values
                       (1,2),
                       (1,4),
                       (2,5),
                       (4,3),
                       (5,4);

多表查询

查询方式

  1、内连接查询
    1.1 内连接求两张表的交集

  2、外连接查询
    2.1 左外连接
    2.2 右外连接

内连接查询

#方式1
seelct * from A,B where 条件;

#方式2
seelct * from A inner jon B on 条件;
seelct * from A  jon B on 条件;

/*
  1、内连接查询
    1.1 内连接求两张表的交集

  2、外连接查询
    2.1 左外连接
    2.2 右外连接
 */

-- 2.1 内连接查询
/*
 #方式1
seelct * from A,B where 条件;

#方式2
seelct * from A inner jon B on 条件;
seelct * from A  jon B on 条件;
 */
-- 方式1
alter  table product drop foreign key  fk_tb_product2;
select * from category,product;  #笛卡尔集，将两张表进行相乘(表的行数=A表行数 * B表行数)
select * from category,product where  cid = category_id;

-- 如果多张表中有同名的字段，则字段名前边必须加表名 : 表名.列名
select *
from actor,actor_role,role
where actor.actor_id = actor_role.actor_id  and actor_role.role_id = role.role_id;

-- 可以给表起别名
select *
from actor as a,actor_role as ar,role as r
where a.actor_id = ar.actor_id  and ar.role_id = r.role_id;

-- 方式2

select * from category inner join product;  #笛卡尔集，将两张表进行相乘(表的行数=A表行数 * B表行数)

select * from category join product on  cid = category_id;

-- 如果多张表中有同名的字段，则字段名前边必须加表名 : 表名.列名
select *
from actor,actor_role,role
where actor.actor_id = actor_role.actor_id  and actor_role.role_id = role.role_id;

-- 可以给表起别名
select *
from actor as a,actor_role as ar,role as r
where a.actor_id = ar.actor_id  and ar.role_id = r.role_id;

select
    *
from actor a
     join actor_role ar on  a.actor_id = ar.actor_id
     join  role r  on ar.role_id = r.role_id;

-- 手拉手join
# select
#     *
# from A1
#     join A2 on A1和A2关联条件
#     join A3 on A2和A3关联条件
#     join A4 on A3和A4关联条件
#     join A5 on A4和A5关联条件;
#
#
# -- 小弟和大哥join
# select
#     *
# from A1
#     join A2 on A1和A2关联条件
#     join A3 on A1和A3关联条件
#     join A4 on A1和A4关联条件
#     join A5 on A1和A5关联条件

-- 省表
create table province(
    pid int primary key ,
    name varchar(20)
);
-- 市表
create table city(
    cid int primary key ,
    name varchar(20),
    pid int
);

-- 区表
create table county(
    id int primary key ,
    name varchar(20),
    cid int
);

select
*
from province p
    join  city c on p.pid = c.pid
    join  county c2 on c.cid = c2.cid;

外连接查询

介绍

1、外连接查询
左外连接： left join
   -- 以左表为主，一定会把左表的数据全部输出，右表有交集的数据就输出，没有交集的数据就输出null
右外连接： right join

操作

-- 2.2  外连接查询
/*
 1、外连接查询
左外连接： left join
   -- 以左表为主，一定会把左表的数据全部输出，右表有交集的数据就输出，没有交集的数据就输出null
右外连接： right join
 */

-- 左外连接
select
*
from category
    left join product p on category.cid = p.category_id;

/*
  select
    *
  from  A1
    left join A2 on 条件1
    left join A3 on 条件2
    left join A4 on 条件3
    left join A5 on 条件3
 */

-- 右外连接

select
*
from category
    right join product p on category.cid = p.category_id;
    
    
-- 统计每种分类对应商品的个数
/*
   电器  3
   服装  5
   化妆品 0
 */
select
 cname,
 count(pname) as total
from category c
    left join  product p  on c.cid = p.category_id
group by cname
order by  total desc;