Mysql数据库面试题

一级目录

二级目录

三级目录

Mysql三种事务隔离级别

脏读：脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。
不可重复读：是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。
例如：
在事务A中，读取到张三的工资为5000，操作没有完成，事务还没提交。
与此同时，
事务B把张三的工资改为8000，并提交了事务。
随后，
在事务A中，再次读取张三的工资，此时工资变为8000。在一个事务中前后两次读取的结果并不致，导致了不可重复读。
幻读：是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。
例如：
目前工资为5000的员工有10人，事务A读取所有工资为5000的人数为10人。
此时，
事务B插入一条工资也为5000的记录。
这是，事务A再次读取工资为5000的员工，记录为11人。此时产生了幻读。

不可重复读的重点是修改：
同样的条件，你读取过的数据，再次读取出来发现值不一样了
幻读的重点在于新增或者删除：
同样的条件，第 1 次和第 2 次读出来的记录数不一样

mysql中，结果集满足联结条件。该联接是（）

内连接

mysql索引的数据结构（b+树）

数据库建立索引为什么会加快查询速度
首先明白为什么索引会增加速度，DB在执行一条Sql语句的时候，默认的方式是根据搜索条件进行全表扫描，遇到匹配条件的就加入搜索结果集合。如果我们对某一字段增加索引，查询时就会先去索引列表中一次定位到特定值的行数，大大减少遍历匹配的行数，所以能明显增加查询的速度。

索引(Index)是帮助MySQL高效获取数据的数据结构。

在引入页的概念之后，MySQL会将多条数据存在一个叫“页”的数据结构中，当MySQL读取id=1的数据时，会将id=1数据所在的页整页读到内存中，然后在内存中进行遍历判断，由于内存的IO速度比磁盘高很多，所以相对于磁盘IO，几乎可以忽略不计，那么我们来看看这样读取数据我们需要经历几次磁盘IO（假设每一页可以存4条数据）。那么我们第一次会读取id=1的数据，并且将id=1到id=4的数据全部读到内存中，这是第一次磁盘IO，第二次将读取id=5的数据到内存中，这是第二次磁盘IO。所以我们只需要经历2次磁盘IO就可以找到id=5的这条数据。

插入数据时排序一定有其目的，就是优化查询的效率。
数据经过排序，存放在页上，因为是排序后的数据，所以可以根据页目录快速定位到可能存在的项里，如果这个目录对应的项里没有数据，那就说明没有

在数据不断变多的情况下，MySQL会再去开辟新的页来存放新的数据，而每个页都有指向下一页的指针和指向上一页的指针，将所有页组织起来（这里修改了一下数据，将每一列的数据都放到了数据区中，其中第一个空格之前的代表id），第一页中存放id为1-5的数据，第二页存放id为6-10的数据，第三页存放id为11-15的数据，需要注意的是在开辟新页的时候，我们插入的数据不一定是放在新开辟的页上，而是要进行所有页的数据比较，来决定这条插入的数据放在哪一页上，而完成数据插入之后，最终的多页结构就会像上图中画的那样。

页目录的目录套页目录，这就是sql索引，即B+树

这就是我们兜兜转转由简到繁形成的一颗B+树。和常规B+树有些许不同，这是一棵MySQL意义上的B+树，MySQL的一种索引结构，其中的每个节点就可以理解为是一个页，而叶子节点也就是数据页，除了叶子节点以外的节点就是目录页。这一点在图中也可以看出来，非叶子节点只存放了索引，而只有叶子节点中存放了真实的数据，这也是符合B+树的特点的。

B+树的优势由于叶子节点上存放了所有的数据，并且有指针相连，每个叶子节点在逻辑上是相连的，所以对于范围查找比较友好。B+树的所有数据都在叶子节点上，所以B+树的查询效率稳定，一般都是查询3次。B+树有利于数据库的扫描。B+树有利于磁盘的IO，因为他的层高基本不会因为数据扩大而增高（三层树结构大概可以存放两千万数据量。

通过页来减少磁盘IO次数，并且在页中实现了页目录来优化页中的查询效率，然后使用多页模式来存储大量的数据，最终使用目录页来实现多页模式的查询效率并形成我们口中的索引结构——B+树。
这是对某一列建立索引
B+树索引就是一种聚簇索引，索引和值是放在一起的，找到索引也就找到了值。

