(ii) MySQL intermediate article

1. Views View

A view is a virtual table whose contents are defined by a query. A filter that defines a view can come from one or more tables or other views of the current or other database.

Advantages of the View:

① simplifies operations by defining frequently used data as views.

② Security, users can only query and modify the data they can see.

③ The logic of independence, shielding the real table of the structure of the impact.

Disadvantages of the View:

① poor performance.

② Modify the Limit .

A. Creating A view

CREATE view name as SQL statement

1 CREATE VIEW  as SELECT *  from WHERE ID<3;

B. Deleting a view

DROP VIEW View1

1 DROP VIEW Stu;

C. Modifying a view

ALTER view name as SQL statement

1 ALTER VIEW  as SELECT *  from CLIENT;

D. Working with views

SELECT * FROM v1

1 SELECT *  from Stu;

2. Stored Procedure procedure

There are two kinds of applications, one is Web-based, the other is desktop-based , and they interact with the database to complete the data access work. Suppose you now have an application that contains both of these, and now you want to modify one of the query SQL statements,

Then we may want to modify their corresponding query SQL statements at the same time, when our application is very large and complex when the problem arises this, is not easy to maintain. Also, placing SQL query statements on our web programs or desktops can easily be compromised by SQL injection.

And the storage routines just can help us solve these problems.

A. Creating A stored procedure

Non-parametric:

1 DELIMITER $$--Custom statement end symbol, because there are a lot of SQL statements to execute, so you have to customize to prevent errors23CREATE PROCEDURE pro1 () 4 BEGIN 5 SELECT * from users; 6 END $$ 7 /  * change back to default value; */
delimiter;
8 -- Execute stored procedure 9 call Pro1 ()

With reference to:

• In only for incoming parameters
• Out is used only for return values
• InOut can be passed in and can be used as a return value

1 --create a stored procedure2 DELIMITER $$3 CREATE PROCEDUREP1 (4     inchI1INT,--Incoming parameter I15     inchI2INT,--Incoming parameter I26INOUT i3INT,--that is, you can get the return value .7Out R1INT                        --Get return value8 )9 BEGINTen     DECLARETemp1INT; One     DECLARETemp2INT DEFAULT 0;  A     SETTemp1= 1; -     SETR1=I1+I2+Temp1+Temp2; -     SETi3=i3+  -; the END$$

1 --Executing stored procedures2 --DECLARE @t1 INT default 3; --Set Variable default value to 33 SET @t1=8;4 DECLARE @t2 INT;--Setting Variables5Call P1 (1,2,@t1,@t2);--executes the stored procedure and passes in the parameter, T2 automatically cancels6 SELECT @t1,@t2;--view stored procedure output results

The MySQL stored procedure assigns the found result set to a variable

DELIMITER $$
CREATE PROCEDURE p2 (out param INT)
BEGIN
DECLARE X INT;
SELECT COUNT (NAME) into X from table WHERE name= ' RJL ';
SET param = X;
end$$
DELIMITER;

B. Deleting a stored procedure

1 DROP PROCEDURE p1;

C.java language Call stored procedure

Detailed Description: http://www.cnblogs.com/57rongjielong/p/7765915.html

3. Function functions

A function can also pass parameters, or it can receive a return value, but the function cannot get the result of executing the statement, and the stored procedure can .

A. Built-in functions

B. Custom Functions

1 DELIMITER $$2 CREATE FUNCTIONF1 (3X1INT,4Y1INT)5 RETURNS INT6 BEGIN7     DECLARENumINT;8     SETNum=X1+Y1;9     RETURNnum;Ten END $$ One DELIMITER; A  - SELECTF1 ( -, -);

C. Delete function:

DROP FUNCTION F1;

4. Business
transactions are used to manipulate multiple SQL for some operations as atomic, and once an error occurs, it can be rolled back to its original state, guaranteeing database data integrity.

(ii) MySQL intermediate article