SQL语句，索引视图，存储过程与触发器

---

一、数据库

按照数据结构来组织、存储和管理数据的仓库；是一个长期存储在计算机内的、有组织的、可共享的、统一管理的大量数据的集合；
总共三类
关系型数据库
关系型数据库包括：MySQL, SQL Server, Oracle, Sybase, postgreSQL 以及 MS Access等；SQL命令包括：DQL、DML、DDL、DCL以及TCL；例如mysql
非关系型数据库，Nosql
例如redis，mongodb
第三种就是elasticsearch

oltp

后端开发常用,OLTP（online transaction processing）翻译为联机事务处理；主要对数据库增删改查；OLTP主要用来记录某类业务事件的发生；数据会以增删改的方式在数据库中进行数据的更新处理操作，要求实时性高、稳定性强、确保数据及时更新成功；

olap

后台开发常用,OLAP（On-Line Analytical Processing）翻译为联机分析处理；主要对数据库查询；当数据积累到一定的程度，我们需要对过去发生的事情做一个总结分析时，就需要把过去一段时间内产生的数据拿出来进行统计分析，从中获取我们想要的信息，为公司做决策提供支持，这时候就是在做OLAP了；

Sql

结构化查询语言(Structured Query Language)简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。SQL是关系数据库系统的标准语言。

DQL

Data Query Language - 数据查询语言；
select ：从一个或者多个表中检索特定的记录；

DML

Data Manipulate Language - 数据操作语言；
insert ：插入记录；
update ：更新记录；
delete ：删除记录；

DDL

Data Define Languge - 数据定义语言；
create ：创建一个新的表、表的视图、或者在数据库中的对象；
alter ：修改现有的数据库对象，例如修改表的属性或者字段；
drop ：删除表、数据库对象或者视图；

DCL

Data Control Language - 数据控制语言；
grant ：授予用户权限；
revoke ：收回用户权限；

TCL

Transaction Control Language - 事务控制语言；
commit ：事务提交；
rollback ：事务回滚；

数据库术语

数据库：数据库是一些关联表的集合；
数据表：表是数据的矩阵；
列：一列包含相同类型的数据；
行：或者称为记录是一组相关的数据；
主键：主键是唯一的；一个数据表只能包含一个主键；
外键：外键用来关联两个表，来保证参照完整性；MyISAM存储引擎本身并不支持外键，只起到注释作用；而innodb完整支持外键；
复合键：或称组合键；将多个列作为一个索引键；
索引：用于快速访问数据表的数据；索引是对表中的一列或者多列的值进行排序的一种结构；
在DBA进场之前，所有mysql知识都需要掌握
关于数据库我们需要的知识有这些

二、Mysql体系结构

MySQL由以下几部分组成：
连接池组件、管理服务和工具组件、SQL接口组件、查询分析器组件、优化器组件、缓冲组件、插件式存储引擎、物理文件。

---

连接者

不同语言的代码程序和mysql的交互（SQL交互）；例如我们的python，Native c API，JDBC等

连接池

管理缓冲用户连接、用户名、密码、权限校验、线程处理等需要缓存的需求；
网络处理流程：主线程接收连接，接收连接交由连接池处理；
主要处理方式：IO多路复用select + 阻塞的io；
需要理解：MySQL命令处理是并发处理的；
Mysql的网络模型
采用的组件是select的io多路复用，监听读事件，一直阻塞等待读事件的到达，到达以后分配一个连接线程，每个线程里边分配一个循环，循环里的read是一个阻塞的io，其中redis用的就是reactor网路异步的封装，为啥mysql不用呢，因为我们的mysql是需要去操作磁盘的，循环里边read到do_command()花费的时间比较长，redis是基于内存的数据库，操作的是内存，不用考虑磁盘，所以考虑这种reactor网络异步封装的方式。
为啥我们这里使用到了连接池，因为我们通常使用连接池是有阻塞的io，我们需要等待mysql的返回才能发起另外一个请求，里边会出现等待的时间过长，影响执行的效率，通常使用的mysql的连接是一种短连接的方式，如果长时间不给mysql发数据，mysql也会主动踢掉这条连接，我们的客户端对Mysql进行连接的时候还需要密码验证，我们的连接池就可以减少这些连接的时间，我们可以看到下边图中mysql采用了线程并发模型，但是我们的mysql不能创建太多的连接，这样会影响mysql的执行效率，100多条连接

我们的mysql采用的是一个sql语句，那我们必须对这种语言进行一个词法句法的分析，这是第一步，例如我们的DML和DDL

