實驗1 SQL Server的安裝

• 實驗目的

1. 掌握SQL Server安裝的硬件要求和系統要求。
2. 熟悉SQL Server的安裝步驟。
3. 掌握SQL Server的卸載方法。
4. 了解SQL Server的主要組件。
5. 掌握登錄和斷開數據庫服務器的方法。

• 實驗內容

1. 檢查計算機的軟、硬件配置（CPU、內存、硬盤和操作系統）是否達到SQL Server的安裝要求。
2. 安裝SQL Server

1. 雙擊安裝軟件中的“Setup”程序圖標，進入SQL Server安裝中心。
2. 輸入有效的產品密鑰、接受許可條款，并且安裝“程序支持文件”。
3. 根據需要選擇安裝組件。
4. 進行服務器配置，配置服務的賬戶、啟動類型、排序規則等。
5. 進行數據庫引擎配置，配置數據庫管理員sa指定的密碼。
6. 進行安裝配置規則的設置，確保狀態列為“已通過”。
7. 進入正式安裝界面，等待安裝進度完畢。

• 查看“SQL Server Management Studio”“配置工具”和“導入和導出數據”等組件，并掌握其使用方法。
• 使用運行安裝軟件的方法或者通過控制面板卸載SQL Server
• 登錄和斷開數據庫服務器。

1. 分別使用Windows身份驗證方式和SQL Server身份驗證方式打開“SQL Server Management Studio”。
2. 查看“對象資源管理器”是服務器中所有數據庫對象的樹視圖。
3. 斷開與數據庫服務器的連接。

安裝過程展示

實驗2 設計數據庫、創建數據庫和數據表

• 實驗目的

1. 掌握在SQL Server中使用對象資源管理器和SQL命令創建數據庫與修改數據庫的方法。
2. 掌握在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

1. 在SQL Server中使用對象資源管理器和SQL命令創建學生作業管理數據庫，數據庫的名稱自定。

1. 使用對象資源管理器創建數據庫，請給出重要步驟的截圖。
2. 刪除第（1）步創建的數據庫，再次使用SQL命令創建數據庫，請給出SQL代碼。
3. 創建數據庫之后，如果有需要，可以修改數據庫。

2. 對表3.6，表3.7和表3.8，分別以下表的方式給出各字段的屬性定義和說明。

字段名 數據類型 長度或者精度 默認值 完整性約束 …… …… …… …… …… …… …… ……

3. 使用SQL命令在學生作業管理數據庫中建立學生表、課程表和學生作業表，在實驗報告中給出SQL代碼。
4. 在各個表中輸入表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 視圖

• 實驗目的

1. 掌握創建視圖的方法。
2. 掌握修改視圖的方法。
3. 掌握查詢視圖的方法。
4. 掌握更新視圖的方法。
5. 掌握刪除視圖的方法

• 實驗內容
  根據第一部分實驗中創建的學生作業管理數據庫以及其中的學生表、課程表和學生作業表，進行以下操作。

1. 創建一個電子05的學生視圖（包括學號、姓名、性別、專業班級、出生日期）。
2. 創建一個生物05的學生作業情況視圖（包括學號、姓名、課程名、作業1成績、作業2成績、作業3成績）。
3. 創建一個學生作業平均成績視圖（包括學號、作業1平均成績、作業2平均成績、作業3平均成績）。
4. 修改第2題中生物05的學生作業情況視圖，將作業2成績和作業3成績去掉。
5. 向電子05的學生視圖中添加一條記錄，其中學號為0596，姓名為趙亦，性別為男，專業班級為電子05，出生日期為1986-6-8（除了電子05的學生視圖發生變化之外，看看學生表中發生了什么變化？）。
6. 將電子05的學生視圖中趙亦的性別改為“女”（除了電子05的學生視圖發生變化之外，看看學生表中發生了什么變化？）。
7. 刪除電子05的學生視圖中趙亦的記錄。
8. 刪除電子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表,其中包含客戶表，商品表，訂單表

1. 查找所以上?？蛻舻男畔?/li>
2. 查找所有商品的名稱、庫存量、價格、及折扣25%后的價格，并使用別名“Discount”標識折扣價，結果按價格由低到高排序
3. 查找商品名中含“Computer”的商品的編號、名稱、及價格
4. 查找庫存量大于100小于500的商品的名稱、庫存量和單價
5. 查找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;