目錄

第一關(guān)

相關(guān)知識(shí)

????????什么是表

????????????????操作數(shù)據(jù)表

????????創(chuàng)建數(shù)據(jù)表

????????插入數(shù)據(jù)

????????修改表結(jié)構(gòu)

????????刪除數(shù)據(jù)表

編程要求

第一關(guān)實(shí)驗(yàn)代碼：

第二關(guān)

相關(guān)知識(shí)

????????視圖是什么

????????????????視圖的優(yōu)缺點(diǎn)

????????????????視圖的優(yōu)點(diǎn)

????????????????視圖的缺點(diǎn)

操作視圖

創(chuàng)建視圖

通過視圖向基本表中插入數(shù)據(jù)

通過視圖修改基本表的數(shù)據(jù)

通過視圖刪除基本表的數(shù)據(jù)

????????修改視圖

????????刪除視圖

編程要求

測(cè)試說明

第二關(guān)實(shí)驗(yàn)代碼：

---

第一關(guān)

相關(guān)知識(shí)

什么是表

如果說數(shù)據(jù)庫是一個(gè)倉庫，那么表就是存放物品的架子。

數(shù)據(jù)表是數(shù)據(jù)庫中最基本的操作對(duì)象，通常把數(shù)據(jù)存放在數(shù)據(jù)庫中，其實(shí)就是將數(shù)據(jù)存放在數(shù)據(jù)庫中的一張表中，且一個(gè)數(shù)據(jù)庫可以包含一個(gè)或多個(gè)表。

數(shù)據(jù)表中的數(shù)據(jù)，按照行和列的規(guī)則，來進(jìn)行數(shù)據(jù)存儲(chǔ)。每一行，為一條數(shù)據(jù)記錄，一條數(shù)據(jù)記錄是由多個(gè)字段的描述信息組成的，每一列就是一個(gè)字段。

例如：有一個(gè)記錄了員工信息的 employee 表，每一列就是用來描述員工特定信息的字段，比如工號(hào)、姓名等等；每一行則用于描述某一員工的所有字段信息：工號(hào)、姓名、性別和學(xué)歷等，這些信息的集合稱之為一條記錄，如下圖所示。

操作數(shù)據(jù)表

SQL Server 提供了非常豐富的數(shù)據(jù)表操作方法，用戶可以通過企業(yè)管理器和 T-SQL 語言進(jìn)行操作。使用 T-SQL 操作數(shù)據(jù)表，具有靈活、快捷等特點(diǎn)，也是數(shù)據(jù)庫管理人員使用最多的一種方式。

對(duì)于數(shù)據(jù)表的操作主要分為：

• CREATE TABLE 創(chuàng)建數(shù)據(jù)表

• ALTER TABLE 修改數(shù)據(jù)表結(jié)構(gòu)

• DROP TABLE 刪除數(shù)據(jù)表

創(chuàng)建數(shù)據(jù)表

數(shù)據(jù)表的創(chuàng)建主要是對(duì)基本結(jié)構(gòu)的構(gòu)建。例如，列屬性的設(shè)定，數(shù)據(jù)完整性的約束。創(chuàng)建數(shù)據(jù)表使用 CREATE TABLE 語句，基本語法格式如下：

CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);

column_name ：列名；

data_type ：列的數(shù)據(jù)類型（例如 varchar 、 integer 、 decimal 、 date 等等）；

size ：列的最大長(zhǎng)度。

例如，使用下面的 CREATE TABLE 語句，創(chuàng)建一個(gè)名為 Persons 的表，包含五列 PersonID 、 LastName 、 FirstName 、 Address 和 City ：

CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

利用 select name, length, COLLATION from syscolumns where id=object_Id('Persons') 查看創(chuàng)建的 Persons 表結(jié)構(gòu)：

插入數(shù)據(jù)

創(chuàng)建完表后，可用 INSERT INTO 語句向表中插入數(shù)據(jù)。其語法如下：

INSERT INTO 表名稱 VALUES (值1, 值2,....)

我們也可以指定所要插入數(shù)據(jù)的列，如下：

INSERT INTO 表名稱 (列1, 列2,...) VALUES (值1, 值2,....)

例如，向 Persons 表中，插入 Tom 的個(gè)人信息，可以使用下列的語句完成。

INSERT INTO Persons VALUES('1001','Tom','Green','West Road','New York')

使用select * from Persons語句查看插入結(jié)果。

修改表結(jié)構(gòu)

在表已經(jīng)創(chuàng)建好的前提下，可以使用 ALTER TABLE 語句，對(duì)表中的列進(jìn)行增加、刪除或修改數(shù)據(jù)類型等操作。

