數(shù)據(jù)庫(kù)中表的基本操作

1、第四章 數(shù)據(jù)庫(kù)中表的基本操作,（表）考試數(shù)據(jù)庫(kù),學(xué)生數(shù)據(jù)、試題數(shù)據(jù)、教師數(shù)據(jù)、試卷數(shù)據(jù)等要存儲(chǔ)起來(lái),學(xué)生關(guān)系模型、試題關(guān)系模型、教師關(guān)系模型、試卷關(guān)系模型,,第四章 數(shù)據(jù)庫(kù)中表的基本操作,4.1 SQL Server表的概述 4.2 數(shù)據(jù)庫(kù)中表的創(chuàng)建 4.3 表的修改與刪除 4.4 數(shù)據(jù)的添加與表的查看 4.5 案例應(yīng)用舉例,第四章 數(shù)據(jù)庫(kù)中表的基本操作,4.1 SQL Server表的概述 一、1、表的概念

2、 在SQL Server數(shù)據(jù)庫(kù)中，表定義為列的集合，與Excel電子表格相似，數(shù)據(jù)在表中是按行和列的格式組織排列的。每行代表唯一的一條記錄，而每列代表記錄中的一個(gè)域。,第四章 數(shù)據(jù)庫(kù)中表的基本操作,4.1 SQL Server表的概述 2、SQL Server表與關(guān)系模型的對(duì)應(yīng),第四章 數(shù)據(jù)庫(kù)中表的基本操作,4.1 SQL Server表的概述 3、表的設(shè)計(jì)過(guò)程中，在SQL Server創(chuàng)建表有如下限制： 1、每個(gè)數(shù)據(jù)

3、庫(kù)里最多有20億個(gè)表。 2、每個(gè)表上最多可以創(chuàng)建一個(gè)聚集索引和249個(gè)非聚集索引。 3、每個(gè)表最多可以配置1024個(gè)字段。 4、每條記錄最多可以占8060B，但不包括text字段和image字段。,第四章 數(shù)據(jù)庫(kù)中表的基本操作,4.1 SQL Server表的概述 二、基本的數(shù)據(jù)類型 在創(chuàng)建數(shù)據(jù)庫(kù)中的表的時(shí)候，牽涉到表體結(jié)構(gòu)，也就是說(shuō)牽涉到表中的字段數(shù)據(jù)的格式，所以為數(shù)據(jù)列選擇數(shù)據(jù)類型是一項(xiàng)非

4、常重要的工作。在定義了表列的數(shù)據(jù)類型以后，它將作為一項(xiàng)永久的特性被保留下來(lái)，而無(wú)法改變。所以精心選擇表列的數(shù)據(jù)類型是建立性能良好的表格的前提。 數(shù)據(jù)類型包含8大類，及幾種新的數(shù)據(jù)類型。,第四章 數(shù)據(jù)庫(kù)中表的基本操作,4.1 SQL Server表的概述 1、整型數(shù)據(jù)類型 能夠存儲(chǔ)精確的不帶小數(shù)點(diǎn)的數(shù)字值的方法，可以分為五大類。 bit 取值范圍只能是0或1，通常用作真假邏輯。 bigin

5、t 占用8個(gè)字節(jié) int 占用4個(gè)字節(jié) 占用字節(jié)數(shù)決定存儲(chǔ)范圍的大小 smallint 占用2個(gè)字節(jié) tiny 占用1個(gè)字節(jié),第四章 數(shù)據(jù)庫(kù)中表的基本操作,掌握基本的數(shù)據(jù)類型2、數(shù)字 數(shù)據(jù)類型 可以精確指定小數(shù)點(diǎn)兩邊的總位數(shù)(精度)和小數(shù)點(diǎn)右面的位數(shù)(刻度). 1numeric

