该楼层疑似违规已被系统折叠 隐藏此楼查看此楼
SQL脚本我已经写好,求高人帮忙。
--创建数据库学生
create database student
go
--使用数据库
use student
go
--创建学生信息表
create table stuInfo
(
sno char(11) not null check(sno like '513%') primary key,
sname char(8) not null,
sex char(2) not null check(sex in ('男','女')),
age int not null check(age between 18 and 50) default(18),
class char(4) not null,
nation char(10) not null
)
go
--创建课程信息表
create table course
(
cid int not null check(cid between 1 and 100) primary key,
cname char(10) not null unique,
ct int not null check(ct between 1 and 200),
tscore int not null default(100),
mark varchar(50)
)
go
--创建学生选课表
create table sc
(
sno char(11) not null,
cid int not null,
score float not null default(0)
)
go
--加约束
--联合主键
alter table sc
add constraint PK_sc primary key(sno,cid)
go
--加外键+级联
alter table sc
add constraint FK_sno foreign key(sno) references stuInfo(sno) on delete cascade on update cascade
go
alter table sc
add constraint FK_cid foreign key(cid) references course(cid) on delete cascade on update cascade
go
--创建权限表
create table login
(
lid int not null identity(1,1) primary key,
admin char(20) not null unique,
pwd char(20) not null,
jb char(6) not null check(jb in ('管理员','学生'))
)
go
--------------------------------------------------------------------------
--插入
insert into stuInfo values('51360522017','张三','男',default,'T25','中国湖北')
insert into course values(1,'s1-java',100,100,null)
insert into sc values('51360522017',1,90)
insert into login values('wgf','123456','管理员')
insert into login values('张三','123456','学生')
go
--查询
select * from stuInfo
select * from course
select * from sc
select * from login
go
-------------------------------------------------------------------------