1, View

1. Concept

View is to wrap a more complex query statement in the view to simplify the statement. Views are stored in the database and can be reused. Data is not stored in the view. The data of the view exists in the base table of the view and changes based on the change of the table data.

2. Why use views?

In order to facilitate complex query statements. The basic idea is to define complex query statements inside the view and query in the view.

3. View statement

(1) Define view:

• CREATE VIEW view name AS SELECT column 1, column 2... FROM table (query statement);
  eg:

 CREATE VIEW sel_employee AS
SELECT
id,NAME,age
FROM employee

(2) Use view

• SELECT * FROM view name
  eg:

SELECT * FROM sel_employee

(3) Delete view

• drop view view name
  eg:

DROP VIEW sel_employee

2, Stored procedure

1. General

Cognition of database: it can store data in table as a unit, and use sql language to operate data. The database can also have logic processing function like java language. It is written in advance and stored in the database. It can be called directly when used.

• Advantages: the process of processing a certain logic is directly stored in the data and runs faster. Improve the efficiency of data processing.
• Disadvantages: high dependence on database and poor portability.

2. Definition of MySQL stored procedure

(1) Syntax format for creating stored procedures

create procedure Stored procedure name([in Variable name type, out Parameter 2])
begin
[declare Variable name type [DEFAULT value];]
Stored procedure statement block;
end;

1. The parameters of stored procedure are divided into in, out and inout.
2.in represents the input parameter (in parameter by default), indicating that the value of this parameter must be specified by the caller.
3.out represents the output parameter, which means that after the value of the parameter is calculated by the stored procedure, the calculation result of the out parameter is returned to the calling program.
4.inout refers to both input and output parameters, indicating that the value of the parameter can be specified by the calling program, and the calculation result of inout parameter can be returned to the calling program.
5. The statements in the stored procedure must be contained between begin and end.
6.declare is used to declare variables, default is used for variable assignment by default, and set variable = value is used to change variable value in statement block;

• You can also create a stored procedure directly in the database (more recommended): right click the stored procedure and select Create stored procedure
  
• Enter a name and click create

• As shown in the figure, you can see the stored procedure syntax
  

3. Stored procedure usage

(1) Define the first stored procedure

-- Start position
DELIMITER $$
CREATE PROCEDURE demo()
  -- Stored procedure body
	BEGIN
		DECLARE v_name VARCHAR(10) DEFAULT '';
		SET v_name='jim'; -- Variable assignment
		SELECT v_name; -- Test statement
	END$$
DELIMITER ;
-- Call stored procedure
CALL demo();

(2) Define a stored procedure with parameters

DELIMITER $$
CREATE PROCEDURE dept_count(IN d_id INT,OUT d_count INT)
  -- Stored procedure body
	BEGIN
		SELECT COUNT(*) INTO d_count FROM dept WHERE id =d_id;
		SELECT d_count;
	END$$
DELIMITER ;
-- Call another store in a stored procedure
CALL dept_count(2,@d_count);

(3) Process control statement if else

DELIMITER $$
CREATE PROCEDURE demo2(IN p_day INT,OUT p_name VARCHAR(10))
	BEGIN
		IF p_day =1 THEN
		SET p_name ="Monday";
		SELECT p_name;
		ELSEIF  p_day =2 THEN
		SET p_name ="Tuesday";
		SELECT p_name;
		ELSE
		SET p_name ="day";
		SELECT p_name;
		END IF;
	END$$
DELIMITER ;

CALL demo2(1,@p_name);

(4) Inserting information using stored procedures

DELIMITER $$
CREATE PROCEDURE save_admin(IN p_account VARCHAR(100),IN p_password VARCHAR(100),OUT p_result VARCHAR(10))
	BEGIN
		-- Declare a variable to receive the query result
		DECLARE v_count INT DEFAULT 0;
		
		SELECT COUNT(*) INTO v_count FROM ADMIN WHERE ACCOUNT =p_account;
		IF v_count = 0 THEN
		   INSERT INTO ADMIN(ACCOUNT,PASSWORD) VALUES (p_account,p_password);
		    SET p_result ="Saved successfully";
			 SELECT p_result;
		ELSE 
		    SET p_result ="Account already exists";
		    SELECT p_result;
		 END IF; 
	END$$
DELIMITER ;

CALL save_admin('222','222',@p_result)

(5) mybatis calls the stored procedure

• void save(Map<String ,Object> map);
  
• final result
  
• Store data in the database
  

3, Functions

First, set the function to have no parameters before you can start writing the function

• SET GLOBAL log_bin_trust_function_creators=TRUE;
  Delete function
• DROP FUNCTION name;
• Function syntax rules:
  

(1) Without parameters

DELIMITER $$
CREATE  FUNCTION demo() RETURNS INT
    BEGIN
    DECLARE v_count INT DEFAULT 0;
    SELECT COUNT(*) INTO v_count FROM ADMIN;
    RETURN v_count; 
    END$$
DELIMITER ;

SELECT demo();

(2) if else with parameter

DELIMITER $$
CREATE  FUNCTION findType(p_type INT) RETURNS VARCHAR(10)
    BEGIN
    DECLARE v_type VARCHAR(10) DEFAULT '';
    IF p_type =0 THEN
	SET v_type='Super management';
    ELSE 
	SET v_type='General management';
    END IF;
    RETURN v_type;
    END$$
DELIMITER ;

SELECT id,ACCOUNT,findType(TYPE) FROM ADMIN

(3) With parameters

DELIMITER $$
CREATE  FUNCTION find_emp_dept(p_typeid INT) RETURNS VARCHAR(10)
    BEGIN
    DECLARE v_type VARCHAR(10) DEFAULT '';
    SELECT Dept_name INTO v_type FROM dept WHERE id =p_typeid;
    RETURN v_type;
    END$$
DELIMITER ;

SELECT id,NAME,find_emp_dept(Dept_id) FROM employee

4, Trigger

Similar to stored procedures, functions, related to tables, a bit like events. Triggered before or after adding, modifying, or deleting a table.

• The trigger has the following characteristics:

1. Associated with table
   Triggers are defined on a specific table, which is called the trigger table.
2. Auto activate trigger
   When an INSERT, UPDATE or DELETE operation is performed on the data in a table, if a trigger is defined for this specific operation on the table, the trigger is automatically executed, which is irrevocable.
3. Cannot be called directly
   Unlike stored procedures, triggers cannot be called directly, nor can they pass or accept parameters.
4. As part of a transaction
   Triggers are treated as a single transaction together with the statement that activates the trigger, and can be rolled back from anywhere in the trigger

• rule of grammar
• In the row level trigger code, you can use old and new to access the old and new data of the row. Old and new are the row record type variables of the corresponding table.
  

(1) Before deleting department information, automatically trigger the deletion of employee related department information

-- delete
DELIMITER $$

CREATE
   
    TRIGGER delete_employee BEFORE DELETE
    ON dept
    FOR EACH ROW
    BEGIN
	DELETE FROM employee WHERE Dept_id=old.id;
    END$$

DELIMITER ;

DELETE FROM dept WHERE id=4

(2) After adding user information, insert information into the log table

-- add to
DELIMITER $$

CREATE
   
    TRIGGER save_adminlog AFTER INSERT
    ON ADMIN
    FOR EACH ROW
    BEGIN
	INSERT INTO save_log(id,ACCOUNT,oper_time)VALUES(new.id,new.account,NOW());
    END$$

DELIMITER ;


INSERT INTO ADMIN(ACCOUNT,PASSWORD)VALUES('jim','111');