SQLServer數(shù)據(jù)庫開發(fā)經(jīng)典案例完整版課件

1、021世紀高職高專創(chuàng)新精品規(guī)劃教材 SQL Server 數(shù)據(jù)庫開發(fā)經(jīng)典案例教程 1第1章 數(shù)據(jù)庫基礎2本章目標數(shù)據(jù)庫存在的必要性和數(shù)據(jù)庫的發(fā)展SQL Server2008數(shù)據(jù)庫以及它所支持的操作系統(tǒng)平臺SQL Server2008的安裝方法服務器上的后臺服務SQL Server2008服務端組件和客戶端工具學會使用SSMS創(chuàng)建登錄 3數(shù)據(jù)庫存在的必要數(shù)據(jù)存在的必要性可以存儲大量的數(shù)據(jù)，便于用戶進行檢索和管理 可以保持數(shù)據(jù)的一致性、完整性，降低數(shù)據(jù)冗余 實現(xiàn)應用程序的數(shù)據(jù)共享和安全 利用數(shù)據(jù)庫可以智能的對數(shù)據(jù)進行分析和統(tǒng)計 數(shù)據(jù)庫的發(fā)展史 第一代數(shù)據(jù)庫層次模型和網(wǎng)狀模型 各種DBMS 軟件都是

2、基于某種數(shù)據(jù)模型的 IBM 公司在1969 年推出了世界上第一個層次數(shù)據(jù)庫系統(tǒng)IMS 第二代數(shù)據(jù)庫關系型數(shù)據(jù)庫 關系數(shù)據(jù)庫是建立在關系模型之上的數(shù)據(jù)庫 第三代數(shù)據(jù)庫面向?qū)ο髷?shù)據(jù)庫 處于發(fā)展階段，未來趨勢4數(shù)據(jù)庫的基本概念數(shù)據(jù)庫的基本概念數(shù)據(jù)和信息數(shù)據(jù)中所包含的意義就是信息 數(shù)據(jù)庫保存有組織的數(shù)據(jù)的容器關系數(shù)據(jù)庫關系數(shù)據(jù)庫是基于關系模型建立的，由一系列二維表格組成，將數(shù)據(jù)分類存儲在多個二維表中，用關系（外鍵）來體現(xiàn)各個表之間的關系。 訂單 客戶 產(chǎn)品 產(chǎn)品數(shù)據(jù)庫表數(shù) 據(jù) 表存儲過程視 圖.5數(shù)據(jù)庫的基本概念數(shù)據(jù)庫的基本概念表、實體和記錄 實體是客觀存在的事物，比如：桌子，椅子，電腦行(記錄)6數(shù)

3、據(jù)庫的基本概念數(shù)據(jù)庫的基本概念數(shù)據(jù)冗余和數(shù)據(jù)完整性 數(shù)據(jù)冗余即相同的數(shù)據(jù)存在了多個地方 數(shù)據(jù)完整性是指數(shù)據(jù)庫中數(shù)據(jù)的有效性和相容性編號姓名年齡民族部門1王濤33漢族人事管理部2李梅27漢族人事管理部市場營銷部回族27李梅2財務管理部漢族33王濤1部門民族年齡姓名編號7SQL Server2008簡介SQL Server2008簡介 SQLServer是微軟公司開發(fā)的一款關系型數(shù)據(jù)庫產(chǎn)品，具有成本低，易上手，工具全等優(yōu)點。適用于大型或超大型數(shù)據(jù)庫服務器端。微軟的軟件的特點是版本分得細，可適合各種使用者不同的需要 SQL Server2008的體系結構 SQL Server2008系統(tǒng)由4個部分組

4、成：數(shù)據(jù)庫引擎Analysis ServicesReporting ServicesIntegration Services數(shù)據(jù)庫引擎 數(shù)據(jù)庫引擎是Microsoft SQL Server2008系統(tǒng)的核心服務，是存儲和處理關系（表格）類型的數(shù)據(jù)或XML文檔數(shù)據(jù)的服務，負責完成數(shù)據(jù)的存儲、處理和安全管理。例如：創(chuàng)建數(shù)據(jù)庫、創(chuàng)建表、創(chuàng)建視圖、查詢數(shù)據(jù)和訪問數(shù)據(jù)庫等操作，都是由數(shù)據(jù)庫引擎完成的 8SQL Server2008的安裝和版本 安裝：下載軟件并參照書步驟進行安裝版本企業(yè)版（SQL Server2008 Enterprise Edition） 標準版 (SQL Server2008 Sta

5、ndard Edition） 工作組版(SQL Server2008 Workgroup Edition）開發(fā)版(SQL Server2008 Developer Edition) 簡易版（SQL Server2008 Express Edition) 9SQL Server2008后臺服務服務器上的后臺服務 在控制面板上選擇【管理工具】- 【服務】選項。SQL Server服務 SQL Server Agent服務 SQL Server Browser SQL Server Integration Services服務 SQL Server Reporting Services服務 客戶端管

6、理工具 SQL Server Management StudioSQL Server Configuration Manager Reporting Services配置SQL Server錯誤和使用情況報告 SQL Server Profiler 數(shù)據(jù)庫引擎優(yōu)化顧問10SQL Server2008的工作模式客戶端應用程序SQL Server服務器批處理語句：語句1語句2GOSQL Server2008支持C/S工作模式，C/S模式即客戶機/服務器（Client/Server）模式的簡稱，在該種模式下，服務器通常是在硬件配置和性能比較高、安裝了服務器軟件的計算機，客戶機就是安裝了客戶端軟件的一

7、般的計算機 11連接與斷開數(shù)據(jù)庫服務器連接與斷開數(shù)據(jù)庫服務器身份驗證：連接之前，需要選擇身份驗證模式，系統(tǒng)提供了兩種身份驗證模式：Windows身份驗證：用當前登錄操作系統(tǒng)的身份去登錄.SQL Server身份驗證：用Windows加SQL Server的混合模式進行驗證，需要用合法的SQL Server登錄用戶去訪問默認系統(tǒng)在安裝以后會自動產(chǎn)生一個sa的登錄用戶，具有最高權限。斷開數(shù)據(jù)庫服務器 12啟動和停止SQL Server服務啟動、停止、暫停和重新啟動SQL Server服務 使用SQL Server配置管理器用SQL Server Management Studio (SSMS) 使

8、用操作系統(tǒng)的服務管理功能使用命令提示符啟動SQL Server默認實例： net start mssqlserver停止SQL Server默認實例： net stop mssqlserver 13SQL Server Management Studio(SSMS)介紹 SSMS簡介 SQL Server是作為單獨的Windows進程在基于Windows的計算機上運行的，它可以運行在獨立的桌面計算機上，也可以運行在服務器或者網(wǎng)絡計算機上 .SQL Server作為服務運行，該服務由Windows自身進行監(jiān)控 SSMS中的一個重要的工具就是是查詢編輯器（query editor )SQL Ser

