Views, stored procedures, triggers

View

View (View) is a virtual table. The data in the view does not actually exist in the database. The row and column data come from the tables used in the query of the custom view and are dynamically generated when the view is used. The view value saves the SQL logic of the query and does not save the query results.

1. Basic view operations

-- Create view
create view stu_v_1 as select id,name from student where id <=20;

-- View the view creation statement
show CREATE view stu_v_1

-- Modify view
CREATE or REPLACE view stu_v_1 as SELECT * from student;
alter view stu_v_1 as SELECT * from student;

-- Delete view
drop view stu_v_1;

2. View check option CASCADED

In MySQL, when using the WITH CHECK OPTION clause to create a view, MySQL will check each row being changed through the view, such as insertion, update, and deletion, so that it conforms to the definition of the view. MySQL allows you to create a view based on another view. It also checks the rules in the dependent view for consistency. In order to determine the scope of the check, mysql provides two options: CASCADED and LOCAL. The default value is CASCADED.

CASCADE: Regardless of whether the view check is turned on in the associated view, it is necessary to determine whether the insertion conditions are met.

SELECT * from student;

create view v1 as SELECT id,name from student where id <=20;
insert into v1 VALUES (5,'Tom');
insert into v1 VALUES (25,'Tom');

create view v2 as SELECT id,name from v1 where id >=10 with CASCADED check OPTION;
insert into v2 VALUES (6,'Tom');
insert into v2 VALUES (11,'Tom');
-- The reason why the insertion with id 21 failed is that the v2 view is queried through the v1 view, and cascaded is used. It is not only necessary to detect the conditions of v2, but also the conditions of v1.
insert into v2 VALUES (21,'Tom');

create view v3 as SELECT id,name from v2 where id >=15;
insert into v3 VALUES (16,'Tom');
insert into v3 VALUES (7,'Tom');
-- The reason why inserting v3 with id 22 failed is that the v3 view is queried through the v2 view, and the conditions of v2 need to be detected, and v2 is cascaded with v1, so the conditions of the three views of v1, v2, and v3 must be Perform testing
insert into v3 VALUES (22,'Tom');

LOCAL: Different from CASCADE, when performing view check, it will check whether the associated table has view check enabled. If view check is not enabled, there is no need to judge the conditions of the associated table.

create view v4 as SELECT id,name from student where id <=20;
insert into v4 VALUES (5,'Tom');
insert into v4 VALUES (25,'Tom');

create view v5 as SELECT id,name from v4 where id >=10 with CASCADED LOCAL OPTION;
insert into v5 VALUES (6,'Tom');
-- The id=11 here, if it is local, you can insert it, because although it is associated with v4, v4 does not enable view checking, so you only need to meet the conditions of the v5 view.
insert into v5 VALUES (11,'Tom');

create view v6 as SELECT id,name from v2 where id >=15
insert into v6 VALUES (16,'Tom');
-- The id=22 here can also be inserted, because although it is associated with v5, although v5 has a view check, the id meets the conditions, and the v4 associated with v5 does not have a view check enabled, so it only needs to meet the conditions of the v5 and v6 views.
insert into v6 VALUES (22,'Tom');

3. View update

Stored procedure

1. Basic syntax

--Create stored procedure
CREATE PROCEDURE p1()
BEGIN
\t
SELECT count(*) from tb_user;
\t
END

-- transfer
call p1()

-- Check
SELECT * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'itcast'
show create PROCEDURE p1;

-- delete
DROP PROCEDURE IF EXISTS P1;

2. Variables

There are three main types of variables: system variables, user-defined variables and local variables.

System variables

-- View system variables
show session variables;

show session variables like 'auto%';
show global variables like 'auto%';

select @@global.autocommit;
select @@session.autocommit;


--Set system variables
set session autocommit = 1;

insert into course(id, name) VALUES (6, 'ES');

set global autocommit = 0;

select @@global.autocommit;

User-defined variables

-- variables: user variables
-- assignment
set @myname = 'itcast';
set @myage := 10;
set @mygender := 'male',@myhobby := 'java';

select @mycolor := 'red';
select count(*) into @mycount from tb_user;

-- use
select @myname,@myage,@mygender,@myhobby;

select @mycolor , @mycount;

select @abc;

Local variables

-- variables: user variables
-- assignment
set @myname = 'itcast';
set @myage := 10;
set @mygender := 'male',@myhobby := 'java';

select @mycolor := 'red';
select count(*) into @mycount from tb_user;

-- use
select @myname,@myage,@mygender,@myhobby;

select @mycolor , @mycount;

select @abc;

3. if conditional judgment

if condition judgment requirement

create procedure p3()
begin
    declare score int default 58;
    declare result varchar(10);

    if score >= 85 then
        set result := 'Excellent';
    elseif score >= 60 then
        set result := 'passed';
    else
        set result := 'failed';
    end if;
    select result;
end;

call p3();

4. Stored procedure-parameters (IN/OUT)

need:

--in/out/inout parameters

-- Based on the incoming (in) parameter score, determine the score level corresponding to the current score and return (out).
-- score >= 85 points, the grade is excellent.
-- score >= 60 points and score < 85 points, the grade is passing.
-- score < 60 points, the grade is failing.

create procedure p4(in score int, out result varchar(10))
begin
    if score >= 85 then
        set result := 'Excellent';
    elseif score >= 60 then
        set result := 'passed';
    else
        set result := 'failed';
    end if;
end;

call p4(18, @result);
select @result;




-- Convert the incoming 200-point score into a hundred-point system, and then return the score ---> inout
create procedure p5(inout score double)
begin
    set score := score * 0.5;
end;

set @score = 198;
call p5(@score);
select @score;

5. Stored procedure – loop

while

