数据库SQL笔记

实验一  数据库的创建和管理 

一、实验目的

1.熟悉SQL Sever2008的图形界面工作环境；

2.理解几个主要系统数据库；

3.掌握分别利用SQL Sever Management Studio和Transact-SQL语句创建数据库的方法；

4.理解数据库文件和文件组，掌握文件和文件组的两种增加、删除、修改文件和文件组的方法。

5.掌握分别用SQL Sever Management Studio和Transact-SQL语句两种方式修改数据库，包括（删除数据库，分离数据库，附加数据库等）

6.掌握使用SQL Sever Management Studio和T-SQL语言创建表、修改表已经删除表。

7.掌握创建、删除和修改约束的方法。

8.了解如何让查看表的定义、数据的依赖关系。

 

二、实验仪器设备/实验环境

1.Windows XP Professional SP3/Windows7

2.SQL Server 2008

三、实验原理

1.数据库的组织结构

1）逻辑存储结构

SQL Sever数据库由一个表集合组成。这些表包含结构化的数据以及为支持对数据的操作额保证数据的完整性而定义的其他对象，如视图、索引、存储过程、用户定义函数和触发器等，它们够成了数据库的逻辑存储结构，如图1-1所示。

2）物理存储结构

数据库的物理存储结构是说明包含数据库对象的数据库文件是如何在磁盘上存储的。创建数据库实际上就是在硬盘上创建数据库文件的过程，数据库文件是存放数据库数据和数据库对象的文件。

在SQL Sever数据库中存在主要数据文件、次要数据文件和事务日志3种数据库文件。主要数据文件（简称主文件）包含数据库的启动信息并用于存储数据，文件扩展名为.mdf。每个数据库必须且只有一个主文件，该文件存放两种对象，即用户对象和系统对象。用户对象包含表、视图及存储过程等，用于保存用户输入的数据；系统对象有表名、数据库用户帐号、索引地址等维护数据库工作所需要的信息。系统表应该保存在主文件中，而用户数据可移动到次要数据文件中。

次要数据文件主要存储用户数据，文件的扩展名为.ndf。如果主文件包含了数据库中所有数据，则可以不要次要文件，但如果数据库非常大（如超出了单个windows文件的最大值），就应该有多个次要数据文件来辅助存放用户数据，但不能存放系统对象。使用次要数据文件可以让数据库文件不断得到扩充，并可以通过将不同的文件存放到不同的磁盘空间中实现同时对多个磁盘进行访问，从而提高数据存储的并发性。

 

                                               

图1-1

SQL Sever主要通过事务日志文件来实现数据的恢复，文件扩展名为.ldf。数据库必须至少包含一个事务日志文件，每个日志文件仅能被一个数据库所拥有，数据和日志信息不应混在一起。

3）数据库文件组

为了方便数据的分配、放置和管理，SQL Sever允许对文件进行分组处理，在同一个组中的文件共同组成一个文件组。例如，可以分别在三个硬盘驱动器上创建三个数据文件Data1.ndf、Data2.ndf和Data3.ndf，并将它们指派到文件组Filegroup中，然后在Filegroup上创建一个表，这样对表中数据的查询将分散到三个磁盘上，从而查询性能得到改善。此外，将维护要求相近的表和索引分配到相同的文件组中，备份时仅备份文件组，而非数据庞大的整个数据库，能提高系统的可维护性。再有，对于不允许修改的表（如历史数据），可以将它们置于文件组中（主文件除外）然后将这些文件组标记为只读，从而起到保护数据的目的。

文件和文件组的设计遵循如下规则：一个文件或文件组不能由多个数据库使用；一个文件只能是一个文件组的成员；事务日志文件不能属于任何文件组。

2.数据库的分离和附加

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

在进行数据库分离操作时，要注意以下几点：

（1）不能对数据库对象进行更新操作和运行任务，用户也不能连接在该数据库上。

（2）在移动数据库之前，最好为数据库做一个完整的备份。

（3）确保数据库要移动的目标位置及将来数据增长能有足够的空间。

四、实验内容

1.数据库的创建

包括主要数据文件，逻辑文件名，物理文件名，次要数据文件，日志文件，文件组等。

2.数据库的管理

