MYSQL语句，索引，视图，存储过程，触发器（一）
目录
1.初识MySQL
数据库
OLTP（On-Line transaction processing）
OLAP（On-Line Analytical Processing）
SQL
数据库术语
2.MySQL 体系结构
3.数据库设计三范式
一范式
二范式
三范式
反范式
4.CRUD
五大约束
数据库·表基本操作
增
删
改
基础查询
条件查询
范围查询
判空查询
模糊查询
分页查询
查询后排序
聚合查询
分组查询
联表查询
1.INNER JOIN
2.LEFT JOIN
3.RIGHT JOIN
子查询
合并查询
5.视图
优点
语法
作用
6.预处理语句
7.触发器
NEW 和 OLD
8.存储过程
1.初识MySQL
数据库
按照数据结构来组织、存储和管理数据的仓库；是一个长期存储在计算机内的、有组织的、可共享的、统一管理的大量数据的集合；
OLTP（On-Line transaction processing）
OLTP 主要用来记录某类业务事件的发生；数据会以增删改的方式在数据库中进行数据的更新处理操作，要求实时性高、稳定性强、确保数据及时更新成功；
- 面向大量短小事务
- 注重响应速度和并发性能
- 常用于银行，电商系统
OLAP（On-Line Analytical Processing）
当数据积累到一定的程度，我们需要对过去发生的事情做一个总结分析时，就需要把过去一段时间内产生的数据拿出来进行统计分析，从中获取我们想要的信息；
- 面向复杂查询，数据分析
- 关注报表，统计，多维分析
- 常用于数据仓库
SQL
结构化查询语言(Structured Query Language) 简称 SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。SQL 是关系数据库系统的标准语言
| 类型 | 全称 | 作用 | 关键字 |
| DQL | Data Query Language | 数据查询 | SELECT |
| DML | Data Manipulation Language | 数据操作 | INSERT，UPDATE，DELETE |
| DDL | Data Definition Language | 定义数据库结构 | CREATE，DROP，ALTER |
| DCL | Data Control Language | 权限控制 | GRANT，REVOKE |
| TCL | Transcation Control Language | 事务管理 | COMMIT，ROLLBACK，SAVEPOINT |
数据库术语
- 数据库：数据库是一些关联表的集合；
- 数据表：表是数据的矩阵；
- 列：一列包含相同类型的数据；
- 行：或者称为记录是一组相关的数据；
- 主键：主键是唯一的；一个数据表只能包含一个主键；
- 外键：外键用来关联两个表，来保证参照完整性；MyISAM 存储引擎本身并不支持外键，只起到注
- 释作用；而 innoDB 完整支持外键；
- 复合键：或称组合键；将多个列作为一个索引键；
- 索引：用于快速访问数据表的数据；索引是对表中的一列或者多列的值进行排序的一种结构；
2.MySQL 体系结构
- 连接池组件：管理数据库连接。
- SQL接口组件：解析 SQL 语句。
- 查询分析器组件：将 SQL 拆解为执行计划。
- 优化器组件：对执行计划进行优化。
- 缓冲组件：缓存数据页，加快查询速度。
- 插件式存储引擎：如 InnoDB、MyISAM 等，负责实际数据操作。
- 物理文件：存储表数据、日志文件、索引等。
- 管理服务和工具组件：运维层面用于监控、配置、管理。
| 属性 | Redo Log（重做日志） | Undo Log（回滚日志） |
| 主要作用 | 崩溃恢复、持久化保障 | 事务回滚、MVCC支持 |
| 写入时机 | 修改后、提交前 | 修改前 |
| 是否落盘 | 会立即落盘 | 通常保存在内存或表空间中 |
| 影响性能 | 大量写操作需优化 | 回滚/快照读需频繁访问 |
| 示例操作 | crash-recovery | rollback, consistent read |
| 类比 | 黑匣子：记录“我做了什么”，飞行器崩溃后可以“重做” | 撤销按钮：随时能“撤销上一步操作”，方便回滚 |
3.数据库设计三范式
为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式。
一范式
确保每列保持原子性；数据库表中的所有字段都是不可分解的原子值；
二范式
满足范式一的基础上，确保表中的每列都和主键完全依赖，而不能只与主键的某一部分依赖（组合索引）；
三范式
满足范式二的基础上，确保每列都和主键直接相关，而不是间接相关；减少数据冗余；
反范式
范式可以避免数据冗余，减少数据库的空间，减小维护数据完整性的麻烦；但是采用数据库范式化设计，可能导致数据库业务涉及的表变多，并且造成更多的联表查询，将导致整个系统的性能降低；因此基于性能考虑，可能需要进行反范式设计；
4.CRUD
五大约束
- 非空约束：限制字段不能为空
- 自增约束：数字列值自动递增，常用于主键（一张表只能有一个 AUTO_INCREMENT 字段）
- 唯一约束： 限制列值唯一，不允许重复
- 主键约束（PRIMARY KEY）：唯一标识每一行，隐含 NOT NULL 和 UNIQUE
- 外键约束： 实现表与表之间的关联（引用其他表的主键），但是要保证引用数据存在
数据库·表基本操作
//创建数据库//默认字符集为 utf8
CREATE DATABASE `数据库名` DEFAULT CHARACTER SET utf8; # 字符集设置为 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` ), ## not null unique
)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 ('相声', 'word', 7580.0)删
DELETE FROM `table_name` [WHERE Clause];
DELETE FROM `abd` 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 * 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聚合查询
| 聚合函数 | 描述 |
| sum() | 计算某列的总和 |
| avg() | 计算某列的平均值 |
| max() | 计算某列的最大值 |
| min() | 计算某列的最小值 |
| count() | 计算某列的行数 |
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_concat
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;联表查询
1.INNER JOIN
--内连接：两边都要有
SELECT
cid
FROM
`course`
INNER JOIN `teacher` ON course.teacher_id = teacher.tid;
2.LEFT JOIN
--左连接：保留左边全部
SELECT users.name, roles.role_name
FROM users
LEFT JOIN roles
ON users.role_id = roles.id;3.RIGHT JOIN
--右连接：保留右边全部
SELECT users.name, roles.role_name
FROM users
RIGHT JOIN roles
ON users.role_id = roles.id;子查询
SELECT * FROM users
WHERE id IN (SELECT user_id FROM ordersWHERE total > 100
);
--子查询常用于 动态过滤 和 嵌套聚合
--可以在 SELECT、FROM、WHERE、HAVING 等位置使用
--尽量避免子查询嵌套太深，效率可能较低
合并查询
SELECT name FROM users
UNION --UNION：去重合并结果
SELECT name FROM admins;SELECT name FROM users
UNION ALL--不去重，效率更高
SELECT name FROM admins;5.视图
视图（ view ）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。其内容由查询定义。基表：用来创建视图的表叫做基表；
优点
- 简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
- 安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
- 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。
语法
CREATE VIEW <视图名> AS <SELCET 语句>作用
- 可复用，减少重复语句书写；类似程序中函数的作用；
- 重构利器
- 逻辑更清晰，屏蔽查询细节，关注数据返回；
- 权限控制，某些表对用户屏蔽，但是可以给该用户通过视图来对该表操作；
6.预处理语句
预处理语句 = SQL模板 + 参数绑定
它将 SQL 拆为两个阶段：
Prepare：只编译一次 SQL 模板
Execute：每次执行时只传递参数，提高性能 & 防注入
优点：
- 减少重复解析和编译
- 防止sql注入，避免拼接字符串导致sql注入
7.触发器
触发器是在对表执行 INSERT、UPDATE、DELETE 操作时自动触发的事件
CREATE TRIGGER trigger_name
AFTER INSERT ON table_name
FOR EACH ROW
BEGIN-- 触发时执行的 SQL
END;| 用途 | 描述 |
| 自动记录日志 | 例如用户插入，更新，删除操作 |
| 数据同步 | 多表之间的数据同步 |
| 自动校验 | 在插入/更新前进行校验和格式化处理 |
NEW 和 OLD
在 INSERT 型触发器中， NEW 用来表示将要（ BEFORE ）或已经（ AFTER ）插入的新数据；
在 DELETE 型触发器中， OLD 用来表示将要或已经被删除的原数据；在 UPDATE 型触发器中， OLD 用来表示将要或已经被修改的原数据， NEW 用来表示将要或已经修改为的新数据
8.存储过程
SQL 语句需要先编译然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的 SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。
特点
- 能完成较复杂的判断和运算进行有限的编程
- 可编程行强，灵活
- SQL 编程的代码可重复使用
- 执行的速度相对快一些
- 减少网络之间的数据传输，节省开销
语法
CREATE PROCEDURE 过程名([[IN|OUT|INOUT] 参数名 数据类型[,[IN|OUT|INOUT] 参数名 数据类
型…]]) [特性 ...] 过程体 存储过程根据需要可能会有输入、输出、输入输出参数，如果有多个参数用 "," 分割开。
MySQL 存储过程的参数用在存储过程的定义，共有三种参数类型 IN , OUT , INOUT 。
IN ：参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，可以设置
默认值
OUT ：该值可在存储过程内部被改变，并可返回
INOUT ：调用时指定，并且可被改变和返回
过程体的开始与结束使用 BEGIN 与 END 进行标识。
案例
//INDELIMITER //
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//OUTDELIMITER //
CREATE PROCEDURE proc_out_param(OUT p_out int)
BEGIN
SELECT p_out;
SET p_out=2;
SELECT p_out;
END;
//
DELIMITER ;
-- 调用
SET @p_out=1;
CALL proc_out_param(@p_out);
SELECT @p_out; -- 2//INOUTDELIMITER //
CREATE PROCEDURE proc_inout_param(INOUT p_inout int)
BEGIN
SELECT p_inout;
SET p_inout=2;
SELECT p_inout;
END;
//
DELIMITER ;
#调用
SET @p_inout=1;
CALL proc_inout_param(@p_inout) ;
SELECT @p_inout; -- 2