MySQL

合集 - mysql(2)

1.MySQL2024-02-20

2.MySQL数据库表关系详解2024-03-11

收起

库的增删改查

（1）语法

# 增
create database db1;
create database db2 charset='gbk'
 
# 查
show databases; # 查所有的库
show create database db1; # 查单个
 
# 改
alter database db2 charset='utf-8'
 
# 删
drop database db2;

表的增删改查

（1）语法

# 查看当前所在的库的名字
select database();
 
# 切换库
use db1;
 
# 增
create table t1(id int,name varchar(255));
 
# 查
show tables; # 查看当前库下面所有的表名
show create table t1; # 查单个
describe t1; # 支持简写 desc t1; 查看表结构
 
# 改
alter table t1 modify name char(16);
 
# 删
drop table t1;

数据的增删改查

（1）语法

# 增
insert into t1 values(1,'heart');
insert into t1 values(1,'heart'),(2,'god'),(3,'zeus');
 
# 查
select * from t1;
select name from t1;
 
# 改
update t1 set name ='thunder' where id ='1'
 
# 删
delete from t1 where id >1;
delete from t1 where name='jason';
 
# 将表所有的数据清空
delete from t1; # 全部删除

MySQL主要存储引擎

（1）介绍

MySQL有多种存储引擎可供选择，每种引擎都有其自身的特点和适用场景。以下是MySQL中一些主要的存储引擎：

• Innodb

  • 是MySQL5.5版本及之后默认的存储引擎
  • 存储数据更加的安全

• myisam

  • 是MySQL5.5版本之前默认的存储引擎
  • 速度要比Innodb更快 但是我们更加注重的是数据的安全

• memory

  • 内存引擎(数据全部存放在内存中)断电数据丢失

• blackhole

  • 无论存什么，都立刻消失(黑洞)

• 查看引擎

show engines;

创建表的完整语法

（1）语法

create table 表名(
	字段名1 类型(宽度) 约束条件,
	字段名2 类型(宽度) 约束条件,
    字段名3 类型(宽度) 约束条件,
 
)
 
# 注意
1 在同一张表中字段名不能重复
2 宽度和约束条件是可选的(可写可不写) 而字段名和字段类型是必须的
3 最后一行不能有逗号
 
# 约束条件 null not null
create table t2(id int,name vchar not null);

基本数据类型

（1）整型

• 整型默认情况下都是带有符号的
• 针对整型()内的符号到底是什么用？
  • 针对整型，括号内的宽度是用来限制存储数据的范围的

特例：只有整型括号内的数字不是表示限制位数而是显示长度

tinyint smallint mediumint int bigint

整数类型	字节	无符号数的取值范围	有符号数的取值范围
TINYINT	1	0~255	-128~127
SMALLINT	2	0~65535	-32768~32767
MEDIUMINT	3	0~16777215	-8388608~8388607
INT	4	0~4294967295	-2147483648~2147483647
BIGINT	8	0~18446744073709551615	-9223372036854774808~9223372036854774807

（2）浮点型

float double decimal

数据类型	字节数	取值范围
FLOAT	4	-2^1282 ^128，即-3.40E+38+3.40E+38
DOUBLE	8	-2^1024~ 2^1024，即-1.79E+308~1.79E+308
DECIMAL	设置位数和精度。	65 ~ 30

（1）存储限制

float(255,30)
# 总共255位 ， 小数部分占 30 位
double(255,30)
# 总共255位 ， 小数部分占 30 位
decimal(65,30)
# 总共65位 ， 小数部分占 30 位

（3）字符型

char 定长
char(4)
#（超过四个字符直接报错，不够,四个字符空格补全）
 
varchar 变长
varchar(4)
#（超过四个字符直接报错，不够,有几个接存几个）
 
text 用于存储较长的文本内容

（4）时间类型

date 年月日
datetime 年月日时分秒
time 时分秒
year 年

（5）枚举与集合类型

（1）枚举(enum)

• 枚举类型存储数据只能从候选项中选取一个才行，多选一

（2）集合(set)

• 集合类型多选多

# 枚举(enum) 只能从中选一个，多选报错
create table user(
	id int,
    name char(16),
    gender enum('male','female','others')
);
 