索引失效的情况

1.最佳左前缀法则（带头索引不能死，中间索引不能断）
如果索引了多个列，要遵守最佳左前缀法则。指的是查询从索引的最左前列开始 并且 不跳过索引中的列。

解释一下最左前缀原则：当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

2.不要在索引上做任何操作（计算、函数、自动/手动类型转换），不然会导致索引失效而转向全表扫描

3.mysql存储引擎不能继续使用索引中范围条件（bettween、<、>、in等）右边的列

4.索引字段使用like以通配符开头（‘%字符串’）时，会导致索引失效而转向全表扫描

事务

1.什么是事务

作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行

2.事务的四大特征（ACID）

A：原子性（Atomicity） 事务中的操作要么都不做，要么就全做。
C：一致性（Consistency） 事务执行的结果必须是从数据库从一个一致性状态转换到另一个一致性状态。
I：隔离性（Isolation）一个事务的执行不能被其他事务干扰
D：持久性（Durability）
也称永久性，指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。

3.不考虑隔离性会产生的3个问题

1、脏读：脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。

例子：A向B转账，A执行了转账语句，但A还没有提交事务，B读取数据，发现自己账户钱变多了！B跟A说，我已经收到钱了。A回滚事务【rollback】，等B再查看账户的钱时，发现钱并没有多。出现脏读的原因是因为在读的时候没有加读锁，导致可以读取出还没释放锁的记录。

