MySQL高级语句操作

按关键字排序

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

SELECT column1. column2, ...FROM table_name ORDER BY column1, column2, ...ASCDESC;

按关键字排序

• 按单字段排序
• 按多字段排序
  mysq> selectid,name,level from player where fevel>=45order by level desc, id desc;
  在level都是46的情况下，id按照降序排列

+-----+------------+------+
|id | name|level|
+-----+------------+------+
| 30 | aaa | 47 |
| 298 | bbb | 46 |
| 199 | ccc | 46 |
| 63 | ddd | 46 |
| 15 | eee| 46 |
| 272 | fff | 45 |
| 51 |  | ggg | 43 |
+-----+------------+------+
7 rows in set (0.00 sec)

对结果进行分组

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

SELECT column_name, aggregate_function(column_name)FROM table_nameWHERE column_name operator value GROUP BY column_name;

对结果进行分组

• GROUP BY分组

mysql> select count(name),level from player where level>=45 group by level;mysq select count(name),level from player where level>=45 group by levelorder by count(name) desc;
+-------------+-------+
| count(name) | level |
+-------------+-------+
| 4 | 46 |
| 2 | 45 |
| 1 | 47 |
+-------------+-------+

对结果进行分组

• 使用GROUP BY语句来实现分组
• 通常结合聚合函数一起使用
• 可以按一个或多个字段对结果进行分组
• GROUP BY的语法结构
  SELECT column_name, aggregate_function(column_name) FROM table_nameWHERE column_name operator value GROUP BY column_name;

对结果进行分组2-2
GROUP BY分组
GROUP BY结合ORDER BY

mysql>select count(name),level from player where level>=45 group by levelorder by count(name) desc;
+-------------+-------+
| count(name) | level |
+-------------+-------+
| 4 | 46 |
| 2 | 45 |
| 1 | 47 |
+-------------+-------+

限制结果条目

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

SELECT column1, column2, ...FROM table_name LIMIT [offset,] number;

[offset]:位置偏移景,从0开始
number:返回记录行的最大数目

限制结果条目

• LIMIT限制结果条数
• 不从第一条开始取值

mysq> select id,name,level from player limit 2,3; #从第3条记录开始显示之后的3条数据
+----+------------+-------+
| id | name | level |
+----+------------+-------+
| 3 | aa | 15 |
| 4 |  | 1 |
| 5 | cc | 35 |
+----+------------+-------+
3 rows in set (0.00 sec)

设置别名

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

SELECT column_name AS alias_name FROM table_name;

AS=列的别名

SELECT column_name(s)FROM table_name AS alias_name;

name(s)=原字段或首表名在库内并不会被改变
AS=表的别名

AS的用法

mysql> select count(*) as number from player;

as=字段的别名

+--------+
| number |
+--------+
| 3218 |
+--------+
1 row in set (0.01 sec)

mysql> select p.id,p.name from player as p limit 1;

player as=表的别名

+----+--------------+
| id | name |
+----+--------------+
| 1 | abc | 
+----+--------------+
3 rows in set (0.00 sec)

设置别名

• AS的用法
• AS作为连接语句

mysql> create table tmp as select * from player;
Query OK, 3215 rws affected (0.15 sec)
Records: 3218 Duplicates: 0 Warnings: 0

通配符

• 用于替换字符串中的部分字符
• 通常配合LIKE一起使用,并协同WHERE完成查询
• 常用通配符
• %表示零个、一个或多个
• _表示单个字符

通配符

• 通配符%的用法

mysql> select id,name,level from player where name like '%s';
+-----+---------+-------+
l id | name l level l
+-----+---------+-------+
| 448 | useless | 1 |
| 713 | guess | 25 |
+-----+---------+-------+
2 rows in set (0.00 sec)

• 通配符_的用法
  mysql> select id,name,level from player where name like "_uess;

+-----+-------+-------+
| id | name | level |
+-----+-------+-------+
| 713 | guess | 25 |
+-----+-------+-------+
1 row in set (0.01 sec)

两者结合使用

mysql> select id,name,level from player where name like '_es%";
+------+---------+-------+
l id | name | level |
+------+---------+-------+
| 2237 | leslie | 3 |
+------+---------+-------+
1 row in set (0.01 sec)