力扣 (LeetCode)SQL题目-知识点易错点感悟记录（边做边更）

字符串截取：

1、LOCATE(substr , str )：返回子串 substr 在字符串 str 中第一次出现的位置，如果字符substr在字符串str中不存在，则返回0；

2、POSITION(substr  IN str )：返回子串 substr 在字符串 str 中第一次出现的位置，如果字符substr在字符串str中不存在，与LOCATE函数作用相同；

3、LEFT(str, length)：从左边开始截取str，length是截取的长度；

4、RIGHT(str, length)：从右边开始截取str，length是截取的长度；

5、SUBSTRING_INDEX(str  ,substr  ,n)：返回字符substr在str中第n次出现位置之前的字符串;

6、SUBSTRING(str  ,n ,m)：返回字符串str从第n个字符截取到第m个字符；

7、REPLACE(str, n, m)：将字符串str中的n字符替换成m字符；

8、LENGTH(str)：计算字符串str的长度。

DATE_FORMAT(date,format)

//可以用于日期截取

trans_date——2018-12-18
date_format(trans_date,"%Y-%m") as month
month——2018-12

mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
        -> 'Sunday October 2009'
mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
        -> '22:23:00'
mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00',
    ->                 '%D %y %a %d %m %b %j');
        -> '4th 00 Thu 04 10 Oct 277'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
    ->                 '%H %k %I %r %T %S %w');
        -> '22 22 10 10:23:00 PM 22:23:00 00 6'
mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
        -> '1998 52'
mysql> SELECT DATE_FORMAT('2006-06-00', '%d');
        -> '00'

DATEDIFF()Returns expr1- expr2表示为从一个日期到另一个日期的天数。expr1 并且expr2是日期或日期和时间表达式。计算中仅使用值的日期部分。

//截至 2019-07-27（含）的 30 天内
//datediff('2019-07-27',activity_date)<30
//activity_date BETWEEN '2019-06-28' AND '2019-07-27'

inner join：两表值都存在

outer join：附表中值可能存在null的情况。

总结：

A inner join B：取交集

A left join B：取A全部，B没有对应的值，则为null

A right join B：取B全部，A没有对应的值，则为null

A full outer join B：取并集，彼此没有对应的值为null

上述4种的对应条件，在on后填写。

AVG(条件)  相当于 sum(if(条件,1,0))/count(全体)
进阶
sum(if(条件,N,0))/count(全体) 可用  N*avg(条件) 代替

使用bool条件将多个样本判断为0和1，多个0和多个1的平均值就是1在整体中的比例，也即满足条件的样本在整体中的比例。

round(avg(rating < 3) * 100,2) as  poor_query_percentage

round(sum(if((rating < 3),1,0))/count(*)*100,2) as  poor_query_percentage 
from Queries

ROUND函数

1.数据库数据计算是从0开始的

2.offset X是跳过X个数据，limit Y是选取Y个数据

3.limit  X,Y  中X表示跳过X个数据，读取Y个数据

1  selete * from testtable limit 2,1;

2  selete * from testtable limit 2 offset 1;

这两个都是能完成需要，但是他们之间是有区别的：

1.是从数据库中第三条开始查询，取一条数据，即第三条数据读取，一二条跳过

2.是从数据库中的第二条数据开始查询两条数据，即第二条和第三条。

图表的行转换成列(横表转竖表） 使用 union all

UNION将多个SELECT语句的结果组合成一个结果集。

结果集的列名UNION 取自第一条 SELECT语句的列名。

SELECT在每个语句 的相应位置列出的选定列 应具有相同的数据类型。

UNION DISTINCT 和 UNION ALL

默认情况下，从 UNION结果中删除重复行。可供选择的 DISTINCT关键字具有相同的效果，但使其明确。使用可选ALL 关键字，不会发生重复行删除，结果包括所有 SELECT语句中的所有匹配行。

CONCAT() 函数
CONCAT 可以将多个字符串拼接在一起。

LEFT(str, length) 函数
从左开始截取字符串，length 是截取的长度。

UPPER(str) 与 LOWER(str)
UPPER(str) 将字符串中所有字符转为大写

LOWER(str) 将字符串中所有字符转为小写

SUBSTRING(str, begin, end)
截取字符串，end 不写默认为空。

SUBSTRING(name, 2) 从第二个截取到末尾，注意并不是下标，就是第二个。

concat(upper(left(name,1)),lower(substr(name,2)))

在 MySQL 中， LIMIT 语句可以被用来限制 SELECT 语句的返回行数。它需要传入 1 个或 2 个非负整数参数，第一个参数 offset 表示跳过前面多少行后开始取数据，第二个参数表示最多返回多少行的数据。默认 offset 为 0（不是 1）。

LIMIT 语句也可以只使用一个参数，这个参数的含义是从结果的第一行开始返回的行数。所以 LIMIT 1 会返回第一行的记录。

NULL 值的问题

MySQL :: MySQL 5.7 Reference Manual :: B.3.4.3 Problems with NULL Values

IFNULL

group_concat( [DISTINCT] 要连接的字段 [Order BY 排序字段 ASC/DESC] [Separator ‘分隔符’] )

group_concat( distinct product separator ',')

筛选出所有的数据，如果有重复数据，需要继续处理。因为表无主键，因此可能会存在重复行，所以我们要对结果去重，使用 DISTINCT 即可。

distinct(viewer_id)

UPDATE是修改表中行的 DML 语句。

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET assignment_list
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

存储程序的CASE语句实现了一个复杂的条件结构。

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

第一次XXX的日期

min(event_date)

使用 UNION 连接子查询比WHERE后OR链接两个条件的速度快