【无标题】

创建数据表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
);

mysql> use db_system
Database changed
mysql> 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);
Query OK, 0 rows affected (0.04 sec)
向数据表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);

mysql> 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);
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0

1、查询所有字段
mysql> select *from grade;
±—±-----±----------±---------±--------±-----±--------+
| id | sex | firstname | lastname | english | math | chinese |
±—±-----±----------±---------±--------±-----±--------+
| 1 | m | john | smith | 88 | 85 | 82 |
| 2 | f | adam | smith | 76 | 78 | 90 |
| 3 | m | allen | william | 88 | 92 | 95 |
| 4 | m | george | william | 62 | 58 | 72 |
| 5 | f | alice | davis | 89 | 94 | 98 |
| 6 | m | kevin | miller | 77 | 88 | 99 |
| 7 | f | helen | davis | 79 | 83 | 91 |
| 8 | m | andrew | johnson | 81 | 86 | 88 |
±—±-----±----------±---------±--------±-----±--------+
8 rows in set (0.00 sec)

2、查询grade表中的id,firstname,lastname字段
mysql> select id,firstname,lastname from grade;
±—±----------±---------+
| id | firstname | lastname |
±—±----------±---------+
| 1 | john | smith |
| 2 | adam | smith |
| 3 | allen | william |
| 4 | george | william |
| 5 | alice | davis |
| 6 | kevin | miller |
| 7 | helen | davis |
| 8 | andrew | johnson |
±—±----------±---------+
8 rows in set (0.00 sec)

3、查询grade表中id大于4的学生姓名
mysql> select firstname from grade where id>4;
±----------+
| firstname |
±----------+
| alice |
| kevin |
| helen |
| andrew |
±----------+
4 rows in set (0.00 sec)

4、查询grade表中女生的记录
mysql> select firstname from grade where sex=‘f’;
±----------+
| firstname |
±----------+
| adam |
| alice |
| helen |
±----------+
3 rows in set (0.00 sec)

***5、查询grade表中id值为2,4,6的学生记录
mysql> select * from grade where id in(2,4,6);
±—±-----±----------±---------±--------±-----±--------+
| id | sex | firstname | lastname | english | math | chinese |
±—±-----±----------±---------±--------±-----±--------+
| 2 | f | adam | smith | 76 | 78 | 90 |
| 4 | m | george | william | 62 | 58 | 72 |
| 6 | m | kevin | miller | 77 | 88 | 99 |
±—±-----±----------±---------±--------±-----±--------+
3 rows in set (0.00 sec)

***6、查询grade表中math成绩在85到94之间的记录
mysql> select * from grade where math between 85 and 94;
±—±-----±----------±---------±--------±-----±--------+
| id | sex | firstname | lastname | english | math | chinese |
±—±-----±----------±---------±--------±-----±--------+
| 1 | m | john | smith | 88 | 85 | 82 |
| 3 | m | allen | william | 88 | 92 | 95 |
| 5 | f | alice | davis | 89 | 94 | 98 |
| 6 | m | kevin | miller | 77 | 88 | 99 |
| 8 | m | andrew | johnson | 81 | 86 | 88 |
±—±-----±----------±---------±--------±-----±--------+
5 rows in set (0.00 sec)

7、查询grade表中firstname以A开头的记录
mysql> select *from grade where firstname like ‘a%’;
±—±-----±----------±---------±--------±-----±--------+
| id | sex | firstname | lastname | english | math | chinese |
±—±-----±----------±---------±--------±-----±--------+
| 2 | f | adam | smith | 76 | 78 | 90 |
| 3 | m | allen | william | 88 | 92 | 95 |
| 5 | f | alice | davis | 89 | 94 | 98 |
| 8 | m | andrew | johnson | 81 | 86 | 88 |
±—±-----±----------±---------±--------±-----±--------+
4 rows in set (0.00 sec)

8、查询grade表中firstname以A开头以e结尾的记录
mysql> select *from grade where firstname like ‘a%e’;
±—±-----±----------±---------±--------±-----±--------+
| id | sex | firstname | lastname | english | math | chinese |
±—±-----±----------±---------±--------±-----±--------+
| 5 | f | alice | davis | 89 | 94 | 98 |
±—±-----±----------±---------±--------±-----±--------+
1 row in set (0.00 sec)