# 集合(set) 可以选择多个
create table teacher(
	id int,
    name varchar(16),
    gender enum('male','female','others'),
    hobby set('read books','listen music','play games')
);
 
insert into teacher values(
	2,
    'heart',
    'female',
    'read books,listen music'
);

（6）补充

（1）模糊匹配/查询like "%mode"

• 关键字 ：
  • like "%mode"匹配任意多个字符
  • like "_mode"只能匹配单个字符

（2）严格模式

• 严格模式在MySQL5.7之后的版本默认都是开启严格模式的

# 查看严格模式
show variables like "%mode";
 
sql_mode(严格模式)
 
# 修改严格模式
 
# 只在当前窗口有效
set session;
 
# 全局有效
set global
 
# 语法(严格模式)
set global sql_mode = 'STRICT_TRANS_TABLES'
# 修改完成后，重启服务端，即可生效

约束条件

（1）介绍

• 约束条件：限制表中的数据，保证添加到数据表中的数据准确和可靠性！凡是不符合约束的数据，插入时就会失败！

• 约束条件在创建表时可以使用， 也可以修改表的时候添加约束条件

• 非空约束（not null）

• 唯一性约束（unique）

• 组合使用 not null 和 unique

• 主键约束PK（primary key）

• 外键约束FK（foreign key）

• 级联更新与级联删除

（2）非空约束 not null

• not null约束的字段不能为null值，必须给值

create table t_user(
  id int(10) ,
  name varchar(32) not null,
  email varchar(128)
);

（3）唯一性约束 unique

• 在MySQL中，unique是一种约束，用于确保表中某一列或一组列的值是唯一的，即在这些列中的每个值都不重复。这意味着在指定了unique约束的列中，任何两行都不会具有相同的值。
• unique约束可以应用于单个列或多个列的组合。当你在表的列上添加unique约束时，MySQL会自动为该列（或列组合）创建唯一索引，以确保其唯一性。

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    unique (column1)
);

（4）组合使用

（1）not null 和 unique 单独使用

• 使用表级约束给多个字段联合添加约束，如：unique（name,email）名字和邮箱这两个字段不能同时重复，但是名字和邮箱字段可以单独重复。

（2）not null 和unique同时使用(列级约束)

• 被 not null 和 unique 约束的字段，该字段即不能为 NULL 也不能重复

create table t(
  id int,
  name varchar(255) not null unique
);

（5）查看当前表的约束条件

• table_constraints 该表专门存储约束信息

use information_schema;
show tables;
desc table_constraints;

• 查看某张表存在哪些约束条件

select constraint_name from table_constraints where table_name='表名';

（6）主键约束primary key

• 主键（Primary Key）约束在数据库中起着非常重要的作用。主键是一种用来唯一标识每一行数据的列或列组合，它具有以下特点和用法：

• 特点：

1. 唯一性： 主键列中的值必须是唯一的，不允许重复。
2. 非空性： 主键列的值不能为NULL。
3. 稳定性： 主键值在数据的生命周期中不会改变，即使它是由系统自动生成的。

• 用法：

1. 唯一标识： 主键用于唯一标识表中的每一行数据，确保每一行都具有唯一的标识符。
2. 索引： 主键列会自动创建索引，加快数据检索速度。
3. 关系： 主键经常被用作其他表的外键，用于建立表之间的关系。
4. 数据完整性： 主键约束确保了数据的完整性和一致性，防止表中出现重复的数据。

# 列级约束
CREATE TABLE table_name (
    column1 datatype PRIMARY KEY,
    column2 datatype,
    ...
);
 
# 表级约束
CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    PRIMARY KEY (column1, column2)
);
 
# 给主键重命名 
CREATE TABLE t_user(
	id int(10),
  	name varchar(32),
  	constraint t_user_id_pk primary key(id)
);
 
# 复合主键，表级约束，并且给其重命名
create table t_user(
	id int(10);
  name varchar(32);
  email varcahr(32);
  constraint t_user_id_name_pk primary key(id,name)
);

ALTER TABLE table_name
ADD PRIMARY KEY (column1);
 
