資料庫系統原理及應用
實驗指導書
(信管10級本科)
福建工程學院計算機與資訊科學系
計算機軟體與理論教研室
2023年9月
目錄實驗一資料定義語言 1
實驗二 sql sever中的單錶查詢 2
實驗三 sql serve中的連線查詢 3
實驗四 sql serve的資料更新、檢視 4
實驗五資料控制(完整性與安全性) 5
實驗六語法元素與流程控制 7
實驗七儲存過程與使用者自定義函式 9
實驗八觸發器 10
一、實驗目的
1. 熟悉sql server2000/2005查詢分析器。
2. 掌握sql語言的ddl語言,在sql server2000/2005環境下採用transact-sql實現表的定義、刪除與修改,掌握索引的建立與刪除方法。
3. 掌握sql server2000/2005實現完整性的六種約束。
二、實驗內容
1.建立資料庫:
1)在sql server2000中建立乙個studb資料庫:(請先在d盤下建立db資料夾)
有乙個資料檔案:邏輯名為studata,檔名為「d:\db\檔案初始大小為2mb,檔案的最大大小不受限制,檔案的增長率為2mb;
有乙個日誌檔案,邏輯名為stulog,檔名為「d:\db\檔案初始大小為1mb,檔案的最大大小為10mb,檔案的增長率為10%
2.設定studb為當前資料庫,在studb資料庫中作如下操作:
設有如下關係表s: s(class,sno, name, ***, age),
其中:class為班號,char(5) ;sno為座號,char(2);name為姓名,char(10),設姓名的取值唯一;***為性別,char(2) ;age為年齡,int,表中主碼為班號+座號。寫出實現下列功能的sql語句。
(1) 建立表s;
(2) 插入乙個記錄95031班25號李明,男性,21歲;
再插入乙個記錄95101班10號王麗,女性,20歲;
(3) 插入95031班座號為30,名為鄭和的學生記錄;
(4) 將年齡的資料型別改為smallint;
(5) 向s表新增「入學時間(comedate)」列,其資料型別為日期型(datetime);
(6) 對錶s,按年齡降序建索引(索引名為inxage);
(7) 刪除s表的inxage索引;
(8) 刪除s表;
(9) 按照《資料庫系統概論》(第四版)p56頁的學生-課程資料庫建立student、course和sc三張表,每一張表都必須有主碼約束,合理使用列級完整性約束和表級完整性。並輸入相關資料。
3. 按照《資料庫系統概論》(第四版)p74頁習題5的spj資料庫。建立spj資料庫,並在其中建立s、p、j和spj四張表。
每一張表都必須有主碼約束,合理使用列級完整性約束和表級完整性。
三、實驗要求:
1. 學會使用sql server2000/2005的查詢分析器,企業管理器和聯機叢書。
2. 根據實驗內容認真寫好實驗報告,記錄的實驗用例及相關資訊。
3. 報告內容:實驗內容1,2(1)~(8)
四、實驗總結
一、實驗目的:
1. 掌握sql語言的dml子語言,在sql server2000/2005環境下採用transact-sql的select語句熟練掌握各種單錶查詢方法。
二、實驗內容:
(一) 按照《資料庫系統概論》p82頁中的學生課程資料庫及資料實現以下查詢:
1.查詢所有課程的課號,課程名。
2.查詢計算機系年齡不到21歲的所有男生的姓名和出生年份。
3.查詢已被學生所選課程的課程號。
4.查詢所有有成績的學生學號和課程號,並為各列分別取相應的中文別名。
(二) 按照《資料庫系統概論》p75頁中的spj資料庫及資料實現以下查詢:
1.查詢地點在『北京』、『上海』的**商**和**商名字。
2.查詢**商名中包含『方』這個漢字的**商資訊。
3.查詢零件名以『刀』字結尾,包含三個漢字的零件資訊。
4.查詢使用了**商s1所**零件的工程專案數。
5.查詢各個**商所**的零件的總數,要求對查詢結果降序顯示。
6.查詢**了3個以上專案的**商**和**的專案總數。
三、實驗要求:
1. 寫出正確的transact-sql命令。
2. 無須記錄正確的查詢結果。但要求記錄實驗過程中發生的有學習意義的錯誤及錯誤資訊。
四、實驗小結
一、 實驗目的:
1. 掌握採用transact-sql實現連線查詢。
2. 掌握等值連線(自然連線)、自身連線、外連線和復合條件連線的查詢方法。
二、 實驗內容:
按照《資料庫系統概論》p82頁中的學生課程資料庫和p75頁中的spj資料庫完成以下查詢
(1) 查詢使用紅色零件的工程名稱。
(2) 查詢每個工程的資訊及相應的**資訊(包括列出尚未被**零件的那些工程)。
(3) 查詢**工程j1紅色零件的**商號sno,請用兩種方法實現。
(4) 求**工程j1零件的**商的完整資訊。
(5) 查詢使用北京**商**零件的工程資訊。
(6) 查詢選修3號課程且成績在85分以上的所有學生。
(7) 查詢先行課的學分為4的課程資訊。
(8) 查詢課程與其間接先行課的名稱。
(9) 查詢其他系中比電腦科學系所有學生年齡都小的學生完整資訊,用兩種方法實現。
(10) 查詢其他系中比電腦科學係某一學生年齡大的學生姓名與年齡,用兩種方法實現。
(11) 查詢至少用了**商s1所**的所有零件的工程號jno,用帶exists謂詞的子查詢實現。
(12) 查詢使用了全部零件的工程號jno,用帶exists謂詞的子查詢實現。
(13) 查詢所有使用了p3零件的工程號jno,用帶exists謂詞的子查詢實現。
三、實驗要求:
1. 寫出正確的transact-sql命令。
2. 無須記錄正確的查詢結果。要求記錄實驗過程中發生的有學習意義的錯誤及錯誤資訊。
四、實驗小結
一、實驗目的:
1. 掌握採用transact-sql實現巢狀查詢。
2. 掌握採用transact-sql實現資料更新。
3. 掌握採用transact-sql實現檢視的定義、刪除、查詢與更新。
二、實驗內容
(一)資料更新
1) 插入乙個學生:張紅,女,200215135,資訊系,20歲
2) 插入乙個選課錄:200215135,1,成績未定
3) 將計算機系所有學生的年齡加1歲
4) 刪除所有***學生的選課記錄
5) 刪除所有姓張的同學的資訊
6) 請自行設計案例對學生-課程資料庫的資料更新,並觀察是否有違反資料的完整性約束。
(二)檢視
1)建立乙個學生成績統計檢視,包括學號,姓名,選課門數,平均分,最高分,最低分
2)利用上題所建檢視實現:
①查詢成績高於自己平均成績的選課記錄
②查詢每個同學獲得最高分的選課記錄
3)請為三建工程專案建立乙個**情況的檢視,包括**商**(sno)、零件**(pno)、**數量(qty)。針對該檢視完成下列查詢:
①找出三建工程專案使用的各種零件**及其數量;
②找出**商s1的**情況;
三、實驗要求
1. 寫出正確的transact-sql命令。
2. 無須記錄正確的查詢結果。但要求記錄實驗過程中發生的有學習意義的錯誤及錯誤資訊。
四、實驗小結
一、實驗目的:
1. 通過實驗加深對資料庫完整性與資料庫安全性的理解;
2. 掌握sqlserver對資料進行安全性控制的方法
二、實驗內容和要求
(一)資料庫完整性
某公司使用資料庫進行內部管理:表employees儲存雇員的代號(整型,標識位,種子1000001,增量為1,主鍵)、身份證號碼(18個字元)、名字(最長20個字元)和工資等資訊;表departments儲存部門的部門號(2個字元,唯一,主鍵)、部門名稱(30個字元)等資訊;表work每一行表示某雇員在某部門工作過及其開始工作時間和備註。
請寫出建立這三個表的sql語句,要保證:工資的值大於0,身份證號碼唯一,開始工作時間非空,預設值為當前時間。
(二) 以sa登入查詢分析器,輸入下列**並執行
第1行 exec sp_addlogin 's1','123'
第2行 use studb
第3行 exec sp_grantdbaccess 's1','u1'
第4行 grant select,insert,update on student to public
第5行 grant all on student to u1
第6行 revoke select on student to u1
問:1)第1行建立了乙個名為s1登入帳戶,請問'123'的含義是什麼?
2)第3行將登入帳戶s1對映到資料庫使用者u1上,它將作為哪個資料庫的使用者?
3)請解釋4到6行的含義
4)在查詢分析器中以s1帳戶連線伺服器,能否對學生-課程資料庫的student表進行select操作,為什麼?
(提示:廢除許可權是刪除已授予的許可權,並不妨礙使用者、組或角色從更高階別繼承已授予的許可權。因此,如果廢除使用者檢視表的許可權,不一定能防止使用者檢視該錶,因為已將檢視該錶的許可權授予了使用者所屬的角色。
)5) 切換回sa使用者的查詢視窗,輸入下面**並執行
deny select on student to u1
請問該**含義?
再切換至s1的查詢視窗,能不能對student 表進行查詢操作?為什麼?
(三)請完成以下操作,並記錄完整語句。在實驗過程注意驗證語句的執行效果。
(1)需要為學生-課程資料庫,建立一使用者,該使用者以「sql server身份驗證」方式登入sql server 伺服器的賬號為學生自己的姓名,密碼為「123」。
(2)給該登入使用者對映到資料庫使用者user2上,並賦予該使用者對student表的sno、sname兩列的查詢許可權,對sc表的所有操作許可權及對course的查詢許可權。
《資料庫原理》實驗指導書
實驗一資料庫 表 索引的定義 一 實驗目的 通過本實驗加深對sql server 2000環境下資料定義語言的理解,能夠熟練地建立資料庫,並通過sql語句來建立和修改基本表 建立和取消索引。二 實驗內容 使用sql server 2000軟體,練習建立和修改資料庫 表 索引。三 實驗要求 建立乙個新...
《資料庫原理及應用》實驗指導
1 理學院信科08 2班陳先國 3080942214 實驗1 建立資料庫與資料表 下面寫出實現如下操作的sql語句 建立 系統 gyxt 資料庫。create database gyxt on name gyxt data,filename d size 10,資料庫原理及應用 實驗1 建立資料庫與...
資料庫原理實驗指導書2019
sql server 2000 左風朝孫濤王玉亭 計算機軟體教研室 2008.9 實驗1 建立基本表和資料完整性 實驗目的 1.掌握表 關係 和索引的建立方法 2.掌握表結構 關係模式 的修改方法 3.實踐dbms提供的資料完整性功能,加深對資料完整性的理解。實驗內容 1.在studentdb資料庫...