SQL学习之count()函数

学习源

• 简单教程
  • https://www.twle.cn/l/yufei/sql/sql-basic-func-avg.html
• 菜鸟教程
  • https://www.runoob.com/sql/sql-function.html

SQL count 函数

SQL 中的 count() 函数返回匹配指定条件的行数

根据参数的不同，count() 大致有三种用法：

1. count ( column_name )

   count ( column_name )函数返回指定列的值的数目，NULL 值除外

   select count(column_name) from table_name;
   

2. count (*)

   count (*) 函数返回表中的记录数，包括 NULL 值

   select count(*) from table_name;
   

3. count (distinct column_name)

count (distinct column_name) 函数返回指定列的不同值的数目

select count (distinct column_name) from table_name;

count (distinct) 不能在Microsoft Access` 中使用

示例数据

CREATE DATABASE IF NOT EXISTS hardy_db default character set utf8mb4 collate utf8mb4_0900_ai_ci;

USE hardy_db;

CREATE TABLE IF NOT EXISTS lesson_views (
    uniq bigint(20) primary key NOT NULL default '0' ,
    lesson_name varchar(32) NOT NULL default '',
    lesson_id int(11) NOT NULL default '0',
    date_at  int(11) NOT NULL default '0',
    views int(11) NOT NULL default '0'
);

INSERT INTO lesson_views(uniq, lesson_name, lesson_id, date_at, views) VALUES
(20170511000001,'Python 基础教程',1,20170511,320),
(20170511000002,'Scala 基础教程', 2,20170511,22),
(20170511000003,'Ruby 基础教程', 3, 20170511,49),
(20170512000001,'Python 基础教程',1,20170512,220),
(20170512000002,'Scala 基础教程',2,20170512,12),
(20170512000003,'Ruby 基础教程',3,20170512,63),
(20170513000001,'Python 基础教程',1,20170513,441),
(20170513000002,'Scala 基础教程',2,20170513,39),
(20170513000003,'Ruby 基础教程',3,20170513,87);

基本使用

1. count(lesson_name)

   mysql> select count (lesson_name) from lesson_views;
   +---------------------+
   | count (lesson_name) |
   +---------------------+
   |                   9 |
   +---------------------+
   

2. count(*)

   mysql> select count(*) from lesson_views;
   +----------+
   | count(*) |
   +----------+
   |       10 |
   +----------+
   

3. count(distinct lesson_name)

   mysql> select count(distinct lesson_name) from lesson_views;
   +------------------------------+
   | count(distinct lesson_name) |
   +------------------------------+
   |                            3 |
   +------------------------------+