编辑代码

-- 函数--字符串函数
create database info;
use info;
create table worker(
    id int,
    name varchar(10),
    age int,
    gender char(1),
    workcity varchar(10),
    entrytime date
);
insert into worker values (1,'张三',28,'F','上海','2020-08-20');
insert into worker values(2,'李四',24,'F','上海','2021-06-25');
insert into worker values(3,'王二麻',27,'M','北京','2020-07-22');
insert into worker values(4,'王五',29,'M','深圳','2022-01-10');
insert into worker values(5,'赵六',32,'F','深圳','2019-07-07');
insert into worker values(6,'钱九',31,'M','北京','2018-04-19');
insert into worker values(7,'孙八',30,'F','深圳','2021-09-17');
insert into worker values(8,'何十',34,'M','北京','2022-03-28');
insert into worker values(9,'九九',26,'M','深圳','2023-08-19');
insert into worker values(10,'零七',24,'F','上海','2021-06-24');
insert into worker values(11,'可可',27,'M',null,'2024-07-21');
-- select* from worker;
-- concat 组合在一起
-- select concat("张三","上海");
-- LOWER & UPPER 大小写
-- select lower ("Femal");
-- select UPPER ("meal");
-- LPAD & RPAD 左右填充(填充谁,长度,用啥)
-- select LPAD("1",5,"0");
-- select RPAD ("1",5."0");
-- trim 删除字符串的前后空格
-- select trim("  hello world  ");
-- substring(截取谁,从哪,多长);
-- select substring("hello world",1,5);
-- 案例演示:由于业务需求变更,企业员工的工号统一调整为5位数,目前不足5位的全部在前面补0;
-- 本次需求是对DB中的员工表的工号做调整,用update+表名 set 字段 = 函数;
update worker set id = lPAD(id,5,"0"); 
select id from worker;

-- 数值函数,常用数值函数:ceil向上取整 floor向下取整 mod取x/y的余数
-- rand取0-1之间数 round 保留几位小数(1.333,2) 
-- 常用语法 select 函数;
-- select ceil(1.33);
-- select floor (1.292);
-- select mod (7,5);
-- select rand();
-- select round(1.3635,2);