USE master

 

 

 

--删除已经存在的student数据库

 

IF EXISTS ( SELECT * FROM dbo.sysdatabases WHERE name = 'student' )

 

       DROP DATABASE student

 

 

 

--创建数据库student

 

CREATE DATABASE student

 

 

 

GO

 

 

 

USE student

 

 

 

--删除已经存在的information数据表

 

IF EXISTS ( SELECT * FROM dbo.sysobjects WHERE name = 'information' AND xtype = 'U' )

 

       DROP TABLE information

 

 

 

--创建数据表information

 

CREATE TABLE information

 

(

 

       s_no                     CHAR(11),                                        --学号

 

       s_name                 VARCHAR(8),                             --姓名

 

       s_sex                   CHAR(2),                                          --性别

 

       s_birth                  DATETIME,                                      --出生年月日

 

       s_address             VARCHAR(50),                                  --家庭住址

 

       s_class                 CHAR(7)                                           --班级

 

)

 

 

 

GO

 

 

 

--删除已经存在的course数据表

 

IF EXISTS ( SELECT * FROM dbo.sysobjects WHERE name = 'course' AND xtype = 'U' )

 

       DROP TABLE course

 

 

 

--创建数据表course

 

CREATE TABLE course

 

(

 

       c_no                    VARCHAR(20),                                  --课程编号

 

       c_name                VARCHAR(30),                                  --课程名称

 

       c_credit                INT,                                                        --学分

 

       c_type                  VARCHAR(10)                                   --课程类型

 

)

 

 

 

GO

 

 

 

--删除已经存在的score数据表

 

IF EXISTS ( SELECT * FROM dbo.sysobjects WHERE name = 'score' AND xtype = 'U' )

 

       DROP TABLE score

 

 

 

--创建数据表score

 

CREATE TABLE score

 

(

 

       s_no                     CHAR(11),                                        --学号

 

       semester                CHAR(6),                                          --学期

 

       c_no                            VARCHAR(20),                                  --课程编号

 

       grade                  DECIMAL(5,1)                                  --成绩

 

)

 

 

 

GO

 

 

 

--插入数据到information数据表中

 

INSERT INTO information VALUES('20021001001', '谭桂香', '女', '1984-07-25', '湖南长沙', '软件021')

 

INSERT INTO information VALUES('20021003010', '刘奇',   '男', '1979-08-02', '广西南宁', '信息021')

 

INSERT INTO information VALUES('20021003011', '刘晶晶', '女', '1985-04-14', '湖南株洲', '信息021')

 

INSERT INTO information VALUES('20021003012', '张以能', '女', '1985-10-02', '湖南长沙', '信息021')

 

INSERT INTO information VALUES('20021003013', '曾建桥', '男', '1984-01-22', '湖南株洲', '信息021')

 

INSERT INTO information VALUES('20021003016', '周天',   '男', '1984-04-20', '广东广州', '信息021')

 

INSERT INTO information VALUES('20031001001', '李婷',   '女', '1986-06-10', '湖北武汉', '软件031')

 

INSERT INTO information VALUES('20031201001', '王玉梅', '女', '1986-05-18', '湖南株洲', '电商031')

 

INSERT INTO information VALUES('20041001001', '谭华',   '男', '1988-11-20', '湖南长沙', '软件041')

 

INSERT INTO information VALUES('20041003012', '罗桂华', '女', '1985-06-15', '湖南株洲', '信息041')

 

 

 

--插入数据到course数据表中

 

INSERT INTO course VALUES('1001dxyy',    '大学英语',             4, '基础课')

 

INSERT INTO course VALUES('1001gdsx',    '高等数学',             4, '基础课')

 

INSERT INTO course VALUES('1002c#',      'C#程序设计',           4, '专业基础课')

 

INSERT INTO course VALUES('1002sjkyl',    '数据库原理与应用',      4, '专业基础课')

 

INSERT INTO course VALUES('1003c#_w',    'C#的windows程序开发', 4, '专业课')

 

INSERT INTO course VALUES('1003rjgc',     '软件工程',             2, '专业课')

 

INSERT INTO course VALUES('1003vbcxsj',   'VB程序设计',          4, '专业课')

 

INSERT INTO course VALUES('1003xmkf',    '项目开发',             2, '专业课')

 

INSERT INTO course VALUES('1203dzswwzjs', '电子商务网站建设',     4, '专业课')

 

 

 

--插入数据到score数据表中

 

INSERT INTO score VALUES('20041003012', '200401', '1003vbcxsj',   95.0)

 

INSERT INTO score VALUES('20031201001', '200401', '1203dzswwzjs', 98.0)

 

INSERT INTO score VALUES('20031001001', '200401', '1002sjkyl',     96.0)

 

INSERT INTO score VALUES('20041001001', '200402', '1001gdsx',     87.0)

 

INSERT INTO score VALUES('20021001001', '200402', '1003xmkf',     87.0)

 

INSERT INTO score VALUES('20021003010', '200401', '1003c#',       78.0)

 

INSERT INTO score VALUES('20021003010', '200401', '1002sjkyl',     87.0)

 

INSERT INTO score VALUES('20021003010', '200402', '1001dxyy',     82.0)

 

INSERT INTO score VALUES('20021003010', '200402', '1001gdsx',     63.0)

 

INSERT INTO score VALUES('20021003010', '200402', '1003rjgc',      75.0)

 

INSERT INTO score VALUES('20021003010', '200402', '1003c#_w',     85.0)

 

INSERT INTO score VALUES('20021003011', '200402', '1003c#_w',     92.0)

 

INSERT INTO score VALUES('20021003016', '200402', '1003c#_w',     76.0)

 

INSERT INTO score VALUES('20021003012', '200402', '1003c#_w',     83.0)

 

INSERT INTO score VALUES('20021003013', '200402', '1003c#_w',     87.0)