MySQL数据库Day01-数据库MySQL的基本概念

た入场券 2023-01-13 13:59 185阅读 0赞

### 数据库MySQL的基本概念 ###

*  MySQ启动和登录
 *  MySQL索引
 *   *  基本概念
     *  索引特点
     *  索引结构
     *  索引分类
     *  索引语法
     *  索引原则
 *  MySQL视图
 *   *  基本概念
     *  视图语法
 *  存储过程和存储函数
 *   *  基本概念
     *  存储过程
     *   *  创建存储过程
         *  调用存储过程
         *  查看存储过程
         *  删除存储过程
         *  语法
         *   *  变量操作
             *  条件判断
             *  参数定义
             *  选择结构
             *  循环结构
             *   *  while循环
                 *  repeat循环
                 *  loop循环
             *  游标
     *  存储函数
     *   *  创建存储函数
         *  调用存储函数
         *  删除存储函数
 *  触发器
 *   *  基本概念
     *  创建触发器
     *  查看触发器
     *  删除触发器

# MySQ启动和登录 #

*  启动**MySQL:**

# service mysql start
    mysql.server start

*  授权**MySQL**远程登录权限:

grant all priviliges on *.* to 'root' identified by '123456'
    
    flush priviliges

# MySQL索引 #

## 基本概念 ##

*  **索引:** index
    
     *  是一种有序的,为了高效获取数据库数据的一种数据结构
     *  索引是数据库维护的一种满足特定查找算法的数据结构,这个数据结构以特定的方式引用或者指向数据
     *  索引本身很大,不会存储在内存中,通常以索引文件的形式存储在磁盘上. 索引是数据库中用来提高性能的最常用工具

## 索引特点 ##

*  **优点:**
    
     *  提高查询数据的效率,降低数据库的**IO**成本
     *  可以通过索引对数据库数据进行排序,降低数据排序的成本,降低**CPU**消耗
 *  **缺点:**
    
     *  索引也是一张表,表中保存了主键和索引字段,并且指向实体数据记录,所以要占用一定的空间
     *  索引在提升查询效率时,也降低了数据库的更新效率.比如对表进行插入**INSERT,** 更新**UPDATE**和删除**DELETE**操作时 **,MySQL**数据库不仅要更新实体数据,还需要更新保存索引文件中每次更新添加的索引列字段,都会调整实体数据变化后的索引信息

## 索引结构 ##

*  索引是在**MySQL**的存储引擎层实现的,而不是在服务器层实现的
 *  不同的存储引擎支持的索引结构不一定完全相同 **,MySQL**中包含以下**4**中索引结构:
    
     *  **BTree:** 常用的索引类型,大部分引擎都支持**BTree**索引
     *  **Hash:** 只有**Memory**引擎支持的索引,使用场景简单
     *  **R-tree:** 空间索引. 空间索引是**MyISAM**中的一个特殊的索引类型,主要适用于地理空间数据类型
     *  **Full-text:** 全文索引. 全文索引是**MyISAM**中的一个特殊的索引类型,主要用于全文索引

<table> 
 <thead> 
  <tr> 
   <th>索引结构</th> 
   <th>InnoDB 引擎</th> 
   <th>MyISAM 引擎</th> 
   <th>Memory 引擎</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>BTree 索引</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>支持</td> 
  </tr> 
  <tr> 
   <td>Hash 索引</td> 
   <td>不支持</td> 
   <td>不支持</td> 
   <td>支持</td> 
  </tr> 
  <tr> 
   <td>R-tree 索引</td> 
   <td>不支持</td> 
   <td>支持</td> 
   <td>不支持</td> 
  </tr> 
  <tr> 
   <td>Full-text 索引</td> 
   <td>支持(5.6)</td> 
   <td>支持</td> 
   <td>不支持</td> 
  </tr> 
 </tbody> 
</table>

