1、數(shù)據(jù)庫(kù)原理與應(yīng)用,SQL Server,2,目錄,第一部分 SQL Server 2005的安裝與管理器 第二部分 數(shù)據(jù)庫(kù)管理 第三部分 數(shù)據(jù)表管理 第四部分 數(shù)據(jù)管理 第五部分 視圖與索引 第六部分 T-SQL編程 第七部分 事務(wù) 第八部分 游標(biāo) 第九部分 存儲(chǔ)過程 第十部分 觸發(fā)器 第十一部分 權(quán)限以及安全 第十二部分 數(shù)據(jù)庫(kù)設(shè)計(jì),第一部分SQL Server 2005的安裝與管理器,1. 數(shù)據(jù)庫(kù)的應(yīng)用 2. 數(shù)據(jù)庫(kù)相關(guān)概念 3. 關(guān)系數(shù)據(jù)庫(kù) 4. SQL Server 2005 5. SQL Server 2005的啟動(dòng),總結(jié),數(shù)據(jù)庫(kù)的應(yīng)用,超市收銀員掃描條碼，就能調(diào)出商品價(jià)格，便于快速

2、結(jié)賬。 火車售票員錄入出發(fā)地和目的就能調(diào)出車次、價(jià)格及車票剩余數(shù)量，利于快速售票。 到營(yíng)業(yè)廳輸入手機(jī)號(hào)和時(shí)間段就能打印出通話記錄單 。 錄入你的游戲賬號(hào)和密碼就能調(diào)出玩家的信息 。 網(wǎng)站發(fā)布的新聞、可轉(zhuǎn)載的網(wǎng)絡(luò)小說、網(wǎng)絡(luò)視頻、博客文章。,都在使用數(shù)據(jù)庫(kù),數(shù)據(jù)庫(kù)和應(yīng)用程序 2-1,數(shù)據(jù)庫(kù)服務(wù)器,響應(yīng)和提供數(shù)據(jù),應(yīng)用程序,操作和查詢,數(shù) 據(jù) 庫(kù),數(shù)據(jù)庫(kù)和應(yīng)用程序 2-2,應(yīng)用程序 作用：響應(yīng)操作并顯示結(jié)果、向數(shù)據(jù)庫(kù)請(qǐng)求數(shù)據(jù) 要求：美觀、操作簡(jiǎn)單方便,數(shù)據(jù)庫(kù) 作用：存儲(chǔ)數(shù)據(jù)、檢索數(shù)據(jù)、生成新的數(shù)據(jù) 要求：統(tǒng)一、安全、性能等,數(shù)據(jù)庫(kù)優(yōu)點(diǎn),存儲(chǔ)大量數(shù)據(jù)，方便檢索和訪問 保持?jǐn)?shù)據(jù)信息的一致、完整 共享和安

3、全 通過組合分析，產(chǎn)生新的有用信息,數(shù)據(jù)庫(kù)的相關(guān)概念3-1,數(shù)據(jù)Data 描述事物的符號(hào) 可以有多種形式：數(shù)字、文字、圖形、圖像、聲音等 數(shù)據(jù)庫(kù) 存放數(shù)據(jù)的地方 需要長(zhǎng)期存放在計(jì)算機(jī)內(nèi)的、有組織的、可共享的數(shù)據(jù)集合 物理形式是存儲(chǔ)在磁盤上的一個(gè)或多個(gè)數(shù)據(jù)文件 數(shù)據(jù)庫(kù)管理系統(tǒng) 是操縱和管理數(shù)據(jù)庫(kù)的大型軟件 可建立、使用和維護(hù)數(shù)據(jù)庫(kù) 它對(duì)數(shù)據(jù)庫(kù)進(jìn)行統(tǒng)一管理和控制，保證數(shù)據(jù)庫(kù)的安全性和完整性,數(shù)據(jù)庫(kù)的相關(guān)概念3-2,數(shù)據(jù)庫(kù)管理員 管理和維護(hù)數(shù)據(jù)庫(kù)的人 數(shù)據(jù)庫(kù)系統(tǒng) 一般指數(shù)據(jù)庫(kù)、數(shù)據(jù)庫(kù)管理系統(tǒng)以及運(yùn)行硬件、應(yīng)用程序、數(shù)據(jù)庫(kù)管理員和用戶的集合 數(shù)據(jù)模型 指數(shù)據(jù)庫(kù)管理系統(tǒng)中數(shù)據(jù)的存儲(chǔ)結(jié)構(gòu),數(shù)據(jù)庫(kù)的相關(guān)概念

4、3-3,11,數(shù)據(jù)庫(kù)的發(fā)展史,初級(jí)階段第一代數(shù)據(jù)庫(kù) 出現(xiàn)了層次模型、網(wǎng)狀模型的數(shù)據(jù)庫(kù) 中級(jí)階段第二代數(shù)據(jù)庫(kù) 關(guān)系型數(shù)據(jù)庫(kù)和結(jié)構(gòu)化查詢語(yǔ)言 高級(jí)階段新一代數(shù)據(jù)庫(kù) “面向?qū)ο蟆毙蛿?shù)據(jù)庫(kù),定義,疑問,使用數(shù)據(jù)表存儲(chǔ)數(shù)據(jù)!,關(guān)系數(shù)據(jù)庫(kù),某公司的員工信息,行(記錄),列（字段）,那么實(shí)體和屬性具體指的是什么呢？,關(guān)系數(shù)據(jù)庫(kù)數(shù)據(jù)表,關(guān)系數(shù)據(jù)庫(kù)實(shí)體,關(guān)系數(shù)據(jù)庫(kù)屬性,例如,屬性（字段）,不同的員工屬于不同的實(shí)體,16,SQL Server 2005概述2-1,MS SQL Server 2005是Microsoft開發(fā)的基于關(guān)系數(shù)據(jù)庫(kù)的管理系統(tǒng) 采用“請(qǐng)求-應(yīng)答”工作方式,SQL Server 2005 針對(duì)

5、不同用戶群體提供多個(gè)版本。 易用性好，受廣大用戶青睞。,版本 企業(yè)版(Enterprise Edition) 標(biāo)準(zhǔn)版(Standard Edition) 開發(fā)板(Developer Edition) 工作組版(Workgroup Edition) 精簡(jiǎn)版(Express Edition),SQL Server 2005概述2-2,SQL Server2005安裝要求,最少：1.0GHz 奔騰以上處理器。 推薦：2.0GHz或速度更快的處理器 。,最?。?12MB。 推薦：2.048GB或更大的內(nèi)存 。,完全安裝1.7GB。,硬件要求,軟件要求,SQL Server 2005的安裝,演示整個(gè)安裝

6、過程,SQL Server2005管理工具,Microsoft SQL Server Management Studio,開發(fā)人員和DBA最常用的管理工具，使用它可以執(zhí)行許多 SQL Server的核心管理任務(wù)。例如：創(chuàng)建數(shù)據(jù)庫(kù)、創(chuàng)建表、 視圖、存儲(chǔ)過程、執(zhí)行SQL語(yǔ)句等。,SQL Server Profiler,SQL Server2005外圍應(yīng)用配置器,能幫助系統(tǒng)管理員監(jiān)視數(shù)據(jù)庫(kù)和服務(wù)器的行為, 比如死鎖的數(shù)量，致命的錯(cuò)誤，跟蹤DELETE、 INSERT、 UPDATE 等Transact-SQL 語(yǔ)句和 存儲(chǔ)過程 。 便于后期一步一步分析。,提供對(duì)SQL Server2005本地實(shí)例和

7、遠(yuǎn)程實(shí)例 的外圍應(yīng)用的更多控制，提高了可管理性和安全性。,SQL Server 配置管理器,通過SQL Server配置管理器是對(duì)Microsoft SQL Server 提供的各種服務(wù)進(jìn)行登錄身份配置、網(wǎng)絡(luò)協(xié)議配置及服 務(wù)的停止啟動(dòng)配置；另外還提供一些高級(jí)選項(xiàng)。其中登 錄身份和各種服務(wù)停止和啟動(dòng)配置是較為常用.,21,停止和啟動(dòng)SQL Server,在對(duì)SQL Server數(shù)據(jù)庫(kù)進(jìn)行任何操作之前，必須啟動(dòng)本地或遠(yuǎn)程SQL Server服務(wù)器 使用操作系統(tǒng)服務(wù)管理器 使用SQL Server Configuration Manager(配置管理) 使用SQL Server Mangement

