配套課件-SQL-Server-2005數(shù)據(jù)庫任務(wù)化教程

SQLServer2005數(shù)據(jù)庫任務(wù)化教程

任務(wù)一分銷系統(tǒng)數(shù)據(jù)庫的設(shè)計(jì)與生成

一、任務(wù)目標(biāo)

1掌握數(shù)據(jù)庫、數(shù)據(jù)庫管理系統(tǒng)和數(shù)據(jù)庫系統(tǒng)的基本概念；2了解數(shù)據(jù)模型的概念、掌握管細(xì)數(shù)據(jù)結(jié)構(gòu)，掌握關(guān)系數(shù)據(jù)庫的定義；3了解并基本掌握E-R實(shí)體聯(lián)系圖。4掌握SQLServer數(shù)據(jù)庫的基本組成和有關(guān)知識(shí)；5初步了解Transact-SQL語句的基礎(chǔ)知識(shí)；6掌握數(shù)據(jù)庫的創(chuàng)建。二、教學(xué)任務(wù)1介紹數(shù)據(jù)庫的基本概念；2介紹數(shù)據(jù)庫系統(tǒng)模型；3介紹關(guān)系模型的完整性約束；4介紹關(guān)系型數(shù)據(jù)庫范式理論；5設(shè)計(jì)分銷系統(tǒng)數(shù)據(jù)庫；6介紹SQLServer數(shù)據(jù)庫的常用對象、數(shù)據(jù)庫結(jié)構(gòu)和系統(tǒng)數(shù)據(jù)庫等基本知識(shí)；7SQLServerManagementStudio的初步使用；8用圖形化工具創(chuàng)建數(shù)據(jù)庫；9使用Transact-SQL創(chuàng)建數(shù)據(jù)庫。1.1分銷系統(tǒng)數(shù)據(jù)庫的規(guī)劃設(shè)計(jì)分銷系統(tǒng)是對營銷業(yè)務(wù)中的客戶資料、銷售訂單、供應(yīng)商資料、采購訂單、付款單、收款單以及倉庫物資等進(jìn)行管理，設(shè)計(jì)良好的數(shù)據(jù)庫能給系統(tǒng)的高效運(yùn)作提供保障。1.1.1數(shù)據(jù)庫的基本概念數(shù)據(jù)庫系統(tǒng)由數(shù)據(jù)庫和數(shù)據(jù)庫管理系統(tǒng)（DatabaseManagementSystem，即DBMS）兩部分組成。1.1.1數(shù)據(jù)庫的基本概念一些相關(guān)概念

