SQL 常用函數(shù) 整理

--創(chuàng)建數(shù)據(jù)表
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的值是由兩個(gè)列（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='菜鳥(niǎo)教程';

delete from Websites
where name='Facebook' AND country='USA';
--刪除所有數(shù)據(jù)
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, ## 分組取每個(gè)組的最小值對(duì)應(yīng)的人    last_value(name) over (PARTITION BY department ORDER BY cost) as max_cost_user ## 分組取每個(gè)組的最大值對(duì)應(yīng)的人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';
--注：當(dāng)組內(nèi)出現(xiàn)重復(fù)時(shí)，累積計(jì)算會(huì)有所問(wèn)題，待核驗(yàn)；

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

--當(dāng)你存儲(chǔ)的是日期，希望轉(zhuǎn)化為UNIX時(shí)間戳?xí)r，使用unix_timestamp函數(shù)，命令格式：unix_timestamp(string date, string pattern) ，表示轉(zhuǎn)換pattern格式的日期到時(shí)間戳；--當(dāng)你存儲(chǔ)的是時(shí)間戳，希望轉(zhuǎn)化為日期，使用from_unixtime函數(shù)，命令格式：from_unixtime(bigint unixtime, [string format])；## 日期轉(zhuǎn)化為時(shí)間戳 ##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## 時(shí)間戳轉(zhuǎn)化為日期 ## 
select from_unixtime (1584782175) 得到 2020-03-21 17:16:15select from_unixtime (1584782175,'yyyyMMdd') 得到 20200321select from_unixtime (1584782175,'yyyy-MM-dd')得到 2020-03-21## 日期和日期之間，也可以通過(guò)時(shí)間戳來(lái)進(jìn)行轉(zhuǎn)換 ##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--注：注意轉(zhuǎn)換的時(shí)間格式要求；

--date_sub(string startdate, int days)
## 使用date_sub (string startdate, int days)得到開(kāi)始日期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)得到開(kāi)始日期startdate增加days天后的日期 ##
select date_add('2012-12-08', 10) 得到 2012-12-18
--datediff(string enddate, string startdate)
## 使用datediff(string enddate, string startdate)得到 結(jié)束日期減去開(kāi)始日期的天數(shù) ## 
select datediff('2012-12-08','2012-05-09') 得到 213

--add_months(d,n); 
--在某一個(gè)日期d上,加上指定的月數(shù)n,返回計(jì)算后的新日期。d表示日期,n表示要加的月數(shù)（n可以為負(fù)值）
select add_months(sys date,1) from student;

--timestampdiff( interval, datetime_1, datetime_2)
interval:
--毫秒：frac_second
--秒   ：second
--分鐘：minuter
--小時(shí)：hour
--天   ：day
--星期：week
--月   ：month
--季度：quarter
--年   ：year
--相差1天
select TIMESTAMPDIFF(DAY, '2018-03-20 23:59:00', '2015-03-22 00:00:00');
--相差49小時(shí)
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函數(shù)，也可以使用字符串提取函數(shù)。
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()     --獲取日期幾號(hào)
now()    --獲取當(dāng)前日期

--返回指定日期當(dāng)前月的最后一天；
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 參數(shù)是合法的日期表達(dá)式。unit 參數(shù)可以是下列的值：
--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：表示年(兩位數(shù))，例如: 17 年。
%Y：表示4位數(shù)中的年，例如: 2017年
%m：表示月(1-12)
%d: 表示月中的天
%H: 小時(shí)(0-23)
%i: 分鐘 (0-59)
%s: 秒 (0-59)
--常用格式：'%Y-%m-%d %H:%i:%s'

（1）獲取當(dāng)前日期select curdate();（2）獲取本月最后一天select last_day(curdate());（3）獲取本月第一天select date_add(curdate(), interval - day(curdate()) + 1 day);（4）獲取下個(gè)月的第一天select date_add(curdate() - day(curdate()) + 1, interval 1 month);（5）獲取當(dāng)前月的天數(shù)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位置到結(jié)尾的字符串##
select substring('abcde', 3) 得到 cde 
--使用substring(string A, int start, int len)返回字符串A從start位置開(kāi)始，長(zhǎng)度為len的字符串
select substring('abcde', 3,2) 得到 cd

