項目四數(shù)據(jù)表的操作.ppt

1、數(shù)據(jù)庫課件,上節(jié)課任務實訓,創(chuàng)建客戶訂單管理系統(tǒng)數(shù)據(jù)庫Goods，邏輯名Goods_data，物理名D:客戶訂貨管理系統(tǒng)Goods.mdf,初始大小8MB，最大容量500MB，增長速度2MB；建立一個輔助文件，邏輯名Good_dat，物理名D:客戶訂貨管理系統(tǒng)Goods1.ndf,初始大小6MB，最大容量200MB，增長速度10%；數(shù)據(jù)庫事務日志邏輯名Goods_log，物理名D:客戶訂貨管理系統(tǒng)Goods.ldf,初始大小5MB，最大容量50MB，增長速度1MB。,項目四 數(shù)據(jù)表的操作,3.1任務描述 本章完成項目的第3個任務：在大學生選課管理數(shù)據(jù)庫Student中，完成如下操作： 1創(chuàng)建數(shù)

2、據(jù)庫的5個數(shù)據(jù)表：學生信息表，教師信息表，課程 信息表，教師教課信息表，學生選課信息表。 2輸入這5個數(shù)據(jù)表的記錄內(nèi)容。 3向有關數(shù)據(jù)表中添加新的記錄。 4調(diào)整有關課程的學時量和收費標準。 5刪除有關學生的記錄和有關教師的記錄。,表的基本概念,表是對現(xiàn)實世界的抽象描述中，將概念數(shù)據(jù)模型轉(zhuǎn)換成結構數(shù)據(jù)模型的產(chǎn)物，是關系模型的主要元素。 SQL Server 2008中支持的表是關系模型中表的實現(xiàn)和具體化,表的基本概念,實體、記錄、行 屬性、字段、列,3.2 SQL Server數(shù)據(jù)類型、表達式和常用函數(shù),3.2.1 SQL Server數(shù)據(jù)類型 1整型數(shù)據(jù) （1）bigint：可以存儲從-263

3、到263-1范圍之間的所有整數(shù)。 （2）int：可以存儲從-231到231-1范圍之間的所有整數(shù)。 （3）smallint：可以存儲從-215到215-1范圍之間的所有整數(shù) 。 （4）tinyint：可以存儲從0到255范圍之間的所有整數(shù)。 2浮點型數(shù)據(jù) （1）real：可以存儲正的或者負的十進制數(shù)值。 （2）float(n)：可以精確到第15位小數(shù)。 （3）decimal(p,s):可以提供小數(shù)所需要的實際存儲空間。其中p為十進制數(shù)的最大位數(shù)，p是從1到38的整數(shù)，默認值為18；s為小數(shù)的最大位數(shù)，s是從0到p的整數(shù)，默認值為0。 （4）numeric(p,s)：同decimal(p,s)。

4、,3字符型數(shù)據(jù) （1）char(n)：為固定長度存儲字符串的數(shù)據(jù)類型。 （2）varchar(n)：為可變長度存儲字符串的數(shù)據(jù)類型。 （3）nchar(n)：存儲固定長度的Unicode字符數(shù)據(jù)。 （4）nvarchar(n)：存儲可變長度的Unicode字符數(shù)據(jù)。 4日期時間型數(shù)據(jù) （1）date：用于存儲從公元0001年1月1日至9999年12月31日的日期型數(shù)據(jù)，格式為：YYYY-MM-DD。 （2）time：用于存儲時間數(shù)據(jù)，格式為：hh:mm:ss。 （3）datetime2：用于存儲日期和時間的結合體，格式為：YYYY-MM-DD hh:mm:ss。 （4）datetime：用于存

5、儲日期和時間的結合體。 （5）smalldatetime：與datetime數(shù)據(jù)類型類似，但其日期時間范圍較小，它存儲從1900年1月1日到2079年6月6日內(nèi)的日期時間。,3.2.1 SQL Server數(shù)據(jù)類型,5文本型數(shù)據(jù) （1）text：用于存儲大量文本數(shù)據(jù)。 （2）ntext：與text數(shù)據(jù)類型類似，存儲在其中的數(shù)據(jù)通常是直接能輸出到顯示設備上的字符（即存儲Unicode字符數(shù)據(jù)）。 6貨幣型數(shù)據(jù) （1）money：用于存儲貨幣值。 （2）smallmoney：與money數(shù)據(jù)類型類似，其存儲范圍為-214748.3648到214748.3647。 7位數(shù)據(jù) bit：稱為位數(shù)據(jù)類型，

