数据库操作命令在IDEA工具database的console命令

数据库表结构与视图

事务隔离级别RR（REPEATABLE-READ）解决不可重复读演示

-- 查询隔离级别
select @@transaction_isolation;

-- 设置隔离级别
set session transaction isolation level read committed ;
set session transaction isolation level REPEATABLE READ ;

start transaction;

select * from sys_user;

commit ;

rollback ;
-- SQL性能分析
show global status like 'Com_______';
-- 查看慢查询是否开启
show variables like 'slow_query_log';

-- linux 重启msql
-- systemctl restart mysqld;

-- 创建视图
create or replace view v_users as select id,username from sys_user where id <10;

explain select id,username from sys_user where username <10;

explain select * from v_users where id = 2;

-- 查询创建视图的语句
show create view  v_users;

drop view if exists v_users;
-- 视图检查选项，插入的数据跟据视图的where条件进行检查是否符合，如果不符合会不让插入数据
-- with cascaded check option
create or replace view v_users as select id,username from sys_user where id <10 with cascaded check option ;
-- 视图可以插入数据并且 会插入到真实表中
insert into v_users values (3,'wangliu');
insert into v_users values (30,'wangliu');

-- 视图简化多表联查 只需要查询视图不需要每次都增加其他的条件
-- 视图屏蔽/隐藏敏感数据字段 授权给无权查看的人

--  存储过程
CREATE PROCEDURE PRO_USERS()
BEGIN
    SELECT COUNT(*) FROM sys_user;
end;
-- 调用存储过程
CALL PRO_USERS();
-- 查看存储过程
select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'test';

-- 查看存储过程建表语句
show create procedure PRO_USERS;

-- 删除存储过程
drop procedure if exists PRO_USERS;

-- 系统变量 session|global 默认都是session
show session variables ;
show session variables  like 'auto%';

select @@autocommit;

select @@session.autocommit;
select @@global.autocommit;
set @@autocommit = 1;

-- 用户自定义变量
set @myName :='zxd';
set @myAge := 30;

set @myName :='lisi',@myAge := 40;

select @myName,@myAge;

select count(*) into @myCount from sys_user;

select @myCount;

-- 局部变量声明 begin ...end 之前
drop procedure if exists pro_test2;
create procedure pro_test2()
begin
    declare var_age int default 0;
    set var_age := 20;
    select var_age;
end;

show create procedure pro_test2;

call pro_test2();

-- IF ELSEIF ELSE 存储过程逻辑分支判断
create procedure pro_p3()
begin
    declare score int default 58;
    declare res varchar(10);
    if score >80 then
        set res:= '优秀';
        elseif score >60 then
        set res :='及格';
        else
        set res:= '不及格';
    end if;
    select res;
end;

call pro_p3();

-- 存储过程带输入，输出参数的
create procedure pro_p4(in score int,out res varchar(10))
begin
    if score >80 then
        set res:= '优秀';
    elseif score >60 then
        set res :='及格';
    else
        set res:= '不及格';
    end if;
    select res;
end;

call pro_p4(18,@res);
select @res;

-- INOUT 出参与入参是同一个参数的写法
drop procedure if exists p5;

create procedure p5(inout score double)
begin
    set score := score*0.5;
end;

set @score :=178.8;
call p5(@score);
select @score;

-- 根据传入的月份，返回季度信息
drop procedure p6;
create procedure p6(in month int,out res varchar(20))
begin
    case
        when month >=1 and month <=3
            then set res:= '第一季度';
        when month >=4 and month<=6
            then set res:= '第二季度';
        when month >=7 and month<=9
            then set res:= '第三季度';
        when month >=10 and month<=12
            then set res:= '第四季度';
        else
            set res:='非法参数';
        end case ;
    select concat('您输入的月份 ',month,',所属季度为',res) into res;
end;

call p6(7,@res);
select @res;

-- while 循环逻辑
drop procedure p7;
create procedure p7(inout num int)
begin
    declare total int default 0;
    while num >0 do
        set total:= total +num;
        set num := num -1;
        end while;
    set num := total;
end;

set @num := 10;
call p7(@num);
select @num;

