1. 排序 order by
1
| mysql> SELCET * FROM table_name ORDER BY author ASC;
|
排序:asc正序
desc 倒序
将table_name表中的数据按照作者名称倒序排列
2. 分组
1
| mysql> select author,count(*) from yable_name group by author;
|
将表中数据按照作者名字统计
1
2
3
4
5
6
7
8
9
| +--------+----------+
| author | count(*) |
+--------+----------+
| Jick | 1 |
| John | 1 |
| To | 1 |
| Tomy | 2 |
| 小 | 2 |
+--------+----------+
|
分组数据再归纳
1
2
3
4
5
6
7
8
9
10
11
12
| mysql> select author,count(*) from table_name group by author with rollup;
+--------+----------+
| author | count(*) |
+--------+----------+
| Jick | 1 |
| John | 1 |
| To | 1 |
| Tomy | 2 |
| 小 | 2 |
| NULL | 7 |
+--------+----------+
|
3. 连接多个表的查询结果
a) 创建新数据表并设置外键
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
| mysql> create table table_name2(
> id int not null auto_increment,
> author_id int not null,
> comments text not null,
> create_date date,
> primary key (id),
> foreign key (author\_id) references test\_table(id),
> )engine=InnoDB default charset=utf8;
mysql> select a.id,a.author,b.comments from table\_name a inner join table\_name2 b on a.id = b.author_id;
+----+--------+----------+
| id | author | comments |
+----+--------+----------+
| 3 | 小 | like |
| 4 | Tomy | like |
| 4 | Tomy | love |
+----+--------+----------+
|