6、 唯一的區(qū)別就是在主鍵為indentity列，只能定義numeric類型 decimal decimal(7 , 3) 9999.994 / 9999.995,,,第四章 數(shù)據(jù)庫(kù)中表的基本操作,掌握基本的數(shù)據(jù)類型3、浮點(diǎn)數(shù)據(jù)類型 能夠存儲(chǔ)范圍非常大的數(shù)字，一般應(yīng)用于科學(xué)計(jì)算或者統(tǒng)計(jì)計(jì)算等，不要求絕對(duì)精度的運(yùn)算場(chǎng)合。 real 存儲(chǔ)范圍占用4

7、個(gè)字節(jié)，精度可達(dá)7位。 float(n) 可以用n來(lái)確定科學(xué)計(jì)數(shù)法中的數(shù)據(jù)尾數(shù)的位數(shù)，范圍可 達(dá)53位。當(dāng)n的取值為1~24時(shí)，可達(dá)到的精度是7位；當(dāng) n的取值是25~53時(shí)，精度是15位，用8個(gè)字節(jié)來(lái)存儲(chǔ)。,第四章 數(shù)據(jù)庫(kù)中表的基本操作,掌握基本的數(shù)據(jù)類型4、字符數(shù)據(jù)類型 char 每個(gè)字符使用一個(gè)字節(jié)的存儲(chǔ)空間，使用固

8、定長(zhǎng)度來(lái)存儲(chǔ)字符，最長(zhǎng)可容納8000個(gè)。如果實(shí)際的字符串小于給定的最大長(zhǎng)度，則會(huì)以空格填充，如果實(shí)際的字符串大于給定的最大長(zhǎng)度，則截?cái)唷?varchar 可以存儲(chǔ)到8000個(gè)字符的變長(zhǎng)字符型數(shù)據(jù)，存儲(chǔ)為實(shí)際的字符串的長(zhǎng)度。 text 當(dāng)要存儲(chǔ)的字符的數(shù)據(jù)量非常龐大時(shí)，則使用text數(shù)據(jù)類型。他的最大長(zhǎng)度可以達(dá)到2G左右。,第四章 數(shù)據(jù)庫(kù)中表的基本操作,掌握基本的數(shù)據(jù)類型5、二進(jìn)制數(shù)據(jù)類型 二進(jìn)制數(shù)據(jù)在SQL中

9、用十六進(jìn)制進(jìn)行表示，前邊用0x標(biāo)記。binary 具有固定長(zhǎng)度,最大長(zhǎng)度可達(dá)8kb，當(dāng)輸入的實(shí)際數(shù)據(jù)小于定 義的長(zhǎng)度時(shí)，后面補(bǔ)0。varbinary 具有不固定的長(zhǎng)度。image 用于存儲(chǔ)字節(jié)數(shù)超過(guò)8kb的數(shù)據(jù)，例如圖象數(shù)據(jù)。,第四章 數(shù)據(jù)庫(kù)中表的基本操作,掌握基本的數(shù)據(jù)類型對(duì)text、image數(shù)據(jù)類型： 1、不可使用select命令中的order by、group by命令。

10、 2、不可作為索引文件的關(guān)鍵字。 3、也不可使用在where語(yǔ)句中，但是對(duì)于text數(shù)據(jù)可以 使用where語(yǔ)句中的模糊查詢。,第四章 數(shù)據(jù)庫(kù)中表的基本操作,掌握基本的數(shù)據(jù)類型6、貨幣數(shù)據(jù)類型 money 占用8個(gè)字節(jié)，前面4個(gè)表示貨幣的整數(shù)，后面4個(gè)表示小數(shù)。 small money 占用4個(gè)字節(jié)。7、日期/時(shí)間數(shù)據(jù)類型 datetime 表示范圍1753年1月1日~~9999年12月

