4.1.1 MySQL语句，索引，视图，存储过程

Linux C/C++服务器

MySQL语句，索引，视图，存储过程

MySQL存储模型：所有数据都在磁盘中，只有少部分高频数据会在内存中，查询存储数据结构主要使用B+树
MySQL网络模型：IO多路复用select+阻塞的io ，命令处理为多线程并发处理的模式，默认并发线程最大数151个，使用短连接的网络方式(长时间不使用会断开这条连接)

Connectors：mysql客户端
Connection Pool：连接池，管理缓冲用户连接、用户名、密码、权限校验、线程处理等需要缓存的需求；
SQL Interface：SQL语句（DML，DDL）生成语法树
Parser：过滤器，SQL语法树内表是否存在等等
Optimizer：优化器，制定多个SQL语句的执行计划，选择最优执行
Caches & Buffers：少量热点数据（最近操作的数据）缓存
Enterprise Management Service & Utilities: 数据备份、恢复、安全验证、主从复制、集群、分区、实力管理等管理模块
InnoDB：存储引擎

数据库设计三范式以及反范式

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

范式一

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

范式二

确保表中的每列都和主键相关，而不能只与主键的某一部分相关（组合索引）；

上表拆分为下边三张表，减少冗余存储

范式三

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

反范式

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

CRUD

执行过程

SQL语句

CREATE DATABASE `数据库名` DEFAULT CHARACTER SET utf8;  --创建数据库
DROP DATABASE `数据库名`;  --删除数据库
USE `数据库名`;  --选择数据库

-- 创建表
CREATE TABLE `table_name` (column_name column_type);

CREATE TABLE IF NOT EXISTS `0voice_tbl` (
`id` INT UNSIGNED AUTO_INCREMENT COMMENT '编号', `course` VARCHAR(100) NOT NULL COMMENT '课程',
`teacher` VARCHAR(40) NOT NULL COMMENT '讲师', `price` DECIMAL(8,2) NOT NULL COMMENT '价格', PRIMARY KEY ( `id` )
)ENGINE=innoDB DEFAULT CHARSET=utf8 COMMENT = '课程表';
--删除表
DROP TABLE `table_name`;
--清空数据库
TRUNCATE TABLE `table_name`; -- 截断表 以页为单位（至少有两行数据），有自增索引的话，从初始值开始累加
DELETE TABLE `table_name`; -- 逐行删除，有自增索引的话，从之前值继续累加
--增
INSERT INTO `table_name`(`field1`, `field2`, ..., `fieldn`) VALUES (value1, value2, ..., valuen);
INSERT INTO `0voice_tbl` (`course`, `teacher`, `price`) VALUES ('C/C++Linux服务器开发/高级架构师', 'Mark', 7580.0);
--删
DELETE FROM `table_name` [WHERE Clause];
DELETE FROM `0voice_tbl` WHERE id = 3;
--改
UPDATE table_name SET field1=new_value1, field2=new_value2 [, fieldn=new_valuen] UPDATE `0voice_tbl` SET `teacher` = 'Mark' WHERE id = 2;
-- 累加
UPDATE `0Voice_tbl` set `age` = `age` + 1 WHERE id = 2;
-- 查
SELECT field1, field2,...fieldN FROM table_name [WHERE Clause]

高级查询

准备

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;
-- innoDB 有外键约束 myisam 注释的作用
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 sum(`num`) FROM `score`;
SELECT avg(`num`) FROM `score`;
SELECT max(`num`) FROM `score`;
SELECT min(`num`) FROM `score`;
SELECT count(`num`) FROM `score`;

分组查询

-- GROUP BY（分组会去重），加group_concat会把age逗号隔开连接起来
SELECT `gender`, group_concat(`age`) as ages FROM `student` GROUP BY `gender`;
-- 可以把查询出来的结果根据某个条件来分组显示 
SELECT `gender` FROM `student` GROUP BY `gender`;
-- 分组加聚合
SELECT `gender`, count(*) as num FROM `student` GROUP BY `gender`;
-- 分组加条件
SELECT `gender`, count(*) as num FROM `student` GROUP BY `gender` HAVING num > 6;

联表查询

-- INNER JOIN 只取两张表有对应关系的记录
SELECT cid FROM `course` INNER JOIN `teacher` ON course.teacher_id = teacher.tid;

-- LEFT JOIN 在内连接的基础上保留左表没有对应关系的记录
SELECT course.cid FROM `course` LEFT JOIN `teacher` ON course.teacher_id = teacher.tid;

-- RIGHT JOIN 在内连接的基础上保留右表没有对应关系的记录
SELECT course.cid FROM `course` RIGHT JOIN `teacher` ON course.teacher_id = teacher.tid;

子查询/合并查询

单行子查询

select * from course where teacher_id = (select tid from teacher where tname = '谢小二老师')

多行子查询

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

select * from student where class_id in (select cid from course where teacher_id = 2);
select * from student where exists(select cid from course where cid = 5);

SELECT
  student_id,
  sname
FROM
  (SELECT * FROM score WHERE course_id = 1 OR course_id = 2) AS A
    LEFT JOIN student ON A.student_id = student.sid;

视图

定义

视图（ view）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。其内容由查询定义。
基表：用来创建视图的表叫做基表；
通过视图，可以展现基表的部分数据；
视图数据来自定义视图的查询中使用的表，使用视图动态生成；

优点

简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

语法

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

-- 创建视图
-- 查询“c++高级”课程比“音视频”课程成绩高的所有学生的学号；
CREATE VIEW view_test1 AS SELECT A.student_id FROM (
    SELECT student_id, num FROM score WHERE course_id = 1
) AS A -- 12
LEFT JOIN (
SELECT student_id, num FROM score WHERE course_id = 2
) AS B -- 11
ON A.student_id = B.student_id WHERE A.num > IF (isnull(B.num), 0, B.num);

流程控制

IF、CASE、WHILE、LEAVE、ITERATE、LOOP、REPEAT