SQL語言-數(shù)據(jù)定義

1、5.1 SQL語言概述 SQL，結(jié)構(gòu)化查詢語言 w Structured Query Languagew 是一種介于關(guān)系代數(shù)與關(guān)系演算之間的語言w 已成為關(guān)系數(shù)據(jù)庫的標(biāo)準(zhǔn)語言w 支持關(guān)系系統(tǒng) Oracle、Informix、Sybase、MS SQL、INGRES、DB2、VF、FoxPro、Access等 SQL86w 由ANSI（美國國家標(biāo)準(zhǔn)局）于1986年10月公布w 1987年6月國際標(biāo)準(zhǔn)化組織OSI把它采納為國際標(biāo)準(zhǔn) SQL89w ISO于1989年4月公布，增強(qiáng)了完整性特征 SQL92w ISO于1992年公布，也稱為SQL2 SQL99意義：自SQL成為國際標(biāo)準(zhǔn)語言以后，各個(gè)數(shù)據(jù)

2、庫廠家紛紛推出各自支持的SQL軟件或與SQL的接口軟件。大多數(shù)數(shù)據(jù)庫均用SQL作為共同的數(shù)據(jù)存取語言和標(biāo)準(zhǔn)接口，使不同數(shù)據(jù)庫系統(tǒng)之間的互操作有了共同的基礎(chǔ)。這個(gè)意義十分重大。因此，有人把確立SQL為關(guān)系數(shù)據(jù)庫語言標(biāo)準(zhǔn)及其后的發(fā)展稱為一場革命。 SQL成為國際標(biāo)準(zhǔn)，對(duì)數(shù)據(jù)庫以外的領(lǐng)域也產(chǎn)生了很大影響，有不少軟件產(chǎn)品將SQL語言的數(shù)據(jù)查詢功能與圖形功能、軟件工程工具、軟件開發(fā)工具、人工智能程序結(jié)合起來。SQL已成為關(guān)系數(shù)據(jù)庫領(lǐng)域中一個(gè)主流語言。 意義： 大多數(shù)數(shù)據(jù)庫開發(fā)商支持SQL/99,但并不是百分之百符合這個(gè)標(biāo)準(zhǔn)。目前市場上存在一些不同風(fēng)格的SQL,因?yàn)槊恳粋€(gè)RDBMS開發(fā)商都試圖擴(kuò)展這個(gè)標(biāo)準(zhǔn)

3、，以增強(qiáng)產(chǎn)品的功能，提高產(chǎn)品的吸引力,因此各個(gè)DBMS產(chǎn)品在實(shí)現(xiàn)標(biāo)準(zhǔn)SQL語言時(shí)也各有差別，讀者在使用某個(gè)DBMS產(chǎn)品時(shí)，應(yīng)仔細(xì)閱讀系統(tǒng)提供的有關(guān)手冊(cè)。本書中我們將遵循SQL/99標(biāo)準(zhǔn)向大家介紹語句。但為了增強(qiáng)大家的認(rèn)識(shí)，我們會(huì)通過SQL Server數(shù)據(jù)庫闡述部分特性，與標(biāo)準(zhǔn)SQL/99會(huì)有一些偏離。 1、數(shù)據(jù)定義w DDL語言（數(shù)據(jù)定義語言），主要定義數(shù)據(jù)庫的邏輯結(jié)構(gòu)，包括定義基本表、視圖和索引； 2、數(shù)據(jù)操縱w DML語言（數(shù)據(jù)操縱語言），包括數(shù)據(jù)查詢和更新（插入、刪除和修改）兩大類操作； 3、數(shù)據(jù)控制w DCL語言（數(shù)據(jù)控制語言），包括對(duì)基本表和視圖的授權(quán)，完整性規(guī)則的描述和事務(wù)控制；