6、其數(shù)據(jù)有兩種取值：0和1，長度為1字節(jié)。 8二進制型數(shù)據(jù) （1）binary(n)：用于存儲固定長度的二進制數(shù)據(jù)。 （2）varbinary(n)：用于存儲可變長度的二進制數(shù)據(jù)。 （3）image：用于存儲可變長度的二進制數(shù)據(jù)，常用于存儲圖形類數(shù)據(jù)，比如照片、圖片等。,3.2.1 SQL Server數(shù)據(jù)類型,3.2.2 SQL Server中的常量,SQL Server主要有以下幾種類型的常量： 整型常量，例如： 34，-890 實型常量，例如：56.78，-789.675，1.2E+5 字符型常量，例如： abCFR12，張大友 日期型常量，例如： 1987-6-23，或者 6/23/19

7、87,5數(shù)據(jù)表字段專用的邏輯表達式 （1）判斷字段的取值范圍 字段名 between 例如： 工資 between 2590 and 5600 字段名 not between 例如： 工資 not between 2590 and 5600 （2）判斷字段的離散取值 字段名 in ,例如：姓名 in (張大有 , 李芳芳 , 吳軍 , 孫曉麗 ) 字段名 not in ,例如：姓名 not in (張大有 , 李芳芳 , 吳軍 ) （3）判斷字符型字段相匹配 字段名 like 字符串例如： 姓名 like 王% ； 職稱 like %工程師 字段名 not like 字符串 例如： 姓名 no

8、t like 王% ; （4）判斷字段是否為空值 字段名 is null,例如：中文成績 is null 字段名 is not null,例如：中文成績 is not null,3.2.3 SQL Server中的表達式,3.2.4 SQL Server中的常用函數(shù),1數(shù)學函數(shù) 常用的數(shù)學函數(shù)有： （1）abs(數(shù)學表達式)：返回指定數(shù)學表達式值的絕對值。 （2）rand()：返回（0 , 1）之內(nèi)一個隨機數(shù) 。 （3）sqrt（數(shù)學表達式）：返回指定數(shù)學表達式值的算術平方根。 2字符函數(shù) 常用的字符函數(shù)有： （1）lower(字符串)：將指定字符串中的大寫字母轉(zhuǎn)為小寫字母。 （2）upper

9、(字符串)：將指定字符串中的小寫字母轉(zhuǎn)為大寫字母。 （3）len(字符串)：返回指定字符串的長度 。 （4）left(字符串 , n)：返回指定字符串的前n個字符組成的字符串。 （5）right(字符串 , n)：返回指定字符串的后n個字符組成的字符串。,3日期函數(shù) 常用的日期函數(shù)有： （1）getdate()：返回當前系統(tǒng)日期和時間 。 （2）year(日期)：返回指定日期中的年。 （3）month(日期)：返回指定日期中的月。 （4）day(日期)：返回指定日期中的天。 （5）datediff(day , 日期1, 日期2) ：返回日期2與日期1之間相差的天數(shù)。 4轉(zhuǎn)換函數(shù) cast(表達

10、式 as 目標類型)：將指定表達式的值轉(zhuǎn)為指定的類型，并返回轉(zhuǎn)后的值。,3.2.4 SQL Server中的常用函數(shù),3.3 SQL Server數(shù)據(jù)表,1數(shù)據(jù)表的概述 表是關系模型中表示實體的方式，是用來組織和存儲數(shù)據(jù)，具有行列結構的數(shù)據(jù)庫對象。表是由定義的列數(shù)和可變的行數(shù)組成的邏輯結構，以二維表格形式顯示。 2數(shù)據(jù)表的組成 數(shù)據(jù)表有表名確定。其由數(shù)據(jù)表結構和數(shù)據(jù)表內(nèi)容兩部分組成。 3數(shù)據(jù)表結構 數(shù)據(jù)表結構包括：表由幾列（幾個字段）組成以及每個字段的字段名、字段類型、字段大小和字段約束的定義等。 字段約束包括：該字段是否定義為主鍵、是否允許取空值、取值應滿足的條件、取值是唯一、字段的默認值和