（1）數(shù)據(jù)（2）數(shù)據(jù)處理（3）數(shù)據(jù)管理（4）數(shù)據(jù)庫（5）數(shù)據(jù)庫技術(shù)（6）數(shù)據(jù)庫技術(shù)特點(diǎn)1.1.1數(shù)據(jù)庫的基本概念2.數(shù)據(jù)庫管理系統(tǒng)（DBMS）的基本功能(1)數(shù)據(jù)庫定義(2)數(shù)據(jù)庫處理(3)數(shù)據(jù)庫控制(4)數(shù)據(jù)字典1.1.2數(shù)據(jù)庫系統(tǒng)模型數(shù)據(jù)庫系統(tǒng)模型是指數(shù)據(jù)庫中數(shù)據(jù)的存儲(chǔ)結(jié)構(gòu)。它是反映客觀事物及其聯(lián)系的數(shù)據(jù)描述形式。數(shù)據(jù)模型通常由數(shù)據(jù)結(jié)構(gòu)、數(shù)據(jù)操作和完整性約束等三部分組成，分別描述數(shù)據(jù)庫系統(tǒng)的靜態(tài)特性、動(dòng)態(tài)特性和完整性約束條件。數(shù)據(jù)庫系統(tǒng)的發(fā)展史上，最有影響的數(shù)據(jù)庫模型有三個(gè)：層次模型、網(wǎng)狀模型、關(guān)系模型。1.1.2數(shù)據(jù)庫系統(tǒng)模型1.層次型數(shù)據(jù)庫這種模型描述數(shù)據(jù)的組織形式像一棵倒置的樹，它由節(jié)點(diǎn)和連線組成，其中節(jié)點(diǎn)表示實(shí)體。樹有根、枝、葉，在這里都稱為節(jié)點(diǎn)，根節(jié)點(diǎn)只有一個(gè)，向下分支，它是一種一對多的關(guān)系。如國家的行政機(jī)構(gòu)、一個(gè)家族的譜的組織形式都可以看做是層次模型，如圖1-5所示。1.1.2數(shù)據(jù)庫系統(tǒng)模型2.網(wǎng)絡(luò)型數(shù)據(jù)庫這種模型描述事物及其聯(lián)系的數(shù)據(jù)組織形式像一張網(wǎng)，節(jié)點(diǎn)表示數(shù)據(jù)元素，節(jié)點(diǎn)間聯(lián)線表示數(shù)據(jù)間聯(lián)系。節(jié)點(diǎn)之間是平等的，無上下層關(guān)系。如學(xué)校中的“教師”、“學(xué)生”、“課程”、“教室”等事物之間有聯(lián)系但無層次關(guān)系，可認(rèn)為是一種網(wǎng)狀結(jié)構(gòu)模型。此種類型數(shù)據(jù)庫的優(yōu)點(diǎn)是它能很容易地反映實(shí)體之間的關(guān)聯(lián)，同時(shí)它還避免了數(shù)據(jù)的重復(fù)性；缺點(diǎn)是這種類型關(guān)聯(lián)錯(cuò)綜復(fù)雜，而且當(dāng)數(shù)據(jù)庫將很難對結(jié)構(gòu)中所謂關(guān)聯(lián)性進(jìn)行維護(hù)。1.1.2數(shù)據(jù)庫系統(tǒng)模型3.關(guān)系型數(shù)據(jù)庫關(guān)系型數(shù)據(jù)庫使用的存儲(chǔ)結(jié)構(gòu)是多個(gè)二維表格，即反映事物及其聯(lián)系的數(shù)據(jù)描述是以平面表格形式體現(xiàn)的。1.1.3數(shù)據(jù)完整性數(shù)據(jù)完整性分為四類：實(shí)體完整性（EntityIntegrity）、域完整性（DomainIntegrity）、參照完整性（ReferentialIntegrity）、用戶定義的完整性（User-definedIntegrity）。1.1.3數(shù)據(jù)完整性1.實(shí)體完整性（EntityIntegrity）實(shí)體完整性規(guī)定表的每一行在表中是唯一的實(shí)體。表中定義的UNIQUE、PRIMARYKEY和IDENTITY約束就是實(shí)體完整性的體現(xiàn)。1.1.3數(shù)據(jù)完整性2.域完整性（DomainIntegrity）域完整性是指數(shù)據(jù)庫表中的列必須滿足某種特定的數(shù)據(jù)類型或約束。其中約束又包括取值范圍、精度等規(guī)定。表中的CHECK、FOREIGNKEY約束和DEFAULT、NOTNULL定義都屬于域完整性的范疇。1.1.3數(shù)據(jù)完整性3.參照完整性（ReferentialIntegrity）參照完整性是指兩個(gè)表的主關(guān)鍵字和外關(guān)鍵字的數(shù)據(jù)應(yīng)對應(yīng)一致。它確保了有主關(guān)鍵字的表中對應(yīng)其它表的外關(guān)鍵字的行存在，即保證了表之間的數(shù)據(jù)的一致性，防止了數(shù)據(jù)丟失或無意義的數(shù)據(jù)在數(shù)據(jù)庫中擴(kuò)散。參照完整性是建立在外關(guān)鍵字和主關(guān)鍵字之間或外關(guān)鍵字和唯一性關(guān)鍵字之間的關(guān)系上的。1.1.4關(guān)系型數(shù)據(jù)庫范式理論關(guān)系數(shù)據(jù)庫范式理論是在數(shù)據(jù)庫設(shè)計(jì)過程中將要依據(jù)的準(zhǔn)則，數(shù)據(jù)庫結(jié)構(gòu)必須要滿足這些準(zhǔn)則，才能確保數(shù)據(jù)的準(zhǔn)確性和可靠性。這些準(zhǔn)則則被稱為規(guī)范化形式，即范式。1.1.4關(guān)系型數(shù)據(jù)庫范式理論1．第一范式（1NF）第一范式要求每一個(gè)數(shù)據(jù)項(xiàng)都不能拆分成兩個(gè)或兩個(gè)以上的數(shù)據(jù)項(xiàng)。例如，如果關(guān)于員工的關(guān)系中有一個(gè)工資屬性，而工資又由更基本的兩個(gè)數(shù)據(jù)項(xiàng)基本工資和崗位工資組成，則這個(gè)員工的關(guān)系模式就不滿足1NF。滿足第一范式是關(guān)系模式規(guī)范化的最低要求，否則，將有許多基本操作在這樣的關(guān)系模式中實(shí)現(xiàn)不了，如上述的員工關(guān)系模式就實(shí)現(xiàn)不了按基本工資的10%給每位員工加薪。1.1.4關(guān)系型數(shù)據(jù)庫范式理論2．第二范式（2NF）數(shù)據(jù)庫表中不存在非關(guān)鍵字段對任一候選關(guān)鍵字段的部分函數(shù)依賴（部分函數(shù)依賴指的是存在組合關(guān)鍵字中的某些字段決定非關(guān)鍵字段的情況），也即所有非關(guān)鍵字段都完全依賴于任意一組候選關(guān)鍵字，那么該數(shù)據(jù)表滿足第二范式。1.1.4關(guān)系型數(shù)據(jù)庫范式理論3．第三范式（3NF）如果一個(gè)表已經(jīng)滿足第二范式，而且該數(shù)據(jù)表中的任何兩個(gè)非主鍵字段的數(shù)值之間不存在函數(shù)依賴關(guān)系，那么該數(shù)據(jù)表滿足第三范式。1.1.5分銷系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)數(shù)據(jù)庫系統(tǒng)設(shè)計(jì)包括數(shù)據(jù)模型設(shè)計(jì)以及圍繞數(shù)據(jù)模型的應(yīng)用程序開發(fā)兩大部分工作，這里主要介紹數(shù)據(jù)模型設(shè)計(jì)，也就是設(shè)計(jì)一組二維表，定義這些表的列名、列的數(shù)據(jù)類型以及表的數(shù)據(jù)完整性約束規(guī)則。數(shù)據(jù)庫設(shè)計(jì)過程一般包括這些內(nèi)容：需求分析、概念設(shè)計(jì)、邏輯設(shè)計(jì)、物理設(shè)計(jì)和實(shí)施維護(hù)。1.1.5分銷系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)1.分銷系統(tǒng)數(shù)據(jù)庫的需求分析用戶的需求具體體現(xiàn)了各種信息的提供、保存、更新和查詢，這就要求數(shù)據(jù)庫結(jié)構(gòu)能充分滿足各種信息的輸出和輸入。收集基本數(shù)據(jù)、數(shù)據(jù)結(jié)構(gòu)以及數(shù)據(jù)處理的流程，組成一份詳盡的數(shù)字字典，為后面的具體設(shè)計(jì)打下基礎(chǔ)。1.1.5分銷系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)2.分銷系統(tǒng)數(shù)據(jù)庫的概念模型設(shè)計(jì)圖1-3銷售訂單實(shí)體E-R圖根據(jù)上面的設(shè)計(jì)規(guī)劃出的實(shí)體有客戶實(shí)體、銷售訂單實(shí)體、銷售訂單明細(xì)實(shí)體、收款單實(shí)體、出庫單實(shí)體、出庫明細(xì)實(shí)體等。1.1.5分銷系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)3.分銷系統(tǒng)數(shù)據(jù)庫的邏輯設(shè)計(jì)現(xiàn)在需要把數(shù)據(jù)庫概念結(jié)構(gòu)轉(zhuǎn)化為SQLServer數(shù)據(jù)庫系統(tǒng)所支持的實(shí)際數(shù)據(jù)模型也就是數(shù)據(jù)庫的邏輯結(jié)構(gòu)。1.2分銷系統(tǒng)數(shù)據(jù)庫的創(chuàng)建數(shù)據(jù)庫主要是存儲(chǔ)了數(shù)據(jù)的表的集合以及其他的數(shù)據(jù)庫對象。SQLServer可以同時(shí)支持許多數(shù)據(jù)庫，每一個(gè)數(shù)據(jù)庫既可以存儲(chǔ)與另一個(gè)數(shù)據(jù)庫相關(guān)的數(shù)據(jù)，也可以存儲(chǔ)不相關(guān)的數(shù)據(jù)。1.2.1SQLServer數(shù)據(jù)庫基本知識(shí)SQLServer數(shù)據(jù)庫中的數(shù)據(jù)在邏輯上被組織成一系列對象，當(dāng)一個(gè)用戶連接到數(shù)據(jù)庫后，他所看到的是這些邏輯對象，而不是物理的數(shù)據(jù)庫文件1.2.1SQLServer數(shù)據(jù)庫基本知識(shí)1.數(shù)據(jù)庫對象SQLServer數(shù)據(jù)庫中的數(shù)據(jù)在邏輯上被組織成一系列對象，當(dāng)一個(gè)用戶連接到數(shù)據(jù)庫后，他所看到的是這些邏輯對象，而不是物理的數(shù)據(jù)庫文件。SQLServer中有以下數(shù)據(jù)庫對象：關(guān)系圖、表、視圖、存儲(chǔ)過程、規(guī)則、默認(rèn)、用戶自定義的數(shù)據(jù)類型、用戶自定義函數(shù)等。1.2.1SQLServer數(shù)據(jù)庫基本知識(shí)2.標(biāo)識(shí)符命名規(guī)則SQLServer中，常規(guī)標(biāo)識(shí)命名規(guī)則如下：（1）標(biāo)識(shí)符長度是1～128個(gè)字符。（2）第一個(gè)字符必須是大小寫字母，以及來自其他語言的字母字符，也可以是漢字、下劃線（_）、at符號(hào)（@）或數(shù)字符號(hào)（＃）（3）后續(xù)字符可以是字母、基本拉丁字母、十進(jìn)制數(shù)字、at符號(hào)（@）、美元符號(hào)（$）、數(shù)字符號(hào)（＃）或下劃線（_）。（3）標(biāo)識(shí)符不能是Transact-SQL語句的保留字。（4）不允許嵌入空格或其他特殊字符。1.2.1SQLServer數(shù)據(jù)庫基本知識(shí)3.數(shù)據(jù)庫結(jié)構(gòu)（1）數(shù)據(jù)庫文件（2）數(shù)據(jù)庫文件組（3）文件和文件組規(guī)則（4）數(shù)據(jù)庫空間管理1.2.1SQLServer數(shù)據(jù)庫基本知識(shí)4.事務(wù)日志每個(gè)數(shù)據(jù)庫都有一個(gè)相關(guān)的事務(wù)日志，事務(wù)日志記錄了SQLServer所有的事務(wù)和由這些事務(wù)引起的數(shù)據(jù)庫的變化，即事務(wù)日志記錄了對數(shù)據(jù)庫的所有修改操作。在數(shù)據(jù)庫中數(shù)據(jù)的任何改變寫到磁盤之前，這個(gè)改變首先在事務(wù)日志中做了記錄。1.2.1SQLServer數(shù)據(jù)庫基本知識(shí)事務(wù)日志可以具有以下三個(gè)作用。（1）恢復(fù)單個(gè)事務(wù)（2）在SQLServer啟動(dòng)時(shí)恢復(fù)所有未完成的事務(wù)（3）恢復(fù)數(shù)據(jù)庫時(shí)，將數(shù)據(jù)庫向前滾動(dòng)到出錯(cuò)前一秒的狀態(tài)1.2.1SQLServer數(shù)據(jù)庫基本知識(shí)5.系統(tǒng)數(shù)據(jù)庫（1）master數(shù)據(jù)庫（2）tempdb數(shù)據(jù)庫（3）model數(shù)據(jù)庫（4）msdb數(shù)據(jù)庫1.2.1SQLServer數(shù)據(jù)庫基本知識(shí)6.估算數(shù)據(jù)庫的空間需求作為數(shù)據(jù)庫管理員，主要任務(wù)之一就是創(chuàng)建數(shù)據(jù)庫，并且需要為每個(gè)文件指定容量。必須盡可能準(zhǔn)確地估算數(shù)據(jù)庫容量，以免浪費(fèi)磁盤空間資源或者因估計(jì)不足造成數(shù)據(jù)庫的空間不夠。1.2.2使用SQLServerManagementStudio創(chuàng)建數(shù)據(jù)庫使用數(shù)據(jù)庫存儲(chǔ)數(shù)據(jù)，首先要?jiǎng)?chuàng)建數(shù)據(jù)庫。前面已介紹過，一個(gè)數(shù)據(jù)庫必須至少包含一個(gè)數(shù)據(jù)文件一個(gè)事務(wù)日志文件。所以創(chuàng)建數(shù)據(jù)庫就是創(chuàng)建主數(shù)據(jù)庫文件和事務(wù)日志文件。在SQLServer中，可以使用SQLServerManagementStudio創(chuàng)建數(shù)據(jù)庫，也可以使用Transact-SQL創(chuàng)建數(shù)據(jù)庫1.2.2使用SQLServerManagementStudio創(chuàng)建數(shù)據(jù)庫任務(wù)1-1：使用SQLServerManagementStudio創(chuàng)建分銷系統(tǒng)數(shù)據(jù)庫。1.2.3Transact-SQL創(chuàng)建數(shù)據(jù)庫任務(wù)1-2：創(chuàng)建一個(gè)student數(shù)據(jù)庫。CREATEDATABASEstudent1.2.3Transact-SQL創(chuàng)建數(shù)據(jù)庫任務(wù)1-3：創(chuàng)建一個(gè)CUSTOMER數(shù)據(jù)庫，該數(shù)據(jù)庫的主數(shù)據(jù)文件的邏輯名稱是CUSTOMER_DATA，操作系統(tǒng)文件是CUSTOME_MDF，大小是15MB，最大是30MB,以20%的速度增加；該數(shù)據(jù)庫的日志文件的邏輯名稱是CUSTOMER_LOG，操作系統(tǒng)文件是CUSTOMER.LDF，大小是3MB，最大是10MB,以1MB的速度增加。1.2.3Transact-SQL創(chuàng)建數(shù)據(jù)庫CREATEDATABASEcustomerONPRIMARY(NAME=customer_data,FILENAME='e:\yxl\customer_data.mdf',SIZE=15MB,MAXSIZE=30MB,FILEGROWTH=20%)LOGON(NAME=customer_log,FILENAME='e:\yxl\customer_log.ldf',SIZE=3MB,MAXSIZE=10MB,FILEGROWTH=1MB)任務(wù)二數(shù)據(jù)表的創(chuàng)建與維護(hù)一、任務(wù)目標(biāo)

