1、數(shù)據(jù)庫原理及應(yīng)用 - SQL Server 實驗指導(dǎo)書 2015 目錄實驗一 使用向?qū)?chuàng)建和刪除數(shù)據(jù)庫3實驗二 用向?qū)Ш蚐QL語句創(chuàng)建和刪除表7實驗三 修改數(shù)據(jù)庫基本表的定義10實驗四 簡單SELECT 語句14實驗五 子查詢15實驗六 使用子句的SELECT 語句16實驗七 更新語句22實驗一 使用向?qū)?chuàng)建和刪除數(shù)據(jù)庫實驗內(nèi)容 設(shè)有一學(xué)籍管理系統(tǒng)，其數(shù)據(jù)庫名為“EDUC”，初始大小為 10MB，最大為50MB，數(shù)據(jù)庫自動增長，增長方式是按5比例增長；日志文件初始為2MB，最大可增長到5MB，按1MB 增長。數(shù)據(jù)庫的邏輯文件名為“student_data”, 物理文件名為“student_da

2、ta.mdf，存放路徑為“D:sql_data”。日志文件的邏輯文件名為“student_log”, 物理文件名為“student_log.ldf”，存放路徑為“D:sql_data”。 1 使用向?qū)?chuàng)建上訴描述的數(shù)據(jù)庫。使用SQL Server Management Studio（簡稱SSMS）創(chuàng)建數(shù)據(jù)庫。 實驗指導(dǎo)1 實驗指導(dǎo)使用向?qū)?chuàng)建和刪除數(shù)據(jù)庫 2 (1) 使用向?qū)?chuàng)建上訴描述的數(shù)據(jù)庫。 使用SQL Server Management Studio（簡稱SSMS）創(chuàng)建數(shù)據(jù)庫。 1啟動SSMS 在開始菜單中：所有程序SQL Server 2005 SQL Server Manageme

3、nt Studio 單擊“連接”按鈕，便可以進(jìn)入【SQL Server Management Studio】窗口。如果身份驗證選擇的是“混合模式”，則要輸入sa 的密碼。 2建立數(shù)據(jù)庫 在“對象資源管理器”窗口，建立上述數(shù)據(jù)庫EDUC。在數(shù)據(jù)庫節(jié)點上右擊選擇新建。同時建立一個同樣屬性的數(shù)據(jù)庫EDUC1。 3.修改數(shù)據(jù)庫名，數(shù)據(jù)庫邏輯名，初始大小，增長方式等 實驗二用向?qū)Ш蚐QL語句創(chuàng)建和刪除表實驗內(nèi)容 1. 在實驗一建立的數(shù)據(jù)庫EDUC 中，根據(jù)分析需要如下幾個表： 表2.1 student 表（學(xué)生信息表） 表2.2 course 表（課程信息表） 表2.3 student_course 表

4、（學(xué)生選課成績表） 表2.4 teacher 表（教師信息表） 表2.5 teacher_course 表（教師上課課表） 2. 根據(jù)如上表結(jié)構(gòu)用向?qū)?chuàng)建各表。 3. 用企業(yè)管理器刪除所建立的表Student_course,Student 和Course 4. 在查詢分析器中用sql 語句刪除表Tearch_course 和表Teacher 5. 根據(jù)如上表結(jié)構(gòu)用SQL 語句創(chuàng)建各表。 6. 將以上創(chuàng)建表的SQL 命令以 .SQL文件的形式保存在磁盤上 實驗指導(dǎo) 實驗指導(dǎo)分別用向?qū)Ш蚐QL 語句創(chuàng)建和刪除表 1,2.在實驗一建立的數(shù)據(jù)庫EDUC 中，根據(jù)分析需要建立幾個表 步驟：右鍵單擊EDU

5、C 的子目錄“表”，選擇“新建表”，在新表中輸入“字段名稱”，選擇“類型”與“寬度”，并設(shè)置“sno“為主鍵，右鍵單擊“sno”, 單擊“設(shè)置主鍵”，關(guān)閉，保存表名為“student” 同理可建立表student_course，teacher，teacher_course。 3.根據(jù)如上表結(jié)構(gòu)用SQL 語句創(chuàng)建各表。 例如創(chuàng)建student 表代碼如下 create table student( sno char(8) NOT NULL, sname char(8) NOT NULL, sex char(2) NULL, birthday smalldatetime NULL, dno cha

6、r(6) NULL, spno char(4) NULL, clssno char(4) NULL, entime smalldatetime NULL, home varchar(40) NULL, tel varchar(40) NULL, constraint pk_student primary key (sno) ) 在查詢分析器中運行以上語句可以創(chuàng)立student，同理可建立表student_course，teacher，teacher_course。實驗三 修改數(shù)據(jù)庫基本表的定義實驗內(nèi)容 1 修改列屬性 （1） 用企業(yè)管理器將Student 表中的birthday 字段設(shè)為不能為

