1、關(guān)系數(shù)據(jù)庫(kù)與SQL Server 2012例題源代碼第1章 關(guān)系數(shù)據(jù)庫(kù)原理第2章 SQL Server 2012基礎(chǔ)第3章 數(shù)據(jù)庫(kù)的創(chuàng)建與管理【例3-2】創(chuàng)建一個(gè)不帶任何參數(shù)的數(shù)據(jù)庫(kù)DB1。CREATE DATABASE DB1【例3-3】創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)，指定數(shù)據(jù)庫(kù)的數(shù)據(jù)文件所在位置。CREATE DATABASE DB2ON( NAME =DB2, FILENAME = 'D:TESTDB2.MDF')【例3-4】創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)，指定數(shù)據(jù)庫(kù)的數(shù)據(jù)文件所在位置、初始容量、最大容量和文件增量。CREATE DATABASE DB3ON( NAME =DB3, FILENAME =

2、 'D:TESTDB3.MDF',SIZE = 10,MAXSIZE = 50,FILEGROWTH = 5% )【例3-5】創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)，指定數(shù)據(jù)庫(kù)的數(shù)據(jù)文件和日志文件的存放位置。CREATE DATABASE DB4ON( NAME =DB4, FILENAME = 'D:TESTDB4.MDF',SIZE = 10,MAXSIZE = 50,FILEGROWTH = 5% )LOG ON( NAME = DB4LOG , FILENAME = 'D:TESTDB4.LDF')【例3-6】創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)，該庫(kù)共包含3個(gè)數(shù)據(jù)文件和2個(gè)日志文件

3、。CREATE DATABASE DB5ON ( NAME = DB51, FILENAME = 'D:TESTDB51.MDF , SIZE = 100, MAXSIZE = 200, FILEGROWTH = 20 ) , ( NAME = DB52, FILENAME = 'D:TESTDB52.NDF , SIZE = 100, MAXSIZE = 200, FILEGROWTH = 20 ) , ( NAME = DB53, FILENAME = 'D:TESTDB53.NDF , SIZE = 100, MAXSIZE = 200, FILEGROWTH =

4、 20)LOG ON ( NAME = DB5LOG1, FILENAME = 'D:TESTDB5LOG1.LDF) , ( NAME = DB5LOG2, FILENAME = 'D:TESTDB5LOG2.LDF , SIZE = 50, MAXSIZE = 200, FILEGROWTH = 20 )【例3-7】創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)，該庫(kù)共包含3個(gè)數(shù)據(jù)文件和2個(gè)自定義文件組。CREATE DATABASE DB6 ON ( NAME = DB61, FILENAME = ' D: TEST DB61.MDF , SIZE = 100, MAXSIZE = 200, F

5、ILEGROWTH = 20) , FILEGROUP FDB61 ( NAME = DB62, FILENAME = ' D: TEST DB62.NDF , SIZE = 100, MAXSIZE = 200, FILEGROWTH = 20 ) , FILEGROUP FDB62 ( NAME = DB63, FILENAME = ' D: TEST DB63.NDF , SIZE = 100, MAXSIZE = 200, FILEGROWTH = 20 )【例3-9】將數(shù)據(jù)庫(kù)DB1的數(shù)據(jù)庫(kù)名稱改為“DB8”。ALTER DATABASE DB1 MODIFY NAME

6、 = DB8【例3-10】在數(shù)據(jù)庫(kù)DB8中增加一個(gè)數(shù)據(jù)文件和一個(gè)事務(wù)日志文件。ALTER DATABASE DB8ADD FILE ( NAME = DB81 , FILENAME = D:TEST DB81.NDF ' )ALTER DATABASE DB8ADD LOG FILE ( NAME = DB8LOG1 , FILENAME = D:TEST DB8LOG1.LDF ' )【例3-11】在數(shù)據(jù)庫(kù)DB8中增加一個(gè)名為“FDB8”的文件組。ALTER DATABASE DB8ADD FILEGROUP FDB8【例3-12】將數(shù)據(jù)庫(kù)DB8中的FDB8文件組的名稱改為“

7、FG8”。ALTER DATABASE DB8 MODIFY FILEGROUP FDB8 NAME = FG8【例3-13】在數(shù)據(jù)庫(kù)DB8中增加兩個(gè)數(shù)據(jù)文件到文件組“FG8”中，并將該文件組設(shè)為默認(rèn)文件組。ALTER DATABASE DB8ADD FILE ( NAME= DB82 , FILENAME= D:TEST DB82.NDF' ) ,( NAME= DB83 , FILENAME= D:TEST DB83.NDF' )TO FILEGROUP FG8GOALTER DATABASE DB8MODIFY FILEGROUP FG8 DEFAULT【例3-14】將數(shù)

