MySQL进阶篇

最新推荐文章于 2023-09-09 21:23:10 发布

yelvens

最新推荐文章于 2023-09-09 21:23:10 发布

阅读量274

点赞数

分类专栏：数据库存储过程

本文链接：https://blog.csdn.net/qq_42570601/article/details/100081844

版权

数据库存储过程专栏收录该内容

7 篇文章 0 订阅

订阅专栏

文章目录

1、超键、候选键、主键、外键

超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。
候选键：是最小超键，即没有冗余元素的超键。
主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。
外键：在一个表中存在的另一个表的主键称此表的外键。

2、数据库的三个范式

第一范式（1NF）：
数据库表中的字段都是单一属性的，不可再分。这个单一属性由基本类型构成，包括整型、实数、字符型、逻辑型、日期型等。

字段是最小的单元，不可再分
如：学生信息组成学生信息表，有年龄、性别、学号等信息组成。这些字段都不可再分，所以它是满足第一范式的

第二范式（2NF）：数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖（部分函数依赖指的是存在组合关键字中的某些字段决定非关键字段的情况），也即所有非关键字段都完全依赖于任意一组候选关键字。

满足第一范式,表中的字段必须完全依赖于全部主键而非部分主键。
如：学号为1024的同学，姓名为二狗子，年龄是22岁。姓名和年龄字段都依赖着学号主键。

第三范式（3NF）：在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。所谓传递函数依赖，指的是如果存在"A
→ B → C"的决定关系，则C传递函数依赖于A。因此，满足第三范式的数据库表应该不存在如下依赖关系：关键字段 → 非关键字段x →
非关键字段y

满足第二范式，非主键外的所有字段必须互不依赖
如：我们大学分了很多系（中文系、英语系、计算机系……），这个系别管理表信息有以下字段组成：系编号，系主任，系简介，系架构。那我们能不能在学生信息表添加系编号，系主任，系简介，系架构字段呢？不行的，因为这样就冗余了，非主键外的字段形成了依赖关系(依赖到学生信息表了)！正确的做法是：学生表就只能增加一个系编号字段。

3、视图

视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，试图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。

3.1 说明：

1）视图建立在已有表的基础上, 视图赖以建立的这些表称为基表
2）向视图提供数据内容的语句为 SELECT 语句,可以将视图理解为存储起来的 SELECT 语句
3）视图没有存储真正的数据，真正的数据还是存储在基表中
4）程序员虽然操作的是视图，但最终视图还会转成操作基表
5）值得注意的是：使用视图可以让我们专注与逻辑，简化SQL代码，但不提高查询效率

3.2 如下两种场景一般会使用到视图：

1）不希望访问者获取整个表的信息，只暴露部分字段给访问者，所以就建一个虚表，就是视图。
2）查询的数据来源于不同的表，而查询者希望以统一的方式查询，这样也可以建立一个视图，把多个表查询结果联合起来，查询者只需要直接从视图中获取数据，不必考虑数据来源于不同表所带来的差异。

3.3 视图应用

//创建试图
CREATE VIEW student_view AS SELECT
s.sno,
s.sname 
FROM
	student_ AS s;
	
//查询试图中的信息
SELECT * FROM student_view;

//删除试图，用逗号分隔开，可以删除多个视图
DROP VIEW student_view;

在这里插入图片描述

4、索引

【注】将下面的知识点看完后再去看这个链接，会对索引彻底理解：

MySQL支持的索引类型（B-Tree索引、hash索引）

4.1 什么是索引

在关系数据库中，索引是对表中一列或多列的值进行排序的一种存储结构，它是表中一列或多列的值的集合，而且其中包含了对应表中记录的引用指针。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

要注意的是，索引也是表的组成部分，建立太多的索引将会影响更新和插入的速度，因为它需要同样更新每个索引文件。对于一个经常需要更新和插入的表格，就没有必要为一个很少使用的where字句单独建立索引了，对于比较小的表，排序的开销不会很大，也没有必要建立索引。

举个例子：

首先，先假设有一张表，表有10W个记录，其中有一条记录我们已知a=‘1’，如果想要拿到对应记录的话，需要的sql语句是 SELECT * FROM xxx WHERE a=‘1’.

一般情况下，对于查询语句，在没有建立索引的时候，mysql会进行全表扫描，而且不扫描完10W个记录不会停止，如果我在nickname上建立索引，那么mysql相当于只扫描nickname这一列即可，而且因为这一列已排好序，找到对应结果或结果集可以直接返回。

mysql的索引分为单列索引(全文索引，主键索引，唯一索引，普通索引)和组合索引。
单列索引:一个索引只包含一个列，一个表可以有多个单列索引。
组合索引:一个组合索引包含两个或两个以上的列，

在这里插入图片描述

4.2 索引分类：