1掌握創(chuàng)建表、修改表結(jié)構(gòu)和刪除表的操作；2熟悉對數(shù)據(jù)表數(shù)據(jù)進(jìn)行添加、修改和刪除的操作；3掌握主鍵、外鍵和其他約束的建立。二、教學(xué)任務(wù)1介紹SQLServer2005表的類型、常用的系統(tǒng)數(shù)據(jù)類型；2使用SQLServerManagementStudio創(chuàng)建表；3使用Transact-SQL創(chuàng)建表；4在【表設(shè)計(jì)器】中修改表結(jié)構(gòu)、刪除表；5使用Transact-SQL修改表結(jié)構(gòu)、刪除表；6在【表設(shè)計(jì)器】中創(chuàng)建主鍵、在【關(guān)系圖】中創(chuàng)建外鍵；7使用Transact-SQL創(chuàng)建主鍵、外鍵；8創(chuàng)建其他約束；9在表數(shù)據(jù)編輯窗口中添加、修改、刪除記錄；10使用Transact-SQL對數(shù)據(jù)表進(jìn)行添加、修改、刪除記錄的操作。2.1SQLServer表概述

在數(shù)據(jù)庫中，表是最重要的數(shù)據(jù)庫對象。在關(guān)系數(shù)據(jù)庫中每個(gè)關(guān)系都體現(xiàn)為一張表。表是數(shù)據(jù)庫的基本構(gòu)成模塊，用來實(shí)際存儲(chǔ)和操作數(shù)據(jù)的邏輯結(jié)構(gòu)。對數(shù)據(jù)庫的各種操作，實(shí)際上就是對數(shù)據(jù)庫中表的操作。2.1.1數(shù)據(jù)表的概念

表是包含數(shù)據(jù)庫中所有數(shù)據(jù)的數(shù)據(jù)庫對象。表與電子表格相似，其結(jié)構(gòu)包含行和列，這樣數(shù)據(jù)在表中就按行和列的格式來組織排列。每行代表唯一的一條記錄，是組織數(shù)據(jù)的單位；而每列代表記錄中的一個(gè)域，用來描述數(shù)據(jù)的屬性。例如，表2-1所表示每一行代表一個(gè)客戶，各列分別表示客戶的詳細(xì)資料。2.1.1數(shù)據(jù)表的概念客戶編號(hào)客戶名稱電話傳真聯(lián)系地址C0001好又多超市020-85530888020-85530888中山大道188號(hào)C0002百佳超市020-83278030020-83278032中山五路219號(hào)C0003家樂福超市020-36637408020-36637418康王中路656號(hào)C0004萬佳百貨020-81930608020-81930604中山八路10號(hào)表2-1客戶資料2.1.1數(shù)據(jù)表的概念與表緊密關(guān)聯(lián)的幾個(gè)名詞如下：(1)記錄：每個(gè)表包含了若干行數(shù)據(jù)，表中的一行為一條記錄。(2)字段：每個(gè)記錄由若干的數(shù)據(jù)項(xiàng)構(gòu)成，將構(gòu)成記錄的每個(gè)數(shù)據(jù)項(xiàng)稱為字段，例如表中的【客戶編號(hào)】、【電話】等是字段。(3)主鍵：數(shù)據(jù)表通常都有一列或多個(gè)列的組合，用于唯一地標(biāo)識(shí)表中的每一行。這種具有標(biāo)識(shí)作用的列或列組稱為主鍵。(4)外鍵：外鍵是用于建立和加強(qiáng)兩個(gè)表數(shù)據(jù)之間的鏈接的一列或多列。通過將保存表中主鍵值的一列或多列添加到另一個(gè)表中，可創(chuàng)建兩個(gè)表之間的鏈接。這個(gè)列就成為第二個(gè)表的外鍵。2.1.2表的類型

1.標(biāo)準(zhǔn)表

2.臨時(shí)表

3.系統(tǒng)表4.分區(qū)表2.1.3系統(tǒng)數(shù)據(jù)類型包含數(shù)據(jù)的對象都具有一個(gè)相關(guān)的數(shù)據(jù)類型。數(shù)據(jù)類型描述了該對象所能包含的數(shù)據(jù)種類，也約束了什么樣的信息可以存儲(chǔ)在該對象中。SQLServer支持4種基本數(shù)據(jù)類型：字符和二進(jìn)制數(shù)據(jù)類型、日期時(shí)間數(shù)據(jù)類型、邏輯數(shù)據(jù)類型、數(shù)字?jǐn)?shù)據(jù)類型，用于各類數(shù)據(jù)值的存儲(chǔ)、檢索和解釋。此外，還有其它的一些數(shù)據(jù)類型，如可變數(shù)據(jù)類型、表類型等。2.1.3系統(tǒng)數(shù)據(jù)類型數(shù)據(jù)類型符號(hào)標(biāo)示字符型char、varchar、textUnicode字符型nchar、nvarchar、ntext整數(shù)型int、smallint、tinyint、bigint精確數(shù)值型decimal、numeric浮點(diǎn)型float、real貨幣型money、smallmoney位型bit日期時(shí)間類型Datetime、smalldatetime二進(jìn)制型binary、varbinary、image其他sql_variant、table、timestamp、Uniqueidentifier表2-2SQLServer提供的部分系統(tǒng)數(shù)據(jù)類型2.2分銷系統(tǒng)數(shù)據(jù)表的創(chuàng)建與維護(hù)表創(chuàng)建與維護(hù)，可以在表設(shè)計(jì)器中完成，也可以在用Transact-SQL來實(shí)現(xiàn)。2.2.1項(xiàng)目中的部分表客戶資料銷售訂單銷售訂單明細(xì)表2.2.2使用SQLServerManagementStudio創(chuàng)建表1.在表設(shè)計(jì)器中創(chuàng)建表的一般方法任務(wù)2-1：創(chuàng)建客戶資料表。圖2-1表設(shè)計(jì)器2.2.2使用SQLServerManagementStudio創(chuàng)建表圖2-2編輯客戶資料表第一個(gè)字段2.2.2使用SQLServerManagementStudio創(chuàng)建表圖2-3設(shè)定字符類型長度2.2.2使用SQLServerManagementStudio創(chuàng)建表圖2-4表設(shè)計(jì)器-客戶資料表的7個(gè)字段2.2.2使用SQLServerManagementStudio創(chuàng)建表圖2-5設(shè)置客戶資料表的主鍵點(diǎn)擊這里選定字段[客戶編碼]2.2.2使用SQLServerManagementStudio創(chuàng)建表2.在表設(shè)計(jì)器中設(shè)置主鍵、外鍵任務(wù)2-2：創(chuàng)建銷售訂單表。2.2.2使用SQLServerManagementStudio創(chuàng)建表圖2-6修改表名稱2.2.2使用SQLServerManagementStudio創(chuàng)建表圖2-7銷售訂單表9個(gè)字段2.2.2使用SQLServerManagementStudio創(chuàng)建表圖2-8添加建立關(guān)系的表2.2.2使用SQLServerManagementStudio創(chuàng)建表圖2-9關(guān)系圖窗口2.2.2使用SQLServerManagementStudio創(chuàng)建表圖2-10【表和列】對話框2.2.2使用SQLServerManagementStudio創(chuàng)建表圖2-11建立好外鍵的關(guān)系圖窗口2.2.2使用SQLServerManagementStudio創(chuàng)建表3.在表設(shè)計(jì)器中設(shè)置標(biāo)識(shí)列任務(wù)2-3：創(chuàng)建銷售訂單明細(xì)表。2.2.2使用SQLServerManagementStudio創(chuàng)建表圖2-12設(shè)置標(biāo)識(shí)列2.2.3使用Transact-SQL創(chuàng)建表在Transact-SQL中，使用CREATETABLE語句創(chuàng)建表，其語法格式如下：CREATETABLEtable_name(column_namedata_type[DEFAULTconstant_express][IDENTITY[(seed,increment)]][NULL|NOTNULL]column_namedata_type[DEFAULTconstant_express][IDENTITY[(seed,increment)]][NULL|NOTNULL]…)[on{group|DEFAULT}]2.2.3使用Transact-SQL創(chuàng)建表任務(wù)2-4：用Transact_SQL創(chuàng)建客戶資料表。use分銷系統(tǒng)goCREATETABLE客戶資料(

客戶編碼varchar(20)NOTNULLPRIMARYKEY,

客戶名稱varchar(100)NOTNULL,

聯(lián)系人varchar(20)NOTNULL,

電話varchar(50)NOTNULL,

傳真varchar(50),

地址varchar(200),

送貨地址varchar(200))go2.2.3使用Transact-SQL創(chuàng)建表任務(wù)2-5：用Transact_SQL創(chuàng)建客戶資料表USE分銷系統(tǒng)GOCREATETABLE銷售訂單(

銷售訂單號(hào)varchar(20)NOTNULLPRIMARYKEY,

日期datetimeNOTNULL,

客戶編碼varchar(20)NOTNULLFOREIGNKEYREFERENCES客戶資料(客戶編碼),

客戶名稱varchar(100)NOTNULL,

聯(lián)系人varchar(20)NOTNULL,

聯(lián)系電話varchar(50)NOTNULL,

送貨地址varchar(200)NOTNULL,

總金額numeric(12,4)NOTNULL,

備注varchar(500)NULL,)go2.2.3使用Transact-SQL創(chuàng)建表任務(wù)2-6：用Transact_SQL創(chuàng)建銷售訂單明細(xì)表。USE分銷系統(tǒng)GOCREATETABLE銷售訂單明細(xì)表(

銷售訂單號(hào)varchar(20)NOTNULL,

序號(hào)intIDENTITY(1,1)NOTNULL,

商品編碼varchar(20)NOTNULL,

商品名稱varchar(50)NOTNULL,

規(guī)格型號(hào)varchar(100)NOTNULL,

單位varchar(8)NOTNULL,

數(shù)量numeric(12,2)NOTNULL,

單價(jià)numeric(12,2)NOTNULL,

金額numeric(12,2)NOTNULL,

備注varchar(500)NULL, PRIMARYKEY(銷售訂單號(hào),序號(hào)), FOREIGNKEY(銷售訂單號(hào))REFERENCES銷售訂單(銷售訂單號(hào)))go2.2.4使用Transact-SQL修改表結(jié)構(gòu)任務(wù)2-7：修改銷售訂單明細(xì)表中單價(jià)字段的數(shù)據(jù)類型為numeric(12,4)。在SQLQuery窗口輸入如下命令，然后點(diǎn)擊【執(zhí)行】按鈕即可。ALTERTABLE銷售訂單明細(xì)表ALTERCOLUMN單價(jià)numeric(12,4)NOTNULL2.2.4使用Transact-SQL修改表結(jié)構(gòu)任務(wù)2-8：給客戶資料表新增一個(gè)名為“登記日期”的datetime類型的字段，然后再將該新增字段刪除。①在SQLQuery窗口中執(zhí)行如下命令，即可增加“登記日期”字段。ALTERTABLE客戶資料ADD登記日期datetimeNULL②在SQLQuery窗口中執(zhí)行如下命令，即可刪除“登記日期”字段。ALTERTABLE客戶資料dropcolumn登記日期2.2.4使用Transact-SQL修改表結(jié)構(gòu)任務(wù)2-9：用ALTERTABLE語句來達(dá)到取消銷售訂單明細(xì)表中序號(hào)字段的標(biāo)識(shí)列定義的目的。在SQLQuery窗口中執(zhí)行如下命令：ALTERTABLE銷售訂單明細(xì)表dropcolumn序號(hào)goALTERTABLE銷售訂單明細(xì)表ADD序號(hào)intNOTNULL2.2.5刪除表啟動(dòng)SQLServerManagementStudio后，在對象資源管理器中，展開分銷系統(tǒng)數(shù)據(jù)庫中的表，然后右擊要?jiǎng)h除的表，在彈出右鍵菜單中選擇[刪除]菜單命令即可。2.2.5刪除表圖2-13刪除表2.2.5刪除表在SQLServer中，刪除表還可以用DROPTABLE語句，其語法如下：DROPTABLE表名稱例如，在SQLQuery窗口中執(zhí)行如下命令，同樣可以刪除采購訂單明細(xì)表：droptable采購訂單明細(xì)表2.3實(shí)現(xiàn)數(shù)據(jù)庫的完整性

