MySQL-DQL之数据多表操作

🕗 发布于 2024-12-13 23:23 mysql android 数据库

文章目录

一. 多表操作
二. 多表查询

数据查询语言：简称DQL(Data Query Language)

一. 多表操作

实际开发中，一个项目通常需要很多张表才能完成。

例如：一个商城项目就需要分类表(category)、商品表(products)、订单表(orders)等多张表。且这些表的数据之间存在一定的关系，接下来我们将在单表的基础上，一起学习多表方面的知识。
在这里插入图片描述

1. 表与表之间的关系

一对多关系：
常见实例：客户和订单，分类和商品，部门和员工。
一对多建表原则：在从表(多方)创建一个字段，字段作为外键指向主表(一方)的主键。
在这里插入图片描述

2. 外键约束

现在我们有两张表“分类表”和“商品表”，为了表明商品属于哪个分类，通常情况下，我们将在商品表上添加一列，用于存放分类cid的信息，此列称为：外键。
在这里插入图片描述

此时“分类表category”称为：主表，“cid”我们称为主键。“商品表products”称为：从表，category_id称为外键。我们通过主表的主键和从表的外键来描述主外键关系，呈现就是一对多关系。

在从表(多方)创建一个字段，字段作为外键指向主表(一方)的主键。
指向谁,谁是主表

外键特点：
从表外键的值是对主表主键的引用。
从表外键类型，必须与主表主键类型一致。

外键优点:
在插入数据时，保证了数据的准确性。
在删除数据时，保证了数据的完整性。

在这里插入图片描述

从表中引用了主表中的数据，主表中数据不可被删除。
主表中没有数据，从表外键也无法被插入。

3. 创建外键约束表(一对多操作)

在这里插入图片描述
category分类表，为一方，也就是主表，必须提供主键cid
products商品表，为多方，也就是从表，必须提供外键category_id

创建分类表

create table category(
  cid varchar(32) primary key ,
  cname varchar(100)
);

创建商品表,添加约束

create table products(
  pid varchar(32) primary key ,
  pname varchar(40),
  price double,
  category_id varchar(32),
  constraint foreign key (category_id) references category(cid)
);

向分类表中添加数据

insert into category(cid, cname) values ('c001','服装');
insert into category(cid, cname) values ('c002','电器');

向商品表添加普通数据,没有外键数据，默认为null

insert into products(pid,pname) values ('p002','商品名称2');
insert into products(pid,pname,category_id) values ('p003','商品名称3',null);

向商品表添加普通数据，含有外键信息(category表中存在这条数据)

insert into products(pid,pname,category_id) values ('p001','商品名称1','c001');

向商品表添加普通数据，含有外键信息(category表中不存在这条数据) – 失败,异常

insert into products(pid,pname,category_id) values ('p004','商品名称4','c009');

删除指定分类(分类未被商品使用)

delete  from category where cid='c002';

删除指定分类(分类被商品使用) – 执行异常

delete  from category where cid='c001';

外键约束参考链接
 on update cascade

二. 多表查询

在这里插入图片描述

1. 多表查询

① 交叉连接查询(基本不会使用-得到的是两个表的乘积) [了解]（不要记住）

语法：select * from A,B;

这个结果有问题是错误的
在这里插入图片描述

② 交集运算：内连接查询(join)

显示内连接：

select * from A inner join B on 条件;
select * from A join B on 条件;

在这里插入图片描述

③ 差集运算：外连接查询

Ⅰ. 左外连接：(left join)

显示左连接:

select * from A left outer join B on 条件;
select * from A left join B on 条件;

在这里插入图片描述

Ⅱ. 右外连接：(right join )

显示右连接:

select * from A right outer join B on 条件;
select * from A right join B on 条件;

在这里插入图片描述

④ 栗子

Ⅰ.栗子1(没有外键约束)

准备数据

INSERT INTO hero VALUES
(1, '鸠摩智', 9),
(3, '乔峰', 1),
(4, '虚竹', 4),
(5, '段誉', 12);

INSERT INTO kongfu VALUES
                       (1, '降龙十八掌'),
                       (2, '乾坤大挪移'),
                       (3, '猴子偷桃'),
                       (4, '天山折梅手');

内连接(左表存在，右表也存在的数据被保留)

SELECT hname,kname FROM hero INNER JOIN kongfu ON hero.kongfu_id = kongfu.kid;

在这里插入图片描述

左连接(左表存在的数据被保留)

SELECT hname,kname FROM hero LEFT OUTER JOIN kongfu ON hero.kongfu_id = kongfu.kid;

在这里插入图片描述

右连接(左表存在的数据被保留)

SELECT hname,kname FROM hero RIGHT OUTER JOIN kongfu ON hero.kongfu_id = kongfu.kid

在这里插入图片描述

Ⅱ. 栗子2(有外键约束)

准备数据
在这里插入图片描述

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

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)
);

#分类
INSERT INTO category(cid,cname) VALUES('c001','家电');
INSERT INTO category(cid,cname) VALUES('c002','服饰');
INSERT INTO category(cid,cname) VALUES('c003','化妆品');
INSERT INTO category(cid,cname) VALUES('c004','奢侈品');

#商品
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');

查询哪些分类的商品已经上架,内连接

SELECT DISTINCT c.cname FROM category c 
 INNER JOIN products p ON c.cid = p.category_id 
 WHERE p.flag = '1';

查询所有分类商品的个数
COUNT(category_id)会过滤掉null

SELECT cname,COUNT(category_id) FROM category c 
 LEFT OUTER JOIN products p 
  ON c.cid = p.category_id 
 GROUP BY cname;

COUNT(*)不会过滤掉null

SELECT cname,COUNT(*) FROM category c 
 LEFT OUTER JOIN products p 
  ON c.cid = p.category_id 
 GROUP BY cname;

