MySql表联查 JOIN查询[INNER JOIN、LEFT JOIN、RIGHT JOIN] 原创
---------------------------------
---------------------------------
-- 表联查
---------------------------------
---------------------------------
-- CREATE TABLE `user_types` (
-- `id` int NOT NULL AUTO_INCREMENT,
-- `title` varchar(20) NOT NULL DEFAULT '' COMMENT '类型名称',
-- `status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '状态',
-- PRIMARY KEY (`id`)
-- ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3 COMMENT='用户类型表';
-- CREATE TABLE `users` (
-- `id` int unsigned NOT NULL AUTO_INCREMENT,
-- `username` varchar(20) DEFAULT NULL,
-- `status` tinyint(1) DEFAULT '0',
-- `create_time` int NOT NULL DEFAULT '0',
-- `integral` int DEFAULT '0' COMMENT '积分',
-- `type_id` int NOT NULL,
-- PRIMARY KEY (`id`),
-- KEY `fk_user_type` (`type_id`),
-- CONSTRAINT `fk_user_type` FOREIGN KEY (`type_id`) REFERENCES `user_types` (`id`)
-- ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb3 COMMENT='用户表';
-- CREATE TABLE `orders` (
-- `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '订单主键',
-- `user_id` int unsigned NOT NULL DEFAULT '0' COMMENT '用户主键ID',
-- `goods_id` int unsigned NOT NULL DEFAULT '0' COMMENT '商品主键ID',
-- `amount` int unsigned NOT NULL DEFAULT '0' COMMENT '订单价格[单位：分]',
-- `pay_amount` int unsigned NOT NULL DEFAULT '0' COMMENT '订单实际支付金额[单位：分]',
-- `status` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '订单状态：1 未支付 2 已支付 3 已退款 4 已评价 5 已取消',
-- `pay_time` int unsigned NOT NULL DEFAULT '0' COMMENT '订单支付时间',
-- `create_time` int unsigned NOT NULL DEFAULT '0' COMMENT '订单创建时间',
-- PRIMARY KEY (`id`),
-- KEY `user_id` (`user_id`),
-- KEY `status` (`status`)
-- ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb3 COMMENT='用户订单表';
-- 多表联查的理论依据：笛卡尔积
-- 通过笛卡尔乘积，把两个或者多个表变为一个大表，里面包含了有效的，无效的记录（需要加条件进行过滤数据）
-- 1、多表联合查询，你想要的数据在多个表中，得从多个表中查询获取，但是必须添加条件过滤，
-- 如果不加条件，会出现大量错误数据。
-- 2、条件，连接条件：先分析表跟表之间的关系，分析数据和数据之间的关系，把关系写成 SQL 语句：
-- 多数情况下，表和表之间的关系是主外键关系，但是有特殊，有例外。
-- 3、一个连接条件，只能连2个表，如果要连接N个表，至少需要 N-1 个连接条件；
-- 表联查分为3中 内连接，左连接，右连接
-- INNER JOIN 内连接 左表和右表通过连接[ON]条件能够连接上的才会查出来。
-- LEFT JOIN 左连接 将左表的内容全部显示，不管右表是否有对应的记录；
-- RIGHT JOIN 右连接 将右表的内容全部显示，不管左表是否有对应的记录。
-- INNER JOIN
-- 当orders表中user_id的值存在于users表中才能查出数据
-- insert into orders(id, user_id, goods_id, amount, pay_amount, status, pay_time, create_time) values(1, 1, 1, 100, 100, 2, 1677142358, 1677122358);
SELECT users.*,orders.id as order_id,orders.goods_id FROM users INNER JOIN orders ON users.id=orders.user_id; -- 查出一条数据
SELECT users.*,orders.id as order_id,orders.goods_id FROM users INNER JOIN orders ON users.id=orders.user_id WHERE users.id = 2; -- 查不出数据
-- 两条结果查出的数据一致，inner join并不以谁为基础,它只显示符合条件的记录
select users.`id` as user_id,users.type_id,users.username,user_types.* from users inner join user_types on users.type_id = user_types.id;
select users.`id` as user_id,users.type_id,users.username,user_types.* from user_types inner join users on users.type_id = user_types.id;
-- +---------+---------+--------------+----+--------+--------+
-- | user_id | type_id | username | id | title | status |
-- +---------+---------+--------------+----+--------+--------+
-- | 1 | 1 | 张三 | 1 | 呵呵 | 1 |
-- | 2 | 1 | 测试 | 1 | 呵呵 | 1 |
-- | 3 | 1 | Hello | 1 | 呵呵 | 1 |
-- | 4 | 1 | 王二 | 1 | 呵呵 | 1 |
-- | 5 | 1 | HAHA | 1 | 呵呵 | 1 |
-- | 6 | 1 | WORLD | 1 | 呵呵 | 1 |
-- | 7 | 1 | 章三2 | 1 | 呵呵 | 1 |
-- | 8 | 1 | 测试你好 | 1 | 呵呵 | 1 |
-- | 9 | 1 | Yesterday | 1 | 呵呵 | 1 |
-- | 10 | 1 | CCCCCCC | 1 | 呵呵 | 1 |
-- | 11 | 1 | AAAAA | 1 | 呵呵 | 1 |
-- | 12 | 1 | BBBBB | 1 | 呵呵 | 1 |
-- | 13 | 1 | NULL | 1 | 呵呵 | 1 |
-- | 14 | 1 | HHHHHH | 1 | 呵呵 | 1 |
-- +---------+---------+--------------+----+--------+--------+
-- LEFT JOIN
-- 将左表的内容全部显示，不管右表是否有对应的记录
-- 如果右表加了搜索条件之后orders表数据为空，那整个查询的结果就是空，不会变成上一条说明的结果[字段值NULL]
SELECT users.`id`, users.username,orders.user_id,orders.goods_id from users LEFT JOIN orders on users.id = orders.user_id;
SELECT users.`id`, users.username,orders.user_id,orders.goods_id from users LEFT JOIN orders on users.id = orders.user_id WHERE orders.goods_id = 2; -- 查不出数据
-- RIGHT JOIN
-- 将右表的内容全部显示，不管左表是否有对应的记录
insert into user_types (id, title, status) values(2, 'ssss', 1),(3, 'aaaaa', 2);
select user_types.*, users.id as user_id from user_types right join users on users.type_id=user_types.id;
select user_types.*, users.id as user_id from users right join user_types on users.type_id=user_types.id;
