百度收录最高发帖网站,抖音代运营的公司,淘宝搜索框去什么网站做,怀仁网站建设数据库三大范式是什么 第一范式#xff1a;列不可再分 第二范式#xff1a;行可以唯一区分#xff0c;主键约束#xff0c;要求表中的每个非主键列完全依赖于主键列 第三范式#xff1a;表的非主属性不能依赖与其他表的非主属性 外键约束 且三大范式是一级一级…数据库三大范式是什么 第一范式列不可再分 第二范式行可以唯一区分主键约束要求表中的每个非主键列完全依赖于主键列 第三范式表的非主属性不能依赖与其他表的非主属性 外键约束 且三大范式是一级一级依赖的第二范式建立在第一范式上第三范式建立第一第二范式上 。 MySQL 索引分类?
在MySQL中索引按照索引列的类型可以分为以下几种
主键索引用于唯一标识每一条记录主键索引的值不允许重复且不能为空并且一个表只能有一个主键索引。唯一索引用于保证索引列的值唯一允许为空值但是一个表可以有多个唯一索引。普通索引没有唯一性限制允许重复值和空值是最基本的索引类型。组合索引在多个字段上创建的索引可以包含多个列。组合索引可以提高多列查询的性能但查询条件必须符合最左前缀原则即查询从左到右使用组合索引中的列。
以上就是MySQL常见的四种索引这些不同类型的索引在数据库中起到了加速数据检索操作的作用可以根据具体的需求和使用场景选择适当的索引类型。同时需要注意索引的创建对写操作如插入、更新、删除可能会产生额外的开销因此需要权衡索引的使用与数据操作的平衡。
MyISAM索引与InnoDB索引的区别
MyISAM和InnoDB是MySQL中两种常见的存储引擎它们在索引实现上存在以下区别
存储方式MyISAM使用非聚簇索引索引文件和数据文件是分开的而InnoDB使用聚簇索引将索引和数据一起存储在同一个文件中。锁机制MyISAM采用表级锁定意味着当对表进行写操作时整个表都会被锁定因此可能导致并发写操作的性能较差。而InnoDB采用行级锁定只锁定需要修改的行可以提供更好的并发性能和多用户写入的支持。事务支持MyISAM不支持事务处理而InnoDB支持事务和ACID特性原子性、一致性、隔离性和持久性可以进行事务管理、回滚和恢复操作。引用完整性MyISAM不支持外键约束而InnoDB支持外键约束可以设置关联关系来保证数据的完整性。性能特点MyISAM在读取频繁、插入和更新较少的场景下性能较好特别适合于读密集型应用而InnoDB在并发写入和更新较多的情况下性能较好适合于写入密集型应用或需要事务支持的场景。
什么是索引下推 索引下推Index Condition Pushdown简称ICP是一种数据库查询优化技术它利用了数据库引擎中的索引和过滤条件将部分过滤工作下推到存储引擎层面进行处理从而减少不必要的数据读取和传输。 在传统的查询执行过程中数据库引擎首先根据索引定位到符合过滤条件的数据行并将这些行读取到内存中然后再进行进一步的过滤操作。而索引下推则在这一步骤中尽可能地将过滤操作下推到存储引擎层面避免将不符合条件的数据行读取到内存中。
具体实现方式可以是通过存储引擎提供的接口或者钩子函数让存储引擎在读取索引页时就进行额外的过滤操作。 通过索引下推数据库系统可以在存储引擎层面根据索引和过滤条件提前过滤掉不符合条件的数据减少了需要传递给查询引擎的数据量和内存消耗。这样可以大大减少磁盘 I/O 和数据传输的开销提升查询性能和整体系统效率。 需要注意的是索引下推并不是对所有类型的查询都适用它更适用于复杂查询条件、多列条件的查询中能够有效地减少不必要的数据读取和传输。
并发事务带来哪些问题
并发事务可以带来以下几个问题
脏读Dirty Read一个事务读取了另一个事务未提交的数据。假设事务A修改了一条数据但未提交事务B却读取了这个未提交的数据导致事务B基于不准确的数据做出了错误的决策。不可重复读Non-repeatable Read一个事务在多次读取同一数据时得到了不同的结果。假设事务A读取了一条数据事务B修改或删除了该数据并提交然后事务A再次读取同一数据发现与之前的读取结果不一致造成数据的不一致性。幻读Phantom Read一个事务在多次查询同一范围的数据时得到了不同数量的结果。假设事务A根据某个条件查询了一组数据事务B插入了符合该条件的新数据并提交然后事务A再次查询同一条件下的数据发现结果集发生了变化产生了幻觉般的新增数据。丢失修改Lost Update两个或多个事务同时修改同一数据并且最终只有一个事务的修改被保留其他事务的修改被覆盖或丢失。这种情况可能会导致数据的部分更新丢失造成数据的不一致性。
不可重复读和幻读区别
不可重复读的重点是修改比如多次读取一条记录发现其中某些列的值被修改幻读的重点在于新增或者删除比如多次读取一条记录发现记录增多或减少了。
CHAR 和 VARCHAR 的区别
CHAR和VARCHAR的区别可以总结如下
存储方式CHAR是固定长度的字符类型而VARCHAR是可变长度的字符类型。占用空间CHAR会以固定的长度存储数据不论实际存储的字符数目而VARCHAR则根据实际需要的空间动态分配存储。尾随空格CHAR在存储时会用空格填充到指定长度并在检索时需要删除尾随空格而VARCHAR没有这个问题。长度限制CHAR的长度范围为1到255个字符而VARCHAR的长度范围也是255个字符但可以根据需求设定更长的长度。访问效率由于CHAR是固定长度的它在某些情况下可能会比VARCHAR稍微快一些。
综上所述CHAR适合存储长度固定且固定大小的数据而VARCHAR适用于长度可变的数据。
count(1)、count(*) 与 count(列名) 的区别
在SQL查询中COUNT(1)、COUNT(*)和COUNT(列名)是用于计算行数的聚合函数但它们在实际应用时有一些区别。
COUNT(1)这种写法中1表示一个常量值它会被用于计算查询结果集的行数。由于1是一个常量在执行COUNT(1)时数据库不会去访问或读取任何实际的数据仅仅是对满足条件的行进行计数因此执行速度相对较快。COUNT(*)这种写法中*表示选取所有列它会对满足条件的行进行计数。与COUNT(1)不同的是执行COUNT(*)时数据库需要读取每一行的数据然后进行计数操作因此它可能会比COUNT(1)稍微慢一些。不过在大多数数据库中优化器会对COUNT(*)进行特殊处理可以通过索引等方式进行优化使得性能相对较好。COUNT(列名)这种写法中列名表示具体的表列它会对非空NULL值的行进行计数。相比于COUNT(1)和COUNT(*)COUNT(列名)会跳过值为NULL的行只统计非空值的行数。这在某些特定的情况下可能更符合实际需求例如统计某个列的非空值个数。
总体来说COUNT(1)和COUNT(*)的性能较好且结果一致而COUNT(列名)则对非空值进行计数。在实际使用时可以根据具体的查询需求和性能要求选择适当的写法。
UNION 与UNION ALL 的区别
UNIONUNION用于合并两个或多个查询结果集并去除重复的行。它将多个查询的结果合并为一个结果集并自动去除重复的行。在执行UNION操作时数据库会进行额外的去重操作这可能会带来一定的性能开销。去重以及排序UNION ALLUNION ALL同样用于合并查询结果集但不去除重复的行。它将多个查询的结果简单地合并在一起包括重复的行。相比于UNIONUNION ALL不进行去重操作因此执行效率更高。(不去重不排序)
总结来说在使用时可以根据具体的需求来选择合适的操作符。如果需要去除重复的行可以使用UNION如果不需要去重或者对性能要求较高可以使用UNION ALL。需要注意的是使用UNION或UNION ALL时要求被合并的查询结果的列数和列类型保持一致。
如何快速定位慢SQL
要查询慢SQL产生的原因可以采取以下4个步骤
启用慢查询日志在MySQL配置中启用慢查询日志这样可以记录执行时间超过阈值的查询语句。通过分析慢查询日志可以找到执行时间较长的SQL语句。使用EXPLAIN分析执行计划对于慢查询的SQL语句使用EXPLAIN命令来查看其执行计划。通过分析执行计划确定查询是否有效利用了索引以及是否存在性能瓶颈。检查索引使用情况确保查询中涉及的列都有适当的索引并且查询条件能够充分利用索引。可以使用SHOW INDEX命令或查询表的索引信息来检查索引情况。分析查询语句仔细分析查询语句本身检查是否存在冗余的操作、重复的子查询、不必要的排序、大量的JOIN操作等。
慢SQL你是怎么优化的
优化查询语句结构检查是否存在冗余的操作、重复的子查询、不必要的排序、大量的JOIN操作等。优化查询语句的结构和逻辑减少不必要的数据读取和计算。添加合适的索引确保查询中涉及的列都有适当的索引并且查询条件能够充分利用索引。通过使用适当的索引提高查询的性能。但是要避免过多的索引因为过多的索引会增加写入操作的开销。使用覆盖索引如果查询只需要使用索引列的数据而不需要访问表的其他列可以考虑使用覆盖索引。覆盖索引避免了访问表的额外IO操作提高查询性能。避免全表扫描尽量避免全表扫描的情况通过合适的索引或筛选条件来限制查询范围减少数据读取量。合理分页查询对于大数据量的分页查询可以通过使用LIMIT分页、使用游标、定期同步缓存等方式来提高性能。
索引失效的情况有哪些
索引失效是指在使用索引进行查询时索引无法发挥作用导致查询性能下降。常见的导致索引失效的情况有以下几种
不满足索引列顺序如果查询条件中的列顺序与索引列的顺序不一致索引可能无法被使用。例如一个联合索引A, B如果查询条件只包含了B列而没有A列那么这个索引就无法被利用。使用函数或表达式当查询条件中对索引列应用了函数、数学运算、类型转换等操作时索引可能无法被使用。因为索引的创建是基于原始列值的无法直接使用函数计算后的结果进行索引匹配。字符串比较问题对于字符串类型的索引列使用了不符合索引规则的比较方式。数据类型不匹配当查询条件的数据类型与索引列的数据类型不匹配时索引可能无法被使用。尤其是在进行隐式数据类型转换、不同字符集的比较或编码问题时需要特别留意。数据量过小当表中的数据量较小时MySQL可能会选择全表扫描而非使用索引因为全表扫描的成本较低。这种情况下索引可能无法发挥作用。使用了NOT、、OR等非优化的逻辑操作符这些逻辑操作符在查询条件中的使用会导致索引失效因为它们无法充分利用索引的特性。
综上所述我们要解决索引失效的问题可以通过合理设计索引、优化查询语句以及避免索引失效的情况发生来提升查询性能。
说下你对数据库事务的理解
数据库事务是指一系列数据库操作的逻辑单元这些操作要么全部成功执行要么全部回滚。它的目的是确保数据的一致性和完整性。事务具备4大特性即原子性、一致性、隔离性和持久性
原子性事务中的所有操作要么全部执行成功要么全部回滚到事务开始前的状态。如果在事务执行期间发生错误系统将回滚所有已执行的操作以保持数据的一致性。
一致性事务的执行不会破坏数据库的完整性约束。在事务开始和结束时数据库必须处于一致的状态。
如小李转账100元给小白不管操作是否成功小李和小白的账户总额是不变的。
隔离性事务的执行是相互隔离的即每个事务对其他事务是透明的。并发执行的多个事务不会相互干扰每个事务感知不到其他事务的存在。
持久性一旦事务提交成功事务中的所有操作都必须持久化到数据库中。
以上就是我对数据库事务的理解。
事务的隔离级别有哪些
读未提交最低的隔离级别。事务可以读取到其他事务尚未提交的数据可能会出现脏读、不可重复读和幻读问题。读已提交事务只能读取到已经提交的数据。但在同一事务中多次读取同一行的数据结果可能会不一致可能会出现不可重复读和幻读问题。可重复读在同一个事务内多次读取同一行的数据结果始终保持一致。MySQL的默认隔离级别就是可重复读。通过使用MVCC机制来实现在读操作期间不会看到其他事务的修改有效地解决了不可重复读问题。串行化最高的隔离级别。事务串行执行避免了脏读、不可重复读和幻读等问题。但是并发性能较差可能导致大量的锁竞争和资源争用。
事务隔离级别
MySQL事务隔离级别是指在多个事务同时访问数据库时数据库如何保证数据的一致性和隔离性。常见的隔离级别如下
读未提交最低的隔离级别允许读取尚未提交的数据变更。串行化最高的隔离级别完全服从ACID的隔离级别。所有的事务依次逐个执行这样事务之间就完全不可能产生干扰。可重复读同一字段的多次读取结果都是一致的除非数据是被本身事务自己所修改。读已提交允许读取并发事务已经提交的数据。 隔离级别 并发问题 适用场景 隔离级别从上往下由低到高。 隔离级别越高事务的并发性能就越低。 读未提交read-uncommitted 可能会导致脏读、幻读或不可重复读 并发性要求不高 读已提交read-committed 可能会导致幻读或不可重复读 并发性要求较高 可重复读repeatable-read 可能会导致幻读 数据一致性要求较高 可串行化serializable 不会产生干扰 数据一致性要求非常高
讲讲你对MVCC的理解
MVCC是一种并发控制策略它在多个事务同时执行时确保数据库的一致性和隔离性。MVCC通过为每个事务创建数据的不同版本避免了锁竞争问题。
它的工作原理如下
每条数据行都有一个隐藏的版本号或时间戳记录该行的创建或最后修改时间。当事务开始它会获取一个唯一的事务ID作为其开始时间戳。在读取数据时事务只能访问在其开始时间戳之前已提交的数据。这个版本的数据在事务开始前就已存在。当事务更新数据会创建新版本的数据将更新后的数据写入新的数据行并将事务ID与新版本关联。其他事务可以继续访问旧版本的数据不受正在进行的更新事务影响。这种机制被称为快照读。当事务提交其所有修改才对其他事务可见。此时新版本的数据成为其他事务读取的数据。
以上就是MVCC的工作原理。它是通过使用多个版本的数据来实现并发控制提高了数据库的并发性能并确保了事务之间的隔离性和数据一致性。
Undo log是如何回滚事务的
首先获取事务的回滚指针或Undo Log的起始位置。
从Undo Log的末尾开始逆向扫描按照事务操作的逆序依次处理每个日志记录。
然后针对 INSERT 操作执行 DELETE 操作来撤销插入的数据。对于 UPDATE 操作使用Undo Log 中记录的旧值将数据还原到之前的状态。
在回滚过程中对于已经提交的其他事务所做的修改需要跳过只处理属于当前回滚事务的 Undo Log 记录。
按照逆序依次处理所有的日志记录直到达到回滚指针位置或 Undo Log 的起始位置。
回滚完成后清除或标记已回滚的 Undo Log 记录。
总体而言事务回滚是通过执行 Undo Log 中记录的反向操作将事务的修改操作撤销恢复到事务开始前的状态。
讲讲主从复制原理与延迟
首先主库将变更写入 binlog 日志。
从库连接到主库后有一个 IO 线程负责将主库的 binlog 日志复制到自己本地并写入到中继日志中。
然后从库中有一个 SQL 线程会从中继日志读取 binlog并执行其中的 SQL 内容即在从库上再次执行一遍。
以上就是主从复制的原理。那么主从延迟的原因有哪些呢
主库的从库太多主库需要将 binlog 日志传输给多个从库导致复制延迟。在从库执行的 SQL 中存在慢查询语句会导致整体复制进程的延迟。如果主库的读写压力过大会导致主库处理 binlog 的速度减慢进而影响复制延迟。
为了优化主从复制的延迟我们可以采取以下措施
减少从库的数量降低主库的负载减少复制延迟。优化慢查询语句减少从库执行SQL 的延迟。对主库进行性能优化减少主库的读写压力提高 binlog 写入速度。
通过以上措施可以帮助降低主从复制的延迟提高复制的效率和一致性。
如何优化深分页limit 1000000
深分页问题是 MySQL 中常见的性能问题当你尝试获取大量数据的后续页面时性能会显著下降。这是因为 MySQL 需要先扫描到指定的偏移量然后再返回数据。
例如以下查询可能会非常慢
SELECT * FROM table ORDER BY id LIMIT 1000000, 10;
这是因为 MySQL 需要先扫描 1000000 行数据然后再返回后面的 10 行数据。
解决深分页问题的常见方法有以下几种
使用覆盖索引 覆盖索引可以让 MySQL 在索引中获取所有需要的数据而无需回表查询。这可以大大提高查询速度。
SELECT id FROM table ORDER BY id LIMIT 1000000, 10;
记住上次的位置 如果你的应用程序可以记住上次查询的最后一个 ID那么你可以使用 WHERE 子句来避免扫描大量数据。
SELECT * FROM table WHERE id last_id ORDER BY id LIMIT 10;
使用分页插件 有些数据库框架提供了分页插件可以自动优化分页查询。避免深分页 在设计应用程序时尽量避免深分页。例如你可以提供搜索功能让用户快速找到他们需要的数据而不是浏览大量的页面。
MySQL 有哪些高可用方案
主从复制这是最常见的高可用方案。主库负责处理写操作并将数据变更记录到binlog日志。从库将主库的binlog复制到自己的中继日志然后执行中继日志中的事件以达到与主库数据一致的目的。当主库出现故障时可以将从库提升为新的主库实现服务的高可用。集群MySQL集群是一个高可用、高性能的数据库集群解决方案。它使用了共享无关的架构可以在节点之间自动分割和复制数据实现了数据的高可用和高性能。
如何解决MySQL死锁问题
MySQL在并发环境下可能会出现死锁问题。死锁是指两个或多个事务互相等待对方释放资源导致无法继续执行的情况。
调整事务隔离级别通过将事务隔离级别降低为读未提交或读已提交可以减少死锁的发生概率。但是要注意隔离级别的降低可能引发脏读、不可重复读等数据一致性问题在选择时需要权衡利弊。优化查询和事务逻辑分析造成死锁的原因优化查询语句和事务逻辑尽量缩短事务持有锁的时间减少死锁的可能性。比如按照相同的顺序获取锁避免跨事务的循环依赖等。使用行级锁行级锁可以较小地限制锁的范围从而减少死锁的可能性。将表的锁粒度调整为行级别可以减少事务之间的冲突。设置合理的超时时间和重试机制当发生死锁时可以设置适当的超时时间在一定时间内尝试解锁操作。如果超过设定的时间仍未成功则进行死锁处理如终止较早请求的事务或进行回滚等。 高并发场景下如何安全修改同一行数据
乐观锁在数据表中添加一个版本号或者时间戳字段每次更新数据时都会检查该字段的值。当多个并发的请求同时修改同一行数据时只有一个请求能够成功执行更新操作其他请求需要重新检查数据是否被修改过。如果数据没有被修改那么它们可以重新尝试更新如果数据已经被修改则这些请求需要触发重试等相应的冲突处理逻辑。悲观锁在读取数据之前使用数据库提供的锁机制如SELECT ... FOR UPDATE语句将要修改的行数据进行加锁。这样其他并发的请求在读取相同行数据时会被阻塞直到锁释放。这种方法能够确保同一时间只有一个请求在修改数据但是会影响系统的并发性能。分布式锁通过使用分布式锁服务如Redis的SETNX命令或ZooKeeper的临时节点来实现对行级数据的互斥访问。在修改数据前先尝试获取锁获取成功后执行数据修改操作修改完成后释放锁。其他请求在获取锁失败时可以等待或执行相应的冲突处理逻辑。事务将对同一行数据的修改操作封装在数据库事务中。在事务中数据库会自动处理并发修改的冲突通过锁定相应的数据行来确保数据的一致性和完整性。并发的请求会被串行化执行保证每个请求都能正确读取并修改数据。
数据库 cpu 飙升你会如何处理
查询性能问题某些查询可能没有被正确地优化导致查询执行时间过长从而占用大量的CPU资源。可以通过查看慢查询日志和执行计划来分析问题查询并进行索引优化、重写查询语句或调整数据库配置等方式来改善查询性能。数据库连接问题如果存在大量的数据库连接并发访问可能会造成CPU负载过高。可以检查应用程序连接池的配置情况、数据库连接数限制以及是否有闲置的连接未关闭等问题并进行相应调整。锁和死锁问题并发事务之间的锁竞争或死锁可能导致CPU飙升。可以通过查看数据库的锁状态、死锁日志以及事务并发控制的设置来解决锁相关的问题。配置问题不合理的数据库配置可能导致CPU资源浪费和效率低下。可以检查MySQL的配置参数如缓冲区大小、并发连接数、线程池大小等是否合理设置并进行相应调整。资源竞争如果服务器的物理资源如内存、磁盘I/O不足或受限可能会导致CPU过度使用。可以通过监控系统资源使用情况调整或增加资源配置以满足数据库的需求。
一条SQL的执行过程是怎样的
1. 连接器 ○ 客户端与数据库建立连接并发送 SQL 语句给数据库服务。 ○ 连接器验证客户端的身份和权限确保用户有足够的权限执行该 SQL 语句。 2. 查询缓存 ○ 连接器首先检查查询缓存尝试找到与当前 SQL 语句完全相同的查询结果。 ○ 如果在缓存中找到匹配的结果查询缓存直接返回结果避免了后续的执行过程。 3. 分析器 ○ 若查询不命中缓存连接器将 SQL 语句传递给分析器进行处理。 ○ 分析器对 SQL 语句进行语法分析确保语句的结构和语法正确。 ○ 分析器还会进行语义分析检查表、列、函数等对象的存在性和合法性并进行权限验证。 4. 优化器 ○ 分析器将经过验证的 SQL 语句传递给优化器。 ○ 优化器根据统计信息和数据库的规则生成多个可能的执行计划这些计划包括不同的索引选择、连接顺序、筛选条件等。 ○ 目的是选出最优的执行路径以提高查询性能。 5. 执行器 ○ 优化器选择一个最优的执行计划并将其传递给执行器。 ○ 执行器根据执行计划执行具体的查询操作。 ○ 它负责调用存储引擎的接口处理数据的存储、检索和修改。 ○ 执行器会根据执行计划从磁盘或内存中获取相关数据并进行联接、过滤、排序等操作生成最终的查询结果。 6. 存储引擎 ○ 执行器将查询请求发送给存储引擎组件。 ○ 存储引擎组件负责具体的数据存储、检索和修改操作。 ○ 存储引擎根据执行器的请求从磁盘或内存中读取或写入相关数据。 7. 返回结果 ○ 存储引擎将查询结果返回给执行器。 ○ 执行器将结果返回给连接器。 ○ 最后连接器将结果发送回客户端完成整个执行过程。 需要注意的是查询缓存在一些场景下可能不太适用因为它有一定的缺陷和开销。MySQL 8.0 版本开始默认情况下查询缓存已被废弃。因此在实际应用中需要权衡是否使用查询缓存。
分表后非分片键的查询、排序怎么处理
联合查询如果需要执行涉及多个分表的查询可以使用联合查询UNION或者连接查询JOIN来合并结果。将查询条件应用到对应的分表然后将结果联合起来返回给用户。扫描所有分表如果查询涉及到的数据无法通过分片键进行过滤那么可能需要扫描所有的分表来获取满足条件的数据。这样的查询会比较慢因为需要访问所有的分表并合并结果但在某些场景下是必要的。手动分页和排序如果需要对分表后的数据进行排序和分页可以在每个分表上执行独立的排序和分页操作然后将结果合并或者按需返回给用户。这需要在应用程序中进行额外的逻辑处理。使用全局索引如果有一些特定的查询需要在非分片键上执行并且这些查询非常频繁可以考虑创建全局索引。全局索引不关心分片键而是在整个集群上创建索引可以加速这类查询的执行速度。
分享7种SQL的进阶用法
前言
还只会使用SQL进行简单的insert、update、detele吗本文给大家带来7种SQL的进阶用法让大家在平常工作中使用SQL简化复杂的代码逻辑。
1.自定义排序ORDER BY FIELD
在MySQL中ORDER BY排序除了可以用ASC和DESC之外还可以使用自定义排序方式来实现。
CREATE TABLE movies ( id INT PRIMARY KEY AUTO_INCREMENT, movie_name VARCHAR(255), actors VARCHAR(255), price DECIMAL(10, 2) DEFAULT 50, release_date DATE
) ENGINEInnoDB DEFAULT CHARSETutf8mb4;INSERT INTO movies (movie_name, actors, price, release_date) VALUES
(咱们结婚吧, 靳东, 43.2, 2013-04-12),
(四大名捕, 刘亦菲, 62.5, 2013-12-21),
(猎场, 靳东, 68.5, 2017-11-03),
(芳华, 范冰冰, 55.0, 2017-09-15),
(功夫瑜伽, 成龙, 91.8, 2017-01-28),
(惊天解密, 靳东, 96.9, 2019-08-13),
(铜雀台, null, 65, 2025-12-16),
(天下无贼, 刘亦菲, 44.9, 2004-12-16),
(建国大业, 范冰冰, 70.5, 2009-09-21),
(赛尔号4疯狂机器城, 范冰冰, 58.9, 2021-07-30),
(花木兰, 刘亦菲, 89.0, 2020-09-11),
(警察故事, 成龙, 68.0, 1985-12-14),
(神话, 成龙, 86.5, 2005-12-22);
用法如下
select * from movies order by movie_name asc;select * from movies ORDER BY FIELD(movie_name,神话,猎场,芳华,花木兰,
铜雀台,警察故事,天下无贼,四大名捕,惊天解密,建国大业,
功夫瑜伽,咱们结婚吧,赛尔号4疯狂机器城); 2.空值NULL排序ORDER BY IF(ISNULL)
在MySQL中使用ORDER BY关键字加上我们需要排序的字段名称就可以完成该字段的排序。如果字段中存在NULL值就会对我们的排序结果造成影响。 这时候我们可以使用 ORDER BY IF(ISNULL(字段), 0, 1) 语法将NULL值转换成0或1实现NULL值数据排序到数据集前面还是后面。
select * from movies ORDER BY actors, price desc;select * from movies ORDER BY if(ISNULL(actors),0,1), actors, price; 3.CASE表达式CASE···WHEN
在实际开发中我们经常会写很多if ··· else if ··· else这时候我们可以使用CASE···WHEN表达式解决这个问题。 以学生成绩举例。比如说学生90分以上评为优秀分数80-90评为良好分数60-80评为一般分数低于60评为“较差”。那么我们可以使用下面这种查询方式
CREATE TABLE student (student_id varchar(10) NOT NULL COMMENT 学号,sname varchar(20) DEFAULT NULL COMMENT 姓名,sex char(2) DEFAULT NULL COMMENT 性别,age int(11) DEFAULT NULL COMMENT 年龄,score float DEFAULT NULL COMMENT 成绩,PRIMARY KEY (student_id)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT学生表;INSERT INTO student (student_id, sname, sex, age , score)
VALUES (001, 张三, 男, 20, 95),(002, 李四, 女, 22, 88),(003, 王五, 男, 21, 90),(004, 赵六, 女, 20, 74),(005, 陈七, 女, 19, 92),(006, 杨八, 男, 23, 78),(007, 周九, 女, 20, 55),(008, 吴十, 男, 22, 91),(009, 刘一, 女, 21, 87),(010, 孙二, 男, 19, 60);
select *,case when score 90 then 优秀when score 80 then 良好when score 60 then 一般else 较差 end level
from student; 4.分组连接函数GROUP_CONCAT
分组连接函数可以在分组后指定字段的字符串连接方式并且还可以指定排序逻辑连接字符串默认为英文逗号。 比如说根据演员进行分组并将相应的电影名称按照票价进行降序排列而且电影名称之间通过“_”拼接。用法如下
select actors,
GROUP_CONCAT(movie_name),
GROUP_CONCAT(price) from movies GROUP BY actors;select actors,
GROUP_CONCAT(movie_name order by price desc SEPARATOR _),
GROUP_CONCAT(price order by price desc SEPARATOR _)
from movies GROUP BY actors; 5.分组统计数据后再进行统计汇总with rollup
在MySql中可以使用 with rollup在分组统计数据的基础上再进行数据统计汇总即将分组后的数据进行汇总。
SELECT actors, SUM(price) FROM movies GROUP BY actors;SELECT actors, SUM(price) FROM movies GROUP BY actors WITH ROLLUP; 6.子查询提取with as
如果一整句查询中多个子查询都需要使用同一个子查询的结果那么就可以用with as将共用的子查询提取出来并取一个别名。后面查询语句可以直接用对于大量复杂的SQL语句起到了很好的优化作用。 需求获取演员刘亦菲票价大于50且小于65的数据。
with m1 as (select * from movies where price 50),m2 as (select * from movies where price 65)
select * from m1 where m1.id not in (select m2.id from m2) and m1.actors 刘亦菲; 7.优雅处理数据插入、更新时主键、唯一键重复
在MySql中插入、更新数据有时会遇到主键重复的场景通常的做法就是先进行删除在插入达到可重复执行的效果但是这种方法有时候会错误删除数据。 1.插入数据时我们可以使用IGNORE它的作用是插入的值遇到主键或者唯一键重复时自动忽略重复的数据不影响后面数据的插入即有则忽略无则插入。示例如下
select * from movies where id 13;INSERT INTO movies (id, movie_name, actors, price, release_date) VALUES
(13, 神话, 成龙, 100, 2005-12-22);INSERT IGNORE INTO movies (id, movie_name, actors, price, release_date) VALUES
(13, 神话, 成龙, 100, 2005-12-22);INSERT IGNORE INTO movies (id, movie_name, actors, price, release_date) VALUES
(14, 神话2, 成龙, 114, 2005-12-22);
2.还可以使用REPLACE关键字当插入的记录遇到主键或者唯一键重复时先删除表中重复的记录行再插入即有则删除插入无则插入示例如下
REPLACE INTO movies (id, movie_name, actors, price, release_date) VALUES
(14, 神话2, 成龙, 100, 2005-12-22);REPLACE INTO movies (id, movie_name, actors, price, release_date) VALUES
(15, 神话3, 成龙, 115, 2005-12-22); 3.更新数据时使用on duplicate key update。它的作用就是当插入的记录遇到主键或者唯一键重复时会执行后面定义的UPDATE操作。相当于先执行Insert 操作再根据主键或者唯一键执行update操作即有就更新没有就插入。示例如下
INSERT INTO movies (id, movie_name, actors, price, release_date) VALUES
(15, 神话3, 成龙, 115, 2005-12-22) on duplicate key update price price 10;INSERT INTO movies (id, movie_name, actors, price, release_date) VALUES
(16, 神话4, 成龙, 75, 2005-12-22) on duplicate key update price price 10;
