目录
说明
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 datetime not null DEFAULT now() 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();
执行结果如下图:
创建函数
DELIMITER //
DELIMITER ;
DROP FUNCTION IF EXISTS 函数名称;
CREATE FUNCTION 函数名称( 参数名称 参数类型,.....)
RETURNS 返回值类型
DETERMINISTIC
NO SQL
BEGIN
-- todo 函数体
END //
DELIMITER ;
mysql 雪花算法函数
生成雪花算法的id,生成的id建议使用bigint存储
DELIMITER //
CREATE FUNCTION `getSnowId`()
RETURNS bigint(20)
DETERMINISTIC
NO SQL
BEGIN
DECLARE b_current_time BIGINT;
DECLARE b_time_tick BIGINT;
DECLARE i_work_id INT;
DECLARE i_work_id_big_length INT;
DECLARE i_seq_big_length INT;
DECLARE f_random FLOAT;
DECLARE b_res BIGINT;
SET i_work_id = 1;
SET i_work_id_big_length = 4;
SET i_seq_big_length = 8;
SET b_current_time = (REPLACE(UNIX_TIMESTAMP(CURRENT_TIMESTAMP(3)),'.','')) + 0;
SET b_time_tick = b_current_time - 1582136402000;
SET f_random = RAND();
SET b_res = b_time_tick * POWER(2, i_work_id_big_length + i_seq_big_length) + i_work_id * POWER(2, i_seq_big_length) + (5 + ROUND((POWER(2, i_seq_big_length) - 1) * f_random, 0));
RETURN b_res;
END //
DELIMITER ;
使用
select getSnowId() ;
时间戳
获取当前的时间戳
-- 获取13位时间戳 和 10位时间戳
SELECT CAST(UNIX_TIMESTAMP(NOW(3)) * 1000 AS UNSIGNED) as timestamp13 ,CAST(UNIX_TIMESTAMP(NOW(3)) AS UNSIGNED) timestamp10
随机数
生成指定范围的随机数
-- 例如:生成1-2之间的数: SELECT FLOOR(1 + (RAND() * 20))
SELECT FLOOR(开始范围 + (RAND() * 结束步长))
生成随机字符
-- 例如:从ABCDEF中随机获取一个字符
-- SELECT SUBSTRING('ABCDEF', FLOOR(1 + (RAND() * 6)), 1)
-- 若要获取多个使用concat拼接重复获取一个的值
SELECT SUBSTRING(指定字符串, FLOOR(1 + (RAND() * 字符串长度)), 1)
随机从指定表中查询指定数量的数据
select * from 表名 order by rand() limit 指定的数量;