MySQL Learning note (10) stored procedures and functions + knowledge points (the difference between having and where)

Learning content:
stored programs and functions ... I am a foggy in this chapter.

1. Stored Procedures ...

The MySQL stored procedure is a new feature that has been added since MySQL 5.0. There are many advantages to stored procedures, but I think the most important advantage of a stored procedure is the encapsulation of SQL code, so why do we need to encapsulate the SQL statement? The reason is that when we are facing a huge database, when we use external programs to access the database ... We can't always embed many SQL statements in external programs ... That's not very efficient, and it's not easy to maintain ... So the stored procedure encapsulates our operation, and when we need to manipulate it, we just need to call the stored procedure ...

Create procedureCl_add (Aint, bint)begin                 //the procedure for executing a stored procedure needs to be defined at begin .... End statement ...DeclareCint;//Declare a variable:  Declare can only be used in stored procedures or functions, otherwise an error will occur. ifA is NULL  Then   //ifstatement, which is used to make conditional judgments. The statement that satisfies the condition is executed if the condition is met. The IF and if () are different here:if() is the control flow function: If the statement that represents the condition is judged ...     The two are not the same. SetA=0; End if; ifB is NULL  Then     SetB=0;//SetAssignment Statement ... Simple statements can be followed by complex functions ...End if; SetC=A+b; SelectC as sum; /*return C; This will produce an error ...*/EndNote: Stored procedures cannot be used Return...return can only be used in functions ... Calls to stored procedures ... call Cl_add (Ten, -);//the stored procedure needs to be called using the call function ...Set @a=Ten;Set @b= -;//We can also define two user variables ... call cl_add (@a,@b);//Pass the value of the user variable past ...

2. Storage function ...

A stored function is also made up of one or more SQL statements to encapsulate the code for reuse ... Also for the convenience of developers to manipulate the database ...

Restrictions used by storage functions: 1. Temporary tables cannot be used. 2. You cannot define the data type of the timestamp,cursor,table in the storage function ... 3. When defining a function, the parameter is only allowed in type ... 4. There are some storage functions built into the system, when calling these functions ... Need to add:: Prefix ... and enable the Allow updates server option to define the owner of a user-defined function as a system type:

// CREATE FUNCTION namebyt () RETURNS CHAR (+) RETURN (SELECTfromWHERE id=2); // DELIMITER;

Create a storage function with the name NAMEBYT, which returns the query result of the SELECT statement, with a numeric type of string type ...

Note: When RETURNS CHAR (50) data type, RETURNS has S, and return (SELECT NAME from T3 WHERE id=2) return is not s

3. The difference between stored functions and stored procedures ...

is essentially the implementation of the SQL code block encapsulation, convenient operation of the database ...

There are several differences between stored procedures and functions:

1) In general, the function of the stored procedure implementation is a little more complicated, and the function's realization function is relatively strong. Stored procedures, powerful, can perform a series of database operations, including modifying tables; user-defined functions cannot be used to perform a set of actions that modify the state of a global database. 2) for stored procedures, parameters, such as recordsets, can be returned, and functions can only return values or table objects. A function can only return one variable, and a stored procedure may return multiple. Stored procedure parameters can have in,out,inout three types, and functions can only have in class ~ ~ ~ The stored procedure declaration does not require a return type, and the function declaration needs to describe the return type, and the function body must contain a valid return statement. 3) Stored procedures, you can use the non-deterministic function, not allowed in the user-defined function body of the built-in non-deterministic function. 4) The stored procedure is typically executed as a separate part (EXECUTE statement execution), and the function can be invoked as part of a query statement (select Call), since the function can return a Table object, so it can be located in the query statement after the FROM keyword. Stored procedures are not available in SQL statements, and functions can be used. Summary:

User-defined functions are particularly handy when working with individual fields in the same data row. Although the use of stored procedures here can be used for query purposes, it is clear that the function is not easy to use. Also, the operation of individual fields in the same data row in a select query cannot be handled even with stored procedures. Because the stored procedure does not return a value (the only value that can be returned directly, although there is no return value, but can be output in a stored procedure to complete the return), it can only be called separately when used, and the function can be anywhere where the expression can now be placed.

4.Mysql can also use declare to define conditions and stored procedures to solve some problems ... The problem here is generally a mistake, and when we are dealing with some errors, we can customize a program to handle errors ....

CREATE TABLET8 (S1INT,PRIMARY KEY(S1)) DELIMITER//CREATE PROCEDUREHandlerdemo ()BEGINDECLARE CONTINUEHANDLER forSQLSTATE'23000' SET @X2=1;SET @X=1;INSERT  intoT8VALUES(1);SET @X=2;INSERT  intoT8VALUES(1);SET @X=3;END;//DELIMITER;/*call a stored procedure*/Call Handlerdemo ();/*View Call stored procedure results*/SELECT @X

Here we have inserted two times 1. This is not allowed in the primary key .... So we used declare CONTINUE HANDLER for SQLSTATE ' 23000 ' SET @X2 = 1; This statement to handle this error occurs ... If there is no such sentence. This piece of code is wrong ....

5. Knowledge points have been omitted, but also today occasionally found themselves not a knowledge point ...

The difference between having and where in a database query statement ...

A very small point of knowledge ... But more important ...

Generally in SQL, where is used in most cases, and rarely used having,where and having basically the same, having clause in the query process is slower than the aggregate statement, where clause in the query process fast with the query statement, So most of the cases are using the where ...

SELECT *  from' Welcome ' havingId>1LIMIT0, -SELECT *  from' Welcome 'WHEREId>1LIMIT0, -//The results of these two operations are the same ... In most cases you can use where when you try not to use having. Because where is faster than the aggregate statement ... Having is used to compensate for the lack of where in the grouping data judgment ... For example, the following code .... SELECT User,MAX(Salary) fromUsersGROUP  by User  having MAX(Salary)>Ten;SELECT User,MAX(Salary) fromUsersGROUP  by User WHERE MAX(Salary)>TenThe second statement will have an error, in the database where the following is not allowed to add a judgmental aggregate function: So if we use the aggregate statement when we are counting data ... We can only use having. If these relationships are not used, then of course the where is preferred ... 

Add a few more points:

1. The SQL standard requires having to refer to a column in a GROUP BY clause or to a column in a total function. However, MySQL supports the extension of the nature of this work and allows the having to involve columns in the select list and in external subqueries.

2. Having clause must precede order by after group by.

3. If the HAVING clause references a column with ambiguous meanings, a warning appears. In the following statement, the col2 meaning is ambiguous, because it is used both as an alias and as a column name using:mysql> SELECT COUNT (col1) as col2 from the T GROUP by col2 have col2 = 2;

The standard SQL work nature has precedence, so if a having column name is used both for group by and as an alias column in the Output column list, then precedence is given to the column in the Group By column.

4. The HAVING clause can refer to the total function, while the WHERE clause cannot be referenced. "This should be the biggest reason for developers to adopt a HAVING clause under certain circumstances"

5, do not have to be used for entries that should be used in the WHERE clause, from the 2 statements we begin with, this does not go wrong, but MySQL is not recommended. And there is no clear reason, but since it is required, we can follow it.

MySQL Learning note (10) stored procedures and functions + knowledge points (the difference between having and where)