mysql基础

1 mysql介绍

1 定义
数据库：按照数据结构来组织和存储，管理Data的仓库。
从事mysql的主要有两类人员：开发人员和数据分析人员。即是OLTP和OLAP。
2 sql
结构化查询语言标准的。支持大部分数据库 Mysql，sql server，orcale，sybase，postgreSQL，MS access等。
分类：
DQL：数据查询语言 select查询
DML：数据操作语言 insert，update，delete
DDL：数据定义语言 create，alter，drop
DCL：数据控制语言 grant，revoke （授权，回收权限）
TCL：事务控制语言 commit，rollback（提交，回滚）事务

3 数据库常见术语
数据库：数据库是一些关联表的集合；
数据表：表是数据的矩阵；
列：一列包含相同类型的数据；
行：或者称为记录是一组相关的数据；
主键：主键是唯一的；一个数据表只能包含一个主键；
外键：外键用来关联两个表，来保证参照完整性；MyISAM存储引擎本身并不支持外键，只起到注释作用；而innodb完整支持外键；
复合键：或称组合键；将多个列作为一个索引键；
索引：用于快速访问数据表的数据；索引是对表中的一列或者多列的值进行排序的一种结构；

2 mysql体系结构

目前是分析的5.7版本，8.0版本有大的改版。

1 组成
mysql由以下几部分组成：驱动api；连接池组件；管理服务和工具组件；sql接口组件；查询分析器组件；优化器组件；缓冲组件；插件试存储引擎；物理文件系统
连接者：不同语言的代码程序与mysql交互。
连接池：管理缓冲用户连接，用户名，密码，权限校验，线程处理等缓存需求。网络处理流程，主线程接收conn，接收连接交由连接池处理。
可采用的处理方式：IO多路复用select+阻塞IO；memcached
mysql命令处理是并发处理的。select(, NULL)一直阻塞。

管理服务和工具组件
系统管理和控制工具，例如备份恢复，msyql复制，集群等。
sql接口
将sql语句解析生成相应对象；DML,DDL，存储过程，视图，触发器等。
查询解析器
将sql对象交由解析器验证和解析，并生成语法树
查询优化器
sql语句执行前使用查询优化器进行优化。
缓冲组件
是一块内存区域，用来弥补磁盘速度较慢对数据库性能的影响；在数据库进行读取页操作，首先将从磁盘读到的页方再缓冲池中，下一次再读相同的页时，首先判断该页是否在缓冲池中，若在缓冲池命中，直接读取；否则读取磁盘中的页，说明该页被LRU淘汰了；缓冲池中LRU采用最近最少使用算法来进行管理；
缓冲池缓冲的数据类型有：索引页，数据页，以及与存储引擎缓存相关的数据(比如innodb引擎：undo页，插入缓存，自适应hash索引，innodb相关锁信息，数据字典信息等);
插件试存储引擎
可以自定义存储引擎，myisam,innodb等
<font size=‘“3” color=“red”>文件系统
物理文件系统和File&logs两部分。

3 数据库设计三范式

作用：为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式。
范式一
确保每列保持原子性；数据库表中的所有字段都是不可分解的原子值；例如：地址省，市，区等是否拆分
范式二
确保表中的每列都和主键相关，而不能只与主键的某一部分相关(组合索引)
范式三
确保每列都和主键直接相关，而不是间接相关；减少数据冗余；

4 crud

1 执行过程如下：

2 基本操作
基础
创建数据库，删除数据库，选择数据库；
创建表，删除表，清空数据表；
增，删，改，查
这些sql语句操作必须要学会。
高级查询：
条件查询(where)，范围查询(between,and)，判断查询(NOT NULL,NULL, <>, =)，模糊查询(LIKE)，分页查询(LIMIT)，排序查询(order by)，聚合查询(sum,avg,max,min,count)，分组查询(group by)
连表查询
内联(inner join)，左表(left join)，右表(right join)
子查询和合并查询
单行子查询和多行子查询
多行子查询即返回多行记录的子查询。
IN 关键字：运算符可以检测结果集中是否存在某个特定的值，如果检测成功就执行外部的查询。
EXISTS 关键字：内层查询语句不返回查询的记录。而是返回一个真假值。如果内层查询语句查询到满足条件的记录，就返回一个真值（true），否则，将返回一个假值（false）。当返回的值为true时，外层查询语句将进行查询；当返回的为false时，外层查询语句不进行查询或者查询不出任何记录。
ALL 关键字：表示满足所有条件。使用ALL关键字时，只有满足内层查询语句返回的所有结果，才可以执行外层查询语句。
ANY 关键字：允许创建一个表达式，对子查询的返回值列表，进行比较，只要满足内层子查询中的，任意一个比较条件，就返回一个结果作为外层查询条件。
在 FROM 子句中使用子查询：子查询出现在from子句中，这种情况下将子查询当做一个临时表使用。