9、查询grade表中firstname包含l的记录
mysql> select *from grade where firstname like ‘%l%’;
±—±-----±----------±---------±--------±-----±--------+
| id | sex | firstname | lastname | english | math | chinese |
±—±-----±----------±---------±--------±-----±--------+
| 3 | m | allen | william | 88 | 92 | 95 |
| 5 | f | alice | davis | 89 | 94 | 98 |
| 7 | f | helen | davis | 79 | 83 | 91 |
±—±-----±----------±---------±--------±-----±--------+
3 rows in set (0.00 sec)

10、查询grade表中firstname以A开头后面有4个字符的记录
mysql> select * from grade where firstname like ‘a____’;
±—±-----±----------±---------±--------±-----±--------+
| id | sex | firstname | lastname | english | math | chinese |
±—±-----±----------±---------±--------±-----±--------+
| 3 | m | allen | william | 88 | 92 | 95 |
| 5 | f | alice | davis | 89 | 94 | 98 |
±—±-----±----------±---------±--------±-----±--------+
2 rows in set (0.00 sec)

11、查询grade表中english在80到90之间的记录
mysql> select * from grade where english between 80 and 90 ;
±—±-----±----------±---------±--------±-----±--------+
| id | sex | firstname | lastname | english | math | chinese |
±—±-----±----------±---------±--------±-----±--------+
| 1 | m | john | smith | 88 | 85 | 82 |
| 3 | m | allen | william | 88 | 92 | 95 |
| 5 | f | alice | davis | 89 | 94 | 98 |
| 8 | m | andrew | johnson | 81 | 86 | 88 |
±—±-----±----------±---------±--------±-----±--------+
4 rows in set (0.00 sec)

12、查询grade表中math大于90 或者 chinese大于90的记录

mysql> select * from grade where math>90 or chinese >90;
±—±-----±----------±---------±--------±-----±--------+
| id | sex | firstname | lastname | english | math | chinese |
±—±-----±----------±---------±--------±-----±--------+
| 3 | m | allen | william | 88 | 92 | 95 |
| 5 | f | alice | davis | 89 | 94 | 98 |
| 6 | m | kevin | miller | 77 | 88 | 99 |
| 7 | f | helen | davis | 79 | 83 | 91 |
±—±-----±----------±---------±--------±-----±--------+
4 rows in set (0.00 sec)

13、查询grade表中id不是1、3、5、7的记录
mysql> select * from grade where id not in (1,3,5,7)
-> ;
±—±-----±----------±---------±--------±-----±--------+
| id | sex | firstname | lastname | english | math | chinese |
±—±-----±----------±---------±--------±-----±--------+
| 2 | f | adam | smith | 76 | 78 | 90 |
| 4 | m | george | william | 62 | 58 | 72 |
| 6 | m | kevin | miller | 77 | 88 | 99 |
| 8 | m | andrew | johnson | 81 | 86 | 88 |
±—±-----±----------±---------±--------±-----±--------+
4 rows in set (0.00 sec)

14、查询grade表中的性别有哪些
mysql> select distinct sex from grade;
±-----+
| sex |
±-----+
| m |
| f |
±-----+
2 rows in set (0.00 sec)
15、查询grade表中的lastname有哪几种
mysql> select distinct lastname from grade;
±---------+
| lastname |
±---------+
| smith |
| william |
| davis |
| miller |
| johnson |
±---------+
5 rows in set (0.00 sec)

16、求出表中所有记录的条数
mysql> select count() from grade ;
±---------+
| count() |
±---------+
| 8 |
±---------+
1 row in set (0.01 sec)

17、求出表中英语成绩的80的记录的条数
mysql> select english,count() from grade group by english;
±--------±---------+
| english | count() |
±--------±---------+
| 88 | 2 |
| 76 | 1 |
| 62 | 1 |
| 89 | 1 |
| 77 | 1 |
| 79 | 1 |
| 81 | 1 |
±--------±---------+
7 rows in set (0.00 sec)

18、计算所有学生的数学成绩的和
mysql> select sum(math) from grade ;
±----------+
| sum(math) |
±----------+
| 664 |
±----------+
1 row in set (0.00 sec)

19、计算女生的数学成绩的和
mysql> select sum(math) from grade where sex=‘f’;
±----------+
| sum(math) |
±----------+
| 255 |
±----------+
1 row in set (0.00 sec)

20、计算英语成绩平均分
mysql> select avg(english) from grade ;
±-------------+
| avg(english) |
±-------------+
| 80 |
±-------------+
1 row in set (0.00 sec)