11、31日，占用8個(gè)字節(jié)可 以精確到毫秒 small datetime 表示范圍1900年1月1日~~2079年6月6日，占用4個(gè)字節(jié) 可以精確到分。,第四章 數(shù)據(jù)庫(kù)中表的基本操作,掌握基本的數(shù)據(jù)類型8、雙字節(jié)數(shù)據(jù)類型 用于存儲(chǔ)要用兩個(gè)字節(jié)才能存儲(chǔ)的雙字節(jié)字符。 nchar(n) 固定長(zhǎng)度的雙字節(jié)，可以存儲(chǔ)1~4

12、000字符。 nvarchar(n) 可變長(zhǎng)度的雙字節(jié)。 ntext(n) 可存儲(chǔ)大量的字符。9、SQL-variant數(shù)據(jù)類型 可以存儲(chǔ)除text、ntext、image以外的各種類型數(shù)據(jù)，也不可以自身轉(zhuǎn)儲(chǔ)。,第四章 數(shù)據(jù)庫(kù)中表的基本操作,掌握基本的數(shù)據(jù)類型10、table數(shù)據(jù)類型 可以用來(lái)定義變量或者用于用戶自定義函數(shù)的返回值，一般用來(lái)存儲(chǔ)從數(shù)據(jù)庫(kù)中提取出來(lái)得結(jié)果。

13、 11、圖象、文本數(shù)據(jù)使用時(shí)涉及到的幾個(gè)函數(shù)12、用戶自定義數(shù)據(jù)類型 null建立語(yǔ)法：sp_addtype type_name , systemtype , not null刪除語(yǔ)法：sp_droptype type_name non

14、null,,第四章 數(shù)據(jù)庫(kù)中表的基本操作,表的創(chuàng)建 1.使用企業(yè)管理器創(chuàng)建表 的步驟： 1）定義表結(jié)構(gòu)：給表的每一列取字段名，并確定每一列的數(shù)據(jù)類型、數(shù)據(jù)長(zhǎng)度、列數(shù)據(jù)是否可以為空等。 2）設(shè)置約束：設(shè)置約束是為了限制該列輸入值的取值范圍，以保證輸入數(shù)據(jù)的正確性和一致性。 3）添加數(shù)據(jù)：表結(jié)構(gòu)建立完成之后，就可以向表中輸入數(shù)據(jù)了。,第四章 數(shù)據(jù)庫(kù)中表的基本操作,表的創(chuàng)建 2

