Function syntax of MySQL database

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)
RADIANS Angle conversion radian RADIANS(30)
DEGRESE Radian conversion angle DEGREES(1)
-- Mathematical function 2
SELECT SQRT(9);
SELECT PI();
SELECT SIN(1);
SELECT COS(1);
SELECT TAN(1);
SELECT COT(1);
SELECT RADIANS(30); -- Angular radian

SELECT SIN(RADIANS(30));
SELECT COS(RADIANS(45));
SELECT TAN(RADIANS(30));
SELECT COT(RADIANS(45));
SELECT DEGREES(1); -- Radian angle

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
SELECT DATE_ADD(NOW(),INTERVAL 400 DAY);
SELECT DATE_ADD("2019-03-02",INTERVAL 500 day);

-- 6 Three more days ahead of the last three months_nested queries
SELECT DATE_FORMAT(
DATE_ADD( DATE_ADD( NOW(),INTERVAL -6 MONTH), INTERVAL -3 DAY), "%Y/%m/%d");

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
SELECT LPAD(SUBSTRING("13810888888",8,4),11,"*");

-- 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
SELECT e.empno, e.ename , d.dname,IF(d.dname='SALES','gift A','gift 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
Private letter follow

Tags: SQL angular MySQL Database

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