1200字范文 > [数据库03]-约束（唯一性-主键-外键/存储引擎/事务/索引/视图/DBA命令/数据库设计三范式

[数据库03]-约束（唯一性-主键-外键/存储引擎/事务/索引/视图/DBA命令/数据库设计三范式

时间：2022-02-22 09:02:11

[数据库03]-约束（唯一性-主键-外键）/存储引擎/事务/索引/视图/DBA命令/数据库设计三范式

一、约束1.1 唯一性约束(unique)1.2 主键约束1.3 外键约束二、存储引擎2.1 完整的建表语句2.2 什么是存储引擎呢？2.3 查看当前mysql支持的存储引擎2.4 常见的存储引擎三、事务(Transaction)3.1 什么是事务？3.2 和事务相关的语句3.3 假设所有的业务都能使用1条DML语句搞定，还需要事务机制吗？3.4 事务的特性3.5 关于事务之间的隔离性3.6 演示事务四、索引4.1 索引的定义和作用4.2 怎么创建索引对象？怎么删除索引对象？4.3 什么时候考虑给字段添加索引？4.4 注意：主键具有unique约束的字段会自动添加索引4.5 查看sql语句的执行计划4.6 索引底层采用的数据结构4.7 索引的实现原理4.8 索引的分类4.9 索引什么时候失效五、视图(view)5.1 什么是视图？5.2 怎么创建视图？怎么删除视图？5.3 操作对表数据影响5.4 面向视图操作5.5 试图的作用六、DBA命令6.1 在数据库当中的数据导出6.2 导入数据七、数据库设计三范式(重点内容，面试经常会问)7.1 什么是设计范式？7.2 三范式都是哪些？7.3 一对一怎么设计

一、约束

1.1 唯一性约束(unique)

* 唯一性约束修饰的字段具有唯一性，不能重复。但可以为null。* 案例：给某一列添加uniquedrop 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(2,'zhangsan'); //出现编译错误，唯一性约束，该字段与上一行字段重复，但可以为null！ERROR 1062 (23000) : Duplicate entry 'zhangsan' for key 'username'insert into t_user(id) values(2);insert into t_user(id) values(3);insert into t_user(id) values(4);*案例：给两个列或者多个列添加uniquedrop table if exists t_user;create table t_user(id int,usercode varchar(255),username varchar(255),unique(usercode,username) //多个字段联合起来添加一个约束unique 【表级约束】);insert into t_user values(1,'111','zs');insert into t_user values(2,'111','ls');insert into t_user values(3,'222','zs');select * from t_user;insert into t_user values(4,'111','zs'); //出现编译错误！ERROR 1062 (23000) : Duplicate entry '111-zs' for key 'usercode'drop table if exists t_user;create table t_suer(id int,usercode varchar(255) unique,username varchar(255) unique);insert into t_user values(1,'111','zs');insert into t_user values(2,'111','ls');ERROR 1062 (23000) : Duplicate entry '111' for key 'usercode'* 注意：not null约束只有列级约束，没有表级约束。

1.2 主键约束

* 怎么给一张表添加主键约束呢？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@');insert into t_user(id,username,email) values(2,'ls','ls@');insert into t_user(id,username,email) values(3,'ww','ww@');select * from t_user;+-----------------------------+| id | username | email |+-----------------------------+| 1 | zs | zs@ || 2 | ls | ls@ || 3 | ww | ww@ |+----+----------+-------------+insert into t_user(id,username,email) values(1,'jack','jack@'); //出现编译错误，主键约束，不能为null也不能重复！ERROR 1364 (HY000) : Field 'id' doesn't have a default value根据以上的测试得出：id是主键，因为添加了主键约束，主键字段中的数据不能为null，也不能重复。主键的特点：不能为null，也不能重复。* 主键相关的术语？主键约束：primary key主键字段 : id字段添加primary key之后，id叫做主键字段主键值：id字段中的每一个值都是主键值。* 主键有什么作用？根据主键字段的字段数量来划分：单一主键 (推荐的，常用的。)复合主键(多个字段联合起来添加一个主键约束) (复合主键不建议使用，因为复合主键违背三范式。)根据主键性质来划分：自然主键：主键值最好就是一个和业务没有任何关系的自然数。(这种方式是推荐的)业务主键 : 主键值和系统的业务挂钩，例如：拿着银行卡的卡号做主键、拿着身份证号做为主键。(不推荐使用)最好不要拿着和业务挂钩的字段做为主键。因为以后的业务一旦发生改变的时候，主键也可能需要随着发生变化，但有的时候没有办法变化，因为变化可能会导致主键重复。* 一张表的主键约束只能有1个。(必须记住)* 使用表级约束方式定义主键：drop table if exists t_user;create table t_user(id int,username varchar(255),primary key(id));insert into t_user(id,username) values(1,'zs');insert into t_user(id,username) values(2,'ls');insert into t_user(id,username) values(3,'ws');insert into t_user(id,username) values(4,'cs');select * from t_user;insert into t_user(id,username) values(3,'cx'); //出现编译错误！ERROR 1062 (23000) : Duplicate entry '4' for key 'PRIMARY'以下内容是演示一下复合主键，不需要掌握：drop table if exists t_user;create table t_user(id int,username varchar(255),password varchar(255),primary key(id,username));insert ......* mysql提供主键值自增：(非常重要。)drop table if exists t_user;create table t_user(id int primary key auto_increment, //id字段自动维护一个自增的数字，从1开始，以1递增。username varchar(255));insert into t_user(username) values('a'); insert into t_user(username) values('b');insert into t_user(username) values('c');insert into t_user(username) values('d');insert into t_user(username) values('e');insert into t_user(username) values('f');select * from t_user;提示：Oracle当中也提供了一个自增机制，叫做：序列(sequence)对象。

