SQL advanced language

catalogue

alias         

  Subquery

exists

join query

view

combine

case

Date time function

Null and no values

regexp regular expression

  stored procedure

deadlock

        Common error codes

alias         

         During MySQL query, when the table name is long or some fields in the table are long, in order to facilitate writing or use the same table multiple times, you can set aliases for field columns or tables. When using the alias directly, it is concise and clear to enhance readability

Syntax:
For field aliases:
select the original field as the modified field, and the original field as the modified field from table name;
#as can be omitted.

example:

(root@localhost) [jianghu]> select name as n,classid as c from Dragon;

#Alias for column

         If the length of a table is long, you can use AS to set an alias for the table. In the process of query, you can directly use the alias to temporarily set the alias of the table to another

For table aliases:
select table alias. Original field as modified field [, table alias. Original field as modified field] from original table name as table alias;
#as can be omitted

(root@localhost) [jianghu]> select d.name as n ,d.classid as c from Dragon d;


Set aggregate function field to average  

(root@localhost) [jianghu]> select *,avg(age) 'average value' from Dragon;
+-----+-----------+------+--------+--------------+---------+-----------+
| Did | Name      | Age  | Gender | MasterId     | ClassId | average value    |
+-----+-----------+------+--------+--------------+---------+-----------+
|   1 | Breezy    |   56 | male     | Seek defeat alone     |       1 |   35.1905 |
+-----+-----------+------+--------+--------------+---------+-----------+
1 row in set (0.01 sec)

Usage scenario:
1. When querying complex tables, aliases can shorten the length of query statements
2. When multiple tables are connected for query (easy to understand and reduce the length of sql statement)

         In addition, AS can also be used AS an operator for join statements.
         Create the t1 table and insert all the query records of the Dragon table into the test1 table

#You can use as to create directly without inheriting special keys

create table test2 (select * from Dragon);

(root@localhost) [jianghu]> create table test1 as select * from Dragon;
Query OK, 21 rows affected (0.02 sec)
Records: 21  Duplicates: 0  Warnings: 0

Inherit special keys  

(root@localhost) [jianghu]> create table test3 like Dragon;
Query OK, 0 rows affected (0.01 sec)

  Subquery

         Subquery is also called inner query or nested query, which means that one query statement is also nested with another query statement.
         The sub query statement is executed before the main query statement, and its results are returned to the main query as outer conditions for further query filtering.
        Subquery: query based on the query results of a statement with poor performance nested in SQL statements
Syntax:
Select field from table 1 where field 2 [comparison operator] (select field 1 from table 2 where condition)
#The comparison operator can be=  >  <  >= <= It can also be a literal operator like in between

example:

In the same table,

(root@localhost) [jianghu]> select * from Dragon where age in (select age from Dragon where age>30);
+-----+--------------+------+--------+--------------+---------+
| Did | Name         | Age  | Gender | MasterId     | ClassId |
+-----+--------------+------+--------+--------------+---------+
|   1 | Breezy       |   56 | male     | Seek defeat alone     |       1 |
|   2 | Xiao Yuanshan       |   34 | male     | 2            |       3 |
|   5 | Zhang Sanfeng       |  100 | male     | 2            |       1 |
|  10 | Seek defeat alone     |   66 | male     | 1            |       1 |
|  11 | Guo Jing         |   33 | male     | 2            |       5 |
|  20 | xxx          |   50 | male     | 2            |       1 |
|  21 | xxx          |   50 | male     | null         |       1 |
|  22 | xxx          |   50 | male     | NULL         |    NULL |
+-----+--------------+------+--------+--------------+---------+
8 rows in set (0.00 sec)

Different tables  

Dragon table