9、ver Management Studio(SSMS)的使用 14創(chuàng)建數(shù)據(jù)庫登錄賬戶 創(chuàng)建數(shù)據(jù)庫登錄賬戶在實際應用中，經(jīng)常要根據(jù)不同的用戶設置不同的訪問權限來限制對數(shù)據(jù)庫的操作 15總結數(shù)據(jù)庫存在的必要性和數(shù)據(jù)庫的發(fā)展使用數(shù)據(jù)庫可以安全，高效的實現(xiàn)數(shù)據(jù)的存儲和管理數(shù)據(jù)和信息的概念SQL Server2008數(shù)據(jù)庫只適合Windows平臺SQL Server2008的安裝方法服務器上的后臺服務SQL Server2008服務端組件和客戶端工具創(chuàng)建登錄的方法16使用數(shù)據(jù)庫可以安全，高效的實現(xiàn)數(shù)據(jù)的存儲和管理數(shù)據(jù)和信息數(shù)據(jù)庫文件包括：主數(shù)據(jù)文件，次要數(shù)據(jù)文件和日志文件，每個數(shù)據(jù)庫至少要包含一個數(shù)據(jù)文

10、件和日志文件文件組是邏輯上對數(shù)據(jù)文件進行管理的一種方式通過分離數(shù)據(jù)庫可以將數(shù)據(jù)庫分離成物理的文件附加數(shù)據(jù)庫可以將物理文件恢復成數(shù)據(jù)庫第2章 數(shù)據(jù)庫管理17本章目標學會創(chuàng)建和維護數(shù)據(jù)庫的方法掌握數(shù)據(jù)庫的組成及數(shù)據(jù)文件的含義掌握數(shù)據(jù)庫的分離和附加方法學會使用幫助文檔18數(shù)據(jù)庫組成19數(shù)據(jù)文件（主要/次要）和日志文件數(shù)據(jù)文件包含數(shù)據(jù)和對象：如表、索引、視圖等日志文件包含恢復數(shù)據(jù)庫中的所有事務所需的信息數(shù)據(jù)庫主數(shù)據(jù)文件(.mdf)次要數(shù)據(jù)文件(.ndf)日志文件(.ldf)數(shù)據(jù)庫文件和文件組簡介主數(shù)據(jù)文件包含數(shù)據(jù)庫的啟動信息，用戶數(shù)據(jù)和對象存儲在此文件中，每個數(shù)據(jù)庫只能有一個主數(shù)據(jù)文件，建議擴展名是

11、.mdf次要數(shù)據(jù)文件次要數(shù)據(jù)文件是可選的，由用戶定義并存儲用戶數(shù)據(jù)。建議擴展名.ndf事務日志文件保存用于恢復數(shù)據(jù)庫的日志信息。每個數(shù)據(jù)庫必須至少有一個日志文件，建議擴展名.ldf20數(shù)據(jù)庫系統(tǒng)數(shù)據(jù)庫21mastertempdbmsdbStudentsDBBookShopDB用戶數(shù)據(jù)庫model4個系統(tǒng)數(shù)據(jù)庫用戶自己創(chuàng)建系統(tǒng)數(shù)據(jù)庫4個系統(tǒng)數(shù)據(jù)庫Master它存儲 SQL Server 系統(tǒng)的所有系統(tǒng)級信息 ,它包括：登錄帳戶、鏈接服務器和系統(tǒng)配置設置。所有其他數(shù)據(jù)庫的存在、數(shù)據(jù)庫文件的位置SQL Server的初始化信息 model在 SQL Server 實例上創(chuàng)建的所有數(shù)據(jù)庫的模板 Msd

12、b 由 SQL Server 代理用來計劃警報和作業(yè) tempdb 是連接到 SQL Server 實例的所有用戶都可用的全局資源，它保存所有臨時表和臨時存儲過程 22文件組BookShopDB的物理文件結構23BookShopDB.ldfE:日志文件 BookInfo Publisher UserInfo 主文件組BookShopDB.mdfC:用戶定義文件組Book1.ndf Book2.ndfD:sys sys sysusers sysobjects 24數(shù)據(jù)庫的建立-可視化方式數(shù)據(jù)庫的建立-使用代碼創(chuàng)建代碼創(chuàng)建數(shù)據(jù)庫25數(shù)據(jù)庫設置數(shù)據(jù)庫的設置數(shù)據(jù)庫右鍵-屬性 -選項-只讀 -收縮26數(shù)

