实验1 数据库的定义和建立实验
实验一 数据库的定义和建立实验
实验目的
- 要求学生熟练掌握和使用T－SQL﹑SQL Server企业管理器创建数据库﹑基本表﹑视图﹑索引和修改表结构，及向数据库输入数据的操作；学会创建和使用表的主码，外码和约束。
- 熟练掌握SQL Sever的使用方法。
- 理解和掌握数据库DDL语言，能够熟练地使用SQL DDL语句创建、修改和删除数据库、模式和基本表。
实验内容
本实验针对上述两个题目分别完成以下相应任务：
1﹑创建相应的数据库和查看数据库属性；
2﹑创建基本表﹑确定表的主码和相应的约束，为主码建索引；
3﹑创建﹑查看视图；
4﹑创建表之间的关联；
5﹑利用T－SQL和SQL Server企业管理器向数据库输入数据。
实验设备
PC机一台
实验前准备
安装SQL Sever集成环境
-
参考安装教程完成SQL Sever的安装。
-
完成账户设置并登录数据库
连接服务器
• 服务器类型选择 数据库引擎
• 服务器名称可以输入localhost
• 身份验证可以选择Windows身份验证或者SQL Sever身份验证
• 输入密码之后点击连接即可连接SQL Sever服务器
实验流程
本次实验选择创建图书发行数据库系统的设计
表格数据如下：
图书馆 L.DBF
| LNO | LNAME | CITY | TEL |
|---|---|---|---|
| L1 | 上海图书馆 | 上海 | 205800 |
| L2 | 上海外文书店 | 上海 | 310310 |
| L3 | 北京图书馆 | 北京 | 282471 |
| L4 | 武汉图书馆 | 武汉 | 812712 |
| L5 | 南京图书馆 | 南京 | 328173 |
图书 B.DBF
| BNO | BNAME | PRICE |
|---|---|---|
| B1 | 数据库原理 | 9.80 |
| B2 | 系统分析与设计 | 8.50 |
| B3 | 数据库设计 | 7.10 |
| B4 | 计算机原理 | 9.60 |
| B5 | 操作系统 | 11.20 |
书店 S.DBF
| SNO | SNAME | ADDRESS |
|---|---|---|
| S1 | 北京新华书店 | 北京 |
| S2 | 上海新华书店 | 上海 |
| S3 | 上海外文书店 | 上海 |
| S4 | 湖北新华书店 | 武汉 |
| S5 | 江苏新华书店 | 南京 |
图书发行 LBS.DBF
| LNO | BNO | SNO | QTY |
|---|---|---|---|
| L1 | B1 | S2 | 10 |
| L1 | B2 | S1 | 5 |
| L1 | B3 | S3 | 5 |
| L1 | B4 | S5 | 20 |
| L2 | B1 | S3 | 10 |
| L2 | B4 | S5 | 20 |
| L2 | B5 | S4 | 15 |
| L4 | B4 | S4 | 20 |
| L4 | B5 | S4 | 30 |
| L5 | B1 | S2 | 10 |
| L5 | B2 | S5 | 20 |
| L5 | B4 | S5 | 30 |
创建数据库
- 在SSMS开始界面点击【新建查询】，输入
CREAT DATABASE 图书发行数据库 - 点击【执行】，显示命令已成功完成
- 点击资源管理器上的刷新按钮，即可看到我们新建立的图书发行数据库
建立基本表
-
在新建好的图书发行数据库下面选择【新建查询】
-
在界面输入
CREAT TABLE 书店 (SNO NCHAR(10)PRIMARY KEY,--将SNO设置为主码 SNAME NCHAR(10), ADDRESS NCHAR(10))CREAT TABLE 图书 (BNO NCHAR(10)PRIMARY KEY, --将BNO设置为主码 BNAME NCHAR(10), PRICE FLOAT)CREAT TABLE 图书馆 (LNO NCHAR(10)PRIMARY KEY,--将LNO设置为主码 LNAME NCHAR(10), CITY NCHAR(10), TEL SMALLINT)CREATE TABLE 图书发行 (LNO NCHAR(10), BNO NCHAR(10), SNO NCHAR(10), QTY SMALLINT, PRIMARY KEY(LNO,BNO,SNO), --主码由三个属性构成，必须作为表级完整性进行定义 /*标记完整性约束条件，外码*/ FOREIGN KEY(LNO) REFERENCES 图书馆(LNO), FOREIGN KEY(BNO) REFERENCES 图书(BNO), FOREIGN KEY(SNO) REFERENCES 书店(SNO)) -
点击【执行】，显示命令执行成功，刷新之后就可以看到建立的四个基本表了
输入数据
在我们建立的基本表右键选择编辑前200行，在界面输入附表中给出的数据即可。
查看数据库属性
-
在SSMS工具栏中选择【新建查询】，打开SQL编辑器页面
-
输入
select * from sys.databases where name='图书发行数据库' -
点击【执行】即可查看数据库属性，内容如下：
- 或者输入
sp_helpdb 图书发行数据库 --使用数据库信息报告的系统过程
创建、查看视图
--判断用户自定义视图是否存在
if exists(select * from sysobjects where id=OBJECT_ID(N'v_Stu_Cou') and objectproperty(id,N'IsView')=1)
drop view v_Stu_Cou --删除视图
go
create view dbo.v_Stu_Cou --创建视图
as
select S_Id,S_StuNo,S_Name,S_Sex,S_Height,S_BirthDate,c.C_Id,c.C_Name
from Student s
left join Course c on s.C_S_Id=c.C_Id
where s.C_S_Id is not null
go
select * from v_Stu_Cou --使用视图查询
/*
参数：
schema_name：是视图所有者的名称，一般为 dbo。（指定视图所有者名称是可选的。）
view_name：是视图的名称。
column：是视图中用于列的名称。仅当列从算术表达式，函数或常数派生时，才需要列名称; 当两个或多个列可能具有相同的名称时，通常是因为连接; 或者当视图中的列被指定与其派生的列的名称不同的名称时。列名也可以在 SELECT 语句中分配。如果列未指定，则视图列获得相同的名称作为 SELECT 语句中的列。
AS：指定视图要执行的操作。
select_statement：是定义视图的 SELECT 语句。该语句可以使用多个表和其他视图。需要适当的权限才能从创建的视图的 SELECT 子句中引用的对象进行选择。
ENCRYPTION：对加密进行加密，加密之后不可修改。使用 WITH ENCRYPTION 可以防止将视图作为 SQL Server 复制的一部分进行发布。
SCHEMABINDING：将视图绑定到底层所应用到的表。指定 SCHEMABINDING 时，不能以影响视图定义的方式修改表。必须首先修改或删除视图定义，以删除要修改的表的依赖关系。使用 SCHEMABINDING 子句创建的视图的视图或表不能删除，除非该视图被删除或更改，以使其不再具有绑定。
VIEW_METADATA：当使用 WITH VIEW_METADATA 创建视图时，返回的是视图的元数据，否则返回的元数据是视图所引用表的元数据。
*/
实验主要内容
数据库的操作(以学生选课数据库为例)
使用SQL建立数据路
• 在开始界面点击【新建查询】
• 输入CREATE DATABASE 学生
• 点击上面的【执行】
• 刷新之后就能够看到刚才建立的学生数据库了
使用SQL删除数据库
• 输入drop database TEST即可删除建立的学生数据库
基本表的操作
使用SQL建立基本表
• 选择【数据库】——》【学生】，然后选择【新建查询】
• 在新建查询界面输入以下代码：
create table student --创建表student
(sno nchar (10),
sname nchar (20),
sage smallint,
ssex nchar (2),
sdept nchar (20),
primary key(sno)
)
create table course --创建表course
(cno nchar(10),
cname nchar(20),
cpno nchar(10),
ccredit smallint ,
primary key(cno)
)
create table sc --创建表sc
(sno nchar (10),
cno nchar (10),
grade smallint
)
• 点击【执行】，刷新之后便能够在我们建立的TEST数据库中看到我们新建立的三张基本表了。
使用SQL修改基本表
修改单列数据
UPDATE [表名称] SET [列名称] = [新值] WHERE [列名称] = [某值]
修改多列数据
UPDATE [表名称] SET [列名称1],[列名称2],[...] = [新值] WHERE [列名称] = [某值]
向学生表增加“身高“列，数据类型为int
alter table student add height int
修改表名
exec sp_rename 'sc','sc2'
列操作
alter table student add NewColumn nchar(5) null --新增字段
alter table student alter column NewColumn nvarchar(10) --修改字段属性
alter table student drop column NewColumn --删除列
约束
alter table student add constraint sname unique(Name) --新增唯一约束（此非索引）
alter table student drop constraint sname--删除此约束
索引
create index NameIndex on Reports(Name) --新增普通索引（非聚集索引）
create unique index Name_UQ on Reports(Name) --新增唯一索引（非聚集索引）
exec sp_helpindex student--查看表的索引
drop index Reports.NameIndex --删除索引
create nonclustered index NameFileIndex on Categories(CategoryName,PictureFile) --创建非聚集索引（组合索引）
使用SQL删除基本表
在【新建查询】页面输入以下代码即可删除之前建立的基本表
drop table student --删除表student
drop table course --删除表course
drop table sc --删除表sc
drop table student cascade;--student表中的sno被sc表中的cno参照了
实验心得
本次实验是数据库系统设计的第一次正式实验，通过本次实验正确安装了SQL Sever和SSMS,学会了SSMS的简单实用，通过建立数据库，创建基本表并对基本表进行相关的操作，熟悉了使用SQL进行数据库创建的基本过程和语法，整个实验过程非常有趣，在建立基本表的时候违反了约束条件，通过询问老师和同学解决了这个问题。本次实验只是初步学会了使用SQL Sever,要想创建一个完整的健全的数据库系统还有很长的路要走，再接再厉！