數(shù)據(jù)庫完整性是指數(shù)據(jù)庫中數(shù)據(jù)的正確性和相容性。所謂數(shù)據(jù)的正確性是指數(shù)據(jù)的值必須是正確的，必須在規(guī)定的范圍之內(nèi)；而數(shù)據(jù)的相容性是指數(shù)據(jù)的存在必須確保同一表格數(shù)據(jù)之間及不同表格數(shù)據(jù)之間的相容關(guān)系。例如，銷售訂單號(hào)必須是唯一的；銷售訂單表中的客戶編號(hào)必須是客戶資料表中已存在的；等等。2.3實(shí)現(xiàn)數(shù)據(jù)庫的完整性

數(shù)據(jù)庫完整性是衡量一個(gè)數(shù)據(jù)庫質(zhì)量好壞的重要標(biāo)準(zhǔn)，是確保數(shù)據(jù)庫中的數(shù)據(jù)的一致性、正確性以及符合企業(yè)規(guī)則的一種思想，是使無序數(shù)據(jù)條理化、確保正確數(shù)據(jù)被存放在正確的位置的一種手段。在SQLServer2005中可以通過各種規(guī)則（rule）、默認(rèn)值（default）、約束（constraint）和觸發(fā)器（trigger）等數(shù)據(jù)庫對象來保證數(shù)據(jù)庫的完整性。2.3.1規(guī)則規(guī)則(Rule)就是數(shù)據(jù)庫中對存儲(chǔ)在表的列或用戶定義數(shù)據(jù)類型中的值的規(guī)定和限制。規(guī)則是單獨(dú)存儲(chǔ)的獨(dú)立的數(shù)據(jù)庫對象。規(guī)則和約束可以同時(shí)使用，表的列可以有一個(gè)規(guī)則及多個(gè)約束。2.3.1規(guī)則1.創(chuàng)建規(guī)則使用CREATERULE語句可以創(chuàng)建規(guī)則，其語法如下：CREATERULE規(guī)則名稱AScondition_expression其中，condition_expression子句是規(guī)則的定義，通常是一個(gè)條件表達(dá)式，用來指定需要滿足的條件。該表達(dá)式可以是能夠用于WHERE條件子句的任何表達(dá)式，可以包含算術(shù)運(yùn)算符、關(guān)系運(yùn)算符和謂詞（如IN、LIKE、BETWEEN等）。該表達(dá)式中的必須有一個(gè)以字符“@”開頭的變量，改變量用于存儲(chǔ)在修改該列的記錄時(shí)用戶輸入的值。2.3.1規(guī)則任務(wù)2-10：為分銷系統(tǒng)數(shù)據(jù)庫創(chuàng)建規(guī)則，規(guī)則名稱為rule1，它將限定使用了該規(guī)則的列的值都必須大于0。在SQLQuery窗口中執(zhí)行如下命令：use分銷系統(tǒng)gocreaterulerule1as@value>02.3.1規(guī)則2.綁定規(guī)則規(guī)則創(chuàng)建后，其僅僅只是一個(gè)存在于數(shù)據(jù)庫中的對象，并未發(fā)生作用。需要將規(guī)則綁定到列或用戶定義數(shù)據(jù)類型上，規(guī)則才起作用，才能達(dá)到創(chuàng)建規(guī)則的目的。使用系統(tǒng)存儲(chǔ)過程sp_bindrule可以將規(guī)則綁定到列或用戶定義的數(shù)據(jù)類型上，其語法如下：sp_bindrule'rule_name','object_name'[,'futureonly_flag']其中，rule_name為規(guī)則名稱；object_name是規(guī)則要綁定到的列名或用戶自定義數(shù)據(jù)類型名；futureonly_flag是可選項(xiàng)，僅當(dāng)規(guī)則被綁定到用戶自定義數(shù)據(jù)類型時(shí)使用。2.3.1規(guī)則任務(wù)2-11：將規(guī)則rule1綁定到銷售訂單明細(xì)表的單價(jià)列中。在SQLQuery窗口中執(zhí)行如下命令：sp_bindrule'rule1','銷售訂單明細(xì)表.單價(jià)'2.3.1規(guī)則3.解除規(guī)則的綁定系統(tǒng)存儲(chǔ)過程sp_unbindrule可以解除規(guī)則與列或用戶自定義數(shù)據(jù)類型的綁定，其語法如下：sp_unbindrule'object_name'[,'futureonly_flag']參數(shù)意義與sp_bindrule相同。任務(wù)2-12：為銷售訂單明細(xì)表的單價(jià)列解除規(guī)則綁定。在SQLQuery窗口中執(zhí)行如下命令：sp_unbindrule'銷售訂單明細(xì)表.單價(jià)'2.3.1規(guī)則4.刪除規(guī)則當(dāng)一個(gè)規(guī)則要被刪除時(shí)，必須確保該規(guī)則當(dāng)前已經(jīng)不再被綁定到任何其他對象上。使用DROPRULE語句可以刪除當(dāng)前數(shù)據(jù)庫中的一個(gè)或多個(gè)規(guī)則，其語法如下：DROPRULErule_name[,rule_name，…]任務(wù)2-13：在確認(rèn)規(guī)則rule1已不被綁定到任何對象上后，刪除規(guī)則rule1。在SQLQuery窗口中執(zhí)行如下命令：DROPRULErule12.3.2約束約束（constrain）是SQLServer提供的自動(dòng)保持?jǐn)?shù)據(jù)完整性的一種機(jī)制，它定義了可以輸入表或表的單個(gè)列中的數(shù)據(jù)的限制條件。約束是用來維護(hù)關(guān)系數(shù)據(jù)庫數(shù)據(jù)的正確性和一致性、保證數(shù)據(jù)庫完整性的必要條件。在SQLServer中有6中約束：主鍵約束（PrimaryKeyConstraint）、外鍵約束（ForeignKeyConstraint）、唯一性約束（UniqueConstraint）、檢查約束（CheckConstraint）、默認(rèn)約束（DefaultConstraint）和非空值約束（NotNullConstraint）2.3.2約束1.主鍵約束表的一列或幾列的組合的值在表中惟一地指定一行記錄，這樣的一列或多列稱為表的主鍵（PrimaryKey，PK），通過它可強(qiáng)制表的實(shí)體完整性。主鍵不允許為空值，且不同兩行的鍵值不能相同。表中可以有不止一個(gè)鍵惟一標(biāo)識(shí)行，每個(gè)鍵都稱為侯選鍵，只可以選一個(gè)侯選鍵作為表的主鍵，其他侯選鍵稱作備用鍵2.3.2約束任務(wù)2-14：創(chuàng)建供應(yīng)商資料表，其中【供應(yīng)商編碼】為主鍵。在SQLQuery窗口中執(zhí)行如下命令：use分銷系統(tǒng)gocreatetable供應(yīng)商資料(

供應(yīng)商編碼varchar(20)constraintPK_GYSBMprimarykey,

供應(yīng)商名稱varchar(100)notnull,

聯(lián)系人varchar(20)NOTNULL,

電話varchar(50)NOTNULL,

傳真varchar(50)NOTNULL,

地址varchar(200)NOTNULL)go2.3.2約束任務(wù)2-15：創(chuàng)建采購訂單明細(xì)表（未考慮外鍵約束）。在SQLQuery窗口中執(zhí)行如下命令：USE分銷系統(tǒng)GOCREATETABLE采購訂單明細(xì)表(

采購訂單號(hào)varchar(20)NOTNULL,

序號(hào)intNOTNULL,

商品編碼varchar(20)NOTNULL,

商品名稱varchar(50)NOTNULL,

規(guī)格型號(hào)varchar(100)NOTNULL,

單位varchar(8)NOTNULL,

數(shù)量numeric(12,2)NOTNULL,

單價(jià)numeric(12,4)NOTNULL,

金額numeric(12,4)NOTNULL,

備注varchar(500)NULL, constraintPK_CGDDH_XHPRIMARYKEY(采購訂單號(hào),序號(hào)))go2.3.2約束2.外鍵約束外鍵約束定義了表與表之間的關(guān)系。主要用來維護(hù)兩個(gè)表之間的一致性關(guān)系。當(dāng)一個(gè)表中一列或多列的組合與其他表中的主鍵定義相同時(shí)，就可以將這些列或列的組合定義為外鍵，并設(shè)定它與哪個(gè)表的哪些列相關(guān)聯(lián)。這樣，當(dāng)在定義主鍵約束的表中更新數(shù)據(jù)時(shí)，與之相關(guān)聯(lián)的外鍵約束的表中的外鍵列也將相應(yīng)地做相同的更新。另一方面，當(dāng)向含有外鍵的表中插入數(shù)據(jù)時(shí)，如果與之關(guān)聯(lián)的表的主鍵列中沒有鍵要插入的數(shù)據(jù)時(shí)，這次的數(shù)據(jù)插入操作會(huì)被拒絕。所以，外鍵是關(guān)系數(shù)據(jù)庫中增強(qiáng)表與表之間參照完整性的主要機(jī)制。2.3.2約束任務(wù)2-16：創(chuàng)建采購訂單表。在SQLQuery窗口中執(zhí)行如下命令：USE分銷系統(tǒng)GOCREATETABLE采購訂單(

采購訂單號(hào)varchar(20)PRIMARYKEY,

日期datetimeNOTNULL,

供應(yīng)商編碼varchar(20)FOREIGNKEYREFERENCES供應(yīng)商資料(供應(yīng)商編碼),

供應(yīng)商名稱varchar(100)NOTNULL,

聯(lián)系人varchar(20)NOTNULL,

聯(lián)系電話varchar(50)NOTNULL,

總金額numeric(12,4)NOTNULL,

備注varchar(500)NULL)go2.3.2約束3.唯一性約束唯一性約束是SQL完整性約束類型中，除主鍵約束外的另一種可以定義唯一約束的類型。唯一性約束指定一個(gè)或多個(gè)列的組合的值具有唯一性，以防止在列中輸入重復(fù)的值。唯一性約束指定的列可以有NULL屬性。主鍵也強(qiáng)制執(zhí)行惟一性，但主鍵不允許空值，故主鍵約束強(qiáng)度大于惟一約束。因此主鍵列不能再設(shè)定唯一性約束。2.3.2約束任務(wù)2-17：創(chuàng)建倉庫資料表。在SQLQuery窗口中執(zhí)行如下命令：use分銷系統(tǒng)gocreatetable倉庫資料(

倉庫編碼varchar(20)primarykey,

倉庫名稱varchar(50)NOTNULL,

倉庫位置varchar(200)NOTNULL,

備注varchar(500)NOTNULL,constraintUK_CCMCUNIQUE(倉庫名稱))go2.3.2約束4.檢查約束檢查(Check)約束對輸入列或整個(gè)表中的值設(shè)置檢查條件，以限制輸入值，保證數(shù)據(jù)庫的數(shù)據(jù)完整性。當(dāng)對具有檢查約束列進(jìn)行插入或修改時(shí)，SQLServer將用該檢查約束的邏輯表達(dá)式對新值進(jìn)行檢查，只有滿足條件(邏輯表達(dá)式返回TRUE)的值才能填入該列，否則報(bào)錯(cuò)?？梢詾槊苛兄付ǘ鄠€(gè)CHECK約束。2.3.2約束任務(wù)2-18：創(chuàng)建期初庫存表，為其中的期初單價(jià)創(chuàng)建檢查約束（期初單價(jià)>0）。在SQLQuery窗口中執(zhí)行如下命令：use分銷系統(tǒng)gocreatetable期初庫存(

序號(hào)intIDENTITY(1,1)PrimaryKey,

倉庫編號(hào)varchar(20),

倉位編號(hào)varchar(20),

商品編碼varchar(20),

商品名稱varchar(50),

規(guī)格型號(hào)varchar(100),

單位varchar(8),

期初數(shù)量numeric(12,2),

期初單價(jià)numeric(12,4),

期初金額numeric(12,4),constraintCK_QCDJCHECK(期初單價(jià)>0))go2.3.2約束5.默認(rèn)約束默認(rèn)約束通過定義列的默認(rèn)值或使用數(shù)據(jù)庫的默認(rèn)值對象綁定表的列，以確保在沒有為某列指定數(shù)據(jù)時(shí)，來指定列的值。默認(rèn)值可以是常量，也可以是表達(dá)式，還可以為NULL值。2.3.2約束任務(wù)2-19：創(chuàng)建調(diào)撥單表，為其中的調(diào)撥日期定義一個(gè)默認(rèn)值為當(dāng)天日期時(shí)間。在SQLQuery窗口中執(zhí)行如下命令：use分銷系統(tǒng)gocreatetable調(diào)撥單(

調(diào)撥單號(hào)varchar(20)PrimaryKey,

調(diào)撥日期datetimeDEFAULT(GetDate()),

調(diào)撥人varchar(20)NOTNULL,

調(diào)出倉庫編號(hào)varchar(20)NOTNULL,

調(diào)出倉位編號(hào)varchar(20)NOTNULL,

調(diào)入倉庫編號(hào)varchar(20)NOTNULL,

調(diào)入倉位編號(hào)varchar(20)NOTNULL,

備注varchar(500))go2.3.2約束6.非空值約束非空值約束限制一列或多個(gè)列的值不能為空（NULL）。空表示未定義或未知的值。在默認(rèn)情況下，所有列都接受空值，若要某列不接受空值，則可以在該列上設(shè)置NOTNULL約束。NOTNULL約束只能用于列約束，其語法格式如下：<column_name>{<data_type>|<domain>}NOTNULL在前面創(chuàng)建表的語句中，已經(jīng)多次是用了非空值約束，應(yīng)該非常熟練了，請讀者自行練習(xí)。2.4錄入、修改和刪除分銷系統(tǒng)數(shù)據(jù)表的數(shù)據(jù)

