第4章關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL第4章

關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL4.1SQL概述4.2學(xué)生選課系統(tǒng)數(shù)據(jù)庫(kù)簡(jiǎn)介4.3數(shù)據(jù)定義4.4數(shù)據(jù)查詢4.5數(shù)據(jù)更新4.6視圖SQL語(yǔ)言的前身是SEQUEL語(yǔ)言，是1974年由Boyce(Codd與Boyce提出BC范式)和Chamberlin提出的，作為SystemR上的查詢語(yǔ)言。后改為SQL(StructuralQueryLanguage)。1986年美國(guó)國(guó)家標(biāo)準(zhǔn)局ANSI將SQL語(yǔ)言作為關(guān)系數(shù)據(jù)庫(kù)語(yǔ)言的美國(guó)標(biāo)準(zhǔn)，即SQL－86標(biāo)準(zhǔn)1992年國(guó)際標(biāo)準(zhǔn)化組織ISO將SQL作為關(guān)系數(shù)據(jù)庫(kù)語(yǔ)言的國(guó)際標(biāo)準(zhǔn)，即SQL-92標(biāo)準(zhǔn)。后來(lái)的SQL-2003標(biāo)準(zhǔn)包含了XML相關(guān)內(nèi)容；SQL-2006標(biāo)準(zhǔn)定義了SQL與XML的關(guān)聯(lián)應(yīng)用；T-SQL全稱Transact-SQL,是微軟公司遵循SQL-92標(biāo)準(zhǔn)在MicrosoftSQLServer系統(tǒng)中使用的語(yǔ)言。4.1.1SQL的產(chǎn)生與發(fā)展4.1.2SQL的特點(diǎn)

綜合統(tǒng)一高度非過程化：只提出做什么而無(wú)須知道怎么做面向集合的操作方式以同一種語(yǔ)法提供兩種使用方式可以直接使用嵌入到其他高級(jí)語(yǔ)言中語(yǔ)言簡(jiǎn)潔，易學(xué)易用，9個(gè)動(dòng)詞4.1.2SQL語(yǔ)言的特點(diǎn)（續(xù)）SQL視圖V1視圖V2基表B1基表B2基表B3基表B4存儲(chǔ)文件S1存儲(chǔ)文件S2存儲(chǔ)文件S3存儲(chǔ)文件S4用戶外模式模式內(nèi)模式4.1.3SQL的工作Sqlserver對(duì)應(yīng)的存儲(chǔ)文件4.1.3SQL的基本概念用戶用SQL語(yǔ)言只對(duì)基本表和視圖進(jìn)行操作基本表本身獨(dú)立存在的表，一個(gè)關(guān)系對(duì)應(yīng)一個(gè)表一個(gè)（或多個(gè)）基本表對(duì)應(yīng)一個(gè)存儲(chǔ)文件一個(gè)表可以帶若干索引，索引也存放在存儲(chǔ)文件中存儲(chǔ)文件存儲(chǔ)文件的邏輯結(jié)構(gòu)組成了關(guān)系數(shù)據(jù)庫(kù)的內(nèi)模式存儲(chǔ)文件的物理結(jié)構(gòu)是任意的，對(duì)用戶是透明的視圖從一個(gè)或幾個(gè)基本表或視圖導(dǎo)出的表是虛表，只存放視圖的定義而不存放對(duì)應(yīng)數(shù)據(jù)4.2學(xué)生選課系統(tǒng)數(shù)據(jù)庫(kù)學(xué)生表：Student(Sno,Sname,Ssex,Sage,Sdept)課程表：Course(Cno,Cname,Cpno,Ccredit)學(xué)生選課表：SC(Sno,Cno,Grade)學(xué)號(hào)Sno姓名Sname性別Ssex年齡Sage所在系Sdept95001李勇男20CS95002劉晨女19IS95003王敏女18MA95004張立男19ISStudent4.2學(xué)生－課程數(shù)據(jù)庫(kù)CourseCnoCnameCpnoCcredit1數(shù)據(jù)庫(kù)542數(shù)學(xué)