Read uncommitted过程：事务A读取记录(没有加任何的锁)事务B修改记录(此时加了写锁，并且还没有commit–>也就没有释放掉写锁)事务A再次读取记录(此时因为事务A在读取时没有加任何锁，所以可以读取到事务B还没提交的(没释放掉写锁)的记录

2、不可重复读：一个事务两次读取同一行的数据，结果得到不同状态的结果，
中间正好另一个事务更新了该数据，两次结果相异，不可被信任。通俗来讲就是：
事务T1在读取某一数据，而事务T2立马修改了这个数据并且提交事务给数据库，
事务T1再次读取该数据就得到了不同的结果，发送了不可重复读。

3、幻读（虚读）：一个事务执行两次查询，
第二次结果集包含第一次中没有或某些行已经被删除的数据，造成两次结果不一致，只是另一个事务在这两次查询中间插入或删除了数据造成的。
通俗来讲就是：例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。
而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读

4. 事务的四种隔离级别

1.读未提交（Read Uncommitted）引发脏读（读取了未提交的数据）

2.读已提交（Read Committed）这是大多数数据库系统默认的隔离级别，但不是MySQL默认的 只能看见已经提交事务所做的改变引发不可重复读，不可重读读意味着我们同一事务执行完全相同的select语句时可能看到不一样的结果。
导致这种情况的原因可能有：
(1)有一个交叉的事务有新的commit，导致了数据的改变;
(2)一个数据库被多个实例操作时,同一事务的其他实例在该实例处理其间可能会有新的commit 多个commit提交时，只读一次出现结果不一致

3、重复读（Repeatable Read）
重复读，就是在开始读取数据（事务开启）时，不再允许修改操作。重复读可以解决不可重复读问题。应该明白的一点就是，不可重复读对应的是修改，即UPDATE操作。但是可能还会有幻读问题。
因为幻读问题对应的是插入INSERT操作，而不是UPDATE操作。采用Serializable可以解决幻读问题

4.可串行化 （Serializable）
Serializable 是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

从锁的类别上分MySQL都有哪些锁呢？像上面那样子进行锁定岂不是有点阻碍并发效率了

从锁的类别上来讲，有共享锁和排他锁。

共享锁: 又叫做读锁。 当用户要进行数据的读取时，对数据加上共享锁。共享锁可以同时加上多个。

排他锁: 又叫做写锁。 当用户要进行数据的写入时，对数据加上排他锁。排他锁只可以加一个，他和其他的排他锁，共享锁都相斥。

用上面的例子来说就是用户的行为有两种，一种是来看房，多个用户一起看房是可以接受的。 一种是真正的入住一晚，在这期间，无论是想入住的还是想看房的都不可以。

隔离级别与锁的关系

在Read Uncommitted级别下，读取数据不需要加共享锁，这样就不会跟被修改的数据上的排他锁冲突

例子：A向B转账，A执行了转账语句，但A还没有提交事务，B读取数据，发现自己账户钱变多了！B跟A说，我已经收到钱了。A回滚事务【rollback】，等B再查看账户的钱时，发现钱并没有多。出现脏读的原因是因为在读的时候没有加读锁，导致可以读取出还没释放锁的记录。

Read uncommitted过程：事务A读取记录(没有加任何的锁)事务B修改记录(此时加了写锁，并且还没有commit–>也就没有释放掉写锁)事务A再次读取记录(此时因为事务A在读取时没有加任何锁，所以可以读取到事务B还没提交的(没释放掉写锁)的记录

在Read Committed级别下，读操作需要加共享锁，但是在语句执行完以后释放共享锁；

在Repeatable Read级别下，读操作需要加共享锁，但是在事务提交之前并不释放共享锁，也就是必须等待事务执行完毕以后才释放共享锁。

SERIALIZABLE 是限制性最强的隔离级别，因为该级别锁定整个范围的键，并一直持有锁，直到事务完成。

这里需要注意的是：Mysql 默认采用的 REPEATABLE_READ隔离级别

SpringBoot事务

spring boot 添加事物使用 @Transactional注解

简单使用 在启动类上方添加 @EnableTransactionManagement注解

使用时直接在类或者方法上使用 @Transactional注解

Mysql默认引擎是InnoDB

InnoDB是默认的数据库存储引擎，他的主要特点有：

（1）可以通过自动增长列，方法是auto_increment。

（2）支持事务。默认的事务隔离级别为可重复度，通过MVCC（并发版本控制）来实现的。

（3）使用的锁粒度为行级锁，可以支持更高的并发；

（4）支持外键约束；外键约束其实降低了表的查询速度，但是增加了表之间的耦合度。

（5）配合一些热备工具可以支持在线热备份；

（6）在InnoDB中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度；

（7）对于InnoDB类型的表，其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一块，都位于B+数的叶子节点上；

当然InnoDB的存储表和索引也有下面两种形式：

（1）使用共享表空间存储：所有的表和索引存放在同一个表空间中。

（2）使用多表空间存储：表结构放在frm文件，数据和索引放在IBD文件中。分区表的话，每个分区对应单独的IBD文件，分区表的定义可以查看我的其他文章。使用分区表的好处在于提升查询效率。

对于InnoDB来说，最大的特点在于支持事务。但是这是以损失效率来换取的。

事务隔离机制的实现基于锁机制和并发调度。其中并发调度使用的是MVVC（多版本并发控制），通过保存修改的旧版本信息来支持并发一致性读和回滚等特性。

因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是READ-COMMITTED(读取提交内容):，但是你要知道的是InnoDB 存储引擎默认使用 **REPEATABLE-READ（可重读）**并不会有任何性能损失。

InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁

表级锁和行级锁对比：

表级锁： Mysql中锁定 粒度最大 的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM和 InnoDB引擎都支持表级锁。

行级锁： Mysql中锁定 粒度最小 的一种锁，只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。

MVCC多版本并发控制

MVCC使得数据库读不会对数据加锁，普通的SELECT请求不会加锁，提高了数据库的并发处理能力。 借助MVCC，数据库可以实现READ COMMITTED，REPEATABLE READ等隔离级别，用户可以查看当前数据的前一个或者前几个历史版本，保证了ACID中的I特性（隔离性)。

为什么要使用MVCC

为什么需要MVCC呢？数据库通常使用锁来实现隔离性。最原生的锁，锁住一个资源后会禁止其他任何线程访问同一个资源。但是很多应用的一个特点都是读多写少的场景，很多数据的读取次数远大于修改的次数，而读取数据间互相排斥显得不是很必要。所以就使用了一种读写锁的方法，读锁和读锁之间不互斥，而写锁和写锁、读锁都互斥。这样就很大提升了系统的并发能力。之后人们发现并发读还是不够，又提出了**能不能让读写之间也不冲突的方法，就是读取数据时通过一种类似快照的方式将数据保存下来，这样读锁就和写锁不冲突了，不同的事务session会看到自己特定版本的数据。**当然快照是一种概念模型，不同的数据库可能用不同的方式来实现这种功能。

MVCC是怎么实现的

面试官： 你在上面提到MVCC（多版本并发控制），你能说一说原理吗？

我： 在实现MVCC时用到了一致性视图，用于支持读提交和可重复读的实现。

我： 在实现可重复读的隔离级别，只需要在事务开始的时候创建一致性视图，也叫做快照，之后的查询里都共用这个一致性视图，后续的事务对数据的更改是对当前事务是不可见的，这样就实现了可重复读。

我： 而读提交，每一个语句执行前都会重新计算出一个新的视图，这个也是可重复读和读提交在MVCC实现层面上的区别。

面试官： 那你知道快照（视图）在MVCC底层是怎么工作的吗？

我： 在InnoDB 中每一个事务都有一个自己的事务id，并且是唯一的，递增的 。

我： 对于Mysql中的每一个数据行都有可能存在多个版本，在每次事务更新数据的时候，都会生成一个新的数据版本，并且把自己的数据id赋值给当前版本的row trx_id。

面试官： 小伙子你可以画个图我看看吗？我不是很明白。

我： 如图中所示，假如三个事务更新了同一行数据，那么就会有对应的三个数据版本。

我： 实际上版本1、版本2并非实际物理存在的，而图中的U1和U2实际就是undo log，这v1和v2版本是根据当前v3和undo log计算出来的。

面试官： 那对于一个快照来说，你知道它要遵循什么规则吗？

我： 嗯，对于一个事务视图来说除了对自己更新的总是可见，另外还有三种情况：版本未提交的，都是不可见的；版本已经提交，但是是在创建视图之后提交的也是不可见的；版本已经提交，若是在创建视图之前提交的是可见的。

面试官： 假如两个事务执行写操作，又怎么保证并发呢？

我： 假如事务1和事务2都要执行update操作，事务1先update数据行的时候，先回获取行锁，锁定数据，当事务2要进行update操作的时候，也会取获取该数据行的行锁，但是已经被事务1占有，事务2只能wait。

我： 若是事务1长时间没有释放锁，事务2就会出现超时异常 。

MVCC适用的隔离级别

MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作。其他两个隔离级别够和MVCC不兼容, 因为 READ UNCOMMITTED 总是读取最新的数据行, 而不是符合当前事务版本的数据行。而 SERIALIZABLE 则会对所有读取的行都加锁。

update才会生成新的事务id
查询的时候要看是 READ COMMITTED还是PEPEATAVLE READ，后者的话在事务中只有第一次查询才会生成read view 后面的查询都是沿用第一次查询的read view
前者的话每次查询都是生成新的read view

mysql主从分离

MySQL 主从复制原理的是啥？主库将变更写入 binlog 日志，然后从库连接到主库之后，从库有一个 IO 线程，将主库的 binlog 日志拷贝到自己本地，写入一个 relay 中继日志中。接着从库中有一个 SQL 线程会从中继日志读取 binlog，然后执行 binlog 日志中的内容，也就是在自己本地再次执行一遍 SQL，这样就可以保证自己跟主库的数据是一样的。

这里有一个非常重要的一点，就是从库同步主库数据的过程是串行化的，也就是说主库上并行的操作，在从库上会串行执行。所以这就是一个非常重要的点了，由于从库从主库拷贝日志以及串行执行 SQL 的特点，**在高并发场景下，从库的数据一定会比主库慢一些，是有延时的。所以经常出现，刚写入主库的数据可能是读不到的，要过几十毫秒，甚至几百毫秒才能读取到。而且这里还有另外一个问题，就是如果主库突然宕机，然后恰好数据还没同步到从库，那么有些数据可能在从库上是没有的，有些数据可能就丢失了。**所以 MySQL 实际上在这一块有两个机制，一个是半同步复制，用来解决主库数据丢失问题；一个是并行复制，用来解决主从同步延时问题。

这个所谓半同步复制，也叫semi-sync复制，指的就是主库写入 binlog 日志之后，就会将强制此时立即将数据同步到从库，从库将日志写入自己本地的 relay log 之后，接着会返回一个 ack 给主库，主库接收到至少一个从库的 ack 之后才会认为写操作完成了。所谓并行复制，指的是从库开启多个线程，并行读取 relay log 中不同库的日志，然后并行重放不同库的日志，这是库级别的并行。

分库分表

单表在处理庞大的数据的时候，性能会很慢，所以需要分库分表来优化性能
垂直分 按照业务分，比如字段 ，或者把不同功能的表分在不同的数据库中
水平分 按照主键的数据分，单数在一表，双数在二表

Mysql1. 事务的基本要素2. 事务隔离级别（必考）3. 如何解决事务的并发问题(脏读，幻读)（必考）4. MVCC多版本并发控制（必考）5. binlog,redolog,undolog都是什么，起什么作用6. InnoDB的行锁/表锁7. myisam和innodb的区别，什么时候选择myisam8. 为什么选择B+树作为索引结构（必考）9. 索引B+树的叶子节点都可以存哪些东西（必考）10. 查询在什么时候不走（预期中的）索引（必考）11. sql如何优化12. explain是如何解析sql的13. order by原理

数据库常用知识

1. and TradeDate between 20090101 and 20091231
   表示select的数据在2009年里
   order by TradeDate asc
   升序排序
   Desc
   降序排序
   select Did from Deposit where Cid=88888888 and TradeDate between 20090101 and 20091231 order by TradeDate asc;
   查找客户号“88888888”的2009全年的存款流水，按日期升序排列

2. 针对上面的查询要求，为Deposit表创建一个索引idx_Deposit
   Create index idx_Deposit on Deposit (Cid, TradeDate)

3.Group by根据一个或多个列对结果集进行分组，例如聚合函数 (比如 SUM) 常常需要添加 Group by语句用于分组。

Group by语法可以根据给定数据列的每个成员对查询结果进行分组，这里的分组就是将一个“数据集合”划分成若干个“小块”，然后对这些“小块”进行数据处理。最终得到按一个分组汇总的结果表。

SELECT子句后面的字段一般是聚合函数或者是Group by 后面的。

Group by 一般和sum、max、avg等聚合函数一起使用。

我们查找平均工资大于7000的部门的最高工资：
执行SQL：
select dept,max(salary)
from person
group by dept
having avg(salary)>7000;

4.
输出整个银行2009年全年各网点名称及对应的存款总额。

Select dname sum(amount) from deposit,dept where deposit.deptid=dept.deptid and deposit.tradedate between 20090101 and 20191231 group by dname

5.聚合函数，
例如SUM, COUNT, MAX, AVG等。这些函数和其它函数的根本区别就是它们一般作用在多条记录上

6.列出曾经有多笔（2笔或者大于2笔）存款的客户号。
Select Cid from Deposit group by Cid having count(*) > 1

统计数据，比如说count，Sum 一定要先group by进行分组，这样的话才能统计组里的各种数据，可以是通过id分组，也可以通过班级号分组 ，当用 having 的时候，前面更是要用 group by 分组

7.列出客户信息表中没有存款交易的客户，输出客户号和客户姓名。

Select cid,cname from customer where not exist(select 1 from deposit where customer.cid = deposit.cid)

,只要exists引导的子句有结果集返回，那么exists这个条件就算成立了,大家注意返回的字段始终为1，如果改成“select 2 from grade where …”，那么返回的字段就是2，这个数字没有意义。所以exists子句不在乎返回什么，而是在乎是不是有结果集返回。
SELECT ID,NAME FROM A WHERE EXIST (SELECT * FROM B WHERE A.ID=B.AID)
–执行结果为
1 A1
2 A2

–原因可以按照如下分析
SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=１)
—> SELECT * FROM B WHERE B.AID=１有值，返回真，所以有数据

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=2)
—> SELECT * FROM B WHERE B.AID=２有值，返回真，所以有数据

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=3)
—> SELECT * FROM B WHERE B.AID=３无值，返回假，所以没有数据

