Database Advanced Objects

1. Functions

A function can pass a parameter, or it can have a return value. But a function can have only one return value
Function can contain 0-multiple parameters

Declare functions with no parameters (the following are examples of MySQL databases)
CREATE function name () RETURNS return value type
return returns the result;
If there are multiple lines of statements in the function body, you need to use the Begin end Package

CREATE FUNCTIONF1 ()RETURNS VARCHAR(255)BEGINDELETE   fromTb1WHEREId=1;RETURN"Hahahahah";ENDSELECTF1 ();--calling FunctionsDROP FUNCTIONF1;--destroying functions

--create a function with parametersCREATE FUNCTIONF2 (usernameVARCHAR(255), ageINT, Sex enum ("Male", "female"))RETURNS VARCHAR(255)BEGININSERT  intoTbVALUES(NULL, username,age,sex);RETURN"Hahahahah";ENDSELECTF2 ("real name", -, "male");

2. Stored Procedures

Stored Procedures
is a pre-compiled set of pre-stored SQL statements and control statements. Use a name to store a set of code to operate as a complete unit
Advantages:

① enhanced SQL statement functionality and flexibility;

② execute faster the SQL statements in the stored procedure are pre-compiled at the time of the declaration process and will only be compiled at the time of the declaration without recompiling the subsequent execution

③ Reduce network request/traffic background Simply submit a stored procedure name to the database without committing a large segment of SQL statements
Creating a non-parametric stored procedure

CREATE PROCEDURE P1 () SELECT *  from TB; -- To Delete a stored procedure DROP PROCEDURE P1; -- The stored procedure is called using call, and if there are no arguments, the parentheses can save Call P1;

Parameters and return values for stored procedures
In the stored procedure, the parameters are divided into three cases
1. In: Similar to a parameter in a function, it needs to be passed in when the stored procedure is called
2. Out: return value in similar function, the stored procedure return value does not return but assigns the result to the parameter of the Out type
3. INOUT: When a stored procedure is called, parameters can be passed in while allowing the stored procedure to modify its value while it is executing and return

DROP PROCEDUREP2;CREATE PROCEDUREP2 (inchIusernameVARCHAR(255),inchIageint,inchIsex enum ("Male", "female"), out LastIDINT, out TotalINT)BEGININSERT  intoTbVALUES(NULL, iusername,iage,isex);SELECTLAST_INSERT_ID () intoLastID;--use into to assign the last ID of the query to LastIDSELECT COUNT(*) fromTb intoTotal ;ENDSET @age= A; Call P2 ("Xiaowu",@age, "Male",@lastId,@total);--A parameter that calls a stored procedure out type must pass in a variable to receive--an in type parameter can make a variable also be literalSELECT @lastId;--after the call is complete, the returned results are stored in the @lastid variable, and the direct select output can be

3. Business

Four properties of a transaction (acid property)
1, Atomic atomicity
A transaction is the smallest unit that is no longer split. All operations in the transaction are either executed or not executed;
Eg: transfer function, a reduction of 1000 yuan, B must be increased by 1000 yuan. Instead of a reduction, B does not increase the situation;
2, consistency consistency
The data for the transaction before and after the start must be consistent.
Eg: transfer function, the total amount of AB must be the same before and after the transfer. The ephemeral differences can only occur when the transaction is not over.
3, the isolation of isolation
When a transaction starts executing, other transactions are quarantined to read and write to and modify the data.
Eg: transfer function, A to B transfer the process, if C also to B transfer, will cause A to B transfer of this transaction,
The total amount at the end is inconsistent with the beginning, that is, the consistency of things cannot be satisfied.
4. Persistent durability
After the transaction execution is complete, the data modification is permanent.

4. Cursors

Cursors are used to process multiple rows of records that are returned from the database, which can traverse the data row by line.

DROP PROCEDUREYoubiao;CREATE PROCEDUREYoubiao (out paramsVARCHAR(255))BEGIN    DECLARECsCURSOR  for SELECTUsername fromTb--declaring a cursor for a result set    OPENCs--Open Cursor    FETCHCs intoParams--Take out the cursor and put it in the variable    INSERT  intoTb2VALUES(NULL, params); CLOSECs--Close CursorsEND; Call Youbiao (@params);SELECT @params;

5. View

Features of the View
is a virtual table in a database
Results from query operations
Structure with normal table
Storage of data is not possible. Data still exists in the actual data sheet
Changes to the view will affect the actual data table, but you must ensure that the view of the original data table is not visible and cannot be non-empty when you add data to it

 create  or  replace  view  username as  select  username,sex from   TB;  select  *  from   username;  delete  from  username where  username=  " Wuli ";  insert  username values   (" Wuli "," male ");  drop  view  username; 

6. Index

Index features: Indexing can greatly improve query speed, but will affect the speed of reducing insertions and modifications
columns suitable for indexing: fields that require frequent queries, fields that are frequently ordered, fields that often appear in the WHERE clause;
columns that are not suitable for indexing: fields that seldom use queries, fields with small amounts of data, and modify insert operations that are much larger than query operations
How to avoid restricting indexing "(Operations that use indexes)
Avoid using the inequality operator (<>,! =)
Avoid using is null or was NOT NULL
Avoid using functions in the WHERE clause
Avoid using mismatched data types when comparing.

Note: MySQL uses the index only for the following operators: <,<=,=,>,>=,between,in, and sometimes like (cases that do not start with a wildcard character% or _)

CREATE INDEXIndex1 onTB (username);--Create an index--Modify table structure How to create an indexALTER TABLETbADD INDEXindex1 (username);CREATE TABLETT (--indexing when creating tablesIdINT PRIMARY KEY, usernameVARCHAR(255),    INDEXindex1 (username));CREATE UNIQUE INDEXIndex1 onTB (username);--A unique index is basically the same as a normal index, except that the value must be unique and cannot be duplicated, can be empty, and can have only one null value--Full-Text indexing--can only be created on string fields such as Char\varchar \text for indexing large amounts of content, but generating full-text indexes consumes time and disk spaceALTER TABLETbADDFulltext INDEX1 (username);--Delete IndexDROP INDEXIndex1 onTb

Database Advanced Objects