(root@localhost) [jianghu]> select * from Dragon;
+-----+--------------+------+--------+--------------+---------+
| Did | Name         | Age  | Gender | MasterId     | ClassId |
+-----+--------------+------+--------+--------------+---------+
|   1 | Breezy       |   56 | male     | Seek defeat alone     |       1 |
|   2 | Xiao Yuanshan       |   34 | male     | 2            |       3 |
|   3 | invincible eastern     |   25 | female     | 7            |       2 |
|   4 | Xiao Feng         |   21 | male     | 6            |       3 |
|   5 | Zhang Sanfeng       |  100 | male     | 2            |       1 |
|   6 | linghu chong       |   25 | male     | 3            |       2 |
|   7 | Guo Yang         |   18 | male     | 3            |       3 |
|   8 | little dragon maiden       |   20 | female     | 3            |       3 |
|   9 | zhang wuji       |   27 | male     | 1            |       8 |
|  10 | Seek defeat alone     |   66 | male     | 1            |       1 |
|  11 | Guo Jing         |   33 | male     | 2            |       5 |
|  12 | Huang Rong         |   25 | female     | 2            |       5 |
|  13 | Hu Yidao       |   21 | male     | 3            |       8 |
|  14 | Yuan Chengzhi       |   19 | male     | 5            |       3 |
|  15 | Phyllostachys pubescens         |   19 | male     | 3            |       1 |
|  16 | Stone breaks the sky       |   24 | male     | 1            |       1 |
|  17 | duan yu         |   28 | male     | 1            |       1 |
|  18 | Ah Qing         |   28 | female     | 3            |       5 |
|  20 | xxx          |   50 | male     | 2            |       1 |
|  21 | xxx          |   50 | male     | null         |       1 |
|  22 | xxx          |   50 | male     | NULL         |    NULL |
+-----+--------------+------+--------+--------------+---------+
21 rows in set (0.00 sec)

one_db table

(root@localhost) [jianghu]> select * from one_db;
+----+--------------+------+--------+-------+
| id | name         | age  | Gender | oneid |
+----+--------------+------+--------+-------+
|  1 | lu xiaofeng       |   26 | male     |     2 |
|  2 | ximen chuixue     |   28 | male     |     3 |
|  3 | Chu Liuxiang       |   28 | male     |    18 |
|  4 | li xunguan       |   28 | male     |    15 |
|  5 | Wooden Taoist       |   56 | male     |     6 |
|  6 | Shen Lang         |   30 | male     |     8 |
+----+--------------+------+--------+-------+
6 rows in set (0.00 sec)

#Show one_db   The oneid in the table is less than 3

(root@localhost) [jianghu]> select * from Dragon where classid in (select oneid from one_db where oneid<3);
+-----+--------------+------+--------+----------+---------+
| Did | Name         | Age  | Gender | MasterId | ClassId |
+-----+--------------+------+--------+----------+---------+
|   3 | invincible eastern     |   25 | female     | 7        |       2 |
|   6 | linghu chong       |   25 | male     | 3        |       2 |
+-----+--------------+------+--------+----------+---------+
2 rows in set (0.00 sec)

Calculate average age

