SQL note

本網頁以打造無障礙閱讀為目標，可以用任何瀏覽器來觀看本網頁

簡介

SQL(Structured Query Language)是一種為了存取或操作關聯式資料庫所設計的語言。
SQL的出現，解決了以往程式與資料庫相依性過高的問題，透過SQL存取資料庫使得後端資料庫較容易更換，達成資料庫的獨立，而前端的介面也可獨立使用不同的開發工具。如此將資料層分離出來，儲存到資料庫伺服器，對於維護與安全都更有保障。

關連式資料庫(RDB)

關連式資料庫中最重要的物件就是資料表(table)，每個資料表名稱在資料庫中都是唯一的，資料表的每個直行(column)稱之為欄位，每個欄位都有其資料型態(data type)，由不同欄位所組成的橫排(row)，稱之為記錄。

發展歷史

關連式資料庫(RDB)源自於 1970 年，由 Dr. E . F. Codd 所發表的一篇文章 --"A RelationalModel of Data for Large Shared Dat a Banks" 。在 1970 年，這篇文章首先刊登於 Association of Computer Machinery(ACM ，即美國的電腦協會)的Communications of the ACM 雜誌上。這篇文章徹底改變了世界的電腦運算史；這個關連式資料庫管理的模型(RDBMS)，Dr. Codd 的論點也就成為關連式資料庫的當然標準，並主宰著今日的資料庫市場。由於 Dr. Codd 的模型需要一個新語言，以存取資料庫，所以，IBM 發展了結構化英文查詢語言(Structured English Query Language)，並於 1974 年將此語言命名為SEQUEL。但很不幸地，SEQUEL 已被英國的航空公司註冊，所以 "English" 這個字眼被放棄了，於是，此新語言被更名為 SQL 或 Structured Query Language。(但是，它的發音仍然是 "sequel"。)

在 1979 年時，Larry Ellison 和 Bob Miner 成立了 Relational Software, Inc.(RSI)，並開始發展第一個商業的 Codd 模型，以及 SQL 語言，這家公司上市了全球第一個關連式資料庫 --Oracle V.2。Relational Software 很快地便改名為 Oracle Systems，以及後來的 Oracle Corporation。在大型電腦盛行的1970, 1980年代，資料庫的主流式階層式資料庫與網路型資料庫，直到現在為止，幾乎所有的資料庫都是RDB形式了。階層式資料庫近來在LDAP與Active Directory中又重新有了新的應用方向。

SQL 標準

許多資料庫系統都支援SQL，例如Oracle, Sybase, Microsoft SQL Server, Mysql等等，但它們為配合自身的資料庫特性，也各自對SQL有所擴充，因此由 ANSI 和 ISO 一同發展與定義了 SQL 標準。今日，所有廠商均宣稱提供了關連式資料庫模型，以及 SQL 語言的使用。

年份	名稱	別名	說明
1986	SQL-86	SQL-87	First published by ANSI. Ratified by ISO in 1987.
1989	SQL-89		Minor revision.
1992	SQL-92	SQL2	Major revision. SQL -92定義了四層的同義標準：初階(Entry)、過渡期(Transitional)、中階(Intermediate)和完整(Full)。必須至少支援到 Entry 層次的標準，才算是符合 SQL 標準。
1999	SQL:1999	SQL3	Added regular expression matching, recursive queries, triggers, non-scalar types and some object-oriented features. (The last two are somewhat controversial and not yet widely supported.)
2003	SQL:2003		Introduced XML-related features, window functions, standardized sequences and columns with auto-generated values (including identity-columns).

SQL指令類別

SQL語言共分四個類別的指令：

資料定義語言(DDL): Data Definition Language用作開新資料表、設定欄位、刪除資料表、刪除欄位，管理所有有關資料庫結構的東西，常見的指令有
●Create：建立資料庫的物件。
●Alter：變更資料庫的物件。
●Drop：刪除資料庫的物件。
資料操作語言(DML): Data Manipulation Language用作新增一筆資料，刪除、更新等工作，常見的指令有
●Insert：新增資料到 Table 中。
●Update：更改 Table 中的資料。
●Delete：刪除 Table 中的資料。
資料查詢語言(DQL): Data Query Language只能取回查詢結果，指令只有1個
●Select：選取資料庫中的資料。
資料控制語言(DCL): Data Control Language用作處理資料庫權限及安全設定，常見的指令有
●Grant：賦予使用者使用物件的權限。
●Revoke：取消使用者使用物件的權限。
●Commit：Transaction 正常作業完成。
●Rollback：Transaction 作業異常，異動的資料回復到 Transaction 開始的狀態。

