谭嘉政5.9作业

create table chengji(id int(10) primary key auto_increment,class int(10),name varchar(10),age int(3),chinese int(3),math int(3),english int(3));

alter table chengji rename score;

insert into score values

(1,1832,"linan",18,76,87,68),

(2,1833,"lindan",19,56,78,58),

(3,1832,"liuli",21,66,84,68),

(4,1833,"lidan",18,36,24,18),

(5,1832,"tiancai",20,99,100,100),

(6,1833,"zhazha",22,10,5,8),

(7,1832,"madong",19,72,82,75),

(8,1832,"mali",21,55,58,68),

(9,1832,"pidan",25,36,45,58),

(10,1833,"yuanhua",24,86,94,88),

(11,1833,"liuji",23,2,0,0),

(12,1832,"li'an",22,86,77,98);

1、查询1832班的成绩信息

select * from score where class=1832;

2，查询1833班，语文成绩大于80小于90的成绩信息

select * from score where class=1833 and chinese>80 and chinese<90;

select * from score where class=1833 and chinese between 81 and 89;

3，查询学生表中5到10行的数据

select * from score limit 4,6;

4，显示1832班英语成绩为98，数学成绩为77的姓名与学号,

select id,name from score where class=1832 and english=98 and math=77;

5，查询出1832班成绩并且按语文成绩排序（降序）

select chinese,math,english from score where class=1832 order by chinese;

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

select name,chinese,math from score where chinese<80 and math<80;

7，查询出没有参加语文考试的学生姓名和班级名称。

select name,class from score where chinese is null;

8，求出班上语文成绩不及格的学生姓名

select name,chinese from score where chinese<60;

9，求出每个班的数学平均成绩

select class,avg(math) from score group by class;

10、求出每个班级语文成绩总分 --涉及到每个的时候都需要分组

select class,sum(chinese) from score group by class;

11、将语文成绩不及格的学生成绩改为60分

update score set chinese=60 where chinese<60;

12、三科分数都大于70分的人名和年纪

select name,age from score where chinese>70 and math>70 and english>70;

13、求出英语分数高于70且其它任何一科目大于60分的人和班级

select name,class,chinese,math,english from score where english>70 and (chinese>60 or math>60);

14、统计每个班的人数

select class,count(*) as renshu from score group by class;

15、求每个班数学成绩大于80的人数

select class,count(*) from score where math>80 group by class;

16、求出每个班英语成绩最高的那个人的姓名和班级名称 每个班英语成绩最高

select class,name,max(english) from score group by class;

17、给student表增加3个字段（数据类型及长度自定义，建议要合理），

alter table score add(sex char(3),jiguan varchar(30),phone bigint(20));

18、创建一个”dcs”远程用户授予该用户只有查询的权限

create user xiaomeng identified by "123456";

grant select on *.*to 'xiaomeng'@'%' identified by '123456';