1）修改数据库（扩展、收缩，分离，附加，移动，复制等）

2）修改数据库属性（添加数据文件或日志文件，添加文件组，删除文件和文件组，修改某个文件属性，重命名文件，重命名数据库等）

五、实验步骤

1.使用SQL Sever Management Studio创建数据库，具体要求如下：

1）数据库名称为Test1。

2）主要数据文件：逻辑文件名为Test1Data1，物理文件名为Test1Data1.mdf，初始容量为5MB，最大容量为10MB，增量为1MB。

3）次要数据文件：逻辑文件名为Test1Data2，物理文件名为Test1Data2.ndf， 初始容量为3MB，最大容量为20MB，增量为2MB。

4）事务日志文件：逻辑文件名为Test1Log1，物理文件名为Test1Log1.ldf，初始容量为1MB，最大容量为5MB，增量为1MB。

2.使用Transact-SQL语句创建数据库，具体要求如下：

1）数据库名称为Test2。

2） 主要数据文件：逻辑文件名为Test2Data1，物理文件名为Test2Data1.mdf，初始容量为5MB，最大容量为10MB，增量为10%。

3） 次要数据文件：逻辑文件名为Test2Data2，物理文件名为Test2Data2.ndf， 初始容量为3MB，最大容量为20MB，增量为2MB。

4） 事务日志文件：逻辑文件名为Test2Log1，物理文件名为Test2Log1.ldf，初始容量为1MB，最大容量为5MB，增量为1MB。

3.使用Transact-SQL语句创建数据库Test3，具体要求如下：

1）该数据库名称为Test3

2）包含两个文件组：PRIMARY和Test3_group1

3）三个数据文件：主文件Test3_data1，Test3_data2放在PRIMARY文件组中，Test_data3放在Test3_group1文件组中。

4）一个日志文件:Test3_log

5）各文件的初始容量、最大容量、增量可任意设置。

CREATE DATABASE Test3

ON PRIMARY

(

NAME='Student',

filename='C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\Student.mdf',

filegrowth=1MB,

maxsize=10MB,

size=3MB

),

 

(

    name='Test3_data1',

    filename='C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\Test3_data1.mdf'

),

(

   name='Test3_data2',

   filename='C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\Test3_data2.mdf'

)

log on  \\4）一个日志文件:Test3_log-----这里需要将日志文件放在创建PRIMARY文件以后

(

name='Test3_log',

    filename='C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\Test3_log.ldf',

    size=3mb,

    maxsize=20mb,

    filegrowth=20%

 

)

alter database Test3 add filegroup Test3_group1;

alter database Test3 add file

(

name='Test_data3',

filename='C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\Test_data3.mdf'

) to filegroup Test3_group1

alter database Test3 add filegroup Test3_group2;

alter database Test3 add file

(

   name='Test3_data4',

   filename='C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\Test3_data4.mdf'

)to filegroup Test3_group2

alter database Test3 remove file Test3_data4;

alter database Test3 remove filegroup Test3_group2;

特此声明，代码都是自己乱打的。

4.使用Transact-SQL语句修改数据库属性，具体要求如下：

1）向3中建立的Test3数据库增加一个新的文件组，名称为Test3_group2

2）向文件组Test3_group2中添加文件Test3_data4。

3）删除文件Test3_data4。

4）删除刚添加的文件组Test3_group2。

代码如下：

alter database Test3 add filegroup Test3_group2;

alter database Test3 add file

(

   name='Test3_data4',

   filename='C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\Test3_data4.mdf'

)to filegroup Test3_group2

alter database Test3 remove file Test3_data4;

alter database Test3 remove filegroup Test3_group2;

     5）修改Test3数据库中主要数据文件Test3_data1的属性，初始容量为2MB，最大容量为20MB，增幅为1MB；修改日志文件的属性，初始容量为2MB，最大容量为50MB，增幅为1MB.

代码：

alter database Test3 modify file

(

     NAME='Test3_data1',

     filename='C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\Test3_data1.mdf',

     filegrowth=1MB,

     maxsize=20MB,

     size=2MB

)

alter database Test3 modify file

(

     name='Test3_log',

     filename='C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\Test3_log.ldf',

     filegrowth=1MB,

     maxsize=50MB,

     size=4MB

)

