数据库的创建

--创建学生数据库
create database SM
use SM
--创建student表
 create table student
(
sno char(9),
sname varchar(8) not null,
ssex char(2) check(ssex in ('男','女')),  --限定性别只能是“男”或者“女”
sbirthday date not null,
sdept varchar(20),
saddress varchar(40) not null,
smark smallint check(smark between 550 and 750), --成绩在550分和750分之间
smemo varchar(300),
primary key(sno)--在表的级别创建主码字段
)
--查看student表并向其中一条记录
select * from student
insert into student values('200502001','刘晓晨','男','05-09-1987','中文','河南郑州二七区',573,'爱好广泛')
delete from student
where sno='200502001'--删除表中一条记录
--创建课程表
create table course
(
Cno char(3),
Cname varchar(20),
Ccredit decimal(2,1) check (ccredit between 1 and 6),--限定学分的约束条件
primary key(cno)
)
--创建成绩表
create table grade
(
sno char(9),
Cno char(3),
regulargrade tinyint check (regulargrade between 0 and 100),--给出成绩的check约束条件
examgrade tinyint check (examgrade between 0 and 100),
totalgrade as regulargrade*20/100+examgrade*80/100,--根据平时成绩和期末考试成绩来计算总成绩
primary key(sno,cno),--让学号和课程编号同时做主键
foreign key (sno) references student(sno),
foreign key (cno) references course(cno)
)
sp_help student--查看新建表的结构和约束条件
select * from grade