2019-09-09 SQL功能語句收集+laravel 分組統(tǒng)計

1蓖捶、創(chuàng)建數(shù)據(jù)庫

CREATE DATABASE database-name

2地回、刪除數(shù)據(jù)庫

drop database dbname

3、說明：創(chuàng)建新表

create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)**

3.1根據(jù)已有的表創(chuàng)建新表：

A：create table tab_new like tab_old (使用舊表創(chuàng)建新表)
B：create table tab_new as select col1,col2… from tab_old definition only

4俊鱼、增加一個列

Alter table tabname add column col type

5刻像、添加主鍵：

Alter table tabname add primary key(col)

5.1、刪除主鍵：

 Alter table tabname drop primary key(col)

6亭引、創(chuàng)建索引

create [unique] index idxname on tabname(col….)

6.1绎速、刪除索引：

drop index idxname

注：索引是不可更改的，想更改必須刪除重新建焙蚓。

7纹冤、創(chuàng)建視圖：

create view viewname as select statement

7.1刪除視圖：

drop view viewname

8洒宝、幾個簡單的基本的sql語句

選擇：select * from table1 where 范圍
插入：insert into table1(field1,field2) values(value1,value2)
刪除：delete from table1 where 范圍
更新：update table1 set field1=value1 where 范圍
查找：select * from table1 where field1 like ’%value1%’ ---like的語法很精妙，查資料!
排序：select * from table1 order by field1,field2 [desc]
總數(shù)：select count as totalcount from table1
求和：select sum(field1) as sumvalue from table1
平均：select avg(field1) as avgvalue from table1
最大：select max(field1) as maxvalue from table1
最忻染：select min(field1) as minvalue from table1

9雁歌、說明：幾個高級查詢運算詞

A： UNION 運算符
UNION 運算符通過組合其他兩個結(jié)果表（例如 TABLE1 和 TABLE2）并消去表中任何重復行而派生出一個結(jié)果表。當 ALL 隨 UNION一起使用時（即 UNION ALL）知残，不消除重復行靠瞎。兩種情況下，派生表的每一行不是來自 TABLE1就是來自 TABLE2 求妹。
B：EXCEPT 運算符
EXCEPT運算符通過包括所有在TABLE1 中但不在 TABLE2中的行并消除所有重復行而派生出一個結(jié)果表乏盐。當 ALL隨 EXCEPT 一起使用時 (EXCEPT ALL)，不消除重復行制恍。
C：INTERSECT 運算符
INTERSECT運算符通過只包括 TABLE1 和 TABLE2中都有的行并消除所有重復行而派生出一個結(jié)果表父能。當ALL隨 INTERSECT 一起使用時 (INTERSECT ALL)，不消除重復行净神。
注：使用運算詞的幾個查詢結(jié)果行必須是一致的何吝。

10、使用外連接

A鹃唯、left （outer） join
左外連接（左連接）：結(jié)果集幾包括連接表的匹配行爱榕，也包括左連接表的所有行。

SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

B：right （outer） join
右外連接(右連接)：結(jié)果集既包括連接表的匹配連接行坡慌，也包括右連接表的所有行黔酥。
C：full/cross （outer） join
全外連接：不僅包括符號連接表的匹配行，還包括兩個連接表中的所有記錄八匠。

11絮爷、改數(shù)據(jù)庫的名稱:

sp_renamedb 'old_name', 'new_name'

批量替換sql
update 表名 set 指定字段 =replace(指定字段,'要替換的字符串','想要的字符串') where條件;
例子：
update `t_user` set url =replace(url,'192.168.','222.222.') ；

12梨树、CONCAT函數(shù)：用于將兩個字符串連接起來坑夯，形成一個單一的字符串。

舉例：查詢當前系統(tǒng)時間
正常查詢：select current_date ;
添加前綴：select CONCAT("系統(tǒng)時間：",current_date);
添加后綴：select CONCAT(current_date,",aaa");
代碼

select concat ("系統(tǒng)時間：",current_date) as info from data_db;

12抡四、去重統(tǒng)計

select count(distinct 去重字段) from 表

查找表中多余的重復記錄柜蜈，重復記錄是根據(jù)單個字段（order_key ）來判斷

select * from eb_credit
where order_key in (select order_key from eb_credit group by order_key having count(order_key) > 1)

刪除表中多余的重復記錄，重復記錄是根據(jù)單個字段（peopleId）來判斷指巡，只留有rowid最小的記錄

delete from people 
where peopleId  in (select peopleId  from people group by peopleId having  count(peopleId) > 1)
and rowid not in (select min(rowid) from   people group by peopleId  having count(peopleId )>1)

查找表中多余的重復記錄（多個字段）

select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)

刪除表中多余的重復記錄（多個字段）凯力，只留有rowid最小的記錄

