MySQL入门指南5(约束,索引,事务)

前期回顾:

Solana

        MySQL入门指南1(进入命令行,数据库,数据表操作)

游戏开发

        MySQL入门指南2(SQLyog使用,增删改查)

回调地址

        MySQL入门指南3(常用函数)

局部内部类

        MySQL入门指南4(查询进阶,外连接)

学习

目录

PLL

一、约束

Linux命令

        1. PRINARY  KEY

图像生成

        2. NOT  NULL

远程调试

        3. UNIQUE

软著申请流程

        4. FOREIGN  KEY

pyinstaller

        5. CHECK

数据结构与算法

二、索引

数据迁移

三、事务

408

        1. 事务介绍

物联网

        2. 事务隔离级别

教学

        3. 事务ACID

三极管

四、最后的话

open


原理

一、约束

        约束用于确保数据库的数据满足特定的商业规则。在MySQL中,约束包括:NOT NULL、UNIQUE、PRIMARY  KEY、FOREIGN  KEY、CHECK。

异常处理

        1. PRINARY  KEY

        用于唯一的标示表行的数据,当定义主键约束后,该列不能重复。    

面试

-- 基本用法:
字段 数据类型 PRIMARY KEY;

简单工厂模式

使用细节:

Python入门

        1.primary key(主键)修饰的列中字段不能重复不能为null

canvas

        2. 一张表最多只能有一个主键,可以是复合主键。

ubuntu

        3. 主键的指定方式有两种

                        a. 创建表时直接在字段后加上 primary  key

                        b. 在表定义最后写primary  key(列)

        4.使用desc可以看到 primary key 的情况

        5. 实际开发中每个表往往都会设计一个主键。

代码演示:

-- 第1种指定主键的方式:直接在字段后加上 PRIMARY KEY
CREATE TABLE test5(
	id INT PRIMARY KEY, -- 指定为主键,其不可为NULL,不可重复
	`name` VARCHAR(23));
	
INSERT INTO test5
	VALUES(1,'mike');
	
INSERT INTO test5
	VALUES(2,'milan');
	
-- id为主键不可重复	
INSERT INTO test5
	VALUES(1,'john');

-- id为主键不可为NULL
INSERT INTO test5
	VALUES(NULL,'erson');
	
-- 第2种指定主键的方式:在表定义最后写PRIMARY KEY(列)	
CREATE TABLE test6(
	id INT,
	`name` VARCHAR(23),
	-- 此处设置为复合主键,这两个皆不可为NULL,
	PRIMARY KEY(id,`name`));

INSERT INTO test6
	VALUES(1,'mike');
	
INSERT INTO test6
	VALUES(2,'milan');
-- 只有id重复不算重复,可以成功插入	
INSERT INTO test6
	VALUES(1,'john');
-- 只有当二者都重复时才算重复	
INSERT INTO test6
	VALUES(1,'john');
-- 并且设为主键的列,均不能为NULL	
INSERT INTO test6
	VALUES(1,NULL);

DESC test5;

图解普通主键

图解复合主键


        2. NOT  NULL

        如果在列上定义了NOT NULL 那么当插入数据时,必须为列提供数据。

 -- 基本用法:
字段 字段类型 NOT NULL;

        3. UNIQUE

        当定义了唯一约束后,该列值是不能重复的。

基本用法:
字段名 字段类型 UNIQUE;

使用细节:

        1. 如果没有指定 NOT  NULL,则UNIQUE字段可以有多个NULL

        2.一张表中可以有多个UNIQUE

代码演示:

CREATE TABLE test7(
	id INT UNIQUE,
	`name` VARCHAR(23),
	email VARCHAR(23) UNIQUE NOT NULL); -- UNIQUE NOT NULL 类似于 PRIMARY KEY
	
INSERT INTO test7
	VALUES(1,'jack','jack@qq.com');
	
INSERT INTO test7
	VALUES(2,'tom','tom@qq.com');
-- id重复无法插入进去
INSERT INTO test7
	VALUES(2,'mumu','mumu@qq.com');

INSERT INTO test7
	VALUES(NULL,'lalala','lalala@qq.com');
-- id 为空,可插入多个
INSERT INTO test7
	VALUES(NULL,'lalala','lala@qq.com');

图解UNIQUE


        4. FOREIGN  KEY

        用于定义主表与从表之间的关系:外键约束要定义在从表上,主表则必须具有主键约束或者UNIQUE约束。当定义外键约束后,要求外键列数据必须在主表的主键列存在或者是为NULL。       

-- 基本用法:
FOREIGN KEY (本表字段名) 
    REFERENCES 主表名(主表的主键名或UNIQUE字段名)

使用细节:

        1. 外键指向的表的字段,要求是PRIMARY KEY或者是UNIQUE。

        2. 表的存储引擎必须是INDODB ,这样的表才支持外键。

        3. 外键字段的类型要与主键字段的类型保持一致(长度可不同)。

        4. 外键字段的值,必须在主键字段中出现过,或者为NULL(未指定NOT  NULL时)。

        5. 一旦建立主外键关系,数据就不能随意删除。必须先删除从表才能再删除主表。

