目录

字符串函数

数值函数

日期函数

流程函数

练习

---

本篇博客所有练习题所需的信息如下:

CREATE TABLE `student` (
  `id`          varchar(10)    COMMENT '学生学号'  ,
  `name`        varchar(10)    COMMENT '学生姓名',
  `gender`      char(1)        COMMENT '学生性别',
  `age`         int            COMMENT '学生年龄',
  `address`     varchar(20)    COMMENT '学生家庭住址',
  `score`       int            COMMENT '总成绩',
  `entrydate`   date           COMMENT '入学日期'
)  COMMENT '学生信息表';

insert into student values ('赵一','男',18,'北京',100, 01, '2021-01-30'),
                           ('钱二','男',19,'天津',56, 02, '2018-05-06'),
                           ('孙三','女',18,'北京',90, 03, '2019-05-12'),
                           ('李四','男',26,'北京',86, 04, '2016-09-07'),
                           ('周五','男',21,'河南',81, 05, '2022-04-16'),
                           ('小红','女',16,'辽宁',100, 06, '2020-02-02'),
                           ('小兰','女',23,'陕西',90, 07, '2017-07-19'),
                           ('小刚','男',22,'山东',95, 08, '2017-07-19'),
                           ('小名','男',14,'深圳',100, 09, '2020-02-02');

字符串函数

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

concat(s1, s2...sn)  // 字符串拼接函数,将s1、s2、sn拼接成一个字符串

select concat('hello ', 'MySQL'); // hello MySQL

lower(s1) // 将一个字符串全部转为小写 

select lower('HEllO'); // hello

upper(s1) // 将一个字符串全部转为大写 

select upper('hello');

 lpad(s1,n,pad) // 左填充,用pad对s1的左边进行填充,达到n个字符的长度

select lpad('1',1,'0'); // 01
select lpad('10',5,'='); // ===10

rpad(s1,n,pad) // 右填充,用pad对s1的右边进行填充,达到n各字符的长度 

select rpad('1',1,'0'); // 10
select rpad('10',5,'='); // 10===

trim(str1) // 去掉字符串头部和尾部的空格 

select trim(' 小 王 '); // '小 王'
// trim() 只去掉两边的空格,其他的不动

substring(s1,start,len) // 返回s1从start位置起的len个字符 

select substring('hello world',1,5); // hello
// 第一个位置的下标为1，而不是0

数值函数

ceil(x)	向上取整
floor(x)	向下取整
mod(x, y)	返回x%y的值
rand()	返回0~1的随机值
round(x, y)	对x四舍五入，保留y位小数
abs(x)	返回x的绝对值
greatest(x1,x2,x3);	返回列表中的最大值

ceil(x) // 向上取整 

select cceil(1.1); // 2

floor(x) // 向下取整 

select floor(1.9); // 1

mod(x,y) // 返回 x % y 的值  

select(10,4); // 2

rand() // 返回0-1的随机数 

select rand(); // 0-1的随机数

round(x,y) // 对x四舍五入，保留y位小数  

select(1.465, 2); // 1.47

日期函数

curdate()	返回当前日期
curtime()	返回当前时间
now()	返回当前日期和时间
year(date)	返回指定date的年份
month(date)	返回指定date的月份
day(date)	返回指定date的日期
date_add(date, interval expr type)	返回一个日期/时间加上一个时间间隔expr后的时间值
datediff(date1, date2)	返回起始时间date1和结束时间date2的时间间隔
dayname(date)	//返回日期是星期几，如Monday...
dayofmonth	返回日期date是本月的第几天
dayofyear	返回日期date是今年的第几天

curdate() ：返回当前日期 

select curdate(); // 2022-04-16

curtime() ：返回当前时间 

select curtime(); // 12:39:20

now() ：返回当前日期+时间 

select now(); // 2022-04-16 12:39:59

year(date) ：返回给定参数的年份 

select year(now()); // 2022
select year(curdate()); // 2022

month(date) ：返回给定参数的月份 

select month(now()) // 4
select month(curdate()); // 4

 day(date) ：返回给定日期是本月的第几天

select day(now()) // 16
select day(curdate()) // 16

 date_add(date, interval exp 类型) ：加上exp后的date

select date_add(curdate(), interval 10 day) ; // 2022-04-26
select date_add(now(), interval 10 day); // 2022-04-26 13:03:16

datediff(date1, date2) ： 返回日期1 - 日期2 

select datediff('2022-04-16', '2022-04-10'); // 6
select datediff('2022-04-10', '2022-04-16'); // -6

dayname(date) ：返回给定日期是周几 

select dayname(now()); // Saturday

dayofweek(date) ：返回给定日期是这一周的第几天 

select dayofweek(now()); // 7
周日 1
周一 2
递推...

dayofmonth(date) ：返回给定日期是那个月的第几天 

select dayofmonth(now()); // 16

dayofyear(date) ：返回给定日期是那一年的第几天 

select dayofyear(now()); // 106

流程函数

if(①,②,③) ： 如果①为true,返回②;如果①为false,返回③。类似三目操作符

select if(age > 18, '成年', ‘未成年);

ifnull (①， ②) ：①为空，返回②；否则返回①。

ifnull(①,②); // 如果①为空,返回②;如果①不为空,原路返回①

case when... 

// 类似if 
//     else if
//     else...
case when [A] then [A1]
     when [B] then [B1] 
     else [C]  end    
A为真则返回A1
B为真则返回B1
都不为真，返回C

 case val when ...

// 类似 switch case...
case val when [A] then [A1]
         when [B] then [B1]
         else [C] end
如果 val = A,返回A1
如果 val = B,返回B1
否则返回C

练习

1、由于需要，所有学生的id都从2位变成4位，不足4位的，在前面补0

update student set id = lpad(id,4,'0');

2、生成一个6位的随机验证码

第一步:select rand()*1000000; // 生成的数:685431.1964...
第二步:select round(rand()*1000000, 0) // 保留0位小数
// 还有可能是:0.001111 * 1000000 = 1111
// 所以在最左边或最右边填充0
select lpad(round(rand()*1000000, 0), 6, '0');

3、查询学生入学日期，以及入学到现在的总天数，并根据入学天数进行排序，最大值放在上面

select name as '姓名',
       entrydate as '入学日期',  
       datediff(now(), entrydate) as '入学天数' 
from student  order by 入学天数 desc;

4、查询学生姓名，18岁以上的显示'已成年'，18岁以下的显示'未成年'

select name,
      case when age > 18 then '成年' else '未成年' end
##      if(age > 18, '成年', '未成年')
from student;

5、查询学生地址，是北京/深圳的，显示一线城市，其他显示'二线城市'

select name ,
       case address when '北京' then '一线城市'
                    when '深圳' then '一线城市'
                    else '二线城市' end
from student;