数据库（从基本概念到事务操作）

已于 2023-06-23 21:05:49 修改
阅读量824 收藏
点赞数 3
分类专栏： 数据库 文章标签： 数据库
于 2022-11-25 23:03:18 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/jc15274630894/article/details/128046014
版权

文章目录

一、数据库基本概念

1.1 数据

数据（data）是数据库存储的基本对象，是描述事物的符号记录；

1.2 数据库

数据库（ dataBase，DB ）是长期储存在计算机内、有组织的、可共享的大量数据的集合；

数据库的基本特征：

  1. 数据按一定的数据模型组织、描述和储存；
  2. 可为各种用户共享；
  3. 冗余度小；
  4. 数据独立性搞；
  5. 易扩展；

1.3 数据库管理系统

数据库管理系统（ DBMS ）是位于用户与操作系统之间的一层数据管理软件，是一个大型复杂的软件系统；

​ DBMS 的用途：

  • 科学地组织和存储数据；
  • 高效地获取和维护数据；

1.4 数据库系统

数据库系统（ Database System ，DBS ）是计算机系统中引入数据库后的系统构成；

数据库系统的构成：

  1. 数据库；
  2. 硬件平台；
  3. 软件（ 应用程序 ）；
  4. 数据库管理员；

数据库系统的设计目标是允许用户逻辑地处理数据，而不必涉及这些数据在计算机中是如何存放的，在数据组织和用户应用之间提供某种程度的独立性；

二、数据库模型

2.1 数据库系统的三种模式

​ 数据库系统可分为：

  1. 外模式（子模式、用户模式）：

（1）数据库用户（ 包括应用程序员和最终用户 ）使用的局部数据的逻辑结构和特征的描述；

（2）数据库用户的数据视图，是与某一应用有关的数据的逻辑表示；

（3）外模式的地位：介于模式与应用之间；

（4）模式与外模式的关系：一对多；

（5）外模式通常是模式的子集；

（6）一个数据库可以有多个外模式，反映了不同的用户的应用需求、看待数据的方式、对数据保密的要求；

（7）外模式的用途：

  • 保证数据库安全性的一个有力措施；
  • 每个用户只能看见和访问所对应的外模式中的数据；
  1. 模式（概念模式、逻辑模式）：

  • 数据库中全体数据的逻辑结构和特征的描述；

  • 所有用户的公共数据视图，综合了所有用户的需求

  • 一个数据库只有一个模式；

  1. 内模式（存储模式）：
  • 是数据物理结构和存储方式的描述；
  • 是数据在数据库内部的表示方式；
  • 一个数据库只有一个内模式；

2.2 三个级别

与三级模式相对应，数据库系统可以划分为三个抽象级：

  1. 用户级数据库：对应于外模式，是用户看到和使用的数据库，又称用户视图，一个数据库可以有多个不同的用户视图；
  2. 概念级数据库：对应于概念模式，是所有的用户视图的最小并集，一个数据库应用系统只有一个 DBA 视图；
  3. 物理级数据库：对应于内模式，是数据库的低层表示，它描述数据的实际存储组织，是最接近于物理存储的，又称内部视图；

三、SQL语言基本规则

3.1 SQL 语言基本定义

  1. SQL 是结构化查询语言（ Structured Query Language ）的缩写，其功能包括数据查询、数据操控、数据定义和数据控制四个部分；
  • SQL 语言简洁、方便实用、功能齐全，已成为目前应用最广的关系数据库语言；
  • SQL 既是自含式语言（ 联机交互 ），又是嵌入式语言（ 宿主语言 ）；
  1. 两个基本概念

基本表：

是独立存在的表，不是由其它的表导出的表，一个关系对应一个基本表，一个或多个基本表对应一个存储文件；

视图：

视图是一个虚拟的表，是一个或几个基本表导出的表，数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中，当基本表中的数据发生变化时，从视图中查询出来的数据也随之改变；

3.2 SQL 语言分类

  1. DDL（Data Definition Language）：数据定义语言，用来定义数据库对象（ 数据库，表，字段 ）；
  2. DML（ Data Manipulation Language）：数据操作语言，用来对数据表中的数据进行增删改；
  3. DQL（ Data Query Language）：数据查询语言，用来查询数据库中表的记录；
  4. DCL（ Data Control Language）：数据控制语言，用来创建数据库用户、控制数据库的访问权限；

3.3 数据类型

MySql 中的数据类型有很多,主要分为三类:数值类型,字符串类型,日期时间类型;

部分数值类型
> > > > > > > >
类型
说明
TINYINT超小整数-有效取值范围(-128, 127)
SMALLINT小整数-有效取值范围(-32768, 32767)
INT 或 INTEGER超小整数-有效取值范围(-128, 127)
TINYINT整数-有效取值范围(-2 147 483 648, 2 147 483 647)
BOOL布尔类型，只有 true 和 false 两个有效值；零值被认为是 false，非零值被认为是 true。 (注意，MySQL 并不真正支持 BOOL 类型，BOOL 是TINYINT(1)的别名)
FLOAT(size, d)单精度浮点数类型，4 个字节大小。size 参数用来指定数字的总个数，d 参数用来指定小数部分（小数点后边）的数字个数。
DOUBLE(size, d)双精度浮点数类型，size 参数用来指定数字的总个数，d 参数用来指定小数部分（小数点后边）的数字个数。
部分字符串类型
> > > > > > > >
类型
说明
CHAR(size)用于表示固定长度的字符串，该字符串可以包含数字、字母和特殊字符。size 的大小可以是从 0 到 255 个字符，默认值为 1。
VARCHAR(size)用于表示可变长度的字符串，该字符串可以包含数字、字母和特殊字符。size 的大小可以是从 0 到 65535 个字符。
TINYTEXT表示一个最大长度为 255（28-1）的字符串文本。
TEXT(size)表示一个最大长度为 65,535（216-1）的字符串文本，也即 64KB。
MEDIUMTEXT表示一个最大长度为 16,777,215（224-1）的字符串文本，也即 16MB。
LONGTEXT表示一个最大长度为 4,294,967,295（232-1）的字符串文本，也即 4GB。
ENUM(val1, val2, val3,...)字符串枚举类型，最多可以包含 65,535 个枚举值。插入的数据必须位于列表中，并且只能命中其中一个值；如果不在，将插入一个空值。
部分日期类型
> > > > > >
类型
说明
DATE日期类型，格式为 YYYY-MM-DD，取值范围从 '1000-01-01' 到 '9999-12-31'。
DATETIME(fsp)日期和时间类型，格式为 YYYY-MM-DD hh:mm:ss，取值范围从 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'。
TIMESTAMP(fsp)时间戳类型，它存储的值为从 Unix 纪元（'1970-01-01 00:00:00' UTC）到现在的秒数。TIMESTAMP 的格式为 YYYY-MM-DD hh:mm:ss，取值范围从 '1970-01-01 00:00:01' UTC 到 '2038-01-09 03:14:07' UTC。
TIME(fsp)时间类型，格式为 hh:mm:ss，取值范围从 '-838:59:59' 到 '838:59:59'。
YEAR四位数字的年份格式，允许使用从 1901 到 2155 之间的四位数字的年份。此外，还有一个特殊的取值，就是 0000。

四、SQL 通用语言

4.1 启动和连接 SQL 服务

  1. MySQL服务启动（以管理员运行）：