delete from vitae a
where (a.peopleId,a.seq) in   (select peopleId,seq from vitae group by peopleId,seqhaving count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq havingcount(*)>1)

查找表中多余的重復記錄（多個字段）棚品，不包含rowid最小的記錄

select * from vitae a
where (a.peopleId,a.seq) in   (select peopleId,seq from vitae group by peopleId,seqhaving count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq havingcount(*)>1)

結(jié)果:

image

laravel 分組統(tǒng)計

EventInfo::select('ajly', DB::raw('count(*) as num'))->where('wg_id', $wg_id)
            ->groupBy('ajly')
            ->get()->toArray();

SQL語句統(tǒng)計每天、每月、每年的數(shù)據(jù)

1徽职、每年
select year(ordertime) 年良瞧，
sum(Total) 銷售合計
from 訂單表
group by year(ordertime)
  
2、每月
select year(ordertime) 年，
month(ordertime) 月,
sum(Total) 銷售合計
from 訂單表
group by year(ordertime)蹋偏，
month(ordertime
  
3、每日
select year(ordertime) 年至壤，
month(ordertime) 月,
day(ordertime) 日威始，
sum(Total) 銷售合計
from 訂單表
group by year(ordertime)，
month(ordertime),
day(ordertime)
  
另外每日也可以這樣:
select convert(char(8),ordertime,112) dt像街，
sum(Total) 銷售合計
from 訂單表
group by convert(char(8),ordertime,112)

找出當前是哪個月用 dataprat

創(chuàng)建一個FOR循環(huán)黎棠，一個個來掃描，掃描時用數(shù)據(jù)列占用日期的部份COUNT(*)就可以了

select @i, count(*) where @ i=datepart(mm,dd)

SQL 一年內(nèi)每月數(shù)據(jù)統(tǒng)計

SELECT YEAR(insert_time) AS year,
MONTH(insert_time) AS month,
count(*) as count
FROM tb1
WHERE YEAR(insert_time)=2019
GROUP BY YEAR(insert_time), MONTH(insert_time);

 //按天統(tǒng)計
 select count(dataid) as every_day_ num,sum()from tab1 where group by trunc(createtime, 'DD'))

//按自然周統(tǒng)計
select to_char(date, 'iw " ) ,sum()from tab1 where group by to_char(date, "iw ')

