mysql多表查询

最新推荐文章于 2024-04-13 22:34:49 发布
最新推荐文章于 2024-04-13 22:34:49 发布
阅读量180 收藏
点赞数
分类专栏： MySQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_41821006/article/details/102388802
版权

文章目录

第一章 多表关系实战

1.1 实战1：省和市

1.1.1 方案1：多张表，一对多

  1. 省份和城市的1对多关系
    在这里插入图片描述

  2. 建表原则: 从表使用主表的主键作为外键

  3. 主表:省份表province

    • 创建主表 :省份表 province 字段:省份主键,省份名称,省份描述

      CREATE TABLE province(
	-- 省份主键
	pid INT PRIMARY KEY AUTO_INCREMENT,
	-- 省份名称
	pname VARCHAR(20),
	-- 省份描述
	description VARCHAR(50)
);

    • 省份表添加数据

      INSERT INTO province(pname,description) VALUES('河北','雾霾'),('内蒙古','草原'),('广东','经济特区');

  4. 从表:城市表city

    • 创建从表 :城市表city 字段:城市主键,城市名称,城市描述,所属省份

      CREATE TABLE city(
	-- 城市主键
	cid INT PRIMARY KEY AUTO_INCREMENT,
	-- 城市名称
	cname VARCHAR(20),
	-- 城市描述
	description VARCHAR(50),
	-- 所属省份(外键)
	province_pid INT,
	-- 添加province_pid字段的外键约束
	FOREIGN KEY(province_pid) REFERENCES province(pid)
);

    • 城市表添加主表省份表存在的数据

      INSERT INTO city(cname,description,province_pid) VALUES('石家庄','雾霾之最',1),('承德','避暑山庄',1);
INSERT INTO city(cname,description,province_pid) VALUES('包头','草原钢城',2),('赤峰','红山文化',2);
INSERT INTO city(cname,description,province_pid) VALUES('深圳','经济特区',3),('东莞','服务行业',3);

    • 城市表添加主表省份表不存在的数据

      -- 报错 Cannot add or update a child row: a foreign key constraint fails
INSERT INTO city(cname,description,province_pid) VALUES('澳门','博彩娱乐',4);

    • 删除省份表,名称是广东的数据

      -- 报错 Cannot delete or update a parent row: a foreign key constraint fails
DELETE FROM province WHERE pname='广东';

  5. 图解:
    在这里插入图片描述

1.1.2 方案2：一张表，自关联一对多

  1. 自关联1对多关系(了解): 本表的外键使用本表的主键

  2. 图解:
    在这里插入图片描述

    • 创建地区表: area 字段:地区主键,地区名称,地区描述,所属省份

      CREATE TABLE AREA(
	-- 地区主键
	aid INT PRIMARY KEY AUTO_INCREMENT,
	-- 地区名称
	aname VARCHAR(20),
	-- 地区描述
	description VARCHAR(50),
	-- 所属省份(外键)
	area_aid INT,
	-- 添加area_aid字段的外键约束
	FOREIGN KEY(area_aid) REFERENCES AREA(aid)
);

    • 添加数据的时候,如果没有写外键字段,默认使用NULL值

      INSERT INTO AREA(aname,description) VALUES('河北','雾霾'),('内蒙古','草原'),('广东','经济特区');
INSERT INTO AREA(aname,description,area_aid) VALUES('石家庄','雾霾之最',1),('承德','避暑山庄',1);
INSERT INTO AREA(aname,description,area_aid) VALUES('包头','草原钢城',2),('赤峰','红山文化',2);
INSERT INTO AREA(aname,description,area_aid) VALUES('深圳','经济特区',3),('东莞','服务行业',3);

    • 弊端,添加不存在省份的数据

      INSERT INTO AREA(aname,description,area_aid) VALUES('香港','旅游购物',4);

1.2 实战2：用户和角色