在數(shù)據(jù)庫中的表對象建立后，用戶對表的訪問，可以歸納為4種基本操作：添加或插入新數(shù)據(jù)、更改或更新現(xiàn)有數(shù)據(jù)、刪除現(xiàn)有數(shù)據(jù)和檢索現(xiàn)有數(shù)據(jù)。2.4.1使用SQLServerManagementStudio對表數(shù)據(jù)進(jìn)行維護(hù)任務(wù)2-20：給客戶資料表錄入表2-6所示數(shù)據(jù)。表2-6倉庫資料倉庫編號(hào)倉庫名稱倉庫位置備注JK01窖口倉庫常青路221號(hào)易燃物HP02黃埔倉庫橋東路12號(hào)DC03東涌倉庫新港東路241號(hào)2.4.1使用SQLServerManagementStudio對表數(shù)據(jù)進(jìn)行維護(hù)任務(wù)2-21：修改客戶資料表中倉庫編碼為“JK01”的記錄的倉庫位置為“長青路221號(hào)”。操作步驟：在表數(shù)據(jù)編輯界面中，選定要修改的數(shù)據(jù)“常青路221號(hào)”，輸入正確的新數(shù)據(jù)“長青路221號(hào)”，覆蓋掉舊數(shù)據(jù)即可。2.4.1使用SQLServerManagementStudio對表數(shù)據(jù)進(jìn)行維護(hù)任務(wù)2-22：刪除客戶資料表中倉庫編碼為“DC03”的記錄。操作步驟：在表數(shù)據(jù)編輯界面中，選定倉庫編碼為“DC03”的記錄，執(zhí)行菜單命令[編輯]-[刪除]即可。如果要選定多條記錄，可以使用CTRL或SHIFT輔助鍵，跟WINDOWS中選擇對象的基本操作一樣。2.4.2使用Transact-SQL對表數(shù)據(jù)進(jìn)行維護(hù)在SQLServer中可以通過INSERT、UPDATE和DELETE這3個(gè)語句分別進(jìn)行插入數(shù)據(jù)、修改數(shù)據(jù)、刪除數(shù)據(jù)的操作。2.4.2使用Transact-SQL對表數(shù)據(jù)進(jìn)行維護(hù)1.INSERTINSERT語句可以給表添加一個(gè)或多個(gè)新行，其語法格式如下：INSERT[INTO]{table_name|view_name}[(column_name[,...n])]VALUES(expression|NULL|DEFAULT[,...n])2.4.2使用Transact-SQL對表數(shù)據(jù)進(jìn)行維護(hù)任務(wù)2-23：使用Transact-SQL語句實(shí)現(xiàn)任務(wù)2-19的操作。在SQLQuery窗口中執(zhí)行如下命令：use分銷系統(tǒng)goinsertinto倉庫資料values('JK01','窖口倉庫','常青路221號(hào)','易燃物')insert倉庫資料values('HP02','黃埔倉庫','橋東路12號(hào)',NULL)insert倉庫資料(倉庫編碼,倉庫名稱,倉庫位置)values('DC03','東涌倉庫','新港東路241號(hào)')go2.4.2使用Transact-SQL對表數(shù)據(jù)進(jìn)行維護(hù)2.UPDATEUPDATE語句用于修改現(xiàn)有的記錄，其語法結(jié)構(gòu)如下：UPDATE{table_name|view_name}SETcolumn_name=new_expression[,...n][WHERE<search_condition>]2.4.2使用Transact-SQL對表數(shù)據(jù)進(jìn)行維護(hù)任務(wù)2-25：使用Transact-SQL語句實(shí)現(xiàn)任務(wù)2-20的操作。在SQLQuery窗口中執(zhí)行如下命令：use分銷系統(tǒng)goupdate倉庫資料set倉庫位置='長青路號(hào)'where倉庫編碼='JK01'go2.4.2使用Transact-SQL對表數(shù)據(jù)進(jìn)行維護(hù)任務(wù)2-26：將表2-7中付款單號(hào)為“F200709190106”的記錄的付款日期修改為“2007-09-20”、付款金額修改為9000。在SQLQuery窗口中執(zhí)行如下命令：use分銷系統(tǒng)goupdate付款單set付款日期='2007-09-20',付款金額=9000where付款單號(hào)='F200709190106'go2.4.2使用Transact-SQL對表數(shù)據(jù)進(jìn)行維護(hù)3.DELETEDELETE語句可刪除表或視圖中的一行或多行，每一行的刪除都將被記入日志。DELETE語句的語法格式如下：DELETE[FROM]{table_name|view_name}[WHERE<search_condition>]2.4.2使用Transact-SQL對表數(shù)據(jù)進(jìn)行維護(hù)任務(wù)2-27：使用Transact-SQL語句實(shí)現(xiàn)任務(wù)2-21的操作。在SQLQuery窗口中執(zhí)行如下命令：use分銷系統(tǒng)godeletefrom倉庫資料where倉庫編碼='DC03'go2.4.2使用Transact-SQL對表數(shù)據(jù)進(jìn)行維護(hù)任務(wù)2-28：在付款單刪除張曉濤在2008-01-23的付款記錄。在SQLQuery窗口中執(zhí)行如下命令：use分銷系統(tǒng)godelete付款單where付款日期='2008-01-23'and付款人='張曉濤'go任務(wù)三對分銷系統(tǒng)數(shù)據(jù)庫