The while loop is to execute the SQL statement in the loop body after the conditions are met, corresponding to the while syntax in Java.

--while calculates the value accumulated from 1 to n, where n is the passed parameter value.

-- A. Define local variables and record the accumulated values;
-- B. Each time through the loop, n will be decremented by 1. If n is reduced to 0, the loop will exit.
create procedure p7(in n int)
begin
    declare total int default 0;

    while n>0 do
         set total := total + n;
         set n := n - 1;
    end while;

    select total;
end;

call p7(100);

repeat

Corresponds to do while syntax in Java

--repeat calculates the value accumulated from 1 to n, where n is the parameter value passed in.
-- A. Define local variables and record the accumulated values;
-- B. Every time it loops, n will be -1. If n decreases to 0, the loop will exit.
create procedure p8(in 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;

call p8(10);
call p8(100);

loop

Corresponds to the infinite loop in Java, leave corresponds to break, and iterate corresponds to continue.

--Loop requirement 1 calculates the value accumulated from 1 to n, where n is the passed parameter value.
-- A. Define local variables and record the accumulated values;
-- B. Every time it loops, n will be -1. If n is reduced to 0, the loop will exit ----> leave xx
create procedure p9(in n int)
begin
    declare total int default 0;

    sum:loop
        if n<=0 then
            leave sum;
        end if;

        set total := total + n;
        set n := n - 1;
    end loop sum;

    select total;
end;


-- Loop requirement 2 Calculate the even accumulated value from 1 to n, where n is the passed parameter value.
-- A. Define local variables and record the accumulated values;
-- B. Every time it loops, n will be -1. If n is reduced to 0, the loop will exit ----> leave xx
-- C. If the accumulated data is an odd number, enter the next cycle directly. --------> iterate xx

create procedure p10(in n int)
begin
    declare total int default 0;

    sum:loop
        if n<=0 then
            leave sum;
        end if;

        if n%2 = 1 then
            set n := n - 1;
            iterate sum;
        end if;

        set total := total + n;
        set n := n - 1;
    end loop sum;

    select total;
end;

call p10(100);

6. Stored procedure-cursor

-- cursor
-- Based on the incoming parameter uage, query the user table tb_user for all users whose age is less than or equal to the user name (name) and profession (profession) of uage.
-- And insert the user's name and profession into a new table created (id, name, profession).

-- Logic:
-- A. Declare the cursor and store the query result set
-- B. Preparation: Create table structure
-- C. Open cursor
-- D. Get the records in the cursor
-- E. Insert data into a new table
-- F. Close cursor

create procedure p11(in uage int)
begin
    declare uname varchar(100);
    declare upro varchar(100);
    declare u_cursor cursor for select name,profession from tb_user where age <= uage;
    declare exit handler for SQLSTATE '02000' close u_cursor;

    drop table if exists tb_user_pro;
    create table if not exists tb_user_pro(
        id int primary key auto_increment,
        name varchar(100),
        profession varchar(100)
    );

    open u_cursor;
    while true do
        fetch u_cursor into uname,upro;
        insert into tb_user_pro values (null, uname, upro);
    end while;
    close u_cursor;

end;


call p11(30);

7. Storage function

The difference between stored functions and stored procedures is that they can only have IN parameters and must have a return value.

-- stored function
-- Accumulation from 1 to n

create function fun1(n int)
returns int deterministic
begin
    declare total int default 0;

    while n>0 do
        set total := total + n;
        set n := n - 1;
    end while;

    return total;
end;


select fun1(50);

Trigger

-- trigger
-- Requirements: Record the data change log (user_logs) of the user table through triggers, including addition, modification, and deletion;

-- Preparation work: log table user_logs
create table user_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 'operate time',
  operate_id int(11) not null comment 'Operation ID',
  operate_params varchar(500) comment 'operating parameters',
  primary key(`id`)
)engine=innodb default charset=utf8;

--Insert data trigger
create trigger tb_user_insert_trigger
    after insert on tb_user for each row
begin
    insert into user_logs(id, operation, operate_time, operate_id, operate_params) VALUES
    (null, 'insert', now(), new.id, concat('The inserted data content is: id=',new.id,',name=',new.name, ', phone=', NEW. phone, ', email=', NEW.email, ', profession=', NEW.profession));
end;


-- Check
show triggers;

-- delete
drop trigger tb_user_insert_trigger;

-- Insert data into tb_user
insert into tb_user(id, name, phone, email, profession, age, gender, status, createtime) VALUES (26,'Third Prince','18809091212','[email protected]','Software Engineering',23, '1','1',now());



-- Modify data trigger
create trigger tb_user_update_trigger
    after update on tb_user for each row
begin
    insert into user_logs(id, operation, operate_time, operate_id, operate_params) VALUES
    (null, 'update', now(), new.id,
        concat('Update previous data: id=',old.id,',name=',old.name, ', phone=', old.phone, ', email=', old.email, ', profession= ', old.profession,
            ' | Updated data: id=',new.id,',name=',new.name, ', phone=', NEW.phone, ', email=', NEW.email, ', profession=' , NEW.profession));
end;

show triggers;

update tb_user set profession = 'Accountant' where id = 23;

update tb_user set profession = 'Accountant' where id <= 5;





-- Delete data trigger
create trigger tb_user_delete_trigger
    after delete on tb_user for each row
begin
    insert into user_logs(id, operation, operate_time, operate_id, operate_params) VALUES
    (null, 'delete', now(), old.id,
        concat('Delete previous data: id=',old.id,',name=',old.name, ', phone=', old.phone, ', email=', old.email, ', profession= ', old.profession));
end;

show triggers;


delete from tb_user where id = 26;

The knowledge points of the article match the official knowledge files, and you can further learn relevant knowledge. MySQL entry-level skills treeDatabase compositionView 76325 people are learning the system