MySQL数据库（二）：DML、DDL、TCL、存储过程

清木

已于 2023-05-30 15:35:46 修改

阅读量596

点赞数 2

分类专栏：数据库文章标签：数据库 mysql sql 学习

于 2023-05-23 09:50:43 首次发布

本文链接：https://blog.csdn.net/weixin_58545784/article/details/130801851

版权

数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

1 数据操作语言（DML）

用于添加、删除、修改数据库记录，并检查数据库完整性；

关键字：insert、delete、update等

1.1 插入语句

方式一：

语法： INSERT INTO 表名（字段名1，字段名2...） VALUES（值1，值2...）

1 插入的值的类型与相应字段的类型一致或兼容；

2 可以为NULL的列如何插入值：

2.1.在对应的字段名位置写入null；

2.2.字段名和值都不写；

3 字段的顺序可以改变；

4 字段和值的个数必须一致；

5 可以省略字段名，这样默认添加所有的字段；

方式二：

语法：INSERT INTO 表名 SET 字段名=值，....

方式一支持多行插入：多条插入语句间用逗号分隔；

方式一支持子查询；

1.2 修改语句

1.2.1 修改单表记录

语法：UPDATE 表名 SET 列1=新值，列2=新值，....

WHERE 筛选条件；

1.2.2 修改多表记录

sql92语法：

UPDATE 表1 别名，表2 别名 SET 列1=新值，列2=新值，...

WHERE 连接条件 AND 筛选条件；

sql99语法：

UPDATE 表1 别名

【inner || left || right】 JOIN 表2 别名

ON 连接条件

SET 列1=新值，列2=新值，...

WHERE 筛选条件;

1.3 删除语句

1.3.1 删除部分

单表删除

语法：DELETE FROM 表名 WHERE 筛选条件

多表的删除

sql92语法：

DELETE 表1别名，表2别名（需要删除谁的就加上谁的别名）

FROM 表1 别名，表2 别名

WHERE 连接条件 AND 筛选条件

sql99语法：

DELETE 表1别名，表2别名

FROM 表1 别名

【inner || left || right】 JOIN 表2 别名

ON 连接条件

WHERE 筛选条件；

1.3.2 删除整张表

TRUNCATE TABLE 表名；（不加where条件）

DELETE与TRUNCATE区别

1.truncate不能加where条件，而delete可以加where条件
2.truncate的效率高
3.truncate 删除带自增长的列的表后，如果再插入数据，数据从1开始
4.delete 删除带自增长列的表后，如果再插入数据，数据从上一次的断点处开始
5.truncate删除不能回滚，delete删除可以回滚

2 数据定义语言（DDL）

关键字：create、drop、alter

2.1 库的管理

库的创建（create）：

语法： CREATE DATABASE【IF EXISTS（如果该库不存在，则创建）】库名;

库的修改（alter）：

修改库的字符串：ALTER DATABASE 库名 CHARACTER SET【gdk、utf8.....】;

库的删除（drop）：

DROP DATABASE IF EXISTS 库名;

创建库和表的通常写法：

先删除，在创建

DROP DATABASE IF EXISTS 库名;
CREATE DATABASE 库名;

DROP TABLE IF EXISTS 表名;
CREATE TABLE 表名;

2.2 表的管理

表的创建

语法：CREATE TABLE 表名（

列名列的类型【（长度）约束】,

列名列的类型【（长度）约束】,

....

列名列的类型【（长度）约束】

）

表的修改/删除

ALTER TABLE 表名【add || drop || modify || change】 COLUMN 列名【列的类型约束】；

COLUMN可以省略、add-添加、drop-删除、modify-修改、change-改列名

表的复制

1.复制表的结构

CREATE TABLE 表名 LIKE 旧表;

2.复制表的结构+数据

CREATE TABLE 表名

SELECT 查询列表 FROM 旧表【WHERE 筛选】;

2.3 常见的数据类型

2.3.1 整型

