1. 树叶云首页
  2. 技术资讯

常用数据库 SQL 命令详解（下）

运维••技术资讯

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

一、函数

1.1 常用函数列表

1.2 自定义函数语法介绍

(1) 创建函数

CREATEFUNCTIONfn_name(func_parameter[,...])
RETURNStype
[characteristic...]
routine_body

参数说明：

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

(2) 编辑函数

ALTERFUNCTIONfn_name [characteristic...]

参数说明：

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

(3) 删除函数

DROPFUNCTION  [IFEXISTS]  fn_name;

参数说明：

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

(4) 查看函数语法

SHOWFUNCTIONSTATUS [LIKE'pattern']

参数说明：

  • pattern：函数名称

示例：

SHOWFUNCTIONSTATUSLIKE'user_function';

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

SHOWCREATEFUNCTIONfn_name;

参数说明：

  • fn_name：自定义函数名称

1.3 实例操作介绍

(1) 创建一个表

CREATETABLE`t_user` (
`user_id`int(10) NOTNULLAUTO_INCREMENTCOMMENT'用户id,作为主键',
`user_name`varchar(5) DEFAULTNULLCOMMENT'用户名',
`age`int(3) DEFAULTNULLCOMMENT'年龄',
PRIMARYKEY (`user_id`)
) ENGINE=InnoDBAUTO_INCREMENT=1DEFAULTCHARSET=utf8;

(2) 插入数据

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

(3) 创建函数

--创建一个函数
DELIMITER$$

--开始创建函数
CREATEFUNCTIONuser_function(v_idINT)
RETURNSVARCHAR(50)
READSSQLDATA
DETERMINISTIC
BEGIN
--定义变量
DECLAREuserNameVARCHAR(50);
--给定义的变量赋值
SELECTuser_nameINTOuserNameFROMt_user
WHEREuser_id=v_id;
--返回函数处理结果
RETURNuserName;
END;

--函数创建定界符
DELIMITER ;

(4) 调用函数

//查询用户ID为1的信息
SELECTuser_function(1);

(5) 删除函数

DROPFUNCTIONIFEXISTSuser_function;

二、存储过程

2.1 创建语法

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

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

DELIMITER$$
或
DELIMITER//

参数说明：

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

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

DELIMITER$$
CREATEPROCEDUREuser_procedure(INv_idint,OUTuserNamevarchar(255))  
BEGIN
SELECTuser_nameasuserNameFROMt_userWHEREuser_id=v_id;
END$$
DELIMITER ;

2.2 存储过程调用

--@out为输出参数
CALLuser_procedure(1, @out);

输出结果：

张三

2.3 存储过程删除

DROPPROCEDURE [IFEXISTS]  proc_name;

删除示例：

DROPPROCEDUREIFEXISTSuser_procedure;

2.4 存储过程和函数的区别

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

三、触发器

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

3.1 创建触发器

定义语法：

CREATE
    [DEFINER= { user|CURRENT_USER }]