15、.使用T-SQL語(yǔ)句創(chuàng)建表的基本語(yǔ)法： CREATE TABLE [ database_name.[ owner ] .| owner.] table_name ( { } | [ { PRIMARY KEY | UNIQUE } [ ,...n ] ) ::= { column_name data_type } [

16、COLLATE ] [ [ DEFAULT constant_expression ] | [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ] ] [ ] [ ...n ],第四章 數(shù)據(jù)庫(kù)中表的基本操作,表的創(chuàng)建 語(yǔ)法注釋： l database_name：指定創(chuàng)建的表所在的數(shù)

17、據(jù)庫(kù) l         owner：指定表的所有者 l         table_name：指定新建表的名稱 l         column_definition：表中字段的定義表達(dá)

18、式 l         column_name：表中的字段名 l         data_type：字段的數(shù)據(jù)類型 l         seed：自動(dòng)標(biāo)識(shí)的開(kāi)始值 l &#

19、160;       increment：自動(dòng)編號(hào)的步長(zhǎng) l         CREATE TABLE：創(chuàng)建表語(yǔ)句的關(guān)鍵字 l         PRIMARY KEY：主鍵約束 l  &#

20、160;      UNIQUE ：字段唯一性約束 l         DEFAULT：字段的默認(rèn)值約束 l         IDENTITY ：自動(dòng)編號(hào)標(biāo)識(shí),第四章 數(shù)據(jù)庫(kù)中表的基本操作,表的創(chuàng)建,例:創(chuàng)建帶有參照約束的

21、學(xué)生表，學(xué)生表的表結(jié)構(gòu)定義如下表所示?！皩W(xué)號(hào)”字段為學(xué)生表的主鍵，“班級(jí)代碼”字段為學(xué)生表的外鍵，它必須參照班級(jí)表中的“班級(jí)代碼”字段的值。,表的創(chuàng)建創(chuàng)建“專業(yè)”表的代碼如下： USE StudentGOCREATE TABLE 專業(yè)(專業(yè)代碼 char(2) not null,專業(yè)名稱 Varchar(20) not null,系部代碼 char(2) not null)GO創(chuàng)建“班級(jí)”表的代碼

22、如下：USE StudentGOCREATE TABLE 班級(jí)(班級(jí)代碼 char(9) constraint pk_bjdm primary key, 班級(jí)名稱 Varchar(20) not null, 專業(yè)代碼 char(2) not null, 系部代碼 char(2) not null, 備注 Varchar(50))GO,第四章 數(shù)據(jù)庫(kù)中表的基本操作,表的創(chuàng)建創(chuàng)建“學(xué)生”表的代碼如下： USE

23、STUDENTGOCREATE TABLE 學(xué)生 (學(xué)號(hào) char(12) CONSTRAINT pk_xh PRIMARY KEY, 姓名 char(8), 性別 char(2), 出生日期 datetime, 入學(xué)時(shí)間 datetime, 班級(jí)代碼 char(9) CONSTRAINT fk_bjdm REFERENC

24、ES 班級(jí)(班級(jí)代碼), 專業(yè)代碼 char(2), 系部代碼 char(2))GO,第四章 數(shù)據(jù)庫(kù)中表的基本操作,表的修改使用企業(yè)管理器修改表 1)打開(kāi)企業(yè)管理器，選擇相應(yīng)的數(shù)據(jù)庫(kù)，展開(kāi)表節(jié)點(diǎn)。 2)在企業(yè)管理器的明細(xì)窗格中，右擊要修改的表，在彈出的快捷菜單中選擇“設(shè)計(jì)表”命令，打開(kāi)表設(shè)計(jì)器。 3)在表設(shè)計(jì)器中修改各字段的定義，如字段名、字段類型

25、、字段長(zhǎng)度、是否為空等。 4)添加刪除字段。,第四章 數(shù)據(jù)庫(kù)中表的基本操作,表的修改語(yǔ)法：ALTER TABLE table_name { [ ALTER COLUMN column_name { new_data_type [ ( precision [ , scale ] ) ] [ COLLATE ] [ NULL | NOT NULL ] | {AD

26、D | DROP } ROWGUIDCOL } ] | ADD { [ ] } [ ,...n ] | DROP { [ CONSTRAINT ] constraint_name | COLUMN column } [ ,...n ] },語(yǔ)法： ::= { column_name data_type }

27、 [ [ DEFAULT constant_expression ] [ WITH VALUES ] | [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ] ] [ ROWGUIDCOL ] [ COLLATE ] [ ] [ ...n ],第四章 數(shù)據(jù)庫(kù)中表的基本操作,表的修改語(yǔ)法注釋： l

28、 ALTER COLUMN：修改表列屬性的子句 l         ADD：增加列或約束的子句 l         DROP COLUMN：刪除表列的子句 l         table

29、_name：需要修改表的表格名稱 l         column_name：希望增加的字段名 l         data_type：需要增加的字段的數(shù)據(jù)類型名 l  collation_name：排序規(guī)則名,例：表的修改在學(xué)生表中增加“家

30、庭住址”： USE Student GO  ALTER TABLE 學(xué)生 ADD 家庭住址 Varchar(30) nullGO將“家庭住址”字段屬性修改代碼如下： USE StudentGOALTER TABLE 學(xué)生 ALTER COLUMN 家庭住址 Varchar(50) nullGO,第四章 數(shù)據(jù)庫(kù)中表的基本操作,表的刪除使用企業(yè)管理器刪除表操作步驟如

