join query
MySQL connection query usually combines the record rows from two or more tables to splice data based on the common fields between these tables.
First, determine a main table as the result set, and then selectively connect the rows of other tables to the selected main table result set. Connection queries that are frequently used include inner connection, left connection and right connection
Environmental preparation
#establish mysql> create table xiaohei (b_id int(10) default null,b_name varchar(15) default null,b_level int(10) default null); Query OK, 0 rows affected (0.00 sec) #Insert information mysql> insert into xiaobai values (1,'One plus',10); Query OK, 1 row affected (0.00 sec) mysql> insert into xiaobai values (2,'Two plus',20); Query OK, 1 row affected (0.00 sec) mysql> insert into xiaobai values (3,'Three plus',30); Query OK, 1 row affected (0.00 sec) mysql> insert into xiaobai values (4,'Four plus',40); Query OK, 1 row affected (0.00 sec) mysql> insert into xiaohei values (2,'Two plus',20); Query OK, 1 row affected (0.00 sec) mysql> insert into xiaohei values (3,'Three plus',30); Query OK, 1 row affected (0.00 sec) mysql> insert into xiaohei values (5,'Acanthopanax',50); Query OK, 1 row affected (0.00 sec) mysql> insert into xiaohei values (6,'Six plus',60); Query OK, 1 row affected (0.00 sec) mysql> select * from xiaobai; +------+--------+---------+ | a_id | a_name | a_level | +------+--------+---------+ | 1 | One plus | 10 | | 2 | Two plus | 20 | | 3 | Three plus | 30 | | 4 | Four plus | 40 | +------+--------+---------+ 4 rows in set (0.00 sec) mysql> select * from xiaohei; +------+--------+---------+ | b_id | b_name | b_level | +------+--------+---------+ | 2 | Two plus | 20 | | 3 | Three plus | 30 | | 5 | Acanthopanax | 50 | | 6 | Six plus | 60 | +------+--------+---------+ 4 rows in set (0.00 sec)
inner join
Only rows with equal join fields in two tables are returned
Syntax: SELECT field FROM table 1, inner join table 2, on table 1. Field = Table 2. Field;
SELECT column_name(s)FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;
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.
In general, the keyword INNER JOIN is used in the FROM clause to connect multiple tables, and the ON clause is used to set the connection conditions. INNER JOIN is the default table connection of the system. Therefore, the INNER keyword can be omitted after the FROM clause, and only the keyword JOIN can be used. When there are multiple tables at the same time, you can also continuously use INNER JOIN to realize the internal connection of multiple tables. However, for better performance, it is recommended not to exceed three tables.
mysql> select a_id,a_name,a_level from xiaobai a inner join xiaohei b on a.a_id=b.b_id; +------+--------+---------+ | a_id | a_name | a_level | +------+--------+---------+ | 2 | Two plus | 20 | | 3 | Three plus | 30 | +------+--------+---------+ 2 rows in set (0.00 sec)
External connection (left connection, right connection)
Left connection
LEFT JOIN can also be called LEFT OUTER JOIN, which is represented by LEFT JOIN or LEFT OUTER JOIN keywords in the FROM clause.
The left connection 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, that is, match all rows in the left table and qualified rows in the right table.
All records in the left table will be displayed, while only records that meet the search criteria will be displayed in the right table. All insufficient records in the right table are NULL.
#Left connection mysql> select * from xiaobai left join xiaohei on a_name=b_name; +------+--------+---------+------+--------+---------+ | a_id | a_name | a_level | b_id | b_name | b_level | +------+--------+---------+------+--------+---------+ | 2 | Two plus | 20 | 2 | Two plus | 20 | | 3 | Three plus | 30 | 3 | Three plus | 30 | | 1 | One plus | 10 | NULL | NULL | NULL | | 4 | Four plus | 40 | NULL | NULL | NULL | +------+--------+---------+------+--------+---------+ 4 rows in set (0.00 sec)
Right connection
Right joins are also called right outer joins, which are represented by the RIGHT JOIN or RIGHT OUTER JOIN keywords in the FROM clause.
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 match these records with the rows in the left table
In the query result set of the right join, 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.
#Right connection mysql> select * from xiaobai right join xiaohei on a_name=b_name; +------+--------+---------+------+--------+---------+ | a_id | a_name | a_level | b_id | b_name | b_level | +------+--------+---------+------+--------+---------+ | 2 | Two plus | 20 | 2 | Two plus | 20 | | 3 | Three plus | 30 | 3 | Three plus | 30 | | NULL | NULL | NULL | 5 | Acanthopanax | 50 | | NULL | NULL | NULL | 6 | Six plus | 60 | +------+--------+---------+------+--------+---------+ 4 rows in set (0.00 sec)
Database function
Mathematical function
The records stored in the database often need a series of arithmetic operations, so MySQL supports many mathematical functions. The commonly used mathematical functions are shown in the table
Export tables in mysql to excel format
Mathematical function describe abs(x) return x Absolute value of rand() Returns a random number from 0 to 1 mod(x,y) return x divide y Remainder after power(x,y) return x of y Power round(x) Return from x Nearest integer round(x,y) retain x of y Value rounded to decimal places sqrt(x) return x Square root of truncate(x,y) Return number x Truncate to y Decimal value ceil(x) Returns greater than or equal to x Minimum integer of floor(x) Returns less than or equal to x Maximum integer of greatest(x1,x2...) Returns the largest value in the collection least(x1,x2...) Returns the smallest value in the collection
#Absolute value of 5 mysql> select abs(-5); +---------+ | abs(-5) | +---------+ | 5 | +---------+ 1 row in set (0.00 sec) #A random number of 0-1 (0 < = x < 1) returns a random number from 0 to 1 mysql> select rand(); +--------------------+ | rand() | +--------------------+ | 0.6677741489073021 | +--------------------+ 1 row in set (0.00 sec) #Collocation operator mysql> select rand()*10; +-------------------+ | rand()*10 | +-------------------+ | 6.347451262499626 | +-------------------+ 1 row in set (0.00 sec) #Divide 3 by 2 mysql> select mod(3,2); +----------+ | mod(3,2) | +----------+ | 1 | +----------+ 1 row in set (0.00 sec) #The third power of 3 mysql> select power(3,3); +------------+ | power(3,3) | +------------+ | 27 | +------------+ 1 row in set (0.00 sec) #Value closest to 2 mysql> select round(2.15); +-------------+ | round(2.15) | +-------------+ | 2 | +-------------+ 1 row in set (0.00 sec) #Take the last digit of 2.345 and round mysql> select round(2.345,1); +----------------+ | round(2.345,1) | +----------------+ | 2.3 | +----------------+ 1 row in set (0.00 sec) . . . Don't make a case, read the copy by yourself
Aggregate function
There is a special group of functions in MySQL database functions, which are specially designed for summation of records in the database or centralized generalization of data in tables. These functions are called aggregate functions.
Aggregate function describe avg() Returns the average value of the specified column count() Returns the value of the non column in the specified column NULL Number of values 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
#Find the sum of level s in the small white table mysql> select sum(a_level) from xiaobai; +--------------+ | sum(a_level) | +--------------+ | 100 | +--------------+ 1 row in set (0.00 sec) #Returns the number of id fields in the small white table mysql> select count(a_id) from xiaobai; +-------------+ | count(a_id) | +-------------+ | 4 | +-------------+ 1 row in set (0.00 sec) #Returns the maximum number of level fields in the small white table mysql> select max(a_level) from xiaobai; +--------------+ | max(a_level) | +--------------+ | 40 | +--------------+ 1 row in set (0.00 sec) #Returns the average value of level in the small white table mysql> select avg(a_level) from xiaobai; +--------------+ | avg(a_level) | +--------------+ | 25.0000 | +--------------+ 1 row in set (0.00 sec)
String function
String function describe length(x) Return string x Length of trim() Returns a value in the specified format concat(x,y) Parameters to be provided x and y Splice into a string upper(x) String x All letters of the become capital letters lower(x) String x All letters of the become lowercase left(x,y) Return string x Before y Characters right(x,y) Return string x After y Characters repeat(x,y) String x repeat y second space(x) return x Spaces replace(x,y,z) String z Substitution string x String in y strcmp(x,y) compare x and y，The returned value can be-1,0,1 substring(x,y,z) Get from string x Section in y The starting length of the first position is z String of reverse(x) String x reversal
#length(x) returns the length of the string X
mysql> select length('qwer');
+----------------+
| length('qwer') |
+----------------+
| 4 |
+----------------+
1 row in set (0.00 sec)
#Spaces also take up one character
mysql> select length('qwe r');
+-----------------+
| length('qwe r') |
+-----------------+
| 5 |
+-----------------+
1 row in set (0.00 sec)
#Remove empty characters
mysql> select trim(' hello');
+------------------+
| trim(' hello') |
+------------------+
| hello |
+------------------+
1 row in set (0.00 sec)
#Splice into a string
mysql> select concat('qwe','er');
+--------------------+
| concat('qwe','er') |
+--------------------+
| qweer |
+--------------------+
1 row in set (0.00 sec)
#Lowercase to uppercase
mysql> select upper('qwe');
+--------------+
| upper('qwe') |
+--------------+
| QWE |
+--------------+
1 row in set (0.00 sec)
#Uppercase to lowercase
mysql> select lower('AAA');
+--------------+
| lower('AAA') |
+--------------+
| aaa |
+--------------+
1 row in set (0.00 sec)
#Returns the first 2 characters of the string qwert
mysql> select left('qwert',2);
+-----------------+
| left('qwert',2) |
+-----------------+
| qw |
+-----------------+
1 row in set (0.00 sec)
#Returns the last 2 characters of the string qwert
mysql> select right('qwert',2);
+------------------+
| right('qwert',2) |
+------------------+
| rt |
+------------------+
1 row in set (0.00 sec)
. . . Operate the following by yourself
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) Return date x Month value in week(x) Return date x What week of the year is it hour(x) return x Hour value in minute(x) return x Minute value in second(x) return x Seconds in dayofweek(x) return x What day is it, 1 Sunday, 2 Monday dayofmonth(x) Calculation date x What day of the month is it dayofyear(x) Calculation date x What day of the year is it
#Displays the current date
mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2021-10-29 |
+------------+
1 row in set (0.00 sec)
#Displays the current time
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 18:56:26 |
+-----------+
1 row in set (0.00 sec)
#Displays the current full time
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2021-10-29 18:57:19 |
+---------------------+
1 row in set (0.00 sec)
#Displays the current month
mysql> select month('2021.10.29');
+---------------------+
| month('2021.10.29') |
+---------------------+
| 10 |
+---------------------+
1 row in set (0.00 sec)
#Displays the current week ordinal
mysql> select week('2021.10.29');
+--------------------+
| week('2021.10.29') |
+--------------------+
| 43 |
+--------------------+
1 row in set (0.01 sec)
. . . Self operation in the later stage
stored procedure
summary
The previous knowledge about MySQL is a single SQL statement for one table or several tables. Although using such SQL statements can meet the needs of users, in actual database applications, some database operations may be very complex and may require multiple SQL statements to be processed together. At this time, you can use stored procedures, It is easy and efficient to complete this requirement, which is a bit similar to the functions in the shell script
brief introduction
1. A stored procedure is a set of SQL statements to complete specific functions.
2. 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
Developer
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.
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
Syntax: CREATE PROCEDURE <Process name> ( [Process parameters[,...] ] ) <Process body> [Process parameters[,...] ] format <Process name>: Try to avoid duplicate names with built-in functions or fields <Process body>: sentence [ IN | OUT | INOUT ] <Parameter name><type>
##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)); -> insert into mk values (1, 'wang',13); -> 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 ##Call stored procedure## CALL Proc(); I The bodies of stored procedures are divided,It is called process body II with BEGIN Start with END End, if there is only one sQL Statement can be omitted BEGIN-END III with DELIMITER Start and end mysgl>DEL work M work TER $$ $$Is a user-defined terminator Omit other steps of the stored procedure mysql>DELIMITER ; Space before semicolon
mysql> use cat #Warehouse entry
Database changed
mysql> DELIMITER $$ #Change the closing symbol of the statement from semicolon; Temporarily changed to two $$(customizable)
mysql> 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));
-> insert into mk values (1, 'wang',13);
-> select * from mk; #Process style sentence
-> END $$ #The procedure body ends with the keyword END
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ; #Returns the closing symbol of the statement to a semicolon
mysql> call proc();
+------+------+-------+
| id | name | score |
+------+------+-------+
| 1 | wang | 13 |
+------+------+-------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
##Viewing stored procedures## Format: SHOW CREATE PROCEDURE [database.]Stored procedure name; #View specific information about a stored procedure
mysql> show create procedure proc\G
*************************** 1. row ***************************
Procedure: proc
sql_mode: PIPES_AS_CONCAT,ANSI_QUOTES,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER="root"@"localhost" PROCEDURE "proc"()
BEGIN
create table mk (id int (10), name char(10),score int (10));
insert into mk values (1, 'wang',13);
select * from mk;
END
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
#Viewing stored procedures
SHOW PROCEDURE STATUS
#View specified stored procedure information
mysql> mysql> SHOW PROCEDURE STATUS like '%proc%'\G
*************************** 1. row ***************************
Db: cat
Name: Proc
Type: PROCEDURE
Definer: root@localhost
Modified: 2021-10-29 19:32:52
Created: 2021-10-29 19:32:52
Security_type: DEFINER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
##Parameters of stored procedure##
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)(Outgoing value can only be variable)
INOUT Input / output parameters: both the value passed in by the caller to the procedure and the value passed out by the procedure to the caller (values can only be variables)
It means that the caller passes in a value to the procedure, and it means that the procedure passes out a value to the caller (it can only be a variable))
give an example:
mysql> delimiter @@
mysql> create procedure proc (in inname varchar(40)) #Line parameter
-> begin
-> select * from info where name=inname;
-> end @@
mysql> delimiter @@
mysql> call proc2('wangwu'); #Argument
+--------+-------+---------+
| name | score | address |
+--------+-------+---------+
| wangwu | 80.00 | beijing |
+--------+-------+---------+
1 row in set (0.00 sec)
#Modify stored procedure
ALTER PROCEDURE <Process name>[<features>... ]
ALTER PROCEDURE GetRole MODIFIES SQL DATA SQL SECURITY INVOKER;
MODIFIES sQLDATA:Indicates that the subroutine contains statements that write data
SECURITY:Safety level
invoker:When defined as INVOKER As long as the executor has execution permission, it can be executed successfully.
##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;