8、據(jù)庫(kù)DB8中增加的“DB83”的數(shù)據(jù)庫(kù)文件名稱改為“DDD”。ALTER DATABASE DB8MODIFY FILE (NAME= DB83, NEWNAME= DDD, FILENAME= D:TEST DDD.NDF ' )【例3-15】將數(shù)據(jù)庫(kù)DB8的文件組“FG8”中的數(shù)據(jù)文件“DB82”刪除，并將事務(wù)日志文件“DB8LOG1”刪除。ALTER DATABASE DB8REMOVE FILE DB82ALTER DATABASE DB8REMOVE FILE DB8LOG1【例3-16】將數(shù)據(jù)庫(kù)DB8中的文件組“FG8”刪除。ALTER DATABASE DB8 MODIF

9、Y FILEGROUP PRIMARY DEFAULT GOALTER DATABASE DB8 REMOVE FILE DDDGOALTER DATABASE DB8 REMOVE FILEGROUP FG8【例3-18】將數(shù)據(jù)庫(kù)DB3、DB4、DB5、DB6、DB7刪除。DROP DATABASE DB3,DB4,DB5,DB6,DB7【例3-20】查看指定數(shù)據(jù)庫(kù)的信息，如圖3-11所示。sp_helpdb db【例3-21】查看服務(wù)器上所有數(shù)據(jù)庫(kù)的信息，如圖3-12所示。sp_helpdb第4章 數(shù)據(jù)表的創(chuàng)建與管理【例4-2】在XSCJ數(shù)據(jù)庫(kù)中，創(chuàng)建一個(gè)名為KC1的數(shù)據(jù)表，該表中只涉及到

10、列的定義。CREATE TABLE XSCJ.DBO.KC1( 序號(hào) INT ,課程號(hào) CHAR (3 ) ,課程名 VARCHAR (20 ) ,授課教師 VARCHAR (10 ) ,開(kāi)課學(xué)期 TINYINT ,學(xué)時(shí) TINYINT ,學(xué)分 TINYINT)【例4-3】在XSCJ數(shù)據(jù)庫(kù)中，創(chuàng)建一個(gè)名為KC2的數(shù)據(jù)表，該表中的“序號(hào)”列為標(biāo)識(shí)列。USE XSCJ CREATE TABLE KC2( 序號(hào) INT IDENTITY , 課程號(hào) CHAR (3 ) ,課程名 VARCHAR (20 ) ,授課教師 VARCHAR (10) ,開(kāi)課學(xué)期 TINYINT ,學(xué)時(shí) TINYINT ,學(xué)

11、分 TINYINT)【例4-4】在XSCJ數(shù)據(jù)庫(kù)中，創(chuàng)建一個(gè)名為KC的數(shù)據(jù)表，該表的結(jié)構(gòu)如表4-3所示。方法一：在新建表時(shí)，在單個(gè)列定義之后，緊接著定義約束。USE XSCJ CREATE TABLE KC( 序號(hào) INT IDENTITY , 課程號(hào) CHAR (3) NOT NULL CONSTRAINT PK_KC_KCH PRIMARY KEY , 課程名 VARCHAR (20) NOT NULL ,授課教師 VARCHAR (10) , 開(kāi)課學(xué)期 TINYINT NOT NULL CONSTRAINT DF_KC_XQ DEFAULT 1 CONSTRAINT CK_KC_XQ C

12、HECK (開(kāi)課學(xué)期>=1 and 開(kāi)課學(xué)期<=6 ) , 學(xué)時(shí) TINYINT NOT NULL ,學(xué)分 TINYINT)方法二：在新建表時(shí)，在所有列定義完之后，再定義約束。USE XSCJ CREATE TABLE KC( 序號(hào) INT IDENTITY , 課程號(hào) CHAR (3) NOT NULL , 課程名 VARCHAR (20) NOT NULL ,授課教師 VARCHAR (10) , 開(kāi)課學(xué)期TINYINT NOT NULL DEFAULT 1 ,學(xué)時(shí) TINYINT NOT NULL ,學(xué)分 TINYINT CONSTRAINT PK_KC_KH PRIMARY

13、 KEY(課程號(hào)) , CONSTRAINT CK_KC_XQ CHECK (開(kāi)課學(xué)期>=1 AND 開(kāi)課學(xué)期<=6) )方法三：在已經(jīng)創(chuàng)建好的表上定義約束，通過(guò)修改該表的方式添加約束。ALTER TABLE KCADD CONSTRAINT PK_KC_KH PRIMARY KEY(課程號(hào)) , CONSTRAINT DF_KC_XQ DEFAULT 1 FOR 開(kāi)課學(xué)期 , CONSTRAINT CK_KC_XQ CHECK (開(kāi)課學(xué)期>=1 AND 開(kāi)課學(xué)期<=6 ) 【例4-5】在XSCJ數(shù)據(jù)庫(kù)中，創(chuàng)建一個(gè)名為XS_KC的數(shù)據(jù)表，該表的結(jié)構(gòu)如表4-4所示。USE

14、 XSCJ CREATE TABLE XS_KC( 學(xué)號(hào) CHAR (10 ) NOT NULL REFERENCES XSQK(學(xué)號(hào)) , 課程號(hào) CHAR (3 ) NOT NULL ,成績(jī) TINYINT , 學(xué)分 TINYINTPRIMARY KEY (學(xué)號(hào),課程號(hào)) , CHECK (成績(jī)>=0 and 成績(jī)<=100 ) , FOREIGN KEY(課程號(hào)) REFERENCES KC(課程號(hào)) )【例4-7】在XSQK表中，增加兩列：“籍貫”字段，char(12)，默認(rèn)值為“重慶”。“email”字段，varchar(30) 。ALTER TABLE XSQKADD

15、籍貫 CHAR(12) CONSTRAINT DF_XSQK_JG DEFAULT 重慶 , EMAIL VARCHAR(30)【例4-8】將學(xué)生課程表XS_KC中的成績(jī)列的數(shù)據(jù)類型修改為numeric(4,1)。ALTER TABLE XS_KCALTER COLUMN 成績(jī) NUMERIC(4,1)【例4-9】在XSQK表的“姓名”列上增加唯一性約束，約束名為UK_XSQK_XM，并忽略對(duì)原有數(shù)據(jù)的約束檢查。ALTER TABLE XSQKWITH NOCHECKADD CONSTRAINT UK_XSQK_XM UNIQUE(姓名)【例4-10】將XSQK表中的“姓名”列上的約束刪除。A

16、LTER TABLE XSQK DROP CONSTRAINT UK_XSQK_XM【例4-11】將XSQK表中的“籍貫”、“emai”列刪除。ALTER TABLE XSQK DROP CONSTRAINT DF_XSQK_JG ALTER TABLE XSQK DROP COLUMN 籍貫, EMAIL 【例4-12】暫停XSQK表中的所有約束。ALTER TABLE XSQKNOCHECK CONSTRAINT ALL【例4-14】向XSQK表插入兩行數(shù)據(jù)。INSERT INTO XSQK (學(xué)號(hào),姓名,性別,出生日期,專業(yè)名,所在系,聯(lián)系電話,總學(xué)分,備注)INSERT XSQK (學(xué)

17、號(hào),姓名,性別,專業(yè)名,所在系,出生日期)VALUES (2012130405 , 田芳, 0 , 信息安全 ,計(jì)算機(jī) ,1995-7-15)【例4-15】將XS_KC表中的成績(jī)不及格的記錄，插入到NOPASS表中。USE XSCJINSERT INTO NOPASSSELECT * FROM XS_KC WHERE 成績(jī)<60GOSELECT * FROM NOPASS【例4-16】將XS_KC表中課程號(hào)為“101”的不及格的學(xué)生成績(jī)加5分。UPDATE XS_KCSET 成績(jī)=成績(jī)+5WHERE (課程號(hào)= 101 AND 成績(jī)<60)【例4-17】刪除XS_KC表中所有不及格

18、的記錄。DELETE XS_KC WHERE (成績(jī)< 60)【例4-20】用命令查看服務(wù)器上所有數(shù)據(jù)表的信息。sp_help 【例4-21】用命令查看XS_KC數(shù)據(jù)表的信息，查看的結(jié)果如圖4-17所示。sp_help XS_KC 【例4-23】查看XS_KC數(shù)據(jù)表的表約束，查看結(jié)果如圖4-19所示。sp_helpconstraint XS_KC第5章 數(shù)據(jù)查詢【例5-1】查看XSQK表中的所有記錄。查詢結(jié)果如圖5-1所示。SELECT * FROM XSQK 【例5-2】查詢XSQK表中的學(xué)號(hào)、姓名和專業(yè)名信息。查詢結(jié)果如圖5-2所示。SELECT 學(xué)號(hào), 姓名, 專業(yè)名FROM XS

19、QK【例5-3】查詢XSQK表中的前3條記錄。查詢結(jié)果如圖5-3所示。SELECT TOP 3 *FROM XSQK【例5-4】查看XS_KC表中的信息，其中，要求查詢折算成績(jī)，折算成績(jī)?yōu)樵煽?jī)的70%。查詢結(jié)果如圖5-4所示。SELECT 學(xué)號(hào), 課程號(hào), 成績(jī), 成績(jī)*0.7FROM XS_KC【例5-5】為【例5-4】中的計(jì)算列指定別名。查詢結(jié)果如圖5-5所示。SELECT 學(xué)號(hào), 課程號(hào), 成績(jī) AS 原成績(jī), 調(diào)整成績(jī)1=成績(jī)*0.7 ,成績(jī)+5 調(diào)整成績(jī)2FROM XS_KC【例5-6】在查詢結(jié)果集中的“備注”列前增加了一個(gè)的說(shuō)明列。查詢結(jié)果如圖5-6所示。SELECT 學(xué)號(hào), 姓

