mysql知识点回顾与梳理

一、sql语句执行顺序

1. from
2. join
3. on
4. where
5. group by
6. avg，sum，count等各种函数
7. having
8. select
9. distinct
10. order by(asc(升序),desc(降序))
11. limit

二、如何获取表记录，或者某字段不同值个数

select 

count(distinct a.user_id) as cnt

from table a

三、如何在sql表(假设为表a)中删除重复行

方法1：

stp1:筛选出不重复的表记录

        select distinct a.* from a

stp2:将记录插入到临时表b中

        insert into b

        select distinct a.* from a

stp3:清空表a 

       truncate table a

stp4:将临时表数据插入表a

       insert into a

       select * from b

方法2：

delete from  a
where (a.col_1,a.col_2) in

( select col_1,col_2 

   from a

   group by col_1,col_2

   having count(*) > 1)

and rowid not in (select min(rowid) a vitae group by col_1,col_2 having count(*)>1)

四、My Sql row_number...partition_by函数以及如何模拟它

SELECT collect_user_name, collect_time,rank
FROM
(
SELECT t.collect_user_name,t.collect_time,
CASE WHEN @partition_by =null  or  @partition_by!= t.collect_user_name THEN @rownum := 1
ELSE @rownum :=@rownum + 1
END AS rank
,@partition_by := t.collect_user_name  AS  partition_by
FROM (select @partition_by:=null, @rownum:=0)s, (
SELECT collect_user_name, collect_time FROM pl_collect_call_info cat
WHERE date(cat.collect_time) = curdate()
ORDER BY cat.collect_user_name,cat.collect_time
) t

#INNER JOIN (SELECT @rownum :=0) r ON 1=1
#INNER JOIN (SELECT @partition_by :='') p ON 1=1
#ORDER BY t.collect_user_name, t.collect_time
)tb ;

五、My SQL间隔进行日期和时间算术，以及示例

日期间比较：date(regist_time)>=curdate() - interval 2 day

日期间计算：datediff(a,b)：返回a,b相隔天数； adddate(a,n)：a加上n天；subdate(a,n)：a减去n天；

返回当前时间：curtime(),current_time();

返回当前日期和时间：now(), current_timestamp(), localtime(), localtimestamp(), sysdate()

时间戳转换：

select unix_timestamp(curdate())
select from_unixtime(1620666000)

 

日期和时间格式

年月日：DATE_FORMAT(cld.calendar_date, '%Y%m%d')

时分秒：DATE_FORMAT(NOW(),'%H:%i:%s'), H :24小时格式；h:12小时格式。

年月日&时分秒：DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s')

 

时间差函数：

datediff函数，返回值是相差的天数，不能定位到小时、分钟和秒。

-- 相差2天(a-b)
select datediff('2018-03-22 09:00:00', '2018-03-20 07:00:00');

 

 TIMEDIFF函数，按时间格式返回时间差，含时分秒。语法：timediff(a,b)：返回两个时间相减得到的差值，(a-b)

SELECT timediff('2018-05-21 14:51:43','2018-05-19 12:54:43');

  TIMESTAMPDIFF函数，有参数设置，可以具体精确到天（DAY）、小时（HOUR），分钟（MINUTE）和秒（SECOND），使用起来比datediff函数更加灵活。对于比较的两个时间，时间小的放在前面，时间大的放在后面。(b-a)

--相差1天
select TIMESTAMPDIFF(DAY, '2018-03-20 23:59:00', '2015-03-22 00:00:00');

 

--相差49小时

select TIMESTAMPDIFF(HOUR, '2018-03-20 09:00:00', '2018-03-22 10:00:00');

  

--相差2940分钟
select TIMESTAMPDIFF(MINUTE, '2018-03-20 09:00:00', '2018-03-22 10:00:00');

 

--相差176400秒

select TIMESTAMPDIFF(SECOND, '2018-03-20 09:00:00', '2018-03-22 10:00:00');

 六、My SQL使用null,及处理null

注意：空值不占空间,null值占空间。定义为NOT NULL的字段只能插入空值，不能插入null值，而NULL字段可以插入空值，也可以插入null值。

可以发现 is not null 只会过滤为null值的列，而<>会同时过滤空值和null值，所以要根据实际情况选择过滤方式。另外，判断null值只能用 is null 或 is not null ，不能用 = 或 <>。

注意事项： 

1：在进行count()统计某列的记录数的时候，如果采用的NULL值，会别系统自动忽略掉，但是空值是会进行统计到其中的。

2： 判断NULL 用IS NULL 或者 is not null,SQL 语句函数中可以使用ifnull()函数来进行处理，判断空字符用 =”或者 <>”来进行处理 。

3: 对于MySQL特殊的注意事项，对于timestamp数据类型，如果往这个数据类型插入的列插入NULL值，则出现的值是当前系统时间。插入空值，则会出现 ‘0000-00-00 00:00:00’ 。

4：对于空值的判断到底是使用is null 还是 =”要根据实际业务来进行区分。

5、当使用ORDER BY时，首先呈现NULL值。如果你用DESC以降序排序，NULL值最后显示。当使用GROUP BY时，所有的NULL值被认为是相等的，故只显示一行。

