# Learning objectives: master the following sql functions

Digital function: format ABS mod ceil floor ground exp
Character function: upper lower char > length concat instr
Date function: now date? Format date? Add DateDiff if ifnull
Conditional function:

## Numeric function

First batch of digital functions

function function Use case
ABS Absolute value ABS(-100)
ROUND Rounding ROUND(4.62)
FLOOR Forced house assignment FLOOR(9.9)
CEIL Mandatory carry CEIL(3.2)
POWER power function POWER(2,3)
LOG Logarithmic function LOG(7,3)
LN Logarithmic function LN(10) based on Changshu e
```-- Mathematical function 1
SELECT ABS(-100);    --Absolute value 100
SELECT ROUND(4.62);  --Round to 5
SELECT FLOOR(9.9);   --Rounding down 9
SELECT CEIL(3.2);    --Strong carry up 4
SELECT POWER(2,3);   --Power function 2 to the third power 8
SELECT LOG(7,3);     --log7(3)
SELECT LN(10);       --ln10

```

The second batch of digital functions

function function Use case
SQRT Square root SQRT(9)
PI PI PI() trigonometric function
SIN trigonometric function SIN(1)
COS trigonometric function COS(1)
TAN trigonometric function TAN(1)
COT trigonometric function COT(1)
```-- Mathematical function 2
SELECT SQRT(9);
SELECT PI();
SELECT SIN(1);
SELECT COS(1);
SELECT TAN(1);
SELECT COT(1);

```

## Date function

### Get system time function

The NOW() function can get the system date and time in the format yyyy MM DD HH: mm: SS
CURDATE() function can obtain the current system time in the format hh:mm:ss
CURTIME() function can obtain the current system time in the format hh:mm:ss

Date "format (date, expression) is used to refine information such as the day of the week \ the day of the day \

```-- Time function
SELECT NOW(),CURDATE(),CURTIME();
-- Check the employee's entry in that year
SELECT ename ,DATE_FORMAT(hiredate,"%j") FROM t_emp;
```

## Date format function

Case study:

```-- case:What's your birthday next year?
SELECT DATE_FORMAT("2020-11-22","%W");

-- Using date functions,How many employees were employed in the first half of 1981?
SELECT count(*) from t_emp where
DATE_FORMAT(hiredate,"%Y")=1981 AND DATE_FORMAT(hiredate,"%m")<=6;
```

### Date offset calculation function

Note to date function: in MySQL database, two dates can't be added or subtracted directly, and date can't be added or subtracted with number
The date add() function can realize date offset calculation, and the time unit is very flexible

```Date? Add (date, INTERVAL offset time unit)
```
• Day hour year
Case study:
```-- time offset

-- 6 Three more days ahead of the last three months_nested queries
SELECT DATE_FORMAT(
```

### Calculate gap between two dates

DateDiff (date, date) brackets can be date, can be year

```-- case:Calculate 2020-02-02 How many years since the founding of the people's Republic of China
SELECT DATEDIFF("2020-02-02","1949-10-01")/365;
```

### Character function

If the initial is capitalized, keep the last four digits of the phone's tail number, etc

### overview

Character function

• instar determines whether A appears and where it is
• insert 1,0 means to insert at the position of the first knife without replacing; change to 1, replace one character, change to 2, replace two characters
• replace is the string in front. replace the gentleman with the lady
Practice cases:
```-- Character function exercise:Lowercase name\Capital name\Name length(utf8 3 characters for a Chinese character)\Wage link dollar sign\Name in A Location
SELECT
LOWER(ename) , UPPER(ename), LENGTH(ename),CONCAT(sal,"\$") , INSTR(ename,"A")
FROM t_emp;

-- String exercise 2 insert,The first 1 means to insert at the position of 1 character;0 in the second position means no replacement,1 Replace 1,2 Replace two...
SELECT INSERT("Hello",1,0,"Sir");
-- String exercise 3 change the word "Hello Sir" in front of you to "madam"
SELECT REPLACE("Hello, sir.","Sir","Ma'am");
```

Character function 2

• Explanation: the difference between substr and substring is that 3 intercepts to 4, and 3 begins to intercept 2 characters
```-- Exercise content extraction
SELECT SUBSTR("Hello world",3,4),SUBSTRING("Hello world",3,2);

-- Practice lpad Telephone tail number

-- Practice rpad  Last name only
SELECT RPAD(SUBSTRING("Li Xiao Na",1,1),LENGTH("Li Xiao Na")/3,"*");

-- Practice TRIM  Blanking
SELECT TRIM("        Hello World       ");
```

## Conditional function

### IF and IFNULL

Conditional function can be used in SQL statement to realize conditional judgment in programming language
Ifnull (expression, replacement value)
IF (expression, value 1, value 2) true returns 1,false returns 2

```-- Conditional function SALES Employee law gifts for Department A,Other gifts B
FROM t_emp e join t_dept d on e.deptno=d.deptno;
```

### Conditional statements with multiple states

CASE WHEN expression THEN value 1 WHEN expression 2 THEN value 2 ELSE END

```SELECT e.empno , e.ename ,
CASE
WHEN d.dname='SALES' THEN  'P1'
WHEN d.dname='ACCOUNTING' THEN  'P2'
WHEN d.dname='RESEARCH' THEN  'P3'
ELSE
'HOME'
END AS place
FROM t_emp e join t_dept d on e.deptno=d.deptno
ORDER BY place;
```

Practice:

```-- The company decided to adjust the basic wage for the employees,The specific scheme is as follows
UPDATE t_emp e
LEFT join t_dept d on e.deptno=d.deptno
LEFT JOIN (SELECT deptno,AVG(sal) AS avgg FROM t_emp GROUP BY deptno) t ON e.deptno=t.deptno
SET e.sal=(
CASE
WHEN d.dname='SALES' and DATEDIFF(NOW(),e.hiredate)/365>=20  THEN e.sal*1.1
WHEN d.dname='SALES' and DATEDIFF(NOW(),e.hiredate)/365<20   THEN e.sal*1.05
WHEN d.dname='ACCOUNTING' THEN e.sal+300
WHEN d.dname='RESEARCH' AND e.sal<t.avgg THEN e.sal+200
WHEN d.dname IS NULL THEN e.sal+100
ELSE e.sal
END);
```

Summary: the following four common functions are involved

• Numeric function
• Character function
• Date function
• Conditional function
Published 41 original articles, won praise 5, visited 1062

Posted on Fri, 17 Jan 2020 09:10:21 -0500 by DKY