**

EXISTS(包括 NOT EXISTS )子句的返回值是一个BOOL值。

在这个查询中，首先会取出 Student 表中的第一条记录，得到其 Sno 列（因为在子查询中用到了）的值（如 95001），然后将该值代入到子查询中。若能找到这样的一条记录，那么说明学号为 95001 的学生选修了 1 号课程。因为能找到这样的一条记录，所以子查询的结果不为空集，那么 EXISTS 会返回 True，从而使 Student 表中的第一条记录中的 Sname 列的值被放入结果集中去。以此类推，遍历 Student 表中的所有记录后，就能得到所有选修了 1 号课程的学生的姓名。
与 EXISTS 关键字相对的是 NOT EXISTS，作用与 EXISTS 正相反，当子查询的结果为空集时，返回 True，反之返回 False。也就是所谓的 ”若不存在“。

**

8.多表查询时，前面的字段名也要加上表名

9.增加员工“王五”2014年5月份工资为3000的记录，GMT_CREATE时间取数据库系统时间

INSERT INTO T_SALARY (EMPLOYEE_ID, SALARY, MONTH, GMT_CREATE) VALUES ((SELECT ID FROM T_EMPLOYEE WHERE NAME=‘王五’), 3000, 201403, CURDATE())

curdate() 是获取当前系统时间

