学习目标

• 能够写出子查询的SQL语句

子查询

1. 子查询的介绍

在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句，外部那个select语句则称为主查询.

主查询和子查询的关系:

1. 子查询是嵌入到主查询中
2. 子查询是辅助主查询的,要么充当条件,要么充当数据源
3. 子查询是可以独立存在的语句,是一条完整的 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）