13、據(jù)庫的修改修改數(shù)據(jù)庫：可以在【文件】選項頁通過修改數(shù)據(jù)文件修改數(shù)據(jù)庫 27數(shù)據(jù)庫刪除可視化方式 代碼方式Drop database test28分離數(shù)據(jù)庫分離數(shù)據(jù)庫 分離數(shù)據(jù)庫是指將數(shù)據(jù)庫從 SQL Server 實例中刪除29附加數(shù)據(jù)庫附加數(shù)據(jù)庫附加是分離的反操作。就可以使用分離后的數(shù)據(jù)文件和日志文件將數(shù)據(jù)庫附加到任何 SQL Server 實例，包括分離該數(shù)據(jù)庫的服務器 30總結數(shù)據(jù)庫文件包括：主數(shù)據(jù)文件，次要數(shù)據(jù)文件和日志文件，每個數(shù)據(jù)庫至少要包含一個數(shù)據(jù)文件和日志文件文件組是邏輯上對數(shù)據(jù)文件進行管理的一種方式創(chuàng)建數(shù)據(jù)庫可視化方式創(chuàng)建代碼方式創(chuàng)建維護數(shù)據(jù)庫數(shù)據(jù)庫的修改和刪除通過分離數(shù)據(jù)庫

14、可以將數(shù)據(jù)庫分離成物理的文件附加數(shù)據(jù)庫可以將物理文件恢復成數(shù)據(jù)庫使用幫助文檔31第3章-1 SQLServer表管理32本章目標掌握數(shù)據(jù)表、數(shù)據(jù)完整性、主鍵和外鍵的基本概念掌握SQLServer數(shù)據(jù)類型創(chuàng)建表的時候要設定字段名，選取合適的數(shù)據(jù)類型和約束如果要保證某個列唯一并且自動增長，設定IDENTITY約束主鍵是保證實體完整性的約束，建議每個表有一個主鍵外鍵保證表之間的引用完整性，從表中的數(shù)據(jù)依賴于主表在建表時設置主鍵約束外鍵約束檢查約束默認約束空值約束標識列 33數(shù)據(jù)表基本概念表書數(shù)據(jù)庫中最重要的數(shù)據(jù)對象，數(shù)據(jù)存儲在表里數(shù)據(jù)表由行和列組成34數(shù)據(jù)完整性數(shù)據(jù)完整性實體完整性將行定義為特定表的

15、唯一實體。即表中的每一行數(shù)據(jù)都代表一個不同的實體，不能存在相同的數(shù)據(jù)行 域完整性域完整性指特定列項的有效性 引用完整性用來限制兩個有關聯(lián)關系的表之間的數(shù)據(jù) 35主鍵和外鍵 主鍵表通常具有包含唯一標識表中每一行值的一個或一多個字段 特點一個表只能有一個主鍵主鍵中的列不能為空如果一個表的主鍵由多個列組成，該主鍵也叫“組合主鍵”外鍵外鍵約束保證了數(shù)據(jù)庫中兩個表中數(shù)據(jù)的引用完整性 將一個表的一列（或列組合）定義為引用其他表的主鍵，則引用表中的這個列（或列組合）就稱為外鍵 36主鍵和外鍵37主鍵外鍵數(shù)據(jù)表建立數(shù)據(jù)表，建表時需考慮：字段的名稱數(shù)據(jù)類型是否允許為Null是否自動增長等 38數(shù)據(jù)類型Sqlse

16、rver有以下數(shù)據(jù)類型數(shù)據(jù)類型類別 具體類型 精確數(shù)據(jù) bigint，decimal，int，numeric，smallint，money，tinyint，smallmoney，bit 近似數(shù)字 float ,real字符串 char ,varchar, text Unicode字符串 nchar , nvarchar, ntext 二進制字符串 binary ,image, varbinary 日期類型 datetime ,smalldatetime 其他類型 Uniqueidentifier, xml, table等 39創(chuàng)建表創(chuàng)建表的實質(zhì)就是定義表結構以及約束等屬性。SQL Server

17、2008種提供了兩種方式創(chuàng)建表。一種是通過SQL Server Management Studio(SSMS)一種是通過T-SQL代碼。本書重點介紹使用T-SQL代碼來創(chuàng)建表 40T-SQL代碼建表通過執(zhí)行Transact-SQL的CREATE TABLE 語句可以創(chuàng)建表，建表的語法如下：例如: CREATE TABLE mytable ( col1 char(10) primary key, col2 int not null )41CREATE TABLE 表名( 字段1 數(shù)據(jù)類型 列的特征， 字段2 數(shù)據(jù)類型 列的特征， 。 )建表【示例3-1】 ：建立用戶表UserInfo, 建表時設

