我的学习笔记

主题

数据库分析函数 ​

sql
# 查看内置函数说名文档
# help `count`;

一、MySQL常用内置函数 ​

1. 数值函数 ​

1.1 小数位 ​

sql
# round(小数)  四舍五入
# format(小数,保留几位小数)  用四舍五入保留
# truncate(小数,截取几位小数)  小数截取直接截取
# floor(小数) 向下取整（小数直接去掉）
# ceil(小数) 向上取整（小数进一）

1.2 数值计算 ​

sql
# 求余数 mod（被除数，除数）
# 求幂 pow（底数，指数）
# 随机数 rand（种子），随机0-1的值，如果有种子，种子不变，随机的值不变。

2. 字符串函数 ​

2.1 大小写转换 ​

sql
# upper转大写   lower转小写

2.2 字符串反转、重复、拼接、替换 ​

sql
# reverse反转（反转字母顺序）
# repeat（重复内容，重复次数）  重复
# concat(内容，内容，...)     拼接
# concat_ws(拼接符号,内容，内容，...) 含拼接符号
# replace(源文本，替换内容，替换成什么)  替换

2.3 字符串截取 ​

  1. 直接截取

    sql
    # substr/substring（要截取的内容，从第几位开始截取） 
# 1表示从第一位开始截取，-1表示从最后一位开始截取

  2. 位置截取

    sql
    # left（要截取的内容，截取几位） 从左侧截取
# right（要截取的内容，截取几位） 从右侧截取

2.4 字符串长度 ​

sql
# char_length
# length   （重点）

3. 时间日期函数 ​

3.1 获取当前时间类型 ​

sql
select now(); #年月日 时分秒
select current_timestamp(); #年月日 时分秒
select current_time(); #时分秒
select current_date(); #年月日

3.2 时间比对的函数 ​

sql
# date_add(初始时间，比对的时间(interval xx day/month/year)——往后顺延的时间
# date_sub(初始时间，比对的时间）——往前滚动的时间
# datediff(时间1，时间2）——求两个日期的时间差，单位是天，前面时间-后面时间
# timestampdiff(比对的维度，时间1，时间2)——求两个日期的时间差

3.3 通过日期获取年月日时分秒 ​

sql
select year(now());
select month(now());
select day(now());
select hour(now());
select minute(now());
select second(now());
select weekday(now()); #0-6 表示周一到周天

3.4 时间转换 ​

大小写：Y：2026 y：26 M：may m：05 D：25th d：25

  1. 字符串-时间

    sql
    # date_format(要格式化的时间,'格式化成什么样（用%）')
# str_to_date(格式化的字符串，'%Y %M %D')  两个格式要对应

  2. 时间戳-时间

    sql
    # unix_timestamp([日期]);  不写日期表示当前时间
# from_unixtime（秒数）;

4. 条件判断 ​

4.1 if语句 ​

sql
# if（expr，v1,v2）  表达式（expr）结果为true返回v1，否则返回v2

4.2 case语句 ​

sql
# case
#   when 表达式  then 表达式成立时的值
#   when 表达式  then 表达式成立时的值
#   when 表达式  then 表达式成立时的值
#   ...
#   else 都不成立时的值
# end

二、经典面试题 ​

拼多列用join表，拼多行用union表

如果列名是中文，那么取列的值列名要用反引号括起来

1. 多行转多列（多行转一行） ​

sql
# 思想：
# 第一列：分组
select number
from area.score
group by number;
# 第二列：查询成绩，判断，聚合
select number                         '学号',
       sum(if(kemu = '语文', score, 0)) '语文',
       sum(if(kemu = '数学', score, 0)) '数学',
       sum(if(kemu = '英语', score, 0)) '英语'
from area.score
group by number;


#思想：先拼接，后筛选 （性能很低）
select s1.number '学号', s1.score '语文', s2.score '数学', s3.score '英语'
from area.score s1
       join area.score s2 on s1.number = s2.number
       join area.score s3 on s1.number = s3.number
where s1.kemu = '语文'
  and s2.kemu = '数学'
  and s3.kemu = '英语';
# 思想：拼接时筛选（性能低）
select s1.number '学号', s1.score '语文', s2.score '数学', s3.score '英语'
from area.score s1
       join area.score s2 on s1.number = s2.number and s1.kemu = '语文' and s2.kemu = '数学'
       join area.score s3 on s1.number = s3.number and s3.kemu = '英语';

2. 多列转多行（一行转多行） ​

sql
# 思想：凑学号，科目，成绩（三个），再用union拼接,再排序
select number '学号', '语文' as '科目', yuwen '成绩'
from area.info
union
select number, '数学' as '科目', shuxue '成绩'
from area.info
union
select number, '英语' as '科目', yingyu '成绩'
from area.info
order by 学号;  # 排序