1.2.1 多对多关系

  1. 图解:
    在这里插入图片描述

  2. 用户和角色的多对多关系

  3. 建表原则: 创建一张中间表,使用两个主表的主键作为外键

  4. 主表: 用户表users 角色表roles

    • 创建主表用户表 :users 字段:用户主键,用户名,密码

      CREATE TABLE users(
	-- 用户主键
	uid INT PRIMARY KEY AUTO_INCREMENT,
	-- 用户名
	username VARCHAR(20),
	-- 密码
	PASSWORD VARCHAR(20)
);

    • 用户表添加数据

      INSERT INTO users(username,PASSWORD) VALUES ('群演1','1234'),('群演2','5678');

    • 创建主表角色表 :roles 字段:角色主键,角色名称

      CREATE TABLE roles(
	-- 角色主键
	rid INT PRIMARY KEY AUTO_INCREMENT,
	-- 角色名称
	rname VARCHAR(20)
);

    • 角色表添加数据

      INSERT INTO roles(rname) VALUES('皇上'),('皇后'),('太监'),('宫女');

  5. 中间表 users_roles

    • 创建中间表 :users_roles

      CREATE TABLE users_roles(
	-- 用户主键
	users_uid INT,
	-- 角色主键
	roles_rid INT,
	-- 添加外键约束
	FOREIGN KEY(users_uid) REFERENCES users(uid),
	FOREIGN KEY(roles_rid) REFERENCES roles(rid)
);

    • 中间表添加两个主表都有的数据

      INSERT INTO users_roles VALUES(1,1),(1,2),(1,3),(1,4),(2,2),(2,3),(2,4);

    • 中间表添加主表用户表不存在的数据

      -- Cannot add or update a child row: a foreign key constraint fails
INSERT INTO users_roles VALUES(3,1);

    • 中间表添加主表角色表不存在的数据

      -- Cannot add or update a child row: a foreign key constraint fails
INSERT INTO users_roles VALUES(1,5);

    • 删除主表角色表,角色名称为太监的数据,中间表在使用,不能删除

      -- Cannot delete or update a parent row: a foreign key constraint fails
DELETE FROM roles WHERE rname = '太监';

    • 解决方法:

      • 先删除中间表中使用了太监的数据

        DELETE FROM users_roles WHERE roles_rid = 3;

      • 再删除主表角色表,角色名称为太监的数据,中间表没有使用,能删除

        DELETE FROM roles WHERE rname = '太监';

  6. 实例图解:
    在这里插入图片描述

第二章 多表查询

  1. 提供表结构如下：
    在这里插入图片描述

  2. 多表查询:一次查询两张以上的表,叫多表查询

    • 交叉连接查询 :很少使用,有错误数据

      • 格式:

        select * from 表A,表B;

    • 交叉连接图解:
      在这里插入图片描述

    • 内连接查询 :在交叉连接查询的基础上,使用外键约束作为查询条件

      • 隐式内连接 :不使用关键字 [inner] join on

        • 格式:
        	select * from 表A,表B where 表A.主键 = 表B.外键;

      • 显示内连接 :使用关键字 [inner] join on

        • 格式:
        	select * from 表A [inner] join 表B on 表A.主键 = 表B.外键;

    • 内连接查询图解:
      在这里插入图片描述

    • 外连接查询 :在交叉连接查询的基础上,使用外键约束作为查询条件

      • 左外连接查询 :使用关键字 left [outer] join on

        • 格式:
         select * from 表A left [outer] join 表B on 表A.主键 = 表B.外键;

    • 注意:

      • 左外连接查询以左边的表为主
        • 左边有的数据,右边没有使用null代替
        • 左边没有的数据,右边也不能出现

    • 右外连接查询 :使用关键字 right [outer] join on

      • 格式:
       select * from 表A right[outer] join 表B on 表A.主键 = 表B.外键;
      • 注意:
        • 右外连接查询以右边的表为主
        • 右边有的数据,左边没有使用null代替
        • 右边没有的数据,左边也不能出现

    • 外连接查询图解:
      在这里插入图片描述

2.1 初始化数据

  1. 分类表

    CREATE TABLE category (
cid VARCHAR(32) PRIMARY KEY ,
cname VARCHAR(50)
);

  2. 商品表

    CREATE TABLE products(
pid VARCHAR(32) PRIMARY KEY ,
pname VARCHAR(50),
price INT,
flag VARCHAR(2), #是否上架标记为：1表示上架、0表示下架
category_id VARCHAR(32),
CONSTRAINT products_fk FOREIGN KEY (category_id) REFERENCES category (cid)
);

  3. 分类

    INSERT INTO category(cid,cname) VALUES('c001','家电');
INSERT INTO category(cid,cname) VALUES('c002','服饰');
INSERT INTO category(cid,cname) VALUES('c003','化妆品');

  4. 商品

    INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p001','联想',5000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p002','海尔',3000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p003','雷神',5000,'1','c001');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p004','JACK
JONES',800,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p005','真维斯',200,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p006','花花公
子',440,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p007','劲霸',2000,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p008','香奈儿',800,'1','c003');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p009','相宜本
草',200,'1','c003');

