ORDER对查询结果进行排序, LIMIT限制显示条数

降序、升序 与随机排序

  1. ASC 是升序,DESC是降序,默认是升序排列,注意,这里是对查询结果进行排序
  2. 按照年龄的升序, 降序排列
mysql> SELECT*FROM cms_user ORDER BY id ASC;
+----+-----------+--------------+-------------+------------+----------+-------+------+---------+
| id | username  | password     | email       | regTime    | face     | proId | age  | sex     |
+----+-----------+--------------+-------------+------------+----------+-------+------+---------+
|  1 | 张三      | zhangsan     | user@qq.com | 1419811708 | user.jpg |     1 | NULL | male    |
|  2 | 张三丰    | zhangsanfeng | user@qq.com | 1419812708 | user.jpg |     2 |   23 | female  |
|  3 | 章子怡    | zhangsan     | user@qq.com | 1419813708 | user.jpg |     3 |   46 | male    |
|  4 | long      | long         | user@qq.com | 1419814708 | user.jpg |     4 |   77 | female  |
|  5 | ring      | ring         | user@qq.com | 1419815708 | user.jpg |     2 |   54 | male    |
|  6 | queen     | queen        | user@qq.com | 1419861708 | user.jpg |     3 |   64 | female  |
|  7 | king      | king         | user@qq.com | 1419817708 | user.jpg |     5 |   28 | male    |
|  8 | blek      | blek         | user@qq.com | 1419818708 | user.jpg |     1 |   98 | female  |
|  9 | rose      | rose         | user@qq.com | 1419821708 | user.jpg |     2 |   27 | male    |
| 10 | lily      | lily         | user@qq.com | 1419831708 | user.jpg |     2 |   27 | female  |
| 11 | john      | john         | user@qq.com | 1419841708 | user.jpg |     2 |   31 | unknown |
| 12 | test1     | test1        | user@qq.com | 1419811708 | user.jpg |     1 |   60 | unknown |
+----+-----------+--------------+-------------+------------+----------+-------+------+---------+
12 rows in set (0.00 sec)

mysql> SELECT*FROM cms_user ORDER BY id DESC;
+----+-----------+--------------+-------------+------------+----------+-------+------+---------+
| id | username  | password     | email       | regTime    | face     | proId | age  | sex     |
+----+-----------+--------------+-------------+------------+----------+-------+------+---------+
| 12 | test1     | test1        | user@qq.com | 1419811708 | user.jpg |     1 |   60 | unknown |
| 11 | john      | john         | user@qq.com | 1419841708 | user.jpg |     2 |   31 | unknown |
| 10 | lily      | lily         | user@qq.com | 1419831708 | user.jpg |     2 |   27 | female  |
|  9 | rose      | rose         | user@qq.com | 1419821708 | user.jpg |     2 |   27 | male    |
|  8 | blek      | blek         | user@qq.com | 1419818708 | user.jpg |     1 |   98 | female  |
|  7 | king      | king         | user@qq.com | 1419817708 | user.jpg |     5 |   28 | male    |
|  6 | queen     | queen        | user@qq.com | 1419861708 | user.jpg |     3 |   64 | female  |
|  5 | ring      | ring         | user@qq.com | 1419815708 | user.jpg |     2 |   54 | male    |
|  4 | long      | long         | user@qq.com | 1419814708 | user.jpg |     4 |   77 | female  |
|  3 | 章子怡    | zhangsan     | user@qq.com | 1419813708 | user.jpg |     3 |   46 | male    |
|  2 | 张三丰    | zhangsanfeng | user@qq.com | 1419812708 | user.jpg |     2 |   23 | female  |
|  1 | 张三      | zhangsan     | user@qq.com | 1419811708 | user.jpg |     1 | NULL | male    |
+----+-----------+--------------+-------------+------------+----------+-------+------+---------+
12 rows in set (0.00 sec)
  1. 使用字段的位置按照降序排列
    这里我用了order by 7 意思就是按照proId的升序排列的
