1、表的創建與修改表的創建與修改, ,完整性約束完整性約束信息工程學院信息工程學院SQLSQL的產生和發展的產生和發展 最早的版本有IBM提出Structured Query Language 最新的版本是SQL-99 主流關系數據庫產品采用的是在SQL-92標準的基礎上自己擴展而來 SQL SERVER的語句叫Transact SQLTransact SQL Oracle的語句叫PL/SQL MySQL的SQL語句與其他的也有所不同SQLSQL的特點的特點 一體化一體化 集DDL、DML、DCL功能于一體。 高度非過程化高度非過程化 只需“做什么”，無須“怎么做”。 簡潔簡潔 接近自然語言 創建

2、表：CREATE TABLE 以多種方式使用以多種方式使用 命令式 嵌入到程序設計語言中SQLSQL的組成部分的組成部分 數據定義（數據定義（DDLDDL） 定義、刪除、修改數據庫（Database） 定義、刪除、修改表(Table)、視圖（View） 定義、刪除、修改索引（Index） 數據操縱（數據操縱（DMLDML） 數據查詢 數據增、刪、改 數據控制（數據控制（DCLDCL） 用戶訪問權限的授予、收回 數據定義數據定義數據庫定義數據庫定義 創建數據庫 CREATE DATABASE CREATE DATABASE ;CREATE DATABASECREATE DATABASE XSCJ

3、; 打開數據庫 USEUSE ;USEUSE XSCJ; 刪除數據庫 DROP DATABASE DROP DATABASE ;DROP DATABASE DROP DATABASE XSCJ; 一個數據庫在打開的時候是不能刪除的SQLSQL語句創建數據庫語句創建數據庫 以下語句創建了一個名為TestDB的數據庫。創建數據庫的相關信息大家都可以從代碼中讀出來。CREATE DATABASE TestDB ON (NAME = TestDB, FILENAME = D:TestDB.mdf, SIZE = 20 MB, FILEGROWTH = 0)LOG ON (NAME = TestDB_L

4、og, FILENAME = D:TestDB_Log.ldf, SIZE = 5 MB, FILEGROWTH = 0)SQL語句大小寫不敏感，用大寫還是小寫都能識別！語句大小寫不敏感，用大寫還是小寫都能識別！建表示例：學生信息表建表示例：學生信息表創建這張表的步驟創建這張表的步驟確定每一列的數據類型確定每一列的數據類型創建無數據的空表創建無數據的空表添加數據添加數據學號姓名性別高考分數班級身份證號照片指紋9912101 李榮浩 男 388.513軟工11234567890123456781312102 上官劉晨 男 44213信管11000000000000000020512103 陸盈盈

5、女 631.513電商1設我們現在要在數據庫中創建下面的學生信息表，步驟是怎樣的呢？假設我們現在要在數據庫中創建下面的學生信息表，步驟是怎樣的呢？數據庫的數據類型數據庫的數據類型 數值型 字符型 日期型 貨幣型 二進制型 數據類型在不同數據庫產品中有差異，這里是指數據類型在不同數據庫產品中有差異，這里是指SQL SQL SERVERSERVER中的數據類型中的數據類型確定數據類型確定數據類型學生信息表列名列名數據類型數據類型學號姓名性別高考分數班級身份證號照片指紋學號姓名性別高考分數班級身份證號照片指紋9912101 李榮浩 男 388.599軟工112

6、34567890123456781312102 上官劉晨 男 44213信管11000000000000000020512103 陸盈盈女 631.507電商1定數據類型確定數據類型學號姓名性別高考分數班級身份證號照片指紋9912101 李榮浩 男 388.599軟工11234567890123456781312102 上官劉晨 男 44213信管11000000000000000020512103 陸盈盈女 631.507電商1名列名數據類型數據類型理由理由學號Char(7)第一個數字可能是0，且數據的長度固定不

