创建新表并插入新数据

mysql> create table user(
    -> id int primary key auto_increment,
    -> name varchar(100) not null,
    -> age tinyint,
    -> email varchar(30),
    -> gender enum('male', 'female')
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc user;
+--------+-----------------------+------+-----+---------+----------------+
| Field  | Type                  | Null | Key | Default | Extra          |
+--------+-----------------------+------+-----+---------+----------------+
| id     | int                   | NO   | PRI | NULL    | auto_increment |
| name   | varchar(100)          | NO   |     | NULL    |                |
| age    | tinyint               | YES  |     | NULL    |                |
| email  | varchar(30)           | YES  |     | NULL    |                |
| gender | enum('male','female') | YES  |     | NULL    |                |
+--------+-----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> insert into user (name, age, email, gender) values
    -> ('ronie', 18, '123@163.com', 'male'),
    -> ('jack', 22, '456@163.com', 'male'),
    -> ('lucy', 20, '789@163.com', 'female'),
    -> ('mike', 30, '147@163.com', 'male'),
    -> ('kelly', 28, '258@163.com', 'female'),
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0


mysql> insert into user (name, gender) values ('jerry', 'male');
Query OK, 1 row affected (0.00 sec)

mysql> insert into user (name, age,  gender) values ('tom', 20, 'male');
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;
+----+-------+------+-------------+--------+
| id | name  | age  | email       | gender |
+----+-------+------+-------------+--------+
|  1 | ronie |   18 | 123@163.com | male   |
|  2 | jack  |   22 | 456@163.com | male   |
|  3 | lucy  |   20 | 789@163.com | female |
|  4 | mike  |   30 | 147@163.com | male   |
|  5 | kelly |   28 | 258@163.com | female |
|  6 | jerry | NULL | NULL        | male   |
|  7 | tom   |   20 | NULL        | male   |
+----+-------+------+-------------+--------+
7 rows in set (0.00 sec)

MySQL集合函数

1. COUNT()函数
   COUNT()函数统计数据表中包含的记录行的总数，或者根据查询结果返回列中包含的数据行数。

   • COUNT(*)计算表中总的行数，不管某列是否有数值或者为空值。

     mysql> select count(*) from user;
     +----------+
     | count(*) |
     +----------+
     |        7 |
     +----------+
      1 row in set (0.00 sec)
     
     

   • COUNT(字段名)计算指定列下总的行数，计算时将忽略空值的行。

     mysql> select count(email) from user;
     +--------------+
     | count(email) |
     +--------------+
     |            5 |
     +--------------+
      1 row in set (0.00 sec)
     
     

2. SUM()函数
   SUM()是一个求总和的函数，返回指定列值的总和。SUM()可以与GROUP BY一起使用，来计算每个分组的总和。SUM()函数在计算时，忽略列值为NULL的行。

   mysql> select sum(age) from user;
   +----------+
   | sum(age) |
   +----------+
   |      138 |
   +----------+
    1 row in set (0.00 sec)
   
   mysql> select gender, sum(age) from user group by gender;
   +--------+----------+
   | gender | sum(age) |
   +--------+----------+
   | male   |       90 |
   | female |       48 |
   +--------+----------+
    2 rows in set (0.00 sec)
   

3. AVG()函数
   AVG()函数通过计算返回的行数和每一行数据的和，求得指定列数据的平均值。

   mysql> select avg(age) from user;
   +----------+
   | avg(age) |
   +----------+
   |  23.0000 |
   +----------+
    1 row in set (0.01 sec)
   
   mysql> select gender, avg(age) from user group by gender;
   +--------+----------+
   | gender | avg(age) |
   +--------+----------+
   | male   |  22.5000 |
   | female |  24.0000 |
   +--------+----------+
    2 rows in set (0.00 sec)
   

4. MAX()函数
   MAX()返回指定列中的最大值。MAX()函数不仅适用于查找数值类型，也可应用于字符类型，在对字符类型数据进行比较时，按照字符的ASCII码值大小进行比较。

   mysql> select max(age) from user;
   +----------+
   | max(age) |
   +----------+
   |       30 |
   +----------+
    1 row in set (0.00 sec)
   
   mysql> select gender, max(age) from user group by gender;
   +--------+----------+
   | gender | max(age) |
   +--------+----------+
   | male   |       30 |
   | female |       28 |
   +--------+----------+
    2 rows in set (0.00 sec)
   
   mysql> select max(name) from user;
   +-----------+
   | max(name) |
   +-----------+
   | tom       |
   +-----------+
    1 row in set (0.00 sec)
   

5. MIN()函数
   MIN()返回查询列中的最小值。

   mysql> select min(age) from user;
   +----------+
   | min(age) |
   +----------+
   |       18 |
   +----------+
    1 row in set (0.00 sec)
   
   mysql> select gender, min(age) from user group by gender;
   +--------+----------+
   | gender | min(age) |
   +--------+----------+
   | male   |       18 |
   | female |       20 |
   +--------+----------+
    2 rows in set (0.00 sec)
   
   mysql> select min(name) from user;
   +-----------+
   | min(name) |
   +-----------+
   | jack      |
   +-----------+
    1 row in set (0.00 sec)
   
   mysql>