MySQL Learning notes--connections and stored procedures

Connection

When we need to query the data from more than one table, we need to use the connection operation, MySQL supports the internal connection, the left connection and the right connection three kinds of connection methods.

Database Preparation

First we create two tables T1, T2:

CREATE TABLE T1 (i1 int, C1 varchar), CREATE TABLE t2 (i2 int, C2 varchar (10));

Insert data:

INSERT into T1 values (1, ' a '), (2, ' B '), (3, ' C '), insert into T2 values (2, ' C '), (3, ' B '), (4, ' a ');

Finally, you can view our database tables as follows:

Internal connection

If multiple data tables are listed in the FROM clause of the SELECT statement and separated by their names with the inner join, MySQL performs an inner JOIN operation, which results from matching the data rows in one table with the data rows in the other table. For example, the following query will combine each data row in the T1 with each data row in the T2:

Without any conditions of the inner join will produce a Cartesian product, the result row number is the product of two rows of data table, for example, two tables have 1000 data, then the result row number will have 1 million data, so we in the use of the connection is generally added conditions, the following query will only select t1.i1= T2.i2 of the line, greatly reducing the inspection trip:

External connection

The inner connection only shows the matching data rows in the two data tables, and the outer joins show the same matching results, and the data rows that are not matched in the other data table can also be displayed, and MySQL supports external connections (left join) and right outer joins JOIN).

The LEFT join works like this: two data tables, with left table as the benchmark, when a row of data from the table is matched to a data row in the right table, the contents of the two tables are used as an output row, and if the right table has no matching rows, the output rows are also generated, and the contents of the right table are filled with null.

The right join is similar to the left join, except that it is based on the table on the starboard side, and if it does not match, fill it with null.

We use the LEFT join instead of the inner join for the example above, and we get the following output:

Sometimes, when we are writing the left connection, we are really interested in the rows that are not matched in the left data table, and we can use the is null to determine the conditions to select the rows:

Right join is very similar to the left join, and all right joins can be changed to a back join here is not an example, and as far as my work experience is, it is better to write a left join uniformly in my work.

Stored Procedures

A stored procedure is simply a collection of one or more MySQL statements saved for later use, which can be treated as a batch file, although their role is not limited to batch processing.

1. Database Preparation

First create a database with the following command and insert the data in bulk:

CREATE table g (num int,value  varchar), insert into G values (1, ' 1 '), (10, ' 10 '), (60, ' 60 '), (100, ' 100 ');

View the table data as follows:

　　

2. Call the stored procedure

Before you create a stored procedure, say how to call a stored procedure, and the command to invoke the stored procedure is as follows:

Call stored procedure name ();

2.1. Creating encapsulated SQL Stored Procedures

One of the functions of a stored procedure is to encapsulate SQL, and the first stored procedure we create is to encapsulate a SELECT statement:

DELIMITER//create PROCEDURE p1 () Beginselect * from G; End//delimiter;

The function of "DELIMITER//" is to replace the line terminator with//, and the default line terminator is ";". Why would you want to replace the line terminator? Because creating a stored procedure is a stand-alone statement, the inside of the creation statement typically contains a ";" End of the statement, if we do not change the Terminator, then the statement resolves to ";" It will end early.

Take the above statement as an example, if we do not change the line terminator, then create the statement to "select * from G;" will end (because a line terminator is encountered ";" , which is obviously not what we want, but after we change the Terminator, the Create statement resolves to "end//" and successfully creates the stored procedure.

Finally, don't forget to call "DELIMITER;" Change the line terminator back.

Once created, we call the stored procedure and the result is as follows:

2.2. Create a stored procedure that contains parameters

Stored procedures can also contain parameters, below we create a stored procedure that contains parameters

DELIMITER//create PROCEDURE P2 (in n int, out avg double, INOUT min int) beginselect avg (num) from G where num > N into Avg;select min (num) from G where num > n into min; End//delimiter;

Note that the In,out,inout keyword is preceded by the parameter, in represents the passed-in value, out represents the outgoing value, and inout indicates that it can be passed in or outgoing.

Finally, let's call the stored procedure we created as follows:

2.3. A stored procedure containing a control structure

Stored procedures can also contain if-else structures, as in the following example:

DELIMITER//create PROCEDURE p3 (in n int, in switch char (1)) beginif switch= ' A ' then        select * from g where num > n;< C1/>else     select * from g where num > N;end if;     End//delimiter;

The result of calling the stored procedure is as follows:

2.4. Stored Procedures with loops

In addition to containing the IF-ELSE structure, stored procedures can also contain a while loop structure, for example:

DELIMITER//create PROCEDURE P4 (in n int) begindeclare I int;declare s int;set i = 1;set s = 0;while i <= n do    set S = s + i;    Set i = i + 1;end while;select s; End//delimiter;

The stored procedure calculates the summation from 1 to N, and the example of calling the stored procedure is as follows:

3. View existing stored procedures

If you want to know what stored procedures the database has, we can use the following command to view:

Show procedure Status

4. Delete stored Procedures

If you want to delete a stored procedure, we can use the following command:

The name of the drop procedure stored procedure

5. View the creation statement for the stored procedure

Sometimes we want to look at the creation of a stored procedure and then we can use the following command:

The name of the show create procedure stored procedure

6. Stored procedures and stored functions　

In addition to stored procedures and stored functions in MySQL, there are two differences between stored procedures and stored functions:

1. Create statements are different, stored procedures are created using the command "Create PROCEDURE", and the storage functions are created using the command "Create FUNCTION";

2, the stored procedure does not return a value, and the function has a return value.

MySQL Learning notes--connections and stored procedures