SQL常见面试题总结1

（以免丢失，建议收藏~~~）

（原创不易，你们对阿超的赞就是阿超持续更新的动力！）

常用SQL语句

--  默认升序排序(ASC)

--增
insert into user values (123,'女','猪猪')

--删
delete from student where id = 6

--改
update student set sname = '猪猪' where sid = 1

--查
select * from user where uid = 1
select * from user where userName = 'admin' and password = '123'

-- 多表关联查询
-- 左连接  left join 或 left outer join
左外连接包含left join左表所有行，如果左表中某行在右表没有匹配，则结果中对应行右表的部分全部为空(NULL).
select * from student left join course on student.ID=course.ID

-- 右连接
右外连接包含right join右表所有行，如果左表中某行在右表没有匹配，则结果中对应左表的部分全部为空(NULL)。
select * from student right join course on student.ID=course.ID

-- 内连接
在每个表中找出符合条件的共有记录
隐式内连接：select * from A,B where 条件;
显示内连接：select * from A inner join B on 条件;

-- 排序
order by 默认升序排序(ASC)

-- 分组
group by

-- 分页查询
m的含义表示从数据的第（m + 1）条开始查询（mysql中第一条数据m=0）
n的含义是从第m条数据开始往后查询n条数据
SELECT * FROM user limit m,n


-- SQL Server
-- 分页查询
select top (@pagesize) * from user
where userid 
not in(
  select top 
    (@pagesize*(@currentPage-1))
  userid
  from user) 
  
-- 查询有几条表数据
select count(*) from user

-- 获得当前日期+时间
oracle： select sysdate from dual;
sqlserver: select getdate();
mysql: select sysdate() 或者 select now()
（date + time）函数：now() 

SQL常用的聚合函数

• max(求最大值)
• min(求最小值)
• sum(求累加和)
• avg(求平均)
• count(统计行数数量)

如何提高MySql的安全性

• 避免从互联网访问MySQL数据库，确保特定主机才拥有访问特权
• 定期备份数据库
• 任何系统都有可能发生灾难。服务器、MySQL也会崩溃，也有可能遭受入侵，数据有可能被删除。只有为最糟糕的情况做好了充分的准备，才能够在事后快速地从灾难中恢复。企业最好把备份过程作为服务器的一项日常工作。

• 禁用或限制远程访问

• 设置root用户的口令并改变其登录名。

• 移除测试(test)数据库

• 禁用LOCAL INFILE

• 移除匿名账户和废弃的账户

• 降低系统特权

• 降低用户的数据库特权

• 移除和禁用.mysql_history文件

• 安全补丁

• 启用日志

• 改变root目录

• Unix操作系统中的chroot可以改变当前正在运行的进程及其子进程的root目录。重新获得另一个目录root权限的程序无法访问或命名此目录之外的文件，此目录被称为“chroot监狱”。

• 通过利用chroot环境，你可以限制MySQL进程及其子进程的写操作，增加服务器的安全性。

为什么不使用readonly使全局库只读

既然表级锁是全库只读，为何不直接set global readonly = true

readonly是可以让全库进入只读状态，还是建议使用FTWRL，原因如下：

• 一是在一些系统中，readonly的值会用作其它逻辑，比如用来判断一个库的主库还是备库，所以修改global变量的方式影响面更大；

• 二是在异常处理机制上有差异，如果执行FTWRL命令之后由于客户端发生异常断开，那么MySQL会自动释放这个全局锁，整个库回到正常更新的状态；而将整个库设置为readonly之后，如果客户端发生异常，则数据库就会一直保持readonly状态，这样会导致整个库长时间处于不可写状态，风险较高；

备份为什么要加锁

假设你现在要维护“极客时间”的购买系统，关注的是用户账户余额表和用户课程表。现在发起一个逻辑备份。假设备份期间，有一个用户，他购买了一门课程，业务逻辑里就要扣掉他的余额，然后往已购课程里面加上一门课。

可以看到，这个备份结果里，用户 A 的数据状态是“账户余额没扣，但是用户课程表里面已经多了一门课”。如果后面用这个备份来恢复数据的话，用户 A 就发现，自己赚了。

也就是说，不加锁的话，备份系统备份的得到的库不是一个逻辑时间点，这个视图是逻辑不一致的。

MySQL存储引擎

mysql默认的存储引擎就是innodb，它的索引结构是优化后的B+tree，原B+tree的叶子节点之间是单向指向，组合成一个单向链表，而优化后，叶子节点之间双向指向，组合成双向链表。

索引分为聚簇索引和非聚簇索引，聚簇索引的叶子节点一个索引携带一row的数据，非聚簇索引下索引携带对应主键数据。

