有哪些好的做h5的网站,怎么做自己的店铺网站,网页美工设计课程教案,公司网站开发人员离职后修改公司网站MySQL 存储过程与触发器
一、初识MySQL的存储过程
Stored Procedure存储过程是数据库系统中一个十分重要的功能#xff0c;使用存储过程可以大幅度缩短大SQL的响应时间#xff0c;同时也可以提高数据库编程的灵活性。
存储过程是一组为了完成特定功能的SQL语句集合#x…MySQL 存储过程与触发器
一、初识MySQL的存储过程
Stored Procedure存储过程是数据库系统中一个十分重要的功能使用存储过程可以大幅度缩短大SQL的响应时间同时也可以提高数据库编程的灵活性。
存储过程是一组为了完成特定功能的SQL语句集合使用存储过程的目的在于将常用且复杂的SQL语句预先写好然后用一个指定名称存储起来这个过程经过MySQL编译解析、执行优化后存储在数据库中因此称为存储过程。当以后需要使用这个过程时只需调用根据名称调用即可。 其实存储过程和Java中的方法、其他语言中的函数十分类似也就是先将一堆代码抽象成一个函数当之后需要使用时不需要再重写一遍代码而是直接根据名称调用相应的函数/方法即可。 对比常规的SQL语句来说普通SQL在执行时需要先经过编译、分析、优化等过程最后再执行而存储过程则不需要一般存储过程都是预先已经编译过的这就好比JIT即时编译器一样为了提升一些常用代码的执行效率JIT会将热点代码编译成本地机器码以此省略解释器翻译执行的步骤从而做到提升性能的目的。
但使用存储过程有利有弊具备的优点如下 复用性存储过程被创建后可以在程序中被反复调用不必重新编写该存储过程的SQL语句同时库表结构发生更改时只需要修改数据库中的存储过程无需修改业务代码也就意味着不会影响到调用它的应用程序源代码。 灵活性普通的SQL语句很难像写代码那么自由而存储过程可以用流程控制语句编写也支持在其中定义变量有很强的灵活性可以完成复杂的条件查询和较繁琐的运算。 省资源普通的SQL一般都会存储在客户端如Java中的dao/mapper层每次执行SQL需要通过网络将SQL语句发送给数据库执行而存储过程是保存在MySQL中的因此当客户端调用存储过程时只需要通过网络传送存储过程的调用语句和参数无需将一条大SQL通过网络传输从而可降低网络负载。 高性能存储过程执行多次后会将SQL语句编译成机器码驻留在线程缓冲区在以后的调用中只需要从缓冲区中执行机器码即可无需再次编译执行从而提高了系统的效率和性能。 安全性对于不同的存储过程可根据权限设置执行的用户因此对于一些特殊的SQL例如清空表这类操作可以设定root、admin用户才可执行。同时由于存储过程编写好之后对于客户端而言是黑盒的因此减小了SQL被暴露的风险。 但还是那句话凡事有利必有弊存储过程也会带来一些之前不存在的问题 CPU开销大如果一个存储过程中涉及大量逻辑运算工作会导致MySQL所在的服务器CPU飙升因而会影响正常业务的执行有可能导致MySQL在线上出现抖动毕竟MySQL在设计时更注重的是数据存储和检索对于计算性的任务并不擅长。 内存占用高为了尽可能的提升执行效率因此当一个数据库连接反复调用某个存储过程后MySQL会直接将该存储过程的机器码放入到连接的线程私有区中当MySQL中的大量连接都在频繁调用存储过程时这必然会导致内存占用率同样飙升。 维护性差一方面是过于复杂的存储过程普通的后端开发人员很难看懂毕竟存储过程类似于一门新的语言不同语言之间跨度较大。另一方面是很少有数据库的存储过程支持Debug调试MySQL的存储过程就不支持这也就意味着Bug出现时无法像应用程序那样正常调试排查必须得采取“人肉排查”模式即一步步拆解存储过程并排查。 基于上述原因咱们在不必要使用存储过程的情况下就尽量减少存储过程的编写除非特定的业务需求导致不得不用时再将注意力转向这块。
二、存储过程的定义、调用与管理
2.1、存储过程的语法
先来看看存储过程的定义语法如下
DELIMITER $-- 创建的语法指定名称、入参、出参
CREATE
PROCEDURE存储过程名称(返回类型参数名1参数类型1,....)
[...这里在后面讲...]
-- 表示开始编写存储过程体
BEGIN
-- 具体组成存储过程的SQL语句....
-- 表示到这里为止存储过程结束
END $DELIMITER ;
存储过程的BEGIN、END就类似于Java方法的{}用来区分起始和结束的边界。所有语言的函数/方法定义时一般都会分为四类如下 • ①无参无返回。 • ②有参无返回。 • ③无参有返回。 • ④有参有返回。
而SQL的存储过程也不例外同样也支持这四种定义主要依赖于IN、OUT、INOUT三个关键字来区分 • 定义存储过程时没有入参也没有出参代表无参无返回类型。 • 定义存储过程时仅定义了带有IN类型的参数表示有参无返回类型。 • 定义存储过程时仅定义了带有OUT类型的参数表示无参有返回类型。 • 定义存储过程时同时定义了带有IN、OUT类型的参数或定义了带有INOUT类型的参数表示有参有返回类型。
在上述给出的语法体中最开始有一个DELIMITER $是什么意思呢 其实这表示指定结束标识在MySQL中默认是以;分号作为一条语句的结束标识因此当存储过程的过程体中如果包含了SQL语句SQL语句以;结束时MySQL会认为存储过程的定义也结束了过程体就会和;结束符冲突所以一般咱们要重新定义结束符例如DELIMITER $表示以$作为结束标识只有当MySQL识别到$符时才会认为结束了。 但记得在结束之后要再次把结束符改回;即DELIMITER ;。
还有一条[...这里在后面讲...]是啥意思呢这是指定存储过程的约束条件取值范围有很多如下 • ①LANGUAGE SQL • ②[NOT] DETERMINISTIC • ③{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } • ④SQL SECURITY { DEFINER | INVOKER } • ⑤COMMENT ....
是不是看起来很头大 • ①说明存储过程中的过程体是否由SQL语句组成。 • ②说明存储过程的返回值是否为固定的没有[NOT]表示为固定的默认为非固定的。 • ③说明过程体使用SQL语句的限制 • CONTAINS SQL表示当前存储过程包含SQL但不包含读写数据的SQL语句。 • NO SQL表示当前存储过程中不包含任何SQL语句。 • READS SQL DATA表示当前存储过程中包含读数据的SQL语句。 • MODIFIES SQL DATA表示当前存储过程中包含写数据的SQL语句。 • ④说明哪些用户可以调用当前创建的存储过程 • DEFINER表示只有定义当前存储过程的用户才能调用。 • INVOKER表示任何具备访问权限的用户都能调用。 • ⑤注释信息可以用来描述当前创建的存储过程。
上述的五条分别和之前的五种取值范围一一对应一般在写存储过程的时候不会加上这些条件通常都是使用默认的。
2.1.1、存储过程的定义
-- 查询用户表中的所有信息
select*fromzz_users;
-------------------------------------------------------------
| user_id | user_name | user_sex | password | register_time |
-------------------------------------------------------------
|1|熊猫|女|6666|2022-08-1415:22:01|
|2|竹子|男|1234|2022-09-1416:17:44|
|3|子竹|男|4321|2022-09-1607:42:21|
|4|黑熊|男|8888|2022-09-1723:48:29|
|8|猫熊|女|8888|2022-09-2717:22:29|
--------------------------------------------------------------- 查询用户表的字段结构
desczz_users;
--------------------------------------------------------
|Field|Type|Null|Key|Default|Extra|
--------------------------------------------------------
| user_id |int(8)|NO| PRI |NULL||
| user_name |varchar(255)| YES | MUL |NULL||
| user_sex |varchar(255)| YES ||NULL||
| password |varchar(255)| YES ||NULL||
| register_time |varchar(255)| YES ||NULL||
--------------------------------------------------------
下面会基于这张用户表来设计几个需求用来加强对存储过程的掌握度一共四个案例 • ①定义一个存储过程查询用户表的所有用户信息。 • ②定义一个存储过程接收一个用户名查询用户的注册时间。 • ③定义一个存储过程查询ID1的用户密码并返回。 • ④定义一个存储过程接收一个用户名返回该用户名对应的用户ID。
这四个案例分别对应存储过程的四种类型也就是分别演示IN、OUT、INOUT的用法。
①查询用户表的所有用户信息
-- 改变结束标识为 $ 符号
DELIMITER $CREATE
-- 定义存储过程的名称为get_all_userInfo()
PROCEDURE get_all_userInfo()
BEGIN
-- 存储过程体由一条查询全表的SQL组成
select*fromzz_users;
-- 标识存储过程体结束
END $
-- 重置结束标识为 ; 符号
DELIMITER ;
上述定义了一个无参无返回的存储过程接着来执行并调用一下该存储过程如下
CALL get_all_userInfo();
-------------------------------------------------------------
| user_id | user_name | user_sex | password | register_time |
-------------------------------------------------------------
|1|熊猫|女|6666|2022-08-1415:22:01|
|2|竹子|男|1234|2022-09-1416:17:44|
|3|子竹|男|4321|2022-09-1607:42:21|
|4|黑熊|男|8888|2022-09-1723:48:29|
|8|猫熊|女|8888|2022-09-2717:22:29|
-------------------------------------------------------------
所有存储过程都是通过CALL命令来调用调用刚刚定义的存储过程后显然将用户表的所有用户信息查询出来啦
②接收一个用户名查询用户的注册时间
上面定义的存储过程即没有入参也没有返回值接着咱们来演示一下带有入参的存储过程
-- 这里又将结束标识换成了 // 符号
DELIMITER //CREATE
-- 在定义存储过程时用 IN 声明了一个入参
PROCEDURE get_user_register_time(IN name varchar(255))
BEGIN
selectregister_timefromzz_userswhereuser_name name;
END//DELIMITER ;
上面这个存储过程中使用IN声明了一个入参其类型为varchar接着来看看如何调用呢
CALL get_user_register_time(竹子);
---------------------
| register_time |
---------------------
| 2022-09-14 16:17:44 |
---------------------
结果十分明显达到了我们想要的效果但在定义带有参数的存储过程时要注意参数名必须在参数类型的前面同时参数类型需要定义长度也就是varchar(255)而并非varchar否则低版本的MySQL会出现不兼容的问题。
③查询ID1的用户密码并返回
上面掌握了IN的用法接下来咱们瞅瞅OUT关键字的用法可以用这个关键字指定返回值
DELIMITER //CREATE
-- 在定义存储过程时用 OUT 声明了一个返回值
PROCEDURE get_user_password(OUT userPassword varchar(255))
BEGIN
selectpasswordinto userPassword fromzz_userswhereuser_id1;
END//DELIMITER ;
在上述这个过程中当ID1的用户密码被查询出来之后会通过into关键字将查询出的密码赋给userPassword那这种带有返回值的存储过程又该如何调用呢如下
CALL get_user_password(userPassword);
select userPassword;
---------------
| userPassword |
---------------
| 6666 |
---------------
没错要调用时直接使用符号在调用的地方定义变量即可调用完成后想要查看返回值还需要手动查询一次调用时定义的变量。 但定义有返回值的存储过程时有一点也要额外注意返回值的数据类型一定要和表字段保持一致否则有可能出现类型转换错误毕竟不是所有的类型之间可以隐式转换。 ④接收一个用户名返回该用户名对应的用户ID
这个需求有两种实现方式 • ①定义两个参数一个IN类型的一个OUT类型的。 • ②使用INOUT关键字来实现。
咱们这里主要是为了讲解因此就采取第二种方式来实现毕竟还没用过INOUT关键字如下
DELIMITER $CREATE
-- 在定义存储过程时用 OUT 声明了一个返回值
PROCEDURE get_user_id(INOUT parameters varchar(255))
BEGIN
selectuser_idinto parameters fromzz_userswhereuser_name parameters;
END $DELIMITER ;
上述存储过程中利用INOUT定义了一个参数parameters在下面的存储过程体当中即使用它作为查询参数又使用它作为了保存返回值的变量再来看看这个函数如何调用
-- 先定义一个变量
setparameters熊猫;
-- 将定义的变量在调用时传入
CALL get_user_id(parameters);
-- 再次查询定义的变量
selectparameters;
-------------
|parameters|
-------------
|1|
-------------
如果想要调用这类方法咱们得先定义一个变量然后在调用时传入最后再次查询这个变量即可。 看到这里大家会发现存储过程中的返回值并不像正常语言中的return而是通过变量传递的方式来实现的上述这个存储过程就类似于Java中的这段代码 public void getUserID(Object obj){obj 1;
}Object obj new Object(熊猫);
getUserID(obj);
System.out.println(obj);
本质上并没有return出结果而是对传入的变量重新赋值从而做到了值的传递。
2.1.2、系统变量和用户变量、局部变量
变量几乎是所有语言都支持的一种语法存储过程也不例外在MySQL中大体存在三种级别的变量即系统变量、用户变量、局部变量这三个不同级别的有效范围也不同。
系统变量
在MySQL启动后其内部也会存在许多的系统变量系统的意思是指由MySQL定义的而并非用户自己定义的一般系统变量要么来自于MySQL编译期要么来自于my.ini配置文件。 MySQL的系统变量也会分为两类一类是全局级变量一类是会话级变量。 事务隔离级别
当在修改命令中加上global关键字则代表修改全局级别的系统变量如若不加或加上session关键字则表示只修改当前会话的系统变量这是啥意思呢修改全局级别表示对所有连接都生效而修改会话级别的变量表示只对当前连接生效在当前连接中修改系统变量的值之后是不会影响其他数据库连接的。
对于系统变量想要查看或修改使用两个符号即可例如
-- 查看某个系统变量
select xxx;
-- 修改某个系统变量
set xxx xxx;
用户变量
系统变量并非咱们的重点接着来瞧瞧用户变量也就是自定义的变量其实非常简单如下
set 变量名称 变量值;
select 变量名称;
相较于系统变量而言用户变量仅仅少了一个符号而已不过上述用户变量的赋值中间的也可改为:其作用也是相同的。除此之外用户变量的定义还可以和SQL组合如下
-- 将用户表的总行数赋值给 row_count 变量
select row_count : count(*) from zz_users;
-- 将 user_id 的平均值赋给 avg_user_id 变量
select avg(user_id) into avg_user_id from zz_users;
上述这两种方式都可以创建一个用户变量也就意味着但凡出现符号时MySQL都会将其识别为在定义变量。
局部变量
前面简单讲述了用户变量但在存储过程中还存在一种名为局部变量的概念这也就是类似于在Java的方法中定义的变量有效范围只对当前方法体生效而局部变量亦是同理只对当前存储过程体有效其他存储过程或外部是无法读取或操作局部变量的定义方式如下
DECLARE 变量名称 数据类型 default 默认值;
这跟通过SQL创建表时声明表字段的语法相差无几就前面多了一个DECLARE关键字举个简单的例子如下
DECLARE message varchar(255) default not message;
上述定义了一个名为message的局部变量如果后续使用时未对其赋值该变量的默认值为not message。
后续使用局部变量时主要有两种赋值方式如下
-- 赋值方式一
SET message 变量值;
SET message : 变量值;-- 赋值方式二
select 字段名或函数 into message from 表名;
非常的简单接着来结合存储过程一起熟悉一下用户变量和局部变量
DELIMITER //CREATE
-- 定义了一个 求两数之和 的存储过程
PROCEDURE add_value(IN number1 int(8),OUTresultint(8))
BEGIN
-- 这里定义了一个局部变量number2默认值为 666
DECLARE number2 int(8)default666;
-- 将两个数字相加计算得到的和放入用户变量 result 中
SETresult: number1 number2;
END//DELIMITER ;-- 定义一个用户变量接收调用存储过程后得到的和
SETresult0;
-- 调用存储过程传入一个数字 888 以及接收结果的 result 变量
CALL add_value(888,result);
-- 查询计算后的和
SELECTresult;
---------
|result|
---------
|1554|
---------
对于局部变量的定义必须要写在BEGIN、END之间否则会提示语法错误这一点需要在使用时注意上述定义的存储过程其工作十分简单即计算两数之和并返回具体的过程参考源码中的注释经过这个例子相信大家对存储过程中的变量能够进一步掌握。
2.1.3、流程控制 - IF判断与CASE分支
上面简单掌握几种变量的语法后接着再来说说存储过程中的流程控制也就是条件判断、循环、跳转等语法先来聊一聊所有语言都有的条件判断。
条件判断IF
在存储过程中主要有两类条件判断的语法即IF、CASE先来说说常见的IF语法如下
IF 条件判断 THEN-- 分支操作.....
ELSEIF 条件判断 THWN-- 分支操作.....
ELSE-- 分支操作.....
END IF
上述这段if判断语句基本上和其他语言中相差无几当一个条件判断成立时就会进入相应的分支中执行否则程序会跳过该分支继续往下执行举个案例快速过一下
DELIMITER $CREATE
PROCEDURE if_user_age(IN age int,OUT msg varchar(255))
BEGINIF age 18THEN
SET msg :未成年;ELSEIF age 18THEN
SET msg :刚成年;
ELSE
SET msg :已成年;
END IF;END $DELIMITER ;
上面定义了一个判断年龄的存储过程调用时需要传入一个age值内部会对传入的值进行判断最后将判断结果写入到msg变量中调用方式如下
SET msg:Not Data;CALL if_user_age(16,msg);
SELECTmsg;
---------
|msg|
---------
|未成年|
---------CALL if_user_age(18,msg);
SELECTmsg;
---------
|msg|
---------
|刚成年|
---------CALL if_user_age(25,msg);
SELECTmsg;
---------
|msg|
---------
|已成年|
---------
OK~存储过程中分支判断的语法与常规编程语言中的if、else if、else无太大差异接着来看看CASE的语法。
分支判断CASE
存储过程中的CASE语法就类似于Java中的switch语法但CASE有两种写法如下
-- 第一种语法
CASE变量
WHEN值1THEN
-- 分支操作1....
WHEN值2THEN
-- 分支操作2....
.....
ELSE
-- 分支操作n....
ENDCASE;-- 第二种语法
CASE
WHEN条件判断1THEN
-- 分支操作1....
WHEN条件判断2THEN
-- 分支操作2....
.....
ELSE
-- 分支操作n....
ENDCASE;
举个例子快速过一下这里就演示第一种语法毕竟第二种方式就等同于多重IF判断
DELIMITER $CREATE
PROCEDURE test_case(IN n int)
BEGIN
CASE n
WHEN1THEN
select竹子真的帅;
WHEN2THEN
select熊猫真好看;
ELSE
select两个都好看;
ENDCASE;
END $DELIMITER ;
调用结果如下上面的SELECT xxx就类似于其他语言的print输出
CALL test_case(1);
---------------
|竹子真的帅|
---------------
|竹子真的帅|
---------------CALL test_case(2);
---------------
|熊猫真好看|
---------------
|熊猫真好看|
---------------CALL test_case(3);
---------------
|两个都好看|
---------------
|两个都好看|
---------------
调用结果很明显基本上与Java中的switch效果相同OK~接着来看看存储过程中的循环语法。
2.1.4、循环LOOP、WHILE、REPEAT
编程中常见的循环有for、foreach、while、do-while四大类而存储过程中也支持LOOP、WHILE、REPEAT三类循环接着挨个简单过一下。
LOOP循环
先简单聊一聊LOOP语法如下
循环名称:LOOP-- 循环体....
END LOOP 循环名称;
在存储过程的循环与其他编程语言的循环并不同在存储过程中可以给每个循环取一个名字后续可以基于这个名字来跳出循环但如果想要跳出一个循环还需要结合LEAVE这个关键字否则会令循环成为一个死循环无限执行下去现在先上个简单的例子
DELIMITER $CREATE PROCEDURE test_loop(IN num int)
BEGIN
--定义一个局部变量numDECLARE num int(8)default1;add_loop:LOOP
--一直循环对num 1SET num num 1;
--当num被加到100时IF num 100 THEN
--跳出循环LEAVE add_loop;
END IF;
END LOOP add_loop;select num;
END $DELIMITER ;
这个存储过程很简单就是利用LOOP循环对局部变量num进行累加加到100时退出循环最后查询一下num值调用结果如下
CALL test_loop();
--------
| num |
--------
| 100 |
--------
效果跟想象的差不多num最终值为100OK接着看看其他的循环。
WHILE循环
WHILE循环的语法也和前面的LOOP循环类似如下
【循环名称】:WHILE 循环条件 DO-- 循环体....
END WHILE 【循环名称】;
其实对于这个循环名称可以写也可以不写主要是利用名称来做跳转这点后续说先来举个简单的例子感受一下WHILE循环
DELIMITER $CREATE
PROCEDURE test_while()
BEGIN
-- 定义一个局部变量num
DECLARE num int(8)default1;-- 循环对num1当10时退出WHILE num 10 DO
-- 一直循环对num 1
SET num num 1;
END WHILE;-- 最后查询一下num值
SELECT num;
END $DELIMITER ;
这个存储过程也是一个对num变量不断做1的循环调用结果如下
CALL test_while();
-------
| num |
-------
| 11 |
-------
但此时来看答案为何是11而并不是10呢因为循环条件是num10也就是当num10的时候依旧会循环一次num又被1所以最终num11。
REPEAT循环
REPEAT循环和之前两个循环不同在这种循环中有专门控制循环结束的语法如下
【循环名称】:REPEAT-- 循环体....
UNTIL 结束循环的条件判断
END REPEAT 【循环名称】;
上述语法种当UNTIL关键字之后的条件为真时循环就会终止来看个用例
DELIMITER $CREATE
PROCEDURE test_repeat()
BEGIN
-- 定义一个局部变量num
DECLARE num int(8)default1;REPEAT
SET num num 1;UNTIL num10
END REPEAT;-- 最后查询一下num值
SELECT num;
END $DELIMITER ;
存储过程和之前的循环案例相同也就是对num做累加调用结果如下
CALL test_repeat();
-------
| num |
-------
| 10 |
-------
此时num10是啥原因呢因为当num10时会停止循环当num被累加到10后就触发了终止条件因此最终num10。
接着来看看存储过程中的跳转语法其实在之前的LOOP循环中简单的用过。
2.1.5、跳转LEAVE、ITERATE
LEAVE、ITERATE两个跳转的关键字其实本质上就和Java中的break、continue类似LEAVE主要功能是结束循环体当执行循环体时遇到了LEAVE关键字就会结束当前循环。而ITERATE则是跳出本次循环继续下次循环的意思作用与continue相同接着举个案例来简单的实验一下。
-- 测试LEAVE关键字终止循环
DELIMITER $CREATE
PROCEDURE test_leave()
BEGIN
-- 定义一个局部变量num
DECLARE num int(8)default1;add_while:WHILE TRUE DO
-- 对num持续做1
SET num num 1;-- 如果num10用LEAVE终止循环IF num 10THENLEAVE add_while;
END IF;END WHILE add_while;-- 最后查询一下num值
SELECT num;
END $DELIMITER ;
依旧还是这个令人熟悉的例子在这里咱们通过WHILE TRUE的方式开启了一个死循环后续利用LEAVE来终止循环调用结果如下
CALL test_leave();
-------
| num |
-------
| 10 |
-------
最终num10这是因为当num10的时候就会执行LEAVE add_while;语句会通过LEAVE关键字终止名为add_while的循环。 存储过程中的这点设计的很不错在编写循环结构时由于可以为每个循环命名因此后续要跳出、终止某个循环时可以直接通过循环的名称来跳转在循环嵌套的情况下这点尤为好用 -- 测试ITERATE关键字跳出循环
DELIMITER $CREATE
PROCEDURE test_iterate()
BEGIN
-- 定义一个局部变量num
DECLARE num int(8)default0;
-- 定义一个局部变量用来统计偶数和
DECLARE even_sum int(8)default0;sum_while:WHILE num 100 DO
-- 对num持续做1
SET num num 1;-- 如果num10用LEAVE终止循环IF num %20THEN
SET even_sum even_sum num;
ELSE
-- 如果num不为偶数则跳过本次循环ITERATE sum_while;
END IF;END WHILE sum_while;-- 最后查询一下偶数之和
SELECT even_sum;
END $DELIMITER ;
上述这个存储过程作用也很简单首先开启一个循环遍历1~100接着对100以内的所有偶数求和如果num%20则表示为偶数将结果累加到even_sum变量中不为0则通过ITERATE关键字跳出本次循环继续下次循环最终调用结果如下
------------
| even_sum |
------------
| 2550 |
------------
结果确实达到了咱们想要的效果100内的偶数之和确实为2550。
2.1.6、存储过程的游标
游标是所有数据库的存储过程中很重要的一种特性它可以对一个结果集中的数据按条处理也就意味着原本查询出的数据是一个整体性质的集合而使用游标可以对该集合中的数据逐条处理在使用游标时一般都会遵循下述四步
-- ①声明创建游标
DECLARE游标名称CURSORFORselect...;-- ②打开游标
OPEN游标名称;-- ③使用游标
FETCH游标名称INTO变量名称;-- ④关闭游标
CLOSE 游标名称;
观察游标的声明语法应该会发现它和创建局部变量的方式类似但后面会加上CURSOR FOR关键字来表明创建的是游标接着来个简单的案例感受一下游标的使用需求如下 计算用户表中user_id最大的前N个奇数ID之和。 DELIMITER //CREATE
PROCEDURE id_odd_number_sum(IN N int(8),OUT sum int(8))
BEGIN
-- 声明局部变量
-- uid用于记录每一个user_id
-- odd_id_count记录奇数ID的个数
-- odd_id_sum记录奇数ID的和
DECLARE uid int(8)DEFAULT0;
DECLARE odd_id_count int(8)DEFAULT0;
DECLARE odd_id_sum int(8)DEFAULT0;-- 声明一个游标存储倒序的user_id结果集
DECLARE uid_cursor CURSORFORselect user_id from zz_users orderby user_id desc;-- 打开游标
OPEN uid_cursor;-- 使用游标REPEAT
-- 将游标中的每一条user_id值赋给user_id变量
FETCH uid_cursor INTO uid;-- 如果当前user_id是奇数则将ID值累加到sum中IF uid %2!0THEN
SET odd_id_count odd_id_count 1;
SET odd_id_sum odd_id_sum uid;
END IF;
-- 根据传入的N来决定循环的次数UNTIL odd_id_count N END REPEAT;-- 将前N个奇数ID之和赋给外部变量sum
SET sum odd_id_sum;
-- 关闭游标
CLOSE uid_cursor;
END//DELIMITER ;
代码的具体逻辑参考上述注释最终调用结果如下
select *from zz_users;
-------------------------------------------------------------
| user_id | user_name | user_sex | password | register_time |
-------------------------------------------------------------
|1|熊猫|女|6666|2022-08-1415:22:01|
|2|竹子|男|1234|2022-09-1416:17:44|
|3|子竹|男|4321|2022-09-1607:42:21|
|4|黑熊|男|8888|2022-09-1723:48:29|
|8|猫熊|女|8888|2022-09-2717:22:29|
-------------------------------------------------------------CALL id_odd_number_sum(2,sum);
selectsum;
------
| num |
------
|4|
------
表中总共存在1、3这两个奇数ID然后调用存储过程时传入2表示获取前两个奇数ID之和最终结果为4(13)显然达到了咱们的需求在这个过程中我们利用了游标对order by之和的结果集其中的每个user_id进行了逐条处理、逐条判断从而完成了前面给出的需求。
2.1.7、存储过程语法小结
2.2、客户端如何调用存储过程
一般在Java项目中都会选择MyBatis作为操作数据库的ORM框架那在其中调用存储过程的方式也很简单如下
parameterMap type根据存储过程决定 id自己命名 parameter property存储过程参数1 jdbcType数据类型 modeIN/parameter property存储过程参数2 jdbcType数据类型 modeIN/parameter property存储过程参数3 jdbcType数据类型 modeOUT/
/parameterMapinsert id和Dao接口的方法同名 parameterMap上面的ID值 statementTypeCALLABLE {call 存储过程名(?, ?, ?)}
/insert
当需要调用存储过程中只需要调用该xml对应的Dao/Mapper层接口即可。
2.3、存储过程的管理
所谓的存储过程管理也就是指存储过程的查看、修改和删除在MySQL中也提供了一系列命令以便于咱们完成这些工作如下 • SHOW PROCEDURE STATUS;查看当前数据库中的所有存储过程。 • SHOW PROCEDURE STATUS WHERE db 库名 AND NAME 过程名;查看指定库中的某个存储过程。 • SHOW CREATE PROCEDURE 存储过程名;查看某个存储过程的源码。 • ALTER PROCEDURE 存储过程名称 ....修改某个存储过程的特性。 • DROP PROCEDURE 存储过程名;删除某个存储过程。
当然也可以通过下述命令来查看某张表的存储过程
-- 查看某张表的所有存储过程
select*from表名.Routineswhere routine_type PROCEDURE;-- 查看某张表的某个存储过程
select*from表名.Routineswhere routine_name 过程名AND routine_type PROCEDURE;
2.4、存储过程的应用场景
存储过程到底该不该用这点在《阿里开发手册》中是强制禁止使用的 阿里开发规范
因为存储过程难以维护同时拓展性和移植性都很差因此大多数的开发规范中都会明令禁止使用但存储过程能够带来的优势也极为明显。 ①插入测试数据时一般为了测试项目都会填充测试数据往常是写Java-for跑数据但现在可以用存储过程来批量插入它的效率会比用for循环快上无数倍毕竟从Java传递SQL需要时间拿到SQL后还要经过解析、优化....一系列工作而用存储过程则不会有这些问题。 ②对数据做批处理时也可以用存储过程来跑比如将一个表中的数据洗到另外一张表时就可以利用存储过程来处理。 ③一条SQL无法完成的、需要应用程序介入处理的业务尤其是组成起来SQL比较长时也可以编写一个存储过程然后客户端调用即可。 三、MySQL的触发器
触发器本质上是一种特殊的存储过程但存储过程需要人为手动调用而触发器则不需要它可以在执行某项数据操作后自动触发就类似于Spring-AOP中的切面一样当执行了某个操作时就会触发相应的切面逻辑。 但触发器是在MySQL5.0.2版本以后才开始被支持的在此之前的MySQL并不能创建触发器而触发器的触发条件是以事件为单位的对于事件相信诸位一定不陌生比如前端的按钮标签就会经常用到它的点击事件当用户点击某个按钮后就会触发对应的点击函数从而执行相应逻辑而MySQL触发器亦是同理。 创建一个触发器的语法如下
CREATE TRIGGER触发器名称
{BEFORE | AFTER}{INSERT|UPDATE|DELETE}ON表名
FOREACHROW-- 触发器的逻辑代码块;
从上述语法结构可以看出对于每一个触发器而言总共有插入、修改以及删除三种触发事件可选同时也可以选择将触发器放在事件开始前亦或事件结束后执行这点几乎和AOP切面的切入点一模一样同时也要记住每个触发器创建后必然是附着在一张表上的因为在创建触发器的时候必须要指定表名它会监控这张表上发生的事件比如举个例子 当我对zz_users表创建了一个插入事件的后置处理器时那么当每次表中插入数据后都会自动触发一次相应的逻辑。 接下来依旧上个小案例简单的感受一下触发器的用法当然依旧不要在乎需求是否合理主要是感受触发器的用法
-- 创建一张注册日志表
CREATETABLEregister_log(
-- 注册时间
register_timevarchar(255),
-- 注册地
register_addressvarchar(255),
-- 注册设备
register_facilityvarchar(255)
)
ENGINE InnoDB
CHARACTERSET utf8
COLLATE utf8_general_ci
ROW_FORMAT Compact;-- 在用户表上创建一个触发器
DELIMITER //CREATETRIGGER zz_users_insert_beforeBEFORE INSERTON zz_users
FOREACHROW
BEGIN
insertintoregister_logvalues(NOW(),北京市海淀区,IOS);
END//DELIMITER ;
上述案例中对zz_users用户表建立了一个插入后置触发器也就是当用户表中插入一条数据时会向注册日志表中自动添加一条注册日志测试效果如下
-- 向用户表插入一条用户记录
INSERTINTOzz_usersVALUES(9,棕熊,男,0369,2022-10-17 23:48:29);-- 查询注册日志表
select*fromregister_log;
------------------------------------------------------------
| register_time | register_address | register_facility |
------------------------------------------------------------
|2022-10-1818:52:50|北京市海淀区| IOS |
------------------------------------------------------------
刚刚并未手动插入注册日志表的数据但会发现当用户表中插入一条数据时会自动触发创建的插入后置触发器这个效果相信了解过切面编程的小伙伴都不陌生。 但此时有个小问题我们可以为一张表的某个事件创建触发器但在触发器中有没有办法拿到当前操作的数据呢比如在insert事件中能否拿到插入的数据update事件中能否拿到修改前后的数据答案是可以的在触发器中有NEW、OLD这两个关键字。 3.1、触发器的NEW、OLD关键字
在触发器中NEW表示新数据OLD表示老数据各类型的事件如下 • insert插入事件NEW表示当前插入的这条行数据。 • update修改事件NEW表示修改后的新数据OLD表示修改前的老数据。 • delete删除事件OLD表示删除前的老数据。
这样似乎有些令人犯迷糊呀举个简单的例子以用户表的修改事件为例
-- 执行的修改语句
updatezz_usersset user_name 粉熊and user_sex 女where user_id 9;-- 用户表修改事件的触发器
DELIMITER //CREATETRIGGER zz_users_update_beforeBEFORE UPDATEON zz_users
FOREACHROW
BEGIN
DECLARE new_name varchar(255);
DECLARE old_name varchar(255);-- 可以通过 NEW 关键字拿到修改后的新数据粉熊
SET new_name : NEW.user_name;-- 可以通过 OLD 关键字拿到修改前的老数据棕熊
SET old_name : OLD.user_name;
END//DELIMITER ;
经过上述这个例子之后大家应该更能理解NEW、OLD两个关键字由于这两个关键字存储它能够使触发器更为灵活。 为啥说触发器是一种特殊的存储过程呢因为本质上触发器中所用的语法和存储过程完全是一模一样的只是存储过程需要手动调用而触发器则是根据事件自动触发。 触发器的可以用于一些特殊的业务场景比如需要在写数据前做数据安全性检测、又或者是洗数据时需要效验数据完整性、正确性、又或者是数据的备份和同步等这类需求。
3.2、触发器的管理 • SHOW TRIGGERS;查看当前数据库中定义的所有触发器。 • SHOW CREATE TRIGGER 触发器名称;查看当前库中指定名称的触发器。 • SELECT * FROM information_schema.TRIGGERS;查看MySQL所有已定义的触发器。 • DROP TRIGGER IF EXISTS 触发器名称;删除某个指定的触发器。
MySQL命令大全
一、基础操作与库命令
MySQL中的所有命令默认是以;分好结尾的因此在执行时一定要记得带上分号否则MySQL会认为你这条命令还未结束会继续等待你的命令输入。
1.1、MySQL基础操作命令 • net start mysqlWindows系统启动MySQL服务。 • 安装目录/mysql startLinux系统启动MySQL服务。 • shutdown后面的start换成这个表示关闭MySQL服务。 • restart换成restart表示重启MySQL服务。 • ps -ef | grep mysqlLinux查看MySQL后台进程的命令。 • kill -9 MySQL进程ID强杀MySQL服务的命令。 • mysql -h地址 -p端口 -u账号 -p客户端连接MySQL服务需要二次输入密码。 • show status;查看MySQL运行状态。 • SHOW VARIABLES like %xxx%;查看指定的系统变量。 • show processlist;查看当前库中正在运行的所有客户端连接/工作线程。 • show status like Threads%;查看当前数据库的工作线程系统。 • help data types;查看当前版本MySQL支持的所有数据类型。 • help xxx查看MySQL的帮助信息。 • quit退出当前数据库连接。
1.2、MySQL库相关的命令 • show databases;查看目前MySQL中拥有的所有库。 • show engines;查看当前数据库支持的所有存储引擎。 • use 库名;使用/进入指定的某个数据库。 • show status;查看当前数据库的状态信息。 • show grants;查看当前连接的权限信息。 • show errors;查看当前库中记录的错误信息。 • show warnings查看当前库抛出的所有警告信息。 • show create database 库名;查看创建某个库的SQL详细信息。 • show create table 表名;查看创建某张表的SQL详细信息。 • show tables;查看一个库中的所有表。 • desc 表名;查看一张表的字段结构。除开这种方式还有几种方式 • describe 表名;查看一张表的字段结构。 • show columns from 表名;查看一张表的字段结构。 • explain 表名;查看一张表的字段结构。 • create database 库名;新建一个数据库后面还可以指定编码格式和排序规则。 • drop database 库名;删除一个数据库。 • ALTER DATABASE 库名 DEFAULT CHARACTER SET 编码格式 DEFAULT COLLATE 排序规则修改数据库的编码格式、排序规则。
1.3、MySQL表相关的命令
对于MySQL表相关的命令首先来聊一聊创建表的SQL命令如下
CREATE TABLE 库名.表名 (字段名称1 数据类型(精度限制) [字段选项],字段名称2 数据类型(精度限制) [字段选项]
) [表选项];
对于表中的每个字段都需要用,分割但最后一个字段后面无需跟,逗号同时创建表时对于每个字段都有多个字段选项对于一张表而言也有多个表选项下面一起来看看。 • 字段选项可以不写不选使用默认值 • NULL表示该字段可以为空。 • NOT NULL表示改字段不允许为空。 • DEFAULT 默认值插入数据时若未对该字段赋值则使用这个默认值。 • AUTO_INCREMENT是否将该字段声明为一个自增列。 • PRIMARY KEY将当前字段声明为表的主键。 • UNIQUE KEY为当前字段设置唯一约束表示不允许重复。 • CHARACTER SET 编码格式指定该字段的编码格式如utf8。 • COLLATE 排序规则指定该字段的排序规则非数值类型生效。 • COMMENT 字段描述为当前字段添加备注信息类似于代码中的注释。 • 表选项可以不写不选使用默认值 • ENGINE 存储引擎名称指定表的存储引擎如InnoDB、MyISAM等。 • CHARACTER SET 编码格式指定表的编码格式未指定使用库的编码格式。 • COLLATE 排序规则指定表的排序规则未指定则使用库的排序规则。 • ROW_FORMAT 格式指定存储行数据的格式如Compact、Redundant、Dynamic....。 • AUTO_INCREMENT n设置自增列的步长默认为1。 • DATA DIRECTORY 目录指定表文件的存储路径。 • INDEX DIRECTORY 目录指定索引文件的存储路径。 • PARTITION BY ...表分区选项后续讲《MySQL表分区》再细聊。 • COMMENT 表描述表的注释信息可以在这里添加一张表的备注。
整体看下来会发现选项还蛮多下面贴个例子感受一下
-- 在 db_zhuzi 库下创建一张名为 zz_user 的用户表
CREATETABLEdb_zhuzi.zz_user(
-- 用户ID字段int类型、不允许为空、设为自增列、声明为主键
user_idint(8)NOTNULL AUTO_INCREMENT PRIMARYi_p_id COMMENT 用户ID,
-- 用户名称字段字符串类型、运行为空、默认值为“新用户”
user_namevarchar(255)NULLDEFAULT新用户 COMMENT 用户名
)
-- 存储引擎为InnoDB、编码格式为utf-8、字符排序规则为utf8_general_ci、行格式为Compact
ENGINE InnoDB
CHARACTERSET utf8
COLLATE utf8_general_ci
ROW_FORMAT Compact; 其他关于表操作的SQL命令 • show table status like zz_users\G;纵排输出一张表的状态信息。 • alter table 表名 表选项;修改一张表的结构如alter table xxx engineMyISAM。 • rename table 表名 to 新表名;修改一张表的表名。 • alter table 表名 字段操作;修改一张表的字段结构操作如下 • add column 字段名 数据类型向已有的表结构添加一个字段。 • add primary key(字段名)将某个字段声明为主键。 • add foreing key 外键字段 表名.字段名将一个字段设置为另一张表的外键。 • add unique 索引名(字段名)为一个字段创建唯一索引。 • add index 索引名(字段名)为一个字段创建普通索引。 • drop column 字段名在已有的表结构中删除一个字段。 • modify column 字段名 字段选项修改一个字段的字段选项。 • change column 字段名 新字段名修改一个字段的字段名称。 • drop primary key移除表中的主键。 • drop index 索引名删除表中的一个索引。 • drop foreing key 外键删除表中的一个外键。 • drop table if exists 表名如果一张表存在则删除对应的表。 • truncate table 表名清空一张表的所有数据。 • create table 表名 like 要复制的表名复制一张表的结构然后创建一张新表。 • create table 表名 as select * from 要复制的表名同时复制表结构和数据创建新表。
1.4、表的分析、检查、修复与优化操作
MySQL本身提供了一系列关于表的分析、检查与优化命令 • ①分析表分析表中键的分布如主键、唯一键、外键等是否合理。 • ②检查表检查表以及表的数据文件是否存在错误。 • ③修复表当一个表的数据或结构文件损坏时可以修复表结构仅支持MyISAM表。 • ④优化表消除delete、update语句执行时造成的空间浪费。
分析表
语法如下
analyze [local | no_write_to_binlog] table 表名1;
其中的可选参数local、no_write_to_binlog代表是否将本条SQL记录进bin-log日志默认情况下是记录的加上这两个参数中的其中一个后则不会记录执行效果如下 分析表
如果Msg_text显示的是OK则代表这张表的键不存在问题存在问题的情况我这边就不模拟了后面举例聊。
检查表
语法如下
check table 表名1,表名2... [检查选项];
分析、检查、优化、修复的命令都支持同时操作多张表不同的表之间只需用,逗号隔开即可。检查命令有多个可选项如下 • quick不扫描行数据不检查链接错误仅检查表结构是否有问题。 • fast只检查表使用完成后是否正确关闭了表文件的FD文件描述符。 • changed从上述检查过的位置开始只检查被更改的表数据。 • medium检查行数据收集每一行数据的键值主键、外键...并计算校验和验证数据是否正确。 • extended对每行数据的所有字段值进行检查检查完成后可确保数据100%正确。
先来看看执行结果吧如下 检查表
这回的结果出现了些许不同Msg_text中出现了一个Error信息提示咱们检查的zz_u表不存在而对于一张存在的zz_users表则返回OK表示没有任何问题。
修复表
语法如下
repair [local | no_write_to_binlog] table 表名 [quick] [extended] [use_frm];
值得一提的是修复表的命令不支持InnoDB引擎仅支持MyISAM、CSV、引擎比如基于InnoDB引擎的表执行修复命令时提示如下 修复表
上述Msg_text信息翻译过来的意思是选择的表其引擎并不支持修复命令。 InnoDB引擎其实也有修复机制可以在my.ini/my.conf文件中加一行配置[mysqld]innodb_force_recovery 1这样在启动时会强制恢复InnoDB的数据。 上述这个修复机制默认是不开启的因为InnoDB不需要这个恢复机制InnoDB有完善的事务和持久化机制客户端提交的事务都会持久化到磁盘除非你人为损坏InnoDB的数据文件否则基本上不会出现InnoDB数据损坏的情况。
优化表
语法如下
optimize [local | no_write_to_binlog] table 表名;
这里值得一提的是此优化非彼优化并不意味着你的表存在性能问题执行后它会自动调优而是指清除老数据执行效果如下 优化表
其实删除一条数据本质上并不会立马从磁盘移除而是会先改掉隐藏的删除标识位执行这条优化命令后MySQL会将一些已经delete过的数据彻底从磁盘删除从而释放这些“废弃数据”占用的空间。 上面的执行结果显示“目前表的数据已经是最新的了”这是啥原因呢因为我这张表中压根没有数据没有插入过数据自然也不会有删除数据的动作因此就会出现这个提示。 其实这几个功能在mysqlcheck工具中也有提供。
1.5、MySQL忘记密码怎么办
**MySQL忘记密码怎么办**对于这种情况其实也十分常见哪忘记时该如何处理呢可以重置密码。
①先停掉MySQL的后台服务 • Windows系统请执行net stop mysql • Linux系统请执行安装目录/mysql shutdownkill强杀进程也可以
②进入到MySQL安装目录下的bin文件夹内执行mysqld --skip-grant-tables去掉连接认证。
③因为上面关掉了连接认证接着输入mysql敲下回车进入mysql终端命令行。
④输入use mysql;进入MySQL自身的系统数据库然后输入show tables;查看所有表。
⑤查询MySQL中注册的所有用户select user,host,password from user;。
⑥使用update语句更改root超级管理员的账号密码如下
update user set passwordpassword(123) where userroot and hostlocalhost; 因为MySQL本身会用一张用户表来存储所有已创建的账号信息连接时的效验基准也是来自于该表中的数据因此在这里修改密码后再用新密码登录即可 如果不是root账号的密码忘记了则可以直接登录root账号修改其他用户的密码如果是root账号则按照上述流程操作。 完成之后可以用mysql -uroot -p123连接一下测试密码是否被重置。 二、增删改查语句
2.1、基本的增删改查语句
插入数据
增删改查俗称为CRUD这也是MySQL运行之后执行次数最多的一类SQL语句同时也是每位开发者写的最多的SQL语句接下来则说说这块的语句首先登场的是咱们的几位老伙伴即insert、delete、update、select...这类普通SQL语句。 • insert into 表名(字段名...) values(字段值...);向指定的表中插入一条数据。 • insert into 表名(字段名...) values(字段值...),(...)...;向表中插入多条数据。 • insert into 表名 set 字段名字段值,...;插入一条数据但只插入某个字段的值。
如果要插入一条完整的数据字段名可以用*代替所有字段除开上述两种插入数据的基本方式外还有几种批量插入的方式如下
-- 使用insert语句批量插入另一张表中查询的数据
insert into 表名(字段名...) select 字段名... from 表名...;-- 使用replace语句来实现批量插入
replace into 表名(字段名1,字段名2...) values(字段值....),(字段值...),...;
上述批量插入数据的方式中还可以通过replace关键字来实现插入它与insert有啥区别呢答案在于它可以实现批量更新使用replace关键字来插入数据的表必须要有主键MySQL会根据主键值来决定新增或修改数据当批量插入的数据中主键字段值在表中不存在时则会向表中插入一条相应的数据而当插入数据中的主键值存在时则会使用新数据覆盖原有的老数据。
删除数据 • delete from 表名;删除一张表的所有数据。 • delete from 表名 where 条件;根据条件删除一条或多条数据。 • truncate table 表名清空一张表的所有数据。
修改数据 • update 表名 set 字段名字段值,...;修改表中所有记录的数据。 • update 表名 set 字段名字段值,... where 条件;根据条件修改一条或多条记录的数据。 • replace 表名(字段名1,...) values(字段值...),...;批量修改对应主键记录的数据。
查询数据 • select * from 表名;查询一张表的所有数据。 • select * from 表名 where 条件;根据条件查询表中相应的数据。 • select 字段1,字段2... from 表名 where 条件;根据条件查询表中相应数据的指定字段。 • select 函数(字段) from 表名;对查询后的结果集进行某个函数的特殊处理。
上述三种是最基本的查询方式接着来看一些高级查询语法如下
-- 为查询出来的字段取别名
select字段1as别名,...from表名where条件;
select字段1别名,...from表名;-- 为查询出的表取别名
select*from表名as别名;-- 以多条件查询数据
select*from表名where字段1值1and字段2值2and...;-- 所有条件都符合时才匹配
select*from表名where字段1值1or字段2值2or...;-- 符合任意条件的数据都会返回
-- 符号可以根据情况换为、、、、!、between and、is null、not is null这些-- 对查询后的结果集使用函数处理
select函数(字段)from表名where条件;-- 对查询条件使用函数处理
select*from表名where函数(条件);-- 模糊查询
select*from表名where字段like%字符;-- 查询字段值以指定字符结尾的所有记录
select*from表名where字段like字符%;-- 查询字段值以指定字符开头的所有记录
select*from表名where字段like%字符%;-- 查询字段值包含指定字符的所有记录-- 按照多值查询对应行记录
select*from表名where字段in(值1值2,...);
-- 按照多值查询相反的行记录
select*from表名where字段notin(值1值2,...);
-- 基于多个字段做多值查询
select*from表名where(字段1,字段2...)in((值1值2,...),(...),...);-- 只需要查询结果中的前N条数据
select*from表名 limit N;
-- 返回查询结果中 N~M 区间的数据
select*from表名 limit N,M;-- 联合多条SQL语句查询union all表示不去重union表示对查询结果去重
select*from表名where条件
unionall
select*from表名where 条件;
分组过滤、数据排序
写SQL语句时有些需求往往无法通过最基本的查询语句来实现因此就需要用到一些高级的查询语法例如分组、过滤、排序等操作接着先聊聊这个语法如下
-- 基于一个字段进行排序查询
select*from表名orderby字段名asc;-- 按字段值正序返回结果集
select*from表名orderby字段名desc;-- 按字段值倒序返回结果集
select*from表名orderby字段1asc,字段2desc;-- 按照多字段进行排序查询-- 基于字段进行分组
select*from表名groupby字段1,字段2....;-- 基于分组查询后的结果做条件过滤
select*from表名groupby字段1having 条件;
实际上group by、having这些语句更多的要配合一些聚合函数使用如min()、max()、count()、sum()、avg()....这样才能更符合业务需求但对于聚合函数后面再介绍先简单说说where、having的区别 这两个关键字都是用来做条件过滤的但where优先级会比group by高因此当分组后需要再做条件过滤时就无法使用where来做筛选而having就是用来对分组后的结果做条件过滤的。查询语句中的各类关键字执行优先级为from → where → select → group by → having → order by。 子查询
子查询也可以理解成是查询嵌套是指一种由多条SQL语句组成的查询语句语法如下
-- 基于一条SQL语句的查询结果进一步做查询
select*from(select*from表名where条件)as别名where条件;-- 将一条SQL语句的查询结果作为条件继续查询只适用于子查询返回单值的情况
select*from表名where字段名(select字段名from表名where条件);-- 将一条SQL语句的查询结果作为条件继续查询适用于子查询返回多值的情况
select*from表名where字段名exists(select字段名from表名where条件);
-- 上述的exists可以换为not exists表示查询不包含相应条件的数据-- 将一条SQL语句的多个查询结果作为条件与多个字段进行范围查询
select*from表名where(字段1,字段2...)in(select字段1,字段2...from 表名);
在上述子查询语法中exists的作用和in大致相同只不过not in时会触发全表扫描而not exists依旧可以走索引查询因此通常情况下尽量使用not exists代替not in来查询数据。
关联查询
关联查询也被称之为连表查询也就是指利用主外键连接多张表去查询数据这几乎也是日常开发中写的最多的一类查询语句MySQL中支持多种关联类型如 • 交叉连接 • 内连接 • 外连接 • 左连接 • 右连接 • 全连接
语法如下
-- 交叉连接默认把前一张表的每一行数据与后一张表的所有数据做关联查询
select*from表1,表2...;-- 这种方式默认采用交叉连接的方式
select*from表1crossjoin表2;-- 显式声明采用交叉连接的方式-- 内连接只返回两张表条件都匹配的数据
-- 隐式的内连接写法
select*from表1,表2...where表1.字段表2.字段...;
-- 等值内连接
select*from表1别名1innerjoin表2别名2on别名1.字段别名2.字段;
-- 不等式内连接
select*from表1别名1innerjoin表2别名2on别名1.字段别名2.字段;-- 左外连接左表为主右表为次无论左表在右表是否匹配都返回左表数据缺失的右表数据显示NULL
select*from表1leftjoin表2on表1.字段表2.字段;-- 右外连接和左连接相反右表为主左表为次永远返回右表的所有数据
select*from表1rightjoin表2on表1.字段表2.字段;-- 全外连接两张表没有主次之分每次查询都会返回两张表的所有数据不匹配的显示NULL
-- MySQL中不支持全连接语法只能通过union all语句将左、右连接查询拼接起来实现
select*from表1leftjoin表2on表1.字段表2.字段
unionall
select*from表1rightjoin表2on表1.字段表2.字段;-- 继续拼接查询两张以上的表
select*from表1leftjoin表2on表1.字段表2.字段leftjoin表3on表2.字段表3.字段;
-- 通过隐式连接的方式查询两张以上的表
select*from表1,表2,表3...where表1.字段表2.字段and表1.字段表3.字段...;
-- 通过子查询的方式查询两张以上的表
select*from
(表1as别名1leftjoin表2as别名2on别名1.字段别名2.字段)
leftjoin
表3as别名3on别名1.字段别名3.字段;
多表联查时的笛卡尔积问题所谓的笛卡尔积问题就是指两张表的所有数据都做关联查询一般连表查询都需要指定连接的条件但如果不指定时MySQL默认会将左表每一条数据挨个和右表所有数据关联一次然后查询一次数据。比如左表有3条数据右表有4条数据笛卡尔积情况出现时一共就会查询出3 x 4 12条数据。 笛卡尔积现象出现时会随着表数据增长越来越大因此在连表查询时一定要消除笛卡尔积问题咋消除呢其实就是指定加上关联条件即可。 三、MySQL数据库函数
MySQL中提供了丰富的函数支持包括数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数、加密函数、格式化函数等通过这些函数一方面可以简化业务的代码量另一方面还能更好的实现各类特殊业务需求下来一起来聊聊MySQL支持的函数。
3.1、数学函数
数学函数是MySQL中最常用的一类函数主要用来处理所有数值类型的字段值一起来看看。 • abs(X)返回X的绝对值如传进-1则返回1。 • ln(X)返回X的自然相对数。 • log(X,Y)返回以X的以Y为底的对数。 • log10(X):返回以X基数为10的对数。 • bin(X)返回X的二进制值。 • oct(X)返回X的八进制值。 • hex(X)返回X的十六进制值。 • mod(X,Y)返回X除以Y的余数。 • ceil(X) | ceiling(X)返回不小于X的最小整数如传入1.23则返回2。 • round(X)返回X四舍五入的整数。 • floor(X)返回X向下取整后的值如传入2.34会返回2。 • greatest(X1,X2....,Xn)返回集合中的最大整数值。 • least(X1,X2....,Xn)返回集合中的最小整数值。 • rand(N)返回一个0~N0~1之间的随机小数不传参默认返回0~1之间的随机小数。 • sign(X)传入正数返回1传入负数返回-1传入0返回0。 • pow(X,Y) | power(X,Y)返回X的Y次方值。 • pi()返回四舍五入后的圆周率3.141593。 • sin(X)返回X的正弦值。 • asin(X)返回X的反正弦值。 • cos(X)返回X的余弦值。 • acos(X)返回X的反余弦值。 • tan(X)返回X的正切值。 • atan(X)返回X的反正切值。 • cot(X)返回X的余切值。 • radians(x)返回x由角度转化为弧度的值。 • degrees(x)返回x由弧度转化为角度的值。 • sqrt(X)返回X的平方根。 • exp(e,X)返回e的x乘方的值。 • truncate(X,N)返回小数X保留N位精准度的小数。 • format(x,y)将x格式化位以逗号隔开的数字列表y是结果的小数位数。 • inet_aton(ip)将IP地址以数字的形式展现。 • inet_ntoa(number)显示数字代表的IP地址。 • ......
3.2、字符串函数 • ascii(C)返回字符C的ASCII码。 • length(S)返回字符串的占位空间传入“竹子爱熊猫”返回15一个汉字占位3字节。 • bit_length(S)返回字符串的比特长度。 • concat(S1,S2,...)合并传入的多个字符串。 • concat_wa(sep,S1,S2...)合并传入的多个字符串每个字符串之间用sep间隔。 • position(str,s) | locate(str,s)返回s在str中第一次出现的位置没有则返回0。 • find_in_set(S,list)返回字符串S在list列表中的位置。 • insert(S1,start,end,S2)使用S2字符串替换掉S1字符串中start~end的内容。 • lcase(S) | lower(S)将传入的字符串中所有大写字母转换为小写。 • ucase(S) | upper(S)将传入的字符串中所有小写字母转换为大写。 • left(S,index)从左侧开始截取字符串S的index个字符。 • right(S,index)从右侧开始截取字符串S的index个字符。 • trim(S)删除字符S左右两侧的空格。 • rtrim(S)删除字符S右侧的空格。 • replace(S,old,new)使用new新字符替换掉S字符串中的old字符。 • repeat(str,count)将str字符串重复count次后返回。 • substring(S,index,N)截取S字符串从index位置开始返回长度为N的字符串。 • reverse(S)将传入的字符串反转即传入Java返回avaJ。 • quote(str)用反斜杠转移str中的英文单引号。 • strcmp(S1,S2)比较两个字符是否相同。 • lpad(str,len,s)对str字符串左边填充len个s字符。 • rpad(str,len,s)对str字符串右边填充len个s字符。
3.3、日期和时间函数 • curdate() | current_date()返回当前系统的日期如2022-10-21。 • curtime() | current_time()返回当前系统的时间如17:30:52。 • now() | sysdate()返回当前系统的日期时间如2022-10-21 17:30:59。 • unix_timestamp()获取一个数值类型的unix时间戳如1666348711。 • from_unixtime()将unix_timestamp()获取的数值时间戳格式化成日期格式。 • month(date)获取date中的月份。 • year(date)获取date中的年份。 • hour(date)获取date中的小时。 • minute(date)获取date中的分钟。 • second(date)获取date中的秒数。 • monthname(date)返回date中月份的英文名称。 • dayname(date)获取日期date是星期几如Friday。 • dayofweek(date)获取date位于一周的索引位置周日是1、周一是2...周六是7。 • week(date)获取date是本年的第多少周。 • quarter(date)获取date位于一年中的哪个季度1~4。 • dayofyear(date)获取date是本年的第多少天。 • dayofmonth(date)获取date是本月的第多少天。 • time_to_sec(time)将传入的时间time转换为秒数比如01:00:00 3600s。 • date_add(date,interval 时间 单位) | adddate(...)将date与给定的时间按单位相加。 • date_sub(date,interval 时间 单位) | subdate(...)将date与给定的时间按单位相减。 • addtime(date,time)将date加上指定的时间如addtime(now(),01:01:01)。 • subtime(date,time)将date减去指定的时间。 • datediff(date1,date2)计算两个日期之间的间隔天数。 • last_day(date)获取date日期这个月的最后一天。 • date_format(date,format)将一个日期格式化成指定格式format可选项如下 • %a工作日的英文缩写Sun~Sat。 • %b月份的英文缩写Jan~Dec。 • %c月份的数字格式1~12。 • %M月份的英文全称January~December。 • %D带有英文后缀的数字月份1th、2st、3nd....。 • %d一个月内的天数双数形式01、02、03....31。 • %e一个月内的天数单数形式1、2、3、4....31。 • %f微妙000000~999999。 • %H一天内的小时24小时的周期00、01、02...23。 • %h | %I一天内的小时12小时的周期01、02、03...12。 • %i一小时内的分钟00~59。 • %j一年中的天数001~366。 • %k以24小时制显示时间00~23。 • %l以12小时制显示时间01~12。 • %m月份的数字形式双数形式01~12。 • %p一天内的时间段上午AM、下午PM。 • %r12小时制的时间12:01:09 AM。 • %S | %s秒数双数形式00~59。 • %T24小时制的时间23:18:22。 • %U一年内的周00~53。 • time_format(time,format)将一个时间格式化成指定格式。 • str_to_date(str,format)将日期字符串格式化成指定格式。 • timestampdiff(unit,start,end)计算两个日期之间间隔的具体时间unit是单位 • year年。 • quarter季度。 • month月。 • week周。 • day天。 • hour小时。 • minute分钟。 • second秒数。 • microsecond微妙。 • weekday(date)返回date位于一周内的索引0是周一...6是周日。
3.4、聚合函数
聚合函数一般是会结合select、group by having筛选数据使用。 • max(字段名)查询指定字段值中的最大值。 • min(字段名)查询指定字段值中的最小值。 • count(字段名)统计查询结果中的行数。 • sum(字段名)求和指定字段的所有值。 • avg(字段名)对指定字段的所有值求出平均值。 • group_concat(字段名)返回指定字段所有值组合成的结果如下 • distinct(字段名)对于查询结果中的指定的字段去重。
这里稍微介绍一个日常业务中碰到次数较多的需求
select *from zz_users;
-------------------------------------------------------------
| user_id | user_name | user_sex | password | register_time |
-------------------------------------------------------------
|1|熊猫|女|6666|2022-08-1415:22:01|
|2|竹子|男|1234|2022-09-1416:17:44|
|3|子竹|男|4321|2022-09-1607:42:21|
|4|黑熊|男|8888|2022-09-1723:48:29|
|8|猫熊|女|8888|2022-09-2717:22:29|
|9|棕熊|男|0369|2022-10-1723:48:29|
--------------------------------------------------------------- 基于性别字段分组然后显示各组中的所有ID
select
convert(group_concat(user_id orderby user_id asc separator ,)
using utf8)as分组统计
fromzz_usersgroupby user_sex;
-------------
|分组统计|
-------------
|1,8|
|2,3,4,9|
-------------
上述利用了group_concat()、group by实现了按照一个字段分组后显示对应分组的所有ID。
3.5、控制流程函数 • if(expr,r1,r2)expr是表达式如果成立返回r1否则返回r2。 • ifnull(v,r)如果v不为null则返回v否则返回r。 • nullif(v1,v2)如果v1 v2则返回null如果不相等则返回V1。
-- if的用例
selectif(user_id 3,√,×)from zz_users;-- ifnull的用例
select ifnull(user_id,×)from zz_users;-- case语法1
case表达式
when值1then操作
when值2then操作
...
else操作
end;
-- 用例判断当前时间是星期几
selectcase weekday(now())
when0then星期一
when1then星期二
when2then星期三
when3then星期四
when4then星期五
when5then星期六
else星期天
endas今天是星期几;-- case语法2
case
when条件1then命令
when条件2then命令
...
else commands
end;
-- 用例判断今天是星期几
selectcase
when weekday(now())0then星期一
when weekday(now())1then星期二
when weekday(now())2then星期三
when weekday(now())3then星期四
when weekday(now())4then星期五
when weekday(now())5then星期六
else星期天
endas今天是星期几;
简单聊一下CASE语法第一种语法就类似于Java中的switch而第二种语法就类似于多重if通过CASE语法能够让SQL更加灵活完成类似于存储过程的工作。
3.6、加密函数 • password(str)将str字符串以数据库密码的形式加密一般用在设置DB用户密码上。 • md5(str)对str字符串以MD5不可逆算法模式加密。 • encode(str,key)通过key密钥对str字符串进行加密对称加密算法。 • decode(str,key)通过key密钥对str字符串进行解密。 • aes_encrypt(str,key)通过key密钥对str字符串以AES算法进行加密。 • aes_decrypt(str,key)通过key密钥对str字符串以AES算法进行解密。 • sha(str)计算str字符串的散列算法校验值。 • encrypt(str,salt)使用salt盐值对str字符串进行加密。 • decrypt(str,salt)使用salt盐值对str字符串进行解密。
3.7、系统函数 • version()查询当前数据库的版本。 • connection_id()返回当前数据库连接的ID。 • database() | schema()返回当前连接位于哪个数据库即use进入的库。 • user()查询当前的登录的所有用户信息。 • system_user()返回当前登录的所有系统用户信息。 • session_user()查询所有连接的用户信息。 • current_user()查询当前连接的用户信息。 • charset(str)返回当前数据库的编码格式。 • collation(str)返回当前数据库的字符排序规则。 • benchmark(count,expr)将expr表达式重复运行count次。 • found_rows()返回最后一个select查询语句检索的数据总行数。 • cast(v as 类型)将v转换为指定的数据类型。 就算某个功能在MySQL中没有提供函数支持也可以通过create function的方式自定义存储函数。 四、MySQL支持的数据类型
这里所谓的数据类型也就是只在创建表时可以选择的列字段类型在MySQL中其实可以通过 • help data types;查看当前版本支持的所有数据类型。如下(MySQL5.1版本) 数据类型
总体可分为数值类型、字符/串类型、时间/日期类型、其他类型四种下面一起来聊聊吧。
4.1、数值类型 • tinyint小整数类型占位1Bytes取值范围-128~127。 • smallint中整数类型占位2Bytes取值范围-32768~32767。 • mediumint中大整数类型占位3Bytes取值范围-8388608~8388607。 • int | integer常用整数类型占位4Bytes取值范围-2147483548~2147483647。 • bigint超大整数类型占位8Bytes取值范围-9223372036854775808~9223372036854775807。 • float单精度浮点数类型占位4Bytes取值范围-3.4E38 ~ 3.4E38。 • double双精度浮点数类型占位8Bytes取值范围-1.7E-3081.7E308。 • decimal(m,d)小数类型占位和取值范围都依赖m、d值决定m是小数点后面的精度d是小数点前面的标度。 • bit(m)存储位值可存储m个比特位取值范围是1~64。
4.2、字符串类型 • char定长字符串类型存储空间0~255Bytes。 • varchar变长字符串类型存储空间0~65535Bytes。 • tinyblob二进制短字符串类型存储空间0~255Bytes。 • tinytext短文本字符串类型存储空间0~255Bytes。 • blob二进制长字符串类型存储空间0~65535Bytes。 • text长文本字符串类型存储空间0~65535Bytes。 • mediumblob二进制大字符串类型存储空间0~16777215Bytes。 • mediumtext大文本字符串类型存储空间0~16777215Bytes。 • longblob二进制超大字符串类型存储空间0~4294967295Bytes。 • longtext超大文本字符串类型存储空间0~4294967295Bytes。 • binary(m)定长字符串类型存储空间为M个字符。 • varbinary(m)定长字符串类型存储空间为M个字符1个字节。
一般在为列指定数据类型时都会varchar(255)这样写其实中间的这个数字限制的并不是字节长度而是字符数量比如varchar(255)表示该列最大能存储255个字符。
4.3、时间/日期类型 • date日期类型占位3Bytes格式为YYYY-MM-DD。 • time时间类型占位3Bytes格式为hh:mm:ss。 • year年份类型占位1Bytes格式为YYYY。 • datetime日期时间类型占位8Bytes格式为YYYY-MM-DD hh:mm:ss。 • timestamp时间戳类型占位4Bytes格式为YYYYMMDDhhmmss最大可精确到微妙。
4.4、其他类型 • jsonMySQL5.7版本引入的在此之前只能用字符串类型来存储json数据需要通过函数辅助使用 • json_array(...)存储一个json数组的数据。 • json_array_insert(字段,$[下标],值)在指定的json数组下标位置上插入数据。 • json_object(...)存储一个json对象。 • json_extract(字段,$.键)查询键为某个值的所有数据。 • json_search(....)通过值查询键。 • json_keys(字段)获取某个字段的所有json键。 • json_set(字段,$.键,值)更新某个键的json数据。 • json_replace(...)替换某个json中的数据。 • json_remove(字段,$.键)删除某个json数据。 还有一些其他json类型的函数这里不再说明一般json类型用的较少。 • enum(选项1,选项2...选项n)新增数据时只能从已有的选项中选择一个并插入。 • set(选项1,选项2...选项n)新增数据时可以从已有的选项中选择多个并插入。 • eometry、point、linestring、polygon空间类型接触不多。
稍微解释一下enum、set类型这两种类型就类似于平时的单选框和多选框必须从已有的选项中选择两者的区别在于enum枚举类型只能选择一个选项而set集合类型可以选择多个选项其实用的比较少多数情况下都是直接在客户端中处理。
五、索引相关的命令
-- 创建一个普通索引方式①
create index 索引名ON表名(列名(索引键长度)[ASC|DESC]);
-- 创建一个普通索引方式②
altertable表名add index 索引名(列名(索引键长度)[ASC|DESC]);
-- 创建一个普通索引方式③
CREATETABLE tableName(columnName1 INT(8)NOTNULL,columnName2 ....,
.....,index [索引名称](列名(长度))
);
-- 后续其他类型的索引都可以通过这三种方式创建-- 创建一个唯一索引
createunique索引名ON表名(列名(索引键长度)[ASC|DESC]);-- 创建一个主键索引
altertable表名addprimary key 索引名(列名);-- 创建一个全文索引
create fulltext index 索引名ON表名(列名);-- 创建一个前缀索引
create index 索引名ON表名(列名(索引键长度));-- 创建一个空间索引
altertable表名add spatial key 索引名(列名);-- 创建一个联合索引
create index 索引名ON表名(列名1(索引键长度),列名2,...列名n);
上面将MySQL中创建各类索引的多种方式都列出来了接着再聊聊索引查看、使用与管理的命令。
-- 查看一张表上的所有索引
show index from表名;-- 删除一张表上的某个索引
drop index 索引名on表名;-- 强制指定一条SQL走某个索引查找数据
select*from表名 force index(索引名)where.....;-- 使用全文索引自然搜索模式
select*from表名wherematch(索引列) against(关键字);
-- 使用全文索引布尔搜索模式
select*from表名wherematch(索引列) against(布尔表达式inboolean mode);
-- 使用全文索引拓展搜索模式
select*from表名wherematch(索引列) against(关键字with query expansion);-- 分析一条SQL是否命中了索引
explain select*from表名where 条件....;
六、事务与锁相关的命令 • start transaction; | begin; | begin work;开启一个事务。 • commit;提交一个事务。 • rollback;回滚一个事务。 • savepoint 事务点名称;添加一个事务点。 • rollback to 事务点名称;回滚到指定名称的事务点。 • release savepoint 事务点名称;删除一个事务点。 • select tx_isolation;查询事务隔离级别方式一。 • show variables like %tx_isolation%;查询事务隔离级别方式二。 • set transaction isolation level 级别设置当前连接的事务隔离级别。 • set tx_isolation 隔离级别;设置当前会话的事务隔离级别。 • set global transaction isolation level 级别;设置全局的事务隔离级别选项如下 • read uncommitted读未提交级别。 • read committed读已提交级别。 • repeatable-read可重复读级别。 • serializable序列化级别。 • show variables like autocommit;查看自动提交事务机制是否开启。 • set autocommit 0|1|ON|OFF;开启或关闭事务的自动提交。 • select ... lock in share mode;手动获取共享锁执行SQL语句。 • select ... for share;MySQL8.0之后优化版的共享锁写法。 • select ... for update;手动获取排他锁执行。 • lock tables 表名 read;获取表级别的共享锁。 • lock tables 表名 write;获取表级别的排他锁。 • show open tables where in_use 0;查看目前数据库中正在使用的表锁。 • flush tables with read lock;获取全局锁。 • unlock tables;释放已获取的表锁/全局锁。 • update 表名 set versionversion1 ... where... and versionversion;乐观锁模式执行。
七、存储过程、存储函数与触发器
-- 创建一个存储过程
DELIMITER $
CREATE
PROCEDURE存储过程名称(返回类型参数名1参数类型1,....)
[......]
BEGIN
-- 具体组成存储过程的SQL语句....
END $
DELIMITER ;-- 创建一个存储函数
DELIMITER $
CREATE
FUNCTION存储函数名称(参数名1参数类型1,....)
RETURNS数据类型
[NOT]DETERMINISTIC statements
BEGIN
-- 具体组成存储函数的SQL语句....
END $
DELIMITER ;-- 创建一个触发器
CREATETRIGGER触发器名称
{BEFORE | AFTER}{INSERT|UPDATE|DELETE}ON表名
FOREACHROW
-- 触发器的逻辑代码块;-- ------------- 用户变量与局部变量 ---------------
-- 定义、修改用户变量
set变量名称变量值;
-- 查询用户变量
select变量名称;-- 定义局部变量
DECLARE变量名称数据类型default默认值;
-- 为局部变量赋值方式1
SET变量名变量值;
-- 为局部变量赋值方式2
SET变量名:变量值;
-- 为局部变量赋值方式3
select查询结果字段into变量名from表名;-- ------------- 流程控制 ---------------
-- if、elseif、else条件分支语法
IF 条件判断THEN
-- 分支操作.....
ELSEIF 条件判断 THWN
-- 分支操作.....
ELSE
-- 分支操作.....
END IF-- case分支判断语句
-- 第一种语法
CASE变量
WHEN值1THEN
-- 分支操作1....
WHEN值2THEN
-- 分支操作2....
.....
ELSE
-- 分支操作n....
ENDCASE;-- 第二种语法
CASE
WHEN条件判断1THEN
-- 分支操作1....
WHEN条件判断2THEN
-- 分支操作2....
.....
ELSE
-- 分支操作n....
ENDCASE;-- 循环LOOP、WHILE、REPEAT
-- loop循环
循环名称:LOOP
-- 循环体....
END LOOP 循环名称;-- while循环
【循环名称】:WHILE 循环条件 DO
-- 循环体....
END WHILE 【循环名称】;-- repeat循环
【循环名称】:REPEAT
-- 循环体....
UNTIL 结束循环的条件判断
END REPEAT 【循环名称】;-- 循环跳转
LEAVE 【循环名称】;-- 结束某个循环体
ITERATE 【循环名称】;-- 跳出某个循环体继续下次循环-- ------------- 存储过程的游标 ---------------
-- ①声明创建游标
DECLARE游标名称CURSORFORselect...;-- ②打开游标
OPEN游标名称;-- ③使用游标
FETCH游标名称INTO变量名称;-- ④关闭游标
CLOSE 游标名称;
在上面列出了MySQL中存储过程、存储函数与触发器的相关语法接着再来聊聊管理的命令 • SHOW PROCEDURE STATUS;查看当前数据库中的所有存储过程。 • SHOW PROCEDURE STATUS WHERE db 库名 AND NAME 过程名;查看指定库中的某个存储过程。 • SHOW CREATE PROCEDURE 存储过程名;查看某个存储过程的源码。 • ALTER PROCEDURE 存储过程名称 ....修改某个存储过程的特性。 • DROP PROCEDURE 存储过程名;删除某个存储过程。 • SHOW FUNCTION STATUS;查看当前数据库中的所有存储函数。 • SHOW CREATE FUNCTION 存储过程名;查看某个存储函数的源码。 • ALTER FUNCTION 存储过程名称 ....修改某个存储函数的特性。 • DROP FUNCTION 存储过程名;删除某个存储函数。 • SHOW TRIGGERS;查看当前数据库中定义的所有触发器。 • SHOW CREATE TRIGGER 触发器名称;查看当前库中指定名称的触发器。 • SELECT * FROM information_schema.TRIGGERS;查看MySQL所有已定义的触发器。 • DROP TRIGGER IF EXISTS 触发器名称;删除某个指定的触发器。
当然如若你对这块感兴趣详细的教程可参考上篇《MySQL存储过程与触发器》。
八、MySQL用户与权限管理 • create user 用户名IP identified by 密码;创建一个新用户。 • drop user 用户名IP;删除某个用户。 • set password password(新密码);为当前用户设置新密码。 • set password for 用户名 password(新密码);为指定用户设置新密码需要权限。 • alter user 用户名IP identified by 新密码;使用root账号修改密码。 • mysqladmin -u用户名 -p旧密码 password 新密码;使用mysqladmin工具更改用户密码。 • rename user 原用户名 to 新用户名;对某个用户重命名。 • show grants;查看当前用户拥有的权限。 • show grants for 用户名;查看指定用户拥有的权限。 • grant 权限1,权限2... on 库名.表名 to 用户名;为指定用户授予权限。 • *.*全局权限表示该用户可对所有库、所有表进行增删改查操作。 • 库名.*单库权限表示该用户可对指定库下的所有表进行增删改查操作。 • 库名.表名单表权限表示该用户可对指定表进行增删改查操作。 • insert插入表数据的权限。 • delete删除表数据的权限。 • update修改表数据的权限。 • select查询表数据的权限。 • alter修改表结构的alter权限。 • alter routine修改子程序存储过程、函数、触发器的alter权限。 • create创建表的create权限。 • create routine创建存储过程、存储函数、触发器的权限。 • create temporary tables创建临时表的权限。 • create user创建/删除/重命名/授权用户的权限。 • create view创建视图的权限。 • drop删除表的权限。 • execute执行存储过程的权限。 • file导出、导入表数据的权限。 • index创建和删除索引的权限。 • lock tables获取表锁的权限。 • process查询工作线程的权限。 • references这个在MySQL中没有。 • reload请空表的权限。 • replication clinet获取主节点、从节点地址的权限。 • replication slave复制主节点数据的权限。 • show databases查看所有数据库的权限。 • show view查看所有视图的权限。 • shutdown关闭数据库服务的权限。 • super修改主节点信息的权限。 • all privileges所有权限。 • 权限可选项 • usage不授予这些权限。其他权限全部授予。 • grant option授予这些权限其他权限全部不授予。 • 权限范围可选项 • revoke 权限1,权限2... on 库名.表名 from 用户名;撤销指定用户的指定权限。 • revoke all privileges from 用户名 with grant option;撤销一个用户的所有权限。 • flush privileges;刷新权限。 • select user,password,host from mysql.user;查询当前库中的所有用户信息。 • MySQL8.0版本后推出的密码管理机制 • set persist default_password_lifetime90;设置所有用户的密码在90天后失效。 • create user 用户IP password expire interval 90 day;创建用户时设置失效时间。 • alter user 用户名IP password expire interval 90 day;设置指定用户密码失效。 • alter user 用户名IP password expire never;设置指定用户的密码永不失效。 • alter user 用户名IP password expire default;使用默认的密码失效策略。上述给出了一系列的用户管理和权限管理的命令最后稍微提一下创建用户时的注意事项sql
-- 创建一个名为 zhuzi 的用户 create user zhuzi196.xxx.xxx.xxx identified by 123456;
在创建用户时需要在用户名称后面跟一个IP地址这个IP的作用是用来限制登录用户的机器如果指定为具体IP则表示只能由该IP的机器登录该用户如果写%表示任意设备都能使用该用户名登录连接。
同时也最后提一嘴MySQL对于所有的用户信息都会放在自带的mysql库的user表中存储因此也可以对表执行insert、delete、update、select操作来实现管理用户的功能。## 九、MySQL视图与临时表
-create view 视图名 as select ...;对查询出的结果集建立一个指定名称的视图。
-select * from 视图名;基于某个已经创建的视图查询数据。
-show create view 视图名;查看某个已存在的视图其详细信息。
-desc 视图名;查看某个视图的字段结构。
-alter view 视图名(字段1,...) as select 字段1...;修改某个视图的字段为查询字段。
-drop view 视图名;删除某个视图。
-create temporary table 表名(....);创建一张临时表方式1。
-create temporary view 表名 as select ...;创建一张临时表方式2。
-truncate table 临时表名;清空某张临时表的数据。MySQL的临时表本质上是一种特殊的视图被称为不可更新的视图也就是临时表只支持查询数据不支持增删改操作因此也可以通过创建视图的方式创建临时表在创建语句中加入temporary关键字即可不指定默认为undedined意思是自动选择视图结构一般为merge结构表示创建一个支持增删改查的视图。## 十、数据的导出、导入与备份、还原
数据库的备份其实本质上就是指通过导出数据的形式或者拷贝表文件的方式来制作数据的副本数据恢复/还原即是指在数据库故障、异常、错误的情况下通过导入原本的数据副本将数据恢复到正常状态下面来介绍MySQL中提供的相关命令。
sql
-- --------使用 mysqldump 工具做数据的逻辑备份导出的是sql语句-----------
-- 导出MySQL中全部的库数据使用--all-databases 或者 -A 参数
mysqldump -uroot -p密码 --all-databases 备份文件名.sql-- 导出MySQL中一部分的库数据使用--databases 或者 -B 参数
mysqldump -uroot -p密码 --databases 备份文件名.sql-- 导出MySQL单库中的一部分表数据
mysqldump –u 用户名 –h主机名 –p密码 库名[表名1,表名2...] 备份文件名.sql-- 导出MySQL单表的部分数据使用 --where 参数
mysqldump -u用户名 -p 库名 表名 --where条件 备份文件名.sql-- 排除某些表导出库中其他的所有数据使用 --ignore-table 参数
mysqldump -u用户名 -p 库名 --ignore-table表名1,表名2... 备份文件名.sql-- 只导出表的结构使用 --no-data 或者 -d 选项
mysqldump -u用户名 -p 库名 --no-data 备份文件名.sql-- 只导出表的数据使用 --no-create-info 或者 -t 选项
mysqldump -u用户名 -p 库名 --no-create-info 备份文件名.sql-- 导出包含存储过程、函数的库数据使用--routines 或者 -R选项
mysqldump -u用户名 -p -R --databases 库名 备份文件名.sql-- 导出包含事件触发器的库数据使用 --events 或者 -E选项
mysqldump -u用户名 -p -E --databases 库名 备份文件名.sql-- --------使用 mysql 工具来恢复备份的数据导入xx.sql文件执行-----------
-- 恢复库级别的数据包含了建库语句的情况下使用
mysql -u用户名 -p xxx.sql-- 恢复库中表级别的数据
mysql -u用户名 -p 库名 xxx.sql-- ----------以物理形式备份数据导出的是表数据 ------------
-- 查看数据库导出数据的路径如果没有则需在my.ini/my.conf中配置
show variables like %secure_file_priv%;-- 导出一张表的数据为txt文件使用 select ... into outfile 语句
select * from 表名 into outfile 备份文件名.txt;-- 导出一张表的数据为txt文件使用 mysql 工具
mysql -u用户名 -p --executeselect ...; 库名 数据存放目录/xxx.txt-- 导出一张表的结构和数据为sql、txt文件使用 mysqldump -T 的方式
mysqldump -u用户名 -p -T 数据存放目录 库名 文件名-- 导出一张表的数据为txt文件以竖排形式存储使用 mysql –veritcal 的方式
mysql -u用户名 -p -veritcal --executeselect ...; 库名 数据存放目录/xxx.txt-- 导出一张表的数据为xml文件使用 mysql -xml 的方式
mysql -u用户名 -p -xml --executeselect ...; 库名 数据存放目录/xxx.xml-- -----------通过物理数据文件恢复数据----------------
-- 使用load data infile 的方式导入.txt 物理数据
load data infile 数据目录/xxx.txt into table 库名.表名;-- 使用 mysqlimport 工具导入xxx.txt物理数据
mysqlimport -u用户名 -p 库名 数据存放目录/xxx.txt --fields-terminatedby, --fields-optionally-enclosed-by\-- 使用 mysqldump 工具迁移数据
mysqldump –h 地址1 –u用户名 –p密码 –-all-databases | mysql –h地址2 –u用户名 –p密码
上述列出了一系列数据导出导入、备份恢复、迁移等命令这些都是MySQL自身就支持的方式但这些自带的命令或工具在一些情况下往往没有那么灵活、方便因此在实际情况下可以适当结合第三方工具来完成比如 • 较大的数据需要做物理备份时可以通过xtrabackup备份工具来完成。 • MySQL5.5版本之前的MyISAM表可以通过MySQLhotcopy工具做逻辑备份速度最快。 • 不同版本的MySQL可以使用XtraBackup备份工具来做数据迁移。 • MySQL、Oracle之间可以通过MySQL Migration Toolkit工具来做数据迁移。 • MySQL、SQL Server之间可以通过MyODBC工具来做数据迁移。
十一、表分区相关的命令
-- 创建范围分区
createtable表名(
xxx xxx notnull,
....
)
partitionbyrange(xxx)(
partition分区名1values less than (范围) data directory /xxx/xxx/xxx,
partition分区名2values less than (范围) data directory /xxx/xxx/xxx,
......
);-- 创建枚举分区
createtable表名(
xxx xxx notnull,
....
)
partitionby list(xxx)(
partition分区名1valuesin(枚举值1,枚举值2...),
partition分区名2valuesin(枚举值),
......
);-- 创建常规哈希分区
createtable表名(
xxx xxx notnull,
....
)
partitionby hash(xxx)
partitions 分区数量;-- 创建线性哈希分区
createtable表名(
xxx xxx notnull,
....
)
partitionby linear hash(xxx)
partitions 分区数量;-- 创建Key键分区
createtable表名(
xxx xxx notnull,
....
)
partitionby key(xxx)
partitions 分区数量;-- 创建Sub子分区
createtable表名(
xxx xxx notnull,
....
)
partitionbyrange(父分区键)
subpartition by hash(子分区键)(
partition分区名1values less than (范围1)(subpartition 子分区名1,subpartition 子分区名2,
......
),
partition分区名2values less than (范围2)(subpartition 子分区名1,subpartition 子分区名2,
......
),
......
);-- 查询一张表各个分区的数据量
selectpartition_name as分区名称,table_rows as数据行数
frominformation_schema.partitions
wheretable_name 表名;-- 查询一张表父子分区的数据量
selectpartition_name as父分区名称,subpartition_name as子分区名称,table_rows as子分区行数
frominformation_schema.partitions
wheretable_name 表名;-- 查询MySQL中所有表分区的信息
select*from information_schema.partitions;-- 查询一张表某个分区中的所有数据
select*from表名partition(分区名);-- 对于一张已存在的表添加分区
altertable表名 reorganize partition分区名into(
partition分区名1values less than (范围) data directory /xxx/xxx/xxx,
partition分区名2values less than (范围) data directory /xxx/xxx/xxx,
......
);-- 将多个分区合并成一个分区
altertable表明 reorganize partition分区名1,分区名2...into(
partition新分区名values less than (范围)
);-- 清空一个分区中的所有数据
altertable表名truncatepartition分区名;-- 删除一个表的指定分区
altertable表名droppartition分区名;-- 重建一张表的分区
altertable表名 rebuild partition分区名;-- 分析一个表分区
altertable表名 analyze partition分区名;
-- 优化一个表分区
altertable表名 optimize partition分区名;
-- 检查一个表分区
altertable表名checkpartition分区名;
-- 修复一个表分区
altertable表名 repair partition分区名;-- 减少hash、key分区方式的 n 个分区
altertable表名 coalesce partition n;-- 将一张表的分区切换到另一张表
altertable表名1 exchange partition分区名withtable表名2;-- 移除一张表的所有分区
altertable表名 remove partitioning;
十二、MySQL、InnoDB、MyISAM的参数
参数也被称之为MySQL的系统变量这些变量是影响MySQL运行的关键对每个参数做出不同调整都有可能直接影响到线上数据库的性能。 MySQL系统变量
通过xpath的方式提取数据大概能够得知MySQL系统变量大概一千个上下。 但是要注意虽说MySQL中有一千多个对外暴露的系统参数但并不是所有的参数都可以让用户调整MySQL的系统参数分为了三类 一类是由MySQL自己维护的参数这类参数用户无法调整。 第二类是以配置文件的形式加载的参数这类参数必须在MySQL停机的情况下才能更改。 第三类是运行时的系统参数这类是可以由用户去做动态调整的。 咱们需要关心的重点就是第三类参数那如何观察这类参数呢方式如下 • show global variables;查看全局所有用户级别可以看到的系统变量。 • show session variables; | show variables;查看当前会话的所有系统变量。 • show variables like %关键字%;使用模糊查询搜索某个系统变量。
MySQL5.1与MySQL8.0版本的执行结果如下 可调整的系统变量
可以很明显的从结果中得知MySQL5.1版本中存在278个系统变量MySQL8.0版本中存在557个系统变量这仅仅只是社区版而在商业版的MySQL中其系统参数会更多下面调出一些重点来聊一聊。 • max_connectionsMySQL的最大连接数超出后新到来的连接会阻塞或被拒绝。 • version当前数据库的版本。 • ft_min_word_len使用MyISAM引擎的表中全文索引最小搜索长度。 • ft_max_word_len使用MyISAM引擎的表中全文索引最大搜索长度。 • ft_query_expansion_limitMyISAM中使用with query expansion搜索的最大匹配数。 • innodb_ft_min_token_sizeInnoDB引擎的表中全文索引最小搜索长度。 • innodb_ft_max_token_sizeInnoDB引擎的表中全文索引最大搜索长度。 • optimizer_switchMySQL隐藏参数的开关。 • skip_scan是否开启索引跳跃扫描机制。 • innodb_page_sizeInnoDB引擎数据页的大小。 • tx_isolation事务的隔离级别。 • autocommit事务自动提交机制。 • innodb_autoinc_lock_mode插入意向锁的工作模式。 • innodb_lock_wait_timeoutInnoDB锁冲突时阻塞的超时时间。 • innodb_deadlock_detect是否开启InnoDB死锁检测机制。 • innodb_max_undo_log_size本地磁盘文件中Undo-log的最大值默认1GB。 • innodb_rollback_segments指定回滚段的数量默认为1个。 • innodb_undo_directory指定Undo-log的存放目录默认放在.ibdata文件中。 • innodb_undo_logs指定回滚段的数量默认为128个也就是之前的innodb_rollback_segments。 • innodb_undo_tablespaces指定Undo-log分成几个文件来存储必须开启innodb_undo_directory参数。 • back_log回滚日志的最大回撤长度一条数据的最长版本链长度。 • innodb_undo_log_truncate是否开启Undo-log的压缩功能即日志文件超过一半时自动压缩默认关闭。 • innodb_flush_log_at_trx_commit设置redo_log_buffer的刷盘策略默认每次提交事务都刷盘。 • innodb_log_group_home_dir指定redo-log日志文件的保存路径默认为./。 • innodb_log_buffer_size指定redo_log_buffer缓冲区的大小默认为16MB。 • innodb_log_files_in_group指定redo日志的磁盘文件个数默认为2个。 • innodb_log_file_size指定redo日志的每个磁盘文件的大小限制默认为48MB。 • innodb_log_write_ahead_size设置checkpoint刷盘机制每次落盘动作的大小。 • innodb_log_compressed_pages是否对Redo日志开启页压缩机制默认ON。 • innodb_log_checksumsRedo日志完整性效验机制默认开启。 • log_bin是否开启bin-log日志默认ON开启表示会记录变更DB的操作。 • log_bin_basename设置bin-log日志的存储目录和文件名前缀默认为./bin.0000x。 • log_bin_index设置bin-log索引文件的存储位置因为本地有多个日志文件需要用索引来确定目前该操作的日志文件。 • binlog_format指定bin-log日志记录的存储方式可选Statment、Row、Mixed。 • max_binlog_size设置bin-log本地单个文件的最大限制最多只能调整到1GB。 • binlog_cache_size设置为每条线程的工作内存分配多大的bin-log缓冲区。 • sync_binlog控制bin-log日志的刷盘频率。 • binlog_do_db设置后只会收集指定库的bin-log日志默认所有库都会记录。 • log-errorerror-log错误日志的保存路径和名字。 • slow_query_log设置是否开启慢查询日志默认OFF关闭。 • slow_query_log_file指定慢查询日志的存储目录及文件名。 • general_log是否开启查询日志默认OFF关闭。 • general_log_file指定查询日志的存储路径和文件名。 • innodb_buffer_pool_sizeInnoDB缓冲区的大小。 • innodb_adaptive_hash_index是否开启InnoDB的自适应哈希索引机制。 • innodb_compression_level调整压缩的级别可控范围在1~9越高压缩效果越好但压缩速度也越慢。 • innodb_compression_failure_threshold_pct当压缩失败的数据页超出该比例时会加入数据填充来减小失败率为0表示禁止填充。 • innodb_compression_pad_pct_max一个数据页中最大允许填充多少比例的空白数据。 • innodb_log_compressed_pages控制是否对redo-log日志的数据也开启压缩机制。 • innodb_cmp_per_index_enabled是否对索引文件开启压缩机制。 • character_set_client客户端的字符编码格式。 • character_set_connection数据库连接的字符编码格式。 • character_set_database数据库的字符编码格式。 • character_set_results返回的结果集的编码格式。 • character_set_serverMySQL-Server的字符编码格式。 • character_set_system系统的字符编码格式。 • collation_database数据库的字符排序规则。
十三、MySQL常见的错误码
MySQL的错误信息由ErrorCode、SQLState、ErrorInfo三部分组成即错误码、SQL状态、错误信息三部分组成如下 ERROR 1045 (28000): Access denied for user zhuzilocalhost (using password: YES) 其中1045属于错误状态码28000属于SQL状态后面跟着的则是具体的错误信息不过MySQL内部大致定义了两三千个错误码其错误码的定义位于include/mysqld_error.h、include/mysqld_ername.h文件中而SQLState的定义则位于include/sql_state.h文件中所有的错误信息列表则位于share/errmsg.txt文件中因此大家感兴趣的可自行编译MySQL源码查看。