• 新增列

ALTER TABLE table_name
ADD column_name datatype

使用如下語句，可以為數(shù)據(jù)表 Persons 添加一列 AddedCol ：

ALTER TABLE Persons
ADD AddedCol varchar(50)

新增列后的表結(jié)構(gòu)如下圖：

• 刪除列

ALTER TABLE table_name
DROP COLUMN column_name

例如，使用下列語句，可以刪除數(shù)據(jù)表 Persons 中的列 City ：

ALTER TABLE Persons
DROP COLUMN City

刪除 City 列后，表 Persons 的結(jié)構(gòu)如下圖：

• 修改列的數(shù)據(jù)類型

ALTER TABLE table_name
ALTER COLUMN column_name datatype

下列語句把數(shù)據(jù)表 Persons 中 AddedCol 列的數(shù)據(jù)類型改為 int 類型：

ALTER TABLE persons
ALTER COLUMN addedCol int

修改數(shù)據(jù)類型后的表結(jié)構(gòu)如下：

刪除數(shù)據(jù)表

刪除數(shù)據(jù)表是對(duì)數(shù)據(jù)庫中已建立的表進(jìn)行刪除，在刪除表的同時(shí)，會(huì)對(duì)表中定義的數(shù)據(jù)、索引和視圖進(jìn)行清除。在做任何刪除操作前，應(yīng)做好備份工作。我們使用 DROP TABLE 語句，對(duì)數(shù)據(jù)庫中的數(shù)據(jù)表進(jìn)行刪除，語法格式如下：

DROP TABLE table_name

編程要求

本次編程任務(wù)是：

• 在右側(cè)編輯區(qū) create table 下的 Begin-End 區(qū)域補(bǔ)全代碼，創(chuàng)建 Student 、 Course 和 Score 三個(gè)表；

• 在右側(cè)編輯區(qū) insert 下的 Begin-End 區(qū)域補(bǔ)全代碼，向 Score 表中插入下面給出的數(shù)據(jù)；

• 在右側(cè)編輯區(qū) alt student table 下的 Begin-End 區(qū)域補(bǔ)全代碼，修改 Student 表結(jié)構(gòu)，新增一列 addr 用于描述地址信息，并設(shè)置數(shù)據(jù)類型以及長(zhǎng)度為 varchar(256)，；

• 在右側(cè)編輯區(qū) del score table 下的 Begin-End 區(qū)域補(bǔ)全代碼，刪除 Score 表。

Student 表結(jié)構(gòu)如下：

字段名	數(shù)據(jù)類型	說明
sno	varchar(50)	學(xué)號(hào)
name	varchar(50)	姓名
sex	varchar(10)	性別
birthday	Date	出生日期
discipline	varchar(50)	專業(yè)
school	varchar(100)	學(xué)院

Course 表結(jié)構(gòu)如下：

字段名	數(shù)據(jù)類型	說明
cno	varchar(50)	課程號(hào)
cname	varchar(50)	課程名稱
description	varchar(256)	課程說明
credit	float	學(xué)分
cinstitution	varchar(128)	開課單位

Score 表結(jié)構(gòu)如下：

字段名	數(shù)據(jù)類型	說明
sno	varchar(50)	學(xué)號(hào)
cno	varchar(50)	課程號(hào)
grade	float	成績(jī)

Score 表待插入的數(shù)據(jù)：

學(xué)號(hào)	課程號(hào)	成績(jī)
09011101	101	89
09011101	102	78

第一關(guān)實(shí)驗(yàn)代碼：

USE studentdb
goSET NOCOUNT ON 
go
--********** create table **********--
--********** Begin **********--
create table student(sno varchar(50),name varchar(50),sex varchar(10),birthday Date,discipline varchar(50),school varchar(100)
);
create table course(cno varchar(50),cname varchar(50),description varchar(256),credit float,cinstitution varchar(128)
);
create table score(sno varchar(50),cno varchar(50),grade float
);--********** End **********--
go
select name, length, COLLATION from syscolumns where id=object_Id('student')
go
select name, length, COLLATION from syscolumns where id=object_Id('course')
go
select name, length, COLLATION from syscolumns where id=object_Id('score')
go--********** insert **********--
--********** Begin **********--
insert into score values('09011101','101',89);
insert into score values('09011101','102',78);--********** End **********--
select * from  score
go--********** alt student table **********--
--********** Begin **********--
ALTER TABLE Student 
ADD addr VARCHAR(256);--********** End **********--
goselect name, length, COLLATION from syscolumns where id=object_Id('student')
go--********** del score table **********--
--********** Begin **********--
drop table score;--********** End **********--
goGO
IF NOT EXISTS(Select 1 From Sysobjects Where Name='score')  --查詢表是否存在
BEGIN
PRINT 'NOT EXISTS TABLE score'
END
GO