8、Studio(管理平臺(tái)) 啟動(dòng)SQL Server Mangement Studio Windows身份驗(yàn)證 SQL Server身份驗(yàn)證,演示整個(gè)啟動(dòng)/停止過程,連接到SQL Server2005,演示連接到服務(wù)器,選擇服務(wù)器,默認(rèn)本機(jī),錄入登錄名和密碼,單擊連接,連接成功后，顯示SQL Server企業(yè)管理器,23,第一部分總結(jié),掌握技能 SQL Server 2005的安裝 SQL Server服務(wù)的啟動(dòng)/停止 SQL Server管理平臺(tái)的進(jìn)入 了解的概念 兩種身份驗(yàn)證 數(shù)據(jù)庫(kù)相關(guān)概念以及相應(yīng)的英文拼寫 4種數(shù)據(jù)模型,層次模型,網(wǎng)狀模型,關(guān)系模型2-1,27,關(guān)系模型2-2,記錄和記錄

9、之間通過屬性之間的關(guān)系來進(jìn)行連接 保證數(shù)據(jù)獨(dú)立性，并形成數(shù)據(jù)集之間的關(guān)系,伸縮性和擴(kuò)展性，處理復(fù)雜的數(shù)據(jù)模型和關(guān)系模型。 利用繼承的方法實(shí)現(xiàn)數(shù)據(jù)的重用，避免數(shù)據(jù)冗余。 提高了對(duì)大對(duì)象（文本、圖像、視頻）信息的描述、操縱和檢索能力。,缺乏像關(guān)系型數(shù)據(jù)模型那樣堅(jiān)實(shí)成熟的理論基礎(chǔ)。 糟糕的運(yùn)行效率。,優(yōu)點(diǎn)：,缺點(diǎn)：,面向?qū)ο竽Ｐ?第二部分?jǐn)?shù)據(jù)庫(kù)管理,1. 數(shù)據(jù)庫(kù)分類 2. 數(shù)據(jù)庫(kù)文件 3. 數(shù)據(jù)庫(kù)的創(chuàng)建、修改（管理平臺(tái)） 4. 數(shù)據(jù)庫(kù)的分離和附加 5. 數(shù)據(jù)庫(kù)的脫機(jī)和聯(lián)機(jī) 6. 數(shù)據(jù)庫(kù)的刪除 7. 數(shù)據(jù)庫(kù)的創(chuàng)建、刪除（SQL語(yǔ)句）,總結(jié),數(shù)據(jù)庫(kù)的分類,系統(tǒng)數(shù)據(jù)庫(kù),用戶數(shù)據(jù)庫(kù),數(shù) 據(jù) 庫(kù),創(chuàng)建數(shù)據(jù)庫(kù)-

10、數(shù)據(jù)庫(kù)文件的構(gòu)成,數(shù) 據(jù) 庫(kù),必備文件,可選文件,一個(gè)數(shù)據(jù)庫(kù)只能有一個(gè)主數(shù)據(jù)文件,一個(gè)數(shù)據(jù)庫(kù)可有多個(gè)日志文件,一個(gè)數(shù)據(jù)庫(kù)可有多個(gè)次數(shù)據(jù)文件,32,采用多個(gè)數(shù)據(jù)庫(kù)文件來存儲(chǔ)數(shù)據(jù)的優(yōu)點(diǎn),數(shù)據(jù)庫(kù)文件可以不斷擴(kuò)充而不受操作系統(tǒng)文件大小的限制 可以將數(shù)據(jù)庫(kù)文件存儲(chǔ)在不同的硬盤中，這樣可以同時(shí)對(duì)幾個(gè)硬盤做數(shù)據(jù)存取提高了數(shù)據(jù)處理的效率，對(duì)于服務(wù)器型的計(jì)算機(jī)尤為有用,創(chuàng)建數(shù)據(jù)庫(kù)-Systop數(shù)據(jù)庫(kù),主數(shù)據(jù)文件,日志文件,次數(shù)據(jù)文件,可分別存儲(chǔ)在不同的磁盤中,演示創(chuàng)建數(shù)據(jù)庫(kù),管理和維護(hù)：分離數(shù)據(jù)庫(kù),演示分離數(shù)據(jù)庫(kù),管理和維護(hù)：附加數(shù)據(jù)庫(kù),演示附加數(shù)據(jù)庫(kù),管理和維護(hù)：脫機(jī)和聯(lián)機(jī),演示設(shè)置數(shù)據(jù)庫(kù)的脫機(jī)和聯(lián)機(jī)狀態(tài),管

11、理和維護(hù)：刪除數(shù)據(jù)庫(kù),演示刪除數(shù)據(jù)庫(kù),回顧,數(shù)據(jù)庫(kù)發(fā)展經(jīng)歷的4種數(shù)據(jù)模式，分別是什么？ 一個(gè)SQL Server數(shù)據(jù)庫(kù)一般可包含哪幾種文件？擴(kuò)展名分別是什么？ 數(shù)據(jù)庫(kù)服務(wù)的啟動(dòng)和關(guān)閉 數(shù)據(jù)庫(kù)的鏈接的身份驗(yàn)證模式分別是什么？ 數(shù)據(jù)庫(kù)的創(chuàng)建、刪除 數(shù)據(jù)庫(kù)的分離、附加、脫機(jī)、聯(lián)機(jī),為什么使用T-SQL操作數(shù)據(jù)庫(kù),管理數(shù)據(jù)庫(kù)有兩種方法： 使用Sql Server Management studio管理數(shù)據(jù)庫(kù) 使用Transact-Sql語(yǔ)言管理數(shù)據(jù)庫(kù) 將數(shù)據(jù)庫(kù)移植到客戶的計(jì)算機(jī)，如果考慮SQL SERVER各種版本的兼容性，最好的辦法就是編寫比較通用的SQL語(yǔ)句創(chuàng)建數(shù)據(jù)庫(kù)。,結(jié)構(gòu)化查詢語(yǔ)言 SQL S

12、tructured Query Language SQL是RDBMS的標(biāo)準(zhǔn)語(yǔ)言，遵循ANSI(美國(guó)國(guó)家標(biāo)準(zhǔn)協(xié)會(huì))所制定的標(biāo)準(zhǔn)。 SQL分類 數(shù)據(jù)查詢語(yǔ)言（DQL）：Select * FROM ABC 數(shù)據(jù)操作語(yǔ)言（DML）：Update ABC SET A = “CH” 數(shù)據(jù)控制語(yǔ)言（DCL）：Revoke Create Table FROM U1 數(shù)據(jù)定義語(yǔ)言（DDL）：Create database ABC 通用命令語(yǔ)言（CCL）：Declare CS Cursor For ,SQL 的分類,創(chuàng)建數(shù)據(jù)庫(kù)的語(yǔ)法2-1,數(shù)據(jù)庫(kù)的數(shù)據(jù)文件主要參數(shù),可以省略,語(yǔ)法,創(chuàng)建數(shù)據(jù)庫(kù)的語(yǔ)法2-2,數(shù)據(jù)庫(kù)的

13、日志文件主要參數(shù),創(chuàng)建數(shù)據(jù)庫(kù)示例1,數(shù)據(jù)文件的主要參數(shù),日志文件的主要參數(shù),教員演示,創(chuàng)建數(shù)據(jù)庫(kù)示例2,主數(shù)據(jù)文件,次要數(shù)據(jù)文件,創(chuàng)建數(shù)據(jù)庫(kù)示例2,日志文件,教員演示,創(chuàng)建數(shù)據(jù)庫(kù)時(shí)的錯(cuò)誤提示,如果Sql Server中已經(jīng)存在myschool數(shù)據(jù)庫(kù)，再次運(yùn)行代碼創(chuàng)建該數(shù)據(jù)庫(kù)，會(huì)出現(xiàn)什么情況呢？,演示：重復(fù)創(chuàng)建數(shù)據(jù)庫(kù),系統(tǒng)數(shù)據(jù)表Sysdatabases,Sql Sever2005安裝完成后，系統(tǒng)自帶master系統(tǒng)數(shù)據(jù)庫(kù)，sysdatabases 是其中一個(gè)系統(tǒng)數(shù)據(jù)表，存放所有數(shù)據(jù)庫(kù)的一些信息。,刪除數(shù)據(jù)庫(kù),語(yǔ)法,示例,刪除數(shù)據(jù)庫(kù)時(shí)的錯(cuò)誤提示,刪除某個(gè)數(shù)據(jù)庫(kù)前，一定要確保該數(shù)據(jù)庫(kù)存在；否則，系統(tǒng)

