🔥distinct的使用🔥
distinct用于在查询中返回列的唯一不同值(去重复),支持单列或多列。在实际的应用中,表中的某一列含有重复值是很常见的,如employ表的dept列。如果在查询数据时,希望得到某列的所有不同值,可以使用distinct。
distinct语法
select distinct column_name,column_name from table_name;
mysql> select * from employee; +----+--------+------+--------+-------+ | id | name | sex | salary | dept | +----+--------+------+--------+-------+ | 1 | 张三 | 男 | 5500 | 部门A | | 2 | 李四 | 男 | 4500 | 部门B | | 3 | 张小妹 | 女 | 4500 | 部门B | +----+--------+------+--------+-------+ 3 rows in set (0.00 sec) mysql> insert into employee(name,sex,salary,dept) values('张三','1',6000,'部门D'); Query OK, 1 row affected (0.01 sec) mysql> select * from employee; +----+--------+------+--------+-------+ | id | name | sex | salary | dept | +----+--------+------+--------+-------+ | 1 | 张三 | 男 | 5500 | 部门A | | 2 | 李四 | 男 | 4500 | 部门B | | 3 | 张小妹 | 女 | 4500 | 部门B | | 4 | 张三 | 1 | 6000 | 部门D | +----+--------+------+--------+-------+ 4 rows in set (0.00 sec)
mysql> select * from employee; +----+--------+------+--------+-------+ | id | name | sex | salary | dept | +----+--------+------+--------+-------+ | 1 | 张三 | 男 | 5500 | 部门A | | 2 | 李四 | 男 | 4500 | 部门B | | 3 | 张小妹 | 女 | 4500 | 部门B | | 4 | 张三 | 1 | 6000 | 部门D | +----+--------+------+--------+-------+ 4 rows in set (0.00 sec) mysql> select distinct name from employee; +--------+ | name | +--------+ | 张三 | | 李四 | | 张小妹 | +--------+ 3 rows in set (0.00 sec)
mysql> select * from employee; +----+--------+------+--------+-------+ | id | name | sex | salary | dept | +----+--------+------+--------+-------+ | 1 | 张三 | 男 | 5500 | 部门A | | 2 | 李四 | 男 | 4500 | 部门B | | 3 | 张小妹 | 女 | 4500 | 部门B | | 4 | 张三 | 1 | 6000 | 部门D | +----+--------+------+--------+-------+ 4 rows in set (0.00 sec) mysql> select distinct sex from employee; +------+ | sex | +------+ | 男 | | 女 | | 1 | +------+ 3 rows in set (0.00 sec) mysql> select distinct dept from employee; +-------+ | dept | +-------+ | 部门A | | 部门B | | 部门D | +-------+ 3 rows in set (0.00 sec) mysql> select dept from employee group by dept; +-------+ | dept | +-------+ | 部门A | | 部门B | | 部门D | +-------+ 3 rows in set (0.00 sec)
mysql> select distinct username from foot; +----------+ | username | +----------+ | 大哥 | | 大哥1 | | 大哥2 | | 大哥4 | | 大哥5 | | 大哥6 | | 大哥7 | | 大哥8 | | 大哥9 | +----------+ 9 rows in set (0.00 sec) mysql> select * from foot; +----+----------+-------+------------+ | id | username | city | visit_date | +----+----------+-------+------------+ | 1 | 大哥 | 上海 | 2022-01-01 | | 2 | 大哥1 | 上海1 | 2022-01-02 | | 3 | 大哥2 | 上海2 | 2022-01-03 | | 35 | 大哥4 | 上海4 | 2022-01-05 | | 36 | 大哥5 | 上海5 | 2022-01-06 | | 37 | 大哥6 | 上海6 | 2022-01-07 | | 38 | 大哥6 | 上海6 | 2022-01-07 | | 39 | 大哥7 | 上海7 | 2022-01-08 | | 40 | 大哥8 | 上海8 | 2022-01-09 | | 41 | 大哥9 | 上海9 | 2022-01-10 | +----+----------+-------+------------+ 10 rows in set (0.00 sec)
mysql> select distinct username,city from foot; +----------+-------+ | username | city | +----------+-------+ | 大哥 | 上海 | | 大哥1 | 上海1 | | 大哥2 | 上海2 | | 大哥4 | 上海4 | | 大哥5 | 上海5 | | 大哥6 | 上海6 | | 大哥7 | 上海7 | | 大哥8 | 上海8 | | 大哥9 | 上海9 | +----------+-------+ 9 rows in set (0.00 sec) mysql> select distinct username from foot; +----------+ | username | +----------+ | 大哥 | | 大哥1 | | 大哥2 | | 大哥4 | | 大哥5 | | 大哥6 | | 大哥7 | | 大哥8 | | 大哥9 | +----------+ 9 rows in set (0.00 sec)