1, View
View is a virtual table (non real). Its essence is to obtain a dynamic data set according to SQL statements and name it. When using, users only need to use the name to obtain the result set, which can be used as a table.
Using the view, we can pick out the temporary table in the query process and implement it with the view. In this way, when we want to operate the data of the temporary table in the future, we don't need to rewrite the complex sql. We can directly look it up in the view, but the view has obvious efficiency problems, and the view is stored in the database. If the sql used in our program depends too much on the view in the database, That is, strong coupling, which means extending sql is extremely inconvenient, so it is not recommended
Application example of temporary table:
#Two related tables mysql> select * from course; +-----+--------+------------+ | cid | cname | teacher_id | +-----+--------+------------+ | 1 | biology | 1 | | 2 | Physics | 2 | | 3 | Sports | 3 | | 4 | Fine Arts | 2 | +-----+--------+------------+ 4 rows in set (0.00 sec) mysql> select * from teacher; +-----+-----------------+ | tid | tname | +-----+-----------------+ | 1 | Mr. Zhang Lei | | 2 | Miss Li Ping | | 3 | Miss Liu Haiyan | | 4 | Miss Zhu Yunhai | | 5 | Miss Li Jie | +-----+-----------------+ 5 rows in set (0.00 sec) #Query the course name taught by Mr. Li Ping mysql> select cname from course where teacher_id = (select tid from teacher where tname='Miss Li Ping'); +--------+ | cname | +--------+ | Physics | | Fine Arts | +--------+ 2 rows in set (0.00 sec) #The sub query finds the temporary table as the teacher_id and other judgment basis select tid from teacher where tname='Miss Li Ping'
1 create view
Syntax:
CREATE VIEW View name AS SQL sentence
For example:
create view teacher_view as select tid from teacher where tname='Miss Li Ping';
So the sql for querying the course name taught by Mr. Li Ping can be rewritten as
mysql> select cname from course where teacher_id = (select tid from teacher_view); +--------+ | cname | +--------+ | Physics | | Fine Arts | +--------+ 2 rows in set (0.00 sec)
be careful:
-
After using the view, you don't need to rewrite the sql of the sub query every time, but it's not as efficient as writing the sub query
-
And there is a fatal problem: the view is stored in the database. If the sql in our program depends too much on the view stored in the database, it means that once the sql needs to be modified and involves the part of the view, it must be modified in the database. Generally, there is a special DBA responsible for the database in the company. If you want to complete the modification, The DBA may help you to complete the modification only after paying a lot of communication costs, which is extremely inconvenient
2 use view
When using a view, you can operate it as a table. Because the attempt is a virtual table, you can't use it to create, update and delete a real table. You can only query it
3 modify view
Syntax:
ALTER VIEW View name AS SQL sentence
For example:
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 | +-----+-------+------------+ 2 rows in set (0.00 sec)
4 delete view
Syntax:
DROP VIEW View name
For example:
DROP VIEW teacher_view
2, Trigger
If you want to trigger a specific behavior before and after adding / deleting / modifying a table, you can use triggers.
1 create trigger
Basic syntax: it refers to adding, deleting and modifying another table before \ after executing the SQL statement
- Before insertion
CREATE TRIGGER Trigger Name BEFORE INSERT ON Table name FOR EACH ROW
BEGIN
SQL sentence
END
- After insertion
CREATE TRIGGER Trigger Name AFTER INSERT ON Table name FOR EACH ROW
BEGIN
SQL sentence
END
- Before deletion
CREATE TRIGGER Trigger Name BEFORE DELETE ON Table name FOR EACH ROW
BEGIN
SQL sentence
END
- After deletion
CREATE TRIGGER Trigger Name AFTER DELETE ON Table name FOR EACH ROW
BEGIN
SQL sentence
END
- Before update
CREATE TRIGGER Trigger Name BEFORE UPDATE ON Table name FOR EACH ROW
BEGIN
SQL sentence
END
- After update
CREATE TRIGGER Trigger Name AFTER UPDATE ON Table name FOR EACH ROW
BEGIN
SQL sentence
END
NEW / OLD:
- NEW: indicates the data row to be inserted
- OLD: indicates the data row to be deleted.
Example: trigger after insertion
#Preparation table
CREATE TABLE cmd (
id INT PRIMARY KEY auto_increment,
USER CHAR (32),
priv CHAR (10),
cmd CHAR (64),
sub_time datetime, #Submission time
success enum ('yes', 'no') #0 means execution failed
);
CREATE TABLE errlog (
id INT PRIMARY KEY auto_increment,
err_cmd CHAR (64),
err_time datetime
);
#Create trigger
delimiter //
CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROW
BEGIN
IF NEW.success = 'no' THEN #Equivalence judgment has only one equal sign
INSERT INTO errlog(err_cmd, err_time) VALUES(NEW.cmd, NEW.sub_time) ; #Semicolon is required
END IF ; #Semicolon is required
END//
delimiter ;
#Insert a record into the cmd table, trigger the trigger, and decide whether to insert the error log according to the conditions of IF
INSERT INTO cmd (
USER,
priv,
cmd,
sub_time,
success
)
VALUES
('egon','0755','ls -l /etc',NOW(),'yes'),
('egon','0755','cat /etc/passwd',NOW(),'no'),
('egon','0755','useradd xxx',NOW(),'no'),
('egon','0755','ps aux',NOW(),'yes');
#Query the error log and find two
mysql> select * from errlog;
+----+-----------------+---------------------+
| id | err_cmd | err_time |
+----+-----------------+---------------------+
| 1 | cat /etc/passwd | 2017-09-14 22:18:48 |
| 2 | useradd xxx | 2017-09-14 22:18:48 |
+----+-----------------+---------------------+
2 rows in set (0.00 sec)
2 use trigger
The trigger cannot be called directly by the user, but is passively triggered by the [add / delete / modify] operation on the table.
3 delete trigger
drop trigger tri_after_insert_cmd;
3, Stored procedure
1 Introduction
A stored procedure is a collection of SQL statements. When you actively call a stored procedure, the internal SQL statements will execute logically.
Advantages of using stored procedures:
-
It is used to replace the SQL statement written by the program and realize the decoupling between the program and SQL
-
Based on network transmission, the amount of data transmitted by alias is small, while the amount of data transmitted directly by sql is large
Disadvantages of using stored procedures: it is inconvenient for programmers to expand functions
Supplement: three ways of combining program and database
Mode 1:
MySQL: stored procedure
Programs: calling stored procedures
Mode 2:
MySQL:
Program: pure SQL statement
Mode 3:
MySQL:
Programs: classes and objects, or ORM (essentially pure SQL statements)
Format:
delimiter // #Change the terminator to / / to prevent the presence of in the SQL statement; And terminate create procedure Stored procedure name() BEGIN SQL sentence END // delimiter; # Change the terminator / / back to; Convenient for subsequent operation
2 create a simple stored procedure (no parameters)
establish:
delimiter //
create procedure p1()
BEGIN
select * from blog;
INSERT into blog(name,sub_time) values("xxx",now());
END //
delimiter ;
Call in mysql
call p1()
Calling MySQL based on python in python
cursor.callproc('p1')
print(cursor.fetchall())
3 Create stored procedure (with reference)
For stored procedures, parameters can be received. There are three types of parameters:
- In is only used to pass in parameters
- out is only used for return values
- inout can be passed in or returned
In: pass in parameters
delimiter //
create procedure p2(
in n1 int,
in n2 int
)
BEGIN
select * from blog where id > n1;
END //
delimiter ;
#Call in mysql
call p2(3,2)
#Calling MySQL based on python in python
cursor.callproc('p2',(3,2))
print(cursor.fetchall())
out: return value
delimiter //
create procedure p3(
in n1 int,
out res int
)
BEGIN
select * from blog where id > n1;
set res = 1;
END //
delimiter ;
#Call in mysql
set @res=0; #0 represents false (execution failed), 1 represents true (execution succeeded)
call p3(3,@res);
select @res;
#Calling MySQL based on python in python
cursor.callproc('p3',(3,0)) #0 is equivalent to set @res=0
print(cursor.fetchall()) #Query results of query select
cursor.execute('select @_p3_0,@_p3_1;') #@p3_0 represents the first parameter, @ p3_1 represents the second parameter, the return value
print(cursor.fetchall())
inout: it can be passed in or returned
delimiter //
create procedure p4(
inout n1 int
)
BEGIN
select * from blog where id > n1;
set n1 = 1;
END //
delimiter ;
#Call in mysql
set @x=3;
call p4(@x);
select @x;
#Calling MySQL based on python in python
cursor.callproc('p4',(3,))
print(cursor.fetchall()) #Query results of query select
cursor.execute('select @_p4_0;')
print(cursor.fetchall())
4 executing stored procedures
4.1 executing stored procedures in MySQL
-
No parameters
call proc_name() -
With parameters, all in
call proc_name(1,2) -
There are parameters, in, out and inout
set @t1=0;
set @t2=3;
call proc_name(1,2,@t1,@t2)
4.2 executing stored procedures based on pymysql in python
All creation processes are in MySQL, and pymysql can only be executed
import pymysql
conn = 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)) #Stored procedure p1, args are the parameters passed in
# Get the stored parameters after execution
cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3") # Fixed format, corresponding to the incoming parameters respectively
result = cursor.fetchall() # Get result set
conn.commit()
cursor.close()
conn.close()
print(result)
# There are two steps to execute the stored procedure: the first step is to obtain the result set, and the second step is to pass the return value to the@_ Stored procedure_ Serial number
5 delete stored procedure
drop procedure Stored procedure name;
4, Function
1 built in function
1, Mathematical function
ROUND(x,y)
Return parameters x Rounded y Value to decimal places
RAND()
Returns a random value from 0 to 1,You can provide a parameter by(seed)send RAND()The random number generator generates a specified value.
2, Aggregate function (commonly used in SELECT query of GROUP BY clause)
AVG(col)Returns the average value of the specified column COUNT(col)Returns the value of non in the specified column NULL Number of values MIN(col)Returns the minimum value of the specified column MAX(col)Returns the maximum value of the specified column SUM(col)Returns the sum of all values of the specified column GROUP_CONCAT(col) Returns a result of a concatenation of column values belonging to a group
3, String function
CHAR_LENGTH(str)
The return value is a string str Length in characters. A multi byte character counts as a single character.
CONCAT(str1,str2,...)
String splicing
If any parameter is NULL ，The return value is NULL.
CONCAT_WS(separator,str1,str2,...)
String splicing (custom connector)
CONCAT_WS()No empty strings will be ignored. (However, it will ignore all NULL).
CONV(N,from_base,to_base)
Binary conversion
For example:
SELECT CONV('a',16,2); Indicates that it will a Converted from hexadecimal to binary string representation
FORMAT(X,D)
Will number X The format of is written as'#,###,###.##', keep the D digits after the decimal point by rounding, and return the result in the form of string. If D is 0, the returned result has no decimal point or fractional part.
For example:
SELECT FORMAT(12332.1,4); The result is: '12,332.1000'
INSERT(str,pos,len,newstr)
stay str Inserts a string at the specified location
pos: Position to replace
len: Length of replacement
newstr: New string
special:
If pos If the length exceeds the original string, the original string is returned
If len If it exceeds the length of the original string, it is completely replaced by the new string
INSTR(str,substr)
Return string str The first occurrence of the substring.
LEFT(str,len)
Return string str From the beginning len Subsequence character of position.
LOWER(str)
Make lowercase
UPPER(str)
Capitalize
REVERSE(str)
Return string str ，Reverse the order of characters.
SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)
Without len The format of the parameter is from string str Returns a substring starting at position pos. have len The format of the parameter is from string str Returns a value of the same length len Substring with the same character, starting at position pos. use FROM The format of is standard SQL Grammar. It may also be true pos Use a negative value. If so, the position of the substring starts at the end of the string pos Character, not the beginning of the string. In the function of the following format, you can pos Use a negative value.
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'
4, Date and time functions
CURDATE()or CURRENT_DATE() Returns the current date CURTIME()or CURRENT_TIME() Returns the current time DAYOFWEEK(date) return date What day of the week does it represent(1~7) DAYOFMONTH(date) return date What day of the month is it(1~31) DAYOFYEAR(date) return date What day of the year is it(1~366) DAYNAME(date) return date The name of the week, such as: SELECT DAYNAME(CURRENT_DATE); FROM_UNIXTIME(ts,fmt) According to the specified fmt Format, formatting UNIX time stamp ts HOUR(time) return time Hourly value of(0~23) MINUTE(time) return time Minute value of(0~59) MONTH(date) return date Month value of(1~12) MONTHNAME(date) return date The name of the month, such as: SELECTMONTHNAME(CURRENT_DATE); NOW() Returns the current date and time QUARTER(date) return date Quarter of the year(1~4)，as SELECT QUARTER(CURRENT_DATE); WEEK(date) Return date date Is the week of the year(0~53) YEAR(date) Return date date Year of(1000~9999)
a key:
DATE_FORMAT(date,format) according to format String formatting date value
mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
-> 'Sunday October 2009'
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 00 Thu 04 10 Oct 277'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
-> '%H %k %I %r %T %S %w');
-> '22 22 10 10:23:00 PM 22:23:00 00 6'
mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
-> '1998 52'
mysql> SELECT DATE_FORMAT('2006-06-00', '%d');
-> '00'
5, Encryption function
MD5()
Calculation string str of MD5 Checksum
PASSWORD(str)
Return string str The encrypted version of this encryption process is irreversible, and UNIX Different algorithms are used in the password encryption process.
6, Control flow function
CASE WHEN[test1] THEN [result1]...ELSE [default] END
If testN If true, return resultN，Otherwise return default
CASE [test] WHEN[val1] THEN [result]...ELSE [default]END
If test and valN Equal, return resultN，Otherwise return default
IF(test,t,f)
If test Yes, return t；Otherwise return f
IFNULL(arg1,arg2)
If arg1 Not empty, return arg1，Otherwise return arg2
NULLIF(arg1,arg2)
If arg1=arg2 return NULL；Otherwise return arg1
Emphasize date_format:
#1 basic use
mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
-> 'Sunday October 2009'
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 00 Thu 04 10 Oct 277'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
-> '%H %k %I %r %T %S %w');
-> '22 22 10 10:23:00 PM 22:23:00 00 6'
mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
-> '1998 52'
mysql> SELECT DATE_FORMAT('2006-06-00', '%d');
-> '00'
#2 preparation form and record
CREATE TABLE blog (
id INT PRIMARY KEY auto_increment,
NAME CHAR (32),
sub_time datetime
);
INSERT INTO blog (NAME, sub_time)
VALUES
('Part 1','2015-03-01 11:31:21'),
('Part 2','2015-03-11 16:31:21'),
('Part 3','2016-07-01 10:21:31'),
('Part 4','2016-07-22 09:23:21'),
('Part 5','2016-07-23 10:11:11'),
('Part 6','2016-07-25 11:21:31'),
('Chapter 7','2017-03-01 15:33:21'),
('Chapter 8','2017-03-01 17:32:21'),
('Chapter 9','2017-03-01 18:31:21');
#3. Extract sub_ The value of the time field is grouped according to the result after the format, i.e. "year and month"
SELECT DATE_FORMAT(sub_time,'%Y-%m'),COUNT(1) FROM blog GROUP BY DATE_FORMAT(sub_time,'%Y-%m');
#result
+-------------------------------+----------+
| DATE_FORMAT(sub_time,'%Y-%m') | COUNT(1) |
+-------------------------------+----------+
| 2015-03 | 2 |
| 2016-07 | 4 |
| 2017-03 | 3 |
+-------------------------------+----------+
3 rows in set (0.00 sec)
2 user defined functions
Note: it is only responsible for obtaining the return value of the incoming parameters, that is, it is not allowed to write sql statements internally to obtain the result set. The function is only a function, which is applied in sql. To write sql in begin... end... Use a stored procedure
delimiter //
create function f1(
i1 int,
i2 int)
returns int
BEGIN
declare num int;
set num = i1 + i2;
return(num);
END //
delimiter ;
3 delete function
drop function func_name;
4 execution function
- Get return value
select UPPER('egon') into @res;
SELECT @res;
- Use in query
select f1(11,nid) ,name from tb2;
5, Process control
1 if conditional statement
delimiter //
CREATE PROCEDURE proc_if ()
BEGIN
declare i int default 0;
if i = 1 THEN
SELECT 1;
ELSEIF i = 2 THEN
SELECT 2;
ELSE
SELECT 7;
END IF;
END //
delimiter ;
2 while loop
delimiter //
CREATE PROCEDURE proc_while ()
BEGIN
DECLARE num INT ;
SET num = 0 ;
WHILE num < 10 DO
SELECT
num ;
SET num = num + 1 ;
END WHILE ;
END //
delimiter ;
3 repeat cycle
delimiter //
CREATE PROCEDURE proc_repeat ()
BEGIN
DECLARE i INT ;
SET i = 0 ;
repeat
select i;
set i = i + 1;
until i >= 5
end repeat;
END //
delimiter ;
4 loop loop
BEGIN
declare i int default 0;
loop_label: loop
set i=i+1;
if i<8 then
iterate loop_label;
end if;
if i>=10 then
leave loop_label;
end if;
select i;
end loop loop_label;
END
5. Execute SQL statements dynamically (prevent SQL injection)
delimiter // drop procedure if exists proc_sql \\ create procedure proc_sql() BEGIN declare p1 int; set @p1 = 11; prepare prod from 'select * from tb1 where nid > ?'; # In the previous line of sql statement? Placeholder like% s in python execute prod using @p1; deallocute prepare prod; END \\ delimiter ;
6, Business
Transaction is used to treat multiple SQL of some operations as atomic operations. Once an error occurs, it can be rolled back to the original state, so as to ensure the integrity of the database.
Format syntax:
delimiter //
create procedure p4(
out status int
)
BEGIN
1. Declare that if an exception occurs, execute{
set status = 1;
rollback;
}
Start transaction
-- Deduct 100 from Qin Bing's account
-- Add 90 to Fang Shaowei's account
-- Zhang Gen account plus 10
commit;
end
set status = 2;
END //
delimiter ;
realization:
delimiter //
create PROCEDURE p5(
OUT p_return_code tinyint
)
BEGIN
DECLARE exit handler for sqlexception # Set up an sql error capture
BEGIN
-- ERROR
set p_return_code = 1;
rollback; # RollBACK
END;
DECLARE exit handler for sqlwarning # Set up an sql warning capture
BEGIN
-- WARNING
set p_return_code = 2;
rollback; # RollBACK
END;
START TRANSACTION; # Create transaction
DELETE from tb1; #Execution failed
insert into blog(name,sub_time) values('yyy',now());
COMMIT; # Commit transaction
# If there is an error or warning in the code execution within the transaction, execute the corresponding code block of the upper error or warning. If there is no error or warning, continue to execute the lower code
-- SUCCESS
set p_return_code = 0; #0 represents successful execution
END //
delimiter ;
Call in mysql:
set @res=123; call p5(@res); select @res;
In python, call MySQL based on pymysql:
cursor.callproc('p5',(123,))
print(cursor.fetchall()) #Query results of query select
cursor.execute('select @_p5_0;')
print(cursor.fetchall())