MySQL基础6-常用数据库函数

数据库,MySQL,常用,基础 · 浏览次数 : 155

小编点评

**需求：在学生成绩表中，根据成绩展示优秀,良好一般。** **学生成绩表（test）原始数据mysql> select * from test;+------+---------+--------------+-----------+| id | stu_num | stu_name | stu_score |+------+---------+--------------+-----------+| 1 | 00001 | 张三 | 90 || 2 | 00002 | 李四 | 87 || 3 | 00003 | 王五 | 78 || 4 | 00014 | 赵六 | 83 || 5 | 00025 | 王二麻子 | 94 |+------+---------+--------------+-----------+5 rows in set (0.00 sec)mysql> select stu_name, (case when stu_score>=90 then \"优秀\" when stu_score >80 then \"良好\" when stu_score >70 then\"一般\" when stu_score >60 then \"及格\" else \"不及格\" end) as \"成绩\" from test;+--------------+--------+| stu_name | 成绩 |+--------------+--------+| 张三 | 优秀 || 李四 | 良好 || 王五 | 一般 || 赵六 | 良好 || 王二麻子 | 优秀 |+--------------+--------+5 rows in set (0.00 sec)** **代码：** ```python import mysql.connector #连接数据库 db_connector = mysql.connector.connect( #数据库连接信息 database_url="mysql://root@localhost/test" ) #创建游标 cursor = db_connector.cursor() #执行查询 cursor.execute("select name ,(case when stu_score>=90 then \"优秀\" when stu_score >80 then \"良好\" when stu_score >70 then\"一般\" when stu_score >60 then \"及格\" else \"不及格\" end) as \"成绩\" from test") #获取结果 results = cursor.fetchall() #关闭游标 cursor.close() #关闭数据库连接 db_connector.close() ``` **排版说明：** ``` name | 特色 | --------+--------------| 张三 | 肉夹馍 | 李四 | 良好 || 王五 | 一般 || 赵六 | 良好 || 王二麻子 | 优秀 || ```

正文

一、字符串函数

　　1、常见MySQL内置字符串函数

- concat(s1,s2,s3,...)：字符串拼接，将s1,s2,s3...等拼接成一个字符串
- lower(str)：将字符串str全部转为小写
- upper(str)：将字符串str全部转为大写
- lpad(str,n,pad)：左填充，将字符串pad对str的左边进行填充，达到n个字符串长度
- rpad(str,n,pad)：右填充，将字符串pad对str的右边进行填充，达到n个字符串长度
- trim(str)：去掉字符串头部和尾部的空格
- substring(str,start,len)：返回字符串str从start位置起的len长度的字符串

　　2、函数示例　　　　

　　　　示例1：concat(s1,s2,s3,...) 函数

mysql> select concat("hello","world");
+-------------------------+
| concat("hello","world") |
+-------------------------+
| helloworld              |
+-------------------------+
1 row in set (0.00 sec)

　　　　示例2：lower(str)> select lower("Hello");

+----------------+
| lower("Hello") |
+----------------+
| hello          |
+----------------+
1 row in set (0.00 sec)

　　　　示例3：upper(str)

mysql> select upper("Hello");
+----------------+
| upper("Hello") |
+----------------+
| HELLO          |
+----------------+
1 row in set (0.00 sec)

　　　　示例4：lpad(str,n,pad)|

　　　　情况一：n小于原始字符串str的长度，只显示原始字符串的长度n的字符　

mysql> select lpad("张三",1,"靓仔");
+---------------------------+
| lpad("张三",1,"靓仔")     |
+---------------------------+
| 张                        |
+---------------------------+
1 row in set (0.00 sec)

　　　　情况二：当长度n大于str字符串的原始长度，小于拼接后的字符串长度后，显示原来的字符串str+pad继续补齐到n个字符串

mysql> select lpad("张三",3,"靓仔");
+---------------------------+
| lpad("张三",3,"靓仔")     |
+---------------------------+
| 靓张三                    |
+---------------------------+
1 row in set (0.00 sec)

　　　情况三：如果n大于拼接后的字符串长度，会重复拼接pad字符串

mysql> select lpad("张三",6,"靓仔");
+---------------------------+
| lpad("张三",6,"靓仔")     |
+---------------------------+
| 靓仔靓仔张三              |
+---------------------------+
1 row in set (0.00 sec)

　　　　示例5：rpad(str,n,pad)

mysql> select rpad("张三",1,"靓仔");
+---------------------------+
| rpad("张三",1,"靓仔")     |
+---------------------------+
| 张                        |
+---------------------------+
1 row in set (0.00 sec)

mysql> select rpad("张三",3,"靓仔");
+---------------------------+
| rpad("张三",3,"靓仔")     |
+---------------------------+
| 张三靓                    |
+---------------------------+
1 row in set (0.00 sec)

mysql> select rpad("张三",6,"靓仔");
+---------------------------+
| rpad("张三",6,"靓仔")     |
+---------------------------+
| 张三靓仔靓仔              |
+---------------------------+
1 row in set (0.00 sec)

　　　　情况处理：同lpad情况

　　　　示例6：trim(str)

mysql> select trim(" Hello world ");
+-----------------------+
| trim(" Hello world ") |
+-----------------------+
| Hello world           |
+-----------------------+
1 row in set (0.00 sec)

　　　　注意：trim只会取出开始和结尾的空格，不会去除中间的空格。

　　　　示例7：substring(str,start,len)

mysql> select substring("hello world", 1, 5);
+--------------------------------+
| substring("hello world", 1, 5) |
+--------------------------------+
| hello                          |
+--------------------------------+
1 row in set (0.00 sec)

　　　　注意：截取字符串的索引从1开始

　　　　示例8：在table_test中对id重新编号，统一显示五位数，不足五位数的前面补0

　　　　原始表test数据

mysql> select * from test;
+------+---------+--------------+-----------+
| id   | stu_num | stu_name     | stu_score |
+------+---------+--------------+-----------+
|    1 | 1       | 张三         | 90        |
|    2 | 2       | 李四         | 87        |
|    3 | 3       | 王五         | 78        |
|    4 | 14      | 赵六         | 83        |
|    5 | 25      | 王二麻子     | 94        |
+------+---------+--------------+-----------+
5 rows in set (0.00 sec)

　　　　修改后的数据

mysql> update test set stu_num = lpad(stu_num,5,'0');
Query OK, 5 rows affected (0.01 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> select * from test;
+------+---------+--------------+-----------+
| id   | stu_num | stu_name     | stu_score |
+------+---------+--------------+-----------+
|    1 | 00001   | 张三         | 90        |
|    2 | 00002   | 李四         | 87        |
|    3 | 00003   | 王五         | 78        |
|    4 | 00014   | 赵六         | 83        |
|    5 | 00025   | 王二麻子     | 94        |
+------+---------+--------------+-----------+
5 rows in set (0.00 sec)

二、数值函数

　　1、常见数值函数

- ceil(x)：向上取整
- floor(x)：向下取整
- mod(x,y)：返回x/y的模
- rand()：返回0-1内的随机数
- round(x,y)：求参数x的四舍五入的值，保留y位小数

　　2、函数示例

　　　　示例1：ceil(x)：向上取整

mysql> select ceil(1.1);
+-----------+
| ceil(1.1) |
+-----------+
|         2 |
+-----------+
1 row in set (0.00 sec)

　　　　示例2：floor(x)：向下取整

mysql> select floor(1.9);
+------------+
| floor(1.9) |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

　　　　示例3：mod(x,y)：返回x/y的模

mysql> select mod(5,2);
+----------+
| mod(5,2) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

　　　　示例4：rand()：返回0-1内的随机数

mysql> select rand();
+---------------------+
| rand()              |
+---------------------+
| 0.20385351237268515 |
+---------------------+
1 row in set (0.00 sec)

　　　　示例5：round(x,y)：求参数x的四舍五入的值，保留y位小数

mysql> select round(3.1415926,2);
+--------------------+
| round(3.1415926,2) |
+--------------------+
|               3.14 |
+--------------------+
1 row in set (0.00 sec)

　　　　示例6：综合案例：通过数据库函数，生成一个六位数的随机验证码

mysql> select rpad(round(rand()*1000000, 0),6,'0');
+--------------------------------------+
| rpad(round(rand()*1000000, 0),6,'0') |
+--------------------------------------+
| 192803                               |
+--------------------------------------+
1 row in set (0.00 sec)

　　　　说明：

　　　　　　1、rand()生成的小数，乘以1000000，才有可能是有六位整数的小数，所以这个时候需要市容round()四舍五入取整数

　　　　　　2、因为rand()生成的小数有可能是0.999999这样的乘以1000000就是6位整数，也有可能生成的小数是0.000009这样的小数就算乘以1000000也不够六位整数

　　　　　　3、所以还需要使用rpad()函数，如果整数不足六位，就在后面补0

三、日期函数

　　1、常见日期函数

- curdate()：返回当前日期
- curtime()：返回当前时间
- now()：返回当前日期和时间
- year(date)：获取指定date的年份
- month(date)：获取指定date的月份
- day(date)：获取指定date的日期
- date_add(date,interval expr type)：返回一个日期/时间值加上一个时间间隔expr后的时间值
- datediff(date1,date2)：返回起始时间date1和结束时间date2之间的天数

　　2、函数示例

　　　　示例1：curdate()：返回当前日期

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2023-07-18 |
+------------+
1 row in set (0.00 sec)

　　　　示例2：curtime()：返回当前时间

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 21:52:43  |
+-----------+
1 row in set (0.00 sec)

　　　　示例3：now()：返回当前日期和时间

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2023-07-18 21:53:10 |
+---------------------+
1 row in set (0.00 sec)

　　　　示例4：year(date)：获取指定date的年份

mysql> select year("2023-07-18 21:53:10");
+-----------------------------+
| year("2023-07-18 21:53:10") |
+-----------------------------+
|                        2023 |
+-----------------------------+
1 row in set (0.01 sec)

　　　　示例5：month(date)：获取指定date的月份

mysql> select month("2023-07-18 21:53:10");
+------------------------------+
| month("2023-07-18 21:53:10") |
+------------------------------+
|                            7 |
+------------------------------+
1 row in set (0.00 sec)

　　　　示例6：day(date)：获取指定date的日期

mysql> select day("2023-07-18 21:53:10");
+----------------------------+
| day("2023-07-18 21:53:10") |
+----------------------------+
|                         18 |
+----------------------------+
1 row in set (0.00 sec)

　　　　示例7：date_add(date,interval expr type)：返回一个日期/时间值加上一个时间间隔expr后的时间值

mysql> select date_add("2023-07-18 21:53:10", interval 2 day);
+-------------------------------------------------+
| date_add("2023-07-18 21:53:10", interval 2 day) |
+-------------------------------------------------+
| 2023-07-20 21:53:10                             |
+-------------------------------------------------+
1 row in set (0.01 sec)

mysql> select date_add("2023-07-18 21:53:10", interval 2 month);
+---------------------------------------------------+
| date_add("2023-07-18 21:53:10", interval 2 month) |
+---------------------------------------------------+
| 2023-09-18 21:53:10                               |
+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> select date_add("2023-07-18 21:53:10", interval 2 year);
+--------------------------------------------------+
| date_add("2023-07-18 21:53:10", interval 2 year) |
+--------------------------------------------------+
| 2025-07-18 21:53:10                              |
+--------------------------------------------------+
1 row in set (0.00 sec)

　　　　示例8：datediff(date1,date2)：返回起始时间date1和结束时间date2之间的天数

mysql> select datediff("2023-07-18 21:53:10", "2023-07-28 21:53:10");
+--------------------------------------------------------+
| datediff("2023-07-18 21:53:10", "2023-07-28 21:53:10") |
+--------------------------------------------------------+
|                                                    -10 |
+--------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select datediff("2023-07-28 21:53:10", "2023-07-18 21:53:10");
+--------------------------------------------------------+
| datediff("2023-07-28 21:53:10", "2023-07-18 21:53:10") |
+--------------------------------------------------------+
|                                                     10 |
+--------------------------------------------------------+
1 row in set (0.00 sec)

四、流程函数

　　1、常见流程函数

- if(value, t, f)：如果value为true，则返回t，否则返回f
- ifnull(value1,value2)：如果value1不为空，返回value1，否则返回value2
- case when [val1] then [res1]...else[default] end：如果val1为true，则返回res1，否则返回default默认值
- case[expr] when [val1] then [res1]...else[default] end：如果expr的值等于val1，返回res1，否则返回默认值

　　2、函数示例

　　　　示例1：if(value, t, f)：如果value为true，则返回t，否则返回f

mysql> select if(1>2,"ok","no");
+-------------------+
| if(1>2,"ok","no") |
+-------------------+
| no                |
+-------------------+
1 row in set (0.00 sec)

　　　　示例2：ifnull(value1,value2)：如果value1不为空，返回value1，否则返回value2

mysql> select ifnull("ok","default");
+------------------------+
| ifnull("ok","default") |
+------------------------+
| ok                     |
+------------------------+
1 row in set (0.00 sec)

mysql> select ifnull("","default");
+----------------------+
| ifnull("","default") |
+----------------------+
|                      |              注意：空字符串不是null
+----------------------+
1 row in set (0.00 sec)

mysql> select ifnull(null,"default");
+------------------------+
| ifnull(null,"default") |
+------------------------+
| default                |
+------------------------+
1 row in set (0.00 sec)

　　　　示例3：case [expr] when [val1] then [res1]...else[default] end：如果val1为true，则返回res1，否则返回default默认值

　　　　需求：在table_test中，展示姓名和特色一栏，特色根据address的值来设定，北京特色烤鸭，上海特色蟹黄包，西安特色肉夹馍，其他地方都是红烧肉

　　　　table_test表原始数据

mysql> select * from table_test;
+------+--------+------+---------+
| id   | name   | age  | address |
+------+--------+------+---------+
|    1 | 刘一   |   18 | 西安    |
|    2 | 陈二   |   19 | 上海    |
|    3 | 张三   |   21 | 北京    |
|    4 | 李四   |   18 | 北京    |
|    5 | 王五   |   21 | 西安    |
|    6 | 赵六   |   22 | NULL    |
+------+--------+------+---------+
6 rows in set (0.00 sec)

mysql> select name ,(case address when "北京" then "烤鸭" when "上海" then "蟹黄包" when "西安" then "肉夹馍" else "红烧肉" end) as "特色" from table_test;
+--------+--------------+
| name   | 特色         |
+--------+--------------+
| 刘一   | 肉夹馍       |
| 陈二   | 蟹黄包       |
| 张三   | 烤鸭         |
| 李四   | 烤鸭         |
| 王五   | 肉夹馍       |
| 赵六   | 红烧肉       |
+--------+--------------+
6 rows in set (0.00 sec)

　　　　示例4：需求：在学生成绩表中，根据成绩展示优秀,良好一般。

　　　　学生成绩表（test）原始数据

mysql> select * from test;
+------+---------+--------------+-----------+
| id   | stu_num | stu_name     | stu_score |
+------+---------+--------------+-----------+
|    1 | 00001   | 张三         | 90        |
|    2 | 00002   | 李四         | 87        |
|    3 | 00003   | 王五         | 78        |
|    4 | 00014   | 赵六         | 83        |
|    5 | 00025   | 王二麻子     | 94        |
+------+---------+--------------+-----------+
5 rows in set (0.00 sec)

mysql> select stu_name, (case when stu_score>=90 then "优秀" when stu_score >=80 then "良好" when stu_score >=70 then
"一般" when stu_score >=60 then "及格" else "不及格" end) as "成绩" from test;
+--------------+--------+
| stu_name     | 成绩   |
+--------------+--------+
| 张三         | 优秀   |
| 李四         | 良好   |
| 王五         | 一般   |
| 赵六         | 良好   |
| 王二麻子     | 优秀   |
+--------------+--------+
5 rows in set (0.00 sec)