Data Definition Language(DDL)

CREATE TABLE

使用 create 指令可建立如下的資料表結構

資料表名稱：student
欄位名稱	資料型態	意義	說明
SID	文字, 長度:8	學號	主鍵(Primary Key;PK)
NM	文字, 長度:10	姓名
ID	文字, 長度:10	身份證號碼	候選鍵(Candidate Key;CK)
TALL	數值	身高
BIR	日期	生日
DPTCD	文字, 長度:4	所屬科系代碼	外來鍵(Foreign Key; FK)

資料表名稱：department
欄位名稱	資料型態	意義	說明
DPTCD	文字, 長度:4	科系代碼	主鍵(Primary Key;PK)
DPTNM	文字, 長度:30	科系名稱

基本語法

CREATE TABLE 資料表名稱
(欄位1 欄位1的資料型態,
欄位2 欄位2的資料型態,
... )

create table student ( SID char(8), NM varchar(10), ID char(10), TALL numeric, BIR datetime, DPTCD char(4) )

ALTER TABLE

異動資料表中的欄位屬性，至少有增加/刪除/更改3種

基本語法

ALTER TABLE 資料表名稱
ADD 欄位欄位的資料型態

alter table student add tel char(15)

ALTER TABLE 資料表名稱
ALTER 欄位欄位的資料型態

alter table student alter tel char(20)

ALTER TABLE 資料表名稱
DROP 欄位

alter table student drop tel

DROP TABLE

刪除資料表

drop table student

Data Manipulation Language(DML)

INSERT INTO

使用 insert into 指令可為 student 資料表建立下列記錄。

student
SID	NM	ID	TALL	BIR	DPTCD
59432001	Peter	B180134374	175	1970-10-26	C403
59432002	Cathy	A286272178	159	1976-09-13	C403
59432003	Tom	A181095544	176	1975-1-25	C403
59532001	Doris	B250681871	157	1974-07-30	C507
59532002	Catherine	A286843097	155	1972-12-08	C507

基本語法

INSERT INTO 資料表名稱
( 欄位1 , 欄位2 , 欄位3 , 欄位4 ,...)
VALUES
( 欄位1的值 , 欄位2的值 , 欄位3的值 , 欄位4的值 , ...)

insert into student (sid, nm, id, tall, bir, dptcd) values (‘59432001‘, ‘Peter‘, ‘B180134374‘, 175, ‘1970-10-26‘, ‘C403‘)
insert into student (sid, nm, id, tall, bir, dptcd) values (‘59432002‘, Cathy‘, ‘A286272178‘, 159, ‘1976-09-13‘, ‘C403‘)
...

UPDATE

尋找符合條件的欄位有哪些紀錄，找到後異動指定欄位的值

基本語法

UPDATE 資料表名稱
SET 欄位=異動的值
WHERE 欄位=條件

update student set id=‘B120668233‘ where sid=‘59432001‘

DELETE FROM

尋找符合條件的欄位有哪些紀錄，找到後將之刪除

基本語法

DELETE FROM 資料表名稱
WHERE 欄位=條件

delete from student where tall < 175

Data Query Language(DQL)

DQL中的select命令應該是整個SQL命令的核心了，搞定了select語法，就等於搞定了SQL。

語法

select 欄位名稱
from 資料表名稱
[where 條件 ]
[group by 欲分類的欄位名稱 [having 條件 ] ]
[order by 欲排序的欄位名稱 ]

欄位名稱

可用 * 代表所有欄位，也可逐一指定欄位名稱，通常為了效率與欄位順序等考量，還是少使用 * 的方式。對於一個資料表而言，欄位的指定等同於資料表的縱向(colunm)篩選。
可使用 distinct 關鍵字過濾重複出現的紀錄。
select distinct dptcd from student
配合函數使用，做欄位顯示的過濾，例如substring()可顯示欄位的部分內容、len()可顯示欄位字串長度。
select substring(sid, 1, 3) from student

select sid , nm , len(nm) from student
在原來欄位名稱後空一格加上另外一個名稱，可為欄位重新命名。
select sid 學號 , nm 姓名 , tall 身高 from student
配合算數運算式，可將欄位的值做加減乘除的運算，成為一個虛擬欄位，並為其加上欄位名稱。
select sid , nm , tall * 1.1 height from student

資料表名稱