20、名, '職務(wù)是', 備注FROM XSQK【例5-7】在查詢結(jié)果集中的“備注”列前增加“其職務(wù)是”的說(shuō)明列。查詢結(jié)果如圖5-7所示。SELECT 學(xué)號(hào), 姓名, '其''職務(wù)''是', 備注FROM XSQK【例5-8】查看XS_KC表中學(xué)生選修了哪些課程。查詢結(jié)果如圖5-8所示。SELECT DISTINCT 課程號(hào)FROM XS_KC【例5-9】查詢XS_KC表中成績(jī)不及格的學(xué)生記錄。查詢結(jié)果如圖5-10所示。SELECT 學(xué)號(hào), 課程號(hào), 成績(jī)FROM XS_KCWHERE 成績(jī)<60【例5-10】查詢XSQK表中19

21、96年及其后出生的學(xué)生信息。查詢結(jié)果如圖5-11所示。SELECT *FROM XSQKWHERE 出生日期>1995-12-31【例5-11】查詢XSQK表中信息安全專業(yè)、性別是1的學(xué)生信息。查詢結(jié)果如圖5-12所示。SELECT 學(xué)號(hào), 姓名, 性別, 專業(yè)名FROM XSQKWHERE 性別=1 AND 專業(yè)名=信息安全【例5-12】查詢XS_KC表中成績(jī)?cè)?0和80之間的學(xué)生信息，查詢結(jié)果如圖5-13所示。SELECT *FROM XS_KCWHERE 成績(jī)>=70 AND 成績(jī)<=80【例5-13】查詢1996年12月出生的學(xué)生信息，查詢結(jié)果如圖5-14所示。SEL

