MySQL约束/存储引擎/事务/索引/执行计划/视图/DBA/三范式（四）

最新推荐文章于 2024-10-05 14:16:38 发布

bengbeng_biu

最新推荐文章于 2024-10-05 14:16:38 发布

阅读量452

点赞数

分类专栏： MySQL 文章标签：数据库 mysql

本文链接：https://blog.csdn.net/weixin_46718393/article/details/108235304

版权

MySQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

文章目录

一、约束（Constraint）

在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了
保证表中数据的合法性、有效性、完整性。

常见的约束	详情
非空约束（not null）	约束的字段不能为NULL
唯一约束（unique）	约束的字段不能重复
主键约束（primary key）	约束的字段既不能为NULL，也不能重复（简称PK）
外键约束（foreign key）	…（简称FK）
检擦约束（check）	注意Oracle数据库有check约束，但是mysql没有，目前mysql不支持该约束

二、非空约束 not null

drop table if exists t_user;
create table t_table(
    id int,
    username varchar(255) not null,
    password varchar(255)
);
insert int t_user(id.password) values(1,'123');
ERROR 1364 (HYOOO): Field 'username' dosen't hava a default value
insert into t_table(id,username,password) values(1,'lisi','123');

三、唯一性约束（unique）

唯一约束修饰的字段具有唯一性，不能重复。但可以为NULL
案例：给一列添加unique（列级约束）

drop table if exists t_user;
create table t_user(
    id int,
    username varchar(255) unique
);
insert into t_user values(1,'zhangsan');
insert into t_user values(1,'zhangsan');
ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'username'
insert into t_user(id) values(2);

案例：给两个或者多个列添加unique（表级约束）

drop table t_user;
create table t_user(
    id int,
    usercode varchar(255),
    username varchar(255),
    unique(usercode,username)
);
insert into t_user values(1,'2017441414','zhangsan');
insert into t_user values(2,'2017441418','zhangsan');
insert into t_user values(3,'2017441418','lisi');
select * from t_user;

注意：not null约束只有列级约束。没有表级约束。

四、主键约束

怎么给一张表添加主键约束呢？（列级约束）

drop table if exists t_user;
create table t_user(
    id int primary key,
    username varchar(255),
    email varchar(255)
);
insert into t_user(id,username,email) values(1,'zs','zs@123.com');
insert into t_user(id,username,email) values(2,'ls','ls@123.com');
insert into t_user(id,username,email) values(3,'ww','ww@123.com');
select * from t_user;

在这里插入图片描述

insert into t_user(id,username,email) values(1,'jack','jack@123.com');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

insert into t_user(id,username,email) values('jack','jack@123.com');
ERROR 1136 (21S01): Column count doesn't match value count at row 1

根据以上的测试得出： id是主键，因为添加了主键约束，主键字段中的数据不能为NULL，也不能重复
**主键的特点：**不能为NULL，也不能重复。

主键相关的术语？
主键约束：primary key
主键字段：id字段添加primary key之后，id叫做主键字段
主键值：id字段中的每一个值都是主键值
主键有什么作用？
表的设计三范式中要求，第一范式就要求任何一张表都应该有主键。
主键的作用：主键值是这行记录在这张表当中的唯一标识
主键的分类？
根据主键字段的字段数量来划分：
单一主键（推荐的，常用的）
复合主键（多个字段联合起来添加一个主键约束）（复合主键不建议使用，因为复合主键违背三范式。）
根据主键性质来划分：
自然主键： 主键值最好就是一个和业务没有任何关系的自然数。
业务主键： 主键值和系统的业务挂钩，例如：拿着银行卡的卡号做主键，拿着身份证号码作为主键。（不推荐常用）最好不要拿着和业务挂钩的字段作为主键。因为以后的业务一旦发生改变的时候，主键值可能也需要发生变化，但有的时候没有办法变化，因为变化可能会导致主键值重复。

主键列支持自增长特性，只需在主键约束前加上auto_increment关键字即可

一张表的主键约束只能有1个。
使用表级约束方式定义主键：（表级约束）

create table t_user(
    id int,
    username varchar(255),
    primary key(id)
);

mysql提供主键值自增（pirmary key auto_increment）：

drop table if exists t_user;
create table t_user(
    id int primary key auto_increment,
    username varchar(255)
);

insert into t_user(username) values('a');
insert into t_user(username) values('b');
insert into t_user(username) values('c');
select * from t_user;

//primary key auto_increment 主键值自动维护一个自增的数字，从1开始，以1递增。
在这里插入图片描述
提示：
Oracle当中也提供一个自增机制，叫做：序列（sequence）对象

五、外键约束

关于外键约束的相关术语：
外键约束：foreign key
外键字段：添加有外键约束的字段
外键值：外键字段中的每一个值
业务背景：
请设计数据库表，用来维护学生和班级的信息？
将以上表的建表语句写出来：
t_student中的classno字段引用t_class表中的cno字段，此时t_student表叫做子表。t_class表叫做父表。
顺序要求：

删除数据的时候，先删除子表，再删除父表。
添加数据的时候，先添加父表，再添加子表。
创建表的时候，先创建父表，再创建子表。
删除表的时候，先删除子表，再删除父表。

