当前位置：首页 > article >正文

SQL自学，mysql从入门到精通 --- 第 5 天，对函数的处理

article 2025/2/10 20:47:08

对函数的处理

新建一个成绩表

root@mysqldb 09:39:  [d1]> create table score (
    -> name varchar(30),
    -> chinese int,
    -> math int,
    -> music int,
    -> team int,
    -> magic int,
    -> computer int
    -> );
Query OK, 0 rows affected (0.01 sec)

root@mysqldb 09:39:  [d1]> insert into score
    -> values
    -> ("1A","90","85","30","92","70","65"),
    -> ("2B","99","98","90","95","92","89"),
    -> ("3C","90","70","60","88","89","92"),
    -> ("4D","89","85","59","78","93","94"),
    -> ("5E","88","70","66","92","58","76");
Query OK, 5 rows affected (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 0

-- 数据如下
root@mysqldb 09:44:  [d1]> select * from score;
+------+---------+------+-------+------+-------+----------+
| name | chinese | math | music | team | magic | computer |
+------+---------+------+-------+------+-------+----------+
| 1A   |      90 |   85 |    30 |   92 |    70 |       65 |
| 2B   |      99 |   98 |    90 |   95 |    92 |       89 |
| 3C   |      90 |   70 |    60 |   88 |    89 |       92 |
| 4D   |      89 |   85 |    59 |   78 |    93 |       94 |
| 5E   |      88 |   70 |    66 |   92 |    58 |       76 |
+------+---------+------+-------+------+-------+----------+
5 rows in set (0.00 sec)

COUNT 统计表中有多少行数据

-- COUNT(*)：统计表中的所有行数，包括所有列的数据行
root@mysqldb 09:46:  [d1]> select count(*) from score;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

-- COUNT(column_name)：统计指定列中非 NULL 值的行数
root@mysqldb 09:47:  [d1]> select count(chinese) from score;
+----------------+
| count(chinese) |
+----------------+
|              5 |
+----------------+
1 row in set (0.00 sec)

-- COUNT(DISTINCT column_name)：统计指定列中不同值的行数。
root@mysqldb 09:49:  [d1]> select count(distinct chinese) from score;
+-------------------------+
| count(distinct chinese) |
+-------------------------+
|                       4 |
+-------------------------+
1 row in set (0.00 sec)

-- COUNT(DISTINCT column_name1, column_name2)：统计多个列中不同值的组合行数

SUM 返回某一列所有数值的总和

-- 统计math列的总和
root@mysqldb 09:55:  [d1]> SELECT SUM(math) AS total_math FROM score;
+------------+
| total_math |
+------------+
|        408 |
+------------+
1 row in set (0.01 sec)

-- 统计多个列数值的总和
root@mysqldb 09:56:  [d1]> SELECT SUM(math) total_math, SUM(team) total_team FROM score;
+------------+------------+
| total_math | total_team |
+------------+------------+
|        408 |        445 |
+------------+------------+
1 row in set (0.00 sec)

-- 求数学成绩的平均分
root@mysqldb 09:57:  [d1]> SELECT <