sqlserver學(xué)習(xí)總結(jié).doc

經(jīng)過(guò)一段時(shí)間的學(xué)習(xí)，也對(duì)數(shù)據(jù)庫(kù)有了一些認(rèn)識(shí)。 數(shù)據(jù)庫(kù)基本是由表，關(guān)系，操作組成；對(duì)于初學(xué)者首先要學(xué)的： 1.數(shù)據(jù)庫(kù)是如何存儲(chǔ)數(shù)據(jù)的 表，約束，觸發(fā)器 2.數(shù)據(jù)庫(kù)是如何操作數(shù)據(jù)的 insert，update，delete T-sql 函數(shù) 存儲(chǔ)過(guò)程 觸發(fā)器 3.數(shù)據(jù)庫(kù)是如何顯示數(shù)據(jù)的 select SQLServer數(shù)據(jù)庫(kù)學(xué)習(xí)總結(jié) 1.SQL基礎(chǔ) SQL Server2000安裝、配置，服務(wù)器啟動(dòng)、停止，企業(yè)管理器、查詢分析器 第一代數(shù)據(jù)庫(kù)-網(wǎng)狀數(shù)據(jù)庫(kù)和層次數(shù)據(jù)庫(kù)；第二代數(shù)據(jù)庫(kù)-關(guān)系數(shù)據(jù)庫(kù) 數(shù)據(jù)庫(kù)（DB）；數(shù)據(jù)庫(kù)管理系統(tǒng)（DBMS）；數(shù)據(jù)庫(kù)系統(tǒng)（DBS） SQL Server 2000 提供了不同版本：企業(yè)版、標(biāo)準(zhǔn)版、個(gè)人版、開(kāi)發(fā)版 SQL Server中的數(shù)據(jù)類型：整數(shù)：int,smallint,tinyint,bigint;浮點(diǎn)數(shù)：real,float,decimal;二進(jìn)制：binary,varbinary;邏輯：bit；字符：char,nchar,varchar,nvarchar；文本和圖形：text,ntext,image；日期和時(shí)間：datetime,smalldatetime；貨幣：money,smallmoney 數(shù)據(jù)庫(kù)的創(chuàng)建和刪除；數(shù)據(jù)庫(kù)表的創(chuàng)建、修改和刪除 數(shù)據(jù)完整性：實(shí)體完整性：Primary Key,Unique Key,Unique Index,Identity Column；域完整性：Default,Check,Foreign Key,Data type，Rule；參照完整性：Foreign Key,Check,Triggers,Procedure；用戶定義完整性：Rule,Triggers,Procedure;Create Table中得全部列級(jí)和表級(jí)約束 SQL Server中有5種約束：主鍵約束（Primary Key Constraint）、默認(rèn)約束（Default Constraint）、檢查約束（Check Constraint）、唯一性約束（Unique Constraint）、外鍵約束（Foreign Key Constraint）. 關(guān)系圖 數(shù)據(jù)庫(kù)設(shè)計(jì)的步驟：需求分析、概念結(jié)構(gòu)設(shè)計(jì)、邏輯結(jié)構(gòu)設(shè)計(jì)、數(shù)據(jù)庫(kù)物理設(shè)計(jì)、數(shù)據(jù)庫(kù)實(shí)施、數(shù)據(jù)庫(kù)運(yùn)行和維護(hù) 兩個(gè)實(shí)體之間的聯(lián)系：一對(duì)一（1:1）、一對(duì)多（1：n）、多對(duì)多（m：n） 實(shí)體關(guān)系模型 - E-R圖 數(shù)據(jù)庫(kù)規(guī)范化：將數(shù)據(jù)庫(kù)的結(jié)構(gòu)精簡(jiǎn)為最簡(jiǎn)單的形式；從表中刪除冗余列；標(biāo)識(shí)所有依賴于其他數(shù)據(jù)庫(kù)的數(shù)據(jù)。 數(shù)據(jù)庫(kù)三范式：第一范式就是無(wú)重復(fù)的列；第二范式就是非主屬性非部分依賴于主關(guān)鍵字；第三范式就是屬性不依賴于其他非主屬性 2.SQL語(yǔ)句 SQL全稱是“結(jié)構(gòu)化查詢語(yǔ)言（Structured Query Language）” SQL的4個(gè)部分： 數(shù)據(jù)定義語(yǔ)言DDL（Data Definition Language）用來(lái)定義數(shù)據(jù)的結(jié)構(gòu)：create、alter、drop。 數(shù)據(jù)控制語(yǔ)言DCL（Data Control Language）用來(lái)控制數(shù)據(jù)庫(kù)組件的存取許可、存取權(quán)限等得命令：grant、revoke。 數(shù)據(jù)操縱語(yǔ)言DML（Data Manipulation Language）用來(lái)操縱數(shù)據(jù)庫(kù)中得數(shù)據(jù)的命令：insert、update、delete。 數(shù)據(jù)查詢語(yǔ)言DQL（Data Query Language）用來(lái)查詢數(shù)據(jù)庫(kù)中得數(shù)據(jù)的命令：select。 SQL中得運(yùn)算符：算術(shù)運(yùn)算符、位運(yùn)算符、比較運(yùn)算符、邏輯運(yùn)算符、通配運(yùn)算符、字符串連接符、賦值運(yùn)算符 3.查詢 簡(jiǎn)單查詢,使用TOP子句 查詢結(jié)果排序order by 帶條件的查詢where,使用算術(shù)表達(dá)式，使用邏輯表達(dá)式，使用between關(guān)鍵字，使用in關(guān)鍵字， 模糊查詢like 在查詢中使用聚合函數(shù)：sum(x),avg(x),min(x),max(x),count(x),count(*) 使用分組查詢group by，having子句 distinct關(guān)鍵字 列別名 select top 6 * from sales order by qty desc select au_id,au_fname,au_lname from authors where state in(ks,ca,mi) select au_fname,au_lname,phone from authors where au_id like 72234-% select type,sum(price),avg(price),count(*) from titles group by type having type in(business,psycheology) 簡(jiǎn)單子查詢：嵌套子查詢、相關(guān)子查詢；子查詢的select語(yǔ)句中不能使用order by子句，roder by子句只能對(duì)最終查詢結(jié)果排序。 嵌套子查詢：執(zhí)行過(guò)程，先執(zhí)行子查詢，子查詢得到的結(jié)果不被顯示，而是傳給外層查詢，作為外層查詢的條件，然后執(zhí)行外層查詢，并顯示結(jié)果。 嵌套子查詢的執(zhí)行不依賴于外層查詢，子查詢只執(zhí)行一次。 帶有比較運(yùn)算符的子查詢，帶有in和not in的子查詢，帶有any或all的子查詢 相關(guān)子查詢：子查詢?yōu)橥鈱硬樵兊拿恳恍袌?zhí)行一次，外層查詢將子查詢引用的列的值傳給了子查詢。 相關(guān)子查詢的執(zhí)行依賴于外層查詢，子查詢需要重復(fù)的執(zhí)行。 帶有exists和not exists的相關(guān)子查詢。 多表聯(lián)接查詢：內(nèi)聯(lián)接（inner join）、外聯(lián)接（left、right、full）outer join）、自聯(lián)接（self join）和交叉聯(lián)接（cross join） 在查詢上創(chuàng)建新表：select into語(yǔ)句首先創(chuàng)建一個(gè)新表，然后用查詢的結(jié)果填充新表。 表別名 select coursename from course where courseid in(select distinct courseid from grade where grade10) select studname from student where sudbirthday any (select studbirthday from student where class = 信息系) and class信息系 select studname from student where exists (select * from grade where studid = student.studid and courseid = 01) select stud1.* from student as stud1 join student as stud2 on stud2.studname = mm and stud1.studsex = stud2.studsex select * into girls from student where studsex=m 4.視圖、索引和事務(wù) 視圖是由一個(gè)或多個(gè)數(shù)據(jù)表（基本表）導(dǎo)出的虛擬表或者查詢表，是關(guān)系數(shù)據(jù)庫(kù)系統(tǒng)提供給用戶以多種角度觀察數(shù)據(jù)庫(kù)中數(shù)據(jù)的重要機(jī)制。 視圖的好處：能夠簡(jiǎn)化用戶的操作；視圖能夠?qū)C(jī)密數(shù)據(jù)提供安全保護(hù)。 創(chuàng)建視圖時(shí)，視圖的名稱存在sysobjects表中。有關(guān)視圖中所定義列的信息添加到syscolumns表中，而有關(guān)視圖相關(guān)性的信息添加到sysdepends表中。另外，create view語(yǔ)句的文本添加到syscomments表中。 在通過(guò)視圖向表中插入數(shù)據(jù)時(shí)，如果insert語(yǔ)句列表中包含有視圖中沒(méi)有選擇的列和不允許為空值的列，這種操作是不允許的。 創(chuàng)建視圖：create view view_employee as select emp_id,fname,lname from employee 使用視圖：select * from view_employee 修改視圖：alter view view_employee as select emp_id,fname,job_id from employee where job_id10 刪除視圖：drop veiw view_employee 查看視圖結(jié)構(gòu)：exec sp_help view_employee 查看視圖定義信息：exec sp_helptext view_employee 索引提供了一種基于一列或多列的值對(duì)表的數(shù)據(jù)行進(jìn)行快速訪問(wèn)的方法。索引提供的是表中得邏輯順序。 聚集索引基于數(shù)據(jù)行的鍵值在表內(nèi)排序和存儲(chǔ)這些數(shù)據(jù)行。當(dāng)數(shù)據(jù)表以某列為關(guān)鍵字建立聚集索引時(shí)，表中得數(shù)據(jù)行就以該列（聚集索引鍵）的排序次序進(jìn)行存儲(chǔ)。每個(gè)表只能有一個(gè)聚集索引。 非聚集索引具有完全獨(dú)立于數(shù)據(jù)行的結(jié)構(gòu)，一個(gè)表可以建立多個(gè)非聚集索引。 創(chuàng)建聚集索引：create clustered index studid_ind on stud(studid) 創(chuàng)建非聚集索引：create unique index studfullname_ind on stud(fname desc,lname) 刪除索引：drop index stud.studid_ind 查看stud表上得索引：exec sp_helpindex stud 事務(wù)是一種機(jī)制，是一個(gè)操作序列，它包含了一組數(shù)據(jù)庫(kù)操作命令，并且所有的命令作為一個(gè)整體一起向系統(tǒng)提交或撤銷操作請(qǐng)求。 事務(wù)的特性：原子性（Atomicity）、一致性（Consistenty）、隔離性（Isolation）、永久性（Durability）。 事務(wù)分類：顯示事務(wù)、隱性事務(wù)、自動(dòng)提交事務(wù)。 視圖、索引和事務(wù)的創(chuàng)建、使用、修改和刪除 5.TransactSQL編程 全局變量：由系統(tǒng)定義和維護(hù)，其名稱以字符開(kāi)頭 局部變量：由用戶定義和賦值，其名稱以字符開(kāi)頭 輸出語(yǔ)句：print 邏輯控制語(yǔ)句：begin.end ;break ;case ;continue ; goto ; if.else ;return ; while 常用函數(shù)：行集函數(shù)，聚合函數(shù)，標(biāo)量函數(shù) 轉(zhuǎn)換函數(shù)：convert(dt,e,s),cast() 數(shù)學(xué)函數(shù)：絕對(duì)值abs(n),向上取整ceiling(n),向下取整floor(n),指定次冪power(n,y),四舍五入round(n,length),求符號(hào)sign(n),平方根sqrt(n) 日期和時(shí)間函數(shù)：dateadd(datepart,num,date),datediff(datepart,date1,date2),datename(datepart,date),datepart(datepart,date),getdate(),year(date),month(date),day(date) 字符串函數(shù)：lower(e),upper(e),left(e,i),right(e,i),replace(s1,s2,s3)用3替換1中的2，replicate(e,i)重復(fù)指定次數(shù)，stuff(s1,start,length,s2)用2替換1中指定位置，substring(expression,start,length) 元數(shù)據(jù)函數(shù)：db_id(database_name),db_name(datebase_id),object_id(obj_name),object_name(obj_id),col_length(table,column),col_name(table_id,col_id) 聚合函數(shù)：avg(expr),count(expr),count(*),max(expr),min(expr),sum(expr) select au_lname,au_fname,contory = case state when ut then utah when ca then california else world end,city from authors order by state desc while(select avg(price) from titles)50 break else continue end print 價(jià)格太高 begin insert into jobs values(a,80,234) if error0 print 數(shù)據(jù)插入失敗 else goto M end M:print 數(shù)據(jù)插入成功 6.游標(biāo) 游標(biāo)是一種能從包含多條數(shù)據(jù)記錄的結(jié)果集中每次提取一條記錄的機(jī)制。將批操作變成行操作，對(duì)結(jié)果集中得某行進(jìn)行操作。 declare author_csr cursor read_only for -定義只讀游標(biāo) select au_fname,au_lname from authors where state = ca order by au_fname,au_lname declare lname varchar(20),fname varchar(20) -定義變量 open author_csr -打開(kāi)游標(biāo) fetch next from author_csr into lname,fname -執(zhí)行一次數(shù)據(jù)讀取操作 while fetch_status=0 -循環(huán)游標(biāo)讀取數(shù)據(jù) begin print author name:+lname+fname fetch next from author_csr into lname,fname end close author_csr -關(guān)閉游標(biāo) deallocate author_csr -釋放游標(biāo) 7.存儲(chǔ)過(guò)程 存儲(chǔ)過(guò)程（stored procedure）類似c語(yǔ)言中的函數(shù)，是一組為了完成特定功能的SQL語(yǔ)句集，經(jīng)編譯后存儲(chǔ)在數(shù)據(jù)庫(kù)中。用戶通過(guò)指定存儲(chǔ)過(guò)程的名字餅給出參數(shù)來(lái)執(zhí)行它。 常用的系統(tǒng)存儲(chǔ)過(guò)程：sp_database,sp_helpdb,sp_renamedb,sp_tables,sp_column,sp_help,sp_helpconstraint,sp_helpindex,sp_stored_procedure,sp_password 創(chuàng)建存儲(chǔ)過(guò)程： create procedure book_num (book_name varchar(26),starttime datetime,endtime datetime,total int output) as select total=count(jy.askbookid) from book,jyls jy where bookname like book_name and book.isbn=jy.isbn and jy.starttime=starttime and endtime=endtime 使用存儲(chǔ)過(guò)程： declare book_name char(26),total int set book_name=面向?qū)ο蠓治龊驮O(shè)計(jì) exec book_num book_name,2007-01-01,2007-11-01,total output select book_name as bookname,total as num 8.觸發(fā)器 觸發(fā)器是一種特殊類型的存儲(chǔ)過(guò)程，主要是通過(guò)實(shí)踐進(jìn)行觸發(fā)而被執(zhí)行。 觸發(fā)器的主要作用就是能夠?qū)崿F(xiàn)由主鍵和外鍵所不能保證的復(fù)雜的參照完整性和數(shù)據(jù)的一致性。其他功能：強(qiáng)化約束，跟蹤變化，級(jí)聯(lián)運(yùn)行，存儲(chǔ)過(guò)程調(diào)用。 SQL Server 2000支持兩種類型觸發(fā)器： after觸發(fā)器：要求只有執(zhí)行某一操作之后，觸發(fā)器才被執(zhí)行，且只能在表上定義。 instead of觸發(fā)器：表示并不執(zhí)行其所定義的操作，而僅是執(zhí)行觸發(fā)器本身。既可以在表上定義，也可以在視圖上定義，但對(duì)同一操作只能定義一個(gè)instead of觸發(fā)器。 工作原理： 當(dāng)觸發(fā)insert觸發(fā)器時(shí)，新的數(shù)據(jù)行就會(huì)被插入到觸發(fā)器表和inserted表中。觸發(fā)器通過(guò)檢查inserted表來(lái)確定是否執(zhí)行觸發(fā)器動(dòng)作或如何執(zhí)行。 當(dāng)在定義有觸發(fā)器的表上執(zhí)行update語(yǔ)句時(shí)，原始行被移入到deleted表，更新行被移入inserted表。觸發(fā)器檢查deleted表和inserted表以及被更新的表，來(lái)確定是否更新了多行以及如何執(zhí)行觸發(fā)器動(dòng)作。 當(dāng)觸發(fā)deleted觸發(fā)器后，從受影響的表中刪除的行將被放置到一個(gè)特殊的deleted表中。 create trigger update_smoke_t_sale on smoke_t_sale for update as declare newsalenum int,smokeproductname varchar(40) select newsalenum= salenum from inserted select smokeproductname=smokeproductname from inserted if update(salenum) -判斷是否更新 begin update smoke_t_sale set saletotalprice=newsalenum * saleprice where smokeproductname=smokeproductname insert into smoke_log(logContent) values(更新成功) end else print 未更新 9.數(shù)據(jù)庫(kù)高級(jí)管理 SQL Server安全體系結(jié)構(gòu)，4個(gè)等級(jí)：客戶機(jī)操作系統(tǒng)的安全性，SQL Server的登錄安全性，數(shù)據(jù)庫(kù)的使用安全性，數(shù)據(jù)對(duì)象的使用安全性 SQL Server驗(yàn)證模式：windows身份驗(yàn)證模式和混合模式（windows身份驗(yàn)證和SQL Server身份