First, common functions

The built-in functions provided by the MySQL database include mathematical functions, string functions, date and time functions, aggregation functions, and conditional judgment functions. These built-in functions can help users more easily process data in tables and simplify user operations. MySQL built-in functions

  1. now

The now() function returns the current date and time. In practice, most business tables have a create_time field that records when each piece of data was created. When inserting data into the table, you can use the now() function in the INSERT statement. For example, insert a student in the student table:

insert into student(id,name,age,create_time) values (1.'jack'.20,now());
Copy the code
  1. date_format

The date_format() function is used to display the date or time in a specified format. In practical applications, the date and time are generally stored in a standard format. However, when querying data, there are often different format requirements, which requires date_format() function for format conversion. For example, to print the student’s birthday information as ‘2000-12-01’ :

select name,date_format(birthdat,'%Y-%m-%d') from student;
Copy the code
  1. Aggregation function

An aggregate function evaluates a set of values and returns a single value. There are five commonly used aggregate functions:

function describe
count Returns the total number of eligible records
sum Returns the sum of the specified columns, ignoring null values
avg Returns the average value of the specified column, ignoring null values
min Returns the minimum value of the specified column, ignoring null values
max Returns the maximum value of the specified column, ignoring null values

The NULL value in TIP refers to NULL

  1. ifnull

The ifnull() function is used to handle NULL values, ifnull(v1,v2), if v1 is not NULL, return v1, otherwise return v2. For example, if the student’s home address is null, the address is not filled:

select name,ifnull(address,'Not filled in') from student;
Copy the code
  1. case when

Case WHEN is a process control statement, and you can use case WHEN in SQL statements to get more accurate and direct results. Case when in SQL is similar to if else or switch in a language. There are two kinds of syntax:

# the firstCASE [col_name] WHEN [value1] TEHN [result1]...ELSE [default] END# the secondCASE WHEN [expr]  TEHN [result1]...ELSE [default] END
Copy the code

For example, the sex field in the student table 1 represents male, 2 represents female, and 0 represents unfilled.

select name , case sex when 1 then 'male'  when 2 then 'woman' else 'Not filled in' end as sex form student;
Copy the code