mysql> SELECT*FROM cms_user ORDER BY 7;
+----+-----------+--------------+-------------+------------+----------+-------+------+---------+
| id | username  | password     | email       | regTime    | face     | proId | age  | sex     |
+----+-----------+--------------+-------------+------------+----------+-------+------+---------+
|  1 | 张三      | zhangsan     | user@qq.com | 1419811708 | user.jpg |     1 | NULL | male    |
|  8 | blek      | blek         | user@qq.com | 1419818708 | user.jpg |     1 |   98 | female  |
| 12 | test1     | test1        | user@qq.com | 1419811708 | user.jpg |     1 |   60 | unknown |
|  2 | 张三丰    | zhangsanfeng | user@qq.com | 1419812708 | user.jpg |     2 |   23 | female  |
|  5 | ring      | ring         | user@qq.com | 1419815708 | user.jpg |     2 |   54 | male    |
|  9 | rose      | rose         | user@qq.com | 1419821708 | user.jpg |     2 |   27 | male    |
| 10 | lily      | lily         | user@qq.com | 1419831708 | user.jpg |     2 |   27 | female  |
| 11 | john      | john         | user@qq.com | 1419841708 | user.jpg |     2 |   31 | unknown |
|  3 | 章子怡    | zhangsan     | user@qq.com | 1419813708 | user.jpg |     3 |   46 | male    |
|  6 | queen     | queen        | user@qq.com | 1419861708 | user.jpg |     3 |   64 | female  |
|  4 | long      | long         | user@qq.com | 1419814708 | user.jpg |     4 |   77 | female  |
|  7 | king      | king         | user@qq.com | 1419817708 | user.jpg |     5 |   28 | male    |
+----+-----------+--------------+-------------+------------+----------+-------+------+---------+
  1. 按照多个字段排
  • 按照年龄升序,id降序来排列
  • 会先考虑第一个字段的排序,再在第一个字段相同的情况下按照第二个字段来排序
mysql> SELECT*FROM cms_user ORDER BY proId, age DESC;
+----+-----------+--------------+-------------+------------+----------+-------+------+---------+
| id | username  | password     | email       | regTime    | face     | proId | age  | sex     |
+----+-----------+--------------+-------------+------------+----------+-------+------+---------+
|  8 | blek      | blek         | user@qq.com | 1419818708 | user.jpg |     1 |   98 | female  |
| 12 | test1     | test1        | user@qq.com | 1419811708 | user.jpg |     1 |   60 | unknown |
|  1 | 张三      | zhangsan     | user@qq.com | 1419811708 | user.jpg |     1 | NULL | male    |
|  5 | ring      | ring         | user@qq.com | 1419815708 | user.jpg |     2 |   54 | male    |
| 11 | john      | john         | user@qq.com | 1419841708 | user.jpg |     2 |   31 | unknown |
|  9 | rose      | rose         | user@qq.com | 1419821708 | user.jpg |     2 |   27 | male    |
| 10 | lily      | lily         | user@qq.com | 1419831708 | user.jpg |     2 |   27 | female  |
|  2 | 张三丰    | zhangsanfeng | user@qq.com | 1419812708 | user.jpg |     2 |   23 | female  |
|  6 | queen     | queen        | user@qq.com | 1419861708 | user.jpg |     3 |   64 | female  |
|  3 | 章子怡    | zhangsan     | user@qq.com | 1419813708 | user.jpg |     3 |   46 | male    |
|  4 | long      | long         | user@qq.com | 1419814708 | user.jpg |     4 |   77 | female  |
|  7 | king      | king         | user@qq.com | 1419817708 | user.jpg |     5 |   28 | male    |
+----+-----------+--------------+-------------+------------+----------+-------+------+---------+
12 rows in set (0.00 sec)
  1. 可以通过ORDER BY RAND()进行随机排序
