【Navicat Premium】自定义 sql 标签

说明

Navicat 中可以自定义一下sql语句的标签，方便开发者使用。开发者将自定义的sql结构转成标签之后，以后使用相同结构的sql语句时，只需要在标签栏中取出补全即可。

下图为Navicat的标签栏，Navicat 自带了一些标签。

自定义标签创建方式

新建一个查询窗口，选择写好的sql语句块 并 右键点击，选择创建片段。
并在弹出的窗口中补全信息即可。

创建的标签块鼠标长按拖出到查询窗口中即可使用

自定义sql标签

注意：下面的sql标签块的sql语句只针对mysql

创建mysql用户并授权

-- 创建mysql用户
-- % 所有地址均可访问，也可指定特定的IP地址
CREATE USER '用户名'@'可访问的主机地址' IDENTIFIED BY '密码';

-- 给用户授权 
-- GRANT *.* 表示全部权限
-- on *.* 表示全部数据库全部表 
GRANT  
SELECT, INSERT, UPDATE, 
REFERENCES, DELETE, CREATE, DROP, 
ALTER, INDEX, CREATE VIEW, SHOW VIEW

ON 数据库名称.* TO '用户名'@'可访问的主机地址';

创建触发器

/*
	TRIGGER_NAME ：触发器名称
	TABLE_NAME：数据表名，作用在哪个表
	[after|before] : 操作顺序，after和before二选一，，分别表示之后和之前
	[update|insert|delete] ：操作，三选一
*/
delimiter ||
DROP TRIGGER if EXISTS TRIGGER_NAME||
create  TRIGGER TRIGGER_NAME [after|before] [update|insert|delete] on  TABLE_NAME for each row 
begin
		-- todo
		-- 这里写触发器的内容
end||
delimiter ;

创建视图

/*
	VIEW_NAME：视图名称
*/
drop view if EXISTS VIEW_NAME; -- 若存在同名的视图，则先删除后创建
create view VIEW_NAME as 
	-- todo
	-- 视图内容

创建数据表

/*
 TABLE_NAME： 表名
 
*/
drop table if EXISTS TABLE_NAME; -- 若存在同名的表，则先删除后创建
create table TABLE_NAME (
	id int(11) not null PRIMARY key auto_increment COMMENT 'id',
	sort int(11) not null default '0' comment '排序',	
 	create_time int(11) not null COMMENT '创建时间',
	update_time int(11) null comment '更新时间',
	delete_flag TINYINT(2) not null DEFAULT '0' comment '是否删除 0否未删除，1是已删除',
	state TINYINT(1) not null DEFAULT '1' COMMENT '状态是否可用, 0不可用，1可用',
	
	-- todo
	-- 其他字段
	
	remark varchar(255) null comment '备注'
)COMMENT '表注释' default charset=utf8 engine=INNODB;

创建数据库

/*
dbName ：数据库名称
*/
create database dbName charset=utf8 collate=utf8_general_ci;

创建索引

/*
TABLE_NAME：表名
[INDEX|UNIQUE |FULLTEXT|SPATIAL] ： 索引类型
INDEX_NAME：索引名称
(col_1,col_2,...) ：建立索引的字段名称，一个或多个字段
*/
ALTER table TABLE_NAME add  [INDEX|UNIQUE |FULLTEXT|SPATIAL]  INDEX_NAME  (col_1,col_2,...);
 

查询数据库的数据容量

当查询条件where指定了数据库名称后，统计出此数据库的数据量

select 
table_schema as '数据库' ,
table_name as '表名称',
table_rows as '记录数',
TRUNCATE(data_length/1024/1024,2) as '数据容量（MB）',
TRUNCATE(index_length/1024/1024,2) as '索引容量（MB）'
from information_schema.TABLES
 -- where table_schema='指定数据库名称'
ORDER BY table_schema asc,data_length desc,index_length desc;

查看指定数据库数据表详细属性信息

/**
查询数据库中各个表信息
dbName：数据库名称
*/
show table status from dbName;

修改已创建的数据表的存储引擎

/**
修改表的存储引擎：
tableName：表名
EngineType：存储引擎：MyISAM、InnoDB等
*/
ALTER TABLE tableName ENGINE = EngineType;

创建存储过程

DELIMITER ;;  -- 设置sql语句结束符号
DROP PROCEDURE IF EXISTS procedureName; -- procedureName 存储过程名称，存在相同名称的存储过程则删除后创建
create procedure procedureName(in paramIn int,out paramOut varchar(255),inout paramInOut varchar(255)) --  paramIn 入参参数，paramOut 出参参数，paramInOut：入参出参参数
begin
	 -- 注意：begin ... end 中间部分是自定义的部分，即存储过程逻辑部分
	
	 -- 声明变量
	 declare v_varName varchar(255) default null;
	 declare v_varAge int default 0;
	 
	 -- 声明游标
	 declare myCursor cursor for
	 	select * from tableName where id>0; -- 这个语句是自定义查询的数据
	 	
	 -- 声明数据02000异常和异常标志位，02000表示无数据异常
	 declare flag int default 0;
	 declare continue handler for sqlstate ‘02000’ set flag = 1;
	
	-- 打开游标
	open myCursor;

	-- 循环 循环游标内的数据，赋值给变量,多个变量按照游标声明的顺序赋值，逗号分隔。
	repeat 
		fetch myCursor into v_varName;
			
			-- 给变量赋值: 把 100 赋值给变量v_varAge;
			select 100 into v_varAge;
			
			-- 调试存储过程：输出存储过程执行过程的变量
			select v_varAge;	
			
		-- 退出循环
		until flag = 1 end repeat; -- 循环直到flag等于1时退出循环
	-- 关闭游标
	close myCursor; -- 关闭游标

end;;
DELIMITER ; -- -- 恢复sql语句结束符号

调试存储过程方法

注意：mysql的存储过程调试没有oracle的存储过程调试的方便。因为oracle的PLSQL Developer 工具可以一步步的调试存储过程，而本人没发现哪个工具有这个功能。（求大神们推荐）。。。

但是：

我们可以通过在mysql存储过程编写的过程中，是可以打印出自己需要的内容。例如存储过程执行过程中某个变量的值。
我们在存储过程中使用查询语句，在mysql的命令行窗口中执行存储过程，即可。
例如：

创建测试的存储过程

DELIMITER ;;
DROP PROCEDURE IF EXISTS pro_loopPrintIndex;
create procedure pro_loopPrintIndex()
begin
	 -- 循环0-5，输出每次循环的值
	 	 
 	-- 定义循环的下标
 	declare v_index int default 0;
	 
	REPEAT
		-- 调试内容，输出循环的值
		select v_index;
		
		-- 下标每次循环都 +1
		SELECT (v_index+1) into v_index;
		
	UNTIL v_index > 5 END REPEAT;
end;;
DELIMITER ;

带哦用存储过程 测试

打开mysql的命令行窗口。Navicat 快捷键F6

call pro_loopPrintIndex();

执行结果如下图：