java mysql 多表查询_我爱Java系列---【mysql多表查询】

1.用户_角色_权限三表关系

2.交叉查询

3.多表查询

提供的表结构如下：

#创建数据库

CREATE DATABASE day18;

#使用数据库

USE day18;

#分类表

CREATE TABLE category(

cid VARCHAR(32) PRIMARY KEY ,

cname VARCHAR(50)

);

#分类表添加数据

INSERT INTO category VALUES('c001','家电');

INSERT INTO category(cid,cname) VALUES('c002','服饰');

INSERT INTO category(cid,cname) VALUES('c003','化妆品');

#商品表

CREATE TABLE products(

pid VARCHAR(32)PRIMARY KEY,

pname VARCHAR(50),

price INT ,

flag VARCHAR(10),

cid VARCHAR(32),

CONSTRAINT products_fk FOREIGN KEY products(cid) REFERENCES category(cid)

);

#商品表添加数据

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)右外连接查询

*/

#查询分类表中的所有信息

SELECT * FROM category;

#查询分类表的记录数

SELECT COUNT(*) FROM category;#3

#查询商品表中的所有信息

SELECT * FROM products ;

#查询商品表中的记录数

SELECT COUNT(*)FROM products;#9

/*

交叉查询

格式:

select ... from 表A,表B

注意:

其实是用表A中的每条记录和表B中的每条记录做连接,

出现错误数据,而查询结果中出现错误数据,是不允许的,

必须要解决这个问题

所以: 交叉查询,用的不多

*/

#交叉查询

SELECT * FROM category,products;

#交叉查询，给表格起别名

SELECT * FROM category c,products p;

SELECT c.*,p.* FROM category c,products p;

SELECT COUNT(*) FROM category ,products;#27

/*

内连接: 隐式内连接  看不到inner join 这个关键字

格式:

select 字段1,字段2 ... from 表A,表B where 条件

条件:

主表.主键=从表.外键

#1.查询所有商品的记录,要求显示商品及所属的分类信息

#2.只查询所有'化妆品'的记录,要求显示商品及所属的分类信息

#3.查询哪些分类的商品已经上架

#4.查询每种分类商品的个数

*/

#1.查询所有商品的记录，要求显示商品及所属的分类信息

#给表起别名

SELECT * FROM products p, category c WHERE c.cid = p.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 products p,category c WHERE c.cid=p.cid;

#2.只查询所有‘化妆品’的记录，要求显示商品及所属的分类信息

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

#3.查询哪些分类的商品已经上架

SELECT * FROM products p , category c WHERE p.flag=1 AND p.cid=c.cid;

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.显示出分类的名称,两张表联查  化妆品 3

*/

SELECT c.cid,c.cname, COUNT (p.pid) FROM category c, products p WHERE c.cid=p.cid GROUP BY c.cid;#注意：count后面的括号不能和count有空格，要连着写

SELECT c.cid,c.cname,COUNT(p.pid) FROM category c,products p WHERE c.cid=p.cid GROUP BY c.cid;

/*

内连接: 显式内连接 看到 inner join

格式:

select 字段1,字段2 ... from 表A inner join 表B on 条件

条件:

主表.主键=从表.外键

#1.查询所有商品的记录,要求显示商品及所属的分类信息

#2.只查询所有'化妆品'的记录,要求显示商品及所属的分类信息

#3.查询哪些分类的商品已经上架

#4.查询每种分类商品的个数

*/

#1.查询所有商品的记录，要求显示商品及所属的分类信息

SELECT * FROM category INNER JOIN products ON category.cid=products.cid;

#给表起别名

SELECT * FROM category c INNER JOIN products p ON c.cid=p.cid;

SELECT c.*,p.* FROM category c INNER JOIN products p ON c.cid=p.cid;

#inner 可以省略，on可以换成where

SELECT * 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 AND c.cname='化妆品';

SELECT c.*,p.* FROM category c INNER JOIN products p ON c.cid=p.cid WHERE c.cname='化妆品';

SELECT c.*,p.* FROM category c INNER JOIN products p WHERE c.cid=p.cid AND c.cname='化妆品';

#3.查询哪些分类的商品已经上架

SELECT * FROM products p,category c WHERE p.cid=c.cid AND flag=1;

SELECT c.cname,p.pname,p.flag FROM category c INNER JOIN products p ON c.cid=p.cid AND p.flag='1';

/*

#4.查询每种分类商品的个数

1.分组: 商品分类的名称

2.聚合函数: count

3.显示分类的名称: 2张表联查

*/

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

/*

外连接

1.左外连接

select 字段1,字段2 ... from 表A left outer join 表B on 条件

2.右外连接

select 字段1,字段2 ... from 表A right outer join 表B on 条件

条件:

主表.主键=从表.外键

#1.查询所有商品的记录,要求显示商品及所属的分类信息

#2.只查询所有'化妆品'的记录,要求显示商品及所属的分类信息

#3.查询哪些分类的商品已经上架

#4.查询每种分类商品的个数

*/

