SQL常见面试题总结
SQL常见面试题总结
- 常用SQL语句
- SQL常用的聚合函数
- Group By和Order By
- where和having子句的区别
- count(*)和count(1)有什么区别
- 请说出sql语句中 left join ,inner join 和right join的区别
- 分库分表的问题如何实现分布式全局唯一ID
- 索引有什么用
- 索引的优缺点
- 如何提高MySql的安全性
- MySQL存储引擎
（原创不易，你们对阿超的赞就是阿超持续更新的动力！）
（以免丢失，建议收藏，阿超持续更新中…）
（------------------------------------------------------------------------）
常用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(统计行数数量)
Group By和Order By
GROUP BY 和 ORDER BY同时使用的方法及注意事项： GROUP BY和ORDER BY同时存在的情况是，ORDER BY对GROUP BY后的结果再进行排序，所以ORDER BY后面的排序字段需要在SELECT里出现，ORDER BY 子句中的列必须包含在聚合函数或 GROUP BY 子句中
where和having子句的区别
having和where的区别：
- 作用的对象不同。WHERE 子句作用于表和视图，HAVING 子句作用于组。
- WHERE 在分组和聚集计算之前选取输入行（因此，它控制哪些行进入聚集计算）， 而 HAVING 在分组和聚集之后选取分组的行。因此，WHERE 子句不能包含聚集函数； 因为试图用聚集函数判断那些行输入给聚集运算是没有意义的。 相反，HAVING 子句总是包含聚集函数。（严格说来，你可以写不使用聚集的 HAVING 子句， 但这样做只是白费劲。同样的条件可以更有效地用于 WHERE 阶段。）
- WHERE不需要聚集。 这样比在 HAVING 里增加限制更加高效，因为我们避免了为那些未通过 WHERE 检查的行进行分组和聚集计算
综上所述： having一般跟在group by之后，执行记录组选择的一部分来工作的。 where则是执行所有数据来工作的。 再者having可以用聚合函数，如having sum(qty)>1000
count(*)和count(1)有什么区别
count(1) 含义
count(1) 会统计表中的所有的记录数，包含字段为null 的记录 count(1)指的并不是计算1的个数，而是指表的第一个字段
用count对字段为null的数据可以查出来吗
不可以 对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加。最后返回累计值
count(*)和count(1)哪个执行效率高
如果count(1)是聚索引 id，那肯定是count(1)快
执行效果上
count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计
执行效率上
列名为主键，count(列名)会比count(1)快 列名不为主键，count(1)会比count(列名)快 如果表多个列并且没有主键，则 count（1） 的执行效率优于 count ( * ) 如果有主键，则 select count（主键）的执行效率是最优的 如果表只有一个字段，则 select count ( * )最优
请说出sql语句中 left join ,inner join 和right join的区别
left join(左联接) ：返回包括左表中的所有记录和右表中联结字段相等的记录 right join(右联接) ：返回包括右表中的所有记录和左表中联结字段相等的记录 inner join(等值连接) ：只返回两个表中联结字段相等的行
分库分表的问题如何实现分布式全局唯一ID
在分库分表的环境中，数据分布在不同的分片上，不能再借助数据库自增长特性直接生成，否则会造成不同分片上的数据表主键会重复。
索引有什么用
索引能大幅度提高查询效率 但是数据的变更（增删改）都需要维护索引，因此更多的索引意味着更多的维护成本 更多的索引意味着也需要更多的空间 （就比如说一本100页的书，却有50页目录） 过小的表，建索引还可能会更慢（就比如说读个2页的宣传手册，你还先去找目录）
索引的优缺点
索引的优点：
- 创建唯一性索引，保证数据库表中每一行数据的唯一性
- 大大加快数据的检索速度，这也是创建索引的最主要的原因
- 加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
- 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
- 通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能。
索引的缺点：
- 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
- 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大
- 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度
如何提高MySql的安全性
- 避免从互联网访问MySQL数据库，确保特定主机才拥有访问特权
- 定期备份数据库 任何系统都有可能发生灾难。服务器、MySQL也会崩溃，也有可能遭受入侵，数据有可能被删除。只有为最糟糕的情况做好了充分的准备，才能够在事后快速地从灾难中恢复。企业最好把备份过程作为服务器的一项日常工作。
- 禁用或限制远程访问
- 设置root用户的口令并改变其登录名。
- 移除测试(test)数据库
- 禁用LOCAL INFILE
- 移除匿名账户和废弃的账户
- 降低系统特权
- 降低用户的数据库特权
- 移除和禁用.mysql_history文件
- 安全补丁
- 启用日志
- 改变root目录 Unix操作系统中的chroot可以改变当前正在运行的进程及其子进程的root目录。重新获得另一个目录root权限的程序无法访问或命名此目录之外的文件，此目录被称为“chroot监狱”。 通过利用chroot环境，你可以限制MySQL进程及其子进程的写操作，增加服务器的安全性。
MySQL存储引擎
mysql默认的存储引擎就是innodb，它的索引结构是优化后的B+tree，原B+tree的叶子节点之间是单向指向，组合成一个单向链表，而优化后，叶子节点之间双向指向，组合成双向链表。
索引分为聚簇索引和非聚簇索引，聚簇索引的叶子节点一个索引携带一row的数据，非聚簇索引下索引携带对应主键数据。
索引主要针对查询，索引可以加快查询效率，例如我们建立索引时尽量在where，orderBy这样的条件需要的字段加索引，因为查询时根据条件查询，条件上加了索引，可以快速定位到需要查询的数据。
我们使用索引时，可以尽量去使用覆盖索引来避免回表的过程，因为我们自己建的索引为非聚簇索引，根据索引定位到数据后，可以找到索引列数据和主键数据，但是如果你的索引不是覆盖索引，那你需要的字段并没有全部包含在当前已经查询到的数据，所以需要根据主键进行回表，通过聚簇索引，查询到当前行的数据，在取出你所需要查询字段的数据，这个回表过程是不必要的。
如果需要建立多个单列索引，我们尽量去使用组合索引，当然组合索引需要注意最左前缀匹配原则，按照建立组合索引的顺序，必须保证当前字段前面的索引列存在才能保证组合索引生效。并且如果当前使用组合索引时，某字段采用了范围查询，就会导致该字段后面的索引失效。
最后我们需要注意的是索引是为了索引表内少量的数据，所以如果你在条件查询时，条件是大量的数据，那么sql语句经过优化器时，就会分析走当前索引还不如走全文检索，索引就会走全文索引，也会造成索引的失效。
还有插入语句的时候，如果是批量插入，我们尽量去使用主键顺序插入，如果数据量国语庞大可以使用load来进行加载。
还有一个除了索引可以优化的点，innoDB它相比于MyISAM他是支持行级锁的，但是有时候我们在操作的时候会有一些误操作，使得行级锁上升到表级锁，就比如我们根据一个字段做条件去更新本行数据时，当前字段没有建立索引，那就会走一个全文检索，那整张表就会被锁住，行级锁就会上升到表级锁，这也是为什么需要在条件字段添加索引的另一个原因。
间隙锁：对表进行改动时，使用了范围条件，当前范围内就会被锁住。
腾讯云开发者