(root@localhost) [jianghu]> select avg(age) from Dragon;
+----------+
| avg(age) |
+----------+
|  35.1905 |
+----------+
1 row in set (0.00 sec

Find someone older than average

(root@localhost) [jianghu]> select * from one_db where age>(select avg(age) from Dragon);
+----+-----------+------+--------+-------+
| id | name      | age  | Gender | oneid |
+----+-----------+------+--------+-------+
|  5 | Wooden Taoist    |   56 | male     |     6 |
+----+-----------+------+--------+-------+
1 row in set (0.00 sec)

Show Dragon   The name and age fields in the table are greater than one_ Average in DB table  

(root@localhost) [jianghu]> select name,age from Dragon where age>(select avg(age) from one_db);
+--------------+------+
| name         | age  |
+--------------+------+
| Breezy       |   56 |
| Xiao Yuanshan       |   34 |
| Zhang Sanfeng       |  100 |
| Seek defeat alone     |   66 |
| Guo Jing         |   33 |
| xxx          |   50 |
| xxx          |   50 |
| xxx          |   50 |
+--------------+------+
8 rows in set (0.00 sec)

exists

         This keyword is mainly used to judge whether the result set of the sub query is empty when performing a sub query. If it is not empty, the value will be returned; Otherwise, it returns nonexistent

(root@localhost) [jianghu]> select * from Dragon where exists (select oneid from one_db where oneid<1);
Empty set (0.00 sec)

join query

inner join on returns only equal rows of join fields in two tables
Left join on: returns records including all records in the left table and those with the same join field in the right table
Right join on: returns records including all records in the right table and those with the same join field in the left table

Inner connection

(root@localhost) [jianghu]> (root@localhost) [jianghu]> select * from Dragon inner join one_db on Dragon.classid=one_db.oneid;
+-----+--------------+------+--------+----------+---------+----+--------------+------+--------+-------+
| Did | Name         | Age  | Gender | MasterId | ClassId | id | name         | age  | Gender | oneid |
+-----+--------------+------+--------+----------+---------+----+--------------+------+--------+-------+
|   2 | Xiao Yuanshan       |   34 | male     | 2        |       3 |  2 | ximen chuixue     |   28 | male     |     3 |
|   3 | invincible eastern     |   25 | female     | 7        |       2 |  1 | lu xiaofeng       |   26 | male     |     2 |
|   4 | Xiao Feng         |   21 | male     | 6        |       3 |  2 | ximen chuixue     |   28 | male     |     3 |
|   6 | linghu chong       |   25 | male     | 3        |       2 |  1 | lu xiaofeng       |   26 | male     |     2 |
|   7 | Guo Yang         |   18 | male     | 3        |       3 |  2 | ximen chuixue     |   28 | male     |     3 |
|   8 | little dragon maiden       |   20 | female     | 3        |       3 |  2 | ximen chuixue     |   28 | male     |     3 |
|   9 | zhang wuji       |   27 | male     | 1        |       8 |  6 | Shen Lang         |   30 | male     |     8 |
|  13 | Hu Yidao       |   21 | male     | 3        |       8 |  6 | Shen Lang         |   30 | male     |     8 |
|  14 | Yuan Chengzhi       |   19 | male     | 5        |       3 |  2 | ximen chuixue     |   28 | male     |     3 |
+-----+--------------+------+--------+----------+---------+----+--------------+------+--------+-------+
9 rows in set (0.00 sec)

Left connection means that all data on the left is available, and the qualified data on the right is displayed

(root@localhost) [jianghu]> select * from Dragon left join one_db on Dragon.classid=one_db.oneid;

Right connection: all data on the right will be displayed, and those on the left that meet the conditions will be displayed

(root@localhost) [jianghu]> select * from Dragon right join one_db on Dragon.classid=one_db.oneid;

view

----CREATE VIEW -- view, which can be regarded as a virtual table or a table that stores query results.
#The difference between the view and the table is that the table actually stores data, while the view is a structure based on the table, which itself does not actually store data.

#The temporary table will disappear automatically after the user exits or the connection to the synchronization database is disconnected, and the view will not disappear.

The view does not contain data, but only stores its definition. Its purpose is generally to simplify complex queries.
For example, you need to perform join queries on several tables and perform operations such as statistical sorting. It will be troublesome to write SQL statements,
Join several tables with a view, and then query the view, which is as convenient as querying a table.

Syntax:
create view "view table name" as select statement;

example:
 

(root@localhost) [jianghu]> create view v_test as select d.name from Dragon d inner join one_db o onn o.oneid=d.classid;

View view table
show tables;

Delete view table
drop view view name

(root@localhost) [jianghu]> drop view v_test;
Query OK, 0 rows affected (0.01 sec)


The view table itself does not actually store data   Just save the query results of a select statement

combine

---- UNION ----
         Joint set, which combines the results of two SQL statements. The fields generated by two SQL statements need to be of the same data type;
UNION: the data values of the generated results will not be repeated and will be sorted in the order of fields
Syntax: select statement 1 union select statement2

Select statement 1 union all select statement2

example:

(root@localhost) [jianghu]> select did,name,age,Gender from Dragon union select id,name,age,Gender from one_db;
+-----+--------------+------+--------+
| did | name         | age  | Gender |
+-----+--------------+------+--------+
|   1 | Breezy       |   56 | male     |
|   2 | Xiao Yuanshan       |   34 | male     |
|   3 | invincible eastern     |   25 | female     |
|   4 | Xiao Feng         |   21 | male     |
|   5 | Zhang Sanfeng       |  100 | male     |
|   6 | linghu chong       |   25 | male     |
|   7 | Guo Yang         |   18 | male     |
|   8 | little dragon maiden       |   20 | female     |
|   9 | zhang wuji       |   27 | male     |
|  10 | Seek defeat alone     |   66 | male     |
|  11 | Guo Jing         |   33 | male     |
|  12 | Huang Rong         |   25 | female     |
|  13 | Hu Yidao       |   21 | male     |
|  14 | Yuan Chengzhi       |   19 | male     |
|  15 | Phyllostachys pubescens         |   19 | male     |
|  16 | Stone breaks the sky       |   24 | male     |
|  17 | duan yu         |   28 | male     |
|  18 | Ah Qing         |   28 | female     |
|  20 | xxx          |   50 | male     |
|  21 | xxx          |   50 | male     |
|  22 | xxx          |   50 | male     |
|   1 | lu xiaofeng       |   26 | male     |
|   2 | ximen chuixue     |   28 | male     |
|   3 | Chu Liuxiang       |   28 | male     |
|   4 | li xunguan       |   28 | male     |
|   5 | Wooden Taoist       |   56 | male     |
|   6 | Shen Lang         |   30 | male     |
+-----+--------------+------+--------+
27 rows in set (0.00 sec)

Note that the field data types should be consistent   Int and int   Char and char  

case

         sql is used as keywords such as if then else
Syntax:
select 
The field name to be displayed 1 'can be customized',
The field name to be displayed 2 'can be customized',
case 
when condition 1 then result 1
when condition 2 then result 2
else 
end 'field name of display result'
The condition can be a numeric value or a formula. The else clause is not required
mysql> select                      # grammar
    -> Name 'name',                # Fields to display
    -> Age 'age',                 # Fields to display
    -> case                       # grammar
    -> When age < 18 then 'juvenile'   # condition
    -> When age < 30 then 'youth'   # condition
    -> When age < 45 then 'middle age'  # condition
    -> Else 'old age'                # condition
    -> End 'status'                 # The field name that displays the result
    -> from students;


Example 1:
 

(root@localhost) [jianghu]> select name, case when age<18 then 'under age' when age>=18 then 'adult' endd 'Adult or not' from Dragon;

Example 2:

(root@localhost) [jianghu]> select * ,
    -> case
    -> when gender='male' then 'M'
    -> when gender='female' then 'F'
    -> end 'Gender'
    -> from one_db;
+----+--------------+------+--------+-------+--------+
| id | name         | age  | Gender | oneid | Gender   |
+----+--------------+------+--------+-------+--------+
|  1 | lu xiaofeng       |   26 | male     |     2 | M      |
|  2 | ximen chuixue     |   28 | male     |     3 | M      |
|  3 | Chu Liuxiang       |   28 | male     |    18 | M      |
|  4 | li xunguan       |   28 | male     |    15 | M      |
|  5 | Wooden Taoist       |   56 | male     |     6 | M      |
|  6 | Shen Lang         |   30 | male     |     8 | M      |
+----+--------------+------+--------+-------+--------+
6 rows in set (0.00 sec)

Date time function

String functiondescribe
curdate()Returns the date of the current time
curtime()Returns the hour, minute, and second of the current time
now()Returns the date and time of the current time
month(x)Returns the month value in date x
week(x)The return date x is the week ordinal of the year
hour(x)Returns the hour value in x
minute(x)Returns the minute value in x
second(x)Returns the second value in x
dayofweek(x)Return x is the day of the week, 1 Sunday, 2 Monday
dayofmonth(x)The calculation date x is the day of the month
dayofyear(x)The calculation date x is the day of the year

#Current date

(root@localhost) [jianghu]> select curdate();
+------------+
| curdate()  |
+------------+
| 2021-12-04 |
+------------+
1 row in set (0.00 sec)

#Hour, minute and second of the current time

(root@localhost) [jianghu]> select curtime();
+-----------+
| curtime() |
+-----------+
| 19:41:33  |
+-----------+
1 row in set (0.00 sec)


#Current time mm / DD / yyyy H / min / S

(root@localhost) [jianghu]> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-12-04 19:42:04 |
+---------------------+
1 row in set (0.00 sec)

#Return month

(root@localhost) [jianghu]> select month('2021-12-04');
+---------------------+
| month('2021-12-04') |
+---------------------+
|                  12 |
+---------------------+
1 row in set (0.00 sec)

#Returns the week ordinal of the year

(root@localhost) [jianghu]> select week('2021-12-04');
+--------------------+
| week('2021-12-04') |
+--------------------+
|                 48 |
+--------------------+
1 row in set (0.00 sec)

#Return hour value

(root@localhost) [jianghu]> select hour('2021-12-04 19:44');
+--------------------------+
| hour('2021-12-04 19:44') |
+--------------------------+
|                       19 |
+--------------------------+
1 row in set (0.00 sec)

#Returns the value of the minute

(root@localhost) [jianghu]> select minute('2021-12-05 14:30:9');
+------------------------------+
| minute('2021-12-05 14:30:9') |
+------------------------------+
|                           30 |
+------------------------------+
1 row in set (0.00 sec)

#Returns the second value

(root@localhost) [jianghu]> select second('2021-12-04 19:45:56');
+-------------------------------+
| second('2021-12-04 19:45:56') |
+-------------------------------+
|                            56 |
+-------------------------------+
1 row in set (0.00 sec)


#Return day of the week

(root@localhost) [jianghu]> select dayofweek('2021-12-04');
+-------------------------+
| dayofweek('2021-12-04') |
+-------------------------+
|                       7 |
+-------------------------+
1 row in set (0.00 sec)

#Returns the value of a day in the month

(root@localhost) [jianghu]> select dayofmonth('2021-12-05');
+--------------------------+
| dayofmonth('2021-12-05') |
+--------------------------+
|                        5 |
+--------------------------+
1 row in set (0.00 sec)

#Returns the day of the year

(root@localhost) [jianghu]> select dayofyear('2021-12-05');
+-------------------------+
| dayofyear('2021-12-05') |
+-------------------------+
|                     339 |
+-------------------------+
1 row in set (0.00 sec)

Null and no values

NULL
The difference between value and null value is as follows:
The length of NULL value is 0, which does not occupy space; The length of NULL value is NULL, which takes up space.
IS NULL or IS   NOT NULL IS used to judge whether the field IS NULL or not. You cannot find out whether it IS NULL.
The judgment of null value is handled with = '' or < > '.
When calculating the number of records through count(), if a NULL value is encountered, it will be automatically ignored, and if a NULL value is encountered, it will be added to the record for calculation.

mysql> select length(null),length(' '),length('abc');
+--------------+-------------+---------------+
| length(null) | length(' ') | length('abc') |
+--------------+-------------+---------------+
|         NULL |           1 |             3 |
+--------------+-------------+---------------+

mysql> select length(null),length(''),length('abc');
+--------------+------------+---------------+
| length(null) | length('') | length('abc') |
+--------------+------------+---------------+
|         NULL |          0 |             3 |
+--------------+------------+---------------+
1 row in set (0.00 sec)

(root@localhost) [jianghu]> select * from Dragon where name is not null;
+-----+--------------+------+--------+--------------+---------+
| Did | Name         | Age  | Gender | MasterId     | ClassId |
+-----+--------------+------+--------+--------------+---------+
|   1 | Breezy       |   56 | male     | Seek defeat alone     |       1 |
|   2 | Xiao Yuanshan       |   34 | male     | 2            |       3 |
|   3 | invincible eastern     |   25 | female     | 7            |       2 |
|   4 | Xiao Feng         |   21 | male     | 6            |       3 |
|   5 | Zhang Sanfeng       |  100 | male     | 2            |       1 |
|   6 | linghu chong       |   25 | male     | 3            |       2 |
|   7 | Guo Yang         |   18 | male     | 3            |       3 |
|   8 | little dragon maiden       |   20 | female     | 3            |       3 |
|   9 | zhang wuji       |   27 | male     | 1            |       8 |
|  10 | Seek defeat alone     |   66 | male     | 1            |       1 |
|  11 | Guo Jing         |   33 | male     | 2            |       5 |
|  12 | Huang Rong         |   25 | female     | 2            |       5 |
|  13 | Hu Yidao       |   21 | male     | 3            |       8 |
|  14 | Yuan Chengzhi       |   19 | male     | 5            |       3 |
|  15 | Phyllostachys pubescens         |   19 | male     | 3            |       1 |
|  16 | Stone breaks the sky       |   24 | male     | 1            |       1 |
|  17 | duan yu         |   28 | male     | 1            |       1 |
|  18 | Ah Qing         |   28 | female     | 3            |       5 |
|  20 | xxx          |   50 | male     | 2            |       1 |
|  21 | xxx          |   50 | male     | null         |       1 |
|  22 | xxx          |   50 | male     | NULL         |    NULL |
+-----+--------------+------+--------+--------------+---------+
21 rows in set (0.00 sec)
(root@localhost) [jianghu]> select * from Dragon where classid is  null;
+-----+------+------+--------+----------+---------+
| Did | Name | Age  | Gender | MasterId | ClassId |
+-----+------+------+--------+----------+---------+
|  22 | xxx  |   50 | male     | NULL     |    NULL |
+-----+------+------+--------+----------+---------+
1 row in set (0.00 sec)

regexp regular expression

Matching patterndescribeexample
^Matches the start character of the text'^ bd' matches a string beginning with bd
$Matches the end character of the text'qn $' matches a string ending in qn
.Match any single character's.t 'matches any string with one character between S and t
*Matches zero or more characters before it'fo*t' matches T with any o before it
+Matches the preceding character 1 or more times'hom +' matches a string starting with ho followed by at least one m
character stringMatch contains the specified string'clo' matches the string containing clo
p1|p2Match p1 or p2'bg|fg' matches BG or FG
[...]Matches any character in the character set'[abc]' matches a or b or c
[^...]Matches any character that is not in parentheses[^ ab] matches a string that does not contain a or b
{n}Match the previous string n times'g{2}' matches a string containing 2 g's
{n,m}Match the previous string at least n times and at most m timesf{1,3} 'matches f at least once and at most three times

  stored procedure

Stored procedure benefits:

  1. Encapsulation

    Usually, multiple SQL statements are required to complete a logical function, and parameters are likely to be passed between statements. Therefore, writing logical functions is relatively more complex, and stored procedures can include these SQL statements in an independent unit, so that complex SQL statements can not be seen by the outside world, and the purpose can be achieved by simple call. And database professionals can modify the stored procedure at any time without affecting the application source code that calls it

  2. It can enhance the function and flexibility of SQL statements

    Stored procedures can be written in process control statements, have strong flexibility, and can complete complex judgments and complex operations.

  3. Reduce network traffic

    Because the stored procedure runs on the server side and executes fast, when the stored procedure is called on the client computer, only the call statement is transmitted in the network, which can reduce the network load.

  4. Improve performance

    After the stored procedure is successfully compiled, it will be stored in the database server. Later, the client can call it directly, so that all SQL statements will be executed from the server, so as to improve performance. However, it should be noted that the more stored procedures, the better. Excessive use of stored procedures will affect the system performance

  5. Improve database security and data integrity

    One way to improve the security of stored procedures is to take them as intermediate components. The stored procedures can do relevant operations on some tables, and then the stored procedures are provided to external programs as interfaces. In this way, the external program cannot directly operate the database table, and can only operate the corresponding table through the stored procedure. Therefore, to a certain extent, the security can be improved.

  6. Make data independent

    The independence of data can achieve the decoupling effect, that is, the program can call the stored procedure to replace the execution of multiple SQL statements. In this case, the stored procedure separates the data from the user. The advantage is that when the structure of the data table changes, the calling table does not need to modify the program, but only needs the database manager to rewrite the stored procedure.

  Syntax:
Create procedure < stored procedure name > ([procedure parameter [,...]]) < procedure body >
[process parameter [,...]] format
< procedure name >: try to avoid duplicate names with bui lt-in functions or fields
< procedure body >: statement
[in | out | inout] < parameter name > < type >

1) Process name
         The name of the stored procedure, which is created in the current database by default. If you need to create a stored procedure in a specific database, add the name of the database, DB, before the name_ name.sp_ name.
         It should be noted that you should try to avoid selecting the same name as the built-in MySQL function, otherwise an error will occur.

