在上篇《​​常用数据库 SQL 命令详解(上)​​》文章中，常用主要介绍上半部分内容，数据今天我们来介绍下半部分内容！命令

一、详解下函数

(1) 创建函数

CREATE FUNCTION fn_name(func_parameter[,常用...])

RETURNS type

[characteristic...]

routine_body

参数说明：

(2) 编辑函数

ALTER FUNCTION fn_name [characteristic...]

参数说明：

(3) 删除函数

DROP FUNCTION [IF EXISTS] fn_name;

参数说明：

(4) 查看函数语法

SHOW FUNCTION STATUS [LIKE pattern]

参数说明：

示例：

SHOW FUNCTION STATUS LIKE user_function;

(5) 查看函数的定义语法

SHOW CREATE FUNCTION fn_name;

参数说明：

(1) 创建一个表

CREATE TABLE `t_user` (

`user_id` int(10) NOT NULL AUTO_INCREMENT COMMENT 用户id,作为主键,

`user_name` varchar(5) DEFAULT NULL COMMENT 用户名,

`age` int(3) DEFAULT NULL COMMENT 年龄,

PRIMARY KEY (`user_id`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

(2) 插入数据

INSERT INTO t_user (user_name, age)

VALUES(张三,24),(李四,25),(王五,26),(赵六,27);

(3) 创建函数

-- 创建一个函数

DELIMITER $$

-- 开始创建函数

CREATE FUNCTION user_function(v_id INT)

RETURNS VARCHAR(50)

READS SQL DATA

DETERMINISTIC

BEGIN

-- 定义变量

DECLARE userName VARCHAR(50);

-- 给定义的变量赋值

SELECT user_name INTO userName FROM t_user

WHERE user_id = v_id;

-- 返回函数处理结果

RETURN userName;

END;

-- 函数创建定界符

DELIMITER ;

(4) 调用函数

//查询用户ID为1的信息

SELECT user_function(1);

(5) 删除函数

DROP FUNCTION IF EXISTS user_function;

二、存储过程

过程与创建函数类似，数据其中的命令声明语句结束符，可以自定义:

DELIMITER $$

或

参数说明：

创建一个查询用户信息的详解下存储过程示例：

DELIMITER $$

CREATE PROCEDURE user_procedure(IN v_id int,OUT userName varchar(255))

BEGIN

SELECT user_name as userName FROM t_user WHERE user_id = v_id;

END $$

DELIMITER ;2.2 存储过程调用-- @out为输出参数

输出结果：

删除示例：

三、触发器

触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。

定义语法：

CREATE

[DEFINER = { user | CURRENT_USER }]

TRIGGER trigger_name

trigger_time trigger_event

ON tbl_name FOR EACH ROW

[trigger_order]

trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

参数说明：

示例，创建了一个名为trig1的触发器，一旦在t_user表中有插入动作，就会自动往t_time表里插入当前时间。

CREATE TRIGGER trig1 AFTER INSERT

ON t_user FOR EACH ROW

创建有多个执行语句的触发器语法：

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件

ON 表名 FOR EACH ROW

BEGIN

执行语句列表

示例如下：

DELIMITER //

CREATE TRIGGER trig2 AFTER INSERT

ON t_user FOR EACH ROW

BEGIN

INSERT INTO t_time VALUES(NOW());

INSERT INTO t_time VALUES(NOW());

END//

一旦插入成功，就会执行BEGIN ...END语句！

查询所有触发器：

查询指定的触发器：

所有触发器信息都存储在information_schema数据库下的triggers表中，可以使用SELECT语句查询，如果触发器信息过多，最好通过TRIGGER_NAME字段指定查询。

示例如下：

删除触发器之后最好使用上面的方法查看一遍。

触发器尽量少的使用，因为不管如何，它还是很消耗资源，如果使用的香港云服务器话要谨慎的使用，确定它是非常高效的：触发器是针对每一行的;对增删改非常频繁的表上切记不要使用触发器，因为它会非常消耗资源。

四、序列

在 MySQL 中，可以有如下几种途径实现唯一值：

在mysql中，一般我们可以给某个主键字段设置为自增模式，例如：

#创建一个表test_db，字段内容为id,name

create table test_db(id int,name char(10));

# 设置id主键

alter table test_db add primary key(id);

# 将id主键设置为自增长模式

这种模式，在单库单表的时候，没啥问题，但是如果要对test_db表进行分库分表，这个时候问题就来了，如果水平分库，这个时候向test_db_1、test_db_2中插入数据，就会出现相同的ID!

当然，为了避免出现这种情况，有的大神就自己单独创建了一张自增序列表，单独维护，这样就不会出现在分表的时候出现相同的ID！

实现过程也很简单！

创建一个序列表：

CREATE TABLE `sequence` (

`name` varchar(50) COLLATE utf8_bin NOT NULL COMMENT 序列的名字,

`current_value` int(11) NOT NULL COMMENT 序列的当前值,

`increment` int(11) NOT NULL DEFAULT 1 COMMENT 序列的自增值,

PRIMARY KEY (`name`)

创建–取当前值的函数：

BEGIN

DECLARE value INTEGER;

SET value = 0;

SELECT current_value INTO value

FROM sequence

WHERE name = seq_name;

RETURN value;

创建–取下一个值的函数：

DROP FUNCTION IF EXISTS nextval;

DELIMITER $

CREATE FUNCTION nextval (seq_name VARCHAR(50))

RETURNS INTEGER

LANGUAGE SQL

DETERMINISTIC

CONTAINS SQL

SQL SECURITY DEFINER

COMMENT

BEGIN

UPDATE sequence

SET current_value = current_value + increment

WHERE name = seq_name;

RETURN currval(seq_name);

END

$

创建–更新当前值的函数：

DROP FUNCTION IF EXISTS setval;

DELIMITER $

CREATE FUNCTION setval (seq_name VARCHAR(50), value INTEGER)

RETURNS INTEGER

LANGUAGE SQL

DETERMINISTIC

CONTAINS SQL

SQL SECURITY DEFINER

COMMENT

BEGIN

UPDATE sequence

SET current_value = value

WHERE name = seq_name;

RETURN currval(seq_name);

END

$

最后，直接通过函数调用，测试如下：

# 添加一个sequence名称和初始值，以及自增幅度

INSERT INTO sequence VALUES (testSeq, 0, 1);

#设置指定sequence的初始值

SELECT SETVAL(testSeq, 10);

#查询指定sequence的当前值

SELECT CURRVAL(testSeq);

#查询指定sequence的下一个值

这方案，某种情况下解决了分表的问题，但是如果分库还是会出现相同的ID！

UUID 基于 16 进制，由 32 位小写的 16 进制数字组成，如下：

比如d0c754a8-178e-11eb-ae3d-2a7bea22ed3d就是一个典型的 UUID。

在 MySQL 的UUID()函数中，前三组数字从时间戳中生成，第四组数字暂时保持时间戳的唯一性，第五组数字是一个IEEE 802节点标点值，保证空间唯一。

使用 UUID() 函数，可以生成时间、空间上都独一无二的值。据说只要是使用了 UUID，都不可能看到两个重复的 UUID 值。当然，这个只是在理论情况下。

使用方法也很简单，在sql可以直接当成函数调用即可！

在 MySQL 5.1 之后的版本，提供UUID_SHORT()函数，生成一个64位无符号整数，在java中可以用Long类型接受。另外，需要注意的是，server_id 的范围必须为0-255，并且不支持 STATEMENT模式复制，否则有可能会产生重复的ID：

同时，需要注意的是，UUID_SHORT()返回的是unsigned long long类型，在字段类型设置的时候，一定要勾选无符号类型，否则有可能生成的ID超过Long类型最大长度!

五、用户权限

查询所有用户：

创建用户：

# 格式

CREATE USER username@host IDENTIFIED BY password;# 例子，创建一个用户名为admin,密码123456，可以本地访问的用户

更改用户密码：

# 格式

SET PASSWORD FOR username@host = PASSWORD(newpassword);# 例子，将用户名admin,密码修改为456789，可以本地访问的用户

删除用户：

# 格式

DROP USER username@host;# 例子，删除用户名为admin的用户

最后刷新操作，使操作生效：

#刷新操作使其生效

查询用户权限：

# 格式

SHOW GRANTS FOR username@host# 查询用户名为 root@%的权限信息

给用户授予某种权限：

# 格式

说明：

在给其他授权前，请先用管理员账户登录！

(1) 设置用户访问数据库权限

设置用户testuser，只能访问数据库test_db，其他数据库均不能访问：

设置用户testuser，可以访问mysql上的所有数据库：

设置用户testuser，只能访问数据库testuser的表user_info，数据库中的其他表均不能访问：

(2) 设置用户操作权限

设置用户testuser，拥有所有的操作权限，也就是管理员：

设置用户testuser，只拥有【查询】操作权限：

设置用户testuser，只拥有【查询/插入/修改/删除】操作权限：

(3) 设置用户远程访问权限

设置用户testuser，只能在客户端IP为192.168.1.100上才能远程访问mysql：

设置所有用户可以远程访问mysql，修改my.cnf配置文件，将bind-address = 127.0.0.1前面加#注释掉：

注意：用以上命令授权的用户不能给其它用户授权，如果想让该用户可以授权，用以下命令！

在结尾加上WITH GRANT OPTION就可以了！

可以使用如下命令，来一键设置root用户的密码，同时拥有所有的权限并设置为远程访问！

如果想关闭root用户远程访问权限，使用如下命令即可！

最后使用如下命令，使其生效：

创建用户并进行授权，也可以使用如下快捷命令：

#例如，创建一个admin用户，密码为admin

grant all privileges on *.* to admin@% identified by admin;

#刷新MySQL的系统权限相关表方可生效

最后需要注意的是：mysql8，使用强校验，所以，如果密码过于简单，会报错，密码尽量搞复杂些！

六、总结

本文主要围绕 Mysql 中常用的语法进行一次梳理和介绍，这些语法大部分也同样适用于其他的数据库，例如 oracle、sqlserver、postgres 等等，在数据操作栏，除了分页函数以外，基本都是通用的！