5 正则表达式

6 视图

1 定义
视图是一种虚拟存在的表，是一个逻辑表，本身并不包括数据。其内容由查询定义。
基表：用来创建视图的表叫做基表；
通过视图，可以展现基表的部分数据；
视图数据来自定义视图的查询中使用的表，使用视图动态生成。
2 优点
简单：使用视图的用户完全不需要关系后面对应表的结构，关联条件和筛选条件，对用户来说已经是过滤好的符合条件的结果集。
安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，原表增加列对视图没有影响；源表修改列表，则可以通过修改视图来解决，不会造成对访问者的影响。
虚拟表，不关心底层表的结构；安全权限控制；屏蔽表结构变化对用户的影响
3 语法

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

4 作用
可复用，减少重复语句书写；类似程序中函数的作用；
重构利器：假如因为某种需求，需要将user拆房表usera和表userb；如果应用程序使用sql语句：select * from user那就会提示该表不存在；若此时创建视图create view user as select a.name,a.age,b.sex from usera as a, userb as b where a.name=b.name;，则只需要更改数据库结构，而不需要更改应用程序；视图在oracle 物化视图 mysql select * from
逻辑更清晰，屏蔽查询细节，关注数据返回
权限控制，某些表对用户屏蔽，但是可以给该用户通过视图来对该表操作

7 流程控制

IF：条件判断
CASE：分支 相当于switch
WHILE:循环
LEAVE:退出，相当于break
ITERATE:继续，相当于continue
LOOP：循环 相当于 while(true) {…}
REPEAT:循环 相当于 do…while(condition)

8 触发器

1 定义
触发器trigger是mysql提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如对一个表进行DML操作(insert，delete，update)时就会激活它执行。
2 要素
监视对象： table
监视事件：insert，update，delete
触发时间：before，after
触发事件：insert，update，delete
3 语法

4 New和OLD
在 INSERT 型触发器中，NEW 用来表示将要（BEFORE）或已经（AFTER）插入的新数据；
在 DELETE 型触发器中，OLD 用来表示将要或已经被删除的原数据；
在 UPDATE 型触发器中，OLD 用来表示将要或已经被修改的原数据，NEW 用来表示将要或已经修改为的新数据；

9 存储过程

1 定义
SQL语句需要先编译然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。
存储过程是可编程的函数，在数据库中创建并保存，可以由SQL语句和控制结构组成。当想要在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟，它允许控制数据的访问方式。
2 特定
能完成较复杂的判断和运算 有限的编程
可编程行强，灵活
SQL编程的代码可重复使用
执行的速度相对快一些
减少网络之间的数据传输，节省开销
3 语法

存储过程根据需要可能会有输入、输出、输入输出参数，如果有多个参数用","分割开。
MySQL 存储过程的参数用在存储过程的定义，共有三种参数类型 IN, OUT, INOUT。
IN：参数的值必须在调用存储过程时指定，0在存储过程中修改该参数的值不能被返回，可以设置默认值
OUT：该值可在存储过程内部被改变，并可返回
INOUT：调用时指定，并且可被改变和返回
过程体的开始与结束使用 BEGIN 与 END 进行标识。

10 游标

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

取游标数据
FETCH cursor_name INTO var_name[,var_name,…]
关闭游标：
CLOSE curso_name;
释放：
DEALLOCATE cursor_name;
设置游标结束标志：
DECLARE done INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET done = 1; – done 为标记为

11 权限管理

创建用户：
create user username@host INENTIFIED BY password;
host 指定该用户在哪个主机上可以登陆，如果是本地用户可用 localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符 %;
授权：
GRANT privileges ON databasename.tablename TO ‘username’@‘host’ WITH GRANT OPTION;
privileges：用户的操作权限，如SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL；
databasename.tablename 如果是 . 表示任意数据库以及任意表；
WITH GRANT OPTION 这个选项表示该用户可以将自己拥有的权限授权给别人。注意：经常有人在创建操作用户的时候不指定 WITH GRANT OPTION 选项导致后来该用户不能使用 GRANT 命令创建用户或者给其它用户授权。
如果不想这个用户有这个grant的权限，则不要加该 WITH GRANT OPTION 选项；
对视图授权
GRANT select, SHOW VIEW ON databasename.tablename to ‘username’@‘host’;
刷新权限
– 修改权限后需要刷新权限
FLUSH PRIVILEGES;