mysql练习
创建表和插入数据
创建一个学生表,插入数据
create table student(
id int,
name varchar(20),
chinese int,
english int,
math int
);
insert into student(id,name,chinese,english,math) values(1, '范建',80,85,90);
insert into student(id,name,chinese,english,math) values(2,'罗况',90,95,95);
insert into student(id,name,chinese,english,math) values(3,'杜子腾',80,96,96);
insert into student(id,name,chinese,english,math) values(4,'范冰',81,97,85);
insert into student(id,name,chinese,english,math) values(5,'申晶冰',85,84,90);
insert into student(id,name,chinese,english,math) values(6,'郝丽海',92,85,87);
insert into student(id,name,chinese,english,math) values(7,'郭迪辉',75,81,80);
insert into student(id,name,chinese,english,math) values(8,'拎壶冲',77,80,79);
insert into student(id,name,chinese,english,math) values(9,'任我行',95,85,85);
insert into student(id,name,chinese,english,math) values(10,'史泰香',94,85,84);
查询和统计
- 查询表中所有学生的信息。
mysql> select * from student;
+------+-----------+---------+---------+------+
| id | name | chinese | english | math |
+------+-----------+---------+---------+------+
| 1 | 范建 | 80 | 85 | 90 |
| 2 | 罗况 | 90 | 95 | 95 |
| 3 | 杜子腾 | 80 | 96 | 96 |
| 4 | 范冰 | 81 | 97 | 85 |
| 5 | 申晶冰 | 85 | 84 | 90 |
| 6 | 郝丽海 | 92 | 85 | 87 |
| 7 | 郭迪辉 | 75 | 81 | 80 |
| 8 | 拎壶冲 | 77 | 80 | 79 |
| 9 | 任我行 | 95 | 85 | 85 |
| 10 | 史泰香 | 94 | 85 | 84 |
+------+-----------+---------+---------+------+
10 rows in set (0.00 sec)
- 查询表中所有学生的姓名和对应的英语成绩。
mysql> select name, english from student;
+-----------+---------+
| name | english |
+-----------+---------+
| 范建 | 85 |
| 罗况 | 95 |
| 杜子腾 | 96 |
| 范冰 | 97 |
| 申晶冰 | 84 |
| 郝丽海 | 85 |
| 郭迪辉 | 81 |
| 拎壶冲 | 80 |
| 任我行 | 85 |
| 史泰香 | 85 |
+-----------+---------+
10 rows in set (0.00 sec)
- 过滤表中重复数据。
select english from student;
select DISTINCT english from student;
select DISTINCT english,name from student;
select english+chinese+math from student;
select english+chinese+math as 总分 from student;
select name,english+chinese+math as 总分 from student;
- 在所有学生英语分数上加10分特长分。
mysql> select name, english+10 from student;
+-----------+------------+
| name | english+10 |
+-----------+------------+
| 范建 | 95 |
| 罗况 | 105 |
| 杜子腾 | 106 |
| 范冰 | 107 |
| 申晶冰 | 94 |
| 郝丽海 | 95 |
| 郭迪辉 | 91 |
| 拎壶冲 | 90 |
| 任我行 | 95 |
| 史泰香 | 95 |
+-----------+------------+
10 rows in set (0.00 sec)
- 统计每个学生的总分。
select english+chinese+math from student;
- 使用别名表示学生分数
mysql> select name, chinese+english+math total from student;
+-----------+-------+
| name | total |
+-----------+-------+
| 范建 | 255 |
| 罗况 | 280 |
| 杜子腾 | 272 |
| 范冰 | 263 |
| 申晶冰 | 259 |
| 郝丽海 | 264 |
| 郭迪辉 | 236 |
| 拎壶冲 | 236 |
| 任我行 | 265 |
| 史泰香 | 263 |
+-----------+-------+
10 rows in set (0.00 sec)
- 查询英语成绩大于90分的同学
mysql> select * from student where english>90;
+------+-----------+---------+---------+------+
| id | name | chinese | english | math |
+------+-----------+---------+---------+------+
| 2 | 罗况 | 90 | 95 | 95 |
| 3 | 杜子腾 | 80 | 96 | 96 |
| 4 | 范冰 | 81 | 97 | 85 |
+------+-----------+---------+---------+------+
3 rows in set (0.00 sec)
- 查询总分大于250分的所有同学
mysql> select * from student where chinese+english+math > 250;
+------+-----------+---------+---------+------+
| id | name | chinese | english | math |
+------+-----------+---------+---------+------+
| 1 | 范建 | 80 | 85 | 90 |
| 2 | 罗况 | 90 | 95 | 95 |
| 3 | 杜子腾 | 80 | 96 | 96 |
| 4 | 范冰 | 81 | 97 | 85 |
| 5 | 申晶冰 | 85 | 84 | 90 |
| 6 | 郝丽海 | 92 | 85 | 87 |
| 9 | 任我行 | 95 | 85 | 85 |
| 10 | 史泰香 | 94 | 85 | 84 |
+------+-----------+---------+---------+------+
8 rows in set (0.00 sec)
- 查询英语分数在 85-95之间的同学。
mysql> select * from student where english between 85 and 95;
+------+-----------+---------+---------+------+
| id | name | chinese | english | math |
+------+-----------+---------+---------+------+
| 1 | 范建 | 80 | 85 | 90 |
| 2 | 罗况 | 90 | 95 | 95 |
| 6 | 郝丽海 | 92 | 85 | 87 |
| 9 | 任我行 | 95 | 85 | 85 |
| 10 | 史泰香 | 94 | 85 | 84 |
+------+-----------+---------+---------+------+
5 rows in set (0.00 sec)
- 查询数学分数为84,90,91的同学。
mysql> select * from student where math in(84, 90, 91);
+------+-----------+---------+---------+------+
| id | name | chinese | english | math |
+------+-----------+---------+---------+------+
| 1 | 范建 | 80 | 85 | 90 |
| 5 | 申晶冰 | 85 | 84 | 90 |
| 10 | 史泰香 | 94 | 85 | 84 |
+------+-----------+---------+---------+------+
3 rows in set (0.01 sec)
- 查询数学分>85,语文分>90的同学。
mysql> select * from student where math>85 and chinese>90;
+------+-----------+---------+---------+------+
| id | name | chinese | english | math |
+------+-----------+---------+---------+------+
| 6 | 郝丽海 | 92 | 85 | 87 |
+------+-----------+---------+---------+------+
1 row in set (0.00 sec)
- 对数学成绩排序后输出。
mysql> select * from student order by math desc;
+------+-----------+---------+---------+------+
| id | name | chinese | english | math |
+------+-----------+---------+---------+------+
| 3 | 杜子腾 | 80 | 96 | 96 |
| 2 | 罗况 | 90 | 95 | 95 |
| 1 | 范建 | 80 | 85 | 90 |
| 5 | 申晶冰 | 85 | 84 | 90 |
| 6 | 郝丽海 | 92 | 85 | 87 |
| 4 | 范冰 | 81 | 97 | 85 |
| 9 | 任我行 | 95 | 85 | 85 |
| 10 | 史泰香 | 94 | 85 | 84 |
| 7 | 郭迪辉 | 75 | 81 | 80 |
| 8 | 拎壶冲 | 77 | 80 | 79 |
+------+-----------+---------+---------+------+
10 rows in set (0.00 sec)
- 对总分排序后输出,然后再按从高到低的顺序输出
mysql> select * from student order by math+chinese+english desc;
+------+-----------+---------+---------+------+
| id | name | chinese | english | math |
+------+-----------+---------+---------+------+
| 2 | 罗况 | 90 | 95 | 95 |
| 3 | 杜子腾 | 80 | 96 | 96 |
| 9 | 任我行 | 95 | 85 | 85 |
| 6 | 郝丽海 | 92 | 85 | 87 |
| 4 | 范冰 | 81 | 97 | 85 |
| 10 | 史泰香 | 94 | 85 | 84 |
| 5 | 申晶冰 | 85 | 84 | 90 |
| 1 | 范建 | 80 | 85 | 90 |
| 7 | 郭迪辉 | 75 | 81 | 80 |
| 8 | 拎壶冲 | 77 | 80 | 79 |
+------+-----------+---------+---------+------+
10 rows in set (0.00 sec)
- 统计一个班级共有多少学生?
mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
| 10 |
+----------+
1 row in set (0.00 sec)
- 统计数学成绩大于90的学生有多少个?
mysql> select count(*) from student where math>90;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
- 统计总分大于250的人数有多少?
mysql> select count(*) from student where chinese+math+english>250;
+----------+
| count(*) |
+----------+
| 8 |
+----------+
1 row in set (0.00 sec)
- 统计一个班级数学总成绩?
mysql> select sum(math) from student;
+-----------+
| sum(math) |
+-----------+
| 871 |
+-----------+
1 row in set (0.00 sec)
- 统计一个班级语文、英语、数学各科的总成绩
mysql> select sum(chinese), sum(math), sum(english) from student;
+--------------+-----------+--------------+
| sum(chinese) | sum(math) | sum(english) |
+--------------+-----------+--------------+
| 849 | 871 | 873 |
+--------------+-----------+--------------+
1 row in set (0.00 sec)
- 统计一个班级语文、英语、数学的成绩总和
mysql> select sum(chinese)+sum(math)+sum(english) from student;
+-------------------------------------+
| sum(chinese)+sum(math)+sum(english) |
+-------------------------------------+
| 2593 |
+-------------------------------------+
1 row in set (0.00 sec)
- 求一个班级数学平均分?
mysql> select avg(math) from student;
+-----------+
| avg(math) |
+-----------+
| 87.1000 |
+-----------+
1 row in set (0.00 sec)
- 求一个班级总分平均分
mysql> select avg(chinese+math+english) from student;
+---------------------------+
| avg(chinese+math+english) |
+---------------------------+
| 259.3000 |
+---------------------------+
1 row in set (0.00 sec)
mysql>
- 求班级最高分和最低分
+---------------------------+---------------------------+
| max(math+chinese+english) | min(math+chinese+english) |
+---------------------------+---------------------------+
| 280 | 236 |
+---------------------------+---------------------------+
1 row in set (0.00 sec)
分组数据
- 为学生表,增加一个班级列,练习分组查询。
alter table student add column class_id int;
注意语法:Oracle中不能有“column”关键字,MySQL中有没有“column”都可以执行。
更新表:
update student set class_id=1 where id<=5;
update student set class_id=2 where id>5;
(update student set class_id=2 where id between 6 and 10;)
- 查出各个班的总分,最高分。
mysql> select distinct class_id, max(chinese+english+math), sum(english)+sum(chinese)+sum(math) total from student group by class_id;
+----------+---------------------------+-------+
| class_id | max(chinese+english+math) | total |
+----------+---------------------------+-------+
| 1 | 280 | 1329 |
| 2 | 265 | 1264 |
+----------+---------------------------+-------+
2 rows in set (0.00 sec)
- 求各个班级 英语的平均分:
mysql> select class_id, avg(english) from student group by class_id;
+----------+--------------+
| class_id | avg(english) |
+----------+--------------+
| 1 | 91.4000 |
| 2 | 83.2000 |
+----------+--------------+
2 rows in set (0.00 sec)
- 查询出班级总分大于1300分的班级ID
mysql> select distinct class_id, sum(english)+sum(chinese)+sum(math) total from student group by class_id having sum(english)+sum(chinese)+sum(math)>1300;
+----------+-------+
| class_id | total |
+----------+-------+
| 1 | 1329 |
+----------+-------+
1 row in set (0.00 sec)