sql:查詢語句大全

一乔妈、mysql

查看數(shù)據(jù)庫：SHOW DATABASES;
創(chuàng)建數(shù)據(jù)庫：CREATE DATABASE db_name;
使用數(shù)據(jù)庫：USE db_name;
刪除數(shù)據(jù)庫：DROP DATABASE db_name;
創(chuàng)建表：
CREATE TABLE table_name(
id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,　--id值畅形，無符號、非空欣范、遞增——唯一性，可做主鍵雅潭。
name VARCHAR(60) NOT NULL,
score TINYINT UNSIGNED NOT NULL DEFAULT 0,--設(shè)置默認(rèn)列值
PRIMARY KEY(id)
)ENGINE=InnoDB　　--設(shè)置表的存儲引擎担平，一般常用InnoDB和MyISAM；InnoDB可靠芒率，支持事務(wù)囤耳；MyISAM高效不支持全文檢索

復(fù)制表：CREATE TABLE tb_name2 SELECT * FROM tb_name
創(chuàng)建臨時表：CREATE TEMPORARY TABLE tb_name
查看表的結(jié)構(gòu)：DESCRIBE tb_name
刪除表：DROP TABLE IF EXISTS tb_name;
表重命名：ALTER TABLE name_old RENAME name_new;
更改表結(jié)構(gòu)：ALTER TABLE tb_name ADD[CHANGE,RENAME,DROP]
實(shí)例
ALTER TABLE tb_name ADD COLUMN address varchar(80) NOT NULL
ALTER TABLE tb_name DROP address
ALTER TABLE tb_name CHANGE score score SMALLINT(4) NOT NULL;

插入數(shù)據(jù)：
INSERT INTO tb_name(id,name,score)
VALUES(NULL,'張三',140),(NULL,'張四',178),(NULL,'張五',134);
更新數(shù)據(jù)：UPDATE tb_name SET score=189 WHERE id=2;
刪除數(shù)據(jù)：DELETE FROM tb_name WHERE id=3;

分組查詢：HAVING 語句、group by 偶芍、order by：
SELECT * FROM tb_name GROUP BY score HAVING count(*)>2 order by id

Mysql支持REGEXP的正則表達(dá)式：
SELECT * FROM tb_name WHERE name REGEXP '^[A-D]' //找出以A-D 為開頭的name
字符串連接：SELECT CONCAT(name,'=>',score) FROM tb_name
數(shù)學(xué)函數(shù)：AVG充择、SUM、MAX匪蟀、MIN椎麦、COUNT；
文本處理函數(shù)：TRIM材彪、LOCATE观挎、UPPER、LOWER段化、SUBSTRING
日期：DATE()嘁捷、CURTIME()、DAY()显熏、YEAR()雄嚣、NOW().....
select
current_timestamp --當(dāng)前時間'YYYY-MM-DD HH:MM:SS'格式
,now()
,SUBDATE(now(),interval 60 second) --前一分鐘
,ADDDATE(now(),interval 1 minute) --后一分鐘
,ADDDATE(now(),interval 1 hour) --后一小時
,ADDDATE(now(),interval 1 day) --后一天
,UNIX_TIMESTAMP(now()) --時間戳
,DATE_FORMAT(now(), '%Y-%m-%d %H:%i:%S')
,date_add('2018-02-17',interval -day('2018-02-17')+1 day) --當(dāng)月第一天

視圖：CREATE VIEW name AS SELECT * FROM tb_name WHERE ~~ ORDER BY ~~;
存儲過程：
CREATE PROCEDURE pro(
IN num INT,OUT total INT)
BEGIN
SELECT SUM(score) INTO total FROM tb_name WHERE id=num;
END;
----BEGIN...END...開始和結(jié)束，這里的存儲過程兩個變量喘蟆，一個是IN一個是OUT现诀，這里的OUT也是需要寫上的夷磕，不寫會出錯

調(diào)用存儲過程：create procedure 存儲過程名(參數(shù))
CALL pro(13,@total)

SELECT @total　　　　--這里就可以看到結(jié)果了；

rownum實(shí)現(xiàn)排序顯示行號
SELECT @rownum:=@rownum+1 AS rownum, Orderstate.*
FROM (SELECT @rownum:=0) r, Orderstate ;

二仔沿、mssql

大部分同mysql
查看數(shù)據(jù)庫列表：
select * from sysobjects where type='u' order by name
查詢上月開始結(jié)束等日期：
--當(dāng)月開始結(jié)束時間
select dateadd(d,-datepart(d,getdate())+1,convert(varchar(10),getdate(),120))
select dateadd(s,-1,dateadd(month,1,dateadd(d,-datepart(d,getdate())+1,convert(varchar(10),getdate(),120))))

