《第五部分-练习题1：MySQL单表查询》
（新建语句）在Mysql中执行创建student数据库和添加数据1、查询1832班的成绩信息2、查询1833班，语文成绩大于80小于90的成绩信息3、查询学生表中5到10行的数据4、显示1832班英语成绩为98，数学成绩为77的姓名与学号，5、查询出1832班成绩并且按语文成绩排序(降序)6、查询1833班与1832班，语文成绩与数学成绩都小于80的姓名。7、查询出没有参加语文考试的学生姓名和班级名称。8、求出班上语文成绩不及格的学生姓名9、求出每个班的数学平均成绩10、求出每个班级语文成绩总分--涉及到每个的时候都需要分组11、将语文成绩不及格的学生成绩改为60分12、三科分数都大于70分的人名和年纪13、求出英语分数高于70且其它任何一科目大于60分的人和班级14、统计每个班的人数15、求每个班数学成绩大于80的人数16、求出每个班英语成绩最高的那个人的姓名和班级名称--每个班英语成绩最高17、给student表增加3个字段（数据类型及长度自定义，建议要合理)18、创建一个”dcs”远程用户授予该用户只有查询的权限 （新建语句）在Mysql中执行创建student数据库和添加数据create table student(id int primary key auto_increment,class int,chinese int,english int,math int,name varchar(20),age int,time date); insert into student values(1,1833,86,90,40,'zhangsan',22,'2021-07-03'),(2,1832,55,86,66,'lisi',25,'2021-07-04'),(3,1833,93,57,98,'wangwu',23,'2021-07-03'),(4,1832,84,90,88,'zhaoliu',24,'2021-07-03'),(5,1833,93,57,22,'niuqi',25,'2021-06-18'),(6,1832,84,98,77,'qianba',26,'2021-06-30'),(7,1832,55,57,77,'chenjiu',29,'2021-05-20'),(8,1833,NULL,79,88,'dingshi',31,'2021-04-23'),(9,1832,56,53,49,'sunyi',22,'2021-04-23'),(10,1833,86,88,49,'xiaoer',28,'2021-05-25') alter table student add sex int; update student set sex=1 where id in (1,2,5,7,10);update student set sex=0 where id in (3,4,5,8,9);
1、查询1832班的成绩信息
select * from student where class=1833;
2、查询1833班，语文成绩大于80小于90的成绩信息
select * from student where class=1833 and chinese >80 and chinese <90;
3、查询学生表中5到10行的数据
select * from student limit 4,10;
4、显示1832班英语成绩为98，数学成绩为77的姓名与学号，
select id,name from studentwhere english=98 and math=77 and class=1832;
5、查询出1832班成绩并且按语文成绩排序(降序)
select * from studentwhereclass=1832 order by chinese desc;
6、查询1833班与1832班，语文成绩与数学成绩都小于80的姓名。
select name from studentwhereclass in(1833,1832) and (chinese <80 and math <80) ;
7、查询出没有参加语文考试的学生姓名和班级名称。
select name,class from student where chinese is null;
8、求出班上语文成绩不及格的学生姓名
select name from student where chinese <60;
9、求出每个班的数学平均成绩
select class, avg(math) from student group by class;
10、求出每个班级语文成绩总分--涉及到每个的时候都需要分组
select class, sum(math) from student group by class;
11、将语文成绩不及格的学生成绩改为60分
update student set chinese=60where chinese <60;
12、三科分数都大于70分的人名和年纪
select name,age from student where chinese >70 and english>70 and math>70;
13、求出英语分数高于70且其它任何一科目大于60分的人和班级
【错误】select name,class from student where english >70 and chinese>60 and math>60; 【正确】select name,class from student where english >70 and (chinese>60 or math>60);
14、统计每个班的人数
select class,count(id) from student group by class;
15、求每个班数学成绩大于80的人数
select class,count(id) from student where math>80group by class;
16、求出每个班英语成绩最高的那个人的姓名和班级名称--每个班英语成绩最高
方法一：(select * from (select name ,class,english from student where class in (select class from student group by class) and english in (select max(english) from student group by class)) a order by class desc,english desclimit 1)union all( select * from (select name ,class,english from student where class in (select class from student group by class) and english in (select max(english) from student group by class)) a order by class asc,english desc limit 1);
方法二： select name ,b.english,b.class from student a right join ( select class,max(english) as english from student group by class) b on a.class=b.class and a.english=b.english;
方法三：select a.class,a.name,a.english from (select class,max(english) as english from student group by class) b left join (select name,english,class from student) a on a.class=b.class and a.english=b.english;
方法四：select b.name,b.class from (select max(english) m.class from student group by classa,(select ' from student)b where a.m=b.english and acls-=b.clss
17、给student表增加3个字段（数据类型及长度自定义，建议要合理)
alter table student add(weight int,weight1 varchar(11),weight2 datetime);
18、创建一个”dcs”远程用户授予该用户只有查询的权限
grant select on student.* to 'dcs'@192.168.1.73 identified by '123456';
页:
[1]