SQL 常用函数 整理

--创建数据表
create TABLE Persons(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
);
create TABLE Persons(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)；
--添加主键
alter TABLE Persons
ADD PRIMARY KEY (P_Id);
--添加主键【pk_PersonID的值是由两个列（P_Id和LastName）组成的】
alter TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
--撤销主键
alter TABLE Persons
drop PRIMARY KEY；
alter TABLE Persons
drop CONSTRAINT pk_PersonID
--添加外键
create TABLE Orders(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)；

insert INTO Websites (name, url, alexa, country)
VALUES ('百度','https://www.baidu.com/','4','CN');
insert INTO Websites (name, url, country)
VALUES ('stackoverflow', 'http://stackoverflow.com/', 'IND');

update salaries
set salary = case when salary >= 10000 then salary * 0.9
                         else salary * 1.2
                         end
;
update Websites 
SET alexa='5000', country='USA' 
where name='菜鸟教程';

delete from Websites
where name='Facebook' AND country='USA';
--删除所有数据
delete from table_name;
--或
delete * from table_name;

select
    *
    ,row_number() over ( partition by department order by cost desc ) as row_number_result
    ,rank() over ( partition by department order by cost desc) as rank_result
    ,dense_rank() over (partition by department order by cost desc) as dense_rank_result
from table;

select
    *,
    first_value(name) over (PARTITION BY department ORDER BY cost) as min_cost_user, ## 分组取每个组的最小值对应的人    last_value(name) over (PARTITION BY department ORDER BY cost) as max_cost_user ## 分组取每个组的最大值对应的人from table；

select
    *,
    cume_dist() OVER (PARTITION BY department ORDER BY cost desc ) as cum_dist,
    sum(cost) OVER (PARTITION BY department ORDER BY cost desc )/sum(cost) OVER (PARTITION BY department) as s
from table
where department = 'A';
--注：当组内出现重复时，累积计算会有所问题，待核验；

select *,
    lead(cost) over(partition by department order by cost) next_cost
from table;

--当你存储的是日期，希望转化为UNIX时间戳时，使用unix_timestamp函数，命令格式：unix_timestamp(string date, string pattern) ，表示转换pattern格式的日期到时间戳；--当你存储的是时间戳，希望转化为日期，使用from_unixtime函数，命令格式：from_unixtime(bigint unixtime, [string format])；## 日期转化为时间戳 ##select unix_timestamp('2020-03-21 17:13:39')：得到 1584782019select unix_timestamp('20200321 13:01:03','yyyyMMdd HH:mm:ss') 得到 1584766863select unix_timestamp('20200321','yyyyMMdd') 得到 1584720000## 时间戳转化为日期 ## 
select from_unixtime (1584782175) 得到 2020-03-21 17:16:15select from_unixtime (1584782175,'yyyyMMdd') 得到 20200321select from_unixtime (1584782175,'yyyy-MM-dd')得到 2020-03-21## 日期和日期之间，也可以通过时间戳来进行转换 ##select from_unixtime(unix_timestamp('20200321','yyyymmdd'),'yyyy-mm-dd') 得到 2020-03-21select from_unixtime(unix_timestamp('2020-03-21','yyyy-mm-dd'),'yyyymmdd')得到 20200321--注：注意转换的时间格式要求；

--date_sub(string startdate, int days)
## 使用date_sub (string startdate, int days)得到开始日期startdate减少days天后的日期## 
select date_sub('2012-12-08', 10) 得到 2012-11-28
--date_add(string start date, int days)
## 使用date_add(string startdate, int days)得到开始日期startdate增加days天后的日期 ##
select date_add('2012-12-08', 10) 得到 2012-12-18
--datediff(string enddate, string startdate)
## 使用datediff(string enddate, string startdate)得到 结束日期减去开始日期的天数 ## 
select datediff('2012-12-08','2012-05-09') 得到 213

