Common functions in MySQL

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 1

2. 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 100

3. 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;

 

47 original articles published, 34 praised, 2147 visited
Private letter follow

Tags: less MySQL

Posted on Sun, 19 Jan 2020 00:42:01 -0500 by Murciano