*  通常说的索引是指**B+** 树结构组织的索引,是一个多路搜索树,不一定是二叉树
 *  聚集索引,复合索引,前缀索引,唯一索引默认都是使用**B+** 树索引
 *  **B树结构:** 多路平衡搜索树,比如一棵**m**叉的**BTree**
    
     *  树中的每个节点最多包含 m m m个孩子
     *  除了根节点和叶子节点以外,每个节点至少有 c e i l ( m 2 ) ceil(\\frac\{m\}\{2\}) ceil(2m)个孩子
     *  如果根节点不是叶子节点,那么至少有两个孩子
     *  所有叶子节点都在同一层
     *  每一个非叶子节点都是由 n n n个**key**和 n + 1 n+1 n\+1个指针组成 **. n**的取值范围 \[ c e i l ( m 2 ) − 1 , m − 1 \] . \[ceil(\\frac\{m\}\{2\}) -1,m-1\]. \[ceil(2m)−1,m−1\].
     *  当非叶子节点的**key**值超出范围时,中间节点向上一层分裂到父节点,两边节点分裂成为子节点
        
         *  **B**树比起二叉树,因为**B**树的层级比二叉树小,所以查询效率更高,搜索速度快
 *  **B+树结构:** **B+树是B**树的扩展 **,B+树和B**树之间有区别
    
     *   m m m叉**B+** 树最多包含 m m m个**key.** 而 m m m叉**B**树最多包含 m − 1 m-1 m−1个**key**
     *  **B+** 树的所有叶子节点保存所有的**key**信息,根据**key**的大小顺序排列
     *  所有的非叶子节点都可以看作是所有**key**信息的索引
        
         *  **B+** 树中所有**key**信息都保存在叶子节点,查询任何**key**都需要从根节点遍历到叶子节点,所以**B+** 树的查询效率更加稳定
 *  **MySQL中的B+树结构:**
    
     *  **MySQL**中中使用了优化的**B+** 树结构
     *  在原有的**B+** 树的结构的基础上 **,MySQL**中增加了一个指向相邻叶子节点的链表指针,形成了一个带有顺序指针的**B+** 树
     *  这样**MySQL**中的**B+** 树结构有利于范围访问,提高了区间的访问性能

## 索引分类 ##

*  **单值索引:** 一个索引只包含一个单列.一个表中可以建立多个单值索引
 *  **唯一索引:** 索引列的值必须唯一,允许存在多个空值**null**
 *  **复合索引:** 一个索引包含多个列

## 索引语法 ##

*  **创建索引:**

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING index_type] ON table_name(column_list);

*  **查看索引:**

SHOW INDEX FROM table_name;

*  **删除索引:**

DROP INDEX ON table_name;

*  **ALTER命令:**

-- 添加一个主键.索引值必须唯一并且不能为null
    ALTER TABLE table_name ADD PRIMARY KEY(colum);
    
    -- 添加一个唯一索引.索引的值必须唯一,不包括null,如果有null,null可以出现多次
    ALTER TABLE table_name ADD UNIQUE index_name(colum_list);
    
    -- 添加普通索引.索引值可以出现多次
    ALTER TABLE table_name ADD INDEX index_name(colum_list);
    
    -- 添加全文索引,用于全文搜索
    ALTER TABLE table_name ADD FULL_TEXT index_name(column_list);

## 索引原则 ##