14、就會(huì)提示錯(cuò)誤。,演示：刪除數(shù)據(jù)庫(kù),完整的刪除數(shù)據(jù)庫(kù)的SQL語(yǔ)句,檢測(cè)某個(gè)記錄是否存在,完整的創(chuàng)建數(shù)據(jù)庫(kù)的SQL,注意事項(xiàng),SQL語(yǔ)句在書寫的時(shí)候不區(qū)分大小寫 一條語(yǔ)句可以寫在多行上 但不能將多條語(yǔ)句寫在一行上,回顧,SQL的全拼是什么？ T-SQL的分類 創(chuàng)建數(shù)據(jù)庫(kù)的create database語(yǔ)句 實(shí)訓(xùn)課習(xí)題,上機(jī)出現(xiàn)問題,格式的書寫 FileName屬性的賦值：包含路徑和文件名 Size和MaxSize屬性的賦值 默認(rèn)單位是MB 若不是MB作為單位，賦值時(shí)必須給設(shè)置對(duì)應(yīng)的單位 多個(gè)文件的分隔 數(shù)據(jù)庫(kù)的邏輯文件名不能重復(fù) 文件組的應(yīng)用,問題,創(chuàng)建了數(shù)據(jù)庫(kù)后，發(fā)現(xiàn)需要再添加一個(gè)數(shù)據(jù)庫(kù)文件，怎

15、樣實(shí)現(xiàn)？,提問,修改數(shù)據(jù)庫(kù)的語(yǔ)法,語(yǔ)法,57,第02章總結(jié),掌握概念 數(shù)據(jù)庫(kù)包含哪幾種文件？ 應(yīng)用技能 用戶數(shù)據(jù)庫(kù)的創(chuàng)建、刪除 數(shù)據(jù)庫(kù)與系統(tǒng)的分離與附加 數(shù)據(jù)庫(kù)與系統(tǒng)的脫機(jī)與聯(lián)機(jī),58,系統(tǒng)數(shù)據(jù)庫(kù),臨時(shí)數(shù)據(jù)庫(kù)，臨時(shí)數(shù)據(jù)可被自動(dòng)刪除，不需要特殊權(quán)限，系統(tǒng)中僅有一個(gè)，空間不足可自動(dòng)擴(kuò)展,系統(tǒng)數(shù)據(jù)庫(kù),用戶數(shù)據(jù)庫(kù),示例數(shù)據(jù)庫(kù),主控?cái)?shù)據(jù)庫(kù)，用于管理其他數(shù)據(jù)庫(kù)和保存SQL Server系統(tǒng)信息,模板數(shù)據(jù)庫(kù)，當(dāng)創(chuàng)建一個(gè)用戶數(shù)據(jù)庫(kù)時(shí)其內(nèi)容自動(dòng)復(fù)制到新數(shù)據(jù)庫(kù)中,來存儲(chǔ)計(jì)劃信息以及與備份和恢復(fù)相關(guān)的信息，SQL Server代理使用其安排報(bào)警、作業(yè)并記錄操作員,59,數(shù)據(jù)庫(kù)文件組,將多個(gè)數(shù)據(jù)文件存放在不同的磁盤上

16、并把多個(gè)數(shù)據(jù)文件組成一個(gè)或多個(gè)文件組 數(shù)據(jù)庫(kù)根據(jù)組內(nèi)數(shù)據(jù)文件的大小，按比例地寫入組內(nèi)所有數(shù)據(jù)文件，使多個(gè)磁盤同時(shí)并行工作、提高讀寫速度 在一個(gè)數(shù)據(jù)庫(kù)中可以創(chuàng)建多個(gè)文件組，而一個(gè)數(shù)據(jù)文件只能屬于一個(gè)文件組 事務(wù)日志文件不能屬于任何文件組 一個(gè)數(shù)據(jù)文件或文件組只被一個(gè)數(shù)據(jù)庫(kù)使用,第三部分?jǐn)?shù)據(jù)表的管理,1. 管理平臺(tái)創(chuàng)建數(shù)據(jù)表 2. 數(shù)據(jù)類型 3. 數(shù)據(jù)冗余 4. 數(shù)據(jù)完整性 5. 約束 6. 語(yǔ)句創(chuàng)建數(shù)據(jù)表,總結(jié),本章任務(wù),創(chuàng)建BooksManager數(shù)據(jù)庫(kù)中Authors和Books數(shù)據(jù)表,創(chuàng)建Authors和Books數(shù)據(jù)表,作者表,圖書表,本章目標(biāo),掌握數(shù)據(jù)表的創(chuàng)建過程 能夠創(chuàng)建約束來滿足數(shù)

17、據(jù)完整性的要求 掌握主外鍵的作用以及引用完整性的實(shí)施步驟,在SQL Server中建立數(shù)據(jù)表,圖書銷售系統(tǒng)一般應(yīng)用于圖書批發(fā)行業(yè)，是用來存儲(chǔ)圖書信息、作者信息、 庫(kù)存信息和銷售情況的，不同的信息需要不同的數(shù)據(jù)表來分別存儲(chǔ)。請(qǐng)問要 建立一個(gè)表來存儲(chǔ)作者信息，那么這個(gè)表應(yīng)包含哪些列？,提問,作者姓名,年齡,性別,電話,電子郵件,作者表,列名應(yīng)該有意義，再考慮圖書表有哪些列呢？,簡(jiǎn)要介紹圖書銷售系統(tǒng),在SQL Server中建立數(shù)據(jù)表,圖書表結(jié)構(gòu),那么如何在SQL Server2005的BooksManager數(shù)據(jù)庫(kù)中創(chuàng)建表呢？,創(chuàng)建數(shù)據(jù)庫(kù)表Authors,演示創(chuàng)創(chuàng)建表的過程.,數(shù)據(jù)類型,小結(jié),簡(jiǎn)述

18、創(chuàng)建數(shù)據(jù)表的過程？ 真假信息使用什么數(shù)據(jù)類型？ 圖片信息使用什么數(shù)據(jù)類型？ 年齡信息使用什么數(shù)據(jù)類型？,提問,數(shù)據(jù)表存在的問題(一)數(shù)據(jù)冗余,數(shù)據(jù)冗余,新增類別表(Categories),新增出版商表(Publishers),圖書表和作者表設(shè)計(jì)完成后，小新向圖書表中插入了幾條圖書信息， 觀察下面數(shù)據(jù)，能否發(fā)現(xiàn)一些問題呢？,作者表(Authors),數(shù)據(jù)表存在的問題(二)數(shù)據(jù)完整性,2、網(wǎng)狀模型,請(qǐng)看下面作者表中存儲(chǔ)的信息，能否發(fā)現(xiàn)不合理的數(shù)據(jù)？,提問,數(shù)據(jù)表中存在不正確、不準(zhǔn)確的數(shù)據(jù)，數(shù)據(jù)庫(kù)“失去了完整性”,數(shù)據(jù)表存在的問題(二)數(shù)據(jù)完整性,什么是數(shù)據(jù)完整性?,數(shù)據(jù)完整性的問題大多是由于設(shè)計(jì)引