7、變姓名nvarchar(8) 可以有多種選擇性別Nchar(2)固定長高考分數Decimal(5,1) 使用Numeric(5,1)是一模一樣的班級Nvarchar(10) Unicode型所有字符都是2個字節身份證號Char(18)照片Varchar(50) 一般保存圖片在服務器中的路徑和文件名數值型數值型準確型準確型整數Int：4字節 Smallint：2字節 tinyint ：1字節小數 （建議使用準確小數型）（建議使用準確小數型）Numeric（p,q）或Decimal（p,q)近似型近似型（不建議使用）（不建議使用） Float：8字節 Real：4字節年齡選擇什么類型? Tinyi

8、nt夠不夠？ 價格？ 3687.88 99.7 131452.99這組數據，什么類型合適？超過bigint的定義范圍的整數該怎么解決？詳細介紹見書上相關表格，這里只介紹常用的、需注意的字符型字符型 CHAR, VARCHAR, TEXT nCHAR, nVARCHAR, nTEXT1、姓名 CHAR( 10 ) 柴靜 李榮浩 姓名2 varchar（10） 柴靜 李榮浩 姓名 和 姓名2 這兩列有什么區別？2、微博內容，需要記錄日文，英文，中文，應該用什么數據 類型？3、中國大陸的手機號碼，用什么類型？ 字符串類型的數據兩端用單引號括起來，如how are you,1234等都是字符串的數據

9、請注意數據庫中字符和字符串都是用單引號 12313，2.343是數值型數據 數字型字符與數值用單引號來區別字符型字符型 日期時間型日期時間型 Datetime：8字節，年月日時分秒毫秒(例：2001/08/03 10:30:00.000 ) SmallDateTime：4字節，年月日時分 (例： 2001/08/03 10:30:00 ) 日期、時間的輸入格式試列出實際生活中需要精確到毫秒和不需要精確到毫秒的時間數據。日期時間類型日期時間類型 l SQL Server的日期時間數據類型是將日期和時間合起來存儲。l 日期的格式： Oct 25 2001 2001-10-25 2001/10/25

10、 20011025注：日期型數據使用單引號括起來注：日期型數據使用單引號括起來貨幣類型貨幣類型 貨幣數據類型表示貨幣值。貨幣數據存儲的精確度固定為四位小數。 SQL92或SQL99沒有對應的貨幣類型，Oracle等數據庫產品中也沒有貨幣類型。 建議使用decimal類型代替二進制類型二進制類型 Binary(n)：固定長度，n = 8000 Varbinary(n)：可變長度，n 8000 注：n為二進制數據的字節數 image：大容量、可變長二進制字符數據，可用于存儲文件。網站中上傳的圖片一般情況下是如何保存的呢？學生信息表學生信息表創建這張表的步驟創建這張表的步驟1.1.確定每一列的數據類

11、型確定每一列的數據類型2.2.創建無數據的空表創建無數據的空表3.3.添加數據添加數據學號姓名性別高考分數班級身份證號照片指紋9912101 李榮浩 男 388.513軟工11234567890123456781312102 上官劉晨 男 44213信管11000000000000000020512103 陸盈盈女 631.513電商1QLSQL語句創建表語句創建表CREATE TABLE CREATE TABLE ( null | not null , )；CREATE TABLE CREATE TABLE students( (sno char(7)

12、 not null, , sname nvarchar(8) not null, , ssex nchar(2) not null, , score decimal(4,1) , ,class nvarchar(10) not null , ,sfzh char(18) not null , ,tp varchar(50) , ,zw image) )學生表結構學生表結構列名列名數據類型數據類型是否允許空是否允許空學號Char(7)不允許空姓名nvarchar(8)不允許空性別Nchar(2)不允許空高考分數Decimal(4,1)班級Nvarchar(10)不允許空身份證號Char(18)不

13、允許空照片Varchar(50)指紋image默認默認null，可以不寫，可以不寫課堂練習課堂練習1 1：創建讀者表：創建讀者表參考答案：CREATE TABLE Reader (Reader_ID CHAR(6) not null,name NVARCHAR(8) not null,sex NVARCHAR(2) not null,birthdate SMALLDATETIME)reader列名列名數據類型數據類型是否允許空是否允許空描述描述reader_IDreader_ID Char(6)否讀者編號rnamernameNvarchar(8)否讀者姓名sexsexNvarchar(2)否性

