当前位置：首页 > news >正文

MariaDB基本知识汇总

news 来源：原创 2024/9/19 19:20:47

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 tempViewasSelect * From ttEvents

/*2、临时表*/

select * from ttEvents/*创建临时表-1 把ttEvents中数据放入到临时表tempTemporary*/Create Temporary Table tempTemporaryasSelect * 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 intbegindeclare 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)begindeclare result varchar(20);if(state>100)thenset result='true';elseif (state50)thenset 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)beginDECLARE 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.DictKeyIdLEFT JOIN FnDictionaryVals C ON C.LnkDictValId=A.DictValIdWHERE B.KEYCODE='JobDegree'AND FIND_IN_SET(CONCAT_WS('|',A.Val,C.Val),str)>0ORDER 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:beginselect * 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 rowbegin 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 THENLEAVE 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 oneCREATE USER 'tom'@'%' IDENTIFIED BY '1';# delete accountDROP 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 xINNER 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.ModifiedByWHERE 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"}');#虚拟列前验证效果EXPLAINSELECT * 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);#验证效果EXPLAINSELECT * FROM user WHERE user_name='zhao';#删除虚拟列alter table user drop column user_name;