查询

Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.1.55-community MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test_1
Database changed
mysql> select * from student;
ERROR 1146 (42S02): Table 'test_1.student' doesn't exist
mysql> select * from students;
+--------+----------+---------+
| stu_id | stu_name | stu_sex |
+--------+----------+---------+
| 1 | xiaobai | F |
+--------+----------+---------+
1 row in set (0.01 sec)

mysql> select * from students;
+--------+----------+---------+
| stu_id | stu_name | stu_sex |
+--------+----------+---------+
| 1 | xiaobai | F |
| 2 | xiaobai | F |
| 3 | xiaobai | F |
| 4 | xiao | F |
| 5 | xiaoming | F |
+--------+----------+---------+
5 rows in set (0.00 sec)

mysql> select stu_id, stu_name, stu_sex from students group by stu_name;
+--------+----------+---------+
| stu_id | stu_name | stu_sex |
+--------+----------+---------+
| 4 | xiao | F |
| 1 | xiaobai | F |
| 5 | xiaoming | F |
+--------+----------+---------+
3 rows in set (0.01 sec)

mysql> select distinct stu_name from students;
+----------+
| stu_name |
+----------+
| xiaobai |
| xiao |
| xiaoming |
+----------+
3 rows in set (0.00 sec)

mysql> select distinct stu_name, stu_id, stu_sex from students;
+----------+--------+---------+
| stu_name | stu_id | stu_sex |
+----------+--------+---------+
| xiaobai | 1 | F |
| xiaobai | 2 | F |
| xiaobai | 3 | F |
| xiao | 4 | F |
| xiaoming | 5 | F |
+----------+--------+---------+
5 rows in set (0.00 sec)

mysql> select *, count(distinct stu_name) from students group by stu_name;
+--------+----------+---------+--------------------------+
| stu_id | stu_name | stu_sex | count(distinct stu_name) |
+--------+----------+---------+--------------------------+
| 4 | xiao | F | 1 |
| 1 | xiaobai | F | 1 |
| 5 | xiaoming | F | 1 |
+--------+----------+---------+--------------------------+
3 rows in set (0.01 sec)

mysql>

目的：从表中筛选出不重复的

1.从表中仅仅将某一列的不重复项给筛选出来
select distinct stu_name from students;
仅仅将students表中stu_name列里不重复的项给筛选出来

2.从表中将某一列的不重复项给筛选出来，同时其关联的其它列也一起显示出来
select stu_id, stu_name, stu_sex from students group by stu_name;
使用的是group by
note：重复项保留的是最先出现的那一行

3.select *, count(distinct stu_name) from students group by stu_name;
会在输出的地方添加count（distinct stu_name)列

4.select distinct stu_name, stu_id, stu_sex from students;
会改变列的列号

5.select *, count(distinct stu_name) from students;

mysql> select *, count(distinct stu_name) from students;
+--------+----------+---------+--------------------------+
| stu_id | stu_name | stu_sex | count(distinct stu_name) |
+--------+----------+---------+--------------------------+
| 1 | xiaobai | F | 3 |
+--------+----------+---------+--------------------------+
1 row in set (0.00 sec)

转载于:https://www.cnblogs.com/qbin/p/10041523.html