Sql 常用語法

来源:http://www.cnblogs.com/yuan-jun/archive/2017/02/11/6390301.html
-Advertisement-
Play Games

一、SQL分類 DDL—數據定義語言(CREATE，ALTER，DROP，DECLARE) DML—數據操縱語言(SELECT，DELETE，UPDATE，INSERT) DCL—數據控制語言(GRANT，REVOKE，COMMIT，ROLLBACK) 二、基礎語句 2.1、說明：創建資料庫 2.2 ...


一、SQL分類

DDL—數據定義語言(CREATE，ALTER，DROP，DECLARE)

DML—數據操縱語言(SELECT，DELETE，UPDATE，INSERT)

DCL—數據控制語言(GRANT，REVOKE，COMMIT，ROLLBACK)

 

二、基礎語句

2.1、說明：創建資料庫

create database db-name

2.2、說明：刪除資料庫

drop database db-name 

2.3、說明：備份sql server

--- 創建 備份
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:mssql7backupMyNwind_1.dat'
--- 開始 備份
BACKUP DATABASE pubs TO testBack

2.4、說明：創建新表

create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)根據已有的表創建新表：
A：create table tab_new like tab_old (使用舊表創建新表)
B：create table tab_new as select col1,col2… from tab_old definition only

2.5、說明：刪除新表

drop table tabname

2.6、說明：增加一個列

Alter table tabname add column col type

註：列增加後將不能刪除。DB2中列加上後數據類型也不能改變，唯一能改變的是增加varchar類型的長度。

2.7、說明：添加/刪除主鍵

Alter table tabname add primary key(col)
Alter table tabname drop primary key(col)

2.8、說明：創建/刪除索引

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

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

2.9、說明：創建/刪除視圖

create view viewname as select statement
drop view viewname

2.10、說明：基本sql語句選擇

查詢：select * from table1 where 範圍
插入：insert into table1(field1,field2) s(1,2)
刪除：delete from table1 where 範圍
更新：update table1 set field1=1 where 範圍
查找：select * from table1 where field1 like ’%1%’ ---like的語法
排序：select * from table1 order by field1,field2 [desc]
總數：select count * as totalcount from table1
求和：select sum(field1) as sum from table1
平均：select avg(field1) as avg from table1
最大：select max(field1) as max from table1
最小：select min(field1) as min from table1

2.11、說明：高級查詢運算

A： UNION 運算符

UNION 運算符通過組合其他兩個結果表（例如 TABLE1 和 TABLE2）並消去表中任何重覆行而派生出一個結果表。

當 ALL 隨 UNION 一起使用時（即 UNION ALL），不消除重覆行。

兩種情況下，派生表的每一行不是來自 TABLE1 就是來自 TABLE2。

B： EXCEPT 運算符

EXCEPT 運算符通過包括所有在 TABLE1 中但不在 TABLE2 中的行並消除所有重覆行而派生出一個結果表。

當 ALL 隨 EXCEPT 一起使用時 (EXCEPT ALL)，不消除重覆行。

C： INTERSECT 運算符

INTERSECT 運算符通過只包括 TABLE1 和 TABLE2 中都有的行並消除所有重覆行而派生出一個結果表。

當 ALL 隨 INTERSECT 一起使用時 (INTERSECT ALL)，不消除重覆行。

註：使用運算詞的幾個查詢結果行必須是一致的。

2.12、說明：使用外連接

A、left outer join： 左外連接（左連接）：結果集幾包括連接表的匹配行，也包括左連接表的所有行。

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: 右外連接(右連接)：結果集既包括連接表的匹配連接行，也包括右連接表的所有行。

C：full outer join： 全外連接：不僅包括符號連接表的匹配行，還包括兩個連接表中的所有記錄。

 

補充：

1)、說明：複製表(只複製結構,源表名：a 新表名：b) (Access可用)

法一：select * into b from a where 1<>1

法二：select top 0 * into b from a

2)、說明：拷貝表(拷貝數據,源表名：a 目標表名：b) (Access可用)