22、ECT 姓名, 性別, 出生日期FROM XSQKWHERE 出生日期 BETWEEN 1996-12-01 AND 1996-12-31【例5-14】查詢XSQK表中的所有陳姓的學(xué)生信息，查詢結(jié)果如圖5-15所示。SELECT 學(xué)號(hào), 姓名FROM XSQKWHERE 姓名 LIKE '陳%'【例5-15】查詢XSQK表中電話號(hào)碼尾數(shù)為3的學(xué)生信息，查詢結(jié)果如圖5-16所示。SELECT *FROM XSQKWHERE 聯(lián)系電話 LIKE %3【例5-16】查詢XSQK表中學(xué)號(hào)尾數(shù)不為1、2、3的學(xué)生信息，查詢結(jié)果如圖5-17所示。SELECT * FROM XSQK WHE

23、RE 學(xué)號(hào) LIKE %123或 SELECT * FROM XSQK WHERE 學(xué)號(hào) NOT LIKE %123【例5-17】查詢XS_KC表中課程號(hào)為101、105或108的學(xué)生成績(jī)信息，查詢結(jié)果如圖5-18所示。SELECT 學(xué)號(hào), 課程號(hào), 成績(jī)FROM XS_KCWHERE 課程號(hào) IN ( '101', '105', '108' )【例5-18】將【例5-17】中的代碼改成如下形式，其查詢結(jié)果不變。SELECT 學(xué)號(hào), 課程號(hào), 成績(jī)FROM XS_KCWHERE 課程號(hào)='101' OR 課程號(hào)='105&

24、#39; OR 課程號(hào)='108'【例5-19】查詢XSQK表中的學(xué)生干部的名單，查詢結(jié)果如圖5-19所示。SELECT 學(xué)號(hào), 姓名, 備注FROM XSQKWHERE 備注 IS NOT NULL【例5-20】為數(shù)據(jù)源指定別名。SELECT * FROM XS_KC CJ【例5-21】查詢XSQK表的記錄，并以姓名降序排列。SELECT *FROM XSQKORDER BY 姓名 DESC【例5-22】查詢XSQK表的記錄，并以出生日期升序排列。SELECT 學(xué)號(hào), 姓名, 出生日期FROM XSQKORDER BY 3【例5-23】查詢XS_KC表的記錄，并先按課程號(hào)升序

25、排列，當(dāng)課程號(hào)相同時(shí)再按成績(jī)降序排列。查詢結(jié)果如圖5-20所示。SELECT 學(xué)號(hào), 課程號(hào), 成績(jī) FROM XS_KCORDER BY 2, 3 DESC【例5-24】將【例5-23】的查詢結(jié)果保存到新表TEMP_KC中。SELECT 學(xué)號(hào), 課程號(hào), 成績(jī) INTO TEMP_KCFROM XS_KCORDER BY 2, 3 DESC【例5-25】計(jì)算XS_KC表中成績(jī)列的總和，并為該列指定別名為“總分”。查詢結(jié)果如圖5-21所示。SELECT SUM(成績(jī)) AS 總分FROM XS_KC【例5-26】計(jì)算XS_KC表中成績(jī)列的算數(shù)平均值，并為該列指定別名為“平均分”。SELECT

26、AVG(成績(jī)) AS 平均分FROM XS_KC【例5-27】計(jì)算XS_KC表中成績(jī)列的最大值，并為該列指定別名為“最高分”。SELECT MAX(成績(jī)) AS 最高分FROM XS_KC【例5-28】計(jì)算XS_KC表中成績(jī)列的最小值，并為該列指定別名為“最低分”。SELECT MIN(成績(jī)) AS 最低分FROM XS_KC【例5-29】計(jì)算XSQK表中學(xué)生記錄的行數(shù)，可以用以下兩種方法實(shí)現(xiàn)，其結(jié)果一樣。SELECT COUNT(學(xué)號(hào)) FROM XSQK或 SELECT COUNT( * ) FROM XSQK【例5-30】計(jì)算XS_KC表中成績(jī)不及格的學(xué)生人次，并為該列指定別名為“不及格

27、學(xué)生人次”。查詢結(jié)果如圖5-22所示。SELECT COUNT(DISTINCT 學(xué)號(hào)) AS '不及格的學(xué)生人數(shù)'FROM XS_KCWHERE 成績(jī)< 60【例5-31】在XS_KC表中，統(tǒng)計(jì)每門課程的平均分。SELECT 課程號(hào), AVG(成績(jī)) AS 平均分FROM XS_KCGROUP BY 課程號(hào)【例5-32】在XSQK表中，統(tǒng)計(jì)各專業(yè)男女生的人數(shù)。統(tǒng)計(jì)結(jié)果如圖5-25所示。SELECT 專業(yè)名, 性別, COUNT(性別) AS 人數(shù)FROM XSQKGROUP BY 專業(yè)名, 性別ORDER BY 專業(yè)名【例5-33】在XSQK表中，統(tǒng)計(jì)各專業(yè)男女生的人數(shù)

