MySQL 基础（高级概念和试验）

[18888628835]

MySQL 基础（高级概念和试验）

数据类型

常见数据类型

• varchar（最长255）可变长度字符串，这种数据类型会根据实际的数据长度动态分配内存空间。优点是节省空间，缺点是速度慢。
• char（最长255）定长字符串，不管实际的字符串长度，固定分配内存空间，使用不恰当时可能造成空间浪费。优点是速度快。
• int（最长11）数字整数型。
• bigint 数字中的长整型。
• float 单精度浮点型数据
• double 双精度浮点型数据
• date 短日期类型
• datetime 长日期类型
• clob（Character Large Object）字符大对象，最多可以存储4g 字符串。比如存储文章、简介、说明等。超过255字符的都需要用到 CLOB 字符大对象
• blob（Binary Large Object） 二进制大对象，存储图片、声音、视频等，往此字段插入数据时，需要使用 流

以下是某电影的数据库设计表示例

编号	名字	描述信息	上映日期	时长	海报	类型
No(bigint)	Name(varchar)	Description(clob)	Playtime(data)	Time(double)	Image(blob)	Type(char)

date和 datetime 的区别

date 是短日期，只包含年月日信息。

datetime 是长日期，包含年月日时分秒信息。

MySql 短日期默认格式：%Y-%m-%d 如 1992-09-22

MySql 短日期默认格式：%Y-%m-%d %h:%i:%s 如 1992-9-22 00:00:00

DML 语句

insert 插入表数据

语法：insert into 表名 (字段名1，字段名2，字段名3 ...) values (值1，值2，值3 ...);

注意字段名和值要一一对应

数量要对应，数据类型要对应

INSERT INTO t_students(no,name,age,gender,email)VALUES(1000,"zhangsan",20,"m","zhangsan@123.com");

insert 插入日期

插入日期可以使用 str_to_date 函数进行转换，将字符串转换成date 格式。

str_to_date('字符串日期','日期格式')

mysql 日期格式：

• %Y 年
• %m 月
• %d 日
• %h 时
• %i 分
• %s 秒

示例

str_to_date('1992-09-22','%Y-%m-%d')

通常在insert 中使用 str_to_date 函数。

数据库默认有自动类型转化，如果语句中默认是以下格式，则可以省略 str_to_date 函数

'%Y-%m-%d'

比如以下 sql 语句就会自动格式化日期,给 birth 字段插入date类型的数据。

INSERT INTO t_students(birth)VALUES('1992-09-22');

当需要查询的时候，sql 会自动将日期格式化成字符串(varchar),默认格式为('%Y-%m-%d')

如果希望 date 转化为 str，则可以使用 date_format 函数。

DATE_FORMAT(birth ,'%Y/%m/%d')   // 查询后显示为 1992/09/22

当插入日期时，获取系统日期使用 now()函数，获取的时间带有时分秒，属于长日期

INSERT INTO t_students(create_time) values(NOW());
2021-07-26 11:52:38

Insert 插入多条记录

语法格式：

INSERT INTO t_students(no) VALUES(1),(2),(3),(4);

以上语句会往 t_students 中的 no 字段插入多条记录，分别是1、2、3、4

insert 将查询结果插入表

语法格式：

insert into 被插入的表 select * from xx表

update 修改数据

语法格式：

update 表名 set 字段名1=值1,字段名2=值2...where 条件;

如果没有写 where条件，那么会导致所有数据都更新。

写了 where条件，则会将所有匹配到的数据的字段进行更新。

UPDATE t_students SET name='qiuyanxi' where birth='1992-09-22';

delete 删除数据

语法格式：

delete from 表名 where 条件;

如果没有 where 条件，则整张表的数据都会被删除。

以下语句会将 name 为'zhangsan'的行数据（record）给删除掉。

delete FROM t_students where name='zhangsan';

使用delete 语句，虽然表中的数据被删除了，但是这个数据在硬盘上的硬盘存储空间不会被释放。

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

