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