MySQL学习笔记03

删除表中数据

使用delete（属于DML语句）

1
2
//删除dept_bak表中的数据
delete from dept_bak; //这种删除数据的方式比较慢。

特点：

  • 表中的数据被删除了，但是这个数据在硬盘上的真实存储空间不会被释放！！！
  • 这种删除缺点是：删除效率比较低。
  • 这种删除优点是：支持回滚，后悔了可以再恢复数据！！！

使用truncate（属于DDL操作）

1
truncate table dept_bak;

特点：

  • 这种删除效率比较高，表被一次截断，物理删除。
  • 这种删除缺点：不支持回滚。
  • 这种删除优点：快速。

如何选择：大表非常大，上亿条记录？？？？ 删除的时候，使用delete，也许需要执行1个小时才能删除完！效率较低。 可以选择使用truncate删除表中的数据。只需要不到1秒钟的时间就删除结束。效率较高。 但是使用truncate之前，必须仔细询问客户是否真的要删除，并警告删除之后不可恢复！

约束

概念

约束对应的英语单词：constraint。 在创建表的时候，我们可以给表中的字段加上一些约束，来保证这个表中数据的完整性、有效性！！！

约束的作用就是为了保证：表中的数据有效！！

分类

  1. 非空约束：not null
  2. 唯一性约束: unique
  3. 主键约束: primary key （简称PK）
  4. 外键约束：foreign key（简称FK）
  5. 检查约束：check（mysql不支持，oracle支持）

介绍

  1. 非空约束

    not null约束的字段不能为NULL。

  2. 唯一性约束: unique

    unique约束的字段不能重复，但是可以为NULL。

    表级约束

    需要给多个字段联合起来添加某一个约束的时候，需要使用表级约束。

    1
    2
    3
    4
    5
    6
    create table t_vip(
    				id int,
    				name varchar(255),
    				email varchar(255),
    				unique(name,email) // 约束没有添加在列的后面，这种约束被称为表级约束。
    			);

  3. 主键约束

    主键约束：就是一种约束。 主键字段：该字段上添加了主键约束，这样的字段叫做：主键字段 主键值：主键字段中的每一个值都叫做：主键值。

    主键值是每一行记录的唯一标识。 主键值是每一行记录的身份证号！！！

    记住：任何一张表都应该有主键，没有主键，表无效！！

    主键的特征：not null + unique（主键值不能是NULL，同时也不能重复！）

    1
    2
    3
    4
    5
    // 1个字段做主键，叫做：单一主键
    		create table t_vip(
    			id int primary key,  //列级约束
    			name varchar(255)
    		);

    表级约束主要是给多个字段联合起来添加约束

    1
    2
    3
    4
    5
    6
    7
    // id和name联合起来做主键：复合主键！！！！
    create table t_vip(
    	id int,
    	name varchar(255),
    	email varchar(255),
    	primary key(id,name)
    );

    在实际开发中不建议使用：复合主键。建议使用单一主键！ 因为主键值存在的意义就是这行记录的身份证号，只要意义达到即可，单一主键可以做到。 复合主键比较复杂，不建议使用！！！

    一张表，主键约束只能添加1个。（主键只能有1个。）

    主键值建议使用：int、bigint、char等类型。

    不建议使用：varchar来做主键。主键值一般都是数字，一般都是定长的！

  4. 外键约束

    外键约束：一种约束（foreign key） 外键字段：该字段上添加了外键约束 外键值：外键字段当中的每一个值。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    t_class 班级表
    classno(pk)			classname
    ------------------------------------------------------
    100					北京市大兴区亦庄镇第二中学高三1班
    101					北京市大兴区亦庄镇第二中学高三1班
       	
    t_student 学生表
    no(pk)			name				cno(FK引用t_class这张表的classno)
    ----------------------------------------------------------------
    1					jack				100
    2					lucy				100
    3					lilei				100
    4					hanmeimei			100
    5					zhangsan			101
    6					lisi				101
    7					wangwu				101
    8					zhaoliu				101

    当cno字段没有任何约束的时候，可能会导致数据无效。可能出现一个102，但是102班级不存在。 所以为了保证cno字段中的值都是100和101，需要给cno字段添加外键约束。 那么：cno字段就是外键字段。cno字段中的每一个值都是外键值。

    注意： t_class是父表 t_student是子表

    ​ 删除表的顺序？ ​ 先删子，再删父。

    ​ 创建表的顺序？ ​ 先创建父，再创建子。

    ​ 删除数据的顺序？ ​ 先删子，再删父。

    ​ 插入数据的顺序？ ​ 先插入父，再插入子。

    思考：子表中的外键引用的父表中的某个字段，被引用的这个字段必须是主键吗？ 不一定是主键，但至少具有unique约束。外键值可以为NULL。

