MariaDB基本知识汇总

jack15850798154

已于 2024-08-12 01:25:35 修改

阅读量406

点赞数 2

文章标签： mariadb 数据库

于 2024-08-12 01:11:09 首次发布

本文链接：https://blog.csdn.net/jack15850798154/article/details/141115014

版权

MariaDB

1、视图

2、临时表

3、自定义函数

4、存储过程

5、触发器

6、游标

7、变量声明与赋值

8、常用函数（日期格式，Guid，判断，循环，XML格式操作）

9、动态执行SQL 语句

10、开启执行计划

11、创建登录MariaDB账号赋予权限

12、check audit 插件

13、批量语句更新

14、数据库版本检查

15、虚拟列

/*1、视图 (tempView 试图名，ttEvents 表名)*/

Create View tempView

as

Select * From ttEvents

/*2、临时表*/

select * from ttEvents

/*创建临时表-1 把ttEvents中数据放入到临时表tempTemporary*/

Create Temporary Table tempTemporary

as

Select * from ttEvents;

/*创建临时表-2*/

Create Temporary Table tempTemporary

(

   EventId char(36) not null,

   Dept varchar(50),

   Title varchar(50)

)TYPE = HEAP;/*内存中直接创建临时表*/

/*查询临时表*/

select * from tempTemporary

/*删除临时表*/

drop Temporary Table tempTemporary

/*3、自定义函数*/

Delimiter $$ 

Drop function If Exists FnReturnInt; $$

create function FnReturnInt()

returns int

begin

   declare state int;

   set state=0;

    return state;

end; $$ 

delimiter ;


/*调用函数*/

select FnReturnInt();

/*带参数函数*/

Delimiter $$ 

Drop function If Exists FnReturnInt; $$

create function FnReturnInt(state int)

returns varchar(20)

begin

   declare result varchar(20);

   if(state>100)

   then

      set result='true';

   elseif (state50)

   then

      set result='error';

   else 

     set result='false';

   end if;

    return result;

end; $$ 

Delimiter ;

/*带参数函数调用*/

select FnReturnInt(60);

Delimiter $$ 

Drop function If Exists FnDutyLevel; $$

create function FnDutyLevel(str varchar(100))

returns varchar(500)

begin

      DECLARE result VARCHAR(2000); 

      SET result = ''; 

SELECT GROUP_CONCAT(A.Txt,'(',C.Txt,')') into result FROM FnDictionaryVals A 

INNER JOIN FNDICTIONARYKEYS B ON A.DictKeyId=B.DictKeyId

LEFT JOIN FnDictionaryVals C ON C.LnkDictValId=A.DictValId

WHERE B.KEYCODE='JobDegree'

AND FIND_IN_SET(CONCAT_WS('|',A.Val,C.Val),str)>0

ORDER BY A.DspOrder,C.DspOrder;

return result;

end; $$ 

Delimiter ;

/*4、存储过程*/

/*创建存储过程*/

Delimiter $$

Drop Procedure If Exists usp_TempProcedure; $$

Create Procedure usp_TempProcedure(in pKey char(36))

Label_Return:begin

    select * from TtEvents where IsDeleted=1 and EventId=pKey;

end; $$

delimiter ; 

/*调用存储过程*/

call usp_TempProcedure('0da453b2-c690-4234-ad5e-bd94c3d6ce58');

/*5、触发器*/

/*创建表*/

create table t(s1 integer);

Delimiter $$

Drop Trigger If Exists trigger_Temp; $$

Create Trigger trigger_Temp 

before insert on t for each row

begin 

   set @x='hello trigger';

   set New.s1=55;

end; $$

delimiter ; 

insert into t values(1);

select @x,t.* from t;

drop table t;

drop trigger trigger_Temp;

/*6、游标（必须放在函数、存储过程等下面执行，单独不可以执行）*/

Delimiter $$

Drop Procedure If Exists usp_TempProcedure; $$