代码演示:

-- FOREIGN KEY 测试
-- 主表  班级表
CREATE TABLE test9(
	id INT PRIMARY KEY,
	nam VARCHAR(23),
	address VARCHAR(23));
	
INSERT INTO test9
	VALUES(100,'java','北京'),(200,'web','上海');
-- 从表  学生表
CREATE TABLE test10(
	id INT PRIMARY KEY,
	`name` VARCHAR(23) NOT NULL DEFAULT '',
	class_id INT,
	-- 此处指向主键或UNIQUE
	FOREIGN KEY (class_id) REFERENCES test9(id)); 

INSERT INTO test10
	VALUES(1,'tom',100),(2,'jack',200);
	
INSERT INTO test10
	VALUES(3,'milan',300);
	
INSERT INTO test10
	VALUES(4,'erson',NULL);
	
SELECT * FROM test10;
SELECT * FROM test9;
	
CREATE TABLE test11(
	id INT PRIMARY KEY,
	`name` VARCHAR(23) NOT NULL DEFAULT '',
	class_id INT,
	-- 此处指向的不是主键因此执行不成功
	FOREIGN KEY (`name`) REFERENCES test9(`name`));

-- 删除时需要先删除从表
DROP TABLE test10;

-- 才能把删除主表
DROP TABLE test9;


        5. CHECK

        用于强制行数据必须满足的条件,若未满足条件则会提示出错。

        MySQL5.7目前还不支持CHECK,只做语法校验。

        在ORACLE和SQL SERVER 中均支持CHECK。

        在MySQL实现CHECK的功能,一般时在程序中控制,或者通过触发器完成。

-- 基本用法:
字段 字段类型 CHECK (检查条件)

代码演示: 

-- CHECK 测试
CREATE TABLE test8(
	id INT UNIQUE NOT NULL DEFAULT 0,
	`name` VARCHAR(23),
	sex CHAR(1) CHECK(sex IN('男','女')),
	age INT CHECK(age>18&&age<60));
	
INSERT INTO test8
	VALUES(1,'Lihua','中',78);

图解CHECK


二、索引

        说起提高数据库性能,索引是最物美价廉的东西了。不用加内存,不用改程序,不用调sql,查询速度就可能提高千倍百倍。

1.索引的原理

        MySQL表进行查询时在没有索引的情况下,是进行全表扫描来查找。

        如果使用了索引,则会形成一个索引的数据结构,比如二叉树。这样在查询时,效率就会大大提升。

        但在查询效率提升的同时,也需要付出一些代价:

        比如:

                1. 会占用更多的磁盘空间

                2. 会影响 dml ( update , delete , insert ) 语句的效率(因为每次在执行这些操作时都要重建索引)

2. 索引的类型

        a. 主键索引,主键自动的为主索引(类型 PRIMARY KEY)

        b. 唯一索引(UNIQUE)

        c. 全文索引(FULLTEXT)[适用于MyISAM存储引擎]

        一般开发,不适应MySQL自带的全文索引,而是使用:全文搜索Solr和ElasticSearch(ES)

 3. 索引的使用

4. 适合使用索引的列

代码演示: 

-- 首先我们创建了一个有八百万条数据的emp表,代码省略

-- 查询索引(三种方式)
-- 方式一
SHOW INDEX FROM emp;

SHOW INDEXES FROM emp;

-- 方式二
SHOW KEYS FROM emp;

-- 方式三
DESC emp;

-- 测试查询速度(未设置索引时)
SELECT * FROM emp
	WHERE ename = 'tZOXxv';  -- 此时用时3秒
	
SELECT * FROM emp
	WHERE empno = 100676;  -- 此时用时3秒

	
-- 添加索引
-- 1.添加普通索引INDEX
-- 方式一
CREATE INDEX index_empno ON emp(empno);

-- 方式二
ALTER TABLE emp ADD INDEX index_empno(empno);

-- 测试查询速度(添加索引列)
SELECT * FROM emp
	WHERE empno = 100676;  -- 此时用时0.008秒
	
-- 测试查询速度(未添加索引列)
SELECT * FROM emp
	WHERE ename = 'tZOXxv'; -- 此时用时3秒

-- 删除索引方式一
DROP INDEX index_empno ON emp;  -- 用于删除索引

-- 删除索引方式二
ALTER TABLE emp DROP INDEX index_empno; -- 用于删除索引

-- 2.添加唯一索引(UNIQUE)
CREATE INDEX index_empno ON emp(empno);

ALTER TABLE emp ADD UNIQUE INDEX index_empno(empno);

-- 3.添加主键索引(比较特殊)
ALTER TABLE emp ADD PRIMARY KEY (empno);

-- 删除主键索引(比较特殊)
ALTER TABLE emp DROP PRIMARY KEY;

三、事务

        1. 事务介绍

        事务用于保证数据的一致性,它由一组相关的DML语句(INSERT ,DELETE ,UPDATE )组成,该组的DML语句要么全部成功,要么全部失败。如:转账就要用事务来处理,以保证数据的一致性。

        当执行事务操作时(DML语句),MySQL会在表上加锁,防止其他用户更改表的数据。这对用户来讲非常重要。

         MySQL数据库控制台事务的几个重要操作

                1. start  transaction      — 开始一个事务

                2. savepoint  保存点名       — 设置保存点

                3. rollback  to   保存点名       — 回退事务

                4. rollback       — 回退全部事务

                5. commit        — 提交事务,所有的操作生效,不能回退

