MYSQL基础

目录

1.基本概念：

2.数据库、数据表、数据关系：

3.SQL分类:

4字符集

5.数据类型

6.约束

7.多表操作

8.视图：

9.备份和恢复

10.存储过程和函数

11.触发器

12.事务

13.MYSQL体系结构：

14.存储引擎

14.索引

15.锁机制

---

1.基本概念：

学习原因：数据库更加方便的对开发中的数据进行管理，之前使用IO流太过麻烦

数据库：用于存储和管理数据的仓库，存储空间很大。

使用统一的方式操作数据库-SQL

MYSQL：最流行的关系型数据库之一。免费（6版本之前）

关系型数据库是将数保存在不同的数据表中，而不是将所有数据放在一个大仓库内，而且表与表之间还可以有关联关系，这样就提高了访问速度以及提高了灵活性。

2.数据库、数据表、数据关系：

①MYSQL服务器中可以创建多个数据库

②每个数据库中可以包含多张数据表

③每个数据表中可以存储多条数据记录

④客户端可以通过数据库管理系统来操作MYSQL数据库

SQL：结构化查询语言定义了操作关系数据库的一种规则

 

3.SQL分类:

DDL：数据定义语言 操作数据库，表，列等

DML：数据操作语言 用来对数据库中表的数据进行增删改

DQL： 数据查询语言 用来查询数据库中表的记录

DCL(了解)：数据控制语言 用来定义数据库的访问权限和安全级别，及创建用户

4字符集

某个范围字符的编码规则

字符集列表

字符集名称	占用字节数
ASCII	1
gbk	1 ~ 2
utf8	1 ~ 3
utf8mb4	1 ~ 4

比如utf8字符集对于所有中文汉字采用3个字节来表示（编码），所以我们称utf8为一种字符集。

这里的范围就是指所有的中文汉字
编码规则就是指 都采用3个字节来表示一个汉字

比如ASCII字符集对于所有英文字母采用1个字节来表示（编码），所以我们称ASCII为一种字符集。

这里的范围就是指所有的英文字母
编码规则就是指 都采用1个字节来表示一个字母

5.数据类型

 

6.约束

约束：对表中的数据进行限定，保证数据的正确性，有效性、完整性

约束的分类：

外键约束：表与表之间的数据有相关联性

作用：让表与表之间产生关联关系，从而保证数据的准确性

外键的级联更新：当把主表中的数据进行删除时，从表中有关联的数据也会随之修改

级联删除：当把主表中的数据进行删除时，从表中有关联的数据也会随之删除

主键约束特点：

• 主键约束包含非空和唯一两个功能

• 一张表只能有一个主键

• 主键一般用于表中数据的唯一标识

主键自增约束:实现自动增长（可以赋值null）mysql中的主键自增约束，必须配合键的约束一起使用

唯一约束：值不能重复

非空约束:这一列值不能出现null值的情况

7.多表操作

• 多表：说白了就是多张数据表，而表与表之间是可以有一定的关联关系，这种关联关系通过外键约束实现

• 一对一：在任意一个表建立外键，去关联另外一个表的主键

• 一对多：在多的一方，建立外键约束，来关联的一方主键

• 多对多：需要建筑第三张中间表，中间至少包括两个列。这两个列作为中间表的外键，分别关联两张表的主键

多表查询：

• 内连接查询：两张表有交集的部分数据

• 左外连接：查询左表的全部数据，和左右两张表有交集部分的数据

• 右外连接：查询右表的全部数据，和左右两张表有交集部分的数据

• 子查询：查询语句中嵌套了查询语句

  • 结果是单行单列的，可以将查询的结果作为另一条语句的查询语句，使用运算符判断。=> >= < <=等

  • 结果是多行单列的，使用运算符in或not in进行判断

  • 结果是多行多列的，查询结果可以作为一张虚拟表差于查询

8.视图：

一种虚拟存在的数据表，并不在数据库中实际存在

作用：将一个复杂的查询语句的结果，自动封装到一个虚拟表中，后期再有相同需求时，直接查询该虚拟表即可，简化后期的操作

注意：修改视图数据后，源表中的数据也会随之修改

9.备份和恢复

