经典而又简单的student数据库

USE master
Go
IF EXISTS(SELECT * FROM SYSDATABASES WHERE NAME=’Students’)
DROP DATABASE Students
GO

CREATE DATABASE Students –创建学生数据库
ON PRIMARY
(
NAME=’Students_data’, –主数据文件的逻辑名
FILENAME=’F:\DB\Students_data.mdf’, –主数据文件的物理名
SIZE=3MB,
FILEGROWTH=1MB
)

LOG ON
(
NAME=’Student_log’,
FILENAME=’F:\DB\Student_log.ldf’,
SIZE=1MB,
FILEGROWTH=10%
)
GO

USE Students
GO

IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME=’StuInfo’)
DROP TABLE StuInfo
GO
CREATE TABLE StuInfo( – 创建学生信息表

stuId int identity(1,1) primary key not null,
stuName varchar(10) not null,
stuSex char(2) default('男') check(stuSex='男' or stuSex='女') not null,
stuAge int not null,
stuAdr varchar(40) not null,
stuQQ int null,
stuIphone int not null

)
GO

IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME=’StuScore’)
DROP TABLE StuScore
GO
CREATE TABLE StuScore( —创建学生成绩表

stuId int references StuInfo(stuId) not null,
examId int unique not null,
major varchar(40) not null,
course varchar(20) not null,
score int check(score>=0 and score<=100) null

)
GO

select * from StuInfo
select * from StuScore

insert into StuInfo(stuName,stuSex,stuAge,stuAdr,stuQQ,stuIphone)values(‘刘明’,’男’,20,’武汉’,13513413,121331454)
insert into StuInfo(stuName,stuSex,stuAge,stuAdr,stuQQ,stuIphone)values(‘赵哈’,’男’,21,’长沙’,NULL,1451135333)
insert into StuInfo(stuName,stuSex,stuAge,stuAdr,stuQQ,stuIphone)values(‘浅笑’,’女’,22,’武汉’,NULL,1543134354)
insert into StuInfo(stuName,stuSex,stuAge,stuAdr,stuQQ,stuIphone)values(‘孙红’,’女’,22,’北京’,NULL,1541313435)
insert into StuInfo(stuName,stuSex,stuAge,stuAdr,stuQQ,stuIphone)values(‘李缇娜’,’女’,22,’厦门’,NULL,1541341313)
insert into StuInfo(stuName,stuSex,stuAge,stuAdr,stuQQ,stuIphone)values(‘李天’,’男’,23,’福州’,NULL,154121313)

insert into StuScore(stuId,examId,major,course,score)values(7,1001,’计算机’,’软件开发’,88)
insert into StuScore(stuId,examId,major,course,score)values(12,1002,’计算机’,’软件测试’,66)
insert into StuScore(stuId,examId,major,course,score)values(13,1003,’管理’,’电子商务’,77)
insert into StuScore(stuId,examId,major,course,score)values(14,1004,’经济’,’金融’,86)
insert into StuScore(stuId,examId,major,course,score)values(15,1005,’艺术’,’绘画’,66)
insert into StuScore(stuId,examId,major,course,score)values(16,1006,’音乐’,’音乐欣赏’,99)