主键分类

  • 自然主键：主键值是一个自然数，和业务没关系。
  • 业务主键：主键值和业务紧密关联，例如拿银行卡账号做主键值。这就是业务主键！

在实际开发中使用业务主键多，还是使用自然主键多一些？ 自然主键使用比较多，因为主键只要做到不重复就行，不需要有意义。 业务主键不好，因为主键一旦和业务挂钩，那么当业务发生变动的时候， 可能会影响到主键值，所以业务主键不建议使用。尽量使用自然主键。

存储引擎

存储引擎是MySQL中特有的一个术语，其它数据库中没有。（Oracle中有，但是不叫这个名字）实际上存储引擎是一个表存储/组织数据的方式。不同的存储引擎，表存储数据的方式不同。

在建表的时候可以在最后小括号的")"的右边使用：

  • ENGINE来指定存储引擎。
  • CHARSET来指定这张表的字符编码方式。
1
2
3
4
5
6
7
8
CREATE TABLE `t_student` (
	  `no` int(11) NOT NULL AUTO_INCREMENT,
	  `name` varchar(255) DEFAULT NULL,
	  `cno` int(11) DEFAULT NULL,
	  PRIMARY KEY (`no`),
	  KEY `cno` (`cno`),
	  CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`cno`) REFERENCES `t_class` (`classno`)
	) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
  • mysql默认的存储引擎是：InnoDB
  • mysql默认的字符编码方式是：utf8

MyISAM存储引擎

它管理的表具有以下特征： 使用三个文件表示每个表：

  • 格式文件 — 存储表结构的定义（mytable.frm）

  • 数据文件 — 存储表行的内容（mytable.MYD）

  • 索引文件 — 存储表上索引（mytable.MYI）：索引是一本书的目录，缩小扫描范围，提高查询效率的一种机制。

    可被转换为压缩、只读表来节省空间

提示一下： 对于一张表来说，只要是主键，或者加有unique约束的字段上会自动创建索引。

MyISAM存储引擎特点：

  • 可被转换为压缩、只读表来节省空间
  • 这是这种存储引擎的优势！！！！
  • MyISAM不支持事务机制，安全性低。

InnoDB存储引擎

这是mysql默认的存储引擎，同时也是一个重量级的存储引擎。 InnoDB支持事务，支持数据库崩溃后自动恢复机制。 InnoDB存储引擎最主要的特点是：非常安全。

它管理的表具有下列主要特征：

  • 每个 InnoDB 表在数据库目录中以.frm 格式文件表示
  • InnoDB 表空间 tablespace 被用于存储表的内容（表空间是一个逻辑名称。表空间存储数据+索引。）
  • 提供一组用来记录事务性活动的日志文件
  • 用 COMMIT(提交)、SAVEPOINT 及ROLLBACK(回滚)支持事务处理
  • 提供全 ACID 兼容
  • 在 MySQL 服务器崩溃后提供自动恢复
  • 多版本（MVCC）和行级锁定
  • 支持外键及引用的完整性，包括级联删除和更新

InnoDB最大的特点就是支持事务：

  • 以保证数据的安全。效率不是很高，并且也不能压缩，不能转换为只读，
  • 不能很好的节省存储空间。

MEMORY存储引擎

使用 MEMORY 存储引擎的表，其数据存储在内存中，且行的长度固定， 这两个特点使得 MEMORY 存储引擎非常快。

MEMORY 存储引擎管理的表具有下列特征：

  • 在数据库目录内，每个表均以.frm 格式的文件表示。
  • 表数据及索引被存储在内存中。（目的就是快，查询快！）
  • 表级锁机制。
  • 不能包含 TEXT 或 BLOB 字段。

MEMORY 存储引擎以前被称为HEAP 引擎。

