《SQL SERVER 數(shù)據(jù)庫培訓教程》

SQL SERVER 數(shù)據(jù)庫 使用,2011-02-14 北京思路創(chuàng)新科技有限公司,SQL SERVER 數(shù)據(jù)庫基礎(chǔ) 創(chuàng)建和管理數(shù)據(jù)庫 創(chuàng)建和管理數(shù)據(jù)表 增、刪、改數(shù)據(jù) 數(shù)據(jù)檢索 創(chuàng)建和管理視圖 T-SQL編程 創(chuàng)建和管理存儲過程,內(nèi) 容,SQL SERVER 數(shù)據(jù)庫,SQL Server 2005是一種高性能的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，該系統(tǒng)是一個全面的數(shù)據(jù)庫平臺，使用集成的商業(yè)智能工具提供企業(yè)級的數(shù)據(jù)管理，可以為不同規(guī)模的企業(yè)提供不同的數(shù)據(jù)解決管理方案。 SQL Server 2005是企業(yè)信息系統(tǒng)客戶服務(wù)器體系結(jié)構(gòu)的首選產(chǎn)品之一。,4,SQL Server 2005的性能與體系結(jié)構(gòu),2.1.2 SQL Server 2005的體系結(jié)構(gòu) 1、CS結(jié)構(gòu) 2、服務(wù)器端是安裝了SQL Server 2005服務(wù)器組件的計算機。 3、客戶端是安裝了SQL Native Client的計算機,SQL SERVER 數(shù)據(jù)庫安裝,SQL SERVER 2005 安裝教程,數(shù)據(jù)庫存儲結(jié)構(gòu),數(shù)據(jù)庫存儲結(jié)構(gòu),系統(tǒng)數(shù)據(jù)庫 SQL Server 2005包含master、model、msdb、 tempdb4個系統(tǒng)數(shù)據(jù)庫。,master： 數(shù)據(jù)庫記錄SQL Server 2005實例的所有系統(tǒng)級信息。master數(shù)據(jù)庫還記錄所有其他數(shù)據(jù)庫是否存在以及這些數(shù)據(jù)庫文件的位置。另外，master還記錄了SQL Server的初始化信息。,Model： 數(shù)據(jù)庫用作SQL Server 2005實例上創(chuàng)建的所有數(shù)據(jù)庫的模板。如果修改model數(shù)據(jù)庫，之后創(chuàng)建的所有數(shù)據(jù)庫都將繼承這些修改。,msdb ： msdb數(shù)據(jù)庫由SQL Server代理用來計劃警報和作業(yè)。,Tempdb： 數(shù)據(jù)庫是連接到SQL Server 2005實例的所有用戶都可用的全局資源，它保存所有臨時表和臨時存儲過程。另外，它還用來滿足所有其他臨時存儲要求。,啟動、停止數(shù)據(jù)庫服務(wù),9,SQL Server 的安全性,SQL server 2005使用兩層安全機制來確認用戶的有效性-即身份驗證和權(quán)限驗證兩個階段。 1.身份驗證模式 Windows認證： 用戶對SQL server 訪問的控制由Windows賬號或用戶組完成，當進行連接時，用戶不需要提供SQL server 登陸賬號。 SQL server認證： SQL server管理員必須建立SQL server 登陸賬號和口令。當用戶要連接到SQL server 時，必須同時提供SQL server 的登陸賬號和口令。 混合認證模式：兩種模式同時工作，用戶即能使用Windows 2005認證模式又能使用SQL Server認證模式連接到SQL Server服務(wù)器。,10,SQL Server 2005的安全性,2.權(quán)限驗證 身份驗證成功，連接到SQL Server數(shù)據(jù)庫后，用戶必須使用特定的用戶賬號才能對數(shù)據(jù)庫進行訪問。 SQL Server采取三個步驟來確認權(quán)限。 1)當用戶執(zhí)行一項操作時，例如用戶執(zhí)行了一條插入一條記錄的指令，客戶端將用戶T-SQL 語句發(fā)給SQL Server。 2)當SQL Server接收到該命令語句后，立即檢查該用戶是否有執(zhí)行這條指令的權(quán)限。 3)如果用戶具備這個權(quán)限，SQL Server將完成相應(yīng)的操作，如果用戶沒有這個權(quán)限，SQL Server系統(tǒng)將返回一個錯誤給用戶。,管理數(shù)據(jù)庫,在開始菜單中打開“所有程序Microsoft SQL Server 2005 SQL Server Management Studio”，如現(xiàn)如下登錄頁面：,SQL SERVER 數(shù)據(jù)庫基礎(chǔ) 創(chuàng)建和管理數(shù)據(jù)庫 創(chuàng)建和管理數(shù)據(jù)表 增、刪、改數(shù)據(jù) 數(shù)據(jù)檢索 創(chuàng)建和管理視圖 T-SQL編程 創(chuàng)建和管理存儲過程,內(nèi) 容,SQL Server 是Microsoft公司推出的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)。從SQL Server 1.0版到目前的SQL Server 2008 R2 經(jīng)歷了20年的發(fā)展過程。 1988年第一次提出SQL Server 產(chǎn)品名稱 1989年SQL Server 1.0版本發(fā)布 1990年SQL Server 1.1版本發(fā)布 1991年SQL Server 1.11版本發(fā)布 1995年SQL Server 6.5版本發(fā)布 1998年SQL Server 7.0版本發(fā)布 2000年SQL Server 2000版本發(fā)布 2005年SQL Server 2005版本發(fā)布 2008年SQL Server 2008版本發(fā)布 2010年SQL Server 2008 R2版本發(fā)布,2.創(chuàng)建和管理數(shù)據(jù)庫,創(chuàng)建數(shù)據(jù)庫,若要創(chuàng)建數(shù)據(jù)庫，必須確定數(shù)據(jù)庫的名稱、所有 者、大小以及存儲該數(shù)據(jù)庫的文件或文件組。 創(chuàng)建數(shù)據(jù)庫時，根據(jù)數(shù)據(jù)庫中預(yù)期的最大數(shù)據(jù)量， 應(yīng)創(chuàng)建盡可能大的數(shù)據(jù)文件 創(chuàng)建數(shù)據(jù)庫有兩種方式： .使用腳本創(chuàng)建數(shù)據(jù)庫 .使用圖形界面創(chuàng)建數(shù)據(jù)庫,使用腳本創(chuàng)建數(shù)據(jù)庫,語法：,CREATE DATABASE database_name ON PRIMARY ,.n , ,.n LOG ON ,.n COLLATE collation_name ; := ( NAME=logical_file_name , FILENAME = os_file_name , SIZE=size KB|MB|GB|TB , MAXSIZE=max_size KB|MB|GB|TB | UNLIMITED , FILEGROWTH =growth_increment KB|MB|GB|TB|% ) ,.n := FILEGROUP filegroup_name DEFAULT ,.n ,Database_name:數(shù)據(jù)庫名稱，必須唯一 NAME=logical_file_name 指定文件的邏輯名稱 FILENAME= os_file_name 指定操作系統(tǒng)（物理）文件名稱,1.使用默認設(shè)置創(chuàng)建數(shù)據(jù)庫 CREATE DATABASE DBTEST 2.創(chuàng)建自定義數(shù)據(jù)路徑的數(shù)據(jù)庫 CREATE DATABASE DBTEST2 ON ( NAME=DBTEST3, FileNAME=c:DBTEST02.mdf,size=5MB ),在圖形界面下創(chuàng)建數(shù)據(jù)庫,在“Microsoft SQL Server Management Studio”中創(chuàng)建數(shù)據(jù)庫的過程如下。 啟動“Microsoft SQL Server Management Studio”，在“數(shù)據(jù)庫”節(jié)點上右擊，選擇快捷菜單中的“新建數(shù)據(jù)庫”命令。彈出“新建數(shù)據(jù)庫”對話框，在“常規(guī)”數(shù)據(jù)頁的“數(shù)據(jù)庫名稱”文本框中，輸入要創(chuàng)建的數(shù)據(jù)庫的名稱，如圖。,刪除數(shù)據(jù)庫,語法： DROP DATABASE database_name | database_snapshot_name ,.n ; 如：刪除DBTEST數(shù)據(jù) DROP DATABASE DBTEST,SQL SERVER 數(shù)據(jù)庫基礎(chǔ) 創(chuàng)建和管理數(shù)據(jù)庫 創(chuàng)建和管理數(shù)據(jù)表 增、刪、改數(shù)據(jù) 數(shù)據(jù)檢索 創(chuàng)建和管理視圖 T-SQL編程 創(chuàng)建和管理存儲過程 SQL SERVER 企業(yè)管理器使用,內(nèi) 容,3.創(chuàng)建和管理表,表的基本概念 設(shè)計表 數(shù)據(jù)類型 創(chuàng)建表 刪除表,3.1 表的基本概念,在數(shù)據(jù)庫中，表是由數(shù)據(jù)按一定的順序和格式構(gòu)成的數(shù)據(jù)集合，是數(shù)據(jù)庫的主要對象。每一行代表一條記錄，每一列代表記錄的一個字段。,3.2 設(shè)計表,對于具體的某一個表，在創(chuàng)建之前，需要確定表的下列特征： 表要包含的數(shù)據(jù)的類型； 表中的列數(shù)，每一列中數(shù)據(jù)的類型和長度（如果必要）； 哪些列允許空值； 是否要使用以及何處使用約束、默認設(shè)置和規(guī)則； 所需索引的類型，哪里需要索引，哪些列是主鍵，哪些是外鍵。,.3 數(shù)據(jù)類型,SQL Server 2005常用數(shù)據(jù)類型： 數(shù)字相關(guān)： Int bit decimal(p,s) Money float(n) 日期和時間： Datetime 字符串相關(guān)： char(n) varchar(n) nvarchar(n) Text 二進制字 Image,3.4 創(chuàng)建表,語法如下： CREATE TABLE table_name (column_name column_property) ,. 參數(shù): table_name: 新表的名稱 column_name:表中列的名稱 column_property:列的屬性,CREATE TABLE TESTTABLE ( ID int primary key, NAME nvarchar(50), CLASS nvarchar(100), DATE DATETIME ),創(chuàng)建如下表,3.5 刪除表,語法： DROP TABLE 表名 如： DROP TABLE TESTTABLE,SQL SERVER 數(shù)據(jù)庫基礎(chǔ) 創(chuàng)建和管理數(shù)據(jù)庫 創(chuàng)建和管理數(shù)據(jù)表 增、刪、改數(shù)據(jù) 數(shù)據(jù)檢索 創(chuàng)建和管理視圖 T-SQL編程 創(chuàng)建和管理存儲過程,內(nèi) 容,4.1 數(shù)據(jù)增加,語法： INSERT INTO table_name(column_list) VALUES(data_values,n) 參數(shù)： column_list是用逗號分隔的一些列名稱，可 用來指定為其提供數(shù)據(jù)的列。 VALUES關(guān)鍵字為表的某一行指定值。,在表TESTTABLE表加一條記錄： INSERT INTO TESTTABLE(ID,NAME,CLASS,DATE) VALUES(10,test1,202,2008-01-10) 同 INSERT INTO TESTTABLE(NAME,CLASS,DATE,ID) VALUES(test1,202,2008-01-10,10) 簡寫為： INSERT INTO TESTTABLE VALUES(10,test1,202,2008-01-10) VALUES提供的值與表的字段數(shù)量和順序必須相同,4.2 數(shù)據(jù)修改,語法： UPDATE table_name SET 列名=表達式 WHERE 表達式 參數(shù)： SET子句指定要更改的列和這些列的新值,修改ID為了10的NAME值為NEWNAME: UPDATE TESTTABLE SET NAME=NEWNAME WHERE ID=10 修改ID為了10的DATE值為當前時間和CLASS的值為2008: UPDATE TESTTABLE SET CLASS=2008 AND DATE=GetDate() WHERE ID=10 修改的值來自其它表中： UPDATE dbo.TESTTABLE SET NAME=dbo.test.NAME FROM test WHERE TESTTABLE.ID=test.ID AND TEST.ID=10,4.3 刪除數(shù)據(jù),語法簡化形式為： DELETE FROM table_name WHERE search_condition 參數(shù)： table_name:表名 search_condition:搜索表達式,刪除表中等于10的數(shù)據(jù)： DELETE TABLE TESTTABLE WHERE ID=10 刪除表中所有的數(shù)據(jù)： DELETE TABLE TESTTABLE 或 TRUNCATE TABLE TRUNCATE TABLE與不含有WHERE子句的DELETE語句在功能上相同。但是，TRUNCATE TABLE速度更快，并且使用更少的系統(tǒng)資源和事務(wù)日志資源。,SQL SERVER 數(shù)據(jù)庫基礎(chǔ) 創(chuàng)建和管理數(shù)據(jù)庫 創(chuàng)建和管理數(shù)據(jù)表 增、刪、改數(shù)據(jù) 數(shù)據(jù)檢索 創(chuàng)建和管理視圖 T-SQL編程 創(chuàng)建和管理存儲過程,內(nèi) 容,.數(shù)據(jù)查詢,所謂查詢，就是對已經(jīng)存在于數(shù)據(jù)庫中的數(shù) 據(jù)按特定的組合、條件或次序進行檢索。查詢功 能是數(shù)據(jù)庫最基本也是最重要的功能。,描述結(jié)果集的列，它指定了結(jié)果集中要包含的列的名稱，是一個逗號分隔的表達式列表,所有列時，使用*代替,SELECT語句的語法如下： SELECT select_list INTO new_table_name FROM table_list WHERE search_conditions GROUP BY group_by_list HAVING search_conditions ORDER BY order_list ASC | DESC ,SELECT 語法：,指定使用結(jié)果集來創(chuàng)建新表。new_table_name指定新表的名稱。,WHERE子句是一個篩選條件，它定義了源表中的行要滿足SELECT語句的要求所必須達到的條件。,GROUP BY子句根據(jù)group_by_list列中的值將結(jié)果集分成組。,HAVING子句是應(yīng)用于結(jié)果集的附加篩選，用來向使用GROUP BY子句的查詢中添加數(shù)據(jù)過濾準則。,ORDER BY子句定義了結(jié)果集中行的排序順序。,指定要檢索數(shù)據(jù)的表名或視圖名。,選擇查詢,一般查詢都不是針對全表所有行的查詢，只是從整 個表中選出滿足指定條件的內(nèi)容，這就要用到WHERE 子句。 以下是選擇行的基本語法： SELECT FROM WHERE ,比較運算符,邏輯運算符,運算符,特殊運算符：,示例數(shù)據(jù)庫結(jié)構(gòu),范圍搜索條件,范圍搜索返回介于兩個指定值之間的所有值 ，可分為包括范圍和排他范圍兩種類型。,查詢年齡在17-18之間的學生： SELECT * FROM dbo.學生表 WHERE 年齡=17 and 年齡=18 SELECT * FROM dbo.學生表 WHERE 年齡between 17 and 18,列表搜索條件,IN關(guān)鍵字使用戶可以選擇與列表中的任意值匹 配的行。,查詢年齡在17-18之間的學生： SELECT * FROM dbo.學生表 WHERE 年齡 IN (17,18),搜索條件中的模式匹配,LIKE關(guān)鍵字搜索與指定模式匹配的字符串、 日期或時間值。模式包含要搜索的字符串，字符 串中可包含4種通配符的任意組合，搜索條件中 可用的通配符如表所示。,查詢所有姓“王”的學生: SELECT * FROM dbo.學生表 WHERE 姓名LIKE 王% 查詢所有姓“王”的學生，但名字長度為兩個字: SELECT * FROM dbo.學生表 WHERE 姓名LIKE 王_ 查詢所有姓王的學生，但是名字中不包含“潔”字： SELECT * FROM dbo.學生表 WHERE 姓名LIKE 王%潔 查詢所有姓王的學生，但是名字中包含“山”字： SELECT * FROM dbo.學生表 WHERE 姓名LIKE 王%山,常用函數(shù),AVG：用于計算查詢結(jié)果的平均值 MAX：該函數(shù)用于查找最大值 IN： 該函數(shù)用于查找最小值 SUM：該函數(shù)計算查詢到的數(shù)據(jù)值的總 COUNT： 該函數(shù)可以計算查詢到的結(jié)果的數(shù)目 GetDate：獲得當前時間 LEN：獲取字符串長度 SUBSTRING：獲回字符串的部分內(nèi)容 CONVERT：數(shù)據(jù)類型轉(zhuǎn)換,數(shù)據(jù)分組,使用GROUP BY子句可以按一定的條件對查詢到的結(jié)果進行分組，再對每一組數(shù)據(jù)計算統(tǒng)計信息。 SELECT 性別,count(*) as 數(shù)量 FROM dbo.學生表 GROUP BY 性別,表連接,在數(shù)據(jù)庫應(yīng)用中，經(jīng)常需要從多個相關(guān)的表中查詢數(shù)據(jù)，這 就需要進行表連接。,表連接,left join(左聯(lián)接) 返回包括左表中的所有記錄和右表中聯(lián)結(jié)字段相等的記 right join(右聯(lián)接) 返回包括右表中的所有記錄和左表中聯(lián)結(jié)字段相等的記錄 inner join(等值連接) 只返回兩個表中聯(lián)結(jié)字段相等的行 最常見的表連接形式： SELECT 選擇字段 FROM 表名1 INNER JOIN 表名2 ON 表名1.列名1=表名2.列名2,編寫查詢語句，顯示每個學生的成績，返回結(jié)果如下：,數(shù)據(jù)初始化腳本,SQL練習,1、查詢“學生表”中年齡大于或等于23歲的學生 2、查詢“學生表”中姓名包括“朋”的學生 3、查詢“學生表”中姓名包含李，且名字長度為兩個漢字的學生 4、查詢平均成績大于60分的學生 5、統(tǒng)計各學生的平均成績，且按成績從高到低排序,數(shù)據(jù)初始化腳本,SQL SERVER 數(shù)據(jù)庫基礎(chǔ) 創(chuàng)建和管理數(shù)據(jù)庫 創(chuàng)建和管理數(shù)據(jù)表 增、刪、改數(shù)據(jù) 數(shù)據(jù)檢索 創(chuàng)建和管理視圖 T-SQL編程 創(chuàng)建和管理存儲過程,內(nèi) 容,創(chuàng)建和管理視圖,視圖是基于某個查詢結(jié)果的虛表。除非是索引視圖，否則視圖的數(shù)據(jù)不會作為非重復(fù)對象存儲在數(shù)據(jù)庫中。數(shù)據(jù)庫中只存儲視圖的定義，即視圖是從某個或某些基本表（或視圖）導出的，視圖不生成所選數(shù)據(jù)庫行和列的永久拷貝。,創(chuàng)建視圖,CREATE VIEW 語句來創(chuàng)建視圖，對應(yīng)的語法如下： CREATE VIEW schema_name . view_name (column ,.n ) WITH ,.n AS select_statement ; WITH CHECK OPTION := ENCRYPTION SCHEMABINDING ,參數(shù)的含義： schema_name： 視圖所屬架構(gòu)的名稱 view_name： 視圖的名稱 Column： 視圖中的列使用的名稱 AS： 指定視圖要執(zhí)行的操作 select_statement： 定義視圖的SELECT語句,使用SQL練習語句創(chuàng)建視圖,刪除視圖,在創(chuàng)建視圖后，如果不再需要該視圖，或想清除視圖定義以及與之相關(guān)聯(lián)的權(quán)限，可以刪除該視圖。刪除視圖后，表和視圖所基于的數(shù)據(jù)并不受到影響 刪除視圖對應(yīng)的T-SQL語句為： DROP VIEW語句，語法為： DROP VIEW ,SQL SERVER 數(shù)據(jù)庫基礎(chǔ) 創(chuàng)建和管理數(shù)據(jù)庫 創(chuàng)建和管理數(shù)據(jù)表 增、刪、改數(shù)據(jù) 數(shù)據(jù)檢索 創(chuàng)建和管理視圖 T-SQL編程 創(chuàng)建和管理存儲過程,內(nèi) 容,概述,Transact-SQL語言是微軟公司在Microsoft SQL Server系統(tǒng)中使用的語言，是對SQL語言的一種擴展形式 在Microsoft SQL Server 2005系統(tǒng)中，根據(jù)Transact-SQL語言的功能特點，可以把Transact-SQL語言分為5種類型，即數(shù)據(jù)定義語言、數(shù)據(jù)操縱語言、數(shù)據(jù)控制語言、事務(wù)管理語言和附加的語言元素,數(shù)據(jù)定義語言,數(shù)據(jù)定義語言用于創(chuàng)建數(shù)據(jù)庫和數(shù)據(jù)庫對象，為數(shù)據(jù)庫操作提供對象。例如，數(shù)據(jù)庫以及表、觸發(fā)器、存儲過程、視圖、索引、函數(shù)、類型、用戶等都是數(shù)據(jù)庫中的對象，都需要通過定義才能使用。在DDL中，主要的Transact-SQL語句包括CREATE語句、ALTER語句、DROP語,數(shù)據(jù)操縱語言,數(shù)據(jù)操縱語言主要是用于操縱表、視圖中數(shù)據(jù)的語句。當我們創(chuàng)建表對象之后，初始狀態(tài)時該表是空的，沒有任何數(shù)據(jù)。如何向表中添加數(shù)據(jù)呢？這時需要使用INSERT語句。如何檢索表中數(shù)據(jù)呢？可以使用SELECT語句。如果表中數(shù)據(jù)不正確的，那么可以使用UPDATE語句進行更新。當然，也可以使用DELETE語句刪除表中的數(shù)據(jù)。實際上，DML語言正是包括了INSERT、SELECT、UPDATE、DELETE等語句。,數(shù)據(jù)控制語言(DCL),數(shù)據(jù)控制語言(DCL)主要用來執(zhí)行有關(guān)安全管理的操作，該語言主要包括GRANT語句、REVOKE語句和DENY語句。GRANT語句可以將指定的安全對象的權(quán)限授予相應(yīng)的主體，REVOKE語句則刪除授予的權(quán)限，DENY語句拒絕授予主體權(quán)限，并且防止主體通過組或角色成員繼承權(quán)限。,注釋,注釋是程序代碼中不被執(zhí)行的文本字符串（也稱為備注）。 SQL Server 2005支持以下兩種類型的注釋字符。 -（雙連字符）。這些注釋字符可與要執(zhí)行的代碼處在同一行，也可另起一行。從雙連字符開始到行尾的內(nèi)容均為注釋。 /* . */（斜杠-星號字符對）。這些注釋字符可與要執(zhí)行的代碼處在同一行，也可另起一行，甚至可以在可執(zhí)行代碼內(nèi)部。開始注釋對(/*)與結(jié)束注釋對(*/)之間的所有內(nèi)容均視為注釋,常量和變量,常量：也稱為文字值或標量值，是在程序運行過程中值保持 不變的量，它是表示一個特定數(shù)據(jù)值的符號。 變量是指在程序運行過程中值可以改變的量。 聲明變量的語法如下： DECLARE variable_name datatype 其中， variable_name是局部變量的名字，必須以“”符號開頭 Datatype是為該局部變量指定的數(shù)據(jù)類型。,流控制語句,Transact-SQL提供稱為控制流語言的特殊 關(guān)鍵字，用于控制Transact-SQL語句、語句塊 和存儲過程的執(zhí)行流。這些關(guān)鍵字可用于臨時 Transact-SQL語句、批處理和存儲過程中。,BEGINEND,包括一系列的Transact-SQL語句，從而可以執(zhí)行一組 Transact-SQL語句。 語法如下： BEGIN sql_statement | statement_block END 參數(shù) ： sql_statement | statement_block 使用語句塊定義的任何有效的Transact-SQL語句或語句組。,IFELSE,語法： IF Boolean_expression sql_statement | statement_block ELSE sql_statement | statement_block 參數(shù)含義： Boolean_expression：返回TRUE或FALSE的表達式。 sql_statement | statement_block ：任何Transact-SQL語句或語句塊,WHILE,設(shè)置重復(fù)執(zhí)行SQL語句或語句塊的條件。 語法如下： WHILE Boolean_expression sql_statement | statement_block 參數(shù)含義： Boolean_expression：表達式，返回TRUE或FALSE。 sql_statement | statement_block ：Transact-SQL語句或語句塊。,BREAK與CONTINUE,可以使用BREAK和CONTINUE關(guān)鍵字在循環(huán)內(nèi) 部控制WHILE循環(huán)中語句的執(zhí)行。 BREAK用于退出最內(nèi)層的WHILE循環(huán)； CONTINUE用于重新開始下一次WHILE循環(huán)，在CONTINUE關(guān)鍵字之后的語句都不會被執(zhí)行，而是跳