//按自然月統(tǒng)計
select to_char(date, 'mm " ) ,sum( )from tab1 where group by to_char(date , ' mm ' )

// 按季統(tǒng)計
select to_char(date, 'q' ) ,sum()from tab1 where group by to_char(date, "q ')

//按年統(tǒng)計
select to_char(date, ' yyyy " ) , sum()from tab1 where group by to_char(date , ' yyyy ')

數(shù)據(jù)庫查詢某年數(shù)據(jù)（sql server）

select *from 表名 where YEAR(存時間的字段名) =某年
select *from News where YEAR(create_time) =2017
//查詢某月
MONTH( create_time )= 月份
MONTH( create_time )= 8

DAY()示例1：

使用DAY()功能并從指定的日期獲取月份中的日期镰绎。

SELECT DAY('2020/01/02');
輸出：2

示例-2：

將DAY()函數(shù)與變量一起使用脓斩，并從指定的日期獲取月份中的某天。

DECLARE @date VARCHAR(50);
SET @date = '2020/01/05';
SELECT DAY(@date);
輸出：5

示例3：

使用帶有日期作為參數(shù)的DAY()函數(shù)畴栖，其中還包括時間俭厚。

SELECT DAY('2018/11/22 07:44');
輸出：22

示例4：

使用帶有變量和日期作為參數(shù)的DAY()函數(shù)，該參數(shù)還包括時間驶臊。

DECLARE @date VARCHAR(50);
SET @date = '2020/11/30 23:59';
SELECT DAY(@date);
輸出：30

DATEPART()

DATEPART ( datepart , date )

//以整數(shù)值的形式返回日期的指定部分。此部分由datepart 來指定叼丑。

DATEPART (dd关翎， date) 等同于DAY (date)

DATEPART (mm， date) 等同于MONTH (date)

DATEPART (yy鸠信， date) 等同于YEAR (date)

日期部分縮寫

年份            yy纵寝、yyyy 

季度            qq、q 

月份            mm星立、m 

每年的某一日    dy爽茴、y 

日期            dd、d 

星期            wk绰垂、ww 

工作日*         dw

小時            hh 

分鐘            mi室奏、n 

秒              ss、s 

毫秒            ms

SQL查詢當天劲装、本周胧沫、本月記錄詳解

--查詢當天：

select * from info where DateDiff(dd,datetime,getdate())=0

--查詢24小時內(nèi)的:

select * from info where DateDiff(hh,datetime,getDate())<=24

--info為表名,datetime為數(shù)據(jù)庫中的字段值

--查詢當天：

select * from table where DateDiff(dd,datetime,getdate())=0 --查詢24小時內(nèi)的: select * from table where DateDiff(hh,datetime,getDate())<=24  

--table 為表名,datetime為數(shù)據(jù)庫中的字段值

DATEDIFF 函數(shù):
語法:

select * from table where DateDiff(dd,datetime,getdate())=0 --查詢24小時內(nèi)的: select * from table where DateDiff(hh,datetime,getDate())<=24

備注:
enddate 減去 startdate。如果 startdate 晚于 enddate占业，則返回負值绒怨。
如果結(jié)果超出整數(shù)值范圍，則 DATEDIFF 將產(chǎn)生錯誤谦疾。對于毫秒南蹂，最大數(shù)是 24 天 20 小時 31 分鐘零 23.647 秒。對于秒念恍，最大數(shù)是 68 年六剥。
跨分鐘晚顷、秒和毫秒等邊界計算的方法使得 DATEDIFF 指定的結(jié)果在所有數(shù)據(jù)類型中均一致。結(jié)果是帶正負號的整數(shù)值仗考，它等于跨第一個和第二個日期間的 datepart 邊界數(shù)音同。例如，在 1 月 4 日（星期日）和 1 月 11 日（星期日）之間的星期數(shù)是 1秃嗜。

--本月記錄

SELECT * FROM 表 WHERE datediff(month,[dateadd],getdate())=0

--本周記錄

SELECT * FROM 表 WHERE datediff(week,[dateadd],getdate())=0

--包括本年這些查詢方式是一樣的

--本月記錄

SELECT * FROM 表 WHERE datediff(month,[dateadd],getdate())=0

--本周記錄

SELECT * FROM 表 WHERE datediff(week,[dateadd],getdate())=0

--包括本年這些查詢方式是一樣的

sql server中的時間函數(shù)

當前系統(tǒng)日期权均、時間

select getdate()

dateadd 在向指定日期加上一段時間的基礎(chǔ)上，返回新的 datetime 值
例如：向日期加上2天

select dateadd(day,2,'2004-10-15')   --返回：2004-10-17 00:00:00.000

datediff 返回跨兩個指定日期的日期和時間邊界數(shù)锅锨。

select datediff(day,'2004-09-01','2004-09-18')    --返回：17

datepart 返回代表指定日期的指定日期部分的整數(shù)叽赊。

SELECT DATEPART(month, '2004-10-15')   --返回 10

datename 返回代表指定日期的指定日期部分的字符串

SELECT datename(weekday, '2004-10-15')   --返回：星期五

day(), month(),year() --可以與datepart對照一下

select 當前日期=convert(varchar(10),getdate(),120),當前時間=convert(varchar(8),getdate(),114)  
select datename(dw,'2004-10-15')  
select 本年第多少周=datename(week,'2004-10-15')  
//今天是周幾=datename(weekday,'2004-10-15')

舉例：

1.GetDate() 用于sql server :select GetDate()
2.DateDiff('s','2005-07-20','2005-7-25 22:56:32')返回值為 514592 秒
DateDiff('d','2005-07-20','2005-7-25 22:56:32')返回值為 5 天
3.DatePart('w','2005-7-25 22:56:32')返回值為 2 即星期一(周日為1，周六為7)
DatePart('d','2005-7-25 22:56:32')返回值為 25即25號
DatePart('y','2005-7-25 22:56:32')返回值為 206即這一年中第206天
DatePart('yyyy','2005-7-25 22:56:32')返回值為 2005即2005年

判斷表存在不存在：

select   count(*)   from   sysobjects   where   type='U'   and   name='你的表名'

判斷字段存在不存在：

select   count(*)   from   syscolumns  
where   id       =   (select   id   from   sysobjects   where   type='U'   and   name='你的表名')      
and       name   =   '你要判斷的字段名'

SQL當前日期獲取技巧

一個月第一天的SQL 腳本：

SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

本周的星期一

SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)

一年的第一天

SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)

季度的第一天

SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)

當天的半夜

SELECT DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)

上個月的最后一天

SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))

SQL將一個表中的某些字段更新到另一個表中(多表聯(lián)合更新)

自定義的 sql

update zhxx,rkxx
set zhxx.rk_id=rkxx.id
where zhxx.zjbh=rkxx.zjbh

user表

userId	username	password	sex	addr	phone
1	張珊	123	男	北京市	1562356586
2	李思	456	女	北京市	1562354256
3	王武	789	男	北京市	1562345544
4	王柳	113	男	北京市	1562445778

score表

scoreId	userId	score	course
1	1	80	語文
2	1	85	數(shù)學
3	3	52	語文
4	2	67	數(shù)學

現(xiàn)在score表中的username和phone字段是空的必搞，需要從user表中必指，查找出相應的值插入score表中，SQL語句如下：