第二關(guān)

相關(guān)知識(shí)

視圖是什么

視圖是一個(gè)虛擬表，其結(jié)構(gòu)和內(nèi)容是通過 SQL 查詢獲得的。用戶可以通過 SQL 查詢語句，像其他普通關(guān)系表一樣，對(duì)視圖中的數(shù)據(jù)進(jìn)行查詢。視圖同樣支持表的相關(guān)操作，并可以直接修改、添加、刪除數(shù)據(jù)庫中的真實(shí)數(shù)據(jù)。

視圖通常用來集中、簡(jiǎn)化和自定義不同用戶對(duì)數(shù)據(jù)庫的不同認(rèn)識(shí)，如視圖可用作安全機(jī)制。方法是只允許用戶由視圖訪問數(shù)據(jù)，而不授予用戶直接訪問數(shù)據(jù)表的權(quán)限。視圖還可以用于提供向后兼容接口，來模擬曾經(jīng)存在，但其架構(gòu)已更改的表。

視圖的優(yōu)缺點(diǎn)

視圖的優(yōu)點(diǎn)

• 安全保密。通過視圖，用戶只能查詢和修改他們所能見到的數(shù)據(jù)，對(duì)于數(shù)據(jù)庫中的其他數(shù)據(jù)，則既看不見也取不到。通過視圖，用戶可以被限制在數(shù)據(jù)的不同子集上。

• 查詢簡(jiǎn)單。視圖能夠從幾個(gè)不同的關(guān)系表中提取數(shù)據(jù)，并且用一個(gè)單表，表示出來。利用視圖，將多表查詢轉(zhuǎn)換成視圖的單表查詢。

• 結(jié)構(gòu)簡(jiǎn)單。視圖能夠給用戶一個(gè)“個(gè)性化”的數(shù)據(jù)庫結(jié)構(gòu)外觀，用一組用戶感興趣的可見表，來代表這個(gè)數(shù)據(jù)庫的內(nèi)容。

• 隔離變化。視圖能夠代表一個(gè)一致的、非變化的數(shù)據(jù)。即使是在作為視圖基礎(chǔ)的數(shù)據(jù)表（基表），被分割、重新構(gòu)造或者重命名的情況下，也是如此。

• 邏輯數(shù)據(jù)獨(dú)立。視圖可以使應(yīng)用程序和數(shù)據(jù)庫表在一定程度上獨(dú)立。如果沒有視圖，應(yīng)用一定是建立在表上。有了視圖之后，程序可以建立在視圖上，從而使程序獨(dú)立于數(shù)據(jù)表。

視圖的缺點(diǎn)

雖然視圖存在上述優(yōu)點(diǎn)，但在定義數(shù)據(jù)庫對(duì)象時(shí)，不能不加選擇地來定義視圖，因?yàn)橐晥D也存在一些缺點(diǎn)：

• 性能。 SQL Server 必須把視圖查詢轉(zhuǎn)化成對(duì)基本表的查詢，如果這個(gè)視圖是由一個(gè)復(fù)雜的多表查詢所定義，那么，即使是視圖的一個(gè)簡(jiǎn)單查詢， SQL Server 也要把它變成一個(gè)復(fù)雜的結(jié)合體，需要花費(fèi)一定的時(shí)間。

• 修改限制。當(dāng)用戶試圖修改視圖的某些信息時(shí)，數(shù)據(jù)庫必須把它轉(zhuǎn)化為對(duì)基本表的某些信息的修改，對(duì)于簡(jiǎn)單的視圖來說，這是很方便的，但對(duì)于比較復(fù)雜的視圖，可能是不可修改的。

操作視圖

創(chuàng)建視圖

使用 T-SQL 命令，創(chuàng)建視圖的基本語法如下：

--語法
CREATE VIEW view_name
AS
SELECT column_name(s) FROM table_name
WHERE condition

view_name ：指定創(chuàng)建視圖的名稱； column_name ：指定視圖中列名稱； table_name ：視圖的基礎(chǔ)表名稱； condition ： select 的限定條件。

下面是一個(gè)創(chuàng)建視圖的實(shí)例：

