專升本數(shù)據(jù)庫練習(xí)題及答案

1、精選優(yōu)質(zhì)文檔-傾情為你奉上一關(guān)系代數(shù)運(yùn)算1.選擇 （1）查詢年齡小于20歲的學(xué)生 sage<20(student) （2）查詢學(xué)號(hào)為的學(xué)生 sno=(student)2.投影 （1）查詢學(xué)生姓名，和所在系 same,sdept(student)（2）查詢選修了2號(hào)課程的學(xué)生學(xué)號(hào) sno（cno=22(student)） SQL語句學(xué)生-課程數(shù)據(jù)庫學(xué)生表：學(xué)生（學(xué)號(hào)，姓名，性別，年齡，所在系）課程表：課程（課程號(hào)，課程名，先行課，學(xué)分）學(xué)生選課表：選課（學(xué)號(hào)，課程號(hào)，成績）二創(chuàng)建數(shù)據(jù)庫SQL1. 創(chuàng)建數(shù)據(jù)庫 Create database 數(shù)據(jù)庫名 On ( name =邏輯文件名，F(xiàn)il

2、ename=物理文件名，Size=初始文件大小，Maxsize=最大文件大小，F(xiàn)ilegrowth=文件自動(dòng)增量 )Log on ( name =邏輯文件名，F(xiàn)ilename=物理文件名，Size=初始文件大小，Maxsize=最大文件大小，F(xiàn)ilegrowth=文件自動(dòng)增量 )例 創(chuàng)建教務(wù)管理數(shù)據(jù)庫 Create database 教務(wù)管理數(shù)據(jù)庫 On ( name =教務(wù)管理_dat，F(xiàn)ilename=E:SQL教務(wù)管理_dat.mdf，Size=2，F(xiàn)ilegrowth=1 )Log on ( name =教務(wù)管理.log，F(xiàn)ilename=E:SQL教務(wù)管理_log.ldf，Size=

3、5M，F(xiàn)ilegrowth=10% )1. 查看數(shù)據(jù)庫屬性1) 使用sp_helpdb查看數(shù)據(jù)庫信息 ： sp_helpdb 數(shù)據(jù)庫名（2）使用sp_databases 查看可使用的數(shù)據(jù)庫信息： sp_databases（顯示所有可以使用的數(shù)據(jù)庫名稱和大?。?）使用sp_helpfile查看數(shù)據(jù)庫文件信息 sp_helpfile 文件名1. 設(shè)置數(shù)據(jù)庫選項(xiàng) Alter database 數(shù)據(jù)庫名 Set 選項(xiàng)例 設(shè)置 教務(wù)管理數(shù)據(jù)庫自動(dòng)收縮 Alter database 教務(wù)管理數(shù)據(jù)庫 Set auto_shrink on （自動(dòng)收縮）1. 修改數(shù)據(jù)庫大小Alter database 數(shù)據(jù)庫

4、名Add file 增加數(shù)據(jù)文件Add log file 增加日志文件Remove file 刪除文件Modify file 修改文件（ ）例 1.修改教務(wù)管理數(shù)據(jù)庫，增加數(shù)據(jù)文件jwgl_dat1 Alter database 教務(wù)管理數(shù)據(jù)庫 Add file （ Name=jwgl_dat1, Filename=E:SQLjwgl_dat1.ndf， Size=2MB， Filegrowth=1MB )2. 修改教務(wù)管理數(shù)據(jù)庫 Alter database 教務(wù)管理數(shù)據(jù)庫 Modify file （ Name=jwgl_dat1, Size=5MB ）2. 刪除教務(wù)管理數(shù)據(jù)庫中次要數(shù)據(jù)文件

5、jwgl_dat1 Alter database 教務(wù)管理數(shù)據(jù)庫 Remove file jwgl_dat11. 數(shù)據(jù)庫更名1) 更改數(shù)據(jù)庫名Alter database 數(shù)據(jù)庫名Modify name = 新數(shù)據(jù)庫名例 將數(shù)據(jù)庫test更名為mytest Alter database test Modify name = mytest1) 更改數(shù)據(jù)庫的邏輯文件名 Alter database 數(shù)據(jù)庫名 Modify file（ Name=邏輯文件名， Newname=新邏輯文件名 ）例 將數(shù)據(jù)庫邏輯文件test更名為mytest Alter database mytest Modify fi