11、是否定義為外鍵等。 4數(shù)據(jù)表內(nèi)容 數(shù)據(jù)表內(nèi)容是指該表中所有記錄的集合。如表3-1,表3-2,3.3 SQL Server數(shù)據(jù)表,數(shù)據(jù)表的三個鍵,用來標識表中記錄的列或列的組合稱為關鍵字，分為主鍵、唯一鍵和外鍵 主鍵 每個表只能定義一個主鍵，它是表中記錄的標識。 主鍵列可以由一個或多個列組成。 主鍵值不能為空。 主鍵值不重復。若主鍵值是多列組成時，某一列的值可以重復，但多列組合后的值不能重復。 image和text類型的列不能作主鍵。 例如：銷售人員表的“工號”列是該表的主鍵,數(shù)據(jù)表的三個鍵,唯一鍵 唯一鍵是表中沒有被選為主鍵的關鍵字，它限定了除主鍵以外的列或多列值的不重復，同樣保證了數(shù)據(jù)的唯一

12、性，和主鍵的區(qū)別如下： 每個表可以有多個唯一鍵。 唯一鍵的列值可以為空，但只能有一個空。 例如：銷售人員表的“電話”列，可以作為該表的唯一鍵，即只要有號碼則號碼不同。,數(shù)據(jù)表的三個鍵,外鍵 外鍵在另一個表中是關鍵字。外鍵是用來建立數(shù)據(jù)庫中多個表之間的關聯(lián)的。 外鍵列可以由一列或多列組成。 外鍵列的取值可以為空，可以重復，但必須是它所引用列（參照列）的值之一。 引用列必須是其所在表的主鍵或唯一鍵。 例如：銷售人員表的“部門號”列，可以作為該表的外鍵，它所引用的列在部門表中是主鍵。并且銷售人員表和部門人員表中對應行的該列值相等。,3.4 創(chuàng)建SQL Server數(shù)據(jù)表,3.4.1 利用SQL Se

13、rver Management Studio創(chuàng)建數(shù)據(jù)表 1在“對象資源管理器”窗口中展開“數(shù)據(jù)庫”節(jié)點，再展開新建表所屬的數(shù)據(jù)庫名（比如Student），右擊其“表”節(jié)點，出現(xiàn)彈出單 ,如圖3-1所示：,2在彈出菜單中執(zhí)行【新建表】命令，打開“表設計器”窗口。如圖3-2所示 3可右擊指定的列名，在出現(xiàn)的彈出菜單中，執(zhí)行【設置主鍵】命令設置該指定列為主鍵。 4定義好所有列之后，單擊工具欄上的【保存】按鈕，保存表，完成表的創(chuàng)建 ，如圖3-3所示：,3.4.1 利用SQL Server Management Studio創(chuàng)建數(shù)據(jù)表,3.3 SQL Server數(shù)據(jù)表,3.4.2利用SQL命令創(chuàng)建數(shù)據(jù)

14、表,語法格式： CREATE TABLE 數(shù)據(jù)庫名. 架構名 新建的表名 （字段名 字段類型 NULL | NOT NULL DEFAULT 字段的默認值表達式 PRIMARY KEY | UNIQUE |FOREIGN KEY REFERENCES 主鍵表(主鍵表的主鍵列) |CHECK (字段的約束條件表達式) ，n,【例3-1】創(chuàng)建上述數(shù)據(jù)庫student中的學生成績表sgrade1。 (上機實踐) Use student Go CREATE TABLE sgrade 1 （xh char(6) PRIMARY KEY , xm varchar(8) NOT NULL , xb char

15、(2) NOT NULL DEFAULT 男 , rxsj date NOT NULL , szx varchar(20) NOT NULL , bj varchar(20) NOT NULL , zw decimal(4,1) CHECK (zw=0 and zw=0 and yw=0 and sx=100) ）,3.4.2利用SQL命令創(chuàng)建數(shù)據(jù)表,3.5 數(shù)據(jù)表的修改與刪除,3.5.1利用SQL Server Management Studio修改數(shù)據(jù)表 1修改數(shù)據(jù)表 （1）在“對象資源管理器”窗口中，展開“數(shù)據(jù)庫”節(jié)點，再展開要修改的表所屬的數(shù)據(jù)庫名（比如Student），再展開其“表”

