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

經(jīng)過一段時間的學(xué)習(xí)，也對數(shù)據(jù)庫有了一些認(rèn)識。 數(shù)據(jù)庫基本是由表，關(guān)系，操作組成；對于初學(xué)者首先要學(xué)的： 1.數(shù)據(jù)庫是如何存儲數(shù)據(jù)的 表，約束，觸發(fā)器 2.數(shù)據(jù)庫是如何操作數(shù)據(jù)的 insert，update，delete T-sql 函數(shù) 存儲過程 觸發(fā)器 3.數(shù)據(jù)庫是如何顯示數(shù)據(jù)的 select SQLServer數(shù)據(jù)庫學(xué)習(xí)總結(jié) 1.SQL基礎(chǔ) SQL Server2000安裝、配置，服務(wù)器啟動、停止，企業(yè)管理器、查詢分析器 第一代數(shù)據(jù)庫-網(wǎng)狀數(shù)據(jù)庫和層次數(shù)據(jù)庫；第二代數(shù)據(jù)庫-關(guān)系數(shù)據(jù)庫 數(shù)據(jù)庫（DB）；數(shù)據(jù)庫管理系統(tǒng)（DBMS）；數(shù)據(jù)庫系統(tǒng)（DBS） SQL Server 2000 提供了不同版本：企業(yè)版、標(biāo)準(zhǔn)版、個人版、開發(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；日期和時間：datetime,smalldatetime；貨幣：money,smallmoney 數(shù)據(jù)庫的創(chuàng)建和刪除；數(shù)據(jù)庫表的創(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中得全部列級和表級約束 SQL Server中有5種約束：主鍵約束（Primary Key Constraint）、默認(rèn)約束（Default Constraint）、檢查約束（Check Constraint）、唯一性約束（Unique Constraint）、外鍵約束（Foreign Key Constraint）. 關(guān)系圖 數(shù)據(jù)庫設(shè)計(jì)的步驟：需求分析、概念結(jié)構(gòu)設(shè)計(jì)、邏輯結(jié)構(gòu)設(shè)計(jì)、數(shù)據(jù)庫物理設(shè)計(jì)、數(shù)據(jù)庫實(shí)施、數(shù)據(jù)庫運(yùn)行和維護(hù) 兩個實(shí)體之間的聯(lián)系：一對一（1:1）、一對多（1：n）、多對多（m：n） 實(shí)體關(guān)系模型 - E-R圖 數(shù)據(jù)庫規(guī)范化：將數(shù)據(jù)庫的結(jié)構(gòu)精簡為最簡單的形式；從表中刪除冗余列；標(biāo)識所有依賴于其他數(shù)據(jù)庫的數(shù)據(jù)。 數(shù)據(jù)庫三范式：第一范式就是無重復(fù)的列；第二范式就是非主屬性非部分依賴于主關(guān)鍵字；第三范式就是屬性不依賴于其他非主屬性 2.SQL語句 SQL全稱是“結(jié)構(gòu)化查詢語言（Structured Query Language）” SQL的4個部分： 數(shù)據(jù)定義語言DDL（Data Definition Language）用來定義數(shù)據(jù)的結(jié)構(gòu)：create、alter、drop。 數(shù)據(jù)控制語言DCL（Data Control Language）用來控制數(shù)據(jù)庫組件的存取許可、存取權(quán)限等得命令：grant、revoke。 數(shù)據(jù)操縱語言DML（Data Manipulation Language）用來操縱數(shù)據(jù)庫中得數(shù)據(jù)的命令：insert、update、delete。 數(shù)據(jù)查詢語言DQL（Data Query Language）用來查詢數(shù)據(jù)庫中得數(shù)據(jù)的命令：select。 SQL中得運(yùn)算符：算術(shù)運(yùn)算符、位運(yùn)算符、比較運(yùn)算符、邏輯運(yùn)算符、通配運(yùn)算符、字符串連接符、賦值運(yùn)算符 3.查詢 簡單查詢,使用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) 簡單子查詢：嵌套子查詢、相關(guān)子查詢；子查詢的select語句中不能使用order by子句，roder by子句只能對最終查詢結(jié)果排序。 嵌套子查詢：執(zhí)行過程，先執(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語句首先創(chuàng)建一個新表，然后用查詢的結(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ù) 視圖是由一個或多個數(shù)據(jù)表（基本表）導(dǎo)出的虛擬表或者查詢表，是關(guān)系數(shù)據(jù)庫系統(tǒng)提供給用戶以多種角度觀察數(shù)據(jù)庫中數(shù)據(jù)的重要機(jī)制。 視圖的好處：能夠簡化用戶的操作；視圖能夠?qū)C(jī)密數(shù)據(jù)提供安全保護(hù)。 創(chuàng)建視圖時，視圖的名稱存在sysobjects表中。有關(guān)視圖中所定義列的信息添加到syscolumns表中，而有關(guān)視圖相關(guān)性的信息添加到sysdepends表中。另外，create view語句的文本添加到syscomments表中。 在通過視圖向表中插入數(shù)據(jù)時，如果insert語句列表中包含有視圖中沒有選擇的列和不允許為空值的列，這種操作是不允許的。 創(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 索引提供了一種基于一列或多列的值對表的數(shù)據(jù)行進(jìn)行快速訪問的方法。索引提供的是表中得邏輯順序。 聚集索引基于數(shù)據(jù)行的鍵值在表內(nèi)排序和存儲這些數(shù)據(jù)行。當(dāng)數(shù)據(jù)表以某列為關(guān)鍵字建立聚集索引時，表中得數(shù)據(jù)行就以該列（聚集索引鍵）的排序次序進(jìn)行存儲。每個表只能有一個聚集索引。 非聚集索引具有完全獨(dú)立于數(shù)據(jù)行的結(jié)構(gòu)，一個表可以建立多個非聚集索引。 創(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ī)制，是一個操作序列，它包含了一組數(shù)據(jù)庫操作命令，并且所有的命令作為一個整體一起向系統(tǒng)提交或撤銷操作請求。 事務(wù)的特性：原子性（Atomicity）、一致性（Consistenty）、隔離性（Isolation）、永久性（Durability）。 事務(wù)分類：顯示事務(wù)、隱性事務(wù)、自動提交事務(wù)。 視圖、索引和事務(wù)的創(chuàng)建、使用、修改和刪除 5.TransactSQL編程 全局變量：由系統(tǒng)定義和維護(hù)，其名稱以字符開頭 局部變量：由用戶定義和賦值，其名稱以字符開頭 輸出語句：print 邏輯控制語句：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ù)：絕對值abs(n),向上取整ceiling(n),向下取整floor(n),指定次冪power(n,y),四舍五入round(n,length),求符號sign(n),平方根sqrt(n) 日期和時間函數(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 價格太高 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ī)制。將批操作變成行操作，對結(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 -打開游標(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.存儲過程 存儲過程（stored procedure）類似c語言中的函數(shù)，是一組為了完成特定功能的SQL語句集，經(jīng)編譯后存儲在數(shù)據(jù)庫中。用戶通過指定存儲過程的名字餅給出參數(shù)來執(zhí)行它。 常用的系統(tǒng)存儲過程：sp_database,sp_helpdb,sp_renamedb,sp_tables,sp_column,sp_help,sp_helpconstraint,sp_helpindex,sp_stored_procedure,sp_password 創(chuàng)建存儲過程： 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 使用存儲過程： 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ā)器是一種特殊類型的存儲過程，主要是通過實(shí)踐進(jìn)行觸發(fā)而被執(zhí)行。 觸發(fā)器的主要作用就是能夠?qū)崿F(xiàn)由主鍵和外鍵所不能保證的復(fù)雜的參照完整性和數(shù)據(jù)的一致性。其他功能：強(qiáng)化約束，跟蹤變化，級聯(lián)運(yùn)行，存儲過程調(diào)用。 SQL Server 2000支持兩種類型觸發(fā)器： after觸發(fā)器：要求只有執(zhí)行某一操作之后，觸發(fā)器才被執(zhí)行，且只能在表上定義。 instead of觸發(fā)器：表示并不執(zhí)行其所定義的操作，而僅是執(zhí)行觸發(fā)器本身。既可以在表上定義，也可以在視圖上定義，但對同一操作只能定義一個instead of觸發(fā)器。 工作原理： 當(dāng)觸發(fā)insert觸發(fā)器時，新的數(shù)據(jù)行就會被插入到觸發(fā)器表和inserted表中。觸發(fā)器通過檢查inserted表來確定是否執(zhí)行觸發(fā)器動作或如何執(zhí)行。 當(dāng)在定義有觸發(fā)器的表上執(zhí)行update語句時，原始行被移入到deleted表，更新行被移入inserted表。觸發(fā)器檢查deleted表和inserted表以及被更新的表，來確定是否更新了多行以及如何執(zhí)行觸發(fā)器動作。 當(dāng)觸發(fā)deleted觸發(fā)器后，從受影響的表中刪除的行將被放置到一個特殊的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ù)庫高級管理 SQL Server安全體系結(jié)構(gòu)，4個等級：客戶機(jī)操作系統(tǒng)的安全性，SQL Server的登錄安全性，數(shù)據(jù)庫的使用安全性，數(shù)據(jù)對象的使用安全性 SQL Server驗(yàn)證模式：windows身份驗(yàn)證模式和混合模式（windows身份驗(yàn)證和SQL Server身份