| | 2. 修改表
SQL語言提供對(duì)設(shè)計(jì)好的表進(jìn)行補(bǔ)充和修改的功能，包括增加，修改和刪除某些列等。在SQL Server中，可以使用SQL語句或SQL企業(yè)管理器來修改數(shù)據(jù)庫表。
(1) 使用企業(yè)管理器修改表格。
從樹形結(jié)構(gòu)展開要修改定義的表所在數(shù)據(jù)庫，并選中該數(shù)據(jù)庫節(jié)點(diǎn)下面的表節(jié)點(diǎn)，在企業(yè)管理器右側(cè)顯示出該數(shù)據(jù)庫下全部表格。選擇要修改的表，單擊鼠標(biāo)右鍵，從彈出的快捷菜單中選擇"設(shè)計(jì)表"命令，其余操作和最初建立表格時(shí)一樣，如圖4-15。
圖4-15
(2) 使用SQL語句修改表
使用SQL語句，可以在更大范圍修改表格。包括增加、刪除、修改列定義或完整性約束條件，其格式：
ALTER TABLE 表名
[ADD子句] 增加新的列定義或列說明
[ALTER COLUMN子句] 修改表中列定義或列說明
[DROP子句] 刪除表中的列或列說明
[ADD CONSTRAINT子句] 增加約束
[DROP CONSTRAINT子句] 刪除約束
| 例1，修改表COUSE。上面的例4產(chǎn)生的表不能滿足設(shè)計(jì)要求，必須對(duì)該表進(jìn)行修改，增加約束外部關(guān)鍵字約束說明,滿足設(shè)計(jì)要求，語句如下: ALTER TABLE COUSE
ADD FOREIGN KEY (cdept) REFERENCES DEPT [deptno]
ADD FOREIGN KEY (ctno) REFERENCES TEACHER [tno] | |
例2，修改學(xué)生的年齡為出生日期（日期類型）。
ALTER TABLE STUDENT
ALTER COLUMN sage DATETIME | |
例3：增加學(xué)生的電話屬性:
ALTER TABLE STUDENT
ADD sphno CHAR(12) | | 例4，增加學(xué)生的國(guó)家屬性
ALTER TABLE STUDENT
ADD country CHAR(30) |
(3) 在查詢分析器中修改表，修改結(jié)果如圖4-16。
圖4-16
| | | | | | ③用T-SQL語句創(chuàng)建數(shù)據(jù)庫：
用T-SQL語句創(chuàng)建數(shù)據(jù)庫，可以在程序中使用，或在查詢分析器中使用。語法格式如下：
CREATE DATABASE database_name
[ ON [PRIMARY]
[ <filespec>[,…n] ][, <filegroup>[,…n] ] ][ LOG ON { <filespec> [,…n] } ]
[ FOR LOAD ∣ FOR ATTACH ] <filespec>:: = (
[ NAME = logical_file_name,]
FILENAME = 'os_file_name'
[,SIZE = size]
[,MAXSIZE = {max_size|UNLIMITED}]
[,FILEGROWTH = growth_increment]) [,…n] <filegroup>:: =
FILEGROUP filegroup_name <filespec>[,…n] 參數(shù)說明：
· database_name
新建數(shù)據(jù)庫的名稱。數(shù)據(jù)庫名稱在服務(wù)器中必須唯一，并且符合標(biāo)識(shí)符的規(guī)則。database_name最多可以包含128個(gè)字符。如果沒有指定日志文件的邏輯名，則SQL Server會(huì)通過向 database_name追加后綴來生成邏輯名。該操作要求database_name在123個(gè)字符之內(nèi)，以便生成的日志文件邏輯名少于128個(gè)字符。
· ON
顯式定義用來存儲(chǔ)數(shù)據(jù)庫數(shù)據(jù)部分的數(shù)據(jù)文件。該關(guān)鍵字后跟以逗號(hào)分隔的 <filespec> 項(xiàng)列表，<filespec> 項(xiàng)用以定義主文件組的數(shù)據(jù)文件。主文件組的文件列表后可跟以逗號(hào)分隔的 <filegroup> 項(xiàng)列表，<filegroup> 項(xiàng)用以定義用戶文件組及其文件。
· LOG ON
顯式定義用來存儲(chǔ)數(shù)據(jù)庫日志的磁盤文件（日志文件）。該關(guān)鍵字后跟以逗號(hào)分隔的 <filespec> 項(xiàng)列表，<filespec> 項(xiàng)用以定義日志文件。如果沒有指定 LOG ON，將自動(dòng)創(chuàng)建一個(gè)日志文件，它使用系統(tǒng)生成的名稱，大小為數(shù)據(jù)庫中所有數(shù)據(jù)文件總大小的25%。
· FOR LOAD
支持該子句是為了與早期版本的 SQL Server 兼容。表示將備份數(shù)據(jù)直接裝入新建的數(shù)據(jù)庫。但從SQL Server7.0開始就可以使用RESTORE語句完成相同的功能。
· FOR ATTACH
指定從現(xiàn)有的一組操作系統(tǒng)文件中建立數(shù)據(jù)庫。必須有指定第一個(gè)主文件的 <filespec> 條目。
· PRIMARY
定義數(shù)據(jù)庫的主數(shù)據(jù)文件。相應(yīng)<filespec>列表的第一個(gè)<filespec>條目成為主數(shù)據(jù)文件，該文件包含數(shù)據(jù)庫的邏輯起點(diǎn)及其系統(tǒng)表。一個(gè)數(shù)據(jù)庫只能有一個(gè)主數(shù)據(jù)文件。如果沒有指定PRIMARY，那么CREATE DATABASE語句中列出的第一個(gè)文件將成為主數(shù)據(jù)文件。
· NAME
為由<filespec>定義的文件指定邏輯名稱。如果指定了FOR ATTACH，則不需要指定 NAME參數(shù)。
· logical_file_name
用來在創(chuàng)建數(shù)據(jù)庫后執(zhí)行的 Transact-SQL 語句中引用文件的名稱。logical_file_name 在數(shù)據(jù)庫中必須唯一，并且符合標(biāo)識(shí)符的規(guī)則。
· FILENAME
為 <filespec> 定義的文件指定操作系統(tǒng)文件名。
· 'os_file_name'
操作系統(tǒng)創(chuàng)建 <filespec> 定義的物理文件時(shí)使用的路徑名和文件名。os_file_name 中的路徑必須指定 SQL Server 實(shí)例上的目錄。os_file_name 不能指定壓縮文件系統(tǒng)中的目錄。
如果文件在原始分區(qū)上創(chuàng)建，則 os_file_name 必須只指定現(xiàn)有原始分區(qū)的驅(qū)動(dòng)器字母。每個(gè)原始分區(qū)上只能創(chuàng)建一個(gè)文件。原始分區(qū)上的文件不會(huì)自動(dòng)增長(zhǎng)；因此，os_file_name 指定原始分區(qū)時(shí)，不需要指定 MAXSIZE 和 FILEGROWTH 參數(shù)。
· SIZE
指定<filespec>中定義的文件的大小。如果主文件的<filespec>中沒有提供SIZE參數(shù)，那么SQL Server將使用model數(shù)據(jù)庫中的主文件大小。如果輔助文件或日志文件的 <filespec>中沒有指定SIZE參數(shù)，則SQL Server將使文件大小為1MB。
· size
<filespec> 中定義的文件的初始大小。可以使用千字節(jié) (KB)、兆字節(jié) (MB)、千兆字節(jié) (GB) 或兆兆字節(jié) (TB) 后綴。默認(rèn)值為 MB。指定一個(gè)整數(shù)，不要包含小數(shù)位。size 的最小值為 512 KB。如果沒有指定 size，則默認(rèn)值為 1 MB。為主文件指定的大小至少應(yīng)與 model 數(shù)據(jù)庫的主文件大小相同。
· MAXSIZE
指定 <filespec> 中定義的文件可以增長(zhǎng)到的最大大小。
· max_size
<filespec> 中定義的文件可以增長(zhǎng)到的最大大小?？梢允褂们ё止?jié) (KB)、兆字節(jié) (MB)、千兆字節(jié) (GB) 或兆兆字節(jié) (TB) 后綴。默認(rèn)值為 MB。指定一個(gè)整數(shù)，不要包含小數(shù)位。如果沒有指定 max_size，那么文件將增長(zhǎng)到磁盤變滿為止。
說明：在磁盤即將變滿時(shí)，Windows NT 系統(tǒng)日志會(huì)警告 SQL Server 系統(tǒng)管理員。
· UNLIMITED
指定 <filespec> 中定義的文件將增長(zhǎng)到磁盤變滿為止。
· FILEGROWTH
指定 <filespec> 中定義的文件的增長(zhǎng)增量。文件的 FILEGROWTH 設(shè)置不能超過 MAXSIZE 設(shè)置。
· growth_increment
每次需要新的空間時(shí)為文件添加的空間大小。指定一個(gè)整數(shù)，不要包含小數(shù)位。0 值表示不增長(zhǎng)。該值可以 MB、KB、GB、TB 或百分比 (%) 為單位指定。如果未在數(shù)量后面指定 MB、KB 或 %，則默認(rèn)值為 MB。如果指定 %，則增量大小為發(fā)生增長(zhǎng)時(shí)文件大小的指定百分比。如果沒有指定 FILEGROWTH，則默認(rèn)值為1MB；
| 例1，在查詢分析器中創(chuàng)建數(shù)據(jù)庫示例如圖4-6，其中相應(yīng)的c:\mssql\data目錄一定要事先存在。創(chuàng)建數(shù)據(jù)庫的T-SQL語句如下：
CREATE DATABASE my_libraryON PRIMARY( --數(shù)據(jù)主文件
NAME = my_library_data, --邏輯文件名
FILENAME ='c:\mssql\data\my_library.mdf', --數(shù)據(jù)主文件存儲(chǔ)路徑，操作系統(tǒng)文件名
SIZE = 10 MB, --10 MB的主要數(shù)據(jù)庫文件
MAXSIZE = 15 MB, --最大容量15MB
FILEGROWTH = 20 % ) --當(dāng)主數(shù)據(jù)文件滿了，自動(dòng)增大20%的容量
LOG ON( --日志文件
NAME = my_library_log, --日志文件邏輯文件名
FILENAME ='c:\mssql\data\my_library.ldf',
--日志文件存儲(chǔ)路徑，操作系統(tǒng)文件名
SIZE = 3 MB , --3MB的日志文件
MAXSIZE = 5 MB , --最大容量5MB
FILEGROWTH = 1 MB ) --當(dāng)日志文件滿了，自動(dòng)增大1MB。 |
圖4-6 創(chuàng)建數(shù)據(jù)庫
| | | | | | | | 在SQL中，最常用的向表格中添加數(shù)據(jù)的方法是使用INSERT語句。但是，用INSERT語句每次只能插入一個(gè)元組。用帶子查詢插入語句，一個(gè)次可以插入一個(gè)或多個(gè)元組。
● 插入一個(gè)元組的語句格式：
INSERT INTO 表名[（列名[，列名]…）]
VALUES(值[，值]…..)
插入單個(gè)元組，按順序在表名后給出表中每個(gè)列名，在VALUES后給出對(duì)應(yīng)的每個(gè)列值。插入一個(gè)完整的新元組時(shí)，可省略表的列名。插入部分列值，必須在表名后給出要輸入值的列名。 | 例1，插入單個(gè)元組，按順序給出表中每個(gè)列值。
INSERT INTO DEPT VALUES( "CS","計(jì)算機(jī)","888", "10區(qū)" ) | |
例2，插入一個(gè)學(xué)生的部分列值，必須在表名后給出要輸入值的列名。
INSERT INTO STUDENT (sno ,sname)
VALUES( "J20045", "劉琉") | 在使用INSERT語句的時(shí)候，VALUES列表中的值或表達(dá)式必須匹配表中的列數(shù)，并和相應(yīng)各列數(shù)據(jù)類型兼容。如果表格中存在某些列定義為NOT NULL，那么在插入時(shí)該列必須要出現(xiàn)在VALUES的列表中，否則插入會(huì)失敗。 ● 用子查詢插入多個(gè)元組值的語句格式：
INSERT INTO 表名[（列名[，列名]…）]
子查詢
注意：在使用子查詢的結(jié)果插入元組時(shí)，子查詢的結(jié)果必須匹配待插入表中的列數(shù)，并和相應(yīng)各列數(shù)據(jù)類型兼容。如果表格中存在某些列定義為NOT NULL，那么子查詢的結(jié)果在該列上必須有值，否則插入會(huì)失敗。 | 例3，建立一個(gè)新表，存放每個(gè)學(xué)生的學(xué)號(hào)、姓名和平均成績(jī)。把子查詢結(jié)果插入新表中。
CREATE TABLE savg( sno CHAR(8),sname CHAR(20), avage REAL )
INSERT INTO savg (sno,sname,avage)
( SELECT sno,sname，AVG(grade)
FROM SC，STUDENT
WHERE SC.sno = STUDENT.sno
GROUP BY sno ) | |
例4，在表Studio中插入屬性name，這些name是在表Movie中提到，但沒有在表Studio中出現(xiàn)的studioName。
INSERT INTO Studio(name)
SELECT DISTINCT studioName
FROM Movie
WHERE studioName NOT IN
(SELECT name FROM Studio); | 考慮上述例子如果去掉DISTINCT會(huì)是什么樣子？和你想象的一樣嗎？ | | | | | | 在SQL中，最常用的向表格中添加數(shù)據(jù)的方法是使用INSERT語句。但是，用INSERT語句每次只能插入一個(gè)元組。用帶子查詢插入語句，一個(gè)次可以插入一個(gè)或多個(gè)元組。
● 插入一個(gè)元組的語句格式：
INSERT INTO 表名[（列名[，列名]…）]
VALUES(值[，值]…..)
插入單個(gè)元組，按順序在表名后給出表中每個(gè)列名，在VALUES后給出對(duì)應(yīng)的每個(gè)列值。插入一個(gè)完整的新元組時(shí)，可省略表的列名。插入部分列值，必須在表名后給出要輸入值的列名。 | 例1，插入單個(gè)元組，按順序給出表中每個(gè)列值。
INSERT INTO DEPT VALUES( "CS","計(jì)算機(jī)","888", "10區(qū)" ) | |
例2，插入一個(gè)學(xué)生的部分列值，必須在表名后給出要輸入值的列名。
INSERT INTO STUDENT (sno ,sname)
VALUES( "J20045", "劉琉") | 在使用INSERT語句的時(shí)候，VALUES列表中的值或表達(dá)式必須匹配表中的列數(shù)，并和相應(yīng)各列數(shù)據(jù)類型兼容。如果表格中存在某些列定義為NOT NULL，那么在插入時(shí)該列必須要出現(xiàn)在VALUES的列表中，否則插入會(huì)失敗。 ● 用子查詢插入多個(gè)元組值的語句格式：
INSERT INTO 表名[（列名[，列名]…）]
子查詢
注意：在使用子查詢的結(jié)果插入元組時(shí)，子查詢的結(jié)果必須匹配待插入表中的列數(shù)，并和相應(yīng)各列數(shù)據(jù)類型兼容。如果表格中存在某些列定義為NOT NULL，那么子查詢的結(jié)果在該列上必須有值，否則插入會(huì)失敗。 | 例3，建立一個(gè)新表，存放每個(gè)學(xué)生的學(xué)號(hào)、姓名和平均成績(jī)。把子查詢結(jié)果插入新表中。
CREATE TABLE savg( sno CHAR(8),sname CHAR(20), avage REAL )
INSERT INTO savg (sno,sname,avage)
( SELECT sno,sname，AVG(grade)
FROM SC，STUDENT
WHERE SC.sno = STUDENT.sno
GROUP BY sno ) | |
例4，在表Studio中插入屬性name，這些name是在表Movie中提到，但沒有在表Studio中出現(xiàn)的studioName。
INSERT INTO Studio(name)
SELECT DISTINCT studioName
FROM Movie
WHERE studioName NOT IN
(SELECT name FROM Studio); |
考慮上述例子如果去掉DISTINCT會(huì)是什么樣子？和你想象的一樣嗎？ | | | | | | | | DELETE 語句從表中刪除一個(gè)或多個(gè)滿足條件的元組。語句格式：
DELETE FROM 表名 ［WHERE 條件表達(dá)式］
WHERE子句中的條件表達(dá)式給出被刪除元組應(yīng)滿足的條件；若不寫WHERE子句，表示刪除表中的所有元組，但表的定義仍存在。
| 例1，只涉及單個(gè)元組，從數(shù)據(jù)庫中刪除某個(gè)學(xué)生李楷:
DELETE FROM STUDENT
WHERE sname = "李楷" | |
例2，刪除操作涉及多個(gè)元組，從數(shù)據(jù)庫中刪除某門課程的所有元組:
DELETE FROM SC
WHERE cno = "E001" | | | | | | |
