SQL note

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


簡介

關連式資料庫(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 欄位名稱
into 資料表名稱
from 資料表名稱
[where 條件 ]
[group by 欲分類的欄位名稱   [having 條件 ] ] 
[order by 欲排序的欄位名稱 ]

欄位名稱

資料表名稱

條件

欲排序的欄位名稱

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)具有三種檔案類型:

在 SQL Server 2005 中,資料庫中所有檔案的位置都會記錄在資料庫的主要檔案(*.mdf),以及記錄在 master 資料庫中。SQL Server Database Engine 大部份時間都會使用 master 資料庫所提供的檔案位置資訊。然而,在下列情況下,Database Engine 會使用主要檔案所提供的檔案位置資訊,來初始化 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 位元組。

select 敘述產生流水號

在SQL 2000上,不像SQL 2005上有row_number(), rank() 等函數可作流水號,必須用到建立暫存資料表搭配IDENTITY()函數來實作

SELECT IDENTITY(int, 1,1) iduniq, id
into #tmp
FROM mytable
where schyy='98' and smt=2 and id like '00%' --條件可以自訂,在此不重要

select * from #tmp

drop table #tmp

查出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的寫入

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 TRUNCATE_ONLY // NO_LOG

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

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

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

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

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

backup log education with NO_LOG
use education
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  //服務名稱可看控制台中服務的名稱

參考書目

網路資源

主 網 站:http://peterju.notlong.com (目前轉址至 http://irw.ncut.edu.tw/peterju/) Sitetag Logo

Level Triple-A conformance icon | [歡迎使用任何作業系統、瀏覽器觀看!] | Valid XHTML 1.0 Transitional | Valid CSS! | [Valid RSS] | [創意公眾許可証]
This work is licensed under a Creative Commons License