在上一篇《??常用数据库 SQL 命令详解(上)??》中，我们主要介绍了前半部分的内容。今天我们就来介绍下半部分的内容！一、函数1.1常用函数列表1.2自定义函数语法介绍（一）创建函数CREATEFUNCTIONfn_name(func_parameter[,...])RETURNStype[characteristic...]routine_body参数说明：fn_name:自定义函数名func_parameter:param_nametypetype:mysql支持的任意类型characteristic:LANGUAGESQLroutine_body:函数体(2)编辑函数ALTERFUNCTIONfn_name[characteristic...]参数说明:fn_name:自定义函数名func_parameter:param_nametypecharacteristic:LANGUAGESQL(3)删除函数DROPFUNCTION[如果存在]fn_name；参数说明：fn_name：自定义函数名func_parameter：param_nametype(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'userid,asprimarykey',`user_name`varchar(5)DEFAULTNULLCOMMENT'username',`age`int(3)DEFAULTNULLCOMMENT'age',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)READSSQLDATADETERMINISTICBEGIN--定义变量DECLAREuserNameVARCHAR(50);--给出定义SELECTuser_nameINTOuserNameFROMt_userWHEREuser_id=v_id;--返回函数处理结果RETURNuserName;END;--函数创建分隔符DELIMITER;(4)调用函数//查询用户ID1的信息SELECTuser_function(1);(5)删除函数DROPFUNCTIONIFEXISTSuser_function;2.存储过程2.1创建语法CREATEPROCEDURE存储过程名([[IN|OUT|INOUT]参数名数据类型...])procedureandcreatefunction同样声明语句的结束符可以自定义：DELIMITER$$或者DELIMITER//参数说明：IN输入参数：表示调用存储过程时必须指定参数的值，不能在存储过程中修改返回，为默认值OUT输出参数：可更改的值存储过程内部，可返回INOUT输入输出参数：调用时指定，可更改返回创建查询用户信息的存储过程示例：DELIMITER$$CREATEPROCEDUREuser_procedure(INv_idint,OUTuserNamevarchar(255））开始从t_user中选择user_name作为userName，其中user_id=v_id；END$$DELIMITER;2.2存储过程调用--@out为输出参数CALLuser_proced乌尔（1，@out）；输出结果：张三2.3存储过程deleteDROPPROCEDURE[IFEXISTS]proc_name;删除示例：DROPPROCEDUREIFEXISTSuser_procedure；2.4存储过程和函数的区别函数只能通过return语句返回单个值或者表对象和存储过程不允许返回，而是通过out参数返回多个值。函数可以嵌入到sql中，可以在select中调用，但是存储过程不可以。函数的限制比较多，比如不能使用临时表，只能使用表变量，还有一些函数不可用等等，而存储过程的限制相对较少。一般来说，存储过程实现的功能比较复杂，而功能的实现也比较有针对性。当存储过程和函数执行时，SQLManager会去过程缓存中获取对应的查询语句。如果过程缓存中没有对应的查询语句，SQLManager将编译存储过程和函数。3.触发器触发器是与表相关的数据库对象，当满足定义的条件时触发，并执行触发器中定义的一组语句。3.1创建触发器定义语法：CREATE[DEFINER={user|CURRENT_USER}]TRIGGERtrigger_nametrigger_timetrigger_eventONtbl_nameFOREACHROW[trigger_order]trigger_bodytrigger_time:{之前|在}trigger_event之后：{插入|更新|other_trigger_name参数说明：FOREACHROW：表示对一条记录的任何满足触发事件的操作都会触发触发器，也就是说触发器的触发频率是每行数据触发一次。trigger_time：BEFORE和AFTER参数指定触发器执行时间，在事件之前或之后。tigger_event详解：INSERT类型触发器：插入一行时触发触发器，可能由INSERT、LOADDATA、REPLACE语句触发（LOADDAT语句用于将文件加载到数据表中，相当于一系列INSERT操作）；UPDATE型触发器：某行发生变化时激活触发器，可能由UPDATE语句触发；DELETE型触发器：删除某一行时激活触发器，可能由DELETE、REPLACE语句触发。trigger_order：是MySQL5.7之后的函数，用于定义多个触发器，使用follows（尾随）或precedes（before...）来选择触发器执行的顺序。例如，创建了一个名为trig1的触发器。一旦t_user表有insert动作，就会自动将当前时间插入到t_time表中。在插入t_user之后创建触发器trig1为每个ROWINSERTINTOt_timeVALUES(NOW());创建多条执行语句的触发器语法：CREATETRIGGER触发器名BEFORE|AFTER触发器事件ON表名FOREACHROWBEGIN执行语句列表END;示例如下：DELIMITER//CREATETRIGGERtrig2AFTERINSERTONt_userFOREACHBEGININSERTINTOt_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小结触发器尽量少用，因为不管怎样，它还是要消耗资源的。如果你使用它，你应该小心使用它以确保它非常高效：触发器是针对每一行的；对于频繁增删改查的表，切记不要使用触发器，因为它非常耗费资源。4.序列在MySQL中，有几种实现唯一值的方式：自增序列程序自定义UUID()函数UUID_SHORT()函数4.1自增序列在mysql中，一般我们可以设置一个主键字段为自增方式，例如：#创建表test_db，字段内容为id,namecreatetabletest_db(idint,namechar(10));#setidprimarykeyaltertabletest_dbaddprimarykey(id);#setidprimarykeyas自增长模式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;create——获取当前值的函数：BEGINDECLAREvalueINTEGER；设置值=0;SELECTcurrent_valueINTOvalueFROMsequenceWHEREname=seq_name;返回值；END创建-获取下一个值的函数：DROPFUNCTIONIFEXISTSn扩展；DELIMITER$CREATEFUNCTIONnextval(seq_nameVARCHAR(50))RETURNSINTEGERLANGUAGESQLDETERMINISTICCONTAINSSQLSQLSECURITYDEFINERCOMMENT''BEGINUPDATEsequenceSETcurrent_value=current_value+incrementWHEREname=seq_name;DETURN_curr(;创建–更新当前值的函数：DROPFUNCTIONIFEXISTSsetval;DELIMITER$CREATEFUNCTIONsetval(seq_nameVARCHAR(50),valueINTEGER)RETURNSINTEGERLANGUAGESQLDETERMINISTICCONTAINSSQLSQLSECURITYDEFINERCOMMENT''BEGINUPDATEsequenceSETvaluecurrent_value=WHEREname=seq_name;返回currval(seq_name);END$DELIMITER;最后直接通过函数调用，测试如下：#添加序列名，初值，自增INSERTINTOsequenceVALUES('testSeq',0,1);#设置指定序列的初始值SELECTSETVAL('testSeq',10);#查询指定序列的当前值SELECTCURRVAL('testSeq');#查询指定序列的下一个值SELECTNEXTVAL('testSeq');该方案在某些情况下分表的问题解决了，但是如果数据库是分库的，还是会出现相同的ID！4.3UUID()函数UUID是基于十六进制的，由32个小写十六进制数组成，如下：aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeeeee例如d0c754a8-178e-11eb-ae3d-2a7bea22ed3d就是典型的MySQL中的UUID的UUID()函数中，前三组数字由时间戳生成，第四组数字暂时保持时间戳的唯一性，第五组数字是一个IEEE802节点标点值，保证唯一性空间。使用UUID()函数，可以生成一个在时间和空间上唯一的值。据说只要使用UUID，就不可能看到两个重复的UUID值。当然，这只是理论上的情况。使用方法也很简单，直接在sql中作为函数调用即可！selectuuid();4.4UUID_SHORT()函数在MySQL5.1之后的版本中提供了UUID_SHORT()函数来生成一个64位的无符号整数，可以被java中的Long类型所接受。另外需要注意的是server_id的范围必须是0-255，不支持STATEMENT方式复制，否则可能会产生重复的ID：selectUUID_SHORT();同时需要注意的是，UUID_SHORT()返回的是Unsignedlonglong类型，在设置字段类型的时候一定要勾选unsigned类型，否则生成的ID可能会超过Long类型的最大长度！5、用户权限5.1用户管理查询所有用户：select*frommysql.user;创建用户：#格式CREATEUSER'用户名'@'主机'IDENTIFIEDBY'密码';#例如，创建一个名为admin，密码123456的用户，可以本地访问CREATEUSER'admin'@'localhost'IDENTIFIEDBY'123456';更改用户密码：#格式SETPASSWORDFOR'username'@'host'=PASSWORD('newpassword');#例子，修改用户名admin，密码为456789，本地可以访问的用户SETPASSWORDFOR'admin'@'localhost'=PASSWORD("456789");删除用户：#格式DROPUSER'用户名'@'主机';#例如删除名为admin的用户DROPUSER'admin'@'localhost';最后刷新操作使操作生效：#刷新操作使其生效flushprivileges5.2用户权限管理查询用户权限：#格式SHOWGRANTSFOR'username'@'host'#查询用户名的权限信息'root'@'%'SHOWGRANTSFOR'root'@'%'授予用户某些权限：#格式GRANTprivilegesONdatabasename.tablenameTO'username'@'host'说明：privileges：用户的操作权限，如SELECT，INSERT、UPDATE、DELETE等，如果要授予所有权限，使用ALLdatabasename：数据库名tablename：表名，如果要授予用户访问所有数据库数据库和表对应的操作权限可以用*表示，比如*.*username:用户名host:可以访问的域名在授权给他人之前，请先用管理员账号登录！(1)设置用户访问数据库权限设置用户testuser，只能访问数据库test_db，其他数据库不能访问：grantallprivilegesontest_db.*to'testuser'@'localhost';设置用户testuser，可以访问所有mysql数据库：将test_db.*的所有权限授予'testuser'@'localhost'；设置用户testuser，只能访问数据库testuser的表user_info，不能访问数据库中的其他表：将test_db.user_info的所有权限授予'testuser'@'localhost'；(2)设置用户操作权限设置用户testuser，拥有所有操作权限，即administrator：将*.*上的所有权限授予'testuser'@'localhost'；设置用户testuser，只有[查询]操作权限：grantselecton*.*to'testuser'@'localhost';设置用户testuser，只有[查询/插入/修改/删除]操作权限：grantselect,insert,update,deleteon*.*到'testuser'@'localhost';(3)设置用户的远程访问权限设置用户testuser，只有在客户端IP192.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;只需在末尾添加WITHGRANTOPTION即可！5.3关于root用户的访问设置您可以使用以下命令一键设置root用户的密码，同时拥有所有权限，并设置为远程访问！将*.*上的所有权限授予由“123456”标识的“root”@“%”；如果要关闭root用户的远程访问权限，使用以下命令！将*.*上的所有权限授予由“123456”标识的“root”@“localhost”；最后使用如下命令使其生效：flushprivileges;创建用户并授权，或者使用如下快捷命令：#例如创建一个admin用户，密码为admingrantallprivilegeson*.*to'admin'@'%'identifiedby'admin';#刷新MySQL系统权限相关表使flushprivileges生效；最后要注意的是：mysql8，使用Strong验证，所以密码太简单会报错，密码越复杂越好！6.小结本文主要对Mysql中常用的语法进行总结和介绍。这些语法大部分也适用于其他数据库，如oracle、sqlserver、postgres等。在数据操作栏目中，除了分页功能，基本上都是通用的！
