对mysql的理解

参考：
关于SQL：除了刷题，还要准备什么？

以下的主要框架和内容都来自以上博主的文章，我主要是做一下记录并且补充自己的理解。

SQL考察的方式

笔试，网上答题。在网上看面经的时候，网易，PDD之类的都会出现SQL来作为筛选条件，Sql是数据分析岗的必备技能之一。但有时候这些笔试题真的好变态，只能多练一些真题进行准备了。有些较难的题可以参考之前的这篇博文：面向业务的mysql笔试题笔记
现场面试写。

在面试小红书，网易的时候，面试官也会现场抽出几道SQL题让你写代码。现场写代码不会遇到太难的题目，一般窗口函数排个序可以搞定，再难点也就是求中位数、众数这些。

远程面试口述语法。我一直觉得口述代码就是反人类的。。。

像蘑菇街，趣头条，招银网络科技等等，是在远程面试（或者电面）的时候随便问起SQL常见的语法。以我的经验来看，电面的考察侧重于提问相似函数的不同点，毕竟让你口述一段代码也不现实……

我学习sql的路径：

在B站上找了一个播放量高的mysql的视频，边看边敲，对各自语法都有了一个大概的认识。
看《SQL必知必会》，对mysql有了比较清晰的认识。
然后就跑去leetcode刷题，发现自己太天真了，有好多语法都没见过(窗口函数、定义变量、定义函数等等)，花了一个月刷完之后感觉自己学到了很多，但还是需要反复巩固，有些题目就算当时会了，现在返回去看也可能还是不太会。(吐槽一下牛客网上的数据库题虽然我也做了，但是感觉题目质量真的好差啊，有些题目都都不明白，刷题体验也是极差的)。

下面谈一下我对以下各个问题的理解：

各种连接方式的区别？

[inner] join ：内连接，根据两个表进行匹配，只有两个表的记录都满足条件才会完成匹配。
left [outer] join/right [outer] join/all [outer] join:以左连接为例，与右表按照条件进行匹配，左表的所有记录都会保留，右表中不满足条件的记录以空值NULL的形式进行返回。
cross join：交叉连接，也叫作笛卡尔乘积。就是左右两表所有可能的组合。以下方式也能实现。

select *
from a, b

索引的作用？

数据库索引的作用和优点缺点

创建索引可以大大提高系统的性能。

通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性
可以大大加快数据的检索速度，这也是创建索引的最主要原因。
可以加速表与表之间的连接，特别是实现数据的参考完整性方面特别有意义。

数据完整性（Data Integrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。它是应防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。

在分组和排序，用子句进行检索时，同样可以显著减少查询中排序的时间。
通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

索引主要建立在：

经常搜索的列
主键所在的列
外键所在的列

SQL 索引的作用（真的是超详细）
聚集索引：

聚集索引将数据行的键值在表内排序并存储对应的数据记录，使得数据表物理顺序与索引顺序一致。
这意味着不论聚集索引里有表的哪个（或哪些）字段，这些字段都会按顺序地被保存在表中。由于存在这种排序，所以每个表只会有一个聚集索引。

非聚集索引：

非聚集索引完全独立于数据行的结构。
一个表中最多只能有一个聚集索引，但可有一个或多个非聚集索引。当在SQLServer上创建索引时，可指定是按升序还是降序存储键。

语法格式：
CREATE[ UNIQUE ]   /*指定索引是否唯一*/
  [ CLUSTERED | NONCLUSTERED ]   /*索引的组织方式*/
  INDEX <索引名>  
    ON<表名>( <列名> [ ASC | DESC ] [ ,...n ] ) /*索引定义的依据*/
  [ WHERE <列名> IN (<筛选值>,…)
    | <列名><筛选谓词> <筛选值>]  /*筛选索引*/
    [WITH ( <索引选项> [ ,...n ] ) ]  /*索引选项*/
 [ ; ]

排名函数与排序函数

如果以下五个同学的分数分别为：100、99、99、98

ROW_NUMBER():顺序排序——1、2、3、4
RANK():并列排序，跳过重复序号——1、2、2、4
DENSE_RANK():并列排序，不跳过重复序号——1、2、2、3

on与where的区别

SQL中过滤条件放在on和where中的区别
我以前对于where和on的理解是这样的:
sql92语法是没有 join on 的是通过这样连接的

select *
from a, b
where a.id = b.id

sql96语法中可以使用join on 是这样连接的

select *
from a
join b
on a.id = b.id

我以前认为on后边就是放置连接的条件的，而剩余的筛选条件则要放在where中，这样的理解是没有问题的，但是还是有些浅薄：

join过程可以这样理解：首先两个表做一个笛卡尔积，on后面的条件是对这个笛卡尔积做一个过滤形成一张临时表，如果没有where就直接返回结果，如果有where就对上一步的临时表再进行过滤。

因此，
对于内连接， where和on是没有区别的。
对于外连接，以左连接为例：on是在生成临时表时使用的条件，无论on的条件是否为真，都会返回左边表中的全部记录。
而where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

如何连接多个select子句：

并集：union / union all ，前者不允许重复值，后者允许重复值，而且合并的两个数据需要有相同的列以及数据类型。

一般来说如果select 字段大于1个，用union all比用union速度快，因为union 会将多个结果中重复的数据合并，union all则是直接合并

