SQL实验 数据表的创建、修改及删除和索引的使用

一、实验目的

1．进一步掌握Management Studio的使用；了解SQL Server数据库的逻辑结构和物理结构。

2．掌握SQL中使用Create Database、Drop Database命令创建、删除数据库。

3. 掌握如何在表中添加、修改、删除数据；掌握SQL中Create Table、Alter Table、Drop Table命令的使用。

4．理解SQL中的标识列、计算列和各种约束。

5．掌握使用SQL命令修改表的定义、删除表。

6．掌握索引的使用。

二、实验内容

1.用SQL命令创建数据库

参照“实验一”中数据库EDUC及表Student、Course、StudentGrade的要求，使用SQL命令创建一个新的数据库“SCHOOL”，并在数据库SCHOOL中创建数据表Student、Course、StudentGrade，并修改基本表定义。

2．使用SQL语句对数据库SCHOOL中的数据表的结构进行修改。

注意：实验过程中创建的各种数据库及其他一切档案，建议同学们都保存好，以备接下来的实验课中继续使用。每次上课前，请也请带上之前实验课的数据备份。

• 实验要求

（一）数据表的创建

使用SQL命令创建数据库“SCHOOL”及表Student、Course、StudentGrade，并将代码记录下来。

 1.创建数据库SCHOOL。

--创建newschool数据库

create database newschool

on

(

name=newschool_dat,

filename='D:\DB\newschool_dat.mdf',

size=10mb,

maxsize=100mb,

filegrowth=10mb

)

log 

on

(

name=newschool_log,

filename='D:\DB\newschool_log.ldf',

size=10mb,

maxsize=unlimited,

filegrowth=10%

)

2.创建数据表Student。

--创建student表

create table student

(

stu_id varchar(10) NOT NULL constraint pk_student  primary key,

stu_name varchar(10) NOT NULL,

stu_sex varchar(2) default('男'),

birthdate datetime,

phone varchar(8) constraint ck_student_phone check (phone like '3935[0-9][0-9][0-9][0-9]'),

address varchar(100),

class_id varchar(4)

)

3.创建数据表Course。

--创建course表

create table course

(

course_id varchar(4) NOT NULL constraint pk_course primary key,

course_name varchar(20) NOT NULL,

course_hour int default('60'),

introduce varchar(200)

)

4.创建数据表StudentGrade。

--创建studentgrade表

create table studentgrade

(

stu_id varchar(10) foreign key references student(stu_id) NOT NULL ,

course_id varchar(4)foreign key references course(course_id) NOT NULL ,

grade decimal(9,2),

constraint pk_studentgrade primary key(stu_id,course_id)

)

5.删除SCHOOL数据库（注意，此操作进行前请先对数据库进行备份，以免影响后面实验）。

--删除newschool数据库

USE master

drop database newschool

GO

（二）修改基本表的定义

 1．修改列属性

（1）用SQL命令将Student表中的Stu_sex字段设为非空（not null）。

--修改student表的stu_sex字段设为非空（NOT NULL）

alter table student

alter column stu_sex varchar(2) NOT NULL

2．添加列

（1）在Course表中添加一列year，类型为int，字段允许空。

--在course表中添加一列year,类型为int,字段允许为空

alter table course

add year int 

（2）在year字段添加约束，year 的属性值在2010-2017之间。

---在course表中对year字段添加约束条件（2010-2017之间）

alter table course

add constraint ck_year check(year between 2004 and 2008)

3．删除列

（1）将Course表中的year字段删除。

--删除course表中的year字段的约束

alter table course

drop  ck_year

--删除course表中的year字段

alter table course

drop column year

（三）、索引的创建与删除(如果不能成功完成，请分析原因) 

1．分别建立以下索引

(1) 在Student表的Stu_name列上建立普通降序索引。

--在student表的stu_name列建立普通降序索引

create index stuname

on student([stu_name] asc)

(2) 在Course表的Course_name列上建立唯一索引。

--在course表的course_name列上建立唯一引索

create unique index coursename

on course(course_name)

(3) 在StudentGrade表的Stu_id(升序)，Course_id(升序)和Grade(降序)三列上建立一个普通索引。

--在studentgrade表的stu_id(升序），course_id(升序）,grade(降序)三列建立一个普通索引

create index ix_stugrade

on studentgrade([stu_id] asc,[course_id] asc,[grade] desc)

2．删除索引

(1)删除Course表中的Course_name列上的唯一索引。

--删除索引course表中的course_name列上的唯一索引

drop index coursename on course