02-函数.md

函数

---

-- 准备测试数据
drop database if exists `java_basic`;
create database if not exists `java_basic` charset utf8mb4;
use `java_basic`;
create table if not exists `student`
(
    `id`         int auto_increment primary key not null comment '编号',
    `name`       varchar(32)                    not null comment '姓名',
    `gender`     tinyint unsigned               not null comment '性别',
    `age`        tinyint unsigned               not null comment '年龄',
    `entry_time` datetime                       not null comment '入学时间'
) comment '学生表';

drop procedure if exists add_student;
create procedure add_student(in num int)
begin
    declare
        i int default 0;
    while
        i != num
        do
            set i = i + 1;
            insert into `student` (`name`, `gender`, `age`, `entry_time`)
            values (concat('student', i),
                    if(rand() < 0.5, 1, 2),
                    18 + rand() * 2,
                    date_add(now(), interval rand() * -30 day));
        end while;
end;

call add_student(10);

select *
from `student`;

字符串函数

select concat('Concat', 'String', 'Test'); -- ConcatStringTest
select lower('To Lower Test'); -- to lower test
select upper('To Upper Test'); -- TO UPPER TEST
select lpad('left padding test', 20, '>'); -- >>> left padding test
select rpad('right padding test', 20, '<'); -- right padding test<<
select trim('  trim test  '); -- trim test
select subString('subString test', 1, 3); -- sub

数值函数

select ceil(1.4); -- 2
select floor(1.4); -- 1
select mod(10.1, 3); -- 1.1
select rand(); -- [0,1)
select round(1.2345, 2); -- 1.23

常用日期函数

select curdate(); -- 当前日期
select curtime(); -- 当前时间
select now(); -- 当前日期和时间
select year(now()); -- 年
select month(now()); -- 月
select day(now()); -- 日
select date_add(now(), interval 1 day); -- 明天
select datediff(now(), date_add(now(), interval 2 day)); -- -2

常用流程控制

select if(true, 1, 2); -- 1
select ifnull(null, 'replace'); -- replace

use `java_basic`;
select `id`,
       `name`,
       case `gender`
           when 1 then '男'
           when 2 then '女'
           else '未设置' end as `gender`
from `student`;
-- 1,zhang3,男
-- 2,li4,女
-- 3,wang5,未设置

select `id`,
       `name`,
       case
           when `gender` = 1 then '男'
           when `gender` = 2 then '女'
           else '未设置' end as `gender`
from `student`;
-- 1,zhang3,男
-- 2,li4,女
-- 3,wang5,未设置