MySQL入门指南5约束,索引,事务
前期回顾:
目录
一、约束
约束用于确保数据库的数据满足特定的商业规则。在MySQL中,约束包括:NOT NULL、UNIQUE、PRIMARY KEY、FOREIGN KEY、CHECK。
1. PRINARY KEY
用于唯一的标示表行的数据,当定义主键约束后,该列不能重复。
-- 基本用法: 字段 数据类型 PRIMARY KEY;
使用细节:
1.primary key(主键)修饰的列中字段不能重复且不能为null。
2. 一张表最多只能有一个主键,可以是复合主键。
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;
![学新通 学新通](/static/whole/images/alert/data-load/load-15.gif)
![学新通 学新通](/static/whole/images/alert/data-load/load-15.gif)
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');
![学新通 学新通](/static/whole/images/alert/data-load/load-15.gif)
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);
![学新通 学新通](/static/whole/images/alert/data-load/load-15.gif)
二、索引
说起提高数据库性能,索引是最物美价廉的东西了。不用加内存,不用改程序,不用调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)
持久性是指一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来即使数据库发生故障也不应该对其有任何影响。
四、最后的话
✨ 原创不易,还希望各位大佬支持一下
👍 点赞,你的认可是我创作的动力!
⭐️ 收藏,你的青睐是我努力的方向!
✏️ 评论,你的意见是我进步的财富!
这篇好文章是转载于:学新通技术网
- 版权申明: 本站部分内容来自互联网,仅供学习及演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系,请提供相关证据及您的身份证明,我们将在收到邮件后48小时内删除。
- 本站站名: 学新通技术网
- 本文地址: /boutique/detail/tanhihbfeg
-
photoshop保存的图片太大微信发不了怎么办
PHP中文网 06-15 -
photoshop扩展功能面板显示灰色怎么办
PHP中文网 06-14 -
word里面弄一个表格后上面的标题会跑到下面怎么办
PHP中文网 06-20 -
《学习通》视频自动暂停处理方法
HelloWorld317 07-05 -
TikTok加速器哪个好免费的TK加速器推荐
TK小达人 10-01 -
Android 11 保存文件到外部存储,并分享文件
Luke 10-12 -
excel下划线不显示怎么办
PHP中文网 06-23 -
微信公众号没有声音提示怎么办
PHP中文网 03-31 -
excel图片置于文字下方的方法
PHP中文网 06-27 -
微信运动停用后别人还能看到步数吗
PHP中文网 07-22