16-基础SQL-函数-数值函数-526互联

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

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)

案例：创建一个 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"));