6、le（ Name = test， Newname = mytest ） Go Alter database mytest Modify file（ Name = test_log， Newname = mytest_log ） 1) 更改數(shù)據(jù)庫的物理文件名 Alter database 數(shù)據(jù)庫名 Modify file（ Name=邏輯文件名， Filename =新路徑操作系統(tǒng)文件名 ） 例 將數(shù)據(jù)庫物理文件test更名為mytest Alter database mytest Modify file（ Name=mytest， Filename =E:sqlmytest.mdf ） GO

7、Alter database mytest Modify file（ Name=mytest_log， Filename =E:sqlmytest_log.ldf ）1. 刪除數(shù)據(jù)庫 Drop database 數(shù)據(jù)庫名例 刪除數(shù)據(jù)庫名為test和數(shù)據(jù)庫sales Drop database test，sales1. 數(shù)據(jù)庫分離 Sp_detach_db 數(shù)據(jù)庫名1. 數(shù)據(jù)庫附加 Create database 數(shù)據(jù)庫名 On （ Filename=主文件的物理文件名） For attach三基本表操作1. 創(chuàng)建表Create table 表名（）例 創(chuàng)建一個(gè)學(xué)生表Create table 學(xué)

8、生表（學(xué)號(hào) char（6） primary key， 姓名 char（20） unique， 性別 char（2）， 年齡 smallint， 所在系 char（20）1. 修改表Alter table 表名Add 列名 數(shù)據(jù)類型（）/增加列（alter column 列名 數(shù)據(jù)類型（）/對(duì)原有的基礎(chǔ)上修改列）（drop column 列名 /對(duì)原有的基礎(chǔ)上刪除列，可一次刪除多列）例1. 添加一新列電子郵箱 char型 長度20 Alter table 學(xué)生表 Add 電子郵箱 char(20)例2. 修改學(xué)生表列 電子郵箱數(shù)據(jù)類型長度為22 Alter table 學(xué)生表 alter col

9、umn 電子郵箱 varchar(22)例3.刪除學(xué)生表的電子郵箱列 Alter table 學(xué)生表 drop column 電子郵箱3.刪除表Drop table 表名例 刪除學(xué)生表 Drop table 學(xué)生表四數(shù)據(jù)更新1. 插入數(shù)據(jù) Insert Into 表名 （） Values 值（）例1 插入一行數(shù)據(jù)到學(xué)生表的學(xué)生 Insert Into 學(xué)生表（學(xué)號(hào)，姓名，性別，年齡，所在系） Values （，張成民，男，18，計(jì)算機(jī)系）1. 修改數(shù)據(jù) Update 表名 Set 列名=表達(dá)式 Where 條件1) 修改某元祖的值 例 將學(xué)生的年齡改為22 Update 學(xué)生表 Set 年齡=

10、22 Where 學(xué)號(hào)=1) 修改多個(gè)元祖的值 例 將所有學(xué)生年齡增加一歲 Update 學(xué)生表 Set 年齡=年齡+11) 帶子查詢的修改語句 例 將計(jì)算機(jī)科學(xué)系全體學(xué)生成績置零 Update 選課表 Set 成績=0 Where 學(xué)號(hào) in （select 學(xué)號(hào) From 學(xué)生表 Where 所在系=計(jì)算機(jī)科學(xué)系）1) 修改表名 Sp_rename 舊對(duì)象名，新對(duì)象名 例 課程表更名為課程信息表 Sp_rename 課程表，課程信息表3. 刪除數(shù)據(jù) Delete from 表名 Where 條件例 將課程信息表中c程序設(shè)計(jì)課程信息刪除 Delete From 課程信息表 Where 課程名

