/*
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;