mysql> SELECT*FROM cms_user ORDER BY RAND();
+----+-----------+--------------+-------------+------------+----------+-------+------+---------+
| id | username  | password     | email       | regTime    | face     | proId | age  | sex     |
+----+-----------+--------------+-------------+------------+----------+-------+------+---------+
| 10 | lily      | lily         | user@qq.com | 1419831708 | user.jpg |     2 |   27 | female  |
| 12 | test1     | test1        | user@qq.com | 1419811708 | user.jpg |     1 |   60 | unknown |
|  6 | queen     | queen        | user@qq.com | 1419861708 | user.jpg |     3 |   64 | female  |
|  7 | king      | king         | user@qq.com | 1419817708 | user.jpg |     5 |   28 | male    |
|  2 | 张三丰    | zhangsanfeng | user@qq.com | 1419812708 | user.jpg |     2 |   23 | female  |
|  3 | 章子怡    | zhangsan     | user@qq.com | 1419813708 | user.jpg |     3 |   46 | male    |
|  9 | rose      | rose         | user@qq.com | 1419821708 | user.jpg |     2 |   27 | male    |
| 11 | john      | john         | user@qq.com | 1419841708 | user.jpg |     2 |   31 | unknown |
|  8 | blek      | blek         | user@qq.com | 1419818708 | user.jpg |     1 |   98 | female  |
|  1 | 张三      | zhangsan     | user@qq.com | 1419811708 | user.jpg |     1 | NULL | male    |
|  5 | ring      | ring         | user@qq.com | 1419815708 | user.jpg |     2 |   54 | male    |
|  4 | long      | long         | user@qq.com | 1419814708 | user.jpg |     4 |   77 | female  |
+----+-----------+--------------+-------------+------------+----------+-------+------+---------+
12 rows in set (0.01 sec)

LIMIT 限制显示条数

  1. 这是对于结果集中显示条数的显示,和编号无关,LIMIT 是显示分页的核心
mysql> SELECT*FROM cms_user ORDER BY id DESC LIMIT 5;
+----+----------+----------+-------------+------------+----------+-------+------+---------+
| id | username | password | email       | regTime    | face     | proId | age  | sex     |
+----+----------+----------+-------------+------------+----------+-------+------+---------+
| 12 | test1    | test1    | user@qq.com | 1419811708 | user.jpg |     1 |   60 | unknown |
| 11 | john     | john     | user@qq.com | 1419841708 | user.jpg |     2 |   31 | unknown |
| 10 | lily     | lily     | user@qq.com | 1419831708 | user.jpg |     2 |   27 | female  |
|  9 | rose     | rose     | user@qq.com | 1419821708 | user.jpg |     2 |   27 | male    |
|  8 | blek     | blek     | user@qq.com | 1419818708 | user.jpg |     1 |   98 | female  |
+----+----------+----------+-------------+------------+----------+-------+------+---------+
5 rows in set (0.00 sec)
  1. 查询第一条记录:
mysql> SELECT*FROM cms_user LIMIT 0,1;
+----+----------+----------+-------------+------------+----------+-------+------+------+
| id | username | password | email       | regTime    | face     | proId | age  | sex  |
+----+----------+----------+-------------+------------+----------+-------+------+------+
|  1 | 张三     | zhangsan | user@qq.com | 1419811708 | user.jpg |     1 | NULL | male |
+----+----------+----------+-------------+------------+----------+-------+------+------+
1 row in set (0.00 sec)
  1. 查询下一条记录
  • LIMIT(A, B) A参数代表第一条记录的偏移量,是从0开始,B代表每一页显示的记录数,比如LIMIT 5,5指的就是从第六条开始往下打印5条记录
mysql> SELECT*FROM cms_user LIMIT 5,5;
+----+----------+----------+-------------+------------+----------+-------+------+--------+
| id | username | password | email       | regTime    | face     | proId | age  | sex    |
+----+----------+----------+-------------+------------+----------+-------+------+--------+
|  6 | queen    | queen    | user@qq.com | 1419861708 | user.jpg |     3 |   64 | female |
|  7 | king     | king     | user@qq.com | 1419817708 | user.jpg |     5 |   28 | male   |
|  8 | blek     | blek     | user@qq.com | 1419818708 | user.jpg |     1 |   98 | female |
|  9 | rose     | rose     | user@qq.com | 1419821708 | user.jpg |     2 |   27 | male   |
| 10 | lily     | lily     | user@qq.com | 1419831708 | user.jpg |     2 |   27 | female |
+----+----------+----------+-------------+------------+----------+-------+------+--------+
5 rows in set (0.00 sec)

  1. 更新删除应用order by和LIMIT
  • 更新前三条记录,让以有年龄+10