2) Process parameters
         Parameter list of the stored procedure. Where, < parameter name > is the parameter name and < type > is the type of parameter (which can be any valid MySQL data type). When there are mu lt iple parameters, the parameter list is separated from each other by commas. A stored procedure can have no parameters (in this case, a pair of parentheses need to be added after the name of the stored procedure), or it can have one or more parameters.
         MySQL stored procedures support three types of parameters, namely input parameters, output parameters and input / output parameters, which are identified by IN, OUT and INOUT keywords respectively. The input parameters can be passed to a stored procedure, and the output parameters are used when the stored procedure needs to return an operation result, and the input / output parameters can be used as both input parameters and output parameters.

3) Process body
         The main part of a stored procedure, also known as the stored procedure body, contains the SQL statements that must be executed when the procedure is called. This section starts with the keyword BEGIN and ends with the keyword END
         In mysql, the server processes SQL statements with a semicolon as the statement end flag by default. However, when creating a stored procedure, the stored procedure body may contain multiple SQL statements. If these SQL statements still use the semicolon as the statement terminator, the MySQL server will use the semicolon at the end of the first SQL statement as the terminator of the whole program instead of processing the subsequent SQL statements in the stored procedure body. This is obviously not possible.
         To solve the above problems, you usually use the delimiter command to modify the end command to other characters. The syntax format is as follows: delimiter$$
