mysql简单操作

mysql是一种关系型数据库，这里主要讲操作和使用。
mysql主要针对两种开发人员。OLTP（on-line transaction processing），主要负责数据的增删改查，也就是后端开发，还有一种是OLAP（On-Line Analytical Processing），主要负责对数据库中数据的分析，也就是后台数据分析。

sql分类

先简单介绍一下sql语句，sql是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。SQL是关系数据库系统的标准语言。

DQL

全称是Data Query Language，数据查询语言，相当于mysql中的select。

DML

全称是Data Manipulate Language，数据操作语言，即是增删改，相当于mysql中的insert、delete和update。

DDL

全称Data Define Languge，数据定义语言，即是对数据库中的表结构等进行操作，相当于mysql中的create、创建一个新的表、表的视图、或者在数据库中的对象、alter和drop。

DCL

全称Data Control Language，数据控制语言，基本上是用户授权操作，相当于mysql中的grant、revoke。

mysql体系结构

将操作之前，还是先讲一下mysql体系结构。

重点掌握优化器、缓冲池、存储引擎、线程以及存储引擎和文件系统之间的关系。

网络处理流程

流程在图中已经解释得很清楚了，这里主要是提示几点需要注意的地方。mysql使用的是select而不是epoll，因为两点，一是mysql的连接数并不会太多，二是为了做到跨平台，select支持跨平台操作，而epoll不行。另外，mysql是并发处理每条命令，因为每一条连接都对应一个线程。

连接池

mysql为了做到并发，快速响应mysql数据与请求，里面做了一个连接池，用来管理缓冲用户连接、用户名、密码、权限校验、线程处理等需要缓存的需求，连接数上限为800。主要处理方式是IO多路复用select +阻塞io。这里插一句，memcached也是这样的。这样做还可以做到权限验证的效果，不同的用户权限不同。mysql里不同的db、table和row都可以有不同的权限。

缓存

缓冲池缓存的数据类型有：索引页、数据页、以及与存储引擎缓存相关的数据（比如innodb引擎：undo页、插入缓冲、自适应hash索引、innodb相关锁信息、数据字典信息等），使用LRU淘汰算法。这里要注意，在mysql8.0后，不再设置查询缓存，因为经过测试，查询缓存命中率很低，且占用较大空间。

存储引擎

mysql使用插件式的存储引擎，也就是说，不同表可以使用不同的存储引擎，但是建议就只使用一种，且建议innodb。

文件系统

mysql文件系统中存储索引数据、表数据、事务中产生的数据、错误日志、慢查询日志等。

数据库设计三范式

为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式。
1、确保每列保持原子性。
数据库表中的所有字段都是不可分解的原子值。例如：某表中有一个地址字段，如果经常需要访问地址字段中的城市属性，则需要将该字段拆分为多个字段，省份、城市、详细地址等。
2、确保表中的每列都和主键相关，而不能只与主键的某一部分相关（组合索引）。
3、确保每列都和主键直接相关，而不是间接相关，减少数据冗余。

mysql执行过程

视图

视图（view）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。其内容由查询定义。oracle中有物化视图，真的对应一张表，而mysql中是一张虚拟表，没有索引的问题，也没有具体数据的问题。优点是简单，安全，数据独立。注意不可以通过视图来插入数据。

CREATE VIEW <视图名> AS <SELECT语句>

可复用，减少重复语句书写，类似程序中函数的作用。还是重构利器，可以在不创建新表的前提下构造一个新表。此外，还可以屏蔽一些细节，使得开发人员只关注关键信息或是屏蔽掉没有权限的信息。

流程控制

主要是IF、CASE、WHILE等语句。主要用在触发器中和存储过程中。

触发器

它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发。触发器有两个全局变量，NEW和OLD，OLD 用来表示将要或已经被修改的原数据， NEW 用来表示将要或已经修改为的新数据。

NEW.columnName （columnName为相应数据表某一列名）
OLD.columnName （columnName为相应数据表某一列名）

触发器四要素

