SELECT DISTINCT 可用于给出不同的值。用它来删除重复的记录并 它也可以与聚合函数一起使用。例如：MAX、AVG等。这个都可以应用 在单个列上。
现在，我正在创建一个表以对列使用 SELECT DISTINCT。创建一个表 CREATE 命令的帮助 -
mysql> CREATE TABLE DistinctDemo
-> (
-> id int,
-> name varchar(100)
-> );
Query OK, 0 rows affected (0.64 sec)插入记录 −
mysql> INSERT into DistinctDemo values(1,'John');
Query OK, 1 row affected (0.17 sec)
mysql> INSERT into DistinctDemo values(2,'John');
Query OK, 1 row affected (0.18 sec)
mysql> INSERT into DistinctDemo values(3,'Bob');
Query OK, 1 row affected (0.17 sec)
mysql> INSERT into DistinctDemo values(4,'John');
Query OK, 1 row affected (0.15 sec)
mysql> INSERT into DistinctDemo values(5,'David');
Query OK, 1 row affected (0.17 sec)
mysql> INSERT into DistinctDemo values(6,'Bob');
Query OK, 1 row affected (0.16 sec)借助 SELECT 命令显示所有记录。查询如下 -
mysql> SELECT * from DistinctDemo;
执行上述查询后，我们将获得以下输出 −
+------+-------+
| id | name |
+------+-------+
| 1 | John |
| 2 | John |
| 3 | Bob |
| 4 | John |
| 5 | David |
| 6 | Bob |
+------+-------+
6 rows in set (0.00 sec)上面，我们有六条记录，其中John和Bob是重复的。我们可以应用DISTINCT来 删除重复记录。语法如下 −
SELECT distinctcolumn_name from yourTableName order by column_name;
现在，我正在应用上述查询来删除重复项 -
mysql> SELECT distinct name from DistinctDemo;以下是输出
+-------+
| name |
+-------+
| John |
| Bob |
| David |
+-------+
3 rows in set (0.00 sec)Select group by 可用于从不同列获取数据并分组为一个或多个 column. This can also be applied with aggregate function.For Example:SUM,AVG etc.
Firstly, I am creating a table with the CREATE 命令的帮助 -
column. 这也可以与聚合函数一起使用。例如：SUM，AVG等。首先，我正在使用CREATE命令创建一个表 −
mysql> CREATE table GroupDemo1
-> (
-> id int,
-> name varchar(100),
-> address varchar(100)
-> );
Query OK, 0 rows affected (0.68 sec)创建表后，我将记录插入到表中，如下所示 −
mysql> INSERT into GroupDemo1 values(1,'John','US');
Query OK, 1 row affected (0.18 sec)
mysql> INSERT into GroupDemo1 values(2,'Bob','UK');
Query OK, 1 row affected (0.13 sec)
mysql> INSERT into GroupDemo1 values(3,'David','US');
Query OK, 1 row affected (0.12 sec)
mysql> INSERT into GroupDemo1 values(4,'David','US');
Query OK, 1 row affected (0.15 sec)现在，我们可以借助SELECT命令显示所有记录 −
mysql> SELECT * from GroupDemo1;
以下是输出 -
+------+-------+---------+
| id | name | address |
+------+-------+---------+
| 1 | John | US |
| 2 | Bob | UK |
| 3 | David | US |
| 4 | David | US |
+------+-------+---------+
4 rows in set (0.00 sec)我将应用 GROUP BY 来了解相同地址的出现次数。这里是 语法 −
SELECT column_name1,......N aggregate function( ) from yourTableName group by
column_name;让我们将上述语法应用于 GROUP BY -
mysql> SELECT address, count(*) from GroupDemo1 group by address;
以下是输出 -
+---------+----------+
| address | count(*) |
+---------+----------+
| US | 3 |
| UK | 1 |
+---------+----------+
2 rows in set (0.00 sec)
