Mysql常用查询语句

0x01、将数据库表行变列

使用(case col when "colname" then col else value end)将行变成列

-- 将行变成列
select name,
        max(case subject when "语文" then score else 0 end) 语文,
        max(case subject when "数学" then score else 0 end) 数学
from stu_score group by name;

 

0x02、将列变成行，使用union

使用union和order by 将列变成行

union会将查询结果去重，union不会去重

select ID, USER_NAME, "语文" course, CN_SCORE as score from test_tb_grade2
union 
select ID, USER_NAME, "数学" course, MATH_SCORE as score from test_tb_grade2
order by USER_NAME,course;

 

0x03、根据日期查看年龄

select * from person;
select id, name, TIMESTAMPDIFF(YEAR,birthday,CURDATE()) as age from person;  -- 2年354天结果为2年
select id, name, ROUND(DATEDIFF(CURDATE(),birthday)/365.2422) as age from person;  -- 将当前日期天数和birthday相减，然后除以一年的平均天数，最后四舍五入

 

0x04、日期函数

-- 查找近30天数据
mysql> SELECT something FROM tbl_name

    -> WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_col；