--add_months(d,n); 
--在某一个日期d上,加上指定的月数n,返回计算后的新日期。d表示日期,n表示要加的月数（n可以为负值）
select add_months(sys date,1) from student;

--timestampdiff( interval, datetime_1, datetime_2)
interval:
--毫秒：frac_second
--秒   ：second
--分钟：minuter
--小时：hour
--天   ：day
--星期：week
--月   ：month
--季度：quarter
--年   ：year
--相差1天
select TIMESTAMPDIFF(DAY, '2018-03-20 23:59:00', '2015-03-22 00:00:00');
--相差49小时
select TIMESTAMPDIFF(HOUR, '2018-03-20 09:00:00', '2018-03-22 10:00:00');
--相差2940分钟
select TIMESTAMPDIFF(MINUTE, '2018-03-20 09:00:00', '2018-03-22 10:00:00');
--相差176400秒
select TIMESTAMPDIFF(SECOND, '2018-03-20 09:00:00', '2018-03-22 10:00:00');

--方法：可以直接使用to_date函数，也可以使用字符串提取函数。
select to_date('2020-03-21 17:13:39') 得到 2020-03-21 
select substr('2020-03-21 17:13:39',1,10) 得到 2020-03-21

year()    --获取日期年份
month() --获取日期月份
day()     --获取日期几号
now()    --获取当前日期

--返回指定日期当前月的最后一天；
select last_day(create_time) from student;

select EXTRACT(YEAR from OrderDate) AS OrderYear,
EXTRACT(MONTH from OrderDate) AS OrderMonth,
EXTRACT(DAY from OrderDate) AS OrderDay
from Orders；
--date 参数是合法的日期表达式。unit 参数可以是下列的值：
--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

---interval:
%y：表示年(两位数)，例如: 17 年。
%Y：表示4位数中的年，例如: 2017年
%m：表示月(1-12)
%d: 表示月中的天
%H: 小时(0-23)
%i: 分钟 (0-59)
%s: 秒 (0-59)
--常用格式：'%Y-%m-%d %H:%i:%s'

（1）获取当前日期select curdate();（2）获取本月最后一天select last_day(curdate());（3）获取本月第一天select date_add(curdate(), interval - day(curdate()) + 1 day);（4）获取下个月的第一天select date_add(curdate() - day(curdate()) + 1, interval 1 month);（5）获取当前月的天数select datediff(date_add(curdate() - day(curdate()) + 1, interval 1 month), date_add(curdate(), interval - day(curdate()) + 1 day));

--使用substr/substring (string A, int start)返回字符串A从start位置到结尾的字符串##
select substring('abcde', 3) 得到 cde 
--使用substring(string A, int start, int len)返回字符串A从start位置开始，长度为len的字符串
select substring('abcde', 3,2) 得到 cd

--使用concat(string A, string B) 返回字符串AB的拼接结果,可以多个字符串进行拼接
select concat('abc', 'def','gh') 得到abcdefgh
--使用concat_ws(string X, stringA, string B) 返回字符串A和B由X拼接的结果
select concat_ws(',', 'abc', 'def', 'gh') 得到 abc,def,gh

--使用length(string A)返回字符串A的长度
select length('abcedfg') 得到 7 
--使用trim(string A) 去除字符串两边的空格
select trim(' abc ') 得到 'abc'
--使用lower(string A)/ lcase(string A)返回字符串的小写形式，常用于不确定原始字段是否统一为大小写
select lower('abSEd') 得到 absed
--使用upper(string A)/ ucase(string A)返回字符串的大写形式，常用于不确定原始字段是否统一为大小写
select upper('abSEd') 得到 ABSED

--bigint转换为字符串
select cast(A as string) as A；

--regexp_extract(string subject, string pattern, int index)
--将字符串subject按照pattern正则表达式的规则拆分，返回index指定的字符
select regexp_extract('foothebar', 'foo(.*?)(bar)', 1) 得到 the
--regexp_replace(string A, string B, string C)
--将字符串A中的符合java正则表达式B的部分替换为C
select regexp_replace('foobar', 'oo|ar', '') 得到 fb

