mysql多表查询、子查询

一、准备数据

1. 用到的数据库及数据表

CREATE DATABASE multi_table;

#分类表
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表示下架
 cid VARCHAR(32),
 CONSTRAINT products_fk FOREIGN KEY products(cid) REFERENCES category(cid)
);

2. 向分类表和商品表两张表格中插入数据

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

#商品 
INSERT INTO products(pid, pname,price,flag,cid) VALUES('p001','联想',5000,'1','c001'); 
INSERT INTO products(pid, pname,price,flag,cid) VALUES('p002','海尔',3000,'1','c001'); 
INSERT INTO products(pid, pname,price,flag,cid) VALUES('p003','雷神',5000,'1','c001'); 
INSERT INTO products (pid, pname,price,flag,cid) VALUES('p004','JACK JONES',800,'1','c002'); 
INSERT INTO products (pid, pname,price,flag,cid) VALUES('p005','真维斯',200,'1','c002'); 
INSERT INTO products (pid, pname,price,flag,cid) VALUES('p006','花花公 子',440,'1','c002'); 
INSERT INTO products (pid, pname,price,flag,cid) VALUES('p007','劲霸',2000,'1','c002'); 
INSERT INTO products (pid, pname,price,flag,cid) VALUES('p008','香奈儿',800,'1','c003'); 
INSERT INTO products (pid, pname,price,flag,cid) VALUES('p009','相宜草',200,'1','c003');

二、多表查询

多表查询主要包括：
 1.交叉查询
 2.内连接查询
    (1)隐式内连接
    (2)显示内连接
 3.外连接查询
   (1)左外连接查询
    (2)右外连接查询

2.1 交叉查询

格式：
  select … from 表A，表B
注意：
  其实是用表A中的每条记录和表B中的每条记录做连接，相当于做笛卡尔积，出现错误数据，而查询结果中出现错误数据，是不允许的，必须要解决这个问题 ，所以：交叉查询，用的不多

#查询分类表中的所有信息
SELECT * FROM category;
#查询分类表的记录数
SELECT COUNT(*) FROM category; #3
#查询商品表中的所有信息
SELECT * FROM products;
#查询商品表的记录数
SELECT COUNT(*) FROM products; #9

#交叉查询
SELECT * FROM category,products;
#交叉查询，给表格起别名
SELECT c.*,p.* FROM category c,products p;
SELECT COUNT(*) FROM category,products; #27

2.2 内连接

（1）隐式内连接
  看不到inner join这个关键字
  格式：
  select 字段1,字段2 … from 表A,表B where 条件
  条件：
  主表.主键 = 从表.外键

需求：
1.查询所有商品的记录，要求显示商品及所属的分类信息
2.只查询所有’化妆品’的记录，要求显示商品及所属的分类信息
3.查询哪些分类的商品已经上架
4.查询每种分类商品的个数

#1.查询所有商品的记录，要求显示商品及所属的分类信息
SELECT * FROM category,products WHERE category.cid=products.cid;
#给表起别名
SELECT c.*,p.* FROM category c,products p WHERE c.cid=p.cid;
#给表起别名，结果显示pid，pname，price，flag，cname
SELECT p.pid,p.pname,p.price,p.flag,c.cname FROM category c,products p WHERE c.cid=p.cid;
#2.只查询所有'化妆品'的记录，要求显示商品及所属的分类信息
SELECT c.*,p.* FROM category c,products p WHERE c.cid=p.cid AND c.cname='化妆品';
#3.查询哪些分类的商品已经上架
SELECT c.cid,c.cname,p.pname,p.flag FROM category c,products p WHERE c.cid=p.cid AND p.flag=1;
#4.查询每种分类商品的个数
 #分析：
  #1.分组：category_id
  #2.聚合函数：个数count
  #3.显示出分类的名称，两张表联查 
SELECT p.cid,c.cname,COUNT(p.pid) FROM category c,products p WHERE c.cid=p.cid GROUP BY p.cid;

（2）显式内连接
  能看到inner join，inner可以省略
  格式：
  select 字段1,字段2 … from 表A inner join 表B on 条件
  条件：
  主表.主键 = 从表.外键