14、別birthdatebirthdate smalldatetime出生日期設置自動編號設置自動編號創建借閱表CREATE TABLE Borrow(Xh int not null identity(1,1),bID CHAR(10) not null,RID CHAR(6) not null,Borrowdate DATETIME not null)列名列名數據類型數據類型是否允許空是否允許空描述描述xhintintNot null序號，要求自動增長編號bIDChar(10)否圖書編號rIDChar(6)否讀者編號borrowdatedatetime否借閱日期borrowSSMSSSMS圖形工

15、具表設計器建表創建數據表圖形工具表設計器建表創建數據表選中要建表的數據庫右鍵新建表創建Books表修改表也是同樣的兩種方式修改表也是同樣的兩種方式 用SSMS圖形工具的表設計器直接修改，方法同剛剛講的建表(同學們自己動手試試，此處不再講解)修改表時可能系統不讓你修改，思考下為什么會有這樣的設置？修改表時可能系統不讓你修改，思考下為什么會有這樣的設置？ 用SQL 語句中的ALTER TABLE語句修改表此處默認是打鉤的，也就此處默認是打鉤的，也就是不讓你修改表的結構，是不讓你修改表的結構，要把勾去掉才能修改表要把勾去掉才能修改表基本表的修改基本表的修改假設我們剛剛創建的假設我們剛剛創建的stud

16、entsstudents表要進行如下修改：表要進行如下修改： 增加一列：聯系電話；增加一列：聯系電話； 學號長度需要調整，學號長度需要調整，7 7位改為位改為1010位；位； 刪除指紋列。刪除指紋列。SQL語言使用ALTER TABLE命令可以實現： 增加新列：ADD 修改原有的列定義： ALTER COLUMN 刪除列： DROP COLUMN 基本表的修改的語法基本表的修改的語法語法格式為： ALTER TABLE ADD ; -添加新列 ALTER TABLE ALTER COLUMN ; -修改已有列定義 ALTER TABLE DROP COLUMN ； -刪除列基本表的修改基本表的

17、修改 - - - -學生表中增加一列學生表中增加一列“手機號碼手機號碼”ALTER TABLE 學生表ADD 手機號碼 char(11);- - -學生表中學生表中“學號學號”列長度改為列長度改為1010位位ALTER TABLE 學生表Alter column 學號 char(10);- - -學生表中刪除學生表中刪除“指紋指紋”列列ALTER TABLE 學生表DROP COLUMN 指紋;課堂練習課堂練習2 2：修改表：修改表 Reader表中添加兩列：職業profession、聯系電話phone ALTER TABLE Reader ADD profession CHAR(20), p

18、hone char(11) 將Reader表中的profession列修改為char(30)，聯系電話改為char(13) ALTER TABLE Reader ALTER COLUMN profession CHAR(30) ALTER TABLE Reader ALTER COLUMN phone CHAR(13) 刪除Reader表中的profession列和phone列 ALTER TABLE Reader DROP COLUMN profession ,phone*關于修改表的思考？關于修改表的思考？ Students表原先“學號”的數據類型為Char(10)，現在改為char(7)

19、是否可以？如何驗證？ 若students表中已有數據，以下語句能否成功執行？ ALTER TABLE students ADD 手機號碼 char(11) not null;注意：表中有數據時，表中有數據時， 增加的列不能增加的列不能not nullnot null 修改的長度不能小于原數據的長度修改的長度不能小于原數據的長度 數據類型不能轉換的不允許修改數據類型不能轉換的不允許修改刪除表刪除表 DROP TABLE , 刪除students表（學生信息表） DROP TABLE students 刪除book、reader、borrow表(一次刪除多個表) drop table book,r

20、eader,borrow數據完整性數據完整性學生表學生表學號學號姓名姓名年齡年齡身份證身份證系別系別13011301nullNull李榮浩李榮浩劉強東劉強東馬云馬云陸盈盈陸盈盈18null1718330227199303174026330105199308300017330105199306061324nullX01X02X03X04系別號系別號系名系名X01計算機系計算機系X02信管系信管系X03軟件系軟件系通過設置約束約束，保證保證數據庫中的數據是完整、一完整、一致、正確致、正確的系別信息表系別信息表關系完整性約束關系完整性約束 實體完整性實體完整性 主鍵（非空且不重復非空且不重復） 參照