-- 启动服务
net start mysql80
-- 关闭服务
net stop mysql80
  1. 客户端连接：
-- 写法一
mysql [-h 127.0.0.1] [-p 3306] -u root -p
-- 写法二
MySQL -u root -p

4.2 数据定义语言（ DDL ）

  1. 查询：
-- 查询所有数据库
SHOW DATABASES;
-- 查询当前数据库
SELECT DATABASE();
-- 查询当前数据库所有表
SHOW TABLES;
-- 查询表结构
DESC 表名；
-- 查询指定表的建表语句
SHOW CREATE TABLE 表名;
  1. 创建：
/*
IF NOT EXISTS：如果不存在
DEFAULT CHARSET：指定默认字符集
COLLATE 指定排序规则
*/
-- 创建一个数据库
CREATE DATABASE [ IF NOT EXISTS ] 数据库名 [ DEFAULT CHARSET 字符集 ] [ COLLATE 排序规则 ]
-- 创建一个表
CREATE TABLE 表名(
	字段1 字段1类型[ COMMENT 字段1注释 ],
	字段2 字段2类型[ COMMENT 字段2注释 ],
	字段3 字段3类型[ COMMENT 字段3注释 ],
	......
	字段n 字段n类型[ COMMENT 字段n注释 ],
)[ COMMENT 表注释 ];
  1. 删除：
-- IF EXISTS：如果存在
DROP DATABASE [ IF EXISTS ] 数据库名;
-- 删除字段
ALTER TABLE 表名 DROP 字段名;
-- 删除表
DROP TABLE [IF EXISTS] 表名;
-- 删除指定表，并重新创建此表
TRUNCATE TABLE 表名;
  1. 使用：
USE 数据库名;
  1. 修改：
-- 添加字段
ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];
-- 修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
-- 修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度)[COMMENT 注释][约束];
-- 修改表名
ALTER TABLE 表名 RENAME TO 新表名;

4.3 数据操作语言（DML）

  1. 添加数据（INSERT）：
  • 插入数据时，指定的字段顺序需要与值的顺序是一一对应的；
  • 字符串和日期类型数据应该包含在引号中；
  • 插入的数据大小应该在规定的范围内；
-- 给指定字段添加数据
INSERT INTO 表名(字段名1,字段名2,...) VALUE(值1,值2,...);
-- 给全部字段添加数据
INSERT INTO 表名 VALUE (值1,值2,...);
-- 批量添加数据
#写法一
INSERT INTO 表名 (字段1,字段2,...) VALUE (值1,值2,...),(值1,值2,...),...,(值1,值2,...);
#写法二
INSERT INTO 表名 VALUE (值1,值2,...),(值1,值2,...),...,(值1,值2,...);
  1. 修改数据（UPDATE）：
  • 如果不设置修改表的条件，则表示修改整张表的数据；
-- 修改一个值
UPDATE 表名 SET 字段名=值 [WHERE 条件];
-- 修改多个值
UPDATE 表名 SET 字段1=值1,字段2=值2,...,字段n=值n [WHERE 条件];
  1. 删除数据（DELETE）：
  • 删除语句不能只删除某一个字段的值（可以利用UPDATE将值设置为 null 相当于删除了一个字段的值）；
  • 删除语句如果没有条件会删除整张表的数据；
-- 删除一条数据
DELETE FROM 表名 [WHERE 条件];

4.4 数据查询语言（DQL）

  1. DQL 语法：
SELECT
	字段列表
FROM
	表名列表
WHERE
	条件列表
GROUP BY
	分组字段查询
HAVING
	分组后条件查询
ORDER BY
	排序字段列表
LIMIT
	分页参数
  1. 基础查询
-- 查询多个字段
#查询指定字段
SELECT 字段1,字段2,字段3... FROM 表名;
#查询所有字段
SELECT * FROM 表名;
-- 查询后设置别名 AS
SELECT 字段1[AS 别名1],字段2[AS 别名2],...,字段n[AS 别名n] FROM 表名;
-- 去除重复记录 DISTINCT
SELECT DISTINCT 字段列表 FROM 表名;
  1. 条件查询
  • WHERE 条件支持的有比较运算符和逻辑运算符；
SELECT 字段列表 FROM 表名 WHERE 条件列表;

以下是常用的条件运算符：

> > > > > > > > > >
比较运算符
说明
>大于
≥大于等于
<小于
≤小于等于
<>或!=不等于
BETWEEN...AND...在某个范围之内（含最小、最大值）
IN(...)在 IN 之后的列表中的值多选一
LIKE 占位符模糊匹配（_匹配单个字符，%匹配任意个字符）
IS NULL是 NULL
> > > >
逻辑运算符
说明
AND 或 &&并且
OR 或 ||或者
NOT 或 !非
  1. 聚合函数
  • 聚合函数：聚合函数又叫组函数,通常是对表中的数据进行统计和计算,一般结合分组(group by)来使用,用于统计和计算分组数据；
SELECT 聚合函数(字段列表) FROM 表名；

常见的聚合函数：

> > > > > >
函数
说明
COUNT()其作用主要是返回每个组的行数,也会返回有NULL值的列，可用于数字和字符列。
SUM()主要用于返回表达式中所有的总和，忽略NULL值，仅用于数字列。
AVG()计算一组值或表达式的平均值。
MIN()返回表达式中的最小值，忽略NULL值，可用于数字、字符和日期时间列。
MAX()返回表达式中的最大值，忽略NULL值，可用于数字、字符和日期时间列。
  1. 分组查询
  • 分组查询就是将查询结果按照指定字段进行分组,字段中数据相等的分为一组；
  • GRPUP BY 分组；
  • HAVING 过滤；
SELECT 字段列表 [分组别名] FROM 表名 [WHERE 条件] GROUP BY 分组字段名[HAVING 分组后过滤条件];

WHERE 和 HAVING 区别：

执行时机：WHERE 是分组之前进行过滤，不满足 WHERE 条件不参与分组，而 HAVING 是分组之后对结果进行过滤；

判断条件不同：WHERE 不能对聚合函数进行判断，而 HAVING 可以；

  1. 排序查询
  • 排序查询就是将查询的结果集按照指定的字段升序或降序显示；
  • 如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序；
  • ORDER BY 排序；
  • ASC：升序（默认值）；
  • DESC：降序；
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2;
  1. 分页查询
  • 当要显示的数据,一页显示不全,需要分页提交 SQL 请求；
  • 起始索引从 0 开始,起始索引 = (查询页码-1) * 每页显示记录数;
  • 分页查询在不同的数据库有不同的实现，MySql 中是 LIMIT 关键字；
  • 如果查询的是第一页数据可以省略起始索引；
SELECT 字段列表 FROM 表名 LIMT 起始索引,查询记录数;
  1. DQL 执行顺序

FROM => WHERE => GROUP BY => HAVING => SELECT => ORDER BY => LIMIT;

4.5 数据控制语言（DCL）

  1. 管理用户
-- 用户信息是存储在mysql数据库中的，管理用户之前要先使用mysql数据库
USE mysql;
-- 查询user（mysql 数据库中的 user 存储的是当前数据库的所有用户）表的所有信息
SELECT * FROM user;

通过查询可以看到数据库中共有如下四个用户：

在这里插入图片描述