28、超過(guò)2人的信息。統(tǒng)計(jì)結(jié)果如圖5-26所示。SELECT 專業(yè)名, 性別, COUNT(性別) AS 人數(shù)FROM XSQKGROUP BY 專業(yè)名, 性別HAVING COUNT(性別)>2【例5-34】查詢不及格學(xué)生的學(xué)號(hào)、姓名、課程號(hào)和成績(jī)信息。查詢結(jié)果如圖5-27所示。 ANSI連接語(yǔ)法形式如下：SELECT XSQK.學(xué)號(hào), 姓名, 課程號(hào), 成績(jī)FROM XSQK INNER JOIN XS_KCON XSQK.學(xué)號(hào)=XS_KC.學(xué)號(hào)WHERE 成績(jī)<60 SQL Server連接語(yǔ)法形式如下：SELECT XSQK.學(xué)號(hào), 姓名, 課程號(hào), 成績(jī)FROM XSQK, X

29、S_KCWHERE XSQK.學(xué)號(hào)=XS_KC.學(xué)號(hào) AND 成績(jī)<60【例5-35】用左外連接方式查詢學(xué)生的學(xué)號(hào)、姓名、課程號(hào)和成績(jī)信息。查詢結(jié)果如圖5-28所示。SELECT XSQK.學(xué)號(hào), 姓名, 課程號(hào), 成績(jī)FROM XSQK LEFT OUTER JOIN XS_KCON XSQK.學(xué)號(hào)=XS_KC.學(xué)號(hào)【例5-36】用右外連接方式查詢不及格學(xué)生的學(xué)號(hào)、姓名、課程號(hào)和成績(jī)信息。查詢結(jié)果如圖5-29所示。SELECT XSQK.學(xué)號(hào), 姓名, 課程號(hào), 成績(jī)FROM XSQK RIGHT OUTER JOIN XS_KCON XSQK.學(xué)號(hào)=XS_KC.學(xué)號(hào)WHERE 成績(jī)&

30、lt;60【例5-37】用全外連接方式查詢學(xué)號(hào)、姓名、課程號(hào)、成績(jī)。查詢結(jié)果如圖5-30所示。SELECT XSQK.學(xué)號(hào), 姓名, 課程號(hào), 成績(jī)FROM XSQK FULL OUTER JOIN XS_KCON XSQK.學(xué)號(hào)=XS_KC.學(xué)號(hào)【例5-38】在查詢結(jié)果集中一行顯示每個(gè)學(xué)生的兩門課程成績(jī)。查詢結(jié)果如圖5-31所示。 ANSI連接語(yǔ)法形式如下：SELECT A.學(xué)號(hào), A.課程號(hào), A.成績(jī), B.課程號(hào), B.成績(jī)FROM XS_KC A JOIN XS_KC B ON A.學(xué)號(hào)=B.學(xué)號(hào)WHERE A.課程號(hào)<B.課程號(hào) SQL Server連接語(yǔ)法形式如下：SELE

31、CT A.學(xué)號(hào), A.課程號(hào), A.成績(jī), B.課程號(hào), B.成績(jī)FROM XS_KC A, XS_KC BWHERE A.學(xué)號(hào)=B.學(xué)號(hào) AND A.課程號(hào)<B.課程號(hào)【例5-39】查詢平均分低于60分的學(xué)生學(xué)號(hào)和姓名。查詢結(jié)果如圖5-32所示。SELECT 學(xué)號(hào), 姓名FROM XSQK AWHERE (SELECT AVG(成績(jī)) FROM XS_KC B WHERE B.學(xué)號(hào)=A.學(xué)號(hào))<60【例5-40】查詢有不及格學(xué)生的課程的授課教師。查詢結(jié)果如圖5-33所示。SELECT DISTINCT 授課教師FROM KCWHERE 課程號(hào)=ANY(SELECT 課程號(hào) FRO

32、M XS_KC B WHERE 成績(jī)<60)【例5-41】查詢每門課程的最低分。查詢結(jié)果如圖5-34所示。SELECT *FROM XS_KC AWHERE 成績(jī)<=ALL(SELECT 成績(jī) FROM XS_KC B WHERE B.課程號(hào)=A.課程號(hào))或SELECT *FROM XS_KC AWHERE 成績(jī)=(SELECT MIN(成績(jī)) FROM XS_KC B WHERE B.課程號(hào)=A.課程號(hào))【例5-42】將【例5-40】中的查詢改為用“IN”運(yùn)算符。查詢結(jié)果如圖5-35所示。SELECT DISTINCT 授課教師FROM KCWHERE 課程號(hào) IN( SELEC

33、T 課程號(hào) FROM XS_KC WHERE 成績(jī)<60 )【例5-43】查詢至少有一門課程不及格的學(xué)生信息。查詢結(jié)果如圖5-36所示。SELECT DISTINCT 學(xué)號(hào), 姓名FROM XSQK AWHERE EXISTS( SELECT * FROM XS_KC B WHERE B.成績(jī)<60 AND B.學(xué)號(hào)=A.學(xué)號(hào) )第6章 視圖與索引【例6-2】創(chuàng)建一個(gè)名為“V_不及格學(xué)生信息”的視圖，該視圖包含所有有不及格記錄的學(xué)生的學(xué)號(hào)、姓名和原始成績(jī)。執(zhí)行結(jié)果如圖6-5所示。USE XSCJGOCREATE VIEW V_不及格學(xué)生信息(學(xué)號(hào),姓名,原始成績(jī))ASSELECT