-- repeat 循环逻辑 SQL逻辑会先执行一次，不管是否满足until条件
drop procedure p8;
create procedure p8(inout num int)
begin
    declare total int default 0;

    repeat
        set total := total + num;
        set num:= num -1;
    until  num <=0
        end repeat;
    set num:= total;
end;

set @num :=-10;
call p8(@num);
select @num;

-- loop循环
drop procedure p9;
create procedure p9(in num int,out res int)
begin
    #一定要初始化赋值
    set res:=0;
    sum:loop
        if num <=0 then
            leave sum;
        end if;
        set res := res +num;
        set num:=num -1;
    end loop sum;
end;

call p9(-10,@res);
select @res;

-- loop 退出当前循环 测试一个数字相加 只加偶数不加奇数
drop procedure p10;
create procedure p10(in num int,out res int)
begin
    #一定要初始化赋值
    set res:=0;
    sum:loop
        if num <=0 then
            leave sum;
        end if;
        if num%2 = 1 then
            set num:= num -1;
            iterate sum;
        end if;
        set res := res +num;
        set num:=num -1;
    end loop sum;
end;

call p10(10,@res);
select @res;

-- cursor
-- 准备一张表
create table tb_user(
    id int auto_increment primary key ,
    name varchar(20),
    age int ,
    phone int,
    email varchar(100),
    zhuanye varchar(50)
);
-- 查看默认建表语句
show create table tb_user;
desc tb_user;
drop table tb_user;
select * from tb_user;
insert into tb_user values (null,'唐僧',80,1990618888,'tangsanzang@qq.com','玄奘法师'),
                           (null,'猪八戒',400,1990613333,'zhubajie@qq.com','净坛使者');
-- 创建存储过程
create procedure p11(in v_num int)
begin
    # 声明的临时变量必须在游标之上
    declare v_name varchar(20);
    declare v_age int default 0;
    declare pro_cur cursor for select name ,age from tb_user where age > v_num;
    #增加异常处理
    # [02000][1329] No data - zero rows fetched, selected, or processed
    #declare exit handler for sqlstate '02000' close pro_cur;
    declare exit handler for not found close pro_cur;
    # 准备要生成的表
    drop table if exists pro_tb_user;
    create table if not exists pro_tb_user(
        id int auto_increment primary key ,
        name varchar(20),
        age int
    );
    #打开游标
    open pro_cur;
    #遍历游标 死循环 知道循环到最后一条查不到数据退出
    while true do
        fetch pro_cur into v_name,v_age;
        insert into pro_tb_user values (null,v_name,v_age);
    end while;
    #关闭游标
    close pro_cur;
end;
drop procedure p11;

call p11(100);

select * from pro_tb_user;

create procedure p12(in v_num int)
begin
    # 声明的临时变量必须在游标之上
    declare v_name varchar(20);
    declare v_age int default 0;
    declare done int default 0;
    declare pro_cur cursor for select name ,age from tb_user where age > v_num;
    #增加异常处理
    # [02000][1329] No data - zero rows fetched, selected, or processed
    #declare exit handler for sqlstate '02000' close pro_cur;
    declare continue handler for not found set done:=1;
    # 准备要生成的表
#     drop table if exists pro_tb_user;
    create table if not exists pro_tb_user(
      id int auto_increment primary key ,
      name varchar(20),
      age int
    );
    #打开游标
    open pro_cur;
    #循环数据
    ext_loop :loop
        fetch pro_cur into v_name,v_age;
        if done =1 then
            leave ext_loop;
        end if;
        insert into pro_tb_user values (null,v_name,v_age);
    end loop;
    #关闭游标
    close pro_cur;
end;

call p12(50);

-- 表锁测试
-- 读锁 -都可以读 但是不能写
-- 写锁 -只有自己能读能写，别人都不能读不能写
lock tables test.tb_user read ;

select * from tb_user;
-- 创建表与删除表字段
alter table pro_tb_user add column java int;
desc pro_tb_user;
alter table pro_tb_user drop column java;

unlock tables;

-- 测试元元数据锁
start transaction ;
select * from tb_user;
select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks t;

commit ;