31、下： 1)打開(kāi)企業(yè)管理器，選擇相應(yīng)的數(shù)據(jù)庫(kù)并展開(kāi)表節(jié)點(diǎn)。 2)在企業(yè)管理器的明細(xì)窗格中，右擊要?jiǎng)h除的表，在彈出的快捷菜單中選擇“刪除”命令，出現(xiàn) 除去對(duì)象對(duì)話框時(shí)，單擊“全部除去”按鈕即可刪除表。使用DROP TABLE語(yǔ)句刪除表USE STUDENTGODROP TABLE 專業(yè)GO,第四章 數(shù)據(jù)庫(kù)中表的基本操作,表的查看1)使用企業(yè)管理器查看 在企業(yè)管理器，右擊需要查看結(jié)構(gòu)的表，在

32、彈出的快捷菜單中選擇“屬性”命令，打開(kāi)表屬性對(duì)話框，。單擊“常規(guī)”和“全文索引”標(biāo)簽查看表信息。2)使用系統(tǒng)存儲(chǔ)過(guò)程sp_help查看 語(yǔ)法格式：[EXECUTE] sp_help [表名] 例如，查看“班級(jí)”表的結(jié)構(gòu)，可以使用下列語(yǔ)句： EXECUTE sp_help 班級(jí),第四章 數(shù)據(jù)庫(kù)中表的基本操作,查看表中的數(shù)據(jù)1）使用企業(yè)管理器查看表中的

33、數(shù)據(jù) 在企業(yè)管理器中，右擊需要查看數(shù)據(jù)的表，彈出快捷菜單，單擊“打開(kāi)表—返回所有的行”命令，打開(kāi)查詢?cè)O(shè)計(jì)器的結(jié)果窗口查看數(shù)據(jù). 2）使用查詢分析器查看表中的數(shù)據(jù) 打開(kāi)查詢分析器，在“對(duì)象瀏覽器”中選擇相應(yīng)的數(shù)據(jù)庫(kù)，展開(kāi)數(shù)據(jù)庫(kù)目錄和用戶表目錄，右擊要查看的表，從彈出快捷菜單中選擇“打開(kāi)”命令，就會(huì)打開(kāi)表窗口，在此窗口中用戶可以查看、修改和刪除表中的數(shù)據(jù)。,第四章 數(shù)據(jù)庫(kù)中表的基本操作,向表中的添