四个用户的区别：

  • mysql.infoschema 用户：是 MySQL 数据库的系统用户，用来管理和访问系统自带实例 information_schema （信息数据库，保存着关于 MySQL 服务器所维护的所有其他数据库的信息）示例的；
  • mysql.session 用户：插件内部使用来访问服务器，该用户已被锁定，客户端无法连接；
  • mysql.sys 用户：用于 sys schema 中对象的定义，使用 mysql.sys 用户可避免 DBA 重命名或者删除 root 用户时发生的问题，该用户已被锁定，客户端无法连接；
  • root 用户：用户拥有所有权限，可执行任何操作；
  1. 创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

示例：

create user 'caterpillar'@'localhost' identified by '123456';

在这里插入图片描述

localhost 表示的是当前本机，如果想让其在任意主机都能访问该数据库则将主机名写为 % ；

  1. 修改用户密码
-- mysql_native_password 本地密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';
  1. 删除用户
DROP USER'用户名'@'主机名';
  1. 权限控制
  • 多个权限之间，使用逗号分隔；
  • 授权时数据库和表可以使用通配符 * 表示所有；
-- 查询权限
SHOW GRANTS FOR '用户名'@'主机名';
-- 授予权限
GRANTS 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
-- 撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
MySql 中的常见权限
> > > >
权限
说明
ALL,ALL PRIVILEGES所有权限
SELECT查询数据
INSERT插入数据
UPDATE修改数据
DELETE删除数据
ALTER修改表
DROP删除数据库/表/视图
CREATE创建数据库/表

五、函数

5.1 字符串函数

常见的字符串函数
> > > > > > > > > > >
函 数
功 能
CONCAT(str1,str2,...,strn)将str1,str2,...,strn连接为一个完整的字符串
INSERT(str,x,y,instr) 将字符串str从第x开始，y个字符串长度的子串替换为字符串instr
LOWER(str)将字符串str中的所有字母变成小写
UPPER(str)将字符串str中的所有字母变成大写
LEFT(str,x)返回字符串最左边的x个字符
RIGHT(str,x)返回字符串最右边的x个字符
LPAD(str,n,pad)使用字符串pad对字符串str最左边进行填充，直到长度为n个字符长度
RPAD(str,n,pad)使用字符串pad对字符串str最右边进行填充，直到长度为n个字符长度
LTRIM(str)去掉str左边的空格
RTRIM(str)去掉str右边的空格
REPEAT(str,x)返回字符串str重复x次的结果
REPLACE(str,a,b)使用字符串b替换字符串str中所有出现的字符串a
STRCMP(str1,str2)根据 ASCII 比较字符串str1和str2，大于结果为1、小于为-1、相等为0
TRIM(str)去掉字符串行头和行尾的空格
SUBSTRING(str,x,y)返回字符串str中从x位置起y个字符串长度的字符串

示例：

-- 获取'hello'和'world'的拼接形式
SELECT CONCAT('hello','world');
/*
输出：
mysql> SELECT CONCAT('hello','world');
+-------------------------+
| CONCAT('hello','world') |
+-------------------------+
| helloworld              |
+-------------------------+
1 row in set (0.00 sec)
*/
-- 将 'caterpillar' 从第2个字符开始数6个替换成 'hello'
SELECT INSERT('caterpillar',2,6,'hello');
/*
输出：
mysql> SELECT INSERT('caterpillar',2,6,'hello');
+-----------------------------------+
| INSERT('caterpillar',2,6,'hello') |
+-----------------------------------+
| chellollar                        |
+-----------------------------------+
1 row in set (0.00 sec)
*/
-- 获取 'HELLO'的小写形式
SELECT LOWER('HELLO');
/*
输出：
mysql> SELECT LOWER('HELLO');
+----------------+
| LOWER('HELLO') |
+----------------+
| hello          |
+----------------+
1 row in set (0.00 sec)
*/
-- 获取'hello'的大写形式
SELECT UPPER('hello');
/*
输出：
mysql> SELECT UPPER('hello');
+----------------+
| UPPER('hello') |
+----------------+
| HELLO          |
+----------------+
1 row in set (0.00 sec)
*/
-- 获取 'caterpillar' 的前5个字符
SELECT LEFT('caterpillar',5);
/*
输出：
mysql> SELECT LEFT('caterpillar',5);
+-----------------------+
| LEFT('caterpillar',5) |
+-----------------------+
| cater                 |
+-----------------------+
1 row in set (0.00 sec)
*/
-- 获取 'caterpillar' 的后6个字符
SELECT RIGHT('caterpillar',6);
/*
输出：
mysql> SELECT RIGHT('caterpillar',6);
+------------------------+
| RIGHT('caterpillar',6) |
+------------------------+
| pillar                 |
+------------------------+
1 row in set (0.00 sec)
*/
-- 在 'caterpillar' 前填充 'AA' 直到长度达到20
SELECT LPAD('caterpillar',20,'AA');
/*
输出：
mysql> SELECT LPAD('caterpillar',20,'AA');
+-----------------------------+
| LPAD('caterpillar',20,'AA') |
+-----------------------------+
| AAAAAAAAAcaterpillar        |
+-----------------------------+
1 row in set (0.00 sec)
 */
-- 在 'caterpillar' 后填充 'ZZ' 直到长度达到20
SELECT RPAD('caterpillar',20,'ZZ');
/*
输出：
 mysql> SELECT RPAD('caterpillar',20,'ZZ');
+-----------------------------+
| RPAD('caterpillar',20,'ZZ') |
+-----------------------------+
| caterpillarZZZZZZZZZ        |
+-----------------------------+
1 row in set (0.00 sec)
 */
-- 去除 '  hello world  ' 左边的空格
SELECT LTRIM('  hello world  ');
/*
输出：
 mysql> SELECT LTRIM('  hello world  ');
+--------------------------+
| LTRIM('  hello world  ') |
+--------------------------+
| hello world              |
+--------------------------+
1 row in set (0.00 sec)
 */
-- 去除 '  hello world  ' 右边的空格
SELECT RTRIM('  hello world  ');
/*
输出：
 mysql> SELECT RTRIM('  hello world  ');
+--------------------------+
| RTRIM('  hello world  ') |
+--------------------------+
|   hello world            |
+--------------------------+
1 row in set (0.00 sec)
 */
-- 将 'caterpillar ' 重复五次
SELECT REPEAT('caterpillar ',5);
/*
输出：
 mysql> SELECT REPEAT('caterpillar ',5);
+--------------------------------------------------------------+
| REPEAT('caterpillar ',5)                                     |
+--------------------------------------------------------------+
| caterpillar caterpillar caterpillar caterpillar caterpillar  |
+--------------------------------------------------------------+
1 row in set (0.00 sec)
 */
-- 使用 'CATER' 替换 在 'caterpillar caterpillar' 出现的所有 'cater'
SELECT REPLACE('caterpillar caterpillar','cater','CATER');
/*
输出：
 mysql> SELECT REPLACE('caterpillar caterpillar','cater','CATER');
+----------------------------------------------------+
| REPLACE('caterpillar caterpillar','cater','CATER') |
+----------------------------------------------------+
| CATERpillar CATERpillar                            |
+----------------------------------------------------+
1 row in set (0.00 sec)
 */
-- 比较 'abcc' 的 'accc' 大小
SELECT STRCMP('abcc','accc');
/*
输出：
 mysql> SELECT STRCMP('abcc','accc');
+-----------------------+
| STRCMP('abcc','accc') |
+-----------------------+
|                    -1 |
+-----------------------+
1 row in set (0.00 sec)
 */