2.2 多表查询

  1. 交叉连接查询 (基本不会使用-得到的是两个表的乘积) [了解]

    • 一次性的查询出分类表和商品表的所有数据

      SELECT * FROM category,products;

  2. 内连接查询

    • 隐式内连接

      SELECT * FROM category,products WHERE category.cid = products.category_cid;

    • 使用给表起别名,简化sql语句

      SELECT * FROM category c,products p WHERE c.cid = p.category_cid;

    • 显示内连接

      SELECT * FROM category c INNER JOIN products p ON c.cid = p.category_cid;

    • 查询哪些分类的商品已经上架

      -- 隐式内连接:多个查询的条件可以使用逻辑运算符连接
SELECT * FROM category c,products p WHERE c.cid = p.category_cid AND p.flag = '1';

    • 显示内连接 :on的后边可以继续写where添加其他查询条件

      SELECT * FROM category c INNER JOIN products p ON c.cid = p.category_cid WHERE p.flag = '1';

    • 显示内连接 :多个查询的条件可以使用逻辑运算符连接

      SELECT * FROM category c INNER JOIN products p ON c.cid = p.category_cid AND p.flag = '1';

  3. 外连接查询

    • 左外连接查询

      SELECT * FROM category c LEFT OUTER JOIN products p ON c.cid = p.category_cid;

    • 右外连接查询

      SELECT * FROM category c RIGHT OUTER JOIN products p ON c.cid = p.category_cid;

    • 使用 左外连接查询 ,查询每类商品的个数

      -- 分组: cid
SELECT cid,COUNT(cid) FROM category c LEFT OUTER JOIN products p 
ON c.cid = p.category_cid
GROUP BY cid;
-- 统计: cid
SELECT cid,COUNT(p.category_cid) FROM category c LEFT OUTER JOIN products p 
ON c.cid = p.category_cid
GROUP BY cid;

    • 使用 内连接查询 ,有玩具的数据吗? 交集

      SELECT * FROM category c,products p WHERE c.cid = p.category_cid;

    • 把 左外连接中 两张表调换位置,相对于右外连接

      SELECT * FROM products p LEFT OUTER JOIN category c  ON c.cid = p.category_cid;

  4. 下面通过一张图说明连接的区别:
    在这里插入图片描述

2.3 子查询

  1. 子查询(sql语句的嵌套)

    • 一条sql语句的查询结果,作为另外一条sql语句的查询条件

      • 格式:

        select * from 表B where 字段 = (select 字段 from 表A [where 条件]);

    • 一条sql语句的查询结果,作为另外一条sql语句的另一张表(隐式内连接查询,先过滤数据)

      • 格式:

        select * from (select * from 表A [where 条件]),表B where 表A.主键 = 表B.外键

  2. 子查询图解:
    在这里插入图片描述

  3. 实例:

    • 查询商品表 ,只显示化妆品的信息

      SELECT * FROM products WHERE category_cid = 'c003';

    • 一条sql语句的查询结果,作为另外一条sql语句的查询条件

      SELECT * FROM products WHERE category_cid = (
	SELECT cid FROM category WHERE cname = '化妆品'
);

    • 一条sql语句的查询结果,作为另外一条sql语句的另一张表

      SELECT * FROM (SELECT * FROM category WHERE cname = '化妆品') c,products p WHERE c.cid = p.category_cid;

    • 查询“化妆品”和“家电”两个分类上架商品详情

      SELECT * FROM (SELECT * FROM category WHERE cname = '化妆品' OR cname = '家电') c,products p 
WHERE c.cid = p.category_cid;