34、加數(shù)據(jù)1）在企業(yè)管理器中，展開(kāi)相關(guān)數(shù)據(jù)庫(kù)節(jié)點(diǎn)，單擊“表”圖標(biāo)，在明細(xì)窗格中右擊需要添加數(shù)據(jù)的表，彈出快捷菜單，單擊“打開(kāi)表—返回所有的行”命令，打開(kāi)查詢?cè)O(shè)計(jì)器的結(jié)果窗口. 2）查詢?cè)O(shè)計(jì)器的表中可以輸入新記錄，也可以修改和刪除已經(jīng)輸入的記錄。3）使用INSERT INTO 語(yǔ)句添加數(shù)據(jù),第四章 數(shù)據(jù)庫(kù)中表的基本操作,4、5案例應(yīng)用舉例use studentgocreate table 系部(系部代碼 char(2) con

35、straint pk_xbdm primary key,系部名稱 varchar(30) not null,系主任 char(8))go,第四章 數(shù)據(jù)庫(kù)中表的基本操作,use studentgocreate table 專業(yè)(專業(yè)代碼 char(4) constraint pk_zydm primary key,專業(yè)名稱 varchar(20) not null,系部代碼 char(2) constraint fp_

36、zyxbdm references 系部(系部代碼))go,第四章 數(shù)據(jù)庫(kù)中表的基本操作,use studentgocreate table 班級(jí)(班級(jí)代碼 char(9) constraint pk_bjdm primary key,班級(jí)名稱 varchar(20),專業(yè)代碼 char(4) constraint fp_bjzydm references 專業(yè)(專業(yè)代碼),系部代碼

37、char(2) constraint fp_bjxbdm references 系部(系部代碼),備注 varchar(5))go,第四章 數(shù)據(jù)庫(kù)中表的基本操作,USE STUDENTGOCREATE TABLE 學(xué)生 (學(xué)號(hào) char(12) CONSTRAINT pk_xh PRIMARY KEY, 姓名 char(8), 性別

38、char(2), 出生日期 datetime, 入學(xué)時(shí)間 datetime, 班級(jí)代碼 char(9) CONSTRAINT fk_xsbjdm REFERENCES 班級(jí)(班級(jí)代碼), 專業(yè)代碼 char(4) constraint fp_xszydm re

39、ferences 專業(yè)(專業(yè)代碼), 系部代碼 char(2) constraint fp_xsxbdm references 系部(系部代碼))GO,第四章 數(shù)據(jù)庫(kù)中表的基本操作,USE STUDENTGOCREATE TABLE 課程 (課程號(hào) char(4) CONSTRAINT pk_kc PRIMARY K

40、EY, 課程名 char(20) not null, 備注 varchar(50)) GO,第四章 數(shù)據(jù)庫(kù)中表的基本操作,USE STUDENTGOCREATE TABLE 教師 (教師編號(hào) char(12) CONSTRAINT pk_jsbh PRIMARY KEY, 姓名 char(8) not null, 性別 char(2), 出生日期 dat

41、etime, 學(xué)歷 char(10), 職務(wù) char(10), 職稱 char(10), 系部代碼 char(2) constraint fp_jsxbdm references 系部(系部代碼), 專業(yè) char(2), 備注 varchar(50)) GO,第四章 數(shù)據(jù)庫(kù)中表的基本操作,USE STUDENTGOCREATE TABLE 教學(xué)計(jì)劃

42、 (課程號(hào) char(4) CONSTRAINT pk_jxjhkch references 課程(課程號(hào)), 專業(yè)代碼 char(4) constraint pk_jxjhzydm references 專業(yè)(專業(yè)代碼), 專業(yè)學(xué)級(jí) char(4) not null, 課程類型 char(8), 開(kāi)學(xué)學(xué)期 tinyint, 學(xué)分 tinyint, 起始周 tinyin

43、t, 結(jié)束周 tinyint, 教材編號(hào) char(6), 備注 char(20)) GO,第四章 數(shù)據(jù)庫(kù)中表的基本操作,USE STUDENTGOCREATE TABLE 教師任課 (教師編號(hào) char(12) CONSTRAINT fk_jsrkjsbh references 教師(教師編號(hào)), 課程號(hào) char(4) CONSTRAINT pk_jsrkk

44、ch references 課程(課程號(hào)), 專業(yè)代碼 char(4), 專業(yè)學(xué)級(jí) char(4), 學(xué)年 char(4), 學(xué)期 tinyint, 學(xué)生數(shù) smallint, 學(xué)時(shí)數(shù) smallint, 酬金 smallint, 起始周 tinyint, 結(jié)束周 tinyint, ) GO,第四章 數(shù)據(jù)庫(kù)中表的基本操作,

45、USE STUDENTGOCREATE TABLE 課程注冊(cè) (注冊(cè)號(hào) bigint identity(010000000,1) not for replication constraint pk_zch primary key, 學(xué)號(hào) char(12) constraint fk_kczcxh references 學(xué)生(學(xué)號(hào)), 課程號(hào) char(4) CON

46、STRAINT fk_kczckch references 課程(課程號(hào)), 教師編號(hào) char(12) CONSTRAINT fk_kczcjsbh references 教師(教師編號(hào)), 專業(yè)代碼 char(4) constraint fk_kczczydm references 專業(yè)(專業(yè)代碼), 專業(yè)學(xué)級(jí) char(4), 選課類型 char(4), 學(xué)年 char(4),