学习目标
- 能够写出子查询的SQL语句
子查询
1. 子查询的介绍
在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句,外部那个select语句则称为主查询.
主查询和子查询的关系:
- 子查询是嵌入到主查询中
- 子查询是辅助主查询的,要么充当条件,要么充当数据源
- 子查询是可以独立存在的语句,是一条完整的 select 语句
2. 子查询的使用
准备数据
# 创建分类表
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表示下架
category_id VARCHAR(32),
CONSTRAINT products_fk FOREIGN KEY (category_id) REFERENCES category (cid)
);
# 插入分类数据
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,category_id) VALUES('p001','联想',5000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p002','海尔',3000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p003','雷神',5000,'1','c001');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p004','JACK JONES',800,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p005','真维斯',200,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p006','花花公子',440,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p007','劲霸',2000,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p008','香奈儿',800,'1','c003');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p009','相宜本草',200,'1','c003');
简单来讲,子查询就是在一个sql语句中嵌套另外一个SQL语句。
所以子查询中的SQL语句,必须可以单独正确的使用,可以查询出正确内容。
例1. 查询当前商品中大于平均价格的商品:
第一步:获取平均价格 (得到的是一个值)
SELECT AVG(price) FROM products;
第二步:查询所有商品
SELECT * FROM products;
第三步:将第一步结果作为第二步的查询条件,这种方式即为子查询
SELECT *
FROM
products
WHERE
price > (SELECT AVG(price) FROM products);
例2:获取所有商品中,平均价格大于1000的分类的全部商品:
第一步:获取平均价格大于1000的分类
SELECT
category_id
FROM
products
GROUP BY
category_id
HAVING
AVG(price) > 1000;
第二步:查询所有商品
SELECT * FROM products;
第三步:第一步查询出来的分类,作为第二步的条件进行筛选
SELECT *
FROM
products
WHERE
category_id IN (SELECT
category_id
FROM
products
GROUP BY
category_id
HAVING
AVG(price) > 1000);
4.练习
练习1:获取所有商品中价格最高的商品(最高价值商品不只有一个)
SELECT *
FROM
products
WHERE
price = (SELECT MAX(price) FROM products);
?
练习2:获取所有商品中,和c003类中最贵商品价格相同的其他商品
SELECT *
FROM
products
WHERE
price = (SELECT max(price) FROM products WHERE category_id = 'c003')
AND category_id != 'c003';
?
练习3:获取全部商品中,价格和c002中商品价格相同的其他商品
SELECT *
FROM
products
WHERE
category_id != 'c002'
AND price IN (SELECT price FROM products WHERE category_id = 'c002');
5.as关键字
在SQL查询,中可以使用as 给表或者字段起别名。
5.1给表起别名
例1:查询每个分类名称所对应的商品数量
SELECT
c.cname,
COUNT(*)
FROM
products AS p
LEFT JOIN category AS c ON c.cid = p.category_id
GROUP BY
c.cname;
?
使用 表名 as 别名 的形式可以给表起别名,==当给表起别名后,在使用该表时只能使用别名,原名已经失效==
如果此时使用原表名将会报错,例如使用category.cname,则出现错误信息:known column 'category.cname' in 'field list'。
在给表起别名时, as关键字可以省略,但是初学时不建议省略,省略后可读性较差,写法如下:
SELECT
c.cname,
COUNT(*)
FROM
products p
LEFT JOIN category c ON c.cid = p.category_id
GROUP BY
c.cname;
5.2给字段起别名
例2:查询每个分类名称所对应的商品数量,并筛选上品总数大于3的分组
SELECT
# 不建议将字段起中文别名
c.cname AS category_name,
COUNT(*) AS total
FROM
products p
LEFT JOIN category c ON c.cid = p.category_id
GROUP BY
category_name
HAVING
total >= 3;
?
给字段定义别名时,as关键字也可以省略,写法如下:
SELECT
# 不建议将字段起中文别名
c.cname category_name,
COUNT(*) total
FROM
products p
LEFT JOIN category c ON c.cid = p.category_id
GROUP BY
category_name
HAVING
total >= 3;
3. 小结
- 子查询是一个完整的SQL语句,子查询被嵌入到一对小括号里面
- select语句可以作为值出现,可以作为列出现,也可以作为表出现(了解)
- 作为值出现:一般情况都是获取聚合函数的值
- 作为列出现:一般都是作为一个范围进行数据筛选 (in)