建库、建表练习
View Code
--创建数据库 if exists(select*from sysdatabases where name='school') drop database school create database school on primary ( name='school_db', filename='D:\project\school_db.mdf', size=5mb, maxsize=100, filegrowth=15% ) log on ( name='school_log', filename='D:\project\school_log.ndf', size=3mb, maxsize=50, filegrowth=5% ) go --学生信息表(stuInfo) create table stuInfo ( stuNo varchar(50) primary key not null, stuName varchar(50) not null, stuSex varchar(50) not null default('男'), stuAge int not null, stuSeat int identity(1,1), stuAddress text default('不详') ) alter table stuinfo add constraint ck_stoNo check(stuNo like 's253[0-9][0-9]') alter table stuinfo add constraint ck_stuSex check(stusex between '男'and'女') alter table stuinfo add constraint ck_stuage check(stuage between 15 and 50) alter table stuinfo add constraint ck_stuNo check(stuNo between 1 and 30) --学生成绩表 create table stuMarks ( examNo varchar(50)primary key not null, StuNo varchar(50)not null, writtenExam int default(0), LabExam int default(0) ) alter table stumarks add constraint ck check(examNo like 'E200507[0-9][0-9][0-9][0-9]') alter table stumarks add constraint FK_stuNo foreign key (stuNo) references stuinfo(stuNO) alter table stumarks add constraint ck_wExam check(writtenExam between 0 and 100 ) alter table stumarks add constraint ck_LExam check(LabExam between 0 and 100 ) --插入数据 insert into stuinfo(stuNo,StuName,stusex,stuage,stuaddress) select 's25301','张梅','女',22,'浙江金华' union select 's25312','李四','男',19,'浙江丽水' union select 's25303','王五','男',25,'浙江文章' select* from stuinfo --插入数据 insert into stumarks(examNo,stuNo,writtenExam,LabExam) select 'E2005071111','s25301',100,50 union select 'E2005072222','s25303',77,60 union select 'E2005073333','s25312',80,76 select *from stumarks --向学员信息表stuInfo插入数据-- INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress) VALUES('张秋丽','s25301','男',18,'北京海淀') INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress) VALUES('李斯文','s25303','女',22,'河南洛阳') INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge) VALUES('李文才','s25302','男',31) INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress) VALUES('欧阳俊雄','s25304','男',28,'新疆克拉玛依') --向学员成绩表stuMarks插入数据-- INSERT INTO stuMarks(ExamNo,stuNo,writtenExam,LabExam) VALUES('E2005070001','s25301',80,58) INSERT INTO stuMarks(ExamNo,stuNo,writtenExam) VALUES('E2005070002','s25302',50) INSERT INTO stuMarks(ExamNo,stuNo,writtenExam,LabExam) VALUES('E2005070003','s25303',97,82) --1.查询两表的数据 select * from stuInfo select * from stuMarks --2.查询男学员名单 select * from stuInfo where stuSex='男' --3.查询笔试成绩优秀的学员情况(成绩在75~100之间) select stuNo ,writtenExam from stuMarks where writtenExam between 75 and 100 --4.查询参加本次考试的学员成绩,包括学员姓名,笔试成绩,机试成绩 select stuName,writtenExam,labExam from stuInfo inner join stuMarks on stuInfo.stuNo=stuMarks.stuNo --5.统计笔试考试平均分和机试考试平均分 select avg(writtenExam) as '笔试考平均分', avg(labexam) as '机试平均分' from stuMarks --6.统计参加本次考试的学员人数 select count(stuNo) as 考试人数 from stuMarks --7.查询没有通过考试的人数(笔试或机试小于60分) select count(stuNo) as 未通过的人数 from stuMarks where labExam<60 or writtenExam<60 --8.查询学员成绩,显示学号,笔试成绩,机试成绩,平均分 select stuNO as 学号,writtenExam as 笔试,labExam as 机试, (writtenExam+labExam)/2.0 as 平均分 from stuMarks --9.排名次(按平均分从高到低排序),显示学号、平均分 select stuNO as 学号,(writtenExam+labExam)/2.0 as 平均分 from stuMarks order by 平均分 desc --10.排名次(按平均分从高到低排序),显示姓名,笔试成绩,机试成绩,平均分 select stuName as 姓名,writtenExam as 笔试,labExam as 机试, (writtenExam+labExam)/2.0 as 平均分 from stuInfo inner join stuMarks on stuInfo.stuNo=stuMarks.stuNo order by 平均分 desc --11.根据平均分,显示前两名信息,包括姓名、笔试成绩、机试成绩、平均分 select top 2 stuName as 姓名,writtenExam as 笔试, labExam as 机试,(writtenExam+labExam)/2.0 as 平均分 from stuInfo inner join stuMarks on stuInfo.stuNo=stuMarks.stuNo order by 平均分 desc --都提5分 update stuMarks set writtenExam=writtenExam+5 select * from stuMarks --100分封顶(加分后超过100分的,按100分计算) update stuMarks set writtenExam=100 where writtenExam>100 select * from stuMarks --创建登录 exec sp_addLogin 'likao',123 exec sp_grantdbaccess 'likao','cccc' grant select on stumarks to cccc grant select,update,delete on stuinfo to cccc