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

最新推荐文章于 2024-11-01 11:28:07 发布

Mr.L-OAM

最新推荐文章于 2024-11-01 11:28:07 发布

阅读量81

点赞数

分类专栏： SQL 文章标签： sql mysql 数据库

本文链接：https://blog.csdn.net/weixin_46153337/article/details/132733073

版权

SQL 专栏收录该内容

17 篇文章 0 订阅

订阅专栏

对函数的处理

新建一个成绩表

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 SUM(MATH)/COUNT(name) FROM score;
+-----------------------+
| SUM(MATH)/COUNT(name) |
+-----------------------+
|               81.6000 |
+-----------------------+
1 row in set (0.00 sec)

-- 上面的结果保留两位小数点
root@mysqldb 10:01:  [d1]> SELECT ROUND(SUM(MATH)/COUNT(name),2) FROM score;
+--------------------------------+
| ROUND(SUM(MATH)/COUNT(name),2) |
+--------------------------------+
|                          81.60 |
+--------------------------------+
1 row in set (0.00 sec)

AGV 返回某一列的平均值

root@mysqldb 10:03:  [d1]> SELECT AVG(math) avg_math FROM score;
+----------+
| avg_math |
+----------+
|  81.6000 |
+----------+
1 row in set (0.00 sec)

-- 上面的平均数查询结果保留1个小数点
root@mysqldb 10:05:  [d1]> SELECT ROUND(AVG(math),1) avg_math FROM score;
+----------+
| avg_math |
+----------+
|     81.6 |
+----------+
1 row in set (0.00 sec)

MAX 查询某一列中的最大值

-- 查询math的最高分
root@mysqldb 10:06:  [d1]> SELECT MAX(math) FROM score;
+-----------+
| MAX(math) |
+-----------+
|        98 |
+-----------+
1 row in set (0.00 sec)

MIN 查询某一列中的最小值

root@mysqldb 10:08:  [d1]> SELECT MIN(math) FROM score;
+-----------+
| MIN(math) |
+-----------+
|        70 |
+-----------+
1 row in set (0.00 sec)

日期时间函数

DATETIME

-- DATETIME ,时间格式为 "yy-mm-dd HH:MM:SS"

root@mysqldb 10:34:  [d1]> CREATE TABLE project (
    -> task varchar(30),
    -> StartTime DATETIME,
    -> endtime DATETIME
    -> );
Query OK, 0 rows affected (0.10 sec)

root@mysqldb 11:06:  [d1]> INSERT INTO project VALUES
    -> ("AA","2023-07-15 10:00:00","2023-08-01 00:00:00"),
    -> ("BB","2023-07-20 10:00:00","2023-08-02 10:00:00"),
    -> ("CC","2023-08-16 00:00:00","2023-08-25 00:00:00"),
    -> ("DD","2023-09-02","2023-09-06"),
    -> ("EE","2023-09-01 10:00:00","2023-09-05 00:00:00");
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

-- 省缺的HH:MM:SS 自动填充成00:00:00
root@mysqldb 11:06:  [d1]> SELECT * FROM project;
+------+---------------------+---------------------+
| task | StartTime           | endtime             |
+------+---------------------+---------------------+
| AA   | 2023-07-15 10:00:00 | 2023-08-01 00:00:00 |
| BB   | 2023-07-20 10:00:00 | 2023-08-02 10:00:00 |
| CC   | 2023-08-16 00:00:00 | 2023-08-25 00:00:00 |
| DD   | 2023-09-02 00:00:00 | 2023-09-06 00:00:00 |
| EE   | 2023-09-01 10:00:00 | 2023-09-05 00:00:00 |
+------+---------------------+---------------------+

DATE 时间格式为 “yy-mm-dd”

root@mysqldb 11:06:  [d1]> CREATE TABLE project2 (
    -> task varchar(30),
    -> StartTime DATE,
    -> endtime DATE
    -> );
Query OK, 0 rows affected (0.01 sec)


root@mysqldb 11:12:  [d1]> INSERT INTO project2 VALUES
    -> ("AA","2023-07-15","2023-08-01"),
    -> ("BB","2023-07-20 10:00:00","2023-08-02 10:00:00"),
    -> ("CC","2023-08-16 00:00:00","2023-08-25 00:00:00"),
    -> ("DD","2023-09-02","2023-09-06"),
    -> ("EE","2023-09-01 10:00:00","2023-09-05 00:00:00");
