老杜MySQL第三天笔记_primary key解决什么问题-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_65508929/article/details/127592345

文章目录

insert语句一次性插入多条记录

语法：insert into t_user(字段名1，字段名2) values (), (), (), ();

insert into t_user(id,name,birth,create_time) values

(1,‘zs’,‘1980-10-11’,now()),

(2,‘lisi’,‘1981-10-11’,now()),

(3,‘wangwu’,‘1982-10-11’,now()

);

快速创建表

原理：将一个查询结果当作一张表新建，这个可以完成表的快速复制，表创建出来，同时表中的数据也就存在了

将查询结果插入到一张表当中（insert相关的）

insert into dept_back select * from dept;

快速删除表中的数据（truncate）

删除dept_back表中的数据

delete from dept_back;//这种删除数据的方式比较慢

delete语句删除数据的原理？（delete属于DML语句！！！）

表中的数据被删除了，但是这个数据在硬盘上的真实存储空间不会被释放！！！

这种删除缺点是：删除效率比较低。

这种删除优点是：支持回滚，后悔了可以再恢复数据！！！

回滚：start transaction

truncate语句删除数据的原理？

这种删除效率比较高，表被一次截断，物理删除。

这种删除缺点：不支持回滚。

这种删除优点：快速。

例如：

大表非常大，上亿条记录

删除的时候，使用delete，也许需要执行1个小时才能删除完！效率较低。

可以选择使用truncate删除表中的数据。只需要不到1秒钟的时间就删除结束。效率较高。

但是使用truncate之前，必须仔细询问客户是否真的要删除，并警告删除之后不可恢复！注意：truncate是删除表中的数据，表还在！

删除表操作：drop table 表名;//这不是删除表数据，而是把表删了

对表结构的增删改

什么是对表结构的修改？

添加一个字段，删除一个字段，修改一个字段！！！

对表结构的修改需要使用：alter

属于DDL语句

DDL包括：create drop alter

第一：在实际的开发中，需求一旦确定之后，表一旦设计好之后，很少的

进行表结构的修改。因为开发进行中的时候，修改表结构，成本比较高。

修改表的结构，对应的java代码就需要进行大量的修改。成本是比较高的。

这个责任应该由设计人员来承担！

第二：由于修改表结构的操作很少，所以我们不需要掌握，如果有一天

真的要修改表结构，你可以使用工具！！！！

修改表结构的操作是不需要写到java程序中的。实际上也不是java程序员的范畴。

约束

约束是什么

约束对应的英文单词：constraint

在创建表的时候，我们可以给表中的字段加上一些约束，来****保证这个表中数据的完整性、有效性

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

约束包括哪些

非空约束：not null

非空约束not null约束的字段不能为null

drop table if exists t_vip;

create table t_vip(

id int,

name varchar(255) not null // not null只有列级约束，没有表级约束**！**

);

insert into t_vip(id,name) values(1,‘zhangsan’);

insert into t_vip(id,name) values(2,‘lisi’);

insert into t_vip(id) values(3);

由于将名字约束为not null，即不能为空，而这里插入的时候为null，因此报错

小插曲：xxxx.sql 这种文件被称为sql脚本文件

sql脚本文件中编写了大量的sql语句，而我们执行sql脚本文件的时候，该文件中所有的sql语句会全部执行，因此如果想要批量的执行sql语句，可以使用sql脚本文件

在MySQL中执行sql脚本

source D:\coures\03-MySQL\document\vip.sql

即将sql文件导入进来

唯一性约束：unique

唯一性约束unique约束的字段不能重复，但是可以为null

drop table if exists t_vip;

create table t_vip(

id int,

name varchar(255) unique**,**

email varchar(255)

);

insert into t_vip(id,name,email) values(1,‘zhangsan’,‘zhangsan@123.com’);

insert into t_vip(id,name,email) values(2,‘lisi’,‘lisi@123.com’);

insert into t_vip(id,name,email) values(3,‘wangwu’,‘wangwu@123.com’);

select * from t_vip;

再插入一个名字相同的记录，会报错，因为名字设置为unique，即唯一性约束

另外，name字段虽然被unique约束了，但是可以为null
insert into t_vip (id) values (4);
insert into t_vip (id) values (5);

新需求：name和email两个字段联合起来具有唯一性
如果按以下形式创建呢

drop table if exists t_vip;

create table t_vip(

id int,

name varchar(255) unique, // 约束直接添加到列后面的，叫做列级约束。

email varchar(255) unique

);

**以上表创建是不符合我们的新需求的，这样的创建表示：**name具有唯一性，email具有唯一性。即各自唯一
这样的话：当我们插入name相同，email不同的数据会插入不进去，会error

只有将约束放在最后，unique(name, email)