mysql> SELECT*FROM cms_user;
+----+-----------+--------------+-------------+------------+----------+-------+------+---------+
| id | username  | password     | email       | regTime    | face     | proId | age  | sex     |
+----+-----------+--------------+-------------+------------+----------+-------+------+---------+
|  1 | 张三      | zhangsan     | user@qq.com | 1419811708 | user.jpg |     1 | NULL | male    |
|  2 | 张三丰    | zhangsanfeng | user@qq.com | 1419812708 | user.jpg |     2 |   33 | female  |
|  3 | 章子怡    | zhangsan     | user@qq.com | 1419813708 | user.jpg |     3 |   56 | male    |
|  4 | long      | long         | user@qq.com | 1419814708 | user.jpg |     4 |   77 | female  |
|  5 | ring      | ring         | user@qq.com | 1419815708 | user.jpg |     2 |   54 | male    |
|  6 | queen     | queen        | user@qq.com | 1419861708 | user.jpg |     3 |   64 | female  |
|  7 | king      | king         | user@qq.com | 1419817708 | user.jpg |     5 |   28 | male    |
|  8 | blek      | blek         | user@qq.com | 1419818708 | user.jpg |     1 |   98 | female  |
|  9 | rose      | rose         | user@qq.com | 1419821708 | user.jpg |     2 |   27 | male    |
| 10 | lily      | lily         | user@qq.com | 1419831708 | user.jpg |     2 |   27 | female  |
| 11 | john      | john         | user@qq.com | 1419841708 | user.jpg |     2 |   31 | unknown |
| 12 | test1     | test1        | user@qq.com | 1419811708 | user.jpg |     1 |   60 | unknown |
+----+-----------+--------------+-------------+------------+----------+-------+------+---------+
12 rows in set (0.00 sec)

mysql> UPDATE cms_user SET age=age+10 LIMIT 3;
Query OK, 2 rows affected (0.04 sec)
Rows matched: 3  Changed: 2  Warnings: 0

mysql> SELECT*FROM cms_user;
+----+-----------+--------------+-------------+------------+----------+-------+------+---------+
| id | username  | password     | email       | regTime    | face     | proId | age  | sex     |
+----+-----------+--------------+-------------+------------+----------+-------+------+---------+
|  1 | 张三      | zhangsan     | user@qq.com | 1419811708 | user.jpg |     1 | NULL | male    |
|  2 | 张三丰    | zhangsanfeng | user@qq.com | 1419812708 | user.jpg |     2 |   43 | female  |
|  3 | 章子怡    | zhangsan     | user@qq.com | 1419813708 | user.jpg |     3 |   66 | male    |
|  4 | long      | long         | user@qq.com | 1419814708 | user.jpg |     4 |   77 | female  |
|  5 | ring      | ring         | user@qq.com | 1419815708 | user.jpg |     2 |   54 | male    |
|  6 | queen     | queen        | user@qq.com | 1419861708 | user.jpg |     3 |   64 | female  |
|  7 | king      | king         | user@qq.com | 1419817708 | user.jpg |     5 |   28 | male    |
|  8 | blek      | blek         | user@qq.com | 1419818708 | user.jpg |     1 |   98 | female  |
|  9 | rose      | rose         | user@qq.com | 1419821708 | user.jpg |     2 |   27 | male    |
| 10 | lily      | lily         | user@qq.com | 1419831708 | user.jpg |     2 |   27 | female  |
| 11 | john      | john         | user@qq.com | 1419841708 | user.jpg |     2 |   31 | unknown |
| 12 | test1     | test1        | user@qq.com | 1419811708 | user.jpg |     1 |   60 | unknown |
+----+-----------+--------------+-------------+------------+----------+-------+------+---------+
12 rows in set (0.00 sec)
  • 不能用偏移量的方法写更新或者删除LIMIT