-- 去除 '  hello world  ' 头尾的空格
SELECT TRIM('  hello world  ');
/*
输出：
 mysql> SELECT TRIM('  hello world  ');
+-------------------------+
| TRIM('  hello world  ') |
+-------------------------+
| hello world             |
+-------------------------+
1 row in set (0.00 sec)
 */
-- 获取 'caterpillar' 从第6个字符开始长度为4的字符串
SELECT SUBSTRING('caterpillar',6,4);
/*
输出：
mysql> SELECT SUBSTRING('caterpillar',6,4);
+------------------------------+
| SUBSTRING('caterpillar',6,4) |
+------------------------------+
| pill                         |
+------------------------------+
1 row in set (0.00 sec)
 */

5.2 数值函数

常见的数值函数
> > > > > > > > >
函 数
功 能
ABS(x)返回数值x的绝对值
CEIL(x)返回大于或等于x的最小整数值
FLOOR(x)返回小于或等于x的最大整数值
MOD(x,y)返回x除以y的余数
RAND()返回0~1内的随机数
RAND(x)返回0~1内的随机数，并x作为标记标记一样的随机数数值相同
ROUND(x)返回x四舍五入后的数值
ROUND(x,y)返回x四舍五入后有y位小数的数值
TRUNCATE(x,y)返回数值x且截断为y位小数的数值
LEAST(NUM1,NUM2,...)返回数据组中的最小值
GREATEST(NUM1,NUM2,...)返回数据组中的最大值
SIGN(x)返回x的正负值，正数返回1，负数返回-1，0返回0
SQRT(x)计算x的平方根

示例：

-- 返回 -1 的绝对值
SELECT ABS(-1);
/*
输出：
mysql> SELECT ABS(-1);
+---------+
| ABS(-1) |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)
*/
-- 将1.5向上取整
SELECT CEIL(1.5);
/*
输出：
mysql> SELECT CEIL(1.5);
+-----------+
| CEIL(1.5) |
+-----------+
|         2 |
+-----------+
1 row in set (0.00 sec)
*/
-- 将1.5向下取整
SELECT FLOOR(1.5);
/*
输出：
mysql> SELECT FLOOR(1.5);
+------------+
| FLOOR(1.5) |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)
*/
-- 求 32 除以 3 的余数
SELECT MOD(32,3);
/*
输出：
mysql> SELECT MOD(32,3);
+-----------+
| MOD(32,3) |
+-----------+
|         2 |
+-----------+
1 row in set (0.00 sec)
*/
-- 返回一个 0 - 1 之间的随机数
SELECT RAND();
/*
输出：
mysql> SELECT RAND();
+--------------------+
| RAND()             |
+--------------------+
| 0.8252376120772563 |
+--------------------+
1 row in set (0.00 sec)
*/
-- 标记 1、2 两个 0 - 1 的随机数
SELECT RAND(1),RAND(1),RAND(2),RAND(2);
/*
输出：
mysql> SELECT RAND(1),RAND(1),RAND(2),RAND(2);
+---------------------+---------------------+--------------------+--------------------+
| RAND(1)             | RAND(1)             | RAND(2)            | RAND(2)            |
+---------------------+---------------------+--------------------+--------------------+
| 0.40540353712197724 | 0.40540353712197724 | 0.6555866465490187 | 0.6555866465490187 |
+---------------------+---------------------+--------------------+--------------------+
1 row in set (0.00 sec)
*/
-- 对 4.56 四舍五入
SELECT ROUND(4.56);
/*
输出：
mysql> SELECT ROUND(4.56);
+-------------+
| ROUND(4.56) |
+-------------+
|           5 |
+-------------+
1 row in set (0.00 sec)
*/
-- 对 4.51624 保留两位小数四舍五入
SELECT ROUND(4.51624,2);
/*
输出：
mysql> SELECT ROUND(4.51624,2);
+------------------+
| ROUND(4.51624,2) |
+------------------+
|             4.52 |
+------------------+
1 row in set (0.00 sec)
*/
-- 对 2.3456 保留3位小数
SELECT TRUNCATE(2.3456,3);
/*
输出：
mysql> SELECT TRUNCATE(2.3456,3);
+--------------------+
| TRUNCATE(2.3456,3) |
+--------------------+
|              2.345 |
+--------------------+
1 row in set (0.00 sec)
*/
-- 返回数据组 (4,2,5,1,4,2) 中的最小值
SELECT LEAST(4,2,5,1,4,2);
/*
输出：
mysql> SELECT LEAST(4,2,5,1,4,2);
+--------------------+
| LEAST(4,2,5,1,4,2) |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.00 sec)
*/
-- 返回数据组 (4,2,5,1,4,2) 中的最大值
SELECT GREATEST(4,2,5,1,4,2);
/*
输出：
mysql> SELECT GREATEST(4,2,5,1,4,2);
+-----------------------+
| GREATEST(4,2,5,1,4,2) |
+-----------------------+
|                     5 |
+-----------------------+
1 row in set (0.00 sec)
*/
-- 返回 -5 的正负值
SELECT SIGN(-5);
/*
输出：
mysql> SELECT SIGN(-5);
+----------+
| SIGN(-5) |
+----------+
|       -1 |
+----------+
1 row in set (0.00 sec)
*/
-- 返回 9 的平方根
SELECT SQRT(9);
/*
输出：
mysql> SELECT SQRT(9);
+---------+
| SQRT(9) |
+---------+
|       3 |
+---------+
1 row in set (0.00 sec)
*/

5.3 日期函数

常见的日期函数
> > > > > > > >
函 数
功 能
NOW()返回当前的日期和时间
CURDATE()返回当前的日期
CURTIME()返回当前的时间
DATE()提取日期或日期/时间表达式的日期部分
EXTRACT()返回日期/时间按的单独部分
DATE_ADD()给日期添加指定的时间间隔
DATE_SUB()从日期减去指定的时间间隔
DATEDIFF()返回两个日期之间的天数
DATE_FORMAT()用不同的格式显示日期/时间
YEAR(data)获取指定data的年份
MONTH(data)获取指定data的月份
DAY(data)获取指定data的日期

示例：

-- 返回当前的日期和时间组合、当前日期、当前时间
SELECT NOW(),CURDATE(),CURTIME();
/*
输出：
mysql> SELECT NOW(),CURDATE(),CURTIME();
+---------------------+------------+-----------+
| NOW()               | CURDATE()  | CURTIME() |
+---------------------+------------+-----------+
| 2022-11-28 14:21:10 | 2022-11-28 | 14:21:10  |
+---------------------+------------+-----------+
1 row in set (0.00 sec)
*/
-- 返回当前日期时间表达式的日期部分
SELECT DATE(NOW());
/*
输出：
mysql> SELECT DATE(NOW());
+-------------+
| DATE(NOW()) |
+-------------+
| 2022-11-28  |
+-------------+
1 row in set (0.00 sec)
*/
-- 返回当前时间的年、月、日、时、分、秒；
SELECT
   EXTRACT(YEAR FROM NOW()) AS '年',
   EXTRACT(MONTH FROM NOW()) AS '月',
   EXTRACT(DAY FROM NOW()) AS '天',
   EXTRACT(HOUR FROM NOW()) AS '时',
   EXTRACT(MINUTE FROM NOW()) AS '分',
   EXTRACT(SECOND FROM NOW()) AS '秒';