--get_json_object(string json_string, string path)
--解析json的字符串json_string,返回path指定的内容
select get_json_object(
                  	{"from_remain_count":420,"reason":"collect","to_remain_count":0},
                    '$.from_remain_count'
                ) 得到 420

⚠️ 使用时注意sql类型，选择合适方案实现目标结果！-- 方案一：支持mysqlset @sum := 0;select p1.ship_day as '日期'
	,p1.order_cnt as '订单数'
	,(@sum := @sum + p1.order_cnt) as  '累计订单数'from(
	select left(a.ship_day, 10) as ship_day
		,count(a.order_no) as order_cnt 
	from order_table as a
	GROUP BY left(a.ship_day, 10)
	ORDER BY left(a.ship_day, 10) asc
	) as p1;-- 方案二：（子查询）select a.date,
  (select sum(a.num) summary
   from test b
   where b.date <=a.date) as summaryfrom test a group by date;-- 方案三：（笛卡尔积）【推荐使用】select b.date
   ,sum(a.num) from test a,test b where a.date<=b.date group by b.date;-- 方案四：（窗口函数）【不支持mysql】select date
   ,sum(num) over(partition by [group] order by date) summary from test；

--当success_cnt 为null值的时候，将返回1，否则将返回success_cnt的真实值。
select coalesce(success_cnt, 1) from tableA；
--当success_cnt不为null，那么无论period是否为null，都将返回success_cnt的真实值（因为success_cnt是第一个参数），
--当success_cnt为null，而period不为null的时候，返回period的真实值。
--只有当success_cnt和period均为null的时候，将返回1。
select coalesce(success_cnt,period,1) from tableA；

---top
select TOP 50 PERCENT * from Websites;
--前5行
select top 5 * from table
--后5行
select top 5 * from table order by id desc  --desc 表示降序排列 asc 表示升序
---limit
select * from Websites LIMIT 2;
select * from Websites LIMIT 2,5;

--pivot
select *
from student
PIVOT (SUM(score) FOR subject IN (语文, 数学, 英语)
)；
--case when
select name,
  MAX( CASE WHEN subject='语文' THEN score  ELSE 0 END) AS "语文",
  MAX( CASE WHEN subject='数学' THEN score ELSE 0 END) AS "数学",
  MAX( CASE WHEN subject='英语' THEN score ELSE 0 END) AS "英语"
from student
GROUP BY name;

--unpivot
select *
from student1
UNPIVOT (score FOR subject IN ("语文","数学","英语")
);
--case when
select
    NAME,
    '语文' AS subject ,
    MAX("语文") AS score
from student1 GROUP BY NAME
union
select
    NAME,
    '数学' AS subject ,
    MAX("数学") AS score
from student1 GROUP BY NAME
union
select
    NAME,
    '英语' AS subject ,
    MAX("英语") AS score
from student1 GROUP BY NAME

--下列 SQL 语句返回的结果是一样的:
-- 聚合后使用 HAVING 子句过滤
select sale_date, SUM(quantity)
from SalesHistory 
GROUP BY sale_date
HAVING sale_date = '2007-10-01';
-- 聚合前使用 where 子句过滤
select sale_date, SUM(quantity)
from SalesHistory
where sale_date = '2007-10-01' 
GROUP BY sale_date;
--使用第二条语句效率更高，原因主要有两点
--1、使用 GROUP BY 子句进行聚合时会进行排序，如果事先通过 where 子句能筛选出一部分行，能减轻排序的负担；
--2、在 where 子句中可以使用索引，而 HAVING 子句是针对聚合后生成的视频进行筛选的，但很多时候聚合后生成的视图并没有保留原表的索引结构；

