数据库实验一：学生信息管理系统数据库结构搭建和表的创建

实验类型：综合性

实验目的与要求

1．熟悉SQL Server2012/2019的图形界面工作环境
2. 理解几个主要系统数据库
3. 掌握SQL Server Management Studio和Transact-SQL语句创建数据库的方法；
4. 理解数据库文件和文件组，掌握文件和文件组的两种增加、删除、修改文件和文件组的方法。
5. 掌握分别用SQL Server Management Studio和Transact-SQL语句两种方式修改数据库，包括（删除数据库，分离数据库，附加数据库等）
6. 掌握使用SQL Server Management Studio和T-SQL语言创建表、修改表已经删除表。
7. 掌握创建、删除和修改约束的方法。
8. 了解如何让查看表的定义、数据的依赖关系

实验原理与内容

1. 数据库的组织结构

1）逻辑存储结构
SQL Server数据库由一个表集合组成。这些表包含结构化的数据以及为支持对数据 的操作额保证数据的完整性而定义的其他对象，如视图、索引、存储过程、用户定义函数和触发器等，它们够成了数据库的逻辑存储结构，如图1-1所示。
2）物理存储结构
数据库的物理存储结构是说明包含数据库对象的数据库文件是如何在磁盘上存储的。创建数据库实际上就是在硬盘上创建数据库文件的过程，数据库文件是存放数据库数据和数据库对象的文件。
在SQL Server数据库中存在主要数据文件、次要数据文件和事务日志3种数据库文件。主要数据文件（简称主文件）包含数据库的启动信息并用于存储数据，文件扩展名为.mdf。每个数据库必须且只有一个主文件，该文件存放两种对象，即用户对象和系统对象。用户对象包含表、视图及存储过程等，用于保存用户输入的数据；系统对象有表名、数据库用户帐号、索引地址等维护数据库工作所需要的信息。系统表应该保存在主文件中，而用户数据可移动到次要数据文件中。
次要数据文件主要存储用户数据，文件的扩展名为.ndf。如果主文件包含了数据库中所有数据，则可以不要次要文件，但如果数据库非常大（如超出了单个windows文件的最大值），就应该有多个次要数据文件来辅助存放用户数据，但不能存放系统对象。使用次要数据文件可以让数据库文件不断得到扩充，并可以通过将不同的文件存放到不同的磁盘空间中实现同时对多个磁盘进行访问，从而提高数据存储的并发性。

							图1-1

SQL Server主要通过事务日志文件来实现数据的恢复，文件扩展名为.ldf。数据库必须至少包含一个事务日志文件，每个日志文件仅能被一个数据库所拥有，数据和日志信息不应混在一起。
3）数据库文件组
为了方便数据的分配、放置和管理，SQL Server允许对文件进行分组处理， 在同一个组中的文件共同组成一个文件组。例如，可以分别在三个硬盘驱动器上创建三个数据文件Data1.ndf、Data2.ndf和Data3.ndf，并将它们指派到文件组Filegroup中，然后在Filegroup上创建一个表，这样对表中数据的查询将分散到三个磁盘上，从而查询性能得到改善。此外，将维护要求相近的表和索引分配到相同的文件组中，备份时仅备份文件组，而非数据庞大的整个数据库，能提高系统的可维护性。再有，对于不允许修改的表（如历史数据），可以将它们置于文件组中（主文件除外）然后将这些文件组标记为只读，从而起到保护数据的目的。
文件和文件组的设计遵循如下规则：一个文件或文件组不能由多个数据库使 用；一个文件只能是一个文件组的成员；事务日志文件不能属于任何文件组。

2. 数据库的分离和附加

数据库的分离和附加是一对互逆的操作。分离数据库是指将数据库从SQL Server实例中删除，即从数据库的逻辑结构中删除，但并不会将其物理文件（数据文件和日志文件）从磁盘上删除，之后，就可以使用这些文件将数据库附加到任何SQL Server实例，包括分离该数据库的服务器。当需要将数据库物理文件从当前位置移到另一磁盘或存储路径下时，可先分离再附加数据库。通常，附加数据库时会将数据库重置为它分离或复制时的状态。

在进行数据库分离操作时，要注意以下几点：
（1）不能对数据库对象进行更新操作和运行任务，用户也不能连接在该数据库上。
（2）在移动数据库之前，最好为数据库做一个完整的备份。
（3）确保数据库要移动的目标位置及将来数据增长能有足够的空间。

