MySQL built-in features

Transaction

A transaction consists of one or more SQL statements, and if all statements execute successfully then the modification will take effect, such as a SQL statement will sell +1, the next +1, and if the second fails, then sales will undo the +1 operation of the first SQL statement. Modifications are added to the database only if all of the statements in the transaction are executed successfully.

Characteristics:

Transaction specific four characteristics, that is often said acid
1. atomicity (atomicity)
Atomicity means that all operations contained by a transaction are either all successful or fail back, so the operation of a transaction must be fully applied to the database if it succeeds, and cannot have any effect on the database if the operation fails.

2. Consistency (consistency)
Consistency means that a transaction must transform a database from one consistent state to another, meaning that a transaction must be in a consistent state before and after execution.

Take the transfer, assume that user A and User B both the sum of money together is 5000, then no matter how a and B transfer, transfer several times, after the end of the transaction two users of the sum should be 5000, this is the consistency of the transaction.

3. Isolation (Isolation)
Isolation is when multiple users concurrently access the database, such as when the same table operation, the database for each user-opened transactions, can not be disturbed by the operation of other transactions, multiple concurrent transactions to be isolated from each other.

To achieve this effect: for any two concurrent transactions T1 and T2, in the case of transaction T1, the T2 either ends before the T1 starts, or starts after the T1 ends, so that every transaction does not feel that another transaction is executing concurrently.

4. Persistence (Durability)
Persistence refers to the fact that once a transaction is committed, changes to the data in the database are permanent, even if the database system encounters a failure, and the commit transaction is not lost. The corresponding operation log is saved in MySQL, and the last update can be resumed through the log even if it encounters a failure.

For example, when we use the JDBC operation database, after committing the transaction method, prompt the user transaction operation completes, when our program execution completes until sees the prompt, can determine the transaction and the correct commit, even if this time the database has the problem, also must have our transaction complete execution, Doing so will cause us to see that the transaction is complete, but the database failed to perform a transaction because of a failure.

MYSQ support for transactional engines: INNODB/BDB

Open transaction: Start transaction

Sql....

Commit Commit

Rollback rollback

Note:

When a transaction commits or rollback, it ends.

Some statements cause an implicit commit of a transaction, such as Start transaction

Trigger

Use triggers to customize the behavior of the user in the "Add, delete, change" operation of the table, note: No query

Create a Trigger

In MySQL, the CREATE TRIGGER syntax is as follows:

CREATE TRIGGER trigger_nametrigger_timetrigger_event on Tbl_namefor each rowtrigger_stmt

Trigger_name: Identifies the trigger name, which is specified by the user;
Trigger_time: Identification trigger time, value is before or after;
Trigger_event: Identifies the triggering event, with a value of INSERT, UPDATE, or DELETE;
Tbl_name: Identifies the name of the table on which the trigger is established, that is, the table on which the trigger is established;
TRIGGER_STMT: A trigger program body, which can be an SQL statement, or multiple statements containing the BEGIN and END.

Thus, there are 6 types of triggers that can be created: before insert, before UPDATE, before DELETE, after INSERT, after UPDATE, and after DELETE.

Another limitation is that you cannot create 2 triggers of the same type on a table at the same time, so there are up to 6 triggers on a table.

Insert before create TRIGGER tri_before_insert_tb1 before insert on TB1 for each rowbegin    ... end# after inserting create TRIGGER tri_after_insert_tb1 after insert on TB1 for each rowbegin    ... end# Delete before create TRIGGER tri_before_delete_tb1 before delete on tb1 for each rowbegin    ... end# Delete Create TRIGGER tri_after_delete_tb1 after delete on tb1 for each rowbegin    ... end# Update before the Create TRIGGER tri_before_update_tb1 before update on TB1 for each rowbegin    ... end# Update after the Create TRIGGER tri_after_update_tb1 after update on TB1 for each rowbegin    ... END

The trigger cannot be called directly by the user, it is triggered when the user changes the table's additions and deletions.

Drop Trigger Tri_after_insert_cmd #删除触发器

View