事务操作代码演示:

-- 事务测试
-- 开始事务
START TRANSACTION;

-- 执行DML语句
INSERT INTO test
	VALUES(5,'乐乐',18);

-- 设置保存点	
SAVEPOINT a;
	
-- 执行DML语句
UPDATE test
	SET age = 23
	WHERE NAME ='浩浩';
	
-- 设置保存点
SAVEPOINT b;

-- 执行DML语句
DELETE FROM test
	WHERE id=3;

-- 设置保存点
SAVEPOINT c;

-- 回退到指定保存点
ROLLBACK TO a;

-- 回退全部事务:回到事务开始时的状态
ROLLBACK;

-- 提交事务,所有操作生效,保存点失效
COMMIT;

回退事务:

        在介绍回退事务前,先介绍一下保存点(savepoint)。保存点是事务中的点,用于取消部分事务,当结束事务时(commit),会自动的删除该事务所定义的所有保存点。当执行回退事务时,通过指定保存点可以回退到指定的状态。 

提交事务:

        使用 commit 语句可以提交事务。当执行了 commit 语句后,会确认事务的变化、结束事务、删除保存点、释放锁、数据生效。当使用 commit 语句结束事务后,其它会话(其他连接)将可以查看到事务变化的新数据(所有数据就此生效)。 

 

 事务细节讨论:

        1. 如果不开始事务,默认情况下,DML操作是自动提交的,不能回滚。

        2. 如果开始一个事务,但没有创建保存点,可以执行 rollback,默认就是回退到你事务你开始的状态。

        3. 可以在事务中创建多个保存点(提交事务前)。

        4. 可以选择回退到你设置的保存点(提交事务前未回到比该保存点更早的保存点前

        5. MySQL的事务机制需要INNODB存储引擎才可以使用。 


        2. 事务隔离级别

        多个连接开启各自事务 操作数据库中数据时,数据库系统要负责隔离操作,以保证各个连接在获取数据时的准确性。

        如果不考虑隔离性,可能会引发如下问题:

        1. 脏读:一个事务读取到另一个事务尚未提交的改变(DML操作),此时发生脏读。

        2. 不可重复读:一个事务多次查询操作,由于其他事务提交所做的修改删除,每次查询到不同的结果,此时发生不可重复读。

        3. 幻读:一个事务多次查询操作,由于其他事务提交所作的插入操作,每次查询到不同的结果,此时发生幻读。

        在多连接事务操作中,一般我们期望的是:当前事务只能读取到当前事务开始时的事务状态,而不能读取到其他操作产生的影响。

        四种隔离级别

        概念:MySQL隔离级别定义了事务与事务之间的隔离程度。

               MySQL隔离级别     脏读   不可重复读   幻读    加锁读
     读未提交(Read uncommitted)       是           是    是    不加锁
       读已提交(Read committed)       否           是    是    不加锁
       可重复读(Repeatable read)       否           否    否    不加锁
           可串行化(Serializable)       否                   否    否      加锁

 设置事务隔离级别

        1. 查看当前会话隔离级别

SELECT @@tx_isolation;

        2. 查看系统当前隔离级别

SELECT @@global.tx_isolation;

        3. 设置当前对话隔离级别

SET SESSION TRANSACTION ISOLATION LEVEL 指定隔离级别;

        4. 设置系统当前隔离级别

SET GLOBAL TRANSACTION ISOLATION LEVEL 指定隔离级别;

        5. 全局修改,修改 my.ini 配置文件,在最后加上

transaction-isolation = 指定隔离级别

        6. MySQL默认的事务隔离级别是Repeatable read,一般情况下,没有特殊要求,没有必要修改,因为该级别可以满足绝大部分项目需求。

   

温馨提示:可以试着多开几个mysql对话然后设置成不同的隔离级别,去做一些DML操作,看看四种隔离级别的差别,体会体会脏读,不可重复读,幻读的发生造成的困扰,理解理解 可串行化(Serializable)加锁操作的利与弊(设置为可串行化时,一个事务未提交,另一个事务将无法开始)


        3. 事务ACID

1. 原子性(Atomicity)

        原子性是指事务时一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生。

2. 一致性(Consistency)

        事务必须使数据库从一个一致性状态变换到另外一个一致性状态。

3. 隔离性(Isolation)

        事务的隔离性是多个用户并发访问数据库时,数据库为每个用户开启的事务,不能被其他事务的操作数据所干扰,多个并发事务之间要相互隔离。

4. 持久性(Durability)

        持久性是指一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来即使数据库发生故障也不应该对其有任何影响。


四、最后的话

✨  原创不易,还希望各位大佬支持一下

👍  点赞,你的认可是我创作的动力!

⭐️  收藏,你的青睐是我努力的方向!

✏️  评论,你的意见是我进步的财富!

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注