#1.查询所有商品的记录，要求显示商品及所属的分类信息
SELECT * FROM category INNER JOIN products ON category.cid=products.cid;
#给表起别名
SELECT c.*,p.* FROM category c INNER JOIN products p ON c.cid=p.cid;
#inner 可以省略
SELECT c.*,p.* FROM category c JOIN products p ON c.cid=p.cid;
#inner可以省略，on可以换成where
SELECT c.*,p.* FROM category c JOIN products p WHERE c.cid=p.cid;
#2.只查询所有'化妆品'的记录，要求显示商品及所属的分类信息
SELECT c.*,p.* FROM category c INNER JOIN products p ON c.cid=p.cid WHERE c.cname='化妆品';
#3.查询哪些分类的商品已经上架
SELECT c.cid,c.cname,p.pname,p.flag FROM category c INNER JOIN products p ON c.cid=p.cid AND p.flag=1;
/*
 #4.查询每种分类商品的个数
 分析：
  1.分组：category_id
  2.聚合函数：个数count
  3.显示出分类的名称，两张表联查 
*/
SELECT c.cname,COUNT(p.pid) FROM category c INNER JOIN products p ON c.cid=p.cid GROUP BY c.cname;

2.3 外连接

（1）左外连接查询
  格式：
  select 字段1,字段2 … from 表A left outer join 表B on 条件
  条件：
  主表.主键 = 从表.外键
（2）右外连接查询
  格式：
  select 字段1,字段2 … from 表A right outer join 表B on 条件
  条件：
  主表.主键 = 从表.外键

#1.查询所有商品的记录，要求显示商品及所属的分类信息
SELECT c.*,p.* FROM category c LEFT OUTER JOIN products p ON c.cid=p.cid;
#2.只查询所有'化妆品'的记录，要求显示商品及所属的分类信息
SELECT c.*,p.* FROM category c RIGHT OUTER JOIN products p ON c.cid=p.cid WHERE c.cname='化妆品';
#outer可以省略
SELECT c.*,p.* FROM category c RIGHT JOIN products p ON c.cid=p.cid WHERE c.cname='化妆品';
#3.查询哪些分类的商品已经上架
SELECT c.*,p.* FROM category c LEFT JOIN products p ON c.cid=p.cid WHERE p.flag='1';
#4.查询每种分类商品的个数
SELECT c.cname,COUNT(*) FROM category c LEFT JOIN products p ON c.cid=p.cid GROUP BY c.cname;

向分类表中插入一条数据，比较左外连接查询和内连接查询的区别：

#向分类表中插入一条数据
INSERT INTO category(cid,cname) VALUES('c004','饮品');

左外连接：

#左外连接查询
SELECT c.*,p.* FROM category c LEFT JOIN products p ON c.cid=p.cid;

左外连接查询结果：

隐式内连接：

#隐式内连接
SELECT c.*,p.* FROM category c,products p WHERE c.cid=p.cid;

隐式内连接查询结果：

左外连接与内连接的区别：
左外连接：以left join左侧的表为标准，左表中的所有记录都会显示，不管有没有对应的右表内容
隐式内连接：会保证左右两边的记录的cid必须相同才会显示

三、子查询

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

(1) 查询结果作为另一条select语句的查询条件

• 查询"化妆品"分类商品详情
• 查询"化妆品"分类上架商品详情
• 查询"化妆品"或者"家电"两个分类上架商品详情

#一、查询"化妆品"分类商品详情
#1.查询'化妆品'分类的cid的值
SELECT cid FROM category WHERE cname='化妆品'; #c003
#2.使用1中查询的cid的值作为条件，进行查询
SELECT * FROM products WHERE cid=(SELECT cid FROM category WHERE cname='化妆品');

#二、查询"化妆品"分类上架商品详情
SELECT * FROM products p WHERE p.flag=1 AND p.cid=(SELECT cid FROM category WHERE cname='化妆品');

#三、查询"化妆品"或者"家电"两个分类上架商品详情
SELECT * FROM products WHERE cid IN('c003','c001') AND flag='1';
#1.查询"化妆品"或者"家电"两个分类的cid
SELECT cid FROM category WHERE cname='化妆品' OR cname='家电';
#2.使用1中的查询结果作为条件
SELECT * FROM products WHERE cid IN(SELECT cid FROM category WHERE cname='化妆品' OR cname='家电');

(2) 查询结果作为另一条select语句的表

• 查询"化妆品"分类商品详情

#1.查询'化妆品'分类的信息
SELECT c.* FROM category c WHERE c.cname='化妆品';
#2.使用1的结果作为一张表，和商品做连接查询
SELECT cc.cname,p.pname FROM products p,(SELECT c.* FROM category c WHERE c.cname='化妆品') cc WHERE p.cid=cc.cid;