視圖

視圖是一種虛擬表，只保存查詢的Sql的邏輯
- 創(chuàng)建
create （or replace）view 視圖名 as 查詢語句 #修改視圖
create view user_v as select user,host from mysql.user;
- 查詢視圖

show create view user_v;
select * from user_v where user='song';
-刪除視圖
drop view if exists user_v;

存儲過程

事先經(jīng)過編譯并存儲在數(shù)據(jù)庫中的一段SQL語句集合
#創(chuàng)建
delimiter $$
create procedure p1()
begin
    select count(*) from student;
end$$
delimiter ;
#調(diào)用
call p1();
#查看某個表的存儲過程
select * from information_schema.routines where routine_schema='world';
#查看某個存儲過程的創(chuàng)建語句
show create procedure p1;
#刪除
drop procedure p1;

存儲過程
變量
系統(tǒng)變量：全局變量global 會話變量session
-- 查看系統(tǒng)變量
show session variables ;

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

select @@global.autocommit;
select @@session.autocommit;
--用戶自定義變量
set @num:='select count(*) from ctiy';
select count(*) into @sum from city; # 將查詢結(jié)果賦值
select @num;
select @sum;
-- 局部變量

create procedure  p3()
begin
    declare s_count int default 3;
        set s_count=5;
    
        select s_count;
end;

call p3();
--if 條件語句

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

    if score >= 85 then
        set result := '優(yōu)秀';
    elseif score >= 60 then
        set result := '及格';
    else
        set result := '不及格';
    end if;
    select result;
end;

-- in/out/inout參數(shù)

-- 根據(jù)傳入(in)參數(shù)score活孩，判定當(dāng)前分?jǐn)?shù)對應(yīng)的分?jǐn)?shù)等級扇商，并返回(out)侥锦。
-- score >= 85分病毡，等級為優(yōu)秀祭务。
-- score >= 60分 且 score < 85分镀迂，等級為及格丁溅。
-- score < 60分，等級為不及格探遵。

create procedure p4(in score int, out result varchar(10))
begin
    if score >= 85 then
        set result := '優(yōu)秀';
    elseif score >= 60 then
        set result := '及格';
    else
        set result := '不及格';
    end if;
end;

call p4(18, @result);


-- 將傳入的 200分制的分?jǐn)?shù),進(jìn)行換算,換算成百分制 , 然后返回分?jǐn)?shù) ---> inout
create procedure p5(inout score double)
begin
    set score := score * 0.5;
end;

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


-- case
-- 根據(jù)傳入的月份窟赏，判定月份所屬的季節(jié)（要求采用case結(jié)構(gòu)）。
-- 1-3月份箱季，為第一季度
-- 4-6月份涯穷，為第二季度
-- 7-9月份，為第三季度
-- 10-12月份藏雏，為第四季度

create procedure p6(in month int)
begin
    declare result varchar(10);

    case
        when month >= 1 and month <= 3 then
            set result := '第一季度';
        when month >= 4 and month <= 6 then
            set result := '第二季度';
        when month >= 7 and month <= 9 then
            set result := '第三季度';
        when month >= 10 and month <= 12 then
            set result := '第四季度';
        else
            set result := '非法參數(shù)';
    end case ;


-- while   計算從1累加到n的值拷况，n為傳入的參數(shù)值。

-- A. 定義局部變量, 記錄累加之后的值;
-- B. 每循環(huán)一次, 就會對n進(jìn)行減1 , 如果n減到0, 則退出循環(huán)
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
repeat
  語句
until 條件
end repeat;
--loop
 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;

-- 游標(biāo)
-- 根據(jù)傳入的參數(shù)uage掘殴，來查詢用戶表 tb_user中赚瘦，所有的用戶年齡小于等于uage的用戶姓名（name）和專業(yè)（profession），
-- 并將用戶的姓名和專業(yè)插入到所創(chuàng)建的一張新表(id,name,profession)中奏寨。

-- 邏輯:
-- A. 聲明游標(biāo), 存儲查詢結(jié)果集
-- B. 準(zhǔn)備: 創(chuàng)建表結(jié)構(gòu)
-- C. 開啟游標(biāo)
-- D. 獲取游標(biāo)中的記錄
-- E. 插入數(shù)據(jù)到新表中
-- F. 關(guān)閉游標(biāo)

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);






create procedure p12(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 not found 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 p12(30);


-- 存儲函數(shù)
-- 從1到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);

觸發(fā)器

-- 觸發(fā)器
-- 需求: 通過觸發(fā)器記錄 user 表的數(shù)據(jù)變更日志(user_logs) , 包含增加, 修改 , 刪除 ;

-- 準(zhǔn)備工作 : 日志表 user_logs
create table user_logs(
id int(11) not null auto_increment,
operation varchar(20) not null comment '操作類型, insert/update/delete',
operate_time datetime not null comment '操作時間',
operate_id int(11) not null comment '操作的ID',
operate_params varchar(500) comment '操作參數(shù)',
primary key(id)
)engine=innodb default charset=utf8;

-- 插入數(shù)據(jù)觸發(fā)器
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('插入的數(shù)據(jù)內(nèi)容為: id=',new.id,',name=',new.name, ', phone=', NEW.phone, ', email=', NEW.email, ', profession=', NEW.profession));
end;

-- 查看
show triggers ;

-- 刪除
drop trigger tb_user_insert_trigger;

-- 插入數(shù)據(jù)到tb_user
insert into tb_user(id, name, phone, email, profession, age, gender, status, createtime) VALUES (26,'三皇子','18809091212','erhuangzi@163.com','軟件工程',23,'1','1',now());

-- 修改數(shù)據(jù)觸發(fā)器
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('更新之前的數(shù)據(jù): id=',old.id,',name=',old.name, ', phone=', old.phone, ', email=', old.email, ', profession=', old.profession,
' | 更新之后的數(shù)據(jù): id=',new.id,',name=',new.name, ', phone=', NEW.phone, ', email=', NEW.email, ', profession=', NEW.profession));
end;

show triggers ;

update tb_user set profession = '會計' where id = 23;

update tb_user set profession = '會計' where id <= 5;

-- 刪除數(shù)據(jù)觸發(fā)器
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('刪除之前的數(shù)據(jù): 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;