進(jìn)行查詢操作

一、任務(wù)目標(biāo)

1熟練掌握SELECT語句的結(jié)構(gòu)和使用；2掌握聚合函數(shù)在查詢語句中的應(yīng)用特點(diǎn)和基本要求；3掌握嵌套查詢的基本概念，能靈活應(yīng)用嵌套查詢；4掌握連接查詢的基本概念，能靈活應(yīng)用連接查詢。二、教學(xué)任務(wù)1介紹SELECT語句的語法格式；2運(yùn)用SELECT子句限定選擇的列及DISTINCT、TOPn、INTO等選項(xiàng)；3運(yùn)用WHERE子句限定查詢條件；4運(yùn)用ORDER子句對查詢結(jié)果進(jìn)行排序；5在查詢中使用聚合函數(shù)6對數(shù)據(jù)進(jìn)行分組篩選、計(jì)算和匯總7使用IN子查詢；8使用比較子查詢；9使用內(nèi)連接查詢；10使用外連接查詢；11使用交叉連接查詢；12使用聯(lián)合查詢。3.1基本查詢SELECT語句的基本結(jié)構(gòu)是SELECT-FROM-WHERE，它包含輸出字段、數(shù)據(jù)來源、和查詢條件等基本子句。這種固定格式中，可以不要WHERE，但是SELECT和FROM是必需的。SELECT語句的子句很多，功能非常強(qiáng)大，同時(shí)查詢條件和嵌套使用也可以很復(fù)雜3.1.1SELECT語句的語法格式SELECT語句的基本語法格式如下：SELECT[ALL|DISTINCT]列表達(dá)式[INTO新表名]FROM數(shù)據(jù)源[WHERE邏輯表達(dá)式][GROUPBY列名][HAVING邏輯表達(dá)式][ORDERBY列名[ASC|DESC]]3.1.2SELECT子句

1.選取表中指定的列使用SELECT指定查詢結(jié)果中出現(xiàn)哪些列，要在SELECT后寫出要出現(xiàn)的字段名，并用逗號(hào)隔開。3.1.2SELECT子句

任務(wù)3-1：查詢分銷系統(tǒng)數(shù)據(jù)庫的客戶資料表中的所有客戶的客戶名稱、聯(lián)系人和電話。在SQLQuery窗口中執(zhí)行如下命令：use分銷系統(tǒng)goselect客戶名稱,聯(lián)系人,電話from客戶資料go3.1.2SELECT子句

任務(wù)3-2：查詢客戶資料表中所有客戶的所有列的信息。在SQLQuery窗口中執(zhí)行如下命令：use分銷系統(tǒng)goselect*from客戶資料go3.1.2SELECT子句

2.自定義列標(biāo)題當(dāng)希望查詢結(jié)果中能使用用戶自己定義的列標(biāo)題，而不是默認(rèn)的用字段名作為查詢結(jié)果的列標(biāo)題時(shí)，可以用AS子句來達(dá)到此目的。3.1.2SELECT子句

任務(wù)3-3：查詢客戶資料表中所有客戶的客戶編碼、客戶名稱、電話，要求查詢結(jié)果中客戶編號(hào)列的標(biāo)題為ID、電話列的標(biāo)題為聯(lián)系電話。在SQLQuery窗口中執(zhí)行如下命令：use分銷系統(tǒng)goselect客戶編碼asID,客戶名稱,電話as聯(lián)系電話from客戶資料go3.1.2SELECT子句

3.消除查詢結(jié)果中的重復(fù)行在查詢結(jié)果中很可能看到重復(fù)行，特別是對表的查詢中只選擇部分列的時(shí)候，例如，對銷售訂單表只選擇客戶名稱、聯(lián)系人、送貨地址則會(huì)出現(xiàn)多行重復(fù)的情況。使用DISTINCT關(guān)鍵字可以消除查詢結(jié)果中出現(xiàn)的重復(fù)行。3.1.2SELECT子句

任務(wù)3-4：查詢銷售訂單明細(xì)表中所有記錄的商品名稱、單價(jià)，消除查詢結(jié)果中的重復(fù)行。在SQLQuery窗口中執(zhí)行如下命令：use分銷系統(tǒng)goselectDISTINCT商品名稱,單價(jià)from銷售訂單明細(xì)表go3.1.2SELECT子句

