MySQL查询语句select的更多用法_mysqlselect用法-优快云博客

本文链接：https://blog.youkuaiyun.com/m0_71518373/article/details/126956528

本文介绍了MySQL中的一些高级查询技巧，包括Order By排序（结合where、多字段、and/or及嵌套）、Distinct查询不重复记录、Group by进行分组、Limit限制查询结果数量、As设置别名、Like进行模糊查询，以及子查询（配合select、insert、update、delete、exists和as）和连接查询（内连接、左连接、右连接）的用法。此外，还探讨了视图的概念和应用，包括单表视图和多表视图。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在MySQL中，除了基本的增、删、改、查语句外，还有一些进阶的语句，本篇就和大家分享一些MySQL中的高阶语句

一、Order By排序

将我们要查询的数据以升序、降序来进行一个排序，默认是进行升序的排序

select 字段 from 表名 order by 字段 asc/desc

#升序排列
MySQL [test]> select name,id from test1 order by id;
+------+------+
| name | id   |
+------+------+
| zyf  | 1    |
| lbp  | 2    |
| wqy  | 3    |
| ljp  | 4    |
| ds   | 5    |
| yzy  | 6    |
+------+------+

#降序排列
MySQL [test]> select name,id from test1 order by id desc;
+------+------+
| name | id   |
+------+------+
| yzy  | 6    |
| ds   | 5    |
| ljp  | 4    |
| wqy  | 3    |
| lbp  | 2    |
| zyf  | 1    |
+------+------+

Ⅰ、结合where排序

#对test1中符合gender=male的记录根据account进行升序排列
MySQL [test]> select * from test1 where gender='male' order by account;
+------+------+--------+--------+---------+--------+------+
| id   | name | gender | cardid | account | level  | acc  |
+------+------+--------+--------+---------+--------+------+
| 1    | zyf  | male   | 24156  |    2.50 | normal | NULL |
| 3    | wqy  | male   | 22233  |   21.00 | normal | NULL |
| 6    | yzy  | male   | 66677  |  100.00 | normal | NULL |
| 4    | ljp  | male   | 24156  |  648.20 | normal | NULL |
+------+------+--------+--------+---------+--------+------+

Ⅱ、多字段排序

在第一个字段排序有多条相同记录的情况下，会对第二个字段进行排序，即order by 之后的第一个参数只有在出现相同值时，第二个字段才有意义。

#先对gender进行倒叙排列，然后对其中有多条符合的记录再进行id的降序排列
MySQL [test]> select * from test1 order by gender,id desc;
+------+------+--------+--------+---------+--------+------+
| id   | name | gender | cardid | account | level  | acc  |
+------+------+--------+--------+---------+--------+------+
| 2    | lbp  | female | abcde  |   46.00 | VIP    | NULL |
| 6    | yzy  | male   | 66677  |  100.00 | normal | NULL |
| 4    | ljp  | male   | 24156  |  648.20 | normal | NULL |
| 3    | wqy  | male   | 22233  |   21.00 | normal | NULL |
| 1    | zyf  | male   | 24156  |    2.50 | normal | NULL |
| 5    | ds   | unknow | 11111  | 1000.00 | SVIP   | NULL |
+------+------+--------+--------+---------+--------+------+

Ⅲ、结合and/or排序

#对test1表中account大于10小于700的记录基于id进行升序排列
MySQL [test]> select * from test1 where account>10 and account<700 order by id;
+------+------+--------+--------+---------+--------+------+
| id   | name | gender | cardid | account | level  | acc  |
+------+------+--------+--------+---------+--------+------+
| 2    | lbp  | female | abcde  |   46.00 | VIP    | NULL |
| 3    | wqy  | male   | 22233  |   21.00 | normal | NULL |
| 4    | ljp  | male   | 24156  |  648.20 | normal | NULL |
| 6    | yzy  | male   | 66677  |  100.00 | normal | NULL |
+------+------+--------+--------+---------+--------+------+
4 rows in set (0.00 sec)

#对test1表中account大于10或者account小于700的记录基于id进行升序排列
MySQL [test]> select * from test1 where account>10 or account<700 order by id;
+------+------+--------+--------+---------+--------+------+
| id   | name | gender | cardid | account | level  | acc  |
+------+------+--------+--------+---------+--------+------+
| 1    | zyf  | male   | 24156  |    2.50 | normal | NULL |
| 2    | lbp  | female | abcde  |   46.00 | VIP    | NULL |
| 3    | wqy  | male   | 22233  |   21.00 | normal | NULL |
| 4    | ljp  | male   | 24156  |  648.20 | normal | NULL |
| 5    | ds   | unknow | 11111  | 1000.00 | SVIP   | NULL |
| 6    | yzy  | male   | 66677  |  100.00 | normal | NULL |
+------+------+--------+--------+---------+--------+------+
6 rows in set (0.00 sec)

Ⅳ、嵌套

#将test.class表中id大于1或者id大于3并小于5的数据根据age进行倒叙排列
mysql -uroot -proot -e'select * from test.class where id>1 or (id>3 and id<5)
 order by age desc;'

+----+-------+-----+---------+
| id | name  | age | address |
+----+-------+-----+---------+
|  4 | user4 | 44  | road4   |
|  3 | user3 | 23  | road3   |
|  2 | user2 | 22  | road2   |
+----+-------+-----+---------+