1 view (simplified query operation)
1-1 overview
Features of view:
- The view is the result set returned after the execution of the SELECT statement. It is the encapsulation of the SELECT statement.
- The view is a virtual table (the table obtained by query), and the rows and columns in the table come from the real table.
Advantages of view over normal table
- Simple: users of views only need to pay attention to the view, without paying attention to the structure, association conditions and filtering conditions of the real table behind the view.
- Security: the view can be used to restrict the viewing permission of access users (including developers). The permission restriction of the view on the table can be specific to a row and a column.
- Data independence: there is independence between the view and the table of data source, which is reflected in:
- The data source table can add or delete records freely
- The structure of the data source table changes only by updating the SQL statement encapsulated by the view definition.
Points for attention in view usage
Views are usually used to simplify query operations. You can update the table of the view data source by updating the view, but this is generally not recommended.
1-2 creation and modification of view
Preparation table
create database demo_01 default charset=utf8mb4; use demo_01; CREATE TABLE `city` ( `city_id` int(11) NOT NULL AUTO_INCREMENT, `city_name` varchar(50) NOT NULL, `country_id` int(11) NOT NULL, PRIMARY KEY (`city_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `country` ( `country_id` int(11) NOT NULL AUTO_INCREMENT, `country_name` varchar(100) NOT NULL, PRIMARY KEY (`country_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into `city` (`city_id`, `city_name`, `country_id`) values(1,'Xi'an',1); insert into `city` (`city_id`, `city_name`, `country_id`) values(2,'NewYork',2); insert into `city` (`city_id`, `city_name`, `country_id`) values(3,'Beijing',1); insert into `city` (`city_id`, `city_name`, `country_id`) values(4,'Shanghai',1); insert into `country` (`country_id`, `country_name`) values(1,'China'); insert into `country` (`country_id`, `country_name`) values(2,'America'); insert into `country` (`country_id`, `country_name`) values(3,'Japan'); insert into `country` (`country_id`, `country_name`) values(4,'UK');
- Note the country in the city table_ ID is the foreign key (the primary key of the external table)
Multi table query: query city information and its associated country information.
SELECT c.*,t.country_name from city c, country t WHERE c.country_id = t.country_id;
View creation
Objective: encapsulate the above SELECT statement into a view.
CREATE view view_city_country as SELECT c.*,t.country_name from city c, country t WHERE c.country_id = t.country_id;
Summary: create a virtual table view in the figure above_ city_ Country, encapsulating the query statements defined between.
Command format: create view < view name > as < select statement >
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]
- [with [cached | local] check option] meaning
Restrictions on update conditions when updating views LOCAL: It can be updated as long as the conditions of this view are met. CASCADED: All conditions for all views of this view must be met before they can be updated. Default value.
View changes
Command format: alter view < view name > as < select statement >
ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]
1-3 view and deletion
see
Starting from MySQL version 5.1, when using the SHOW TABLES command, not only the name of the table but also the name of the view will be displayed without saving
The SHOW VIEWS command displays views separately.
show tables; SHOW CREATE VIEW View name \G; # View the specific definition of the view
Delete (DROP VIEW view name)
DROP VIEW [IF EXISTS] view_name [, view_name] ...[RESTRICT | CASCADE]
2 stored procedures and stored functions
2-1 overview
Definition of stored procedure and stored function:
- A collection of SQL statements previously compiled and stored in a database
Advantages of stored procedures and functions:
- Simplify a lot of work for application developers
- Reduce data transmission between database and application server and improve the efficiency of data processing
Distinction between stored procedure and stored function:
- Functions can be understood as stored procedures with return values
2-2 create, call and query stored procedures
establish
CREATE PROCEDURE procedure_name ([proc_parameter[,...]]) begin -- SQL sentence end ;
example
delimiter $ create procedure pro_test1() begin select 'hello mysql' ; end$ delimiter ;
Note: due to the delimiter in MYSQL+ enter means to let the database execute commands. In order to execute multiple SQL statements, you need to temporarily transfer the delimiter from the database; Replace with other characters.
call
call procedure_name() ;
View created storage
-- query db_name All stored procedures in the database select name from mysql.proc where db='db_name'; -- Query the status information of stored procedure show procedure status; -- Query the definition of a stored procedure in the current database show create procedure Database name.Stored procedure name \G;
Note: in fact, the created stored procedures are also stored in the database in the form of tables, and stored in the proc table in MySQL database in MySQL.
Note: the above command can query all stored procedures created in the database.
Note: as long as you know the name of the stored procedure and its database, you can view the SQL statement set corresponding to the stored procedure through the show command.
A faster way: directly view MySQL through the GUI interface Proc table, you can see all the above information.
delete
DROP PROCEDURE [IF EXISTS] Stored procedure name;
2-2 SQL statement syntax corresponding to stored procedure
2-2-1 assignment of variables (2 ways)
Variable definition: local variables can be declared by DECLARE. The scope of the variable can only be in the BEGIN... END block.
- Manually defined constant (SET)
DELIMITER $ CREATE PROCEDURE pro_test3() BEGIN DECLARE NAME VARCHAR(20); SET NAME = 'MYSQL'; SELECT NAME ; END$ DELIMITER
- Get variable value from data table (select... Into)
DELIMITER $ CREATE PROCEDURE pro_test5() BEGIN declare countnum int; select count(*) into countnum from city; select countnum; END$ DELIMITER ;
The COUNT(*) function returns the number of records in the table
Summary: the statistical results in the data table can be written into variables through into
2-2-2 use of if statement
demand
According to the defined height variable, determine the body type of the current height 180 And above ----------> Tall and tall 170 - 180 ---------> Standard figure 170 following ----------> General figure
Stored procedure creation
delimiter $ create procedure pro_test6() begin declare height int default 175; declare description varchar(50); if height >= 180 then set description = 'good body'; elseif height >= 170 and height < 180 then set description = 'standard body'; else set description = 'common body'; end if; select description ; end$ delimiter ;
Summary: the default height is 175, which is judged as standard body according to the if statement
2-2-3 transfer of stored procedure parameters
Template
create procedure procedure_name([in/out/inout] Parameter name parameter type) ... IN : This parameter can be used as input, that is, the value needs to be passed in by the caller , default OUT: This parameter is used as output, that is, it can be used as return value INOUT: It can be used as input parameter or output parameter
demand
Write a stored procedure, pass the height as a parameter, and return the determination result through the parameter
code
delimiter $ create procedure pro_test7(IN height INT,OUT description VARCHAR(100)) begin if height >= 180 then set description = 'good body'; elseif height >= 170 and height < 180 then set description = 'standard body'; else set description = 'common body'; end if; end$ delimiter ;
View returned variables
- @description: the "@" symbol is added before the variable name, which is called the user session variable, which represents the whole session process. It is useful
, this is similar to a global variable. - @@global.sort_buffer_size: a variable preceded by "@ @" is called a system variable
call pro_test7(168, @description); select @description;
2-2-4 use of case statement
Template
Mode 1 : CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN statement_list] ... [ELSE statement_list] END CASE; Mode II : CASE WHEN search_condition THEN statement_list [WHEN search_condition THEN statement_list] ... [ELSE statement_list] END CASE;
example:
delimiter $ create procedure pro_test9(month int) begin declare result varchar(20); case when month >= 1 and month <=3 then set result = 'first quarter '; when month >= 4 and month <=6 then set result = 'Second quarter'; when month >= 7 and month <=9 then set result = 'Third quarter'; when month >= 10 and month <=12 then set result = 'Fourth quarter'; end case; select concat('The month you entered is :', month , ' , The month is : ' , result) as content; end$ delimiter ;
Execution result:
2-2-5 use of while statement
Template
while search_condition do statement_list end while;
Example (calculate the value of 1 to n)
delimiter $ create procedure pro_test8(n int) begin declare total int default 0; declare num int default 1; while num<=n do set total = total + num; set num = num + 1; end while; select total; end$ delimiter ;
results of enforcement
2-2-6 use of repeat
Template
REPEAT statement_list UNTIL search_condition END REPEAT;
Example: calculate 1 to n
delimiter $ create procedure pro_test10(n int) begin declare total int default 0; repeat set total = total + n; set n = n - 1; until n=0 end repeat; select total ; end$ delimiter ;
2-2-7 use of loop and LEAVE
- LOOP can realize a simple dead LOOP. If you want to exit the LOOP, you need to use it with leave
[begin_label:] LOOP statement_list END LOOP [end_label]
example:
delimiter $ CREATE PROCEDURE pro_test11(n int) BEGIN declare total int default 0; ins: LOOP IF n <= 0 then leave ins; END IF; set total = total + n; set n = n - 1; END LOOP ins; select total; END$ delimiter ;
Note: the ins above is the start and result mark of the loop.
2-2-8 use of cursor / cursor
definition:
- The data type used to store the query result set. The cursor can be used to cycle the result set in stored procedures and functions
- Extract one record at a time
The syntax of cursor declaration, OPEN, FETCH and CLOSE is as follows:
DECLARE cursor_name CURSOR FOR select_statement ; OPEN cursor_name ; FETCH cursor_name INTO var_name [, var_name] ... CLOSE cursor_name ;
Examples of cursor usage:
Data table creation
create table emp( id int(11) not null auto_increment , name varchar(50) not null comment 'full name', age int(11) comment 'Age', salary int(11) comment 'salary', primary key(`id`) )engine=innodb default charset=utf8 ; insert into emp(id,name,age,salary) values(null,'Golden King ',55,3800),(null,'White browed Eagle king',60,4000),(null,'Green winged bat King',38,2800),(null,'Purple Dragon King',42,1800);
Requirement 1: query the data in the emp table and obtain it row by row for display
delimiter $ create procedure pro_test15() begin declare e_id int(11); declare e_name varchar(50); declare e_age int(11); declare e_salary int(11); declare emp_result cursor for select * from emp; open emp_result; fetch emp_result into e_id,e_name,e_age,e_salary; select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', Salary is: ',e_salary); fetch emp_result into e_id,e_name,e_age,e_salary; select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', Salary is: ',e_salary); fetch emp_result into e_id,e_name,e_age,e_salary; select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', Salary is: ',e_salary); fetch emp_result into e_id,e_name,e_age,e_salary; select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', Salary is: ',e_salary); fetch emp_result into e_id,e_name,e_age,e_salary; select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', Salary is: ',e_salary); close emp_result; end$ delimiter ;
Execution result:
Summary:
declare emp_result cursor for select * from emp;
- Encapsulate the results of the SQL statement select query into the defined cursor
open emp_result; close emp_result;
- The use of cursors requires open and close
fetch emp_result into e_id,e_name,e_age,e_salary;
- fetch is used to extract a record of the result encapsulated in the cursor and put it into the defined variable
- In the above example, if the number of fetch es > the number of records in the data table, the error prompt No data will occur
Requirement 2: use the circular structure to view all records in the table through the cursor.
- The following example judges whether the record has been read through the handle
- You can also determine the number of records in the table through count()
DELIMITER $ create procedure pro_test15() begin DECLARE id int(11); DECLARE name varchar(50); DECLARE age int(11); DECLARE salary int(11); DECLARE has_data int default 1; DECLARE emp_result CURSOR FOR select * from emp; DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0; open emp_result; repeat fetch emp_result into id , name , age , salary; select concat('id by',id, ', name by' ,name , ', age by ' ,age , ', Salary is: ', salary); until has_data = 0 end repeat; close emp_result; end$ DELIMITER ;
Execution result:
MYSQL determines whether there are records in the data table by providing a handle mechanism.
DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0; DECLARE EXIT HANDLER FOR NOT FOUND It defines the handle that cannot find the data. When the data cannot be found, the following statements will be triggered set has_data = 0 Execution.
Note: the declaration of the handle must follow the declaration of the cursor, otherwise an error will be reported.
DECLARE emp_result CURSOR FOR select * from emp; DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0;
2-2-9 storage function
Stored functions are somewhat similar to stored procedures. Their syntax differences are as follows:
- PROCEDURE and FUNCTION, and the storage FUNCTION needs RETURN
- A stored function can be regarded as a "stored procedure with return value". In fact, a stored procedure can also return data through the OUT keyword in the parameter list.
- In fact, what can be done by storage functions can also be realized by using stored procedures.
CREATE FUNCTION function_name([param type ... ]) RETURNS type BEGIN ... END;
Example: define a function to query the number of records in a specific country
delimiter $ create function count_city(countryId int) returns int begin declare cnum int ; select count(*) into cnum from city where country_id = countryId; return cnum; end$ delimiter ;
Execution result:
In the figure above, the storage function is called through the select statement to count the number of records with the specified id.
3 trigger
3-1 overview
Definition: the collection of SQL statements triggered and executed before or after insert/update/delete
Application scenario:
- Assist the application in ensuring data integrity, logging, data verification and other operations on the database side
Classification:
- NEW (NEW record) and old (modified old record) are two line record variables
- Store records that have changed in the trigger
Note: MYSQL database only supports row level triggers, not statement level triggers. Oracle data is supported.
3-2 creation of trigger
Example: record the data change log of the table through the trigger, including adding, modifying and deleting;
Template
create trigger trigger_name before/after insert/update/delete # Trigger Type on tbl_name # Data sheet name for each row # row-level trigger begin trigger_stmt ; # SQL statement collection end;
Step 1: create a log table to save operation records
create table emp_logs( id int(11) not null auto_increment, operation varchar(20) not null comment 'Operation type, insert/update/delete', operate_time datetime not null comment 'Operation time', operate_id int(11) not null comment 'Operation table ID', operate_params varchar(500) comment 'Operating parameters', primary key(`id`) )engine=innodb default charset=utf8;
Step 2: create an INSERT trigger to record the insertion operation of the data table
DELIMITER $ create trigger emp_logs_insert_trigger after insert on emp for each row begin insert into emp_logs (id,operation,operate_time,operate_id,operate_params) values(null,'insert',now(),new.id,concat('After insertion(id:',new.id,', name:',new.name,', age:',new.age,', salary:',new.salary,')')); end $ DELIMITER ;
step3: create an update trigger to record the insertion operation of the data table
DELIMITER $ create trigger emp_logs_update_trigger after update on emp for each row begin insert into emp_logs (id,operation,operate_time,operate_id,operate_params) values(null,'update',now(),new.id,concat('Before modification(id:',old.id,', name:',old.name,', age:',old.age,', salary:',old.salary,') , After modification(id',new.id, 'name:',new.name,', age:',new.age,', salary:',new.salary,')')); end $ DELIMITER ;
step4: create a trigger for deleting rows and record the log of deleting data
DELIMITER $ create trigger emp_logs_delete_trigger after delete on emp for each row begin insert into emp_logs (id,operation,operate_time,operate_id,operate_params) values(null,'delete',now(),old.id,concat('Before deletion(id:',old.id,', name:',old.name,', age:',old.age,', salary:',old.salary,')')); end $ DELIMITER ;
Summary:
- The creation of trigger must specify the data table and data operation
- In the SQL statement executed by the trigger, the previous records can be obtained through new or old.
3-3 deleting and viewing triggers
drop trigger [schema_name.]trigger_name # shema_name is the name of the database. If it is not specified, it is the current database show triggers; # View triggers
- In the figure above, you can see the three triggers created before and the SQL statements executed after triggering.
reference material
03 concept and function of database cursor
20210228