MySQL Query basics

Query: Field Select field 1, Field 2, Field 3 from table name where condition

"+" sign: Only for numeric calculations select 1+2 or select 1+ ' 2 '

"CONCAT": Used for connection string CONCAT (character 1, character 2), such as a select CONCAT (12) query result. If there is a null parameter in the CANCAT function, the result is null.

"Ifnull": Specify a default value for NULL, Syntax ifnull (field, default) such as Select Ifnull (null,0), and then select CONCAT (character 1,ifnull (character 2,0)) from table name.

Conditional query

Conditional Expressions:<, >, =, <> (can also be!) =), <=, >=, <=> (Security equals, can be judged by null value)

Logical operators:&&, | |,! ; generally use and, or, not instead

Fuzzy query: Like, between and, In/not in, is null, is not NULL

Like is generally followed by the wildcard character% (any number of characters contains 0), _ (any single character matches). Wildcards can be escaped with "\" in front, or escaped with escape declaration.

Between and can improve the simplicity of the conditional query, including the threshold value, the order of non-exchangeable conditions (generally small in front, large in the rear)

Order By:order by Keyword ASC/DESC by ASC (Ascending)

Common functions

Character functions: Length (field), Concat (Field 1, Field 2,...), Upper (field), Lower (field), substr (substring, SQL index starting from 1), InStr (field, String), trim (character from field), Rpad (field, length, character) right padding, lpad (field, length, character) left padding

Mathematical functions: Round rounding, ceil up rounding, floor down rounding, truncate interception, mod redundancy (mod (A, b) <=>a-a/b*b)

Date function: Now () returns the current time (date + time), curdate () Current date, curtime () Current time, year (date), month (date), MonthName (date), day (date), hour (date ), minute (date), second (date), Str_to_date, Date_format, Datedif (DATE1,DATE2)

System functions: Version () current, database () current, user () Current database

Process Control functions: if (expression, value 1, value 2), Case expression, or field when constant 1 then value or statement when constant 1 then value or statement else value or statement, case when expression 1 then value or statement case when expression 2 Then value or statement else value or statement.

grouping functions: sum (field) SUM, AVG (field) average, max (field), Min (field), Count (field). These grouping functions ignore computed null values. Under the InnoDB storage engine, the efficiency of count (*) or COUNT (1) is greater than the efficiency of count (field).

MySQL Query basics