1. Aggregation function
SQL provides some functions that can calculate the columns of the queried records – aggregate functions.
1.count() statistical function, counts the number of values of specified characters that meet the conditions
The number of rebirth_mood in the statistics table
select count(column name) from table name;
#The number of rebirth_name=’lcl’ in the statistical table
select count(column name) from table name where restriction;
2.max() function counts the maximum value of characters that meet specified conditions
Query the largest item of rebirth_name in the table
select max(column name) from table name;
Query the largest item judged by adding conditions
select max(column name) from table name where constraints;
3.min() function counts the smallest value of characters that meet specified conditions
Query the smallest item of rebirth_name in the query table
select min() column name from table name where constraint;
4.sum() function, calculates the sum, and queries the sum of the specified columns in the records that meet the conditions
select sum(data column) from table name;
Query the sum of a column in a table
select sum(column name) from table name;
Query the sum of items with constraints in a column in the query table
select sum(column name) from table name where constraints;
5.avg() function, find the average, query the records that meet the conditions and calculate the average of the specified column
Query the average value in a column
select avg (column name) from table name;
Query the average value of items with constraints in a column
select avg(column name) from table name where constraints;
2. Date functions and string functions
Date function
Time addition and time functions
#Add data to the better_time column in the rebir table insert into rebirth( rebirth_name,rebirth_happen,rebirth_time,rebirth_mood,rebirth_go,better_time )values( 'lvcl','Getting better',20230929,'expected','insist','2023-10-26 21:43:00' ); #Time assignment is the current time now() function insert into rebirth( rebirth_name,rebirth_happen,rebirth_time,rebirth_mood,rebirth_go,better_time )values( 'wp','Getting better',20230928,'health','health',now() ); #Observe new data select * from rebirth;
now() function gets the current date
The sysdate() function obtains the current date and system time
#Time assignment is the current time now() function insert into rebirth( rebirth_name,rebirth_happen,rebirth_time,rebirth_mood,rebirth_go,better_time )values( 'lxr','better',20231117,'health','better',sysdate() );
Modify data in the table update
#Modify the happened item of the data named lxr to merry update rebirth set rebirth_happen ='merry' where rebirth_name='lxr';
3. String function
It is to process strings through SQL instructions.
Splicing multiple columns contact function
select contact(column name 1, connection symbol, column name 2) from table name;
# Splice the name and go fields together and use the middle content for splicing select concat(rebirth_name,'-best-',rebirth_go) from rebirth;
uppercase (column name) converts all values of this column field to uppercase
lower (column name) converts all values of this field to lowercase
upper (column name) converts the field value to uppercase
select upper(column name) from table name;
#upper (column name) converts the field value to uppercase select upper(rebirth_name) from rebirth;
lower (column name) converts all values of this field to lowercase
select lower(column name) from table name;
#lower (column name) Convert all field values to lowercase select lower(rebirth_name) from rebirth;
substring function string splitting function
substring (column name, interception starting position, interception number)
#substring (column name, interception starting position, interception digits) intercept the string part select rebirth_name,substring(rebirth_time,5,4) from rebirth;