MySQL的整型类型一共五种：tinyint、smallint、mediumint、int/integer、bigint；以上所占的字节分别为：1、2、3、4、8。

特点：

都可以设置无符号和有符号，默认有符号，通过unsigned（加在数据类型后）设置无符号
如果超出了范围，会报out or range异常，插入临界值
长度可以不指定，默认会有一个长度
长度代表显示的最大宽度，如果不够则左边用0填充，但需要搭配zerofill（加在数据类型后），并且默认变为无符号整型

2.3.2 浮点型

定点数：decimal（M，D），用于保存精度要求较高的数

浮点数：float（M，D）、double（M，D）

特点：

D：代表小数点后保留几位；

M：小数部位与整数部位的和；

如果超过范围，则插入临界值。

原则：所选择的数据类型越简单越好，能保存数值的类型越小越好

2.3.4 字符型

char：固定长度的字符，写法 CHAR（M），最大长度不能超过M，其中M可以省略，默认M为1

varchar：可变长度的字符串，写法VARCHAR（M），最大长度不能超过M，M不可省略

区别： char中存放的内容即使为达到M，也会分配长度M的空间给这个数据，比较消耗空间，而varchar中会根据存放的内容分配存储空间，对空间的消耗比较节省。

char和varchar都只是用来保存MySQL中较短的字符串，如果需要保存较长的字符串可用‘text’

binary和varbinary类型和char、varchar类似，只是它们用来保存较短的二进制字符串，较长的二进制字符串用 blob。

枚举（enum）

要求插入的值必须属于列表中指定的值之一

列表成员为1~255时，需要1个字节存储；

列表成员为255~65535时，需要2个字节存储；

语法 CREATE TABLE 表名（

c1 ENUM('a','b','c')

）;

INSERT INTO 表名 VALUES('a');只能插入列表中有的值，否则会报错，且不区分大小写；

集合（set）

和enum类型类似，里面可以保存0~64个成员。但是set类型可以一次选取多个成员，成员数量的不同，存储所占的字节数也不一样

enum就像选择题中的单选一样，而set就是多选

2.3.5 日期类型

日期和时间类型	字节	最小值	最大值
date	4	1000-01-01	9999-12-31
datetime	8	1000-01-01 00:00:00	9999-12-31 23:59:59
timestamp	4	19700101080001	2038年的某个时刻
time	3	-838:59:59	838:59:59
year	1	1901	2155

timestamp和实际时区有关，更能反应实际的日期。

2.4 常见的约束

约束：用于限制表中的数据，保证数据的准确性和可靠性

NOT NULL：非空，该字段的值必填
UNIQUE：唯一，该字段的值不可重复
DEFAULT：默认，该字段的值不用手动插入有默认值
CHECK：检查，mysql不支持
PRIMARY KEY：主键，该字段的值不可重复并且非空 unique+not null
FOREIGN KEY：外键，该字段的值引用了另外的表的字段（在从表添加外键约束，用于引用主表中某列的值）

约束的分类

列级约束：六大约束语法上都支持，但外键约束没有效果，一个字段可添加多个列级约束，多个约束用空格隔开
表级约束：除了非空、默认，其他都支持（外键除外）

主键和唯一键的区别

1.区别：

①、一个表至多有一个主键，但可拥有多个唯一键

②、主键不允许为空，唯一键可以为空，但也只能有一个为空

2.相同点：

①、都具有唯一性

②、都支持组合键

外键

要求从表设置外键关系

主表的关联必须是一个key（一般是主键或唯一键）

插入数据时，先插入主表中、在插入从表

删除数据时，先删除从表中的数据、在删除主表中的数据

创建表时添加约束

语法：

CREATE TABLE 表名（

字段名字段类型【NOT NULL || PRIMARY || DEFAULT】

CONSTAINT 约束名 FORREIGH KEY(字段名) REFERENCES 主表（被引用列）

）

修改表时添加或删除约束

