创建数据表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
);
#创建一个grades数据库,用于存储grade表
mysql> create database grades;
Query OK, 1 row affected (0.00 sec)
mysql> use grades;
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.01 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','Adma','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 | Adma | 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 | Adma | 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 * from grade where sex='m';
+----+------+-----------+----------+---------+------+---------+
| id | sex | firstname | lastname | english | math | chinese |
+----+------+-----------+----------+---------+------+---------+
| 1 | m | John | Smith | 88 | 85 | 82 |
| 3 | m | Allen | William | 88 | 92 | 95 |
| 4 | m | George | William | 62 | 58 | 72 |
| 6 | m | Kevin | Miller | 77 | 88 | 99 |
| 8 | m | Andrew | Johnson | 81 | 86 | 88 |
+----+------+-----------+----------+---------+------+---------+
5 rows in set (0.00 sec)
5、查询grade表中id值为2,4,6的学生记录\
第一种
mysql> select * from grade where id=2 or id=4 or id=6;
+----+------+-----------+----------+---------+------+---------+
| id | sex | firstname | lastname | english | math | chinese |
+----+------+-----------+----------+---------+------+---------+
| 2 | f | Adma | 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)
第二种
mysql> select * from grade where id in(2,4,6);
+----+------+-----------+----------+---------+------+---------+
| id | sex | firstname | lastname | english | math | chinese |
+----+------+-----------+----------+---------+------+---------+
| 2 | f | Adma | 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 | Adma | 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 | Adma | 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.00 sec)
17、求出表中英语成绩为80的记录的条数
mysql> select count(1) from grade where english=80.0;
+----------+
| count(1) |
+----------+
| 0 |
+----------+
1 row 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='m';
+-----------+
| sum(math) |
+-----------+
| 409 |
+-----------+
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='f';
+-------------------+
| avg(english) |
+-------------------+
| 81.33333333333333 |
+-------------------+
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='f';
+-----------+
| max(math) |
+-----------+
| 94 |
+-----------+
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 | Adma | 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 , 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 | Adma | 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 * from grade order by sex;
+----+------+-----------+----------+---------+------+---------+
| id | sex | firstname | lastname | english | math | chinese |
+----+------+-----------+----------+---------+------+---------+
| 2 | f | Adma | Smith | 76 | 78 | 90 |
| 5 | f | Alice | Davis | 89 | 94 | 98 |
| 7 | f | Helen | Davis | 79 | 83 | 91 |
| 1 | m | John | Smith | 88 | 85 | 82 |
| 3 | m | Allen | William | 88 | 92 | 95 |
| 4 | m | George | William | 62 | 58 | 72 |
| 6 | m | Kevin | Miller | 77 | 88 | 99 |
| 8 | m | Andrew | Johnson | 81 | 86 | 88 |
+----+------+-----------+----------+---------+------+---------+
8 rows in set (0.00 sec)
27、将grade表按照lastname字段值分组,并计算每个分组中的学生数
mysql> select lastname,count(*) as student from grade group by lastname;
+----------+---------+
| lastname | student |
+----------+---------+
| 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,sum(math) as student from grade group by lastname having sum(math)<100;
+----------+---------+
| lastname | student |
+----------+---------+
| Miller | 88 |
| Johnson | 86 |
+----------+---------+
2 rows in set (0.00 sec)
29、查询grade表中的第3到第6条记录
mysql> select * from grade order by id limit 2,5;
+----+------+-----------+----------+---------+------+---------+
| 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 |
| 7 | f | Helen | Davis | 79 | 83 | 91 |
+----+------+-----------+----------+---------+------+---------+
5 rows in set (0.00 sec)
30、查询grade表中男生平均成绩(三科)大于85的记录
mysql> select * from grade where (english+math+english)/3>85 and sex='f';
+----+------+-----------+----------+---------+------+---------+
| id | sex | firstname | lastname | english | math | chinese |
+----+------+-----------+----------+---------+------+---------+
| 5 | f | Alice | Davis | 89 | 94 | 98 |
+----+------+-----------+----------+---------+------+---------+
1 row in set (0.00 sec)