1. 首页
  2. 文章中心
  3. MySQL

mysql多表查询第一章 多表关系实战第二章 多表查询

54 阅读 0 评论
我是靠谱客的博主 缓慢摩托，最近开发中收集的这篇文章主要介绍mysql多表查询第一章 多表关系实战第二章 多表查询，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

文章目录

  • 第一章 多表关系实战
    • 1.1 实战1：省和市
      • 1.1.1 方案1：多张表，一对多
      • 1.1.2 方案2：一张表，自关联一对多
    • 1.2 实战2：用户和角色
      • 1.2.1 多对多关系
  • 第二章 多表查询
    • 2.1 初始化数据
    • 2.2 多表查询
    • 2.3 子查询

第一章 多表关系实战

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 ('化妆品','家电') 
);

最后

以上就是缓慢摩托为你收集整理的mysql多表查询第一章 多表关系实战第二章 多表查询的全部内容，希望文章能够帮你解决mysql多表查询第一章 多表关系实战第二章 多表查询所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。

本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
点赞(36)

相关文章

使用JpaRepository的save方法执行成功,数据库却没有保存
使用JpaRepository的save方法执行成功,数据库却没有保存
MySQL数据库多表查询
MySQL数据库多表查询
java jpa saveall方法优化_JPA批量保存saveAll的问题
java jpa saveall方法优化_JPA批量保存saveAll的问题
mysql存储过程中传入，传出参数和输出多条查询记录的方法
mysql存储过程中传入，传出参数和输出多条查询记录的方法
mysql多表查询第一章 多表关系实战第二章 多表查询
mysql多表查询第一章 多表关系实战第二章 多表查询
SpringBoot系列教程JPA之新增记录使用姿势
SpringBoot系列教程JPA之新增记录使用姿势
使用SpringBoot-JPA进行自定义的保存及批量保存
使用SpringBoot-JPA进行自定义的保存及批量保存
JPA创建实体类映射表+ 创建索引
JPA创建实体类映射表+ 创建索引

评论列表共有 0 条评论

立即
投稿
返回
顶部