*Blog home page ——Xiaojie's blog home page to cheer
*Welcome to pay attention to likes collection messages
*Just learned, if ## title is wrong, please correct it!

Function table:

1. Statistical total function	effect
COUNT(*)	Returns the number of rows of records that meet the criteria
Count (column)	Returns the number of rows, but does not count NULL
SUM()	Sum
AVG()	Average
MAX()	Find the maximum value
MIN()	Find the minimum value

2. String function	effect
CHARSET()	Returns the string character set
CONCAT ()	connection string
UCASE()	Capitalize
LCASE()	Make lowercase
LENGTH()	string length
REPLACE(str,search_str,replace_str)	Replace word channeling
SUBSTRING(str,position,[,length])	Intercept string

3. Mathematical function	effect
ABS()	Return absolute value
CEILING()	Round up
FLOOR()	Round down
FORMAT(num.decimal_places)	Keep decimal places
RAND([seed])	Keep decimal places

String function	effect
CHARSET()	Returns the string character set
CONCAT ()	connection string
UCASE()	Capitalize
LCASE()	Make lowercase
LENGTH()	string length
REPLACE(str,search_str,replace_str)	Replace word channeling
SUBSTRING(str,position,[,length])	Intercept string

1. Statistical function - COUNT

Template:

Simple example:

SELECT COUNT(*) FROM student;
SELECT COUNT(*) FROM student WHERE math>=80;

Details:
Difference between count(*) and count (column):

1.count(*) is used to return the number of rows of records that meet the conditions.
2. Count (column) counts the number of columns that meet the conditions, but NULL will be excluded. That is, if the column data is NULL, the statistics will not be included.

2. Total function - SUM-AVG-MAX-MIN

Simple example:

SELECT SUM(`math`) FROM studen

SELECT AVG(`math`) FROM student

SELECT MAX(math) FROM student

Details:
1.SUM only works on numerical values and has no significance for other numerical types
2. For the summation of multiple columns, the "," sign cannot be less

3. Group statistics - use statistical functions

Template:


Simple example:
First, create an employee emp table and insert some information:

#Create EMP employee table
CREATE TABLE emp
(empno  MEDIUMINT UNSIGNED  NOT NULL  DEFAULT 0, /*number*/
ename VARCHAR(20) NOT NULL DEFAULT "", /*name*/
job VARCHAR(9) NOT NULL DEFAULT "",/*work*/
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*Superior number*/
hiredate DATE NOT NULL,/*Entry time*/
sal DECIMAL(7,2)  NOT NULL,/*salary*/
comm DECIMAL(7,2),/*dividend*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*Department number*/
) ;
-- drop table emp
INSERT INTO emp VALUES
(7369, 'SMITH', 'CLERK', 7902, '1990-12-17', 800.00,NULL , 20), 
(7499, 'ALLEN', 'SALESMAN', 7698, '1991-2-20', 1600.00, 300.00, 30), 
(7521, 'WARD', 'SALESMAN', 7698, '1991-2-22', 1250.00, 500.00, 30), 
(7566, 'JONES', 'MANAGER', 7839, '1991-4-2', 2975.00,NULL,20), 
(7654, 'MARTIN', 'SALESMAN', 7698, '1991-9-28',1250.00,1400.00,30),
(7698, 'BLAKE','MANAGER', 7839,'1991-5-1', 2850.00,NULL,30), 
(7782, 'CLARK','MANAGER', 7839, '1991-6-9',2450.00,NULL,10), 
(7788, 'SCOTT','ANALYST',7566, '1997-4-19',3000.00,NULL,20), 
(7844, 'TURNER', 'SALESMAN',7698, '1991-9-8', 1500.00, NULL,30), 
(7900, 'JAMES','CLERK',7698, '1991-12-3',950.00,NULL,30),
(7902, 'FORD', 'ANALYST',7566,'1991-12-3',3000.00, NULL,20), 
(7934,'MILLER','CLERK',7782,'1992-1-23', 1300.00, NULL,10);
SELECT *FROM emp

Two questions lead to the grouping:

SELECT SUM(`sal`),AVG(`sal`),deptno FROM emp GROUP BY deptno

Insert the code slice here SELECT AVG(`sal`) AS avg_sal,deptno FROM emp GROUP BY deptno
                                             HAVING avg_sal<2000;

Personal understanding:

If there is no matching statistical function, it is likely to report an error:

4. String function

Simple example:

SELECT CHARSET(sal) FROM emp

SELECT CONCAT(ename,'  ',job) FROM emp;

SELECT UCASE(sal) FROM emp; 

SELECT empno,CONCAT(LCASE(SUBSTRING(ename,1,1)),SUBSTRING(ename,2)) FROM emp

5. Mathematical function

SELECT CURRENT_DATE() FROM DUAL

SELECT CURRENT_TIME() FROM DUAL

SELECT CURRENT_TIMESTAMP() FROM DUAL

Small details:


LAST_DAY() can get the date of the last day of the month

6. Encryption function and system function

SELECT USER() FROM DUAL(View login to mysql Who are your users and who are logged in ip)

SELECT DATABASE() FROM DUAL

SELECT MD5('wangjie') FROM DUAL(Password encryption)

7. Control flow function

Bold style

SELECT IF(TRUE,12,23) FROM DUAL

Small details:

Small example:

CREATE TABLE t10(
	id INT,
	`name` VARCHAR(12));
INSERT INTO t10 VALUES(11,'clerk');
INSERT INTO t10 VALUES(12,'manager');
INSERT INTO t10 VALUES(13,'saleman');
INSERT INTO t10 VALUES(14,'clerk');
INSERT INTO t10 VALUES(15,'manager');
INSERT INTO t10 VALUES(16,'saleman');
SELECT *FROM t10

How to change English into Chinese?
Implementation code:

SELECT id,(SELECT CASE 
		WHEN `name` = 'clerk' THEN 'secretary'
		WHEN `name` = 'manager' THEN 'manager'
		WHEN `name` = 'saleman' THEN 'salesperson'
		ELSE `name` END) AS 'new'
		FROM t10;

The above part intercepts the PPT of Mr. Han Shunping's java course!