对函数的处理
新建一个成绩表
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: 3root@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: 0root@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 secroot@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)
