Mysql基础（3）--多表操作

多表操作

多表概念：说白了就是多张数据表,而表与表之间是可以有一定的关联关系 ,这种关联关系通过外键约束实现。

一对一

适用场景：
人和身份证。一个人只有一个身份证,一个身份证只能对应一个人。
建表原则：
在任意一个表建 立外键，去关联另外-一个表的主键。

一对多

适用场景
用户和订单。一个用户可以多个订单。
商品分类和商品。一个分类下可以有多个商品。
建表原则
在多的一方，建立外键约束，来关联一的一方主键。

多对多

适用场景
学生和课程。一个学生可以选择多个课程,一个课程也可以被多个学生选择。
建表原则
需要借助第三张中间表,中间表至包含两个列。这两个列作为中间表的外键,分别关联两张表的主键。

多表查询

• orderlist表数据
  
• user表
  

内连接查询

-- 内连接
-- 查询的是相关的数据表之间有交集的那部分的数据，也就是有外键关系的数据
/*
	显示内连接
		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;

外连接查询

-- 左外连接
-- 查询所有用户信息，以及用户对应的订单信息
SELECT
	u.*,
	o.number
FROM
	USER u
LEFT OUTER JOIN
	orderlist o
ON
	o.uid=u.id;

-- 右外连接
-- 查询所有订单信息，以及订单所属的用户信息
SELECT
	o.*,
	u.name
FROM 
	USER u
RIGHT OUTER JOIN
	orderlist o
ON
	o.uid = u.id;

子查询

-- 结果是单行单列的
-- 查询年龄最高的用户姓名
SELECT NAME,age FROM USER WHERE age=(SELECT MAX(age) FROM USER);

-- 结果是多行多列的
-- 查询张三1和张三2的订单信息
SELECT * FROM orderlist WHERE uid IN (1,2);

SELECT id FROM USER WHERE NAME IN ('张三1','张三2');

SELECT * FROM orderlist WHERE uid IN (SELECT id FROM USER WHERE NAME IN ('张三1','张三2'));

-- 查询订单表中id大于4的订单信息和所属用户信息
SELECT * FROM orderlist WHERE id > 4;

SELECT 
	u.name,
	o.number
FROM
	USER u,
	(SELECT * FROM orderlist WHERE id > 4) o
WHERE
	u.id = o.uid;

自关联查询

在同一张表中数据有关联性,我们可以把这张表当成多个表来查询。

-- 创建员工表
CREATE TABLE employee(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	mgr INT,
	salary DOUBLE
);
-- 添加数据
INSERT INTO employee VALUES (1001,'孙悟空',1005,9000.0),
(1002,'沙和尚',1005,8000.00),
(1003,'猪八戒',1005,8500.00),
(1004,'小白龙',1005,7900.00),
(1005,'唐僧',NULL,18000.00),
(1006,'李逵',1009,8500.00),
(1007,'林冲',1009,7900.00),
(1008,'武松',1009,8100.00),
(1009,'宋江',NULL,16000.00);

-- mgr上级编号，mgr=1005，表示他们的上级都是唐僧，唐僧的mgr为null，没有上级
-- 1009类似
-- 查询所有员工的姓名及其直接上级的姓名，没有上级的员工也需要查询
/*
	员工信息：employee表
	条件：employee.mgr = employee.id
	查询左表的全部数据，和左右两张表有交集部分数据，左外连接
*/
SELECT
	e1.id,
	e1.name,
	e1.mgr,
	e2.id,
	e2.name
FROM
	employee e1
LEFT OUTER JOIN
	employee e2
ON
	e1.mgr = e2.id;

练习

• product表
  
• category表
  
• us_pro表
  

-- 1.查询用尸的编号、姓名、年龄。订单编号
/*
	user表、orderlist表
	条件：user.id=orderlist.uid
*/
SELECT 
	u.id,
	u.name,
	u.age,
	o.number
FROM
	USER u,
	orderlist o
WHERE
	u.id = o.uid;

-- 2.查询所有的用户。用户的编号、姓名、年龄。订单编号
/*
	外连接
	
*/
SELECT
	u.id,
	u.name,
	u.age,
	o.number
FROM
	USER u
LEFT OUTER JOIN
	orderlist o
ON
	u.id=o.uid;

-- 3.查询所有的订单。用户的编号、姓名、年龄。订单编号
/*
	右外连接
*/
SELECT
	u.id,
	u.age,
	u.name,
	o.number
FROM
	USER u
RIGHT OUTER JOIN
	orderlist o
ON
	u.id=o.uid;

-- 4.查询用户年龄大于23岁的信息。显示用户的编号、姓名、年龄。订单编号
/*
	user orderlist
	条件：user.id=o.uid and u.age>23
*/
SELECT 
	u.id,
	u.age,
	u.name,
	o.number
FROM
	USER u,
	orderlist o
WHERE
	u.age>23 AND u.id=o.uid;

-- 5.查询张三和李四用户的信息。显示用户的编号、姓名、年龄。订单编号
/*
	user orderlist
	条件：user.id=orderlist.uid and user.name in ('张三','李四');
*/
SELECT 
	u.id,
	u.age,
	u.name,
	o.number
FROM
	USER u,
	orderlist o
WHERE 
	u.id=o.uid
	AND
	u.name IN ('张三1','张三2');

-- 6.查询商品分类的编号、分类名称。分类下的商品名称
/*
	category product
	条件：category.id = product.cid
*/
SELECT
	c.id,
	c.name,
	p.name
FROM
	category c,
	product p
WHERE
	c.id=p.cid;

-- 7.查询所有的商品分类。商品分类的编号、分类名称。分类下的商品名称
/*
	category product
	条件：category.id=product.cid
	左外连接
*/
SELECT
	c.id,
	c.name,
	p.name

FROM
	category c
LEFT OUTER JOIN
	product p
ON
	c.id=p.cid;

-- 8.查询所有的商品信息。商品分类的编号、分类名称。分类下的商品名称
SELECT
	c.id,
	c.name,
	p.name

FROM
	category c
RIGHT OUTER JOIN
	product p
ON
	c.id=p.cid;

-- 9.查询所有的用户和该用户能查看的所有的商品。显示用户的编号、姓名、年龄。商品名称
/*
	user	product	多对多关系，所以还需要查询 us_pro
	条件：us_pro.uid=user.id and us_pro.pid=peoduct.id
*/
SELECT
	u.id,
	u.name,
	u.age,
	p.name
FROM
	USER u,
	product p,
	us_pro up
WHERE
	up.uid=u.id
	AND
	up.pid=p.id;

-- 10.查询张三和李四这两个用户可以看到的商品。显示用户的编号、姓名、年龄。商品名称
/*
	user	product	us_pro
	条件：us_pro.uid=user.id and us_pro.pid=product.id and user.name in ('张三1','张三2');
*/
SELECT
	u.id,
	u.name,
	u.age,
	p.name
FROM
	USER u,
	product p,
	us_pro up
WHERE
	up.uid=u.id
	AND
	up.pid=p.id
	AND
	u.name IN ('张三1','张三2');