3. 数据库表的创建，修改和删除

（1）基本表的创建
定义基本表
CREATE TABLE <表名>
(<列名> <数据类型>[ <列级完整性约束条件> ]
[,<列名> <数据类型>[ <列级完整性约束条件>] ]
…
[,<表级完整性约束条件> ] );
<表名>：所要定义的基本表的名字
<列名>：组成该表的各个属性（列）
<列级完整性约束条件>：涉及相应属性列的完整性约束条件
<表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件
如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。

（2）基本表的修改
ALTER TABLE <表名>
[ ADD[COLUMN] <新列名> <数据类型> [ 完整性约束 ] ]
[ ADD <表级完整性约束>]
[ DROP [ COLUMN ] <列名> [CASCADE| RESTRICT] ]
[ DROP CONSTRAINT<完整性约束名>[ RESTRICT | CASCADE ] ]
[ALTER COLUMN <列名><数据类型> ] ;
<表名>是要修改的基本表
ADD子句用于增加新列、新的列级完整性约束条件和新的表级完整性约束条件
DROP COLUMN子句用于删除表中的列
如果指定了CASCADE短语，则自动删除引用了该列的其他对象
如果指定了RESTRICT短语，则如果该列被其他对象引用，关系数据库管理系统将拒绝删除该列
DROP CONSTRAINT子句用于删除指定的完整性约束条件
ALTER COLUMN子句用于修改原有的列定义，包括修改列名和数据类型
（3）基本表的删除
DROP TABLE <表名>［RESTRICT| CASCADE］;
RESTRICT：删除表是有限制的。
欲删除的基本表不能被其他表的约束所引用
如果存在依赖该表的对象，则此表不能被删除
CASCADE：删除该表没有限制。
在删除基本表的同时，相关的依赖对象一起删除

实验过程与结果

1. 根据学生信息管理系统创建相关的数据库

1.1 使用SQL Server Management Studio创建数据库，要求创建主数据文件（.mdf），次要数据文件(.ndf)，日志文件等。文件的具体参数组别自己决定，但要阐述设置该参数的理由。要求：数据库名为：mydb。
答：

1.2 使用SQL Server Management Studio删除1.1创建的数据库
答：

附加另一种方式（上面的已经是答案）：
drop database mydb;

1.3 使用Transact-SQL语句创建数据库，要求创建主数据文件，次要数据文件，日志文件等。文件的具体参数组别自己决定，但至少包含3个数据文件，文件的增量要包含具体数字和百分比两种形式。阐述设置相关参数的理由。
答：

create database mydb
on primary	--指定显式定义用来存储数据库数据部分的磁盘文件
(
name=mydb,
filename="E:\SQLtext\test_data.mdf",	--数据库文件的路径
size=5MB,		--文件大小
maxsize=20MB,	--文件可增长到最大大小
filegrowth=10%	--文件的自动增量
),
 ( NAME = 'MyDB_FG1_Dat1',
    FILENAME =
       'E:\SQLtext\MyDB_FG1_1.ndf',
    SIZE = 1MB,
    MAXSIZE=10MB,
    FILEGROWTH=1MB)

log on	--指定显式定义用来存储数据库日志的磁盘文件（日志文件）
(
name=test_log,
filename="E:\SQLtext\test_log.ldf",	--事务日志文件的路径
size=1MB,
maxsize=5MB,
filegrowth=10%
)

参考资料：
CREATE DATABASE
SQL server数据库的操作步骤·Transact-SQL创建

CREATE DATABASE database_name
[ CONTAINMENT = { NONE | PARTIAL } ]
[ ON
      [ PRIMARY ] <filespec> [ ,...n ]
      [ , <filegroup> [ ,...n ] ]
      [ LOG ON <filespec> [ ,...n ] ]
]
[ COLLATE collation_name ]
[ WITH <option> [,...n ] ]
[;]