--上個月開始結(jié)束時間
select dateadd(m,-1,dateadd(d,-datepart(d,getdate())+1,convert(varchar(10),getdate(),120)))
select dateadd(s,-1,dateadd(m,-0,dateadd(d,-datepart(d,getdate())+1,convert(varchar(10),getdate(),120))))
--dateadd(m,-1,dateadd(d,-datepart(d,getdate())+1,convert(varchar(10),getdate(),120))) and dateadd(s,-1,dateadd(d,-datepart(d,getdate())+1,convert(varchar(10),getdate(),120)))

--上周開始結(jié)束時間
select dateadd(d,(datepart(week,getdate())-1-1)7,DATEADD(wk,DATEDIFF(wk,0,DATEADD(yy, DATEDIFF(yy,0,getdate()),0)),0))
select dateadd(s,-1,dateadd(d,(datepart(week,getdate())-1-1)7,DATEADD(wk,DATEDIFF(wk,0,DATEADD(yy, DATEDIFF(yy,0,getdate()),0)),7)))

--查詢上個月日期列表
select
序號=row_Number() over(order by number)
,日期=convert(varchar(10),dateadd(dd,number,convert(varchar(8),dateadd(m,-1,getdate()),120)+'01'),120)
from master..spt_values
where type='P'
and dateadd(dd,number,convert(varchar(8),dateadd(m,-1,getdate()),120)+'01')<=dateadd(s,-1,convert(varchar(8),getdate(),120)+'01')

跨服務(wù)器、數(shù)據(jù)鏈接：
--開啟
--允許配置高級選項(xiàng)
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
--開啟xp_cmdshell服務(wù)
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO

--關(guān)閉
--允許配置高級選項(xiàng)
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
--禁用xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 0
GO
RECONFIGURE
GO

--在使用openrowset/opendatasource前首先要啟用Ad Hoc Distributed Queries服務(wù)
--啟用Ad Hoc Distributed Queries
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

--關(guān)閉Ad Hoc Distributed Queries
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure

use local_datebase
go
select * from openrowset ---------數(shù)據(jù)庫中無來源擴(kuò)充行業(yè)表
('microsoft.Ace.oledb.12.0','excel 12.0;hdr=yes;database=D:\backup\desk\航空展導(dǎo)入來源.xls',Sheet2) a ---------注意工作表名接符號
---------注:HDR=Yes尺棋，這代表第一行是標(biāo)題封锉，不做為數(shù)據(jù)使用 ，如果用HDR=NO膘螟，則表示第一行不是標(biāo)題成福，做為數(shù)據(jù)來使用。系統(tǒng)默認(rèn)的是YES.
---------excel 2003：'microsoft.jet.oledb.4.0','excel 5.0
---------excel 2007：xlsx的2007版以上的為microsoft.Ace.oledb.12.0','excel 12.0
---------工作表以數(shù)字開頭'select * from [3et$]'
go

鏈接服務(wù)器：使用OLEDB驅(qū)動程序創(chuàng)建SQL Server鏈接服務(wù)器
IF EXISTS(SELECT * FROM master..sysservers WHERE srvname='192.168.1.12')
EXEC sp_dropserver '192.168.1.12','droplogins'
EXEC sp_addlinkedserver '192.168.1.12','','SQLOLEDB','192.168.1.12'
EXEC sp_addlinkedsrvlogin '192.168.1.12','false',null,'sa','sa'
GO

row_number()實(shí)現(xiàn)排序顯示行號:row_number() over(partition by column order by column)
不重復(fù)統(tǒng)計(jì)：來源不重復(fù)統(tǒng)計(jì)
select
來源類型=soName
,soCode
,成交量=(select count(distinct saclId) from v_SalesAuditList
where sassId=3047 and sasdId not in(1044,1047,1049,1054,1055,1058,1060,1062,1068,1070,1072,1076,1077,1079)
and saBocDate between '2014-01-01' and '2018-01-31'
--and saclId not in(select saclId from V_SalesAuditList where sasdId not in(1044,1047,1049,1054,1055,1058,1060,1062,1068,1070,1072,1076,1077,1079) and sassId=3047 and saBocDate<'2018-01-01')
and (select min(soId) from tb_Source where len(soCode)=8 and soCode in (select left((select soCode from tb_source where soId=snsoId),8) from tb_SourceNexus where sncoId=saclId))=soId
)
from tb_Source a
where len(soCode)=8
order by soCode

多行轉(zhuǎn)一行：
select
產(chǎn)品=isnull((select STUFF((SELECT ','+waName from tb_WaySort
where exists(select 1 from tb_ProductNexus where pncoId=clId and pnwaId=waId) FOR XML PATH('')),1, 1, '') AS A
),'')
from tb_ClientMain
where clId=1000001