16、節(jié)點，右擊要修改的表名稱，出現(xiàn)彈出菜單，如圖3-4所示。,（2）在彈出菜單中執(zhí)行【設計】命令，進入“表設計器”窗口，可以利用圖形化工具完成增加、刪除字段和修改有關字段的屬性，還可以右擊任意列名，通過彈出菜單中的有關約束設置命令。如圖3-5所示。,3.5.1利用SQL Server Management Studio修改數(shù)據(jù)表,2數(shù)據(jù)表的刪除、重命名與查看屬性 （1）在“對象資源管理器”窗口中展開“數(shù)據(jù)庫”節(jié)點，再展開其“表”節(jié)點，右擊要刪除或重命名或查看屬性的表名稱，出現(xiàn)彈出菜單 ，如圖3-6所示 （2）執(zhí)行彈出菜單中的【刪除】命令，可刪除數(shù)據(jù)表 （3）執(zhí)行彈出菜單中的【重命名】命令，可重命名

17、數(shù)據(jù)表 （4）執(zhí)行彈出菜單中的【屬性】命令，可查看數(shù)據(jù)表的屬性,3.5.1利用SQL Server Management Studio修改數(shù)據(jù)表,3.5.2利用SQL命令修改數(shù)據(jù)表,1查看數(shù)據(jù)表的信息 語法格式：EXEC sp_help table_name 2向數(shù)據(jù)表中添加一列 語法格式： ALTER TABLE 表名 ADD 字段名 字段類型 NULL | NOT NULL DEFAULT 字段的默認值表達式 PRIMARY KEY | UNIQUE |FOREIGN KEY REFERENCES 主鍵表(主鍵表的主鍵列)|CHECK (字段的約束條件表達式),3.5.2利用SQL命令修改

18、數(shù)據(jù)表,上機實操： 【例3-2】于上述學生成績表sgrade中，添加一年齡字段nl， 取值范圍為18歲到25歲。 Use student Go ALTER TABLE sgrade ADD nl tinyint NOT NULL CHECK (nl=18 and nl=25) Go,3修改數(shù)據(jù)表中某一列的類型或大小或是否取空值 語法格式： ALTER TABLE 表名 ALTER COLUMN 字段名 修改后字段類型 NULL | NOT NULL 【例3-3】于上述學生成績表sgrade中，將字段xm的寬度改為8 Use Student Go ALTER TABLE sgrade ALTER

19、 COLUMN xm char(8) Go,3.5.2利用SQL命令修改數(shù)據(jù)表,4刪除數(shù)據(jù)表中的某一列 語法格式： ALTER TABLE 表名 DROP COLUMN 列名 Go 【例3-4】于上述學生成績表sgrade中，刪除字段nl Use Student Go ALTER TABLE sgrade DROP COLUMN nl Go,3.5.2利用SQL命令修改數(shù)據(jù)表,上機實操：,用SQL語句實現(xiàn)： 1、于上述學生成績表sgrade中，添加一“民族”字段mz 字符類型，長度8，默認為“漢族” 2、于上述學生成績表sgrade中，將字段mz的寬度改為12 3、于上述學生成績表sgrade

20、中，刪除字段mz,1、于上述學生成績表sgrade中，添加一“民族”字段mz 字符類型，長度8，默認為“漢族” ALTER TABLE sgrade ADD mz char(8) NOT NULL default 漢族 2、于上述學生成績表sgrade中，將字段mz的寬度改為12 ALTER TABLE sgrade ALTER COLUMN xm char(8) 3、于上述學生成績表sgrade中，刪除字段mz ALTER TABLE sgrade DROP COLUMN mz,5刪除數(shù)據(jù)表 語法格式： DROP Table 表名 【例3-5】刪除數(shù)據(jù)庫Student中的數(shù)據(jù)表sgrade