监视对象：table
监视事件：insert 、update 、delete
触发时间：before 、after
触发事件：insert 、update 、delete
最后再提示一下，触发器效率较低，不建议使用，使用环境是DML操作。

存储过程

存储过程是可编程的函数，在数据库中创建并保存，可以由SQL语句和控制结构组成。

特点

1、能完成较复杂的判断和运算 有限的编程。
2、可编程行强，灵活。
3、SQL编程的代码可重复使用。
4、执行的速度相对快一些。
5、减少网络之间的数据传输，节省开销。

语法

CREATE PROCEDURE 过程名([[IN|OUT|INOUT] 参数名 数据类型[,[IN|OUT|INOUT] 参数名 数据类型…]]) [特性 ...] 过程体

MySQL 存储过程的参数用在存储过程的定义，共有三种参数类型 IN , OUT , INOUT 。
IN：参数的值必须在调用存储过程时指定，0在存储过程中修改该参数的值不能被返回，可以设置默认值。
OUT ：该值可在存储过程内部被改变，并可返回。
INOUT ：调用时指定，并且可被改变和返回。
过程体的开始与结束使用 BEGIN 与 END 进行标识。
给个例子。

-- delimiter是mysql分隔符，在mysql客户端中分隔符默认是分号 ;。如果一次输入的语句较多，并且语句中间有分号，这时需要重新指定一个特殊的分隔符。通常指定 $$ 或 ||
DELIMITER //
CREATE PROCEDURE proc_in_param (IN p_in INT)
BEGIN
SELECT p_in ;
SET p_in = 2 ; 
SELECT p_in ;
END ;//
DELIMITER ;

-- 调用
SET @p_in = 1;
CALL proc_in_param (@p_in);
-- p_in虽然在存储过程中被修改，但并不影响@p_id的值
SELECT @p_in;  --=1

游标

游标是针对行操作的，对从数据库中 select 查询得到的结果集的每一行可以进行分开的独立的相同或者不相同的操作。
对于取出多行数据集，需要针对每行操作，可以使用游标。
游标常用于存储过程、函数、触发器、事件，游标相当于迭代器。

-- 定义游标
DECLARE cursor_name CURSOR FOR select_statement;
-- 打开游标
OPEN cursor_name;
-- 取游标数据
FETCH cursor_name INTO var_name[,var_name,......]
-- 关闭游标
CLOSE cursor_name;
-- 释放
DEALLOCATE cursor_name;
-- 设置游标结束标志
DECLARE done INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET done = 1; 

还是给个例子

CREATE PROCEDURE proc_while (IN age_in INT,OUT total_out INT)
BEGIN
-- 创建 用于接收游标值的变量
DECLARE p_id,p_age,p_total INT ;
DECLARE p_sex TINYINT ;
-- 注意:接收游标值为中文时,需要给变量 指定字符集utf8
DECLARE p_name VARCHAR (32) CHARACTER SET utf8 ;
DECLARE done INT DEFAULT 0 ;  -- 游标结束的标志
DECLARE cur_teacher CURSOR FOR SELECT teacher_id,teacher_name,teacher_sex,teacher_age FROM teacher
-- 声明游标
WHERE teacher_age > age_in ; -- 指定游标循环结束时的返回值

DECLARE CONTINUE HANDLER FOR NOT found
SET done = 1 ; 
OPEN cur_teacher ; -- 打开游标
SET p_total = 0 ; 
WHILE done != 1 DO
FETCH cur_teacher INTO p_id,p_name,p_sex,p_age ;
    IF done != 1 THEN
        SET p_total = p_total + 1 ;
END
    IF ;
END
WHILE ;
    CLOSE cur_teacher ;  -- 关闭游标
    SET total_out = p_total ;  -- 将累计的结果复制给输出参数
END//
delimiter ;

-- 调用
SET @p_age =20;
CALL proc_while(@p_age, @total);
SELECT @total;  -- 结果是5

权限管理

创建用户

CREATE USER username@host IDENTIFIED BY password;

对用户授权

GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;

对视图授权

GRANT select, SHOW VIEW ON `databasename`.`tablename` to 'username'@'host';

还可以对存储过程、row等授权。