19、起的，為了避免上述問題的發(fā)生我們應(yīng)重視表的前期設(shè)計(jì)。,下面列舉一些常用的數(shù)據(jù)完整性要求,數(shù)據(jù)完整性,數(shù)據(jù)表中是否存在重復(fù)數(shù)據(jù)？ 作者表中一個(gè)作者只能出現(xiàn)一次。 圖書表中每本圖書只能描述一次。 是否符合特定規(guī)則的要求。 會(huì)員信譽(yù)等級(jí)為90以上，才能 成為版主。,數(shù)據(jù)是否在允許的范圍內(nèi)？ 性別信息只能是“男”或“女”。 數(shù)據(jù)類型是否正確？ 生日必須是日期類型。 輸入的格式是否正確？ 電子郵件中應(yīng)包含“”。,數(shù)據(jù)完整性實(shí)體完整性,約束方法：主鍵約束、標(biāo)識(shí)列、唯一約束,主鍵約束,完善Authors表，增加主鍵約束，避免作者重復(fù)問題。,演示增加主鍵約束.,主鍵約束,設(shè)置AuthorID為主鍵后，再錄入重

20、復(fù)信息將給予提示。,演示效果.,選擇主鍵的原則,最少性 盡量選擇單個(gè)鍵作為主鍵。 穩(wěn)定性 盡量選擇數(shù)值更新少的列作為主鍵。,標(biāo)識(shí)列,有時(shí)在設(shè)計(jì)表的時(shí)候，表中各列都會(huì)出現(xiàn)重復(fù)信息的可能，不知道用 哪個(gè)字段作為主鍵好，怎么辦？,疑問,分析,在表中增加標(biāo)識(shí)列。 標(biāo)識(shí)列也稱自增字段，一般為數(shù)字類型，本身沒有實(shí)際意義，不屬于實(shí)體 屬性，只是用來區(qū)別不同行信息 。,演示增加標(biāo)識(shí)列.,標(biāo)識(shí)列,思考,標(biāo)識(shí)列必須是數(shù)字類型嗎？ 標(biāo)識(shí)列只能是數(shù)字類型。 如果標(biāo)識(shí)種子是2，標(biāo)識(shí)增量是3，那么增加2行數(shù)據(jù)，如果刪除最后那條數(shù)據(jù)后，再插入信息時(shí)，標(biāo)識(shí)列值應(yīng)該是多少？ 應(yīng)該是8. 刪除數(shù)據(jù)后，標(biāo)識(shí)列的數(shù)值依然繼續(xù)遞增。,

21、數(shù)據(jù)完整性域完整性,約束方法：限制數(shù)據(jù)類型、檢查約束、外鍵約束、默認(rèn)值、非空約束,檢查約束,設(shè)置檢查約束，以避免作者表中年齡數(shù)據(jù)在不合理的范圍。,演示增加檢查約束.,非空約束,如果表中某些字段為必填項(xiàng)，可設(shè)置非空約束。,演示增加非空約束.,默認(rèn)約束,演示增加默認(rèn)約束.,數(shù)據(jù)完整性引用完整性,李小旭,100,168901,4,高好,1,168901,3,紀(jì)曉嵐,50,178922,2,李云龍,5,165212,1,和珅,10,169876,1,訂購(gòu)人,訂購(gòu)數(shù)量,圖書編號(hào),訂單編號(hào),約束方法：外鍵約束,外鍵約束,演示設(shè)置外鍵.,建立關(guān)系圖,主表,子表,演示建立關(guān)系圖.,引用關(guān)系規(guī)則,不能將主表中關(guān)

22、聯(lián)列不存在的數(shù)據(jù)，插入到子表中。 圖書表中不能夠出現(xiàn)作者信息表中不存在的作者編號(hào)。 不能更改主表中的數(shù)據(jù)，而導(dǎo)致子表中數(shù)據(jù)的孤立。 如果作者表中的作者編號(hào)改變了，圖書表中作者編號(hào)也應(yīng)隨之改變。 不能刪除主表中的數(shù)據(jù)，而導(dǎo)致子表中數(shù)據(jù)的孤立 如果刪除了作者表中作者信息，那么圖書表中對(duì)應(yīng)的作者信息也應(yīng)隨之刪除。 刪除主表前，先刪子表 先刪圖書表、再刪除作者表。,逐一演示規(guī)則,可以設(shè)置級(jí)聯(lián)更新,可設(shè)置級(jí)聯(lián)刪除,數(shù)據(jù)完整性自定義完整性,約束方法：規(guī)則、存儲(chǔ)過程、觸發(fā)器,回顧,管理平臺(tái)創(chuàng)建數(shù)據(jù)表的步驟 數(shù)據(jù)完整性 指數(shù)據(jù)庫(kù)中數(shù)據(jù)的準(zhǔn)確性，從數(shù)據(jù)表中取得的數(shù)據(jù)是準(zhǔn)確的和可靠的。 實(shí)體完整性 域完整性 引用

23、完整性 自定義完整性 各種約束,表達(dá)式,創(chuàng)建數(shù)據(jù)表的語(yǔ)法2-1,語(yǔ)法,創(chuàng)建數(shù)據(jù)表的語(yǔ)法2-2,語(yǔ)法,創(chuàng)建數(shù)據(jù)表的示例,教員演示,系統(tǒng)數(shù)據(jù)表sysobjects,每個(gè)數(shù)據(jù)庫(kù)中都有一個(gè)系統(tǒng)表sysobjects，存儲(chǔ)了本數(shù)據(jù)庫(kù)所有的數(shù)據(jù)表、約束等信息。,刪除數(shù)據(jù)表,如果當(dāng)前數(shù)據(jù)庫(kù)已經(jīng)存在Authors表，再次創(chuàng)建時(shí)會(huì)引發(fā)錯(cuò)誤，應(yīng)該怎么辦？,完整的創(chuàng)建數(shù)據(jù)表的SQL,回顧SQL SERVER中的約束,添加約束的目的是什么？ 常用的約束類型有哪幾種？,提問,創(chuàng)建表時(shí)添加約束,教員演示,現(xiàn)有數(shù)據(jù)表中添加約束的語(yǔ)法,語(yǔ)法,現(xiàn)有數(shù)據(jù)表添加約束的示例,刪除約束,語(yǔ)法,示例,99,修改表,ALTER TABLE

24、 表名 ADD 列名 數(shù)據(jù)類型(長(zhǎng)度)NULL|NOT NULL | ALTER COLUMN 列名 數(shù)據(jù)類型(長(zhǎng)度)NULL|NOT NULL | DROP COLUMN 字段名 , n | ADD CONSTRAINT 約束定義 , n | DROP CONSTRAINT 約束名 , n | NOCHECK CONSTRAINT 約束名 | CHECK CONSTRAINT 約束名,100,使用ADD子句添加列,ALTER TABLE 表名 ADD 列名 數(shù)據(jù)類型(長(zhǎng)度) NULL | NOT NULL 若向已存在記錄的表中添加列，新添加的列可以設(shè)置允許為空；若不允許為空，則需指定默認(rèn)值,

25、101,使用DROP COLUMN子句刪除列,ALTER TABLE 表名 DROP COLUMN 字段名 , 在刪除列時(shí)，必須先刪除基于該列的索引和約束后，才能刪除該列,102,使用ALTER COLUMN子句修改列屬性,ALTER TABLE 表名 ALTER COLUMN 列名 數(shù)據(jù)類型(長(zhǎng)度) NULL | NOT NULL 在默認(rèn)狀態(tài)下，列可以被設(shè)置為空值，將一個(gè)原來允許為空值的列改為不允許為空值時(shí)，必須滿足列中沒有存放空值的記錄以及在列上沒有創(chuàng)建索引,103,使用NOCHECK CONSTRAINT子句使約束無效,ALTER TABLE 表名 NOCHECK CONSTRAINT

26、約束名 | CHECK CONSTRAINT約束名 該子句只對(duì)外鍵約束和檢查約束起作用,104,刪除表,DROP TABLE 表名 , n drop table 語(yǔ)句不能刪除系統(tǒng)表 如果一個(gè)表被其他表通過外鍵約束引用，那么必須先刪除設(shè)置了外鍵約束的表，或刪除其外鍵約束,總結(jié),創(chuàng)建數(shù)據(jù)庫(kù)或表時(shí)一般需要預(yù)先檢測(cè)是否存在該對(duì)象，如何檢測(cè)？ 數(shù)據(jù)庫(kù)從master系統(tǒng)數(shù)據(jù)庫(kù)的sysdatabases表中查詢 表從該數(shù)據(jù)庫(kù)的系統(tǒng)表sysobjects表中查詢 創(chuàng)建約束有哪兩種方式？,第3章 總結(jié),需要?jiǎng)?chuàng)建一個(gè)數(shù)據(jù)表，你如何創(chuàng)建，需要考慮哪些問題？ 在數(shù)據(jù)表中數(shù)據(jù)重復(fù)的現(xiàn)象就是數(shù)據(jù)冗余，解決數(shù)據(jù)冗余常用的辦