相关函数：

 COALESCE(exp1,value1,...)函数:返回第一个非null值,如果所有都为null，则返回null；

IFNULL(expression,value) 函数：如果表达式为null，则返回value,否则返回表达式的值；

七、使用MySQL会话变量实现窗口函数

      函数形式：聚合函数() over (partition by ... order by ...) ，其中over() 为窗口 ;

      窗口分类：可分为静态窗口、滑动窗口(聚合函数)。     

     按照功能划分，可以把MySQL支持的窗口函数分为如下几类：

• 序号函数：row_number() / rank() / dense_rank()
• 分布函数：percent_rank() / cume_dist()
• 前后函数：lag() / lead()
• 头尾函数：first_val() / last_val()
• 其他函数：nth_value() / nfile()

详细可参考随笔：使用MySQL会话变量实现窗口函数

对于滑动窗口的范围指定，有两种方式，基于行和基于范围，具体区别如下：

1、基于行：

通常使用BETWEEN frame_start AND frame_end语法来表示行范围，frame_start和frame_end可以支持如下关键字，来确定不同的动态行记录：

CURRENT ROW 边界是当前行，一般和其他范围关键字一起使用

UNBOUNDED PRECEDING 边界是分区中的第一行

UNBOUNDED FOLLOWING 边界是分区中的最后一行

expr PRECEDING 边界是当前行减去expr的值

expr FOLLOWING 边界是当前行加上expr的值

比如，下面都是合法的范围：

rows BETWEEN 1 PRECEDING AND 1 FOLLOWING 窗口范围是当前行、前一行、后一行一共三行记录。

rows UNBOUNDED FOLLOWING 窗口范围是当前行到分区中的最后一行。

rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 窗口范围是当前分区中所有行，等同于不写。

2、基于范围：

和基于行类似，但有些范围不是直接可以用行数来表示的，比如希望窗口范围是一周前的订单开始，截止到当前行，则无法使用rows来直接表示，此时就可以使用范围来表示窗口：INTERVAL 7 DAY PRECEDING。Linux中常见的最近1分钟、5分钟负载是一个典型的应用场景。

有的函数不管有没有frame子句，它的窗口都是固定的，也就是前面介绍的静态窗口，这些函数包括如下：

• CUME_DIST()
• DENSE_RANK()
• LAG()
• LEAD()
• NTILE()
• PERCENT_RANK()
• RANK()
• ROW_NUMBER()

八、时间与时间间隔的运算有哪些

 参考第五条 

九、concat(,) 与group concat()函数

concat()函数

1、功能：将多个字符串连接成一个字符串。

2、语法：concat(str1, str2,...)

返回结果为连接参数产生的字符串，如果有任何一个参数为null，则返回值为null。

 

group_concat()函数

功能：将group by产生的同一个分组中的值连接起来，返回一个字符串结果。该函数返回带有来自一个组的连接的非NULL值的字符串结果。

例如：

  

 

 

1、使用group concat()函数实现列转行

 

2、使用substring_index和cross join将列里面的的数字都拆分出来，把一行变成一列

substring_index（str,delim,count） 
说明：substring_index（被截取字段，关键字，关键字出现的次数） 
例：select substring_index（"blog.jb51.net"，"。"，2） as abstract from my_content_t 
结果：blog.jb51 
（注：如果关键字出现的次数是负数 如-2 则是从后倒数，到字符串结束）

select substring_index("blog.jb51.net",".",-2) as abstract

结果：jb51.net 

 表a cross join 表b,以b为准，返回表b每行对应的表a中的所有记录（表b每行都对应表a中的所有的记录）

例如：存在下表digits,表gurl

 

 

 a cross join b,返回

 

行转列实例：

 

 附代码如下：

select *,substring_index(gurl, ',', digit), SUBSTRING_INDEX(substring_index(gurl, ',', digit),',' ,- 1) gurl
FROM digits
cross join gurl
where digit BETWEEN 1 AND
(SELECT 1 + LENGTH(gurl) - LENGTH(REPLACE (gurl, ',', '')));

 

 十、where、having的条件筛选类型有哪些，哪些包含null,哪些不包含

“Where” 是一个约束声明，是在查询结果集返回之前约束来自数据库的数据，且Where中不能使用聚合函数。
“Having”是一个过滤声明，是在查询结果集返回以后对查询结果进行的过滤操作，在Having中可以使用聚合函数。

HAVING语句通常（亲自验证，不是必须！）与GROUP BY语句联合使用，用来过滤由GROUP BY语句返回的记录集。

HAVING语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足。

 

十一、length()与char_length

select length(remerk),char_length(remerk),mad.*
from `id-admin-db`.ad_operate_log mad
limit 10;

 

 

char_length(str)
1、单位为字符
2、不管汉字还是数字或者是字母都算是一个字符

length(str)
1、字节，utf8编码下,一个汉字三个字节，一个数字或字母一个字节。
2、gbk下,一个汉字两个字节，一个数字或字母一个字节。

length()<>char_length()可以用来检验是否含有中文字符