The transaction
Transaction: ONE or a group of SQL statements constitute a unit of execution that either executes completely or does not execute at all. Case: transfer Zhang sanfeng 1000 Guo Xiang 1000 update table set zhang sanfeng's balance = 500 where name = 'zhang sanfeng accident update table set Guo Xiang balance = 1500 where name =' Guo Xiang 'transaction features: ACID atomicity: a transaction cannot be split again, perform or not perform consistency: a transaction execution will make data switch from one consistent state to another consistent state isolation: the execution of a transaction is not affected by the interference of other transactions persistence: once a transaction is submitted, will be a permanent change database data. Delete from table where id =1; delete from table where id =1; Explicit transaction: the transaction has obvious start and end marks. Prerequisite: The automatic commit function must be disabled. Set Autocommit =0. Set autocommit=0; start transaction; Optional Step 2: Write the SQL statement (SELECT INSERT UPDATE delete) statement 1 in the transaction; Statements 2; . Step 3: End the transaction commit; Commit transaction ROLLBACK; Rollback transaction savepoint node name; Set the isolation level of savepoint transactions: Dirty Read Unrepeatable Read Mirage Read UNcommitted: √ √ √ Read COMMITTED: × √ √ REPEATable read: Repeatable read Read COMMITTED Check isolation level select @@TX_ISOLATION; repeatable read Check isolation level select @@TX_ISOLATION; Set the isolation level set session | global transaction isolation level isolation level. A statement to start a transaction; Select * from table_name where name=' select * from table_name where name=' select * from table_name where name=' select * from table_name where name=' select * from table_name where name='; */ SHOW VARIABLES LIKE 'autocommit'; SHOW ENGINES; SET autocommit=0; START TRANSACTION; UPDATE account SET balance = 1000 WHERE username=' zhang '; UPDATE account SET balance = 1000 WHERE username=' '; ROLLBACK; #commit; SELECT * FROM account; SET autocommit=0; START TRANSACTION; DELETE FROM account; ROLLBACK; SET autocommit=0; START TRANSACTION; DELETE FROM account WHERE id=25; SAVEPOINT a; DELETE FROM account WHERE id=28; ROLLBACK TO a; SELECT * FROM account; Delete SET autocommit = 0; START TRANSACTION; DELETE FROM account; ROLLBACK; SET autocommit = 0; START TRANSACTION; TRUNCATE TABLE account; ROLLBACK;Copy the codeview
Mysql5.1: virtual tables, like regular tables, use a new feature in mysql5.1. Mysql > create table SQL > create table SQL > create table SQL > create table SQL > create table SQL > create table SQL > create table SQL > create table SELECT stuname,majorname FROM stuinfo s INNER JOIN major m ON S. 'majorid' = m. 'id' WHERE S. 'stuname' LIKE 'a %'; CREATE VIEW v1 AS SELECT stuname,majorname FROM stuinfo s INNER JOIN major m ON s.`majorid`= m.`id`; SELECT * FROM stuname WHERE stuname = '%'; Create view name as query statement; */ USE myemployees; CREATE CREATE VIEW myv1 AS SELECT last_name,department_name,job_title FROM employees e JOIN departments d ON e.department_id = d.department_id JOIN jobs j ON j.job_id = e.job_id; SELECT * FROM myv1 WHERE last_name LIKE '%a%'; CREATE VIEW myv2 AS SELECT AVG(salary) AG,department_id FROM employees GROUP BY department_id; SELECT myv2. 'ag',g. 'highest_level' FROM myv2 JOIN job_grades G ON myv2. 'ag' BETWEEN g. SELECT * FROM myv2 ORDER BY ag LIMIT 1; CREATE VIEW myv3 AS SELECT * FROM myv2 ORDER BY ag LIMIT 1; SELECT d.*,m.ag FROM myv3 m JOIN departments d ON m.`department_id`=d.`department_id`; /* create or replace view name as query statement; */ SELECT * FROM myv3 CREATE OR REPLACE VIEW myv3 AS SELECT AVG(salary),job_id FROM employees GROUP BY job_id; Alter view view name as; */ ALTER VIEW myv3 AS SELECT * FROM employees; Drop view name, view name,... ; */ DROP VIEW emp_v1,emp_v2,myv3; # DESC myv3; SHOW CREATE VIEW myv3; CREATE OR REPLACE VIEW myv1 AS SELECT last_name,email,salary*12*(1+IFNULL(commission_pct,0)) "annual salary" FROM employees; CREATE OR REPLACE VIEW myv1 AS SELECT last_name,email FROM employees; SELECT * FROM myv1; SELECT * FROM employees; #1. INSERT INTO myv1 VALUES(' [email protected]'); UPDATE myv1 SET last_name=' last_name 'WHERE last_name=' last_fly '; DELETE FROM myv1 WHERE last_name = 'last_name '; SQL statements containing the following keywords are not allowed to update views: Grouping functions, distinct, Group by, HAVING, Union OR Union all CREATE OR REPLACE VIEW myv1 AS SELECT MAX(salary) M,department_id FROM employees GROUP BY department_id; SELECT * FROM myv1; UPDATE myv1 SET m=9000 WHERE department_id=10; CREATE OR REPLACE VIEW myv2 AS SELECT 'John' NAME; SELECT * FROM myv2; UPDATE myv2 SET NAME=' Lucy '; CREATE OR REPLACE VIEW myv3 AS Select department_id,(Select MAX(salary) FROM employees) maximum salary FROM departments; SELECT * FROM myv3; UPDATE myv3 SET maximum salary =100000; #④join CREATE OR REPLACE VIEW myV4 AS SELECT last_name,department_name FROM employees e join DEPARTMENTS D ON e.department_id = d.department_id; SELECT * FROM myv4; UPDATE myv4 SET last_name=' last_name 'WHERE last_name='Whalen'; INSERT INTO MYv4 VALUES(' c0 ',' c0 '); CREATE OR REPLACE VIEW myv5 AS SELECT * from myv3; SELECT * FROM myv5; UPDATE myv5 SET department_id=60 WHERE department_id=60; CREATE OR REPLACE VIEW myv6 AS SELECT last_name,email,salary from employees where employee_id IN( SELECT manager_id FROM employees WHERE manager_id IS NOT NULL ); SELECT * FROM myv6; UPDATE myv6 SET salary=10000 WHERE last_name = 'k_ing';Copy the codeCase view
CREATE OR REPLACE VIEW emp_v1 AS SELECT last_name,salary,email FROM employees SELECT last_name,salary,email FROM employees WHERE phone_number LIKE '011%'; Create emp_v2; CREATE OR REPLACE VIEW emp_v2 AS SELECT MAX(salary) mx_dep,department_id FROM employees GROUP BY department_id HAVING MAX(salary)>12000; SELECT d.*,m.mx_dep FROM departments d JOIN emp_v2 m ON m.department_id = d.`department_id`;Copy the codevariable
/* System variables: global variables session variables custom variables: user variables local variables */ Global variables you need to add the global keyword, session variables you need to add the session key, if you don't write, use the default session level steps: 1, to see all system variables show global | "session" the variables; 2, check the meet the conditions of a part of the system variables show global | "session" variables like '% char %'; 3, look at the specified system, the value of the variable select @ @ global | variable name "session" system; 4, as a system variable assignment method one: the set global | variable name = value "session" system; Method 2: set @ @ global | variable name = value "session" system; */ #1 "GLOBAL VARIABLES /* scope: valid for all sessions (connections), but not across restarts */ # #② SHOW GLOBAL VARIABLES LIKE '%char%'; SELECT @@global.autocommit; SET @@global. Autocommit =0; SET GLOBAL autocommit=0; #2 "SESSION VARIABLES /* scope: valid for current SESSION */ # # SHOW SESSION VARIABLES LIKE '%char%'; SELECT @@autocommit; SELECT @@session.tx_isolation; SET @@session. tx_ISOLATION ='read-uncommitted'; SET SESSION tx_isolation='read-committed'; */ #1 "user variable /* scope: valid for the current session (connection), the same as session variable */ # assignment operator: Declare and initialize the SET @ variable name = value; SET @ variable name := value; SELECT @variable name := value; SET @variable name = value; SET @ variable name := value; SELECT @variable name := value; SELECT * FROM table where name = 'SELECT * FROM table where name =' #③ Use (check the value of the variable) SELECT @name; #2 "local variable /* scope: only the first sentence of begin End is valid in the begin end block where it is defined */ # DECLARE variable name type [DEFAULT value]; SET local variable name = value; SET local variable name := value; SELECT local variable name := value; SELECT * FROM table where name = 'SELECT * FROM table where name =' SELECT local variable name; Example: Declare two variables, sum and print # user variable SET @m=1; SET @n=1; SET @sum=@m+@n; SELECT @sum; DECLARE m INT DEFAULT 1; DECLARE n INT DEFAULT 1; DECLARE SUM INT; SET SUM=m+n; SELECT SUM; # user variable vs. local variable Scope define location syntax User variable Current session add @ sign anywhere in the session, do not specify typeCopy the codeThe stored procedure
# stored procedures and functions /* Stored procedures and functions: similar to methods in Java benefits: 1, improve code reuse 2, simplify operations */ # stored procedures /* A collection of pre-compiled SQL statements, understanding batch processing statements (1) improves code reuse (2) simplifies operations (3) reduces compilation times and connections to the database server */ # CREATE PROCEDURE name BEGIN Body of a stored PROCEDURE END In stuname vARCHar (20) Parameter mode: in: This parameter can be used as input, which requires the caller to pass in a value. Out: This parameter can be used as output, which requires the caller to pass in a value. This parameter can be used as either input or output. That is, it requires both a value and a return value. 2. The end of the stored procedure can be reset with the delimiter syntax: DELIMiter End tag example: DELIMiter $*/ # # -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- cases present -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- # 1. SELECT * FROM admin; SELECT * FROM admin; DELIMITER $ CREATE PROCEDURE myp1() BEGIN INSERT INTO admin(username,`password`) VALUES('john1','0000'),('lily','0000'),('rose','0000'),('jack','0000'),('tom','0000'); END $# CALL myp1()$#2. Create a stored procedure with an in mode parameter Create a stored procedure implementation based on the goddess name, CREATE PROCEDURE myp2(IN beautyName VARCHAR(20)) BEGIN SELECT bo.* FROM boys bo RIGHT JOIN beauty b ON bo.id = b.boyfriend_id WHERE b.name=beautyName; END $# CALL myp2(' myp2 ')$# Create a stored procedure implementation, CREATE PROCEDURE myp4(IN username VARCHAR(20),IN PASSWORD VARCHAR(20)) BEGIN DECLARE result INT DEFAULT 0; SELECT COUNT(*) FROM admin WHERE admin. Username = username AND admin. Password = password; SELECT IF(result>0,' result ',' result '); CALL myp3(' zhang Fei ','8888')$#3 with END $# A stored procedure for creating out schema parameters # Case 1: Based on the goddess name entered, CREATE PROCEDURE myp6(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20)) BEGIN SELECT bo.boyname INTO boyName FROM boys bo RIGHT JOIN beauty b ON b.boyfriend_id = bo.id WHERE b.name=beautyName ; END $# Based on the goddess name entered, CREATE PROCEDURE myp7(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT userCP INT) BEGIN SELECT boys.boyname ,boys.usercp INTO boyname,usercp FROM boys RIGHT JOIN beauty b ON b.boyfriend_id = boys.id WHERE b.name=beautyName ; $SELECT @name,@cp$#4 from myp7('小 时 ',@name,@cp $#4) CREATE PROCEDURE myp8(inout a INT, inout b INT) BEGIN SET A =a*2; CREATE PROCEDURE myp8(inout a INT, inout b INT) SET b=b*2; SET @m=10$SET @n=20$CALL myp8(@m,@n)$SELECT @m,@n$# DROP PROCEDURE p2,p3; Select * from the stored procedure DESC myp2; × SHOW CREATE PROCEDURE myp2;Copy the codeCase stored procedure
Create a stored procedure and pass in the username and password. CREATE PROCEDURE test_PRO1 (IN username VARCHAR(20),IN loginPwd VARCHAR(20)) BEGIN INSERT INTO the admin table admin(admin.username,PASSWORD) VALUES(username,loginpwd); END $# CREATE PROCEDURE test_PRO2 (IN ID INT,OUT NAME VARCHAR(20),OUT phone VARCHAR(20)) BEGIN SELECT B. NAME ,b.phone INTO NAME,phone FROM beauty b WHERE b.id = id; END $# create a stored procedure or function to implement the passing of two goddess birthdays, Return size CREATE PROCEDURE test_PRO3 (IN birth1 DATETIME,IN Birth2 DATETIME,OUT result INT) BEGIN SELECT DATEDIFF(birth1,birth2) INTO result; END $# create a stored procedure or function and pass in a date. CREATE PROCEDURE test_PRO4 (IN mydate DATETIME,OUT strDate VARCHAR(50)) BEGIN SELECT DATE_FORMAT(mydate,'%y year %m month %d day ') INTO strDate; Test_pro4 (NOW(),@str)$SELECT @str $# END $CALL test_pro4(NOW(),@str)$# DROP PROCEDURE test_PRO5 $CREATE PROCEDURE test_PRO5 (IN beautyName VARCHAR(20),OUT STR VARCHAR(50)) BEGIN SELECT CONCAT(beautyName,' and ',IFNULL(boyName,'null')) INTO str FROM boys bo RIGHT JOIN beauty b ON b.boyfriend_id = bo.id WHERE b.name=beautyName; SET STR = END $CALL test_pro5(' test_pro5 ',@str)$SELECT @str $# DROP PROCEDURE test_PRO6 $CREATE PROCEDURE test_PRO6 (IN startIndex INT,IN size INT) BEGIN SELECT * FROM beauty LIMIT startIndex,size; END $CALL test_pro6 (3, 5) $Copy the codefunction
A set of pre-compiled SQL statements, which can be used to understand batch processing statements 1, improves code reuse 2, simplifies operations 3, reduces compile times and reduces the number of connections to the database server, improves efficiency Can have zero return, can also have multiple return, suitable for batch insert, batch update function: CREATE FUNCTION name (parameter list) return type BEGIN FUNCTION body END /* Note: 1. The parameter list consists of two parts: Parameter name Parameter Type 2. Function body: There must be a return statement. If there is no return statement, an error is reported. 3. There is only one sentence in the function body, 4. Use the delimiter statement to set the end tag */ # # -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- cases present -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- # 1. Return # Example: Return the number of employees CREATE FUNCTION myF1 () RETURNS INT BEGIN DECLARE C INT DEFAULT 0; SELECT COUNT(*) INTO c# FROM employees; RETURN c; END $SELECT myf1()$#2 CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE BEGIN SET @sal=0; SELECT salary INTO @sal FROM employees WHERE last_name = empName; RETURN @sal; END $SELECT myf2('k_ing') $# CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE BEGIN DECLARE sal DOUBLE; SELECT AVG(salary) INTO sal FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE d.department_name=deptName; RETURN sal; END $SELECT myf3('IT')$# DROP FUNCTION myf3; Create a function that passes two floats, CREATE FUNCTION test_FUN1 (num1 FLOAT,num2 FLOAT) RETURNS FLOAT BEGIN DECLARE SUM FLOAT DEFAULT 0; SET SUM=num1+num2; RETURN SUM; END $SELECT test_fun1 (1, 2) $Copy the codeFlow control structure
Varchar (20) inserts a random string into this TABLE */ DROP TABLE IF EXISTS stringContent; CREATE TABLE stringcontent( id INT PRIMARY KEY AUTO_INCREMENT, content VARCHAR(20) ); DELIMITER $ CREATE PROCEDURE test_randstr_insert(IN insertCount INT) BEGIN DECLARE i INT DEFAULT 1; DECLARE str VARCHAR(26) DEFAULT 'abcdefghijklmnopqrstuvwxyz'; DECLARE startIndex INT; # DECLARE len INT; WHILE I <=insertcount DO SET startIndex=FLOOR(RAND()*26+1); SET len=FLOOR(RAND()*(20-startIndex+1)+1); INSERT INTO stringContent (content) VALUES(SUBSTR(STR,startIndex,len)); SET i=i+1; END WHILE; END $ CALL test_randstr_insert(10)$Copy the codeView, stored procedure, function differences:
Stored procedures and functions are essentially the same. The collection of SQL statements that operate on a database for repeatable execution differs in the way they are written and called.
-
The parameter list of the stored procedure can have input parameters, output parameters, and input and output parameters. The argument list of a function has only input arguments and a return.
-
Return value: The return value of the stored procedure. The return value of the function has only one value
A view is a virtual table generated by a query result and can be treated like a table. Stored procedures are similar to functions, but once written, stored procedures are compiled and stored in the database, and the code can execute action views or tables. Moreover, stored procedures are faster to execute than ordinary retrieval statements.