18、置主鍵并設置相應字段的not null（非空）屬性 42USE BookShopDb -將當前數(shù)據(jù)庫設置為BookShopDb GO CREATE TABLE UserInfo -創(chuàng)建用戶表( UserID char(3) PRIMARY KEY, -UerID為主鍵 UserName char(10) not null, Sex char(2) not null, Age int , hiredate datetime, tele char(15), Addr varchar(100)GO 建表表的IDENTITY 屬性標識列字段的數(shù)據(jù)類型只能是decimal，int，numeric，sma

19、llint，bitint，tinyint每個表最多有一列具有IDENTITY屬性，且該列不能為空、不允許有默認值IDENTITY屬性的語法: IDENTITY(seed,increment),seed稱為種子，表示裝載到表中第一行使用的值；如IDENTITY(1,1),表示該列值從1開始，每條記錄增1.IDENTITY屬性與CREATE TABLE和ALTER TABLE一起使用 43建表時使用標識列【示例3-2】建立圖書類別表Category 44USE BookShopDb -將當前數(shù)據(jù)庫設置為BookShopDb GOCREATE TABLE Publisher( PublisherID

20、 int IDENTITY(1,1) PRIMARY KEY, -自動編號（標識列） PublisherName varchar(50) not null -出版社名稱，必須輸入)GO 建表時使用約束45USE BookShopDb -將當前數(shù)據(jù)庫設置為BookShopDbGO CREATE TABLE BookInfo /*創(chuàng)建BookInfo數(shù)據(jù)表*/( BookID char(15) PRIMARY KEY, -書號 ，主鍵 BookName varchar(100) not null, -書名 Author varchar(50) not null, -作者 ISBN char(13)

21、 , -ISBN,null可以省略 PublishDate datetime not null, -出版日期 PublisherID int not null REFERENCES Publisher(PublisherID),-外鍵 CategoryID int not null REFERENCES Category(CategoryID), -外鍵 Price money , -價格 Content text -介紹)GO 建表時使用約束46USE BookShopDb -將當前數(shù)據(jù)庫設置為BookShopDbGO Drop table userInfoCREATE TABLE User

22、Info -創(chuàng)建用戶表( UserID char(3) PRIMARY KEY CHECK(UserID LIKE 0-90-90-9), UserName char(10) UNIQUE, -用戶名，唯一約束 Sex char(2) CHECK(Sex in (男,女), -性別，Check約束 Age int not null, -年齡,非空約束 hiredate datetime , -入職日期 tele char(15), -電話 Addr varchar(100) DEFAULT 廣東廣州 -地址，默認約束 )GO 【示例3-6】重建UserInfo表，演示在建表時如何設定約束建表時

23、使用約束47USE BookShopDb -將當前數(shù)據(jù)庫設置為BookShopDb,GO CREATE TABLE SalesMaster /*創(chuàng)建SalesMaster銷售主表*/(SalesMasterID int identity primary key, -銷售id,自動增長，主鍵 SalesDate datetime not null, -銷售日期 UserID char(3) not null), CONSTRAINT fk_UserID FOREIGN KEY (UserID) REFERENCES UserInfo(UserID) ON DELETE CASCADE 【示例3-

24、7】建立SalesMaster銷售主表，通過設定外鍵與UserInfo表之間建立聯(lián)系總結掌握數(shù)據(jù)表的概念和含義數(shù)據(jù)完整性、主鍵和外鍵的基本概念主鍵是保證實體完整性的約束，建議每個表有一個主鍵外鍵保證表之間的引用完整性，從表中的數(shù)據(jù)依賴于主表創(chuàng)建數(shù)據(jù)表創(chuàng)建表的時候要設定字段名，選取合適的數(shù)據(jù)類型和約束如果要保證某個列唯一并且自動增長，設定IDENTITY約束掌握SQLServer數(shù)據(jù)類型，三大類：數(shù)字、字符和日期在建表時設置主鍵約束外鍵約束檢查約束默認約束空值約束標識列 48第3章-2 SQLServer表管理49本章目標約束的維護修改表添加約束刪除約束建立數(shù)據(jù)庫關系圖 以圖形方式顯示數(shù)據(jù)庫表之

25、間的關系SQL Server中的特殊表類型 已分區(qū)表臨時表系統(tǒng)表修改和刪除表修改表結構、刪除表50約束的維護添加約束語法51ALTER TABLE 表名ADD CONSTRAINT 約束名約束類型具體的約束說明 USE BookShopDb -選擇數(shù)據(jù)庫，以下示例從略GOALTER TABLE UserInfo ADD CONSTRAINT pk_ UserID PRIMARY KEY(UserID)添加主鍵約束添加外鍵約束ALTER TABLE SalesDetails ADD CONSTRAINT fk_BookID FOREIGN KEY(BookID) REFERENCES BookI

26、nfo(BookID) 約束的維護刪除約束語法52ALTER TABLE 表名DROP CONSTRAINT 約束名 ALTER TABLE UserInfo DROP CONSTRAINT pk_ UserID 數(shù)據(jù)庫關系圖 已建立了數(shù)據(jù)庫中各個數(shù)據(jù)表之間的關系，可通過SQL Server提供的數(shù)據(jù)庫關系圖，即可以圖表的形式顯示各個數(shù)據(jù)表之間的關系如果表之間的關系沒有建立，也可在通過數(shù)據(jù)庫關系圖建立數(shù)據(jù)表之間的關系 53數(shù)據(jù)庫關系圖54SQL Server中的特殊表類型 分區(qū)表當表數(shù)據(jù)非常大時可以將一個表水平劃分為多個表單獨存儲，并且可以存儲在多個不同的物理文件中。這樣的表就是分區(qū)表 臨時表

27、在操作過程中系統(tǒng)需要維護一些臨時數(shù)據(jù)，比如果用戶需要將查詢結果排序，則會創(chuàng)建臨時表并在臨時表中將數(shù)據(jù)排序后返回給用戶 系統(tǒng)表SQL Server 將定義服務器配置及其所有表的數(shù)據(jù)存儲在一組特殊的表中，這組表稱為系統(tǒng)表。任何用戶都不應該直接更改系統(tǒng)表 55修改表結構修改表結構使用ALTER TABLE語句修改表結構增加列刪除列修改列的數(shù)據(jù)類型改變列的長度等在表沒有數(shù)據(jù)的情況下，可以對列進行任意修改或刪除 56修改表結構-增加列和修改列57USE BookShopDbGOALTER TABLE UserInfo ADD email char(20) GO 增加列 給UserInfo表增加列emai

28、l,字符類型長度是20，非空修改列USE BookShopDbGOALTER TABLE UserInfo ALTER COLUMN addr varchar(150) not null增加UserInfo表的字段addr 長度，將其增加到150，類型不變 修改表結構-刪除列58刪除列刪除UserInfo表 email列USE BookShopDbGOALTER TABLE UserInfo DROP COLUMN emailGO刪除表刪除表 DROP TABLE UserInfo如果刪除的表和其他表有關聯(lián)關系，必須先刪除子表，然后再刪除主表。否則系統(tǒng)會彈出錯誤 59總結約束的維護修改表添加約

29、束Alter table add constraint 約束名稱 約束類型刪除約束 alter table drop constraint 約束名稱建立數(shù)據(jù)庫關系圖 以圖形方式顯示數(shù)據(jù)庫表之間的關系SQL Server中的特殊表類型 已分區(qū)表臨時表系統(tǒng)表修改和刪除表修改表結構：修改添加字段Alter table 表名 add 字段名稱 數(shù)據(jù)類型修改表修改字段 alter table 表名 alter column 字段名稱 數(shù)據(jù)類型修改表刪除字段 alter table 表名 drop 字段名稱刪除表Drop table 表名 60第4章 SQLServer數(shù)據(jù)管理61本章目標了解SQL和T-

30、SQLT-SQL實現(xiàn)數(shù)據(jù)的增加、修改、刪除數(shù)據(jù)操作62SQL和T-SQL SQL(Structure Query Language,結構化查詢語言) 主要功能就是同各種數(shù)據(jù)庫建立聯(lián)系，進行溝通。用于存取數(shù)據(jù)以及查詢、更新和管理關系數(shù)據(jù)系統(tǒng) SQL語言主要包括 數(shù)據(jù)操縱語言（DML）用來操作數(shù)據(jù)庫中的數(shù)據(jù)，包括插入數(shù)據(jù)（INSERT）、修改數(shù)據(jù)（UPDATE）、刪除數(shù)據(jù)（DELETE）、查詢數(shù)據(jù)（SELECT）等操作 數(shù)據(jù)定義語言（DDL）用于創(chuàng)建或修改數(shù)據(jù)庫里的對象，如創(chuàng)建Create table、修改Alter table 、刪除Drop table等 數(shù)據(jù)控制語言（DCL） 用來控制用戶對

31、數(shù)據(jù)庫數(shù)據(jù)的訪問權限,如：Grant、Revoke等63T-SQL介紹 T-SQLT-SQL（Transact-SQL）是SQL Server的核心 所有與SQL Server實例通信的應用程序，都是通過發(fā)送T-SQL語句到服務器來完成對數(shù)據(jù)庫的操作 T-SQL是對標準SQL的擴展 是在標準SQL的基礎上增加了變量定義、函數(shù)、流程控制等編程要素 語法約定 語句不區(qū)分大小寫 64插入數(shù)據(jù) 65語法格式 INSERT INTO 表名（列名，n) VALUES (值，n) 【例4-1】 向BookShopDb 數(shù)據(jù)庫的UserInfo表中插入所有列數(shù)據(jù)USE BookShopDb GO -批處理結束

32、標志，表示執(zhí)行 INSERT INTO UserInfo(UserID,UserName,Sex,Age,hiredate,tele,Addr) VALUES(001,王琳琳,女,26,2005-1-24,07313677867,湖南長沙)INSERT INTO UserInfo VALUES(002,張家輝,男,27,2004-3-5,02034657654,廣東廣州)INSERT INTO UserInfo(UserID,UserName) VALUES(003,張?zhí)锾?插入部分列時列名不能省略 插入所有列時列名可省略 插入數(shù)據(jù)66 【例4-3】向UserInfo表插入數(shù)據(jù)，嘗試輸入性別為

33、其他內(nèi)容 INSERT INTO UserInfo(UserID,UserName,Sex,Age,hiredate,tele,Addr)VALUES(004,周晨陽,中,30,2003-4-2,02734652345,湖北宜昌)標識列字段數(shù)據(jù)的插入 【例4-5】向Category表插入數(shù)據(jù), Category帶有標識列CategoryID INSERT INTO Category(CategoryName) VALUES(計算機基礎理論) 使用SELECT INTO 語句拷貝表SELECT INTO 可將幾個表或視圖中的數(shù)據(jù)組合成一個表 查看表數(shù)據(jù)：select * from copyUse

34、rInfo若只拷貝表結構： SELECT * INTO NewUserInfo FROM UserInfo where 1=2 67【例4-9】 使用SELECT . INTO拷貝UserInfo表年齡大于等于30的數(shù)據(jù) 生成一新表CopyUserInfoSELECT * INTO CopyUserInfo FROM UserInfo WHERE Age=30 更新數(shù)據(jù)語法格式68UPDATE 表名 SET WHERE 更新條件【例4-10】修改UserInfo表數(shù)據(jù)，把UserID為007的用戶的住址改為甘肅酒泉UPDATE UserInfo SET Addr =甘肅酒泉 WHERE Use

35、rID =007修改數(shù)據(jù)69 【例4-11】如圖完善UserInfo表數(shù)據(jù),把用戶號003用戶的年齡、參加工作日期分別修改為33，2004-8-30 UPDATE UserInfo SET Age =33, hiredate =2004-8-30 WHERE UserID =003【例4-12】把用戶001的參加工作日期加1天，地址改為湖南湘潭UPDATE UserInfoSET hiredate=hiredate+1, Addr=湖南湘潭WHERE UserID =001 注意：修改數(shù)據(jù)如果只修改一條記錄，一般都按主鍵字段修改。刪除數(shù)據(jù) 語法：70DELETE FROM 表名 WHERE 條

36、件 【例4-14】刪除參加工作日期在2004-1-1之后的用戶信息DELETE FROM UserInfo WHERE hiredate2004-1-1 【例4-15】 刪除BookInfo表中書號是B0001的記錄 DELETE FROM BookInfo WHERE BookID=B0001 因為要刪除BookInfo表的書號是B0001的記錄被SalesDetails表引用，所以必須先刪除SalesDetails表的相應記錄，才能刪除BookInfo表的記錄 使用TRUNCATE TABLE語句 語法：71 TRUNCATE TABLE 表名【例4-16】 刪除CopyUserInfo表

37、的所有數(shù)據(jù) TRUNCATE TABLE CopyUserInfo總結增加單行數(shù)據(jù)用INSERT INTOVALUES語句修改數(shù)據(jù)用UPDATE 表名 SET 字段名=新值 WHERE 條件。語句刪除數(shù)據(jù)用DELETE FROM WHERE 語句快速刪除整個表中的數(shù)據(jù)用TRUNCATE TABLE 語句 TRUNCATE TABLE 表名72第5章 簡單查詢和函數(shù)73本章目標掌握查詢的基本語法掌握各種函數(shù)的應用字符串函數(shù)日期函數(shù)數(shù)字函數(shù)掌握函數(shù)在查詢中的應用74查詢75查詢請求查詢結果集結果集如何查詢查詢性別“男”的所有用戶76查詢 語法：77 SELECT FROM WHERE ORDER

38、BY ASC或DESC查詢數(shù)據(jù)返回所有的行和列查詢表中所有字段時，可以用星號*來代替字段名返回部分行和列篩選部分字段，必須明確指定字段名【例5-2】查詢BookInfo表的BookName(書名),作者(Author),出版日期(publishDate),類別ID(CategoryID),價格(Price)等字段的數(shù)據(jù) 78 SELECT * FROM BookInfo SELECT BookName,Author,publishDate,CategoryID FROM BookInfo查詢-使用別名79SELECT bookName as 書名,Author as 作者,publishDat

39、e as 出版日期,CategoryID as 類別,price as 價格FROM bookInfo 1，用as關鍵字 【例5-3】 用中文名稱來顯示書的信息,3種不同使用別名方法 2，用空格3，用=SELECT bookName 書名,Author 作者,publishDate 出版日期,CategoryID 類別,price 價格FROM bookInfoSELECT 書名= bookName,作者= Author, 出版日期=publishDate,類別=CategoryID , 價格=price FROM bookInfo查詢-使用where使用WHERE子句篩選滿足條件的數(shù)據(jù)行 8

40、0【例5-4】查詢2007年以后出版的所有書籍的書名、作者、出版日期和價格SELECT bookName,author,publishDate,price FROM BookInfo WHERE PublishDate2007-01-01函數(shù)T-SQL函數(shù)表示可以接受零個、一個或多個輸入值，并返回一個標量值或一組值81 表5-1 常用T-SQL函數(shù)函數(shù)類別 說明 字符串函數(shù) 對字符串（char 或 varchar）輸入值執(zhí)行運算，然后返回一個字符串或數(shù)字值。日期和時間函數(shù) 對日期和時間輸入值執(zhí)行運算，然后返回字符串、數(shù)字或日期和時間值。數(shù)學函數(shù) 基于作為函數(shù)的參數(shù)提供的輸入值執(zhí)行運算，然后返回

41、數(shù)字值。系統(tǒng)函數(shù)返回系統(tǒng)配置信息、返回有關數(shù)據(jù)庫和數(shù)據(jù)庫對象的信息、執(zhí)行運算后返回 SQL Server 實例中有關值、對象和設置的信息，返回有關用戶和角色的信息等。字符串函數(shù) 字符串函數(shù) 字符串函數(shù)用于對字符串數(shù)據(jù)進行處理，如字符串截取、查找子串、計算字符串長度等，并返回一個字符串或數(shù)值 82字符串函數(shù)83字符串函數(shù)的使用84 SELECT bookName,Author,PATINDEX(%數(shù)據(jù)庫%,bookName) POSFROM BookInfo WHERE PATINDEX(%數(shù)據(jù)庫%,bookName)0【例5-8】使用日期函數(shù)獲取今天的日期時間及明天的日期和時間【例5-7】使用

42、PATINDEX函數(shù)查詢BookInfo表中包含數(shù)據(jù)庫的書名和作者以及數(shù)據(jù)庫出現(xiàn)的位置 SELECT GETDATE() 今天, DATEADD(DAY,1,GETDATE() 明天日期和時間函數(shù)85系統(tǒng)函數(shù)86總結掌握查詢的基本語法從表中篩選記錄的時候用SELECT語句通過給列起別名的方式簡化代碼掌握各種函數(shù)的應用字符串函數(shù)日期函數(shù)數(shù)字函數(shù)掌握函數(shù)在查詢中的應用87第6章 檢索數(shù)據(jù)88本章目標掌握WHERE子句中運算符的使用掌握使用Top關鍵字和使用Distinct消除重復行掌握模糊查詢Like,between,in的用法89在WHERE子句中使用運算符 SELECT通過WHERE子句構造記

43、錄的篩選條件，從而選擇出滿足條件的記錄 記錄的篩選條件：運算符 函數(shù)使用 IS NULL條件90運算符使用比較運算符和邏輯運算符 91【例6-1】 查詢BookInfo表中價格低于的書名、作者和價格 SELECT BookName,Author,Price FROM BookInfo WHERE Price 40【例6-2】 查詢BookInfo表中價格在30到80之間（包括邊界） 的書名、價格和說明 SELECT BookName,price,Content FROM BookInfo WHERE Price =30 使用 IS NULL條件 使用 IS NULL條件92【例6-3】 查詢U

44、serInfo中電話（tele）為空的用戶信息 SELECT UserID,UserName,Sex,Age,hiredate,tele,Addr FROM UserInfo WHERE tele is null 查詢中使用函數(shù)93【例6-5】 在BookInfo表中，查詢書名長度小于10的圖書書名、作者和書名長度 SELECT bookname,Author ,LEN(bookName) FROM BookInfo WHERE LEN(bookname)10設置結果集格式 使用ORDER BY排序數(shù)據(jù) 94【例6-6】 在BookInfo表中，按出版日期的先后順序，查詢價格低于45的圖書的書

45、號、書名、價格、作者和出版日期。 SELECT bookid,bookName,Price,Author,PublishDate FROM BookInfo WHERE Price b.PriceWHERE b.BookID =B0002總結對數(shù)據(jù)進行統(tǒng)計使用SQL內(nèi)置的聚合函數(shù)MaxMinSumAvgCount對數(shù)據(jù)進行分組用GROUP BY，分組條件篩選用Having查詢的數(shù)據(jù)來自于多個表用關聯(lián)查詢關聯(lián)查詢連接內(nèi)聯(lián)接內(nèi)聯(lián)接篩選多表中完全匹配的行外部聯(lián)接左外聯(lián)接以左表為主篩選數(shù)據(jù)右外聯(lián)接以右表為主篩選數(shù)據(jù)自聯(lián)接自聯(lián)接將表自身通過別名關聯(lián)125第8章 子查詢126本章目標掌握子查詢的使用子查詢

46、返回單一值子查詢返回值列表子查詢的存在性測試EXISTS掌握集合運算并集(Union)交集(Intersection)減(Except)127子查詢問題：顯示價格最高書籍的書號、書名和價格 解決方法：查詢出最高價格select MAX(price) from BookInfo以價格為條件查詢出書號、書名和價格select bookid,bookname,price from BookInfo where Price=(select MAX(price) from BookInfo) 128外部查詢內(nèi)部查詢子查詢子查詢就是一個嵌套在 SELECT、INSERT、UPDATE 或 DELETE 語

47、句或其他子查詢中的查詢，子查詢也稱為內(nèi)部查詢，而包含子查詢的語句也稱為外部查詢 子查詢的SELECT 查詢總是使用圓括號括起來?；菊Z法：129SELECT 列名.FROM 表名WHERE 條件 =(SELECT 列名 FROM 表名 WHERE 條件）返回單個值的子查詢 當子查詢返回單個值時，外部查詢條件和子查詢之間使用比較運算符（、=、(SELECT AVG(price) FROM BookInfo) 比較運算符返回單個值的子查詢查詢書名為“JavaWeb開發(fā)技術詳解”的出版社ID和出版年限 SELECT publisherID FROM BookInfo WHERE BookName =

48、JavaWeb開發(fā)技術詳解SELECT YEAR(publishDate) FROM BookInfo WHERE BookName =JavaWeb開發(fā)技術詳解 代碼為：SELECT BookID,BookName,publishDate,PublisherID FROM BookInfo WHERE PublisherID =(SELECT publisherID FROM BookInfo WHERE BookName =JavaWeb開發(fā)技術詳解) AND YEAR(PublishDate) = (SELECT YEAR(publishDate) FROM BookInfo WHERE

49、 BookName =JavaWeb開發(fā)技術詳解 )131【例8-2】 查詢具有和“JavaWeb開發(fā)技術詳解”相同出版社和相同出版年限的書籍的書號、書名和出版日期解決方法：返回值列表的子查詢 子查詢返回值列表即子查詢返回的不是單個值，外部查詢的檢索條件WHERE和子查詢之間使用以下運算符 132返回值列表的子查詢【例8-3】查詢有銷售記錄的人員信息先求子查詢，即在銷售主表SalesMaster中查找銷售人員ID SELECT UserID FROM SalesMaster因為銷售人員不止一個，所以外部查詢和子查詢之間使用INSELECT * FROM UserInfo WHERE UserI

50、D IN (SELECT UserID FROM SalesMaster) 133返回值列表的子查詢【例8-3】顯示價格高于類別JAVA的所有書籍的書號、書名、作者和價格。分析：先求出類別是“JAVA“的所有書籍的價格列表 SELECT Price,BookID,CategoryID FROM BookInfo WHERE CategoryID = (SELECT categoryid FROM Category WHERE CategoryName =JAVA)外部查詢和子查詢之間應該使用ALLSELECT BookID ,BookName ,Author,Price FROM BookIn

51、fo WHERE Price ALL(SELECT Price FROM BookInfo WHERE CategoryID =(SELECT categoryid FROM Category WHERE CategoryName =JAVA) ) 134值列表EXISTS存在性測試相關子查詢在子查詢中引用外部查詢的列，即子查詢的執(zhí)行與外部查詢有關 135 【例8-4】顯示同類圖書中價格最高的的書籍信息，顯示內(nèi)容包括書號、書名、價格和類別。分析：對BookInfo表的每一條記錄，與同類圖書的價格比較，求出最高價格的書籍SELECT BookID ,BookName ,Price ,Catego

52、ryID FROM BookInfo a WHERE a.Price =( SELECT max(b.price) FROM BookInfo b WHERE a.CategoryID =b.CategoryID ) EXISTS存在性測試EXISTS存在性測試 指定一個子查詢，測試行是否存在，也就是對于外部查詢的每一行，檢查子查詢是否是空集合，如不是空集合，主查詢返回值，否則主查詢沒有返回值 該題目涉及銷售細表SalesDetails和書籍表BookInfo,因為查詢的是書籍信息，所以BookInfo表應該放在外部查詢位置，銷售細表SalesDetails應放在子查詢位置 136【例8-5】

53、查詢已銷售的書籍信息，顯示內(nèi)容包括書號、書名和作者。SELECT BookID,BookName,Author FROM BookInfo WHERE EXISTS(SELECT * FROM SalesDetails WHERE BookID=BookInfo.BookID)NOT Exists子查詢【例8-6】查詢沒有銷售記錄的用戶信息，顯示內(nèi)容包括用戶ID、用戶名和入職月份。分析：該題目外部查詢是用戶UserInfo表，子查詢是銷售主表SalesMaster,由于查詢的是沒有銷售記錄的用戶，所以使用NOT EXISTS關鍵字判斷子查詢是否有記錄返回 137用戶id用戶idSELECT a

54、.UserId,a.UserName,YEAR(a.hiredate) 入職年份FROM UserInfo a WHERE NOT EXISTS (SELECT * FROM SalesMaster b WHERE b.UserID=a.UserID)集合操作 集合操作是一種以兩個或多個SELECT 查詢結果集為操作對象，通過對這些查詢結果集實施并、交、差等集合運算，獲取新的結果集的一種查詢方法 集合操作的運算符主要包括以下3種 并集(UNION)交集(INTERSECT)差(EXCEPT)138并集（UNION） 并集并集是將兩個或更多查詢的結果合并為單個結果集，該結果集包含聯(lián)合查詢中的所有

55、查詢的全部行 UNION的使用限制：集合操作符所涉及的查詢應有相同的列數(shù)，對應的列必須具有相同的數(shù)據(jù)類型。所顯示的行上面的標簽來自第一個SELECT語句。UNION和UNION ALLUNION ALL:返回兩個查詢所選定的所有行，包括重復行UNION:未指定ALL,返回的結果集中不包括重復行 139并集（UNION）【例8-7】 把“機械工業(yè)出版社”出版的圖書和“人民郵電出版社”出版社的圖書一起顯示輸出查詢“機械工業(yè)出版社”的圖書的T-SQL如下查詢“人民郵電出版社”的圖書的T-SQL如下 140SELECT BookID ,BookName,Author,PublisherID FROM

56、BookInfo WHERE PublishERID=(SELECT PublisherID FROM Publisher WHERE PublisherName =機械工業(yè)出版社)SELECT BookID ,BookName,Author,PublisherID FROM BookInfo WHERE PublishERID=(SELECT PublisherID FROM Publisher WHERE PublisherName =人民郵電出版社 )SELECT BookID ,BookName,Author,PublisherID FROM BookInfo WHERE Publis

57、hERID=(SELECT PublisherID FROM Publisher WHERE PublisherName =機械工業(yè)出版社)UNION SELECT BookID ,BookName,Author,PublisherID FROM BookInfo WHERE PublishERID=(SELECT PublisherID FROM Publisher WHERE PublisherName =人民郵電出版社)并集（UNION）【例8-8】將新數(shù)據(jù)顯示在查詢中141SELECT 009 AS 用戶ID,張三 as 用戶名, 男 as 性別, 20 as 年齡UNION SELE

58、CT UserID ,UserName,Sex,Age FROM UserInfo交集(INTERSECT) 交集(INTERSECT) INTERSECT運算符比較兩個查詢的結果，返回重復值，即返回兩個結果集中的交集，兩者都包含的記錄 INTERSECT運算符的使用限制與UNION運算符相同?！纠?-9】查詢有銷售記錄的書籍142SELECT BookID as 書ID FROM SalesDetailsINTERSECT SELECT BookID FROM BookInfo 差(EXCEPT) 差(EXCEPT)運算符比較兩個查詢的結果，返回非重復值。EXCEPT運算符獲取在結果集A中但

59、不在結果集B中的記錄，即結果集A減去結果集B EXCEPT運算符的使用限制與UNION運算符相同 143差(EXCEPT)【例8-10】查詢BookInfo表中價格低于40的書籍中去掉2006年出版的書籍信息，顯示內(nèi)容包括書號、書名、價格和出版日期 144SELECT BookID 書ID ,BookName 書名 ,Price 價格 ,PublishDate 出版日期 FROM BookInfo WHERE Price 40EXCEPTSELECT BookID ,BookName ,Price ,PublishDate FROM BookInfo WHERE YEAR(PublishDat

60、e) =2006總結子查詢就是嵌套在SELECT，INSERT，UPDATE和DELETE中的查詢。一般子查詢不依賴于外部查詢，執(zhí)行時先執(zhí)行內(nèi)部查詢再執(zhí)行外部查詢，通常用比較運算符和IN，NOT IN關鍵字。相關子查詢即子查詢的條件依賴于外部查詢，執(zhí)行時先執(zhí)行外 部查詢，然后根據(jù)外部查詢返回的記錄行數(shù)重復執(zhí)行內(nèi)部查詢通 常用EXISTS，NOT EXISTS關鍵字。集合運算結果集要進行合并時用UNION運算符。結果集取交集用INTERSECT運算符。結果集相減用EXCEPT運算符。145第9章 數(shù)據(jù)庫的設計本章目標了解設計數(shù)據(jù)庫的步驟掌握數(shù)據(jù)庫設計模型ER模型掌握使用范式規(guī)范化數(shù)據(jù)庫為什么需要