21、完整性參照完整性 外鍵（表示表間列值的參照關系表示表間列值的參照關系） 用戶自定義完整性用戶自定義完整性 非空：不允許空值不允許空值 唯一：不可重復，允許空值不可重復，允許空值 檢查：設置該列值的范圍設置該列值的范圍 缺省值（默認）：當新輸入的數據沒指定值時，當新輸入的數據沒指定值時，就將默認值存儲在數據表中就將默認值存儲在數據表中主鍵約束主鍵約束（PRIMARY KEY） 主鍵（主鍵（PRIMARY KEYPRIMARY KEY）約束）約束 PRIMARY KEY約束用于定義基本表的主鍵，它是惟一確定表中每一條記錄的標識符，又稱主碼。其值不能為NULL，也不能重復，以此來保證實體的完整性。

22、下表中（學號）為主碼，則學號不能取空值，亦不得重復。學學號號姓姓名名性性別別身身份份證證系系別別13011301nullNull李李榮榮浩浩劉劉強強東東馬馬云云陸陸盈盈盈盈男男男男女女女女330227199303174026330105199308300017330105199306061324nullX01X02X03X04學學號號姓姓名名性性別別身身份份證證系系別別13011301nullNull李李榮榮浩浩劉劉強強東東馬馬云云陸陸盈盈盈盈男男男男女女女女330227199303174026330105199308300017330105199306061324nullX01X02X03X

