文章目录一、参考文献二、基本格式三、基本操作3.1 插入3.2 查询3.3 更新3.4 删除3.4.1 delete3.4.2 drop3.4.3 truncate四、进阶操作4.1 操作符like、通配符4.2 联合表操作4.2.1 举例4.3 嵌套操作4.4 SQL常用函数五、数据库索引六、执行查询语句期间发生了什么6.1 MySQL 的两层架构6.1.1 Server 层6.1.2 存储引擎层1 Memory2 MylSAM3 InnoDB6.2 详解InnoDB存储引擎6.2.1 Buffer Pool 缓冲池6.2.2 undo 日志文件6.2.3 redo 日志文件6.2.4 bin log文件6.2.5 后台线程一、参考文献参考菜鸟教程二、基本格式select * from表名left join表名xon条件1where条件2group by … having … order by …执行顺序from _where __group by _ 对结果集进行分组having __主要和GROUP BY子句配合使用用于过滤聚合值select 查看结果集中的哪个列或列的计算结果DISTINCT去重order by __LIMIT举例从多个班级中选出这些条件的班级——数学平均成绩大于75分、平均成绩按从高到低排名最前三的班级。SQLselect 班级, avg(数学成绩) as 数学平均成绩 where 数学成绩 is not null group by 班级 having 数学平均成绩 75 order by 数学平均成绩 desc limit 0, 3。执行步骤执行 FROM 子句, 从学生成绩表中组装数据源的数据。执行 WHERE 子句, 筛选学生成绩表中所有学生的数学成绩不为 NULL 的数据 。执行 GROUP BY 子句, 把学生成绩表按 “班级” 字段进行分组。计算 avg 聚合函数, 按group by的班级分组求出 数学平均成绩。执行 HAVING 子句, 筛选出班级 数学平均成绩大于 75 分的。执行SELECT语句选择数据继续执行后面几个步骤。执行 ORDER BY 子句, 把最后的结果按 “数学平均成绩” 进行排序。执行LIMIT 限制仅返回3条数据。结合ORDER BY 子句即返回所有班级中数学平均成绩的前三的班级及其数学平均成绩。三、基本操作3.1 插入INSERT INTO table_name (column_name1,column_name2,…) VALUES (value1,value2,…)3.2 查询查询某些字段SELECT column_name1,column_name2 FROM table_name;3.3 更新UPDATE table_name SET column1value1,column2value2,… WHERE some_columnsome_value;3.4 删除3.4.1 deletedelete语句执行删除的过程是从表中删除行并且同时将行删除操作作为事务记录在日志中保存以便进行进行回滚操作。请注意添加where如果省略了 WHERE 子句所有的记录都将被删除例如DELETE FROM table_name WHERE some_columnsome_value;3.4.2 dropdrop会删除内容和定义释放空间。即把整个表去掉以后要新增数据是不可能的只能新增一个表。drop语句将删除表的结构被依赖的约束constrain)、触发器trigger)、索引index)依赖于该表的存储过程/函数将被保留但其状态会变为invalid。drop table 表名称 eg: drop table dbo.Sys_Test3.4.3 truncatetruncate (清空表中数据)不删除定义保留表的数据结构、删除内容、释放空间、重置主键/自动增长列计数器。与drop不同truncate 只是清空表数据。注意truncate只能清空表数据不能删除指定行数据。truncate table 表名称比如runcate table dbo.Sys_Test四、进阶操作4.1 操作符like、通配符like1选取 name 以字母 “G” 开始的所有客户SELECT * FROM Websites WHERE name LIKE ‘G%’;2选取 name 以字母 “k” 结尾的所有客户SELECT * FROM Websites WHERE name LIKE ‘%k’;3选取 name 包含模式 “oo” 的所有客户SELECT * FROM Websites WHERE name LIKE ‘%oo%’;4选取 name 不包含模式 “oo” 的所有客户SELECT * FROM Websites WHERE name NOT LIKE ‘%oo%’;通配符通配符描述例子%替代 0 个或多个字符选取 url 以字母 “https” 开始的所有网站SELECT * FROM Websites WHERE url LIKE ‘https%’_替代一个字符选取 name 以 “G” 开始然后是一个任意字符然后是 “o”然后是一个任意字符然后是 “le” 的所有网站SELECT * FROM Websites WHERE name LIKE ‘G_o_le’[charlist]MySQL不支持 字符列中的任何单一字符1选取 name 以 “G”、“F” 或 “s” 开始的所有网站SELECT * FROM Websites WHERE name REGEXP ‘^ [GFs]’2选取 name 以 A 到 H 字母开头的网站SELECT * FROM Websites WHERE name REGEXP ‘^ [A-H]’[^charlist] 或 [!charlist]MySQL不支持不在字符列中的任何单一字符选取 name 不以 A 到 H 字母开头的网站SELECT * FROM Websites WHERE name REGEXP ‘^ [^A-H]’4.2 联合表操作inner join返回两张表的交集部分inner join joinleft join以左表为主表返回所有左表的数据left outer join left joinright join以右表为主表返回所有右表的数据right outer join right joinFULL JOIN完全连接可看作是两张表的并集。如果匹配列的值在两个表中匹配那么返回数据行否则返回空值。4.2.1 举例参考知乎文章1person表2score表举例select * from person t1 left join score t2 on t1.uid t2.uidselect * from person t1 join scorep t2 on t1.uid t2.uidselect * from person t1 full join scorep t2 on t1.uid t2.uid4.3 嵌套操作略代码尽量避免嵌套原因难写一旦写错就很难定位还可能把数据库跑死。SQL调试难只能自己一步步执行子语句调试。长SQL后期想跟随业务修改太难了。长SQL过段时间连自己都看不懂重新看懂跟又开发了一遍似的。复杂 SQL 还会影响数据库移植在一个数据库上使用的函数放到另一数据库可能不支持。4.4 SQL常用函数求平均值avg()求和sum()求总行数count求最大值max()求最小值min()求第n1名到第nm名limit n,m五、数据库索引参考前面写的文章索引六、执行查询语句期间发生了什么参考博客一条SQL查询语句是如何执行的MySQL是典型的 C/S架构客户端/服务器架构客户端进程向服务端进程发送一段文本MySQL指令服务器进程进行语句处理然后执行并返回结果。6.1 MySQL 的两层架构6.1.1 Server 层Server 层是MySQL的核心功能模块负责建立连接、分析和执行 SQL主要包括连接器查询缓存、解析器、预处理器、优化器、执行器等。另外所有的内置函数如日期、时间、数学和加密函数等和所有跨存储引擎的功能如存储过程、触发器、视图等都在 Server 层实现。执行一条 SQL 查询语句期间发生了什么连接器建立连接管理连接。建立连接之后除非客户端主动断开连接否则服务器会等待客户端发送请求。但是线程的创建和保持是需要消耗服务器资源的因此服务器会把长时间不活动的客户端连接断开。校验用户身份查询缓存查询语句如果命中查询缓存则直接返回否则继续往下执行。MySQL 8.0 已删除该模块。解析 SQL通过解析器对 SQL 查询语句进行如下操作方便后续模块读取表名、字段、语句类型词法分析。就是把一条完整的SQL语句打碎成一个个单词比如MySQL会把SELECT识别成查询语句把字符串t_user识别成“表名 t_user”把字符串user_name识别成“列 user_name。语法分析。语法分析器会根据语法规则生成解析树从而判断SQL 语句是否满足语法比如单引号是否闭合关键词拼写是否正确等。构建语法树。解析树执行 SQL执行 SQL 共有三个阶段预处理阶段检查表或字段是否存在将 select * 中的 * 符号扩展为表的所有列。优化阶段基于查询成本的考虑 查询优化器会选择成本最小的执行计划MySQL作者担心我们写的SQL太垃圾所以有设计出查询优化器辅助我们提高查询效率。查询优化器会根据解析树生成不同的执行计划Execution Plan然后选择一种成本最小的执行计划。这里的成本指【I/O成本 CPU成本】IO 成本: 即从磁盘把数据加载到内存的成本默认情况下读取数据页的 IO 成本是 1MySQL 是以页的形式读取数据的即当用到某个数据时并不会只读取这个数据而会把这个数据相邻的数据也一起读到内存中这就是有名的程序局部性原理所以 MySQL 每次会读取一整页一页的成本就是 1。所以 IO 的成本主要和页的大小有关CPU 成本将数据读入内存后还要检测数据是否满足条件和排序等 CPU 操作的成本显然它与行数有关默认情况下检测记录的成本是 0.2。执行阶段根据执行计划执行 SQL 查询语句从存储引擎读取记录返回给客户端。存储引擎处理数据6.1.2 存储引擎层补充知识:MySQL支持 InnoDB、MyISAM、Memory 等多个存储引擎不同的存储引擎共用一个 Server 层。从 MySQL 5.5 版本开始MySQL默认InnoDB为存储引擎 。我们常说的索引数据结构就是由存储引擎层实现的。不同的存储引擎支持的索引类型也不相同比如 InnoDB 支持索引类型是 B树且是默认使用。在数据表中创建的主键索引和二级索引默认使用的是 B 树索引。存储引擎层负责数据存储和提取比如数据存储在内存还是磁盘、怎么从表里读取数据怎么把数据写入表中。表是由一行一行的记录组成的但这只是逻辑上的概念其实只是看上去是这样而已。为什么需要多种存储引擎不同存储引擎特性不同存储引擎只是读写MySQL数据的插件可以根据不同目随意更换。如何选择存储引擎1对数据一致性要求比较高需要事务支持可以选择InnoDB。2如果数据查询多更新少对查询性能要求比较高可以选择MyISAM。3如果需要一个用于查询的临时表可以选择Memory。1 MemoryMemory存储引擎以前也称堆引擎它将所有数据存储在RAM内存中以便快速访问。特点把数据放在内存里面读写的速度很快。但是数据库重启或者崩溃数据会全部消失只适合做临时表。2 MylSAM应用范围比较小表级锁限制了读/写性能因此在Web和数据仓库配置中通常用于只读或以读为主的工作。特点:支持表级别的锁插入和更新会锁表不支持事务拥有较高的插入insert和查询select速度存储了表的行数count速度更快。怎么快速向数据库插入100万条数据可以先用MylSAM插入数据然后修改存储引擎为InnoDB。ALTER TABLE 表名 ENGINE 存储引擎名称;3 InnoDBMySQL 5.7及更新版中的默认存储引擎。InnoDB是事务安全兼容ACID它具有提交、回滚和崩溃恢复功能来保护用户数据。InnoDB行级锁和Oracle风格的一致非锁读提高了多用户并发性。InnoDB将用户数据存储在聚集索引中以减少基于主键的常见查询的I/O。为了保持数据完整性InnoDB还支持外键引用完整性约束。特点支持事务支持外键因此数据的完整性、一致性更高支持行级别的锁和表级别的锁支持读写并发写不阻塞读MVCC特殊的索引存放方式可以减少IO提升査询效率。番外为什么MySQL越来越像OracleInnoDB是InnobaseOy公司开发的它和MySQL AB公司合作开源了InnoDB的代码。但是MySQL的竞争对手Oracle把InnobaseOy收购了。后来2008年Sun公司开发Java语言的Sun收购了MySQL AB2009年Sun公司又被Oracle收购了所以MySQL和 InnoDB又是一家了。6.2 详解InnoDB存储引擎事务在InnoDB中从提交到完成的整个流程准备更新一条 SQL 语句MySQLinnodb会先去缓冲池BufferPool中去查找这条数据没找到就会去磁盘中查找如果查找到就会将这条数据加载到缓冲池BufferPool中。在加载到 Buffer Pool 的同时会将这条数据的原始记录保存到 undo 日志文件中。innodb 会在 Buffer Pool 中执行更新操作。更新后的数据会记录在 redo log buffer 中。提交事务时会将内存 redo log buffer 中的数据写入到磁盘的 redo log 文件中。提交事务时MySQL还会1将本次修改的数据记录到 bin log文件中2将本次修改的bin log文件名和修改的内容在bin log中的位置记录到redo log中3在redo log中写入 commit 标记标识本次事务被成功提交了。6.2.1 Buffer Pool 缓冲池缓冲池 Buffer Pool是InnoDB非常重要的组件。MySQL 的数据最终是存储在磁盘中的有了 Buffer Pool第一次查询时就会将查询结果存到Buffer Pool之后再有请求时就会先从缓冲池中查询没查到再去磁盘中I/O查找然后在放到 Buffer Pool 中。6.2.2 undo 日志文件在准备更新一条语句的时候该条语句已经被加载到 Buffer pool 中了实际上这里还会同时在 undo 日志文件记录下更新前的值。为什么要记录更新前的值Innodb 存储引擎的最大特点就是支持事务如果本次更新失败也就是事务提交失败那么该事务中的所有的操作都必须回滚到执行前的样子也就是说当事务失败的时候也不会对原始数据有影响6.2.3 redo 日志文件redo log buffer内存缓存记录将要做的一些操作。redo log磁盘文件记录数据被修改后的样子。MySQL 为了提高效率会将更新操作先放在内存中去完成然后会在事务提交后 将其持久化到磁盘日志文件中。知识补充如果 redo log Buffer 刷入磁盘前MySQL宕机了缓存会丢失没关系因为 MySQL 会认为本次事务是失败的所以数据依旧是更新前的样子没有任何影响。如果 redo log Buffer 刷入磁盘后MySQL宕机了缓存会丢失也没关系因为 redo log buffer 中的数据已经被写入到磁盘redo log了下次重启时 MySQL 会将 redo log 文件内容恢复到 Buffer Pool 中和 Redis 的持久化机制类似Redis 启动时会检查 RDB 或者 AOF 或者两者都检查根据持久化的文件将数据恢复到内存中。刷入磁盘参数设置通过 innodb_flush_log_at_trx_commit 参数设置刷入磁盘0 表示不刷入磁盘1 表示立即刷入磁盘2 表示先刷到 os cache6.2.4 bin log文件bin log 记录对数据库的整个修改操作对主从复制非常有用bin log刷盘策略可以通过sync_bin log修改策略。为0表示提交事务后先写入os cache数据不会直接到磁盘中如果宕机bin log数据会丢失。建议将sync_bin log设置为 1 表示直接将数据写入到磁盘文件中。bin log在redo log中被记录提交事务时MySQL还会1将本次修改的数据记录到 bin log文件中2将本次修改的bin log文件名和修改的内容在bin log中的位置记录到redo log中3在redo log中写入 commit 标记标识本次事务被成功提交了。如果数据刚被写入到bin log文件数据库宕机了数据会丢失吗——不会丢失只要redo log最后没有 commit 标记就说明本次的事务是失败的但是数据已经被记录到redo log的磁盘文件中了MySQL 重启时会将 redo log 中的数据恢复加载到Buffer Pool。6.2.5 后台线程疑问上面仅描述了在内存中的更新操作哪怕是宕机又恢复了也仅是将更新后的记录加载到Buffer Pool中这时 MySQL 数据库中的这条记录依旧是旧值内存数据依旧是脏数据MySQL怎么保持内存和数据库表数据统一的呢解答MySQL 有个后台线程它会在某个时机将Buffer Pool 中的脏数据刷到磁盘表中保持内存和数据库数据统一。