/*
输出：
mysql> SELECT
   ->     EXTRACT(YEAR FROM NOW()) AS '年',
   ->     EXTRACT(MONTH FROM NOW()) AS '月',
   ->     EXTRACT(DAY FROM NOW()) AS '天',
   ->     EXTRACT(HOUR FROM NOW()) AS '时',
   ->     EXTRACT(MINUTE FROM NOW()) AS '分',
   ->     EXTRACT(SECOND FROM NOW()) AS '秒';
+------+------+------+------+------+------+
| 年   | 月   | 天   | 时   | 分   | 秒   |
+------+------+------+------+------+------+
| 2022 |   11 |   28 |   14 |   29 |   10 |
+------+------+------+------+------+------+
1 row in set (0.00 sec)
*/
-- 一个月后的当前时间
SELECT DATE_ADD(NOW(),INTERVAL 1 MONTH ) AS '一月后';
/*
输出：
SELECT DATE_ADD(NOW(),INTERVAL 1 MONTH ) AS '一月后';
+---------------------+
| 一月后              |
+---------------------+
| 2022-12-28 14:30:57 |
+---------------------+
1 row in set (0.00 sec)
*/
-- 十年前的今天
SELECT DATE_SUB(NOW(),INTERVAL 10 YEAR ) AS '十年前';
/*
输出：
mysql> SELECT DATE_SUB(NOW(),INTERVAL 10 YEAR ) AS '十年前';
+---------------------+
| 十年前              |
+---------------------+
| 2012-11-28 14:33:32 |
+---------------------+
1 row in set (0.00 sec)
*/
-- '2012-10-20' 减去 '2022-01-05' 的天数
SELECT DATEDIFF('2012-10-20','2022-01-05');
/*
输出：
mysql> SELECT DATEDIFF('2012-10-20','2022-01-05');
+-------------------------------------+
| DATEDIFF('2012-10-20','2022-01-05') |
+-------------------------------------+
|                               -3364 |
+-------------------------------------+
1 row in set (0.00 sec)
*/
-- 根据 '%Y %c %a %D' 的格式输出当前时间
SELECT DATE_FORMAT(NOW(),'%Y %c %a %D');
/*
输出：
mysql> SELECT DATE_FORMAT(NOW(),'%Y %c %a %D');
+----------------------------------+
| DATE_FORMAT(NOW(),'%Y %c %a %D') |
+----------------------------------+
| 2022 11 Mon 28th                 |
+----------------------------------+
1 row in set (0.00 sec)
*/
-- 获取当前时间的年月日
SELECT
   YEAR(NOW()) AS '年',
   MONTH(NOW()) AS '月',
   DAY(NOW()) AS '日';
/*
输出：
mysql> SELECT
   ->     YEAR(NOW()) AS '年',
   ->     MONTH(NOW()) AS '月',
   ->     DAY(NOW()) AS '日';
+------+------+------+
| 年   | 月   | 日   |
+------+------+------+
| 2022 |   11 |   28 |
+------+------+------+
1 row in set (0.00 sec)
*/
MySql中的日期格式
格式描述
%a缩写星期名
%b缩写月名
%c月，数值
%D带有英文前缀的月中的天
%d月的天，数值(00-31)
%e月的天，数值(0-31)
%f微秒
%H小时 (00-23)
%h小时 (01-12)
%I小时 (01-12)
%i分钟，数值(00-59)
%j年的天 (001-366)
%k小时 (0-23)
%l小时 (1-12)
%M月名
%m月，数值(00-12)
%pAM 或 PM
%r时间，12-小时（hh:mm:ss AM 或 PM）
%S秒(00-59)
%s秒(00-59)
%T时间, 24-小时 (hh:mm:ss)
%U周 (00-53) 星期日是一周的第一天
%u周 (00-53) 星期一是一周的第一天
%V周 (01-53) 星期日是一周的第一天，与 %X 使用
%v周 (01-53) 星期一是一周的第一天，与 %x 使用
%W星期名
%w周的天 （0=星期日, 6=星期六）
%X年，其中的星期日是周的第一天，4 位，与 %V 使用
%x年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y年，4 位
%y年，2 位

5.4 流程控制函数

三种流程控制函数
函 数
功 能
IF(bool,value1,value2)bool为true返回value1否则返回value2
IFNULL(value1,value2)如果value1不为空返回value1否则返回value2
CASE...WHEN...ELSE...END多重条件判断

示例：

-- 如果 4 > 5 成立返回 'A' 否则返回 'B'
SELECT IF(4>5,'A','B');
/*
输出：
mysql> SELECT IF(4>5,'A','B');
+-----------------+
| IF(4>5,'A','B') |
+-----------------+
| B               |
+-----------------+
1 row in set (0.00 sec)
*/
-- 因为首项为空，所有返回 'caterpillar'
SELECT IFNULL(NULL,'caterpillar');
/*
输出：
mysql> SELECT IFNULL(NULL,'caterpillar');
+----------------------------+
| IFNULL(NULL,'caterpillar') |
+----------------------------+
| caterpillar                |
+----------------------------+
1 row in set (0.00 sec)
*/
-- 表名设为 list ，如果 50>55 表数据为 'A'，否则如果 40>45 表数据为 'B'，否则表数据为 'C'
SELECT CASE WHEN 50>55 THEN 'A' WHEN 40>45 THEN 'B' ELSE 'C' END 'list';
/*
输出：
mysql> SELECT CASE WHEN 50>55 THEN 'A' WHEN 40>45 THEN 'B' ELSE 'C' END 'list';
+------+
| list |
+------+
| C    |
+------+
1 row in set (0.00 sec)
*/
-- 表名设为 list ，如果有跟 'caterpillar' 相同的条件则表数据为第一个为 'caterpillar' 条件所跟随的值
SELECT CASE 'caterpillar' WHEN 'hello' THEN 'A' WHEN 'caterpillar' THEN 'B' END 'list';
/*
输出：
mysql> SELECT CASE 'caterpillar' WHEN 'hello' THEN 'A' WHEN 'caterpillar' THEN 'B' END 'list';
+------+
| list |
+------+
| B    |
+------+
1 row in set (0.00 sec)
*/

六、约束

6.1 概述

概念：约束是作用于表中的规则，用于限制存储在表中的数据，约束是作用于表中字段上的，在创建和修改表的时候可以添加约束；

常见的约束
函 数
功 能
关键字
非空约束
限制该字段不能为NULL
NOT NULL
唯一约束
限制此字段是唯一的、不可重复的
UNIQUE
主键约束
主键是一个元组的唯一标识，主键被要求非空且是唯一的
PRIMARY KEY
自增约束
只有整型列才能设置此属性，每个表只能定义一个auto_increment列，并且必须在该列上定义主键约束或候选键
AUTO_INCREMENT
默认约束
添加元组时如果未指定该属性的键值则采用默认值
DEFAULT
检查约束(8.0.16版本之后)
保证字段值满足某一个条件
CHECK
外键约束
用来让两张表之间的数据建立连接，保证数据的一致性和完整性
FOREIGN KEY

目的：保证数据库中的数据的正确性、有效性和完整性；

6.2 约束演示

  1. 基础约束

示例：