insert into b(a, b, c) select d,e,f from b; 

3)、說明：跨資料庫之間表的拷貝(具體數據使用絕對路徑) (Access可用)

insert into b(a, b, c) select d,e,f from b in ‘具體資料庫’ where 條件

例子：..from b in '"&Server.MapPath(".")&"data.mdb" &"' where..

4)、說明：子查詢(表名1：a 表名2：b)

select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3) 

5)、說明：顯示文章、提交人和最後回覆時間

select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b

6)、說明：外連接查詢(表名1：a 表名2：b)

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

7)、說明：線上視圖查詢(表名1：a )

select * from (SELECT a,b,c FROM a) T where t.a > 1; 

8)、說明：between的用法,between限制查詢數據範圍時包括了邊界值,not between不包括

select * from table1 where time between time1 and time2
select a,b,c, from table1 where a not between 數值1 and 數值2

9）、說明：in 的使用方法

select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’) 

10）、說明：兩張關聯表，刪除主表中已經在副表中沒有的信息

delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )

11）、說明：四表聯查問題

select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where ..... 

12）、說明：日程安排提前五分鐘提醒

SQL: select * from 日程安排 where datediff('minute',f開始時間,getdate())>5 

13）、說明：一條sql 語句搞定資料庫分頁

select top 10 b.* from (select top 20 主鍵欄位,排序欄位 from 表名 order by 排序欄位 desc) a,表名 b where b.主鍵欄位 = a.主鍵欄位 order by a.排序欄位

14）、說明：前10條記錄

select top 10 * form table1 where 範圍 

15）、說明：選擇在每一組b值相同的數據中對應的a最大的記錄的所有信息(類似這樣的用法可以用於論壇每月排行榜,每月熱銷產品分析,按科目成績排名,等等.)

select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) 

16）、說明：包括所有在 TableA 中但不在 TableB和TableC 中的行並消除所有重覆行而派生出一個結果表

(select a from tableA ) except (select a from tableB) except (select a from tableC) 

17）、說明：隨機取出10條數據

select top 10 * from tablename order by newid() 

18）、說明：隨機選擇記錄

select newid()

19）、說明：刪除重覆記錄

Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) 

20）、說明：列出資料庫里所有的表名

select name from sysobjects where type='U'

21）、說明：列出表裡的所有的

select name from syscolumns where id=object_id('TableName') 

22）、說明：列示type、vender、pcs欄位，以type欄位排列，case可以方便地實現多重選擇，類似select 中的case。

select type,
sum(case vender when 'A' then pcs else 0 end),
sum(case vender when 'C' then pcs else 0 end),
sum(case vender when 'B' then pcs else 0 end)
FROM tablename group by type

顯示結果：

type vender pcs
電腦 A 1
電腦 A 1
光碟 B 2
光碟 A 2
手機 B 3
手機 C 3

23）、說明：初始化表table1

TRUNCATE TABLE table1 

24）、說明：選擇從10到15的記錄

select top 5 * from (select top 15 * from table order by id asc) table_別名 order by id desc

 

三、sql技巧

3.1、如何刪除一個表中重覆的記錄？

create table a_dist(id int,name varchar(20))

insert into a_dist values(1,'abc')
insert into a_dist values(1,'abc')
insert into a_dist values(1,'abc')
insert into a_dist values(1,'abc')

exec up_distinct 'a_dist','id'

select * from a_dist

 存儲過程如下:

 1 create procedure up_distinct(@t_name varchar(30),@f_key varchar(30))
 2 --f_key表示是分組欄位﹐即主鍵欄位
 3 
 4 as
 5 begin
 6 declare @max integer,@id varchar(30) ,@sql varchar(7999) ,@type integer
 7 select @sql = 'declare cur_rows cursor for select '+@f_key+' ,count(*) from ' +@t_name +' group by ' +@f_key +' having count(*) > 1'
 8 exec(@sql)
 9 
