1、查询所有字段
mysql> select * from grade;
2、查询grade表中的id,firstname,lastname字段
mysql> select id,firstname,lastname from grade;
3、查询grade表中id大于4的学生姓名
mysql> select id,firstname,lastname from grade
-> where id>4;
4、查询grade表中女生的记录
mysql> select * from grade
-> where sex='f';
5、查询grade表中id值为2,4,6的学生记录
mysql> select * from grade
-> where id in (2,4,6);
6、查询grade表中math成绩在85到94之间的记录
mysql> select * from grade
-> where math between 85 and 94;
7、查询grade表中firstname以A开头的记录
mysql> select * from grade
-> where firstname like 'A%';
8、查询grade表中firstname以A开头以e结尾的记录
mysql> select * from grade
-> where firstname like 'A%E';
9、查询grade表中firstname包含l的记录
mysql> select * from grade
-> where firstname like '%l%';
10、查询grade表中firstname以A开头后面有4个字符的记录
mysql> select * from grade
-> where firstname like 'A____';
11、查询grade表中english在80到90之间的记录
mysql> select * from grade
-> where english>80 and math>90;
12、查询grade表中math大于90 或者 chinese大于90的记录
mysql> select * from grade
-> where math>90 or chinese>90;
13、查询grade表中id不是1、3、5、7的记录
mysql> select * from grade
-> where id not in (1,3,5,7);
14、查询grade表中的性别有哪些
mysql> select distinct sex from grade;
15、查询grade表中的lastname有哪几种
mysql> select distinct lastname from grade;
16、求出表中所有记录的条数
mysql> select count(*) from grade;
17、求出表中英语成绩的80的记录的条数
mysql> select count(*) from grade
-> where english>80;
18、计算所有学生的数学成绩的和
mysql> select sum(math) from grade;
19、计算女生的数学成绩的和
mysql> select sum(math) from grade
-> where sex='f';
20、计算英语成绩平均分
mysql> select avg(english) from grade;
21、计算男生的英语成绩平均分
mysql> select avg(english) from grade
-> where sex='m';
22、求出数学成绩的最高分
mysql> select max(math) from grade;
23、求出男生中的数学最高分
mysql> select max(math) from grade
-> where sex='m';
24、按照math成绩的升序进行排列
mysql> select * from grade
-> order by math;
25、按照sex字段的升序和chinese字段的降序排列
mysql> select sex,english,math,chinese from grade
-> order by sex,chinese desc;
26、查询数据表grade中的记录,按照sex字段进行分组
mysql> select * from grade
-> group by sex;
27、将grade表按照lastname字段值分组,并计算每个分组中的学生数
mysql> select count(*),lastname from grade
-> group by lastname;
28、对grade表按照lastname字段分组,查出math字段和小于100的组
mysql> select sum(math),lastname from grade
-> group by lastname
-> having sum(math)<100;
29、查询grade表中的第3到第6条记录
mysql> select * from grade
-> limit 2,4;
30、查询grade表中男生平均成绩(三科)大于85的记录
mysql> select id,firstname,lastname avg(math,english,chinese) a from grade
-> group by sex='m'
-> having a>85;