①登录mysql服务器，输入mysqldump -u root -p 数据库名称>文件保存路径

恢复：

1. 登录mysql数据库

2. 删除已备份的数据库

3. 重新创建名称相同的数据库

4. 使用该数据库

5. 导入文件执行：source 备份文件全路径

②图形化工具方式来进行备份

10.存储过程和函数

• 存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合(类似于java中的方法)

• 存储过程和函数的好处：

  提高代码的复用性

  减少数据在数据库和应用服务器之间的传输，提高效率

  减少代码层面的业务处理

• 存储过程和函数的区别

  存储函数必须有返回值

  存储过程可以没有返回值

• 存储过程的参数和返回值：

  in：代表输入参数，需要由调用者传递实际参数(默认)

  out：代表输出参数，该参数可以作为返回值

  inout：代表既可以作为输入参数，也可以作为输出参数

11.触发器

• 触发器是与表有关的数据库对象，可以在insert、update、delete之前或之后触发并执行触发器中定义的sql语句

• 这种特性可以协助应用系统在数据库端确保数据的完整性、日志记录、数据校验等操作

• 使用别名new和old来获取触发器中发生变化的内容记录

 

12.事务

• 事务：一条或多条SQL语句组成一个执行单元，其特点是这个单元要么同时成功，要么同时失败

• 单元中的每条SQL语句都相互依赖，形成一个整体

• 如果每条SQL语句执行失败或者出现错误，那么整个单元就会撤回到事务最初的状态

• 如果单元中SQL语句都执行成功，则事务就顺利执行

• 事务的提交方式：

  • 自动提交(MYSQL默认)

  • 手动提交

• 事务的四大特征（ACID）：

  • 原子性(A)：事务所包含的所有操作妖魔全部成功，要么全部失败回滚。（因此事务的操作如果操作成功就必须完全应用到数据库，如果操作失败不能对数据库有任何影响）

  • 一致性(C)：事务必须使一个数据库从一个一致性状态变换到另一个一致性状态。（也就是说事务执行之前和执行之后都必须处于一致性状态）

  • 隔离性(I):隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务不能被其他事务操作所干扰，多个并发事务之间要相互隔离

  • 持久性(D)：一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的。即便是在数据库系统中遇到故障的状态下也不会丢失事务的操作

• 事务的隔离级别：多个客户端操作时，各个客户端的事务应该是隔离的，相互独立的，不受影响的。而如果多个事务操作同一批数据时，就会产生不同的问题，外面需要设置不同的隔离级别来解决这些问题

• 隔离级别分类：

  

   

  MMYSQL默认隔离级别 ：repeatable-read

  Oracle默认隔离级别:read committed

• 引发的问题：

  

   

• 脏读问题解决：修改隔离级别为 read committed

• 不可重复读问题解决：修改隔离级别为 repeatable read

• 幻读问题解决：修改隔离级别为 serializable

• 注意：隔离级别从小到大安全性越来越高，但是效率越来越低，所以不建议修改数据库默认的隔离级别

13.MYSQL体系结构：

 

• Connectors:客户端连接

• Connection Pool：连接值，类似于多线程中的线程池，客户端发起请求连接，会从整个连接值里面先获取连接进行使用，使用完之后还会把连接重新归还给使用者当中，以此达到连接复用的效果

• Enterprise Mangement Services& Utilities：管理服务和工具

• parser：查询解析器；Optimizer：查询优化器

• Caches &Buffers：缓存

 

14.存储引擎

• MYSQL数据库使用不同的机制存取表文件，包括存储方式、索取技巧、锁定水平等不同的功能。这些不同的技术以及配套的功能称为存储引擎

• Oracle、SqlServer等数据据库只有一种存储引擎。而MYSQL针对不同的需求，配置不同的存储引擎，就会让数据库采取不同处理数据的方式和扩展功能。

• MYSQL支持的存储引擎有很多，最常见的三种：InnDB(最常用)、MyISAM、MEMORY

• 特性对比：

  • MyISAM：访问快、不支持事务和外键操作

  • InnDB：支持事务和外键操作，支持并发控制，占用磁盘空间大

  • MEMORY：内存存储，速度快，不安全。适合小量快速访问的数据

  

存储引擎的选择：