21、计算男生的英语成绩平均分
mysql> select avg(english) from grade where sex=‘m’;
±-------------+
| avg(english) |
±-------------+
| 79.2 |
±-------------+
1 row in set (0.00 sec)
22、求出数学成绩的最高分
mysql> select max(math) from grade
-> ;
±----------+
| max(math) |
±----------+
| 94 |
±----------+
1 row in set (0.00 sec)
23、求出男生中的数学最高分
mysql> select max(math) from grade where sex=‘m’;
±----------+
| max(math) |
±----------+
| 92 |
±----------+
1 row in set (0.00 sec)

24、按照math成绩的升序进行排列
mysql> select * from grade order by math;
±—±-----±----------±---------±--------±-----±--------+
| id | sex | firstname | lastname | english | math | chinese |
±—±-----±----------±---------±--------±-----±--------+
| 4 | m | george | william | 62 | 58 | 72 |
| 2 | f | adam | smith | 76 | 78 | 90 |
| 7 | f | helen | davis | 79 | 83 | 91 |
| 1 | m | john | smith | 88 | 85 | 82 |
| 8 | m | andrew | johnson | 81 | 86 | 88 |
| 6 | m | kevin | miller | 77 | 88 | 99 |
| 3 | m | allen | william | 88 | 92 | 95 |
| 5 | f | alice | davis | 89 | 94 | 98 |
±—±-----±----------±---------±--------±-----±--------+
8 rows in set (0.00 sec)

25、按照sex字段的升序和chinese字段的降序排列
mysql> select * from grade order by sex asc,chinese desc;
±—±-----±----------±---------±--------±-----±--------+
| id | sex | firstname | lastname | english | math | chinese |
±—±-----±----------±---------±--------±-----±--------+
| 5 | f | alice | davis | 89 | 94 | 98 |
| 7 | f | helen | davis | 79 | 83 | 91 |
| 2 | f | adam | smith | 76 | 78 | 90 |
| 6 | m | kevin | miller | 77 | 88 | 99 |
| 3 | m | allen | william | 88 | 92 | 95 |
| 8 | m | andrew | johnson | 81 | 86 | 88 |
| 1 | m | john | smith | 88 | 85 | 82 |
| 4 | m | george | william | 62 | 58 | 72 |
±—±-----±----------±---------±--------±-----±--------+
8 rows in set (0.00 sec)

26、查询数据表grade中的记录，按照sex字段进行分组
mysql> select sex, count() from grade group by sex
-> ;
±-----±---------+
| sex | count() |
±-----±---------+
| m | 5 |
| f | 3 |
±-----±---------+
2 rows in set (0.00 sec)

27、将grade表按照lastname字段值分组，并计算每个分组中的学生数
mysql> select lastname ,count(*) as sutdent from grade group by lastname;
±---------±--------+
| lastname | sutdent |
±---------±--------+
| smith | 2 |
| william | 2 |
| davis | 2 |
| miller | 1 |
| johnson | 1 |
±---------±--------+
5 rows in set (0.00 sec)

28、对grade表按照lastname字段分组，查出math字段和小于100的组
mysql> select lastname ,count(*) as sudtent from grade group by lastname having sum(math) <100;
±---------±--------+
| lastname | sudtent |
±---------±--------+
| miller | 1 |
| johnson | 1 |
±---------±--------+
2 rows in set (0.00 sec)

29、查询grade表中的第3到第6条记录
mysql> select * from grade order by id limit 2,4
-> ;
±—±-----±----------±---------±--------±-----±--------+
| id | sex | firstname | lastname | english | math | chinese |
±—±-----±----------±---------±--------±-----±--------+
| 3 | m | allen | william | 88 | 92 | 95 |
| 4 | m | george | william | 62 | 58 | 72 |
| 5 | f | alice | davis | 89 | 94 | 98 |
| 6 | m | kevin | miller | 77 | 88 | 99 |
±—±-----±----------±---------±--------±-----±--------+
4 rows in set (0.00 sec)

30、查询grade表中男生平均成绩(三科)大于85的记录
mysql> select * from grade where sex=‘m’ and (english+chinese+math)/3>85;
±—±-----±----------±---------±--------±-----±--------+
| id | sex | firstname | lastname | english | math | chinese |
±—±-----±----------±---------±--------±-----±--------+
| 3 | m | allen | william | 88 | 92 | 95 |
| 6 | m | kevin | miller | 77 | 88 | 99 |
±—±-----±----------±---------±--------±-----±--------+
2 rows in set (0.00 sec)