1）单列索引：一个索引只包含一个列，一个表可以有多个单列索引
- 普通索引：最基本的索引
- 唯一索引：要求字段所有的值是唯一的，这一点和主键索引一样，但是允许有空值。
- 主键索引：为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的，并且不能为空
- 全文索引：假设字段的数据类型是长文本，文本字段上(text等)建立了普通索引，我们需要查找关键字的话，那么其条件只能是where column like ‘%xxxx%’ ，但是，这样做就会让索引失效，这时就需要全文索引了
2）组合索引：一个组合索引包含两个或两个以上的列组合索引
假设字段a，b都有索引，我们的查询条件是a=1，b=2查询过程是mysql会先挑选出符合a=1的结果集，再在这些结果集中挑选b=2的结果集，但是mysql并不会在查询a，b时都用到索引，只会用其中一个，这和我们的预期不一样，所以，我们要使用组合索引

4.3 索引的基本使用：

1）可以在创建表的时候添加索引

//普通索引
CREATE TABLE school (
	NAME VARCHAR ( 8 ) NOT NULL,
	sid INT PRIMARY KEY auto_increment NOT NULL,
	age INT NOT NULL,
	sex enum ( 'F', 'M' ),
INDEX IndexName( sid, NAME ) //索引在这里
);

//唯一索引
UNIQUE INDEX IndexName(`字段名`(length)) 

//全文索引
FULLTEXT INDEX IndexName(`字段名`(length)) 

//组合索引
INDEX IndexName(`字段名`(length)，`字段名`(length)，........)

2）建表后创建索引

//创建普通索引
CREATE INDEX student_index ON student_ ( sno );
或
ALTER TABLE student_ ADD INDEX student_index ( sno, sname );

