1 /*练习：
2 通过代码操作，创建一个数据库，里面新建一个学生信息表
3 内容包括：学号、姓名、性别、体重、年龄和语数外三门课的的分数及所在班级
4 插入20条数据
5 1、查姓王的同学的信息
6 2、分别查每门课的最高分和最低分
7 3、查男同学的所有名字，一列
8 4、查每个班每门课程分别的最高分和最低分
9 */
10 create database student
11 go
12 use student
13 go
14 create table biao
15 (
16 code int,
17 name varchar(20),
18 sex char(10),
19 tizhong decimal(18,1),
20 age int,
21 yuwen decimal(18,1),
22 shuxue decimal(18,1),
23 yingyu decimal(18,1),
24 banji varchar(20),
25 )
26 go
27 insert into biao values(1,'邓紫棋','女',50,25,90,88,70,'一班')
28 insert into biao values(2,'李易峰','男',66,29,89,90,89,'一班')
29 insert into biao values(3,'范冰冰','女',45,35,91,78,94,'一班')
30 insert into biao values(4,'杨幂','女',46,30,92,85,78,'一班')
31 insert into biao values(5,'唐嫣','女',44,33,88,80,87,'一班')
32 insert into biao values(6,'吴亦凡','男',67,26,90,94,62,'一班')
33 insert into biao values(7,'刘诗诗','女',52,28,92,83,95,'一班')
34 insert into biao values(8,'周星驰','男',70,54,94,85,90,'一班')
35 insert into biao values(9,'李小璐','女',48,35,87,86,89,'一班')
36 insert into biao values(10,'柳岩','女',46,36,67,92,67,'一班')
37 insert into biao values(11,'周杰伦','男',62,37,88,65,85,'二班')
38 insert into biao values(12,'鹿晗','男',59,27,91,75,78,'二班')
39 insert into biao values(13,'林志颖','男',65,42,90,90,88,'二班')
40 insert into biao values(14,'谢娜','女',44,35,89,80,91,'二班')
41 insert into biao values(15,'汪峰','男',78,45,94,78,77,'二班')
42 insert into biao values(16,'章子怡','女',49,37,78,91,87,'二班')
43 insert into biao values(17,'吴镇宇','男',79,55,79,87,67,'二班')
44 insert into biao values(18,'陈学冬','男',60,27,93,67,78,'二班')
45 insert into biao values(19,'王祖蓝','男',62,36,89,80,90,'二班')
46 insert into biao values(20,'王宝强','男',60,32,95,90,89,'二班')
47 go
48 --查姓王的同学的所有信息
49 select*from biao where name like '王%'
50 --查语文分数最高的学生的所有信息
51 select top 1*from biao order by yuwen desc
52 --查数学分数最高的学生的所有信息
53 select top 1*from biao order by shuxue desc
54 --查英语分数最高的学生的所有信息
55 select top 1*from biao order by yingyu desc
56
57 --查所有男同学的名字
58 select name from biao where sex='男'
59
60 --查一班的语文最高分和最低分
61 select top 1*from biao where banji='一班' order by yuwen desc --最高分
62 select top 1*from biao where banji='一班' order by yuwen --最低分
63
64 --查一班的数学最高分和最低分
65 select top 1*from biao where banji='一班' order by shuxue desc
66 select top 1*from biao where banji='一班' order by shuxue
67
68 --查一班的英语最高分和最低分
69 select top 1*from biao where banji='一班' order by yingyu desc
70 select top 1*from biao where banji='一班' order by yingyu
71
72 --查二班的语文最高分和最低分
73 select top 1*from biao where banji='二班' order by yuwen desc
74 select top 1*from biao where banji='二班' order by yuwen
75
76 --查二班的数学最高分和最低分
77 select top 1*from biao where banji='二班' order by shuxue desc
78 select top 1*from biao where banji='二班' order by shuxue
79
80 --查二班的英语最高分和最低分
81 select top 1*from biao where banji='二班' order by yingyu desc
82 select top 1*from biao where banji='二班' order by yingyu
