实验二 使用SQL语句创建和修改表

实验目的：

1. 掌握使用可视化方式创建数据库。
2. 掌握使用SQL语句创建数据表，修改表的结构。

实验内容：

一、 使用可视化方式完成下列题。

1、创建一个名称为JXGL的数据库，要求：

1. 将主数据文件JXGL.mdf放置在c:\DBF文件夹中，其文件大小自动增长为按5mb增长。
2. 将事务日志文件JXGL_log.ldf放置在C:\DBF文件夹中，其文件大小自动增长为按1mb增长。

使用SQL语句完成下列题。

1、删除JXGL数据库。

2、创建一个名称为JXGL的数据库，要求：

1. 将主数据文件JXGL.mdf放置在c:\DBF文件夹中，其文件大小自动增长为按5mb增长。
2. 将事务日志文件JXGL_log.ldf放置在C:\DBF文件夹中，其文件大小自动增长为按1mb增长。

CREATE DATABASE JXGL
ON PRIMARY
(
   name="JXGL", /*主数据文件的逻辑名*/
   filename="C:\DBF\JXGL.mdf",/*.mdf 主数据文件储存位置*/
   size=5mb,
   maxsize=100mb,
   filegrowth=5mb  /*增长率*/
)
log on
(   /***日志文件****/
   name="JXGL_log",  /*必须以_log 结尾**/
   filename="C:\DBF\JXGL.ldf",
   size=2mb,
   filegrowth=1mb 
)

3、创建一个名为TEST2的数据库，它有三个数据文件，其中主数据文件为100MB，最大大小为200MB，按20MB增长；2个辅数据文件为20MB，最大大小不限，按10%增长；有2个日志文件，大小均为50MB，最大大小均为100MB，按10MB增长。

create database test2
on
(
    name='test2_ data1',
    filename='C:\DBF\test2\test2_ data1.mdf',
    size= 100mb,
    maxsize= 200mb,
    filegrowth= 20mb
),
(
    name='test_ data2',
    filename='C:\DBF\test2\test2 _data2.ndf',
    size= 20mb,
    maxsize=unlimited,
    filegrowth= 10%
),
(
    name='test_ data3',
    filename='C:\DBF\test2\test2 _data3.ndf',
    size= 20mb,
    maxsize=unlimited,
    filegrowth= 10%
)
log on
(
    name='test2_ log1',
    filename='C:\DBF\test2\test2_ log1.ldf',
    size= 50mb,
    maxsize= 100mb,
    filegrowth= 10mb
),
(
    name='test2_ log2',
    filename='C:\DBF\test2\test2_ log2.Idf',
    size=50mb,
    maxsize= 100mb,
    filegrowth= 10mb
 )
go

4、修改数据库jxgl现有数据文件的属性，将主数据文件的最大大小改为不限制，增长方式改为按每次5MB增长。

alter database JXGL
modify file
(name='JXGL',maxsize=unlimited,filegrowth=5mb)

5、先为数据库jxgl增加数据文件jxglBAK。然后删除数据文件jxglBAK。

• 增加

  alter database JXGL
  add file
  (name='JXGLbak',filename='C:\jxglbak.ndf',size=5mb)

• 删除

  alter database JXGL
  remove file jxglbak

6、为数据库jxgl添加文件组FGROUP，并为此文件组添加两个大小均为10MB的数据文件。

alter database JXGL
      add filegroup FGROUP
go
alter database JXGL
      add file
      (name='jxgl_data2',filename='C:\DBF\jxgl_data2.ndf',size=10mb),
      (name='jxgl_data3',filename='C:\DBF\jxgl_data3.ndf',size=10mb)
      to filegroup FGROUP
go

7、从数据库中删除文件组，将jxgl数据库中的数据组FGROUP删除。

alter database JXGL
       remove file jxgl_data2
go
alter database JXGL
       remove file jxgl_data3
go
alter database JXGL
       remove filegroup Fgroup
go

8、为数据库jxgl添加一个日志文件。初始大小为3mb，最大大小为500mb。

alter database JXGL
       add log file
       (name='jxgl_log2',filename='C:\DBF\jxgl_log2.ldf',size=3mb,maxsize=500mb)
go

9、分离jxgl数据库，将数据库文件复制到U盘。

use master
exec sp_detach_db JXGL 
go

使用界面方式和SQL语言完成下列题

1. 在JXGL数据库中创建下列表。
   1. 学生表Student，由学号(Sno)、姓名(Sname)、性别(Ssex)、年龄(Sage)、所在系(Sdept)五个属性组成，记作：Student(Sno,Sname,Ssex,Sage,Sdept)
      其中主码为Sno。姓名取值唯一，性别取值为男或女，默认为男。

Create Table Student 
( 
    Sno CHAR(5) NOT NULL PRIMARY KEY(Sno), 
    Sname VARCHAR(20) constraint uq_sname unique, Sage SMALLINT, 
    Ssex CHAR(2) DEFAULT '男' CHECK (Ssex='男' OR Ssex='女'), 
    Sdept CHAR(2)
)

  2. **课程表Course**，由课程号(Cno)、课程名(Cname)、先修课号(Cpno)、学分(Ccredit)四个属性组成，记作：Course(Cno,Cname,Cpno,Ccredit)，
     其中主码为Cno。Cname取值唯一,Cpno参照cno。

Create Table Course  
( 
    Cno CHAR(2) NOT NULL PRIMARY KEY(Cno), 
    Cname VARCHAR(20) UNIQUE,
    Cpno CHAR(2) references course(cno), 
    Ccredit SMALLINT
)

  3. **学生SC**，由学号(Sno)、课程号(Cno)、成绩(Grade)三个属性组成，记作:SC(Sno,Cno,Grade)
     其中主码为(SNO,CNO)，sno参照student表的sno列，cno参照course表的cno列。grade取值范围0-100。

Create Table SC 
( 
    Sno CHAR(5) NOT NULL CONSTRAINT S_F FOREIGN KEY REFERENCES Student(Sno),
    Cno CHAR(2) NOT NULL Constraint fk_cno FOREIGN KEY(Cno) REFERENCES Course(Cno),
    Grade SMALLINT CHECK ((Grade IS NULL) OR (Grade BETWEEN 0 AND 100)),
    PRIMARY KEY(Sno,Cno)
)

1. 向Student表增加“入学时间”列，其数据类型为日期型。

Alter table student 
Add 入学时间 smalldatetime

1. 将年龄的数据类型改为smallint。

Alter table student 
Alter column sage smallint

1. 删除student表中的入学时间列。

Alter table student 
drop column 入学时间

使用SQL 语句向JXGL数据库中插入如下数据：

Student

INSERT INTO Student
Values
('98001','钱横',18,'男','CS'),
('98002','王林',19,'女','CS'),
('98003','李民',20,'男','IS'),
('98004','赵三',16,'男','MA'),
('98005','欧阳勇',19,'男','MA'),
('98019','李四',18,'男','IS')

Course

INSERT INTO Course
Values
(1,'数据库系统',5,4),
(2,'数学分析',NULL,2),
(3,'信息系统导论',1,3),
(4,'操作系统原理',6,3),
(5,'数据结构',7,4),
(6,'数据处理基础',NULL,4),
(7,'C语言',6,3)

Sc

INSERT INTO Sc
Values
('98001','1','87'),
('98001','2','67'),
('98001','3','90'),
('98002','2','95'),
('98002','3','88'),
('98004','2'.NULL)