sql面试01

1,各种连接方式的区别？

inner join：内连接，根据两个表共有的列来匹配其中的行
left join/right join/all join :以left join 为例，无论右表对应行是否包含满足连接条件的数据，左表的数据都会提取出来，则结果会将右表的这些值以空值的形式匹配进来。
cross join：交叉连接，结果是笛卡尔积，就是第一个表符合查询条件的行数乘以第二个表符合查询条件的行数。

2,索引的作用？

索引是为了提高数据库查询数据的速度而增加的标志符号（通过创建唯一性索引，可以保证表中每一行数据的唯一性）。索引主要建立在①经常搜索的列；②主键所在列；③外键所在列
索引包括聚集索引与非聚集索引，它们的区别在于索引记录的顺序与表记录的顺序是否一致。
聚集索引: 可以理解为索引记录的顺序与表记录的顺序一致，SQL默认在依次递增的主键上建立聚集索引，
非聚集索引: 可以理解数据存储在一个地方，索引指向数据存储的位置，索引的顺序与表中数据记录的顺序不一定一致。

3.排名函数与排序函数？

排序函数： order by （默认asc升序，指定desc降序），例如将表格数据按照考试成绩从低到高排序。
排名函数： rank, dense rank, row number ，得到的成绩的排序后，根据成绩的高低对学生排名，100分对应第一名，99分第二名。它们的区别在于：
row number: 根据成绩排序生成连续的序列号，1,2,3,4,5……
rank： 和row number 不同，rank 考虑了相同分数学生的排名问题。如第一名100分，第2名两个人并列99分，那么98分的同学排名第四。
dense rank: 和rank的区别在于，同样考虑了分区内的排名，但dense rank的输出结果是连续的。如第一名100分，第2名两个人并列99分，那么98分的同学排名第三。

4.on 和 where 的区别？

前提：数据库在连接多张表返回记录时，都会生成一个中间临时表。
在内连接中，使用on或者where没有区别。
在外连接里，例如使用left join时：
on是在生成临时表时使用的条件，不管on的条件是否为真，都会返回左边表中的全部记录。
where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

5.连接多个select？

union: 连接select，不允许重复值，而且select的对象要有相同的列以及数据类型；(例如A表中某字段的数据是1,1,2,3,4，B表中对应的数据是（0,0,0,0,0），则提取的数据是（1,0；2,0；3,0；4,0），也就是说（1,0）这个组合只会出现一次。)
union all: 作用同union,但是允许重复值（也就是说，与上面一样的操作里，(1,0)这个组合会出现2次）
一般来说如果select 字段大于1个，用union all比用union速度快，因为union 会将多个结果中重复的数据合并，union all则是直接合并
Intersect:和 union指令类似，intersect也是合并两个select语句结果的函数。不同的地方是， union的处理结果类似于全集 (如果这个值存在于第一个select或者第二个select，它就会被选出)，而 intersect则比较像取出交集 ( 值要同时存在于第一个select和第二个select)。
minus: 先找出第一个 select 语句所产生的结果，然后看这些结果有没有在第二个 select语句的结果中。最后会输出第一个select中没有，但是第二个select中有的数据

6,主键和外键？

主键是一张表中能够确定一条记录的唯一标志(数据库中的一条记录中有若干个属性，若其中某一个属性组(注意是组)能唯一标识一条记录，该属性组就可以成为一个主键 )，比如身份证号。
外键用于和另一张表进行关联。例如，A字段是A表的主键，那么出现在B表中的A字段能够作为B表的外键，实现A,B表的连接查询。

7.向表中插入数据？

insert into tablename values: 普通插入数据模式
insert or ignore into: 如果没有则插入数据，如果有则忽略
insert or replace into: 如果不存在就插入，存在就更新

8.删除表中数据？

delete : 删除表中数据，可以指定具体数据（where）
drop column/ drop table : 删除列数据，与delete 不同，drop函数会将数据以及表的结构全部删除。
truncate: 仅删除数据，且默认删除所有数据。和delete不同，truncate不能用where进行筛选，但删除速度比delete快

9,字符串常见操作函数？

concat(): 将多个字符串连接成一个字符串，连接符用“”包起来
concat_ws()； 将多个字符串连接成一个字符串，在最开始的位置指定连接符（指定一次即可）
group concat(): 将group by产生的同一个分组中的值连接起来，返回一个字符串。
like(): 需要与通配符一起使用（’%‘代表任意字符出现任意次数；’_'仅能匹配单个字符）
substr(): 用于从字段中提取相应位置的字符。
regexp() : 正则表达式匹配函数

10,In/exist的联系与区别

子查询过程中，In和exist函数效率比较：
当进行连接的两个表大小相似，效率差不多；
如果子查询的内表更大，则exist的效率更高（exist先查询外表，然后根据外表中的每一个记录，分别执行exist语句判断子查询的内表是否满足条件，满足条件就返回ture）。
如果子查询的内表小，则in的效率高（in在查询的时候，首先查询子查询的表，然后将内表和外表做一个笛卡尔积 (表中的每一行数据都能够任意组合A表有a行，B表有b行，最后会输出a*b行)，然后按照条件进行筛选。所以相对内表比较小的时候，in的速度较快）。
Exist的原理: 使用exist时，若子查询能够找到匹配的记录，则返回true，外表能够提取查询数据；使用 not exist 时，若子查询找不到匹配记录，则返回true，外表能够提取查询数据。

11,什么是开窗函数

我们知道聚合函数对一组值执行计算并返回单一的值，如sum()，count()，max()，min()， avg()等，这些函数常与group by子句连用。除了 COUNT 以外，聚合函数忽略空值。
但有时候一组数据只返回一组值是不能满足需求的，如我们经常想知道各个地区的前几名、各个班或各个学科的前几名。这时候需要每一组返回多个值。用开窗函数解决这类问题非常方便。
开窗函数也叫分析函数，有两类：一类是聚合开窗函数，一类是排序开窗函数。
开窗函数的调用格式为：函数名(列名) OVER(partition by 列名 order by列名) 。

开窗函数和聚合函数的区别：
（1）SQL 标准允许将所有聚合函数用作开窗函数，用OVER 关键字区分开窗函数和聚合函数。
（2）聚合函数每组只返回一个值，开窗函数每组可返回多个值。
注：常见主流数据库目前都支持开窗函数，但mysql数据库目前还不支持。

12.几个排序函数row_number() over()、rank() over()、dense_rank() over()、ntile() over()的区别

（1） row_number() over()：对相等的值不进行区分，相等的值对应的排名相同，序号从1到n连续。
（2） rank() over()：相等的值排名相同，但若有相等的值，则序号从1到n不连续。如果有两个人都排在第3名，则没有第4名。
（3） dense_rank() over()：对相等的值排名相同，但序号从1到n连续。如果有两个人都排在第一名，则排在第2名（假设仅有1个第二名）的人是第3个人。
（4） ntile( n ) over()：可以看作是把有序的数据集合平均分配到指定的数量n的桶中,将桶号分配给每一行，排序对应的数字为桶号。如果不能平均分配，则较小桶号的桶分配额外的行，并且各个桶中能放的数据条数最多相差1。