6）修改Test3数据库的名称为new_Test3。

代码：alter database Test3 modify name="new_Test3"

5.分别使用dbcc shrinkdatabase和dbcc shrinkfile对数据库Test2进行压缩。

代码：dbcc shrinkdatabase('name', int)

介个问题俺找不到解决方法，懂的朋友可以留言告诉我。。谢谢指教。

6.分别采用SQL Sever Management Studio和T-SQL语言删除数据库Test1.

代码：drop database Tset1

2.创建表，并创建相应的约束。

要求：在学生管理数据库XSGL中创建如下三个表：创建名为Student（学生信息）的表，表中的各列要求如下：

字段名称	字段类型	约束	说明
Sno	Int	主键	学生学号，标识列（以1为种子，1为增量）
Sname	Nchar（8）		学生姓名，不能空
Sex	Char（2）		性别，默认为男，只能输入男或女
Birthday	Datetime		出生日期，不能为空
Age	smallint		当前时间-birthday
Sdept	Char（20）		系部

代码：

CREATE DATABASE STUDENT

USE STUDENT

CREATE TABLE STUDENT 

(

   Sno Int primary key IDENTITY(1,1),

   Sname Nchar(8) NOT NULL,

   Sex char(2) DEFAULT'男',

   Brithday Datetime,

   Age Smallint CHECK(Age>15 AND Age<25),

   Sdept char(20)

);

创建名为Course（课程信息）的表，表中的各列要求如下：

 

字段名称	字段类型	约束	说明
Cno	char(10)	主键	课程代号
Cname	Varchar(40)	唯一性	课程名
Credit	smallint		学分，取值0-10之间
Cteacher	Char（10）		教师

 

代码如下：

CREATE Course,

USE Course，

CREATE TABLE Course

(

   Cno char(10)  primary key,

   Cname Varchar(40) Unique,

   Credit smallint CHECK(Credit>0 and Credit<10),

   Cteacher Char(10)

)

创建名为SC（选课成绩）表，表中的各列要求如下：

字段名称	字段类型	约束	说明
Sno	Int	主键、外键（参考Student中的Sno）	学号
Cno	Char(10)	主键、外键（参考Cource中的Cno）	课程号
Grade	Smallint	可以为空	成绩，0—100之间

两种方法SQL Sever Management Studio和T-SQL语言创建表、修改表以及删除表。

代码如下：

CREATE DATABASE SC1

CREATE TABLE STUDENT1

(

   Sno Int primary key IDENTITY(1,1),

   Sname Nchar(8) NOT NULL,

   Sex char(2) DEFAULT'男',

   Brithday Datetime,

   Age Smallint CHECK(Age>15 AND Age<25),

   Sdept char(20)

)

 

CREATE TABLE Course1

(

   Cno char(10)  primary key,

   Cname Varchar(40) Unique,

   Credit smallint CHECK(Credit>0 and Credit<10),

   Cteacher Char(10)

)

 

CREATE TABLE SC1

(

   Sno Int,

   Cno Char(10),

   Grade Smallint CHECK(Grade>=0 AND Grade<=100),

   primary key(Sno, Cno),

   foreign key(Sno)references STUDENT1 (Sno),

   foreign key(Cno)references Course1 (Cno)

);

在这里需要分步执行，在建立好第一个和第二个表格以后再执行第三个表格。

3.增加、修改和删除字段，要求：

1）给Student表中增加一个memo字段，类型为varchar（200）

代码：alter table STUDENT add MEMO char (200) 

     2）将memo字段的类型改为nvarchar(250)

代码： alter table STUDENT alter column MEMO Nvarchar(250)

 3）删除memo字段。

代码：ALTER TABLE STUDENT drop column MEMO

4）修改表Cource的名称为CourceInfo

代码：

CREATE DATABASE Cource

USE Cource

CREATE TABLE Cource

(  

   Cno char(10)  primary key,

   Cname Varchar(40) Unique,

   Credit smallint CHECK(Credit>0 and Credit<10),

   Cteacher Char(10)

)  

ALTER DATABASE Cource modify name="CourceInfo"

在里面阴影的都是分段执行。