Mysql单表查询30个经典案例

单表查询（30个例子）

新建一个数据表grade

create table `grade`(
	`class` int (5),
	`chinese` int(10),
	`english` int(10),
	`math` int(10),
	`name` varchar(30),
	`age` int(10),
	`sid` int(4) not null auto_increment,
	PRIMARY key (sid)
)

grade表中插入数据

insert into grade (class,chinese,english,math,name,age,sid) VALUES
(1944,72,90,40,'zhangyibo',22,1),(1945,68,80,50,'liuyibo',25,2),(1945,74,20,45,'fuwo',23,3),(1945,44,24,99,'wanghong',25,4),(1946,90,90,90,'zhangqifei',30,5),(1945,80,80,80,'wangfu',28,6);

1、查询1945班的成绩信息

SELECT * from grade where class =1945

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

SELECT class = 1945,chinese from grade where chinese >60 and chinese <90

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

SELECT * from grade limit 1,5;

4，显示1945班英语成绩为80，数学成绩为50的姓名与学号,

SELECT name,sid from grade where english = 80 and math =50

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

desc降序
asc升序
select class,english from grade where class=1945 ORDER BY english desc;

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

SELECT name from grade where class in(1944,1945) and chinese <80 and math <80

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

select name,class from grade where chinese is null and math is null and english is null

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

SELECT name from grade where chinese<60

9，求出每个班的语文平均成绩

select avg(chinese),class from grade GROUP BY class

10、求出每个班级英语成绩总分

select sum(chinese) from grade

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

SELECT name,class FROM grade where (class,english) in (SELECT class,MAX(english) from grade GROUP BY class)

12、英语、语文二科分数都大于70分的人名和年纪

SELECT name,age from grade where chinese >70 and english >70

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

SELECT name,class from grade where chinese >70 and (math>60 or english>60)

14、统计每个班的人数

SELECT COUNT(name),class from grade GROUP BY class

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

SELECT COUNT(chinese),class from grade where chinese>80 GROUP BY class

16、查询姓名为z开头的人的班级姓名年纪

SELECT name,class,age from grade where name like ‘z%’

17、查询三科成绩都及格的班级并显示成绩姓名和年纪、学号

SELECT name,age,sid,class from grade where chinese>60 and english>60 AND math>60

18、查询1944班语文成绩及格人数并显示及格成绩姓名，年纪和ID号

SELECT name,age,sid,chinese FROM grade where class =1944 and chinese>60

19、查询数学分数为99分的班级分数姓名和年纪

SELECT class,math,name,age from grade where math = 99

20、查询语文成绩和数学成绩都不及格的班级成绩姓名年纪

SELECT class,chinese,math,name,age from grade where chinese<60 and math<60

21、查询语文成绩和英语成绩都低于60分的班级，并显示成绩姓名和ID号

SELECT chinese,english,name,sid from grade where chinese<60 AND english<60

22、统计1944班语文成绩总和

select sum(chinese) from grade WHERE class=1944

23、统计1944班语文成绩大于平均成绩的人的姓名，成绩和ID号

select name,chinese,sid from grade where class = 1944 and chinese>(SELECT avg(chinese) from grade where class=1944)

24、查询姓名结尾为u的人，并显示班级姓名

SELECT name,class from grade where name like ‘%u’

25、查询数学成绩分数不为60的班级姓名年纪

select class,name,age from grade where math!=60

26、给表增加2个字段，history和science

alter table grade add (history int(4),science int(4));

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

UPDATE grade set chinese=60 WHERE chinese<60

28、插入新数据

class =1944,wangxiaobo,math=80,chinese=66,english=77,history=98,science=90,sid=8,age=33

INSERT INTO grade ( class, chinese, english, math, NAME, age, sid, history, science )
VALUES(1944,66,77,80,‘wangxiaobo’,33,8,98,90)

29、将liuyifa的english成绩改成99

update grade SET english=99 where name=‘liuyifa’

30.删除liuyibo的数据

DELETE from grade where name=‘liuyibo’