Shulou(Shulou.com)06/01 Report--

Because of the powerful function and complex logic of the database, sql alone can not meet the needs of our use and development, so mysql or each database service has its own extra syntax that can be used with sql statements to achieve more complex logic.

Application scenarios of mysql programming stored procedures, custom functions, triggers and other syntax rules (1) comments

Line comments: # and-- with spaces

Block comments: / * /

(2) statement Terminator

The default statement Terminator for mysql is a semicolon

Two additional\ g and\ G are provided under the mysql command line.

You can use the delimiter + new statement Terminator to modify to a new statement Terminator

For example, delimiter $$this modifies the statement Terminator to $$

(3) variable system variable

Here system variables refer to the variables defined during system initialization. These system variables are used to describe the environment in which mysql (or other databases) are located, so I understand them as mysql system variables.

User-defined variable

Define syntax

Set variable name = variable value; to distinguish between system variables, field names, and custom variables. We all start with @ for custom variables. For example: br/ > to distinguish between system variables, field names, and custom variables. We all start with @ for custom variables. For example:

Set @ aa = 1

If it does not start with @, then the mysql system will think that you want to modify a system variable. If there is a system variable with the same name, its value may be changed unexpectedly, and if there is no system variable with the same name, mysql will report an error.

Select into syntax

Select field name list into variable name list

Select values list into variable name list

For example:

Select 1, 2, 2, 3, into @ a, 1, 2, 2, 3, 3, 1, 2, 3, 3, 1, 2, 3, 3, 1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2, 3, into @ a.

: = assignment syntax

Due to the particularity of the equal sign (=) in the select statement. A = is a comparison, so we use: = to assign a value. : = can also be used in set, but can only be used in select: = View custom variables

To see the defined variable, all we need is the "select variable name;"

Life cycle

When the life cycle of a custom variable is accompanied by the operation MySQL, the entire session cycle is valid from connecting to disconnecting to disconnecting the database.

Scope

The global scope, if it is inside the function, then the scope is inside the function.

(4) operator

Operators are almost 90% the same in many languages, nothing more than arithmetic operators, logical operators, and bit operators.

(5) process control branch structure if

If condition then statementelseif contdition then statement1else statement2end if

Case

CASE case_valueWHEN when_value THEN statement_ list[WHEN when_value THEN statement_list]... [ELSE statement_list] END CASE

Or

CASEWHEN search_condition THEN statement_ list[WHEN search_condition THEN statement_list]... [ELSE statement_list] END CASE

Circular statement looprepeatwhile

Since this loop structure cannot be demonstrated separately, I intend to learn it in detail in future stored procedures. Its syntax can be found in the official https://dev.mysql.com/doc/refman/8.0/en/flow-control-statements.html documentation.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.