#1.查询所有商品的记录,要求显示商品及所属的分类信息

SELECT c.*,p.* FROM products p LEFT OUTER JOIN category c ON p.cid=c.cid;

#2.只查询所有'化妆品'的记录,要求显示商品及所属的分类信息

SELECT c.*,p.* FROM products p LEFT OUTER JOIN category c ON p.cid = c.cid WHERE c.cname='化妆品';

#outer可以省略

SELECT c.*,p.* FROM products p LEFT JOIN category c ON p.cid = c.cid WHERE c.cname='化妆品';

#3.查询哪些分类的商品已经上架

SELECT * FROM category c RIGHT JOIN products p  ON p.cid = c.cid WHERE p.flag=1;

#4.查询每种分类商品的个数

SELECT c.*,COUNT(*) FROM category c RIGHT JOIN products p ON p.cid=c.cid GROUP BY p.pname;

SELECT * FROM category;

#向分类表中插入一条记录

INSERT INTO category(cid,cname) VALUES('c004','饮品');

#商品表中没有分类是c004的商品

SELECT * FROM products WHERE cid ='c004';

/*

#左外连接查询

以left join 左侧的表为标准,左表中的所有记录都会显示,

不管有没有对应的右表内容

#隐式内连接:会保证左右两边的记录的cid必须相同才会显示

*/

SELECT c.*,p.* FROM category c LEFT JOIN products p ON c.cid=p.cid;#会有null项

#过滤null

SELECT * FROM category c LEFT JOIN products p ON c.cid=p.cid WHERE p.pid IS NOT NULL;

#隐式内连接

SELECT * FROM category c ,products p WHERE c.cid=p.cid;#没有null

4.子查询作为表

/*

子查询：查询结果作为条件

一条select语句结果作为另一条select语法一部分(查询条件，查询结果，表等)。

语法：`select ....查询字段 ... from ... 表.. where ... 查询条件`

#一.查询“化妆品”分类商品详情

#二.查询“化妆品”分类上架商品详情

#三.查询“化妆品”或者“家电”两个分类上架商品详情

*/

/*

#一.查询“化妆品”分类商品详情

以下写法,虽然可以查询出结果,

但是有问题:

sql语句中把'化妆品'分类的cid 'c003'当成了已知条件,

但是题目给出的是商品分类的名称'化妆品'

1.把cid的值写死了

2.cid值如果发生变化,查询结果就不对了

*/

SELECT * FROM products WHERE cid='c003';

#一、查询“化妆品”分类商品详情

#1.查询‘化妆品’分类的cid的值

SELECT cid FROM category WHERE cname='化妆品';#c003

#2.使用1中查询的cid的值作为条件进行查询

SELECT * FROM category WHERE cid=(SELECT cid FROM category WHERE cname='化妆品');

#二、查询“化妆品”分类上架商品详情

#1.查询‘化妆品’分类的cid的值

SELECT cid FROM category WHERE cname='化妆品';

#2.使用1中的结果，作为查询条件

SELECT * FROM products p WHERE p.flag='1'AND p.cid=(SELECT cid FROM category WHERE cname='化妆品');

#三、查询“化妆品”或者“家电”两个分类上架商品详情

SELECT * FROM products p 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='家电') AND flag='1';

#1.查询“化妆品”或者“家电”两个分类的cid

SELECT cid FROM category WHERE cname IN('化妆品','家电');

#2.使用1中的查询结果作为条件

SELECT p.* FROM products p WHERE p.cid IN(SELECT cid FROM category WHERE cname IN('化妆品','家电')) AND p.flag='1';

/*

子查询：查询结果作为表格

#一.查询“化妆品”分类商品详情

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

*/

SELECT c.* FROM category c WHERE c.cname='化妆品';

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

#一.查询“化妆品”分类商品详情

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

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

#1.查询'化妆品'和'家电'分类的信息

SELECT c.* FROM category c WHERE c.cname IN('化妆品','家电');

#2.使用1的结果作为一张表,和商品做隐式内连接查询

SELECT cc.cname,p.pname,p.flag FROM products p,(SELECT c.* FROM category c WHERE c.cname IN('化妆品','家电')) cc WHERE p.cid=cc.cid AND p.flag='1';

#2.使用1的结果作为一张表,和商品做左外连接查询

SELECT cc.cname,p.pname,p.flag FROM products p LEFT JOIN (SELECT c.* FROM category c WHERE c.cname IN('化妆品','家电')) cc ON p.cid=cc.cid WHERE cc.cname IS NOT NULL AND p.flag='1';