4、 4、嵌入式SQLw 規(guī)定了SQL語句在宿主語言的程序中使用規(guī)則。1. SQL語言的組成： 1. 綜合統(tǒng)一w 查詢、操縱、定義、控制操作功能一體化w 語言風(fēng)格統(tǒng)一，數(shù)據(jù)操作符統(tǒng)一性 2. 高度非過程化w 用戶只需提出“做什么”，而不必指明“怎么做”w 隱蔽數(shù)據(jù)存取路徑 3. 面向集合的操作方式w 操作的對(duì)象和結(jié)果都是元組的集合（關(guān)系）2. SQL語言的特點(diǎn)： 4. 以同一種語法結(jié)構(gòu)提供兩種使用方式w 交互式語言，一般DBMS都提供聯(lián)機(jī)交互工具，用戶可直接鍵入SQL命令對(duì)數(shù)據(jù)庫進(jìn)行操作，由DBMS來進(jìn)行解釋，針對(duì)所有DB用戶。w 嵌入式語言，將SQL語句嵌入到高級(jí)語言（宿主語言），使應(yīng)用程序充分

5、利用SQL訪問數(shù)據(jù)庫的能力，針對(duì)應(yīng)用程序員。 w 兩種類型的語法結(jié)構(gòu)基本一致 5. 語言簡潔，易學(xué)易用w 類似于英語的自然語言w 操作謂詞少（核心功能只用9個(gè)動(dòng)詞）SQL功能動(dòng)詞 數(shù)據(jù)查詢 數(shù)據(jù)定義 數(shù)據(jù)操縱 數(shù)據(jù)控制SELECTCREATE，DROP，ALTERINSERT，UPDATE，DELETEGRANT，REVOKE3. SQL對(duì)關(guān)系數(shù)據(jù)庫模式的支持基本表基本表B1視圖視圖V1基本表基本表B3基本表基本表B4存儲(chǔ)文件存儲(chǔ)文件S1存儲(chǔ)文件存儲(chǔ)文件S2基本表基本表B2視圖視圖V2SQL外層外層/ /外模式外模式概念層概念層/ /概念模式概念模式內(nèi)層內(nèi)層/ /存儲(chǔ)模式存儲(chǔ)模式 基本表（ba

6、se table)：w 數(shù)據(jù)庫中實(shí)際獨(dú)立存在的表（關(guān)系）w 存儲(chǔ)在實(shí)際的文件中 存儲(chǔ)文件w 存儲(chǔ)基本表的數(shù)據(jù) 視圖（view)w 是一個(gè)虛擬表w 從基本表或其他視圖中導(dǎo)出的表w 沒有實(shí)際的存儲(chǔ)位置w 數(shù)據(jù)庫中只存放視圖的定義而不存放視圖對(duì)應(yīng)的數(shù)據(jù)，數(shù)據(jù)仍然存放在導(dǎo)出視圖的基本表中3.2 數(shù)據(jù)定義 數(shù)據(jù)定義功能包括定義基本表、視圖和索引操作對(duì)象操作方式創(chuàng) 建刪 除修 改表CREAT TABLEDROP TABLEALTER TABLE視圖CREAT VIEWDROP VIEW索引CREAT INDEXDROP INDEX準(zhǔn)備工作：數(shù)據(jù)類型 數(shù)據(jù)類型用于給特定的列提供數(shù)據(jù)規(guī)則，數(shù)據(jù)類型用于給特定的

7、列提供數(shù)據(jù)規(guī)則，不同的不同的DBMS支持的數(shù)據(jù)類型不同，有支持的數(shù)據(jù)類型不同，有細(xì)微的差別。細(xì)微的差別。SQL提供的主要類型：提供的主要類型： 數(shù)值型、字符型、位串型、日期型數(shù)值型、字符型、位串型、日期型T-SQL數(shù)據(jù)類型 T-SQL中支持的、常用的數(shù)據(jù)類型 w 字符型 w 日期時(shí)間類型 w 數(shù)值類型 w 二進(jìn)制型 w 位型 w 文本型 w 圖像型 w 時(shí)間戳型 非unicod字符型 固定長度或可變長度的字符數(shù)據(jù)類型 允許定義的長度是1-8000 有兩種w 固定長度的char( 10)類型w 可變長度的varchar( )類型 輸入字符型數(shù)據(jù)時(shí)必須用單引號(hào)將數(shù)據(jù)括起來，例：hello unic

