《第五部分-练习题1：MySQL单表查询》

（新建语句）在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 student where english=98 and math=77 and class=1832;

5、查询出1832班成绩并且按语文成绩排序(降序)

select * from student where class=1832 order by chinese desc;

6、查询1833班与1832班，语文成绩与数学成绩都小于80的姓名。

select name from student where class 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=60 where 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>80 group 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 desc limit 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';