创建数据表grade:
CREATE TABLE grade(
id INT NOT NULL,
sex CHAR(1),
firstname VARCHAR(20) NOT NULL,
lastname VARCHAR(20) NOT NULL,
english FLOAT,
math FLOAT,
chinese FLOAT
);向数据表grade中插入几条数据:
INSERT INTO grade
VALUES (1,'m','John','Smith',88.0,85.0,82.0),
(2,'f','Adam','Smith',76.0,78.0,90.0),
(3,'m','Allen','William',88.0,92.0,95.0),
(4,'m','George','William',62.0,58.0,72.0),
(5,'f','Alice','Davis',89.0,94.0,98.0),
(6,'m','Kevin','Miller',77.0,88.0,99.0),
(7,'f','Helen','Davis',79.0,83.0,91.0),
(8,'m','Andrew','Johnson',81.0,86.0,88.0);
1、查询所有字段
select * from grade;
![](https://i-blog.csdnimg.cn/direct/534816c1e3804af282f0efa1255b9b4c.png)
2、查询grade表中的id,firstname,lastname字段
select id,firstname,lastname from grade;
![](https://i-blog.csdnimg.cn/direct/c7a71cc4791540118baa8f7389726c80.png)
3、查询grade表中id大于4的学生姓名
select id,firstname,lastname from grade where id>4;
![](https://i-blog.csdnimg.cn/direct/ddfad3241eca413ab9a1b6693f4c82b1.png)
4、查询grade表中女生的记录
select * from grade where sex='f';
![](https://i-blog.csdnimg.cn/direct/7ab5817e80a34873b8c838e8bbda1480.png)
5、查询grade表中id值为2,4,6的学生记录
select * from grade where id in (2,4,6);
![](https://i-blog.csdnimg.cn/direct/8fdf5ad9b986482a9282accd7d840ece.png)
6、查询grade表中math成绩在85到94之间的记录
select * from grade where math between 85 and 94;
![](https://i-blog.csdnimg.cn/direct/f2a2f332b36d4cf493ce44d76e28b58f.png)
7、查询grade表中firstname以A开头的记录
select * from grade where firstname like 'A%';
![](https://i-blog.csdnimg.cn/direct/e976800e722444f4a8616faf4fa63a50.png)
8、查询grade表中firstname以A开头以e结尾的记录
select * from grade where firstname like 'A%e';
![](https://i-blog.csdnimg.cn/direct/6b3f1c0941a3490e8eb7375c13e32748.png)
9、查询grade表中firstname包含l的记录
select * from grade where firstname like '%l%';
![](https://i-blog.csdnimg.cn/direct/d9ca9357b7f4487b834dc998d2a6a924.png)
10、查询grade表中firstname以A开头后面有4个字符的记录
select * from grade where firstname liike 'A____';
![](https://i-blog.csdnimg.cn/direct/4bed4ac7a9ab43be9cec06fa80f69467.png)
11、查询grade表中english在80到90之间的记录
select * from grade where english between 80 and 90;
![](https://i-blog.csdnimg.cn/direct/56076e8cea9a4a0d9d52a26f0656c52d.png)
12、查询grade表中math大于90 或者 chinese大于90的记录
select * from grade where math>90 or chinese>90;
![](https://i-blog.csdnimg.cn/direct/c92bc6d22f4140d0a36074dab3dc6a90.png)
13、查询grade表中id不是1、3、5、7的记录
select * from grade where id not in (1,3,5,7);
![](https://i-blog.csdnimg.cn/direct/47958375f3814182a316a96d8cd0a614.png)
14、查询grade表中的性别有哪些
select distinct sex from grade;
![](https://i-blog.csdnimg.cn/direct/90df6776b012484e83097cf8f67070fb.png)
15、查询grade表中的lastname有哪几种
select distinct lastname from grade;
![](https://i-blog.csdnimg.cn/direct/82e4f659da9f49579eb271164637fb34.png)
16、求出表中所有记录的条数
select count(1) from grade;
![](https://i-blog.csdnimg.cn/direct/1a15751ade5a4cd3a612a69a26c0cafc.png)
17、求出表中英语成绩大于80的记录的条数
![](https://i-blog.csdnimg.cn/direct/ed6d3dc58ad347b7a3ca4282549b6ba1.png)
18、计算所有学生的数学成绩的和
select sum(math) from grade;
![](https://i-blog.csdnimg.cn/direct/ccae69b41ab84840b305a2681fc91213.png)
19、计算女生的数学成绩的和
select sum(math) from grade where sex='f';
![](https://i-blog.csdnimg.cn/direct/19cdfe80dc0c4f548657dcfd901f7f75.png)
20、计算英语成绩平均分
select avg(english) from grade;
![](https://i-blog.csdnimg.cn/direct/9a27bd31e034415b998148b826056fb1.png)
21、计算男生的英语成绩平均分
select avg(english) from grade where sex='m';
![](https://i-blog.csdnimg.cn/direct/1f10367c7d334902be48529b8af82b0b.png)
22、求出数学成绩的最高分
select max(math) from grade;
![](https://i-blog.csdnimg.cn/direct/7fdbe201b1824b8894e171dc35d7bab5.png)
23、求出男生中的数学最高分
select max(math) from grade where sex='m';
![](https://i-blog.csdnimg.cn/direct/ef7646b7a0794e69b14a04a3e3dcbb79.png)
24、按照math成绩的升序进行排列
select * from grade order by math asc;
![](https://i-blog.csdnimg.cn/direct/16b42fe3118341e09b10fb4af2c4564e.png)
25、按照sex字段的升序和chinese字段的降序排列
select * from grade order by sex asc,chinese desc;
![](https://i-blog.csdnimg.cn/direct/6b1ae3ffafd9416d81e560b2280e1bf3.png)
26、查询数据表grade中的记录,按照sex字段进行分组
select sex,count (1) from grade group by sex;
![](https://i-blog.csdnimg.cn/direct/f5555ecfc10a4cfb8f858fd1e6491369.png)
27、将grade表按照lastname字段值分组,并计算每个分组中的学生数
select lastname,count(1) "学生个数" from grade group by lastname;
![](https://i-blog.csdnimg.cn/direct/93ca8b06698148d08fc1740b66ea0a41.png)
28、对grade表按照lastname字段分组,查出math字段和小于100的组
select lastname,count(1) "学生个数" from grade group by lastname having max(math)<100;
![](https://i-blog.csdnimg.cn/direct/2e7f38e5ffb547c7a7124096bbbe4b97.png)
29、查询grade表中的第3到第6条记录
select * from grade limit 2,4;
![](https://i-blog.csdnimg.cn/direct/0896439395a245598d8b2c070cda60c0.png)
30、查询grade表中男生平均成绩(三科)大于85的记录
select * from grade where (english+math+chinese)/3>85 and sex='m';