ALTER TABLE table_name
ADD PRIMARY KEY (column1, column2);

（7）自增字段 auto_increment

create table t_user(
  id int(10) primary key auto_increment,
  name varchar(32)
);

• 重置 AUTO_INCREMENT 列的值

# 重置为新的起始值
# new_start_value 是你希望 AUTO_INCREMENT 列从哪个值开始自增的新起始值。
ALTER TABLE table_name AUTO_INCREMENT = new_start_value;
 
# 重置为从 1 开始自增
ALTER TABLE table_name AUTO_INCREMENT = 1;

（8）外键约束 foreign key

外键约束（Foreign Key Constraint）在数据库中用于建立表与表之间的关系，它定义了一个表中的列或一组列，这些列的值必须在另一个表中的指定列中存在。外键约束有以下特点和用法：

• 特点：

1. 关联性： 外键用于建立表与表之间的关联关系，通过在一个表中引用另一个表中的列来实现数据关联。
2. 数据一致性： 外键约束确保了数据的一致性，保证了一个表中引用的值在另一个表中必须存在。
3. 引用完整性： 外键约束确保了引用的完整性，防止了对被引用表中的数据进行意外的删除或修改。

• 用法：

1. 建立关联： 外键用于建立表与表之间的关联关系，通常用于建立一对多或多对多的关系。
2. 数据检索： 外键可以用于快速检索相关联的数据。
3. 级联操作： 外键约束可以定义级联操作规则，如级联删除和级联更新，当引用表中的数据被修改或删除时，自动更新或删除相关的数据。

• 在MySQL中，你可以在创建表时或者使用ALTER TABLE语句添加外键约束。

CREATE TABLE table_name1 (
    column1 datatype,
    column2 datatype,
    ...
    CONSTRAINT constraint_name FOREIGN KEY (column1) REFERENCES table_name2 (referenced_column)
);

ALTER TABLE table_name1
ADD CONSTRAINT constraint_name FOREIGN KEY (column1) REFERENCES table_name2 (referenced_column);

• 在这里，table_name1 是包含外键列的表名，column1 是外键列名，constraint_name 是约束的名称（可选），table_name2 是引用表的名称，referenced_column 是引用表中被引用的列名。

（9）级联更新和级联删除

• 在 MySQL 中，级联更新（CASCADE）和级联删除（CASCADE）是外键约束的一部分，它们允许在父表中更新或删除行时，自动更新或删除子表中相关的行。

（1）级联更新（cascade）

• 当在父表中更新了主键值时，级联更新操作会自动更新子表中相关的外键值，以保持引用的一致性。

ALTER TABLE child_table
ADD CONSTRAINT fk_constraint_name
FOREIGN KEY (child_column)
REFERENCES parent_table (parent_column)
ON UPDATE CASCADE;

（2）级联删除（cascade）

• 当在父表中删除了主键值时，级联删除操作会自动删除子表中相关的行，以保持引用的一致性。

ALTER TABLE child_table
ADD CONSTRAINT fk_constraint_name
FOREIGN KEY (child_column)
REFERENCES parent_table (parent_column)
ON DELETE CASCADE;

（3）示例

假设有两个表：orders 和 order_details。orders 表包含订单信息，order_details 表包含订单详情信息，并且 order_details 表的 order_id 列是对 orders 表的 id 列的外键引用。

• 级联更新：

ALTER TABLE order_details
ADD CONSTRAINT fk_order_id
FOREIGN KEY (order_id)
REFERENCES orders (id)
ON UPDATE CASCADE;

现在，如果在 orders 表中更新了订单的主键值，所有 order_details 表中相关订单的 order_id 值也会自动更新。

• 级联删除：

ALTER TABLE order_details
ADD CONSTRAINT fk_order_id
FOREIGN KEY (order_id)
REFERENCES orders (id)
ON DELETE CASCADE;

如果在 orders 表中删除了订单，所有相关的 order_details 行也会自动删除。

筛选过滤条件

• 在MySQL中，过滤条件通常是指在查询数据时使用的条件，用于筛选出符合特定条件的数据。常见的过滤条件使用在 SELECT、UPDATE、DELETE 等语句中。