删除员工姓名为“李四”的员工信息和工资信息

delete from T_EMPLOYEE E,T_SALARY S
where E.ID = S.EMPLOYEE_ID
and E.NAME=‘李四’;

1.找出未持有账户的客户信息列表（输出姓名、性别以及所属分行）；

select * from clientinfo where custid not in(select cusitid from acctibalance)

这个查询涉及两张表，客户信息表和账户余额表，两张表重合的字段为custid，要先查询出账户余额表中的客户id，再在客户信息表中用where custid not in来查询出所有没有账户的客户信息

2. 找出在2013年有交易发生的账户列表（输出帐号、币种、客户姓名、性别）；

select accountno,ccycode,custname,custsex
from acctbalance ac,clientinfo cl
where ac.custid=cl.custid and accountno in(
select accountno from funddetail
where trandsdate between
‘2013-01-01 00:00:00’ and ‘2013-12-31 23:59:59’
)
如果要查询的是有的，那就要用in

5) 将账户余额超过1000000且属于分行‘300000’的客户存款利率涨15%；

update acctbalance a set interestrate = interestrate * 1.15 where currentbalance > 1000000 and custid in (select custid from clientinfo where branchid = ‘300000’）；

更新语句：update 表名 set 字段=’ ’ where 条件语句

1.查询所有学过 “数据库”的学生姓名，并且按姓名由大到小排序。

注：由于补考、重修，一个学生可能有多个考试成绩，查询结果只需要输出一个。

SELECT **distinct** Sname

        FROM S,C,SC

        WHERE S.Sno=SC.Sno AND C.Cno=SC.Cno

        AND C.Cname=’数据库’

        ORDER BY Sname DESC;

输出一个用distinct

3) 给SC表增加代课教师字段Teacher，数据类型为字符串，长度20，非空