7、空（not null）。 （2） 用SQL 語句將Student 表中的屬性sno char(8)改成varchar(20)類型。2 添加列 （1） 用企業(yè)管理器在Course 表中添加一列year ，類型為varchar（4）， 默認(rèn)置為空。 （2） 用SQL 語句在year 字段添加約束，year 的屬性值在2004-2008 之間。3 刪除列 語句將Course 表中的year 字段刪除。 實驗指導(dǎo) 實驗指導(dǎo)修改數(shù)據(jù)庫基本表的定義 1. 修改列屬性 步驟1：用企業(yè)管理器將Student 表中的birthday 字段設(shè)為不能為空（not null）。 方法：右鍵點擊表student，單擊“

8、修改”，將birthday 屬性的“允許空”修改為“否” 步驟2：由于sno 為主鍵，要用SQL 語句將Student 表中的屬性sno char(8)改成varchar(20)類型，則需要現(xiàn)刪除主鍵，修改后在添加主鍵 代碼： USE EDUC GO alter table student drop constraint PK_student alter table student alter column sno varchar(20) not null alter table student add constraint PK_student primary key (sno) 2. 添加

9、列 子目錄，右鍵點擊-新建列，加入year 和類型 步驟：新建查詢，在查詢編輯窗口輸入： Use educ ALTER TABLE course ADD CONSTRAINT year CHECK(year BETWEEN 2004 AND 2008) 然后執(zhí)行。 3. 刪除列 步驟：新建查詢，在查詢編輯窗口輸入 USE EDUC GO ALTER TABLE dbo.course DROP CONSTRAINT year /*去掉外鍵約束*/ alter table course drop column year 實驗四 簡單SELECT 語句語句實現(xiàn) 1. 簡單查詢操作 此部分查詢包括投影

10、、選擇條件表達(dá)、數(shù)據(jù)排序、使用臨時表等。 對EDUC 數(shù)據(jù)庫實現(xiàn)以下查詢： 1 求計算機系的學(xué)生學(xué)號和姓名； 2 求選修了課程的學(xué)生學(xué)號； 3 求選修C1 課程的學(xué)生學(xué)號和成績，并要求對查詢結(jié)果按成績的降序排列，如果成績相同則按學(xué)號的升序排列； 4 求選修課程C1 且成績在8090 之間的學(xué)生學(xué)號和成績，并將成績乘以系數(shù)0.75 輸出； 5 求計算機系和數(shù)學(xué)系的姓張的學(xué)生的信息； 6 求缺少了成績的學(xué)生的學(xué)號和課程號。 7 將2000 以后的成績大于90 分的學(xué)生成績存入永久成績表；將2000年以前的成績存入臨時成績表中。 2. 連接查詢操作 對EDUC 數(shù)據(jù)庫實現(xiàn)以下查詢： 1 查詢每個學(xué)生

11、的情況以及他（她）所選修的課程； 2 求學(xué)生的學(xué)號、姓名、選修的課程名及成績； 3 求選修C1 課程且成績在90 分以上的學(xué)生學(xué)號、姓名及成績； 4 查詢每一門課的間接先行課。 51實驗五 子查詢 實驗內(nèi)容實現(xiàn)查詢： 1）求選修了高等數(shù)學(xué)的學(xué)生學(xué)號和姓名； 2）求C1 課程的成績高于張三的學(xué)生學(xué)號和成績； 3）求其他系中比計算機系某一學(xué)生年齡小的學(xué)生信息（即求其它系中年齡小于計算機系年齡最大者的學(xué)生）； 4）求其他系中比計算機系學(xué)生年齡都小的學(xué)生信息； 5）求選修了C2 課程的學(xué)生姓名；6）求沒有選修C2 課程的學(xué)生姓名； 實驗六 使用子句的SELECT 語句實驗內(nèi)容 所有查詢都全部用SQL

12、語句實現(xiàn) 1. 在數(shù)據(jù)庫EDUC 中實現(xiàn)如下查詢 1)求學(xué)生的總?cè)藬?shù)。 2)求選修了課程的學(xué)生人數(shù)。 3)求課程的課程號和選修該課程的人數(shù)。 4)求選修課超過3 門課的學(xué)生學(xué)號 2. 提高操作實驗 建立“圖書_讀者”數(shù)據(jù)庫及如下3 個表，并輸入實驗數(shù)據(jù)，用SQL 語句實現(xiàn)如下五個查詢： 1）查找這樣的圖書類別：要求類別中最高的圖書定價不低于全部按類別分組的圖書的平均定價的2 倍。 2）求機械工業(yè)出版社出版的各類圖書的平均定價，分別用GROUP BY 和GROUP BY ALL 表示。 3）列出計算機類圖書的書號、名稱及價格，最后求出冊數(shù)和總價格。 4）列出計算機類圖書的書號、名稱及價格，并求出

