本文详细介绍了MySQL数据库的索引类型，包括B树和B+树的区别，主键索引和辅助索引，以及聚簇和非聚簇索引的概念。还探讨了事务的ACID特性，以及事务隔离级别，强调了并发控制中的脏读、不可重复读和幻读问题。此外，讲解了索引的优化策略，如最左前缀匹配和覆盖索引，并讨论了如何防止SQL注入。最后，简述了MySQL日志系统，包括redolog、binlog和undolog的作用。
目录
结果排序（ORDER BY）：ASC：升序；DESC：降序；
三大范式：
1NF(第一范式)
属性不能再被分割。
2NF(第二范式)
在1NF的基础之上，非主属性完全依赖于主属性。比如stu_id和kc_id组成的联合主键，非主属性就要完全依赖于这两个主键，而不能只依赖于其中一个。
3NF(第三范式)
在 2NF 的基础之上，不存在传递函数依赖。ex_desc依赖于sex_code，而sex_code依赖于id(主键)，形成一种传递函数依赖，而是通过依赖于非主键列而依赖于主键，属于传递依赖。
ASCII表
MySQL 支持哪些存储引擎？默认使用哪个？
MySQL 当前默认的存储引擎是 InnoDB， MySQL 5.5 之前，MyISAM 引擎是 MySQL 的默认存储引擎。但是，MyISAM 不支持事务和行级锁，也不支持数据异常崩溃后的安全回复，不支持外键，不支持MVCC。
数据库事务：
要么全部执行成功,要么全部不执行
关系型数据库事务都支持 ACID 特性： A、I、D 是手段，C 是目的
- 原子性（
Atomicity） ： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用； - 一致性（
Consistency）： 执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的； - 隔离性（
Isolation）： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的； - 持久性（
Durability）： 一个事务被提交之后，它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响
表级锁和行级锁对比 ：
- 表级锁： MySQL 中锁定粒度最大的一种锁，对整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。但是触发锁冲突的概率最高，并发度最低，MyISAM 和 InnoDB 引擎都支持表级锁。
- 行级锁： MySQL 中锁定粒度最小的一种锁，是针对索引字段加的锁，对行记录进行加锁。 行级锁能够减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。
共享锁和排他锁：
- 共享锁（S 锁） ：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。
- 排他锁（X 锁） ：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条事务加任何类型的锁（锁不兼容）
3.3、乐观锁和悲观锁
悲观锁：假设多个线程会同时访问一个共享资源，并且这些线程会试图修改它。悲观锁的策略是假设最坏情况，即认为在任何时候都有可能有另一个线程来修改这个共享资源，因此在访问前会先锁定这个资源，以防止其他线程修改。悲观锁的典型实现是数据库中的行锁或表锁。
乐观锁：相反，乐观锁的策略是认为多个线程同时访问共享资源的概率很小，因此不需要在访问前锁定资源。相反，每次访问共享资源时，先获取一个版本号或时间戳，并在更新数据时检查这个版本号或时间戳是否被其他线程修改过。如果检查到冲突，则放弃当前操作。乐观锁的典型实现是在Java中使用的CAS（Compare And Swap）操作。
在并发控制的选择上，悲观锁一般会降低并发性，因为它会频繁地加锁和解锁资源，而乐观锁则可以更好地保持并发性，因为它只在冲突发生时才进行回滚操作。但是，乐观锁在并发更新高的情况下容易发生冲突，因为每次更新都需要检查版本号或时间戳，这会增加系统的开销。因此，在实际应用中，应该根据具体情况选择适合的并发控制策略。
乐观锁：通常应用于读多写少的情况下，竞争不激烈的数据访问场景；
悲观锁：通常应用于写多读少的情况下，竞争激烈的数据访问场景；
多个事务并发运行，并发事务带来了哪些问题
- 脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
- 丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。 例如：事务 1 读取某表中的数据 A=20，事务 2 也读取 A=20，事务 1 修改 A=A-1，事务 2 也修改 A=A-1，最终结果 A=19，事务 1 的修改被丢失。
- 不可重复读（Unrepeatable read）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
- 幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读
事务隔离级别总结
SQL 标准定义了四个隔离级别：
- READ-UNCOMMITTED(读取未提交) ： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
- READ-COMMITTED(读取已提交) ： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
- REPEATABLE-READ(可重复读) 默认： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
- SERIALIZABLE(可串行化) ： 最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。
参考：
事务分为隐式事务和显式事务两种。我们的DML语句（insert、update、delete）就是隐式事务
显示事务：该事务具有明显的开启和结束标记。使用显式事务的前提是得先把自动提交事务的功能给禁用。禁用自动提交功能就是设置autocommit变量值为0
#步骤一：开启事务（可选）
start transaction;
#步骤二：编写事务中的sql语句（insert、update、delete）
#这里实现一下"李二给王五转账"的事务过程
update t_account set balance = 50 where vname = "李二";
update t_account set balance = 130 where vname = "王五";
#步骤三：结束事务
commit; #提交事务
# rollback; #回滚事务:就是事务不执行，回滚到事务执行前的状态
-
事务的保存点（回滚点）
语法: savepoint 节点名称 ;
注意：保存点只允许搭配rollback回滚来使用，不能和commit一起使用
MySQL索引：
索引是一种用于快速查询和检索数据的数据结构，其本质可以看成是一种排序好的数据结构。相当于目录。索引比较大，往往是存储在磁盘上的文件中的，而非内存。
索引的优势和劣势：
优势：
- 可以提高数据检索的效率，类似于书的目录。
- 保证数据的唯一性。
- 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。
- 被索引的列会自动进行排序，包括【单列索引】和【组合索引】，只是组合索引的排序要复杂一些。
劣势：
- 索引会占据磁盘空间
- 创建索引和维护索引需要耗费许多时间，降低效率。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要进行修改。
Hash 索引不支持顺序和范围查询。
B树和B+树：
B树的插入操作。
- 若该节点元素个数小于m-1，直接插入；
- 若该节点元素个数等于m-1，引起节点分裂；以该节点中间元素为分界，取中间元素（偶数个数，中间两个随机选取）插入到父节点中；
（参考：B树、B+树详解_KuoGavin的博客-CSDN博客_b树和b+树）
B树的删除：
- 某结点中元素数目小于（m/2）-1,(m/2)向上取整，则需要看其某相邻兄弟结点是否丰满；
- 如果丰满（结点中元素个数大于(m/2)-1），则向父节点借一个元素来满足条件；
- 如果其相邻兄弟都不丰满，即其结点数目等于(m/2)-1，则该结点与其相邻的某一兄弟结点进行“合并”成一个结点
B树的不足：
不支持范围查找。
B+树：
- B 树的所有节点既存放键(key) 也存放 数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
- B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链，将叶子节点连接起来。
- B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。
- B树的所有索引值是不会重复的，而B+树 非叶子结点的索引值 最终一定会全部出现在 叶子结点中
索引类型：
主键索引(Primary Key)
数据表的主键列使用的就是主键索引。
一张数据表有只能有一个主键，并且主键不能为 null，不能重复。
在 MySQL 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引且不允许存在 null 值的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。
二级索引(辅助索引)
二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。即使用普通属性建立辅助索引。
以下都属于二级索引：
- 唯一索引(Unique Key) ：唯一索引的属性列不能出现重复的数据，保证数据的唯一性，但是允许数据为 NULL，一张表允许创建多个唯一索引。
- 普通索引(Index) ：为了能够快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。
- 前缀索引(Prefix) ：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小， 因为只取前几个字符。
- 全文索引(Full Text) ：全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。
聚簇索引
聚簇索引即索引结构和数据一起存放的索引，并不是一种单独的索引类型。InnoDB 中的主键索引就属于聚簇索引。
优点 ：
- 查询速度非常快 ：聚簇索引的查询速度非常的快，叶子节点包括索引和数据，定位到索引的节点，就相当于定位到了数据。相比于非聚簇索引， 聚簇索引少了一次读取数据的 IO 操作。
- 对排序查找和范围查找优化 ：聚簇索引对于主键的排序查找和范围查找速度非常快。
缺点：
- 依赖于有序的数据 ：如果索引的数据不是有序的，那么就需要在插入时排序。
- 更新代价大 ：当要修改索引列的数据时， 那么对应的索引也将会被修改，而且聚簇索引的叶子节点还存放着数据，修改代价比较大
非聚簇索引
非聚簇索引即索引结构和数据分开存放的索引。二级索引就属于非聚簇索引
优点 ：
更新代价比聚簇索引要小，因为叶子节点存放的数据是主键 。
缺点：可能需要二次查询，即第一次找到主键，然后通过主键索引查到数据。当查询的字段建立了索引时，不需要二次查询。
组合索引：
最左匹配原则：
使用组合索引查询时，选择最左匹配原则，即先匹配第一个，如果相等，则匹配第二个
覆盖索引：
如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”
。SQL只需要通过索引就可以返回查询所需要的数据，不做回表操作！
联合索引：
但在我们需要多条件查询的时候，就可以建立联合索引，来提高我们的查询效率。使用多个字段同时建立一个索引,
非叶子节点存储完整的索引关键字信息，排列规则和叶子节点一致，叶子节点data 部分存储的是联合索引所在行的主键值。
- 拿到联合索引所在行的主键值后，在通过主键索引
B+ Tree就可以直接拿到具体的行数据了
最左前缀匹配原则
比如两个字段A,B建立了联合索引，当我们有查询条件时，就先判断A，如果A相等，再判断B
索引下推
- 在 MySQL 5.6 之前，只能从匹配的位置一个个回表。到主键索引上找出数据行，再对比字段值
- 在 MySQL 5.6 中引入的索引下推优化 (index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数
MyIsam索引
以一个简单的user表为例。user表存在两个索引，id列为主键索引，age列为普通索引
CREATE TABLE `user`
(
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(20) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
KEY `idx_age` (`age`) USING BTREE
) ENGINE = MyISAM
AUTO_INCREMENT = 1
DEFAULT CHARSET = utf8;
MyISAM的数据文件和索引文件是分开存储的。MyISAM使用B+树构建索引树时，叶子节点中存储的键值为索引列的值，数据为索引所在行的磁盘地址。
表user的索引存储在索引文件user.MYI中，数据文件存储在数据文件 user.MYD中。
在 MyISAM 中,辅助索引和主键索引的结构是一样的，没有任何区别，叶子节点的数据存储的都是行记录的磁盘地址。只是主键索引的键值是唯一的，而辅助索引的键值可以重复。
InnoDB索引
//创建索引
create index emp_idx（索引名） on emp（表名） (job,ename);（字段）每个InnoDB表都有一个聚簇索引 ，聚簇索引使用B+树构建，叶子节点存储的数据是整行记录。一般情况下，聚簇索引等同于主键索引，当一个表没有创建主键索引时，InnoDB会自动创建一个ROWID字段来构建聚簇索引。
InnoDB的数据和索引存储在一个文件t_user_innodb.ibd中。InnoDB的数据组织方式，是聚簇索引。
主键索引的叶子节点会存储数据行，辅助索引只会存储主键值。
辅助索引会触发回表机制，进行回表的磁盘io次数。
如何避免频繁的回表操作，使用联合索引。
索引失效
对索引使用左或者左右模糊匹配
当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx% 这两种方式都会造成索引失效。
// name 字段为二级索引
select * from t_user where name like '%林';对索引使用函数，会失效
// name 为二级索引
select * from t_user where length(name)=6;因为索引保存的是索引字段的原始值，而不是经过函数计算后的值，自然就没办法走索引了。
不过，从 MySQL 8.0 开始，索引特性增加了函数索引，即可以针对函数计算后的值建立一个索引，也就是说该索引的值是函数计算后的值，所以就可以通过扫描索引来查询数据。‘
alter table t_user add key idx_name_length ((length(name)));对索引进行表达式计算
explain select * from t_user where id + 1 = 10;对索引隐式类型转换
如果索引字段是字符串类型，但是在条件查询中，输入的参数是整型的话，你会在执行计划的结果发现这条语句会走全表扫描。
//phone 是varchar类型
select * from t_user where phone = 1300000001;MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。
//上述查询会转化为这个，相当于使用了函数
select * from t_user where CAST(phone AS signed int) = 1300000001;联合索引不符合最左前缀匹配
查询有range（范围）时, age>18 ，会导致索引失效
is null 会使用索引 ， is not null 不会使用索引
WHERE 子句中的 OR
select * from t_user where id = 1 or age = 18;要解决办法很简单，将 age 字段设置为索引即可。
查看索引走了全表还是索引：
用explain关键字：
1.创建一个表
2.此时主键（id）默认索引，创建一个新的phone_index索引
3.假设索引失效的情况，隐式转换，表是varchar类型，而查询参数是int
explain SELECT * FROM stu WHERE phone = 16673202222
4.传入String类型的参数
explain SELECT * FROM stu WHERE phone = '16673202222'
explain函数关键字解释
table：顾名思义，显示这一行的数据是关于哪张表的；
type：走的是否是索引，这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为：const、eq_reg、ref、range、indexhe和ALL；
possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从where语句中选择一个合适的语句；
key： 实际使用的索引列。如果为空，则没有使用索引。
key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好；
ref：显示索引的哪一列被使用了，如果可能的话，是一个常数；
rows：MySQL认为必须检查的用来返回请求数据的行数；
Extra：关于MySQL如何解析查询的额外信息
总结
- 当我们使用左或者左右模糊匹配的时候，也就是
like %xx或者like %xx%这两种方式都会造成索引失效； - 当我们在查询条件中对索引列使用函数，就会导致索引失效。
- 当我们在查询条件中对索引列进行表达式计算，也是无法走索引的。
- MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。如果字符串是索引列，而条件语句中的输入参数是数字的话，那么索引列会发生隐式类型转换，由于隐式类型转换是通过 CAST 函数实现的，等同于对索引列使用了函数，所以就会导致索引失效。
- 联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
- 在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。
索引优化
引入了索引下推优化，可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数
MySQL高级篇（SQL优化、索引优化、锁机制、主从复制）_mysql sql优化_袁袁袁袁满的博客-CSDN博客
如果查询有range时，会导致索引失效，此时应当删除该字段的索引
-
当comments字段在联合索引里处于中间位置时，因comments > 1条件是一个范围值(所谓range)，MySQL无法利用索引再对后面的views部分进行检索，即
range类型查询字段后面的索引无效。
关联查询时，
数据量大的表放在被驱动表，小的放在驱动表
左连接，左表一般都会全表查询，因此进行右表加索引同理：右连接，左表加索引
建立覆盖索引或者联合索引
MySQL日志：
常用的有redo log（重做日志）、binlog（归档日志）、两阶段提交、undo log （回滚日志）
redo log
是InnoDB存储引擎独有的，它让MySQL拥有了崩溃恢复能力。
- 当我们查询数据库记录时，会从硬盘把一页的数据加载出来，加载出来的数据叫数据页，会放入到
Buffer Pool（缓冲池）中。 - 后续的查询都是先从
Buffer Pool中找，没有命中再去硬盘加载，减少硬盘IO开销，提升性能。 - 更新表数据的时候，也是如此，发现
Buffer Pool里存在要更新的数据，就直接在Buffer Pool里更新。 - 然后会把“在某个数据页上做了什么修改”记录到重做日志缓存（
redo log buffer）里，接着刷盘到redo log文件里。
binlog
redo log 是物理日志，记录的是具体的修改内容，属于 InnoDB 存储引擎。而 binlog 是逻辑日志，记录内容是语句的原始逻辑，属于MySQL Server 层。binlog会记录所有涉及更新数据的逻辑操作，并且是顺序写。MySQL数据库的数据备份、主备、主主、主从都离不开binlog，需要依靠binlog来同步数据，保证数据一致性。
记录格式：
binlog 日志有三种格式，可以通过binlog_format参数指定。
- statement
- row
- mixed
指定为statement，记录的内容是SQL语句原文；但是存在问题，如获取当前系统的时间，在我们同步的时候就会不一致。
指定为row，记录的内容包括sql语句和操作的具体数据。（但是这种格式，比较占用空间，恢复与同步时会更消耗IO资源，影响执行速度。）
指定为mixed，MySQL会判断这条SQL语句是否可能引起数据不一致，如果是，就用row格式，否则就用statement
undo log
所有事务进行的修改都会先记录到这个回滚日志中，然后再执行相关的操作。如果执行过程中遇到异常的话，我们直接利用 回滚日志 中的信息将数据回滚到修改之前的样子。并且，回滚日志会先于数据持久化到磁盘上。这样就保证了即使遇到数据库突然宕机等情况，当用户再次启动数据库的时候，数据库还能够通过查询回滚日志来回滚将之前未完成的事务。
redo log(重做日志) 保证事务的持久性，使用 undo log(回滚日志) 来保证事务的原子性。
MySQL 基础架构
- 连接器： 身份认证和权限相关(登录 MySQL 的时候)。
- 查询缓存： 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。
- 分析器： 没有命中缓存的话，SQL 语句就会经过分析器，分析SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
- 优化器： 按照 MySQL 认为最优的方案去执行。
- 执行器： 执行语句，然后从存储引擎返回数据。
（图源自JavaGuide）
- Server 层：包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binlog 日志模块。
- 存储引擎： 主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB（默认）、MyISAM、Memory 等多个存储引擎，
查询语句的执行过程：
权限校验（如果命中缓存）--->查询缓存--->分析器（分析sql语句要干嘛，是否符合规范）--->优化器（选择最优的方案）--->权限校验（如果没有权限就会返回错误信息，如果有权限就会调用数据库引擎接口）--->执行器--->引擎
更新语句的执行过程：
- 先查询到张三这一条数据，如果有缓存，也是会用到缓存。
- 然后拿到查询的语句，把 age 改为 19，然后调用引擎 API 接口，写入这一行数据，InnoDB 引擎把数据保存在内存中，同时记录 redo log，此时 redo log 进入 prepare 状态，然后告诉执行器，执行完成了，随时可以提交。
- 执行器收到通知后记录 binlog，然后调用引擎接口，提交 redo log 为提交状态。
- 更新完成。
为什么要有两个日志：
- 先写 redo log 直接提交，然后写 binlog，假设写完 redo log 后，机器挂了，binlog 日志没有被写入，那么机器重启后，这台机器会通过 redo log 恢复数据，但是这个时候 binlog 并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。
- 先写 binlog，然后写 redo log，假设写完了 binlog，机器异常重启了，由于没有 redo log，本机是无法恢复这一条记录的，但是 binlog 又有记录，那么和上面同样的道理，就会产生数据不一致的情况。
MySQL语句：
增删查改
// 1、插入完整数据记录
INSERT INTO t_student (name, email, age) VALUES ("xiaoming", "xiao@qq.com", 16);
// 2、插入部分数据记录
INSERT INTO t_student (name, age) VALUES ("xiaoming", 16);
//删除
DELETE FROM t_student WHERE id = 2;
//查
SELECT name,email,age FROM t_student;
//改
UPDATE t_student SET name="独孤求败" WHERE name="张三";
设置别名：
// 第一种
SELECT 列名 AS 别名 FROM 表名 [WHERE];
// 第二种
SELECT 列名 别名 FROM 表名 [WHERE];
// 查询所有货物的id，名称和折扣价价（折扣价=销售价*折扣）(使用别名）
SELECT id AS s From product;
消除重复元素：
SELECT DISTINCT 列名, ... FROM 表名;
逻辑运算符（AND、OR、NOT）
// 查询售价在300-400（包括300和400）的货品信息
SELECT * FROM product WHERE salePrice >= 300 ADN salePrice <= 400;
// 查询分类编号为2, 4的所有货品信息
SELECT * FROM product WHERE dir_id = 2 OR dir_id = 4;
// 查询编号不为2的所有商品信息
SELECT * FROM product WHERE NOT dir_id = 2
范围匹配：BETWEEN AND
// 使用的是闭区间，也就是包括minValue 和 maxValue
WHERE 列名 BETWEEN minValue AND maxValue;
// 查询零售价不在 300 - 400 之间的货品信息
SELECT * FROM product WHERE NOT salePrice BETWEEN 300 AND 400;
集合查询：IN
// 查询分类编号为 2，4 的所有货品的 id，货品名称
SELECT id, productName FROM product WHERE dir_id IN (2,4);
// 查询分类编号不为 2, 4 的所有货品的 id，货品名称
SELECT id, dir_id, productName FROM product WHERE NOT dir_id IN (2,4);
判空（IS NULL)
// 查询商品名为NULL的所有商品信息
SELECT * FROM product WHERE productName IS NULL;
SELECT * FROM product WHERE supplier = "";
模糊匹配查询（LIKE，%，_）
// 查询货品名称以 罗技M9* 结尾的所有货品信息，这里的 * 表示一个任意字符，
SELECT * FROM product WHERE productName LIKE "%罗技M9_";
结果排序（ORDER BY）：ASC：升序；DESC：降序；
// 按分类编号降序排序，如果分类编号相同再按零售价升序排序
SELECT * FROM product ORDER BY dir_id DESC, salePrice ASC;
分组查询（GROUP BY）：一般与having一起用
-- 根据gender字段来分组
select gender from students group by gender;
-- 根据name和gender字段进行分组
select name, gender from students group by name, gender;
// group_concat(字段名): 统计每个分组指定字段的信息集合，每个信息之间使用逗号进行分割
-- 根据gender字段进行分组， 查询gender字段和分组的name字段信息
select gender,group_concat(name) from students group by gender;
//聚合函数
-- 统计不同性别的人的平均年龄
select gender,avg(age) from students group by gender;
-- 统计不同性别的人的个数
select gender,count(*) from students group by gender;
//having作用和where类似都是过滤数据的，但having是过滤分组数据的，只能用于group by
-- 根据gender字段进行分组，统计分组条数大于2的
select gender,count(*) from students group by gender having count(*)>2;
//with rollup的作用是：在最后记录后面新增一行，显示select查询时聚合函数的统计和计算结果
-- 根据gender字段进行分组，汇总总人数
select gender,count(*) from students group by gender with rollup;
-- 根据gender字段进行分组，汇总所有人的年龄
select gender,group_concat(age) from students group by gender with rollup;1）COUNT(*)：统计表中有多少条数据；
2）SUM(列)：汇总列的总和；
3）MAX(列)：获取某一列的最大值；
4）MIN(列)：获取某一列的最小值；
5）AVG(列)：获取某一列的平均值；
sql注入：
Sql 注入攻击是通过将恶意的 Sql 语句插入到应用的输入参数中，导致其传入的“数据”拼接到SQL语句中后，被当作SQL语句的一部分执行，导致数据库被攻击。
常见的sql注入：登陆注入
当我们输入正确的用户名和密码才能够正确登录，但是如果尝试在用户名中输入 123' or 1=1 #, 密码同样输入 123' or 1=1 # ：此时sql语句为
-- # 后面的内容会被忽略
select * from users where username='123' or 1=1 #' and password='123' or 1=1 #'
就可以绕过验证，登录数据库。
如何防止sql注入：
- 对输入进行严格的转义和过滤。
- 使用参数化（Parameterized）：将用户输入的数据并不直接内嵌到SQL语句中，而通过参数来进行传输，目前有很多ORM框架会自动使用参数化解决注入问题，但其也提供了"拼接"的方式,所以使用时需要慎重!
- PDO预处理 (Java、PHP防范推荐方法：)如果进行了PDO预处理的SQL，会让MYSQL自己进行拼凑，就算夹带了危险的SQL语句，也不会进行处理只会当成参数传进去，而不是以拼接进SQL语句传进去，从而防止了SQL注入。
MVCC
MVCC，全称 Multi-Version Concurrency Control ，即多版本并发控制。实现对数据库的并发访问，提高数据库并发性能，用更好的方式去处理读-写冲突。以往数据库智能采用悲观锁这样性能不佳的形式去解决读-写冲突问题，有了MVCC，提高性能。
「维持一个数据的多个版本，使得读写操作没有冲突」 。
undo log 为每条记录保存多份历史数据，MySQL 在执行快照读（普通 select 语句）的时候，会根据事务的 Read View 里的信息，顺着 undo log 的版本链找到满足其可见性的记录。
当前读
像 select lock in share mode (共享锁), select for update; update; insert; delete (排他锁)这些操作都是一种当前读，它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。
快照读
像不加锁的 select 操作就是快照读，快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；这样做是为了提高并发性能，快照读的实现是基于多版本并发控制，即 MVCC 。
在mysql存储的数据中，除了我们显式定义的字段，mysql会隐含的帮我们定义几个字段。
-
trx_id:事务id，每进行一次事务操作，就会自增1。
-
roll_pointer:回滚指针，用于找到上一个版本的数据，结合undolog进行回滚。
undo_log
Read View
当我们某个事务执行快照读的时候，对该记录创建一个 Read View 读视图，把它比作条件用来判断当前事务能够看到哪个版本的数据，通过readview我们才知道自己能够读取哪个版本。
扫一扫
2176
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
