MySQL view view/the use of stored procedures and functions

**************************************

MYSQL Use of views view : Create, modify, delete

***************************************

1. MYSQL Build View

Creating a view is similar to creating a table syntax, unlike creating a view that is created from a query statement. Once a view is created, it can be used like a table, but only for data queries, such as: You can use it in one query, in a stored procedure, in another view. The MySQL CREATE VIEW syntax is as follows:

CREATE VIEW View name as SELECT query statement;

2. MYSQL View modification

Views that have been created, and sometimes need to modify their query fields or query criteria, theMySQL view modification syntax is as follows:

ALTER VIEW View name as SELECT query statement;

3. MYSQL View Delete

MySQL view deletes the syntax with the drop table type, syntax as follows:

DROP VIEW The name of the view;

3. MYSQL View uses

After creating the view, you can use the view directly from the view name, with the following syntax:

SELECT * from View name

***************************************

CREATE VIEW Statement Introduction

***************************************

To create a new view in MySQL, use the CREATE VIEW statement. The syntax for creating a view in MySQL is as follows:

Let's look at the above syntax in more detail.

View processing algorithms

The algorithm properties allow you to control the mechanism that MySQL uses when creating views. MySQL offers three algorithms: MERGE, TEMP TABLE, and undefined.

• using merge algorithm, mysql first input query with defined view The select statement is combined into a single query. mysql execute the combined query to return the result set. If the select statement contains aggregate functions, such as: min, Max, SUM, Count, avg or distinct, GROUP by, Having, Limit, Union, UNION all, subquery merge algorithm. If the select statement does not have a reference table, the merge algorithm is also not allowed. If the merge algorithm is not allowed, mysql the algorithm to undefined. note that the combination of a query in the input query and the view definition is called a view resolution.
• Using the TEMP table algorithm,MySQL first creates a temporary table based on the SELECT statement that defines the view , and then executes the input query against the temporary table. Because MySQL must create a temporary table to store the result set and move the data from the base table to the staging table,the TEMP table algorithm is less efficient than the merge algorithm. Additionally, views that use the TEMP table algorithm are not updatable .
  
• UNDEFINED is the default algorithm that does not specify an explicit algorithm when creating a view. The UNDEFINED algorithm lets MySQL make the choice of using the merge or temp TABLE algorithm. MySQL First chooses the merge algorithm to the temp TABLE algorithm because the merge algorithm is more efficient.
  

View Name

In a database, views and tables share the same namespace, so views and tables cannot have the same name. In addition, the name of the view must follow the table naming rules.

SELECT Statement

In a SELECT statement, you can query data from any table or view that exists in the database. The SELECT statement must follow several rules:

• The SELECT statement can include subqueries in the where clause , but not in the FROM clause.
• The SELECT statement cannot reference any variables , including local variables, user variables, and session variables.
• The SELECT statement cannot reference the parameters of a preprocessed statement .

Note that theSELECT statement does not need to reference any tables.

**********************************************

syntax for creating stored procedures and functions in Mysql

**********************************************

The syntax for stored procedures and functions in MySQL is very close so put together, the main difference is that the function must have a return value, and the parameters of the function only in the type and stored procedure has in , out,inout of the three types.

Example:

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

#创建数据库

DROP DATABASE IF EXISTS dpro;

CREATE DATABASE Dpro

CHARACTER SET UTF8

;

Use Dpro;

#创建部门表

DROP TABLE IF EXISTS Employee;

CREATE TABLE Employee

(ID INT not NULL PRIMARY key COMMENT ' primary key ',

Name VARCHAR() not NULL COMMENT ' name ',

Depid INT not NULL COMMENT ' Department ID '

);

#插入测试数据