--使用concat(string A, string B) 返回字符串AB的拼接結(jié)果,可以多個(gè)字符串進(jìn)行拼接
select concat('abc', 'def','gh') 得到abcdefgh
--使用concat_ws(string X, stringA, string B) 返回字符串A和B由X拼接的結(jié)果
select concat_ws(',', 'abc', 'def', 'gh') 得到 abc,def,gh

--使用length(string A)返回字符串A的長(zhǎng)度
select length('abcedfg') 得到 7 
--使用trim(string A) 去除字符串兩邊的空格
select trim(' abc ') 得到 'abc'
--使用lower(string A)/ lcase(string A)返回字符串的小寫形式，常用于不確定原始字段是否統(tǒng)一為大小寫
select lower('abSEd') 得到 absed
--使用upper(string A)/ ucase(string A)返回字符串的大寫形式，常用于不確定原始字段是否統(tǒng)一為大小寫
select upper('abSEd') 得到 ABSED

--bigint轉(zhuǎn)換為字符串
select cast(A as string) as A；

--regexp_extract(string subject, string pattern, int index)
--將字符串subject按照pattern正則表達(dá)式的規(guī)則拆分，返回index指定的字符
select regexp_extract('foothebar', 'foo(.*?)(bar)', 1) 得到 the
--regexp_replace(string A, string B, string C)
--將字符串A中的符合java正則表達(dá)式B的部分替換為C
select regexp_replace('foobar', 'oo|ar', '') 得到 fb

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

⚠️ 使用時(shí)注意sql類型，選擇合適方案實(shí)現(xiàn)目標(biāo)結(jié)果！-- 方案一：支持mysqlset @sum := 0;select p1.ship_day as '日期'
	,p1.order_cnt as '訂單數(shù)'
	,(@sum := @sum + p1.order_cnt) as  '累計(jì)訂單數(shù)'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;-- 方案四：（窗口函數(shù)）【不支持mysql】select date
   ,sum(num) over(partition by [group] order by date) summary from test；

--當(dāng)success_cnt 為null值的時(shí)候，將返回1，否則將返回success_cnt的真實(shí)值。
select coalesce(success_cnt, 1) from tableA；
--當(dāng)success_cnt不為null，那么無(wú)論period是否為null，都將返回success_cnt的真實(shí)值（因?yàn)閟uccess_cnt是第一個(gè)參數(shù)），
--當(dāng)success_cnt為null，而period不為null的時(shí)候，返回period的真實(shí)值。
--只有當(dāng)success_cnt和period均為null的時(shí)候，將返回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 (語(yǔ)文, 數(shù)學(xué), 英語(yǔ))
)；
--case when
select name,
  MAX( CASE WHEN subject='語(yǔ)文' THEN score  ELSE 0 END) AS "語(yǔ)文",
  MAX( CASE WHEN subject='數(shù)學(xué)' THEN score ELSE 0 END) AS "數(shù)學(xué)",
  MAX( CASE WHEN subject='英語(yǔ)' THEN score ELSE 0 END) AS "英語(yǔ)"
from student
GROUP BY name;

--unpivot
select *
from student1
UNPIVOT (score FOR subject IN ("語(yǔ)文","數(shù)學(xué)","英語(yǔ)")
);
--case when
select
    NAME,
    '語(yǔ)文' AS subject ,
    MAX("語(yǔ)文") AS score
from student1 GROUP BY NAME
union
select
    NAME,
    '數(shù)學(xué)' AS subject ,
    MAX("數(shù)學(xué)") AS score
from student1 GROUP BY NAME
union
select
    NAME,
    '英語(yǔ)' AS subject ,
    MAX("英語(yǔ)") AS score
from student1 GROUP BY NAME

--下列 SQL 語(yǔ)句返回的結(jié)果是一樣的:
-- 聚合后使用 HAVING 子句過(guò)濾
select sale_date, SUM(quantity)
from SalesHistory 
GROUP BY sale_date
HAVING sale_date = '2007-10-01';
-- 聚合前使用 where 子句過(guò)濾
select sale_date, SUM(quantity)
from SalesHistory
where sale_date = '2007-10-01' 
GROUP BY sale_date;
--使用第二條語(yǔ)句效率更高，原因主要有兩點(diǎn)
--1、使用 GROUP BY 子句進(jìn)行聚合時(shí)會(huì)進(jìn)行排序，如果事先通過(guò) where 子句能篩選出一部分行，能減輕排序的負(fù)擔(dān)；
--2、在 where 子句中可以使用索引，而 HAVING 子句是針對(duì)聚合后生成的視頻進(jìn)行篩選的，但很多時(shí)候聚合后生成的視圖并沒(méi)有保留原表的索引結(jié)構(gòu)；

