面试宝典——SQL

面试宝典——SQL

一、基本SQL语句

选择：SELECT —— 从数据库中提取数据

SELECT * FROM table1 WHERE 范围

插入：INSERT INTO

INSERT INTO table1(field 1,field2) values(value1, value2)

删除：DELETE

DELETE FROM table WHERE 范围

更新：UPDATE

UPDATE table1 SET field1 = value1 WHERE 范围

查找：SELECT FROM WHERE LIKE

SELECT * FROM table1 WHERE field LIKE '%value1%'

排序：SELECT FROM ORDER BY

SELECT * FROM table1 ORDER BY field1, field2 [DESC]

总数：COUNT

SELECT COUNT AS TOTALCOUNT FROM table1

求和：SUM

SELECT SUM(file1) AS SUMVALUE FROM table1

平均：AVG

SELECT AVG(file1) AS AVGVALUE FROM table1

最大：MAX

SELECT MAX(file1) AS MAXVALUE FROM table1

最小：MIN

SELECT MIN(file1) AS MINVALUE FROM table1

创建新数据库：CRETAE DATABASE

修改数据库：ALTER DATABASE

创建新表：CREATE TABLE

修改表：ALTER TABLE

删除表：DROP TABLE

创建索引：CREATE INDEX

删除索引：DROP INDEX

二、语句

1. 某个表格中有10条一模一样的数据，现在要删掉其中的9条，请你写一下sql语句

delete * from table_name limit 9

2. 查询表A中id字段重复出现三次以上的记录

Select * From A Where id in(select ID from A group by id having count(id)>3)

三、事务的四大特性（ACID）

ACID 是事务的四个特性，分别是原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）

• 原子性是指事务是一个不可分割的工作单位，事务中的的操作要么都发生，要么都不发生。（最经典的转账案例，我们把转入和转出当作一个事务的话，就需要在SQL中显示指定开启事物）
• 一致性是指数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性。
• 隔离性是指多个事务并发访问时，事务之间是隔离的，一个事务不应该影响其他事务运行效果。
• 持久性意味着即使出现任何事故比如断电等，事务一旦提交，则持久化保存在数据库中，不会被回滚。

四、脏读，不可重复读和幻读（事务的并发问题）

• 脏读：一个事务读取了另一个事务未提交的数据，而这个数据有可能是回滚的。即这个事务读取的数据是不正确的。
• 不可重复读：在数据库访问中，一个事务范围内两个相同的查询却返回了不同数据。这是由于查询时系统中的其他事务修改的提交而引起的，即这个事务在读的过程中被修改了
• 幻读：当一个事务对整个table进行修改后，第二个事务向表中插入一行数据，此时第一个事务发现了新插入的没有修改的数据行，好像是发生了幻觉一样。

五、三大范式

• 第一范式：所有字段值都是不可分解的原子值。

例如有一个列是电话号码一个人可能有一个办公电话和一个移动电话，第一范式就需要拆开两个属性。

• 第二范式：非主属性完全函数依赖于候选键。

如 PersonID，ProductID，ProductName，PersonName 可以看到，PersonID 和ProductID 是联合主键，但是ProductName 依赖于ProductID ，只依赖了部分主键，没有依赖全部主键。所以需要拆分为三个表： PersonID，PersonName ； ProductID， ProductName 和 PersonID ,ProductID

• 第三范式：每一列数据和主键都是直接相关，不能间接相关。

如OrderID，ProductID， ProductName是主键，但是ProductID 依赖 OrderID，而ProductName 依赖了ProductID， 等于说是间接依赖了OrderID， 所以需要拆分两个表 OrderID, ProductID ； ProductName ， ProductID

范式不是最好的，我们需要混合使用范式和反范式

• 范式的优点：相对有较少的重复数据，范式化的更新操作要比反范式快，同时范式化需要更少的distinct 和 Order by
• 范式化缺点：通常需要关联，不仅代价昂贵，也可能会使的一些索引无效
• 常用的反范式方法：

1. 复制：在两个表中根据实际业务情况存储部分相同的字段列，即有利查询，也不会把表搞的太大
2. 缓存：对于需要多次join查询的表，可以在一个表中加入一个缓存列，用来缓存所join表的部分常用数据，如count等，我们需要实时更新该缓存。

六、数据库的隔离级别

隔离级别	脏读	不可重复读	幻读
未提交读（Read uncommitted）	可能	可能	可能
已提交读（Read committed）	不可能	可能	可能
可重复读（Repeatable read）	不可能	不可能	可能
可串行化（Serializable ）	不可能	不可能	不可能

• 未提交读（RU）
  1. 一个事务还没提交时，它做的变更就能被别的事务看到
  2. 会出现脏读，不可重复读，幻读
  3. 更新数据时加上行级共享锁，事务结束即释放