添加非空/默认
ALTER TABLE 表名 MODIFY COLUMN 字段名字段类型【not NULL || DEFAULT 值】;
删除非空/默认
ALTER TABLE 表名 MODIFY COLUMN 字段名字段类型 ;

添加主键/唯一
ALTER TABLE 表名 add【 CONSTRAINT 约束名】【primary KEY(字段名) || UNIQUE(字段名)】;
删除主键/唯一
ALTER TABLE 表名 DROP 【primary KEY || INDEX 索引名】;

添加外键
alter table 表名 add【 constraint 约束名】 foreign key(字段名) references 主表（被引用列）;
删除外键
alter table 表名 drop foreign key 约束名;

3 事务控制语言（TCL）

事务：一个或一组sql语句组成的最小执行单元，它们综合在一起才是一个完整的工作单元，这些动作必须全部完成，如果有一个失败的话，那么事务就会回滚到最开始的状态，仿佛什么都没发生过一样。

数据库事务是保证在并发情况下能够正确执行的重要支撑，MySQL常见的数据库引擎中支持事务的是InnoDB。

事务就是一系列操作，正确执行并提交，如果中途出现错误就回滚。事务要保证能够正常的执行，就必须要保持ACID特性。

事务的特征（AICD）

原子性（Atomicity）：一个事务不可在被分割，要么都执行、要么都不执行；

一致性（Consistency）：一个事务会使数据从一个一致状态切换到另一个一致状态；

隔离性（Isolation）：一个事务的执行不受其他事务的干扰

持久性（Durability）：一个事务一旦提交，在会永久的改变数据库的数据。

3.1 事务的创建

隐式事务

事务没有明显的开启和结束的标记

比如：insert、pudate、delete语句即：执行DML无需手动开启和提交事务

但在MyBatis持久层框架中，进行DML操作时必须要手动开启事务，并且手动提交事务！！因为在MyBatis持久层框架中，它们处理DML语句的时候会自动设置autocommit=0;如果DML中不进行手动提交事务，那么最后事务就会进行回滚。

在写SSM项目的时候，不用手动提交呢？

spring boot 自动提交事务

org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(Object, TransactionDefinition)

显示事务

事务具有明显的开启和结束的标记

前提：必须先设置自动提交为禁用

步骤一：开启事务 set autcommit = 0；（默认值是1，也就是默认自动开启提交）

start transation；

步骤二：编写事务中的sql语句（select、insert、update、delete）

语句1；

语句2；

...

步骤三：结束事务根据情况选择是提交事务还是回滚事务

commit；提交事务

rollback；回滚事务

3.2 为什么需要使用事务？

当同时存在多个事务时，这些事务共同访问数据库中的相同数据时，如果不采取必要的隔离机制，就有极大的可能出现各种并发问题：

脏读：对于两个事务T1、T2，T1读取了已被T2更新但还未提交的字段，之后若T2回滚，T1 读取的内容就是临时且无效的。

不可重复读：对于两个事务T1、T2，T1读取了一个字段，然后T2更新了该字段之后，T1再次读取同一个字段，值就不同了。

幻读：对于两个事务T1、T2，T1从一个表中读取了一个字段，然后T2在该表中插入了一些新数据之后，T1再次读取同一个表，就会多出几行来。

不可重复读与幻读的区别：

不可重复读主要体现在update，即事务前后对特定字段的内容的修改；而幻读体现在insrt和delete，即事务前后对整个数据结果集的对比

数据库的隔离性：数据库必须具有隔离并发运行各个事务的能力，使它们不会相互影响，避免发生各种并发问题

隔离级别：一个事务与其他事务隔离的程度；隔离级别越高，数据一致性就越好，并发性越弱，但性能相对就越低

3.3 数据库提供的4中事务隔离级别：