交集：Intersect

差集：minus
从t1表的查询中返回不同于t2表查询结果中的值。

SELECT id FROM t1
MINUS
SELECT id FROM t2;

主键和外键

主键（PRIMARY KEY）是一张表中能够确定一条记录的唯一标志(数据库中的一条记录中有若干个属性，若其中某一个属性组(注意是组)能唯一标识一条记录，该属性组就可以成为一个主键 )，比如身份证号。

CREATE TABLE users(
   user_id INT AUTO_INCREMENT PRIMARY KEY,
   username VARCHAR(40),
   password VARCHAR(255),
   email VARCHAR(255)
);

外键用于和另一张表进行关联。例如，A字段是A表的主键，那么出现在B表中的A字段能够作为B表的外键，实现A,B表的连接查询。
MySQL外键简介

CONSTRAINT constraint_name
FOREIGN KEY foreign_key_name (columns)
REFERENCES parent_table(columns)
ON DELETE action
ON UPDATE action

向表中插入数据

刷牛客网的时候有注意到如下几个语法：

普通插入模式
INSERT INTO tablename VALUES(...);

如果不存在（根据主键或者唯一索引判断）则插入，如果存在则忽略
INSERT OR IGNORE INTO tablename VALUES(...);

如果不存在（根据主键或者唯一索引判断）则插入，如果存在则替换
INSERT OR REPLACE INTO tablename VALUES(...);

删除表中的数据

SQL truncate 、delete与drop区别

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

字符串常见操作函数？

concat(): 将多个字符串连接成一个字符串，连接符用“”包起来
concat_ws():代表 CONCAT With Separator ，是CONCAT()的特殊形式。第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。如果分隔符为 NULL，则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。
group concat(): 将group by产生的同一个分组中的值连接起来，返回一个字符串。
like(): 模糊查询，需要与通配符一起使用（'%'代表任意字符出现任意次数；'_'仅能匹配单个字符）
substr(): substr(string string,num start,num length); 用于从字段中提取相应位置的字符。
regexp() : 正则表达式匹配函数

-- 查找name字段中以元音字符开头或以'ok'字符串结尾的所有数据：
WHERE name REGEXP '^[aeiou]|ok$';

In/exist的联系与区别

当进行连接的两个表大小相似，效率差不多；
当主表比从表大时，IN查询的效率较高；
当从表比主表大时，EXISTS查询的效率较高；
原因如下：
i- n是先执行子查询，得到一个结果集，将结果集代入外层谓词条件执行主查询，子查询只需要执行一次
exists是先从主查询中取得一条数据，再代入到子查询中，执行一次子查询，判断子查询是否能返回结果，主查询有多少条数据，子查询就要执行多少次

Exist的原理:使用exist时，若子查询能够找到匹配的记录，则返回true，外表能够提取查询数据；使用 not exist 时，若子查询找不到匹配记录，则返回true，外表能够提取查询数据。

常用的时间函数

很多sql题目的考察都是基于业务的，比如每日新用户数目、每日留存率这都涉及到一个我们避不开的量就是时间，但是时间有着不同的格式，有时候是年月日时间、有时候是年月日，有时候是时间戳。我们需要将其转化为合理的格式进行运用。

比如我在猿辅导的笔试中就遇到了时间戳的转化：

now(): 获取当前日期+时间
current_timestamp()：获取当前时间戳
date_format(date,format)或time_format(time,format): 将时间或者日期转化为字符串
str_to_date(str, format)：将字符串转化为日期
unix_timestamp(date)：获取时间戳
from_unixtime(unix_timestamp)：时间戳转为时间
from_unixtime(unix_timestamp,format)：时间戳格式化
date_add(dt, interval 1 day)：为日期增加一个时间间隔
date_sub(dt, interval 1 day)：为日期减去一个时间间隔
datediff(date1,date2)：计算两个时间之差
注意：timediff(time1,time2) 函数的两个参数类型必须相同。
时间戳（timestamp）转换、增、减函数：
timestamp(date) -- date to timestamp
timestamp(dt,time) -- dt + time
timestampadd(unit,interval,datetime_expr) --
timestampdiff(unit,datetime_expr1,datetime_expr2) --

格式化的格式：
%M 月名字(January……December)
%W 星期名字(Sunday……Saturday)
%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。）
%Y 年, 数字, 4 位
%y 年, 数字, 2 位
%a 缩写的星期名字(Sun……Sat)
%d 月份中的天数, 数字(00……31)
%e 月份中的天数, 数字(0……31)
%m 月, 数字(01……12)
%c 月, 数字(1……12)
%b 缩写的月份名字(Jan……Dec)
%j 一年中的天数(001……366)
%H 小时(00……23)
%k 小时(0……23)
%h 小时(01……12)
%I 小时(01……12)
%l 小时(1……12)
%i 分钟, 数字(00……59)
%r 时间,12 小时(hh:mm:ss [AP]M)
%T 时间,24 小时(hh:mm:ss)
%S 秒(00……59)
%s 秒(00……59)
%p AM或PM
%w 一个星期中的天数(0=Sunday ……6=Saturday ）
%U 星期(0……52), 这里星期天是星期的第一天
%u 星期(0……52), 这里星期一是星期的第一天