10 open cur_rows
11 fetch cur_rows into @id,@max
12 
13 while @@fetch_status=0
14 begin
15 select @max = @max -1
16 set rowcount @max
17 select @type = xtype from syscolumns where id=object_id(@t_name) and name=@f_key
18 if @type=56
19 select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+ @id
20 if @type=167
21 select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+''''+ @id +''''
22 exec(@sql)
23 
24 fetch cur_rows into @id,@max
25 end
26 
27 close cur_rows
28 deallocate cur_rows
29 
30 set rowcount 0
31 end
32 
33 select * from systypes
34 select * from syscolumns where id = object_id('a_dist')
View Code

3.2、查詢數據的最大排序問題（只能用一條語句寫）

CREATE TABLE hard (qu char (11) ,co char (11) ,je numeric(3, 0))

insert into hard values ('A','1',3)
insert into hard values ('A','2',4)
insert into hard values ('A','4',2)
insert into hard values ('A','6',9)
insert into hard values ('B','1',4)
insert into hard values ('B','2',5)
insert into hard values ('B','3',6)
insert into hard values ('C','3',4)
insert into hard values ('C','6',7)
insert into hard values ('C','2',3)

要求查詢出來的結果如下： 

qu co je
----------- ----------- -----
A 6 9
A 2 4
B 3 6
B 2 5
C 6 7
C 3 4

就是要按qu分組，每組中取je最大的前2位,且只能用一句sql語句。
select * from hard a where je in (select top 2 je from hard b where a.qu=b.qu order by je)

3.3、求刪除重覆記錄的sql語句

怎樣把具有相同欄位的記錄刪除，只留下一條。

例如，表test里有id,name欄位

如果有name相同的記錄 只留下一條，其餘的刪除。

name的內容不定，相同的記錄數不定。

==============================

一個完整的解決方案：

1）將重覆的記錄記入temp1表:
select [標誌欄位id],count(*) into temp1 from [表名]
group by [標誌欄位id]
having count(*)>1


2）將不重覆的記錄記入temp1表:
insert temp1 select [標誌欄位id],count(*) from [表名] group by [標誌欄位id] having count(*)=1

3）作一個包含所有不重覆記錄的表：
select * into temp2 from [表名] where 標誌欄位id in(select 標誌欄位id from temp1)

4）刪除重覆表:
delete [表名]

5）恢復表：
insert [表名] select * from temp2

6）刪除臨時表:
drop table temp1
drop table temp2

3.4、行列轉換--普通

假設有張學生成績表(CJ)如下

Name Subject Result
張三  語文    80
張三  數學    90
張三  物理    85
李四  語文    85
李四  數學    92
李四  物理    82

想變成

姓名 語文 數學 物理
張三 80   90  85
李四 85   92  82
declare @sql varchar(4000)
set @sql = 'select Name'