34、DISTINCT XSQK.學(xué)號(hào),姓名,成績(jī)FROM XSQK, XS_KCWHERE XSQK.學(xué)號(hào)=XS_KC.學(xué)號(hào) AND XS_KC.成績(jī)<60GOSELECT * FROM V_不及格學(xué)生信息【例6-3】查看XSCJ數(shù)據(jù)庫(kù)中的“V_不及格學(xué)生信息”視圖的定義，執(zhí)行結(jié)果如圖6-6所示。USE XSCJGOEXEC sp_helptext V_不及格學(xué)生信息【例6-6】修改“V_學(xué)生信息”視圖，使該視圖用于查詢是班委成員的男生信息，并強(qiáng)制檢查指定條件。執(zhí)行結(jié)果如圖6-9所示。USE XSCJGOALTER VIEW V_學(xué)生信息ASSELECT 學(xué)號(hào), 姓名, 性別, 出生日期,

35、專業(yè)名, 所在系, 備注 AS 職務(wù)FROM XSQKWHERE 性別=1 AND 備注 LIKE %WITH CHECK OPTIONGOSELECT * FROM V_學(xué)生信息【例6-8】通過(guò)“V_學(xué)生信息”視圖中添加一條記錄。執(zhí)行結(jié)果如圖6-14所示。USE XSCJGOINSERT INTO V_學(xué)生信息VALUES( '2012130112', '陳立', 1, '1996-05-08', '信息安全', '計(jì)算機(jī)應(yīng)用', '體育委員')GOSELECT * FROM XSQK【例6-9】

36、將“V_學(xué)生信息”視圖中的“張琳”的姓名改為“張林”。執(zhí)行結(jié)果如圖6-15所示。USE XSCJGOUPDATE V_學(xué)生信息SET 姓名='張林'WHERE 姓名='張琳'GOSELECT * FROM XSQK【例6-10】通過(guò)“V_學(xué)生信息”視圖刪除“張林”的記錄。DELETE FROM V_學(xué)生信息WHERE 姓名='張林'【例6-13】為XS_KC表創(chuàng)建非聚集索引IX_XS_KC_學(xué)號(hào)_課程號(hào)，該索引包括學(xué)號(hào)和課程號(hào)兩個(gè)索引列，均按升序排列。USE XSCJCREATE INDEX IX_XS_KC_學(xué)號(hào)_課程號(hào)ON XS_KC (學(xué)號(hào)

37、, 課程號(hào))【例6-14】對(duì)【例6-13】作一點(diǎn)修改，使該索引變?yōu)槲ㄒ恍缘姆蔷奂饕Ｓ捎谒饕呀?jīng)存在，我們使用DROP_EXISTING選項(xiàng)刪除同名的原索引，然后再創(chuàng)建新索引。USE XSCJCREATE UNIQUE INDEX IX_XS_KC_學(xué)號(hào)_課程號(hào)ON XS_KC (學(xué)號(hào), 課程號(hào))WITH DROP_EXISTING【例6-15】創(chuàng)建復(fù)雜的索引。為“XS_KC”表創(chuàng)建一個(gè)唯一索引，索引字段為“課程號(hào)”、“學(xué)號(hào)”，其中“課程號(hào)”為降序排列，該索引的包含性列里面包含“成績(jī)”、“學(xué)分”兩個(gè)字段。指定索引填充，索引的填充因子為70，并允許在Tempdb數(shù)據(jù)庫(kù)中存儲(chǔ)臨時(shí)排序的信息，可以

38、忽略索引的重復(fù)鍵值，運(yùn)行自動(dòng)重新計(jì)算統(tǒng)計(jì)信息。USE XSCJCREATE UNIQUE INDEX IX_XS_KC_1ON XS_KC(課程號(hào) DESC, 學(xué)號(hào))INCLUDE(成績(jī), 學(xué)分)WITH(PAD_INDEX=ON,FILLFACTOR=70,SORT_IN_TEMPDB=ON,IGNORE_DUP_KEY=ON,STATISTICS_NORECOMPUTE=OFF)【例6-16】查看表XSQK中的索引信息。執(zhí)行結(jié)果如圖6-23所示。USE XSCJEXEC sp_helpindex XSQK【例6-18】重新生成“IX_XS_KC_學(xué)號(hào)_課程號(hào)”索引。ALTER INDEX

39、IX_XS_KC_學(xué)號(hào)_課程號(hào) ON XS_KC REBUILD【例6-19】刪除“XS_KC”表中的“IX_XS_KC_學(xué)號(hào)_課程號(hào)”索引。DROP INDEX XSQK.IX_XS_KC_學(xué)號(hào)_課程號(hào)【例6-20】為“XSCJ”數(shù)據(jù)庫(kù)啟用全文索引。EXEC sp_fulltext_database 'enable'【例6-22】為“XSCJ”數(shù)據(jù)庫(kù)在默認(rèn)位置創(chuàng)建一個(gè)全文目錄“FTC_XSCJ”。EXEC sp_fulltext_catalog 'FTC_XSCJ', 'create'【例6-24】利用唯一索引PK_KC，用命令方式完成【例6-