Create Procedure usp_TempProcedure()

Label_Return:begin

  -- 需要定义接收游标数据的变量 

  DECLARE a CHAR(36);

    -- 遍历数据结束标志

  DECLARE done INT DEFAULT FALSE;

  -- 游标

  DECLARE cur CURSOR FOR SELECT eventid FROM ttEvents limit 10;

  -- 将结束标志绑定到游标

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  -- 打开游标

  OPEN cur;

  -- 开始循环

  read_loop: LOOP

    -- 提取游标里的数据，这里只有一个，多个的话也一样；

    FETCH cur INTO a;

    -- 声明结束的时候

    IF done THEN

      LEAVE read_loop;

    END IF;

    -- 这里做你想做的循环的事件

select a;

#    INSERT INTO t VALUES (a);

  END LOOP;

  -- 关闭游标

  CLOSE cur;

end; $$

delimiter ;

call usp_TempProcedure();

/*7、声明变量与赋值*/

set @num='string';

select @EventId :=eventid from ttevents；

/*8、判断表是否存在,如果存在则删除*/

Drop Table If Exists tempTemporary;

/*9、动态执行SQL 语句*/

set @ss='select * from ttevents limit 10;';

PREPARE s1 FROM @ss;

execute s1;

set @col=concat('a','.','eventid');

set @ss=concat('select ',@col,' from ttevents a limit 10;');

PREPARE s1 FROM @ss;

execute s1;

/*10、开启执行计划*/

 set global event_scheduler =1;

/*11、创建登录MariaDB账号赋予权限*/

select * from mysql.user

# mariadb create account and password

# method one

CREATE USER 'tom'@'%' IDENTIFIED BY '1';

# delete account

DROP USER 'tom'@'%';

# grant roles to account

# 授权tom用户拥有smartevent数据库的所有权限。 

grant all privileges on smartevent.* to 'tom'@'%' identified by '1'; 

# 权限回收

revoke select,drop,update on smartevent.* from tom;

grant select on smartevent.* to 'tom'@'%' identified by '1'; 

# 刷新特权

flush privileges;

/*12、check audit 插件*/

show global variables like '%plugin_dir%';

install plugin server_audit soname 'server_audit.dll';

show global variables like '%server_audit%';

set global server_audit_events='QUERY';

set global server_audit_LOGGING='ON';

/*13、批量语句更新*/

UPDATE ToSchedules as x

INNER JOIN TtEventSessions as s ON  s.TenantId = @TenantId AND s.SessionId = x.OriSessionId 

 SET 

    x.DtStart = s.DtStart,

    x.DtEnd = s.DtStart,

    x.TimeStart = s.TimeStart,

    x.TimeEnd = s.TimeEnd,

    x.Subject = s.Name,

    x.Descr = IFNULL(s.Intro,''),

    x.ModifiedOn = s.ModifiedOn,

    x.ModifiedBy = s.ModifiedBy

WHERE x.TenantId=@TenantId AND x.UserId = @UserId;

/*14、数据库版本检查*/

#检查版本

SELECT @@VERSION;

/*15、虚拟列*/

#创建测试表

create table smartx_temp.temp_user(

uid int AUTO_INCREMENT primary key,

jsondata json

);

#插入数据

insert into smartx_temp.temp_user values (NULL,'{"name":"wang","address":"shenyang"}');

insert into smartx_temp.temp_user values (NULL,'{"name":"zhao","address":"riben"}');

#虚拟列前验证效果

EXPLAIN

SELECT * FROM user WHERE user_name='zhao';

#新建虚拟列

alter table user add user_name varchar(20) generated always as (json_value(jsondata,'$.name'));

#新建虚拟列索引

alter table user add index idx_name(user_name);

#验证效果

EXPLAIN

SELECT * FROM user WHERE user_name='zhao';

#删除虚拟列

alter table user drop column user_name;