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