1. String function
length(a) gets the length of string a concat(a,b,c) splices multiple values into a string trim(a) remove the space at the beginning and end of string a replace(a,b,c) replace the b string in string a with C subString(str,index,len) intercepts the string str from the index character. The maximum number is len reverse(a) reverse string a locate(a,b) query the position of substring a in string B, starting from 12. Mathematical functions
abs(a) takes the absolute value of a sqrt(a) takes the square root of a mod(a,b) takes a%b to calculate the remainder after dividing a by B ceiling(a); round up to get the minimum integer greater than or equal to a floor(a); round down to get the maximum integer less than or equal to a round(a,b); round to B decimal places for a truncate(a,b); keep b decimal places for value a, and directly remove the redundant mantissa sign(a) returns 10 - 1 to indicate that a is a positive or negative number power(a,b) returns the B power of a rand() returns a random number between 0 and 1 floor(rand()*100) gets the random number between 0 and 1003. Date time function
addDate(d,days) Add to current date days day addDate(d,interval num unit) Add to current date days day unit Could be day month year week curdate() Get current system date curtime() Get system current time sysdate() Get system date time now() Get system date time date_add(d,interval num unit) Add the corresponding unit date_sub(d,interval num unit) Subtract the corresponding on the current date unit sundate(d,interval num unit) Subtract the corresponding on the current date unit date_format(now(),'%Y %m %d') Format date time in specified format date_format(now(),'%Y year%m month%d day %H:%i:%s')
4. Special functions
case when then end
If the employee's salary is more than 3000, the output will be excellent; if the salary is more than 2000, the output will be qualified; otherwise, the output will be poor
select sal,case when sal>=3000 then 'excellent' when sal>=2000 then 'qualified' else 'Poor' end from emp; select sal,case sal when 3000 then 'excellent' else 'All but' end from emp;
if(a,b,c) if a is true, output b, otherwise output C
//If 1 < 2 is true, output aa select if(1<2,'aa','bb'); select if(comm is null,0,comm) from emp;
ifnull(a,b) if a is null, output B
select ifnull(comm,0) from emp;
nullif(a,b) output null if a=b, if a! =b. Just output a
MySQL job:
1.select * from dept where deptno in(select distinct deptno from emp); 2.First look up SMITH Wages select sal from emp where ename='SMITH'; //Query the previous statement as a condition select * from emp where sal > (select sal from emp where ename='SMITH'); 3.select a.ename,b.ename from (select ename,mgr from emp) a,(select empno,ename from emp) b where a.mgr=b.empno 4.select a.* from (select * from emp) a,(select empno,ename,hiredate from emp) b where a.mgr=b.empno and a.hiredate<b.hiredate; 5.select a.dname,b.* from dept a left join emp b on a.deptno=b.deptno; 6.select e.ename,d.dname from dept d,emp e where e.job='CLERK' and d.deptno=e.deptno; 7.select job from emp group by job having min(sal)>1500; 8.select ename from emp,dept where emp.deptno=dept.deptno and dept.dname='SALES'; 9.select ename from emp where sal > (select avg(sal) from emp); 10.select ename,job from emp where job=(select job from emp where ename='SCOTT') and ename!='SCOTT'; 11.select ename,sal from emp where sal in (select sal from emp where deptno=30); 12.select ename,sal from emp where sal > (select max(sal) from emp where deptno=30); 13.Query owner's service life select hiredate,datediff(sysdate(),hiredate) from emp; select avg(sal)，count(empno),avg(datediff(sysdate(),hiredate)) Average time limit from emp group by deptno; 14.select e.ename,d.dname,e.sal from emp e join dept d on e.deptno=d.deptno; 15.select count(empno),dept.* from emp right join dept on emp.deptno=dept.deptno group by dept.deptno;
Xiaobai on the way 47 original articles published, 34 praised, 2147 visited Private letter follow