优点是：删除的数据可回滚

DDL 语句

truncate快速删除数据

语法格式：

truncate table 表名

使用 truncate 语句，删除效率比较高，表被一次截断，物理删除。

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

优点是：删除效率高

常用场景：当遇到很多数据的表且不需要回滚时，可以使用truncate删除。删除后数据不可恢复。

约束（constraint）

在创建表时，我们可以在表中加入一些约束，来保证数据的完整性、有效性。

常见约束包括：

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

• 唯一性约束 unique 约束的字段有唯一性，但是可以都为 null

• 主键约束 primary key

• 外键约束 foreign key

• 检查约束 check (mysql 不支持)

unique

当创建表时，可以用两种方式指定unique，分别为列约束和表约束。

列约束语法：

create table 表名(

	id int,

	name varchar(255) unique,
);

表约束语法：

create table 表名(
  
	id int,
  
  name varchar(255),
  
  unique(id,name)
);

当使用多个字段联合起来添加一种约束时，则使用表级约束。

在 mysql 中，如果一个字段同时被 not null 和 unique 约束的话，该字段自动变成主键字段。

主键 primary key

主键约束的相关术语：

• 主键约束：就是一直约束

• 主键字段：添加了主键约束的字段

• 主键值：主键字段对应的每一个值

1. 什么是主键？

主键（值）是每一行记录的唯一标识，可以理解为身份 ID。

任何一张表都应该有主键，如果没有主键，表无效。

主键的特征：not null + unique。（主键不能是 null，且不能重复）

2. 向表中添加主键字段

列级约束语法：

create table 表名(
  
	id int primary key,
  
  name varchar(255)
  
);

列级约束后的主键叫单一主键。

表级约束语法：

create table 表名(
  
	id int,
  
  name varchar(255),
  
  primary key(id)
  
);

表级约束主要给多个字段联合起来添加约束 ，多字段联合起来的主键叫复合主键。

create table 表名(
  
	id int,
  
  name varchar(255),
  
  primary key(id,name)//复合主键
  
);

在一张表中，主键约束只能添加一个。

3. 主键值建议类型

主键建议使用 bigint 或者 char 类型，不建议使用 varchar

4. 自然主键和业务主键

主键除了分为单一主键和复合主键外，还可以分为自然主键和业务主键。

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

在实际开发中，自然主键使用更多。因为主键并不需要有意义，只需要做到不重复就行了。

如果使用业务主键，那么当业务发生变动时，很有可能影响到主键值，所以不建议使用业务主键。

5. 自动生成主键值

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

使用 auto_increment 关键字可以进行自增(从1开始以1递增)

create table 表名 (
	id int primary key auto_increment,
  name varchar(255)
);

外键约束

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

• 外键约束:一种约束(foreign key)

• 外键字段:该字段添加了外键约束

• 外键值:外键字段对应的每一个值

1. 什么是外键

关系型数据库有一个特点,为了让多表之间的数据互相有关联,表字段中会有一个外键.

这个外键承接着本表与外表的业务关系.

比如现在有这样两张表:

班级编号 | 班级姓名

class_no	class_name
100	北京市高级中学高一1班
101	北京市高级中学高一2班

编号 | 姓名 | 班级编号

num	stu_name	class_no
1	Qiuyanxi	100
2	Yangfan	101

这两张表之间的关系依靠 classNo 维系,classNo 就是外键 (foreign key)

2. 外键约束

当使用外键时,为了规范,以保证数据的有效性,会特意对外键进行约束.这里的顺序是这样的

当一张表依赖另一张表时,需要先创建被依赖的表

以上面的两张表为例,首先应该创建 班级编号|班级姓名 表

create table t_class(
	class_no int primary key auto_increment,
  class_name varchar(255)
);

再创建 编号 | 姓名 | 班级编号 表,并且设置外键依赖

create table t_students(
	num int primary key auto_increment,
  stu_name varchar(255),
  class_no int,
  foreign key(class_no) references t_class(class_no)
);