drop table if exists t_vip;

create table t_vip(

id int,

name varchar(255),

email varchar(255),

unique(name,email) // 约束没有添加在列的后面，这种约束被称为表级约束。

);

这样子name和email两个字段是联合起来唯一，这样子添加name相同，email不同的数据不会添加不进去

只有当添加name和email两个字段都相同的情况下，才会添加不进去，error

那么什么时候使用表级约束呢？
需要给多个字段联合起来添加某一个约束的时候，需要使用表级约束
unique和not null可以联合吗？ – 可以

drop table if exists t_vip;

create table t_vip(

id int,

name varchar(255) not null unique

);

mysql> desc t_vip;

在MySQL当中，如果一个字段同时被not null和unique约束的话，该字段自动变成主键字段（注意：Oracle中不一样）

insert into t_vip(id,name) values(1,‘zhangsan’);

insert into t_vip(id,name) values(2,‘zhangsan’); //错误了：name不能重复

insert into t_vip(id) values(2); //错误了：name不能为NULL。

主键约束：primary key（简称PK）

主键约束的相关术语：

主键约束：就是一种约束

主键字段：该字段上添加了主键约束，这样的字段叫做：主键字段

主键值：主键字段中的每一个值都叫做：主键值

什么是主键？有啥用？

主键值是每一行记录的唯一标识

主键值是每一行记录的身份证号

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

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

给一张表添加主键约束

drop table if exists t_vip;

// 1个字段做主键，叫做：单一主键

create table t_vip(

**id int primary key, //**列级约束

name varchar(255)

);

insert into t_vip(id,name) values(1,‘zhangsan’);

insert into t_vip(id,name) values(2,‘lisi’);

增加重复的主键值 - error

主键值为null - error

使用表级约束添加主键

drop table if exists t_vip;

create table t_vip(

id int,

name varchar(255),

primary key(id) // 表级约束

);

insert into t_vip(id,name) values(1,‘zhangsan’);

添加重复的主键 - error

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

drop table if exists t_vip;

// id和name联合起来做主键：复合主键**！！！！**

create table t_vip(

id int,

name varchar(255),

email varchar(255),

primary key(id,name)

);

insert into t_vip(id,name,email) values(1,‘zhangsan’,‘zhangsan@123.com’);

insert into t_vip(id,name,email) values(1,‘lisi’,‘lisi@123.com’);

再次添加重复的记录，即id和name都相同，error

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

主键值一般使用：int、bigint、char等类型
不建议使用：varchar来做主键，主键值一般都是数字，一般都是定长的

主键除了：单一主键和复合主键之外，还可以这样进行分类？

自然主键：主键值是一个自然数，和业务没关系。

业务主键：主键值和业务紧密关联，例如拿银行卡账号做主键值。这就是业务主键！

在实际开发中使用业务主键多，还是使用自然主键多一些？

自然主键使用比较多，因为主键只要做到不重复就行，不需要有意义。

业务主键不好，因为主键一旦和业务挂钩，那么当业务发生变动的时候，

可能会影响到主键值，所以业务主键不建议使用。尽量使用自然主键。

在mysql当中，有一种机制，可以帮助我们自动维护一个主键值？

drop table if exists t_vip;

create table t_vip(

id int primary key auto_increment, //auto_increment表示自增，从1开始，以1递增！

name varchar(255)

);

insert into t_vip(name) values(‘zhangsan’);

select * from t_vip;

外键约束：foreign key（简称FK）

外键约束涉及到的相关术语：

外键约束：一种约束（foreign key）

外键字段：该字段上添加了外键约束

外键值：外键字段当中的每一个值。

业务背景：设置数据库表，来描述班级和学生的信息

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

所以：cno字段就是外键字段，cno字段中的每一个值都是外键值
由于有外键约束，因此error

注意：

t_class是父表

t_student是子表

删除表的顺序：先删子，再删父
创建表的顺序：先创建父，再创建子
删除数据的顺序：先删子，再删父
插入数据的顺序：先插入父，再插入子

有了外键约束后，就不能随便写了，要写引用表的字段的数据
**子表中的外键引用的父表中的某个字段，**被引用的这个字段不一定是主键，但是至少要有unique约束
注意：外键值可以为null

检查约束：check（MySQL不支持，Oracle支持）

存储引擎

基本介绍

存储引擎是MySQL中特有的一个术语，其它数据库中没有。（Oracle中有，但是不叫这个名字）

存储引擎这个名字高端大气上档次。

实际上存储引擎是一个表存储/组织数据的方式。

不同的存储引擎，表存储数据的方式不同。

给表添加/指定“存储引擎”

可以在建表的时候给表指定存储引擎

看表的内部：show create table t_student;

