SQL-初识函数

 

 

select concat('宝','我好爱你呀');
select lower('hadsjkfhJKHLH');
select upper('hadsjkfhJKHLH');
select LPAD('爱你',3,'我');
select rPAD('爱你',3,'我');
select trim('    吉林省雕刻技法  萨');
select SUBSTRING('我曾经跨过山河大海',1,4);

 

select lpad('1',5,'00000');

show create table user;
alter table user modify worknum varchar(12);
update user set worknum = lpad(worknum,11,'0');

 

select ceil(1.2);
select floor(1.2);
select mod(8,9);
select rand();
select round(rand(),0);

 

select round(rand(),6) * 1000000;/

select lpad(round(rand(),6) * 1000000,6,'0');

select curdate();
select curtime();
select now();
select year(curdate());
select month(curdate());
select day(curdate());
select date_add(curdate(),interval 2 month );
select date_add(curtime(),interval 2 MINUTE );
select datediff('2021-10-1',now());

 

 

select name,datediff(now(),entrydata) '入职天数' from user order by datediff(now(),entrydata) desc;

 

 

select if(true,'OK','fault');
select ifnull(null,' ');
-- 查询城市广东为0，广东汕头为1
select name,
    (case address when '广东' then '省' else '市' end) as '地址范围'
    from user;

select name,
       (case when soces >= 85 then '优秀' when soces >= 60 && soces < 85 then '及格' else '不及格' end) as '分数'
from user;