【零】数据准备
【1】创建用户信息表
(1)创建表
- id:编号
- name:用户名
- sex:性别,默认男
- balance:余额
- register_time:注册时间
drop table if exists user;
create table user(
id int auto_increment primary key,
name varchar(20),
sex enum('female', 'male') default 'male',
balance float(8, 2),
register_time datetime);
(2)插入数据
truncate user;
insert user(name, sex, balance, register_time)
values('bruce', 'male', 1642.79, '2022-07-01 10:21:31'),
('alice', 'female', 2546.55, '2022-07-02 12:45:20'),
('john', 'male', 125.74, '2022-08-03 08:15:47'),
('emma', 'female', 2651.23, '2022-08-04 16:30:12'),
('mike', 'male', 852.90, '2022-09-05 09:54:38');
【一】流程控制
- 需要在存储过程或函数中使用
【1】什么是流程控制
(1)说明
-
流程控制是编程中一种用于控制程序执行流程的机制,通常涉及条件判断、循环和分支等结构。
-
在数据库中的存储过程、触发器、以及一些脚本语言中,流程控制结构用于根据不同条件执行不同的代码块,从而实现更为复杂和灵活的逻辑。
-
在数据库中,流程控制结构的主要目的是在 SQL 语句的基础上提供更强大的编程能力,使得可以实现更复杂的业务逻辑。
【2】变量
- 在 MySQL 中,有多种类型的变量,包括系统变量、用户定义变量和存储过程中的局部变量。
(1)系统变量
-
系统变量是 MySQL 服务器上的全局配置参数,它们影响着 MySQL 的整体行为。
-
可以使用
SHOW VARIABLES;
查询当前服务器上的所有系统变量。 -
例如:
- max_connections:这是系统允许的最大连接数量
show variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 200 |
+-----------------+-------+
(2)自定义变量
- 用户定义变量是在会话中由用户创建和使用的变量。
- 这些变量的名字以
@
符号为前缀。 - 用户定义变量的值在连接会话期间保持有效,连接结束后失效。
- 例如:
- 定义并查看变量name
set @name = 'bruce';
select @name;
(3)局部变量
-
在存储过程中,可以声明局部变量,这些变量仅在存储过程的执行期间存在,超出存储过程的范围后被销毁。
-
语法:
declare 变量名 类型
【3】条件判断
(1)if else
- 根据条件判断是否执行某个操作。
if 条件 then
-- 执行内容
elseif 条件 then
-- 执行内容
else
-- 执行内容
end if;
【4】多分支体条件判断
- 在满足条件的情况下反复执行一组语句
(1)case
case
when 条件1 then
-- 执行内容
when 条件2 then
-- 执行内容
......
else
-- 执行内容
end case;
【5】循环结构
- 在满足条件的情况下反复执行一组语句
(1)while
while 条件 do
-- 执行内容
end while;
(2)repeat until
repeat
-- 执行内容
until 条件;
(3)loop
loop
-- 执行内容
end loop;
(4)for
for 变量 in 多内容 do
-- 执行内容
end for;
【5】退出循环结构
- 循环中满足某个条件退出
(1)leave
while 条件 do
-- 执行内容
if 条件 then
leave;
end if;
end while;
【二】存储过程
【1】说明
(1)什么是存储过程
- 存储过程是在数据库中预先定义的一组SQL语句,可以接收参数并返回结果。
- 它们在数据库服务器中保存,可以通过应用程序调用,执行特定的数据库操作。
- 存储过程的使用有助于提高数据库的可维护性、安全性,并可以提高性能,因为它们可以在数据库服务器上直接执行,减少了与应用程序之间的通信开销。
- 理解为自定义函数
(2)特点
- 预编译: 存储过程在创建时进行编译和优化,执行时无需重复编译,提高数据库执行效率。
- 数据库端执行: 存储过程在数据库服务器端执行,减少了与应用程序之间的网络传输开销,提高了数据访问性能。
- 代码重用: 存储过程可被多个应用程序共享和重用,避免了重复编写相同的SQL语句,提高了开发效率。
- 安全性: 通过存储过程,可以限制对数据库的访问权限,提高数据的安全性。
- 事务支持: 存储过程可以包含事务处理逻辑,确保数据库操作的一致性和完整性。
- 简化复杂操作: 存储过程能执行复杂的数据操作和计算,简化了应用程序的开发和维护过程。
【2】存储过程的开发模式
-
命令式开发模式(Imperative Development Model)
-
存储过程以类似编程语言的方式编写,包括条件语句、循环结构和异常处理等。
-
逻辑较为灵活,但容易导致存储过程复杂难懂,维护困难。
-
-
声明式开发模式(Declarative Development Model)
-
存储过程的逻辑更关注数据操作语句,如查询、插入、更新和删除等。
-
通过声明数据操作的目标和条件来实现,逻辑简洁明了,更易于理解和维护。
-
-
触发器开发模式(Trigger Development Model)
-
触发器是一种特殊的存储过程,与数据库表相关联,在预定义的事件发生时自动触发执行。
-
用于实现数据一致性、数据验证、日志记录等功能。
-
开发模式与其他存储过程略有不同,执行时机和上下文由数据库事件触发,而不是外部调用。
-
【3】使用
(1)创建存储过程
- 创建存储过程模板
- in是传入参数
- out是返回参数
delimiter $$
create procedure 存储器名(in 形参 类型, in 形参 类型, out 形参 类型)
begin
--执行语句
end $$
delimiter ;
- 创建一个存储过程
- 通过输入用户的名字和金额
- 对用户进行充值
delimiter $$
create procedure my_func_recharge(in n varchar(20), in m float(8, 2), out res varchar(20))
begin
declare name_exists int;
select count(*) into name_exists
from user where name = n;
if name_exists > 0 then
update user
set balance = balance + m
where name = n;
set res = '充值成功';
else
set res = '充值失败,名字不存在';
end if;
end $$
delimiter ;
(2)查看存储过程创建信息
- 语法
show create procedure 存储过程名字 \G;
show create procedure my_func_recharge \G;
mysql> show create procedure my_func_recharge \G;
*************************** 1. row ***************************
Procedure: my_func_recharge
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PAD_CHAR_TO_FULL_LENGTH
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `my_func_recharge`(in n varchar(20), in m float(8, 2), out res varchar(20))
begin
declare name_exists int;
select count(*) into name_exists
from user where name = n;
if name_exists > 0 then
update user
set balance = balance + m
where name = n;
set res = '充值成功';
else
set res = '充值失败,名字不存在';
end if;
end
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
Database Collation: utf8mb4_general_ci
1 row in set (0.00 sec)
ERROR:
No query specified
(3)查看所有存储过程(过多)
- 语法
show procedure status;
(4)调用存储过程
- 语法
call 存储过程名字(参数);
- 测试
- 成功
# 查看原来的金额
select balance from user where name = 'bruce';
+---------+
| balance |
+---------+
| 1642.79 |
+---------+
# 充值测试
set @res = ''
call my_func_recharge('bruce', 100.5, @res);
# 查看标志位
select @res;
+--------------+
| @res |
+--------------+
| 充值成功 |
+--------------+
# 查看金额
+---------+
| balance |
+---------+
| 1743.29 |
+---------+
# 名字错误测试
call my_func_recharge('bbruce', 100.5, @res);
select @res;
+--------------------------------+
| @res |
+--------------------------------+
| 充值失败,名字不存在 |
+--------------------------------+
(5)删除存储过程
- 语法
drop procedure if exists 存储过程名称;
drop procedure if exists my_func_recharge;
【三】函数
- 前面的存储过程就相当于自定义函数
- 这里的函数就相当于内置函数
【1】字符串函数
(1)concat
CONCAT
: 将多个字符串拼接在一起。
select concat(id, "-", name) as id_name
from user limit 2;
+---------+
| id_name |
+---------+
| 1-bruce |
| 2-alice |
+---------+
(2)substring
SUBSTRING
: 截取字符串的子串。
select substring('python', 3, 2) as res;
+------+
| res |
+------+
| th |
+------+
(3)char_length
CHAR_LENGTH
: 返回字符串的字符数。
select name, char_length(name) as name_length
from user limit 3;
+-------+-------------+
| name | name_length |
+-------+-------------+
| bruce | 5 |
| alice | 5 |
| john | 4 |
+-------+-------------+
(4)upper/lower
- lower():全小写
- upper():全大写
select upper(name) as name
from user limit 3;
+-------+
| name |
+-------+
| BRUCE |
| ALICE |
| JOHN |
+-------+
【2】数值函数
(1)round
ROUND(num, decimals)
: 对数值进行四舍五入。
select balance as old, round(balance) as new
from user limit 3;
+---------+------+
| old | new |
+---------+------+
| 1743.29 | 1743 |
| 2546.55 | 2547 |
| 125.74 | 126 |
+---------+------+
(2)floor
FLOOR(num)
: 返回不大于给定数值的最大整数。
select balance as old, floor(balance) as new
from user limit 3;
+---------+------+
| old | new |
+---------+------+
| 1743.29 | 1743 |
| 2546.55 | 2546 |
| 125.74 | 125 |
+---------+------+
(3)ceiling
CEILING(num)
: 返回不小于给定数值的最小整数。
select balance as old, ceiling(balance) as new
from user limit 3;
+---------+------+
| old | new |
+---------+------+
| 1743.29 | 1744 |
| 2546.55 | 2547 |
| 125.74 | 126 |
+---------+------+
(4)abs
ABS(num)
: 返回给定数值的绝对值。
【3】日期和时间函数
(1)now
NOW()
: 返回当前日期和时间。
select now() as time;
+---------------------+
| time |
+---------------------+
| 2024-01-30 21:07:15 |
+---------------------+
(2)curdate
CURDATE()
: 返回当前日期。
select curdate() as today;
+------------+
| today |
+------------+
| 2024-01-30 |
+------------+
(3)curtime
CURTIME()
: 返回当前时间。
select curtime() as time;
+----------+
| time |
+----------+
| 21:09:56 |
+----------+
(4)date_formate
DATE_FORMAT(date, format)
: 格式化日期。
select date_format(register_time, '%Y-%m') as date, count(*)
from user
group by date_format(register_time, '%Y-%m');
+---------+----------+
| date | count(*) |
+---------+----------+
| 2022-07 | 2 |
| 2022-08 | 2 |
| 2022-09 | 1 |
+---------+----------+
select * from user where date(register_time) = '2022-08-03';
+----+------+------+---------+---------------------+
| id | name | sex | balance | register_time |
+----+------+------+---------+---------------------+
| 3 | john | male | 125.74 | 2022-08-03 08:15:47 |
+----+------+------+---------+---------------------+
select * from user year
where month(register_time) = '07';
+----+-------+--------+---------+---------------------+
| id | name | sex | balance | register_time |
+----+-------+--------+---------+---------------------+
| 1 | bruce | male | 1642.79 | 2022-07-01 10:21:31 |
| 2 | alice | female | 2546.55 | 2022-07-02 12:45:20 |
+----+-------+--------+---------+---------------------+
【4】聚合函数
-
详细信息查看筛选条件的
group by
-
COUNT(column)
: 计算符合条件的行数。 -
SUM(column)
: 对指定列的值求和。 -
AVG(column)
: 计算指定列的平均值。 -
MIN(column)
: 找到指定列的最小值。 -
MAX(column)
: 找到指定列的最大值。 -
GROUP_CONCAT
:拼接字段值或字符串。
【5】条件函数
(1)if
- 语法
IF(condition, value_if_true, value_if_false): 如果条件成立,则返回value_if_true,否则返回value_if_false。
- 示例
select name, if(balance > 2000, "rich", "poor") as money_con
from user;
+-------+-----------+
| name | money_con |
+-------+-----------+
| bruce | poor |
| alice | rich |
| john | poor |
| emma | rich |
| mike | poor |
+-------+-----------+
(2)case
- 语法
CASE WHEN condition THEN value END: 根据满足的条件返回相应的值。
- 示例
select name,
case
when balance > 2500 then 'very rich'
when balance > 2000 then 'rich'
else
'poor'
end as money_con
from user;
+-------+-----------+
| name | money_con |
+-------+-----------+
| bruce | poor |
| alice | very rich |
| john | poor |
| emma | very rich |
| mike | poor |
+-------+-----------+