A view is a virtual table (not a real one), which is essentially "getting a dynamic dataset from an SQL statement and naming it", which you can use as a table by simply using "name" to get a result set.

Using views we can take out the temporary table in the query process, and use the view to implement, so that later on to manipulate the temporary table data without rewriting the complex SQL, directly to the view to find, but the view has obvious efficiency problems, and the view is stored in the database, If the SQL used in our program relies too much on the views in the database, which is strongly coupled, it means that the extension of SQL is extremely inconvenient and therefore not recommended.

Create a View

#语法: CREATE view name as  SQL statement CREATE view Teacher_view as select Tid from teacher where tname= ' Mr. Li Ping '; #于是查询李平老师教授的课程名的sq L can be rewritten as mysql> select CNAME from course where teacher_id = (select Tid from Teacher_view); +--------+| CNAME  |+--------+| Physics   | | Art   |+--------+rows in Set (0.00 sec) #!!! Note Note: #1. There is no need to rewrite the SQL for subqueries every time you use a view, but it is not as efficient as writing a subquery. And there is a fatal problem: The view is stored in the database, if the SQL in our program is too dependent on the view stored in the database, it means that once the SQL needs to be modified and related to the part of the view, you must go to the database to modify, and usually in the company database has a dedicated DBA responsible for You have to pay a lot of communication costs if you want to complete the change. The DBA may be able to help you with the modification, which is extremely inconvenient.

#修改视图记录，原始表也跟着改

　　

Modify a View

Syntax: ALTER VIEW name as SQL statement mysql> ALTER view Teacher_view as SELECT * from course where cid>3; Query OK, 0 rows affected (0.04 sec) mysql> select * FROM teacher_view;+-----+-------+------------+| CID | CNAME | teacher_id |+-----+-------+------------+|   4 | XXX   |          2 | |   5 | YYY   |          2 |+-----+-------+------------+rows in Set (0.00 sec)

　　

Delete a view

Syntax: Drop view Name drop view Teacher_view

　　

Storage process

The stored procedure contains a series of executable SQL statements stored in MySQL, which can be executed by calling its name to execute a heap of SQL inside it.

Advantages of using Stored procedures:

#1. Used to replace the SQL statements written by the program, the implementation program and SQL decoupling # #. Based on the network transmission, the data volume of the alias is small, and the amount of direct SQL data is large.

Disadvantages of using Stored procedures:

#1. Programmer extension is not a convenient feature

　　

Supplement: Three Ways to use the program in conjunction with the database

#方式一:    mysql: Stored procedure    Program: Call stored Procedure # Way two:    MySQL:    program: Pure SQL Statement # Way three:    MySQL:    Program: Class and Object, i.e. ORM (essentially or pure SQL statement)

　　

 
创建简单的存储过程（无参）

Delimiter//create procedure P1 () BEGIN    select * from blog;    INSERT into blog (name,sub_time) VALUES ("XXX", now ()); END//delimiter; #在mysql中调用call p1 () #在python中基于pymysql调用cursor. Callproc (' P1 ') print (Cursor.fetchall ())

　　

Create a stored procedure (with parameters)

For stored procedures, you can receive parameters with three types of parameters: #in          only used for incoming parameters with #out        only for return values #inout     can be passed in as well as return values

　　

Delimiter//create procedure P2 (    in N1 int, in    n2 int) BEGIN    select * from blog where ID > n1; END//delimiter; #在mysql中调用call p2 (3,2) #在python中基于pymysql调用cursor. Callproc (' P2 ', (3,2)) print (Cursor.fetchall ()) in: Incoming parameters

　　

Delimiter//create procedure P3 (    in N1 int, out    res int.) BEGIN    SELECT * from blog where ID > n1;    Set res = 1; END//delimiter; #在mysql中调用set @res = 0; #0代表假 (Execution failed), 1 represents true (execution succeeded) call P3 (3, @res); select @res; #在python中基于pymysql调用cursor. Callproc (' P3 ', (3,0)) #0相当于set @res = 0print (Cursor.fetchall ()) #查询select的查询结果cursor. Execute (' select @_p3_0,@_p3_1; ') # @p3_0 represents the first argument, @p3_1 represents the second argument, That is, the return value print (Cursor.fetchall ()) Out: return value

　　