我们称 班级编号|班级姓名为父表,编号 | 姓名 | 班级编号为子表,

当子表增删改查的数据跟不依赖父表时,就会报错.这就是外键约束.

外键值可以是 null,外键字段可以不是父表的主键,但是必须具有唯一性,不能重复(unique约束).

存储引擎

存储引擎是 MySQL 中特有的术语,存储引擎实际上是一个表存储或者组织数据的方式.

不同的存储引擎,存储的方式不同.

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

create table t_class(
	class_no int primary key auto_increment,
  class_name varchar(255)
) ENGINE=InnoDB charset=utf8;

ENGINE=InnoDB charset=utf8的意思是

指定引擎为 InnoDB,指定字符编码方式为 utf8.

可以省略这句,因为 MySQL 的默认引擎为 InnoDB,默认字符编码方式为 utf8.

1. MySQL 支持的引擎

   可以使用这句命令查看所有支持的存储引擎

show engines;

2. 常用引擎

• InnoDB:默认存储引擎,支持事务处理、支持数据库崩溃后自动恢复机制。特点:安全
• MEMORY:使用MEMORY存储引擎的表，其数据存储在内存中，且行的长度固定，特点:查询效率极高。缺点：关机后数据消失。

事务 transaction

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

比如银行转账，从 A 账户向 B 账户转账10000元，数据库需要这样做

• 第一步：从 A 的账户执行 UPDATE 语句，减少10000元
• 第二步：从 B 的账户执行 UPDATE 语句，增加10000元

这就是一个完整的业务逻辑，同时也是最小的工作单位，不可再分。

两个语句要么同时成功要么同时失败，才能保证最后的业务逻辑——转账，是正确的。

事务的一些要点：

1. 只有DML 语句才有事务，也就是 delete、update、insert 语句才能有事务
2. 一个事务其实就是多条 DML 语句的同时成功或者同时失败

事务的实现

事务是如何让 多条DML语句同时成功或者失败的呢？

由于事务是基于 InnoDB 存储引擎的，InnoDB 会提供一组用来记录事务性活动的日志文件。

当事务开始时，每一条 DML 的操作都会记录到事务性活动的日志文件中。

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

当提交事务时：日志文件中的操作就会生效，将数据全部彻底持久化到数据库表中，并清空事务性活动的日志文件，提交事务标志着事务的结束，并且是全部成功的结束。

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

提交事务

mysql 默认情况下是自动提交（commit）事务的，每执行一条 DML 语句都会提交一次。

手动操作事务需要执行下面命令以开启手动事务功能 ：

start transaction;

开启手动事务后，需要 commit 才可以将数据真正存入到数据库中，否则不会存入，但可以查。

执行以下语句

-- 创建表
create table t_class(class_no int primary key auto_increment,class_name varchar(255)) ENGINE=InnoDB charset=utf8;
-- 开启手动事务
START TRANSACTION;
-- 插入数据
INSERT INTO t_class(class_name) values('中学');
-- 插入数据
INSERT INTO t_class(class_name) values('中学');
-- 插入数据
INSERT INTO t_class(class_name) values('中学');
-- 查询数据
SELECT * FROM t_class;

会查询出以下数据

但是实际上数据库的表里是没有数据的，也就是没有 commit，数据不会持久化。

当执行 commit 后，数据就在表中了。

COMMIT;

回滚事务rollback

commit之后的数据是不能再回滚的，rollback 和 commit 只能选择一个。每次回滚都只能回滚到上次的提交点。

事务的特性

事务包括四个特性：

A.原子性：说明事务是最小的工作单元，不可再分

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

I.隔离性：A 事务跟 B 事务之间具有一定的距离

D.持久性：事务最终结束的保障，事务提交就相当于将没有保存到表中的硬盘上的数据保存到硬盘上。

其他三条都很明了，最难懂是事务的隔离性

事务有4层隔离级别，从低到高的排序是这样的：

