06 view, trigger, thing, stored procedure, function
1. View
1.1. What is a view
''' View is to get a virtual table through query, and then save it for direct use next time In fact, views are also tables '''
1.2. Why view
''' If you want to frequently operate a virtual table (composed of spell tables), you can make it into a view for subsequent direct operation '''
1.3. Specific operation
# Fixed syntax create view Virtual table name as Query statement of virtual table sql sentence
be careful
''' 1 When creating a view, there will be a table structure on the hard disk without table data(The data is still from the previous table) 2 Views are generally only used to query the data inside. Do not modify the data that may affect the real table '''
How often are views used
''' Not high Because when you create many views, the table will be difficult to maintain ''' # conclusion View can be understood
2. Trigger
In the case of adding, deleting and modifying table data, the function of automatic trigger can help us monitor the log in real time
The trigger can automatically trigger adding before adding after deleting before deleting after modifying before modifying under six conditions
Basic grammatical structure
create trigger The name of the trigger before/after insert/update/delete on Table name for each row
begin sql sentence end
# We usually need to know the meaning of the trigger name
# For increase
create trigger tri_before_insert_t1 before insert on t1 for each row
begin sql sentence end
create trigger trig_before_insert_t1 after insert on t1 for each row
begin sql sentence end
ps:modify MySQL Default statement Terminator
delimiter $$ Will default;End No. changed to $$
delimiter ;Change back
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 insert into errlog(err_cmd,err_time)
values(NEW.cmd,NEW.sub_time);
end if;
end $$
delimiter ;
# insert data
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');
2.1. Using triggers
drop trigger tri_after_insert_cmd;
3. Things
3.1. What is a thing
Opening a thing can contain multiple sql statements. These sql statements either succeed at the same time or none of them want to succeed. It is called the atomicity of things
3.2. The function of things
It ensures the security of data operation
Examples of paying back money
wyz Transfer me to Alipay by bank card 1000
1 take egon The data of bank card account is reduced by 1000 yuan
2 take jason Alipay account data plus 1000 pieces
When you operate multiple pieces of data, some operations may fail
3.3. Four characteristics of transaction
''' ACID A: Atomicity A transaction is an indivisible unit. Many operations contained in a transaction either fail at the same time or fail at the same time C: uniformity Things must be the database from a consistent state to another consistent state. Consistency is closely related to atomicity I: Isolation The execution of a transaction cannot be disturbed by other things That is, the operations and data used within a transaction are isolated from other concurrent things, and the concurrent things do not interfere with each other D: persistence Also called "permanent" Once a transaction is committed and executed successfully, its modifications to the data should be permanent, and other subsequent operations or failures should not have any impact on it '''
3.4. How to use things
# Food related keywords
# 1 open things
start transaction;
# 2 rollback (return to the state before food execution)
rollback;
# 3. Confirm (the rollback cannot be performed after confirmation)
commit;
# Analog rotation
create table user(
id int primary key auto_increment,
name char(32),
balance int
);
insert into user(name,balance)
values
('wsb',1000),
('egon',1000),
('ysb',1000);
# 1. Start transaction
start transaction;
# 2 multiple sql statements
update user set balance=900 where name='wsb';
update user set balance=1010 where name='egon';
update user set balance=110 where name = 'ysb';
'''
summary
When you meet more than one sql If the statements are consistent and either succeed or fail at the same time, you should consider using transactions
'''
4. Stored procedure
Stored procedures are similar to custom functions in python
It contains some sql statements that can be executed. The stored procedure is stored in the MySQL server. You can directly trigger the execution of internal sql statements by calling the stored procedure
4.1. Basic use
create procedure The name of the stored procedure()begin sql code end# The name of the call stored procedure ();
4.2. Three development models
First kind
''' Application development: programmers write code MySQL: Write the stored procedure in advance for the application to call Benefits: the development efficiency is improved, and the execution efficiency is also improved Disadvantages: considering the problem of cross departmental communication, the scalability of subsequent stored procedures is poor '''
Second
''' Application: programmers write code, design to database operation, and write it themselves Advantages: high scalability Disadvantages: reduced development efficiency to write sql The statement is too cumbersome and needs to be considered later sql Optimization problem '''
Third
''' Application: only write program code, not write sql Statements are based on operations written by others MySQL of python The framework can call the operation directly ORM frame Advantages: development efficiency is higher than the above two cases Disadvantages: the expansibility of the statement is poor, and the problem of low efficiency may occur '''
The first one is basically not used. Generally, it is the third one. If there is an efficiency problem, move it again
Specific demonstration of stored procedure
delimiter $$
create procedure p1(
in m int, # Only in and out m can't go back out
in n int,
out res int # The parameter can be returned
)
begin
select tname from teacher where tid>m and tid<n;
set res=0; # Modify the res variable to identify that the current stored procedure code is actually executed
sql code
end $$
delimiter ;
# example
delimiter $$
create procedure p1(
in m int, # Only in and out m can't go back out
in n int,
out res int # The parameter can be returned
)
begin
select tname from teacher where tid>m and tid<n;
set res=0; # Modify the res variable to identify that the current stored procedure code is actually executed
end $$
delimiter ;
# For the formal parameter res, you cannot directly transfer data. Instead, you should transfer a variable name
# Define variables
set @res = 10;
explain # check whether the query and statement go through the index
5. Function
It is different from stored procedures. Stored procedures are user-defined functions, which are similar to built-in functions
1, Mathematical function
ROUND(x,y)
Return parameters x Rounded y Decimal value
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 GROUP BY Clause SELECT In query)
AVG(col)Returns the average value of the specified column
COUNT(col)Returns the value of the non column 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 for 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 multibyte 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 are ignored. (However, all of them will be ignored 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 does not contain a decimal point or a decimal 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 len Subsequence character of position.
LOWER(str)
Make lowercase
UPPER(str)
Capitalize
REVERSE(str)
Return string str ，The order is opposite to the character order.
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 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 a 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 The day of the week represented(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 for(1~12)
MONTHNAME(date) return date The name of the month, such as: SELECT MONTHNAME(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 The password encryption process uses different algorithms.
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, returns 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
7, Control flow function exercise
#7.1 preparation form
/*
Navicat MySQL Data Transfer
Source Server : localhost_3306
Source Server Version : 50720
Source Host : localhost:3306
Source Database : student
Target Server Type : MYSQL
Target Server Version : 50720
File Encoding : 65001
Date: 2018-01-02 12:05:30
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`c_id` int(11) NOT NULL,
`c_name` varchar(255) DEFAULT NULL,
`t_id` int(11) DEFAULT NULL,
PRIMARY KEY (`c_id`),
KEY `t_id` (`t_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('1', 'python', '1');
INSERT INTO `course` VALUES ('2', 'java', '2');
INSERT INTO `course` VALUES ('3', 'linux', '3');
INSERT INTO `course` VALUES ('4', 'web', '2');
-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`s_id` int(10) DEFAULT NULL,
`c_id` int(11) DEFAULT NULL,
`num` double DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('1', '1', '1', '79');
INSERT INTO `score` VALUES ('2', '1', '2', '78');
INSERT INTO `score` VALUES ('3', '1', '3', '35');
INSERT INTO `score` VALUES ('4', '2', '2', '32');
INSERT INTO `score` VALUES ('5', '3', '1', '66');
INSERT INTO `score` VALUES ('6', '4', '2', '77');
INSERT INTO `score` VALUES ('7', '4', '1', '68');
INSERT INTO `score` VALUES ('8', '5', '1', '66');
INSERT INTO `score` VALUES ('9', '2', '1', '69');
INSERT INTO `score` VALUES ('10', '4', '4', '75');
INSERT INTO `score` VALUES ('11', '5', '4', '66.7');
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`s_id` varchar(20) NOT NULL,
`s_name` varchar(255) DEFAULT NULL,
`s_age` int(10) DEFAULT NULL,
`s_sex` char(1) DEFAULT NULL,
PRIMARY KEY (`s_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', 'Luban', '12', 'male');
INSERT INTO `student` VALUES ('2', 'army officer's hat ornaments', '20', 'female');
INSERT INTO `student` VALUES ('3', 'Liu Bei', '35', 'male');
INSERT INTO `student` VALUES ('4', 'Guan Yu', '34', 'male');
INSERT INTO `student` VALUES ('5', 'Fei Zhang', '33', 'female');
-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`t_id` int(10) NOT NULL,
`t_name` varchar(50) DEFAULT NULL,
PRIMARY KEY (`t_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', 'king');
INSERT INTO `teacher` VALUES ('2', 'alex');
INSERT INTO `teacher` VALUES ('3', 'egon');
INSERT INTO `teacher` VALUES ('4', 'peiqi');
#7.2. Count the number of people in each section Display format: course ID, course name, [100-85], [85-70], [70-60], [< 60]
select score.c_id,
course.c_name,
sum(CASE WHEN num BETWEEN 85 and 100 THEN 1 ELSE 0 END) as '[100-85]',
sum(CASE WHEN num BETWEEN 70 and 85 THEN 1 ELSE 0 END) as '[85-70]',
sum(CASE WHEN num BETWEEN 60 and 70 THEN 1 ELSE 0 END) as '[70-60]',
sum(CASE WHEN num < 60 THEN 1 ELSE 0 END) as '[ <60]'
from score,course where score.c_id=course.c_id GROUP BY score.c_id;
Need to master the function: 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 values of the time field are grouped according to the formatted result, 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)
More functions: Chinese bash here OR The official slammed here
5.1 user defined functions
#!!! be careful!!!
#Do not write sql statements in the function (otherwise an error will be reported). The function is only a function, which is applied in sql
#If you want to be in begin end... To write sql in, use stored procedures
delimiter //
create function f1(
i1 int,
i2 int)
returns int
BEGIN
declare num int;
set num = i1 + i2;
return(num);
END //
delimiter ;
delimiter //
create function f5(
i int
)
returns int
begin
declare res int default 0;
if i = 10 then
set res=100;
elseif i = 20 then
set res=200;
elseif i = 30 then
set res=300;
else
set res=400;
end if;
return res;
end //
delimiter ;
5.2 deleting functions
drop function func_name;
5.3 execution function
# Get return value
select UPPER('egon') into @res;
SELECT @res;
# Use in query
select f1(11,nid) ,name from tb2;
6. Process control
6.1. Conditional statement
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 ;
6.2. Circular statement
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 ;
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 ;
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
7. Index
ps: the data is stored on the hard disk. It is inevitable to perform IO operations to query the data
Index: it is a data structure, similar to the directory of books. This means that when querying data in the future, you should first find the directory and find the data instead of turning the book page by page, so as to improve the query speed and reduce IO operations
Index is the footer "key" in MySQL, which is a data structure used by the storage engine to quickly find records
- primary key
- unique key
- index key
Note that the foreign key is not used to speed up the query and is not within the scope of our research. The first three keys have constraints in addition to increasing the query speed, while the last index key has no constraints and is only used to help you quickly query data
essence
Filter out the final results by constantly narrowing the desired data range, and turn random events (page by page)
Become a sequential event (find the directory and data first)
In other words, with the microcosm mechanism, we can always find data in a fixed way
There can be multiple indexes (multiple directories) in a table
Although indexing can help you speed up your query, it also has disadvantages
""" 1 When there is a large amount of data in the table, the speed of index creation will be very slow 2 After the index is created, the query performance of the table will be greatly improved, but the write performance will also be greatly reduced """ Don't create index randomly!!!
7.1. b + tree
''' Only leaf nodes store real data, and other nodes store virtual data, which is only used to guide the way The higher the input level, the more steps to go through to query data (it is necessary to query data at several levels of the tree) A disk storage is limited Why do you suggest that you id Field as index Less space, more data can be stored in a disk block Then the height of the tree is reduced, thereby reducing the number of queries '''
7.2. Clustered index (primary key)
''' A clustered index is a primary key Innodb There are only two files Put the primary key directly in the idb In the table MyIsam Three documents The index will be stored in a single file '''
7.3. Unique index
When querying data, you can't always use the primary key, or you may use other fields such as name and age. At this time, you can't use the clustered index. At this time, you can set the auxiliary index (i.e. b + index) for other fields according to the situation
''' The leaf node stores the primary key value corresponding to the data First get the primary key value of the data according to the auxiliary index After that, you still need to query the data in the clustered index of the primary key '''
7.4. Overlay index
The leaf node of the secondary index has obtained the required data
# Set secondary index for name select name from user where name='jason'; # Non overlay index select age from user where name ='jason';