要使用COUNT执行SELECT,请使用聚合函数COUNT()。让我们首先创建一个表-mysql> create table DemoTable
(
Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
Name varchar(100),
Subject varchar(100)
);
使用插入命令在表中插入一些记录-mysql> insert into DemoTable(Name,Subject) values('John','MySQL');
mysql> insert into DemoTable(Name,Subject) values('John','Java');
mysql> insert into DemoTable(Name,Subject) values('Carol','MongoDB');
mysql> insert into DemoTable(Name,Subject) values('Carol','Java');
mysql> insert into DemoTable(Name,Subject) values('Carol','MySQL');
mysql> insert into DemoTable(Name,Subject) values('John','MySQL');
mysql> insert into DemoTable(Name,Subject) values('Carol','MongoDB');
使用select语句显示表中的所有记录-mysql> select *from DemoTable;
输出结果+----+-------+---------+
| Id | Name | Subject |
+----+-------+---------+
| 1 | John | MySQL |
| 2 | John | Java |
| 3 | Carol | MongoDB |
| 4 | Carol | Java |
| 5 | Carol | MySQL |
| 6 | John | MySQL |
| 7 | Carol | MongoDB |
+----+-------+---------+
7 rows in set (0.00 sec)
以下是在MySQL中使用count选择的查询-mysql> select Name,Subject,count(Subject) from DemoTable group by Subject,Name;
输出结果+-------+---------+----------------+
| Name | Subject | count(Subject) |
+-------+---------+----------------+
| John | MySQL | 2 |
| John | Java | 1 |
| Carol | MongoDB | 2 |
| Carol | Java | 1 |
| Carol | MySQL | 1 |
+-------+---------+----------------+
5 rows in set (0.00 sec)