兰州做网站的,广州公司网站设计制作,网站建设中gif,怎样开平台软件一、MySql基础
MySQL常见面试题
一、索引相关
#xff08;1#xff09;什么是索引?
索引是一种数据结构#xff0c;可以帮助我们快速的进行数据的查找。
#xff08;2#xff09;索引是个什么样的数据结构呢?
索引的数据结构和具体存储引擎的实现有关#xff0c;…一、MySql基础
MySQL常见面试题
一、索引相关
1什么是索引?
索引是一种数据结构可以帮助我们快速的进行数据的查找。
2索引是个什么样的数据结构呢?
索引的数据结构和具体存储引擎的实现有关在 MySQL 中使用较多的索引有 Hash 索引B 树索引等而我们经常使用的 InnoDB 存储引擎的默认索引实现为B 树索引。
3为什么使用索引
通过创建唯一性索引可以保证数据库表中每一行数据的唯一性。可以大大加快数据的检索速度这也是创建索引的最主要的原因。帮助服务器避免排序和临时表。将随机IO变为顺序IO。可以加速表和表之间的连接特别是在实现数据的参考完整性方面特别有意义。
4Innodb为什么要用自增id作为主键
如果表使用自增主键那么每次插入新的记录记录就会顺序添加到当前索引节点的后续位置当一页写满就会自动开辟一个新的页。如果使用非自增主键如果身份证号或学号等由于每次插入主键的值近似于随机因此每次新纪录都要被插到现有索引页得中间某个位置 频繁的移动、分页操作造成了大量的碎片得到了不够紧凑的索引结构后续不得不通过OPTIMIZE TABLEoptimize table来重建表并优化填充页面。
5Hash 索引和 B 树索引有什么区别或者说优劣呢?
首先要知道 Hash 索引和 B 树索引的底层实现原理
hash 索引底层就是 hash 表进行查找时调用一次 hash 函数就可以获取到相应的键值之后进行回表查询获得实际数据。B 树底层实现是多路平衡查找树。对于每一次的查询都是从根节点出发查找到叶子节点方可以获得所查键值然后根据查询判断是否需要回表查询数据。
那么可以看出他们有以下的不同
hash 索引进行等值查询更快(一般情况下)但是却无法进行范围查询。 因为在 hash 索引中经过 hash 函数建立索引之后索引的顺序与原顺序无法保持一致不能支持范围查询。而 B 树的的所有节点皆遵循(左节点小于父节点右节点大于父节点多叉树也类似)天然支持范围。hash 索引不支持使用索引进行排序原理同上。hash 索引不支持模糊查询以及多列索引的最左前缀匹配。原理也是因为 hash 函数的不可预测。hash索引任何时候都避免不了回表查询数据,而B树在符合某些条件(聚簇索引,覆盖索引等)的时候可以只通过索引完成查询hash 索引虽然在等值查询上较快但是不稳定。性能不可预测当某个键值存在大量重复的时候发生 hash 碰撞此时效率可能极差。而 B 树的查询效率比较稳定对于所有的查询都是从根节点到叶子节点且树的高度较低。
因此在大多数情况下直接选择 B 树索引可以获得稳定且较好的查询速度。而不需要使用 hash 索引。
6什么是聚簇索引
聚簇索引就是按照每张表的 主键 构造一棵B树同时叶子节点中存放的就是整张表的行记录数据。
在 InnoDB 中只有主键索引是聚簇索引如果没有主键则挑选一个唯一键建立聚簇索引。如果没有唯一键则MySQL自动为InnoDB表生成一个隐含字段来建立聚簇索引这个字段长度为6个字节类型为长整形。
当查询使用聚簇索引时在对应的叶子节点可以获取到整行数据因此不用再次进行回表查询。
7说一说索引的底层实现
Hash索引
基于哈希表实现只有精确匹配索引所有列的查询才有效对于每一行数据存储引擎都会对所有的索引列计算一个哈希码hash code并且Hash索引将所有的哈希码存储在索引中同时在索引表中保存指向每个数据行的指针。
B-Tree索引MySQL使用BTree
B-Tree能加快数据的访问速度因为存储引擎不再需要进行全表扫描来获取数据数据分布在各个节点之中。
BTree索引
是B-Tree的改进版本同时也是数据库索引所采用的存储结构。数据都在叶子节点上并且增加了顺序访问指针每个叶子节点都指向相邻的叶子节点的地址。相比B-Tree来说进行范围查找时只需要查找两个节点进行遍历即可。而B-Tree需要获取所有节点相比之下BTree效率更高。
Btree性质
n棵子tree的节点包含n个关键字不用来保存数据而是保存数据的索引。所有的叶子结点中包含了全部关键字的信息及指向含这些关键字记录的指针且叶子结点本身依关键字的大小自小而大顺序链接。所有的非终端结点可以看成是索引部分结点中仅含其子树中的最大或最小关键字。B 树中数据对象的插入和删除仅在叶节点上进行。B树有2个头指针一个是树的根节点一个是最小关键码的叶节点。
8索引有哪些优缺点
索引的优点
可以大大加快数据的检索速度这也是创建索引的最主要的原因。通过使用索引可以在查询的过程中使用优化隐藏器提高系统的性能。
索引的缺点
时间方面创建索引和维护索引要耗费时间具体地当对表中的数据进行增加、删除和修改的时候索引也要动态的维护会降低增/改/删的执行效率空间方面索引需要占物理空间。
9聚簇索引和非聚簇索引的区别
聚簇索引的叶子节点存放的是主键值和数据行支持覆盖索引非聚簇索引的叶子节点存放的是主键值或数据记录的地址InnoDB辅助索引的data域存储相应记录主键的值MyISAM辅助索引的data域保存数据记录的地址
10MyISAM和InnoDB实现B树索引方式的区别是什么
MyISAMBTree叶节点的data域存放的是数据记录的地址在索引检索的时候首先按照BTree搜索算法搜索索引如果指定的key存在则取出其data域的值然后以data域的值为地址读取相应的数据记录这被称为“非聚簇索引”InnoDB其数据文件本身就是索引文件相比MyISAM索引文件和数据文件是分离的其表数据文件本身就是按BTree组织的一个索引结构树的节点data域保存了完整的数据记录这个索引的key是数据表的主键因此InnoDB表数据文件本身就是主索引这被称为“聚簇索引”或者聚集索引而其余的索引都作为辅助索引辅助索引的data域存储相应记录主键的值而不是地址这也是和MyISAM不同的地方。
在根据主键索引搜索时直接找到key所在的节点即可取出数据根据辅助索引查找时则需要先取出主键的值再走一遍主索引。 因此在设计表的时候不建议使用过长的字段为主键也不建议使用非单调的字段作为主键这样会造成主索引频繁分裂。
总结InnoDB 主键索引使用的是聚簇索引MyISAM 不管是主键索引还是二级索引使用的都是非聚簇索引。
11MySQL中有几种索引类型可以简单说说吗
FULLTEXT 即为全文索引目前只有MyISAM引擎支持。其可以在CREATE TABLE ALTER TABLE CREATE INDEX 使用不过目前只有 CHAR、VARCHAR TEXT 列上可以创建全文索引。HASH 由于HASH的唯一几乎100%的唯一及类似键值对的形式很适合作为索引。HASH索引可以一次定位不需要像树形索引那样逐层查找,因此具有极高的效率。但是这种高效是有条件的即只在“”和“in”条件下高效对于范围查询、排序及组合索引仍然效率不高。BTREE BTREE索引就是一种将索引值按一定的算法存入一个树形的数据结构中二叉树每次查询都是从树的入口root开始依次遍历node获取leaf。这是MySQL里默认和最常用的索引类型。RTREE RTREE在MySQL很少使用仅支持geometry数据类型支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。相对于BTREERTREE的优势在于范围查找。
12覆盖索引是什么
如果一个索引包含或者说覆盖所有需要查询的字段的值我们就称 之为“覆盖索引”。
我们知道在InnoDB存储引 擎中如果不是主键索引叶子节点存储的是主键值。最终还是要“回表”也就是要通过主键再查找一次,这样就 会比较慢。覆盖索引就是把要查询出的列和索引是对应的不做回表操作
13非聚簇索引一定会回表查询吗?
不一定这涉及到查询语句所要求的字段是否全部命中了索引如果全部命中了索引那么就不必再进行回表查询。
举个简单的例子假设我们在员工表的年龄上建立了索引那么当进行select age from employee where age 20的查询时在索引的叶子节点上已经包含了age信息不会再次进行回表查询。
14联合索引是什么?为什么需要注意联合索引中的顺序?
MySQL 可以使用多个字段同时建立一个索引叫做联合索引。在联合索引中如果想要命中索引需要按照建立索引时的字段顺序挨个使用否则无法命中索引。
具体原因为
MySQL 使用索引时需要索引有序假设现在建立了name,age,school的联合索引那么索引的排序为先按照name排序如果 name 相同则按照 age 排序如果 age 的值也相等则按照 school 进行排序。
当进行查询时此时索引仅仅按照 name 严格有序因此必须首先使用 name 字段进行等值查询之后对于匹配到的列而言其按照 age 字段严格有序此时可以使用 age 字段用做索引查找以此类推。因此在建立联合索引的时候应该注意索引列的顺序一般情况下将查询需求频繁或者字段选择性高的列放在前面。此外可以根据特例的查询或者表结构进行单独的调整。
15创建的索引有没有被使用到?或者说怎么才可以知道这条语句运行很慢的原因?
MySQL 提供了 explain 命令来查看语句的执行计划MySQL 在执行某个语句之前会将该语句过一遍查询优化器之后会拿到对语句的分析也就是执行计划其中包含了许多信息。可以通过其中和索引有关的信息来分析是否命中了索引例如possilbe_keykeykey_len等字段分别说明了此语句可能会使用的索引实际使用的索引以及使用的索引长度。
“执行计划”中需要知道的几个“关键字”
id 编号select_type 查询类型table 表type 类型possible_keys 预测用到的索引key 实际使用的索引key_len 实际使用索引的长度ref 表之间的引用rows 通过索引查询到的数据量Extra 额外的信息
16那么在哪些情况下会发生针对该列创建了索引但是在查询的时候并没有使用呢?
使用不等于查询列参与了数学运算或者函数在字符串 like 时左边是通配符。类似于’%aaa’。当 mysql 分析全表扫描比使用索引快的时候不使用索引。当使用联合索引前面一个条件为范围查询后面的即使符合最左前缀原则也无法使用索引。
以上情况MySQL无法使用索引。
17为什么Mysql用B树做索引而不用B-树或红黑树、二叉树
主要原因B树只要遍历叶子节点就可以实现整棵树的遍历而且在数据库中基于范围的查询是非常频繁的而B树只能中序遍历所有节点效率太低。
B-tree
B树的磁盘读写代价更低B树的内部节点并没有指向关键字具体信息的指针因此其内部节点相对B(B-)树更小如果把所有同一内部节点的关键字存放在同一盘块中那么盘块所能容纳的关键字数量也越多一次性读入内存的需要查找的关键字也就越多相对IO读写次数就降低了。由于B树的数据都存储在叶子结点中分支结点均为索引方便扫库只需要扫一遍叶子结点即可但是B树因为其分支结点同样存储着数据我们要找到具体的数据需要进行一次中序遍历按序来扫所以B树更加适合在区间查询的情况所以通常B树用于数据库索引。
Hash
虽然可以快速定位但是没有顺序IO复杂度高基于Hash表实现只有Memory存储引擎显式支持哈希索引 适合等值查询如、in()、不支持范围查询 因为不是按照索引值顺序存储的就不能像BTree索引一样利用索引完成排序 Hash索引在查询等值时非常快 因为Hash索引始终索引的所有列的全部内容所以不支持部分索引列的匹配查找 如果有大量重复键值得情况下哈希索引的效率会很低因为存在哈希碰撞问题 。
二叉树 树的高度不均匀不能自平衡查找效率跟数据有关树的高度并且IO代价高。
红黑树 树的高度随着数据量增加而增加IO代价高。
18MySQL索引种类
普通索引、唯一索引(主键索引、唯一索引)、联合索引、全文索引、空间索引
19索引在什么情况下遵循最左前缀的规则
在建立了联合索引的前提条件下数据库会一直从左向右的顺序依次查找直到遇到了范围查询(,,between,like等)
二、锁相关
1对 MySQL 的锁了解吗?
当数据库有并发事务的时候,可能会产生数据的不一致,这时候需要一些机制来保证访问的次序,锁机制就是这样的一个机制.
就像酒店的房间,如果大家随意进出,就会出现多人抢夺同一个房间的情况,而在房间上装上锁,申请到钥匙的人才可以入住并且将房间锁起来,其他人只有等他使用完毕才可以再次使用.
2MySQL 锁的分类
Mysql中锁的分类按照不同类型的划分可以分成不同的锁
按照 锁的粒度 划分可以分成
行锁表锁页锁
按照 使用的方式 划分可以分为
共享锁排它锁
按照 思想 的划分
乐观锁悲观锁
3行级锁、表级锁、页级锁的描述与特点
行级锁
描述行级锁是mysql中锁定粒度最细的一种锁。表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突其加锁粒度最小但加锁的开销也最大。行级锁分为共享锁和排他锁特点开销大加锁慢会出现死锁。发生锁冲突的概率最低并发度也最高。
表级锁
描述表级锁是mysql中锁定粒度最大的一种锁表示对当前操作的整张表加锁它实现简单资源消耗较少被大部分mysql引擎支持。最常使用的MyISAM与InnoDB都支持表级锁定。表级锁定分为表共享读锁共享锁与表独占写锁排他锁特点 开销小加锁快不会出现死锁。发生锁冲突的概率最高并发度也最低。
页级锁
描述页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快但冲突多行级冲突少但速度慢。因此采取了折衷的页级锁一次锁定相邻的一组记录。BDB 支持页级锁。特点开销和加锁时间界于表锁和行锁之间会出现死锁锁定粒度界于表锁和行锁之间并发度一般。
4共享锁 、 排他锁的描述
共享锁 描述 共享锁又称读锁是读取操作创建的锁。其他用户可以并发读取数据但任何事务都不能对数据进行修改获取数据上的排他锁直到已释放所有共享锁。 如果事务T对数据A加上共享锁后则其他事务只能对A再加共享锁不能加排他锁。获准共享锁的事务只能读数据不能修改数据。 用法 SELECT … LOCK IN SHARE MODE;在查询语句后面增加LOCK IN SHARE MODEMySQL 就会对查询结果中的每行都加共享锁当没有其他线程对查询结果集中的任何一行使用排他锁时可以成功申请共享锁否则会被阻塞。其他线程也可以读取使用了共享锁的表而且这些线程读取的是同一个版本的数据。
排他锁 描述 排他锁又称写锁、独占锁如果事务T对数据A加上排他锁后则其他事务不能再对A加任何类型的封锁。获准排他锁的事务既能读数据又能修改数据。 用法 SELECT … FOR UPDATE;在查询语句后面增加FOR UPDATEMySQL 就会对查询结果中的每行都加排他锁当没有其他线程对查询结果集中的任何一行使用排他锁时可以成功申请排他锁否则会被阻塞。 用上面的例子来说就是用户的行为有两种,一种是来看房,多个用户一起看房是可以接受的. 一种是真正的入住一晚,在这期间,无论是想入住的还是想看房的都不可以. 5悲观锁与乐观锁
乐观锁(Optimistic Lock)假设不会发生并发冲突只在提交操作时检查是否违反数据完整性。 乐观锁不能解决脏读的问题。 乐观锁, 顾名思义就是很乐观每次去拿数据的时候都认为别人不会修改所以不会上锁但是在更新的时候会判断一下在此期间别人有没有去更新这个数据可以使用版本号等机制。乐观锁适用于多读的应用类型这样可以提高吞吐量像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。 悲观锁(Pessimistic Lock)假定会发生并发冲突屏蔽一切可能违反数据完整性的操作。 悲观锁顾名思义就是很悲观每次去拿数据的时候都认为别人会修改所以每次在拿数据的时候都会上锁这样别人想拿这个数据就会block直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制比如行锁表锁等读锁写锁等都是在做操作之前先上锁。 6数据库悲观锁和乐观锁的原理和应用场景分别有什么
悲观锁先获取锁再进行业务操作一般就是利用类似 SELECT … FOR UPDATE 这样的语句对数据加锁避免其他事务意外修改数据。当数据库执行SELECT … FOR UPDATE时会获取被select中的数据行的行锁select for update获取的行锁会在当前事务结束时自动释放因此必须在事务中使用。
乐观锁先进行业务操作只在最后实际更新数据时进行检查数据是否被更新过。Java 并发包中的 AtomicFieldUpdater 类似也是利用 CAS 机制并不会对数据加锁而是通过对比数据的时间戳或者版本号来实现乐观锁需要的版本判断。
7MySQL常用存储引擎的锁机制
MyISAM和MEMORY采用表级锁(table-level locking)BDB采用页面锁(page-level locking)或表级锁默认为页面锁InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁
8InnoDB 存储引擎有几种锁算法
Record Lock — 单个行记录上的锁Gap Lock — 间隙锁锁定一个范围不包括记录本身Next-Key Lock — 锁定一个范围包括记录本身。
9什么是死锁
是指二个或者二个以上的进程在执行时候因为争夺资源造成相互等待的现象进程一直处于等待中无法得到释放这种状态就叫做死锁。
10死锁出现的案列
批量入库存在则更新不存在则插入insert into tab(xx,xx) on duplicate key update xx‘xx’。
11如何处理死锁
通过innodblockwait_timeout来设置超时时间一直等待直到超时发起死锁检测发现死锁之后主动回滚死锁中的事务不需要其他事务继续
12如何避免死锁
为了在单个innodb表上执行多个并发写入操作时避免死锁可以在事务开始时通过为预期要修改行使用select …for update语句来获取必要的锁即使这些行的更改语句是在之后才执行的在事务中如果要更新记录应该直接申请足够级别的锁即排他锁而不应先申请共享锁更新时在申请排他锁。因为这时候当用户在申请排他锁时其他事务可能又已经获得了相同记录的共享锁如果事务需要修改或锁定多个表则应在每个事务中以相同的顺序使用加锁语句。在应用中如果不同的程序会并发获取多个表应尽量约定以相同的顺序来访问表这样可以大大降低产生死锁的机会通过 select …lock in share mode获取行的读锁后如果当前事务在需要对该记录进行更新操作则很有可能造成死锁改变事务隔离级别
13Innodb默认是如何对待死锁的
innodb默认是使用设置死锁时间来让死锁超时的策略默认innodblockwait_timeout设置的时长是50s
14如何开启死锁检测
设置innodbdeadlockdetect设置为on可以主动检测死锁在innodb中这个值默认就是on开启的状态
15什么是全局锁它的应用场景有哪些
全局锁就是对整个数据库实例加锁它的典型使用场景就是做全库逻辑备份这个命令可以使用整个库处于只读状态使用该命令之后数据更新语句数据定义语句更新类事务的提交语句等操作都会被阻塞。
16使用全局锁会导致的问题
如果在主库备份在备份期间不能更新业务停止所以更新业务会处于等待状态如果在从库备份在备份期间不能执行主库同步的binlog导致主从延迟
17优化锁方面你有什么建议
尽量使用较低的隔离级别。精心设计索引 并尽量使用索引访问数据 使加锁更精确 从而减少锁冲突的机会。选择合理的事务大小小事务发生锁冲突的几率也更小。给记录集显示加锁时最好一次性请求足够级别的锁。比如要修改数据的话最好直接申请排他锁而不是先申请共享锁修改时再请求排他锁这样容易产生死锁。不同的程序访问一组表时应尽量约定以相同的顺序访问各表对一个表而言尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会。尽量用相等条件访问数据这样可以避免间隙锁对并发插入的影响。不要申请超过实际需要的锁级别。除非必须查询时不要显示加锁。 MySQL 的 MVCC 可以实现事务中的查询不用加锁优化事务性能MVCC 只在 COMMITTED READ读提交和 REPEATABLE READ可重复读两种隔离级别下工作。对于一些特定的事务可以使用表锁来提高处理速度或减少死锁的可能。
三、存储引擎相关
1MySQL 支持哪些存储引擎?
MySQL 支持多种存储引擎比如InnoDBMyISAMMemoryArchive等等。在大多数的情况下直接选择使用 InnoDB 引擎都是最合适的InnoDB 也是 MySQL 的默认存储引擎。
2InnoDB 和 MyISAM 有什么区别?
InnoDB
是 MySQL 默认的事务型存储引擎只有在需要它不支持的特性时才考虑使用其它存储引擎。实现了四个标准的隔离级别默认级别是可重复读(REPEATABLE READ)。在可重复读隔离级别下通过多版本并发控制(MVCC) 间隙锁(Next-Key Locking)防止幻影读。主索引是聚簇索引在索引中保存了数据从而避免直接读取磁盘因此对查询性能有很大的提升。内部做了很多优化包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。支持真正的在线热备份。其它存储引擎不支持在线热备份要获取一致性视图需要停止对所有表的写入而在读写混合场景中停止写入可能也意味着停止读取。
MyISAM
设计简单数据以紧密格式存储。对于只读数据或者表比较小、可以容忍修复操作则依然可以使用它。提供了大量的特性包括压缩表、空间数据索引等。不支持事务。不支持行级锁只能对整张表加锁读取时会对需要读到的所有表加共享锁写入时则对表加排它锁。但在表有读取操作的同时也可以往表中插入新的记录这被称为并发插入(CONCURRENT INSERT)。
总结
InnoDB 支持事物而 MyISAM 不支持事物InnoDB 支持行级锁表锁而 MyISAM 支持表级锁InnoDB 支持 MVCC而 MyISAM 不支持InnoDB 支持外键而 MyISAM 不支持InnoDB5.7之前不支持全文索引而 MyISAM 支持nnoDB必须有主键没有指定会默认生成一个隐藏列作为主键而MyISAM可以没有
3你了解MySQL的内部构造吗一般可以分为哪两个部分
可以分为服务层和存储引擎层两部分其中 服务层包括连接器、查询缓存、分析器、优化器、执行器等涵盖MySQL的大多数核心服务功能以及所有的内置函数如日期、时间、数学和加密函数等所有跨存储引擎的功能都在这一层实现比如存储过程、触发器、视图等。 存储引擎层负责数据的存储和提取。 其架构模式是插件式的支持InnoDB、MyISAM、Memory等多个存储引擎。现在最常用的存储引擎是InnoDB它从MySQL5.5.5版本开始成为了默认的存储引擎。 4说一下MySQL是如何执行一条SQL的具体步骤有哪些
Server层按顺序执行sql的步骤为
1.客户端请求-
2.连接器验证用户身份给予权限 -
3.查询缓存存在缓存则直接返回不存在则执行后续操作-
4.分析器对SQL进行词法分析和语法分析操作 -
5.优化器主要对执行的sql优化选择最优的执行方案方法 -
6.执行器执行时会先看用户是否有执行权限有才去使用这个引擎提供的接口-
7.去引擎层获取数据返回如果开启查询缓存则会缓存查询结果简单概括
连接器管理连接、权限验证查询缓存命中缓存则直接返回结果分析器对SQL进行词法分析、语法分析判断查询的SQL字段是否存在也是在这步优化器执行计划生成、选择索引执行器操作引擎、返回结果存储引擎存储数据、提供读写接口。
5SQL 的执行顺序
SELECT DISTINCT select_list
FROM left_table join_type
JOIN right_table ON join_condition
WHERE where_condition
GROUP BY group_by_list
HAVING having_condition
ORDER BY order_by_condition
LIMIT limit_number 它的执行顺序你知道吗这道题就给你一个回答。
FROM 连接 首先对 SELECT 语句执行查询时对FROM 关键字两边的表执行连接会形成笛卡尔积这时候会产生一个虚表VT1(virtual table) ON 过滤 然后对 FROM 连接的结果进行 ON 筛选创建 VT2把符合记录的条件存在 VT2 中。 JOIN 连接 第三步如果是 OUTER JOIN(left join、right join) 那么这一步就将添加外部行如果是 left join 就把 ON 过滤条件的左表添加进来如果是 right join 就把右表添加进来从而生成新的虚拟表 VT3。 WHERE 过滤 第四步是执行 WHERE 过滤器对上一步生产的虚拟表引用 WHERE 筛选生成虚拟表 VT4。 GROUP BY 根据 group by 字句中的列会对 VT4 中的记录进行分组操作产生虚拟机表 VT5。果应用了group by那么后面的所有步骤都只能得到的 VT5 的列或者是聚合函数count、sum、avg等。 HAVING 紧跟着 GROUP BY 字句后面的是 HAVING使用 HAVING 过滤会把符合条件的放在 VT6 SELECT 第七步才会执行 SELECT 语句将 VT6 中的结果按照 SELECT 进行刷选生成 VT7 DISTINCT 在第八步中会对 TV7 生成的记录进行去重操作生成 VT8。事实上如果应用了 group by 子句那么 distinct 是多余的原因同样在于分组的时候是将列中唯一的值分成一组同时只为每一组返回一行记录那么所以的记录都将是不相同的。 ORDER BY 应用 order by 子句。按照 order_by_condition 排序 VT8此时返回的一个游标而不是虚拟表。sql 是基于集合的理论的集合不会预先对他的行排序它只是成员的逻辑集合成员的顺序是无关紧要的。 SQL 语句执行的过程如下
6简述触发器、函数、视图、存储过程
触发器使用触发器可以定制用户对表进行【增、删、改】操作时前后的行为,触发器无法由用户直接调用而知由于对表的【增/删/改】操作被动引发的函数是MySQL数据库提供的内部函数(当然也可以自定义函数)。这些内部函数可以帮助用户更加方便-的处理表中的数据视图视图是虚拟表或逻辑表它被定义为具有连接的SQL SELECT查询语句。存储过程存储过程是存储在数据库目录中的一坨的声明性SQL语句数据库中的一个重要对象,有效提高了程序的性能
6听说过视图吗那游标呢
视图是一种虚拟的表通常是有一个表或者多个表的行或列的子集具有和物理表相同的功能 游标是对查询出来的结果集作为一个单元来有效的处理。一般不使用游标但是需要逐条处理数据的时候游标显得十分重要。
6视图的作用是什么可以更改吗
视图是虚拟的表与包含数据的表不一样视图只包含使用时动态检索数据的查询不包含任何列或数据。使用视图可以简化复杂的 sql 操作隐藏具体的细节保护数据视图创建后可以使用与表相同的方式利用它们。
视图不能被索引也不能有关联的触发器或默认值如果视图本身内有order by 则对视图再次order by将被覆盖。
创建视图
create view xxx as xxxx对于某些视图比如未使用联结子查询分组聚集函数Distinct Union等是可以对其更新的对视图的更新将对基表进行更新但是视图主要用于简化检索保护数据并不用于更新而且大部分视图都不可以更新。
四、事务相关
1什么是事务?
事务是一系列的数据库操作他们要符合 ACID 特性事务是数据库应用的基本单位。MySQL 事务主要用于处理操作量大复杂度高的数据。
2ACID是什么?可以详细说一下吗?
AAtomicity原子性就是要么全部成功要么全部失败。不可能只执行一部分操作。CConsistency一致性系统(数据库)总是从一个一致性的状态转移到另一个一致性的状态不会存在中间状态。IIsolation隔离性通常来说一个事务在完全提交之前对其他事务是不可见的.注意前面的通常来说加了红色意味着有例外情况。DDurability持久性一旦事务提交那么就永远是这样子了哪怕系统崩溃也不会影响到这个事务的结果。
3MySQL中为什么要有事务回滚机制
而在 MySQL 中恢复机制是通过回滚日志undo log实现的所有事务进行的修改都会先记录到这个回滚日志中然后在对数据库中的对应行进行写入。当事务已经被提交之后就无法再次回滚了。
回滚日志作用
能够在发生错误或者用户执行 ROLLBACK 时提供回滚相关的信息在整个系统发生崩溃、数据库进程直接被杀死后当用户再次启动数据库进程时还能够立刻通过查询回滚日志将之前未完成的事务进行回滚这也就需要回滚日志必须先于数据持久化到磁盘上是我们需要先写日志后写数据库的主要原因。
4数据库并发事务会带来哪些问题
数据库并发事务会带来 脏读、幻读、丢弃更改、不可重复读 这四个常见问题其中
脏读A 事务读取到了 B 事务未提交的内容但是之后B事务满足一致性等特性而做了回滚操作那么读取事务得到的结果就是脏数据了。幻读A 事务读取了一个范围的内容而同时 B 事务在此期间插入删除了一条数据。造成幻觉。丢弃修改两个写事务T1 T2同时对A0进行递增操作结果T2覆盖T1导致最终结果是1 而不是2事务被覆盖不可重复读当设置T2事务只能读取 T1 事务已经提交的部分T2 读取一个数据然后T1 对该数据做了修改。如果 T2 再次读取这个数据此时读取的结果和第一次读取的结果不同。
五、表结构相关
1为什么要尽量设定一个主键?
主键是数据库确保数据行在整张表唯一性的保障,即使业务上本张表没有主键,也建议添加一个自增长的 ID 列作为主键.设定了主键之后,在后续的删改查的时候可能更加快速以及确保操作数据范围安全。
2主键使用自增 ID 还是 UUID?
推荐使用自增ID不要使用 UUID。 因为在 InnoDB存储引擎中,主键索引是作为聚簇索引存在的,也就是说,主键索引的B树叶子节点上存储了主键索引以及全部的数据(按照顺序),如果主键索引是自增ID,那么只需要不断向后排列即可,如果是UUID,由于到来的ID与原来的大小不确定,会造成非常多的数据插入,数据移动,然后导致产生很多的内存碎片,进而造成插入性能的下降. 总之在数据量大一些的情况下用自增主键性能会好一些。
图片来源于《高性能MySQL》: 其中默认后缀为使用自增ID,_uuid为使用 UUID为主键的测试测试了插入 100w 行和 300w 行的性能。
3字段为什么要求定义为not null?
null 值会占用更多的字节且会在程序中造成很多与预期不符的情况。
4如果要存储用户的密码散列,应该使用什么字段进行存储?
密码散列用户身份证号等固定长度的字符串应该使用 char 而不是 varchar 来存储这样可以节省空间且提高检索效率。
5说一说Drop、Delete与Truncate的共同点和区别?
Drop直接删掉表;Truncate删除表中数据再插入时自增长id又从1开始 ;Delete删除表中数据可以加where字句。
6数据库中的主键、超键、候选键、外键是什么
超键在关系中能唯一标识元组的属性集称为关系模式的超键候选键不含有多余属性的超键称为候选键。也就是在候选键中若再删除属性就不是键了主键用户选作元组标识的一个候选键程序主键外键如果关系模式R中属性K是其它模式的主键那么k在模式R中称为外键。
六、其他问题
1MySQL 中的 varchar 和 char 有什么区别?
char 是一个定长字段假如申请了char(10)的空间那么无论实际存储多少内容。该字段都占用 10 个字符而 varchar 是变长的也就是说申请的只是最大长度占用的空间为实际字符长度 1最后一个字符存储使用了多长的空间
在检索效率上来讲char varchar因此在使用中如果确定某个字段的值的长度可以使用 char否则应该尽量使用 varchar。例如存储用户 MD5 加密后的密码则应该使用 char。
2varchar(10) 和 int(10) 代表什么含义?
varchar的10代表了申请的空间长度,也是可以存储的数据的最大长度,而int的10只是代表了展示的长度,不足10位以0填充.也就是说,varchar(10) 和int(10)所能存储的数字大小以及占用的空间都是相同的,只是在展示时按照长度展示.
3MySQL的binlog有有几种录入格式?分别有什么区别?
有三种格式statement、row和mixed.
statement模式下,记录单元为语句.即每一个sql造成的影响会记录.由于sql的执行是有上下文的,因此在保存的时候需要保存相关的信息,同时还有一些使用了函数之类的语句无法被记录复制.row级别下,记录单元为每一行的改动,基本是可以全部记下来但是由于很多操作,会导致大量行的改动(比如alter table),因此这种模式的文件保存的信息太多,日志量太大.mixed. 一种折中的方案,普通操作使用statement记录,当无法使用statement的时候使用row.
4超大分页怎么处理?
超大的分页一般从两个方向上来解决.
数据库层面,这也是我们主要集中关注的(虽然收效没那么大),类似于select * from table where age 20 limit 1000000,10这种查询其实也是有可以优化的余地的. 这条语句需要load1000000数据然后基本上全部丢弃,只取10条当然比较慢. 当时我们可以修改为select * from table where id in (select id from table where age 20 limit 1000000,10).这样虽然也load了一百万的数据,但是由于索引覆盖,要查询的所有字段都在索引中,所以速度会很快. 同时如果ID连续的好,我们还可以select * from table where id 1000000 limit 10,效率也是不错的,优化的可能性有许多种,但是核心思想都一样,就是减少load的数据.从需求的角度减少这种请求…主要是不做类似的需求(直接跳转到几百万页之后的具体某一页.只允许逐页查看或者按照给定的路线走,这样可预测,可缓存)以及防止ID泄漏且连续被人恶意攻击.
5说一说三个范式?
第一范式: 每个列都不可以再拆分。第二范式: 非主键列完全依赖于主键而不能是依赖于主键的一部分。第三范式: 非主键列只依赖于主键不依赖于其他非主键。 在设计数据库结构的时候要尽量遵守三范式如果不遵守必须有足够的理由。比如性能事实上我们经常会为了性能而妥协数据库的设计。 6left join、right join以及inner join的区别?
left join左关联主表在左边右边为从表。如果左侧的主表中没有关联字段会用null 填满right join右关联 主表在右边和letf join相反inner join 内关联只会显示主表和从表相关联的字段不会出现null
7什么是数据库约束,常见的约束有哪几种?
数据库约束用于保证数据库、表数据的完整性正确性和一致性。 可以通过定义约束\索引\触发器来保证数据的完整性。总体来讲,约束可以分为:
主键约束primary key外键约束foreign key唯一约束unique检查约束check空值约束not null默认值约束default
8什么是sql注入
SQL注入攻击指的是通过构建特殊的输入作为参数传入Web应用程序而这些输入大都是SQL语法里的一些组合通过执行SQL语句进而执行攻击者所要的操作其主要原因是程序没有细致地过滤用户输入的数据致使非法数据侵入系统。
9简述数据库的读写分离
读写分离为了确保数据库产品的稳数据定性很多数据库拥有双机热备功能。也就是第一台数据库服务器是对外提供增删改业务的生产服务器第二台数据库服务器主要进行读的操作。
10MySQL数据库cpu飙升的话要怎么处理呢
排查过程
使用top 命令观察确定是mysqld导致还是其他原因。如果是mysqld导致的show processlist查看session情况确定是不是有消耗资源的sql在运行。找出消耗高的 sql看看执行计划是否准确 索引是否缺失数据量是否太大。
处理
kill 掉这些线程(同时观察 cpu 使用率是否下降)进行相应的调整(比如说加索引、改 sql、改内存参数)重新跑这些 SQL。
11MYSQL的主从延迟你怎么解决
主从复制分了五个步骤进行
步骤一主库的更新事件(update、insert、delete)被写到binlog步骤二从库发起连接连接到主库。步骤三此时主库创建一个binlog dump thread把binlog的内容发送到从库。步骤四从库启动之后创建一个I/O线程读取主库传过来的binlog内容并写入到relay log步骤五还会创建一个SQL线程从relay log里面读取内容从Exec_Master_Log_Pos位置开始执行读取到的更新事件将更新内容写入到slave的db
主从同步延迟的原因
一个服务器开放个链接给客户端来连接的这样有会有大并发的更新操作, 但是从服务器的里面读取binlog的线程仅有一个当某个SQL在从服务器上执行的时间稍长 或者由于某个SQL要进行锁表就会导致主服务器的SQL大量积压未被同步到从服务器里。这就导致了主从不一致 也就是主从延迟。
主从同步延迟的解决办法
主服务器要负责更新操作对安全性的要求比从服务器要高所以有些设置参数可以修改比如sync_binlog1innodb_flush_log_at_trx_commit 1 之类的设置等。选择更好的硬件设备作为slave。把一台从服务器当度作为备份使用 而不提供查询 那边他的负载下来了 执行relay log 里面的SQL效率自然就高了。增加从服务器这个目的还是分散读的压力从而降低服务器负载。
12如果让你做分库与分表的设计简单说说你会怎么做
分库分表方案:
水平分库以字段为依据按照一定策略hash、range等将一个库中的数据拆分到多个库中。水平分表以字段为依据按照一定策略hash、range等将一个表中的数据拆分到多个表中。垂直分库以表为依据按照业务归属不同将不同的表拆分到不同的库中。垂直分表以字段为依据按照字段的活跃性将表中字段拆到不同的表主表和扩展表中。
常用的分库分表中间件
sharding-jdbcMycat
分库分表可能遇到的问题
事务问题需要用分布式事务跨节点Join的问题解决这一问题可以分两次查询实现跨节点的count,order by,group by以及聚合函数问题分别在各个节点上得到结果后在应用程序端进行合并。数据迁移容量规划扩容等问题ID问题数据库被切分后不能再依赖数据库自身的主键生成机制最简单可以考虑UUID跨分片的排序分页问题
13count(1)、count(*)与count(列名)的执行区别
执行效果上
count(*)包括了所有的列相当于行数在统计结果的时候 不会忽略列值为NULLcount(1)包括了忽略所有列用1代表代码行在统计结果的时候 不会忽略列值为NULLcount(列名)只包括列名那一列在统计结果的时候会忽略列值为空这里的空不是只空字符串或者0而是表示null的计数 即某个字段值为NULL时不统计。
执行效率上
列名为主键count(列名)会比count(1)快列名不为主键count(1)会比count(列名)快如果表多个列并且没有主键则 count1 的执行效率优于 count*如果有主键则 select count主键的执行效率是最优的如果表只有一个字段则 select count*最优。
14sql 语句中where 11的作用
SQL注入语法规范拷贝表复制表结构11的坏处
见文章
https://blog.csdn.net/SeizeeveryDay/article/details/109523669?utm_mediumdistribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromMachineLearnPai2%7Edefault-10.essearch_pc_relevantdepth_1-utm_sourcedistribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromMachineLearnPai2%7Edefault-10.essearch_pc_relevant
15sql中null与空值的区别
1.占用空间区别空值(’’)的长度是0是不占用空间的而的NULL长度是NULL是占用空间的2.插入/查询方式区别NULL值查询使用is null/is not null查询而空值(’’)可以使用或者!、、等算术运算符。3.COUNT 和 IFNULL函数使用 COUNT(字段) 统计会过滤掉 NULL 值但是不会过滤掉空值。4.索引字段说明在有NULL值的字段上使用常用的索引如普通索引、复合索引、全文索引等不会使索引失效。在官网查看在空间索引的情况下说明了 索引列必须为NOT NULL。
七、优化相关
1日常工作中你是怎么优化SQL的
1.1表结构优化
尽量使用数字型字段 若只含数值信息的字段尽量不要设计为字符型这会降低查询和连接的性能并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符而对于数字型而言只需要比较一次就够了。 尽可能的使用 varchar 代替 char 变长字段存储空间小可以节省存储空间。 当索引列大量重复数据时可以把索引删除掉 比如有一列是性别几乎只有男、女、未知这样的索引是无效的。 1.2查询优化
应尽量避免在 where 子句中使用!或操作符应尽量避免在 where 子句中使用 or 来连接条件任何查询也不要出现select *避免在 where 子句中对字段进行 null 值判断
1.3索引优化
对作为查询条件和 order by的字段建立索引避免建立过多的索引多使用组合索引
1.4慢查询优化
分析语句是否加载了不必要的字段/数据分析 SQL 执行句话是否命中索引等如果 SQL 很复杂优化 SQL 结构如果表数据量太大考虑分表
1、sql注入问题解决
SQL注入攻击是一种常见的网络安全威胁它利用恶意构造的SQL查询语句来绕过应用程序的输入验证从而执行未经授权的操作或者获取敏感数据。网络物理隔绝并不能直接解决SQL注入攻击问题因为SQL注入攻击是通过恶意构造的SQL查询语句进行的与网络物理隔离无关。
要防止SQL注入攻击需要在应用程序层面采取相应的防护措施。以下是一些常见的防护方法
1. 使用参数化查询或预编译语句使用参数化查询或称为绑定变量可以将用户输入与SQL查询语句分开从而消除注入漏洞的可能性。
2. 输入验证和过滤对用户输入进行验证和过滤确保输入符合预期的格式和范围避免非法字符进入SQL查询语句。
3. 最小特权原则限制数据库用户的权限确保其只能执行必要的操作避免恶意用户利用注入漏洞获取敏感数据或执行危险操作。
4.安全编码实践开发人员应该了解SQL注入攻击的原理和常见手段并采用安全编码实践如避免使用拼接字符串的方式构建SQL查询语句使用框架提供的安全接口等。
5. 使用参数化查询或预编译语句通过使用参数化查询或预编译语句可将用户提供的输入与SQL语句分开处理从而避免将用户输入作为SQL查询的一部分。
6. 输入验证和过滤在接收用户输入之前对其进行验证和过滤确保只接受符合预期格式的输入并拒绝包含有害字符的输入。可以使用正则表达式或白名单过滤器对输入进行检查。
7. 最小权限原则数据库用户应被授予最小权限即仅允许其执行必要的操作以限制攻击者利用注入漏洞对数据库进行恶意操作的能力。
8. 错误处理和日志记录在应用程序中实施严格的错误处理机制并对异常情况进行适当的日志记录。这样可以帮助检测到潜在的SQL注入攻击并提供调试和追踪攻击者的信息。
9. 定期更新和维护及时应用数据库供应商发布的安全补丁和更新以修复已知的漏洞并确保数据库系统处于最新和最安全的状态。2.熟悉数据库 SQL 操作常用数据库(mysql、sqlserver 等) 的运行机制和体系构架,
数据库SQL操作是指使用结构化查询语言SQL对数据库进行增删改查等操作的技术。常用的数据库包括MySQL、SQL Server等。
MySQL是一种开源的关系型数据库管理系统采用客户端-服务器模式。它由一个或多个服务器组成每个服务器保存着一部分数据。应用程序通过客户端与服务器进行通信并发送SQL语句来操作数据。
MySQL的体系构架主要包括以下几个组件
连接器Connection Manager负责与客户端建立连接并进行身份验证和权限校验。查询缓存Query Cache将查询结果缓存起来当有相同查询再次执行时可以直接返回缓存的结果提高查询速度。查询解析器Query Parser负责对SQL语句进行解析确定语法是否正确并生成相应的查询执行计划。优化器Optimizer通过对查询进行分析选择最优的查询执行计划以提高查询性能。执行器Executor根据查询执行计划执行具体的SQL操作包括读取数据、写入数据等。存储引擎Storage Engine负责存储数据和处理数据的底层引擎MySQL支持多种存储引擎如InnoDB、MyISAM等。
SQL Server是由Microsoft开发的关系型数据库管理系统也采用了客户端-服务器模式。它的体系构架类似于MySQL但在一些细节上有所不同。
SQL Server的体系构架主要包括以下几个组件
连接器Connection Manager负责与客户端建立连接并进行身份验证和权限校验。查询分析器Query Analyzer负责对SQL语句进行解析和优化生成查询执行计划。执行引擎Execution Engine根据查询执行计划执行具体的SQL操作包括读取数据、写入数据等。存储引擎Storage Engine负责存储数据和处理数据的底层引擎SQL Server内置了多种存储引擎如InnoDB、MyISAM等。
以上是MySQL和SQL Server两种常用数据库的运行机制和体系构架的简介。在实际应用中根据需要选择适合的数据库并掌握相应的SQL操作技术可以高效地管理和操作数据。
3、假设有个学生的表要实现同个班级里学号不能重复请问如何实现面试被问过的题 要实现同一个班级里学号不能重复的功能可以通过以下步骤来实现在学生表中添加一个唯一约束(unique constraint)或者创建一个唯一索引(unique index)来保证学号的唯一性。这将阻止插入或更新操作中出现重复的学号。如果你使用的是SQL语言可以使用以下语句来添加唯一约束ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE (学号列名);或者使用以下语句来创建唯一索引CREATE UNIQUE INDEX 索引名 ON 表名 (学号列名);当你插入或更新学生数据时数据库会自动检查学号是否已存在。如果学号重复将会抛出错误并阻止操作。以上方法可以有效地确保同一个班级里学号的唯一性。希望对你有帮助如有任何其他问题请随时提问。CREATE TABLE 学生表 (学号 INT PRIMARY KEY,姓名 VARCHAR(50),班级 VARCHAR(50),..其他字段..
);ALTER TABLE 学生表
ADD CONSTRAINT 唯一学号 UNIQUE (学号);
Mysql常规27种sql查询语句
话不多说直接上干货
1、查询部分列 select 列名 from 表名 例员工表中查询员工编号、名字、邮箱 select employee_id,employee_name,employee_email from t_employee;
2、查询所有列 例查询所有员工信息 select * from t_employee;
3、对列中的数据进行运算 例查询员工编号、姓名、月薪、年薪 select employee_id,employee_name,employee_salary,employee_salary12; 注算术运算符(加)、-(减)、(乘)、/(除);%为占位符而非模运算符
4、去重查询DISTINCT 列名 例查询员工表中所有的经理ID select distinct manager_id from t_employee;
5、排序查询 排序规则 ASC 升序 DESC 降序 select 列名 from 表名 order by 排序列 排序规则 依据单列排序 例查询员工编号、姓名、月薪薪资按高到低排序 select employee_id,employee_name,employee_salary from t_employee order by employee_salary DESC; 依据多列排序 例 查询员工编号、姓名、月薪。按照薪资降序排列薪资相同时候按照编号进行升序排列 select employee_id,employee_name,employee_salary from t_employee order by employee_salary DESC,employee_id ASC;
6、条件查询where select 列名 from 表名 where 条件 补充where查询条件筛选符号条件的查询结果为布尔表达式 例查询员工为rich_wang的员工信息 select * from t_employee where employee_name “rich_wang”
7、等值判断() 查询薪资是11000的员工信息(编号、名字、薪资) select employee_id,employee_name,employee_salary from t_employee where employee_salary 11000;
8、逻辑判断(and,or,not) 例 查询薪资是11000且提成是0.3的员工信息(编号、名字、薪资) select employee_id,employee_name,employee_salary from t_employee where employee_salary 11000 and employee_pct 0.3; or和not同理
9、不等值判断(,,,,!,) 例 查询员工薪资在10000-20000之间的员工信息(编号、名字、薪资) select employee_id,employee_name,employee_salary from t_employee where employee_salary10000 and employee_salary20000 其他几个同理
10、区间判断(between and) 例查询员工薪资在10000-20000之间的员工信息 select employee_id,employee_name,employee_salary from t_employee where employee_salary between 10000 and 20000; 说明区间判断中小值在前大值在后否则得不到正确结果
11、NULL值判断(IS NULL,IS NOT NULL) 列名 IS NULL 列名 IS NOT NULL 例查询没有提成的员工信息(编号、名字、薪资、提成) select employee_id,employee_name,employee_salary,employee_pct from t_employee where employee_pct IS NULL; IS NOT NULL同理 注不能是NULL,要拿到NULL值需要IS NULL和IS NOT NULL来获取
12、枚举查询 IN (值1,值2,值3…) 例 查询部门编号为70,80,90的员工信息(编号、名字、薪资、部门编号) select e,manager_id,employee_name,employee_salary,employee_dpt from t_employee where employee_dpt in (70,80,90); 注in的查询效率较低推荐使用多条件拼接查询
13、模糊查询(LIKE、%) LIKE_(单个任意字符)列名 LIKE ‘王_’; %(任意长度字符)列名 LIKE ‘王%’; 注模糊查询只能和LIKE关键字结合使用 例 查询名字以’王’开头的员工信息(编号、名字、薪资、部门编号) select employee_id,employee_name,employee_salary,employee_dpt from t_employee where employee_name LIKE ‘王%’; 例 查询名字以王开头且长度为3的员工信息(编号、名字、薪资、部门编号) select employee_id,employee_name,employee_salary,employee_pct from t_employee where employee_name LIKE ‘王___’; 注一个_占一位
14、分支结构查询CASE END CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 WHEN 条件3 THEN 结果3 ELSE 除以上条件外的结果 … END 说明通过使用CASE END进行条件判断每条数据对应生成一个值类似Java中的switch case 例 查询员工信息(编号、名字、薪资、薪资级别根据级别条件生成) select employee_id、employee_name,employee_salary, CASE WHEN employee_salary 10000 THEN ‘A’ WHEN employee_salary 8000 AND employee_salary10000 THEN ‘B’ WHEN employee_salary 6000 AND employee_salary8000 THEN ‘C’ WHEN employee_salary 4000 AND employee_salary6000 THEN ‘D’ ELSE ‘E’ END AS “薪资级别” from t_employee;
15、时间查询-时间函数 select 时间函数(参数列表) 注执行时间函数查询会自动生成一张虚表(一列一行) 相关时间函数 SYSDATE() 当前系统时间(年月日时分秒) CURDATE() 获取当前日期(年月日) CURTIME() 获取当前时间(时分秒) WEEK(DATE) 获取指定日期为一年中的第几周 YEAR(DATE) 获取指定日期的年份 HOUR(TIME) 获取指定时间的小时值 MINUTE(TIME) 获取指定时间分钟值 DATEDIFF(DATE1,DATE2) 获取DATE1和DATE2之间相差的天数 ADDDATE(DATE,N) 计算DATE加上N天后的日期 例 获取系统当前时间 select SYSDATE() 获取当前日期 select CURDATE 获取指定日期中的年份 select YEAR(‘2022-07-03’) 指定日期之前的天数 select DATEDIFF(‘2021-07-03’,‘2022-07-03’) 其他时间函数同理
16、字符串查询-字符串函数 select 字符串函数(参数列表) 相关字符串函数 CONCAT(str1,str2,…) 将多个字符串拼接 INSERT(str,pos,len,newStr) 将str字符串中指定的pos位置开始长度为len的内容替换为newStr LOWER(str) 将指定字符串str转换为小写 UPPER(str) 将指定字符串str转换为大写 SUBSTRING(str,num,len) 将指定字符串str中从位置num开始截取len个长度的子串 例 拼接字符串 select CONCAT(‘My’,‘S’,‘QL’);//MUSQL 字符串替换 select INSERT(‘学习什么数据库’,3,2,‘MYSQL’);//学习MYSQL数据库 其他几个同理 注在mysql中字符串下标从1开始而非像Java中从0开始
17、聚合函数(SUM、AVG、MAX、MIN、COUNT) select 聚合函数(列名) from 表名 相关聚合函数 SUM() 对所有行中的单列结果求和 AVG() 平均值 MAX() 最大值 MIN() 最小值 COUNT() 求表总行数 例 统计所有员工每月的薪资总和 select SUM(employee_salary) from t_employee 其他几个同理 注聚合函数会自动忽略NULL值不对NUL进行统计
18、分组查询GROUP BY select 列名 from 表名 where 条件 GROUP BY 分组依据(列名) 注分组依据必须在where之后生效 例 查询各部门的总人数 分析 先按部门编号(employee_dpt)进行分组,再针对各部门人数进行统计(COUNT) select employee_dpt AS ‘部门’,COUNT(employee_id) from t_employee GROUP BY employee_dpt
查询各部门的平均工资 select employee_dpt,AVG(employee_salary) from t_employee GROUP BY employee_dpt
查询各个部门、各个岗位的人数 分析 先按部门编号进行分组再按岗位进行分组再对各个部门各个岗位进行COUNT select employee_dpt,employee_job,COUNT(employee_id) AS ‘岗位人数’ from t_employee GROUP BY employee_dpt,employee_job
注分组查询中select显示的列只能是分组依据列或者聚合函数列不能出现其他列注意规避
19、分组过滤查询HAVING select 列名 from 表名 where 条件 GROUP BY 分组列 HAVING 过滤规则 注过滤规则定义对分组后的数据进行过滤 例 统计部门编号70、80、90部门的最高工资 分析 先按部门编号进行分组对分组后的数据过滤出部门编号为70、80、90的部门然后在MAX进行统计 select employee_dpt,MAX(employee_salary) from t_employee GROUP BY employee_dpt HAVING employee_dpt in (70,80,90)
20、限定查询LIMIT select 列名 from 表名 LIMIT 起始行,查询行数 关键字 LIMIT offset_start,row_count 限定查询结果的起始行和总行数 限定查询一般用于分页的场景最多 例 查询前5条数据 select * from t_employee LIMIT 0,5; 注起始行是从0开始代表第一行第二个参数代表的是指定行开始查询几行
查询范围记录表中第三条开始查询10行 select * from t_employee LIMIT 3,10
小结 sql语句的编写顺序 select列名from表名where条件GROUP BY分组依据HAVING过滤条件order by排序列 排序规则 LIMIT 起始行,总条数 sql语句的执行顺序 from-where-GROUP BY-HAVING-select-order by-LIMIT
21、子查询(作为条件判断) select 列名 from 表名 where 条件(子查询结果) 例 查询工资大于员工rich_wang的员工信息 select * from t_employee where employee_salary ( select employee_salary from t_employee where employee_name“rich_wang” ); 注将子查询的一行一列的结果作为外部查询的条件做二次查询子查询结果得到一行一列才能作为外部查询的判断条件
21、子查询(作为枚举查询条件) select 列名 from 表名 where 列名 in (子查询结果) 例 查询与员工rich_wang在同一个部门的员工信息其中rich_wang所属多个部门 select * from t_employee where employee_dpt in ( select employee_dpt from t_employee where employee_name “rich_wang” ); 注将多行一列的子查询结果作为外部查询枚举查询条件
22、子查询(ANY和ALL) 查询工资高于60部门的所有员工信息 高于部分 select * from t_employee where employee_salary ANY( select employee_salary from t_employee where employee_dpt60 ); 高于所有 select * from t_employee where employee_salary ALL( select employee_salary from t_employee where employee_dpt60 ); 注当子查询结果形式为多行单列时可使用ANY或者ALL关键字
23、子查询(作为一张表) select 列名 from 子查询结果集 where 条件 例 查询员工表中工资排名前五的员工信息(编号、名字、薪资) select employee_id,employee_name,employee_salary from (select employee_id,employee_name,employee_salary from t_employee order by employee_salary DESC ) as temp LIMIT 0,5 注将多行多列的结果作为外部查询的一张表进行二次查询
24、合并查询UNION(应用场景较少) select * from 表名1 UNION select * from 表2 select * from 表名1 UNION ALL select * from 表2 例 合并2张表的结果(去除重复记录) select * from t1 UNION select * from t2 注意合并结果的两张表列数必须相同列的数据类型可以不同
合并2张表(保留重复记录) select * from t1 UNION ALL select * from t2
25、表连接查询-内连接查询(INNER JOIN ON) select 列名 from 表1 连接方式 表2 连接条件 通过员工表t_employee和岗位表t_job查询所有有部门的员工信息 sql标准写法 select * from t_employee INNER JOIN t_job ON t_employee.job_id t_job.job_id mysql可这样写 select * from t_employee,t_job where t_employee.job_id t_job.job_id 注意务必要指定连接条件否则会造成笛卡尔积的结果
26、表连接查询-三表连接查询 例 查询所有员工信息包含员工编号姓名部门名称部门所在国家ID 涉及三张表t_employee员工表t_department部门表,t_location国家地区表 select * from t_employee e INNER JOIN t_department d ON e.employee_id d.employee_id INNER JOIN t_location l ON d.location_id l.location_id
说明左外连接是以左表作为主表依次像右表匹配匹配成功则返回结果匹配不到则返回NULL填充 查询所有员工信息以及对应的部门名称(没有部门的员工也在查询结果中部门以NULL填充) 涉及员工表和部门表 select e.employee_id,e.employee_name,e.employee_salary,d.department_name from t_employee e LEFT JOIN t_department d ON e.department_id d.department_id 注实际开发时根据需求选择主表
27、表连接查询-右外连接查询(RIGHT JOIN ON) 说明右外连接是以右表作为主表依次像左表匹配匹配成功则返回结果匹配不到则返回NULL填充 查询所有部门信息以及此部门中的所有员工信息(没有员工的部门也在查询结果中员工信息以NULL填充) select e.employee_id,e.employee_name,e.employee_salary,d.department_name from t_employee e RIGHT JOIN t_department d ON e.department_id d.department_id elect * from 表2 例 合并2张表的结果(去除重复记录) select * from t1 UNION select * from t2 注意合并结果的两张表列数必须相同列的数据类型可以不同
合并2张表(保留重复记录) select * from t1 UNION ALL select * from t2
25、表连接查询-内连接查询(INNER JOIN ON) select 列名 from 表1 连接方式 表2 连接条件 通过员工表t_employee和岗位表t_job查询所有有部门的员工信息 sql标准写法 select * from t_employee INNER JOIN t_job ON t_employee.job_id t_job.job_id mysql可这样写 select * from t_employee,t_job where t_employee.job_id t_job.job_id 注意务必要指定连接条件否则会造成笛卡尔积的结果
26、表连接查询-三表连接查询 例 查询所有员工信息包含员工编号姓名部门名称部门所在国家ID 涉及三张表t_employee员工表t_department部门表,t_location国家地区表 select * from t_employee e INNER JOIN t_department d ON e.employee_id d.employee_id INNER JOIN t_location l ON d.location_id l.location_id
说明左外连接是以左表作为主表依次像右表匹配匹配成功则返回结果匹配不到则返回NULL填充 查询所有员工信息以及对应的部门名称(没有部门的员工也在查询结果中部门以NULL填充) 涉及员工表和部门表 select e.employee_id,e.employee_name,e.employee_salary,d.department_name from t_employee e LEFT JOIN t_department d ON e.department_id d.department_id 注实际开发时根据需求选择主表
27、表连接查询-右外连接查询(RIGHT JOIN ON) 说明右外连接是以右表作为主表依次像左表匹配匹配成功则返回结果匹配不到则返回NULL填充 查询所有部门信息以及此部门中的所有员工信息(没有员工的部门也在查询结果中员工信息以NULL填充) select e.employee_id,e.employee_name,e.employee_salary,d.department_name from t_employee e RIGHT JOIN t_department d ON e.department_id d.department_id 注实际开发时根据需求选择主表
