9. Common functions

One ： String function

1. String concatenation

select concat(' The number is ',empno,' The employees' , The name is ',ename) from emp;

2. Change the string to lowercase

select lower('Hello') from dual

3. Change the string to uppercase

select upper('Hello') from dual;

4. Get string length

select length('hello') from dual;

5. Invert string

select reverse('hello') from dual;

6. Remove the spaces on both sides of the string

select trim(' hello ') from dual;

7. The string s Medium s1 Replace with s2

select replace('hello world','o','xx') from dual

8. The string s repeat n Go back to

select repeat('hello',3) from dual

9. In string s Use... On the left of the s1 Fill in , Until the length is len

select lpad('hello',8,'x') from dual;

10. character string s Use... On the right of s1 Fill in , Until the length is len

select rpad('hello',8,'x') from dual;

11. From i String starting at s Intercept , Intercept len individual

select substr('hello',2,3) from dual;

Two ： Numerical function

1. Return is greater than the n Minimum integer of

select ceil(10.1) from dual;

2. Back to less than n Maximum integer for

select floor(10.1) from dual;

3. take n Retain y Decimal place , rounding

select round(3.1415.3) from dual;

4. take n Retain y Decimal place , Do not round off

select truncate(3.1415,3) from dual;

5. return 0 To 1 The random number

select rand() from dual;

3、 ... and ： Date and time functions

1. Returns the current date time

select now() from dual;

2. Return current date

select curdate() from dual;

3. Return current time

select curtime from dual;

4. Returns the year in the date

select year('2018-2-14') from dual;

5. Returns the month in the date

select month('2018-2-14') from dual;

6. Returns the day of the date

select day('2018-2-14') from dual;

7. Returns the integer between two dates and times

select timestampdiff(day,'1993-9-23','2018-11-22') from dual;

8. Format date

select date_format(now(),'%Y year %m month %d Japan  %H:%i:%s') from dual;

Four ： Process control functions

1.if(f,v1,v2) If f It's true , Then return to v1, Otherwise return to v2

select if(5>2,'yes','no') from dual;

2.ifnull(v1,v2) If v2 Not for null, Then return to v1, Otherwise return to v2

select ifnull(null,'0') from dual

3.case when f1 then v1 when f2 then v2…else v end If f1 It's true , Then return to v1; If f2 It's true , Then return to v2… Otherwise return to v

select case when 5<2 then 'one' when 6>4 then 'two' 
else  'three' end 
from dual;

5、 ... and ： System information function

1. Returns the database of the current operation

select database();

2. Return the currently logged in user

select user();

3. return MySQL Server version

select version();