SQL server数据库案列

                       数据库创建

数据库创建 cjgl

主文件格式和存储位置

create database cjgl on

primary

(

name=cjgl,filename='d:\cjgl\cjgl.mdf',

size=3,

maxsize=10

filegrowth=1

)

辅助文件格式和存储位置

log on

(

name=cjgl_log,filename='d:\cjgl\cjgl_log.ldf',

size=1,

filegrowth=10%

)

使用数据库

use cjgl

删除数据库

drop database cjgl

创建教师表 teacher

               数据表结构的创建

create table teacher

(

教师编号 char(3) primary key,

姓名 varchar(20) not null,

性别 char(2) not null check(性别 in('男','女')),

出生日期 datetime not null,

联系电话 char(11) null

)

创建课程表 course

create table course

(

课程编号 char(3) primary key,

课程名称 varchar(30) not null,

教材 varchar(30) not null,

课时 tinyint null

)

创建班级表 class

create table class

(

班级编号 char(3) primary key,

班级名 varchar(30) not null,

班主任 varchar(20) not null,

专业 varchar(30) not null,

入学日期 datetime null

)

创建学生表 student

create table student

(

学号 char(4) not null primary key,

姓名 varchar(20) not null,

性别 char(2) not null check(性别 in('男','女')),

班级编号 char(3) not null foreign key references class(班级编号),

出生日期 datetime not null,

联系电话 char(11) null

)

创建授课表 teaching

create table teaching

(

授课编号 int not null primary key identity (1,1),

课程编号 char(3) not null foreign key references course(课程编号),

教师编号 char(3) not null foreign key references teacher(教师编号),

班级编号 char(3) not null foreign key references class(班级编号),

学期 char(6) not null

)

创建成绩表 score

create table score

(

成绩编号 int not null primary key identity (1,1),

学号 char(4) not null foreign key references student(学号),

授课编号 int not null foreign key references teaching(授课编号),

成绩 tinyint not null,

备注 varchar(200) null

)

                   表数据的插入

提示:数据纯属虚构

插入教师数据

insert into teacher

values('J01','刘志宝','男','1998-8-2','15947292828')

insert into teacher

values('J02','许丽','女','1980-3-5','15925402563')

insert into teacher

values('J03','李欣','女','1995-1-9','13412657687')

insert into teacher

values('J04','张岚婷','女','1999-5-8','15947992828')

insert into teacher

values('J05','秦卓','男','1996-8-9','15012364596')

查询教师数据

select * from teacher

插入课程数据

insert into course values('K01','网络数据库','SQL数据库开发与管理',6)

insert into course values('K02','计算机基础','计算机网络基础',8)

insert into course values('K03','前端开发与使用','WEB程序设计',5)

insert into course values('K04','后端维护与使用','linux系统管理',7)

insert into course values('K05','java基础','java基础应用',4)

查询课程数据

select * from course

插入班级数据

insert into class values('B01','网络1班','杨振荣','计算机网络','2021-8-30')

insert into class values('B02','网络2班','李志飞','计算机程序设计','2020-9-1')

insert into class values('B03','网络3班','王建豪','网络维护','2021-9-5')

查询班级数据

select * from class

插入学生数据

insert into student values('X001','张英','女','B03','2002-5-15','17874569654')

insert into student values('X002','李明','男','B01','2001/6/12','15947584562')

insert into student values('X003','李宏远','男','B03','1999/1/13','15125465869')

insert into student values('X004','刘凯','男','B01','2000/1/22','15858693652')

insert into student values('X005','陈浩飞','男','B03','2001/6/5','13125401269')

insert into student values('X006','李洁丽','女','B01','2002/9/6','14756192545')

insert into student values('X007','张成刚','男','B03','1998/8/8','13412655856')

insert into student values('X008','王丽','女','B03','2000/7/6','15125473669')

insert into student values('X009','高天舞','女','B01','2001/9/16','15947365959')

insert into student values('X010','赵广军','男','B01','2002/5/26','15125434447')

查询学生数据

select * from student

insert into teaching (课程编号,教师编号,班级编号,学期) values('K01','J02','B01','2021上')

insert into teaching (课程编号,教师编号,班级编号,学期) values('K03','J02','B03','2021下')

insert into teaching (课程编号,教师编号,班级编号,学期) values('K03','J05','B03','2021下')

insert into teaching (课程编号,教师编号,班级编号,学期) values('K01','J03','B01','2020上')

insert into teaching (课程编号,教师编号,班级编号,学期) values('K02','J05','B03','2020下')

查询

select * from teaching

插入成绩数据

insert into score (学号,授课编号,成绩,备注) values('X001','2','78','及格')

insert into score (学号,授课编号,成绩,备注) values('X002','1','89','良好')

insert into score (学号,授课编号,成绩,备注) values('X003','2','75','及格')

insert into score (学号,授课编号,成绩,备注) values('X004','4','95','优秀')

insert into score (学号,授课编号,成绩,备注) values('X005','5','92','优秀')

insert into score (学号,授课编号,成绩,备注) values('X006','1','57','不及格')

insert into score (学号,授课编号,成绩,备注) values('X007','3','68','及格')

insert into score (学号,授课编号,成绩,备注) values('X008','3','87','良好')

insert into score (学号,授课编号,成绩,备注) values('X009','4','97','优秀')

insert into score (学号,授课编号,成绩,备注) values('X010','1','100','优秀')

insert into score (学号,授课编号,成绩,备注) values('X005','2','90','优秀')

insert into score (学号,授课编号,成绩,备注) values('X007','2','59','不及格')

insert into score (学号,授课编号,成绩,备注) values('X002','4','89','良好')

insert into score (学号,授课编号,成绩,备注) values('X004','1','77','及格')

insert into score (学号,授课编号,成绩,备注) values('X009','1','97','优秀')

insert into score (学号,授课编号,成绩,备注) values('X001','3','88','良好')

insert into score (学号,授课编号,成绩,备注) values('X003','5','79','及格')

insert into score (学号,授课编号,成绩,备注) values('X008','2','68','及格')

insert into score (学号,授课编号,成绩,备注) values('X007','5','78','及格')

insert into score (学号,授课编号,成绩,备注) 

values('X003','3','76','及格')