The syntax is as follows:
$$is a user-defined terminator. Usually, this symbol can be some special symbols, such as two "?" or two "¥".
When using the DELIMITER command, you should avoid using the backslash "\" character because it is an escape character of MySQL

After successfully executing this SQL statement, the end flag of any command, statement or program will be changed to two??
mysql > DELIMITER ??
If you want to change back to the default semicolon ";" as the end flag, you can enter the following statement in the MySQL command line client
mysql > DELIMITER ;
Note: there must be a space between DELIMITER and semicolon ";"

delimiter ??
CREATE PROCEDURE stored procedure name ()                                               
BEGIN                
Executed sql statement 1;
Executed sql statement 2;
end ??

Delimiter (must add space, must add space, must add space);

call stored procedure name

Example (creation without parameters)
##Create stored procedure##
DELIMITER $$                                                       # Change the closing symbol of the statement from semicolon; Temporarily changed to two $$(customizable)
CREATE PROCEDURE Proc()                                               # Create a stored procedure named proc without parameters
-> BEGIN                                                           # The process body starts with the keyword begin
-> create table mk (id int (10), name char(10),score int (10));   # Process style sentence
-> insert into mk values (1, 'wang',13);                          # Process style sentence
-> select * from mk;                                               # Process style sentence
-> END $$                                                           # The procedure body ends with the keyword end