• 已提交读（RC）
  1. 一个事务提交后，它所作的变更才能被别的事务看到
  2. 会出现幻读，不可重复读，但是不会出现脏读
  3. 写数据时加入 行级排他锁，这样写过程是无法读取的，直到事务处理完毕才释放排他锁，给读的数据加一个行级共享锁，读的时候无法写，一旦读完该行就释放共享锁
  4. MySQL会在SQL语句开始执行时创建一个视图
• 可重复读（RR）
  1. 一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的
  2. 会出现幻读，不会出现不可重复读，脏读
  3. 给写的数据加行级排他锁，事务结束释放，给读的数据加行级共享锁，事务结束后释放
  4. MySQL会在事物开始时创建一个一致性视图，事物结束时销毁
• 可串行化S
  1. 当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行
  2. 不会出现幻读，不可重复读，脏读
  3. 事务读数据则加表级共享锁，事务写数据则加表级排他锁
  4. 不区分快照度与当前读

七、索引

索引是一种数据结构，用于帮助我们在大量数据中快速定位到我们想要查找的数据，可以加快查的速度，但是会增加容量，降低增，删，改的速度。

通过explain可以查看Mysql语句有没有用到索引

---

1、MySQL有哪几种索引类型，各自特点

常见的MySQL索引结构有B树索引，B+树索引，Hash索引和全文索引

① B树索引

• 因为存储引擎不用进行全表扫描来获取数据，直接从索引的根节点开始搜索，从而能加快访问数据的速度。
• B树对索引是顺序组织存储的，很适合查找范围数据。
• 适用于全键值、键值范围或者键前缀查找（根据最左前缀查找）
• 限制：对于联合索引来说，如果不是从最左列开始查找，则无法使用索引；不能跳过索引中间的列。

②B+树索引

• 是B-树索引的变种，现在主流的存储引擎都不用单纯的B-树，而是B+树或T-树等
• 和B-树最主要的区别是 B+树的内节点不存储data，只存储key，叶子节点不存储指针

③Hash索引

• 基于Hash表实现，只有Memory存储引擎显示支持哈希索引
• 适合等值查询，如 =、in()、<=>，不支持范围查询
• 因为不是按照索引值顺序存储的、就不能像B+树一样利用索引完成排序
• Hash索引在查询等值时非常快
• 因为Hash索引始终索引的所有列的全部内容，所有不支持部分索引列的匹配查找
• 如果有大量重复键值的情况下，哈希索引的效率会很低，因为存在哈希碰撞问题
• 程序员可以在B+树索引的基础上创建自适应Hash索引

④全文索引

• MyISAM 和InnoDB 都支持全文索引
• 有三种模式：自然语言模式、布尔模式和查询扩展模式

八、锁

Mysql中的锁可以分为共享锁/读锁（Shared Locks）、排他锁/写锁（Exclusive Locks） 、间隙锁、行锁（Record Locks）、表锁。

1、从操作上来说分为：共享锁/读锁 ， 排它锁/写锁，

• 共享锁/读锁 ： 针对同一份数据，多个读操作可以同时进行，简单来说即读加锁，不能写并且可并行读；

• 排它锁/写锁： 当前写操作没有完成，那么它会阻断其它的写锁和读锁，即写加锁，其它读写都阻塞 。

2、从粒度上来说分为：行锁， 表锁

• 行锁：行锁锁定当前数据行，锁的粒度小，加锁慢，发生锁冲突的概率小，并发度高，行锁也是MyISAM和InnoDB的区别之一，InnoDB支持行锁并且支持事务 。

• 表锁：表锁则锁的粒度大，加锁快，开销小，但是锁冲突的概率大，并发度低。

3、什么是间隙锁？

间隙锁是一个在索引记录之间的间隙上的锁。

4、死锁产生的四个必要条件

• 互斥条件。一个资源只能被一个进程占用
• 不可剥夺条件。某个进程占用了资源，就只能他自己去释放。
• 请求和保持条件。某个进程之前申请了资源，我还想再申请资源，之前的资源还是我占用着，别人别想动。除非我自己不想用了，释放掉。
• 循环等待条件。一定会有一个环互相等待。

九、内连接和外连接

• 内连接也叫自然连接，只有两个表相匹配的行才能在结果集中出现。返回的结果集选取两个表中所匹配的数据，舍弃不匹配的数据

select fieldlist 
from table1 [inner] join table2 on table1.column = table2.column

• 外连接不仅仅包含符合连接条件的行，而且还包括左表（左外连接），右表（右外连接），或者两个边表（全外连接）中的所有数据行

select fieldlist 
from table1 left/ right outer join table2 on table1.column = table2.column

• 目前（8.0）MySQL不支持全外连接

十、MySQL如何为表字段添加索引

1. 添加主键索引

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

2. 添加唯一索引

ALTER TABLE `table_name` ADD UNIQUE ( `column` )

3. 添加普通索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

4. 添加全文索引(适用于MyISAM，InnoDB 5.6+)

ALTER TABLE `table_name` ADD FULLTEXT ( `column`)

5. 添加联合索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )