1 /*  通过代码操作:创建一个数据库,里面有一个学生信息表, 2     内容包括:学号,姓名,性别,体重,年龄,语数外三门课分数,班级    插入20条数据 3     执行以下查询操作: 4     1.查姓王的同学的信息 5     2.分别查每门课程最高分,最低分 6     3.查男同学的名字,只查一列 7     4.查每个班每门课程分别最高分最低分*/ 8  9 create database data0110 go11 use data0112 go13 create table xueshengxinxi--注意列之间用,隔开14 (15 [No.] int,16 name varchar(10),17 sex varchar(10),18 age int,19 [weight] decimal(10,2),20 Chinese int,21 Math int,22 English int23 )24 insert into xueshengxinxi values(1,‘张蕾蕾‘,‘女‘,20,50,95,90,97)25 insert into xueshengxinxi values(2,‘李鑫‘,‘男‘,21,65,90,90,88)26 insert into xueshengxinxi values(3,‘刘莉莉‘,‘女‘,21,55,90,80,100)27 insert into xueshengxinxi values(4,‘张峰‘,‘男‘,20,63,85,85,80)28 insert into xueshengxinxi values(5,‘王乐‘,‘男‘,20,66,80,90,73)29 insert into xueshengxinxi values(6,‘王馨茹‘,‘女‘,19,47,99,70,96)30 insert into xueshengxinxi values(7,‘赵矿一‘,‘男‘,20,70,70,60,55)31 insert into xueshengxinxi values(8,‘程依依‘,‘女‘,19,45,87,89,86)32 insert into xueshengxinxi values(9,‘程依儿‘,‘女‘,19,45,90,85,87)33 insert into xueshengxinxi values(10,‘孙厚‘,‘男‘,21,76,75,67,63)34 insert into xueshengxinxi values(11,‘朱磊‘,‘男‘,20,67,70,78,71)35 insert into xueshengxinxi values(12,‘王谦‘,‘男‘,22,71,80,81,74)36 insert into xueshengxinxi values(13,‘孙丽娜‘,‘女‘,21,49,90,71,96)37 insert into xueshengxinxi values(14,‘张乐乐‘,‘女‘,22,51,81,72,84)38 insert into xueshengxinxi values(15,‘李忠‘,‘男‘,21,61,79,81,75)39 insert into xueshengxinxi values(16,‘李艳艳‘,‘女‘,20,48,70,60,98)40 insert into xueshengxinxi values(17,‘王萌萌‘,‘女‘,22,50,90,63,70)41 insert into xueshengxinxi values(18,‘刘星‘,‘男‘,22,66,70,81,66)42 insert into xueshengxinxi values(19,‘邹子冰‘,‘女‘,21,44,70,70,80)43 insert into xueshengxinxi values(20,‘富国庆‘,‘男‘,22,70,80,50,70)44 45 select *from xueshengxinxi46 --查某姓的同学47 select *from xueshengxinxi where name like ‘刘%‘48 --分别查每门课程最高分,最低分49 select top 1 *from xueshengxinxi order by Chinese desc--Chinese最高分50 select top 1 *from xueshengxinxi order by Math desc--Math最高分51 select top 1 *from xueshengxinxi order by English desc--English最高分52 select top 1 *from xueshengxinxi order by Chinese asc--Chinese最低分53 select top 1 *from xueshengxinxi order by Math --Math最低分54 select top 1 *from xueshengxinxi order by English --English最低分55 --    3.查男同学的名字,只查一列56 select name from xueshengxinxi where sex=‘男‘57 --    4.查每个班每门课程分别最高分最低分*/58 --把班级忘了,59 alter table xueshengxinxi add Class varchar(10)--添加班级一列60 update xueshengxinxi set Class=‘12级1班‘ where [No.] between 1 and 761 update xueshengxinxi set Class=‘12级2班‘ where [No.] between 8 and 1462 update xueshengxinxi set Class=‘12级3班‘ where [No.] between 15 and 2063 --查每个班每门课程分别最高分最低分64 select top 1 *from xueshengxinxi where Class=‘12级1班‘ order by English65 select top 1 *from xueshengxinxi where Class=‘12级1班‘ order by Chinese66 select top 1 *from xueshengxinxi where Class=‘12级1班‘ order by Math67 select top 1 *from xueshengxinxi where Class=‘12级1班‘ order by English desc68 select top 1 *from xueshengxinxi where Class=‘12级1班‘ order by Chinese desc69 select top 1 *from xueshengxinxi where Class=‘12级1班‘ order by Math desc70 71 select top 1 *from xueshengxinxi where Class=‘12级2班‘ order by English72 select top 1 *from xueshengxinxi where Class=‘12级2班‘ order by Chinese73 select top 1 *from xueshengxinxi where Class=‘12级2班‘ order by Math74 select top 1 *from xueshengxinxi where Class=‘12级2班‘ order by English desc75 select top 1 *from xueshengxinxi where Class=‘12级2班‘ order by Chinese desc76 select top 1 *from xueshengxinxi where Class=‘12级2班‘ order by Math desc77 78 select top 1 *from xueshengxinxi where Class=‘12级3班‘ order by English79 select top 1 *from xueshengxinxi where Class=‘12级3班‘ order by Chinese80 select top 1 *from xueshengxinxi where Class=‘12级3班‘ order by Math81 select top 1 *from xueshengxinxi where Class=‘12级3班‘ order by English desc82 select top 1 *from xueshengxinxi where Class=‘12级3班‘ order by Chinese desc83 select top 1 *from xueshengxinxi where Class=‘12级3班‘ order by Math desc