數(shù)據(jù)表的創(chuàng)建和管理.ppt

第三章 基本表的創(chuàng)建與管理,學(xué)習(xí)要點：,表的概念 表的創(chuàng)建、修改和刪除 添加、更新與刪除表中的數(shù)據(jù) 數(shù)據(jù)完整性的概念及實施方法,3.1 表的概念,表是數(shù)據(jù)的集合，是用來存儲數(shù)據(jù)和操作數(shù)據(jù)的邏輯結(jié)構(gòu)。 表是由行和列組成的，行被稱為記錄，是組織數(shù)據(jù)的單位；列被稱為字段，每一列表示記錄的一個屬性。 在特定表中，列名必須是唯一的，但相同的列名可以在數(shù)據(jù)庫中的不同表中使用。 SQL Server中的每個表中最多允許有1024列，每行最多允許有8060字節(jié)的用戶數(shù)據(jù)。行和列的次序是任意的。,3.2 數(shù)據(jù)類型,第一大類:整數(shù)數(shù)據(jù) bit:bit數(shù)據(jù)類型代表0,1或NULL,就是表true,false. 占用1byte. int:以4個字節(jié)來存儲正負(fù)數(shù).可存儲范圍為:- （-2147483648至2147483647） . smallint:以2個字節(jié)來存儲正負(fù)數(shù).存儲范圍為:- （-32768至32767） tinyint: 是最小的整數(shù)類型,僅用1字節(jié),范圍:0至 255 bigint：以8個字節(jié)存儲，是SQL Server 2000引入的全新的數(shù)據(jù)類型，可以存儲范圍：（-263到263-1）,第二大類:精確數(shù)值數(shù)據(jù) numeric:表示的數(shù)字可以達到38位,存儲數(shù)據(jù)時所用的字節(jié)數(shù)目會隨著使用權(quán)用位數(shù)的多少變化.格式寫為numeric( p，s) 例如：numeric(10，4)，表示共有10位數(shù)，其中整數(shù)6位，小數(shù)4位。 decimal:和numeric類似 。 區(qū)別：numeric類型的列可以帶有IDENTITY關(guān)鍵字（唯一標(biāo)識符）。,第三大類:近似浮點數(shù)值數(shù)據(jù) float:用8個字節(jié)來存儲數(shù)據(jù).最多可為53位.范圍為:-1.79E+308至1.79E+308. real:位數(shù)為24,用4個字節(jié),數(shù)字范圍:-3.04E+38至3.04E+38 第四大類:日期時間數(shù)據(jù) datatime:表示時間范圍可以表示從1753/1/1至9999/12/31,時間可以表示到3.33/1000秒.使用8個字節(jié). smalldatetime:表示時間范圍可以表示從1900/1/1至2079/12/31.使用4個字節(jié).,第五大類:字符串?dāng)?shù)據(jù) char（n）:長度是設(shè)定的,最短為1字節(jié),最長為8000個字節(jié).不足的長度會用空白補上. 例如：char（10） Varchar（n）:變長字符型數(shù)據(jù),最短為1字節(jié),最長為8000個字節(jié),尾部的空白會去掉. text:長專門用于存儲數(shù)量龐大的變長字符數(shù)據(jù)，,最長可以存放2G（231-1個字符）的數(shù)據(jù).,第六大類:Unincode字符串?dāng)?shù)據(jù) nchar:長度是設(shè)定的,最短為1字節(jié),最長為4000個字節(jié).不足的長度會用空白補上.儲存雙字節(jié)字符,nchar(m)中的m代表的含義是用來定義字符數(shù)據(jù)的最大長度. nvarchar:可變長度,最短為1字節(jié),最長為4000個字節(jié).尾部的空白會去掉.儲存一個字符需要2個字節(jié). ntext:可變長度,最短為1字節(jié),最長為230-1個unicode數(shù)據(jù).尾部的空白會去掉,儲存一個字符需要2個字節(jié).,第七大類:貨幣數(shù)據(jù)類型 money:記錄金額范圍為:-92233720368577.5808至92233720368577.5807.需要8 個字節(jié). smallmoney:記錄金額范圍為:-214748.3648至214748.36487.需要4個字節(jié). 第八大類:標(biāo)記數(shù)據(jù) timestamp:該數(shù)據(jù)類型在每一個表中是唯一的!當(dāng)表中的一個記錄更改時,該記錄的timestamp字段會自動更新.,第九大類:二進制碼字符串?dāng)?shù)據(jù) binary:固定長度的二進制碼字符串字段,最短為1,最長為8000. varbinary:與binary差異為數(shù)據(jù)尾部是00時,varbinary會將其去掉 image:為可變長度的二進制碼字符串,最長2G.,3.3 表結(jié)構(gòu)的建立和刪除,1、表的建立 表是包含數(shù)據(jù)庫中所有數(shù)據(jù)的數(shù)據(jù)庫對象，用來存儲各種各樣的信息。 在SQL Server 2005中，一個數(shù)據(jù)庫中最多可以創(chuàng)建20億個表，用戶創(chuàng)建數(shù)據(jù)庫表時，最多可以定義1024列。在同一數(shù)據(jù)庫的不同表中，可以有相同的字段，但在同一個表中不允許有相同的字段，而且每個字段都要求數(shù)據(jù)類型相同。,一般的SQL建表語句形式 CREATE TABLE 表名 表約束 (列名1 數(shù)據(jù)類型 缺省值1，列約束1 列名2 數(shù)據(jù)類型 缺省值2，列約束2 列名n 數(shù)據(jù)類型 缺省值n，列約束n）,例3-1 創(chuàng)建三張表，表結(jié)構(gòu)如下：,Student表結(jié)構(gòu),Course表結(jié)構(gòu),Sc表結(jié)構(gòu),CREATE TABLE Student (Sno CHAR(7) PRIMARY KEY， Sname CHAR(10) NOT NULL, Ssex CHAR(2) DEFAULT 男, Sage TINYINT, Sdept CHAR(20), ),CREATE TABLE Course (Cno CHAR(10) PRIMARY KEY， Cname CHAR(20) NOT NULL, Ccredit TINYINT DEFAULT 3, Semester TINYINT, ),CREATE TABLE Sc (Sno CHAR(7) ， Cno CHAR(10), Grade FLOAT， PRIMARY KEY（Sno，Cno）， FOREIGN KEY （Sno）REFERENCES Student（Sno）， FOREIGN KEY （Cno）REFERENCES Course（Cno）,2、表的刪除 刪除表的命令一般形式 DROP TABLE 表名 例3-2 刪除教師表 DROP TABLE teacher,3.4 表結(jié)構(gòu)的擴充和修改 1 、修改表的命令一般形式 ALTER TABLE 表名 ADD 列名 數(shù)據(jù)類型 NULL/NOT NULL |ALTER COLUMN 列名 數(shù)據(jù)類型 NULL|NOT NULL |DROP COLUMN 列名 ,例3-3 在student中增加住址列 ALTER TABLE student ADD address CHAR（50） 例3-4 修改student表中的sdept字段，將數(shù)據(jù)類型長度為30 ALTER TABLE student ALTER COLUMN sdept char(30) 例3-5 把Student表中的Sdept列刪除。 ALTER TABLE Student DROP COLUMN Sdept,2添加主關(guān)鍵字 ALTER TABLE 表名 ADD CONSTRAINT 約束名 PRIMARY KEY (主鍵列) 3、刪除主關(guān)鍵字 ALTER TABLE 表名 DROP CONSTRAINT 約束名 3.補充定義外來關(guān)鍵字 ALTER TABLE 表名-1 ADD CONSTRAINT 約束名 FOREIGN KEY (外來關(guān)鍵字名) REFERENCES (表名-2) 5刪除外來關(guān)鍵字 ALTER TABLE 表名 DROP 外來關(guān)鍵字名,例3-6 補充定義student表的主關(guān)鍵字。 ALTER TABLE Student ADD CONSTRAINT pk_student PRIMARY KEY (Sno) 例3-7、刪除course表中的主關(guān)鍵字。 ALTER TABLE course DROP pk_course 例3-8、修改sc表，在sno字段上創(chuàng)建外鍵約束。 ALTER TABLE sc ADD CONSTRAINT fk_sc_student FOREIGN KEY (sno) REFERENCES student(sno),例3-9、刪除sc表中sno字段上的外鍵約束 ALTER TABLE sc DROP fk_sc_student 例3-10、向Student表增加“入學(xué)時間”列，其數(shù)據(jù)類型為日期型。 ALTER TABLE Student ADD Scomedate DATETIME 例3-11、將年齡的數(shù)據(jù)類型改為半字長整數(shù)。 ALTER TABLE Student ALTER COLUMN Sage SMALLINT,3.5 數(shù)據(jù)完整性,數(shù)據(jù)完整性是指存儲在數(shù)據(jù)庫中的數(shù)據(jù)的一致性和準(zhǔn)確性。數(shù)據(jù)完整性技術(shù)是衡量數(shù)據(jù)庫質(zhì)量好壞的重要標(biāo)準(zhǔn)。在Microsoft SQL Server系統(tǒng)中，提供了一系列實現(xiàn)數(shù)據(jù)完整性的方法，這些方法主要包括主鍵約束、外鍵約束、非空約束、惟一約束、默認(rèn)值約束、檢查約束以及規(guī)則和默認(rèn)對象。,分類：,實體完整性 參照完整性 域完整性 實體完整性又稱為行完整性。這里的實體是指表中的記錄，一個實體就是表的一條記錄。實體完整性要求在表中不能存在完全相同的記錄，而且每條記錄都要具有一個非空且不重復(fù)的主鍵值。,參照完整性：一般指多個實體或關(guān)系表之間的關(guān)聯(lián)關(guān)系，在關(guān)系數(shù)據(jù)庫中用外碼（Foreign key）來實現(xiàn)參照完整性。 域完整性也稱用戶定義的完整性或語義完整性。用戶定義的完整性實際上就是指明關(guān)系中屬性的取值范圍，也就是屬性的域，即限制關(guān)系中屬性的取值類型及取值范圍，防止屬性的值與應(yīng)用語義矛盾。,約束定義了必須遵循的用于維護數(shù)據(jù)一致性和正確性的規(guī)則，是強制實現(xiàn)數(shù)據(jù)完整性的途徑。 約束分類： 主鍵約束 外鍵約束 唯一值約束 檢查約束 默認(rèn)約束,1、唯一性約束（UNIQUE）,UNIQUE 用來限制不受主鍵約束的列上的數(shù)據(jù)的唯一性。 一個表可以放置多個UNIQUE約束 語法： CONSTRAINT constraint_name UNIQUE (列名),例3-12 為student表添加身份證號列id，并為id列添加唯一值約束。 ALTER TABLE student ADD id BIGINT CONSTRAINT unq_id UNIQUE 注：UNIQUE約束列允許有NULL值，但最多只能有一個NULL值，否則認(rèn)為重復(fù)。 例3-13 為course表的cname列添加唯一值約束。 ALTER TABLE course ADD CONSTRAINT unq_cname UNIQUE(cname),2、檢查約束（CHECK）,CHECK 約束用來指定某列的可取值范圍。通過限制輸入值強制控制域的完整性。 語法： CONSTRAINT constraint_name CHECK (檢查條件) 例3-14 限制student表中的ssex列只能添入值為男或女。 ALTER TABLE student ADD CONSTRAINT check_ssex CHECK (ssex=男 or ssex=女),3、默認(rèn)約束（DEFAULT）,DEFAULT約束用于給表中指定列賦予一個常量值（默認(rèn)值）。 語法： CONSTRAINT 約束名 DEFAULT 默認(rèn)值 FOR 列名 例3-15 為student中的sage列添加默認(rèn)值“19”。 ALTER TABLE student ADD CONSTRAINT default_sage DEFAULT 19 FOR sage,上機實踐（獨立完成實驗）,實驗?zāi)康模?本章實驗主要目的是學(xué)習(xí)正確創(chuàng)建表；掌握修改表結(jié)構(gòu)的方法；正確刪除表。 實驗內(nèi)容： 自定義數(shù)據(jù)類型。 分別用企業(yè)管理器和查詢分析器創(chuàng)建表。 分別用企業(yè)管理器和查詢分析器修改表。 分別用企業(yè)管理器和查詢分析器刪除表。,實驗題目： 題目一：在學(xué)生數(shù)據(jù)庫student中創(chuàng)建學(xué)生個人信息數(shù)據(jù)表studentinfo 題目要求分析：studentinfo數(shù)據(jù)表中包含以下信息：學(xué)號、姓名、性別、年齡、班級、地址以及聯(lián)系方式等，如下表所示。,題目二：修改個人信息數(shù)據(jù)表studentinfo，完成如下操作： 刪除year_old字段。 向該表中添加一列，列名為email，數(shù)據(jù)類型為char，長度40，允許空。 題目要求分析：根據(jù)題目一創(chuàng)建的個人信息數(shù)據(jù)表studentinfo，可以使用企業(yè)管理器和Transact-SQL語句來實現(xiàn)。,題目三：刪除個人信息數(shù)據(jù)表studentinfo。