一行轉(zhuǎn)多行：

查詢方法如下：

select

a.id,b.txt

from

(select id,txt=convert(xml,'<root><v>'+replace(txt,';','</v><v>')+'</v></root>') from PkTable)a

outer apply

(select txt=C.v.value('.','nvarchar(100)') from a.txt.nodes('/root/v')C(v))b

我解釋下這個sql中用到的一些語法：

（1）replace函數(shù)荆残，是將';'替換為了'</v><v>'奴艾，又經(jīng)'+'連接，經(jīng)convert類型轉(zhuǎn)化内斯，表a的數(shù)據(jù)如下：


(2)在b表中蕴潦，a.txt指的是a表的txt列，nodes函數(shù)的作用是根據(jù)參數(shù)中的字符串（標(biāo)簽）將xml轉(zhuǎn)化為關(guān)系數(shù)據(jù)集俘闯，即轉(zhuǎn)化為行 潭苞，C(v)是別名，C為表明真朗，v為列名此疹。

通過以下語句進(jìn)行查詢C表的數(shù)據(jù)：
select C.v.query('.') txt
from PkTable
outer apply txt.nodes('/root/v')as C(v)

查詢結(jié)果如下：

(3)C.v.value('.','nvarchar(100)')，C是表遮婶，v是列蝗碎，value函數(shù)是讀取標(biāo)簽之間的值，對于這個列子旗扑，讀取的為<v>和</v>中間的值蹦骑；

value的第一個參數(shù)是一個字符串文字，從 XML 實(shí)例內(nèi)部檢索數(shù)據(jù)肩豁。 XQuery 必須最多返回一個值脊串。 否則，將返回錯誤清钥；

value的第二個參數(shù)是指將查詢結(jié)果轉(zhuǎn)化為何種類型的數(shù)據(jù)琼锋。

此處，'.'表示當(dāng)前目錄祟昭，即<v>目錄缕坎，另外'..'表示上級目錄，'/'表示根目錄篡悟，這個跟Linux是一樣的谜叹。
通過以下語句查詢b表：
select b.txt
from
(select id,txt=convert(xml,'<root><v>'+replace(txt,';','</v><v>')+'</v></root>') from PkTable)a
outer apply
(select txt=C.v.value('.','nvarchar(100)') from a.txt.nodes('/root/v')C(v))b
b表的數(shù)據(jù)如下：



（4）最后就是outer apply了匾寝，他是先定好a表的列，然后取b表的列插入荷腊。對于這個函數(shù)艳悔，最后的查詢結(jié)果為：


三、python連接數(shù)據(jù)庫

import pymysql
--連接mysql女仰，獲取連接的對象
dbmysql = pymysql.connect(host='localhost', port=3306, user='root', passwd='root123', db='school', charset='utf8')

with dbmysql:
--獲取連接的cursor對象猜年，用于執(zhí)行查詢
cur = dbmysql.cursor()
--類似于其他語言的query函數(shù)，execute是python中的執(zhí)行查詢函數(shù)
cur.execute("select * from tb_Student")
--使用fetchall函數(shù)疾忍，將結(jié)果集（多維元組）存入datas里面
datas = cur.fetchall()
--依次遍歷結(jié)果集乔外，發(fā)現(xiàn)每個元素，就是表中的一條記錄一罩，用一個元組來顯示
for data in datas:
print(data)
cur.close()
dbmysql.close()

import pymssql
--連接mysql杨幼，獲取連接的對象
dbmssql = pymssql.connect(host='localhost',user='sa',password='sa',database='db_tables',charset='utf8')

with dbmssql:
--獲取連接的cursor對象，用于執(zhí)行查詢
cur = dbmssql.cursor()
--類似于其他語言的query函數(shù)聂渊，execute是python中的執(zhí)行查詢函數(shù),varchar亂碼轉(zhuǎn)為ncarchar正常
cur.execute("select * from tb_Student")
--使用fetchall函數(shù)差购，將結(jié)果集（多維元組）存入datas里面
datas = cur.fetchall()
--依次遍歷結(jié)果集，發(fā)現(xiàn)每個元素歧沪，就是表中的一條記錄歹撒，用一個元組來顯示
for data in datas:
print(data)
cur.close()
dbmssql.close()

