catalogue
2. Advantages of stored procedures
4. Modify and delete stored procedures
1, Connection query
MySQL join queries usually combine record rows from two or more tables based on the common words between these tables
Segment for data splicing. First, determine a main table as the result set, and then selectively connect the rows of other tables to the selected table
On the result set of the specified main table. The most used connection queries include inner connection, left connection and right connection.
The left table is the main table, which is called left join query.
The right table is the main table, which is called right join query.
1. Inner connection
An internal connection in MySQL is a combination of data records in two or more tables that meet certain conditions at the same time. Usually in the FROM clause
In, the keyword INNER JOIN is used to connect multiple tables, and the ON clause is used to set the connection conditions. INNER JOIN is the system default
Table JOIN, so you can omit the INNER keyword after the FROM clause and only use the keyword JOIN.
When there are multiple tables at the same time, the INNER JOIN can also be used continuously to realize the internal connection of multiple tables. However, for better performance, the
It's best not to exceed three tables.
In short: inline query: output the record rows of the same field specified by the two tables through inner join.
Then make an inner connection between the two tables to distinguish them from the outer connection below.
2. Left connection
LEFT JOIN can also be called LEFT OUTER JOIN. Use the LEFT JOIN or LEFT OUTER JOIN key in the FROM clause
Words. The left join takes the left table as the base table, receives all rows of the left table, and uses these rows to match the records in the right reference table
Row matching, that is, matching all rows in the left table and qualified rows in the right table.
3. Right connection
RIGHT JOIN is also called RIGHT OUTER JOIN. Use the RIGHT JOIN or RIGHT OUTER JOIN keyword in the FROM clause
To show. The right join is opposite to the left join. It is based on the right table. It is used to receive all rows in the right table and use these records
The record matches the row in the left table.
Note: all the records in the left table in the left connection will be displayed, while the right table will only display the records that meet the search criteria, and the records in the right table are insufficient
All places are NULL.
In the right join query result set, in addition to the rows that meet the matching rules, there are also rows in the right table that do not match in the left table
These records are supplemented with NULL in the left table
2, Database function
The records stored in the database often need a series of arithmetic operations. MySQL provides functions to realize various functions. Commonly used
The functions of are divided into mathematical function, aggregate function, string function and date time function.
1. Mathematical function
| Common mathematical functions | |
| Mathematical function | describe |
| abs(x) | Returns the absolute value of x |
| rand() | Returns a random number from 0 to 1 |
| mod(x,y) | Returns the remainder of x divided by y |
| power(x,y) | Returns the y power of x |
| round(x) | Returns the integer closest to x |
| round(x,y) | Keep the y decimal places of x and the value after rounding |
| sqrt(x) | Returns the square root of x |
| truncate(x,y) | Returns the value of the number x truncated to y decimal places |
| ceil(x) | Returns the smallest integer greater than or equal to x, rounded up |
| floor(x) | Returns the largest integer less than or equal to x, rounded down |
| greatest(x1,x2...) | Returns the largest value in the collection |
| least(x1,x2...) | Returns the smallest value in the collection |
The following describes each common function in detail
mysql> select abs(addr) from info where id=2; #Returns the absolute value of the value of the addr field with id 2 +-----------+ | abs(addr) | +-----------+ | 2.345 | +-----------+ 1 row in set (0.00 sec) mysql> select rand(); #MySQL returns random numbers from 0 to 1 mysql> select rand()*100; #MySQL returns random numbers from 0 to 1 and multiplies them +--------------------+ | rand()*100 | +--------------------+ | 41.355874521057935 | +--------------------+ 1 row in set (0.00 sec) mysql> update info set addr=(rand(0)) where id=7; #Add the generated random number to the table mysql> select * from info; +----+-----------+-------+------------+-------+---------------------+ | id | name | score | address | hobby | addr | +----+-----------+-------+------------+-------+---------------------+ ...... | 7 | lilei | 11.00 | nanjing | 5 | 0.15522042769493574 | | 8 | caicai | 16.00 | nanjing | 5 | NULL | +----+-----------+-------+------------+-------+---------------------+ 8 rows in set (0.00 sec) mysql> select mod(5,2),mod(id,hobby) from info; #Returns the remainder of 5 divided by 2 and the remainder of id divided by hobby in the info table. +----------+---------------+ | mod(5,2) | mod(id,hobby) | +----------+---------------+ | 1 | NULL | | 1 | 0 | | 1 | 3 | | 1 | 4 | | 1 | 2 | | 1 | 0 | | 1 | 2 | | 1 | 3 | +----------+---------------+ 8 rows in set, 1 warning (0.00 sec) mysql> select power(2,3),power(id,hobby) from info; #The 3rd power of 2, the hobby power of di +------------+-----------------+ | power(2,3) | power(id,hobby) | +------------+-----------------+ | 8 | 1 | | 8 | 4 | | 8 | 81 | | 8 | 1024 | | 8 | 125 | | 8 | 216 | | 8 | 16807 | | 8 | 32768 | +------------+-----------------+ 8 rows in set (0.00 sec) mysql> select round(1.147),round(1.51),round(1.147592,4),round(1.51,1); #Returns the integer closest to 1.147; Returns the integer closest to 1.51; Keep 4 digits after the decimal point of 1.147592 and round; Keep 1.51 decimal place and round. +--------------+-------------+-------------------+---------------+ | round(1.147) | round(1.51) | round(1.147592,4) | round(1.51,1) | +--------------+-------------+-------------------+---------------+ | 1 | 2 | 1.1476 | 1.5 | +--------------+-------------+-------------------+---------------+ 1 row in set (0.00 sec) mysql>select sqrt(2),sqrt(4); +--------------------+---------+ | sqrt(2) | sqrt(4) | +--------------------+---------+ | 1.4142135623730951 | 2 | +--------------------+---------+ 1 row in set (0.00 sec) mysql> select truncate(sqrt(2),3); Keep 3 decimal places, but truncate Functions are not rounded (truncated) +---------------------+ | truncate(sqrt(2),3) | +---------------------+ | 1.414 | +---------------------+ 1 row in set (0.00 sec) mysql> select ceil(5.2),ceil(5.7),floor(5.2),floor(5.7); #Returns the smallest integer greater than or equal to 5.2 and 5.7; Returns the largest integer less than or equal to 5.2 and 5.7. +-----------+-----------+------------+------------+ | ceil(5.2) | ceil(5.7) | floor(5.2) | floor(5.7) | +-----------+-----------+------------+------------+ | 6 | 6 | 5 | 5 | +-----------+-----------+------------+------------+ 1 row in set (0.00 sec) mysql> select greatest(2,5,3),least(1,4,5); #Returns the maximum and minimum values in the array +-----------------+--------------+ | greatest(2,5,3) | least(1,4,5) | +-----------------+--------------+ | 5 | 1 | +-----------------+--------------+ 1 row in set (0.00 sec) mysql>
2. Aggregate function
There is a special group of functions in MySQL database functions, which are specially designed to sum the records in the database or summarize the data in the table
These functions are called aggregate functions.
| Aggregate function | |
| Aggregate function | describe |
| avg() | Returns the average value of the specified column |
| count() | Returns the number of non NULL values in the specified column |
| min() | Returns the minimum value of the specified column |
| max() | Returns the maximum value of the specified column |
| sum(x) | Returns the sum of all values for the specified column |
mysql> select sum(score) from info; #Returns the sum of scores in the info table +------------+ | sum(score) | +------------+ | 370.00 | +------------+ 1 row in set (0.00 sec) mysql> select count(score) from info; #Returns the number of score fields +--------------+ | count(score) | +--------------+ | 8 | +--------------+ 1 row in set (0.00 sec) mysql> select min(score),max(score) from info; #Returns the minimum and maximum value of a score +------------+------------+ | min(score) | max(score) | +------------+------------+ | 0.00 | 99.00 | +------------+------------+ 1 row in set (0.00 sec) mysql> select avg(score) from info; #Returns the average of the scores +------------+ | avg(score) | +------------+ | 46.250000 | +------------+ 1 row in set (0.00 sec) mysql>
3. String function
| Common string functions | |
| String function | describe |
| length(x) | Returns the length of the string x |
| trim() | Returns a value in the specified format |
| concat(x,y) | Splice the supplied parameters x and y into a string |
| upper(x) | Turns all letters of string x into uppercase letters |
| lower(x) | Turns all letters of string x into lowercase letters |
| left(x,y) | Returns the first y characters of string x |
| right(x,y) | Returns the last y characters of string x |
| repeat(x,y) | Repeat the string x y times |
| space(x) | Return x spaces |
| replace(x,y,z) | Replaces string y in string x with string z |
| strcmp(x,y) | Comparing x and y, the returned value can be - 1,0,1 |
| substring(x,y,z) | Gets a string of length z starting from the y-th position in string x |
| reverse(x) | Invert string x |
ysql> select length('ad c'),length(name) from info where id=2; #Return the length of adc, and the space is also a character; And return the length of the name field with id=2 in the info table.
+-----------------+--------------+
| length('ad c') | length(name) |
+-----------------+--------------+
| 5 | 6 |
+-----------------+--------------+
1 row in set (0.00 sec)
mysql> select' shen',trim(' shen'); #Returns a formatted value
+--------+---------------+
| shen | trim(' shen') |
+--------+---------------+
| shen | shen |
+--------+---------------+
1 row in set (0.00 sec)
mysql> select concat('abc','def'),concat(name,' ',score) from info; #Splice the supplied parameters into a string, and spaces are also characters
+---------------------+------------------------+
| concat('abc','def') | concat(name,' ',score) |
+---------------------+------------------------+
| abcdef | liuyi 0.00 |
| abcdef | wangwu 95.00 |
| abcdef | lisi 60.00 |
| abcdef | tianqi 99.00 |
| abcdef | jiaoshou 79.00 |
| abcdef | hanmeimei 10.00 |
| abcdef | lilei 11.00 |
| abcdef | caicai 16.00 |
+---------------------+------------------------+
8 rows in set (0.00 sec)
mysql> select upper('abc'),lower('ABC'),lower(upper('def')); #Change all letters of the string into uppercase letters; Turns all letters of a string into lowercase letters
+--------------+--------------+---------------------+
| upper('abc') | lower('ABC') | lower(upper('def')) |
+--------------+--------------+---------------------+
| ABC | abc | def |
+--------------+--------------+---------------------+
1 row in set (0.00 sec)
mysql> select left('abcdefg',2),right('abcdefg',3); #Returns the first 2 characters of the string; Returns the last 3 characters of a string
+-------------------+--------------------+
| left('abcdefg',2) | right('abcdefg',3) |
+-------------------+--------------------+
| ab | efg |
+-------------------+--------------------+
1 row in set (0.00 sec)
mysql> select repeat('ac',2),repeat(hobby,2) from info; #Repeat the string ac twice; Repeat the hobby field in the info table twice.
+----------------+-----------------+
| repeat('ac',2) | repeat(hobby,2) |
+----------------+-----------------+
| acac | |
| acac | 22 |
| acac | 44 |
| acac | 55 |
......
mysql> select length(space(3)),space(3); #Return 3 spaces and count the length of space characters as 3
+------------------+----------+
| length(space(3)) | space(3) |
+------------------+----------+
| 3 | |
+------------------+----------+
1 row in set (0.00 sec)
mysql> select replace('hello','ll','aa'); #Replace string aa in string ll with string hello.
+----------------------------+
| replace('hello','ll','aa') |
+----------------------------+
| heaao |
+----------------------------+
1 row in set (0.00 sec)
mysql> select strcmp(7,8),strcmp(7,7),strcmp(9,5),strcmp(3.3,2.4); #The value returned by comparing two values can be - 1,0,1. If it is less than - 1, it will return 0. If it is greater than 1, only these three values will be returned. It is the first different number to compare.
+-------------+-------------+-------------+-----------------+
| strcmp(7,8) | strcmp(7,7) | strcmp(9,5) | strcmp(3.3,2.4) |
+-------------+-------------+-------------+-----------------+
| -1 | 0 | 1 | 1 |
+-------------+-------------+-------------+-----------------+
1 row in set (0.00 sec)
mysql> select substring('abcdefg',3,4); #Returns the 4 characters starting from the 3rd character in a string
+--------------------------+
| substring('abcdefg',3,4) |
+--------------------------+
| cdef |
+--------------------------+
1 row in set (0.00 sec)
mysql> select reverse('abcdefg'),reverse(left('abcdefg',3)); #Invert the string to output; Returns the first three characters of a string and inverts the output
+--------------------+----------------------------+
| reverse('abcdefg') | reverse(left('abcdefg',3)) |
+--------------------+----------------------------+
| gfedcba | cba |
+--------------------+----------------------------+
1 row in set (0.00 sec)
4. Date time function
| Date time function | |
| String function | describe |
| 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 |
mysql> select curdate(), curtime(), now(); #Return to the current month, year and day; Return to the current time; Returns the current full date and time. +------------+-----------+---------------------+ | curdate() | curtime() | now() | +------------+-----------+---------------------+ | 2021-10-29 | 23:25:46 | 2021-10-29 23:25:46 | +------------+-----------+---------------------+ 1 row in set (0.00 sec) mysql> select month(now()),week(now()),hour(now()); #Return month; Returns the week of the year that the current date is; Returns the hour of the current time +--------------+-------------+-------------+ | month(now()) | week(now()) | hour(now()) | +--------------+-------------+-------------+ | 10 | 43 | 23 | +--------------+-------------+-------------+ 1 row in set (0.00 sec) mysql> select minute(now()),minute(curtime()),second(now()),second(curdate()); #Returns the minute of the current time; Returns the second of the current time +---------------+-------------------+---------------+-------------------+ | minute(now()) | minute(curtime()) | second(now()) | second(curdate()) | +---------------+-------------------+---------------+-------------------+ | 57 | 57 | 21 | 0 | +---------------+-------------------+---------------+-------------------+ 1 row in set (0.00 sec) mysql> select dayofweek(curdate()),dayofmonth(curdate()),dayofyear(curdate()); #What day of the week is it; The current date is the day of the month; What day of the year is the current date +----------------------+-----------------------+----------------------+ | dayofweek(curdate()) | dayofmonth(curdate()) | dayofyear(curdate()) | +----------------------+-----------------------+----------------------+ | 6 | 29 | 302 | +----------------------+-----------------------+----------------------+ 1 row in set (0.00 sec) mysql>
III
1. Overview
The stored procedure is a set of sql statements to complete the specified functions. These sql statement sets are stored in a specified name and called when used; It is faster and faster than traditional sql.
The function of stored procedure has been supported since version 5.0. It can speed up the processing speed of database and enhance the flexibility of database in practical application. In the process of using stored procedures, common or complex work is written in SQL statements in advance and stored with a specified name. This process is compiled and optimized and stored in the database server. When you need to use the stored procedure, you just need to call it. The traditional SQL statements that operate the database need to be compiled before execution. Compared with stored procedures, it is obvious that stored procedures are faster and more efficient in execution.
Stored procedure is created and saved in the database. It is not only a collection of SQL statements, but also can add some special control structures and control the access mode of data. Stored procedures have a wide range of applications, such as encapsulating specific functions, executing the same functions on different applications or platforms, and so on.
2. Advantages of stored procedures
(1) After one execution, the generated binary code will reside in the buffer to improve the execution efficiency
(2) SQL statements plus a collection of control statements are highly flexible
(3) In the server-side storage, when the client calls, reduce the network load
(4) It can be called repeatedly and can be modified at any time without affecting the client call
(5) It can complete all database operations and control the information access permission of the database
3. Create stored procedure
Syntax structure:
Create procedure < procedure name > ([procedure parameter [...]]) < procedure body >
[process parameter [,...]] format
[in | out | inout] < parameter name > < type >
Note: < procedure name >: try to avoid duplicate names with bui lt-in functions or fields
< procedure body >: statement
Input parameter: IN; Output parameter: OUT; Input / output parameters: INOUT
#Call stored procedure: call procedure name ();
#View stored procedure: SHOW CREATE PROCEDURE [database.] stored procedure name;
#View specified stored procedure information: SHOW PROCEDURE STATUS like '%proc%'\G
#Modify stored procedure: alter procedure < procedure name > [< characteristics >...]
#Delete stored procedure: DROP PROCEDURE IF EXISTS procedure name;
(1) creation without parameters
mysql> use ku; #Warehousing
Database changed
mysql> delimiter $$ #Temporarily change the closing symbol of the statement from semicolon ";" to two $$(customizable)
mysql> create procedure proc1() #Create a stored procedure named Proc1 without parameters
-> begin #The process body starts with the keyword BEGIN
-> create table mkk (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 terminator
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ; #Restore the closing symbol of the statement to a semicolon. Note that there is a space before the semicolon
mysql> call proc1();
+------+------+-------+
| id | name | score |
+------+------+-------+
| 1 | wang | 133 |
| 1 | wang | 13 |
+------+------+-------+
2 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
(2) stored procedure with parameters
IN input parameter: indicates that the caller passes IN a value to the procedure (the passed IN value can be literal or variable)
OUT output parameter: indicates that the procedure sends OUT a value to the caller (multiple values can be returned) (the outgoing value can only be a variable)
INOUT input / output parameter: it indicates that the caller passes in the value to the procedure and the procedure passes out the value to the caller (the value can only be variable)
Quantity)
mysql> delimiter $$
mysql> create procedure in_param(in p_in int)
-> begin
-> select p_in;
-> set p_in=2;
-> select P_in;
-> end $$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> set @p_in=1;
Query OK, 0 rows affected (0.00 sec)
mysql> call in_param(@p_in);
+------+
| p_in |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
+------+
| P_in |
+------+
| 2 |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> select @p_in;
+-------+
| @p_in |
+-------+
| 1 |
+-------+
1 row in set (0.00 sec)
mysql>
#As can be seen above, p_in is modified in the stored procedure, but does not affect @p_id, because the former is a local variable and the latter is a global variable.
mysql> select * from info;
+----+-----------+-------+------------+-------+---------------------+
| id | name | score | address | hobby | addr |
+----+-----------+-------+------------+-------+---------------------+
| 1 | liuyi | 0.00 | beijing | | 1.22 |
| 2 | wangwu | 95.00 | shengzheng | 2 | -2.345 |
| 3 | lisi | 60.00 | shanghai | 4 | -1.1475927 |
| 4 | tianqi | 99.00 | hangzhou | 5 | -1 |
| 5 | jiaoshou | 79.00 | laowo | 3 | 111 |
| 6 | hanmeimei | 10.00 | nanjing | 3 | 4.0024 |
| 7 | lilei | 11.00 | nanjing | 5 | 0.15522042769493574 |
| 8 | caicai | 16.00 | nanjing | 5 | NULL |
+----+-----------+-------+------------+-------+---------------------+
8 rows in set (0.00 sec)
mysql> delimiter @@
mysql> create procedure proc (in inname varchar(40)) #Line parameter
-> begin
-> select * from info where name=inname;
-> end @@
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call proc('wangwu'); #Argument
+----+--------+-------+------------+-------+--------+
| id | name | score | address | hobby | addr |
+----+--------+-------+------------+-------+--------+
| 2 | wangwu | 95.00 | shengzheng | 2 | -2.345 |
+----+--------+-------+------------+-------+--------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>
4. Modify and delete stored procedures
#Modify stored procedure
Alter procedure < procedure name > [< characteristic >...]
ALTER PROCEDURE GetRole MODIFIES SQL DATA SQL SECURITY INVOKER;
Modifications sqldata: indicates that the subroutine contains statements to write data
SECURITY: SECURITY level
INVOKER: when it is defined as INVOKER, it can be executed successfully as long as the executor has execution permission.
##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. mysql> ALTER PROCEDURE proc1 MODIFIES SQL DATA SQL SECURITY INVOKER; Query OK, 0 rows affected (0.00 sec) mysql> drop procedure if exists proc1; Query OK, 0 rows affected (0.01 sec) mysql> show create procedure proc1\G ERROR 1305 (42000): PROCEDURE proc1 does not exist mysql>
summary
1. order by keyword: single field sorting (asc, desc); Conditional query (where); Multi field sorting; Interval judgment and query do not duplicate records (and/or: and/or).
2. Query distinct: distinct must be placed at the beginning; Distinct can only use the fields that need to be de duplicated for operation; Distinct de duplication of multiple fields (several fields will be filtered only if they are repeated by colleagues).
3. The common aggregation functions used to query results are COUNT (COUNT), SUM (SUM), AVG (AVG), MAX (MAX), MIN (MIN). When group by is used to group results, the results can be grouped by one or more fields.
4. Query results: grouping and sorting; Grouping conditions.
5. limit: note that the position offset of the first record is 0, the second record is 1, and so on.
6. alias setting: it is concise and easy to read.
7. Wildcard character:% (the percent sign indicates zero, one or more characters)_ (underline indicates a single character).
8. Subquery: inner query or nested query.
9. View: a virtual table in the database. This virtual table does not contain real data, but is mapped.
10. Null value: the difference between null value and null value (air and vacuum); When the length of null value is 0, it does not occupy space, and when the length of null value is null, it occupies space; Is null cannot determine null value; Null values are processed with "=" or "< >" (! =); null will be ignored during count() calculation, and null values will be added to the calculation.
11. MySQL regular expression: use the keyword regexp to specify the matching pattern of regular expression.
12. Operators: arithmetic operators, comparison operators, logical operators, and bitwise operators.
13. Connection query: inner join / join, left join and right join.
14. Database functions: mathematical function, aggregate function, string function, date time function
15. Stored procedure: in actual database applications, some database operations may be very complex and may require multiple SQL statements to be processed together. At this time, stored procedures can be used to complete this requirement easily and efficiently, which is somewhat similar to the functions in shell scripts.