指的是屬於某個資料庫底下的資料表(table)，例如student。
資料表名稱若有名稱過長的情形，可以別名(alias)幫助記憶。
可連結多個資料表做合併(join)查詢，此時需搭配條件式連結二個資料表的關連欄位。
select s.sid , s.nm , d.dptnm from student s , department d where s.dptcd=d.dptcd

條件

指定欲篩選的欄位條件值為何? 亦可使用複合條件對於不同的欄位作設定。對於一個資料表而言，條件的指定等同於資料表的橫向(row)篩選。
可使用 not 來表達與條件相反地狀況，多用在排除某些記錄的場合上。
使用複合條件時多搭配邏輯運算子 and 與 or，特別注意的是 and 的優先權較 or 為高，使用時若不確定優先順序，儘量使用小括弧包起來。
null代表未定或不明，要與null作比較時要使用 is 這個比較運算子，而不能使用 = , <>這些比較運算子喔。
select * from orders where paid_date is null
通常使用 >, <, >=, <=, =, <> 等比較運算子，字元與日期欄位通常以單引號刮起來。
select nm from student where tall >= 170
字元欄位使用 like 運算子時，多使用 % 與 _ 配合運算元作模糊比對。
select dptnm from department where dptnm like ‘%電子%‘
若欄位符合個數不多的條件時，通常搭配冗長的邏輯條件，此時可改用 in函數(子集合的概念)簡化之
select nm from student where not (dptcd=‘C507‘ or dptcd=‘C302‘ or dptcd=‘C405‘)

select nm from student where dptcd not in (‘C507‘ , ‘C302‘ , ‘C405‘)
若欄位符合某個連續區間時，可使用 between ~ and ~
select nm from student where tall between 170 and 180

select nm from student where bir between ‘1970-01-01‘ and ‘1975-12-31‘
配合函數使用，做條件的過濾，例如substring()可設定子字串的條件。
select nm from student where substring(sid,2,2) = ‘95‘

欲排序的欄位名稱

order by 後接欲排序的欄位名稱，預設排序方式為由小到大(ascending)，若要指定由大到小排序則需加上關鍵字 DESC

select sid, nm from student order by nm , sid desc

欲分類的欄位名稱

group by 後接欲分類的欄位名稱，同樣分類的欄位名稱會成為一筆紀錄。

select dptcd from student group by dptcd

select s.dptcd , d.dptnm from student s, department d where s.dptcd=d.dptcd group by s.dptcd --依照dptcd分組，並join科系名稱

SQL 常用函數

字串函數

len(字串運算式): 傳回字串運算式的字元數目(非位元數)，並去除字串最後的連續空白。
select len(nm) from student
lower(字串運算式): 將字串運算式中的大寫字母轉換為小寫字母後回傳。
select lower(nm) from student
upper(字串運算式): 將字串運算式中的小寫字母轉換為大寫字母後回傳。
select upper(nm) from student
ltrim(字串運算式): 去除字串運算式前面的連續空白。
select ltrim(‘ ABCDEFG‘)
rtrim(字串運算式): 去除字串運算式後面的連續空白。
select rtrim(‘ ABCDEFG ‘)+‘HIJKLMN‘
reverse(字串運算式): 反轉排列字串運算式後回傳。
select reverse(‘台中勤益科技大學‘)

數學函數

abs(運算式): 傳回運算式的絕對值。
ceiling(運算式): 傳回運算式中大於或等於的最小整數。
floor(運算式): 傳回運算式中小於或等於的最大整數。
found(運算式 , 四捨五入的長度): 傳回運算式四捨五入後的值。

日期時間函數

day(日期): 傳回指定日期的日數為何。
month(日期): 傳回指定日期的月份為何。
year(日期): 傳回指定日期的年份為何。
getdate(): 傳回目前的系統日期與時間。

聚合函數 (Aggregate Function)

簡單的說聚合函數就是針對不同分類的統計函數，大多搭配 group by 一起使用。

count(expr): 計算分類中的紀錄筆數但不包含 Null 的欄位，除非 expr 是星號 (*) 萬用字元。
select count(*) from student
select dptcd, count(*) from student group by dptcd --依照group分組後的分類集合做統計
select s.dptcd , d.dptnm , count(s.*) from student s, department d where s.dptcd=d.dptcd group by s.dptcd --依照group分組後的分類集合做統計，並join科系名稱
sum(): 傳回分類中的數值總和。
select sum(tall) from student
select sum(tall) from student group by dptcd
select s.dptcd , d.dptnm , sum(s.tall) from student s, department d where s.dptcd=d.dptcd group by s.dptcd
avg(): 傳回分類中的數值平均。
select avg(tall) from student
select avg(tall) from student group by dptcd
select s.dptcd , d.dptnm , avg(s.tall) from student s, department d where s.dptcd=d.dptcd group by s.dptcd
min(): 傳回分類中的數值最小值。
select min(tall) from student
select min(tall) from student group by dptcd
select s.dptcd , d.dptnm , min(s.tall) from student s, department d where s.dptcd=d.dptcd group by s.dptcd
max(): 傳回分類中的數值最大值。
select max(tall) from student
select max(tall) from student group by dptcd
select s.dptcd , d.dptnm , max(s.tall) from student s, department d where s.dptcd=d.dptcd group by s.dptcd