Query OK, 5 rows affected, 3 warnings (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 3


root@mysqldb 11:12:  [d1]> select * from project2;
+------+------------+------------+
| task | StartTime  | endtime    |
+------+------------+------------+
| AA   | 2023-07-15 | 2023-08-01 |
| BB   | 2023-07-20 | 2023-08-02 |
| CC   | 2023-08-16 | 2023-08-25 |
| DD   | 2023-09-02 | 2023-09-06 |
| EE   | 2023-09-01 | 2023-09-05 |
+------+------------+------------+
5 rows in set (0.00 sec)

DATA_ADD

-- 将结束时间推迟1一个月
root@mysqldb 11:36:  [d1]> SELECT task, DATE_ADD(endtime, INTERVAL 1 MONTH) AS TC_endtime FROM project;
+------+---------------------+
| task | TC_endtime          |
+------+---------------------+
| AA   | 2023-09-01 00:00:00 |
| BB   | 2023-09-02 10:00:00 |
| CC   | 2023-09-25 00:00:00 |
| DD   | 2023-10-06 00:00:00 |
| EE   | 2023-10-05 00:00:00 |
+------+---------------------+
5 rows in set (0.00 sec)

root@mysqldb 11:37:  [d1]> SELECT task, DATE_ADD(endtime, INTERVAL 1 MONTH) AS TC_endtime FROM project;
+------+---------------------+
| task | TC_endtime          |
+------+---------------------+
| AA   | 2023-09-01 00:00:00 |
| BB   | 2023-09-02 10:00:00 |
| CC   | 2023-09-25 00:00:00 |
| DD   | 2023-10-06 00:00:00 |
| EE   | 2023-10-05 00:00:00 |
+------+---------------------+
5 rows in set (0.00 sec)

CONCAT

root@mysqldb 16:33:  [d1]> SELECT name, num, CONCAT(name, num) AS new_name FROM cars_price;
+---------+------+-----------+
| name    | num  | new_name  |
+---------+------+-----------+
| changan |   23 | changan23 |
| jili    |   24 | jili24    |
| adi     |   22 | adi22     |
| wlai    |   21 | wlai21    |
| lke     |   20 | lke20     |
| bchi    |   23 | bchi23    |
+---------+------+-----------+
6 rows in set (0.00 sec)

UPPER 将参数所有字母转换成大写

root@mysqldb 16:42:  [d1]> SELECT name,UPPER(name) as new_name FROM cars_price;
+---------+----------+
| name    | new_name |
+---------+----------+
| changan | CHANGAN  |
| jili    | JILI     |
| adi     | ADI      |
| wlai    | WLAI     |
| lke     | LKE      |
| bchi    | BCHI     |
+---------+----------+
6 rows in set (0.00 sec)

-- 只将首字符变成大写
root@mysqldb 16:48:  [d1]> SELECT name, CONCAT(
    -> UPPER(SUBSTRING(name, 1, 1)), 
    -> LOWER(SUBSTRING(name,2))
    -> ) 
    -> AS newname_column 
    -> FROM cars_price;
+---------+----------------+
| name    | newname_column |
+---------+----------------+
| changan | Changan        |
| jili    | Jili           |
| adi     | Adi            |
| wlai    | Wlai           |
| lke     | Lke            |
| bchi    | Bchi           |
+---------+----------------+
6 rows in set (0.00 sec)

-- 将name字段的值全部改成大写
root@mysqldb 17:19:  [d1]> UPDATE cars_price 
    -> SET name = UPPER(name);
Query OK, 6 rows affected (0.00 sec)
Rows matched: 6  Changed: 6  Warnings: 0

root@mysqldb 17:20:  [d1]> select * from cars_price;
+---------+------+--------+-------+------+
| name    | num  | colour | price | type |
+---------+------+--------+-------+------+
| CHANGAN |   23 | white  |    15 | zdfs |
| JILI    |   24 | black  |    14 | zdzx |
| ADI     |   22 | red    |    13 | sdfx |
| WLAI    |   21 | green  |    12 | acd  |
| LKE     |   20 | white  |    11 | aa   |
| BCHI    |   23 | white  |    15 | zdfs |
+---------+------+--------+-------+------+
6 rows in set (0.00 sec)

LOWER 将字段的值变成小写

root@mysqldb 17:21:  [d1]> SELECT name,LOWER(name) as new_name FROM cars_price;
+---------+----------+
| name    | new_name |
+---------+----------+
| CHANGAN | changan  |
| JILI    | jili     |
| ADI     | adi      |
| WLAI    | wlai     |
| LKE     | lke      |
| BCHI    | bchi     |
+---------+----------+
6 rows in set (0.00 sec)

LPAD 将字符串扩充到指定的长度，并指定用什么字符填充，LPAD，第一个参数需要处理的字符，第二个参数指定的长度，第三个参数指定填充的字符

root@mysqldb 17:22:  [d1]> SELECT name, LPAD(name,10,"*") FROM cars_price;
+---------+-------------------+
| name    | LPAD(name,10,"*") |
+---------+-------------------+
| CHANGAN | ***CHANGAN        |
| JILI    | ******JILI        |
| ADI     | *******ADI        |
| WLAI    | ******WLAI        |
| LKE     | *******LKE        |
| BCHI    | ******BCHI        |
+---------+-------------------+
6 rows in set (0.00 sec)

RPAD 同 LPAD 用法

root@mysqldb 17:44:  [d1]> SELECT name, RPAD(name,10,"-") FROM cars_price;
+---------+-------------------+
| name    | RPAD(name,10,"-") |
+---------+-------------------+
| CHANGAN | CHANGAN---        |
| JILI    | JILI------        |
| ADI     | ADI-------        |
| WLAI    | WLAI------        |
| LKE     | LKE-------        |
| BCHI    | BCHI------        |
+---------+-------------------+
6 rows in set (0.00 sec)

LTRIM 和 RTRIM

root@mysqldb 17:47:  [d1]> SELECT name,LTRIM(name) FROM cars_price;
+---------+-------------+
| name    | LTRIM(name) |
+---------+-------------+
| CHANGAN | CHANGAN     |
| JILI    | JILI        |
| ADI     | ADI         |
| WLAI    | WLAI        |
| LKE     | LKE         |
| BCHI    | BCHI        |
+---------+-------------+
6 rows in set (0.00 sec

root@mysqldb 17:48:  [d1]> SELECT name,LPAD(LTRIM(name),20,"*") FROM cars_price;
+---------+--------------------------+
| name    | LPAD(LTRIM(name),20,"*") |
+---------+--------------------------+
| CHANGAN | *************CHANGAN     |
| JILI    | ****************JILI     |
| ADI     | *****************ADI     |
| WLAI    | ****************WLAI     |
| LKE     | *****************LKE     |
| BCHI    | ****************BCHI     |
+---------+--------------------------+
6 rows in set (0.00 sec)

               root@mysqldb 17:52:  [d1]>  SELECT name,RPAD(RTRIM(name),20,"*") FROM cars_price;
+---------+--------------------------+
| name    | RPAD(RTRIM(name),20,"*") |
+---------+--------------------------+
| CHANGAN | CHANGAN*************     |
| JILI    | JILI****************     |
| ADI     | ADI*****************     |
| WLAI    | WLAI****************     |
| LKE     | LKE*****************     |
| BCHI    | BCHI****************     |
+---------+--------------------------+
6 rows in set (0.00 sec)

REPLACE 需要三个参数，第一个是要搜索的字符串，第二个是搜索的字符，第三个是替换的字符

root@mysqldb 17:56:  [d1]> SELECT name, REPLACE(name,"I","Z") FROM cars_price;
+---------+-----------------------+
| name    | REPLACE(name,"I","Z") |
+---------+-----------------------+
| CHANGAN | CHANGAN               |
| JILI    | JZLZ                  |
| ADI     | ADZ                   |
| WLAI    | WLAZ                  |
| LKE     | LKE                   |
| BCHI    | BCHZ                  |
+---------+-----------------------+
6 rows in set (0.00 sec)

SUBSTR 允许将目标字符串的一部分输出。需要三个参数，第一个参数为目标字符串，第二个字符串是将要输出的字符串的起点，第三个是要输出的字符串的长度。

root@mysqldb 10:09:  [d1]> SELECT name, SUBSTR(name, 2, 2) as new_name FROM cars_price;
+---------+----------+
| name    | new_name |
+---------+----------+
| CHANGAN | HA       |
| JILI    | IL       |
| ADI     | DI       |
| WLAI    | LA       |
| LKE     | KE       |
| BCHI    | CH       |
+---------+----------+
6 rows in set (0.00 sec)

-- 如果第二个参数是负数，将从尾部开始向前定位值负数的绝对值的位置
root@mysqldb 10:12:  [d1]> SELECT name, SUBSTR(name, -3, 2) as new_name FROM cars_price;
+---------+----------+
| name    | new_name |
+---------+----------+
| CHANGAN | GA       |
| JILI    | IL       |
| ADI     | AD       |
| WLAI    | LA       |
| LKE     | LK       |
| BCHI    | CH       |
+---------+----------+
6 rows in set (0.00 sec)

root@mysqldb 10:14:  [d1]> SELECT name, CONCAT(
    -> SUBSTR(name, 1, 2),
    -> "-",
    -> SUBSTR(name,3,2)
    -> )
    -> AS new_name
    -> FROM cars_price;
+---------+----------+
| name    | new_name |
+---------+----------+
| CHANGAN | CH-AN    |
| JILI    | JI-LI    |
| ADI     | AD-I     |
| WLAI    | WL-AI    |
| LKE     | LK-E     |
| BCHI    | BC-HI    |
+---------+----------+
6 rows in set (0.00 sec)

INSTR 函数

root@mysqldb 10:31:  [d1]> select name,instr(name,"A") FROM cars_price;
+---------+-----------------+
| name    | instr(name,"A") |
+---------+-----------------+
| CHANGAN |               3 |
| JILI    |               0 |
| ADI     |               1 |
| WLAI    |               3 |
| LKE     |               0 |
| BCHI    |               0 |
+---------+-----------------+
6 rows in set (0.00 sec)

LENGTH 返回字符串的长度

root@mysqldb 10:32:  [d1]> SELECT name, LENGTH(name) FROM cars_price;
+---------+--------------+
| name    | LENGTH(name) |
+---------+--------------+
| CHANGAN |            7 |
| JILI    |            4 |
| ADI     |            3 |
| WLAI    |            4 |
| LKE     |            3 |
| BCHI    |            4 |
+---------+--------------+
6 rows in set (0.00 sec)