-- 慢
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 運(yùn)行更快呢，有兩個(gè)原因：
--1、可以`用到索引，如果連接列 (id) 上建立了索引，那么查詢 Class_B 時(shí)不用查實(shí)際的表，只需查索引就可以了。
--2、如果使用 EXISTS，那么只要查到一行數(shù)據(jù)滿足條件就會(huì)終止查詢， 不用像使用 IN 時(shí)一樣掃描全表。在這一點(diǎn)上 NOT EXISTS 也一樣。
--另外如果 IN 后面如果跟著的是子查詢，由于 SQL 會(huì)先執(zhí)行 IN 后面的子查詢，會(huì)將子查詢的結(jié)果保存在一張臨時(shí)的工作表里（內(nèi)聯(lián)視圖），
--然后掃描整個(gè)視圖，顯然掃描整個(gè)視圖這個(gè)工作很多時(shí)候是非常耗時(shí)的，而用 EXISTS 不會(huì)生成臨時(shí)表。

<>
!=
NOT IN

--否定形式語(yǔ)句會(huì)導(dǎo)致對(duì)全表掃描
--錯(cuò)誤寫法
select *
from student
where price <>100;
--正確寫法
select *
from student
where price < 100 or price > 100;

--復(fù)雜寫法：以下寫法會(huì)產(chǎn)生臨時(shí)表 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;
--優(yōu)化寫法
select sale_date
         ,max(price) as max_prcie
from sale_table
group by sale_date
having max(price) >= 10;

--【此處有點(diǎn)高級(jí)，作者本人也沒(méi)有徹底吸收】
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);
--優(yōu)化寫法：以上寫法產(chǎn)生了兩張臨時(shí)表
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 成績(jī)  
     from 成績(jī)表
     where 課程='語(yǔ)文'
     order by 課程,成績(jī) desc
     limit 1,1),null
     ) as '第二高的學(xué)生成績(jī)';

case
       when mod(座位號(hào), 2) != 0  then 座位號(hào) + 1
       when mod(座位號(hào), 2)  = 0  then 座位號(hào) - 1
end  as  '交換后座位號(hào)'

select
    (case
      # 當(dāng)座位號(hào)是奇數(shù)并且不是不是最后一個(gè)座位號(hào)時(shí)        when mod(id, 2) != 0 and counts!= id then id + 1
       # 當(dāng)座位號(hào)是奇數(shù)并且是最后一個(gè)座位號(hào)時(shí)，座位號(hào)不變        when mod(id, 2) != 0 and counts = id then id
       # 當(dāng)座位號(hào)是偶數(shù)時(shí)        else id - 1
    end) as id2,studentfrom seat,(select count(*) as counts from seat);

select a.姓名 as 不近視的學(xué)生名單
from 學(xué)生表 as a
     left join 近視學(xué)生表 as b on a.學(xué)號(hào)=b.學(xué)生學(xué)號(hào)
where b.序號(hào) is null;

select 用戶類型,avg(訪問(wèn)量)
from 
      (select * 
      from 
            (select *,
                     row_number() over(order by 訪問(wèn)量 desc) as 排名
            from 用戶訪問(wèn)次數(shù)表) as a
      where 排名 > (select max(排名) from a) * 0.2) as b
group by 用戶類型;

select 
	  # 第四步
	  # 1、計(jì)算根據(jù)用戶id和新的日期，進(jìn)行計(jì)算，如果計(jì)數(shù)結(jié)果大于等于N，則N天連續(xù)登陸
		d.user_id
	   ,d.user_name
	   ,d.cal_date
from(
	# 第三步
	# 1、日期 減去 排序結(jié)果【邏輯：日期減去天數(shù)得到新的日期，對(duì)新的日期計(jì)數(shù)，如果計(jì)數(shù)結(jié)果大于等于N，則N天連續(xù)登陸】
	select c.user_id
		   ,c.user_name
		   ,date_sub(c.log_data, c.rank) as cal_date
	from(
		# 第二步
		# 1、對(duì)每個(gè)用戶根據(jù)日期進(jìn)行排序
		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、提取時(shí)間段在8-22點(diǎn)之間的數(shù)據(jù)；
			# 2、日期從 2020-10-25 開(kāi)始；
			# 3、數(shù)據(jù)去重，根據(jù)用戶對(duì)日期去重
			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;

#第一步：計(jì)算每個(gè)時(shí)間點(diǎn)A、B兩支隊(duì)伍的得分情況，如果沒(méi)有得分，就顯示為0；【考點(diǎn)：coalesce函數(shù)】
#第二步：計(jì)算每個(gè)時(shí)間點(diǎn)A、B兩隊(duì)分別累計(jì)得分；【考點(diǎn)：sum() over (partition ```order by ```)】
#第三步：計(jì)算每個(gè)時(shí)刻兩隊(duì)的分差；【考點(diǎn)：lead函數(shù)】
#第四步：當(dāng)前時(shí)刻的 累計(jì)分差 與 下一個(gè)時(shí)刻的 累積分差 相乘，相等結(jié)果小于等于0時(shí)，則為比分反超時(shí)刻；同時(shí)通過(guò)兩隊(duì)的累積比分相同時(shí)刻；【考點(diǎn)：篩選思路】
select z.score_time
	 ,z.name
from(
	#第三步：計(jì)算每個(gè)時(shí)刻兩隊(duì)的分差；
	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(
		#第二步：計(jì)算每個(gè)時(shí)間點(diǎn)A、B兩隊(duì)分別累計(jì)得分
		select team
			 ,number
			 ,name
			 ,score_time
			 ,A_score
			 ,B_score
			 ,sum(A_score) over (order by score_time) a_sum_score2 --計(jì)算每個(gè)時(shí)點(diǎn)A隊(duì)的累計(jì)得分
			 ,sum(b_score) over (order by score_time) b_sum_score2 --計(jì)算每個(gè)時(shí)點(diǎn)B隊(duì)的累計(jì)得分
		from(
			#第一步：計(jì)算每個(gè)時(shí)間點(diǎn)A、B兩支隊(duì)伍的得分情況，如果沒(méi)有得分，就顯示為0；
			select team
				    ,number
				    ,name
				    ,score_time
				    ,coalesce(case when team='A' then score end,0) as A_score --如果某個(gè)得分時(shí)點(diǎn)B隊(duì)得分了，A隊(duì)沒(méi)有得分，那么A對(duì)在這個(gè)時(shí)點(diǎn)的得分置為0
				    ,coalesce(case when team='B' then score end,0) as B_score --如果某個(gè)得分時(shí)點(diǎn)A隊(duì)得分了，B隊(duì)沒(méi)有得分，那么B對(duì)在這個(gè)時(shí)點(diǎn)的得分置為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； --排除得分相等的時(shí)點(diǎn)，這些時(shí)點(diǎn)肯定不考慮

【以下方案?jìng)€(gè)人覺(jué)得復(fù)雜了，期望有梗簡(jiǎn)單解析思路輸出的朋友給予建議】
#第一步：根據(jù)時(shí)間進(jìn)行所有人排序
#第二步：
#（1）對(duì)每個(gè)球員根據(jù)時(shí)間進(jìn)行排序
#（2）所有人排序結(jié)果 減去 每個(gè)球員根據(jù)時(shí)間排序結(jié)果 得到兩者排序差，如果排序差結(jié)果相同，則說(shuō)明是連續(xù)的，通過(guò)having count() >= N，計(jì)算得到連續(xù)N次得分的球員
#第三步：通過(guò)內(nèi)連接篩選相關(guān)信息
select b.name
	 ,b.score
from(
	#第三步：通過(guò)內(nèi)連接篩選相關(guān)信息
	select *
		 ,(rank - row_number() over(partition by a.name order by a.score_time)) as rank_diff
	from(
		#第一步：根據(jù)時(shí)間進(jìn)行所有人排序
		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）對(duì)每個(gè)球員根據(jù)時(shí)間進(jìn)行排序
					#（2）所有人排序結(jié)果 減去 每個(gè)球員根據(jù)時(shí)間排序結(jié)果 得到兩者排序差
					select *
						  ,(rank - row_number() over(partition by a.name order by a.score_time)) as rank_diff
					from(
						#第一步：根據(jù)時(shí)間進(jìn)行所有人排序
						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;