超详细的mysql总结（DQL）

上一篇文章总结了 DDL、DML的使用，这一篇文章把剩下的 DQL 加上~

DQL（Data Query Language）即数据库查询语言，用来查询所需要的信息，在查询的过程中，需要判断所查询的数据与表之间的关系，可能需要的数据在一张表中可以查询到，可能需要联合多张表才能查询到，在这种情况下，查询的语句也不一样。

要进行查询之前，首先需要有表数据，以下sql语句创建一张products表，存储着不同品牌及型号的手机

CREATE TABLE IF NOT EXISTS `products` (
    id INT PRIMARY KEY AUTO_INCREMENT,
    brand VARCHAR(20),
    title VARCHAR(100) NOT NULL,
    price DOUBLE NOT NULL,
    score DECIMAL(2,1),
    voteCnt INT,
    url VARCHAR(100),
    pid INT
)

// 省略 insert into 插入数据部分

首先来查看一下表中的所有数据

SELECT * FROM `products`;

// * 代表展示所有的字段，如果只展示部分字段可以指定，可通过as对字段取别名，as可省略
SELECT id, brand as phoneBrand, title phoneTitle, price, score FROM `products`;

有时候我们并不需要查询出所有的数据，比如只需要查看所有品牌为华为的手机，这个时候就可以通过 where 来进行处理了

SELECT id, brand as phoneBrand, title phoneTitle, price, score From `products`  WHERE brand = '华为';

// where后可以跟多个条件，用 AND() 或者 OR 来进行连接
SELECT * From `products` WHERE brand = '华为' && price > 4000; // 查询价格大于4000的华为手机
SELECT * From `products` WHERE score BETWEEN 5 AND 6; // 查询手机评分在5-6分之间的手机，包含5和6分

SELECT * FROM `products` WHERE brand LIKE '%v%'; // 匹配品牌名称包含字母为V的数据
SELECT * FROM `products` WHERE brand LIKE '_v%'; // 匹配品牌名称包含第二个字母为V的数据

对于获取到的手机数据，价格是随意排列的，不便于我们查看范围，想要对数据进行排序，则可以通过 ORDER BY

SELECT id, brand as phoneBrand, title phoneTitle, price, score 
From `products` WHERE brand = '华为' ORDER BY price DESC; 
// DESC 代表降序, ASC 代表升序

// 通过price降序排列的时候，相同价格的产品，再对评分倒序排列
SELECT id, brand as phoneBrand, title phoneTitle, price, score From 
`products` WHERE brand = '华为' ORDER BY price DESC, score DESC;

以上的查询当我们没有限制查询的数量时，就会把所有符合条件的内容都展示出来，当数据量非常大的时候，就可能存在卡顿的情况，通常情况我们都会对数据进行分页处理，一次性返回10条、20条，只需要前端告知后端查询的页码和分页的数量即可，使用 LIMIT 来限制数量

SELECT * FROM `products` LIMIT 10 OFFSET 40;

// LIMIT 10 OFFSET 40 代表偏移40条数据，往后取10条数据
// LIMIT 10, 40 代表偏移10条数据，往后取40条数据

以上的查询方式针对于所有的明细数据，如果想要查询某种品牌的平均值，最大值，就需要使用 GROUP BY 来进行分组，使用聚合函数来计算，通过Having 来限制分组计算后的条件

SELECT brand, AVG(price), MAX(price) as maxPrice, MIN(price) minPrice 
FROM `products` GROUP BY brand;

// 同样可以取别名，不取别名时展示的就是查询语句的字段名称
// 在统计出所有品牌的平均值、最大值、最小值后，取出价格范围在1000-5000之间的
SELECT brand, AVG(price), MAX(price) as maxPrice, MIN(price) minPrice 
FROM `products` GROUP BY brand HAVING maxPrice < 5000 && minPrice > 1000;

// 计算投票总数 SUM(voteCount)
// 计算所有的价格类别，去重 COUNT(DISTINCT price)

以上查询可以总结为以下语句，根据自己数据的需要选择合适的条件限制

SELECT select_expr [, select_expr]...
    [FROM table_references]
    [WHERE where_condition]
    [ORDER BY expr [ASC | DESC]]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [GROUP BY expr] [HAVING where_condition]

上面都是针对于一张表格来进行查询，但我们仔细观察，发现表格中的同一品牌手机的 brand 字段都是一致的，如果针对不同的品牌，要增加描述信息、官网等，那么需要在每一条数据中都重复添加，这样表格数据有大量冗余，可以考虑另建一张品牌表，放置品牌数据，这样就只用在商品表中存放品牌id，通过外键将商品和品牌关联上。

// 创建商品表
CREATE TABLE IF NOT EXISTS `brand`(
    id INT PRIMARY KEY AUTO_INCREMENT,
    brand VARCHAR(20) NOT NULL,
    phoneRank VARCHAR(20),
    website VARCHAR(100)
);

在products 表中通过外键 FOREIGN KEY 与brand表关联上

ALTER TABLE `products` ADD brand_id INT;
ALTER TABLE `products` ADD FOREIGN KEY(brand_id) REFERENCES brand(id);
UPDATE `products` SET brand_id = 1 WHERE brand = '苹果';
UPDATE `products` SET brand_id = 2 WHERE brand = 'VIVO';
UPDATE `products` SET brand_id = 3 WHERE brand = 'oppo';
UPDATE `products` SET brand_id = 4 WHERE brand = '小米';
UPDATE `products` SET brand_id = 7 WHERE brand = '华为';
SELECT * FROM `products`;