mysql> UPDATE cms_user SET age=age+4 LIMIT 1,3;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '3' at line 1
  • 删除用户性别为男的用户,按照年龄降序排列,只删除降序五条里的记录
mysql> SELECT*FROM cms_user;
+----+-----------+--------------+-------------+------------+----------+-------+------+---------+
| id | username  | password     | email       | regTime    | face     | proId | age  | sex     |
+----+-----------+--------------+-------------+------------+----------+-------+------+---------+
|  2 | 张三丰    | zhangsanfeng | user@qq.com | 1419812708 | user.jpg |     2 |   43 | female  |
|  4 | long      | long         | user@qq.com | 1419814708 | user.jpg |     4 |   77 | female  |
|  6 | queen     | queen        | user@qq.com | 1419861708 | user.jpg |     3 |   64 | female  |
|  8 | blek      | blek         | user@qq.com | 1419818708 | user.jpg |     1 |   98 | female  |
| 10 | lily      | lily         | user@qq.com | 1419831708 | user.jpg |     2 |   27 | female  |
| 11 | john      | john         | user@qq.com | 1419841708 | user.jpg |     2 |   31 | unknown |
| 12 | test1     | test1        | user@qq.com | 1419811708 | user.jpg |     1 |   60 | unknown |
+----+-----------+--------------+-------------+------------+----------+-------+------+---------+
7 rows in set (0.00 sec)

总结

1. SELECT

  • SELECT 字段名称 FROM tbl_name
  • 运用AS 给字段或是table起别名

2. WHERE

  • 添加比较俩条件例如>=, <=, !=,
  • 添加指定范围, BETWEEN AND
  • 添加出现的集合 IN
  • 模糊查询 LIKE
  • 多条件查询 AND, OR

3. GROUP BY

  • GROUP BY 指的是分组
  • 按字段分组
  • 按字段位置分组
  • 聚合函数 GROUP_CONCAT()
    – 显示某个字段分组下的详情
  • COUNT()
    – 查询每个分组中的总人数
    – 字段有空值时,不统计空值
  • MAX()
  • MIN()
  • AVG()
  • SUM()
  • ROLLUP
    – 对分组后的每一列进行总结

4. HAVING

  • 对分组进行二次筛选

5. ORDER BY

  • 对分组进行升,降,或乱序的排列
  • ASC升序
  • DESC降序

6. LIMIT

  • 限制显示条数
  • 可以使用LIMIT A,B 来选择从第A条开始往下显示B条内容
mysql> SELECT id,age,sex, GROUP_CONCAT(username) AS username,
    -> COUNT(*) AS totalusers,
    -> SUM(age) AS SumAge,
    -> MAX(age) AS MaxAge,
    -> MIN(age) AS MinAge,
    -> AVG(age) AS AveAge
    -> FROM cms_user
    -> WHERE id>=1
    -> GROUP BY sex
    -> HAVING COUNT(*)>=2
    -> ORDER BY age DESC
    -> LIMIT 0,2;
+----+------+---------+--------------------------------+------------+--------+--------+--------+---------+
| id | age  | sex     | username                       | totalusers | SumAge | MaxAge | MinAge | AveAge  |
+----+------+---------+--------------------------------+------------+--------+--------+--------+---------+
| 11 |   31 | unknown | john,test1                     |          2 |     91 |     60 |     31 | 45.5000 |
|  2 |   23 | female  | 张三丰,long,queen,blek,lily    |          5 |    289 |     98 |     23 | 57.8000 |
+----+------+---------+--------------------------------+------------+--------+--------+--------+---------+
2 rows in set (0.00 sec)
  • 使用删除DELETE 和更新UPDATE的时候,不能使用偏移量的方法
©️2020 CSDN 皮肤主题: 深蓝海洋 设计师:CSDN官方博客 返回首页