23信息系統(tǒng)144操作系統(tǒng)635數(shù)據(jù)結(jié)構(gòu)746數(shù)據(jù)處理

27PASCAL語(yǔ)言644.2學(xué)生－課程數(shù)據(jù)庫(kù)SCSnoCnoGrade9500119295001285950013889500229095002380第一個(gè)動(dòng)詞create創(chuàng)建數(shù)據(jù)庫(kù)2、創(chuàng)建數(shù)據(jù)庫(kù)在默認(rèn)情況下，只有系統(tǒng)管理員可以創(chuàng)建數(shù)據(jù)庫(kù)。SQL創(chuàng)建數(shù)據(jù)庫(kù)的方法：（1）使用managementstudio創(chuàng)建數(shù)據(jù)庫(kù)（2）使用Transact-SQL創(chuàng)建數(shù)據(jù)庫(kù)創(chuàng)建數(shù)據(jù)庫(kù)1．?dāng)?shù)據(jù)庫(kù)的存儲(chǔ)結(jié)構(gòu)數(shù)據(jù)庫(kù)的邏輯存儲(chǔ)結(jié)構(gòu)：指的是數(shù)據(jù)庫(kù)是由哪些性質(zhì)的信息所組成。諸如表、視圖、索引、約束、存儲(chǔ)過程等各種不同的數(shù)據(jù)庫(kù)對(duì)象數(shù)據(jù)庫(kù)的物理存儲(chǔ)結(jié)構(gòu)：指的是數(shù)據(jù)庫(kù)文件是如何在磁盤上存儲(chǔ)的。一個(gè)數(shù)據(jù)庫(kù)至少應(yīng)該包含一個(gè)數(shù)據(jù)庫(kù)文件和一個(gè)事務(wù)日志文件。存儲(chǔ)數(shù)據(jù)的文件，擴(kuò)展名為“mdf”存儲(chǔ)數(shù)據(jù)庫(kù)的更新情況等日志信息，擴(kuò)展名為“l(fā)df”managementstudio創(chuàng)建數(shù)據(jù)庫(kù)創(chuàng)建數(shù)據(jù)庫(kù)CREATEDATABASE數(shù)據(jù)庫(kù)名

[ON[PRIMARY][<文件格式>[，…n]]][LOGON{<文件格式>[，…n]}]（2）使用Transact-SQL創(chuàng)建數(shù)據(jù)庫(kù)主文件日志文件[<文件格式>::=([NAME=邏輯文件名，]FILENAME=‘操作系統(tǒng)下的物理路徑和文件名’

[，SIZE=文件初始大小][，MAXSIZE={文件最大大小|UNLIMITED}][，F(xiàn)ILEGROWTH=增量值][，…n]]創(chuàng)建數(shù)據(jù)庫(kù)CREATEDATABASE數(shù)據(jù)庫(kù)名

[ON[PRIMARY][<文件格式>[，…n]]][LOGON{<文件格式>[，…n]}]（2）使用Transact-SQL創(chuàng)建數(shù)據(jù)庫(kù)創(chuàng)建數(shù)據(jù)庫(kù)例用createdatabase創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)，此數(shù)據(jù)庫(kù)的名字為sm，其他選項(xiàng)均采用默認(rèn)設(shè)置。createdatabasesm在默認(rèn)位置創(chuàng)建數(shù)據(jù)庫(kù)的主數(shù)據(jù)庫(kù)文件－sm.mdf。和數(shù)據(jù)庫(kù)的事務(wù)日志文件－sm_Log.ldf。C:\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\DATA數(shù)據(jù)文件和日志文件的初始大小均為默認(rèn)設(shè)置，增長(zhǎng)方式－自動(dòng)增長(zhǎng)；大大小無(wú)限制。補(bǔ)：創(chuàng)建數(shù)據(jù)庫(kù)例createdatabaseCompanyOnprimary(pany_data,filename='C:\SQL\Data\Company.mdf',size=10,maxsize=unlimited,filegrowth=10%)Logon(pany_log,filename='C:\SQL\Data\Company.ldf',size=1,maxsize=5,filegrowth=1)數(shù)據(jù)文件邏輯名物理文件名日志文件邏輯名日志文件物理名用SQL語(yǔ)句新建的數(shù)據(jù)庫(kù)只能在下拉列表中顯示出來(lái)，刷新數(shù)據(jù)庫(kù)后，才能在“數(shù)據(jù)庫(kù)”列表中顯示出來(lái)。第一個(gè)動(dòng)詞alterManagementstudio修改數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù)---右鍵----屬性，所有建立數(shù)據(jù)庫(kù)時(shí)的所有設(shè)置都可以修改。修改數(shù)據(jù)庫(kù)使用ALTERDATABASE語(yǔ)句修改數(shù)據(jù)庫(kù)語(yǔ)法格式如下：

