面试准备之SQL 4—— 数据查询

代码
use master
go
-----------创建数据库------------

if exists (select * from sysdatabases where name='stuDB')
drop database stuDB
create database stuDB
on PRimary
(
name='stuDB_data',
filename='D:/stuDB_data.mdf',
size=3mb,
maxsize=10mb,
filegrowth=1mb
)
log on
(
name='stuDB_log',
filename='D:/stuDB_data.ldf',
size=1mb,
filegrowth=1mb
)

-----------创建数据库表------------
use stuDB
go
if exists (select * from sysobjects where name='stuInfo')
drop table stuInfo
create table stuInfo
(
    stuId int identity(1,1) primary key not null,
    stuName varchar(20) not null,
    stuNo varchar(20) not null,
    stuSex char(6) not null,
    stuAge int not null,
    stuAddress text null
)
go

if exists (select * from sysobjects where name='stuMarks')
drop table stuMarks
create table stuMarks
(
    marksId int identity(1,1) primary key not null,
    ExamNo varchar(50) not null, --考号
    stuNo char(6) not null,--学号
    writtenExam int  null,--笔试成绩
    LabExam int null--机试成绩
)
go
--向学员信息表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)

--查看数据--
select * from stuInfo
select * from stuMarks

/*=======查询数据练习=========*/
--1.查询两表的数据--
select * from stuInfo
select * from stuMarks
 --2.查询男学员名单--
select * from stuInfo where stuSex='男'
 --3.查询笔试成绩优秀的学员情况（成绩在75~100之间)--
select * from stumarks where writtenexam between 75 and 100
 --4.查询参加本次考试的学员成绩，包括学员姓名，笔试成绩，机试成绩--
select i.stuName,m.writtenExam,m.LabExam from stuInfo as i inner join stuMarks as m on m.stuNo = i.stuNo
 --5.统计笔试考试平均分和机试考试平均分--
select avg(writtenExam) as 笔试平均成绩,avg(LabExam) as 机试平均成绩 from stuMarks
select avg(writtenExam) 笔试平均成绩,avg(LabExam) 机试平均成绩 from stuMarks
 --6.统计参加本次考试的学员人数
select count(stuno) from stumarks
 --7.查询没有通过考试的人数（笔试或机试小于60分）--
select count(stuno) from stumarks where writtenExam <= 60 or labexam<=60
select * from stumarks where writtenExam is null or labexam is null --查询为全部参加考试的信息
 --8.查询学员成绩，显示学号，笔试成绩，机试成绩，平均分--
select stuno as 学号,writtenExam 笔试,labexam 机试,(writtenExam+labexam)/2 平均成绩 from stumarks
 --9.排名次（按平均分从高到低排序），显示学号、平均分-- 
select stuno as 学号,(writtenExam+labexam)/2 平均成绩 from stumarks order by (writtenExam+labexam)/2 desc

select stuno as 学号,(writtenExam+labexam)/2 平均成绩 from stumarks order by 平均成绩 desc
 --10.排名次（按平均分从高到低排序），显示姓名，笔试成绩，机试成绩，平均分--
select i.stuno as 学号,writtenExam 笔试,labexam 机试,(writtenExam+labexam)/2 平均成绩 
from stumarks as m inner join stuinfo as i on m.stuno = i.stuno order by 平均成绩 desc
--根据以上SQL语句总结：凡是两个表中有同名的列名就需要用别名却分开来，如果没用别名可以直接查询列明

 --11.根据平均分，显示前两名信息，包括姓名、笔试成绩、机试成绩、平均分--
select top 2 i.stuno as 学号,writtenExam 笔试,labexam 机试,(writtenExam+labexam)/2 平均成绩 
from stumarks as m inner join stuinfo as i on m.stuno = i.stuno order by 平均成绩 desc
/*=======修改数据练习=========*/
--都提5分--
--100分封顶（加分后超过100分的，按100分计算）--

update stumarks set writtenExam = writtenExam + 5
update stumarks set writtenExam = 100 where writtenExam>100