超详细的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`;
![](https://img2020.cnblogs.com/blog/1408181/202108/1408181-20210822213417289-1130830888.png)
有时候我们并不需要查询出所有的数据,比如只需要查看所有品牌为华为的手机,这个时候就可以通过 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的数据
![](https://img2020.cnblogs.com/blog/1408181/202108/1408181-20210822213417182-695568435.png)
对于获取到的手机数据,价格是随意排列的,不便于我们查看范围,想要对数据进行排序,则可以通过 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;
![](https://img2020.cnblogs.com/blog/1408181/202108/1408181-20210822213417181-1018483810.png)
以上的查询当我们没有限制查询的数量时,就会把所有符合条件的内容都展示出来,当数据量非常大的时候,就可能存在卡顿的情况,通常情况我们都会对数据进行分页处理,一次性返回10条、20条,只需要前端告知后端查询的页码和分页的数量即可,使用 LIMIT 来限制数量
SELECT * FROM `products` LIMIT 10 OFFSET 40; // LIMIT 10 OFFSET 40 代表偏移40条数据,往后取10条数据 // LIMIT 10, 40 代表偏移10条数据,往后取40条数据
![](https://img2020.cnblogs.com/blog/1408181/202108/1408181-20210822213417200-974089987.png)
以上的查询方式针对于所有的明细数据,如果想要查询某种品牌的平均值,最大值,就需要使用 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)
![](https://img2020.cnblogs.com/blog/1408181/202108/1408181-20210822213417131-422121922.png)
以上查询可以总结为以下语句,根据自己数据的需要选择合适的条件限制
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) );
![](https://img2020.cnblogs.com/blog/1408181/202108/1408181-20210822213417187-1723401215.png)
在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,左右两张图重叠处,下图③),外连接(通过左右连接组合,下图⑥⑦)
![](https://img2020.cnblogs.com/blog/1408181/202108/1408181-20210822213417297-378517061.png)
以上连接方式的区别就在于以哪张表为主,为主的表数据将全部选取,再去匹配对应的条件
// 左连接,展示所有的商品,无论商品的品牌不在品牌表中,对应上图① 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)
![](https://img2020.cnblogs.com/blog/1408181/202108/1408181-20210822213417201-1350351478.png)
以上是一对多的查询情况,还有多对多的情况,那就更为复杂一些,举个例子,学校提供了一些课程供学生自主选择,他们可任意选择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)