SQL期末考試復(fù)習(xí)資料.docx

1、第一童數(shù)據(jù)庫技術(shù)發(fā)展的三個階段人工管理階段、文件系統(tǒng)階段、數(shù)據(jù)庫系統(tǒng)階段數(shù)據(jù)庫的基本概念Data Base 數(shù)據(jù)庫是為了實現(xiàn)一定的目的、按某種規(guī)則組織起來的數(shù)據(jù)的”集合 , 簡而言之, 它是有結(jié)構(gòu)的數(shù)據(jù)集合。數(shù)據(jù)庫系統(tǒng)的三級模式結(jié)構(gòu)( 考 )外模式、模式、內(nèi)模式外模式 / 模式映射保證邏輯獨立性模式 / 內(nèi)模式映射保證物理獨立性數(shù)據(jù)模型的要素及其特點層次模型、網(wǎng)狀模型、關(guān)系模型要素：數(shù)據(jù)結(jié)構(gòu):數(shù)據(jù)操作: 關(guān)系代數(shù)、關(guān)系演算、具有關(guān)系代數(shù)和關(guān)系演算雙重特點的語言( SQL )完整性約束：實體完整性、參照完整性和用戶定義的完整性關(guān)系的特點：1 、 每列數(shù)據(jù)項不可再分解2、同一個關(guān)系中，不能出現(xiàn)相同

2、的屬性名3 、 同一個關(guān)系中, 不能有完全相同的元組4 、 所有元組格式相同, 長度相同；屬性類型相同5 、行列順序無關(guān)緊要 關(guān)系的完整性（插入）：實體完整性、參照完整性關(guān)系的完整性（刪除）：參照完整性規(guī)則關(guān)系的完整性（修改）二刪除+插入實體之間的關(guān)系：（1 ）一對一關(guān)系（2） 一對多關(guān)系（3） 多多關(guān)系常見數(shù)據(jù)庫：fox pro 、 access, sql server, mybase 、 oracle第章SQL Server 2008 新特性 : 可信、高效率、智能SQL （結(jié)構(gòu)化查詢語言）:數(shù)據(jù)定義語言（DDL ） CREATE. DROP 、 ALTER數(shù)據(jù)操作語言（DML） SELE

