#concat 连接
SELECT CONCAT(dname,loc)FROM tb_dept;
#转换大写、小写LOWER
SELECT UPPER(ename) from tb_emp where empno = 8000;
#返回字符串长度
select length(ename) from tb_emp where empno=8000;
#截子串,从1开始
select substr(ename,1,2) from tb_emp where empno = 8000;
#返回当前时间
select now();
#查询入职时间是在1980年
select * from tb_emp where year(hiredate) = 1980;
#查询入职时间是在1981年2月
select * from tb_emp where year(hiredate) = 1981 and month(hiredate) = 2;
#插入时间
insert into tb_emp(empno,ename,job,hiredate,sal)
values(8001,'周杰伦','天王','2011-12-14',600);
insert into tb_emp(empno,ename,job,hiredate,sal)
values(8001,'蔡依林','小天后',now(),600);
#ifnull函数,如果字段不为null,则直接返回该值,如果为null,取第二个值
select comm,ifnull(comm,100) as comm2 from tb_emp ;
#if(exp1,exp2,exp3),如果字段不为null,取第二个值,如果为null,则取第三个值
select comm,if(comm,comm+100,100) as comm2 from tb_emp;
#条件判断语句
select ename,job,sal,comm'原定奖金',
case
when comm is null then 100
else comm
end as '奖金'
from tb_emp;
SELECT CONCAT(dname,loc)FROM tb_dept;
#转换大写、小写LOWER
SELECT UPPER(ename) from tb_emp where empno = 8000;
#返回字符串长度
select length(ename) from tb_emp where empno=8000;
#截子串,从1开始
select substr(ename,1,2) from tb_emp where empno = 8000;
#返回当前时间
select now();
#查询入职时间是在1980年
select * from tb_emp where year(hiredate) = 1980;
#查询入职时间是在1981年2月
select * from tb_emp where year(hiredate) = 1981 and month(hiredate) = 2;
#插入时间
insert into tb_emp(empno,ename,job,hiredate,sal)
values(8001,'周杰伦','天王','2011-12-14',600);
insert into tb_emp(empno,ename,job,hiredate,sal)
values(8001,'蔡依林','小天后',now(),600);
#ifnull函数,如果字段不为null,则直接返回该值,如果为null,取第二个值
select comm,ifnull(comm,100) as comm2 from tb_emp ;
#if(exp1,exp2,exp3),如果字段不为null,取第二个值,如果为null,则取第三个值
select comm,if(comm,comm+100,100) as comm2 from tb_emp;
#条件判断语句
select ename,job,sal,comm'原定奖金',
case
when comm is null then 100
else comm
end as '奖金'
from tb_emp;