关联外键之后，就可以通过两张表一起查询了，查询方式分为四种，分别是左连接（LEFT JOIN，以左边的表为主，下图①④），右连接（RIGHT JOIN，以右边的表为主，下图 ②⑤），内连接（INNER / CROSS JOIN，左右两张图重叠处，下图③），外连接（通过左右连接组合，下图⑥⑦）

以上连接方式的区别就在于以哪张表为主，为主的表数据将全部选取，再去匹配对应的条件

// // 左连接，展示所有的商品，无论商品的品牌在不在品牌表中，对应上图 ①
SELECT products.id, products.title, products.price, brand.id, 
brand.brand, brand.phoneRank, brand.website FROM `products` 
LEFT JOIN `brand` ON products.brand_id = brand.id;

// 内连接，展示所有有brand_id的商品，对应上图 ③
SELECT products.id, products.title, products.price, brand.id,
brand.brand, brand.phoneRank, brand.website FROM `products` 
INNER JOIN `brand` ON products.brand_id = brand.id;

// 右连接，展示所有的品牌，无论是否存在该品牌的商品，对应上图⑤
SELECT products.id, products.title, products.price, brand.id, 
brand.brand, brand.phoneRank, brand.website FROM `products` 
RIGHT JOIN `brand` ON products.brand_id = brand.id 
WHERE products.brand_id IS NULL;

// 外连接，展示所有商品和所有的品牌，对应上图 ⑥
(SELECT products.id, products.title, products.price, brand.id, brand.brand, 
brand.phoneRank, brand.website FROM `products` LEFT JOIN `brand` 
ON products.brand_id = brand.id)
UNION
(SELECT products.id, products.title, products.price, brand.id, brand.brand, 
brand.phoneRank, brand.website FROM `products` RIGHT JOIN `brand` 
ON products.brand_id = brand.id)

以上是一对多的查询情况，还有多对多的情况，那就更为复杂一些，举个例子，学校提供了一些课程供学生自主选择，他们可任意选择2-4门课程，这种情况下肯定会存在一个学生表记录所有的学生信息，还有一张课程表，记录所有的课程信息，如何将学生和课程关联起来，在学生表中增加一个字段用来记录选课id，用逗号分隔吗？这种方式可以，每一次增加数据或者删除数据都需要对所有的数据进行获取，然后还得通过代码来实现，并不是最优解，最好的方式是建立一个关系表，将学生和课程之间的关系全部保存到这张表当中

// 学生表
CREATE TABLE IF NOT EXISTS `students`(
   id INT PRIMARY KEY AUTO_INCREMENT,
   name VARCHAR(20) NOT NULL, age INT
);

// 课程表
CREATE TABLE IF NOT EXISTS `courses`(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    price DOUBLE NOT NULL
);

// 学生选课关系表，设置联合主键，保证学生不会重复选课
CREATE TABLE IF NOT EXISTS `students_select_courses`(
    student_id INT NOT NULL,
    courses_id INT NOT NULL,
    FOREIGN KEY(student_id) REFERENCES students(id) ON UPDATE CASCADE,
    FOREIGN KEY(courses_id) REFERENCES courses(id) ON UPDATE CASCADE,
    PRIMARY KEY(student_id, courses_id)
);

创建三张表，并添加数据后，通过 左连接、右连接、内连接等方式组合在一起查询出所需要的数据

// 左连接，查询所有的学生选课情况
SELECT * FROM students stu
LEFT JOIN students_select_courses ssc ON stu.id = ssc.student_id
LEFT JOIN courses cour ON cour.id = ssc.courses_id;

// 查询所有选课的学生情况
SELECT stu.name stuName, stu.age, cour.name courseName, 
cour.price courPrice FROM students stu 
LEFT JOIN students_select_courses ssc ON stu.id = ssc.student_id 
LEFT JOIN courses cour ON cour.id = ssc.courses_id
WHERE ssc.courses_id IS NOT NULL;

// 哪些课程没有被选择
SELECT stu.name stuName, stu.age, cour.name courseName, 
cour.price courPrice FROM students stu
LEFT JOIN students_select_courses ssc ON stu.id = ssc.student_id
RIGHT JOIN courses cour ON cour.id = ssc.courses_id
WHERE stu.id IS NULL;

最后再来看一下如何组装数据，比如在一个字段中方式对象或数组，可以使用 JSON.OBJECT，JSON.ARRAY、JSON.ARRAYAGG

// 组装成对象
SELECT products.id as id, products.title as title,
products.price as price, products.score as score,
JSON_OBJECT('id', brand.id, 'name', brand.brand, 'rank', 
brand.phoneRank, 'website', brand.website) as brand FROM products
LEFT JOIN brand ON products.brand_id = brand.id;

// 数据组装成数组对象
SELECT stu.id, stu.name, stu.age,
JSON_ARRAYAGG(JSON_OBJECT('id', cour.id, 'name', cour.name)) 
as courses FROM students stu
LEFT JOIN students_select_courses ssc ON stu.id = ssc.student_id
RIGHT JOIN courses cour ON cour.id = ssc.courses_id GROUP BY stu.id 
HAVING id IS NOT NULL;

以上就是关于DQL的所有内容啦，如果对DDL、DML还有不熟悉可以参考这一篇~ 超详细的mysql总结（基本概念、DDL、DML）