<option> ::=
{
      FILESTREAM ( <filestream_option> [,...n ] )
    | DEFAULT_FULLTEXT_LANGUAGE = { lcid | language_name | language_alias }
    | DEFAULT_LANGUAGE = { lcid | language_name | language_alias }
    | NESTED_TRIGGERS = { OFF | ON }
    | TRANSFORM_NOISE_WORDS = { OFF | ON }
    | TWO_DIGIT_YEAR_CUTOFF = <two_digit_year_cutoff>
    | DB_CHAINING { OFF | ON }
    | TRUSTWORTHY { OFF | ON }
    | PERSISTENT_LOG_BUFFER=ON ( DIRECTORY_NAME='<Filepath to folder on DAX formatted volume>' )
    | LEDGER = {ON | OFF }
}

<filestream_option> ::=
{
      NON_TRANSACTED_ACCESS = { OFF | READ_ONLY | FULL }
    | DIRECTORY_NAME = 'directory_name'
}

<filespec> ::=
{
(
    NAME = logical_file_name ,
    FILENAME = { 'os_file_name' | 'filestream_path' }
    [ , SIZE = size [ KB | MB | GB | TB ] ]
    [ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]
    [ , FILEGROWTH = growth_increment [ KB | MB | GB | TB | % ] ]
)
}

<filegroup> ::=
{
FILEGROUP filegroup name [ [ CONTAINS FILESTREAM ] [ DEFAULT ] | CONTAINS MEMORY_OPTIMIZED_DATA ]
    <filespec> [ ,...n ]
}

1.4 设计一种情景，阐述需要修改数据库的数据文件和日志文件，并修改它们。
答：

use master 
go
alter database mydb
MODIFY FILE
(
	name = 'MyDB_FG1_Dat1', --修改次要数据文件
	FILEGROWTH = 9MB --修改增量
)
go

use master 
go
alter database mydb
MODIFY FILE
(
	name=test_log, --修改日志文件
	size=4MB --修改大小
)
go

2. 数据库表初步设计及实现

2.1 根据相关的业务要求，在数据服务器上用Transact-SQL语句创建相关的表。至少包含以下3个表以及合理的相关属性：学生表（至少含8个属性），班级表（至少含6个属性）和宿舍表（至少含5个属性）。三个表中属性的数据类型，包含数字型（整型和浮点型），字符串性（可变和不可变），日期型，货币等不同数据类型。
数据字典可以参考如下：

属性 类型 完整性 备注
Sno Char(11) 主键 学生的学号
Sname varchar（50） 非空 学生的姓名
Cno Char(10) 外键 班级编号
Birthday Data 允许为空 出生日期
Gender Char(2) 男/女 性别
Age tinyint 根据出生日期计算 年龄
Telephone Char(11) 唯一，不允许为空 手机号
Email varchar（50） 唯一，不允许为空 email

班级表：Class
属性 类型 完整性 备注
Cno Char（10） 主键 班级编号
Cname varchar（50） 唯一，非空 班级名称
Sdeptname Char（30） 非空 专业名称
Cnum Char（10） 非空 辅导员编号
Counselor Char（8） 非空 辅导员姓名
Ctel Char（11） 允许为空 辅导员电话

宿舍表：Dorm
属性 类型 完整性 备注
Dno Char（10） 联合主键 宿舍编号
Sno Char（11） 联合主键、外键 学生学号
Dname varchar（50） 唯一，非空 宿舍名称
Dnum Tinyint 非空
取值范围(0-12) 床位号
Accommodation money 非空 住宿费
Degree Numerice(8,2) 默认（0.0） 该生月用电度数
Price Numerice(8,2) 非空 电费

答：

drop table if exists Student; --如果有就删除

create table Student(
Sno char(11) PRIMARY KEY,
Sname varchar(50) not null,
Cno char(10),
Birthday date,
Gender char(2) check(Gender = '男' or Gender = '女'),
Age tinyint ,
Telephone char(11) NOT NULL UNIQUE,
Email varchar(50) NOT NULL UNIQUE,
);

drop table if exists Class;

create table Class(
Cno Char(10) PRIMARY KEY,
Cname varchar(50) not null UNIQUE ,
Sdeptname Char(30) not null,
Cnum Char(10) not null,
Counselor Char(8) not null,
Ctel Char(11) ,
);

alter table Student add FOREIGN KEY (Cno) REFERENCES  Class(Cno); 

create table Dorm(
Dno Char(10) ,
Sno Char(11) FOREIGN KEY  REFERENCES Student(Sno),
Dname varchar(50) not null UNIQUE,
Dnum Tinyint not null check(Dnum >= 0 and Dnum <= 12),
Accommodation money not null,
Degree Numeric(8,2) DEFAULT(0.0),
Price Numeric(8,2) not null,
PRIMARY KEY(Dno , Sno),
);