过滤，optimizer与缓存

接下来呢，我们需要一些过滤，例如对象的权限，过滤完之后就会生成执行计划，也就是Optimizer，包括执行路径以及统计分析，用来制定执行计划，比如说语句当中有select操作，我们就要根据这个来制定我们的执行计划，如果我们当中有一条语句执行比较慢，我们就会在Optimizer当中分析出来。接下来就是缓存，缓存对于mysql是非常重要的，缓存主要操作的是下面的引擎，引擎需要把经常访问的磁盘数据缓存到我们的buffer当中，便于我们下次查询的时候我们能够在内存当中查找数据，以及获取数据，如果没有查到数据，我们会去访问磁盘

工具管理

接下来是工具管理的组件，主要包括数据备份恢复以及安全等等，主要是一些管理相关的以及集群备份恢复相关的

数据库引擎

mysql采用的是一种插拔式的，可以在里边选择多个引擎，我们需要了解的是我们的引擎是一个表级别的引擎，就是我们创建表，我们根据这个表来使用具体的引擎，以前有人问到，这些数据库引擎描述的是数据库的还是表的，这里引擎是用来描述表的，我们表可以选择MyISAM，或者是其他的引擎，。我们用的最多的还属InnoDB，在我们的5.5以前的版本，主流的是MyISAM，从锁的概念上来说，MyISAM使用的是一种表锁，InnoDB用的是一种行锁。
那么我们的引擎的作用是啥呢，我们的语句到达了，以及我们的执行计划已经有了的情况下，我们是怎么去协调磁盘的数据，以及我们buffer缓存当中的数据，第一个我们怎么去存储，第二个我们怎么进行缓存，这些都是在我们的引擎当中进行

文件系统

其中我们有几个重要的文件，例如Redo和Undo，是跟我们的InnoDB引擎相关的，Binary是跟我们的引擎无关的，主要是用来实现我们的Replication，就是工具管理组件当中那个。Replication也就是我们的主从复制，我们的mysql为了它的数据安全，会创建从数据库，那么这个数据怎么传输过去呢，就是采用的这种二进制日志的传输的方式Binary，把我们的这些DML，DDL语句造成数据库变更的数据通过这种二进制传输方式传输到我们的从数据库。
Data就是我们的数据文件，index是我们的索引文件，error就是我们的错误文件，query和slow就是我们的慢日志查询，比如说我们有的语句执行很慢，就会记录到这种日志中去，便于我们日后进行分析，解决问题

三、数据库设计三范式与反范式

三范式相当于软件设计模式的设计原则，为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式。

范式一

确保每列保持原子性；数据库表中的所有字段都是不可分解的原子值；
例如：某表中有一个地址字段，如果经常需要访问地址字段中的城市属性，则需要将该字段拆分为多个字段，省份、城市、详细地址等；这种设计就不符合原子设计，因为地址字段还可以再分成多个字段

范式二

确保表中的每列都和主键相关，而不能只与主键的某一部分相关（组合索引）；
如果与主键部分相关，必须将表再次拆分，不拆分的话会出现表的数据的冗余

范式三

确保每列都和主键直接相关，而不是间接相关；减少数据冗余；

反范式

范式可以避免数据冗余，减少数据库的空间，减小维护数据完整性的麻烦；但是采用数据库范式化设计，可能导致数据库业务涉及的表变多，并且造成更多的联表查询，将导致整个系统的性能降低；因此处于性能考虑，可能需要进行反范式设计；
比如重复数据较少，并且经常被查询的

四、数据库常用命令

许多资料写得很详细，所以这里略去一些基本命令
oltp读次数大约是写DML10倍左右
解决数据库读性能的问题

高级查询

为什么innodb需要主动创建主键索引，并且以自增的整数作为主键
主键是非空唯一的

DROP TABLE IF EXISTS `class`; 
CREATE TABLE `class` ( `cid` int(11) NOT NULL AUTO_INCREMENT, 
`caption` varchar(32) NOT NULL, 
PRIMARY KEY (`cid`)
 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `course`;
 CREATE TABLE `course` ( 
 `cid` int(11) NOT NULL AUTO_INCREMENT, 
 `cname` varchar(32) NOT NULL, 
 `teacher_id` int(11) NOT NULL, 
 PRIMARY KEY (`cid`), 
 KEY `fk_course_teacher` (`teacher_id`), 
 CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)
  ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; 
DROP TABLE IF EXISTS `score`; 
CREATE TABLE `score` ( 
`sid` int(11) NOT NULL AUTO_INCREMENT, 
`student_id` int(11) NOT NULL,
`course_id` int(11) NOT NULL, 
`num` int(11) NOT NULL,
 PRIMARY KEY (`sid`),
 KEY `fk_score_student` (`student_id`), 
 KEY `fk_score_course` (`course_id`),
  CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`), 
  CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`) 
  ) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;
   DROP TABLE IF EXISTS `student`; 
