mysql排序、分组、多表查询

最新推荐文章于 2024-04-07 10:05:21 发布

原创最新推荐文章于 2024-04-07 10:05:21 发布 · 265 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

本文介绍了MySQL中的排序方法，包括升序与降序排列，并展示了如何使用`LIMIT`进行分页查询。此外，讲解了如何通过`GROUP BY`对数据进行分组聚合，获取最小值。最后，详细阐述了SQL中的内连接、左连接和右连接查询，帮助理解不同类型的多表查询操作。

1.mysql排序

1.1.从大到小排序，不加desc默认情况下是升序

MariaDB [fei1]> select * from student order by age desc;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  7 | lisi        |   50 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  3 | wangqing    |   25 |
|  2 | jerry       |   23 |
|  1 | tom         |   20 |
|  6 | zhangshan   |   20 |
| 11 | qiuxiaotian |   20 |
| 10 | qiuyi       |   15 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |    3 |
+----+-------------+------+
11 rows in set (0.001 sec)

1.2.根据表中column_name进行降序并只取前4个结果


MariaDB [fei1]> select * from student order by age desc limit 4;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  7 | lisi      |   50 |
|  4 | sean      |   28 |
|  5 | zhangshan |   26 |
|  3 | wangqing  |   25 |
+----+-----------+------+
4 rows in set (0.000 sec)

1.3.根据表中column_name进行升序，并跳过前2个结果取后面3个结果

MariaDB [fei1]> select * from student order by age limit 2,3;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
| 10 | qiuyi       |   15 |
|  1 | tom         |   20 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
3 rows in set (0.000 sec)

2.使用group by 对取出表中的内容进行分组

MariaDB [fei1]> select name,min(age) as age from student  group by name;
+-------------+------+
| name        | age  |
+-------------+------+
| chenshuo    |   10 |
| jerry       |   23 |
| lisi        |   50 |
| qiuxiaotian |   20 |
| qiuyi       |   15 |
| sean        |   28 |
| tom         |   20 |
| wangqing    |   25 |
| wangwu      |  100 |
| zhangshan   |   26 |
+-------------+------+
10 rows in set (0.010 sec)

3,多表查询，使用左连接，右连接，内连接

3.0.多表查询语法

select 字段1,字段2… from 表1,表2… [where 条件]

3.1.内连接：内连接查询 (只显示符合条件的数据)

注：内连接查询与多表联合查询的效果是一样的

#查询人员和部门所有信息
select * from person inner join dept  on person.did =dept.did;

在这里插入图片描述

3.2.左连接：左外连接查询 (左边表中的数据优先全部显示)

#查询人员和部门所有信息
select * from person left join  dept  on  person.did =dept.did;