27、法是什么？ 數(shù)據(jù)完整性的概念是什么？ 通過哪些約束來保證數(shù)據(jù)的實(shí)體完整性 ？,條件運(yùn)算符,常用的條件運(yùn)算符,通配運(yùn)算符,常與LIKE關(guān)鍵字配合使用，表示一個(gè)模糊的范圍 。,經(jīng)常用于檢查約束及數(shù)據(jù)查詢過濾條件,邏輯運(yùn)算符,邏輯運(yùn)算符,回顧,請(qǐng)寫出Email的檢查約束表達(dá)式 ？ 什么是數(shù)據(jù)冗余？其解決方法是什么？ 請(qǐng)說出引用完整性的約束規(guī)則？,第四部分?jǐn)?shù)據(jù)的管理,1. 插入數(shù)據(jù) 2. 更新數(shù)據(jù) 3. 刪除數(shù)據(jù) 4. 查詢數(shù)據(jù),總結(jié),本章任務(wù),使用T-SQL語(yǔ)句對(duì)Authors和Books表數(shù)據(jù)進(jìn)行增、刪、改、查操作 。,本章目標(biāo),理解T-SQL的及其作用。 掌握T-SQL運(yùn)算符和表達(dá)式的使用。 掌

28、握使用T-SQL語(yǔ)句實(shí)現(xiàn)增、刪、改、查操作。,為什么要學(xué)習(xí)SQL語(yǔ)言,購(gòu)買火車票時(shí)，當(dāng)你告知售票員出發(fā)地和目的地后，售票系統(tǒng)立刻能查詢 出有哪些車次，及每個(gè)車次的剩余票數(shù)，而這些信息是存儲(chǔ)在數(shù)據(jù)庫(kù)中的。 請(qǐng)問售票軟件是怎樣從數(shù)據(jù)庫(kù)中取出這些數(shù)據(jù)的？,數(shù) 據(jù) 庫(kù) 服 務(wù) 器,發(fā)送的就是SQL指令,T-SQL語(yǔ)言,SQL：專門對(duì)數(shù)據(jù)庫(kù)操作的指令集 SQL語(yǔ)言的分類 DML：Data Manipulation Language數(shù)據(jù)操縱語(yǔ)言 insert、update、delete DQL：Data Query Language數(shù)據(jù)查詢語(yǔ)言，select DDL：Data Definition Lan

29、guage數(shù)據(jù)定義語(yǔ)言 create databse、create table、alter table、drop table等 DCL：Data Control Language數(shù)據(jù)控制語(yǔ)言 revoke、grant T-SQL語(yǔ)言 標(biāo)準(zhǔn)SQL語(yǔ)言 CCL：通用命令語(yǔ)言,T-SQL表達(dá)式,變量：一般是列名 運(yùn)算符：算術(shù)運(yùn)算符、邏輯運(yùn)算符、關(guān)系運(yùn)算符、通配符 運(yùn)算符的優(yōu)先級(jí) Not 算術(shù)運(yùn)算符 條件運(yùn)算符 And Or,使用Insert插入數(shù)據(jù)-1,INSERT INTO (列名) VALUES ,語(yǔ)法,其中：“”代表可選的；“”代表必需的。 如果有多個(gè)列名和多個(gè)列值需要用逗號(hào)隔開。,使用Ins

30、ert插入數(shù)據(jù)-2,注意1: 值列表的個(gè)數(shù)必須與列名數(shù)目保持一致。,執(zhí)行錯(cuò)誤,使用Insert插入數(shù)據(jù)-3,注意2: 值列表中值的數(shù)據(jù)類型、精度要與對(duì)應(yīng)的列類型保持一致。,執(zhí)行錯(cuò)誤,使用Insert插入數(shù)據(jù)-4,注意3: 可以不指定列名，但值列表中值的順序應(yīng)該與表中字段順序保持一致 。,使用Insert插入數(shù)據(jù)-5,注意4: 不應(yīng)為標(biāo)識(shí)列字段指定值，因?yàn)樗窍到y(tǒng)控制自動(dòng)增長(zhǎng)的 。,執(zhí)行錯(cuò)誤,使用Insert插入數(shù)據(jù)-6,注意5: 值列表中的數(shù)據(jù)項(xiàng)，應(yīng)符合對(duì)應(yīng)列的檢查約束要求 。,執(zhí)行錯(cuò)誤,使用Insert插入數(shù)據(jù)-7,方法1:可以使用DEFAULT（缺?。╆P(guān)鍵字來代替插入的數(shù)值。,如何使用In

31、sert語(yǔ)句向具有缺省值的列插入數(shù)據(jù)？,方法2:Insert語(yǔ)句中不指定具有默認(rèn)值的列名和列值 。,小結(jié),假設(shè)你自己是一位有名的作者，把你的信息插入到Authors表中？,編碼,使用Insert Select語(yǔ)句插多行數(shù)據(jù),INSERT INTO 列名 SELECT FROM ,語(yǔ)法,注意： 1、必須事先存在。如果不存在，執(zhí)行時(shí)將會(huì)出現(xiàn)錯(cuò)誤。 2、查詢出的字段數(shù)目、數(shù)據(jù)類型、字段順序，與插入列保持一致。,要將Authors表中所有作者信息保存到AuthorsInfo表中。,示例,使用Select Into語(yǔ)句插入多行數(shù)據(jù),SELECT INTO FROM ,語(yǔ)法,注意： 不能事先存在，它是在執(zhí)

32、行該語(yǔ)句時(shí)系統(tǒng)自動(dòng)創(chuàng)建的。,要將Authors表中“作者姓名、年齡、電子郵件、所在城市”數(shù)據(jù)存儲(chǔ)到一個(gè)新表中,示例,該語(yǔ)句可以執(zhí)行兩次嗎？,使用Select Into語(yǔ)句插入標(biāo)識(shí)列數(shù)據(jù),SELECT IDENTITY(數(shù)據(jù)類型, 標(biāo)識(shí)種子, 標(biāo)識(shí)增量) as 列名 INTO FROM ,語(yǔ)法,要將Authors表中“作者姓名、年齡、電子郵件”數(shù)據(jù)存儲(chǔ)到一個(gè)新表中，并且 要求插入新表時(shí)自動(dòng)生成標(biāo)識(shí)列字段。,示例,使用Union關(guān)鍵字插入多行數(shù)據(jù),INSERT INTO 列名 SELECT UNION SELECT UNION SELECT ,語(yǔ)法,向Authors表中一次插入多位作者信息 。,示

33、例,注意： 列名：可以省略，如果省略列名，那么的數(shù)據(jù)個(gè)數(shù)、順序、數(shù)據(jù)類型必須與表中字段順序、數(shù)據(jù)類型保持一致。,使用Union注意的問題,Union語(yǔ)句中不能使用DEFAULT關(guān)鍵字,語(yǔ)法錯(cuò)誤,小結(jié),編寫代碼將Books表中數(shù)據(jù)保通過一條SQL語(yǔ)句存到newBooks中，newBooks表與Books表結(jié)構(gòu)類似，要求事先存在。,編碼,使用T-SQL中Update語(yǔ)句修改數(shù)據(jù),UPDATE SET WHERE ,語(yǔ)法,注意： 1、為必選項(xiàng)，用于更新表中某列數(shù)據(jù)，在SET后面可以出現(xiàn)多 個(gè)，需用逗號(hào)隔開。 2、Where關(guān)鍵字是可選的，用來限定條件，如果Update語(yǔ)句不限定條件，表中所有數(shù)據(jù)行都

