数据库实验 认识DBMS+交互式SQL
一、实验环境
本实验的实验环境为SQL Sever 2008 R2。
实验一 认识DBMS
1.安装
安装SQL Server数据库及相关组件。
安装成功后的界面显示如图1.1。
2.创建用户、赋予权限
利用Management Studio创建一个用户，赋予DBA权限（sysadmin角色）。
（1）先用windows身份验证模式登陆SQL Sevser 2008 R2。
（2）登陆成功之后，开“安全性”，右击“登录名”，在弹出的菜单中点击“新建登录名”。
（3）在“常规”选项中，进行设置，输入登录名，选择Sql Server 身份验证，并输入登录密码；强制密码过期等设置，可以有单独需求的可勾选。
（4）在“服务器角色”选项中，在右侧的服务器角色面板中，勾选 public 和 sysadmin 两项，这是常用的两项，如果有其他需求自己操作。
(5)“用户映射”，在右侧的面板中勾选该账号可以进行管理操作的数据库名称，并在该面板下面的“数据库角色成员身份”中勾选 db_owner 项。
（6）在“状态”选项卡中，进行权限设置。
（7）用新添加的用户，尝试登陆，看是否成功。
（8）已经登录成功。
3.服务的启动和停止
利用Configuration Manager启动和停止数据库服务，并验证效果。
（1）如图3.1，首先在开始菜单中找到Configuration Manager，找到SQL Sever服务，右键选择停止。
（2）右键选择启动。
4.创建数据库和表
利用Management Studio创建选课数据库和Student表，定义表的属性并输入数据（可创建自己设计的数据库和表）。
（1）创建数据库
在数据库上右键选择新建数据库，进入图4.1的界面，填写数据库名称。
（2）创建表，定义表的属性并输入数据。
在数据库中找到“学生-课程数据库”，在该数据库内找到表，右键点击新建表（如图4.2）。随后进入界面，定义表的属性，并命名为Student表（如图4.3），然后在这个新表上右键选择编辑前200行（如图4.4），进入新界面，导入数据（如图4.5），完成后，按同样的方法建立Course表（如图4.6）。
实验二 交互式SQL
1.数据定义
（1）模式的创建和删除
首先是模式的创建。运用
CREATE SCHEMA <模式名> AUTHORIZATION <用户名>;
语句，创建模式。
如果没有指定<模式名>，那么<模式名>隐含为<用户名>。要创建模式，调用该命令的用户必须拥有数据库管理员权限，或者获得了数据库管理员授予的CREATE SCHEMA的权限。
随后，查验模式，在安全性-用户中找到数据库用户lyx，右键选择属性，如图2.1.2，在架构中可以找到test模式。
然后是模式删除。运用
DROP SCHEMA <模式名><CASCADE|RESTRICT>;
语句（其中CASCADE和RESTRICT二者必选其一），删除模式。
其中，选择了CASCADE（级联），表示在删除模式的同时把该模式中所有的数据库对象全部删除；选择了RESTRICT（限制），表示如果该模式中已经定义了下属的数据库对象（如表、视图等），则拒绝该删除语句的执行。只有当该模式中没有任何下属对象时才能执行DROP SCHEMA语句。
随后，查验模式，在安全性-用户中找到数据库用户lyx，右键选择属性，如图2.1.4，在架构中已经无法找到test模式。
（2）基本表的创建、修改和删除
这里我创建了新数据库G.E.M.，在该数据库中进行基本表的操作。
①定义基本表
SQL语言使用CREATE TABLE语句定义基本表，其基本格式如下：
CREATE TABLE <表名> (<列名><数据类型>[列级完整性约束条件]
[,<列名><数据类型>[列级完整性约束条件]]
…
[,<表级完整性约束条件>]);
建表的同时通常还可以定义与该表有关的完整性约束条件，这些完整性约束条件被存入系统的数据字典中，当用户操作表中数据时由关系数据库管理系统自动检查该操作是否违背这些完整性约束条件。如果完整性约束条件涉及该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。
下面建立了一个“专辑”表Albums，一个“歌曲”表Songs，一个“评价”表Evaluation。
系统执行该CREATE TABLE语句后，就在数据库建立一个新的空的“专辑”表Albums，并将有关“专辑”表的定义及有关约束条件存放在数据字典中。
②修改基本表
随着应用环境和应用需求的变化，有时需要修改已建立好的基本表。SQL语言用ALTER TABLE语句修改基本表，其一般格式为
ALTER TABLE<表名>
[ADD [COLUMN] <新列名><数据类型> [完整性约束]]
[ADD <表级完整性约束>]
[DROP [COLUMN] <列名> [CASCADE|RESTRICT]]
[DROP CONSTRAINT<完整性约束名> [RESTRICT|CASCADE ]]
[ALTER COLUMN <列名><数据类型>];
其中，<表名>时要修改的基本表，ADD子句用于增加新列、新的列级完整性约束条件和新的表级完整性约束条件。DROP COLUMN子句用于删除表中列，如果指定了CASCADE短语，则自动删除引用了该列的其他对象，比图视图；如果指定了RESTRICT短语，则如果该列被其他对象引用，RDBMS将拒绝删除该列。DROP CONSTRAINT子句用于删除指定的完整性约束条件，ALTER COLUMN子句用于修改原有的列定义，包括修改列名和数据类型。
我的应用：
1.向Albums表增加“评分”列，其数据类型为单精度浮点数型。
ALTER TABLE Albums ADD Ascore REAL;
③删除基本表
当某个基本表不再需要时，可以使用DROP TABLE语句删除它。其一般格式为：
DROP TABLE<表名> [RESTRICT|CASCADE];
若选择RESTRICT，则该表的删除是有限制条件的。欲删除的基本表不能被其他表的约束所引用（如CHECK，FOREIGN KEY等约束），不能有视图，不能有触发器（trigger），不能有存储过程或函数。如果存在这些依赖该表的对象，则此表不能被删除。
默认情况是RESTRICT。
我的应用：
删除Evaluation表
DROP TABLE Evaluation
（3）索引的创建和删除
当表的数据量比较大时，查询操作会比较耗时。建立索引是加快查询速度的有效手段。数据库索引类似于图书后面的索引，能快速定位到需要查询的内容。用户可以根据应用环境的需要在基本表上建立一个或多个索引，以提供多种存取路径，加快查找速度。
①建立索引
在SQL语言中，建立索引使用CREATE INDEX语句，其一般格式为
CREATE [UNIQUE] [CLUSTER] INDEX <索引名>
ON<表名>(<列名>[<次序>][,<列名>[<次序>]] …);
其中，<表名>是要建索引的基本表的名字。索引可以建立在该表的一列或多列上，各列名之间用逗号分隔。每个<列名>后面还可以用<次序>指定索引值的排列次序，可选ASC（升序）或DESC（降序），默认值为ASC。
UNIQUE表明此索引的每一个索引值只对应唯一的数据记录。
CLUSTER表示要建立的索引是聚簇索引。
例如，为G.E.M.数据库中的Albums表按序号升序建立唯一索引。（注：我在建立索引前已经提前手动输入数据）
CREATE UNIQUE INDEX LYXDZQ ON Albums(Asequence);
②删除索引
索引一经建立就由系统使用和维护，不需用户干预。建立索引是为了减少查询操作的时间，但如果数据增、删、改频繁，系统会花费许多时间来维护索引，从而降低查询效率。这时可以删除一些不必要的索引。
在SQL中，删除索引使用DROP INDEX语句，其一般格式为
DROP INDEX <索引名> ON <表名>;
例如，删除Albums表的q索引。
DROP INDEX LYXDZQ ON Albums;
2.数据操作
（1）各类更新操作
①插入数据
SQL插入元组的INSERT语句的格式为
INSERT
INTO <表名> [(<属性列1> [,<属性列2>]…)]
VALUES (<常量1> [,<常量2>] …);
其功能是将新元组插入指定表中。其中新元组的属性列1的值为常量1，属性列2的值为常量2，…。INTO子句中没有出现的属性列，新元组在这些列上将取空值。但必须注意的是，在表定义时说明了NOT NULL的属性列不能取空值，否则会出错。
如果INTO子句中没有指明任何属性列名，则新插入的元组必须在每个属性列上均有值。
例如，将新的信息插入歌曲表中。
INSERT
INTO Songs
VALUES (‘龙卷风’,‘4.08’,‘pop’);
INSERT
INTO Songs
VALUES (‘多远都要在一起’,‘3.37’,‘pop’);
INSERT
INTO Songs
VALUES (‘再见’,‘3.26’,‘pop’);
INSERT
INTO Songs
VALUES (‘一路逆风’,‘3.45’,‘pop’);
INSERT
INTO Songs
VALUES (‘画’,‘2.48’,‘pop’);
INSERT
INTO Songs
VALUES (‘泡沫’,‘4.18’,‘pop’);
INSERT
INTO Songs
VALUES (‘于是’,‘3.49’,‘pop’);
INSERT
INTO Songs
VALUES (‘有心人’,‘3.59’,‘pop’);
INSERT
INTO Songs
VALUES (‘我的秘密’,‘4.11’,‘pop’);
INSERT
INTO Songs
VALUES (‘红蔷薇白玫瑰’,‘4.42’,‘pop’);
INSERT
INTO Songs
VALUES (‘光年之外’,‘3.55’,‘pop’);
INSERT
INTO Songs
VALUES (‘睡皇后’,‘3.56’,‘pop’);
INSERT
INTO Songs
VALUES (‘依然睡公主’,‘2.40’,‘rap’);
②修改数据
修改操作又称为更新操作，其语句一般格式为
UPDATE <表名>
SET <列名>=<表达式> [,<列名>=<表达式>] …
[WHERE<条件>];
其功能是修改指定表中满足WHERE子句条件的元组。其中SET子句给出<表达式>的值用于取代相应的属性列值。如果省略WHERE子句，则表示要修改表中的所有元组。
例如，把歌曲《睡公主》的时长从4.43改到3.44
UPDATE Songs
SET Sduration=3.44
WHERE Sname=‘睡公主’;
③删除数据
删除语句的一般格式为
DELETE
FROM <表名>
[WHERE <条件>];
DELETE语句的功能是从指定表中删除满足WHERE子句条件的所有元组。如果省略WHERE子句则表示删除表中的全部元组，但表的定义仍在字典中。也就是说，DELETE语句删除的是表中的数据，而不是关于表的定义。
例如，删除歌曲《于是》的记录。
DELETE
FROM Songs
WHERE Sname=‘于是’;
（2）各类查询操作
数据查询是数据库的核心操作。SQL提供了SELECT语句进行数据查询，该语句具有灵活的使用方式和丰富的功能。其一般格式为
SELECT [ALL|DISTINCT] <目标列表达式> [,<目标列表达式>]…
FROM <表明或视图名> [,<表名或视图名>…]|(<SELECT语句>)[AS]<别名>
[WHERE <条件表达式>]
[GROUP BY <列名1>[HAVING<条件表达式>]]
[ORDER BY <列名2> [ASC|DESC]];
整个SELECT语句的含义是，根据WHERE子句的条件表达式从FROM子句指定的基本表、视图或派生表中找出满足条件的元组，再按SELECT子句中的目标列表达式选出元组中的属性值形成结果表。
如果有GROUP BY子句，则将结果按<列名1>的值进行分组，该属性列值相等的元组为一个组。通常会在每组中作用聚集函数。如果GROUP BY子句带HAVING短语，则只有满足指定条件的组才予以输出。
如果有ORDER BY子句，则结果表还要按<列名2>的值升序或降序排序。
①单表查询
单表查询是指仅涉及一个表的查询。
例如，查询邓紫棋的所有专辑名
SELECT Aname
FROM Albums;
②连接查询
连接查询是关系数据库中最主要的查询，包括等值连接查询、自然连接查询、非等值连接查询、自身连接查询、外连接查询和复合条件连接查询等。
例如，查询歌曲名及评分
SELECT Songs.*,Evaluation.**
FROM Songs,Evaluation
WHERE Songs.Sname=Evaluation.Sname;
再例如，查询邓紫棋收录歌曲数小于10首的专辑SELECT Albums.*
FROM Albums
WHERE Albums.ANumbersOfSongs<10;
③嵌套查询
在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。
例如，查询和专辑《睡皇后》歌曲数相同的专辑
SELECT Albums.*
FROM Albums
WHERE Aname IN
(SELECT Aname
FROM Albums
WHERE Albums.ANumbersOfSongs=3 AND Albums.Aname<>‘睡皇后’
);
④集合查询
SELECT语句的查询结果是元组的集合，所以多个SELECT语句的结果可进行集合操作。集合操作主要包括并操作UNION，交操作INTERSECT和差操作EXCEPT。
例如，查询邓紫棋专辑中序号大于5且为LP的专辑
SELECT Albums.*
FROM Albums
WHERE Asequence>5
INTERSECT
SELECT Albums.*
FROM Albums
WHERE Aclassification=‘LP’
3.视图操作
（1）视图的创建
SQL语言用CREATE VIEW命令建立视图，其一般格式为
CREATE VIEW <视图名>[(<列名> [,<列名>]…)]
AS <子查询>
[WITH CHECK OPTION]
例如，建立邓紫棋EP专辑的视图
CREATE VIEW EPGEM
AS
SELECT Aname,Abirth,Asequence
FROM Albums
WHERE Aclassification=‘EP’;
（2）查询视图
视图定义后，用户就可以像基本表一样对视图进行查询了。
例如，在邓紫棋EP专辑视图中找出收录歌曲数小于5的专辑。
SELECT Aname
FROM EPGEM
WHERE ANumbersOfSongs<5;
（3）更新视图
更新视图是指通过视图来插入（INSERT）、删除（DELETE）和修改（UPDATE）数据。
例如，将邓紫棋EP专辑视图中的收录歌曲数为6的专辑名改为“邓紫棋”
UPDATE EPGEM
SET Aname=‘邓紫棋’
WHERE AnumbersOfSongs=6;
（4）删除视图
DROP VIEW EPGEM
三、出现的问题及解决方法
1.在构建模式的时候出现写语句时曾出现把模式名和用户名写反导致错误的情况，后已改正。
2.在登录SQL Sever时，出现18456错误，经查询是登录时密码输错所致。
3.删除表时出现报错，事实上报错的原因在于SQL Server不支持在删除过程中使用CASCADE关键字。
4.在创建索引时出现有重复的键的错误，后来查询表时确实有重复，把重复修改后正常。
