mysql基础面试必会_面试必备sql知识点——MySQL基础进阶

上期讲解了MySQL的一些基础知识(面试必备sql知识点——MySQL基础)

本期继续深化知识点，基于基础，做进一步延伸。学习就是个不断反刍的过程，将碎片化的知识，搭建成完整的框架体系，才能够真正掌握。如果对你有帮助，建议点赞收藏！

内容大纲：

一、条件

CASE表达式

case 函数的格式为：case when 判断表达式 then 表达式 else 表达式 end

else 可省略，end不可省略。连续数值转离散分类表格

--借款金额分组select (case

when 借款金额>0 and 借款金额 <=5000 then '0-5000'

when 借款金额>5000 and 借款金额 <=10000 then '5000-10000'

when 借款金额>10000 and 借款金额 <=20000 then '10000-20000'

when 借款金额>20000 and 借款金额 <=50000 then '20000-50000'

else'50000以上' end )as "借款金额区间"

from table1行列转换表格

SELECT 学号,

sum(case 课程号 when '001' then 成绩 else 0 end ) as '课程001',

sum(case 课程号 when '002' then 成绩 else 0 end ) as '课程002',

sum(case 课程号 when '003' then 成绩 else 0 end ) as '课程003'

from score

GROUP BY 学号;查询结果

if函数

if函数格式为：if(条件，条件为真时的值，条件为假时的值)

如上述行列转换也可以这样写，得到的结果一致。

SELECT

sum(if(课程号=001,成绩,0))as '课程001',

sum(if(课程号=002,成绩,0))as '课程002',

sum(if(课程号=003,成绩,0))as '课程003'

from score

GROUP BY 学号;查询结果

但借款金额的分组就不如case when 书写方便了，但在计数算比列方面，if的写法可以减少查询的嵌套。

二、字符串函数

常用concat 字符串拼接 —— concat(字符串,字符串···)表格

SELECT concat(str1,str2) as string

from str;查询结果replace 字符串替换 —— replace(字符串，被替换的字符串，替换字符串)

-- 改变上表中str1列的值UPDATE str set str1 = replace(str1,'ab','cc');结果substring字符串截取——substring(对象字符串 from 起始位置 for 截取字符数)原表新增1列

UPDATE str set str3 = substring(str2 from 1 for 2);str3列被赋值str2列字符串的前两个字符

不常用length长度——length()

select length(str1) from str;str1列字符串对应长度lower小写——lower()修改一下str1列，将首字母改为大写

select lower(str1) from str;结果upper大写——upper()

select upper(str1) from str;结果

类型转换

cast——cast(值 as 类型)

select cast('20190308' as date ) as date;查询结果

三、日期函数

日期转换str_to_date(format,date)：将指定字符串格式转换成日期

SELECT STR_TO_DATE('2018/09/02', '%Y/%c/%d') AS outputdate_format(date,format)：将日期转换成指定字符串格式

SELECT DATE_FORMAT('2018-05-06', '%Y年%m月%d日')AS output;from_unixtime(unix_timestamp,format)：将时间戳转化为日期

SELECT FROM_UNIXTIME(1452959999,'%Y-%m-%d %H:%i:%s')as date；

日期提取now( ) ——返回当前系统日期+时间

SELECT now( );curdate( )——返回当前系统日期，不包含时间

SELECT curdate( );curtime( ) ——返回当前时间,不包含日期

SELECT curtime( );year ()month()day()——获取日期年月日(%Y-%m-%d)

SELECT YEAR('2019-01-08') AS 'year', MONTH('2019-03-06') AS 月, DAY('2019-02-09') "日";hour()minute ( ) second( ) ——获取日期时分秒(%h：%i：%s)

SELECT HOUR('2019-01-08 18:36:25') AS '时', MINUTE('2019-03-06 18:36:25') AS 分,

SECOND('2019-02-09 18:36:25') "秒";

日期运算datediff——datediff(date1，date2) 返回起始时间 date1 和结束时间 date2 之间的天数。

SELECT DATEDIFF('2017-11-30','2017-11-27') AS date;date_sub——date_sub(date,INTERVAL expr type) 表示从日期减去指定的时间间隔后的日期，date参数是日期表达式。expr 参数是时间间隔，type 参数有很多，常用的是day。

SELECT DATE_sub('2017-11-30',interval 3 day) AS date;dateadd——date_add(date,INTERVAL expr type) 表示向日期添加指定的时间间隔后的日期

SELECT DATE_add('2017-11-30',interval 3 day) AS date;

四、算术函数+ - * / —— 加减乘除：比较简单常用

round —— round(数值，保留小数的位数)：对数据四舍五入处理

select round(1.3567,2) as number;abs——abs(数值)：求绝对值

select abs(-2) as number;mod——mod(被除数，除数)：求余数

select mod(10,3) as number;

五、窗口函数

窗口函数语法： 窗口函数 over(partition by 列名 order by 列名)

partition by 后接的是分组列名 (可根据需求省略)

order by 后接的是排序的列名

简单来说，窗口函数的作用就是将数据表先分组后排序，然后根据选用的窗口函数来实现具体的功能。

mysql中窗口函数可以分为两类

【1】专用窗口函数(排序)：rank 函数：计算排序时，存在相同位次记录，则会跳过之后位次：1，1，1，4····

-- rank()over(partition by 列名 order by 列名)select*,rank()over(PARTITION by department ORDER BY salary desc)as rk

from table1;dense_rank函数：计算排序时，存在相同位次，也不会跳过之后的位次：1，1，1，2····

-- dense_rank()over(partition by 列名 order by 列名)select*,dense_rank()over(PARTITION by department ORDER BY salary desc)as dr

from table1;row_number函数：计算排序时，哪怕值相同，也是连续排列：1，2，3，4·····

-- row_number()over(partition by 列名 order by 列名)select*,row_number()over(PARTITION by department ORDER BY salary desc)as rn

from table1;

【2】聚合窗口函数(不常用)

聚合函数：sum、avg、count、max、minsum作为窗口函数使用，计算累计值

select*,sum(salary)over(ORDER BY id)as sum_salary

from table1;计算累计salaryavg作为窗口函数使用，计算移动平均

拓展：指定框架(汇总范围)

row N preceding ：截止到当前记录之前N行——加上自身，一共计算N+1行值的平均值

row N following：截止到当前记录之后N行 ——加上自身，一共计算N+1行值的平均值某股票日收盘价格表

SELECT*,avg(close_price)over(ORDER BY date rows 1 preceding )as avg_2

from price;

六、自连接

自连接查询就是以类似多表对比的方式，实现对同一张表内的数据进行复杂的关系表示或关系处理。

示例1：成绩表score，查询001课程成绩>= 002课程成绩的学生的学号

写法一：

SELECT a.学号,a.成绩 as score_001,b.成绩 as score_002

from score a ,score b

where a.学号=b.学号

and a.课程号 = 001

and b.课程号 = 002

and a.成绩>=b.成绩;

写法二：

SELECT a.学号,a.成绩 as score_001,b.成绩 as score_002

from score a join score b

on a.学号 = b.学号

where a.课程号 =001 and b.课程号 =002

and a.成绩>=b.成绩;

示例2：weather表，查询今日温度比昨天温度高的日期

写法一：

select a.recorddate

from weather a, weather b

where a.Temperature > b.Temperature

and datediff(a.RecordDate,b.RecordDate) = 1;

写法二：

select a.recorddate

from weather a join weather b

on a.Temperature > b.Temperature

where datediff(a.RecordDate,b.RecordDate) = 1;