2.2 考虑表相互间的约束条件和本身字段的约束条件，并实现
答：
学生需要有班级，添加外键约束

alter table Student add FOREIGN KEY (Cno) REFERENCES  Class(Cno); 

2.3 设计某种情景，要修改和删除表里面的约束条件，并实现
答：
删除约束：

alter table Student drop constraint CK__Student__Gender__398D8EEE;

然后再添加回来 ， 情景：需要约束性别选项

alter table Student add constraint CK__Student__Gender__398D8EEE Check(Gender = '男' or Gender = '女');

2.4 三个表中，任选其中一个表，根据某种业务逻辑，创建索引
答：create index one on Student(Sno);
(注：没有业务逻辑，只是创建索引)

2.5 使用SQL Server Management Studio，每个表至少输入3条有效数据。要求学生表中增加包括自己与其后或前的3位同学的信息，并对应增加其选课信息。
答：

insert into Class(Cno ,Cname , Sdeptname, Cnum, Counselor)
values('1' ,'1' , 'fe' ,'1','1' );--添加数据
insert into Class(Cno ,Cname , Sdeptname, Cnum, Counselor)
values('2' ,'2' , 'ge' ,'2','2' );

insert into Student (Sno , Sname ,Birthday ,Email , Telephone ,Cno)
values('1','a','2001-1-1','www','10086' ,  '1');
insert into Student (Sno , Sname ,Birthday ,Email , Telephone ,Cno)
values('2','b','2001-1-2','eee','1008611' ,  '2');
--第三位自己写吧

2.6 请用两种不同方法，实现删除学生表和对应宿舍表中的数据（提示：级联删除）
答：
前置设置:

-- 已经删除上次建立的外键关系
alter table Student add FOREIGN KEY (Cno) REFERENCES  Class(Cno) on delete cascade;  --添加外键关系（声明了级联删除）

insert into Class(Cno ,Cname , Sdeptname, Cnum, Counselor)
values('1' ,'1' , 'fe' ,'1','1' );--添加数据

insert into Student (Sno , Sname ,Birthday ,Email , Telephone ,Cno)
values('1','a','2001-1-1','www','10086' ,  '1');

delete Class where Cno='1';

这时候会发现Student表中行1数据也被删除了，这就是级联删除

2.7 请实现完全删除学生表和宿舍表（包含表数据及其定义）
答：

drop table Student ;
drop table Dorm ;
--执行前已经在面板中删除相关关系

3. 实现数据库的分离和附加

3.1 使用SQL Server Management Studio分离1.3所创建的数据库
答：

如果报错勾上中间两个选项即可
3.2 附加分离的数据库
答：

选择添加你想要的即可

操作异常问题与解决方案

异常1.修改文件增量/大小 报错

对于查询语句：

use master 
go
alter database mydb
MODIFY FILE
(
	name = 'MyDB_FG1_Dat1', --修改次要数据文件
	FILEGROWTH = 100MB --修改增量
)
go

use master 
go
alter database mydb
MODIFY FILE
(
	name=test_log, --修改日志文件
	size=4MB --修改大小
)
go

报错

消息 5169，级别 16，状态 1，第 3 行
FILEGROWTH 不能大于文件 'MyDB_FG1_Dat1' 的 MAXSIZE。
消息 5039，级别 16，状态 1，第 13 行
MODIFY FILE 失败。指定的大小小于或等于当前大小。

修改：

use master 
go
alter database mydb
MODIFY FILE
(
	name = 'MyDB_FG1_Dat1', --修改次要数据文件
	FILEGROWTH = 9MB --修改增量 -- 不能大于MAXSIZE
)
go

use master 
go
alter database mydb
MODIFY FILE
(
	name=test_log, --修改日志文件
	size=5MB --修改大小 --修改地方 -- 必须大于原来的值
)
go

MAXSIZE参考创建代码

( NAME = 'MyDB_FG1_Dat1',
    FILENAME =
       'E:\SQLtext\MyDB_FG1_1.ndf',
    SIZE = 1MB,
    MAXSIZE=10MB, -- MAXSIZE
    FILEGROWTH=1MB)