MySQL中常见的内置函数及其作用
函数:是指一段可以直接被另一段程序直接调用的程序或代码。
1、字符串函数
2、数值函数
3、日期函数
4、流程控制函数
具体函数练习语句如下:
select concat('Hello', 'mysql');
select lower('Hello');
select upper('Hello');
select lpad('01', 5, '-');
select rpad('01', 5, '-');
select trim(' hello, world. ');
select substring('Hello, mysql', 1,5);
update emp set workno = lpad(workno,5, '0');
select ceil(1.1);
select floor(1.9);
select mod(3,4);
select rand();
select round(2.34, 1);
select lpad(round(rand()*1000000, 0), 6, '0');
select curdate();
select curtime();
select now();
select YEAR(now());
select MONTH(now());
select DAY(now());
select date_add(now(),INTERVAL 70 DAY );
select date_add(now(),INTERVAL 70 MONTH );
select date_add(now(),INTERVAL 70 YEAR );
select datediff('2021-12-01', '2021-11-01');
select name, datediff(curdate(), entrydate) as 'entrydays' from emp order by entrydays desc ;
select if(true, 'OK', 'Error');
select ifnull('OK', 'Default');
select ifnull('', 'Default');
select ifnull(null, 'Default');
select
name,
(case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) as '工作地址'
from emp;
create table score(
id int comment 'ID',
name varchar(20) comment '姓名',
math int comment '数学',
english int comment '英语',
chinese int comment '语文'
) comment '学员成绩表';
insert into score(id, name, math, english, chinese) VALUES (1, 'Tom', 67,88,95),(2,'Rose', 23,66,90),(3,'Jack', 56,98,76);
select
id,
name,
(case when math >= 85 then '优秀' when math >= 60 then '及格' else '不及格' end) '数学',
(case when score.english >= 85 then '优秀' when score.english >= 60 then '及格' else '不及格' end) '英语',
(case when score.chinese >= 85 then '优秀' when score.chinese >= 60 then '及格' else '不及格' end) '语文'
from score;