//唯一索引
CREATE UNIQUE  INDEX IndexName ON `TableName`(`字段名`(length)) 
或
ALTER TABLE TableName ADD UNIQUE  INDEX IndexName(`字段名`(length)）

//创建全文索引
CREATE FULLTEXT  INDEX IndexName ON `TableName`(`字段名`(length)) 
或
ALTER TABLE TableName ADD FULLTEXT  INDEX IndexName(`字段名`(length)）

//组合索引
CREATE INDEX IndexName ON `TableName`(`字段名`(length)，`字段名`(length)，........) 
或
ALTER TABLE TableName ADD INDEX IndexName(`字段名`(length)，`字段名`(length)，........)

3）显示与删除一个索引

//显示索引
SHOW INDEX 
FROM
	student_;

//删除索引
DROP INDEX student_index ON student_;

4.4 索引失效的情况

1）如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)，要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
2）使用查询的时候遵循mysql组合索引的"最左前缀"规则：他会按照你建立的索引的最左边的字段来搜索，如果没有匹配到最左边的，就算你为另一个字段建立了索引，他也不会来搜索，也无法匹配到

假设现在有组合索引（a，b，c），查询语句就只能是a=1 或 a=1and b=1 或 a=1 and b=1 and c=1。
这里有两点需要注意
①a=1 and b=1和b=1 and a=1一样，没有区别，都会使用索引
②组合索引（a，b，c）的最左前缀是a；组合索引（c，b，a）的最左前缀是c，最左前缀和表字段顺序无关

3）like查询以%开头
在组合索引中，如果where查询条件中某个列使用了范围查询（不管%在哪），则其右边的所有列都无法使用索引优化查询
4）.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
5）如果mysql估计使用全表扫描要比使用索引快,则不使用索引
6）索引列不能是表达式的一部分，也不能作为函数的参数，否则无法使用索引查询。下面是例子：

SELECT * FROM user_test WHERE user_name = concat(user_name, 'fei');

4.5 创建索引可以大大提高系统的性能（优点）

第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

4.6 增加索引也有许多不利的方面（缺点）

第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
索引是建立在数据库表中的某些列的上面。在创建索引的时候，应该考虑在哪些列上可以创建索引，在哪些列上不能创建索引。

4.7一般来说，应该在这些列上创建索引：

1）在经常需要搜索的列上，可以加快搜索的速度；
2）在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
3）在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
4）在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
5）在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
6）在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

4.8 同样，对于有些列不应该创建索引

第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
第三，对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

5、存储过程

存储过程：是一组预先编译好的SQL语句。将其放在服务器上，有用户通过指定存储过程的名字来执行它。存储过程可以作为一个独立的数据库对象，也可以作为一个单元被用户的应用程序调用。存储过程可以接受和输出参数，返回执行存储过程的状态值，还可以嵌套调用。

通俗点说就是，把复杂的操作语句，封装起来（有点函数的意思），用户用的时候只要传相应的参数，就能得到想要的结果。
看看这个博主写的，非常棒：Mysql存储过程

6、事务

就是被绑定在一起作为一个逻辑工作单元的 SQL语句分组，如果任何一个语句操作失败那么整个操作就被失败，以后操作就会回滚到操作前状态，或者是上有个节点。为了确保要么执行，要么不执行，就可以使用事务。要将有组语句作为事务考虑，就需要通过 ACID 测试，即原子性，一致性，隔离性和持久性。

原子性：整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性：在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。
隔离性：隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。
持久性：在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

           //开启事务,对数据的操作就不会立即生效。
            connection.setAutoCommit(false);

            //A账户减去500块
            String sql = "UPDATE a SET money=money-500 ";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.executeUpdate();

            //在转账过程中出现问题
            int a = 3 / 0;

            //B账户多500块
            String sql2 = "UPDATE b SET money=money+500";
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.executeUpdate();

            //如果程序能执行到这里，没有抛出异常，我们就提交数据
            connection.commit();

            //关闭事务【自动提交】
            connection.setAutoCommit(true);


        } catch (SQLException e) {
            try {
                //如果出现了异常，就会进到这里来，我们就把事务回滚【将数据变成原来那样】
                connection.rollback();

                //关闭事务【自动提交】
                connection.setAutoCommit(true);
            } catch (SQLException e1) {
                e1.printStackTrace();
            }

7、锁

确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性，乐观锁和悲观锁是并发控制主要采用的技术手段。

表级锁：每次操作锁住整张表。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；（MyIASM引擎）
行级锁：每次操作锁住一行数据。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；（Innodb引擎）
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作

主要用于数据争用激烈的环境，以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本的环境中。

在查询完数据的时候就把事务锁起来，直到提交事务

实现方式：使用数据库中的锁机制
乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。

在修改数据的时候把事务锁起来，通过version的方式来进行锁定

实现方式：使用version版本或者时间戳
看看大佬对乐观锁和悲观锁的解释：深入理解乐观锁与悲观锁

8、触发器

触发器（trigger）特殊的存储过程，是用户定义在关系表上的一类由事件驱动的特殊过程。一单定义，触发器将被保存在数据库服务器中。任何用户对表的增、删、改操作均由服务器自动激活相应的触发器，在关系数据库管理系统核心层进行集中的完整性控制。触发器类似于约束，但是比约束更加灵活，可以实施更为复杂的检查和操作，具有更精细和更强大的数据控制能力。

存储过程和触发器二者是有很大的联系的，触发器是一个隐藏的存储过程，因为它不需要参数，不需要显示调用，往往在你不知情的情况下已经做了很多操作。
可以看看这个博主写的，基本上就能理解了： MySQL 触发器

9、SQL 约束有哪几种？

NOT NULL: 用于控制字段的内容一定不能为空（NULL）。
UNIQUE: 控件字段内容不能重复，一个表允许有多个 Unique 约束。
PRIMARY KEY: 主键，也是用于控件字段内容不能重复，但它在一个表只允许出现一个。
FOREIGN KEY: 外键，用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
CHECK: 用于控制字段的值范围。

10、drop,delete与truncate的区别

drop直接删掉表。
truncate删除表中数据，再插入时自增长id又从1开始。
delete删除表中数据，可以加where字句。

（1） DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

（2）表和索引所占空间。当表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小，而DELETE操作不会减少表或索引所占用的空间。drop语句将表所占用的空间全释放掉。

（3）一般而言，drop > truncate > delete

（4）应用范围。TRUNCATE 只能对TABLE；DELETE可以是table和view

（5） TRUNCATE 和DELETE只删除数据，而DROP则删除整个表（结构和数据）。

（6） truncate与不带where的delete ：只删除数据，而不删除表的结构（定义）drop语句将删除表的结构被依赖的约束（constrain),触发器（trigger)索引（index);依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。

（7） delete语句为DML（data maintain Language),这个操作会被放到 rollback segment中,事务提交后才生效。如果有相应的 tigger,执行的时候将被触发。

（8） truncate、drop是DLL（data define language),操作立即生效，原数据不放到 rollback segment中，不能回滚。

（9）在没有备份情况下，谨慎使用 drop 与 truncate。要删除部分数据行采用delete且注意结合where来约束影响范围。回滚段要足够大。要删除表用drop;若想保留表而将表中数据删除，如果于事务无关，用truncate即可实现。如果和事务有关，或老师想触发trigger,还是用delete。

（10） Truncate table 表名速度快,而且效率高,因为:
truncate table 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

（11） TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。

（12）对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。

11、MySql存储引擎

Innodb引擎，Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。
MyIASM引擎(原本Mysql的默认引擎),不提供事务的支持，也不支持行级锁和外键。
MEMORY引擎：所有的数据都在内存中，数据的处理速度快，但是安全性不高。

同一个数据库也可以使用多种存储引擎的表。如果一个表修改要求比较高的事务处理，可以选择InnoDB。这个数据库中可以将查询要求比较高的表选择MyISAM存储。如果该数据库需要一个用于查询的临时表，可以选择MEMORY存储引擎。

yelvens

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL进阶篇

1、超键、候选键、主键、外键超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。候选键：是最小超键，即没有冗余元素的超键。主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。外键：在一个表中存...
复制链接

扫一扫