如上图，我们发现那么可以在建表的时候，可以在最后小括号")"的右边使用

ENGINE来指定存储引擎

CHARSET来指定这张表的字符编码方式

结论：MySQL默认的存储引擎是：InnoDB，默认的字符编码方式是：utf8

建表时指定存储引擎以及字符编码方式

MySQL支持的存储引擎

show engines \G

我的MySQL是8.0.31版本的，支持8个引擎

MySQL常用的存储引擎

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引擎缺点：不安全，关机之后数据消失。因为数据和索引都是在内存当中。

事务（transaction）

基本介绍

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

一个完整的业务逻辑：假设转账，从A账户向B账户中转账10000，将A账户的钱减去10000(update语句)，将B账户的钱加上10000(update语句)，这就是一个完整的业务逻辑

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

**注意：**只有DML语句才会有事务，其他语句和事务无关

即insert、delete、update，只有这三个语句和事务有关系，其他的都没有关系

**因为：**只有以上的三个语句是数据库表中数据进行增、删、改的，只要你的操作一旦涉及到数据的增、删、改，那么就一定要考虑安全问题

数据安全第一位

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

做某件事的时候，需要多条DML语句共同联合起来才能完成，所以才需要事务的存在，如果任何一件复杂的事情都能一条DML语句搞定，那么事务将会没有存在的价值

事务如何做到多条DML语句同时成功和同时失败呢（提交事务、回滚事务）

InnoDB存储引擎：提供一组用来****记录事务性活动的日志文件

在事务的执行过程中，每一条DML的操作都会记录到"事务性活动的日志文件"中

在事务的执行过程中，我们可以提交事务，也可以回滚事务

测试当前MySQL中默认的事务行为

MySQL默认情况下是支持自动提交事务的。即每执行一条DML语句，就提交一次

但是这种自动提交实际上是不符合我们的开发习惯的，因为一个业务通常是需要多条DML语句共同执行才能完成的，为了保证数据的安全，必须要求同时成功之后再提交，所以不能执行一条就提交一条

如何关闭MySQL的自动提交机制：start transaction;

提交事务：commit;

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

即：提交事务就是结束了事务，然后将存储在事务性活动的日志文件给"删除"，相当于是代码转换成数据一样放到数据库表中

注意：回滚只能回滚到上一次提交点

回滚事务：rollback;

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

注意：回滚永远都是只能回滚到上一次的提交点

由于没有提交，因此回滚会直接回滚到最开始的情况

事务包含的4个特性

A：原子性

说明事务是最小的工作单元，不可再分

C：一致性

所有事物要求，在同一个事务当中，所有操作必须同时成功，或者同时失败，以保证数据的一致性

I：隔离性

A事务和B事务之间具有一定的隔离，教室A与教室B之间就有一道墙，这道墙就是隔离性

D：持久性

**事务最终结束的一个保障。**事务提交，就相当于将没有保存到硬盘上的数据保存到硬盘上

事务的隔离性

事务与事务之间的隔离级别（4个级别）

读未提交：read uncommitted（最低的隔离级别）【没有提交就读到了】

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

存在的问题：脏读现象（Dirty Read）

这种隔离级别一般都是理论上的，大多数的数据库隔离级别都是二档起步

读已提交：read committed【提交之后才能读到】

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

解决的问题：解决了脏读的现象

存在的问题：不可重复读取数据

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

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

可重复读：repeatable read【提交之后也读不到，永远读取的都是刚开启事务时的数据】

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

解决的问题：解决了不可重复读取数据

存在的问题：会出现幻影读，每一次读取到的数据都是幻想，不够真实

好比银行总账需要执行一条select语句，而这条select语句可能从下午1点开始执行到下午3点，那么1点到3点可能会有存款取款什么的，即1点-3点，这个结果应该是1点的结果，那么就可以用可重复读
MySQL中默认的事务隔离级别就是：repeatable read

序列化/串行化：serializable（最高的隔离级别）

这是最高隔离级别，效率最低，解决了所有的问题。

这种隔离级别表示事务排队，不能并发

synchronized，线程同步（事务同步）

每一次读取到的数据都是最真实的，并且效率是最低的

验证各种隔离级别

查看隔离级别：select @@transaction_isolation;
注意：版本不同，所用的命令不同

read uncommitted

set global transaction isolation level read uncommitted;

这种隔离级别是一边没有退出commit，另一边就能接受到

read committed

set global transaction isolation level read committed;

这种隔离级别是一边只有提交了，另一边才能查到

repeatable read

set global transaction isolation level repeatable read;

这种隔离级别是即使提交了，也还是原来那样

serializable

set global transaction isolation level serializable;

当一边在使用的时候，另一边就卡住了，只有一边结束后，另一边才会继续