2. 子查询

子查询：一条select语句结果作为另一条select语法一部分（查询条件，查询结果，表等）。
select …查询字段 … from … 表… where … 查询条件

栗子:查询“化妆品”分类上架商品详情

子查询(作为查询条件)

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

作为另一张表

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

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

SELECT * FROM products p 
 WHERE p.category_id in 
 ( 
  SELECT c.cid FROM category c 
   WHERE c.cname='化妆品' or c.cname='家电'
 );

3. 自连接查询

在这里插入图片描述

自查询：左表和右表是同一个表，根据连接查询条件查询两个表中的数据
自查询(自连接)要起别名

准备数据

create table tb_areas(
    id varchar(30) not null primary key ,
    title varchar(30),
    pid varchar(30)
);

INSERT INTO test.tb_areas (id, title, pid) VALUES ('1', '广东省', 'null');
INSERT INTO test.tb_areas (id, title, pid) VALUES ('2', '河南省', 'null');
INSERT INTO test.tb_areas (id, title, pid) VALUES ('3', '深圳市', '1');
INSERT INTO test.tb_areas (id, title, pid) VALUES ('4', '广州市', '1');
INSERT INTO test.tb_areas (id, title, pid) VALUES ('5', '南山区', '3');
INSERT INTO test.tb_areas (id, title, pid) VALUES ('6', '宝安区', '3');
INSERT INTO test.tb_areas (id, title, pid) VALUES ('7', '越秀区', '4');
INSERT INTO test.tb_areas (id, title, pid) VALUES ('8', '天河区', '4');

在这里插入图片描述

select  a.title,b.title,c.title from tb_areas as a
    inner join tb_areas as b on a.id=b.pid
    left join tb_areas as c on b.id=c.pid
where a.pid = 'null';

select a.title,b.title,c.title from tb_areas a
    left join tb_areas b on a.id=b.pid
             left join tb_areas c on b.id=c.pid
where b.id is not null and c.id is not null ;

自连接的问题, 需要注意表要起别名, 如果自连接的次数比较多, 字段也要注意需要起别名

select a.province 省 , a. city 市 , disctrict.title 区 from
        (
select  p.title province, c.title city,c.id,c.pid
    from tb_areas as c
inner join  tb_areas as p on c.pid = p.id where p.title = '广东省') a
inner join tb_areas as disctrict on disctrict.pid = a.id;

起别名 AS 可以写, 也可以省略

4. case when

5. 窗口函数

原文地址：https://blog.csdn.net/weixin_45862390/article/details/144366826

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

上一篇：在CentOS7虚拟机上使用Ollama本地部署Llama3大模型中文版+Open WebUI
下一篇：el-tree懒加载状态下实现搜索筛选(纯前端）

.NET(C#) 如何配置用户首选项及保存用户设置
.NET(C#) 如何配置用户首选项及保存用户设置
阅读更多2024-12-14
【最新】北大数字普惠金融指数数据集-省市县（2011-2023年）
郭峰,王靖一,王芳,孔涛,张勋,程志云.测度中国数字普惠金融发展:指数编制与空间特征[J].经济学(季刊),2020,19(04):1401-1418.时间跨度：省级和城市级指数时间跨度为2011-2
阅读更多2024-12-14
GESP202412 四级【Recamán】题解（AC）
a11ak−1−kkakak−1−kak−1k小杨想知道 Recamán 数列的前n项从小到大排序后的结果。手动计算非常困难，小杨希望你能帮他解决这个问题。
阅读更多2024-12-14
IDEA遇到EasyConnect中的网络资源无法访问的问题
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。原文链接：https://blog.csdn.net/wanshanyu_/article/de
阅读更多2024-12-14
双目摄像头标定方法
此时已经完成标定，左下角为反投影误差，右边为外参可视化。将双目左右目拍的图像上传（左右目最好不少于20张）此时回到主页面，即可看到成功导出。把这些误差大的删除即可。
阅读更多2024-12-14
Servlet、omcat服务器架构与工作原理
Servlet是运行在服务器端的Java程序，它的主要职责之一是接收并处理来自客户端（如浏览器）的HTTP请求。当客户端发送一个请求到服务器时，Servlet可以解析请求中的信息，例如请求的URL路径
阅读更多2024-12-14
Vue生命周期钩子函数：深入解析与实践
作为高级Vue前端开发人员，对Vue组件的生命周期钩子函数有着深刻的理解是至关重要的。生命周期钩子函数是指在Vue组件的创建、更新、销毁等过程中，Vue自动调用的一系列方法。通过这些钩子函数，我们可以
阅读更多2024-12-14
安卓开发--使用android studio发布APP
app发布
阅读更多2024-12-14
数据结构与算法学习笔记----拓扑排序
@ author: 明月清了个风。
阅读更多2024-12-14
python 将数据保存到现有的Excel文件的新工作表
out_file = ‘query.xlsx’df1 = pd.DataFrame(out_data)若直接写入：df1.to_excel(out_file, index=False, sheet_n
阅读更多2024-12-14

MySQL-DQL之数据多表操作

文章目录

一. 多表操作

1. 表与表之间的关系

2. 外键约束

3. 创建外键约束表(一对多操作)

二. 多表查询

1. 多表查询

① 交叉连接查询(基本不会使用-得到的是两个表的乘积) [了解]（不要记住）

② 交集运算：内连接查询(join)

③ 差集运算：外连接查询

Ⅰ. 左外连接：(left join)

Ⅱ. 右外连接：(right join )

④ 栗子

Ⅰ.栗子1(没有外键约束)

Ⅱ. 栗子2(有外键约束)

2. 子查询

3. 自连接查询

4. case when

5. 窗口函数

相关文章