ALTERDATABASE數(shù)據(jù)庫(kù)名

{ADDFILE<文件格式>[,…n][TO文件組]|ADDLOGFILE<文件格式>[,…n]|REMOVEFILE<日志文件名>[withdelete]|MODIFYFILE<文件格式>|MODIFYNAME=新數(shù)據(jù)庫(kù)名

|ADDFILEGROUP<文件組名>|REMOVEFILEGROUP<文件組名>|MODIFYFILEGROUP<文件組名>{FILEGROUP_PROPERTYNAME=新文件組名}}有關(guān)sqlserver的文件組mdf文件是數(shù)據(jù)庫(kù)文件,這樣的話也就意味著隨著數(shù)據(jù)庫(kù)的增大mdf就會(huì)相應(yīng)的增大,在實(shí)際的應(yīng)用中數(shù)據(jù)膨脹是太常見的事情了,然而windows對(duì)文件的大小是有要求的,這樣的話很容易導(dǎo)致mdf文件達(dá)到windows所允許的文件大小的界限(于是數(shù)據(jù)庫(kù)就崩潰了)。如左圖，所有的數(shù)據(jù)文件都放到了Primary組里了。文件組是sqlserver管理文件的方式，可以均衡數(shù)據(jù)庫(kù)的負(fù)載，提高文件存取的性能Primary文件組是系統(tǒng)默認(rèn)的文件組。用戶可以自己定義文件組。數(shù)據(jù)庫(kù)---屬性----文件組建立數(shù)據(jù)庫(kù)通常的做法是：分別建立不同的數(shù)據(jù)文件只有一個(gè)是主數(shù)據(jù)文件，擴(kuò)展名為*.mdf多個(gè)輔助數(shù)據(jù)文件，擴(kuò)展名為*.ndf這些不同的數(shù)據(jù)文件位于不同的文件組中。對(duì)應(yīng)的sql語(yǔ)句createdatabaseCompanyzzOnprimary(pany_data,filename='C:\Companyzz.mdf',size=10,maxsize=unlimited,filegrowth=10%),filegroupaa(pany_data1,filename='C:\Companyzz1.ndf',size=10,maxsize=unlimited,filegrowth=10%),filegroupbb(pany_data2,filename='C:\Companyzz2.ndf',size=10,maxsize=unlimited,filegrowth=10%)Logon(pany_log,filename='C:\Companyzz.ldf',size=1,maxsize=5,filegrowth=1)修改數(shù)據(jù)庫(kù)例

在sm數(shù)據(jù)庫(kù)中添加一個(gè)數(shù)據(jù)文件sm_data1alterdatabasesmaddfile(name='sm_data1',filename='C:\SQL\Data\sm_data1.ndf',size=4,maxsize=unlimited,filegrowth=10%)修改數(shù)據(jù)庫(kù)例