SELECT * FROM products WHERE category_cid IN (
	SELECT cid FROM category WHERE cname IN ('化妆品','家电') 
);
石国旺
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL --- 多表查询 & 驱动表
weixin_53622554的博客
05-12 9084
指从多张表中查询数据，就是从多张表当中进行查询单表查询的SQL语句：select 字段列表 from 表名;那么要执行多表查询，只需要使用逗号分隔多张表即可，如： select 字段列表 from 表1, 表2;-- 多表查询:直接在from之后跟上多张表就可以了,多张表之间使用逗号分隔可以看到，在多表查询的结果当中，每个员工都展示了5次，将每个员工分别和五个部门进行了一次匹配， 也就是将17个员工和5个部门挨个组合了一次，就是17 * 5 = 85。
MySQL多表查询 （超详细）
weixin_55772633的博客
08-05 6567
关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率。实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（UNIQUE）（开发中一般取消外键的设定，即在多的一方建立字段指向一的一方的主键即可）（开发中一般取消外键的设定，即在任意一方加入字段关联另外一方的主键即可）（开发中一般取消外键的设定，即在中间表建立两个字段指向两方的主键即可）关系：一个部门对应多个员工，一个员工对应一个部门。实现：在多的一方建立外键，指向一的一方的主键。
MYSQL多表查询
最新发布
weixin_42894555的博客
04-13 1433
多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号”进行关联。
【MySQL】多表查询策略（多表联查&子查询）
尹煜的博客
05-01 1万+
MySQL语句学习的难点和重点就在于多表查询，同时MySQL也有诸多方法供大家选择，不论是多表联查（联结表、左连接、右连接……）还是子查询（SELECT子查询、WHERE子查询、FROM子查询），掌握一种方法达到目的即可，当然其他方法也需要理解，本文将阐述完整的多表查询方法。 首先分享下学习链接，大家可以在看完本文后选择以下链接巩固学习成果，我相信如果都给掌握的话，MySQL语句中90%的问题都将迎刃而解！！ 视频学习链接：SQL学习之道_哔哩哔哩_bilibili
SQL之多表查询
LP'S Blog
03-30 1万+
一对多(多对一) 多对多一对一一对多(多对一)案例:部门与员工的关系关系:一个部门对应多个员工，一个员工对应一个部门实现:在多的一方建立外键，指向一的一方的主键多对多案例:学生与课程的关系关系:一个学生可以选修多门课程，一门课程也可以供多个学生选择实现:建立第三张中间表，中间表至少包含两个外键，分别关联两方主键一对一案例:用户与用户详情的关系关系:一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率。
MySQL之多表查询
小月月的博客
05-26 5870
一、介绍 二、多表关系 三、多表查询简述 四、多表查询操作 ：1.连接查询--内连接 2.连接查询--外连接 3.连接查询--自连接
MySQL 多表查询实现分析
09-11
MySQL 多表查询实现分析 MySQL 多表查询是指在一个数据库中，存在多个表，这些表都是相互关联的。我们可以使用多表查询来组合这些表，实现复杂的数据查询和分析。 在 MySQL 中，多表查询可以使用多种方式，包括 ...
MySQL多表查询练习题原数据
08-18
练习题答案解决思路请查看本人原文章MySQL数据库多表查询练习题
MySQL多表查询详解上
09-08
主要介绍了MySQL多表查询详解上，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧
MySQL多表查询详解
03-08
文档讲述了MySQL中的多表查询和一些其他的知识点，这是以后经常用到的
解析Mysql多表查询的实现
09-10
本篇文章是对Mysql多表查询的实现进行了详细的分析介绍，需要的朋友参考下
MySql学习4：多表查询
weixin_41897680的博客
08-26 1302
MySql学习4：多表查询
基础篇6、多表查询
希望有点用
02-06 799
MySQL多表查询
图解数据库多表查询（多表联查）的几种方式
weixin_44901808的博客
08-08 7246
多表查询
MySQL多表查询（联合查询、连接查询、子查询）
热门推荐
m0_49864110的博客
07-27 2万+
EXCEPT 将两张表的某些字段的公共记录减去，然后返回一张表的剩余记录（差集）（MySQL不存在，使用NOT IN字段实现）3、作为联合查询的多表之间的列数、以及列数的类型必须相同（例如：表1查询哪些列，表2就查询哪些列）1、通过联合查询，可以得到两张表中记录的集合或者公共记录的集合，或者其中某张表中的记录的集合。将两张表的全部记录交叉组合，在添加了其他表字段的同时，所得的记录数是两张表行数的乘积。#查询张三入职之后的所有员工信息（先查询张三的入职信息，再查询此时间之后的员工信息。
MySQL ——多表连接查询
白白白鲤鱼的博客
09-24 4493
MYSQL ——连表查询
【MySQL数据库 | 第十三篇】多表查询
fckbb的博客
06-07 3785
多表查询是指在一个SQL语句中使用多个表进行数据查询和操作。多表查询可以对数据表之间的关系进行查询，例如可以通过连接多个表来获取更完整的数据信息
MySQL联合查询（多表查询）
fwdwqdwq的博客
06-07 1373
一、内连接select *from 表1 [ inner | cross ] join 表2 [ on过滤条件 ] [ where查询条件 ] ；[ inner | cross ]:join 内连接关键字(必须要有)；on从语法上可省略，但如果省略它，将查询的是多表的笛卡尔积；1.select *from 表1 join 表2 [ on过滤条件 ] [ where查询条件 ] ；2.select *from 表1，表2 [ where查询条件 ] ；3.select *from 表1 inner join
MySQL数据库——多表查询
Sakurapaid的博客
12-27 1465
MySQL中的多表查询是指在一个查询中同时使用两个或多个表，通过某种关系将它们连接起来，以检索所需的数据。多表查询在数据库操作中非常常见，尤其是在处理复杂的数据关系时。简单讲就是：多表查询是指从多个表中检索数据。当你需要从多个表中获取信息并将它们组合在一起时，可以使用多表查询。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值