Delimiter//create procedure P4 (    inout N1 int) BEGIN    select * from blog where ID > n1;    Set N1 = 1; END//delimiter; #在mysql中调用set @x=3;call P4 (@x); select @x; #在python中基于pymysql调用cursor. Callproc (' P4 ', (3,)) print ( Cursor.fetchall ()) #查询select的查询结果cursor. Execute (' select @_p4_0; ') print (Cursor.fetchall ()) InOut: can be passed in and returned

　　

Executing stored procedures

--Parametric call Proc_name ()-parameter, full incall proc_name-parameters, In,out,inoutset @t1 =0;set @t2 =3;call proc_name (@t1, @t2) Execute stored procedure execute stored procedure in MySQL--parameterless call Proc_name ()--parameter, full incall proc_name--parameters, In,out,inoutset @t1 =0;set @t2 =3;call Proc_name (@t1, @t2) executing stored procedures executing stored procedures in MySQL

#!/usr/bin/env python#-*-coding:utf-8-*-import pymysqlconn = pymysql.connect (host= ' 127.0.0.1 ', port=3306, user= ' root ', passwd= ' 123 ', db= ' t1 ') cursor = Conn.cursor (cursor=pymysql.cursors.dictcursor) # Execute Stored procedure cursor.callproc (' P1 ', args= ( 1, 22, 3, 4) # Gets the stored parameter cursor.execute ("select @_p1_0,@_p1_1,@_p1_2,@_p1_3") result = Cursor.fetchall () conn.commit () Cursor.close () Conn.close () print (result) executes a stored procedure based on Pymysql in Python

　　

删除存储过程

drop procedure Proc_name;

　

　

Function

A number of built-in functions are available in MySQL, such as:

