MySQL随笔1

MySQL随笔

​ 国庆期间花一点时间刷了牛客和力扣的MySQL入门，两个网站的题目各有各的特色。不过最大的感触就是牛客的SQL题你都可以做，但力扣就只给你SQL入门里的题，其他专项等级你就要开VIP了，而且还邪贵。牛客的简单题那就真的太弱智了，力扣相比之下还装一下，虽然也很弱智，但不至于让你写都懒得写。但力扣的中等题明显难度不如牛客的大，而且题型也不如牛客多。总之这两边都总结一下，写个记录

​ MySQL函数手册：MySQL ASCII() 函数 (w3schools.cn)

coalesce()函数

​ 这个函数可以拿来和ISNULL比较，但是coalesce更强大一些。语法如下

COALESCE(expression [,……n])

​ 如果入参均为null，那么coalesce函数会返回null。如果有非null参数，那么就返回第一个非null参数。因为函数会返回第一个非NULL参数的值，所以如果我们用COALESCE(null,null,null……)这种用法，就会报错

​ 这个函数可以这么应用：如果为null，就在查询表中记录为0，如果非null，就为查询到的值

COALESCE(getdate(),0)

Column 'xxx' in field list is ambiguous报错

​ 这个英文的意思很好懂，就是他说有一个字段是模糊的。出现的原因是在涉及查询的表中，有多张表具有同一名字的字段，比如表1中有一个字段是name，表2中也有一个字段是name，如果不在name前加表的名字或别名，就会报这个错误。

​ 所以解决方式也很简单，在查询语句中报错的模糊字段前加上表的名字即可

LEFT JOIN和JOIN的区别

​ INNER JOIN和JOIN在MySQL中是相同的，使用JOIN时默认是INNER JOIN

​ LEFT JOIN和JOIN的区别如下：

• JOIN只返回两个表中连结字段相等的行，而LEFT JOIN会返回左表中的所有记录和与右表连结字段相等的记录
• INNER JOIN的数量小于等于左表和右表的记录数量，而LEFT JOIN的数量与左表的记录数量相同
• INNER JOIN中不足的记录属性会被直接舍弃，而LEFT JOIN中不足的属性会用NULL填充

HAVING和WHERE

​ 如果有GROUP BY子句的情况下，我们可以用HAVING子句进行分类依据的限定，而不是使用WHERE

COUNT(*)

​ COUNT(*)的作用是返回本次SELECT语句所查询到的行数

DATEDIFF()函数

​ DATEDIFF()函数会返回两个日期值之间的天数差值，语法如下：

DATEDIFF(date1,date2)

​ 计算方式为date1-date2，参数形式均为yyyy-MM-dd形式

窗口函数

窗口函数在何种场景下使用：

​ 窗口函数可以在topN场景下使用：按照某一标准找出每个部门排名前N的员工

什么是窗口函数：

​ 窗口函数又名OLAP函数，可以对数据库的数据进行实时分析处理。窗口函数的语法如下：

<窗口函数> over (partition by <用于分组的列名>
             	order by <用于排序的列名>)

​ 窗口函数的的位置可以放如下两种函数：

• 专用窗口函数，比如rank、dense_rank、row_number等
• 聚合函数，比如sum、avg、count、max、min等

​ 因为窗口函数是以where或者group by子句处理后的结果为操作对象，所以原则上窗口函数只能写在SELECT子句中

使用示例：

​ 这部分可以看猴子大佬的教程，写的非常通俗易懂。

​ 通俗易懂的学会：SQL窗口函数 - 知乎 (zhihu.com)

小应用：

​ 比如我们现在想要找到每个部分中的TOP1，我们可以使用row_number这个窗口函数，并且将查询结果设置一个别名，比如为rn，那就可以使用WHERE rn=1来获取每个部门的TOP1

SUBSTRING_INDEX()函数

​ SUBSTRING_INDEX()函数可以返回一个字符串出现指定数量的分隔符之前的字符串，语法如下：

SUBSTRING_INDEX(string，delimiter，number)

使用示例：

​ 比如我们现在有一个url是：http:/url/bisdgboy777，那一般来说每个url的前缀都相同，实际场景可能就是最后一个分隔符后的内容是有具体含义的，需要我们提取之后拿来进行一些操作。这时候我们就可以使用这个函数。SUBSTRING_INDEX(url,'/',-1)意思就是按照/分隔符进行分割，从右往左取第一个分隔符后的内容，即bisdgboy777，这点和Python的语法一样，-1就是右往左第一个，0就是左往右第一个。

​ 有时候可能我们需要倒数第二个字符，那就出现一个问题，如果我们用-2，就连带着把-1的内容也加上了，这时候我们可以连着用两次这个函数，每次都要倒数第一个就行了

LIKE子句中的通配符

​ LIKE子句中可以有如下四种通配符

• _：匹配任意一个字符
• %：匹配0或多个字符
• []：匹配[]中的任意一个字符，若比较字符是连续的，可以用'-'来表达
• [^]：不匹配[]中的任意一个字符

#查询学生中姓张的学生
SELECT * FROM student_table WHERE name LIKE '张%'
#查询学生中姓张且名字是三个字的同学
SELECT * FROM student_table WHERE name LIKE '张__'
#查询学生中姓张，姓刘，姓李的同学
SELECT * FROM student_table WHERE name LIKE '[张刘李]%'
#查询学生中不姓刘的学生
SELECT * FROM student_table WHERE name NOT LIKE '刘%'
#查询学号最后一位不是2、3、5的学生
SELECT * FROM student_table WHERE student_number LIKE '%[^235]'

三表联查

​ 在牛客刷题时，遇到很多题目需要三表联查，之前一直都是两表联查，第一次还犹豫了一下，三表联查语法支持吗，结果发现是可以的。后来差了一些资料，推荐最好是不要进行三表联查的，这样效率太低了，更应该在设计数据库时安排好表中的字段，尽可能尝试能否避免三表联查