索引主要针对查询，索引可以加快查询效率，例如我们建立索引时尽量在where，orderBy这样的条件需要的字段加索引，因为查询时根据条件查询，条件上加了索引，可以快速定位到需要查询的数据。

我们使用索引时，可以尽量去使用覆盖索引来避免回表的过程，因为我们自己建的索引为非聚簇索引，根据索引定位到数据后，可以找到索引列数据和主键数据，但是如果你的索引不是覆盖索引，那你需要的字段并没有全部包含在当前已经查询到的数据，所以需要根据主键进行回表，通过聚簇索引，查询到当前行的数据，在取出你所需要查询字段的数据，这个回表过程是不必要的。

如果需要建立多个单列索引，我们尽量去使用组合索引，当然组合索引需要注意最左前缀匹配原则，按照建立组合索引的顺序，必须保证当前字段前面的索引列存在才能保证组合索引生效。并且如果当前使用组合索引时，某字段采用了范围查询，就会导致该字段后面的索引失效。

最后我们需要注意的是索引是为了索引表内少量的数据，所以如果你在条件查询时，条件是大量的数据，那么sql语句经过优化器时，就会分析走当前索引还不如走全文检索，索引就会走全文索引，也会造成索引的失效。

还有插入语句的时候，如果是批量插入，我们尽量去使用主键顺序插入，如果数据量国语庞大可以使用load来进行加载。

还有一个除了索引可以优化的点，innoDB它相比于MyISAM他是支持行级锁的，但是有时候我们在操作的时候会有一些误操作，使得行级锁上升到表级锁，就比如我们根据一个字段做条件去更新本行数据时，当前字段没有建立索引，那就会走一个全文检索，那整张表就会被锁住，行级锁就会上升到表级锁，这也是为什么需要在条件字段添加索引的另一个原因。

间隙锁：对表进行改动时，使用了范围条件，当前范围内就会被锁住。

MySQL的事务隔离级别

• 读未提交(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据

• 读已提交(Read Committed)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)

• 可重复读(Repeated Read)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读，但是innoDB解决了幻读

• 串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞

MySQL中的MyISAM与InnoDB 的区别

MyISAM是MySQL的默认数据库引擎（5.5版之前）

InnoDB支持事务，MyISAM不支持。

InnoDB支持外键，而MyISAM不支持。

InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。

MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

InnoDB不保存表的具体行数*，*而MyISAM用一个变量保存了整个表的行数

Innodb不支持全文索引，而MyISAM支持全文索引

（5.7以后的InnoDB支持全文索引了）

MyISAM表格可以被压缩后进行查询操作

InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁

InnoDB表必须有唯一索引（如主键）（用户没有指定的话会自己找/生产一个隐藏列Row_id来充当默认主键），而Myisam可以没有

Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI

如何选择MyISAM和InnoDB

• 是否要支持事务，如果要请选择 InnoDB，如果不需要可以考虑 MyISAM；
• 如果表中绝大多数都只是读查询，可以考虑 MyISAM，如果既有读写也挺频繁，请使用InnoDB。
• 系统奔溃后，MyISAM恢复起来更困难，能否接受，不能接受就选 InnoDB；

• MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的。如果你不知道用什么存储引擎，那就用InnoDB，至少不会差。

索引常见的几种失效情况

• 对于联合索引，没有遵循左前缀原则
• 索引的字段区分度不大，可能引起索引近乎全表扫描
• 对于join操作，索引字段的编码不一致，导致使用索引失效
• 对于hash索引，范围查询失效，hash索引只适合精确匹配
• 有索引，但操作索引项字段“·不干净” 加了函数或者各种骚操作

• （对于sql执行耗时问题 最好使用explain和 profilings 查看执行计划详细信息）

• 对于innodb 或myisam，is null 走索引的情况 不走索引的情况？

• is not null 对于索引字段设计不管允许为空或不许为null 都不走索引

• is null 对于索引字段设计为可许为null时 走索引 ；如果字段设计不可null 则不走索引

• 条件中用or，即使其中有条件带索引，也不会使用索引查询（这就是查询尽量不要用or的原因，用in吧）

索引失效与优化

• 全值匹配
• 最佳左前缀法则（带头大哥不能死，中间兄弟不能少）
• 不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
• 存储引擎不能使用索引中范围条件右边的列
• 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一直）），减少select *

• mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描

• 使用is null， is not null也无法使用索引

• like以通配符开头（‘%abc…’）mysql索引失效会变成全表扫描的操作
• 字符串不加单引号索引失效
• 少用or，用它来连接时索引会失效

SQL常见面试题总结2:https://developer.aliyun.com/article/1473883?spm=a2c6h.13148508.setting.18.70d84f0eKWaSX3