DELIMITER ;                                                           # Returns the closing symbol of the statement to a semicolon


mysql> delimiter //
mysql> create procedure data()
    -> begin
    -> select now();
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call data;
+---------------------+
| now()               |
+---------------------+
| 2021-12-01 17:36:43 |
+---------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)


delimiter @@
create procedure proc (in inname varchar(40))    
begin
select * from info where name=inname;
end @@

delimiter ;        

call proc2('wangwu');    

Conditional judgment
if then else   ..... end if
delimiter $$
create procedure  proc6(in var int)
begin
if var>10 then
update students set age=age+1 where stuid=1;
end if;
end $$
 
delimiter ;
call proc(11)


Loop while do....end while

create table testlog (id int auto_increment primary key,name char(10),age int default 20);

delimiter $$

create procedure  ky15() 
begin  
declare i int;
set i = 1; 
while i <= 100000 
do  insert into testlog(name,age) values (concat('zhou',i),i); 
set i = i +1; 
end while; 
end$$

delimiter ;

select concat(zhou,1);
select * from testlog limit 10;


##Viewing stored procedures##
Format:
SHOW CREATE PROCEDURE [database.] stored procedure name;         # View specific information about a stored procedure
SHOW CREATE PROCEDURE  proc1

##Delete stored procedure##
The method of modifying stored procedure content is to delete the original stored procedure, and then create a new stored procedure with the same name.