select id,name from emp where id > 3;

（1）where 筛选条件

• WHERE 关键字用于在查询语句中指定筛选条件，以便从表中选择满足特定条件的行。WHERE 子句通常在 SELECT、UPDATE 和 DELETE 语句中使用。

select *
from customers
where age > 18;

• WHERE 子句支持各种类型的条件表达式，包括比较操作符（如 =、<、>、<=、>=）、逻辑操作符（如 AND、OR、NOT）、IN 子句、BETWEEN 子句、LIKE 子句等等。

SELECT *
FROM orders
WHERE order_date BETWEEN '2022-01-01' AND '2022-12-31'
  AND status = 'Shipped';
  
# 这个查询将从 orders 表中选择订单日期在 2022 年以及订单状态为已发货的所有行。

（2）group by 分组

• 在MySQL中，GROUP BY 语句用于根据一个或多个列对结果集进行分组。分组后，可以对每个分组应用聚合函数（如 SUM、COUNT、AVG、MAX、MIN 等），以计算每个分组的汇总值。
• 假设有一个名为 orders 的表，包含订单信息，其中包括 customer_id（客户ID） 和 order_amount（订单金额）列。要计算每个客户的订单总金额，可以使用以下查询：

SELECT customer_id, SUM(order_amount) AS total_amount
FROM orders
GROUP BY customer_id;
 
# 这个查询将根据 customer_id 列对订单表进行分组，并对每个分组计算 order_amount 列的总和。结果将返回每个客户的客户ID以及其订单的总金额。

（3）max 最大值

• 在MySQL中，MAX 函数用于返回一组值中的最大值。它通常与 GROUP BY 子句一起使用，以计算每个分组中的最大值。

SELECT MAX(price) FROM products;

SELECT category, MAX(price)
FROM products
GROUP BY category;

（4）min 最小值

• 在MySQL中，MIN 函数用于返回一组值中的最小值。它也常与 GROUP BY 子句一起使用，以计算每个分组中的最小值。

SELECT MIN(column_name)
FROM table_name;

SELECT category, MIN(price)
FROM products
GROUP BY category;

（5）sum 总和

• 在MySQL中，SUM 函数用于计算一组值的总和。它通常与 GROUP BY 子句一起使用，以计算每个分组中的值的总和。

SELECT SUM(column_name)
FROM table_name;

SELECT category, SUM(amount)
FROM sales
GROUP BY category;

（6）count 行数

• 在MySQL中，COUNT 函数用于计算查询结果集中行的数量。它可以用于统计符合条件的行数，也可以与 GROUP BY 子句一起使用，以计算每个分组中的行数。

SELECT COUNT(*)
FROM table_name;

SELECT department, COUNT(*) AS num_students
FROM students
GROUP BY department;

（7）group_concat 合并

• 在 MySQL 中，GROUP_CONCAT 函数用于将查询结果集中的多个行合并成一个字符串，并以指定的分隔符分隔各个值。通常用于在 GROUP BY 子句中，将分组的结果集中的多个值合并成一个字符串返回。

SELECT column1, GROUP_CONCAT(column2 SEPARATOR ',')
FROM table_name
GROUP BY column1;

• 假设有一个名为 students 的表，包含学生信息，其中包括 student_id（学生ID）和 course_name（课程名称）列。要将每个学生所选课程的名称合并成一个字符串，可以使用以下查询：

SELECT student_id, GROUP_CONCAT(course_name) AS courses
FROM students
GROUP BY student_id;
 
# 这个查询将返回每个学生ID以及该学生所选的课程名称，课程名称之间使用逗号分隔。
 
# 也可以加冒号分隔
group_concat(name,':',salary)

（8）distinct 唯一

• 在MySQL中，DISTINCT 关键字用于从查询结果中去除重复的行，只返回唯一的行。它通常用于 SELECT 语句中，以便消除重复的值。

SELECT DISTINCT column1, column2, ...
FROM table_name;

（9）order by 排序

• 在 MySQL 中，ORDER BY 子句用于对查询结果集进行排序，可以按照一个或多个列的值进行升序或降序排序。
• ASC（升序）和 DESC（降序）是可选的关键字，用于指定排序顺序，默认为升序。

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