4.限制返回行數(shù)如果只需要返回查詢結(jié)果中的前幾個(gè)記錄，可以使用TOP選項(xiàng)。3.1.2SELECT子句

任務(wù)3-5：查詢銷售訂單表中所有記錄的客戶名稱、聯(lián)系人、聯(lián)系電話，只返回查詢結(jié)果的前3行。在SQLQuery窗口中執(zhí)行如下命令：use分銷系統(tǒng)goselectTOP3客戶名稱,聯(lián)系人,聯(lián)系電話from銷售訂單go3.1.2SELECT子句

任務(wù)3-6：查詢銷售訂單明細(xì)表中所有記錄的所有列，只返回查詢結(jié)果的前20%行。在SQLQuery窗口中執(zhí)行如下命令：use分銷系統(tǒng)goselectTOP20PERCENT*from銷售訂單明細(xì)表go3.1.2SELECT子句

5.計(jì)算列值SELECT子句中可以使用表達(dá)式作為查詢結(jié)果。3.1.2SELECT子句

任務(wù)3-7：查詢銷售訂單表中所有記錄的所有列，要求在查詢結(jié)果中多加一個(gè)標(biāo)題為運(yùn)費(fèi)的列，運(yùn)費(fèi)等于總金額的5%。在SQLQuery窗口中執(zhí)行如下命令：use分銷系統(tǒng)goselect*,總金額*0.05as運(yùn)費(fèi)from銷售訂單go3.1.2SELECT子句

6.INTO保存查詢結(jié)果在SELECT語句中，INTO子句用于執(zhí)行查詢后新建一個(gè)表，并把查詢結(jié)果放到該新建的表中。3.1.2SELECT子句

例如，把任務(wù)3-7的查詢結(jié)果保存到一個(gè)新建的表”運(yùn)費(fèi)表”中，可以用下面的語句：use分銷系統(tǒng)goselect*,總金額*0.05as運(yùn)費(fèi)INTO運(yùn)費(fèi)表from銷售訂單go3.1.3WHERE子句使用WHERE子句可以從數(shù)據(jù)表中過濾出符合WHERE子句指定的選擇條件的記錄，從而實(shí)現(xiàn)行的查詢。靈活地使用WHERE子句能夠指定許多不同的查詢條件，使得查詢結(jié)果更精確。WHERE子句必須緊跟在FROM子句子后，其包含一個(gè)邏輯表達(dá)式作為查詢條件。該邏輯表達(dá)式中可以包含的運(yùn)算符如表3-1所示。3.1.3WHERE子句1.使用比較運(yùn)算符WHERE子句中的比較運(yùn)算符有>、>=、=、<、<=、<>、!>、!<、!=，分別表示大于、大于等于、等于、小于、小于等于、不等于、不大于、不小于、不等于，其中<>和!=都表示不等于。3.1.3WHERE子句任務(wù)3-8：查詢銷售訂單表中客戶編碼為C0001的記錄的所有列。在SQLQuery窗口中執(zhí)行如下命令：use分銷系統(tǒng)goselect*from銷售訂單where客戶編碼='C0001'go3.1.3WHERE子句任務(wù)3-9：查詢銷售訂單表中總金額大于等于10000的記錄的客戶名稱、聯(lián)系電話和總金額。在SQLQuery窗口中執(zhí)行如下命令：use分銷系統(tǒng)goselect客戶名稱,聯(lián)系電話,總金額from銷售訂單where總金額>=10000go3.1.3WHERE子句2.使用邏輯運(yùn)算符WHERE子句中的邏輯運(yùn)算符包括AND、OR、NOT，這三個(gè)運(yùn)算符可以混合使用。使用AND連接的所有條件都為TRUE時(shí)，返回查詢結(jié)果；使用OR連接的所有條件中只要有一個(gè)為TRUE時(shí)，返回查詢結(jié)果；使用NOT連接的條件不成立時(shí)，返回查詢結(jié)果。3.1.3WHERE子句任務(wù)3-10：查詢銷售訂單表中客戶編碼為C0001，同時(shí)總金額大于等于10000的記錄的所有列。在SQLQuery窗口中執(zhí)行如下命令：use分銷系統(tǒng)goselect*from銷售訂單where客戶編碼='C0001'AND總金額>=10000go3.1.3WHERE子句任務(wù)3-11：查詢銷售訂單表中客戶編碼為C0001或總金額大于等于10000的記錄的所有列。在SQLQuery窗口中執(zhí)行如下命令：use分銷系統(tǒng)goselect*from銷售訂單where客戶編碼='C0001'OR總金額>=10000go3.1.3WHERE子句3.BETWEEN在WHERE子句中的BETWEEN條件可以為用戶查詢限定范圍。其中，BEWTEEN表示返回在某一范圍內(nèi)的數(shù)據(jù)，而NOTBEWTEEN表示返回不在某一范圍內(nèi)的數(shù)據(jù)。必須注意BETWEEN表示范圍時(shí)用了AND而不是TO，例如，BETWEEN12AND20表達(dá)了12到20這樣的數(shù)值范圍，而用BETWEEN12TO20這樣的表達(dá)則是錯(cuò)的。3.1.3WHERE子句任務(wù)3-12：查詢銷售訂單表中總金額在5000到10000之間的記錄的所有列。在SQLQuery窗口中執(zhí)行如下命令：use分銷系統(tǒng)goselect*from銷售訂單where總金額BETWEEN5000AND10000go3.1.3WHERE子句任務(wù)3-13：查詢銷售訂單表中總金額在5000到10000之外的記錄的所有列。在SQLQuery窗口中執(zhí)行如下命令：use分銷系統(tǒng)goselect*from銷售訂單where總金額NOTBETWEEN5000AND10000go3.1.3WHERE子句4.IN當(dāng)某查詢表達(dá)式的取值屬于某一列表子一的數(shù)據(jù)時(shí)，可以使用IN來限定查詢條件。3.1.3WHERE子句任務(wù)3-14：查詢銷售訂單表中客戶編碼C0001、C0002和C0003的記錄的所有列。在SQLQuery窗口中執(zhí)行如下命令：use分銷系統(tǒng)goselect*from銷售訂單where客戶編碼IN('C0001','C0002','C0003')go3.1.3WHERE子句5.LIKE當(dāng)查詢時(shí)無法確定某條記錄中的具體信息，可以使用模糊查詢。比如，要在客戶資料表中，需要查找一個(gè)“王”姓的聯(lián)系人，但具體名字不記得，可以使用LIKE關(guān)鍵字。WHERE子句中使用LIKE與通配符搭配使用，將表達(dá)式與字符串作比較。使用LIKE關(guān)鍵字來限定查詢條件的語法格式為：WHERE表達(dá)式[NOT]LIKE'pattern'其中，'pattern'是匹配字符串，該字符串可以使用通配符。3.1.3WHERE子句任務(wù)3-15：查詢客戶資料表中聯(lián)系人姓“王”的記錄的所有列。在SQLQuery窗口中執(zhí)行如下命令：use分銷系統(tǒng)goselect*from客戶資料where聯(lián)系人LIKE'王%'go3.1.3WHERE子句任務(wù)3-16：查詢客戶資料表中聯(lián)系人姓“王”，名字的第二字為“成”或“誠”的記錄的所有列。在SQLQuery窗口中執(zhí)行如下命令：use分銷系統(tǒng)goselect*from客戶資料where聯(lián)系人LIKE'王[成誠]%'go3.1.3WHERE子句5.ISNULLNULL表示未知、不可用或?qū)⒃谝院筇砑拥臄?shù)據(jù)，NULL不同于零，也不同于零長度的字符串。ISNULL可用于區(qū)分輸入的是0、空字符串還是無數(shù)據(jù)輸入。3.1.3WHERE子句任務(wù)3-17：查詢客戶資料表中未錄入傳真號(hào)碼的記錄的所有列。在SQLQuery窗口中執(zhí)行如下命令：use分銷系統(tǒng)goselect*from客戶資料where傳真ISNULLgo3.1.4ORDERBY子句

ORDERBY子句一般位于SELECT語句的最后，其功能是對查詢返回的數(shù)據(jù)進(jìn)行重新排序。ORDERBY子句的語法格式如下：ORDERBYorder_expression[ASC|DESC]其中，order_expression是排序表達(dá)式，可以是列名、表達(dá)式，關(guān)鍵字ASC表示升序排序，DESC表示降序排序，系統(tǒng)默認(rèn)值為ASC。3.1.4ORDERBY子句

任務(wù)3-18：將銷售訂單表中所有記錄按照總金額由大到小的順序排序輸出。在SQLQuery窗口中執(zhí)行如下命令：use分銷系統(tǒng)goselect*from銷售訂單orderby總金額descgo3.1.4ORDERBY子句

任務(wù)3-19：將銷售訂單表中所有記錄按照客戶編碼由小到大、客戶編碼相同時(shí)總金額由大到小的順序排序輸出。在SQLQuery窗口中執(zhí)行如下命令：use分銷系統(tǒng)goselect*from銷售訂單orderby客戶編碼asc,總金額descgo3.1.4ORDERBY子句

