库的增删改查
(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
-
查看引擎

创建表的完整语法
(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 |
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)
| |
| create table user( |
| id int, |
| name char(16), |
| gender enum('male','female','others') |
| ); |
| |
| |
| 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)介绍
(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
- 唯一性: 主键列中的值必须是唯一的,不允许重复。
- 非空性: 主键列的值不能为NULL。
- 稳定性: 主键值在数据的生命周期中不会改变,即使它是由系统自动生成的。
- 唯一标识: 主键用于唯一标识表中的每一行数据,确保每一行都具有唯一的标识符。
- 索引: 主键列会自动创建索引,加快数据检索速度。
- 关系: 主键经常被用作其他表的外键,用于建立表之间的关系。
- 数据完整性: 主键约束确保了数据的完整性和一致性,防止表中出现重复的数据。
| # 列级约束 |
| 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) |
| ); |
| # 重置为新的起始值 |
| # 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)在数据库中用于建立表与表之间的关系,它定义了一个表中的列或一组列,这些列的值必须在另一个表中的指定列中存在。外键约束有以下特点和用法:
- 关联性: 外键用于建立表与表之间的关联关系,通过在一个表中引用另一个表中的列来实现数据关联。
- 数据一致性: 外键约束确保了数据的一致性,保证了一个表中引用的值在另一个表中必须存在。
- 引用完整性: 外键约束确保了引用的完整性,防止了对被引用表中的数据进行意外的删除或修改。
- 建立关联: 外键用于建立表与表之间的关联关系,通常用于建立一对多或多对多的关系。
- 数据检索: 外键可以用于快速检索相关联的数据。
- 级联操作: 外键约束可以定义级联操作规则,如级联删除和级联更新,当引用表中的数据被修改或删除时,自动更新或删除相关的数据。
- 在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 |
’ 匹配以 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 |
| |
| end; |
| delimiter $ |
| select * from user$ |
事务
- 原子性(Atomicity):事务是一个原子操作单元,不可再分割。它要么全部执行成功,要么全部失败回滚,不存在部分执行的情况。
- 一致性(Consistency):事务执行后,数据库状态从一个一致状态变为另一个一致状态。在事务执行过程中,数据库始终保持一致性。
- 隔离性(Isolation):事务的执行结果对其他事务是隔离的,即事务之间不会互相影响。隔离性保证了并发执行的事务之间不会产生不一致的结果。
- 持久性(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;开始。 |
| |
| # 然后一系列操作被执行,包括插入用户、插入账户和更新库存。 |
| |
| # 然后检查库存是否足够。如果库存不足,则回滚事务,撤销之前的操作。否则,提交事务,将更改永久保存到数据库中。 |
| |
| # 事务保证了所有操作要么全部成功,要么全部失败回滚,从而确保了数据的一致性。如果在事务过程中出现故障或错误,整个事务都将被回滚,数据库将恢复到事务开始时的状态,不会留下不一致的状态。 |
存储过程
| create procedure 存储过程的名字(形参1,形参2,...) |
| begin |
| sql代码 |
| end |
| |
| # 调用 |
| call 存储过程的名字(); |
| |
| # 查看存储过程具体信息 |
| show create procedure pro1; |
| |
| # 查看所有存储过程 |
| show procedure status; |
| |
| # 删除存储过程 |
| drop procedure pro1; |
函数
-
标量函数(Scalar Function):这种函数接受零个或多个参数,并返回一个单一的值。常见的标量函数包括字符串函数(如 CONCAT
、SUBSTRING
)、数值函数(如 ABS
、ROUND
)、日期函数(如 NOW
、DATE_FORMAT
)等。
-
表函数(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
用于返回函数的结果。
-
要调用这个函数,可以像调用任何其他函数一样使用它:
- 通过创建函数,可以封装常用的功能并提高代码的可重用性和可维护性。函数还可以用于在SQL查询中进行复杂的计算或转换操作。
流程控制
- 在 MySQL 存储过程中,可以使用流程控制结构来执行条件判断、循环等操作。
- IF 语句:用于条件判断,根据条件执行不同的代码块。
| IF condition THEN |
| statement; |
| ELSE |
| statement; |
| END IF; |
- CASE 语句:用于多条件判断,根据不同的条件执行不同的代码块。
| CASE case_expression |
| WHEN value1 THEN statement; |
| WHEN value2 THEN statement; |
| ELSE statement; |
| END CASE; |
- WHILE 循环:当条件为真时,重复执行代码块。
| WHILE condition DO |
| statement; |
| END WHILE; |
- REPEAT 循环:先执行代码块,然后检查条件,只有在条件为假时才停止循环。
| REPEAT |
| statement; |
| UNTIL condition; |
| END REPEAT; |
- LOOP 循环:无限循环,直到遇到 LEAVE 语句。
| LOOP |
| statement; |
| IF condition THEN |
| LEAVE; |
| END IF; |
| END LOOP; |
- LEAVE 语句:用于退出循环。
索引
- 在MySQL中,索引是一种数据结构,用于加快对数据库表中数据的检索速度。索引是通过将某些列或列组合排序并存储为单独的结构来实现的,这样就可以快速地定位到要查询的数据行,而不必扫描整个表。
(1)创建索引
- 在创建表时定义索引:
| CREATE TABLE table_name ( |
| column1 datatype, |
| column2 datatype, |
| ... |
| INDEX index_name (column1, column2) |
| ); |
- 在已存在的表上创建索引:
| CREATE INDEX index_name ON table_name (column1, column2); |
(2)类型
MySQL中常见的索引类型包括:
-
单列索引:对单个列进行索引。
-
复合索引:对多个列进行组合索引,可以加快联合查询的速度。
-
唯一索引:确保索引列的值是唯一的。
-
全文索引:用于全文搜索,适用于包含大量文本数据的列。
(3)使用场景
- 当经常需要通过某些列进行检索时,可以考虑创建索引,以提高查询效率。
- 在频繁进行排序或分组的列上创建索引,可以加快排序和分组操作的速度。
- 外键列通常需要索引,以提高连接操作的速度。
(4)注意事项
- 索引可以提高查询性能,但会增加写入操作的开销,因为每次写入都需要更新索引。
- 索引占用磁盘空间,并且会影响数据库的性能,因此需要权衡索引的数量和大小。
- 不是所有的列都适合创建索引,应该根据实际情况进行选择。
事务隔离机制
-
读未提交(Read Uncommitted):事务可以读取到其他事务尚未提交的数据。在这个隔离级别下,存在脏读、不可重复读和幻读的问题。
-
读已提交(Read Committed):事务只能读取到已经提交的数据。这个级别下可以避免脏读问题,但仍可能出现不可重复读和幻读的问题。
-
可重复读(Repeatable Read):事务在执行期间看到的数据保持一致,即使其他事务对数据进行了修改也不会影响当前事务的结果。在这个级别下可以避免脏读和不可重复读问题,但仍可能出现幻读问题。
-
串行化(Serializable):事务串行执行,相当于每个事务在执行时对数据库加锁,确保并发事务之间不会产生任何冲突。这个级别下可以避免所有的并发问题,但可能会影响系统的性能。
锁机制
-
行级锁(Row-level Lock):行级锁是最细粒度的锁,它可以在数据表的单个行上进行加锁。行级锁可以防止其他事务修改同一行的数据,从而确保数据的完整性。MySQL中的InnoDB存储引擎默认使用行级锁来实现事务隔离。
-
表级锁(Table-level Lock):表级锁是在整个数据表上进行加锁,它可以防止其他事务访问或修改整个表。表级锁通常用于对整个表进行操作时,例如进行DDL操作或备份数据等。
-
页级锁(Page-level Lock):页级锁是在数据表的页面(页)上进行加锁,它可以防止其他事务访问或修改同一页面的数据。页级锁通常用于MyISAM等存储引擎中,而InnoDB通常使用行级锁。
-
意向锁(Intention Lock):意向锁是一种用于表级锁的辅助锁,它表示事务打算在表上加锁的意图,可以减少锁冲突的概率。意向锁分为意向共享锁(IS)和意向排他锁(IX),它们分别表示事务打算在表上加共享锁或排他锁。
-
记录锁(Record Lock):记录锁是行级锁的一种特殊形式,用于在事务中对数据行进行加锁,以防止其他事务修改相同的数据行。记录锁可以分为共享锁(S锁)和排他锁(X锁),分别用于读操作和写操作。
- 这些锁机制在数据库中起着非常重要的作用,它们可以确保数据的一致性和完整性,并且防止并发事务之间产生冲突。在使用锁机制时,需要注意避免死锁(Deadlock)等并发问题,以确保系统的稳定性和性能。
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步