34、將被更新。,Update語(yǔ)句的使用,示例,將作者表中所有作者的居住城市都更改成“北京”,將作者表中AuthorID為2的作者年齡更改成36歲,更新語(yǔ)句中還可以使用表達(dá)式,使用Update語(yǔ)句注意的問題1,可以更新主鍵列數(shù)據(jù)，但應(yīng)保證更新后的主鍵列數(shù)據(jù)不能出現(xiàn)重復(fù) 信息，否則將更新失?。?圖書表中已經(jīng)存在圖書編號(hào)為”169876”的圖書，違反了主鍵約束,使用Update語(yǔ)句注意的問題2,可以更新表中外鍵列數(shù)據(jù)，但應(yīng)保證新數(shù)據(jù)在主鍵表的中應(yīng)事先存在,違反了外鍵約束 ，因?yàn)镃ategories表中CatagoryID列不存在類別為12的編號(hào)信息。,使用T-SQL中Delete語(yǔ)句刪除數(shù)據(jù),DELET

35、E FROM WHERE ,語(yǔ)法,示例,Delete語(yǔ)句注意問題,李小旭,100,168901,4,高好,1,168901,3,紀(jì)曉嵐,50,178922,2,李云龍,5,165212,1,和珅,10,169876,1,訂購(gòu)人,訂購(gòu)數(shù)量,圖書編號(hào),訂單編號(hào),執(zhí)行錯(cuò)誤！因?yàn)閳D書表中169876被引用,使用Truncate Table語(yǔ)句刪除數(shù)據(jù),示例,等同于,Truncate Table ,語(yǔ)法,注意： 1、Truncate Table只刪除表中的數(shù)據(jù)行，不會(huì)刪除表結(jié)構(gòu)及各種約束。2、Truncate Table不能刪除具有引用關(guān)系的數(shù)據(jù)表。,執(zhí)行效率更高,Truncate Table和Dele

36、te的區(qū)別,Delete刪除的數(shù)據(jù)可以通過日志文件進(jìn)行恢復(fù) Truncate Table刪除的數(shù)據(jù)不能進(jìn)行恢復(fù) Delete刪除時(shí)，標(biāo)識(shí)列取值保留原使用中最大值 Truncate Table刪除時(shí)，標(biāo)識(shí)列恢復(fù)到最初設(shè)置的標(biāo)識(shí)種子值 Delete刪除數(shù)據(jù)時(shí)是一條記錄一條記錄刪除的 Truncate Table刪除數(shù)據(jù)時(shí)，是以數(shù)據(jù)頁(yè)的形式刪除的,本次課總結(jié),Insert語(yǔ)句使用注意要點(diǎn) 一次插入多行數(shù)據(jù)，有哪幾種SQL語(yǔ)句。 更新語(yǔ)句的使用，以及注意要點(diǎn)？ Delete語(yǔ)句作用？以及注意要點(diǎn)？ Truncate Table 語(yǔ)句作用？以及注意要點(diǎn)？,查詢和記錄集的概念,數(shù) 據(jù) 庫(kù) 服 務(wù) 器,SE

37、LECT * FROM TICKET,售票員查詢從北京到石家莊的車次。,查詢將產(chǎn)生一個(gè)虛擬表，這些數(shù)據(jù)是從數(shù)據(jù)庫(kù)中現(xiàn)有表中過濾出來的，類似表的一個(gè)結(jié)構(gòu)體，稱為“記錄集”,數(shù)據(jù)庫(kù)中是如何查詢的？,下面就來學(xué)習(xí)SQL中的查詢語(yǔ)句,SELECT查詢語(yǔ)句,語(yǔ)法,SELECT FROM WHERE ORDER BY ASC或DESC,示例,演示效果.,基礎(chǔ)查詢5-1,1、查詢數(shù)據(jù)表中部分列,2、使用“*”查找所有列,3、使用WHERE過濾部分行數(shù)據(jù),基礎(chǔ)查詢5-2,4、使用As子句將列轉(zhuǎn)別名,轉(zhuǎn)別名還可以使用“=”,注意： 1、應(yīng)使用英文半角 2、如果是中間不包含空格可以不寫引號(hào),5、使用“+”連接多個(gè)

38、字段，合并成一列,注意： 1、+前后類型應(yīng)兼容 2、如果+連接數(shù)值類型，結(jié)果是數(shù)值之和。 3、如果+連接字符串類型，結(jié)果是字符串的連接。,基礎(chǔ)查詢5-3,6、使用Top關(guān)鍵字查詢表中限定行數(shù),使用PERCENT按百分比取數(shù)據(jù),7、在查詢結(jié)果中使用常量列,基礎(chǔ)查詢5-4,9、使用IS NULL查詢空數(shù)據(jù),如果填寫了生日信息，然后又刪了，如何查詢出來?,8、使用DISTINCT關(guān)鍵字屏蔽重復(fù)數(shù)據(jù),基礎(chǔ)查詢5-5,10、UNION聯(lián)合查詢,排序,1、查詢所有圖書信息并按照價(jià)格字段升序顯示,2、查詢單價(jià)在50元以上的圖書信息，要求價(jià)格以8折顯示，并按價(jià)格降序排列,排序-按多列排序,3、查詢結(jié)果還可以按

39、多列進(jìn)行排序,先按 “圖書類別”進(jìn)行主排序，然后在“圖書類別”排序的基礎(chǔ)上按“單價(jià)”列進(jìn)行次排序,小結(jié),查詢電話號(hào)碼會(huì)員信息，要求顯示會(huì)員名稱、居住城市、會(huì)員等級(jí)。 編寫代碼查詢積分在500分以上的會(huì)員信息，并按積分降序排列。,提問,模糊查詢,查詢時(shí)，字段中的內(nèi)容并不一定與查詢內(nèi)容完全匹配， 只要字段中含有這些內(nèi)容。,LIKE進(jìn)行模糊查詢,查詢作者表中作者姓名以“張”開頭的作者信息,語(yǔ)法,WHERE NOT LIKE ,通常LIKE與通配符配合使用,示例,IN進(jìn)行模糊查詢,查詢居住地是“北京”、“上?！?、“西安“、“成都”的作者,語(yǔ)法,WHERE IN ,其中：中各常

40、量值用逗號(hào)隔開,示例,考慮用以前學(xué)的邏輯運(yùn)算符能夠查詢出來？,BETWEEN進(jìn)行模糊查詢,查找單價(jià)在20到99元的圖書信息,語(yǔ)法,WHERE NOT BETWEEN And ,注意：和的順序不能顛倒,示例,Between語(yǔ)句還經(jīng)常用于查找指定日期范圍內(nèi)的數(shù)據(jù),小結(jié),查詢電話號(hào)碼以139開頭的會(huì)員信息，并按照會(huì)員積分升序排列。,提問,常用的SQL內(nèi)置函數(shù),函數(shù)就相當(dāng)與Java中的方法，每個(gè)函數(shù)實(shí)現(xiàn)了一個(gè)特定的功能。,字符串函數(shù),日期函數(shù),數(shù)學(xué)函數(shù),類型轉(zhuǎn)換函數(shù),字符串函數(shù),數(shù)學(xué)函數(shù),日期函數(shù),轉(zhuǎn)換函數(shù),小結(jié)練習(xí),在BooksManager數(shù)據(jù)庫(kù)中，有會(huì)員表(Customers),現(xiàn)在要查詢注冊(cè)時(shí)