The mathematical function ROUND (x, y) returns the rounding of the parameter x with the value of the Y decimal place RAND () returns a random value from 0 to 1, which can be provided by providing a parameter (seed) to enable the RAND () random number generator to generate a specified value. The aggregate function (in a select query commonly used in a GROUP BY clause) AVG (COL) returns the mean Count (col) of the specified column returns the number of non-null values in the specified column min (col) returns the minimum value of the specified column max (COL) returns the maximum of the specified column The value SUM (COL) returns the sum of all the values of the specified column group_concat (col) Returns the result of a combination of column values belonging to a set of three, String function char_length (str) returns the length of the string str, the length of the The unit is a character.    A multibyte character counts as a single character.        CONCAT (STR1,STR2,...)    string concatenation if any one of the arguments is NULL, the return value is null.        Concat_ws (SEPARATOR,STR1,STR2,...) string concatenation (custom connector) Concat_ws () does not ignore any empty strings.    (All NULL is ignored, however). CONV (n,from_base,to_base) binary conversion For example: SELECT CONV (' A ', 16,2); Represents the conversion of a from 16 to a 2 binary string representation of format (X,D) that formats the number X as ' #,###,###.## ', retains the D-bit after the decimal point in a rounded manner, and returns the result as a string.        If D is 0, the result is returned without a decimal point, or with no fractional part. For example: SELECT FORMAT (12332.1,4);            The result is: ' 12,332.1000 ' Insert (STR,POS,LEN,NEWSTR) inserts the string at the specified position in str pos: to replace the position in the location Len: the length of the replacement NEWSTR: New String Special: If Pos exceeds the original string length, the original string is returned if Len exceeds the length of the original string, and the new string is completely replaced by INSTR (STR,SUBSTR) to return the first occurrence of the string str neutron string.    Left (Str,len) returns the substring character of the string Str from the beginning of the Len position.    LOWER (str) to lowercase UPPER (str) to uppercase REVERSE (str) returns the string str, in reverse order and character order. SUBSTRING (Str,pos), SUBSTRING (str from POS) SUBSTRING (Str,pos,len), SUBSTRING (str from POS for len) format without len parameter from The string str returns a substring starting at position pos. The format with the Len parameter returns a substring of the same length as the Len character from the string str, starting at position pos. Use the from format as standard SQL syntax. You may also use a negative value for the POS. If so, the position of the substring starts at the POS character at the end of the string, not at the beginning of the string.        You can use a negative value for the POS in the following format function.            mysql> SELECT SUBSTRING (' quadratically ', 5);            ' ratically ' mysql> SELECT SUBSTRING (' Foobarbar ' from 4);            ' Barbar ' mysql> SELECT SUBSTRING (' quadratically ', 5,6);            ' Ratica ' mysql> SELECT SUBSTRING (' Sakila ',-3);            ' ila ' mysql> SELECT SUBSTRING (' Sakila ',-5, 3);        ' Aki ' mysql> SELECT SUBSTRING (' Sakila ' FROM-4 for 2);    ' Ki ' four, date and time function curdate () or current_date () returns the current date Curtime () or Current_time () returns the current time DAYOFWEEK (date) return dat   The day of the week represented by E (1~7) dayofmonth (date) Returns date is the day of the one month (1~31) dayofyear (date) Returns date is the day of the Year (1~366) dayname (date)    Returns the day of the week name, such as: SELECT Dayname (current_date); From_unixtime (TS,FMT) Formats the UNIX timestamp TS HOUR (time) According to the specified FMT format to return the hour value (0~23) MINUTE (times) to return time minutes (0~59) MONT    H (date) returns the month value of date (1~12) MONTHNAME (date) returns the month name of date, such as: SELECT MONTHNAME (current_date);    Now () returns the current date and time QUARTER (date) Returns a date in the quarter of the year (1~4), such as Select QUARTER (current_date); WEEK (date) Returns the date of the year (0~53), year (date), date of the month (1000~9999) Focus: Date_format (Date,format) is formatted according to the format string        A DATE value of mysql> SELECT date_format (' 2009-10-04 22:23:00 ', '%W%M%Y ');        ' Sunday October ' mysql> SELECT date_format (' 2007-10-04 22:23:00 ', '%h:%i:%s '); ' 22:23:00 ' mysql> SELECT date_format (' 1900-10-04 22:23:00 ',                 '%d%y%a%d%m%b%j '); 4th Thu Oct 277 ' mysql> SELECT date_format (' 1997-10-04 22:23:00 ', '%        H%k%I%r%T%s%w ');        -10:23:00 PM 22:23:00 6 ' mysql> SELECT date_format (' 1999-01-01 ', '%x%V ');        ' 1998 ' Mysql> SELECT date_format (' 2006-06-00 ', '%d '); ' 00 ', cryptographic function MD5 () computes the string Str's MD5 checksum PASSWORD (str) returns the encrypted version of the string str, the encryption process is irreversible, and the UNIX password encryption process uses a different The algorithm. Six, control flow function case When[test1] then [RESULT1] ... else [default] END if TESTN is true, return RESULTN, otherwise return to default case [test] when[val1 and then [result] ... else [Default]end returns RESULTN if test and valn are equal, otherwise returns the default if (test,t,f) if test is true, returns T; otherwise returns F Ifnull (arg        1,ARG2) If Arg1 is not empty, return arg1, otherwise return arg2 Nullif (ARG1,ARG2) if arg1=arg2 returns null; otherwise return arg1

Custom functions

#！！！ Attention!!! #函数中不要写sql语句 (otherwise error), the function is just a function, is a function that is applied in SQL # to want to be in begin...end ... Write SQL, use the stored procedure

Delimiter//create function F1 (    i1 int,    i2 int) returns intbegin    declare num int;    Set num = I1 + i2;    return (NUM); END//delimiter;d elimiter//create function f5 (    i int) returns Intbegin    declare res int default 0;    If i = Ten then        set res=100;    ElseIf i = then        set res=200;    ElseIf i = then        set res=300;    else        set res=400;    End If;    return res;end//delimiter;

　　

Delete a function

Drop function Func_name;

　　

Execute function

# Gets the return value of select UPPER (' Egon ') into @res; Select @res; # Use Select F1 (11,nid) in the query, name from TB2;

　　

MySQL built-in features