8、od字符型 使用 UNICODE UCS-2 字符集 定義值在 1 到 4,000 之間（含）。存儲(chǔ)大小為兩倍 n 字節(jié)。 有兩種w 固定長度的nchar( )類型w 可變長度的nvarchar( )類型 輸入字符型數(shù)據(jù)時(shí)格式，例：Nhello 日期時(shí)間類型 datetime和smalldatetime是用來存儲(chǔ)日期和時(shí)間數(shù)據(jù) 比較內(nèi)容 Datetime Smalldatetime 最小值 Jan 1,1753 Jan 1,1900 最大值 Dec 31,9999 Jun 6,2079 占用存儲(chǔ)空間 8byte 4byte 精度 3.33毫秒 1分鐘 整數(shù)類型 整數(shù)類型的比較 比較內(nèi)容 Int

9、 Smallint Tinyint 最小值 -231 -215 0 最大值 231-1 215-1 255 占用存儲(chǔ)空間 4byte 2byte 1byte 浮點(diǎn)數(shù)類型 比較內(nèi)容FloatReal最小值-1.79E+308-3.40E+38最大值1.79E+3083.40E+38占用存儲(chǔ)空間8Byte4Byte精度最多15位最多7位精確數(shù)類型 精確數(shù)類型有兩種形式w Decimalw Numeric 與浮點(diǎn)類型數(shù)據(jù)的區(qū)別 w 可以自定義精度的位數(shù)w 例 num_col(5,2)貨幣型 貨幣類型的比較 比較內(nèi)容MoneySmallmoney最小值-922,337,203,685,477.5808

10、-214,748.3648最大值922,337,203,685,477.5807214,748.3647占用存儲(chǔ)空間8Byte4Byte精度小數(shù)點(diǎn)后4位小數(shù)點(diǎn)后4位位數(shù)據(jù)類型 位數(shù)據(jù)類型bit是一種邏輯數(shù)據(jù)類型 只有1和0兩種數(shù)值 一般常用作true/false使用 定義bit列時(shí)，不允許為NULL，也不能建立索引 多個(gè)bit列可占用一個(gè)字節(jié)文本型和圖像型 文本類型text 圖像類型image 每行可以存儲(chǔ)2GB的二進(jìn)制大型對(duì)象 時(shí)間戳型 時(shí)間戳型表示Timestamp 由系統(tǒng)自動(dòng)賦值的一個(gè)計(jì)數(shù)器數(shù)據(jù) 記錄了數(shù)據(jù)行的操作順序 時(shí)間戳列的數(shù)據(jù)保持惟一，它可以惟一標(biāo)識(shí)表中的列 時(shí)間戳列的值實(shí)際來自