SELECT product_name, price
FROM products
ORDER BY price ASC;
 
# 这个查询将返回 products 表中所有产品按照价格从低到高排序的结果集。

（10）limit

• 在 MySQL 中，LIMIT 子句用于限制查询结果返回的行数，可以用于控制返回的结果集的大小。

SELECT column1, column2, ...
FROM table_name
LIMIT number_of_rows;

SELECT *
FROM students
LIMIT 10;
 
# 这个查询将返回 students 表中的前 10 条记录。

• LIMIT 子句也可以用于指定起始行和返回的行数，以便返回一个范围内的行数。例如，要返回从第 11 行开始的 10 条记录：

SELECT *
FROM students
LIMIT 10 OFFSET 10;
 
# 这个查询将返回 students 表中的第 11 条记录到第 20 条记录。OFFSET 关键字用于指定起始行数，而 LIMIT 关键字指定返回的行数。

（11）正则

• 在MySQL中，可以使用正则表达式进行模式匹配。正则表达式允许你根据特定的模式来匹配文本，并且支持在查询中使用 REGEXP 或 RLIKE 运算符。

SELECT column1, column2, ...
FROM table_name
WHERE column_name REGEXP 'pattern';
 
# column_name 是要进行模式匹配的列名。
# 'pattern' 是要匹配的正则表达式模式。

（1）匹配方式

选项	说明	例子	匹配值示例
^	匹配文本的开始字符	‘^b’ 匹配以字母 b 开头的字符串	book、big、banana、bike
$|\mathrm{匹}\mathrm{配}\mathrm{文}\mathrm{本}\mathrm{的}\mathrm{结}\mathrm{束}\mathrm{字}\mathrm{符}|‘st$’ 匹配以 st 结尾的字符串	test、resist、persist
.	匹配任何单个字符	‘b.t’ 匹配任何 b 和 t 之间有一个字符	bit、bat、but、bite
*	匹配前面的字符 0 次或多次	‘f*n’ 匹配字符 n 前面有任意个字符 f	fn、fan、faan、abcn
+	匹配前面的字符 1 次或多次	‘ba+’ 匹配以 b 开头，后面至少紧跟一个 a	ba、bay、bare、battle
?	匹配前面的字符 0 次或1次	‘sa?’ 匹配0个或1个a字符	sa、s
字符串	匹配包含指定字符的文本	‘fa’ 匹配包含‘fa’的文本	fan、afa、faad
[字符集合]	匹配字符集合中的任何一个字符	‘[xz]’ 匹配 x 或者 z	dizzy、zebra、x-ray、extra
[^]	匹配不在括号中的任何字符	‘[^abc]’ 匹配任何不包含 a、b 或 c 的字符串	desk、fox、f8ke
字符串	匹配前面的字符串至少 n 次	‘b{2}’ 匹配 2 个或更多的 b	bbb、bbbb、bbbbbbb
字符串	匹配前面的字符串至少 n 次， 至多 m 次	‘b{2,4}’ 匹配最少 2 个，最多 4 个 b	bbb、bbbb

（2）示例

• 假设有一个名为 emails 的表，其中包含 email 列，想查找所有以 .com 结尾的电子邮件地址，可以使用以下查询：

SELECT email
FROM emails
WHERE email REGEXP '\.com$';
 
# 这个查询将返回 emails 表中所有以 .com 结尾的电子邮件地址。
 
# 除了 REGEXP 运算符外，还可以使用 RLIKE 运算符执行相同的操作，例如：
SELECT email
FROM emails
WHERE email RLIKE '\.com$';

多表查询和子查询

• 多表查询和子查询都是在 MySQL 中用于检索数据的重要技术，它们允许从一个以上的表中检索数据，并且在查询中嵌套另一个查询。

（1）多表查询

• 多表查询用于从多个表中联合检索数据，通常使用 JOIN 关键字将表连接起来。常见的连接类型包括内连接、外连接（左连接、右连接、全连接）等。

SELECT orders.order_id, customers.customer_name
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id;

（1）内连接 inner join

