資料庫實驗系列之1資料庫及資料庫中表等資料庫對象的建立實驗（包括關係圖、完整性、許可權控制、視圖、索引等內容） ...
目錄
實驗1 SQL Server的安裝
- 實驗目的
- 掌握SQL Server安裝的硬體要求和系統要求。
- 熟悉SQL Server的安裝步驟。
- 掌握SQL Server的卸載方法。
- 瞭解SQL Server的主要組件。
- 掌握登錄和斷開資料庫伺服器的方法。
- 實驗內容
- 檢查電腦的軟、硬體配置（CPU、記憶體、硬碟和操作系統）是否達到SQL Server的安裝要求。
- 安裝SQL Server
- 雙擊安裝軟體中的“Setup”程式圖標，進入SQL Server安裝中心。
- 輸入有效的產品密鑰、接受許可條款，並且安裝“程式支持文件”。
- 根據需要選擇安裝組件。
- 進行伺服器配置，配置服務的賬戶、啟動類型、排序規則等。
- 進行資料庫引擎配置，配置資料庫管理員sa指定的密碼。
- 進行安裝配置規則的設置，確保狀態列為“已通過”。
- 進入正式安裝界面，等待安裝進度完畢。
- 查看“SQL Server Management Studio”“配置工具”和“導入和導出數據”等組件，並掌握其使用方法。
- 使用運行安裝軟體的方法或者通過控制面板卸載SQL Server
- 登錄和斷開資料庫伺服器。
- 分別使用Windows身份驗證方式和SQL Server身份驗證方式打開“SQL Server Management Studio”。
- 查看“對象資源管理器”是伺服器中所有資料庫對象的樹視圖。
- 斷開與資料庫伺服器的連接。
安裝過程展示
實驗2 設計資料庫、創建資料庫和數據表
- 實驗目的
- 掌握在SQL Server中使用對象資源管理器和SQL命令創建資料庫與修改資料庫的方法。
- 掌握在SQL Server中使用對象資源管理器或者SQL命令創建數據表和修改數據表的方法（以SQL命令為重點）。
- 實驗內容
給定如表3.6、表3.7和表3.8所示的學生信息。
表3.6 學生表
學號 姓名 性別 專業班級 出生日期 聯繫電話
0433 張艷 女 生物04 1986-9-13
0496 李越 男 電子04 1984-2-23 1381290××××
0529 趙欣 男 會計05 1984-1-27 1350222××××
0531 張志國 男 生物05 1986-9-10 1331256××××
0538 於蘭蘭 女 生物05 1984-2-20 1331200××××
0591 王麗麗 女 電子05 1984-3-20 1332080××××
0592 王海強 男 電子05 1986-11-1
表3.7 課程表
課程號 課程名 學分數 學時數 任課教師
K001 電腦圖形學 2.5 40 胡晶晶
K002 電腦應用基礎 3 48 任泉
K006 數據結構 4 64 馬躍先
M001 政治經濟學 4 64 孔繁新
S001 高等數學 3 48 趙曉塵
表3.8 學生作業表
課程號 學號 作業1成績 作業2成績 作業3成績
K001 0433 60 75 75
K001 0529 70 70 60
K001 0531 70 80 80
K001 0591 80 90 90
K002 0496 80 80 90
K002 0529 70 70 85
K002 0531 80 80 80
K002 0538 65 75 85
K002 0592 75 85 85
K006 0531 80 80 90
K006 0591 80 80 80
M001 0496 70 70 80
M001 0591 65 75 75
S001 0531 80 80 80
S001 0538 60 80
- 在SQL Server中使用對象資源管理器和SQL命令創建學生作業管理資料庫，資料庫的名稱自定。
- 使用對象資源管理器創建資料庫，請給出重要步驟的截圖。
- 刪除第（1）步創建的資料庫，再次使用SQL命令創建資料庫，請給出SQL代碼。
- 創建資料庫之後，如果有需要，可以修改資料庫。
- 對錶3.6，表3.7和表3.8，分別以下表的方式給出各欄位的屬性定義和說明。
欄位名 數據類型 長度或者精度 預設值 完整性約束 …… …… …… …… …… …… …… ……
- 使用SQL命令在學生作業管理資料庫中建立學生表、課程表和學生作業表，在實驗報告中給出SQL代碼。
- 在各個表中輸入表3.6、表3.7和表3.8中的相應內容。
SQL語句代碼
--使用SQL語句創建學生作業管理資料庫
Create database 學生作業管理資料庫;
--使用SQL語句創建表
use 學生作業管理資料庫;
Create table 學生表(
學號 int primary key not null,
姓名 nvarchar(20) not null,
性別 nchar(1) default '男' not null,
專業班級 nvarchar(20) not null,
出生日期 nvarchar(20) not null,
聯繫電話 nvarchar(20) not null,
);
--修改學生表聯繫電話可以為空
alter table 學生表
alter column 聯繫電話 nvarchar(20);
alter table 學生表
alter column 出生日期 nvarchar(20);
Create table 課程表(
課程號 nvarchar(20) primary key,
課程名 nvarchar(20)not null,
學分數 int not null,
學時數 int not null,
任課教師 nvarchar(20) not null,
);
alter table 課程表
alter column 學分數 float;
create table 學生作業表(
課程號 nvarchar(20) foreign key references 課程表(課程號),
學號 int foreign key references 學生表(學號),
作業1成績 int check (作業1成績>=0 and 作業1成績<=100),
作業2成績 int check (作業2成績>=0 and 作業2成績<=100) not null,
作業3成績 int check (作業3成績>=0 and 作業3成績<=100) not null,
);
alter table 學生作業表
alter column 作業2成績 int ;
--插入學生表的數據
insert into 學生表 (學號,姓名,性別,專業班級,出生日期)
values(0433,'張艷','女','生物04',1986-9-13);
--上邊寫錯出生日期，進行改正
update 學生表 set 出生日期='1986-9-13' where 學號=0433;
--繼續插入數據
insert into 學生表 (學號,姓名,性別,專業班級,出生日期,聯繫電話)
values(0496,'李越','男','電子04','1984-2-23','1381290××××');
insert into 學生表 (學號,姓名,性別,專業班級,出生日期,聯繫電話)
values(0529,'趙欣','男','會計05','1984-1-27','1350222××××');
insert into 學生表 (學號,姓名,性別,專業班級,出生日期,聯繫電話)
values(0531 ,'張志國','男','生物05','1986-9-10','1331256××××');
insert into 學生表 (學號,姓名,性別,專業班級,出生日期,聯繫電話)
values(0538,'於蘭蘭','女','生物05','1984-2-20', '1331200××××');
insert into 學生表 (學號,姓名,性別,專業班級,出生日期,聯繫電話)
values(0591 ,'王麗麗', '女', '電子05', '1984-3-20 ','1332080××××');
insert into 學生表 (學號,姓名,性別,專業班級,出生日期)
values(0592,'王海強','男','電子05','1986-11-1');
--查詢學生表，查看數據
select * from 學生表;
--為課程表插入數據
insert into 課程表(課程號,課程名,學分數,學時數,任課教師)
values ('K001', '電腦圖形學', 2.5, 40, '胡晶晶');
insert into 課程表(課程號,課程名,學分數,學時數,任課教師)
values ('K002' ,'電腦應用基礎', 3 ,48, '任泉');
insert into 課程表(課程號,課程名,學分數,學時數,任課教師)
values ('K006', '數據結構', 4, 64, '馬躍先');
insert into 課程表(課程號,課程名,學分數,學時數,任課教師)
values ('M001', '政治經濟學', 4 ,64 ,'孔繁新');
insert into 課程表(課程號,課程名,學分數,學時數,任課教師)
values ('S001', '高等數學', 3, 48 ,'趙曉塵');
--查詢課程表
select * from 課程表;
--為學生作業表插入數據
insert into 學生作業表(課程號,學號,作業1成績,作業2成績,作業3成績)
values('K001',0433, 60, 75 ,75);
insert into 學生作業表(課程號,學號,作業1成績,作業2成績,作業3成績)
values('K001', 0529 ,70, 70, 60);
insert into 學生作業表(課程號,學號,作業1成績,作業2成績,作業3成績)
values('K001', 0531, 70, 80 ,80),('K001', 0591, 80, 90, 90);
select * from 學生作業表;
--多行插入
insert into 學生作業表(課程號,學號,作業1成績,作業2成績,作業3成績)
values('K002',0496,80,80,90),
('K002',0529,70,70,85),
('K002',0531,80,80,80),
('K002',0538,65,75,85),
('K002',0592,75,85,85),
('K006',0531,80,80,90),
('K006',0591,80,80,80),
('M001',0496,70,70,80),
('M001',0591,65,75,75),
('S001',0531,80,80,80);
insert into 學生作業表(課程號,學號,作業1成績,作業3成績)
values('S001',0538,60,80);
--查詢學生作業表
select * from 學生作業表;
--實驗2第4問
select * from 課程表;
select * from 學生表;
select * from 學生作業表;
實驗5 視圖
- 實驗目的
- 掌握創建視圖的方法。
- 掌握修改視圖的方法。
- 掌握查詢視圖的方法。
- 掌握更新視圖的方法。
- 掌握刪除視圖的方法
- 實驗內容
根據第一部分實驗中創建的學生作業管理資料庫以及其中的學生表、課程表和學生作業表，進行以下操作。
- 創建一個電子05的學生視圖（包括學號、姓名、性別、專業班級、出生日期）。
- 創建一個生物05的學生作業情況視圖（包括學號、姓名、課程名、作業1成績、作業2成績、作業3成績）。
- 創建一個學生作業平均成績視圖（包括學號、作業1平均成績、作業2平均成績、作業3平均成績）。
- 修改第2題中生物05的學生作業情況視圖，將作業2成績和作業3成績去掉。
- 向電子05的學生視圖中添加一條記錄，其中學號為0596，姓名為趙亦，性別為男，專業班級為電子05，出生日期為1986-6-8（除了電子05的學生視圖發生變化之外，看看學生表中發生了什麼變化？）。
- 將電子05的學生視圖中趙亦的性別改為“女”（除了電子05的學生視圖發生變化之外，看看學生表中發生了什麼變化？）。
- 刪除電子05的學生視圖中趙亦的記錄。
- 刪除電子05的學生視圖（給出SQL語句即可）。
SQL語句代碼
--1．創建一個電子05的學生視圖（包括學號、姓名、性別、專業班級、出生日期）。
create view 電子05學生視圖
as
select 學號,姓名,性別,專業班級,出生日期 from 學生表 where 專業班級='電子05';
--2．創建一個生物05的學生作業情況視圖（包括學號、姓名、課程名、作業1成績、作業2成績、作業3成績）。
create view 生物05的學生作業情況
as
select 學生作業表.學號,姓名,課程名,作業1成績,作業2成績,作業3成績 from 學生作業表,學生表,課程表
where 學生作業表.學號=學生表.學號 and 課程表.課程號=學生作業表.課程號 and 學生表.專業班級='生物05';
--3．創建一個學生作業平均成績視圖（包括學號、作業1平均成績、作業2平均成績、作業3平均成績）。
create view 學生作業平均成績
as
select 學號,avg(作業1成績) as 作業1平均成績,avg(作業2成績) as 作業2平均成績,avg(作業3成績) as 作業3平均成績 from 學生作業表
group by 學號;
--4．修改第2題中生物05的學生作業情況視圖，將作業2成績和作業3成績去掉。
create view 生物05的學生作業情況修改 as
select 學號,姓名,課程名,作業1成績
from 生物05的學生作業情況;
--5．向電子05的學生視圖中添加一條記錄，其中學號為0596，姓名為趙亦，性別為男，專業班級為電子05，出生日期為1986-6-8
-- （除了電子05的學生視圖發生變化之外，看看學生表中發生了什麼變化？）。
insert into 電子05學生視圖(學號,姓名,性別,專業班級,出生日期) values
(0596,'趙亦','男','電子05','1986-6-8');
--查詢看一下視圖內容
select * from 電子05學生視圖;
select * from 學生表;
--6．將電子05的學生視圖中趙亦的性別改為“女”
--（除了電子05的學生視圖發生變化之外，看看學生表中發生了什麼變化？）。
update 電子05學生視圖 set 性別='女' where 姓名='趙亦';
--7．刪除電子05的學生視圖中趙亦的記錄。
delete from 電子05學生視圖
where 學號=596;
--8．刪除電子05的學生視圖（給出SQL語句即可）。
drop view 電子05學生視圖;
實驗6 完整性約束
針對實驗一（學生課程資料庫）或實驗二（學生課程作業資料庫）完成各種完整性約束的定義補充
SQL語句代碼
--實驗6
/*針對實驗一（學生課程資料庫）或實驗二（學生課程作業資料庫）完成各種完整性約束的定義補充
*/
--首先查詢一波看有什麼欄位
select * from 學生表;
select * from 課程表;
select * from 學生作業表;
--關於學生表
/*這是添加主鍵的SQL語句，如果已經有主鍵則添加不成功
alter table 學生表
add constraint py_key1 primary key(姓名);
*/
/*--這是刪除主鍵約束的SQL語句，如果no active則刪除不成功
alter table 學生表
drop constraint PK__學生表__1CC396D29329FDB5;
*/
--計劃將課程表的學時數定義為float 類型
alter table 課程表
alter column 學時數 float ;
--將學生作業表中3個作業成績都定義為float類型，值在0到100 可以為null
/*
alter table 學生作業表
alter column 作業1成績 float;
*/
--出錯原因:因為他有一個視圖，所以改不成
/*消息 5074，級別 16，狀態 1，第 25 行
對象'CK__學生作業表__作業1成績__33D4B598' 依賴於 列'作業1成績'。
消息 4922，級別 16，狀態 9，第 25 行
由於一個或多個對象訪問此 列，ALTER TABLE ALTER COLUMN 作業1成績 失敗。*/
實驗7 許可權管理
創建用戶、角色，用GRANT語句授權，用REVOKE語句撤銷授權，觀察對資料庫操作時許可權控制的情況
SQL語句代碼
use 學生作業管理資料庫;
--首先把兩個表創起來
create table P155_6_學生
(
學號 int constraint py_key_155_6_學生 primary key,
姓名 nvarchar(20),
年齡 int constraint check_155 check(年齡>0 and 年齡<120),
性別 nchar default '男',
家庭住址 nvarchar(20),
班級號 nvarchar(20)
);
CREATE TABLE P155_6_班級
(
班級號 int constraint py_key_155_6_班級 primary key,
班級名 nvarchar(20),
班主任 nvarchar(20),
班長 nvarchar(20)
);
--創建用戶U1、u2
create login U1 with password='123', default_database=學生作業管理資料庫;
create user U1 for login U1 with default_schema=dbo;
--開始授權
grant select
on P155_6_學生
To U1
with grant OPTION;
--解除查詢授權
revoke select on P155_6_學生 from U1 cascade;
--刪除資料庫用戶：
drop user U1
--刪除 SQL Server登陸帳戶：
drop login U1;
--正式開始
--先創個U1、U2
create login U1 with password='123', default_database=學生作業管理資料庫;
create user U1 for login U1 with default_schema=dbo;
create login U2 with password='123', default_database=學生作業管理資料庫;
create user U2 for login U2 with default_schema=dbo;
--開始授權
--1.授予U1兩表所有許可權，並可將許可權授予他人
--該語句是錯誤的，但是還沒有找到原因 grant all on P155_6_班級,P155_6_學生 to U1 ;with grant option;
exec sp_addrolemember 'db_owner', 'U1';
--grant all privileges on table P155_6_班級,P155_6_學生 to U1 with grant option;
--2.授予U2對學生表具有查看許可權，對家庭住址有更新許可權
grant select,update(家庭住址) on P155_6_學生 to U2;
--3.將對班級表查看許可權授予所有用戶
grant select on P155_6_學生 to public;
--4.將對學生表的查詢、更新許可權授予角色R1
create login R1 with password='123', default_database=學生作業管理資料庫;
create user R1 for login R1 with default_schema=dbo;
grant select,update on P155_6_學生 to R1;
--5.將角色R1授予用戶U1，並且U1可以繼續授予其他角色
--錯誤，目前還找不到原因 GRANT R1 TO U1 with admin option;
--第7題
create table P155_7_職工(
職工號 int constraint pk_1 primary key,
姓名 nvarchar(20),
年齡 int constraint ck_1 check(年齡 between 0 and 100),
職務 nvarchar(20),
工資 float check(工資>0),
部門號 int constraint fk_1 foreign key(部門號) references P155_7_部門(部門號)
);
create table P155_7_部門(
部門號 int constraint pk_2 primary key,
名稱 nvarchar(20),
經理名 nvarchar(20),
地址 nvarchar(20),
電話號 nvarchar(20)
);
--1.用戶王明對兩個表有select許可權
create login 王明 with password='123', default_database=學生作業管理資料庫;
create user 王明 for login 王明 with default_schema=dbo;
GRANT select on P155_7_部門 to 王明;
GRANT select on P155_7_職工 to 王明;
--2.用戶李勇對兩個表有insert和delete許可權
create login 李勇 with password='123',default_database=學生作業管理資料庫;
create user 李勇 for login 李勇 with default_schema=dbo;
GRANT insert,delete on P155_7_部門 to 李勇;
GRANT insert,delete on P155_7_職工 to 李勇;
--3.每個職工只對自己的記錄有select許可權
--4.用戶劉星對職工表有select許可權，對工資欄位有更新許可權
create login 劉星 with password='123',default_database=學生作業管理資料庫;
create user 劉星 for login 劉星 with default_schema=dbo;
GRANT select,update(工資) on P155_7_職工 to 劉星;
--5.用戶張新具有修改這兩個表的許可權
create login 張新 with password='123',default_database=學生作業管理資料庫;
create user 張新 for login 張新 with default_schema=dbo;
GRANT alter on P155_7_部門 to 張新;
GRANT alter on P155_7_職工 to 張新;
--6.用戶周平具有這兩個表的所有許可權，並可以授權其他用戶
create login 周平 with password='123',default_database=學生作業管理資料庫;
create user 周平 for login 周平 with default_schema=dbo;
--ALL 許可權已不再推薦使用，並且只保留用於相容性目的。它並不表示對實體定義了 ALL 許可權。
--GRANT all privileges on P155_7_部門 to 周平 with grant option;
--GRANT all on P155_7_職工 to 周平 with grant option;
--7.用戶楊蘭具有從每個部門職工中select最高工資，最低工資，平均工資的許可權，他不能查看每個人的工資
create login 楊蘭 with password='123',default_database=學生作業管理資料庫;
create user 楊蘭 for login 楊蘭 with default_schema=dbo;
create view P155
as
select 名稱,max(工資) as '最高工資',min(工資) as '最低工資',avg(工資) as '平均工資' from P155_7_職工 join
(select 部門號,名稱 from P155_7_部門) as 新命名錶 on P155_7_職工.部門號=新命名錶.部門號
group by 名稱;
GRANT select on P155 to 楊蘭;
附 創建market表，後續將會使用
創建market表,其中包含客戶表，商品表，訂單表
- 查找所以上海客戶的信息
- 查找所有商品的名稱、庫存量、價格、及折扣25%後的價格，並使用別名“Discount”標識折扣價，結果按價格由低到高排序
- 查找商品名中含“Computer”的商品的編號、名稱、及價格
- 查找庫存量大於100小於500的商品的名稱、庫存量和單價
- 查找2007年7月1日至2007年12月31日期間，訂貨金額大於30000的所有訂單的客戶姓名、商品名稱、單價、單貨數量、訂貨金額
SQL語句代碼
create database market;
use market;
create table Customers( --客戶表
CustomerID int constraint pk_1 primary key, --客戶編號為主鍵
Cname nvarchar(20) not null, --客戶姓名
City nvarchar(20) --所在城市
);
create table Goods( --商品表
GoodID int constraint pk_2 primary key, --商品編號為主鍵
Gname nvarchar(20) not null, --商品名稱
Price float not null, --單價
Provider nvarchar(20) not null,--供應商
Stocks int check(Stocks>=0), --庫存量
Status nvarchar(20) --商品狀態
);
create table Orders( --訂單表
OrderID int constraint pk_3 primary key, --訂單號
GoodID int constraint fk_1 foreign key references Goods(GoodID), --商品編號
CustomerID int constraint fk_2 foreign key references Customers(CustomerID),--客戶編號
Quantity int check(Quantity>0),--訂貨數量
OrderSum float ,--訂貨金額
Date nvarchar(20) --日期
);
--查找所以上海客戶的信息
select * from Customers where City='上海';
--查找所有商品的名稱、庫存量、價格、及折扣25%後的價格，並使用別名“Discount”標識折扣價，結果按價格由低到高排序
select Gname,Stocks,Price,Price*0.75 Discount from Goods order by Price asc;
--查找商品名中含“Computer”的商品的編號、名稱、及價格
select GoodID,Gname,Price from Goods where Gname like '%Computer%';
--查找庫存量大於100小於500的商品的名稱、庫存量和單價
select Gname,Stocks,Price from Goods where Stocks between 100 and 500;
--查找2007年7月1日至2007年12月31日期間，訂貨金額大於30000的所有訂單的客戶姓名、商品名稱、單價、單貨數量、訂貨金額
select Cname,Gname,Price,Quantity,OrderSum
from Customers,Goods,Orders
where Customers.CustomerID=Orders.CustomerID and Goods.GoodID=Orders.OrderID and
date between '2007-7-1' and '2007-12-31' and OrderSum>30000;