• 读，未提交 read uncommitted （没有提交就读到了）

  事务 A 可以读取到事务 B 未提交的数据，这种隔离级别存在的问题是脏读现象 Dirty Read。

  我们称读到了脏数据。这种隔离级别是理论上的，大多数的数据库隔离级别在第二层以上。

• 读，已提交 read committed （提交之后才能读到）

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

  这种隔离级别解决了脏读的现象。

  这种隔离级别存在问题，不可重复读取数据。

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

  这种隔离级别是比较真实的数据，每次读取到的数据绝对真实。

• 可重复读 repeatable read （提交之后也读不到，永远读取的都是开启事务时的数据）

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

  可重复读解决了不可重复读的问题，但是它存在的问题是可能会出现幻影读==>即每一次读取到的数据都是幻象，不够真实。

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

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

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

在 navicat 中的试验

read uncommitted试验步骤

1. 设置隔离级别

   -- 查看隔离级别
   SELECT @@tx_isolation;
   -- 设置全局隔离级别为 读未提交
    SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

   设置后需要关闭数据库重启一下

2. 分别建立两个查询窗口，进行演示

首先我们在窗口1执行以下 SQL

--  窗口1
 START TRANSACTION;
 SELECT * FROM t_class;

然后我们在窗口2执行以下 SQL

-- 窗口2
START TRANSACTION;
INSERT INTO t_class(class_name)values('123');

命令会开启事务并插入数据，未提交。

然后再进入窗口1执行以下 SQL

 SELECT * FROM t_class;

就会发现读取到了未提交的数据了。

此时窗口2可以回滚

ROLLBACK;

最终数据就会回滚，并清空。

这个试验说明当处于读未提交状态时，事务A 插入数据了没有提交，插入的数据可以被另外一个事务给读出来。这就是read uncommitted

read committed试验步骤

 -- 设置全局隔离级别为 读已提交
 SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
 --  窗口1
 START TRANSACTION;
 SELECT * FROM t_class;
 -- 窗口2
 START TRANSACTION;
 INSERT INTO t_class(class_name)values('123');

当前窗口2的数据处于未提交状态，如果从窗口1查询表，会发现表是空的，因为数据未提交

 --  窗口1
 SELECT * FROM t_class;

当窗口2的事务提交后，窗口1开启的事务查到了

-- 窗口2
COMMIT;
--  窗口1
 SELECT * FROM t_class;

repeatable read 试验步骤

 -- 设置全局隔离级别为 可重复读
 SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ; 
 -- 窗口1
 START TRANSACTION;
 SELECT * FROM t_class;

此时我数据库中的数据在第二个试验后没有删除，所以查出来是这样的

然后我手动将数据删除了

再查一下看看

 SELECT * FROM t_class;

会发现虽然我手动将数据库的数据删除了，并且在删除后进行查询，而结果依然存在。

这就是 可重复读 repeatable read。

有点类似于数据自从 start transaction 后被备份了，怎么查都是备份的数据，跟真实数据无关。

serializable

这个演示就不多说了，只说结论：当开启两个事务时，如果先开启的 A 事务没有提交，那么事务 B 就永远没办法执行下去。

有点像排队上厕所，需要一个人先出来，另一个人才能进去。

事务总结

事务在数据库中是非常重要的概念，主要需要理解的是以下内容：

1. 事务的实现，手动开启事务 START TRANSACTION;,事务的提交 commit;和回滚rollback;

2. 事务的概念：就是打一套组合拳把事情做完，组合拳只包括 DML 语句

3. 事务只能同时失败或者同时成功

4. 理解事务的特性，最重要的是隔离性，隔离性分为四个不同的级别，在开发中，一般是二档起步。

索引 index

索引是什么

索引在数据库表的字段上添加，是为了提高检索效率而存在的一种机制。如何提高检索效率呢？

这就好比我们翻字典，字典中会有偏旁、拼音的目录，我们通过这个目录来快速找到我们想要知道的字。