40、23】中全文索引的創(chuàng)建。EXEC sp_fulltext_table 'KC', 'create', 'FTC_XSCJ', 'PK_KC'【例6-25】將KC表中的“課程名”列添加到全文索引中。EXEC sp_fulltext_column 'KC', '課程名', 'add'【例6-26】激活表的全文檢索能力，在全文目錄中注冊(cè)表KC。EXEC sp_fulltext_table 'KC', 'activate'【例6-27】填充全文目錄。EXEC

41、sp_fulltext_catalog 'FTC_XSCJ', 'start_full'第7章 規(guī)則與默認(rèn)值【例7-1】創(chuàng)建數(shù)據(jù)庫(kù)XSCJ的規(guī)則，規(guī)則名為CJ_rule，要求表XS_KC中成績(jī)列的取值范圍為0100。執(zhí)行結(jié)果如圖7-1所示。USE XSCJGOCREATE RULE CJ_ruleAS成績(jī)>=0 AND 成績(jī)<=100【例7-2】查看規(guī)則CJ_rule。執(zhí)行結(jié)果如圖7-2所示。USE XSCJGOEXEC sp_helptext CJ_rule【例7-3】將規(guī)則CJ_rule綁定到表XS_KC的成績(jī)列上。執(zhí)行結(jié)果如圖7-3所示。USE

42、 XSCJGOEXEC sp_bindrule 'CJ_rule', 'XS_KC.成績(jī)'【例7-4】解除XS_KC表中“成績(jī)”列上綁定的規(guī)則。執(zhí)行結(jié)果如圖7-4所示。USE XSCJGOEXEC sp_unbindrule 'XS_KC.成績(jī)'【例7-5】刪除規(guī)則CJ_rule。USE XSCJGODROP RULE CJ_rule【例7-6】創(chuàng)建一個(gè)名為XS_def的默認(rèn)值對(duì)象，要求默認(rèn)值為20。執(zhí)行結(jié)果如圖7-5所示。USE XSCJGOCREATE DEFAULT XS_def AS 20【例7-7】將默認(rèn)值XS_def綁定到表KC的“學(xué)

43、時(shí)”列上。執(zhí)行結(jié)果如圖7-6所示。USE XSCJGOEXEC sp_bindefault 'XS_def', 'KC.學(xué)時(shí)'【例7-8】刪除數(shù)據(jù)庫(kù)XSCJ中名為XS_def的默認(rèn)值對(duì)象。執(zhí)行結(jié)果如圖7-7所示。USE XSCJGOEXEC sp_unbindefault KC.學(xué)時(shí)GODROP DEFAULT XS_def第8章 T-SQL編程【例8-1】在T-SQL中，可用多種方式使用常量。 作為算術(shù)表達(dá)式中的數(shù)據(jù)值：SELECT 成績(jī)+5 FROM XS_KC 在WHERE子句中，作為與列進(jìn)行比較的數(shù)據(jù)值：SELECT * FROM XS_KC WHERE

44、 學(xué)號(hào)='2012130101' 作為賦給變量的數(shù)據(jù)值：SET 學(xué)分=4 作為當(dāng)前行的某列中的數(shù)據(jù)值。可使用UPDATE語(yǔ)句的SET子句或INSERT語(yǔ)句的VALUES子句來(lái)指定：UPDATE XS_KCSET 成績(jī)=90WHERE 學(xué)號(hào)='2012130101' AND 課程號(hào)='101'或INSERT XS_KC VALUES(2012130101, 103, 78) 作為指定PRINT或RAISERROR語(yǔ)句發(fā)出的消息文本的字符串：PRINT 'This is a message.' 作為條件語(yǔ)句（例如：IF語(yǔ)句或CASE語(yǔ)

45、句）中進(jìn)行測(cè)試的值：IF (SALESTOTAL>$100000.00) EXECUTE Proc_SALES【例8-2】聲明3個(gè)變量，用兩種不同的方法分別為變量賦值，并輸出顯示，執(zhí)行結(jié)果如圖8-1所示。DECLARE x1 char(10), x2 varchar(20), x3 char(8)SELECT x1='關(guān)系數(shù)據(jù)庫(kù)', x2='SQL Server'SET x3='2012'PRINT x1+x2+x3【例8-3】定義一個(gè)整數(shù)變量，并將其作為循環(huán)計(jì)數(shù)器使用。DECLARE counter intSELECT counter=0

46、WHILE counter<10BEGINSELECT counter=counter+1PRINT counter END【例8-4】聲明一個(gè)變量，將“XSCJ”數(shù)據(jù)庫(kù)的“XSQK”表中的2012130101號(hào)學(xué)生的姓名賦值給變量，并輸出顯示，執(zhí)行結(jié)果如圖8-2所示。USE XSCJGODECLARE name varchar(10)SELECT name=姓名 FROM XSQK WHERE 學(xué)號(hào)='2012130101'SELECT name AS 姓名【例8-5】在Accounts表中檢索tot_assets列，并將money數(shù)據(jù)類型的檢索結(jié)果利用CAST或CON

