常用数据库 SQL 命令详解(下)

数据库 MySQL
本文主要围绕 Mysql 中常用的语法进行一次梳理和介绍,这些语法大部分也同样适用于其他的数据库。

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

一、函数

1.1 常用函数列表

1.2 自定义函数语法介绍

(1) 创建函数

CREATE FUNCTION fn_name(func_parameter[,...])
RETURNS type
[characteristic...]
routine_body

参数说明:

  • fn_name:自定义函数名称
  • func_parameter: param_name type
  • type: 任何mysql支持的类型
  • characteristic: LANGUAGE SQL
  • routine_body: 函数体

(2) 编辑函数

ALTER FUNCTION fn_name [characteristic...]

参数说明:

  • fn_name:自定义函数名称
  • func_parameter: param_name type
  • characteristic: LANGUAGE SQL

(3) 删除函数

DROP FUNCTION  [IF EXISTS]  fn_name;

参数说明:

  • fn_name:自定义函数名称
  • func_parameter: param_name type

(4) 查看函数语法

SHOW FUNCTION STATUS [LIKE 'pattern']

参数说明:

  • pattern:函数名称

示例:

SHOW FUNCTION STATUS LIKE 'user_function';

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

SHOW CREATE FUNCTION fn_name;

参数说明:

  • fn_name:自定义函数名称

1.3 实例操作介绍

(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;

二、存储过程

2.1 创建语法

CREATE PROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类形...])

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

DELIMITER $$

DELIMITER //

参数说明:

  • IN 输入参数:表示该参数的值必须在调用存储过程时指定,在存储过程中修改该参数的值不能被返回,为默认值
  • OUT 输出参数:该值可在存储过程内部被改变,并可返回
  • INOUT 输入输出参数:调用时指定,并且可被改变和返回

创建一个查询用户信息的存储过程示例:

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为输出参数
CALL user_procedure(1, @out);

输出结果:

张三

2.3 存储过程删除

DROP PROCEDURE [IF EXISTS]  proc_name;

删除示例:

DROP PROCEDURE IF EXISTS  user_procedure;

2.4 存储过程和函数的区别

  • 函数只能通过return语句返回单个值或者表对象。而存储过程不允许执行return,但是通过out参数返回多个值。
  • 函数是可以嵌入在sql中使用的,可以在select中调用,而存储过程不行。
  • 函数限制比较多,比如不能用临时表,只能用表变量,还有一些函数都不可用等等,而存储过程的限制相对就比较少
  • 一般来说,存储过程实现的功能要复杂一点,而函数的实现的功能针对性比较强。
  • 当存储过程和函数被执行的时候,SQL Manager会到procedure cache中去取相应的查询语句,如果在procedure cache里没有相应的查询语句,SQL Manager就会对存储过程和函数进行编译。

三、触发器

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

3.1 创建触发器

定义语法:

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 }
trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

参数说明:

  • FOR EACH ROW:表示任何一条记录上的操作满足触发事件都会触发该触发器,也就是说触发器的触发频率是针对每一行数据触发一次。
  • trigger_time:BEFORE和AFTER参数指定了触发执行的时间,在事件之前或是之后。
  • tigger_event详解:
  • INSERT 型触发器:插入某一行时激活触发器,可能通过INSERT、LOAD DATA、REPLACE 语句触发(LOAD DAT语句用于将一个文件装入到一个数据表中,相当与一系列的INSERT操作);
  • UPDATE型触发器:更改某一行时激活触发器,可能通过UPDATE语句触发;
  • DELETE型触发器:删除某一行时激活触发器,可能通过DELETE、REPLACE语句触发。
  • trigger_order:是MySQL5.7之后的一个功能,用于定义多个触发器,使用follows(尾随)或precedes(在…之先)来选择触发器执行的先后顺序。

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

CREATE TRIGGER trig1 AFTER INSERT
ON t_user FOR EACH ROW
INSERT INTO t_time VALUES(NOW());

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

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
ON 表名 FOR EACH ROW
BEGIN
执行语句列表
END;

示例如下:

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

一旦插入成功,就会执行BEGIN ...END语句!

3.2 查询触发器

查询所有触发器:

SHOW TRIGGERS;

查询指定的触发器:

select * from information_schema.triggers where trigger_name='trig1';

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

3.3 删除触发器

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

示例如下:

DROP TRIGGER IF EXISTS trig1

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

3.4 总结

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

四、序列

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

  • 自增序列
  • 程序自定义
  • UUID() 函数
  • UUID_SHORT() 函数