-- 慢
select * 
from Class_A
where id IN (select id 
                    from  CLASS_B);
-- 快
select *
 from Class_A A 
 where EXISTS (select * 
                         from Class_B  B
                         where A.id = B.id);
--为啥使用 EXISTS 的 SQL 运行更快呢，有两个原因：
--1、可以`用到索引，如果连接列 (id) 上建立了索引，那么查询 Class_B 时不用查实际的表，只需查索引就可以了。
--2、如果使用 EXISTS，那么只要查到一行数据满足条件就会终止查询， 不用像使用 IN 时一样扫描全表。在这一点上 NOT EXISTS 也一样。
--另外如果 IN 后面如果跟着的是子查询，由于 SQL 会先执行 IN 后面的子查询，会将子查询的结果保存在一张临时的工作表里（内联视图），
--然后扫描整个视图，显然扫描整个视图这个工作很多时候是非常耗时的，而用 EXISTS 不会生成临时表。

<>
!=
NOT IN

--否定形式语句会导致对全表扫描
--错误写法
select *
from student
where price <>100;
--正确写法
select *
from student
where price < 100 or price > 100;

--复杂写法：以下写法会产生临时表 a；
select *
from( select sale_date
                  ,max(price) as max_price
         from sale_table
         group by sale_date) as a
where a.max_price >= 10;
--优化写法
select sale_date
         ,max(price) as max_prcie
from sale_table
group by sale_date
having max(price) >= 10;

--【此处有点高级，作者本人也没有彻底吸收】
select id
            ,state
            ,city 
from Addresses1 as A1
where state IN (select state
                         from Addresses2 as A2
                         where A1.id = A2.id) 
            AND city IN (select city
                              from Addresses2 as A2 
                              where A1.id = A2.id);
--优化写法：以上写法产生了两张临时表
select *
from Addresses1 as A1
where id || state || city
           IN (select id || state|| city
                from Addresses2 A2);

select a.name
from student as a
group by a.name
having count(a.name) > 1;

select ifnull(
     (select distinct 成绩  
     from 成绩表
     where 课程='语文'
     order by 课程,成绩 desc
     limit 1,1),null
     ) as '第二高的学生成绩';

case
       when mod(座位号, 2) != 0  then 座位号 + 1
       when mod(座位号, 2)  = 0  then 座位号 - 1
end  as  '交换后座位号'

select
    (case
      # 当座位号是奇数并且不是不是最后一个座位号时        when mod(id, 2) != 0 and counts!= id then id + 1
       # 当座位号是奇数并且是最后一个座位号时，座位号不变        when mod(id, 2) != 0 and counts = id then id
       # 当座位号是偶数时        else id - 1
    end) as id2,studentfrom seat,(select count(*) as counts from seat);

select a.姓名 as 不近视的学生名单
from 学生表 as a
     left join 近视学生表 as b on a.学号=b.学生学号
where b.序号 is null;

select 用户类型,avg(访问量)
from 
      (select * 
      from 
            (select *,
                     row_number() over(order by 访问量 desc) as 排名
            from 用户访问次数表) as a
      where 排名 > (select max(排名) from a) * 0.2) as b
group by 用户类型;

select 
	  # 第四步
	  # 1、计算根据用户id和新的日期，进行计算，如果计数结果大于等于N，则N天连续登陆
		d.user_id
	   ,d.user_name
	   ,d.cal_date
from(
	# 第三步
	# 1、日期 减去 排序结果【逻辑：日期减去天数得到新的日期，对新的日期计数，如果计数结果大于等于N，则N天连续登陆】
	select c.user_id
		   ,c.user_name
		   ,date_sub(c.log_data, c.rank) as cal_date
	from(
		# 第二步
		# 1、对每个用户根据日期进行排序
		select b.user_id
			   ,b.user_name
			   ,b.log_data
			   ,row_number() over (PARTITION by b.user_id order by b.log_data) as rank
		from(
			# 第一步
			# 1、提取时间段在8-22点之间的数据；
			# 2、日期从 2020-10-25 开始；
			# 3、数据去重，根据用户对日期去重
			select DISTINCT user_id, user_name, to_date(log_time) as log_data
			from B as a
			where extract(HOUR from a.log_time) between 8 and 22
				  and to_date(a.log_time) > '2020-10-24'
			) as b
		) as c 
	) as d 
group by d.user_id, d.cal_date
having count(*) >= 7;

#第一步：计算每个时间点A、B两支队伍的得分情况，如果没有得分，就显示为0；【考点：coalesce函数】
#第二步：计算每个时间点A、B两队分别累计得分；【考点：sum() over (partition ```order by ```)】
#第三步：计算每个时刻两队的分差；【考点：lead函数】
#第四步：当前时刻的 累计分差 与 下一个时刻的 累积分差 相乘，相等结果小于等于0时，则为比分反超时刻；同时通过两队的累积比分相同时刻；【考点：筛选思路】
select z.score_time
	 ,z.name
from(
	#第三步：计算每个时刻两队的分差；
	select *
		 ,a_sum_score2-b_sum_score2 as score_gap
		 ,lead(a_sum_score2-b_sum_score2)over(order by score_time) as last_score_gap
	from(
		#第二步：计算每个时间点A、B两队分别累计得分
		select team
			 ,number
			 ,name
			 ,score_time
			 ,A_score
			 ,B_score
			 ,sum(A_score) over (order by score_time) a_sum_score2 --计算每个时点A队的累计得分
			 ,sum(b_score) over (order by score_time) b_sum_score2 --计算每个时点B队的累计得分
		from(
			#第一步：计算每个时间点A、B两支队伍的得分情况，如果没有得分，就显示为0；
			select team
				    ,number
				    ,name
				    ,score_time
				    ,coalesce(case when team='A' then score end,0) as A_score --如果某个得分时点B队得分了，A队没有得分，那么A对在这个时点的得分置为0
				    ,coalesce(case when team='B' then score end,0) as B_score --如果某个得分时点A队得分了，B队没有得分，那么B对在这个时点的得分置为0
			from test.basketball_game_score_detail
			ORDER BY score_time
			) as x
		) as y
	) as z
where z.score_gap*last_score_gap<=0
	 and a_sum_score2<>b_sum_score2； --排除得分相等的时点，这些时点肯定不考虑

【以下方案个人觉得复杂了，期望有梗简单解析思路输出的朋友给予建议】
#第一步：根据时间进行所有人排序
#第二步：
#（1）对每个球员根据时间进行排序
#（2）所有人排序结果 减去 每个球员根据时间排序结果 得到两者排序差，如果排序差结果相同，则说明是连续的，通过having count() >= N，计算得到连续N次得分的球员
#第三步：通过内连接筛选相关信息
select b.name
	 ,b.score
from(
	#第三步：通过内连接筛选相关信息
	select *
		 ,(rank - row_number() over(partition by a.name order by a.score_time)) as rank_diff
	from(
		#第一步：根据时间进行所有人排序
		select *
			 ,row_number() over(order by score_time) as 'rank'
		from test.basketball_game_score_detail
		) as a
	) as b inner join(
				select b.name
				         ,b.rank_diff
				from(
					#第二步：
					#（1）对每个球员根据时间进行排序
					#（2）所有人排序结果 减去 每个球员根据时间排序结果 得到两者排序差
					select *
						  ,(rank - row_number() over(partition by a.name order by a.score_time)) as rank_diff
					from(
						#第一步：根据时间进行所有人排序
						select *
							 ,row_number() over(order by score_time) as 'rank'
						from test.basketball_game_score_detail
						) as a
					) as b 
				group by b.name, b.rank_diff
				having count(b.rank_diff) >= 3
	                       ) as c on b.name = c.name and b.rank_diff = c.rank_diff;