import sqlite3
conn = sqlite3.connect('sqlite.db')
cur = conn.cursor()
cur.execute("select * from tb_Student")
datas = cur.fetchall()
con.close()

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 人面猴
    序言：七十年代末，一起剝皮案震驚了整個濱河市诊胞，隨后出現(xiàn)的幾起案子暖夭，更是在濱河造成了極大的恐慌，老刑警劉巖撵孤，帶你破解...
    沈念sama閱讀 217,084評論 6贊 503
  • 死咒
    序言：濱河連續(xù)發(fā)生了三起死亡事件迈着，死亡現(xiàn)場離奇詭異，居然都是意外死亡邪码，警方通過查閱死者的電腦和手機(jī)裕菠，發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 92,623評論 3贊 392
  • 救了他兩次的神仙讓他今天三更去死
    文/潘曉璐 我一進(jìn)店門，熙熙樓的掌柜王于貴愁眉苦臉地迎上來闭专，“玉大人奴潘，你說我怎么就攤上這事∮岸ぃ” “怎么了画髓？”我有些...
    開封第一講書人閱讀 163,450評論 0贊 353
  • 道士緝兇錄：失蹤的賣姜人
    文/不壞的土叔 我叫張陵，是天一觀的道長平委。 經(jīng)常有香客問我奈虾，道長，這世上最難降的妖魔是什么？ 我笑而不...
    開封第一講書人閱讀 58,322評論 1贊 293
  • ?港島之戀（遺憾婚禮）
    正文 為了忘掉前任肉微，我火速辦了婚禮匾鸥，結(jié)果婚禮上，老公的妹妹穿的比我還像新娘碉纳。我一直安慰自己勿负，他們只是感情好，可當(dāng)我...
    茶點(diǎn)故事閱讀 67,370評論 6贊 390
  • 惡毒庶女頂嫁案：這布局不是一般人想出來的
    文/花漫 我一把揭開白布劳曹。 她就那樣靜靜地躺著笆环，像睡著了一般。 火紅的嫁衣襯著肌膚如雪厚者。 梳的紋絲不亂的頭發(fā)上，一...
    開封第一講書人閱讀 51,274評論 1贊 300
  • 城市分裂傳說
    那天迫吐，我揣著相機(jī)與錄音库菲，去河邊找鬼。 笑死志膀，一個胖子當(dāng)著我的面吹牛熙宇，可吹牛的內(nèi)容都是我干的。 我是一名探鬼主播溉浙，決...
    沈念sama閱讀 40,126評論 3贊 418
  • 雙鴛鴦連環(huán)套：你想象不到人心有多黑
    文/蒼蘭香墨 我猛地睜開眼烫止，長吁一口氣：“原來是場噩夢啊……” “哼！你這毒婦竟也來了戳稽？” 一聲冷哼從身側(cè)響起馆蠕，我...
    開封第一講書人閱讀 38,980評論 0贊 275
  • 萬榮殺人案實(shí)錄
    序言：老撾萬榮一對情侶失蹤，失蹤者是張志新（化名）和其女友劉穎惊奇，沒想到半個月后互躬，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體，經(jīng)...
    沈念sama閱讀 45,414評論 1贊 313
  • ?護(hù)林員之死
    正文 獨(dú)居荒郊野嶺守林人離奇死亡颂郎，尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 37,599評論 3贊 334
  • ?白月光啟示錄
    正文 我和宋清朗相戀三年吼渡，在試婚紗的時候發(fā)現(xiàn)自己被綠了。 大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片乓序。...
    茶點(diǎn)故事閱讀 39,773評論 1贊 348
  • 活死人
    序言：一個原本活蹦亂跳的男人離奇死亡寺酪，死狀恐怖，靈堂內(nèi)的尸體忽然破棺而出替劈，到底是詐尸還是另有隱情寄雀，我是刑警寧澤，帶...
    沈念sama閱讀 35,470評論 5贊 344
  • ?日本核電站爆炸內(nèi)幕
    正文 年R本政府宣布抬纸，位于F島的核電站咙俩，受9級特大地震影響，放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜阿趁，卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 41,080評論 3贊 327
  • 男人毒藥：我在死后第九天來索命
    文/蒙蒙 一膜蛔、第九天 我趴在偏房一處隱蔽的房頂上張望。 院中可真熱鬧脖阵，春花似錦皂股、人聲如沸。這莊子的主人今日做“春日...
    開封第一講書人閱讀 31,713評論 0贊 22
  • 一樁弒父案呜呐，背后竟有這般陰謀
    文/蒼蘭香墨 我抬頭看了看天上的太陽。三九已至悍募，卻和暖如春蘑辑，著一層夾襖步出監(jiān)牢的瞬間，已是汗流浹背坠宴。 一陣腳步聲響...
    開封第一講書人閱讀 32,852評論 1贊 269
  • 情欲美人皮
    我被黑心中介騙來泰國打工洋魂， 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留，地道東北人喜鼓。 一個月前我還...
    沈念sama閱讀 47,865評論 2贊 370
  • 代替公主和親
    正文 我出身青樓副砍，卻偏偏與公主長得像，于是被迫代替她去往敵國和親庄岖。 傳聞我的和親對象是個殘疾皇子豁翎，可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 44,689評論 2贊 354