23、04方法 （1） SQL Server管理平臺操創建選擇多個字段共同做為主鍵需要選擇多個字段共同做為主鍵需要同時選中這幾個字段，如圖所示同時選中這幾個字段，如圖所示創建主鍵創建主鍵方法二：使用方法二：使用SQLSQL語句創建主鍵語句創建主鍵(1)建表時在字段上創建create table students(sno char（7） primary key,)(2)建表時在末尾非標準創建create table students( (sno char(7) not null , zw image，primary key (sno) )(3)建表時在末尾標準創建語法：CONSTRAINT 主鍵名 P

24、RIMARY KEY （列名）上面的最后一句改成： CONSTRAINT PK_Students primary key (sno) 關于主鍵名，可以由創建者自己取，一般系統習慣取名為PK_表名此時設為主鍵的字段可不必再寫此時設為主鍵的字段可不必再寫not null，因為隱含了不為空的信息，因為隱含了不為空的信息constraint knstreint n.強制, 約束組合主鍵只能用后面兩種方法定義組合主鍵只能用后面兩種方法定義唯一性約束唯一性約束(unique)(unique) 惟一性約束用于指定一個或者多個列的組合值具有惟一性，以防止在列中輸入重復的值 唯一性約束與主鍵約束的區別：主鍵約束

25、一個表只能有一個，而唯一性約束可創建多個、主鍵不能為空而唯一性約束可以。 學生表中，學號是主鍵，身份證號就可以定義為惟一性約束學學號號姓姓名名性性別別身身份份證證系系別別1301130213041303李李榮榮浩浩劉劉強強東東馬馬云云陸陸盈盈盈盈男男男男F女女330227199303174026330227199303174026330105199306061324nullX01X02X03null學學號號姓姓名名性性別別身身份份證證系系別別1301130213041303李李榮榮浩浩劉劉強強東東馬馬云云陸陸盈盈盈盈男男男男F女女33022719930317402633022719930317

26、4026330105199306061324nullX01X02X03null創建唯一性約束創建唯一性約束方法 （1） SQL Server管理平臺操創建選中某個表-右鍵設計選擇列- 索引/鍵如果創建了主如果創建了主鍵，在這里也鍵，在這里也可以看到可以看到方法二：使用方法二：使用SQLSQL語句創建唯一性約束語句創建唯一性約束(1)建表時在字段上創建create table students(sfzh char（18） unique,)(2)建表時在末尾非標準創建create table students( (zw image，primary key (sno)，unique (sfzh) )

27、(3)建表時在末尾標準創建語法：CONSTRAINT 唯一性約束名 UNIQUE （列名）上面的最后一句改成： CONSTRAINT uq_Students unique (sfzh) 關于唯一性約束名，可以由創建者自己取，一般系統習慣取名為IX_表名（因為跟創建索引是在一個地方）此時設為唯一性約束的字段如果不可為空，一定要指明此時設為唯一性約束的字段如果不可為空，一定要指明not null檢查約束（檢查約束（checkcheck） 檢查約束對輸入列或者整個表中的值設置檢查條件，以限制輸入值，保證數據庫數據的完整性。 比如：要求學生年齡的約束為15-30歲的話，一旦你設置好后， 輸入8或65就

28、輸不進去了！如果已經輸入了的話，它就會顯示錯誤。再比如：學生信息表中設置了性別的檢查約束只能是“男”或者“女”，就不能輸入其他的信息。學學號號姓姓名名性性別別身身份份證證系系別別1301130213041303李李榮榮浩浩劉劉強強東東馬馬云云陸陸盈盈盈盈男男男男F女女330227199303174026330227199303174026330105199306061324nullX01X02X03null學學號號姓姓名名性性別別身身份份證證系系別別1301130213041303李李榮榮浩浩劉劉強強東東馬馬云云陸陸盈盈盈盈男男男男F女女3302271993031740263302271993

29、03174026330105199306061324nullX01X02X03null創建檢查約束創建檢查約束方法 （1） SQL Server管理平臺操創建選中某列-右鍵-CHECK約束方法二：使用方法二：使用SQLSQL語句創建檢查約束語句創建檢查約束(1)建表時在字段上創建create table students(ssex nchar（2） check(ssex=男or ssex=女),)(2)建表時在末尾非標準創建create table students( (zw image，primary key (sno)，unique (sfzh)，check (ssex=男or ssex=

30、女) )(3)建表時在末尾標準創建語法：CONSTRAINT 檢查約束名 CHECK （邏輯表達式）上面的最后一句改成： CONSTRAINT CK_Students check (ssex=男or ssex=女)關于唯一性約束名，可以由創建者自己取，一般系統習慣取名為CK_表名課堂練習課堂練習3 3 使用SQL語句建立一個學生選課信息SC表，表中有學號sno，課程號cno，成績score。請為該表設置主鍵，并定義SCORE 的取值范圍為0到100之間。 建立一個學生表stu（學生編號sno，姓名sname，年齡sage，家庭地址address，聯系電話tel），定義年齡的取值范圍為0到30之

31、間,學生編號為主鍵,姓名為唯一性約束.默認約束默認約束 （DEFAULTDEFAULT） 默認約束指定在插入操作中如果沒有提供輸入值，則系統自動按默認約束的設置指定值。默認約束可以包括常量、函數、不帶變元的內建函數或者空值。 使用默認約束時，應該注意以下幾點：(1)每個字段只能定義一個默認約束；(2)如果定義的默認值長于其對應字段的允許長度， 那么輸入到表中的默認值將被截斷；(3)不能在設置為IDENTITY屬性或者數據類型為timestamp(時間戳)的字段上創建默認約束；(4)如果某字段設置為not null，則設置默認約束沒有任何意義創建默認約束創建默認約束 方法 （1） SQL Ser

32、ver管理平臺操創建圖5-10 創建默認約束對話框 方法二：使用方法二：使用SQLSQL語句創建默認約束語句創建默認約束其語法形式如下： CONSTRAINT 默認約束名稱 DEFAULT 默認約束表達式 FOR 列名(放在表的末尾)例1：為ssex字段創建默認約束“男”。constraint DF_students default 計算機 for dept例2：為 dept字段創建默認約束“計算機”。constraint DF_dept default 計算機 for dept 外鍵（外碼）約束 student dept學號學號姓名姓名性別性別系別系別1301李榮浩李榮浩女女X011302劉

33、強東劉強東男男X011303馬云馬云男男X041304陸盈盈陸盈盈女女X02系別號系別號系名系名X01計算機系計算機系X02信管系信管系X03軟工系軟工系參照被參照表被參照表（主表）（主表）參照表參照表（從表）（從表）主碼外碼Null值值可以嗎？可以嗎？外鍵 參照關系和和被參照關系可以是同一張表學號姓名性別年齡專業號班長學號801張三女1901802802李四男2001null803王五男2001802804趙六女2002805805錢七男1902null外碼主碼創建外鍵約束創建外鍵約束 第一種方法：在SQL Server管理平臺中添加外部鍵約束圖5-12 創建外鍵約束對話框 方法二：使用方法

34、二：使用SQLSQL語句創建外鍵約束語句創建外鍵約束使用SQL語句設置外部鍵約束 ，其語法形式如下： CONSTRAINT 外鍵約束名 FOREIGN KEY （外鍵約束的列名） REFERENCES 主鍵表 （主鍵表中的主鍵列名）與前面創建主鍵類似，也可在建表時用3種方式創建創建借閱表創建借閱表Borrow(P34)Borrow(P34)CREATE TABLE Borrow(book_ID CHAR(10) PRIMARY KEY,Reader_ID CHAR(10) PRIMARY KEY,Borrowdate DATETIME default getdate(),FOREIGN KEY

35、(book_ID) REFERENCES Book(book_ID), FOREIGN KEY(Reader_ID) REFERENCES Reader(Reader_ID) )列名數據類型約束reader_IDchar(10) 主鍵，外鍵，參照reader表中reader_idbook_IDchar(10) 主鍵，外鍵，參照book表中book_idborrowdate datetime 默認值為當前的系統時間表級完整性約束（表級完整性約束（外外鍵鍵），建在表的末尾處），建在表的末尾處創建借閱表創建借閱表Borrow(P34)Borrow(P34)CREATE TABLE Borrow(bo

36、ok_ID CHAR(10) REFERENCES Book(book_ID),Reader_ID CHAR(10) REFERENCES Reader(Reader_ID),Borrowdate DATETIME default getdate(),PRIMARY KEY(book_ID,Reader_ID)列名數據類型約束reader_IDchar(10)主鍵，外鍵，參照reader表中reader_idbook_IDchar(10)主鍵，外鍵，參照book表中book_idborrowdatedatetime默認值為當前的系統時間列級完整性約束（列級完整性約束（外外鍵鍵），建在字段后面）

37、，建在字段后面表級完整性約束表級完整性約束（組合組合主鍵主鍵）創建借閱表創建借閱表Borrow(P34)Borrow(P34)CREATE TABLE Borrow(book_ID CHAR(10) ,Reader_ID CHAR(10),Borrowdate DATETIME default getdate(),constraint PK_Borrow PRIMARY KEY(book_ID,Reader_ID),constraint FK_B_B Foreign key(book_id) REFERENCES Book(book_ID),constraint FK_B_R Foreign

38、key(reader_id) REFERENCES Reader(Reader_ID)列名數據類型約束reader_IDchar(10)主鍵，外鍵，參照reader表中reader_idbook_IDchar(10)主鍵，外鍵，參照book表中book_idborrowdatedatetime默認值為當前的系統時間列級完整性約束表級完整性約束在已有表中添加和刪除約束在已有表中添加和刪除約束語法格式：ALTER TABLE ADD CONSTRAINT 約束定義; -添加約束ALTER TABLE DROP CONSTRAINT； -刪除約束添加主鍵添加主鍵 語法：語法：ALTER TABLE

39、ADD CONSTRAINT Primary Key(列名,n); 在student表的sno列上添加主碼ALTER TABLE student ADD PRIMARY KEY(sno); 添加Sc表的主鍵ALTER TABLE sc ADD constraint pk_sc PRIMARY KEY(sno,cno);由系統分配由系統分配約束名約束名自己定義約束名列名列名約束約束sno主碼cno主碼grade添加外鍵添加外鍵 語法：ALTER TABLE ADD CONSTRAINT Foreign Key(列名,n) references 主表表名(主表的列名); 添加Sc表的外鍵ALTER TABLE sc ADD FOREIGN KEY(sno) REFERENCES student(sno);ALTER TABLE sc ADD constraint fk_sc_cno FOREIGN KEY(cno) REFERENCES course(cno);由系統分配由系統分配約束名約束名自己定義約束名列名列名約束約束sno外碼，引用student的snocno外碼，引用course的cnograde級聯更新、刪除級聯更新、刪除ALTER TABLE ADD CONSTRAINT Foreign Key(