update score,user
set score.username=user.username,score.phone=user.phone
where score.userId=user.userId

或者

update score join user on score.userId=user.userId
set score.username=user.username,score.phone=user.phone

sql 獲取從右到左截取恕洲。

rk_id=RIGHT(b.id,1)

感謝提供參考：
https://www.cnblogs.com/yubinfeng/archive/2010/11/02/1867386.html
https://www.cnblogs.com/ray-mr-huang/p/10389246.html

最后編輯于：2022.03.03 18:51:10

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者

人面猴
序言：七十年代末塔橡，一起剝皮案震驚了整個濱河市，隨后出現(xiàn)的幾起案子霜第，更是在濱河造成了極大的恐慌葛家，老刑警劉巖，帶你破解...
沈念sama閱讀 216,919評論 6贊 502
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件弹砚，死亡現(xiàn)場離奇詭異，居然都是意外死亡枢希，警方通過查閱死者的電腦和手機，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 92,567評論 3贊 392
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進店門苞轿，熙熙樓的掌柜王于貴愁眉苦臉地迎上來读存，“玉大人，你說我怎么就攤上這事秀睛∪裰模” “怎么了？”我有些...
開封第一講書人閱讀 163,316評論 0贊 353
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵，是天一觀的道長皮钠。經(jīng)常有香客問我，道長，這世上最難降的妖魔是什么？我笑而不...
開封第一講書人閱讀 58,294評論 1贊 292
?港島之戀（遺憾婚禮）
正文為了忘掉前任茂蚓，我火速辦了婚禮壕鹉，結(jié)果婚禮上，老公的妹妹穿的比我還像新娘聋涨。我一直安慰自己晾浴，他們只是感情好，可當我...
茶點故事閱讀 67,318評論 6贊 390
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布牍白。她就那樣靜靜地躺著脊凰，像睡著了一般。火紅的嫁衣襯著肌膚如雪茂腥。梳的紋絲不亂的頭發(fā)上狸涌，一...
開封第一講書人閱讀 51,245評論 1贊 299
城市分裂傳說
那天切省，我揣著相機與錄音，去河邊找鬼帕胆。笑死朝捆，一個胖子當著我的面吹牛，可吹牛的內(nèi)容都是我干的懒豹。我是一名探鬼主播芙盘，決...
沈念sama閱讀 40,120評論 3贊 418
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼，長吁一口氣：“原來是場噩夢啊……” “哼歼捐！你這毒婦竟也來了何陆？” 一聲冷哼從身側(cè)響起，我...
開封第一講書人閱讀 38,964評論 0贊 275
萬榮殺人案實錄
序言：老撾萬榮一對情侶失蹤豹储，失蹤者是張志新（化名）和其女友劉穎贷盲，沒想到半個月后，有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體剥扣，經(jīng)...
沈念sama閱讀 45,376評論 1贊 313
?護林員之死
正文獨居荒郊野嶺守林人離奇死亡巩剖，尸身上長有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點故事閱讀 37,592評論 2贊 333
?白月光啟示錄
正文我和宋清朗相戀三年，在試婚紗的時候發(fā)現(xiàn)自己被綠了钠怯。大學時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片佳魔。...
茶點故事閱讀 39,764評論 1贊 348
活死人
序言：一個原本活蹦亂跳的男人離奇死亡，死狀恐怖晦炊，靈堂內(nèi)的尸體忽然破棺而出鞠鲜，到底是詐尸還是另有隱情，我是刑警寧澤断国，帶...
沈念sama閱讀 35,460評論 5贊 344
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布贤姆，位于F島的核電站，受9級特大地震影響稳衬，放射性物質(zhì)發(fā)生泄漏霞捡。R本人自食惡果不足惜，卻給世界環(huán)境...
茶點故事閱讀 41,070評論 3贊 327
男人毒藥：我在死后第九天來索命
文/蒙蒙一薄疚、第九天我趴在偏房一處隱蔽的房頂上張望碧信。院中可真熱鬧，春花似錦街夭、人聲如沸砰碴。這莊子的主人今日做“春日...
開封第一講書人閱讀 31,697評論 0贊 22
一樁弒父案板丽，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽衣式。三九已至，卻和暖如春，著一層夾襖步出監(jiān)牢的瞬間碴卧，已是汗流浹背弱卡。一陣腳步聲響...
開封第一講書人閱讀 32,846評論 1贊 269
情欲美人皮
我被黑心中介騙來泰國打工，沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留住册，地道東北人婶博。一個月前我還...
沈念sama閱讀 47,819評論 2贊 370
代替公主和親
正文我出身青樓，卻偏偏與公主長得像荧飞，于是被迫代替她去往敵國和親凡人。傳聞我的和親對象是個殘疾皇子，可洞房花燭夜當晚...
茶點故事閱讀 44,665評論 2贊 354