41、間大于5年并且本月過生日的會(huì)員，要求顯示會(huì)員姓名、居住城市、出生日期、聯(lián)系電話和會(huì)員年齡。,分析,在要解決這個(gè)問題需要使用查詢語(yǔ)句SELECT。 要求注冊(cè)時(shí)間大于5年，WHERE條件中需要用到日期函數(shù)YEAR。 并且本月過生日的會(huì)員，需要用到日期函數(shù)中的MONTH函數(shù) 。 查詢結(jié)果中顯示會(huì)員年齡，當(dāng)前年份-出生日期。,YEAR(GETDATE() YEAR(RegDate) 5,MONTH(GETDATE() = MONTH(Birthday),YEAR(GETDATE() YEAR(Birthday),案例分析1要求,某公司印了一批充值卡，卡的密碼是隨機(jī)生成的，現(xiàn)在出現(xiàn)這個(gè)問題： 卡里面的“

42、O和0”（哦和零）“i和1”（哎和一），用戶反映說看不清楚，公司決定，把存儲(chǔ)在數(shù)據(jù)庫(kù)中的密碼中所有的“哦”都改成“零”，把所有的“i”都改成“1”； 請(qǐng)編寫SQL語(yǔ)句實(shí)現(xiàn)以上要求； 數(shù)據(jù)庫(kù)表名：Card 密碼字段名：PassWord,案例分析1分析,這是更新語(yǔ)句，需要使用UPDATE語(yǔ)句 牽涉到字符串的替換，需要使用到SQL Server中的函數(shù)Replace,案例分析1T-SQL,兩行SQL語(yǔ)句,UPDATE Card SET PassWord = Replace(PassWord ,O,0) UPDATE Card SET PassWord = Replace(PassWord ,i,1)

43、,一行SQL語(yǔ)句,UPDATE Card SET PassWord = Replace(Replace(PassWord ,O,0),i,1),案例分析2要求,在數(shù)據(jù)庫(kù)表中有以下字符數(shù)據(jù)，如： 13-1、13-2、13-3、13-10、13-100、13-108、13-18、13-11、13-15、14-1、14-2 現(xiàn)在希望通過SQL語(yǔ)句進(jìn)行排序，并且首先要按照前半部分的數(shù)字進(jìn)行排序，然后再按照后半部分的數(shù)字進(jìn)行排需，輸出要排成這樣： 13-1、13-2、13-3、13-10、13-11、13-15、13-18、13-100、13-108、14-1、14-2 數(shù)據(jù)庫(kù)表名：SellRecord

44、 字段名：ListNumber,案例分析2分析,查詢語(yǔ)句：使用SELECT語(yǔ)句 排序：ORDER BY 在ORDER BY的排序列中，需要重新計(jì)算出排序的數(shù)字 前半部分的數(shù)字： 找到“-”符號(hào)的位置 取其左半部分 使用Convert函數(shù)將其轉(zhuǎn)換為數(shù)字： Convert(int, Left(ListNumber, CharIndex(-, ListNumber)-1) 后半部分的數(shù)字： 找到“-”符號(hào)的位置 把從第一個(gè)位置到該位置的全部字符替換為空格 使用Convert函數(shù)將其轉(zhuǎn)換為數(shù)字： Convert(int, Stuff(ListNumber,1, Charindex(-, ListNum

45、ber), ),案例分析2T-SQL,SELECT ListNumber FROM SellRecord ORDER BY Convert(int, Left(ListNumber, CharIndex(-, ListNumber)-1), Convert(int, Stuff(ListNumber,1, Charindex(-, ListNumber), ),本次總結(jié),什么是記錄集？ SELECT語(yǔ)句中的as作用是什么 ？ 請(qǐng)說出2個(gè)日期函數(shù)及2個(gè)字符串函數(shù)的作用？,回顧,編寫SQL語(yǔ)句查詢一個(gè)月前的訂單信息。 查詢作者表中姓名以“李”開頭的作者信息。 查詢前20%的圖書信息。,本節(jié)目標(biāo),掌

46、握聚合函數(shù)的使用。 掌握Group By進(jìn)行分組統(tǒng)計(jì)。,聚合函數(shù),會(huì)員表中存儲(chǔ)了所有會(huì)員的積分，如果想計(jì)算出 會(huì)員的最高分、平均積分、積分大于300的會(huì)員個(gè)數(shù)， 怎么辦？,會(huì)員表Customers,聚合函數(shù)-SUM、AVG、MAX、MIN,1、SUM,2、AVG,3、MAX和MIN,聚合函數(shù)-COUNT,4、COUNT(表達(dá)式) 返回結(jié)果集的非空行數(shù)。,MAX和MIN除了計(jì)算數(shù)值列，還可以用于計(jì)算字符型以及日期時(shí)間類型數(shù)據(jù)列,其中“表達(dá)式”可以是“*”，“列名”。 （1）COUNT(*)：返回表中所有數(shù)據(jù)行的記錄數(shù)。 （2）COUNT(列名)：返回指定列非空值個(gè)數(shù)。,示例,聚合函數(shù),如果想統(tǒng)計(jì)

47、居住在不同城市的人員個(gè)數(shù)，編寫下面代碼正確嗎？,執(zhí)行出現(xiàn)錯(cuò)誤,解決這樣的問題需要使用GROUP BY分組統(tǒng)計(jì),什么是分組統(tǒng)計(jì),一家水果店的一天銷售情況，要統(tǒng)計(jì)各類水果的銷售額 。,示例,按水果的種類分組,然后分別匯總,分組統(tǒng)計(jì),分組統(tǒng)計(jì)牢記： SELECT語(yǔ)句中除聚合函數(shù)外，所有列應(yīng)寫在Group By語(yǔ)句后面, 否則將出現(xiàn)錯(cuò)誤。,語(yǔ)法,SELECT 聚合函數(shù) FROM 表 WHERE 條件 GROUP BY ,其中可以有多個(gè)字段，各字段用逗號(hào)隔開，它表示按哪些列進(jìn)行分組。,分組統(tǒng)計(jì)應(yīng)用,統(tǒng)計(jì)會(huì)員表中居住在不同城市的人員個(gè)數(shù)？,提問,分析,應(yīng)該按照City列進(jìn)行分組 分組后使用Count()聚

48、合函數(shù)匯總行數(shù)。,統(tǒng)計(jì)結(jié)果,多列分組統(tǒng)計(jì),統(tǒng)計(jì)不同城市不同性別的會(huì)員個(gè)數(shù) ？,提問,分析,統(tǒng)計(jì)結(jié)果集應(yīng)包含“城市、性別和人數(shù)3列信息”。 很明顯應(yīng)該按照City和Sex兩列進(jìn)行分組。 分組后仍使用Count()聚合函數(shù)匯總行數(shù)。,統(tǒng)計(jì)結(jié)果,多列分組統(tǒng)計(jì),如果代碼寫成這樣，結(jié)果會(huì)怎樣？,所以牢記： SELECT語(yǔ)句中除聚合函數(shù)外，所有列應(yīng)寫在Group By語(yǔ)句后面。,執(zhí)行錯(cuò)誤,增加Sex才正確.,Sex,使用HAVING子句對(duì)分組結(jié)果再次過濾,繼續(xù)考慮上面的查詢，如果“要統(tǒng)計(jì)不同城市不同性別的會(huì)員個(gè)數(shù)，并且只顯示會(huì)員個(gè)數(shù)大于2個(gè)的記錄”如何處理呢？,需要對(duì)分組后的數(shù)據(jù)進(jìn)行篩選， 篩選條件Cou

49、nt(*) =2。 對(duì)分組后的數(shù)據(jù)進(jìn)行篩選需要使用Having子句。,統(tǒng)計(jì)結(jié)果：,分組查詢對(duì)比,WHERE子句： 從數(shù)據(jù)源中去掉不符合其搜索條件的數(shù)據(jù) GROUP BY子句： 搜集數(shù)據(jù)行到各個(gè)組中，統(tǒng)計(jì)函數(shù)為各個(gè)組計(jì)算統(tǒng)計(jì)值 HAVING子句： 去掉不符合其組搜索條件的各組數(shù)據(jù)行,問題,成績(jī)表中存儲(chǔ)了所有學(xué)員的成績(jī)，想知道： 學(xué)員的總成績(jī)、平均成績(jī)? 怎么辦？,問題,如果不是統(tǒng)計(jì)所有人所有課程的總成績(jī) 而是想求每一門課的平均績(jī)或者某個(gè)人的所有課的總成績(jī)?cè)趺崔k？,分組匯總,第一門課6個(gè)成績(jī)?nèi)∑骄?第二門課6個(gè)成績(jī)?nèi)∑骄?第三門課5個(gè)成績(jī)?nèi)∑骄?分組查詢GROUP BY,SELECT Cou

50、rseID, AVG(Score) AS 課程平均成績(jī) FROM Score GROUP BY CourseID,分組查詢思考,SELECT StudentID, CourseID, AVG(Score) AS 課程平均成績(jī) FROM Score GROUP BY CourseID,思考：執(zhí)行以下的T-SQL：, 結(jié)果如何？,分組查詢多列分組,怎么樣來統(tǒng)計(jì)每次的內(nèi)部測(cè)試不同學(xué)員的成績(jī)？,分組查詢?cè)倏纯?StudentID和CourseID的組合存在重復(fù),需要按照這兩個(gè)來進(jìn)行分組，避免StudentID和CourseID 同時(shí)一樣,分組查詢多列分組,SELECT StudentID AS 學(xué)員編

51、號(hào),CourseID AS 課程編號(hào), AVG(Score) AS 內(nèi)部測(cè)試平均成績(jī) FROM Score GROUP BY StudentID,CourseID,分組查詢問題,在以上統(tǒng)計(jì)內(nèi)部測(cè)試成績(jī)的基礎(chǔ)上， 如果只想看補(bǔ)考的學(xué)員的成績(jī)，怎么辦,分組查詢?cè)倏纯?增加條件：要求該學(xué)員的CourseID在分組內(nèi)出現(xiàn)過一次以上,SELECT StudentID AS 學(xué)員編號(hào),CourseID AS 內(nèi)部測(cè)試, AVG(Score) AS 內(nèi)部測(cè)試平均成績(jī) FROM Score GROUP BY StudentID,CourseID,分組查詢HAVING,SELECT StudentID AS 學(xué)

52、員編號(hào),CourseID AS 內(nèi)部測(cè)試, AVG(Score) AS 內(nèi)部測(cè)試平均成績(jī) FROM Score GROUP BY StudentID,CourseID HAVING COUNT(Score) 1,分組查詢思考,SELECT 部門編號(hào), COUNT(*) FROM 員工信息表 WHERE 工資 = 2000 GROUP BY 部門編號(hào) HAVING COUNT(*) 1,思考：分析以下T-SQL的含義,多表關(guān)聯(lián)查詢,為什么要用多表關(guān)聯(lián)查詢？,這是從圖書表中查詢出的信息。如果時(shí)間長(zhǎng)了你還能記得作者編號(hào)1、4、11、12都代表是哪個(gè)作者嗎？,實(shí)際上最好顯示作者姓名，而作者姓名是存儲(chǔ)在

53、作者表中，如何同時(shí)從這兩個(gè)表中取得數(shù)據(jù)呢？,多表關(guān)聯(lián)查詢,內(nèi)聯(lián)接(INNER JOIN) 外聯(lián)接 左外聯(lián)接 (LEFT JOIN) 右外聯(lián)接 (RIGHT JOIN) 完整外聯(lián)接(FULL JOIN) 交叉聯(lián)接(CROSS JOIN),Authors,內(nèi)連接,Books,內(nèi)連接查詢出的是兩個(gè)表公共列共有的記錄。,內(nèi)連接,語(yǔ)法,SELECT FROM 表1 INNER JOIN 表2 ON 表1.列名 條件運(yùn)算符 表2.列名 WHERE 條件 ORDER BY 排序列,其中“ON 表1.列名 條件運(yùn)算符 表2.列名” 中，條件運(yùn)算符常用的是：=、。 表1.列名和表2.列名,分別是兩個(gè)表的公共列。

54、,示例,Books與Authors內(nèi)連接SQL語(yǔ)句,內(nèi)連接另外一種寫法,SQL Server2005還兼容以前版本的內(nèi)連接語(yǔ)法，即使用WHERE子句進(jìn)行內(nèi)連接。,多表內(nèi)連接,語(yǔ)法,SELECT FROM 表1 INNER JOIN 表2 ON 表1.列名 條件運(yùn)算符 表2.列名 INNER JOIN 表3 ON 表1.列名 條件運(yùn)算符 表3.列名 WHERE 條件 ORDER BY 排序列,示例,左外連接,語(yǔ)法,SELECT FROM 左表 LEFT OUTER JOIN 右表 ON 左表.列名 條件運(yùn)算符 右表.列名 WHERE 條件 ORDER BY 排序列,示例,使用左外連接查詢Book

55、s與Authors表中的數(shù)據(jù),右表 Authors(從),左外連接,左表：Books(主),左外連接是以左表為主表，去關(guān)聯(lián)右表(從表)，結(jié)果集中包含主表所有數(shù)據(jù)行，如果主表的某行在從表中沒有匹配行時(shí)，則從表的選擇列為NULL值。,左外連接,如果把上面的語(yǔ)句改如下代碼，查詢結(jié)果一樣嗎？為什么？,右外連接,語(yǔ)法,SELECT FROM 左表 RIGHT OUTER JOIN 右表 ON 左表.列名 條件運(yùn)算符 右表.列名 WHERE 條件 ORDER BY 排序列,示例,使用右外連接查詢Books與Authors表中的數(shù)據(jù),右表 Authors(主),右外連接,左表：Books(從),右外連接是以

56、右表為主表，去關(guān)聯(lián)左表(從表)，結(jié)果集中包含主表所有數(shù)據(jù)行，如果主表的某行在從表中沒有匹配行時(shí)，則從表的選擇列為NULL值。,完全連接,語(yǔ)法,SELECT FROM 左表 FULL OUTER JOIN 右表 ON 左表.列名 條件運(yùn)算符 右表.列名 WHERE 條件 ORDER BY 排序列,示例,使用完全連接查詢Books與Authors表中的數(shù)據(jù),完全連接左表和右表中所有行，當(dāng)某行數(shù)據(jù)在另一個(gè)表中沒有匹配時(shí)，則另一個(gè)表的選擇列值為NULL,案例分析,在查找作者表中哪些作者還沒有出過書，并將這些作者信息保存到新表TempAuthors中。,分析,先查找內(nèi)容，然后插入到新表中，可以使用SEL

57、ECT.INTO.語(yǔ)句。 關(guān)鍵是能查詢出哪些作者還沒有出過書，這涉及到2個(gè)表，一個(gè)是作者表，另一個(gè)是圖書表 。 查詢出作者表中作者編號(hào)在圖書表中沒有出現(xiàn)過的，以作者表為主表、圖書表為從表，使用左外連接查詢。,最后將查詢出的信息，插入到TempAuthors表中 。,為什么使用子查詢,用所學(xué)知識(shí)點(diǎn)，找出比喜來樂積分高的客戶,數(shù)據(jù)表：Customers,問題,實(shí)現(xiàn)方法,什么是子查詢,SELECT FROM 表1 WHERE 字段1 比較運(yùn)算符 （ 子查詢 ） 或者: UPDATE 表1 SET WHERE 字段1 比較運(yùn)算符 （ 子查詢 ）,語(yǔ)法,select,(,),簡(jiǎn)單子查詢,查詢“喜來樂”的

58、所有訂單信息 Select * from orders Where customerid=( select customerid from customers where customername=喜來樂 ),簡(jiǎn)單子查詢,假如某個(gè)客戶“喜來樂”信譽(yù)不好，要?jiǎng)h除該客戶信息，因表關(guān)聯(lián)，需要先刪除該客戶的訂單信息，此時(shí)怎樣操作？,當(dāng)子查詢的結(jié)果多于一個(gè)時(shí)，就不能使用比較運(yùn)算符了，要使用IN關(guān)鍵字。,簡(jiǎn)單子查詢,查詢沒有訂單的客戶信息 檢查每個(gè)客戶今年的訂單總額如果超過100元，則積分增加100,子查詢,從customers表中檢索數(shù)據(jù)，以查詢一次訂貨金額大于等于100的客戶編號(hào)和姓名 SELECT

59、a.customerid,a.customername FROM customers a WHERE 100=ANY (SELECT total FROM orders WHERE a.customerid=orders.customerid),子查詢,檢索所有圖書中價(jià)格最高的圖書信息 SELECT * FROM books WHERE unitprice=ALL (SELECT unitprice FROM books),218,子查詢,如果一個(gè)SELECT語(yǔ)句能夠返回一個(gè)單值或一列值并嵌套在一個(gè)SELECT、INSERT、UPDATE或DELETE語(yǔ)句中，則稱之為子查詢或內(nèi)層查詢，而包含一個(gè)子查詢的語(yǔ)句則