--準(zhǔn)備表和數(shù)據(jù)
create table studentTable(
id int identity(1,1) primary key,
name varchar(20),
gender char(2),
age int,
)insert into studentTable (name,gender,age)
values
('劉備','男',28),
('張飛','男',24),
('關(guān)羽','男',26);--刪除已存在的視圖student_view
if (exists (select * from sys.objects where name = 'student_view'))
drop view student_view
go--創(chuàng)建視圖student_view
create view student_view
as
select name,age from studentTable where age>24;--查詢視圖
select * from student_view;

通過創(chuàng)建的視圖查詢的結(jié)果：

通過視圖向基本表中插入數(shù)據(jù)

--通過視圖向基本表中插入數(shù)據(jù)
create view stu_insert_view(編號(hào),姓名,性別,年齡)
as
select id,name,gender,age from studentTable;
goselect * from studentTable;---插入一條數(shù)據(jù)
insert into stu_insert_view values('曹操2','男',40);----查看插入記錄之后表中的內(nèi)容。
select * from studentTable;

插入數(shù)據(jù)之前表中的數(shù)據(jù)如下：

通過視圖插入數(shù)據(jù)后，表中的數(shù)據(jù)如下：

通過視圖修改基本表的數(shù)據(jù)

--查看修改之前的數(shù)據(jù)
select * from studentTable;--修改數(shù)據(jù)
update student_view set age=30 where name='劉備';--查看修改后的數(shù)據(jù)
select * from studentTable;

修改視圖之前表 studentTable 中的數(shù)據(jù)如下：

通過視圖修改表 studentTable 中的數(shù)據(jù)后如下：

通過視圖刪除基本表的數(shù)據(jù)

通過視圖刪除基本表數(shù)據(jù)的語法如下：

delete view_name where condition;--查看表中源數(shù)據(jù)
select * from studentTable;--刪除記錄
delete student_view where name ='關(guān)羽';--通過視圖刪除之后的數(shù)據(jù)
select * from studentTable;

刪除之前的數(shù)據(jù)：

刪除之后的數(shù)據(jù)：

修改視圖

可以使用 ALTER VIEW 語句來對(duì)已有的視圖進(jìn)行修改。

ALTER VIEW view_name AS select_statement

語法說明如下：

• view_name ：指定視圖的名稱。該名稱在數(shù)據(jù)庫中必須是唯一的，不能與其他表或視圖同名。

• select_statement ：指定創(chuàng)建視圖的 SELECT 語句，可用于查詢多個(gè)基表或源視圖。

例如：

--修改視圖
alter view student_view
as
select name,age from studentTable where age>30;

此時(shí)再執(zhí)行select * from student_view會(huì)得到如下年齡大于 30 的記錄：

刪除視圖

可以使用 drop view 語句刪除視圖：

drop view view_name1,view_name2,......,view_nameN;
--該語句可以同時(shí)刪除多個(gè)視圖，只要在刪除各視圖名稱之間用逗號(hào)分隔即可。

例如

drop view student_view;

編程要求

本次編程任務(wù)是，在右側(cè)編輯區(qū) create score_view_cdept 下的 Begin-End 區(qū)域補(bǔ)全代碼，創(chuàng)建學(xué)生的成績(jī)單視圖，包括學(xué)號(hào) sno 、姓名 name 、課程名 cname 和成績(jī) grade 字段。

涉及到的數(shù)據(jù)庫和表都已經(jīng)建立好了，并插入了相應(yīng)的數(shù)據(jù)，具體如下： Student 表結(jié)構(gòu)如下：

sno	name	sex	birthday	discipline	school
s001	Tom	male	2019-06-01	computer	information
s002	Bob	male	2019-06-01	software	information
s003	Alice	female	2019-05-02	computer	information
Course 表結(jié)構(gòu)如下：

cno	cname	description	credit	cinstitution
c001	network	computer network description	2	College Of Information Engineering
c002	Java	java description	2	College Of Information Engineering

Score 表結(jié)構(gòu)如下：

sno	cno	grade
s001	c001	85
s002	c001	90

測(cè)試說明

平臺(tái)將對(duì)你補(bǔ)充的代碼進(jìn)行評(píng)測(cè)，輸出結(jié)果正確才可通關(guān)。

預(yù)期輸出：

---

第二關(guān)實(shí)驗(yàn)代碼：

USE studentdb
goSET NOCOUNT ON 
go--********** create score_view_cdept **********--
--********** Begin **********--
create view score_view_cdept
as
select student.sno,student.name,cname,grade
from student,course,score 
where student.sno=score.sno and score.cno=course.cno--********** End **********--
go