隔离级别	脏读	不可重复读	幻读	描述
READ UNVCOMMITED （读未提交数据）	√	√	√	允许事务读取未被其他事务提交的变更
READ COMMITED （读已提交数据）	×	√	√	只允许事务读取已经被其他事务提交的变更
REPEATABLE READ （可重复读）	×	×	√	确保事务可以从一个字段中读取相同的值，在这个事务持续期间，禁止其他事务对这个字段进行更新
SERIALIZABLE （串行化）	×	×	×	确保事务可以从一个表中读取相同的行，在这个事务持续期间，禁止其他事务对该表执行插入、删除、更新和操作，可以避免所有的并发问题，但性能十分低下

MySQL支持以上四种事务隔离级别，且将repeatable read设置为默认隔离级别。

Oracle支持两种事务级别read commited，serializable，Oracle默认的事务隔离级别是read commited。

3.4 在MySQL中设置隔离级别

每启动一个mysql程序，就会获得一个单独的数据库连接，每个数据库连接都会有一个全局变量@@tx_isolation，表示当前的事务隔离级别。
查看当前的隔离级别：SELECT @@tx_isolation;
设置当前mysql连接的隔离级别： SET TRANSACTION ISOLATION LEVEL 隔离级别名称；

4 存储过程

4.1 什么是存储过程

由MySQL5.0 版本开始支持存储过程。

如果在实现用户的某些需求时，需要编写一组复杂的SQL语句才能实现的时候，那么我们就可以将这组复杂的SQL语句集提前编写在数据库中，由JDBC调用来执行这组SQL语句。把编写在数据库中的SQL语句集称为存储过程。

存储过程：（PROCEDURE）是事先经过编译并存储在数据库中的一段SQL语句的集合。调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是很有好处的。

也就是数据库SQL语言层面的代码封装与重用。

好处：1.提高代码的重用性；

2.简化操作；

3.减少编译的次数和连接数据库服务器的连接次数，提高了效率。

4.2 基本使用

创建语法：

CREATE PROCEDURE 存储过程名（参数列表）

BEGIN

存储过程体

END 结束标记

参数列表

参数列表包含三个部分：参数模式参数名参数类型

如：in stuname varchar（20）

参数模式：

in ：该参数需要调用时需传入值

out：该参数可以作为返回值

inout：该参数既需要传入值，又可以返回值

存储过程体只有一句话时，begin end可以省略，每条sql语句的结尾必须加分号。

设置存储过程的结尾

语法：delimiter 结束标记

如： delimiter $; 此时'$'就为当前存储过程的结束标记，也就是说 '$' 替代了 ';' 的功能

调用语法：CALL 存储过程名（实参列表）结束标记

删除存储过程：DROP PROCEDURE 存储过程名结束标记

不可一条语句删除多个存储过程，一次只能删除一个

查看存储过程信息：SHOW CREATE PROCEDURE 存储过程名；

4.3 为何要使用存储过程

简化对变动的管理。如果表名、列名、或业务逻辑有了变化。只需要更改存储过程的代码。使用它的人不用更改自己的代码。
通常存储过程都是有助于提高应用程序的性能。当创建的存储过程被编译之后，就存储在数据库中，可重复使用，提高代码重用率。
存储过程有助于减少应用程序和数据库服务器之间的流量。因为应运程序不必发送多个冗长的SQL语句，只用发送存储过程中的名称和参数即可。

清木

关注

2
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
MySQL数据库（二）：DML、DDL、TCL、存储过程

当同时存在多个事务时，这些事务共同访问数据库中的相同数据时，如果不采取必要的隔离机制，就有极大的可能出现各种并发问题：脏读：对于两个事务T1、T2，T1读取了已被T2更新但还未提交的字段，之后若T2回滚，T1 读取的内容就是临时且无效的。不可重复读：对于两个事务T1、T2，T1读取了一个字段，然后T2更新了该字段之后，T1再次读取同一个字段，值就不同了。幻读：对于两个事务T1、T2，T1从一个表中读取了一个字段，然后T2在该表中插入了一些新数据之后，T1再次读取同一个表，就会多出几行来。
复制链接

扫一扫