• MyISAM：

  • 特点：不支持事务和外键操作，读取速度块，节约资源

  • 使用场景：以查询操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性并不高

• InnDB：

  • 特点：MYSQL的默认存储引擎，支持事务和外键操作

  • 使用场景：对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，读写频繁的操作

• MEMORY：

  • 特点：将所有数据保存在内存中，在需要快速定位记录和其他类似数据环境下，可以提供更快的访问

  • 使用场景：通常那个用于更新不太频繁的小表，用来快速得到访问的结果

  总结：针对不同的需求场景，来选择最适合的存储引擎，不确定使用数据库默认的存储引擎

14.索引

• MYSQL索引：是帮助MYSQL高效获取数据的一种数据结构，所以索引的本质就是数据结构

• 在表数据之外，数据库系统还维护者满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就i可以在这些数据结构上实现高级查找算法，这种数据结构就是索引

• 索引分类：

索引原理：

磁盘存储：

• 系统从磁盘读取数据到内存是以磁盘块为基本单位的

• 位于同一个磁盘块中的数据会被一次性读出来，而不是需要什么读什么

• InnoDB存储引擎中有页的概念，页是其磁盘管理的最小单位，InnDB存储引擎中默认每个页的大小为16KB

• InnoDB引擎将若干个地址连接磁盘块，以此来达到页的大小16KB，在查询数据中如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率

BTree数据结构特点：

• 在每一个结点上除了会保存键值(key)以外，还会保存真实的数据，会增加查询数据时磁盘的IO次数

• 在进行查询数据的时候，只要涉及到相关的磁盘块，这些数据也会被全部读取出来，效率就并不高

B+Tree特点：

• 非叶子节点只会保存键(key)值

• 所有叶子节点之间都有指针

• 所有的数据都存储在叶子节点上

B+Tree好处：

• 提高查询速度

• 减少磁盘IO次数

• 相比于BTree来说树形结构较小

索引的设计原则：

创建索引遵循的原则：

• 1.对查询频次较高，且数据量较大的表建立索引

• 2.使用唯一索引，区分度越高，使用索引的效率越高

• 3.索引字段的选择，最佳候选列应当从where自居的条件中提取

• 4.索引可以有效提升查询数据的效率，但并不是多多益善

最左匹配原则(适用组合索引)：

15.锁机制

• 锁机制：数据库为了保证数据的一致性，在共享的资源被并发访问时变得安全所设计的一种原则

• 锁机制类似多线程中的同步，作用就是可以保证数据的一致性和安全性

• 锁按操作分类：

  • 共享锁：也叫读锁。针对同一份数据，多个事务读取操作可以同时加锁而互不影响，但是不能修改数据

  • 排他锁：也叫写锁。当前的操作没有完成前，会阻断其他操作的读取和写入

• 按粒度分类：

  • 表级锁：会锁定整个表，开销小，加锁快。锁定力度大，发生锁冲突概率高，并发度低，不会出现死锁情况

  • 行级锁：会锁定当前行。开销大，加锁慢。锁定粒度小，发生锁冲突概率低，并发度高。会出现死锁情况

• 按使用方式分类：

  • 悲观锁：每次查询数据都认为别人会修改，很悲观，所以查询时加锁。悲观锁一般考关系型数据库提供的锁机制

  • 乐观锁：每次查询数据时都认为别人不会修改，很乐观，但是更新时会判断一下在此期间别人有没有去更新这个数据。需要用户自己去实现，不会发生并发抢占资源，只是在提交操作的时候检查是否违反数据完整性

• 不同存储引擎支持的锁：

  

• InnDB共享锁：

  • 特点：数据可以被多个事务查询，但是不能修改

  • 共享锁和共享锁之间时是兼容的

  • InnDB默认加的是行锁

  • InnoDB引擎如果不采用带索引的列加锁，加的是表锁

• InnDB排他锁：

  • 特点：加锁的数据，不能被其他事务加锁查询或修改

  • 排他锁和共享锁是不兼容的

  • 排他锁和排他锁是不兼容的

• MyISAM读锁：

  • 所有连接只能查询数据，不能修改

• MyISAM写锁：

  • 写锁特点：其他链接不能查询和修改数据