--1.切换到master数据库
use master
go
--2.检查删除数据库
if exists(select * from sys.sysdatabases where name='student')
drop database student
go
--3.按照四个步骤创建student数据库
create database student
on
(name=student_data,
filename='c:\student\student_data.mdf')
log on
(name=student_log,
filename='c:\student\student_log.ldf')
go
--4.切换到数据库
use student
go
--5.创建学生信息表stuinfo
create table stuinfo
(stu_id char(3) not null primary key,--主键
stuname nvarchar(10) not null ,
sex bit not null default 0,--默认为男
birth smalldatetime ,
card_id char(18) unique)--身份证唯一
go
--6.创建学生成绩表stuexam
create table stuexam
(stu_id char(3) not null references stuinfo(stu_id),--外键
course nvarchar(20) not null,
score tinyint check(score between 0 and 100)
primary key(stu_id,score)--设置主键
)
go
--7.为stuinfo添加数据
insert into stuinfo
select '001','张小平','0','1980-4-5','420401198004053527'union
select '002','王红','1','1981-12-11','410402198112114528'union
select '003','刘涛','0','1980-7-10','410400198007103545'
go
--8.为stuexam添加数据
insert into stuexam
select '001','c#编程技术','85'union
select '001','sqlserver初级','87'union
select'002','c#编程技术','87'union
select '002','sqlserver初级','65'
go
--9.将001号学生的姓名更新为“张红平”；
update stuinfo
set stuname='张红平'
where stu_id='001'
go
--10.将001号学生的C#编程技术课程的成绩修改为80；
update stuexam
set course='c#编程技术'
where stu_id='sqlserver初级'
go
--11.删除003号学生的记录信息；
delete from stuinfo
where stu_id='003'
go
--12.删除002号学生的Sqlserver初级课程的记录信息
delete from stuexam
where stu_id='002' and course='sqlserver初级'
go
--查询两张表目前数据
select * from stuexam
go
select * from stuinfo
go
