信息数据应用实验报告.doc

信息数据应用实验报告实验5数据库查询（I）实验日期2011-10-26学生姓名11学号2010201245数据准备：use master;gocreate database MyDB;goUSE MyDB;gocreate table class(ID char(3) primary key,name nvarchar(10) not null);goinsert into class values(A01,08信管);insert into class values(A02,09信管);insert into class values(A03,10信管);gocreate table student( ID int identity(1,1) not null, sex nchar(1) not null, classID char(3) not null, score int, birthday datetime, constraint zhujian primary key(ID), constraint scorecheck check(score=0 and score=70 and score=0 and score=30 and 成绩=100;离散值-select班号,班名,姓名,成绩from #tmptablewhere 班号 in (a02,a03)；空值-select班号,班名,姓名,成绩from #tmptableWhere 成绩is not null;字符与日期：通配符查询-select班号,班名,姓名,成绩from #tmptablewhere 班号=a03;-select班号,班名,姓名,成绩from #tmptablewhere 班号like a%;-select班号,班名,姓名,成绩from #tmptablewhere 班号like a00-2;或者 where 班号 like a_1 and 班号 like a_2;或者 where 班号 not like a_3 and 班号 not like a_4;-(4) 多表联合查询select a.classID as 班号,a.className as 班名,b.stuName as 姓名,b.score as 成绩from student as b,class as awhere a.classID=b.classNumber and a.classID not like %1 and b.score between 60 and 100;-(5) TOPSelect top 2 a.score as 成绩,a.stuID as 学号,a.stuName as 姓名,b.className as 班名,b.classID as 班号From student as a,class as bWhere a.classNumber=b.classID-(6) 查询结果排序Select top 4 a.score as 成绩,a.stuID as 学号,a.stuName as 姓名,b.className as 班名,b.classID as 班号From student as a,class as bWhere a.classNumber=b.classID order by score asc;/asc由低到高 desc由高到低-(7) 分组查询，聚合函数(group,compute,compute by)select classNumber,avg(score) as 平均成绩from studentgroup by classNumber;select classNumber,avg(score) as 平均成绩from studentgroup by classNumberhaving classNumber like %3;-select classNumber,avg(score) as 平均成绩from studentgroup by classNumberhaving classNumber in (a02,a03)order by classNumber;-select classNumber,score as 分数,avg(score) as 平均成绩from studentgroup by classNumber,scorehaving classNumber in (a02,a03)order by classNumbercompute sum(score),max(score),min(score),count(score);-select classNumber,score as 分数,avg(score) as 平均成绩from studentgroup by classNumber,scorehaving classNumb