• 内连接返回两个表中满足连接条件的行。只有在连接列的值在两个表中都存在匹配时，才会返回结果。

SELECT * FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

（2）左连接 left join

• 左连接返回左表中所有的行，以及右表中满足连接条件的行。如果右表中没有匹配的行，则返回 NULL 值。

SELECT * FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

（3）右连接 right join

• 右连接返回右表中所有的行，以及左表中满足连接条件的行。如果左表中没有匹配的行，则返回 NULL 值。

SELECT * FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

（4）全连接 full join

• 全连接返回左表和右表中所有的行，如果某个表中没有匹配的行，则返回 NULL 值。

SELECT * FROM table1
FULL JOIN table2
ON table1.column_name = table2.column_name;

（2）子查询

• 子查询是将一个查询嵌套在另一个查询中的技术。子查询通常用于从一个查询中获取结果，并将其作为另一个查询的条件或结果使用。

SELECT column1
FROM table1
WHERE column1 IN (SELECT column2 FROM table2);

（3）关键字exist

• 在 MySQL 中，EXISTS 是一个谓词，用于检查子查询是否返回任何行。如果子查询返回了至少一行结果，则 EXISTS 返回 true，否则返回 false。EXISTS 通常与 WHERE 子句一起使用。

SELECT column1, column2, ...
FROM table_name
WHERE EXISTS (subquery);

视图

• 在MySQL中，视图（View）是虚拟的表，其内容由查询定义。视图包含的行和列可以来自一个或多个基本表，也可以来自其他视图。视图的作用类似于一个存储在数据库中的查询结果集，它可以简化复杂的查询，隐藏数据结构的细节，以及提供安全性控制。

create view 表名 AS 虚拟sql查询语句

create view view_name as
select column1, column2, ...
from table_name
where ...;

触发器

• 在满足对表数据的增、删、改的情况下，自动触发的功能
• 使用触发器可以帮助我们实现监控、日志、自动处理异常等等
• 触发器可以在六中情况下自动触发 增前 增后 删前删后 改前改后

create trigger 触发器的名字
before|after insert|update|delete on table_name
for each row
begin
    -- 触发器执行的 SQL 语句
end;

• 修改MySQL默认的语句结束符 只作用于当前窗口

delimiter $
select * from user$

• 删除触发器

drop trigger 触发器的名字

事务

• 开启一个事务可以包含多条sql语句，这些sql语句要么同时成功，要么一个都别想成功，称之为事务的原子性

• 事务的作用：保证了对数据操作的安全性

• 事务具有以下四个特性，通常称为 ACID 特性：

1. 原子性（Atomicity）：事务是一个原子操作单元，不可再分割。它要么全部执行成功，要么全部失败回滚，不存在部分执行的情况。
2. 一致性（Consistency）：事务执行后，数据库状态从一个一致状态变为另一个一致状态。在事务执行过程中，数据库始终保持一致性。
3. 隔离性（Isolation）：事务的执行结果对其他事务是隔离的，即事务之间不会互相影响。隔离性保证了并发执行的事务之间不会产生不一致的结果。
4. 持久性（Durability）：一旦事务提交成功，其修改的数据将持久保存在数据库中，即使系统发生故障也不会丢失。

-- 开始事务
START TRANSACTION;
 
-- 执行一系列操作
INSERT INTO users (username, email) VALUES ('user1', 'user1@example.com');
INSERT INTO accounts (user_id, balance) VALUES (LAST_INSERT_ID(), 1000);
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 123;
 
-- 检查是否有库存
SELECT quantity FROM inventory WHERE product_id = 123;
IF quantity < 0 THEN
    -- 回滚事务
    ROLLBACK;
ELSE
    -- 提交事务
    COMMIT;
END IF;
 
# 在这个示例中：
# 事务从START TRANSACTION;开始。
 
# 然后一系列操作被执行，包括插入用户、插入账户和更新库存。
 
# 然后检查库存是否足够。如果库存不足，则回滚事务，撤销之前的操作。否则，提交事务，将更改永久保存到数据库中。
 