1.3 外键约束

* 关于外键约束的相关术语：外键约束：foreign key外键字段：添加有外键约束的字段外键值：外键字段中的每一个值。* 业务背景：请设计数据库表，用来维护学生和班级的信息？第一种方案：一张表存储所有数据no(pk)nameclassno classname-----------------------------------------------------------1zs1 101河南省平顶山市舞钢市垭口一高高三1班 2zs2 101河南省平顶山市舞钢市垭口一高高三1班 3zs3 102河南省平顶山市舞钢市垭口一高高三2班 4zs4 102河南省平顶山市舞钢市垭口一高高三2班5zs5 102河南省平顶山市舞钢市垭口一高高三2班缺点：冗余。【不推荐】第二种方案：两张表(班级表和学生表)t_class 班级表cno(pk) cname-------------------------------------------------------------101 河南省平顶山市舞钢市垭口一高高三1班 102 河南省平顶山市舞钢市垭口一高高三2班 t_student 学生表sno(pk) snameclassno(该字段添加外键约束fk)-----------------------------------------------------------1 zs1 1012 zs2 1013 zs3 1024 zs4 1025 zs5 102* 将以上表的建表语句写出来：t_student中的classno字段引用t_class表中的cno字段，此时t_student表叫做子表。t_class表叫做父表。删除数据的时候，先删除子表，再删除父表。添加数据的时候，先添加父表，再添加子表。创建表的时候，先创建父表，再创建子表。删除表的时候，先删除子表，再删除父表。drop table if exists t_student;drop table if exists t_class;create table t_class(cno int,cname varchar(255),primary key(cno));create table t_student(sno int,sname varchar(255),classno int,primary key(sno),foreign key(classno) references t_class(cno));insert into t_class values(101,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');insert into t_class values(102,'yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy');insert into t_student values(1,'zs1',101);insert into t_student values(2,'zs2',101);insert into t_student values(3,'zs3',102);insert into t_student values(4,'zs4',102);insert into t_student values(5,'zs5',102);insert into t_student values(6,'zs6',102);select * from t_class;select * from t_student;insert into t_student values(7,'lisi',103); //编译错误，引用的103，父表中没有该字段！ERROR 1452 (23000) : Cannot add or update a child row :aforeign key constraint fails (bjpowernode INT YT......)* 外键值可以为NULL？外键可以为null。* 外键字段引用其他表的某个字段的时候，被引用的字段必须是主键吗？注意：被引用的字段不一定是主键，但至少是具有unique约束，具有唯一性，不可重复！

二、存储引擎

2.1 完整的建表语句

CREATE TABLE `t_x` (`id` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8; 注意：在MySQL当中，凡是标识符使用飘号括起来的。最好别用，不通用。建表的时候可以指定存储引擎，也可以指定字符集。mysql默认使用的存储引擎是InnoDB方式。默认采用的字符集是UTF-8。

2.2 什么是存储引擎呢？

存储引擎这个名字只有在mysql中存在。(Oracle中有对应的机制，但不叫做存储引擎。Oracle中没有特殊的名字，就是"表的存储方式")mysql支持很多存储引擎，每个存储引擎都对应了一种不同的存储方式。每一个存储引擎都有自己的优缺点，需要在合适的时机选择合适的存储引擎。

2.3 查看当前mysql支持的存储引擎

show engines \Gmysql 5.5.36版本支持的光速引擎有9个：*************************** 1. row ***************************Engine: FEDERATEDSupport: NOComment: Federated MySQL storage engineTransactions: NULLXA: NULLSavepoints: NULL*************************** 2. row ***************************Engine: MRG_MYISAMSupport: YESComment: Collection of identical MyISAM tablesTransactions: NOXA: NOSavepoints: NO*************************** 3. row ***************************Engine: MyISAMSupport: YESComment: MyISAM storage engineTransactions: NOXA: NOSavepoints: NO*************************** 4. row ***************************Engine: BLACKHOLESupport: YESComment: /dev/null storage engine (anything you write to it disappears)Transactions: NOXA: NOSavepoints: NO*************************** 5. row ***************************Engine: CSVSupport: YESComment: CSV storage engineTransactions: NOXA: NOSavepoints: NO*************************** 6. row ***************************Engine: MEMORYSupport: YESComment: Hash based, stored in memory, useful for temporary tablesTransactions: NOXA: NOSavepoints: NO*************************** 7. row ***************************Engine: ARCHIVESupport: YESComment: Archive storage engineTransactions: NOXA: NOSavepoints: NO*************************** 8. row ***************************Engine: InnoDBSupport: DEFAULTComment: Supports transactions, row-level locking, and foreign keysTransactions: YESXA: YESSavepoints: YES*************************** 9. row ***************************Engine: PERFORMANCE_SCHEMASupport: YESComment: Performance SchemaTransactions: NOXA: NOSavepoints: NO

2.4 常见的存储引擎

Engine: MyISAMSupport: YESComment: MyISAM storage engineTransactions: NOXA: NOSavepoints: NOMyISAM这种存储引擎不支持事务。MyISAM是mysql最常用的存储引擎，但是这种存储引擎不是默认的。MyISAM采用三个文件组织一个表：xxx.frm(存储格式的文件)xxx.MYD(存储表中数据的文件)xxx.MYI(存储表中索引的文件)优点：可被压缩，节省存储空间。并且可以转换为只读表，提高检索效率。缺点:不支持事务。--------------------------------------------------------------------------Engine: InnoDBSupport: DEFAULTComment: Supports transactions, row-level locking, and foreign keysTransactions: YESXA: YESSavepoints: YES优点：支持事务、行级锁、外键等。这种存储引擎数据的安全得到保障。表的结构存储在xxx.frm文件中数据存储在tablespace这样的表空间中(逻辑概念)，无法被压缩，无法转换成只读。这种InnoDB存储引擎在MySQL数据库崩溃之后提供自动恢复机制。InoDB支持级联删除和级联更新。----------------------------------------------------------------------------------Engine: MEMORYSupport: YESComment: Hash based, stored in memory, useful for temporary tablesTransactions: NOXA: NOSavepoints: NO缺点：不支持事务。数据容易丢失。因为所有数据和索引都是存储在内存当中的。优点：查询速度最快。以前叫做HEPA引擎。

三、事务(Transaction)

3.1 什么是事务？

一个事务是一个完整的业务逻辑单元，不可再分。比如：银行账户，从A账户向B账户转账10000元，需要执行两条update语句。update t_act set balance = balance - 10000 where actno = 'act-001';update t_act set balance = balance + 10000 where actno = 'act-002';以上两条DML语句必须同时成功，或者同时失败，不允许出现一条成功，一条失败。想要保证以上的两条DML语句同时成功或者同时失败，那么就要使用数据库的"事务机制"。

3.2 和事务相关的语句

只有：DML语句。(insert delete update)为什么？因为他们这三个语句都是和数据库表当中的"数据"相关的。事务的存在是为了保证数据的完整性，安全性。

3.3 假设所有的业务都能使用1条DML语句搞定，还需要事务机制吗？

不需要事务机制。但实际情况不是这样的，通常一个"事儿(事务【业务】)"需要多条DML语句共同联合完成。

3.4 事务的特性

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

3.5 关于事务之间的隔离性

事务隔离性存在隔离级别，理论上隔离级别包括4个：第一级别：读未提交(read uncommitted)对方事务还没有提交，我们当前事务可以读取到对方未提交的数据。读未提交存在脏读(Dirty Read) 现象：表示读到了脏数据。第二级别：读已提交(read committed)对方事务提交之后的数据我方可以读取到。读已提交存在的问题是：不可重复读。第三级别：可重复读(repeatable read)这种隔离级别解决了：不可重复读问题。这种隔离级别存在的问题是：读取到的数据是幻象。第四级别：序列化读/串行化读解决了所有问题。效率低，需要事务排队。Oracle数据库默认的隔离级别是：第二级别，读已提交。mysql数据库默认的隔离级别是：第三级别，可重复读。

3.6 演示事务

* mysql事务默认情况下是自动提交的。(什么是自动提交？只要执行任意一条DML语句则提交一次。)怎么关闭默认提交？start transaction;* 建表：drop table if exists t_user;create table t_user(id int primary key auto_increment,username varchar(255));* 演示：mysql中的事务是支持自动提交的，只要执行一条DML语句，则提交一次。mysql> insert into t_user(username) values('zs');Querk OK, 1 row affected (0.03 sec)mysql> select * from t_user;+----+----------+| id | username |+----+----------+| 1 | zs |+----+----------+mysql> rollback;Query OK, 0 rows affected (0.00 sec)mysql> select * from t_user;+----+----------+| id | username |+----+----------+| 1 | zs |+----+----------+* 演示：使用start transaction; 关闭自动提交机制。mysql> select * from t_user;+----+----------+| id | username |+----+----------+| 1 | zs |+----+----------+1 row in set (0.00 sec)mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> insert into t_user(username) values("lisi");Query OK, 1 row affected (0.00 sec)mysql> select * from t_user;+----+----------+| id | username |+----+----------+| 1 | zs || 2 | lisi|+----+----------+2 rows in set (0.00 sec)mysql> insert into t_user(username) values("wangwu");Query OK, 1 row affected (0.00 sec)mysql> select * from t_user;+----+----------+| id | username |+----+----------+| 1 | zs || 2 | lisi|| 3 | wangwu |+----+----------+3 rows in set (0.00 sec)mysql> rollback; //回滚Query OK, 0 rows affected (0.00 sec)mysql> select * from t_user;+----+----------+| id | username |+----+----------+| 1 | zs |+----+----------+1 row in set (0.00 sec)mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> insert into t_user(username) values("wangwu");Query OK, 1 row affected (0.00 sec)mysql> insert into t_user(username) values("object");Query OK, 1 row affected (0.00 sec)mysql> insert into t_user(username) values("joke");Query OK, 1 row affected (0.00 sec)mysql> insert into t_user(username) values("xiaozhaozhao");Query OK, 1 row affected (0.00 sec)mysql> select * from t_user;+----+--------------+| id | username|+----+--------------+| 1 | zs || 4 | wangwu || 5 | object || 6 | joke || 7 | xiaozhaozhao |+----+--------------+5 rows in set (0.00 sec)mysql> commit;Query OK, 0 rows affected (0.00 sec)mysql> select * from t_user;+----+--------------+| id | username|+----+--------------+| 1 | zs || 4 | wangwu || 5 | object || 6 | joke || 7 | xiaozhaozhao |+----+--------------+5 rows in set (0.00 sec)mysql> rollback;Query OK, 0 rows affected (0.00 sec)mysql> select * from t_user;+----+--------------+| id | username|+----+--------------+| 1 | zs || 4 | wangwu || 5 | object || 6 | joke || 7 | xiaozhaozhao |+----+--------------+5 rows in set (0.00 sec)rollback : 回滚。commit ：提交。start transaction : 关闭自动提交机制。-----------------------------------------------------------------* 演示两个事务，假如隔离级别：演示第1级别：读未提交set global transaction isolation level read uncommitted;演示第二级别；读已提交set global transaction isolation level read committed;演示第三级别:可重复读set global transaction isolation level repeatable read;*mysql远程登录：mysql -h192.168.151.18 -uroot -p444

四、索引

4.1 索引的定义和作用

索引就相当于一本书的目录，通过目录可以快速的找到对应的资源。在数据库方面，查询一张表的时候有两种检索方式：第一种方式：全表扫描第二种方式：根据索引检索(效率很高)索引为什么可以提高检索效率呢？其实最根本的原理是缩小了扫描的范围。索引虽然可以提高检索效率，但是不能随意的添加索引，因为索引也是数据库当中的对象，也需要数据库不断的维护。是有维护成本的。比如：表中的数据经常被修改，这样就不适合添加索引，因为数据一旦修改，索引需要重新排序，进行维护。添加索引是给某一个字段，或者说某些字段添加索引。select ename,sal from emp where ename = 'SMITH';当ename字段没有添加索引的时候，以上sql语句会进行全表扫描，扫描ename字段中所有的值。当ename字段添加索引的时候，以上sql语句会根据索引扫描，快速定位。

4.2 怎么创建索引对象？怎么删除索引对象？

创建索引对象：create index 索引名称 on 表名(字段名);删除索引对象：drop index 索引名称 on 表名;

4.3 什么时候考虑给字段添加索引？

* 数据量庞大。(根据客户的需求，根据线上的环境)* 该字段很少的DML操作。(因为字段进行修改操作，索引也需要维护)* 该字段经常出现在where子句中。(经常根据哪个字段维护)

4.4 注意：主键具有unique约束的字段会自动添加索引

根据主键查询效率较高，尽量根据主键检索。

4.5 查看sql语句的执行计划

4.6 索引底层采用的数据结构

B + Tree

4.7 索引的实现原理

通过B Tree缩小扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的"物理地址"，最终通过索引检索到数据之后，获取到关联的物理地址，通过物理索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率是最高的。select ename from emp where ename = 'SMITH';通过索引转换为：select ename from emp where 物理地址 = 0x123;

4.8 索引的分类

单一索引：给单个字段添加索引复合索引：给多个字段联合起来添加一个索引主键索引：主键上会自动添加索引唯一索引：有unique约束的字段会自动添加索引......

4.9 索引什么时候失效

select ename from emp where ename like ' %A% ';模糊查询的时候，第一个通配符使用的是%，这个时候索引是是失效的。

五、视图(view)

5.1 什么是视图？

站在不同的角度去看到数据。(同一张表的数据，通过不同的角度去看待)

5.2 怎么创建视图？怎么删除视图？

create view myview as select empno,ename from emp;drop view myview;注意：只有DQL语句才能以试图对象的方式创建出来。

5.3 操作对表数据影响

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

5.4 面向视图操作

mysql> select * from myview;+-------+--------+| empno | ename |+-------+--------+| 7369 | SMITH || 7499 | ALLEN || 7521 | WARD || 7566 | JONES || 7654 | MARTIN || 7698 | BLAKE || 7782 | CLARK || 7788 | SCOTT || 7839 | KING || 7844 | TURNER || 7876 | ADAMS || 7900 | JAMES || 7902 | FORD || 7934 | MILLER |+-------+--------+create table emp_bak as select * from emp;create view myview1 as select empno,ename,sal from emp_bak;update myview1 set ename = 'hehe',sal = 1 where empno 7369; //通过视图修改原表数据。delete from myview1 where empno = 7369; //通过试图删除原表数据。

5.5 试图的作用

试图可以隐藏表的实现细节。保密级别较高的系统，数据库只对外提供相关的视图，java程序员只对视图对象进行CRUD。

六、DBA命令

6.1 在数据库当中的数据导出

在windows的DOS命令窗口中执行： (导出整个库)mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p999在windows的dos命令窗口中执行：(导出数据库中指定的表)mysqldump bjpowernode emp>D:\bjpowernode.sql -uroot -p999

6.2 导入数据

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

七、数据库设计三范式(重点内容，面试经常会问)

7.1 什么是设计范式？

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

7.2 三范式都是哪些？

第一范式：任何一张表都应该有主键，并且每一个字段原子性不可再分。第二范式：建立在第一范式的基础上，所有非主键字段完全依赖主键，不能产生部份依赖。多对多？三张表，关系表两个外键。t_student学生表sno(pk) sname---------------------1 张三2 李四 3 王五t_teacher 讲师表tno(pk) tname----------------------1王老师2张老师3李老师t_student_teacher_relation 学生讲师关系表id(pk) sno(fk)tno(fk)-------------------------------------------11 3211322423531633第三范式：建立在第二范式的基础上，所有非主键字段直接依赖主键，不能产生传递依赖。一对多？两张表，多的表加外键。班级t_classcno(pk)cname--------------------------1 班级12 班级2学生t_studentsno(pk) sname classno(fk)--------------------------------------------101张11102 张22103张32104张41105张52提醒：在实际的开发中，以满足客户需求为主，有的时候会拿冗余换执行速度。

7.3 一对一怎么设计

一对一设计有两种方案：主键共享t_user_login 用户登陆表id(pk) username password----------------------------------------1 zs1232 ls456t_user_detail 用户详细信息表id(pk+fk) realnametel...----------------------------------------------------1张三 111111111122342 李四 12112523432412一对一设计有两种方案:外键唯一。t_user_login 用户登陆表id(pk) username password----------------------------------------1 zs1232 ls456t_user_detail 用户详细信息表id(pk) realnametel userid(fk+unique)----------------------------------------------------1 张三 11111111422 李四 1214324121

本内容不代表本网观点和政治立场，如有侵犯你的权益请联系我们处理。

网友评论

网友评论仅供其表达个人看法，并不表明网站立场。