DROP PROCEDURE IF EXISTS Proc;

deadlock

         It refers to the phenomenon that two or more transactions wait for each other due to competition for resources in the execution process. The so-called lock resource request generates a loop phenomenon, that is, an endless loop. At this time, it is said that the system is in a deadlock state or the system has a deadlock. Common error messages are "Deadlock found when trying to get lock...".      

         In practical application, we should try our best to prevent deadlock waiting. Here are several methods to avoid deadlock:

  1. If different programs access multiple tables concurrently or multiple rows of records are involved, try to agree to access the tables in the same order, which can greatly reduce the occurrence of deadlock.

  2. In business, transactions should be submitted or rolled back in time to reduce the probability of deadlock.

  3. In the same transaction, try to lock all resources required at one time to reduce the probability of deadlock.

  4. For business parts that are prone to deadlock, you can try to use upgrade lock granularity to reduce the probability of deadlock through table locking (table level locks will not produce deadlock).

        Common error codes

error code explain
1004could not create file
1005Unable to create data table, failed to create table
1006Unable to create database, failed to create database
1007Unable to create database, database already exists
1008Unable to delete database, database does not exist
1009The database file cannot be deleted, resulting in database deletion failure
1010Failed to delete the database because the data directory could not be deleted
1011Error deleting database file
1012Unable to read records in system table
1013Unable to get the status of
1014Unable to get working directory
1015Unable to lock file
1016could not open file
1017cannot find file
1018Unreadable directory
1019Unable to change directory for
1020The record has been modified by another user
1021There is not enough space left on the hard disk. Please increase the available space on the hard disk
1022Keyword reread, failed to change record
1023Error closing
1025An error occurred while changing the name
1032Record does not exist
1036The data table is read-only and cannot be modified
1037The system memory is insufficient. Please restart the database or server
1042Invalid host name
1044The current user does not have permission to access the database
1045Unable to connect to the database. The user name or password is incorrect

Common client error codes and descriptions are as follows:

2000Unknown MySQL error
2001Unable to create UNIX socket (% d)
2002Cannot connect to the local MySQL server via socket '% s' (% d), self service is not started
2003Unable to connect to MySQL server on% s (% d), MySQL service is not started
2004Cannot create TCP/IP adapter (% d)
2005Unknown MySQL server host '% s' (% d)
2007Protocol mismatch, server version =% D, client version =% d
2008MySQL client memory overflow
2009Bad host information
2010Local host connected via UNIX socket
2012An error occurred during the server handshake
2013The connection to the SQL Server was lost during the query
2014The command is out of sync and cannot be run now
2024Error connecting to slave server
2025Error connecting to primary server
2026SSL connection error

Tags: MySQL

Posted on Sun, 05 Dec 2021 03:57:56 -0500 by pozer69