select @sql = @sql + ',sum(case Subject when '''+Subject+''' then Result end) ['+Subject+']'
from (select distinct Subject from CJ) as a

select @sql = @sql+' from test group by name'

exec(@sql)

3.5、行列轉換--合併

有表A,

id pid
1 1
1 2
1 3
2 1
2 2
3 1

如何化成表B:

id pid
1 1,2,3
2 1,2
3 1

創建一個合併的函數

create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''

select @str=@str+','+cast(pid as varchar) from 表A where id=@id
set @str=right(@str,len(@str)-1)

return(@str)
End
go

調用自定義函數得到結果

select distinct id,dbo.fmerg(id) from 表A

 

3.6、如何取得一個數據表的所有列名

先從SYSTEMOBJECT系統表中取得數據表的SYSTEMID,然後再SYSCOLUMN表中取得該數據表的所有列名。

SQL語句如下：

declare @objid int,@objname char(40)
set @objname = 'tablename'


select @objid = id from sysobjects where id = object_id(@objname)
select 'Column_name' = name from syscolumns where id = @objid order by colid

或 

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='users'

 

3.7、通過SQL語句來更改用戶的密碼

修改別人的,需要sysadmin role
EXEC sp_password NULL, 'newpassword', 'User'

如果帳號為SA執行
EXEC sp_password NULL, 'newpassword', sa

3.8、怎麼判斷出一個表的哪些欄位不允許為空

select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where IS_NULLABLE='NO' and TABLE_NAME=tablename

 

3.9、如何在資料庫里找到含有相同欄位的表

a. 查已知列名的情況

SELECT b.name as TableName,a.name as columnname
From syscolumns a INNER JOIN sysobjects b
ON a.id=b.id
AND b.type='U'
AND a.name='欄位名'

 

b.未知列名查所有在不同表出現過的列名

Select o.name As tablename,s1.name As columnname
From syscolumns s1, sysobjects o
Where s1.id = o.id
And o.type = 'U'
And Exists (
Select 1 From syscolumns s2
Where s1.name = s2.name
And s1.id <> s2.id
)

 

3.10、查詢第xxx行數據

假設id是主鍵：

select * from (select top xxx * from yourtable) aa where not exists(select 1 from (select top xxx-1 * from yourtable) bb where aa.id=bb.id) 

如果使用游標也是可以的

fetch absolute [number] from [cursor_name]

 

註：行數為絕對行數

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Android7.0 Phone應用源碼分析（三） phone拒接流程分析
    Android7.0 phone拒接流程分析--- 本文為原創文章，轉載請註明出處，http://www.cnblogs.com/lance2016/p/6391096.html ...
  • 利用bintray-release插件上傳到Bintray- HTTP/1.1 404 Not Found [message:Repo 'maven' was not found]問題解決
    凡是網上教程 有個5678步的總有這樣或者那樣的坑。 上周擼了一個小工具準備上傳到jcenter，方便管理以及以後使用。看了一下教程，短短幾步，弄了很久。 按Hongyang的教程http://www.voidcn.com/blog/lmj623565791/article/p-5798330.ht ...
  • 真機測試錯誤“The application could not be verified”
    真機測試錯誤“The application could not be verified” 真機測試的時候報錯：“The application could not be verified”。這裡 http://stackoverflow.com/questions/28231338/ios app ...
  • BaseAdapter獲取View之三重境界
    在BaseAdapter獲取View之前，BaseAdapter需要與數據源相關聯。 可以使用構造方法： BaseAdapter獲取View分三種：逗比式，普通式，文藝式。 逗比式：需要多次新建View，沒有充分利用列表組件(ListView,GridView)的緩衝機制，有浪費很大資源的嫌疑。 普 ...
  • 【MySQL】MySQL5.7的安裝與配置
    一、MySQL的下載 1.登陸MySQL的官網下載適用於64位系統的ZIP壓縮包（https://dev.mysql.com/downloads/mysql/） 二、解壓安裝包 將下載的ZIP壓縮包解壓到任意文件夾。（此處為： C:\mysql5.7） 三、修改配置文件 將解壓文件夾目錄下的my-d ...
  • 開啟mysql遠程登錄
    開發過程中經常遇到遠程訪問mysql的問題，每次都需要搜索，感覺太麻煩，這裡記錄下，也方便我以後查閱。 首先訪問本機的mysql(用ssh登錄終端，輸入如下命令)： 輸入密碼登陸進去後，輸入如下的語句： 其中: user是用戶名mysql預設的是rootpassword是密碼自己設置%表示任意主機, ...
  • 貝葉斯機器學習路線圖
    這是一份貝葉斯機器學習路線圖, 正在不斷更新中. 路線圖由簡短的介紹配以相應的學習資源組成, 讀者不一定要按順序學習, 可以直接定位到自己需要的地方. 很多時候, 我們希望自學某個領域的知識, 學習能力是不差的, 但苦於不知該學哪些, 從何學起, 看什麼書/視頻好? 各個概念/知識點之間是怎樣的聯繫... ...
  • Mysql 安裝與卸載
    Mysql官網下載教程： http://jingyan.baidu.com/article/ceb9fb10b77bba8cad2ba09a.html Mysql安裝圖文教程： http://jingyan.baidu.com/article/cd4c2979033a17756f6e6047.htm ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類