21、1。 Use Student Go DROP Table sgrade 1 Go,3.5.2利用SQL命令修改數(shù)據(jù)表,6復制數(shù)據(jù)表結構 語法格式： Select 字段名表/* Into 目的數(shù)據(jù)表 From 源數(shù)據(jù)表 Where 01 【例3-6】在上述數(shù)據(jù)庫Student中，復制數(shù)據(jù)表sgrade的表結構成為一新表sgrade2，要求只復制xh，xm，zw，yw，sx這5個字段。 Use Student Go Select xh , xm , zw , yw , sx Into sgrade2 From sgrade Where 01 Go,3.5.2利用SQL命令修改數(shù)據(jù)表,3.6創(chuàng)建數(shù)據(jù)

22、表的約束,3.6.1約束的類型 1PRIMARY KEY（主鍵）約束 主鍵約束實現(xiàn)數(shù)據(jù)的實體完整性，它是唯一標識。 2UNIQUE（唯一）約束 唯一約束實現(xiàn)數(shù)據(jù)的實體完整性，限制表的非主鍵列中不允許輸入重復值。 3NOT NULL（非空）約束 非空約束實現(xiàn)數(shù)據(jù)的域完整性，它用于設定某列值不能為空。 4CHECK（檢查）約束 檢查約束強制域完整性，可以接受的數(shù)據(jù)范圍。 5DEFAULT（默認）約束 默認約束強制域完整性，它為表中某列建立一個默認值。 6FOREIGN KEY（外鍵）約束 外鍵是指一個表中的一列或列組合，它雖不是該表的主鍵，但卻是另外一個表的主鍵。維護兩表之間數(shù)據(jù)的一致性關系。,3

23、.6.2利用SQL Server Management Studio建立約束,1創(chuàng)建主鍵約束 （1）打開“表設計器”對話框。 （2）在表設計器對話框中，選定要設置主鍵的列或多個列組合（比如：“學號、課程號”，注意：選定多個列時，可按住Ctrl鍵進行選定），然后于選定的列上右擊，出現(xiàn)彈出菜單，執(zhí)行菜單中的【設置主鍵】命令即可，如圖3-7所示。,2創(chuàng)建唯一約束 （1）打開“表設計器”對話框，在表設計對話框中右擊任意字段，執(zhí)行彈出菜單中的【索引/鍵】命令，打開“索引/鍵”對話框。 （2）在索引/鍵對話框中，單擊“添加”按鈕，系統(tǒng)給出默認的唯一約束名稱：“IX_課程表”，顯示在“選定的主/唯一鍵或索引

24、”列表中，如圖3-8所示。,3.6.2利用SQL Server Management Studio建立約束,3創(chuàng)建檢查約束 （1）打開“表設計器”對話框，于表設計對話框中右擊任意字段，執(zhí)行彈出菜單中的【CHECK約束】命令，打開“CHECK約束”對話框。 （2）在CHECK約束對話框中，單擊“添加”按鈕，系統(tǒng)給出默認的CHECK約束名：“CK_學生表”，顯示在“選定的CHECK約束”列表如圖3-11所示。,3.6.2利用SQL Server Management Studio建立約束,（3）選擇CHECK約束名“CK_學生表”，可在其右側(cè)的“屬性”窗口中：通過“標識”項中的“名稱”屬性，修改約

25、束名稱；通過單擊“常規(guī)”項中的“表達式”屬性，在其對應的文本輸入框中，輸入約束條件表達式，或者通過其右側(cè)出現(xiàn)的“”按鈕，打開“CHECK約束表達式”對話框，在其中輸入約束條件表達式。如圖3-12所示。,3.6.2利用SQL Server Management Studio建立約束,4創(chuàng)建外鍵約束 （1）打開“表設計器”對話框，于表設計對話框中右擊任意字段，執(zhí)行彈出菜單中的【關系】命令，打開“外鍵關系”對話框。 （2）在外鍵關系對話框中，單擊“添加”按鈕，系統(tǒng)給出默認的外鍵約束名：“FK_教師教課表_教師教課表”，顯示在“選定的關系”列表中，如圖3-13所示。,3.6.2利用SQL Server

26、 Management Studio建立約束,3.6.3利用SQL命令建立約束,1設置數(shù)據(jù)表的主鍵 語法格式： ALTER TABLE 表名 ADD CONSTRAINT 約束名 PRIMARY KEY(字段) 2設置字段的默認值 語法格式： ALTER TABLE 表名 ADD CONSTRAINT 約束名 DEFAULT 表達式 FOR 字段 3設置字段只取唯一值，不取重復值 語法格式： ALTER TABLE 表名 ADD CONSTRAINT 約束名 UNIQUE ( 字段 ),3.6.3利用SQL命令建立約束,4設置字段取值限制的約束條件 語法格式： ALTER TABLE 表名 A

27、DD CONSTRAINT 約束名 CHECK ( 字段條件表達式 ) 5設置數(shù)據(jù)表的外鍵 語法格式： ALTER TABLE 表名 ADD CONSTRAINT 約束名 FOREIGN KEY（外鍵列, .） REFERENCES 對應的主鍵表 （該主鍵表的主鍵列, .）,3.6.4查看和刪除約束,1利用SQL Server Management Studio查看約束（略） 2利用SQL命令查看約束 語法格式： EXEC sp_helptext 約束名 3利用SQL Server Management Studio刪除約束（略） 4利用SQL命令刪除表的約束 語法格式： ALTER TABL

28、E 表名 DROP CONSTRAINT 約束名,3.7 為數(shù)據(jù)表創(chuàng)建IDENTITY列,1打開“表設計器”窗口。 2在表設計器窗口中，添加一列，列名（比如為ID），數(shù)據(jù)類型為int，然后，在其下面的“列屬性”窗口中，展開“標識規(guī)范”屬性，設置其“是標識”屬性值為“是”，修改其“標識種子”屬性值（比如100），其“標識增量”屬性值（比如1），這樣即可創(chuàng)建一個IDENTITY列。如圖3-16所示。,3.8更新數(shù)據(jù)表的內(nèi)容,3.8.1利用SQL Server Management Studio創(chuàng)建表內(nèi)容 1利用SQL Server Management Studio創(chuàng)建表和內(nèi)容（步驟略） 由學生參

29、照課本66頁完成。 2利用SQL Server Management Studio查看和更新表內(nèi)容（步驟略） 由學生參照課本66-69頁完成。,3.8.2 利用SQL命令更新表內(nèi)容,1向數(shù)據(jù)表中添加單個記錄 語法格式： Insert into 表名 (字段名列表) Values (字段值列表) 其中： “字段值列表”中的各個字段值表達式與“字段名列表”中的各個字段，從個數(shù)、類型和順序必須一一對應；各個字段名之間和各個字段值之間都是用逗號隔開。 若“(字段名列表)”缺省，指數(shù)據(jù)表的所有字段。,3.8.2 利用SQL命令更新表內(nèi)容,【例3-14】在上述數(shù)據(jù)庫Teaching的學生成績表sgrade

30、中添加3條記錄。 Use Student Go Insert into sgrade values（100011 , 孫陽 , 女 , 2009-9-1 , 計算機系 , 09網(wǎng)絡 , 89 , 56 , 76.8） Insert into sgrade values（100013 , 解曉東 , 男 , 2008-9-1 , 電氣系 , 08機電 , 98.7 , 67 , 76） Insert into sgrade values（100019 , 張大名 , 男 , 2007-9-1 , 機械系 , 07制造 , 80 , 50 , 70） Go,2修改數(shù)據(jù)表中的記錄內(nèi)容 語法格式： U

31、PDATE 表名 SET 字段=表達式，n WHERE 條件表達式 其中：“WHERE 條件表達式”缺省，指數(shù)據(jù)表的所有記錄。,3.8.2 利用SQL命令更新表內(nèi)容,【例3-15】在上述數(shù)據(jù)庫Student的學生成績表sgrade中，把中文、英文和數(shù)學都不及格的學生記錄的中文增加5分，英文提高10%，數(shù)學改為及格。 Use Student Go Update sgrade Set zw=zw+5 , yw=1.1*yw , sx=60 Where zw60 and yw60 and sx60 Go,3.8.2 利用SQL命令更新表內(nèi)容,3用一個（或多個）表的內(nèi)容來更新另一個表的內(nèi)容 語法格式： UPDATE 目的表 SET 字段=表達式 ，n From 源表，n Where 表之間的連接條件表達式 and 其他條件表達式 ,3.8.2 利用SQL命令更新表內(nèi)容,【例3-16】設數(shù)據(jù)庫Teaching中有如下三個數(shù)據(jù)表：,3.8.2 利用SQL命令更新表內(nèi)容,其中 各個字段名的意義為：xh(學號)，xm(姓名)， szx(所在系