MYSQL-多表查询之内外连接

21.多表查询_数据准备

-- 创建db4数据库

CREATE DATABASE db4;

-- 使用db4数据库

USE db4;

-- 创建user表

CREATE TABLE USER(

​	id INT PRIMARY KEY AUTO_INCREMENT,	-- 用户id

​	NAME VARCHAR(20),			-- 用户姓名

​	age INT                 -- 用户年龄

);

-- 添加数据

INSERT INTO USER VALUES (1,'张三',23);

INSERT INTO USER VALUES (2,'李四',24);

INSERT INTO USER VALUES (3,'王五',25);

INSERT INTO USER VALUES (4,'赵六',26);

-- 订单表

CREATE TABLE orderlist(

​	id INT PRIMARY KEY AUTO_INCREMENT,	-- 订单id

​	number VARCHAR(30),			-- 订单编号

​	uid INT,   -- 外键字段

​	CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id)

);

-- 添加数据

INSERT INTO orderlist VALUES (1,'hm001',1);

INSERT INTO orderlist VALUES (2,'hm002',1);

INSERT INTO orderlist VALUES (3,'hm003',2);

INSERT INTO orderlist VALUES (4,'hm004',2);

INSERT INTO orderlist VALUES (5,'hm005',3);

INSERT INTO orderlist VALUES (6,'hm006',3);

INSERT INTO orderlist VALUES (7,'hm007',NULL);

-- 商品分类表

CREATE TABLE category(

​	id INT PRIMARY KEY AUTO_INCREMENT,  -- 商品分类id

​	NAME VARCHAR(10)           -- 商品分类名称

);

-- 添加数据

INSERT INTO category VALUES (1,'手机数码');

INSERT INTO category VALUES (2,'电脑办公');

INSERT INTO category VALUES (3,'烟酒茶糖');

INSERT INTO category VALUES (4,'鞋靴箱包');

-- 商品表

CREATE TABLE product(

​	id INT PRIMARY KEY AUTO_INCREMENT,  -- 商品id

​	NAME VARCHAR(30),           -- 商品名称

​	cid INT, -- 外键字段

​	CONSTRAINT cp_fk1 FOREIGN KEY (cid) REFERENCES category(id)

);

-- 添加数据

INSERT INTO product VALUES (1,'华为手机',1);

INSERT INTO product VALUES (2,'小米手机',1);

INSERT INTO product VALUES (3,'联想电脑',2);

INSERT INTO product VALUES (4,'苹果电脑',2);

INSERT INTO product VALUES (5,'中华香烟',3);

INSERT INTO product VALUES (6,'玉溪香烟',3);

INSERT INTO product VALUES (7,'计生用品',NULL);

-- 中间表

CREATE TABLE us_pro(

​	upid INT PRIMARY KEY AUTO_INCREMENT,  -- 中间表id

​	uid INT, -- 外键字段。需要和用户表的主键产生关联

​	pid INT, -- 外键字段。需要和商品表的主键产生关联

​	CONSTRAINT up_fk1 FOREIGN KEY (uid) REFERENCES USER(id),

​	CONSTRAINT up_fk2 FOREIGN KEY (pid) REFERENCES product(id)

);

-- 添加数据

INSERT INTO us_pro VALUES (NULL,1,1);

INSERT INTO us_pro VALUES (NULL,1,2);

INSERT INTO us_pro VALUES (NULL,1,3);

INSERT INTO us_pro VALUES (NULL,1,4);

INSERT INTO us_pro VALUES (NULL,1,5);

INSERT INTO us_pro VALUES (NULL,1,6);

INSERT INTO us_pro VALUES (NULL,1,7);

INSERT INTO us_pro VALUES (NULL,2,1);

INSERT INTO us_pro VALUES (NULL,2,2);

INSERT INTO us_pro VALUES (NULL,2,3);

INSERT INTO us_pro VALUES (NULL,2,4);

INSERT INTO us_pro VALUES (NULL,2,5);

INSERT INTO us_pro VALUES (NULL,2,6);

INSERT INTO us_pro VALUES (NULL,2,7);

INSERT INTO us_pro VALUES (NULL,3,1);

INSERT INTO us_pro VALUES (NULL,3,2);

INSERT INTO us_pro VALUES (NULL,3,3);

INSERT INTO us_pro VALUES (NULL,3,4);

INSERT INTO us_pro VALUES (NULL,3,5);

INSERT INTO us_pro VALUES (NULL,3,6);

INSERT INTO us_pro VALUES (NULL,3,7);

INSERT INTO us_pro VALUES (NULL,4,1);

INSERT INTO us_pro VALUES (NULL,4,2);

INSERT INTO us_pro VALUES (NULL,4,3);

INSERT INTO us_pro VALUES (NULL,4,4);

INSERT INTO us_pro VALUES (NULL,4,5);

INSERT INTO us_pro VALUES (NULL,4,6);

INSERT INTO us_pro VALUES (NULL,4,7);

22.多表查询_内连接查询

显示内连接

标准语法：

SELECT 列名 FROM 表名1 [INNER] JOIN 表名2 ON 关联条件;

-- 查询用户信息和对应的订单信息

SELECT * FROM USER INNER JOIN orderlist ON orderlist.uid = user.id;

-- 查询用户信息和对应的订单信息，起别名

SELECT * FROM USER u INNER JOIN orderlist o ON o.uid=u.id;

-- 查询用户姓名，年龄。和订单编号

SELECT

​	u.name,		-- 用户姓名

​	u.age,		-- 用户年龄

​	o.number	-- 订单编号

FROM

​	USER u      -- 用户表

INNER JOIN

​	orderlist o   -- 订单表

ON

​	o.uid=u.id;

隐式内连接

标准语法：

​SELECT 列名 FROM 表名1,表名2 WHERE 关联条件;

-- 查询用户姓名，年龄。和订单编号

SELECT

​	u.name,		-- 用户姓名

​	u.age,		-- 用户年龄

​	o.number	-- 订单编号

FROM

​	USER u,		-- 用户表

​	orderlist o   -- 订单表

WHERE

​	o.uid=u.id;

23.多表查询_外连接查询

左外连接

标准语法：

SELECT 列名 FROM 表名1 LEFT [OUTER] JOIN 表名2 ON 条件;

-- 查询所有用户信息，以及用户对应的订单信息

SELECT

​	u.*,

​	o.number

FROM

​	USER u

LEFT OUTER JOIN

​	orderlist o

ON

​	o.uid=u.id;

右外连接

标准语法：

SELECT 列名 FROM 表名1 RIGHT [OUTER] JOIN 表名2 ON 条件;

-- 查询所有订单信息，以及订单所属的用户信息

SELECT

​	o.*,

​	u.name

FROM

​	USER u

RIGHT OUTER JOIN

​	orderlist o

ON

​	o.uid=u.id;