11、=c程序設(shè)計(jì)五使用約束1. 非空約束（NOT NULL） 例 創(chuàng)建一個(gè)學(xué)生表 使姓名，性別 為非空約束 學(xué)號(hào)為主鍵Create table 學(xué)生表（學(xué)號(hào) char（6） primary key， 姓名 char（20） NOT NULL， 性別 char（2）NOT NULL， 年齡 smallint， 所在系 char（20）1. 唯一約束（UNIQUE）例 創(chuàng)建一個(gè)學(xué)生表 使姓名，性別 為唯一約束 學(xué)號(hào)為主鍵Create table 學(xué)生表（學(xué)號(hào) char（6） primary key， 姓名 char（20） UNIQUE， 性別 char（2）UNIQUE， 年齡 smallint，

12、所在系 char（20） ）1. Check約束（檢查列值是否滿足一個(gè)條件表達(dá)式）1. 完整性約束命令子句 Constraint 完整性約束條件名（包括非空 主鍵 唯一 外鍵 check約束等） 約束條件例 1創(chuàng)建一個(gè)學(xué)生表 使性別 只允許為男或女,年齡在20到30之間Create table 學(xué)生表（學(xué)號(hào) char（6） primary key， 姓名 char（20） UNIQUE， 性別 char（2） CHECK（性別=男）or （性別=女）， 年齡 smallint CHECK（年齡between 20 and 30）， 所在系 char（20） ） 2 利用T-SQL命令設(shè)置課程表

13、中的考核方式只能取值考試或考查，學(xué)分的值為1-6之間 Alter table 課程表 with check Add constraint CK_課程表_1 check（考核方式 in （考查，考試） Alter table 課程表 with nocheck Add constraint CK_課程表_2 check（學(xué)分 between 1 and 6）1. 主鍵約束（PRIMARY KEY）例 創(chuàng)建一個(gè)學(xué)生表 使姓名，性別 為唯一約束 學(xué)號(hào)為主鍵Create table 學(xué)生表（學(xué)號(hào) char（6） primary key，（在列定義后設(shè)置主鍵） 姓名 char（20） UNIQUE， 性別

14、 char（2）UNIQUE， 年齡 smallint， 所在系 char（20）1. 外鍵約束（FOREIGN KEY） Constraint 約束名 FOREIGN KEY（列名） Reference 被引用表（列名） on delete cascade（級(jí)聯(lián)刪除）|on update cascade（級(jí)聯(lián)更新）例 創(chuàng)建選課表 設(shè)置選課表的課程號(hào)為外鍵 引用課程表 課程號(hào) Create table 選課表（ 學(xué)號(hào) char（6） primary key， 課程號(hào) char（11） Reference 課程表（課程號(hào)）on update cascade， 成績 numeric（5,1）7.默

15、認(rèn)約束（DEFAULT） Constraint 約束名 DEFAULT 常量表達(dá)式 可在創(chuàng)建表中 數(shù)據(jù)類型后直接加例 修改課程表 設(shè)置考核方式默認(rèn)值為考試 Alter table 課程表 Add constraint df_課程表_考核 DEFAULT考試）for 考核方式六數(shù)據(jù)查詢1. 單表查詢1) 選擇列查詢 例 查詢?nèi)w學(xué)生姓名 學(xué)號(hào) Select 姓名，學(xué)號(hào) From 學(xué)生表1) 查詢?nèi)苛?例 查詢?nèi)w學(xué)生姓名 學(xué)號(hào) Select * From 學(xué)生表1) 查詢經(jīng)過計(jì)算的值 例 查詢?nèi)w學(xué)生姓名 及出生年份 Select 姓名，2017-年齡 From 學(xué)生表1) 消除取值重復(fù)的行

16、（distinct） 例 查詢選修了課程的學(xué)生學(xué)號(hào) 結(jié)果取消重復(fù)行 Select distinct 學(xué)號(hào) From 學(xué)生表1) 查詢滿足條件的元祖 （where子句）· 比較大小 （> = < 等） 例 查詢選修了課程為計(jì)算機(jī)的學(xué)生學(xué)號(hào) 結(jié)果取消重復(fù)行 Select distinct 學(xué)號(hào) From 學(xué)生表 Where 課程=計(jì)算機(jī)· 確定查找范圍（between and） 例 查詢成績?yōu)?0-90的學(xué)生學(xué)號(hào) 結(jié)果取消重復(fù)行 Select distinct 學(xué)號(hào) From 選課表 Where 成績between 80 and 90· 確定集合（in）