MEMORY引擎优点：查询效率是最高的。不需要和硬盘交互。 MEMORY引擎缺点：不安全，关机之后数据消失。因为数据和索引都是在内存当中。

事务

一个事务其实就是一个完整的业务逻辑。是一个最小的工作单元。不可再分。

什么是一个完整的业务逻辑？ 假设转账，从A账户向B账户中转账10000. 将A账户的钱减去10000（update语句） 将B账户的钱加上10000（update语句） 这就是一个完整的业务逻辑。

以上的操作是一个最小的工作单元，要么同时成功，要么同时失败，不可再分。 这两个update语句要求必须同时成功或者同时失败，这样才能保证钱是正确的。

只有DML语句才会有事务这一说，其它语句和事务无关！！！

insert delete update

只要你的操作一旦涉及到数据的增、删、改，那么就一定要考虑安全问题。

本质上，一个事务其实就是多条DML语句同时成功，或者同时失败！

事务：就是批量的DML语句同时成功，或者同时失败！

事务的提交与回滚

在事务的执行过程中，每一条DML的操作都会记录到“事务性活动的日志文件”中。 在事务的执行过程中，我们可以提交事务，也可以回滚事务。

  • 提交事务 清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中。 提交事务标志着，事务的结束。并且是一种全部成功的结束。

  • 回滚事务 将之前所有的DML操作全部撤销，并且清空事务性活动的日志文件 回滚事务标志着，事务的结束。并且是一种全部失败的结束。

mysql默认情况下是支持自动提交事务的。（自动提交）

每执行一条DML语句，则提交一次！

将mysql的自动提交机制关闭掉

1
start transaction;

事务的特性

A：原子性
  • 说明事务是最小的工作单元。不可再分。
C：一致性
  • 所有事务要求，在同一个事务当中，所有操作必须同时成功，或者同时失败，以保证数据的一致性。
I：隔离性
  • A事务和B事务之间具有一定的隔离。教室A和教室B之间有一道墙，这道墙就是隔离性。
  • A事务在操作一张表的时候，另一个事务B也操作这张表会那样？？？
D：持久性
  • 事务最终结束的一个保障。事务提交，就相当于将没有保存到硬盘上的数据保存到硬盘上！

事务的隔离性

事务和事务之间4个隔离级别：

  1. 读未提交：read uncommitted（最低的隔离级别）

    事务A可以读取到事务B未提交的数据。

    这种隔离级别存在的问题就是： 脏读现象！(Dirty Read) 我们称读到了脏数据。 这种隔离级别一般都是理论上的，大多数的数据库隔离级别都是二档起步！

  2. 读已提交：read committed

    事务A只能读取到事务B提交之后的数据。

    这种隔离级别解决了什么问题？

    解决了脏读的现象。

    这种隔离级别存在什么问题？

    不可重复读取数据。

    什么是不可重复读取数据呢？

    在事务开启之后，第一次读到的数据是3条，当前事务还没有结束，可能第二次再读取的时候，读到的数据是4条，3不等于4，称为不可重复读取。

    这种隔离级别是比较真实的数据，每一次读到的数据是绝对的真实。 oracle数据库默认的隔离级别是：read committed

  3. 可重复读：repeatable read

    事务A开启之后，不管是多久，每一次在事务A中读取到的数据都是一致的。即使事务B将数据已经修改，并且提交了，事务A 读取到的数据还是没有发生改变，这就是可重复读。

    可重复读解决了什么问题？ 解决了不可重复读取数据。

    可重复读存在的问题是什么？ 可能会出现幻影读。

    每一次读取到的数据都是幻象。不够真实！

    早晨9点开始开启了事务，只要事务不结束，到晚上9点，读到的数据还是那样！ 读到的是假象。不够绝对的真实。

    mysql中默认的事务隔离级别就是这个！！！！！！！！！！！

  4. 序列化/串行化：serializable

    这是最高隔离级别，效率最低。解决了所有的问题。 这种隔离级别表示事务排队，不能并发！ synchronized，线程同步（事务同步） 每一次读取到的数据都是最真实的，并且效率是最低的。

MySQL 所有
#MySQL
MySQL学习笔记03
https://jetthuang.top/所有/MySQL学习笔记03/
作者
Jett Huang
发布于
2021年8月27日
许可协议