任務(wù)3-20：將銷售訂單明細(xì)表中商品編碼為“G0003”的記錄按照金額由大到小的順序排序輸出。在SQLQuery窗口中執(zhí)行如下命令：use分銷系統(tǒng)goselect*from銷售訂單明細(xì)表where商品編碼='G0003'orderby金額descgo3.2包含聚合函數(shù)的高級(jí)查詢在數(shù)據(jù)查詢時(shí)，經(jīng)常要對查詢結(jié)果進(jìn)行分類、匯總或計(jì)算，比如，統(tǒng)計(jì)一個(gè)月的銷售訂單數(shù)、統(tǒng)計(jì)每個(gè)客戶的訂單金額、對每樣商品進(jìn)行分類匯總看看哪樣商品是最暢銷等等。本節(jié)將介紹SELECT語句中用于數(shù)據(jù)統(tǒng)計(jì)的子句和SQLServer提供的幾個(gè)聚合函數(shù)3.2.1常用的聚合函數(shù)表3-3常用的聚合函數(shù)函數(shù)名功能COUNT()用于統(tǒng)計(jì)滿足條件的行數(shù)SUM()返回表達(dá)式中所有值的和AVG()返回表達(dá)式中所有值的平均值MAX()返回表達(dá)式中所有值的最大值MIN()返回表達(dá)式中所有值的最小值3.2.1常用的聚合函數(shù)任務(wù)3-21：查詢客戶資料表中的記錄數(shù)。在SQLQuery窗口中執(zhí)行如下命令：use分銷系統(tǒng)goselectcount(*)as客戶數(shù)from客戶資料go3.2.1常用的聚合函數(shù)任務(wù)3-22：查詢2008-01-01以來的銷售訂單的總金額之和。use分銷系統(tǒng)goselectSUM(總金額)from銷售訂單where日期>='2008-01-01'go3.2.1常用的聚合函數(shù)任務(wù)3-23：查詢屬于當(dāng)日的銷售訂單的總金額之平均值。use分銷系統(tǒng)goselectAVG(總金額)from銷售訂單where日期=GetDate()go3.2.1常用的聚合函數(shù)任務(wù)3-24：查詢當(dāng)日的銷售訂單的總金額的最大值和最小值。use分銷系統(tǒng)goselectMAX(總金額)as最大單,MIN(總金額)as最小單from銷售訂單where日期=GetDate()go3.2.2分組篩選分組是按照某一列數(shù)據(jù)的值或某個(gè)列組合的值將查詢出來的行分成若干組，每組在指定列或列組合上具有相同的值。分組可以通過GROUPBY子句來實(shí)現(xiàn)，其語法格式如下：GROUPBYgroup_by_epression其中，group_by_epression是用于分組的表達(dá)式，通常包含字段名。SELECT子句中的列列表中只能包含在GROUPBY中指出的列或在聚合函數(shù)中指定的列。3.2.2分組篩選任務(wù)3-25：在銷售訂單中查詢每個(gè)客戶的訂單數(shù)。use分銷系統(tǒng)goselect客戶編碼,count(*)as訂單數(shù)from銷售訂單groupby客戶編碼go3.2.2分組篩選任務(wù)3-26：在銷售訂單中統(tǒng)計(jì)每個(gè)客戶各自的訂單總金額之和。use分銷系統(tǒng)goselect客戶編碼,SUM(總金額)as訂單總金額from銷售訂單groupby客戶編碼go3.2.2分組篩選任務(wù)3-27：在銷售訂單中統(tǒng)計(jì)每個(gè)客戶各自的訂單總金額之和，從中篩選出訂單總金額之和大于20000的。use分銷系統(tǒng)goselect客戶編碼,SUM(總金額)as訂單總金額from銷售訂單groupby客戶編碼havingSUM(總金額)>20000go3.2.2分組篩選任務(wù)3-28：在銷售訂單中統(tǒng)計(jì)2007-01-01以來每個(gè)客戶各自的訂單總金額之和，從中篩選出訂單總金額之和大于20000的。use分銷系統(tǒng)goselect客戶編碼,SUM(總金額)as訂單總金額from銷售訂單where日期>='2007-01-01'groupby客戶編碼havingSUM(總金額)>20000go3.2.3計(jì)算與匯總

計(jì)算與匯總時(shí)生成合計(jì)作為附加的匯總行出現(xiàn)在結(jié)果集的最后。當(dāng)與BY一起使用時(shí)，COMPUTE子句在結(jié)果集內(nèi)生成控制中斷和小計(jì)。其語法格式如下：[COMPUTE{聚合函數(shù)(expression)}[,...n][BYexpression[,...n]]]其中，expression是列名，COMPUTE將產(chǎn)生額外的匯總行。3.2.3計(jì)算與匯總

任務(wù)3-29：查詢銷售訂單中2008-01-01以來的訂單的銷售訂單號(hào)、客戶編碼、聯(lián)系人、聯(lián)系電話，并產(chǎn)生一個(gè)銷售訂單總數(shù)行。use分銷系統(tǒng)goselect銷售訂單號(hào),客戶編碼,聯(lián)系人,聯(lián)系電話from銷售訂單where日期>='2008-01-01'computecount(銷售訂單號(hào))go3.2.3計(jì)算與匯總

任務(wù)3-30：將銷售訂單按客戶編碼排序，并匯總各客戶的訂單總數(shù)和訂單總金額之和。use分銷系統(tǒng)goselect銷售訂單號(hào),日期,客戶編碼,聯(lián)系人,聯(lián)系電話,總金額from銷售訂單orderby客戶編碼computecount(銷售訂單號(hào)),sum(總金額)by客戶編碼go3.3嵌套查詢在SQLServer中，一個(gè)SELECT-FROM-WHERE語句稱為一個(gè)查詢塊。將一個(gè)查詢塊嵌套在另一個(gè)查詢塊的WHERE子句或HAVING子句的條件中的查詢稱為嵌套查詢。SQLServer允許多層嵌套查詢，內(nèi)嵌的select語句稱為子查詢。嵌套查詢一般的查詢方法是由里向外進(jìn)行處理，即每個(gè)子查詢在上一級(jí)查詢處理之前處理，子查詢的結(jié)果用于建立其父查詢的查找條件。子查詢中所存取的表可以是父查詢沒有存取的表，子查詢選出的記錄不顯示。子查詢中如果使用orderby子句的話還必須在該子查詢中使用TOPn選項(xiàng)。3.3.1IN子查詢IN子查詢用于進(jìn)行一個(gè)給定值是否在只查詢結(jié)果集中的判斷。例如，銷售員需要做客戶跟蹤回訪，想獲到訂單明細(xì)中有名稱為“運(yùn)動(dòng)水壺”的客戶資料。這樣，需要先從銷售訂單明細(xì)表中查詢出存在“運(yùn)動(dòng)水壺”的訂單的銷售訂單號(hào)，再根據(jù)銷售訂單號(hào)在銷售訂單表中查得客戶名稱、聯(lián)系人和聯(lián)系電話。3.3.1IN子查詢?nèi)蝿?wù)3-31：查詢出訂單明細(xì)中有名稱為“運(yùn)動(dòng)水壺”的客戶的客戶名稱、聯(lián)系人和聯(lián)系電話。use分銷系統(tǒng)goselect客戶名稱,聯(lián)系人,聯(lián)系電話from銷售訂單where銷售訂單號(hào)IN(selectdistinct銷售訂單號(hào)from銷售訂單明細(xì)表where商品名稱='運(yùn)動(dòng)水壺')go3.3.1IN子查詢?nèi)蝿?wù)3-32：查詢出訂單明細(xì)中沒有名稱為“運(yùn)動(dòng)水壺”的客戶的客戶名稱、聯(lián)系人和聯(lián)系電話，銷售員將把這些客戶作為業(yè)務(wù)拓展對象。use分銷系統(tǒng)goselect客戶名稱,聯(lián)系人,電話from客戶資料where客戶編碼NOTIN(selectdistinct客戶編碼from銷售訂單where銷售訂單號(hào)IN(selectdistinct銷售訂單號(hào)from銷售訂單明細(xì)表where商品名稱='運(yùn)動(dòng)水壺'))go3.3.1IN子查詢?nèi)蝿?wù)3-33：查詢?nèi)坑唵慰偨痤~之和最高的1個(gè)客戶的客戶名稱、聯(lián)系人和電話，業(yè)務(wù)員將邀請這個(gè)客戶出席公司年會(huì)。use分銷系統(tǒng)goselect客戶名稱,聯(lián)系人,電話from客戶資料where客戶編碼IN(selectTOP1客戶編碼from銷售訂單groupby客戶編碼orderbySUM(總金額)desc)go3.3.2比較子查詢可以用比較運(yùn)算符=、!=、<、<=、>、>=等連接子查詢，但比較運(yùn)算符連接的子查詢的話，只允許子查詢返回一個(gè)值。像任務(wù)3-33那樣，子查詢只返回一個(gè)客戶編碼，可以使用比較運(yùn)算符來連接該子查詢3.3.2比較子查詢?nèi)蝿?wù)3-34：在銷售訂單表中查詢總金額大于銷售訂單號(hào)為“200801010001”的總金額的所有銷售訂單的所有列。use分銷系統(tǒng)