17、例 查詢成績?yōu)?0，90，100的學(xué)生學(xué)號(hào) 結(jié)果取消重復(fù)行 Select distinct 學(xué)號(hào) From 選課表 Where 成績in（80,90,100）· 字符匹配（like ） %：任意長度的字符串 _（下劃線）：任意單個(gè)字符 例 1查詢所有姓劉的學(xué)生姓名 學(xué)號(hào) 結(jié)果取消重復(fù)行 Select distinct 姓名，學(xué)號(hào) From 學(xué)生表 Where 姓名 like劉% 2查詢名字中第二個(gè)字是陽 學(xué)生姓名 學(xué)號(hào) 結(jié)果取消重復(fù)行 Select distinct 姓名，學(xué)號(hào) From 學(xué)生表 Where 姓名 like_陽%· 空值查詢（is null/not null

18、） 例 查詢成績?yōu)榭盏闹械诙€(gè)字是陽 學(xué)生姓名 學(xué)號(hào) 結(jié)果取消重復(fù)行 Select distinct 姓名，學(xué)號(hào) From 學(xué)生表 Where 姓名 like_陽%· 多重條件查詢（and or in）例 查詢成績?yōu)?0以上的女同學(xué)的姓名 學(xué)號(hào) 結(jié)果取消重復(fù)行 Select distinct 姓名，學(xué)號(hào) From 學(xué)生表，選課表 Where 學(xué)生表.學(xué)號(hào)=選課表.學(xué)號(hào) And 成績>=80 AND 性別=女6) 設(shè)置字段別名（as） 表達(dá)式 as 別名 或 字段別名 = 表達(dá)式例 查詢學(xué)生姓名 性別 年齡Select 姓名as學(xué)生姓名,性別,datediff（year，出生日期

19、，getdate（）as年齡From 學(xué)生表 或Select 學(xué)生姓名=姓名,性別,年齡=datediff（year，出生日期，getdate（）From 學(xué)生表2. Order by子句（對(duì)查詢結(jié)果默認(rèn)升序（asc）降序（desc）例 查詢選修了3號(hào)課程的學(xué)生學(xué)號(hào) 成績 結(jié)果按分?jǐn)?shù)降序排列 Select 成績，學(xué)號(hào) From 選課表 Where 課程號(hào)=3 Order by成績 desc2. 聚集函數(shù)（count 統(tǒng)計(jì)個(gè)數(shù)，sum 求和，avg 求平均數(shù)，max 最大數(shù)，min 最小數(shù)） 只能用于select，group by，having 子句中 不能用于where條件句中 例 查詢選修課

20、程號(hào)為1的學(xué)生最高成績 Select max（成績） From 選課表 Where 課程號(hào)=1 2. Group by . having子句（功能為分組） 例 1.求各個(gè)課程號(hào)及相應(yīng)的選課人數(shù) Select 課程號(hào)，count（學(xué)號(hào)） From 選課表 Group by 課程號(hào) 2.查詢選修了三門以上課程的學(xué)生學(xué)號(hào) Select 學(xué)號(hào) From 選課表 Group by 學(xué)號(hào) having count（*）> 3（having子句作用范圍為Group by后） 3.查詢平均成績大于等于90分的學(xué)生學(xué)號(hào) 和平均成績 Select 學(xué)號(hào)avg（成績） From 選課表 Group by 學(xué)號(hào)

21、 having avg（成績）>=90七多表查詢1. 表連接 用where子句連接兩個(gè)表Where 表一.列名(比較運(yùn)算符>,=,<)表2.列名 例 查詢每個(gè)學(xué)生及選修課程的情況（用到學(xué)生表，選課表） Select 學(xué)生表.*，選課表.*（如查詢的屬性在兩個(gè)表不唯一則不用加表前綴） From 學(xué)生表，選課表 where 學(xué)生表.學(xué)號(hào)=選課表.學(xué)號(hào)1. 多表連接例 查詢每個(gè)學(xué)生的學(xué)號(hào)，姓名，選修的課程名及成績（用到學(xué)生表，課程表選課表） Select 學(xué)生表.學(xué)號(hào)，姓名，課程名，成績（查詢的屬性在兩個(gè)表不唯一則不用加表前綴） From 學(xué)生表，課程表，選課表 where 學(xué)生表