TRIGGERtrigger_name
trigger_timetrigger_event
ONtbl_nameFOREACHROW
  [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表里插入当前时间。

CREATETRIGGERtrig1AFTERINSERT
ONt_userFOREACHROW
INSERTINTOt_timeVALUES(NOW());

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

CREATETRIGGER触发器名BEFORE|AFTER触发事件
ON表名FOREACHROW
BEGIN
执行语句列表
END;

示例如下：

DELIMITER//
CREATETRIGGERtrig2AFTERINSERT
ONt_userFOREACHROW
BEGIN
INSERTINTOt_timeVALUES(NOW());
INSERTINTOt_timeVALUES(NOW());
END//
DELIMITER ;

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

3.2 查询触发器

查询所有触发器：

SHOWTRIGGERS;

查询指定的触发器：

select*frominformation_schema.triggerswheretrigger_name='trig1';

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

3.3 删除触发器

DROPTRIGGER [IFEXISTS] [schema_name.]trigger_name

示例如下：

DROPTRIGGERIFEXISTStrig1

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

3.4 总结

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

四、序列

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

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

4.1 自增序列

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

#创建一个表test_db，字段内容为id,name
createtabletest_db(idint,namechar(10));

# 设置id主键
altertabletest_dbaddprimarykey(id);

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

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

4.2 程序自定义

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

实现过程也很简单！

创建一个序列表：

CREATETABLE`sequence` (
`name`varchar(50) COLLATEutf8_binNOTNULLCOMMENT'序列的名字',
`current_value`int(11) NOTNULLCOMMENT'序列的当前值',
`increment`int(11) NOTNULLDEFAULT'1'COMMENT'序列的自增值',
PRIMARYKEY (`name`)
) ENGINE=InnoDBDEFAULTCHARSET=utf8COLLATE=utf8_bin;

创建–取当前值的函数：

BEGIN
DECLAREvalueINTEGER; 
SETvalue=0; 
SELECTcurrent_valueINTOvalue
FROMsequence
WHEREname=seq_name; 
RETURNvalue; 
END

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

DROPFUNCTIONIFEXISTSnextval; 
DELIMITER$
CREATEFUNCTIONnextval (seq_nameVARCHAR(50)) 
RETURNSINTEGER
LANGUAGESQL
DETERMINISTIC
CONTAINSSQL
SQLSECURITYDEFINER
COMMENT''
BEGIN
UPDATEsequence
SETcurrent_value=current_value+increment
WHEREname=seq_name; 
RETURNcurrval(seq_name); 
END
$
DELIMITER ;

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

DROPFUNCTIONIFEXISTSsetval; 
DELIMITER$
CREATEFUNCTIONsetval (seq_nameVARCHAR(50), valueINTEGER) 
RETURNSINTEGER
LANGUAGESQL
DETERMINISTIC
CONTAINSSQL
SQLSECURITYDEFINER
COMMENT''
BEGIN
UPDATEsequence
SETcurrent_value=value
WHEREname=seq_name; 
RETURNcurrval(seq_name); 
END
$
DELIMITER ;

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

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

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

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

#查询指定sequence的下一个值
SELECTNEXTVAL('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可以直接当成函数调用即可！

selectuuid();

4.4 UUID_SHORT() 函数

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

selectUUID_SHORT();

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

五、用户权限

5.1 用户管理

查询所有用户：

select*frommysql.user;

创建用户：

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

更改用户密码：

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

删除用户：

# 格式
DROPUSER'username'@'host';
# 例子，删除用户名为admin的用户
DROPUSER'admin'@'localhost';

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

#刷新操作使其生效
flushprivileges

5.2 用户权限管理

查询用户权限：

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

给用户授予某种权限：

# 格式
GRANTprivilegesONdatabasename.tablenameTO'username'@'host'

说明：

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

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

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

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

grantallprivilegesontest_db.*to'testuser'@'localhost';

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

grantallprivilegesontest_db.*to'testuser'@'localhost';

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

grantallprivilegesontest_db.user_infoto'testuser'@'localhost';

(2) 设置用户操作权限

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

grantallprivilegeson*.*to'testuser'@'localhost';

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

grantselecton*.*to'testuser'@'localhost';

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

grantselect,insert,update,deleteon*.*to'testuser'@'localhost';

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

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

grantallprivilegeson*.*to'testuser'@'192.168.1.100';

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

# bind-address=127.0.0.1

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

GRANTprivilegesONdatabasename.tablenameTO'username'@'host'WITHGRANTOPTION;

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

5.3 关于root用户的访问设置

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

grantallprivilegeson*.*to'root'@'%'identifiedby'123456';

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

grantallprivilegeson*.*to'root'@'localhost'identifiedby'123456';

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

flushprivileges;

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

#例如，创建一个admin用户，密码为admin
grantallprivilegeson*.*to'admin'@'%'identifiedby'admin';

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

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

六、总结

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

文章来源网络，作者：运维，如若转载，请注明出处：https://shuyeidc.com/wp/304870.html<

赞 (0)
运维的头像运维
00
上一篇2025-05-25 22:25
下一篇 2025-05-25 22:27

相关推荐

  • 如何制作个人主题技术资讯

    个人主题怎么制作？

    制作个人主题是一个将个人风格、兴趣或专业领域转化为视觉化或结构化内容的过程，无论是用于个人博客、作品集、社交媒体账号还是品牌形象，核心都是围绕“个人特色”展开，以下从定位、内容规划、视觉设计、技术实现四个维度,详细拆解制作个人主题的完整流程，明确主题定位：找到个人特色的核心主题定位是所有工作的起点，需要先回答……

    运维的头像运维
    2025-11-20
    0
  • 如何管理社群营销技术资讯

    社群营销管理关键是什么？

    社群营销的核心在于通过建立有温度、有价值、有归属感的社群，实现用户留存、转化和品牌传播，其管理需贯穿“目标定位-内容运营-用户互动-数据驱动-风险控制”全流程,以下从五个维度展开详细说明：明确社群定位与目标社群管理的首要任务是精准定位，需明确社群的核心价值（如行业交流、产品使用指导、兴趣分享等）、目标用户画像……

    运维的头像运维
    2025-11-20
    0
  • 香港公司如何网站备案技术资讯

    香港公司网站备案需要什么材料？

    香港公司进行网站备案是一个涉及多部门协调、流程相对严谨的过程，尤其需兼顾中国内地与香港两地的监管要求，由于香港公司注册地与中国内地不同，其网站若主要服务内地用户或使用内地服务器，需根据服务器位置、网站内容性质等，选择对应的备案路径（如工信部ICP备案或公安备案），以下从备案主体资格、流程步骤、材料准备、注意事项……

    运维的头像运维
    2025-11-20
    0
  • 如何企业上云推广技术资讯

    如何企业上云推广

    企业上云已成为数字化转型的核心战略,但推广过程中需结合行业特性、企业痛点与市场需求，构建系统性、多维度的推广体系，以下从市场定位、策略设计、执行落地及效果优化四个维度，详细拆解企业上云推广的实践路径，精准定位：明确目标企业与核心价值企业上云并非“一刀切”的方案，需先锁定目标客户群体，提炼差异化价值主张，客户分层……

    运维的头像运维
    2025-11-20
    0
  • PS如何设计搜索框技术资讯

    PS设计搜索框的实用技巧有哪些？

    在PS中设计一个美观且功能性的搜索框需要结合创意构思、视觉设计和用户体验考量，以下从设计思路、制作步骤、细节优化及交互预览等方面详细说明，帮助打造符合需求的搜索框，设计前的规划明确使用场景：根据网站或APP的整体风格确定搜索框的调性，例如极简风适合细线条和纯色，科技感适合渐变和发光效果，电商类则可能需要突出搜索……

    运维的头像运维
    2025-11-20
    0

发表回复

您的邮箱地址不会被公开。必填项已用 * 标注