11、于事務(wù)日志 語句格式：CREATE TABLE ( 列級(jí)完整性約束條件 , 列級(jí)完整性約束條件 , ）；1、定義基本表 CREATE TABLE 列和表完整性約束條件列和表完整性約束條件 約束類型說明Not null防止NULL值進(jìn)入該列 unqiue防止重復(fù)值進(jìn)入該列 Default默認(rèn)約束，將該列常用的值定義為缺省值，減少數(shù)據(jù)輸入Check檢查約束，通過約束條件表達(dá)式設(shè)置列值應(yīng)滿足的條件Primary key要求進(jìn)入該列的所有值是唯一的，且不為NULL Foreign key表間約束列約束或表約束，分別取決于否應(yīng)用于一個(gè)或多個(gè)列 例1 創(chuàng)建無約束的表結(jié)構(gòu) 建立學(xué)生表student，由學(xué)號(hào)、

12、姓名、性別、年齡、系別5個(gè)屬性組成。CREATE TABLE student (Sno char(8), Sname char(20) , Ssex char(1) Sage int, Sdept char(20) 創(chuàng)建有約束的表結(jié)構(gòu)Create table student(sno char(8) primary key, sname char(8) not null unique, ssex char(2) default 男 check(ssex=男 or ssex=女), sage tinyint check(sage between 15 and 50), sdept char(20)

13、 T-SQL中具體創(chuàng)建約束時(shí)，分為命名約束與自命名約束，自命名約束不需要constraint關(guān)鍵字，直接在字段定義后創(chuàng)建，命名約束需要constraint關(guān)鍵字，對(duì)約束命名，位置在字段定義結(jié)束后。創(chuàng)建有約束的表結(jié)構(gòu)create table sc(sno char(8), cno char(2), grade tinyint, constraint pk_sc primary key(sno,cno), constraint fk_stuid foreign key(sno) references student(sno), constraint fk_course foreign key(cn

14、o) references course(cno), constraint ck_grade check(grade=0 and grade=100) )注意：教材是標(biāo)準(zhǔn)SQL99語法，和具體的DBMS的語法有區(qū)別 語句格式ALTER TABLE ADD 完整性約束 ADD constraint 完整性約束 DROP constraint DROP COLUMN -T-SQL ALTER column -T-SQL 修改內(nèi)容w 增加新列和新的完整性約束條件（ADD子句） w 刪除指定的完整性約束條件（DROP子句） w 修改原有的列定義 （ ALTER子句）2、修改基本表 ALTER TABL

15、E 例2 向Student表中增加“入學(xué)時(shí)間”列，其數(shù)據(jù)類型為日期型。ALTER TABLE Student ADD Scome DATEtime; 例3 將Student表中年齡的數(shù)據(jù)類型改為半字長整數(shù)。ALTER TABLE Student ALTER Sage SMALLINT; 例4 刪除sc表中成績?nèi)≈迪拗频募s束ALTER TABLE Student DROP constraint ck_grade; 語句格式DROP TABLE 例5 刪除Student表DROP TABLE Student 說明：w 刪除基本表定義時(shí)，表中的數(shù)據(jù)、在此表上建立的索引將同時(shí)被刪除；w 此表上的視圖不

16、被刪除，但已無法使用。3、刪除基本表 DROP TABLE4、索引 表的索引就是表中數(shù)據(jù)的目錄 索引基于表中的列創(chuàng)建，可以基于不同的列創(chuàng)建多個(gè)索引 在進(jìn)行數(shù)據(jù)查詢時(shí)，如果不使用索引，就需要將數(shù)據(jù)文件分塊，逐個(gè)讀到內(nèi)存中進(jìn)行查找比較操作。如果使用索引，可先將索引文件讀入內(nèi)存，根據(jù)索引項(xiàng)找到元組的地址，然后再根據(jù)地址將元組數(shù)據(jù)讀入內(nèi)存，并且由于索引文件中只含有索引項(xiàng)和元組地址，文件很小，而且索引項(xiàng)經(jīng)過排序，索引可以很快的讀入內(nèi)存并找到相應(yīng)元組地址，極大地提高查詢的速度。 使用索引可保證數(shù)據(jù)的唯一性。在索引的定義中包括了數(shù)據(jù)唯一性的內(nèi)容。 使用索引可加快連接速度 索引的分類 聚簇/聚集索引 w 索引

17、樹的葉級(jí)頁包含實(shí)際的數(shù)據(jù)；表的物理順序與記錄的索引順序相同。非常類似于目錄表，目錄表的順序與實(shí)際頁碼順序。一個(gè)表只能有一個(gè)聚簇索引。 非聚簇/非聚集索引 w 記錄的物理順序與邏輯順序沒有必然關(guān)系，一個(gè)表也可以同時(shí)有多個(gè)非聚簇索引，類似于一本書同時(shí)有主題索引和作者索引。 Sql server 在創(chuàng)建主鍵是會(huì)根據(jù)主鍵自動(dòng)創(chuàng)建聚簇索引，表中數(shù)據(jù)的物理順序要重新排列，和聚簇索引的順序保持一致。 SQL Server在某個(gè)列上建立unique約束時(shí)，會(huì)根據(jù)該列自動(dòng)創(chuàng)建唯一索引。 語句格式CREATE UNIQUE CLUSTERINDEX ON (, ) ;w UNIQUE 表示每一個(gè)索引值只對(duì)應(yīng)唯一的數(shù)據(jù)記錄w CLUSTER 聚簇索引：索引項(xiàng)的順序與表中記錄的物理順序相一致 相關(guān)說明w 索引可以提高數(shù)據(jù)檢索速度w 索引可以保證行的唯一性。w 索引會(huì)增加系統(tǒng)的開銷（空間、速度）w 索引由系統(tǒng)自動(dòng)使用和維護(hù)建立索引 CREATE INDEX 例6 為學(xué)生-課程數(shù)據(jù)庫中Student，Course，SC三個(gè)表建立索引。w Student表按姓名升序建立唯一索引；w SC表按學(xué)號(hào)升序課程號(hào)降序建立唯一索引。CR