*  对于查询频次较高,数据量大的数据表建立索引
 *  索引字段最好从**WHERE**的子句的条件中提取. 如果**WHERE**子句中的组合比较多,最好挑选最常用的,过滤效果最好的列的组合
 *  最好使用唯一索引.因为索引的区分度越高,索引的使用效率越高
 *  索引可以有效提升查询效率,但不是索引数量越多越好. 索引越多,索引维护的代价也就越高.对于插入,更新,删除等**DML**操作频繁的表而言,索引越多,维护的代价越高,降低DML操作的效率,增加操作的时间成本. 索引越多,也会增加**MySQL**的选择成本,尽管最终会找到一个可用的索引,但是提高了选择的代价
 *  最好使用短索引. 创建的索引是使用硬盘来存储的,可以提升索引访问的**I/O**效率,也可以提升总体的访问效率.当构成索引的总长度较短时,就可以在给定大小的存储块内存储更多的索引值,可以有效的提升**MySQL**访问索引的**I/O**效率
 *  利用好最左前缀原则 **. N**个列组合而成的索引,就相当于创建了**N**个索引. 查询时**WHERE**的子语句中使用了组成索引的前几个字段,这条**SQL**语句就可以利用组合索引提升查询效率

# MySQL视图 #

## 基本概念 ##

*  **视图:** View
    
     *  一种**MySQL**数据库中虚拟存在的表
     *  **MySQL**数据库中不存在视图的表,视图中行和列的数据来源于定义视图时查询中使用到的表,在使用视图时动态生成
     *  视图就是**SELECT**语句返回的结果集,创建视图主要工作就是写好视图的**SELECT**语句
 *  **视图的优点:**
    
     *  **简单:** 使用的视图就是一个已经过滤好的复合条件的结果集,不需要关心视图后面对应的表的结构,关联条件和筛选条件
     *  **安全:** MySQL中可以实现表的权限管理,但是无法精确到对表的某个行和列,使用视图可以简单实现对数据的访问精确到某个表的某行和某列中
     *  **数据独立:** 使用的视图确定好后,视图后面对应的表结构变化不会影响视图的结构. 如果对应的表增加列完全不会影响到视图,如果对应的表修改了列名,只需要对应的修改视图的列名即可,不会引发数据访问的问题

## 视图语法 ##

*  **创建视图:**

CREATE [OR REPLACE] [ALGORITHM = {
       UNDEFINED | MERGE | TEMPTABLE}]
    VIEW view_name [(cloumn_list)]
    AS select_statement
    [WITH [CASCADE | LOCAL] CHECK POINT]

*  视图既主要用于查询,在特殊的情况也可以进行更新,但是不建议对视图进行更新
    
     *  **WITH \[CASCADE | LOCAL\] CHECK POINT :** 用于指定是否允许对视图中的数据记录进行更新的操作
        
         *  **CASCADE:** 默认,必须满足当前视图依赖的所有视图的条件可以进行更新操作
         *  **LOCAL:** 满足当前视图的条件可以进行更新操作
 *  **修改视图:**

