mysql：时间、逻辑判断（if、case when）、排序、自定义数据查询、MSQL三大范式、group分组、TopN

一、时间

1、获取时间

SELECT CURRENT_TIMESTAMP();# 年月日时分秒

SELECT CURRENT_DATE();# 年月日

SELECT CURRENT_TIME();# 时分秒

可以不加括号。

2、时间和varchar的相互转换

SELECT DATE_FORMAT(CURRENT_Date(),'%Y-%m-%d'); 时间转varchar，中间连接符可改。

SELECT STR_TO_DATE('2022-03-10','%Y-%m-%d');varchar改为时间。

3、时间的相加减

时间相减：select datediff('2022-03-14','2022-03-01'); # 13

时间相加：DATE_ADD(date,INTERVAL expr unit)
INTERVAL:关键字
expr:加减的值
unit:单位(year 月 day)

例：select DATE_ADD('2022-03-03',INTERVAL -3 day);

4、求时间的分类

-- 第一步 时间相减 0 1 2 3 4 5 6---13
select *,datediff(datetime,'2022-03-01') from test;
-- 第二部 除以7    0.0 0.0002 0.0003..1.1
select *,datediff(datetime,'2022-03-01')/7 from test;
-- 第三步 向下取整
select *,FLOOR(datediff(datetime,'2022-03-01')/7) as g from test;
-- 第四步:确定开头和结尾
select *,FLOOR(datediff(datetime,'2022-03-01')/7)*7  from test;
select *,DATE_ADD('2022-03-01',INTERVAL FLOOR(datediff(datetime,'2022-03-01')/7)*7 day) from test;
select *,DATE_ADD('2022-03-01',INTERVAL FLOOR(datediff(datetime,'2022-03-01')/7)*7+6 day) from test;
-- 第五步concat()
select *,CONCAT(
    DATE_ADD('2022-03-01',INTERVAL FLOOR(datediff(datetime,'2022-03-01')/7)*7 day),
    '~',
    DATE_ADD('2022-03-01',INTERVAL FLOOR(datediff(datetime,'2022-03-01')/7)*7+6 day)) from test;

二、逻辑判断

1、if

格式:if(判断条件,true,false)

select *,if(sex='1','男','女') from student;
select *,if(age>=18,'成年','未成年') from student;

2、case when

格式1双分支: case  when  判断条件  then  true  else  false  end;

格式2多分枝:case  when  判断条件  then  true  when  判断条件  then  true  else  false  end

select *,case when sex='1' then '男' else '女' end as gender from student;
select *,case when age>=18 then '成年' else '未成年' end as gender from student;
​​
select *,case when age=18 then '刚成年' when age<18 then '未成年' else '以成年' end from student;
​

三、排序

order by 字段1，字段2 （asc或者desc）  asc升序，可不写默认升序，desc降序。（由上而下）

#  默认不给排序规则 就是升序
select * from student order by age;     # age 从小到大 升序
select * from student order by age asc;#   age从小到大 升序
select * from student order by age desc;#  从大到小 降序
​
# age 从小到大 如果有相同的age按sex从小到大
select * from student order by age ,sex;
​
# age 从小到大 如果有相同的age按sex从大到小
select * from student order by age ,sex desc;
​
​
# age 从大到小 如果有相同的age按sex从大到小
select * from student order by age desc ,sex desc;

四、自定义数据查询

limit

limit m;默认从0开始取m个数据，如果m大于总的数据个数，则取到末尾。

limit n,m 从n开始取m个数据

select * from student limit 2; # 从0开始取两个
select * from student limit 2,5; # 从2开始取5个

五、注意的问题

1、当limit和排序同时运行时，先排序在自定义查询多少为数据

SELECT * from test ORDER BY sum DESC limit 3;  显示的是最大的三个数。先排序后limit。且limit只能在最后，不然会报错。

2、求大于平均年龄的数据

SELECT * from test WHERE sum>avg(sum);这样写会报错，因为聚合函数avg的结果只有一个，不能连续进行比较。

正确写法：SELECT * from test WHERE sum>(SELECT avg(sum) from test);

六、MSQL三大范式

第一范式:原子性 字段不可再分割

第二范式:唯一性 字段必须依赖与逐渐

第三范式:冗余性 不能进行依赖传递

七、group分组

格式 :select ..... group by 字段1,字段2...字段n;  每组中字段数据都是相同的

求的是组数据 ，不是组内的数据

有多少组 结果就有多少条数据(是把每组中数据进行了聚合转化为了一条)

gruop by 通常和聚合函数一起使用

后面有多少个字段就根据所有的字段数据一起进行分组

默认情况下 显示每组中第一条数据，不是分组字段的数据没有任何作用。

select * from student group by sex; # 所获取的id name age 没有任何作用
select sex,max(age) from student group by sex;  # 通常 获取分组字段和聚合函数一起使用,求根据性别分组的每组中年龄最大的值。

八、TopN，求每组的最大或最小的n个。

不能再使用group by 解决TopN的问题

因为group by所获的数据量是固定的(和组的数量保持一致)

TopN数量不固定

输出每组中年龄最大的n个学生
-- TopN 
-- Top1
select * from student as s1 where 0=(select count(*) from student as s2  where s1.sex=s2.sex and s1.age<s2.age)
把一个表在复制一份，分别为表1和表2，因为有条件为两表的性别相同的进行比较，所以将表12都分成两组分别比较，用表1的组中每一条字段数据和表2的相同组中所有字段数据进行比较，统计小于的个数，如果为0，则代表最大的数据。
-- -- Top3
select * from student as s1 where 3>(select count(*) from student as s2  where s1.sex=s2.sex and s1.age<s2.age)
​3表示小于的个数为0,1,2，表示组中最大的三个数据

如果没有性别相同的条件，则是显示表中排序前n个数据。
​