索引就相当于字典的目录，是为了缩小扫描范围而存在的一种机制。

一张表的一个字段可以添加一个索引，多个字段联合起来也可以添加索引。

索引实现的简单原理

假设有一张用户表：t_user

id(primary key)	name
100	Zhaoyi
99	Qianer
120	Sunsan

上面的用户表中，id 是主键，在任何数据库中，主键和有 unique 约束的字段都会自动创建索引对象。

索引对象（index）是一棵二叉搜索树，以上的索引对象就类似这样

二叉搜索树的左边始终比父节点要小，右边要比父节点大。

当我输入一下以下 sql 命令

select * from t_user where id=99;

如果没有索引，那么就会全表查询。上面的 id 是主键，自动拥有索引，于是就会顺着索引对象（二叉树），一级一级往下找。

比如查询时，需要查询的 id 为99，比当前节点100要小，就会顺着当前节点的左边查找。从而缩小扫描范围。

真实的索引树比图上画的复杂得多，但实现思想大致如此。

索引创建的条件

• 当数据量庞大时，需要创建索引
• 当经常依靠此字段进行搜索查询时，比如此字段经常出现在 where 的后面，以条件的形式存在时，说明此字段经常被扫描
• 该字段很少的 DML 操作。（因为 DML 之后，索引需要重新排序）

建议不要随意添加索引，因为索引也是需要维护的，太多的话会降低系统性能。

建议通过主键或者 unique 约束的字段查询，效率都是比较高的。

索引的创建

主键和 unique 字段会自动添加上索引，手动创建索引需要用这样的方式

语法

CREATE INDEX indexName ON table_name (column_name);

索引的删除

DROP INDEX [indexName] ON mytable; 

查看SQL 语句是否使用了索引进行检索

explain select ...

索引的失效

有些语句会自动让索引失效，下面列举几个（不代表全部）

1. 使用 like 模糊查询并且采用百分号开头

select * from emp where ename like '%T';

上面的语句用了模糊查询并且采用百分号开头%，这种情况下，索引就没有意义了，MySQL 会全表查询。

尽量避免模糊查询的时候使用%开头，这是一种优化策略。

2. 使用or的时候可能会失效

如果使用 or 则需要 or 两边的条件字段都要有索引，才会走索引，如果其中一个没有索引，那另外有索引的一方索引也自动失效。

使用or 的时候尽量保证条件字段都有索引

3. 使用复合索引的时候，没有使用左侧的列查找，索引失效

复合索引的意思是在添加索引时，使用两个或者两个以上的字段联合起来添加一个索引。

CREATE INDEX indexName ON table_name (column_name1,column_name2);

上面的 SQL 语句就是复合索引。

如果这样查就是走索引

select * from table_name where column_name1 = 'xxx';

如果这样查就是就不走索引

select * from table_name where column_name2 = 'xxx';

4. 索引列字段参加了列运算

select * from table_name where sal+1 =800;

5. 索引列字段使用了函数运算

select * from table_name where lower(ename)='smith';

索引总结

• 索引是各种数据库中优化的重要手段，优化的时候优先因素就是索引。

• 采用索引的方式可以避免全表查询，缩小查询范围

• 主键跟 unique 字段会自动添加索引

• 索引在有些情况下会失效

视图 view

视图对象的创建

create view viewName as select ...;

视图对象的删除

create view viewName;

只有 DQL 语句才能以 view 的形式创建。

create view viewName as DQL 语句;

视图的作用

我们可以面向视图对象进行增删改查，对视图对象的增删改查，会导致原表被操作（影响到原表的数据）

在实际开发中，很有可能出现重复的 SQL 语句来查询，我们可以创建一个视图对象来引用这条重复的 SQL 语句，

这样在需要编写这条 SQL 语句的位置直接使用视图对象，可以大大简化SQL 语句。

同时，这也有利于后期的维护，因为只需要修改创建视图对象时所映射的 SQL 语句就可以了。