create table t_class(
    cno int primary key,
    cname varchar(255)
);
//插入数据
insert into t_class(cno,cname) values(101,'xxxxxx');
insert into t_class(cno,cname) values(102,'xxxxxx');
//创建子表
create table t_student(
    sno int primary key auto_increment,
    sname varchar(255),
    classno int,
    foreign key(classno) references t_class(cno)
);
//插如学生新信息
insert into t_student(sname,classno) values('zs',101);
insert into t_student(sname,classno) values('ls',102);
insert into t_student(sname,classno) values('zw',103);
//错误信息
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails 
(`cqnb`.`t_student`, CONSTRAINT `t_student_ibfk_1` FOREIGN K//EY (`classno`) REFERENCES `t_class` (`cno`))

外键值可以NULL
外键字段引用其他表的某个字段的时候，被引用的字段必须是主键嘛？
注意：被引用的字段不一定是主键，但至少具有unique约束。

六、存储引擎

完整建表语句

CREATE TABLE `t_x` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

注意：在MySQL当中，凡是标识符是可以使用飘号(`)括起来的。最好别用，不通用。

建表的时候可以指定存储引擎，也可以指定字符集。

mysql默认使用的存储引擎是InnoDB方式。
默认采用的字符集是UTF8
常见的存储引擎
在这里插入图片描述

七、事务（Transaction）

一个事务是一个完整的业务逻辑单元，不可分

和事务相关的语句只有：DML语句。（insert delete update）
这三个语句都是和数据库表当中的“数据”相关的。
事务的存在是为了保证数据的完整性，安全性。

事物的特点？
事务包括四大特性：ACID
A：原子性 ——事务是最小的工作单元，不可再分。
B：一致性 ——事务必须保证多条DML语句同时成功或者失败。
I：隔离性 ——事务A与事务B之间具有隔离。
D：持久性 ——持久性说的是最终数据必须持久化到硬盘文件中，事务才算成功的结束。

关于事物之间的隔离性
事务隔离性存在隔离级别，理论上隔离级别包括4个：

第一级别：读未提交（read uncommitted）
对方事务还没有提交，我们当前事务可以读取到对方提交的数据。
读未提交存在脏读（Dirty Read）现象：表示读到了脏的数据。
第二级别：读已提交（read commintted）
对方事务提交之后的数据我方可以读取到。
读已提交存在的问题是：不可重复读。
第三级别：可重复读（repeatable read）
这种隔离级别解决了：不可重复都问题。
这种隔离级别存在的问题是：读取到的数据是幻想。
第四级别：序列化读/串行化读
解决了所有问题。
效率低。需要事务排队。

oracle数据库默认的隔离级别是：读已提交。
mysql数据库默认的隔离级别是：可重复读。

演示事务
mysql事务默认情况下是自动提交的。（什么是自动提交？
只要执行任意一条DML语句则提交一次。）
start transaction // 关闭自动提交

八、索引

索引就相当于一本书的目录，通过目录可以快速的找到对应的资源。
在数据库方面，查询一张表的时候有两种检索方式：

第一种方式：全表扫描
第二种方式：根据索引检索（效率很高）

索引最根本的原理是缩小了扫描的范围。
索引虽然可以提高检索效率，但是不能随意的添加索引，因为索引也是数据库当中的对象，也需要数据库不断的维护。是有维护成本的。比如，表中的数据经常被修改这样就不适合添加，因为数据一旦修改，索引需要重新排序，进行维护。
怎么创建索引对象？怎么删除索引对象？
//创建索引对象：
create index 索引名称 on 表名（字段名）;
//删除索引对象：
drop index 索引名称 on 表名;
什么时候考虑给字段添加索引？

数据量庞大。（根据客户的需求，根据线上的环境）
该字段很少的DML操作。（因为字段进行修改操作，索引也需要维护）
该字段经常出现正在where子句中。（经常根据哪个字段查询）
注意：主键和具有unique约束的字段自动会添加索引。
根据主键查询效率

索引底层采用的数据结构是：B + Tree

索引的实现原理？
通过B Tree缩小扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的“物理地址”，最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址得表中的数据，效率是最高的
索引的分类
单一索引：给单个字段添加索引
复合索引：给多个字段联合起来添加1个索引
主键索引：主键上会自动添加索引
唯一索引：有unique约束的字段上会自动添加索引
索引什么时候失效？

select ename from emp where ename like '%A%';

模糊查询的时候，第一个通配符使用的是%，这个时候索引是失效的。

九、执行计划（explain）

在这里插入图片描述

十、什么是视图？（view）

站在不同的角度去看到数据。（同一张表的数据，通过不同的角度去看待）
怎么创建视图？怎么删除视图？

create view myview as select empnp,ename from emp;
drop view myview;

注意： 只有DQL语句才能以视图对象的方式创建出来。

对视图进行增删改查，会影响到原表数据。（通过视图影响原表数据的，不是直接操作的原表）
可以对视图进行CRUD操作。

视图的作用
视图可以隐藏表的实现细节。保密级别较高的系统，数据库只对外提供相关的视图，
java程序员只对视图对象进行CRUD。
提高了查询语句复用性，避免了在多处地方重复进行查询语句开发行为

十一、DBA命令

将数据库当中的数据导出
在windows的dos命令窗口中执行：（导出整个库）

mysqldump cqnb>D:\cqnb.sql -uroot -p0320

在windows的dos命令窗口中执行：（导出指定数据库当中的指定表）

mysqldump cqnb emp>D:\cqnb.sql -uroot -p0320

导入数据

create database cqnb;
use cqnb;
source D:\cqnb.sql

十二、数据库设计三范式

设计表的依据。按照这个三个范式设计的表不会出现数据冗余。

三范式都有哪些？

第一范式：任何一张表都应有主键，并且每一个字段原子性不可再分。

第二范式：建立在第一范式的基础之上，所有非主键字段完全依赖主键，不能产生部分依赖。

多对多？三张表，关系表两个外键。
t_student学生表

第三范式：建立在第二范式的基础之上，所有非主键字段直接依赖主键，不能产生传递依赖。

一对多？两张表，多的表加外键。

提醒： 在实际的开发中，以满足客户的需求为主，有时候会拿冗余换执行速度。