CREATE TABLE `student` ( 
`sid` int(11) NOT NULL AUTO_INCREMENT,
 `gender` char(1) NOT NULL, 
 `class_id` int(11) NOT NULL,
 `sname` varchar(32) NOT NULL, 
 PRIMARY KEY (`sid`), 
 KEY `fk_class` (`class_id`), 
 CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`) 
 ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `teacher`; 
CREATE TABLE `teacher` ( 
`tid` int(11) NOT NULL AUTO_INCREMENT, 
`tname` varchar(32) NOT NULL, 
 PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; 

基础查询

– 全部查询
SELECT * FROM student;
– 只查询部分字段
SELECT sname, class_id FROM student;
– 别名 列明 不要用关键字
SELECT sname AS ‘姓名’ , class_id AS ‘班级ID’ FROM student;
– 把查询出来的结果的重复记录去掉
SELECT distinct class_id FROM student;

条件查询
– 查询姓名为 邓洋洋 的学生信息
SELECT * FROM student WHERE name = ‘邓洋洋’;
– 查询性别为 男，并且班级为 2 的学生信息
SELECT * FROM student WHERE gender=“男” AND class_id=2;

范围查询
– 查询班级id 1 到 3 的学生的信息
SELECT * FROM student WHERE class_id BETWEEN 1 AND 3;

判空语句
#is null 判断造成索引失效
#索引 B+ 树
SELECT * FROM student WHERE class_id IS NOT NULL; #判断不为空 SELECT * FROM student WHERE class_id IS NULL; #判断为空 SELECT * FROM student WHERE gender <> ‘’; #判断不为空字符串
SELECT * FROM student WHERE gender = ‘’; #判断为空字符串

模糊查询
– 使用 like关键字，"%"代表任意数量的字符，”_”代表占位符
– 查询名字为 m 开头的学生的信息
SELECT * FROM teacher WHERE tname LIKE ‘谢%’;
– 查询姓名里第二个字为小的学生的信息
SELECT * FROM teacher WHERE tname LIKE ‘_小%’;

分页查询
– 分页查询主要用于查看第N条 到 第M条的信息，通常和排序查询一起使用
– 使用limit关键字，第一个参数表示从条记录开始显示，第二个参数表示要显示的数目。表中默认第 一条记录的参数为0。
– 查询第二条到第三条内容
SELECT * FROM student LIMIT 1,2;

查询后排序
– 关键字：order by field, asc:升序, desc:降序
SELECT * FROM score ORDER BY num ASC;
– 按照多个字段排序
SELECT * FROM score ORDER BY course_id DESC, num DESC;

分组查询
第一个作用是去重，第二个是去合并
聚合查询

联表查询

子查询/合并查询

单行子查询
select * from course where teacher_id = (select tid from teacher where tname = ‘谢小二老师’)
多行子查询
多行子查询即返回多行记录的子查询
IN 关键字：运算符可以检测结果集中是否存在某个特定的值，如果检测成功就执行外部的查询。
EXISTS 关键字：内层查询语句不返回查询的记录。而是返回一个真假值。如果内层查询语句查询到满足条件的记录，就返回一个真值（true），否则，将返回一个假值（false）。当返回的值为true时，外层查询语句将进行查询；当返回的为false时，外层查询语句不进行查询或者查询不出任何记录。
ALL 关键字：表示满足所有条件。使用ALL关键字时，只有满足内层查询语句返回的所有结果，才可以执行外层查询语句。
ANY 关键字：允许创建一个表达式，对子查询的返回值列表，进行比较，只要满足内层子查询中的，任意一个比较条件，就返回一个结果作为外层查询条件。
在 FROM 子句中使用子查询：子查询出现在from子句中，这种情况下将子查询当做一个临时表使用。

视图

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

权限管理

触发器

触发器是否具备事务性？
数据一致性的 udf技术 c++开发
定义
触发器（trigger）是MySQL提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表
事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比
如当对一个表进行DML操作（ insert ， delete ， update ）时就会激活它执行。
4要素
监视对象： table
监视事件： insert 、 update 、 delete
触发时间： before ， after
触发事件： insert 、 update 、 delete

存储过程

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