16-基础SQL-函数-数值函数

什么是函数：是指一段可以直接被另一段程序调用的程序或代码

MySQL的函数主要包括：字符串函数、数值函数、日期函数、流程函数。

数值函数

常见的数值函数如下：

CEIL(x) 演示：对 1.2 进行向上取整

SELECT CEIL(1.2);

FLOOR(x) 演示：对1.6进行向下取整

SELECT FLOOR(1.6);

MOD(x,y) 演示：返回 x/y 的模（这里指的是余数），比如 7/4 的模就是3

SELECT MOD(7,4);

RAND() 演示：返回1-10之间的随机整数

SELECT FLOOR(1+RAND()*10);

ROUND(x,y) 演示：将小数 "3.33333" 保留两位小数（按照四舍五入）

SELECT ROUND(3.333333,2)

 

扩展：FORMAT(column_name,format) 函数，用于对字段的显示进行格式化

就比如上面的演示，使用ROUND(x,y) 将小数 "113.1300" 保留四位小数，它的结果会是什么，只得到了 "113.13" ,很明显并没有得到我们想要的保留四位小数，而使用RPAD()函数进行补全也无法处理灵活多变的场景

SELECT ROUND("113.1300", 4);

 这里我们可以使用FORMAT()函数，进行格式化处理，保留四位小数

SELECT FORMAT(ROUND("113.1300", 4),4);

 同理，我们也可以使用DATE_FORMAT()函数，对当前时间进行格式化处理

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S');

 

 

案例：创建一个 emp 员工表，添加一些员工数据

CREATE TABLE emp(
    id int comment "编号",
    workno varchar(10) comment "工号",
    name varchar(10) comment "姓名",
    gender char(1) comment "性别",
    age tinyint unsigned comment "年龄",
    idcard char(18) comment "身份证号",
    workaddress varchar(50) comment "工作地址",
    entrydate date comment "入职时间"
) comment "员工表";

INSERT INTO emp (id,workno,name,gender,age,idcard,workaddress,entrydate)
VALUES    
(1,"1","员工1","男","23","123456789000000000","江西","2023-8-31"),
(2,"2","员工2","男","26","123456789000000001","河北","2023-12-31"),
(3,"3","员工3","女","23","123456789000000002","河北","2023-5-31"),
(4,"4","员工4","女","24","123456789000000003","山西","2023-3-31"),
(5,"5","员工5","男","23","123456789000000000","江西","2023-8-31"),
(6,"6","员工6","男","26","123456789000000001","河北","2023-12-31"),
(7,"7","员工7","女","23","123456789000000002","河北","2023-5-31"),
(8,"8","员工8","女","24","123456789000000003","山西","2023-3-31"),
(9,"9","员工9","男","23","123456789000000000","江西","2023-8-31"),
(10,"10","员工10","男","26","123456789000000001","河北","2023-12-31"),
(11,"11","员工11","女","23","123456789000000002","河北","2023-5-31"),
(12,"12","员工12","女","24","123456789000000003","山西","2023-3-31")

1）将所有员工信息的 "idcard" 字段，前十位统一按照 "1304031996"，后八位进行随机生成

UPDATE emp SET idcard = CONCAT("1304031996",LPAD(ROUND(RAND()*10000000,0),8,"0"));