MySQL---进阶查询

MySQL进阶查询

按关键字排序

• 使用ORDER BY语句来实现排序
• 排序可针对一个或多个字段
• ASC: 升序，默认排序方式
• DESC:降序

ORDER BY的语法结构

SELECT column1, column2, .. FROM table_ name ORDER BY column1, column2, ..
ASC|DESC;

按单字段排序

我创建了一个mask库，mess表

mysql> select * from mess;
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | zs   |    90 |
|  2 | ls   |    80 |
|  3 | ww   |    70 |
|  4 | zl   |    60 |
+----+------+-------+
4 rows in set (0.00 sec)
###单字段降序
mysql> select * from mess where score>=70 order by score desc;
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | zs   |    90 |
|  2 | ls   |    80 |
|  3 | ww   |    70 |
+----+------+-------+
3 rows in set (0.00 sec)
###单字段升序，默认即为升序ASC
mysql> select * from mess where score>=70 order by score;
+----+------+-------+
| id | name | score |
+----+------+-------+
|  3 | ww   |    70 |
|  2 | ls   |    80 |
|  1 | zs   |    90 |
+----+------+-------+
3 rows in set (0.00 sec)

按多字段排序

多字段排序，大概就是先按一个字段进行排序，如果有相同的属性的值再对此进行排序；
如下先按分数进行升序，然后在分数相同的记录中再按照id进行升序排序

mysql> select * from mess order by score ,id ;
+----+------+-------+
| id | name | score |
+----+------+-------+
|  4 | zl   |    60 |
|  3 | ww   |    70 |
|  5 | hb   |    70 |
|  2 | ls   |    80 |
|  1 | zs   |    90 |
+----+------+-------+
5 rows in set (0.00 sec)

对结果进行分组

• 使用GROUP BY语句来实现分组
• 通常结合聚合函数一起使用
• 可以按一个或多个字段对结果进行分组

GROUP BY的语法结构

SELECT column name, aggregate_ function(column_ name) FROM table_ name
WHERE column_ name operator value GROUP BY column_ name;

group by大概就是将字段进行分组，可以再对组内的数据进行统计
如下，把score进行分组，分完后计算组内name的个数；将分数进行降序排序

mysql> select count(name),score from mess group by score order by score desc;
+-------------+-------+
| count(name) | score |
+-------------+-------+
|           1 |    90 |
|           1 |    80 |
|           2 |    70 |
|           1 |    60 |
+-------------+-------+
4 rows in set (0.00 sec)
##group by 不与聚合函数用，可以将相应重复的字段给过滤了
mysql> select * from mess group by score order by score desc;
+----+------+-------+-------+
| id | name | score | award |
+----+------+-------+-------+
|  1 | zs   |    90 |     1 |
|  2 | ls   |    80 |     1 |
|  3 | ww   |    70 |     2 |
|  4 | zl   |    60 |     2 |
+----+------+-------+-------+

上面的语句中有一个count()聚合函数，可以有如下类型

• count(字段名称) 计数
• sum(字段名称) 求和
• avg(字段名称) 平均值
• max(字段名称) 最大值
• min(字段名称) 最小值

mysql> select avg(score) from mess;
+------------+
| avg(score) |
+------------+
|    74.0000 |
+------------+
1 row in set (0.00 sec)

mysql> select sum(score) from mess;
+------------+
| sum(score) |
+------------+
|        370 |
+------------+
1 row in set (0.00 sec)

mysql> select max(score) from mess;
+------------+
| max(score) |
+------------+
|         90 |
+------------+
1 row in set (0.00 sec)

限制结果条目

• 只返回SELECT查询结果的第一行或前几行
• 使用LIMIT语句限制条目

LIMIT语法结构

offset位置偏移量，从0开始
SELECT column1, column2, .. FROM table_ name LIMIT [offset,] number;
number返回记录行的最大数目

limit限制结果条目
显示3条

mysql> select * from mess limit 3 ;
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | zs   |    90 |
|  2 | ls   |    80 |
|  3 | ww   |    70 |
+----+------+-------+
3 rows in set (0.00 sec)

不从第一条开始，从索引值后输出相应数量的条目，索引值从0开始
下面表示从索引值为1的记录开始，输出3条记录，也就是第2条到第5条记录

mysql> select * from mess limit 1,3;
+----+------+-------+
| id | name | score |
+----+------+-------+
|  2 | ls   |    80 |
|  3 | ww   |    70 |
|  4 | zl   |    60 |
+----+------+-------+
3 rows in set (0.01 sec)

设置别名

• 使用AS语句设置别名，关键字AS可省略
• 设置别名时，保证不能与库中其他表或字段名称冲突

别名的语法结构

alias _name 列的别名
SELECT column_ name AS alias _name FROM table_ name;
alias name表的别名
SELECT column_ name(s) FROM table_ name AS alias name;
原字段或者表名在库内并不会被改变

字段别名

mysql> select count(*) from mess;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)
##别名
mysql> select count(*) as number from mess;
+--------+
| number |
+--------+
|      5 |
+--------+
1 row in set (0.00 sec)

表的别名

mysql> select a.id,a.name from mess as a;
+----+------+
| id | name |
+----+------+
|  1 | zs   |
|  2 | ls   |
|  3 | ww   |
|  4 | zl   |
|  5 | hb   |
+----+------+
5 rows in set (0.00 sec)

拓展
改善下原来的mess表

##插入一列award
mysql> alter table mess add column award int(3);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from mess;
+----+------+-------+-------+
| id | name | score | award |
+----+------+-------+-------+
|  1 | zs   |    9