將sm數(shù)據(jù)庫(kù)中的數(shù)據(jù)文件sm_data1的最大大小改為120MBalterdatabasesmmodifyfile(name='sm_data1',maxsize=120MB)利用系統(tǒng)存儲(chǔ)過程設(shè)置數(shù)據(jù)庫(kù)選項(xiàng)數(shù)據(jù)庫(kù)的選項(xiàng)：一個(gè)數(shù)據(jù)庫(kù)的特性。系統(tǒng)存儲(chǔ)過程就是DBMS預(yù)先編譯和優(yōu)化好的并存儲(chǔ)在數(shù)據(jù)庫(kù)中的完成特定功能的函數(shù)（方法，過程），可以被反復(fù)調(diào)用SQLSERVER2005中的系統(tǒng)存儲(chǔ)過程：存儲(chǔ)在master數(shù)據(jù)庫(kù)中以SP_為前綴名系統(tǒng)存儲(chǔ)過程對(duì)未加密的過程,可通過查詢master數(shù)據(jù)庫(kù)中的ments系統(tǒng)表得到.設(shè)置數(shù)據(jù)庫(kù)選項(xiàng)用sp_dboption系統(tǒng)存儲(chǔ)過程設(shè)置數(shù)據(jù)庫(kù)選項(xiàng)?；菊Z(yǔ)法：

sp_dboption[‘?dāng)?shù)據(jù)庫(kù)名’][‘配置選項(xiàng)名’][‘設(shè)置值’]顯示相應(yīng)的數(shù)據(jù)庫(kù)屬性選項(xiàng)名稱取值：Authoshrink,Dbouseonly,Readonly,Singleuse取值：Ture,false或on,off補(bǔ)：設(shè)置數(shù)據(jù)庫(kù)選項(xiàng)例sp_dboption'sm','readonly','true'將sm數(shù)據(jù)庫(kù)設(shè)置為只讀例sp_dboption'sm','readonly',‘false'將sm數(shù)據(jù)庫(kù)設(shè)置為可寫例sp_dboption'sm',‘a(chǎn)utoshrink','true'將sm數(shù)據(jù)庫(kù)設(shè)置為可自動(dòng)收縮系統(tǒng)存儲(chǔ)過程的使用第三個(gè)動(dòng)詞drop刪除數(shù)據(jù)庫(kù)

刪除一個(gè)數(shù)據(jù)庫(kù)時(shí)也刪除了該數(shù)據(jù)庫(kù)的全部對(duì)象，從而將其所占的磁盤空間全部釋放掉。刪除數(shù)據(jù)庫(kù)的基本語(yǔ)法：

DROPDATABASE數(shù)據(jù)庫(kù)名[,...n]注：（1）刪除數(shù)據(jù)庫(kù)后應(yīng)備份master數(shù)據(jù)庫(kù)。（2）無(wú)法刪除系統(tǒng)數(shù)據(jù)庫(kù)（msdb,model,master,tempdb）（3）以下情況不能刪除數(shù)據(jù)庫(kù)：用戶使用此數(shù)據(jù)庫(kù)時(shí)數(shù)據(jù)庫(kù)正在被恢復(fù)時(shí)數(shù)據(jù)庫(kù)正在參與復(fù)制時(shí)dropdatabaseCompany刪除數(shù)據(jù)庫(kù)company創(chuàng)建數(shù)據(jù)庫(kù)更通用的寫法usemaster--設(shè)置當(dāng)前數(shù)據(jù)庫(kù)為master,以便方便訪問表sysdatabases

ifexists(select*fromsysdatabaseswherename=‘Company')--查詢是否存在Company數(shù)據(jù)庫(kù)

DropdatabaseCompany--如果存在，則刪除數(shù)據(jù)庫(kù)

gocreatedatabaseCompanyOnprimary(pany_data,filename='C:\SQL\Data\Company.mdf',size=10,maxsize=unlimited,filegrowth=10%)L