MS-SQL

SQL Server 2005

SQL Server 2005預設安裝起來時, 關閉了TCP/IP與具名管道(Named Pipes)的連接方式, 可利用SQL Server Configuration Manager中的SQL Server 2005網路組態將其開放

SQL Server 2005提供了許多命令提示字元公用程式, 可在SQL Server線上叢書查得到, 例如使用sqlcmd來執行 sql 程式碼

sqlcmd -S ComputerName\InstanceName -i MyScript.sql -o MyOutput.rpt

資料庫檔案

SQL Server 2005 資料庫(預設放在目錄C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data)具有三種檔案類型：

主要資料檔
主要資料檔是資料庫的起點，並指到資料庫中的其他檔案。每個資料庫都有一個主要資料檔案。建議您將主要資料檔的副檔名設為 .mdf。
次要資料檔
次要資料檔是由主要資料檔以外的所有資料檔所組成。有些資料庫可能不包含任何次要資料檔，而有些則擁有數個次要資料檔。建議您將次要資料檔的副檔名設為 .ndf。
記錄檔
記錄檔中保存可用來復原資料庫的所有記錄資訊。每一個資料庫至少要有一個記錄檔，也可以有多個記錄檔。建議的記錄檔副檔名為 .ldf。

在 SQL Server 2005 中，資料庫中所有檔案的位置都會記錄在資料庫的主要檔案(*.mdf)，以及記錄在 master 資料庫中。SQL Server Database Engine 大部份時間都會使用 master 資料庫所提供的檔案位置資訊。然而，在下列情況下，Database Engine 會使用主要檔案所提供的檔案位置資訊，來初始化 master 資料庫中的檔案位置項目：

使用 CREATE DATABASE 陳述式搭配 FOR ATTACH 或 FOR ATTACH_REBUILD_LOG 選項，來附加資料庫時。
從 SQL Server 2000 版或 7.0 版升級到 SQL Server 2005 時。
還原 master 資料庫時。

新的資料型態

SQL Server 2005 針對於字串與XML的儲存提供了新的資料型態：
1.VARCHAR(MAX)
2.NVARCHAR(MAX)
3.VARBINARY(MAX)
當宣告了 MAX 關鍵字，該欄位最多可以存放 2Giga (2^31-1)位元組的資料。而不再受限於以往的 8K(也就是 Page 的大小)。這三種資料型態可以用來取代 text、ntext 以及 image 等資料型態。由 SQL Server 自行決定存放大型資料的方式，而不需我們事先決定要用指標結構存放 text 一類的資料，還是一般的 char 等資料格式。而以 VARCHAR(MAX)/NVARCHAR(MAX) 取代 text、ntext 最大的好處在於一般的 T-SQL 字串處理函數都可以操作 VARCHAR(MAX)/NVARCHAR(MAX) 欄位內的資料，而不像 text、ntext 會讓很多函數無法使用。

4.XML：原生的 XML 資料型態，可以提供 XML 資料驗證、查詢、更新、建立索引等等。該型態的資料最大長度也是 2 Giga 位元組。

查出table中的紀錄中哪些欄位重複

例如有一資料表有欄位A,B,C,D，其中有許多筆紀錄重複了,如何用SQL找出有哪些完全相同的紀錄，並顯示出重複的次數

Select *, count(*) as 重複次數
from table
group by A,B,C,D
having count(*)>1

刪除table中的重複紀錄

ALTER TABLE admin ADD NewID int IDENTITY (1, 1)
go

DELETE FROM admin WHERE EXISTS
(SELECT 1
FROM admin a
WHERE a.newid > admin.newid AND
admin.uid = a.uid AND
admin.uname = a.uname AND
admin.upass a.upass AND
admin.udep = a.udep)
go

ALTER TABLE admin DROP COLUMN NewID
go

T-SQL

Microsoft SQL Server使用的SQL語法稱之為Transact SQL，簡稱T-SQL。