22、.學(xué)號(hào)=選課表.學(xué)號(hào)and 課程表.課程號(hào)=選課表.課程號(hào)1. 帶in的子查詢（嵌套查詢） 例 查詢與劉晨在同一個(gè)系學(xué)習(xí)的學(xué)生的學(xué)號(hào)，姓名 Select 學(xué)號(hào)，姓名，系名 From 學(xué)生表 Where系名 in （ Select 系名 From 學(xué)生表 Where姓名=劉晨）1. 帶比較運(yùn)算符的子查詢（>,<,=,>=,<=,!=） 例 查詢與劉晨在同一個(gè)系學(xué)習(xí)的學(xué)生的學(xué)號(hào)，姓名 Select 學(xué)號(hào)，姓名，系名 From 學(xué)生表 Where系名 in （ Select 系名 From 學(xué)生表 Where姓名=劉晨）八建立索引1.建立索引 Create unique（唯一

23、）clustered ( 聚集 ) nonclustered ( 非聚集 ) Index 索引名 On （表/視圖）（列名asc（升序）desc（降序） With pad_indexfillfactor=填充因子drop_existing 例 在學(xué)生表上創(chuàng)建一個(gè)名為XS XM的非唯一性非聚簇索引 索引關(guān)鍵字為姓名 升序 填充因子為50% Create nonclustered Index XS XM On 學(xué)生表（姓名 asc） With fillfactor=502. 刪除索引 Drop index 表名.索引名 九視圖1. 定義視圖1) 建立視圖Create view 視圖名 列名As S

24、elect （在視圖后加上with check option子句 以后對(duì)該視圖進(jìn)行增刪改時(shí)則自動(dòng)添加） 例 建立信息系學(xué)生的視圖 Create view 信息系學(xué)生 As Select 學(xué)號(hào)，姓名，年齡 From 學(xué)生表 Where 系別=信息系1) 刪除視圖Drop view 視圖名（如選擇級(jí)聯(lián)刪除 視圖名后加cascade）例 刪除視圖BT_S和視圖IS_S1 Drop view BT_S Drop view IS_S1 （拒絕執(zhí)行 因?yàn)榕c另一個(gè)視圖有連接所以 后面加cascade）2. 查詢視圖！同表查詢 唯一區(qū)別在于from中添加的為視圖名！2. 更新視圖（指對(duì)視圖進(jìn)行增刪改，由于對(duì)視

