代码 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 学号,writ |