4.1 自增序列

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

#创建一个表test_db,字段内容为id,name
create table test_db(id int,name char(10));

# 设置id主键
alter table test_db add primary key(id);

# 将id主键设置为自增长模式
alter table test_db modify id int auto_increment;

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

4.2 程序自定义

当然,为了避免出现这种情况,有的大神就自己单独创建了一张自增序列表,单独维护,这样就不会出现在分表的时候出现相同的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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

创建–取当前值的函数:

BEGIN 
DECLARE value INTEGER;
SET value = 0;
SELECT current_value INTO value
FROM sequence
WHERE name = seq_name;
RETURN value;
END

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

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
$
DELIMITER ;

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

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
$
DELIMITER ;

最后,直接通过函数调用,测试如下:

# 添加一个sequence名称和初始值,以及自增幅度
INSERT INTO sequence VALUES ('testSeq', 0, 1);

#设置指定sequence的初始值
SELECT SETVAL('testSeq', 10);

#查询指定sequence的当前值
SELECT CURRVAL('testSeq');

#查询指定sequence的下一个值
SELECT NEXTVAL('testSeq');

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

4.3 UUID() 函数

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

aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee

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

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

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

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

select uuid();

4.4 UUID_SHORT() 函数

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

select UUID_SHORT();

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

五、用户权限

5.1 用户管理

查询所有用户:

select * from mysql.user;

创建用户:

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

更改用户密码:

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

删除用户:

# 格式
DROP USER 'username'@'host';
# 例子,删除用户名为admin的用户
DROP USER 'admin'@'localhost';

最后刷新操作,使操作生效:

#刷新操作使其生效
flush privileges

5.2 用户权限管理

查询用户权限:

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

给用户授予某种权限:

# 格式
GRANT privileges ON databasename.tablename TO 'username'@'host'

说明:

  • privileges:用户的操作权限,如SELECT,INSERT,UPDATE、DELETE等,如果要授予所的权限则使用ALL
  • databasename:数据库名
  • tablename:表名,如果要授予该用户对所有数据库和表的相应操作权限则可用*表示,如*.*
  • username:用户名
  • host:可以访问的域名

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

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

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

grant all privileges on test_db.* to 'testuser'@'localhost';

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

grant all privileges on test_db.* to 'testuser'@'localhost';

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

grant all privileges on test_db.user_info to 'testuser'@'localhost';

(2) 设置用户操作权限

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

grant all privileges on *.* to 'testuser'@'localhost';

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

grant select on *.* to 'testuser'@'localhost';

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

grant select,insert,update,delete on *.* to 'testuser'@'localhost';

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

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

grant all privileges on *.* to 'testuser'@'192.168.1.100';

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

# bind-address = 127.0.0.1

注意:用以上命令授权的用户不能给其它用户授权,如果想让该用户可以授权,用以下命令!

GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;

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

5.3 关于root用户的访问设置

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

grant all privileges on *.* to 'root'@'%'  identified by '123456';

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

grant all privileges on *.* to 'root'@'localhost'  identified by '123456';

最后使用如下命令,使其生效:

flush privileges;

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

#例如,创建一个admin用户,密码为admin
grant all privileges on *.* to 'admin'@'%' identified by 'admin';

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

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

六、总结

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

责任编辑:赵宁宁 来源: Java极客技术
相关推荐

2022-03-25 09:04:01

Mysql数据库

2011-01-06 09:28:19

SQL语句

2019-04-25 15:35:37

MySQL数据库运维Linux

2021-03-18 08:20:19

SQLServer数据库SQL

2011-03-31 16:09:56

Mysql数据库

2009-08-07 16:19:00

C#下数据库编程

2010-10-20 09:41:37

卸载Oracle

2009-08-07 16:19:00

C#下数据库编程

2010-10-22 15:42:59

SQL Server创

2011-03-03 15:54:27

MySQL数据库管理

2009-08-11 09:58:22

Linux命令行Linux命令svn命令

2010-09-08 16:03:57

SQL临时表数据库

2011-08-11 09:12:31

SQL Server nolock

2010-03-16 10:12:40

SQL Server

2011-08-16 13:17:29

2011-08-25 13:41:50

SQL Server 变更跟踪

2011-03-31 09:30:27

SQL Server数管理SQL

2010-07-13 10:43:26

SQL Server数

2010-11-16 11:27:53

SQL Azure数据

2010-07-22 14:46:41

SQL Server
点赞
收藏

51CTO技术栈公众号