25、圖修改最后都?xì)w結(jié)于對(duì)表的修改所以修改視圖數(shù)據(jù)=修改表數(shù)據(jù)）函數(shù)1、 標(biāo)量值 create function 函數(shù)名(變量 類型)returns 返回值類型beginreturnend例：給專業(yè)名求男女比例CREATE FUNCTION 某專業(yè)男女比例(專業(yè)名 varchar(20)RETURNS numeric(6,2)ASBEGINdeclare man intdeclare woman intselect man=count(*)from 學(xué)生表,專業(yè)表,班級(jí)表where 學(xué)生表.學(xué)號(hào)=班級(jí)表.班號(hào) and 班級(jí)表.專業(yè)=專業(yè)表.專業(yè)號(hào) and 性別='男'and 專業(yè)名=

26、專業(yè)名select woman=count(*)from 學(xué)生表,專業(yè)表,班級(jí)表where 學(xué)生表.學(xué)號(hào)=班級(jí)表.班號(hào) and 班級(jí)表.專業(yè)=專業(yè)表.專業(yè)號(hào) and 性別='女'and 專業(yè)名=專業(yè)名 return cast(man as float)/cast(woman as float)END執(zhí)行select dbo.某專業(yè)男女比例('數(shù)據(jù)庫')2、表值函數(shù) 給班名和課程名 求某班某課成績單CREATE FUNCTION 某班某課成績單(班名 char(20),課程名 varchar(20)RETURNS TABLE ASRETURN (select 班名

27、,學(xué)生表.學(xué)號(hào),姓名,課程名,成績from 學(xué)生表,課程表,班級(jí)表,選課表where 課程表.課程號(hào)=選課表.課程號(hào) and 學(xué)生表.學(xué)號(hào)=選課表.學(xué)號(hào) and 學(xué)生表.班號(hào)=班級(jí)表.班號(hào) and 班名=班名 and 課程名=課程名)GO 執(zhí)行select * from dbo.某班某課成績單('嵌入式班','java程序設(shè)計(jì)')3、CREATE FUNCTION 某班平均函數(shù)(班號(hào) char(5)RETURNS TABLE ASRETURN (SELECt 課程號(hào),avg(成績) 平均成績from 選課表,學(xué)生表where 學(xué)生表.學(xué)號(hào)=選課表.學(xué)號(hào) and

28、班號(hào) =班號(hào)group by 課程號(hào))GO調(diào)用select * from dbo.某班平均函數(shù)('1001')十存儲(chǔ)過程1.存儲(chǔ)過程創(chuàng)建與執(zhí)行 （1）創(chuàng)建存儲(chǔ)過程 Create procedure 存儲(chǔ)過程名 參數(shù) 數(shù)據(jù)類型 output（指明返回參數(shù)） As Begin Sql語句 End 說明：存儲(chǔ)過程可以帶參數(shù) 也可不帶 參數(shù)可以輸入 也可輸出 例1. 創(chuàng)建“學(xué)生名單”存儲(chǔ)過程 功能為輸出所有學(xué)生名單 Create procedure 學(xué)生名單 As Begin Select 學(xué)號(hào)，姓名 From 學(xué)生表 End 例2.創(chuàng)建“某班學(xué)生名單”存儲(chǔ)過程 其功能查看某個(gè)班級(jí)學(xué)生

29、名單 結(jié)果按學(xué)號(hào)排序 Create procedure 某班學(xué)生名單 班名 varchar （20） As Begin Select 學(xué)號(hào)，姓名 From 學(xué)生表，班級(jí)表 Where 學(xué)生表.班號(hào) = 班級(jí)表.班號(hào) and班名=班名 Order by 學(xué)號(hào) End 例3.創(chuàng)建“查詢學(xué)生”存儲(chǔ)過程 功能根據(jù)學(xué)號(hào)查詢學(xué)生姓名 年齡 Create procedure 查詢學(xué)生 學(xué)號(hào) char（11），姓名 nchar（5）output，年齡 int output As Begin Select 姓名 = 姓名，年齡=datediff（year，出生日期，getdate（） From 學(xué)生表 Wher

30、e 學(xué)號(hào)=學(xué)號(hào) End （2）執(zhí)行存儲(chǔ)過程 Execute 存儲(chǔ)過程名 參數(shù)=值 例1 查看學(xué)校學(xué)生名單 Execute 學(xué)生名單 例2 查看計(jì)算機(jī)應(yīng)用071班學(xué)生名單 Execute 某班學(xué)生名單 班級(jí)=計(jì)算機(jī)應(yīng)用071班 例3 查看學(xué)號(hào)為“”的學(xué)生 姓名 年齡 declare 姓名 nchar(5)declare 年齡 intexec 查詢學(xué)生 '',姓名 output, 年齡 output print 姓名print 年齡查看存儲(chǔ)過程 （1）查看存儲(chǔ)過程的定義 Exec sp_helptext “ ” （2）查看存儲(chǔ)過程的參數(shù)和數(shù)據(jù)類型信息 Exec sp_help “ ” （3）查看存儲(chǔ)過程的依賴關(guān)系 Exec sp_depends “ ”2. 修改存儲(chǔ)過程 Alter procedure 存儲(chǔ)過程名 參數(shù) 數(shù)據(jù)類型 output（指明返回參數(shù)） As Begin Sql語句 End 例 修改“學(xué)生名單”存儲(chǔ)過程 結(jié)果按學(xué)號(hào)排序 alter procedure 學(xué)生名單 As Begin Select 學(xué)號(hào)，姓名 From 學(xué)生表 Order by 學(xué)號(hào) End 2. 刪除存儲(chǔ)過程 Drop procedure