/*
要求：
1. 建立一个表 tb_personnel；
2. 表内数据有(id, name, age, srarus, opender);
3. id 要求为主键且作自增；
4. name 要求不为空且不能重复；
5. age 要求大于 0 小于 120；
6. srarus 要求默认为 '1'；
*/
CREATE TABLE tb_personnel(
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',
    name VARCHAR(20) NOT NULL UNIQUE COMMENT '姓名',
    age INT CHECK ( 120 > age && age > 0 ) COMMENT '年龄',
    srarus CHAR(1) DEFAULT 1 COMMENT '状态',
    opender CHAR(1) COMMENT '性别'
) COMMENT '人员表';
  1. 外键约束

语法：

-- 建表时添加外键
CREATE TABLE 表名(
	字段名 数据类型,
	...
	[CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
);
-- 建表后添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名);
-- 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

示例：

-- 建立外键表
CREATE TABLE tb_srarus(
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',
    srarus varchar(10) NOT NULL UNIQUE COMMENT '状态'
);
-- 建表时添加外键
CREATE TABLE tb_personnel(
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',
    name VARCHAR(20) NOT NULL UNIQUE COMMENT '姓名',
    age INT CHECK ( 120 > age && age > 0 ) COMMENT '年龄',
    srarus INT DEFAULT 1 COMMENT '状态',
    opender CHAR(1) COMMENT '性别',
    CONSTRAINT additional FOREIGN KEY (srarus) REFERENCES tb_srarus(id)
) COMMENT '人员表';
-- 建表后添加外键
ALTER TABLE tb_personnel ADD CONSTRAINT additional FOREIGN KEY (srarus) REFERENCES tb_srarus(id);
-- 删除外键
ALTER TABLE tb_personnel DROP FOREIGN KEY additional;
  1. 外键的删除和更新
删除更新行为
行 为
说明
NO ACTION
当在父表删除/更新对应记录时，首先先检查记录是否有对应外键，如果有则不允许删除
RESTRICT
当在父表删除/更新对应记录时，首先先检查记录是否有对应外键，如果有则不允许删除（与NO ACTION一致）
CASCADE
当在父表删除/更新对应记录时，首先先检查记录是否有对应外键，如果有则将子表中的记录也一并删除/更新
SET NULL
当在父表删除对应记录时，首先先检查记录是否有对应外键，如果有则将子表中的外键值设为NULL
SET DEFAULT
父表有变更时，子表将外键列设置成一个默认的值（Innodb不支持）

注：RESTRICT与 NO ACTION 是默认行为

语法：

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名) ON UPDATE CASCADE ON DELETE 约束名;

示例：

-- 指定约束为 SET NULL
ALTER TABLE tb_personnel ADD CONSTRAINT additional FOREIGN KEY (srarus) REFERENCES tb_srarus(id) ON UPDATE CASCADE ON DELETE SET NULL ;
-- 指定约束为 CASCADE
ALTER TABLE tb_personnel ADD CONSTRAINT additional FOREIGN KEY (srarus) REFERENCES tb_srarus(id) ON UPDATE CASCADE ON DELETE CASCADE ;

七、多表查询

7.1 概述

项目开发中在进行数据库表与表之间的设计时有以下三种联系：

  1. 一对一联系
  • 多用于多表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中；
  • 在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的；
  1. 一对多联系
  • 在多的一方建表，指向一的一方的主键；
  1. 多对多联系
  • 建立第三张中间表，中间表至少包含两个外键，分别关联两方主键；

多表查询分类：

  1. 连接查询
  • 内连接：相当于查询A、B的交集部分；
  • 外连接：查询左/右表所有数据，以及两张表交集部分数据；
  • 自连接：当前表与自身的连接查询，自连接必须使用表别名；
  1. 子查询
  • 即嵌套查询；

笛卡尔积的概念：

笛卡尔积运算 设a,b为集合，用a中元素为第一元素，b中元素为第二元素构成有序对，所有这样的有序对组成的集合叫做a与b的笛卡尔积；

表信息：

在这里插入图片描述)

笛卡尔积示例：

select * from tb_staff,tb_branch;

结果图：

在这里插入图片描述)

7.2 连接查询

内连接：

  1. 隐式内连接
SELECT 字段列表 FROM 表1,表2 WHERE 条件...;
  1. 显式内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件...;

示例：

-- 建立一张员工表、一张部门表
create table tb_staff(
> sid int auto_increment comment '员工id' primary key,
sname varchar(20) comment '员工姓名',
> branch int comment '部门号'
)comment '员工表';
create table tb_branch(
bid int auto_increment comment '部门号' primary key ,
> bname varchar(10) unique comment '部门名称',
manager varchar(10) comment '部门经理'
>)comment '部门表';
-- 添加外键约束
alter table tb_staff add constraint branch_key foreign key (branch)references tb_branch(bid);
-- 在两张表中输入测试样例
insert into tb_branch (bname, manager) VALUE ('人事部','毛毛虫'),('安保部','张三丰'),('技术部','金庸'),('李世民',null);
insert into tb_staff (sname, branch) VALUE ('caterpillar',1),('张无忌',3),('小龙女',2),('杨过',2),(null,null);
-- 隐式内连接方式
select
tb_staff.sname as '员工姓名',tb_branch.bname as '部门',tb_branch.manager as '部门经理'
from
tb_staff,tb_branch
where
tb_staff.branch = tb_branch.bid;
-- 显式内连接方式
select
tb_staff.sname as '员工姓名',tb_branch.bid as '部门id',tb_branch.bname as '部门',tb_branch.manager as '部门经理'
from
tb_staff inner join tb_branch
on
tb_staff.branch = tb_branch.bid;

查询结果：

在这里插入图片描述)

外连接：

  1. 左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON条件...;
  1. 右外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件...;

示例：

-- 左外连接
select
    tb_staff.sname as '员工姓名',tb_branch.bid as '部门id',tb_branch.bname as '部门',tb_branch.manager as '部门经理'
 from
     tb_staff left outer join tb_branch
on
    tb_staff.branch = tb_branch.bid;
-- 右外连接
select
    tb_staff.sname as '员工姓名',tb_branch.bid as '部门id',tb_branch.bname as '部门',tb_branch.manager as '部门经理'
from
     tb_staff right outer join tb_branch
on
    tb_staff.branch = tb_branch.bid;

结果：

在这里插入图片描述)

自连接：

  • 自连接可以是内连接也可以是外连接；

语法：

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...;

示例：

-- 建立员工丛属表
create table tb_firm(
    fid int auto_increment comment '员工id' primary key,
    fname varchar(20) not null comment '姓名',
    b_mid int comment '直系领导'
)comment '员工从属表';
-- 插入测试数据
insert into
    tb_firm (fname, b_mid)
values
    ('张三丰',null),('张无忌',1),('小龙女',2),('韦一笑',2),('杨过',3),
    ('老顽童',3),('天山童姥',3),('赵无极',4),('韦小宝',4);
-- 使用自连接投影出每个员工的直系上司
select f1.fname as '员工名称',f2.fname as '直系上司' from tb_firm as f1 left outer join tb_firm as f2 on f1.b_mid = f2.fid;

结果：

在这里插入图片描述

联合查询：

  • 对于联合查询（union），就是把多次查询的结果合并，形成一个新的结果集；
  • 联合查询的数张表必须保持一致，字段类型也需要保持一致；
  • union all 会将所有数据合并在一起，union 会对合并之后的数据去重；

语法：

SELECT 字段列表 FROM 表A ...
UNION [ALL]
SELECT 字段列表 FROM 表B ...;

示例：