INSERT into Employee(id, name, Depid) VALUES(1, ' Chen ', '), (2, ' King', 101), (3, ' Zhang ', 101), (4, ' li ', 102), (5, ' Guo ',103);

#创建存储过程

#改变默认的分隔符 (;) is "$$" as the identity of the end of the stored procedure and then restores the default value.

DELIMITER $$

DROP PROCEDURE IF EXISTS pro_employee;

CREATE PROCEDURE pro_employee(in Pdepid VARCHAR(a), out Pcount INT )

READS SQL DATA

SQL SECURITY INVOKER

BEGIN

SELECT COUNT(id) into Pcount from Employee WHERE depid=pdepid;

# more than one SQL statement

...

END$$

# $$ represents the end of the stored procedure, and then restores the default delimiter as (;)

DELIMITER ;

#执行存储过程, precede the variable name with @,

Call Pro_employee(101, @pcount);

# variable queries also need to be added @

SELECT @pcount;

# list all stored procedures

SHOW PROCEDURE STATUS;

# View information for a stored procedure that already exists

SHOW CREATE PROCEDURE <sp_name>;

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

DELIMITER $$

CREATE PROCEDURE Stored Procedure name ()

(   

[in|out|inout ] parameter datatype

)   

Begin

MySQL statement;    

end$$

DELIMITER ;

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Notice ??

• MySQL stored procedure Parameters If you do not explicitly specify "in", "out", and"inout", the default is "in". In practice, We do not explicitly specify parameters that are "in".
• The "()" after the name of the MySQL stored procedure is required, even if there is no parameter, "()"
• MySQL stored procedure parameter, cannot add "@" before parameter name,
• MySQL Client User variable to add an "@"
• Structure control statements, such as IF, while , case , etc., can be used to complete complex operations . In addition, define stored procedures to usp_ Prefixes are best practices for differentiating system stored procedures from user-defined stored procedures . SET assigns or re-assigns values to declared variables,SELECT displays variables; SELECT var into out_var writes the value of the variable to the out parameter.
  BEGIN
  DECLARE MyVar INT;

SET myvar = (SELECT ID from users LIMIT 1);

SELECT myvar into Num;
END $$

• The parameters of a MySQL stored procedure cannot specify a default value
• If the MySQL stored procedure contains more than one MySQL statement, the BEGIN END keyword is required.
• comments in the MySQL stored procedure. /* ... * * multi-line Comment
• Conditional statement: declare c int;  If A is null and then set c = 0; End If;
• when you call the MySQL stored procedure, you need to add "()" After the procedure name, even if there is no parameter, "()"
• Because the MySQL stored procedure parameter does not have a default value, you cannot omit the parameter when you call the MySQL stored procedure. Can be substituted with null.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Syntax explanation :

When you create a stored procedure , you will typically use

DELIMITER $$ ... BEGIN ... end$$ DELIMITER ; put in the beginning and end, the purpose is to avoid MySQL put inside the stored procedure ";" interpreted as an end symbol, and finally passed "DELIMITER ;" To inform the end of the stored procedure.

There are three types of parameters for stored procedures :

• In: Input parameters . Specified when a stored procedure is called, which is the type when the type is not specified by default .
• Out: Output parameter . Can be changed in the stored procedure and can be returned .
• INOUT: input and output parameters. In and out combination

characteristic section:

LANGUAGE SQL: Used to illustrate that the statement part is an SQL statement that may support other types of statements in the future.

[not] deterministic: If the program or thread always produces the same result for the same input parameter, it is considered "deterministic", otherwise it is "indeterminate". If neither a given deterministic nor a given not deterministic, the default is not deterministic (indeterminate)CONTAINS SQL: Represents a statement in which a subroutine does not contain read or write data.

No SQL: Indicates that the subroutine does not contain SQL statements .

READS SQLdata: A statement that represents a subroutine that contains read data, but does not contain a statement that writes data.

modifies SQL DATA : Indicates that the subroutine contains statements that write data.

SQL SECURITY definer: Indicates that the program executing the stored procedure is executed by the permissions of the user who created the stored procedure.

SQL SECURITY INVOKER: Indicates that the program executing the stored procedure is executed by the permission of the user who called the stored procedure. (for example, the above stored procedure I wrote is executed by the user who called the stored procedure, the current stored procedure is used to query the employee table, if the user I am currently executing the stored procedure does not have permission to query the employee table, then it will return an insufficient permission error, if replaced by definer If the stored procedure was created by the root user then any user logged in to the calling stored procedure can be executed because the permission to execute the stored procedure becomes root)

COMMENT ' string ': remarks, same as Field notes for table creation.

Note: It is recommended to explicitly specify the state of the above characteristic section when writing stored procedures and functions, especially in environments where replication exists, if the creation function does not explicitly specify these states to be an error, Migrating a database with functions from a non-replicated environment to a machine on a replication environment if no deterministic is explicitly specified , No SQL, or READS SQL data will be given an error in the three states.

**********************************************

Transaction block in Mysql

**********************************************

Http://gaunthan.leanote.com/post/MySQL-%E4%BA%8B%E5%8A%A1%E4%B8%8E%E4%BA%8B%E5%8A%A1%E5%A4%84%E7%90%86

Https://github.com/MrLining/mysql/wiki/Mysql%E4%BA%8B%E5%8A%A1%E5%A4%84%E7%90%86#mysql%E4%BA%8B%E5%8A%A1%E5%A4%84%E7%90%86

MySQL view view/the use of stored procedures and functions