13、各出版社這類書的總價格，最后求出全部冊書和總價格。 5）查詢計算機類和機械工業(yè)出版社出版的圖書。 表結(jié)構(gòu)如下： 圖書： 列名 類型 長度 是否可為空 書號 char 10 類別 char 12 是 出版社 char 50 作者 char 20 是 書名 char 50 定價 money 8 是 讀者： 列名 類型 長度 是否可為空 編號 char 10 姓名 char 8 單位 char 50 是 性別 char 2 是 電話 char 15 是 借閱： 列名 類型 長度 是否可為空 串號 char 10 書號 char 10 讀者編號 char 10 借閱日期 datetime 8 要求：

14、1 圖書表：書號為主碼 2 讀者表：編號為主碼； 性別只能是“男”或“女”的CHECK 約束 3 借閱表：串號為主碼； 48 書號為外碼，參照表、列是圖書表的書號列； 讀者編號為外碼，參照表、列是讀者表的編號列； 書號和讀者編號的聯(lián)合UNIQUE 約束 實驗數(shù)據(jù)： 圖書： 書號 類別 出版社 作者 書名 定價 1001 計算機 機械工業(yè)出版社 王民 數(shù)據(jù)結(jié)構(gòu) 80 1002 計算機 機械工業(yè)出版社 張建平 計算機應(yīng)用 20 1003 計算機 電子工業(yè)出版社 王敏 數(shù)據(jù)庫技術(shù) 15 1004 計算機 電子工業(yè)出版社 譚浩強 C 語言 25 1005 英語 中國人民大學(xué)出版社 張錦芯 應(yīng)用文寫作

15、25 1006 管理 高等教育出版社 Robison 管理學(xué) 15 1007 管理 機械工業(yè)出版社 Fayol 工業(yè)管理 70 1008 數(shù)學(xué) 機械工業(yè)出版社 李平 線性代數(shù) 50 1009 管理 機械工業(yè)出版社 Durark 公司的概念 14 1010 數(shù)學(xué) 機械工業(yè)出版社 徐新國 統(tǒng)計學(xué) 15 讀者： 編號 姓名 單位 性別 電話 1001 丁一 數(shù)學(xué)院 男 1002 趙二 經(jīng)濟學(xué)院 男 1003 張三 管理學(xué)院 女 1004 李四 文學(xué)院 男 1005 王五 歷史文化學(xué)院女 1006 孫六 物理學(xué)院 男 1007 周七 生命科學(xué)院 女 1008 徐八 化學(xué)院 男 1009 宋九 信息學(xué)院

16、 女 1010 劉十 計算機學(xué)院 女 借閱： 串號 書號 讀者編號 借閱日期 01 1001 1003 2000-1-1 02 1002 1005 2002-3-5 03 1003 1008 1998-6-18 04 1004 1003 1997-12-8 05 1005 1010 2001-5-4 06 1006 1001 2005-7-25 07 1007 1010 997-11-3 08 1008 1009 2004-2-1 09 1009 1004 1996-9-1 10 1010 1008 2000-6-4 實驗指導(dǎo)使用子句的SELECT 語句 要求： 圖書表：書號為主碼 讀者表：編

17、號為主碼； 性別只能是“男”或“女”的CHECK 約束 步驟1：右鍵點擊性別“CHECK約束” 步驟2：“添加”-在表達(dá)式欄目中填入“性別 in (男,女)” 借閱表：1 串號為主碼； 2 書號為外碼，參照表、列是圖書表的書號列； 步驟1：右鍵點擊“書號”，選擇“關(guān)系” 步驟2：點擊“添加”，將選擇外鍵為“書號” 3 讀者編號為外碼，參照表、列是讀者表的編號列；（參照2） 4 書號和讀者編號的聯(lián)合UNIQUE 約束 步驟1：選中“書號”和“讀者編號”右鍵點擊“索引/鍵” 步驟2：將唯一性改為“是”，關(guān)閉并保存 實驗七 更新語句實驗內(nèi)容 1.對于student 表，將所有專業(yè)號為001的，并且入

18、學(xué)年份為2006 的學(xué)生，或是專業(yè)號為003，并且年齡小于20 歲的學(xué)生的班級號改為001。2對于student 表，刪掉所有年齡小于20 歲，并且專業(yè)號為003的學(xué)生的記錄。3對于student 表，插入一條新記錄，它的具體信息為，學(xué)號：、姓名：張三、性別：男、出生日期：、院系編號：001、專業(yè)編號：001、班級號：001、入學(xué)時間：。4對于student 表，將入學(xué)時間最晚的學(xué)生和年齡最小的學(xué)生的聯(lián)系方式去掉。5對于student 表，將平均年齡最小的一個院系的院系編號改為008。 實驗指導(dǎo)更新語句 對于student 表，將所有專業(yè)號為001的，并且入學(xué)年份為2006 的學(xué)生，或是專業(yè)號為003，并且年齡小于20 歲的學(xué)生的班級號改為001。步驟：新建查詢-輸入代碼: use educ