-- 建立一张学生表
create table tb_stduent(
    sid int auto_increment comment '学生id' primary key,
    sname varchar(20) comment '姓名',
    class varchar(10) comment '学生班级',
    sage int check ( 0 < sage and sage < 120 ) comment '年龄'
)comment '学生表';
-- 插入测试数据
insert into
    tb_stduent (sname, class, sage)
values
    ('张三丰','3班',18),('张无忌','3班',16),('杨过','1班',17),('韦小宝','2班',18),
    ('金台','3班',18),('李存孝','1班',18),('李元霸','2班',18),('高宠','3班',18);
-- 通过联合查询将姓张的学生和在1班的学生投影出来
select * from tb_stduent where sname like '张%'
union all
select * from tb_stduent where class = '1班';

结果：

在这里插入图片描述

7.3 子查询

7.3.1 基本知识

概念：

  • sql 语句当中嵌套 select 语句，称为嵌套查询，又称子查询；

语法：

SELECT * t1 WHERE column1 = (SELECT column1 FROM t2);
  1. 根据子查询的结果可分为：
  • 标量子查询
  • 列子子查询
  • 行子子查询
  • 表子查询
  1. 子查询的位置可以是：
  • WHERE 之后；
  • FROM 之后；
  • SELECT 之后；

7.3.2 标量子查询

概念：

  • 标量子查询返回的结果是单个值（数字、字符串、日期等）；
  • 常用的操作符：=、<>、>、>=、<、<=；

有如下一张表：

在这里插入图片描述

示例：

-- 生成一张3班的学生表，查询表中姓名为高宠的学生的年龄
select sage from (select * from tb_stduent where class = '3班') as list where sname = '高宠';

结果：

在这里插入图片描述

7.3.3 列子查询

概念：

  • 列子查询返回的结果是一列（可以是多行）；
  • 常用的操作符：IN、NOT IN、ANY、SOME、ALL

有如下一张表：

在这里插入图片描述

示例：

-- 查询不和高宠在一个班的学生姓名
select sname from tb_stduent where class not in (select class from tb_stduent where sname = '高宠');
-- 查询年龄小于1班所有学生的学生姓名
select sname from tb_stduent where sage < all (select sage from tb_stduent where class = '1班');
-- 查询年龄小于1班任意一名学生的学生姓名
select sname from tb_stduent where sage < some (select sage from tb_stduent where class = '1班');

结果：

在这里插入图片描述

7.3.4 行子查询

概念：

  • 行子查询返回的结果是一行（可以是多列）；
  • 常用的操作符：=、<>、IN、NOT IN；

有如下一张表：

在这里插入图片描述

示例：

-- 查询和张三丰同属一个班级且年龄相同的学生信息
select * from tb_stduent where (class,sage) = (select class,sage from tb_stduent where sname = '张三丰');

结果：

在这里插入图片描述

7.3.5 表子查询

概念：

  • 表子查询返回的结果是多行多列；
  • 常用操作符：IN

有如下一张表：

在这里插入图片描述

示例：

-- 生成一张班级为3班的学生表，再从生成的表中查询姓张的学生信息
select * from (select * from tb_stduent where class = '3班') as list where sname like '张%';

结果：

在这里插入图片描述

八、事务

8.1 简介

  1. 概念

事物是一组操作的集合，他是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，也就是说要么同时成功，要么同时失败；

  1. 事务的四大特性
  • 原子性：事务是不可再分割的最小操作单元，要么全部成功，要么全部失败；
  • 一致性：事务完成时，必须使所有的数据都保持一致状态；
  • 隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境；
  • 持久性：事务一旦提交或回滚对数据的改变是永久的；

（注：默认 MySql 的事务是自动提交的，也就是说，当执行一条 DML 语句，MySql 会立即隐式的提交事务）

8.2 事务操作

方法一：通过设置事务的提交方式

语法：

-- 查看事务
SELECT @@autocommit;
-- @@autocommit为1时是自动提交，为0是手动提交，默认是1
SET @@autocommit = 0;
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;

现有下表：

-- 建立一张银行用户表
create table tb_bank_user(
    id int auto_increment comment '用户id' primary key,
    name varchar(20) comment '姓名',
    money double check ( money >= 0 ) comment '余额'
)comment '银行用户表';
-- 插入测试数据
insert into tb_bank_user (name, money) values ( '赵无极',2000.00),('高宠',1000.00),('杨坚',0.00),('李世民',500.00);
-- 将事务提交方式更改为手动
set @@autocommit = 0;

示例1：

-- 执行完转账操作之后使用commit提交
update tb_bank_user set money = money - 1000 where name = '赵无极';
update tb_bank_user set money = money + 1000 where name = '高宠';
commit;

示例2：

-- 执行转帐的过程中出错使用rollback回滚
update tb_bank_user set money = money - 1000 where name = '杨坚';
update tb_bank_user set money = money + 1000 where name = '高宠';
rollback;

方法二：

语法：

-- 开启事务
START TRANSACTION 或 BEGIN;
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;

这种方法跟方法一操作一致，区别在于一旦使用了提交或回滚就表示事务已经结束不需要再手动更改事务提交规则；

并发事务

并发事务问题：

  • 脏读：一个事务读到另外一个事务还未提交的数据；
  • 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读；
  • 幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在了；

事务隔离级别（安全性从低到高）：

  • 未提交读（Read uncommitted）：一个事务在执行过程中可以看到其他事务没有提交的新插入的记录，而 且能看到其他事务没有提交的对已有记录的更新；
  • 已提交读（Read committed）：一个事务在执行过程中可以看到其他事务已经提交的新插入的记录，而且能看到其他事务已经提交的对已有记录的更新；
  • 可重复读/默认（Repeatable read）：一个事务在执行过程中可以看到其他事务已经提交的新插入的记录，但是不能看到其他其他事务对已有记录的更新；
  • 可串行化（Serializable）：一个事务在执行过程中完全看不到其他事务对数据库所做的更新。（事务执行的时候不允许别的事务并发执行。事务串行化执行，事务只能一个接着一个地执行，而不能并发执行）；

（注：事务隔离级别越高，运行越慢）

隔离级别脏读不可重复读幻读
未提交读（Read uncommitted）可能可能可能
已提交读（Read committed）不可能可能可能
可重复读（Repeatable read）不可能不可能可能
可串行化（Serializable）不可能不可能不可能

语法：