ALTER [ALGRITHM = {
       UNDEFINED | MERGE | TEMPTABLE}]
    VIEW view_name [(cloumn_list)]
    AS select_statement
    [WITH [CASCADE | LOCAL] CHECK POINT

*  **查看视图:**

-- 从 MySQL 5.1 版本以后,使用SHOW TABLE不仅可以查看表的名称,也可以查看视图的名称
    SHOW TABLES
    
    -- 从 MySQL 5.1 版本以后,使用SHOW STATUS不仅可以查看表的信息,也可以查看视图的信息
    SHOW STATUS
    
    -- 查看视图的创建语句
    SHOW CREATE VIEW view_name

*  **删除视图:**

DROP VIEW [IF EXISTS] view_name [,..view_name] [RESTRICT | CASCADE]

# 存储过程和存储函数 #

## 基本概念 ##

*  **存储过程和存储函数:**
    
     *  事先经过编译并且存储在数据库中的一段**SQL**语句的集合
     *  可以减少数据在数据库和应用服务器之间的数据传输次数,有利于提高数据处理效率
 *  **存储过程:** 一个没有返回值的函数
 *  **存储函数:** 一个有返回值的过程

## 存储过程 ##

### 创建存储过程 ###

*  创建存储过程的语法中,因为存储过程体中的**SQL**语句结束符分号和**MySQL**的语句默认的分隔符分号冲突,所以需要使用**DELIMITER**命令修改**MySQL**中的语句分隔符
 *  **DELIMITER :** 分隔符,默认为分号
    
     *  用来声明**MySQL**数据库中的分隔符的关键字
     *  定义指定的符号表明**MySQL**命令编写结束,可以执行

DELIMITER $

*  **创建存储过程:**

CREATE PROCEDURE procedure_name([procedure_parameter, ...])
    BEGIN
    select_statement
    ...
    END$
    
    DELIMITER ;

### 调用存储过程 ###

*  **调用存储过程:**

CALL procedure_name();

### 查看存储过程 ###

*  **查看存储过程:**

-- 查询MySQL数据库db_name中所有的存储过程的名称
    SELECT name FROM mysql.proc WHERE db='db_name';
    
    -- 查询MySQL数据库中存储过程的状态信息
    SHOW PROCEDURE STATUS
    
    -- 查看MySQL数据库中指定存储过程的创建语句
    SHOW CREATE PROCEDURE procedure_name [\G];

### 删除存储过程 ###

*  **删除存储过程:**

DROP PROCEDURE [IF EXISTS] procedure_name;

### 语法 ###

*  存储过程时有可编程的性质，所以可以在存储过程中使用变量，表达式和条件控制结果来实现复杂的功能

#### 变量操作 ####

*  **DECLARE:**
    
     *  可以使用**DECLARE**声明一个局部变量
     *  使用**DECLARE**声明的局部变量的作用范围在当前的**begin**和**end**的过程方法体中
    
        DECLARE var_name[,...] var_type [DEFAULT var_value];
    
     *  **示例:**
    
        DELIMITER $
        CREATE PROCEDURE procedure_variable()
        BEGIN
        DECLARE num int DEFAULT 6;
        SELECT num+10;
        END $
 *  **SET:**
    
     *  可以使用**SET**对变量进行赋值
     *  使用**SET**可以对变量赋值为常量或者表达式
    
        SET var_name=expr [,...var_name=expr]
    
     *  **示例:**
    
        DELIMITER $
        CREATE PROCEDURE procedure_variable()
        BEGIN
        DECLARE name varchar(20);
        SET name='Chova';
        SELECT name;
        END$
 *  **INTO:**
    
     *  可以使用**SELECT…INTO**的方式为变量赋值为常量或者表达式
    
        DECLARE $
        CREATE PROCEDURE procedure_variable()
        BEGIN
        DECLARE count int;
        SELECT count(1) INTO count FROM table_name;
        END$

#### 条件判断 ####

*  使用**if**实现条件判断:

IF search_condition THEN statement_list
    [ELSE IF search_condition THEN statement_list]
    [ELSE statement_list]
    END IF;

#### 参数定义 ####

*  可以在创建存储过程的语法定义中定义参数,实现存储过程参数变量的输入和输出操作

CREATE PROCEDURE procedure_variable([IN | OUT | INOUT] 参数名 参数类型, ...)
    BEGIN
    ...
    END$

*  **IN :** 输入参数,需要调用方调用时传入参数值.默认定义的参数为输入参数
 *  **OUT :** 输出参数,定义的参数可以作为执行存储过程的返回值,不需要进行返回操作,参数可以在函数执行结束,直接返回. 使用一个变量接收即可
 *  **INOUT :** 既可以作为输入参数,也可以作为输出参数. 也就是既可以是调用方调用时传入的参数值,也可以是执行存储过程的返回值
 *  **MySQL**中的变量:
    
     *  **@ :** 用户会话变量. 在变量名称前面使用,代表在整个用户会话中使用.会话结束,变量消失.
     *  **@@ :** 系统变量

#### 选择结构 ####

*  **case**结构:

CASE
    	WHEN when_value THEN statement;
    	[WHEN when_value THEN statement;]
    	...
    	[ELSE statement;]
    END CASE;
    
    -- 
    
    CASE 
    	WHEN search_condition THEN statement;
        [WHEN search_condition THEN statement;]
        ...
    	[ELSE statement;]
    END CASE;

#### 循环结构 ####

##### while循环 #####

*  **while:** 条件循环控制语句,满足条件则进入循环

WHILE search_condition DO
    	statement;
    END WHILE;

##### repeat循环 #####

*  **repeat:** 条件循环控制语句,满足条件则退出循环

REPEAT
    	statement;
    UNTIL search_condition
    END REPEAT;

##### loop循环 #####

*  **loop:** 使用**loop**语句可以实现简单的循环,退出循环的条件需要使用到其余的语句定义,否则会进入死循环. 通常情况下,使用**leave**语句实现循环退出

[alias:] LOOP
    	statement;
    	leave [alias];
    END LOOP [alias];

*  **leave:**
    
     *  用来从标注的流程结构中退出
     *  通常和**BEGIN…END**或者循环结构联合使用

#### 游标 ####

*  **游标:** 用来存储查询结果集的数据类型
    
     *  在存储过程和存储函数中可以使用游标对结果集进行循环地处理
     *  游标的使用方式包括声明游标,开启游标,获取游标和关闭游标
 *  **声明游标:**

DECLARE cursor_name CURSOR FOR select_statement;

*  **开启游标:**

OPEN cursor_name;

*  **获取游标:** 每次获取结果集中的一行数据

FETCH cursor_name INTO var_name [,var_name...];

*  **关闭游标:**

CLOSE cursor_name;

*  **MySQL中的句柄:**

DECLARE EXIT HANDLER FOR NOT FOUND SET varaible_name = variable_value

*  **注意:** 如果游标和句柄联合使用时,句柄定义语句要紧接在游标定义语句之后

## 存储函数 ##

*  存储函数是有返回值的

### 创建存储函数 ###

*  **创建存储函数:**

CREATE FUNCTION function_name([function_param param_type])
    RETURN return_type
    BEGIN
    	statement
    END$

### 调用存储函数 ###

*  **获取存储函数的返回值:**

SELECT function_name(param);

### 删除存储函数 ###

*  **删除存储函数:**

DROP FUNCTION [IF EXISTS] function_name;

*  存储函数的语法和存储过程的语法是一样的

# 触发器 #

## 基本概念 ##

*  **触发器的定义:** 触发器是与数据库表有关的数据库对象. 可以在**INSERT, UPDATE, DELETE**操作之前或者之后,触发并执行触发器中定义的**SQL**语句集合
 *  **触发器的特点:**
    
     *  可以协助应用在数据库端确保数据的完整性
     *  可以进行日志记录,数据校验操作等
 *  **行记录变量:** 可以使用行记录变量**OLD**和**NEW**来引用触发器中发生变化的记录内容
 *  **MySQL**中的触发器目前只支持行级触发,不支持语句级触发

<table> 
 <thead> 
  <tr> 
   <th>触发器类型</th> 
   <th>行记录变量</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>INSERT 型触发器</td> 
   <td>NEW 表示将要或者已经新增的记录内容</td> 
  </tr> 
  <tr> 
   <td>UPDATE 型触发器</td> 
   <td>OLD 表示修改之前的记录内容 <br>NEW 表示修改之后的记录内容</td> 
  </tr> 
  <tr> 
   <td>DELETE 型触发器</td> 
   <td>OLD 表示将要或者已经删除的记录内容</td> 
  </tr> 
 </tbody> 
</table>

## 创建触发器 ##

*  **创建触发器:**

CREATE TRIGGER trigger_name
    BEFORE/AFTER INSERT/UPDATE/DELETE
    ON table_name
    FOR EACH ROW
    BEGIN
    	sql_statement;
    END$

## 查看触发器 ##

*  可以通过以下命令查看触发器的状态和语法等信息

SHOW TRIGGERS;

## 删除触发器 ##

*  可以删除指定数据库中的触发器,默认为当前数据库

DROP TRIGGER [schema_name.]trigger_name;