# 事务保证了所有操作要么全部成功，要么全部失败回滚，从而确保了数据的一致性。如果在事务过程中出现故障或错误，整个事务都将被回滚，数据库将恢复到事务开始时的状态，不会留下不一致的状态。

存储过程

• 存储过程就类似于python中的自定义函数

• 它的内部包含了一系列可以执行的sql语句，存储过程存放于MySQL服务端中，你可以直接通过调用存储过程触发内部sql语句的执行

create procedure 存储过程的名字(形参1,形参2,...)
begin
	sql代码
end
 
# 调用
call 存储过程的名字();
 
# 查看存储过程具体信息
show create procedure pro1;
 
# 查看所有存储过程
show procedure status;
 
# 删除存储过程
drop procedure pro1;

函数

• 在MySQL中，函数（Function）是一种可以接受参数并返回值的命名代码块。函数可以被视为一种封装了特定功能的子程序，它们可以在需要时被调用，并且可以返回一个值。

• MySQL支持两种类型的函数：标量函数和表函数。

1. 标量函数（Scalar Function）：这种函数接受零个或多个参数，并返回一个单一的值。常见的标量函数包括字符串函数（如 CONCAT、SUBSTRING）、数值函数（如 ABS、ROUND）、日期函数（如 NOW、DATE_FORMAT）等。

2. 表函数（Table Function）：这种函数接受零个或多个参数，并返回一个结果集。表函数常用于动态生成表格数据，例如使用 UNPIVOT 将行数据转换为列数据。

• 以下是一个简单的标量函数示例，用于计算两个数的和：

CREATE FUNCTION addNumbers(x INT, y INT)
RETURNS INT
BEGIN
    DECLARE sum INT;
    SET sum = x + y;
    RETURN sum;
END;

在这个示例中：

• CREATE FUNCTION 用于创建一个新的函数。

• addNumbers 是函数的名称。

• (x INT, y INT) 是函数的参数列表。

• RETURNS INT 指定了函数的返回类型。

• BEGIN...END 之间是函数的主体，包含了函数的逻辑。

• DECLARE 用于声明局部变量。

• RETURN 用于返回函数的结果。

• 要调用这个函数，可以像调用任何其他函数一样使用它：

SELECT addNumbers(5, 3); -- 结果为 8

• 通过创建函数，可以封装常用的功能并提高代码的可重用性和可维护性。函数还可以用于在SQL查询中进行复杂的计算或转换操作。

流程控制

• 在 MySQL 存储过程中，可以使用流程控制结构来执行条件判断、循环等操作。

1. IF 语句：用于条件判断，根据条件执行不同的代码块。

IF condition THEN
    statement;
ELSE
    statement;
END IF;

2. CASE 语句：用于多条件判断，根据不同的条件执行不同的代码块。

CASE case_expression
    WHEN value1 THEN statement;
    WHEN value2 THEN statement;
    ELSE statement;
END CASE;

3. WHILE 循环：当条件为真时，重复执行代码块。

WHILE condition DO
    statement;
END WHILE;

4. REPEAT 循环：先执行代码块，然后检查条件，只有在条件为假时才停止循环。

REPEAT
    statement;
UNTIL condition;
END REPEAT;

5. LOOP 循环：无限循环，直到遇到 LEAVE 语句。

LOOP
    statement;
    IF condition THEN
        LEAVE;
    END IF;
END LOOP;

6. LEAVE 语句：用于退出循环。

LEAVE;

索引

• 在MySQL中，索引是一种数据结构，用于加快对数据库表中数据的检索速度。索引是通过将某些列或列组合排序并存储为单独的结构来实现的，这样就可以快速地定位到要查询的数据行，而不必扫描整个表。

（1）创建索引

• 在MySQL中，可以通过以下方式创建索引：

1. 在创建表时定义索引：

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    INDEX index_name (column1, column2)
);

2. 在已存在的表上创建索引：

CREATE INDEX index_name ON table_name (column1, column2);

（2）类型

MySQL中常见的索引类型包括：

1. 单列索引：对单个列进行索引。

2. 复合索引：对多个列进行组合索引，可以加快联合查询的速度。

3. 唯一索引：确保索引列的值是唯一的。