47、VERT函數(shù)轉(zhuǎn)換為varchar數(shù)據(jù)類型，以便在字符串連接中使用。SELECT (acct_firstname+acct_lastname)+-+CAST(tot_assets AS varchar(15)FROM Accounts或者SELECT (acct_firstname+acct_lastname)+- +CONVERT(varchar(15), tot_assets)FROM Accounts【例8-6】統(tǒng)計(jì)XSQK表中學(xué)生記錄的行數(shù)。執(zhí)行結(jié)果如圖8-3所示。USE XSCJGODECLARE counter intSELECT counter=COUNT(*) FROM XSQK

48、PRINT '總行數(shù)為：'+STR(counter)【例8-7】將浮點(diǎn)數(shù)123.21轉(zhuǎn)換為5個(gè)字符的字符串。執(zhí)行結(jié)果如圖8-4所示。DECLARE x floatSET x=123.21PRINT '將浮點(diǎn)數(shù)123.21轉(zhuǎn)換成5個(gè)字符的字符串為：' +STR(x, 5, 1)【例8-8】查找KC表中課程號(hào)為“103”的課程名稱，并計(jì)算該課程名稱的字符數(shù)目。執(zhí)行結(jié)果如圖8-5所示。USE XSCJGODECLARE counter intSELECT counter=LEN(課程名) FROM KCWHERE 課程號(hào)='103'PRINT '

49、;103號(hào)課程名稱的字符數(shù)為：'+STR(counter)【例8-9】將字符串“Hello World”中的字符全部轉(zhuǎn)換為大寫(xiě)字母。執(zhí)行結(jié)果如圖8-6所示。DECLARE s1 varchar(20)SET s1='Hello World'PRINT '將“Hello World”中的字符全部轉(zhuǎn)換為大寫(xiě)字母：'+UPPER(s1)【例8-10】從字符串“SQL Server 2012”的左邊開(kāi)始截取5個(gè)字符構(gòu)成的子串。執(zhí)行結(jié)果如圖8-7所示。DECLARE s2 varchar(20)SET s2='SQL Server 2012'PRI

50、NT '從“SQL Server 2012”中截取5個(gè)字符構(gòu)成的子串為：'+LEFT(s2, 5)【例8-11】修改【例8-6】中的顯示結(jié)果，去除顯示結(jié)果中多余的空格。執(zhí)行結(jié)果如圖8-8所示。USE XSCJGODECLARE counter intSELECT counter=COUNT(*) FROM XSQKPRINT '總行數(shù)為：'+LTRIM(STR(counter)【例8-12】分別計(jì)算在日期“2015-5-10”上加上22年、22月、22天后得到的新日期。執(zhí)行結(jié)果如圖8-9所示。SELECT DATEADD(yy, 22, '2015-5-

51、10') AS 加年后的新日期, DATEADD(mm, 22, '2015-5-10') AS 加月后的新日期, DATEADD(dd, 22, '2015-5-10') AS 加日后的新日期【例8-13】分別計(jì)算日期“2012-8-18”和“2015-5-10”相隔的天數(shù)、月數(shù)和年數(shù)。執(zhí)行結(jié)果如圖8-10所示。SELECT DATEDIFF(dd, '2012-8-18', '2015-5-10') AS 間隔天數(shù), DATEDIFF(mm, '2012-8-18', '2015-5-10

52、9;) AS 間隔月數(shù), DATEDIFF(yy, '2012-8-18', '2015-5-10') AS 間隔年數(shù)【例8-14】分別返回日期“2015-5-1”的年、月、日。執(zhí)行結(jié)果如圖8-11所示。SELECT DATEPART(yy, '2015-5-1') AS 年, DATEPART(mm, '2015-5-1') AS 月, DATEPART(dd, '2015-5-1') AS 日【例8-15】計(jì)算XSQK表中陳偉的年齡，并顯示“年齡為：XX歲”。執(zhí)行結(jié)果如圖8-12所示。DECLARE nl va

53、rchar(4)SELECT nl=CONVERT(varchar(4), DATEDIFF(yy, 出生日期, GETDATE( )FROM XSQKWHERE 姓名='陳偉'PRINT '年齡為：'+nl+'歲'【例8-16】檢查XSQK表中是否存在“李淵”這個(gè)人，若有則顯示其信息，若無(wú)則顯示“沒(méi)有XX這個(gè)人！”。執(zhí)行結(jié)果如圖8-13所示。USE XSCJGODECLARE xm varchar(10)SET xm='李淵'IF EXISTS(SELECT * FROM XSQK WHERE 姓名=xm) SELECT * F

54、ROM XSQK WHERE 姓名=xmELSE PRINT '沒(méi)有'+xm+'這個(gè)人!'【例8-17】計(jì)算1+2+3+100的累加和。執(zhí)行結(jié)果如圖8-14所示。DECLARE i int, sum intSET i=1SET sum=0WHILE (i<=100)BEGINSET sum=sum +iSET i=i+1ENDPRINT '1+2+3+100='+CAST(sum AS varchar(10)【例8-18】查找失敗時(shí)輸出錯(cuò)誤信息并退出。執(zhí)行結(jié)果如圖8-15所示。USE XSCJGOIF NOT EXISTS(SELECT * FROM XSQK WHERE 姓名='李敏')BEGINPRINT 'not found'RETURNENDPRINT 'no error found'【例8-19】用IF和GOTO語(yǔ)句實(shí)現(xiàn)循環(huán)，求1+2+3+100的累加和。DECLARE i int, sum intSET i=1SET sum=0CountLoop: