后端基础SQL——高级查询与子查询

1、MySQL的基础查询语句

     ①排序：知道字段的情况下 select*from  表 order  by  字段名；

mysql> select*from bdd;
+----+--------------+
| xh | xm           |
+----+--------------+
| 43 | Hello        |
|  2 | bb           |
|  3 | bb           |
| 40 | Hello        |
| 41 | Hello        |
| 42 | bb           |
| 44 | 0x48656c6c6f |
+----+--------------+
7 rows in set (0.00 sec)

mysql> select*from bdd order by xh;
+----+--------------+
| xh | xm           |
+----+--------------+
|  2 | bb           |
|  3 | bb           |
| 40 | Hello        |
| 41 | Hello        |
| 42 | bb           |
| 43 | Hello        |
| 44 | 0x48656c6c6f |
+----+--------------+
7 rows in set (0.00 sec)

在不知道字段名的情况下，可以使用order by 1这样的语句；

order by 1指的是第一个字段排序，order by 2指的是第二个字段排序以此类推；

mysql> select*from bdd order by 1;
+----+--------------+
| xh | xm           |
+----+--------------+
|  2 | bb           |
|  3 | bb           |
| 40 | Hello        |
| 41 | Hello        |
| 42 | bb           |
| 43 | Hello        |
| 44 | 0x48656c6c6f |
+----+--------------+
7 rows in set (0.00 sec)

mysql> select*from bdd order by 2;
+----+--------------+
| xh | xm           |
+----+--------------+
| 44 | 0x48656c6c6f |
|  2 | bb           |
|  3 | bb           |
| 42 | bb           |
| 43 | Hello        |
| 40 | Hello        |
| 41 | Hello        |
+----+--------------+
7 rows in set (0.00 sec)

  desc倒序         asc正序；

mysql> select*from bdd order by xh desc;
+----+--------------+
| xh | xm           |
+----+--------------+
| 44 | 0x48656c6c6f |
| 43 | Hello        |
| 42 | bb           |
| 41 | Hello        |
| 40 | Hello        |
|  3 | bb           |
|  2 | bb           |
+----+--------------+
7 rows in set (0.00 sec)

mysql> select*from bdd order by xh asc;
+----+--------------+
| xh | xm           |
+----+--------------+
|  2 | bb           |
|  3 | bb           |
| 40 | Hello        |
| 41 | Hello        |
| 42 | bb           |
| 43 | Hello        |
| 44 | 0x48656c6c6f |
+----+--------------+
7 rows in set (0.00 sec)

②分页：select * from 表 limit n ， m；[注：n表示从第几行开始，m表示取几条]

mysql> select*from bdd;
+----+--------------+
| xh | xm           |
+----+--------------&