4. 全文索引：用于全文搜索，适用于包含大量文本数据的列。

（3）使用场景

• 当经常需要通过某些列进行检索时，可以考虑创建索引，以提高查询效率。
• 在频繁进行排序或分组的列上创建索引，可以加快排序和分组操作的速度。
• 外键列通常需要索引，以提高连接操作的速度。

（4）注意事项

• 索引可以提高查询性能，但会增加写入操作的开销，因为每次写入都需要更新索引。
• 索引占用磁盘空间，并且会影响数据库的性能，因此需要权衡索引的数量和大小。
• 不是所有的列都适合创建索引，应该根据实际情况进行选择。

事务隔离机制

• 事务隔离机制是数据库管理系统中用于控制并发访问的重要特性之一。它确保在多个事务同时执行时，每个事务都能够看到一致性的数据视图，并且不会相互干扰或产生意外结果。在MySQL中，有四种标准的事务隔离级别，每种级别提供了不同程度的数据隔离和并发性。

• 这四种隔离级别分别是：

1. 读未提交（Read Uncommitted）：事务可以读取到其他事务尚未提交的数据。在这个隔离级别下，存在脏读、不可重复读和幻读的问题。

2. 读已提交（Read Committed）：事务只能读取到已经提交的数据。这个级别下可以避免脏读问题，但仍可能出现不可重复读和幻读的问题。

3. 可重复读（Repeatable Read）：事务在执行期间看到的数据保持一致，即使其他事务对数据进行了修改也不会影响当前事务的结果。在这个级别下可以避免脏读和不可重复读问题，但仍可能出现幻读问题。

4. 串行化（Serializable）：事务串行执行，相当于每个事务在执行时对数据库加锁，确保并发事务之间不会产生任何冲突。这个级别下可以避免所有的并发问题，但可能会影响系统的性能。

• 在MySQL中，默认的事务隔离级别是可重复读（Repeatable Read），但可以通过设置来修改为其他隔离级别。

• 隔离级别的选择取决于应用程序的需求和对并发性和数据一致性的要求。一般来说，隔离级别越高，数据的一致性越好，但并发性越差；而隔离级别越低，则并发性越好，但数据的一致性也可能受到影响。因此，在选择隔离级别时需要权衡考虑系统的性能和一致性需求。

锁机制

• 在数据库管理系统中，锁（Lock）是一种用于控制并发访问的机制，它可以防止多个事务同时访问或修改同一资源，从而确保数据的一致性和完整性。在MySQL中，常见的锁包括行级锁、表级锁和页级锁等。

• 以下是MySQL中常见的锁机制：

1. 行级锁（Row-level Lock）：行级锁是最细粒度的锁，它可以在数据表的单个行上进行加锁。行级锁可以防止其他事务修改同一行的数据，从而确保数据的完整性。MySQL中的InnoDB存储引擎默认使用行级锁来实现事务隔离。

2. 表级锁（Table-level Lock）：表级锁是在整个数据表上进行加锁，它可以防止其他事务访问或修改整个表。表级锁通常用于对整个表进行操作时，例如进行DDL操作或备份数据等。

3. 页级锁（Page-level Lock）：页级锁是在数据表的页面（页）上进行加锁，它可以防止其他事务访问或修改同一页面的数据。页级锁通常用于MyISAM等存储引擎中，而InnoDB通常使用行级锁。

4. 意向锁（Intention Lock）：意向锁是一种用于表级锁的辅助锁，它表示事务打算在表上加锁的意图，可以减少锁冲突的概率。意向锁分为意向共享锁（IS）和意向排他锁（IX），它们分别表示事务打算在表上加共享锁或排他锁。

5. 记录锁（Record Lock）：记录锁是行级锁的一种特殊形式，用于在事务中对数据行进行加锁，以防止其他事务修改相同的数据行。记录锁可以分为共享锁（S锁）和排他锁（X锁），分别用于读操作和写操作。

• 这些锁机制在数据库中起着非常重要的作用，它们可以确保数据的一致性和完整性，并且防止并发事务之间产生冲突。在使用锁机制时，需要注意避免死锁（Deadlock）等并发问题，以确保系统的稳定性和性能。