3、CT、 INSERT、 UPDATE. DELETE數(shù)據(jù)控制語言（DCL ） GRANT. REVOKEr Create Table/Alter Table/Drop Table-定義基本表(DDL 彳 Create View/Drop View :視圖操作Create Index/Drop Index :索引操作r InsertDeleteSelect< UpdateGrant匚 Revoke嵌入式SQL的使用規(guī)定DMLDCL記錄操作權(quán)限管理易學(xué)易用特點：一體化、高度非過程化、以同一種語法格式提供兩種使用方式、語言簡捷SQL標(biāo)識符1.標(biāo)識符的首字母必須是以下兩種情況之一所有在統(tǒng)一碼（U

4、nicode）2.0 標(biāo)準(zhǔn) 規(guī)定的字符，包括26個英文字母a-z和A-Z,以及其他一些語言字符,如漢字。下劃線 _、/或 # # 。2.后續(xù)字符：除首字母約定外,還有數(shù)字字符及$"。3 .不允許是SQL的保留字4 .不允許嵌入空格或其他特殊字符 （ #、#、） 常規(guī)標(biāo)識符：符合標(biāo)識符的格式規(guī)則 分隔標(biāo)識符：使用了雙引號（ “）或方括號（口）等分隔符號，既可以遵守標(biāo)識 符命名規(guī)則, 也可以不遵守標(biāo)識符命名規(guī)則第 3 章創(chuàng)建和管理數(shù)據(jù)庫數(shù)據(jù)庫：是按照數(shù)據(jù)結(jié)構(gòu)來組織、存儲、和管理數(shù)據(jù)的倉庫，是存儲在一起的相關(guān)數(shù)據(jù)的集合。SQL Server2008 的數(shù)據(jù)庫對象主要包括表( 視索引 ( i

5、ndex 存儲過程 ( stored procedure )、觸發(fā)器 ( trigger) 和約束 ( constraint) 等。SQL Server 2008 數(shù)據(jù)庫主要由文件和文件組組成。文件主要分為3 類：主要數(shù)據(jù)文件、次要數(shù)據(jù)文件和事務(wù)日志文件。文件組主要分為兩種類型：主文件組和用戶定義文件組。數(shù)據(jù)庫對象的全名應(yīng)該由服務(wù)器名、數(shù)據(jù)庫名、擁有者名和對象名兩種類型的實例：默認實例和命名實例(計算機名稱實例名稱)數(shù)據(jù)庫操作( 使用 SSMS 和 T-SQL 創(chuàng)建、修改、刪除數(shù)據(jù)庫)新創(chuàng)建的數(shù)據(jù)庫，系統(tǒng)對數(shù)據(jù)文件的默認值為：初始大小3MB, 最大大小不限制，增量為1 MB 。對日志文件的默認

6、值為：初始大小1MB , 最大大小不限制，增長方式為按10% 比例增長。DROP DATABASE數(shù)據(jù)表操作（ 使用 SSMS 和 SQL 創(chuàng)建、修改、刪除數(shù)據(jù)表）createdatabase userdblon（name=userdb4_data ffilename= 1 E:sql_datauserdb4.mdf 1 ,size=5,maxsize=10,filegrowth=l logonname=userdb4_log ffilename= 1 E:sql_datauserdb4.ldf! ,size=2 ,maxsize=5 , filegrowth=l)創(chuàng)建、修改、刪除約束( 根據(jù)

7、完整性規(guī)則應(yīng)用SSMS 和 T-SQL 設(shè)置表的主碼、約束和外碼等)添加日志文件：use booksys- 使用數(shù)據(jù)庫alterdatabase booksys- 修改數(shù)據(jù)庫addlogfile - 添加 I 志文件 (name-booksys_ldf,filename= 1 I:sql_datatestlbooksys.ldf1 ,size=2mb, maxsize=10mb a filegrowth=2mb ) tofilegroup 文件組名一將文件添加到文件組中添加次要數(shù)據(jù)文件：use booksys alterdatabase booksys addfile(name=booksys

8、,filename= 1 I:sql_datatest1booksys.ndf1zsize-2mb,maxsize=10mb,filegrowth=2mb)添加文件組：Al. terdatabase 數(shù)據(jù)庫乞Addfilegroup 文件組名刪除文件：Al t erdat abaremove file文件名刪除數(shù)據(jù)庫：Drop 數(shù)據(jù)庫名 創(chuàng)建表：use booksyscreatetable bookbookid varchar (20 ) primarykey, - 唯 - 喲束 editor char (8)notnull,- price numeric (5,2) check (price

9、>20)主鍵 bookname varchar (60) unique,非空約束, - 檢查約束publish varchar (30)default ( ' 電子無斗人'), 一一默認彳肖publishdata d&tetime,qty int,foreignkey ( 字段 ) references 參照表名( 字段主鍵)修改列：ALTER teble 表名(ADD列名類型約束，I ALTERCOLUMN 列名數(shù)據(jù)類型約束，I DROPCOLUMN 歹 M添加約束altertable borrow- 添加主鍵約束reader(cardid)addconstra

10、int pk_id primarykey(bookid,cardid,bdate)添加外鍵約束altertable borrowaddconstraint fk_card foreignkey(cardid)references檢查約束ALTERTABLE studentADDCONSTRAINT CHECK_sex CHECK(sex= 11 or sex =' 女)默認值A(chǔ)LTERTABLE studentADDCONSTRAINT df_sp DEFAULT f HK 1 FOR 歹 U 名唯一約束ALTERTABLE studentADDCONSTRAINT uq_ID UNI

11、QUE (ID)刪除約束：ALTERTABLE 衣名DROP約束名常見的約束：1、 、 NULL/NOT NULL : 空甸非空值約束。2、 PRIMARY KEY : 主鍵約束 ， 等價非空、單值。3、 CHECK （邏輯表達式）：檢查約束。4、 DEFAULT 常量表達式: 默認值約束。5、 UNIQUE: 單值約束。6、 foreign key REFERENCES 父表名（主鍵）：外鍵約束刪除表droptable 表名數(shù)據(jù)操作（使用SSMS 和 SQL 添加、修改、刪除數(shù)據(jù)）* TP2001-001', )* 數(shù)據(jù)結(jié)構(gòu)J ，李國慶J *22.00*, ，清插入數(shù)據(jù)inserti

12、nto book values華大 學(xué)出版社- *2001-1-8 * , 20更新數(shù)據(jù)update student set classno=105 1 where spno= 103 1103 1刪除數(shù)據(jù)delete student where spno=刪除列前必須先刪除其上的約束第四章T-SQL編程與應(yīng)用DECLARE （考）DECLARE磔量多數(shù)據(jù)類型， 磔量名數(shù)據(jù)類型，n 通配符（考）通配符（下劃線）描述示例包含零個或更多字符的任意 字符串。WHERE title LIKE ' Wcoinputer%'將查找處于書名任意位置的包 含單詞computer 的所有書名c任

13、何單個字符指定范圍（a-f） 或集臺（abcdef）中的任 何單個字符。WHERE au fname LIKE ' ean J 將 查找以ean結(jié)尾的所有4個 字母的名孑（Dean、Sean等）v = WHERE au_lname LIKEC-P arsen* 將查找以 arsen 結(jié) 尾且以介于C與P之間的 任何單個字 符開始的作者姓氏，例如.Carsen、Larsen、Karsen 等。上課例子：不屬于指定范（a-f）或集合（Labe def j ）的任何單個子付。WHERE auNname LIKE '將查找以de開始且其W的字母不為1的所有作者的姓氏。SELECT *

14、FROM 數(shù)據(jù)表 WHERE 編號 LIKE' 00 A8%A-D%#可能會查出的編號值為:A、006SABCB、 007SFFGC、 08ASXD、 005B運算符優(yōu)先級當(dāng)一個復(fù)雜表達式中包含有多個運算符時，運算符的優(yōu)先級決定了表達式計算和比較操作的先后順序。運算符的優(yōu)先級由高到低的順序如下：1 ）+ （正）- （ 負） （位反）2 2） * （乘）/ （除）% （取余）（3 ） + （加）+ （字符串串聯(lián)運算符）（減）（4）二、二、二、！二、！ 、！ v （比較運算符）5 ） A （按位異或）& （按位與）| （按位或）6 ） NOT7 ) AND8 ) ALL ANY B

15、ETWEEN IN LIKE OR SOME (邏輯運算符) （ 9 ）二（賦值）第五章數(shù)據(jù)查詢（設(shè)計題一定考）查詢語法格式（考）SELECT 列名INTO 新農(nóng)名FROMC表或視圖WHEREI輯表達式一列級條件GROUPBY 列多HAVING 邏輯表達式一組級條件ORDERBY歹U名asc升序，desc降序常用的聚合函數(shù)（考）sum （）Avg （）Max （）Min （）Count （）Count （ distinct ）GROUP BY 注意事項（ 1 ）在 SELECT 子句的字段列表中，除了聚集函數(shù)外，其他所出現(xiàn)的字段一定要在 GROUP BY 子句中有定義才行（ 2 ） SELEC

16、T 子句的字段列表中不一定要有聚集函數(shù)旦至少要用到GROUP BY子句列表中的一個項目(3 )在5、1 Server中text、ntext和image數(shù)據(jù)類型的字段不能作為 GROUPBY子句的分組依據(jù)。(4 ) GROUP BY 子句不能使用字段別名。交叉連接(笛卡爾積)SELECT fieldlist FROM tablel,table2SELECT fieldlist FROM tablel crossJOIN table2連接SELECT fieldlistFROM tablel INNER | LEFT | RIGHT| FULL JOIN table2ON tablel.colum

17、n=table2.columnINNER JOIN:如果表中有至少一個匹配，則返回行LEFT JOIN:即使右表中沒有匹配，也從左表返回所有的行RIGHT JOIN:即使左表中沒有匹配，也從右表返回所有的行FULL JOIN:只要其中一個表中存在匹配，就返回行子查詢(IN比較運算符EXISTSUNION)(考 選擇、填空)UNION操作符用于合并兩個或多個 SELECT語句的結(jié)果集。UNION內(nèi)部的SELECT語句必須擁有相同數(shù)量的列。列也必須擁有相似的數(shù)據(jù)類型。同時，每條SELECT語句中的列的順序必須相同。Between :列名 > 二開始值and列名 < 二結(jié)束值(考)(1)

18、 NOT:不滿足條件(2) AND :滿足所有條件(3) OR :滿足其中任意一個條件DISTINCT 消除重復(fù)記錄（考 ）弟八早二級存儲映象三級模式SQLSERVER 數(shù)據(jù)庫對象Transact-SQL DDL外模式視圖（ VIEW）邏輯獨立性模式基本表（ TABLE ）物理獨立性內(nèi)模式數(shù)據(jù)庫（ DATABASE）CREATE VIEWCREATE TABLECREATE DATABASE（考）視圖 （ View ） 是一個虛擬表。視圖作用（考多選題）視圖能簡化用戶的操作、提高安全性、邏輯數(shù)據(jù)獨立性修改數(shù)據(jù)注意事項（不能通過視圖修改數(shù)據(jù)）:? Select 列表中含有Distinct? Se

19、lect 列表中含有表達式，如計算列?在from 子句中引用多個表?引用不可更新的視圖? GROUP BY 或 Having 子句?視圖中不含非空列函數(shù)等創(chuàng)建視圖：（考）CREATEVIEW view_nameAS select_statement修改視圖：ALTERVIEW view_name AS select_statement刪除視圖：DROPVIEW視圖名視圖添加數(shù)據(jù)Insert into視圖名values （列值，列值，列值 n）視圖更新數(shù)據(jù)update 視圖名 set 列 =列值，列=列值Where 條件表達式視圖刪除數(shù)據(jù)DELETEFROM視圖名WHEREI輯表達式第七章索引與

20、數(shù)據(jù)完整性索引優(yōu)點（考）提高數(shù)據(jù)庫的查找速度、保證列的惟一性、加快表與表之間的連接速度、減少查詢中分組和排序的時間、檢索數(shù)據(jù)的過程中使用優(yōu)化隱藏器，提高系統(tǒng)性能索引類型（考）存儲結(jié)構(gòu)：聚集索引（物理順序與索引順序相同、_個表一個）和非聚集索引（物理順序與索引順序不相同、一個表可有多個）數(shù)據(jù)唯一性: 唯一索引 （ UNIQUEIndex ） 和非唯一索引 （ Nonunique Index ）唯一索引是指索引值必須是唯一的, 不允許數(shù)據(jù)表中具有兩行相同的索引值.鍵列的個數(shù)：單列索引和多列索引索引分類（考）聚集索引、非聚集索引、唯一索引、索引視圖、全文索引 關(guān)鍵字- 唯 -S 聚集，非聚集CREA

21、TEUNIQUE CLUSTERED | NONCLUSTEREDINDEX 索弓 | 名ON 表（視圖）名（列名ASCIDESC）刪除索引dropindex 表名 . 索弓 I 名數(shù)據(jù)完整性（ 1 ） 數(shù)據(jù)類型準(zhǔn)確無誤。（ 2 ） 數(shù)據(jù)取值符合規(guī)定的范圍。（3 ） 多個數(shù)據(jù)表之間的數(shù)據(jù)不存在沖突。數(shù)據(jù)完整性機制（ 考 ）( 行 1 域完整性( 列 1 引用完整性( 表與表 1 用戶定義完整性以下為了解即可, 不知道考不考(1) 唯一索引 (Unique Index) 。(2) 主鍵 (Primary Key) 。(3) 唯一碼(Unique Key) 。(4) 標(biāo)識列(Identity Co

22、lumn)。域完整性常見的實現(xiàn)機制包括以下幾點(1) 默認值(Default) o(2) 檢查(Check) 。(3) 外鍵(Foreign Key)。(4) 數(shù)據(jù)類型 (Data Type) 。(5) 規(guī)則(Rule) o(1) 當(dāng)主表中沒有關(guān)聯(lián)的記錄時，將記錄添加到相關(guān)表中。(2) 更改主表中的值并導(dǎo)致相關(guān)表中的記錄孤立。(3) 從主表中刪除記錄，但仍存在與該記錄匹配的相關(guān)記錄。(1) 規(guī)則 (Rule) o(2) 觸發(fā)器 (Trigger) 。(3) 存儲過程 (Stored Procedure) 。(4) 創(chuàng)建數(shù)據(jù)表時的所有約束(Constraint) 存儲過程優(yōu)點（ 考選擇題）允許標(biāo)

23、準(zhǔn)組件式編程, 可以嵌套使用, 增強重用性和共享性可以接受并使用參數(shù)動態(tài)執(zhí)行其中的SQL 語句比一般的SQL 語句執(zhí)行速度快具有安全特性允許模塊俯辯設(shè)計減少網(wǎng)絡(luò)通信流量可以強制應(yīng)用程序的安全性存儲過程類別（ 考 ）系統(tǒng)提供的存儲過程、用戶自走義存儲過程和擴展存儲過程。創(chuàng)建語法CREATEPROCEDURE proc procedure_name 參數(shù) with recompile （ 重新編譯） |encryption （ 加密 ）（ 考 ） As sql 語句調(diào)用方法Exec 存儲過程名參數(shù)變量傳值的調(diào)用方法：declare變量名數(shù)據(jù)類型set 變量名Exec procedure_name變

24、量名參數(shù)修改存儲過程:alterPROCEDURE proc procedure_nameAs sq _L語句刪除存儲過程dropproc 存儲過程名獲得存儲過程信息：sp_helptext查看存儲過程的相關(guān)性信息：sp.depends查看存儲過程的一般信息：sp_help例子：createproc p_stu_avgstu_id char (10),avg_cj intoutputasselect avg_cj=avg(chengji)from xuankewhere xkid=stu_iddeclare cj intBS 平均成績exec p_stu_avg stu_id= 109002

25、1 r avg_cj=cj output select c j觸發(fā)器 ： trigger觸發(fā)器分為DML 觸發(fā)器和DDL 觸發(fā)器。DML蟲發(fā)器可以分為如下5種類型。UPDATED2) INSERT蛾器DELETE蟲發(fā)器INSTEAD OF蟲發(fā)器根據(jù)觸發(fā)的方式AFTER蟲發(fā)器創(chuàng)建 DM L 觸發(fā)器語法：CREATETRIGGER schema_name ? trigger_nameONtable|viewFOR| AFTER |INSTEADOF INSERT , UPDATE , DELETE ASsql_statementDDL 觸發(fā)器只能在觸發(fā)事件發(fā)生后才會調(diào)用執(zhí)行，即它只能是AFTER

26、觸發(fā)器。創(chuàng)建 DDL 觸發(fā)器語句（無設(shè)計題）CREATETRIGGER trigger_nameONALLSERVER I DATABASEWith encryptionFORI AFTER 觸發(fā)的事件類型AS sql_statement刪除觸發(fā)器DROPTRIGGER觸發(fā)器名管理觸發(fā)器修改觸發(fā)器、刪除觸發(fā)器、禁用（啟用）觸發(fā)器第九章事務(wù)系統(tǒng)函數(shù)：標(biāo)量函數(shù)，聚合函數(shù)，行集函數(shù)。（考）三種用戶自定義函數(shù)：標(biāo)量函數(shù)：返回單個數(shù)據(jù)值。（考設(shè)計題）內(nèi)連表值函數(shù)：返回值是一個記錄集合表。在此函數(shù)中 z return 語句包含條單獨的select 語句。多語句表值函數(shù)：返回值是由選擇的結(jié)果構(gòu)成的記錄集。函

27、數(shù)不能有輸出參數(shù)，存儲過程可以有輸出參數(shù)。創(chuàng)建函數(shù)語法：（考設(shè)計題）createfunction 函數(shù)名（形參名數(shù)據(jù)類型,形參名數(shù)據(jù)類型n ） returns 返回值數(shù)據(jù)類型as begin命令行或程序塊（必須有Return 子句）End調(diào)用函數(shù)：Print dbo. 函數(shù)（實參）或select dbo. 函數(shù)（實參）刪除自定義函數(shù)dropfunction 函數(shù)名Pivot（ 必考）select sex, 信息學(xué)院計算機學(xué)院機械學(xué)院from （ select a.studentID,a.sex a.speciality fromstudent a ） as tpivot （ count （ s

28、tudentID ） for speciality in （信息學(xué)院，計算機學(xué)院機械學(xué)院）as b事務(wù)屬性：原子性（Atomicity ）、一致性（ Consistency ） 、隔離線 （ Isolation ） .持久性 （ Durability ）（ 考）事務(wù)管理主要使用四條語句（考）1. BEGIN TRANSACTION （事務(wù)的起點）2. COMMIT TRANSACTION （ 事務(wù)的終點）3. ROLLBACK TRANSACTION （ 事務(wù)回滾 ）4. SAVE TRANSACTION （ 事務(wù)保存點）類型：系統(tǒng)提供事務(wù)、用戶定義事務(wù)（考）模式 : 自動提交事務(wù)、顯式事務(wù)、

29、隱性事務(wù)（考）并發(fā)問題：丟失更新、未確認的相關(guān)性（臟讀）、不一致的分析（不可重復(fù)讀X 幻象讀死鎖條件：互斥條件、請求與保持條件、不剝奪條件、循環(huán)等待條件鎖：共享鎖S、獨占鎖X、更新鎖U （考）第十章SQL Server 的安全性管理是建立在驗證( authentication ) 和訪問許可(permission ) 這兩種機制上的。SQL Server 2008 的驗證方式包括Windows 驗證模式與混合驗證模式.SQL Server 的安全模型中包括以下幾部分：? SQL Server 身份驗證；?登錄賬戶；?數(shù)據(jù)庫用戶；?角色；?權(quán)限在 SQL Server 中 , 賬號有兩種：一種是

30、登錄服務(wù)器的登錄賬號, 另外一種就是使用數(shù)據(jù)庫的用戶賬號.創(chuàng)建登陸名：createlogin登錄名 with password 密碼，修改登錄名：alterloginA:錄名 with password= z 密碼，刪除登錄名：droplogin 登錄名課后作業(yè)- 作業(yè)二- -IN顯 zjxbook 表中所有書號、書名和出版社select bookid,bookname,publish from book- 2、 顯示所有的書目(DISTINCT)selectdistinet bo oknmme from book orderby bookname一 3、顯示年以后出版的書select*fro

31、m book where(year)publishdate)>2001- 4 、顯示讀者信息表中所有計算機系的男同學(xué)的信息1select*f rom reader where dept=1 計算機系1 and sex= ' 男- -5 、 顯示所有編者姓劉的圖書情況select* from reader wherename1ike' 夫 U% '- -6、將b o o k 中的I!;籍信息按出版時間先后顯示select*from book orderby publishdate ASC- 7、 列出在 book 表中總共有多少種書selectcount(booki

32、d)from book- 8. 列出水利水電出版社出版的書的種數(shù)SELECT bookname FROM bookwhere publish= ，水利水電出版社，GROUPBY bookname- -9 、 查找最貴和最便宜的書的價格SELECTmax(price)FROM bookSELECTmin(price)FROM book- 10 、各類書的總庫存select booknmme, count (qty) ms總庫存 from book groupby bookname- IK 統(tǒng)計各出版社的圖書庫存總量select publish, count (qty)BS 總庫存 from book groupb