ALTER TABLE CS ADD(Teacher,char(20) not null)

4) 创建每个学生学习总分的视图，字段有学号、总分，视图名

SCSum CREATE VIEW** SCSum

**AS** **SELECT** Sno,SUM(Grade) Grade

**FROM** SC

**GROUP BY** Sno

group by和having的用法

having是分组（group by）后的筛选条件，分组后的数据组内再筛选
where则是在分组前筛选

通过使用GROUP BY 子句，可以让SUM 和 COUNT 这些函数对属于一组的数据起作用。
当你指定 GROUP BY region 时， 属于同一个region（地区）的一组数据将只能返回一行值．
也就是说，表中所有除region（地区）外的字段，只能通过 SUM, COUNT等聚合函数运算后返回一个值．

SQL实例：

一、显示每个地区的总人口数和总面积．

SELECT region, SUM(population), SUM(area)
FROM bbc# `& e4 k' X* n1 v% ?+ |
GROUP BY region

先以region把返回记录分成多个组，这就是GROUP BY的字面含义。分完组后，然后用聚合函数对每组中的不同字段（一或多条记录）作运算。# B* i’ z `, }* S, E5 i

二、 显示每个地区的总人口数和总面积．仅显示那些面积超过1000000的地区。

SELECT region, SUM(population), SUM(area) 7 ]; Z& I! t% i
FROM bbc 8 F4 w2 v( P- f
GROUP BY region
HAVING SUM(area)>1000000 

在这里，我们不能用where来筛选超过1000000的地区，因为表中不存在这样一条记录。
相反，HAVING子句可以让我们筛选成组后的各组数据

sql limit 语句

select * from Customer limit 5,10 ;

–检索从第6行开始向前加10条数据，共显示id为6,7…15

如果想从第5个数据查询，到最后一个数据，总不能一个一个数吧？其实是这样写的：

SELECT * FROM table LIMIT 4,-1

特殊字符

order
****

mysql分页

limit n,m -->(limit m offset n)：跳过n条数据返回m条数据。.

redo/undo log

Redo/Undo机制是将所有对数据的更新操作都写到日志中

Redo log的主要作用是用于数据库的崩溃恢复
重做日志(redo log)用来保证事务的持久性，即事务ACID中的D。

undo log主要记录的是数据的逻辑变化，为了在发生错误时回滚之前的操作，需要将之前的操作都记录下来，然后在发生错误时才可以回滚。

undo是一种逻辑日志，有两个作用：

用于事务的回滚
MVCC

为什么要优化

系统的吞吐量瓶颈往往出现在数据库的访问速度上
随着应用程序的运行，数据库的中的数据会越来越多，处理时间会相应变慢
数据是存放在磁盘上的，读写速度无法和内存相比

如何性能优化

优化索引
主从复制、读写分离
数据库分库分表

聚集索引和普通索引的区别

聚集索引的叶子节点存储行记录
普通索引的叶子节点存储主键值

回表查询

先定位到主键值，再定位行记录，它的性能较扫一遍索引树更低

如粉红色路径，需要扫码两遍索引树：

（1）先通过普通索引定位到主键值id=5；

（2）在通过聚集索引定位到行记录；

什么是覆盖索引，如何利用覆盖索引实现mysql性能的优化

SQL只需要通过索引就可以返回查询所需要的数据，而不必通过二级索引查到主键之后再去查询数据。无需回表，速度更快

如何实现覆盖索引

将单列索引(name)升级为联合索引(name, sex)，也可以避免回表。

如何定位慢查询

1 通过慢查询日志
2 通过 show processlist;

explain

Explain 可以获取 MySQL 中 SQL 语句的执行计划

select_type 查询类型：显示本行是简单还是复杂查询

type 本次查询的表连接类型

key 实际选择的索引

rows 预计需要扫描的行数，对 InnoDB 来说，这个值是估值，并不一定准确

Extra 附加信息

2.1 select_type

SIMPLE 简单查询 (不使用关联查询或子查询)
PRIMARY 如果包含关联查询或者子查询，则最外层的查询部分标记为 primary
UNION 联合查询中第二个及后面的查询
DEPENDENT UNION 满足依赖外部的关联查询中第二个及以后的查询
UNION RESULT 联合查询的结果
SUBQUERY 子查询中的第一个查询
DEPENDENT SUBQUERY 子查询中的第一个查询，并且依赖外部查询
DERIVED 用到派生表的查询
MATERIALIZED 被物化的子查询
UNCACHEABLE SUBQUERY 一个子查询的结果不能被缓存，必须重新评估外层查询的每一行
UNCACHEABLE UNION 关联查询第二个或后面的语句属于不可缓存的子查询

2.2 type

system 查询对象表只有一行数据，且只能用于 MyISAM 和 Memory 引擎的表，这是最好的情况
const 基于主键或唯一索引查询，最多返回一条结果

eq_ref 表连接时基于主键或非 NULL 的唯一索引完成扫描

ref 基于普通索引的等值查询，或者表间等值连接

fulltext 全文检索

ref_or_null 表连接类型是 ref，但进行扫描的索引列中可能包含 NULL 值
index_merge 利用多个索引
unique_subquery 子查询中使用唯一索引
index_subquery 子查询中使用普通索引
range 利用索引进行范围查询
index 全索引扫描
ALL 全表扫描

2.3 Extra

Using filesort 将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序

Using temporary 需要创建一个临时表来存储结构，通常发生对没有索引的列进行 GROUP BY 时

Using index 使用覆盖索引 explain select a from t1 where a=111;

Using where 使用 where 语句来处理结果

Impossible WHERE 对 where 子句判断的结果总是 false 而不能选择任何数据 explain select * from t1 where 1<0;

Using join buffer (Block Nested Loop) 关联查询中，被驱动表的关联字段没索引

Using index condition 先条件过滤索引，再查数据

如何优化数据导入

1、一条SQL语句插入多条数据
2、将插入语句写在事务中
3、数据有序插入。

mysql三种排序方式

内部实现排序主要有3种方式，常规排序，优化排序和优先队列排序，主要涉及3种排序算法：快速排序、归并排序和堆排序。