SQL学习

一、函数学习

1、ROW_NUMBER() OVER()函数

语法格式：row_number() over(partition by 分组列 order by 排序列 desc)

注意；在使用 row_number() over()函数时候，over()里头的分组以及排序的执行晚于 where 、group by、 order by 的执行。

　　①表数据

create table TEST_ROW_NUMBER_OVER(
       id varchar(10) not null,
       name varchar(10) null,
       age varchar(10) null,
       salary int null
);
select * from TEST_ROW_NUMBER_OVER t;
 
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(1,'a',10,8000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(1,'a2',11,6500);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(2,'b',12,13000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(2,'b2',13,4500);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(3,'c',14,3000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(3,'c2',15,20000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(4,'d',16,30000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(5,'d2',17,1800);

 

　　②进行一次排序，无分组

格式：row_number() over(order by 字段 desc)

select id,name,age,salary,row_number() over(order by salary desc) rn from TEST_ROW_NUMBER_OVER 

　　③根据id分组排序

格式 row_number()over(partition by 字段1 order by 字段2 desc)
根据字段1进行分组，再根据字段2进行组内排序

select id,`name`,age,salary,row_number()over(partition by id order by salary desc) rn from TEST_ROW_NUMBER_OVER t;

　　④练习题

有这么一张表student，存在多个相同数据的项，因此请删除no、name、score都不相同的项。

第一步使用开窗函数筛选出以no、name、score为分组的项

select id,`no`,`name`,score,row_number()over(partition by `no`,`name`,score)as num from student

第二部通过join连接表进行删除多余相同的项

delete s from student s join 
(select id,`no`,`name`,score,row_number()over(partition by `no`,`name`,score)as num from student) a 
on s.id = a.id where num != 1

执行完delete后在查询整个student表

2、DATE_ADD()、DATE_SUB函数

DATE_ADD(date,INTERVAL expr unit) 加

DATE_SUB(date,INTERVAL expr unit) 减

说明;其中 date表示时间字段或者时间类型
interval是固定标志，expr 表示数量，可以为正负，表示加减，unit表示日期类型 可以是yy,ww,dd等分别表示，年，周，天等，

unit 参数可以是下列值：
MICROSECOND  微秒；[计量] 一百万分之一秒
SECOND       秒
MINUTE       分
HOUR         小时
DAY          天
WEEK         周
MONTH        月
QUARTER      三个月
YEAR         年 
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

　　具体示例如下：

-- 得到当前时间增加1个小时的结果
select date_add(now(),interval 1 hour)

-- 得到当前时间增加1天的结果
select date_add(now(),interval 1 day)

-- 得到当前时间减少72个小时的结果
select date_add(now(),interval -72 hour)

-- 还有一些更加高级的用法，比如增加一天一小时一分一秒
-- 但是如果你不太喜欢这种用法，你仍然可以使用等同于该数值的second来进行加减
date_add(now(),interval '1 1:1:1' day_second)

 

3、时间函数NOW()和SYSDATE()

NOW()取的是语句开始执行的时间，SYSDATE()取的是动态的实时时间。

因为NOW()取自mysql的一个变量”TIMESTAMP”，而这个变量在语句开始执行的时候就设定好了，因此在整个语句执行过程中都不会变化。

执行下面这个例子就明白了：

SELECT NOW(),SYSDATE(),SLEEP(3),NOW(),SYSDATE()

先查询了NOW()和SYSDATE()，然后sleep了3秒，再查询NOW()和SYSDATE()，结果如下：

 

NOW()还有3个同义词，效果跟NOW()一样，而且都有2种形式写法：

LOCALTIME或LOCALTIME() ---> localtime LOCALTIMESTAMP

LOCALTIMESTAMP() ---> localtimestamp CURRENT_TIMESTAMP

CURRENT_TIMESTAMP() --->current_timestamp