sql数据库第九课

--建库
if exists (select * from sysdatabases where name='StuDB')
drop database StuDB
go
Create database StuDB
on primary
(
 name='StuDB_db',--逻辑名
 filename='D:\project\Student_db.mdf',
 size=5mb,
 maxsize=100mb,
 filegrowth=15%
)
log on
(
 name='StuDB_log',
 filename='D:\project\Student_log.ldf',
 size=2mb,
 filegrowth=1mb
)

--建表
create table StuInfo
(
 StuName varchar(50) not null,
 stuNo varchar(50) not null,
 StuSex varchar(50)  not null,
 StuAge int not null,
 StuSeat int,
 StuAddress varchar(100)
)
alter table StuInfo
add constraint PK_StuNo primary key (StuNo)
alter table StuInfo
add constraint UQ_stuName unique (stuName)
alter table stuInfo
add constraint CK_stuNo check ( stuNo like 'S253[0-9][0-9]')
alter table stuInfo
add constraint DF_stusex default('男')for stusex
alter table StuInfo
add constraint CK_stuAge check (StuAge between 15 and 50)
alter table StuInfo
add constraint CK_stuSeat check(StuSeat between 1 and 30)
alter table StuInfo
add constraint DF_Address default('地址不详')for Address
select * from stuInfo

 

create table StuMark
(
 examNo varchar(50) not null,
 Stuno varchar(50) not null,
 writtenExam int ,
 labExam int
)

alter table stumark
add constraint PK_examNo primary key (examNo)
alter table stumark
add constraint CK_examNo check(examNo like 'E200507[0-9][0-9][0-9][0-9]')
alter table StuMark
add constraint FK_stuno foreign key(Stuno)
references StuInfo(stuNo)
alter table stumark
add constraint CK_writtenExam check(writtenExam between 0 and 100)
alter table stumark
add constraint DF_writtenExam default 0 for writtenExam
alter table stumark
add constraint CK_labExam check(labExam between 0 and 100)
alter table stumark
add constraint DF_labExam default 0 for labExam

--向学员信息表stuInfo插入数据--
insert into stuInfo(stuname,stuno,stuSex,stuAge,stuAddress)
values ('张秋丽','s25301','男',18,'北京海淀')
insert into stuInfo(stuname,stuno,stuSex,stuAge,stuAddress)
values ('李斯文','s25303','女',18,'河南洛阳')
insert into stuInfo(stuname,stuno,stuSex,stuAge)
values ('李文才','s25302','男',31)
insert into stuInfo(stuname,stuno,stuSex,stuAge,stuAddress)
values ('欧阳俊雄','s25304','女',28,'新疆克拉玛依')

--向学员成绩表stuMarks插入数据--
insert into stuMark(ExamNo,stuNo,writtenExam,LabExam)
    values('E2005070001','s25301',80,58)
insert into stuMark(ExamNo,stuNo,writtenExam)
    values('E2005070002','s25302',50)
insert into stuMark(ExamNo,stuNo,writtenExam,LabExam)
    values('E2005070003','s25303',97,82)

select * from stuInfo
select * from stuMark

--1.查询两表的数据--
select * from stuInfo
select * from stuMarks

--2.查询男学员名单--
select * from stuInfo where stuSex='男'

--3.查询笔试成绩优秀的学员情况（成绩在75～100之间)--
select stuNo ,writtenExam from stuMark
where writtenExam between 75 and 100

--4.查询参加本次考试的学员成绩，包括学员姓名，笔试成绩，机试成绩--
select stuName,writtenExam,labExam from  stuInfo
inner join stuMark on stuInfo.stuNo=stuMark.stuNo

--5.统计笔试考试平均分和机试考试平均分--
select avg(writtenExam) as '笔试考平均分',
avg(labexam) as '机试平均分' from stuMark

--6.统计参加本次考试的学员人数
select count(stuNo) as 考试人数 from stuMark

--7.查询没有通过考试的人数（笔试或机试小于60分）--
select count(stuNo) as 未通过的人数 from stuMark  where
labExam<60 or writtenExam<60

--8.查询学员成绩，显示学号，笔试成绩，机试成绩，平均分--
select stuNO as 学号,writtenExam as 笔试,labExam as 机试,
(writtenExam+labExam)/2.0 as 平均分 from stuMark

 --9.排名次（按平均分从高到低排序），显示学号、平均分-- 
select stuNO as 学号,(writtenExam+labExam)/2.0 as 平均分
from stuMark order by 平均分 desc

--10.排名次(按平均分从高到低排序),显示姓名,笔试成绩,机试成绩,平均分-
select stuName as 姓名,writtenExam as 笔试,labExam as 机试,
(writtenExam+labExam)/2.0 as 平均分 from stuInfo
inner join stuMark on stuInfo.stuNo=stuMark.stuNo
order by 平均分 desc

 --11.根据平均分,显示前两名信息,包括姓名、笔试成绩、机试成绩、平均分-
select top 2 stuName as 姓名,writtenExam as 笔试,
labExam as 机试,(writtenExam+labExam)/2.0 as 平均分 from stuInfo
inner join stuMark on stuInfo.stuNo=stuMark.stuNo
order by 平均分 desc

--都提5分--
update stuMark set writtenExam=writtenExam+5
select * from stuMark

--100分封顶（加分后超过100分的，按100分计算）--
update stuMark set writtenExam=100
where writtenExam>100

--查看修改后的数据--
select * from stuMark

--创建登录名
exec sp_addlogin 'teacher',123
exec sp_addlogin 'banzhuren',123456
--创建数据库用户
exec sp_grantdbaccess 'teacher','zs'
exec sp_grantdbaccess 'banzhuren','zs'
--授予数据库用户操作权限
grant select,insert,update,delete on StuInfo to zs

转载于:https://www.cnblogs.com/xyb1117/archive/2012/03/09/2387495.html