-- 查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;
-- 设置事务隔离级别
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
jc_caterpillar
关注
专栏目录
MySQL基本概念、PyMySQL操作及数据库事务操作
m0_63409362的博客
04-19 1784
一、数据库概念及其分类 数据库：存储数据的软件，长期存储在计算机内，有组织的数据集合。 数据表：表是数据库存储数据的基本单位，数据按照分类存储到不同的表中，能够非常高效的的查询其中数据 数据库分类： 1.关系型数据库: 以数据表为核心，将数据的关系用数据库表的形式表达，并将数据存储在表格中，以便查询 数据库 应用 注：关系型数据库核心元素 Oracle 大型项目中使用，例如银行、电信等项目; ·数据行（一条记录） ·数据列（字段）...
Mysql数据库基本操作（十二）事务
weixin_42445051的博客
01-25 3209
1，基本概念 1，只有innodb存储引擎支持事务，行级锁定，外键 2，事务处理可以用来维护数据库的完整性，保证成批的sQL语句要么全部执行，要么全部不执行 3，事务用来管理DDL、DML、DCL操作，比如insert,update,delete语句，默认是自动提交的。 4，为什么我们需要事务管理DDL，DML，DCL操作？因为这些操作都是对数据库有一些增删改的动作，有时候为了完成一个逻辑动作必须要用两个或者多个sql语句执行这个逻辑动作。因此我们必须确保这些sql语句都成功或者都失败。例如银行转账问题，转
数据库事务介绍（一）数据库事务的基本概念
qq_39746820的博客
04-02 1382
数据库事务的介绍，希望能对你在数据库事务方面的学习有所帮助。
数据库系统基本概念
weixin_53204083的博客
09-09 4269
保证数据的逻辑独立性 当模式改变时，数据库管理员修改有关的外模式／模式映象，使外模式保持不变，保证了外模式的稳定性 应用程序是依据数据的外模式编写的，从而应用程序不必修改，保证了数据与程序的逻辑独立性，简称数据的逻辑独立性。实体内部的联系通常是指组成实体的各属性之间的联系实体之间的联系通常是指不同实体集之间的联系。保证数据的物理独立性 当数据库的存储结构改变了（例如选用了另一种存储结构），数据库管理员修改模式／内模式映象，使模式保持不变 应用程序不受影响。设计数据库模式结构时应首先确定数据库的逻辑模式；
数据库基本概念
gangtianyi的博客
02-27 6301
1.数据库是信息系统的核心和基础 一.数据库的四个基本概念 1.数据(data)：描述事物的符号记录。包括图像音频等多种形式。 数据的含义也就是数据的语义就是所谓的信息。 2.数据库(DataBase)：长期储存在计算机内、有组织的、可共享的大量数据的集合。 特征：数据库中的数据按一定数据模型组织、描述和储存，具有较小的冗余度、较高的数据独立性、易拓展性，并可为各种用户共享 3.数据库管理系统(DataBase Management System,DBMS):位于用户与操作系统之间的一层数据管理
【数据库】事务的基本概念
WINGREZ‘S BLOG
10-22 4868
相关资料来源于网络，侵删歉。 如果文章中存在错误，请下方评论告知我，谢谢！ 事务(Transaction) 1.事务是用户定义的一个数据库操作序列，这些操作要么全做，要么全不做，是一个不可分割的工作单位。 2.事务和程序是两个概念：在关系数据库中，一个事务可以是一条SQL语句，一组SQL语句或整个程序；一个应用程序通常包含多个事务。 3.事务是恢复和并发控制的基本单位。 显式定义事务 B...
数据库事务的概念及其实现原理
小强快跑~~
07-02 6624
目录 1. 认识事务 1.1 为什么需要数据库事务 1.2 什么是数据库事务 1.3 事务如何解决问题 1.4 事务的ACID特性以及实现原理概述 2.并发异常与并发控制技术 2.1 常见的并发异常 2.2 事务的隔离级别 2.3 事务隔离性的实现——常见的并发控制技术 2.3.1 基于封锁的并发控制 2.3.2 基于时间戳的并发控制 2.3.3 基于有效性检查的并发控制 2.3.4 基于快照隔离的并发控制 2.3.5 关于并发控制技术的总结
数据库(mysql)基本概念
m0_71521555的博客
09-11 1671
数据库(mysql)基本概念
mysql数据库事务的回滚操作
weixin_40228600的博客
11-07 3934
3幻读：一个事务操作DML数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。事务的基本概念：如果一个包含多个步骤业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。语法：create USER ‘账号’@‘主机名’ IDENTIFIED By ‘密码’SELECT @@autocommit --1代表自动提交，0代表手动提交。2不可重复读（虚读）：在同一个事务中，两次读取到数据不一样。1脏读：一个事务，读取到另一个没有提交的数据。
数据库的一些基本概念
qq_45870087的博客
01-27 4702
1、数据（data）是数据库中存储的基本对象，数据不单纯指数字，广义的理解认为数据的种类很多，例如文本（text）、图形（graph）、图像（image）、音频（audio）、视频（video）、学生的档案记录、货物的运输情况等都算是数据。可以将数据定义为：描述事物的符号记录。数据的含义称为数据的语义，数据与其语义是不可分的。 2、数据库（Database，DB）顾名思义，是存放数据的仓库。严格来讲，数据库是长期存放在计算机内、有组织的、可共享的大量数据的集合。数据库中的数据按一定的数据模型组织、描述和存
Android数据库中事务操作方法之银行转账示例
08-29
Android数据库事务操作的基本概念 在Android数据库中，事务操作是通过beginTransaction()和endTransaction()方法来实现的。其中，beginTransaction()方法用于开启一个事务，endTransaction()方法用于结束一个事务。...
数据库基本概念面试必问
09-10
以下是关于数据库基本概念的详细解释： 1. 超键、候选键、主键、外键： - 超键：在关系数据库中，能够唯一标识一行记录的一组或多组属性被称为超键。超键可能包含多余属性。 - 候选键：是最小的超键，即没有冗余...
数据库的概念和操作PPT课件.ppt
11-23
事务日志文件记录所有事务操作，确保数据一致性。 【数据库文件组】文件组是管理数据库文件的一种方式，允许将文件分组以便更有效地管理和分配数据。主文件组包含主数据文件和未指明文件组的其他文件，系统表默认...
mysql面试题-mysql经典面试题目-数据库的基本概念-SQL语法-事务处理-索引优化-性能调优-mysql-面试题目
01-20
1. **数据库基本概念**： - 数据库是用于存储和管理数据的系统，如MySQL是一种关系型数据库管理系统（RDBMS）。 - 关系型数据库中的关键概念包括表、字段、主键、外键、索引等。 - 数据模型包括关系模型、层次...
4.网络编程
weixin_45476535的博客
09-14 596
程序注册端口：1024-49151。单个协议下，端口号不能冲突。公有端口0-1023。
Redis的Key的过期策略是怎样实现的？
daydayup
09-19 653
在学习Redis时，我们知道可以设置Key的过期时间，我们还知道，Redis一大特点–。那么当Redis中的数据量起来时，如果直接遍历所有的Key，那么对于Key过期时间的校验应该很费时间，那么Redis究竟是怎样做的呢？本文就来探讨关于Redis中的Key的过期册策略。Redis中的过期策略有两种。
Java项目: 基于SpringBoot+mybatis+maven课程答疑系统(含源码+数据库+毕业论文)
weixin_43860634的博客
09-14 873
Java项目: 基于SpringBoot+mybatis+maven课程答疑系统(含源码+数据库+毕业论文)
阿里1688一面总结
weixin_44804108的博客
09-19 235
发布-订阅模式，即当实验发生变更时，就发出一个变更事件，然后，每台机器感知到这个变更事件后，清空本地缓存，触发reload操作。为了避免大量请求打到DB，可以对查询请求进行加锁，保证相同的实验只有一个线程去查，然后更新到缓存中，其他的请求，走缓存查询结果。运维团队将扩缩容事件投递在kafka中，容量平台起一个线程去订阅变更事件，当有事件时，更新公共缓存中的数据。kafka单broker的消息是可以保证顺序性的，但是kafka集群的消息实际是无序的。首先，面试官进行对业务进行介绍，然后，候选人进行自我介绍。
No operations allowed after statement closed
最新发布
abments的博客
09-20 543
错误信息：参考解决方案 https://github.com/alibaba/druid/issues/5549如果修改socket-timeout不生效，可以尝试修改 socketTimeout。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

jc_caterpillar

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值