case

SQL CASE 是一種通用的條件表達式，類似於其他語言裡的 if/else 語句． CASE 子句可以用於任何可以有效存在的地方。

CASE WHEN condition THEN result [ WHEN ... ] [ ELSE result ] END

condition 是一個傳回 boolean 的表達式．如果結果為真，那麼 CASE 表達式的結果就是 result ．如果結果為假，那麼以相同方式搜尋任何隨後的 WHEN 子句．如果沒有 WHEN condition 為真，那麼 case 表達式的結果就是在 ELSE 子句裡的值．如果省略了 ELSE 子句而且沒有相符的條件，結果為 NULL．

範例：

SELECT a , b, c=
　　CASE WHEN a=1 THEN ‘one‘
　　　　　WHEN a=2 THEN ‘two‘
ELSE ‘other‘
END
FROM test ;

所有 result 表達式的資料的類型都必須可以轉換成單一的輸出類型。

CASE expression WHEN value THEN result [ WHEN ... ] [ ELSE result ] END

這個 "簡單的" CASE 表達式是上面的通用形式的一個特殊的變種．先計算 expression 的值，然後與所有在 WHEN 子句裡的 value 對比，直到找到一個相等的．如果沒有找到相符的，則傳回在 ELSE 子句裡的 result （或者 NULL）．這個類似於 C 裡的 switch 語句．

範例：

SELECT a ,
　　CASE a WHEN 1 THEN ‘one‘
　　　　　 WHEN 2 THEN ‘two‘
ELSE ‘other‘
　　 END
FROM test

Unicode的寫入

SQL Server中對於可能存放Unicode的欄位必須要用 NCHAR,NVARCHAR,NTEXT 這些定義才不會發生儲存的錯誤。
當您在 SQL Server 中處理 Unicode 字串常數時，必須在所有 Unicode 字串之前加上大寫字母 N(prefix N) ，例如

insert into (A,B,C) values (N‘萬國碼xxx‘, N‘萬國碼yyy‘, N‘萬國碼zzz‘) //記住N要大寫喔

UPDATE student SET name = N‘萬國碼‘ WHERE sid = ‘s9154610‘ //記住N要大寫喔

清除交易紀錄檔

若sql的交易紀錄一直沒有去清除的話，會導致整個資料庫都不能使用，此時我們可以在 Query Analyzer 中鍵入下列指令

backup log 資料庫名 with NO_LOG | TRUNCATE_ONLY

此指令之用意為備份資料庫的Log檔，由於我們在上述語法中沒有指定備份的裝置為何，Sql Server即會認為此動作為單純要把已交易完成的Log資料清空(已交易完成的資料所指即為已commit的資料)，而Truncate_Only的選項則是告訴Sql Server目的在清空Log之空間，至於清出的空間則由Sql Server管理，不還給OS。 NO_LOG 與 TRUNCATE_ONLY 是同義字。

壓縮指定資料庫中資料檔的大小

dbcc shrinkdatabase (資料庫名 , { NOTRUNCATE | TRUNCATEONLY } )

NOTRUNCATE ：讓已釋放的檔案空間保留在資料庫檔案內。若未指定，會釋放檔案空間給作業系統。
TRUNCATEONLY：讓資料檔中任何未使用的空間釋放給作業系統，並將檔案縮到上一次配置的範圍。

壓縮目前資料庫指定的資料檔或記錄檔的大小

dbcc shrinkfile ( 指定檔名 [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ] )

NOTRUNCATE ：讓已釋放的檔案空間保留在檔案中。
TRUNCATEONLY ：讓檔案中任何未使用的空間釋放到作業系統，在不移動任何資料的情形下縮減檔案大小。

backup log education with NO_LOG
dbcc shrinkfile (education_log , truncateonly)

參考 sql 的線上叢書

其它

sql server要如何輸入null? => ctrl+0
sql 的單行註解使用--，多行註解使用 /* */
sql的binary欄位轉到VFP會變成Memo
VFP的logical欄位轉到sql上要改用bit
VFP的date欄位轉到sql上要改用datetime

在命令列上停止SQL Server的服務

net stop mssqlserver stop //服務名稱可看控制台中服務的名稱

參考書目

SQL基礎講座, Lance,INC著張士新譯, 博碩文化, ISBN : 957-527-609-4
SQL Server資料庫設計與系統管理, 李勁謝兆陽編著, 文魁資訊, ISBN：957-8260-66-0

網路資源

from: http://irw.ncit.edu.tw/peterju/sql.html

本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请点击举报。