1 使用mydb1数据库
use mydb1;//数据库事先已经创建好了
2 创建表头
create table student(
id int,
name varchar(20),
chinese int,
english int,
math int
);
3 插入数据构建一个学生表
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,85,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);
4 查看表
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 | 85 | 79 |
| 9 | 任知了 | 95 | 85 | 85 |
| 10 | 王越 | 94 | 85 | 84 |
+------+-----------+---------+---------+------+
10 rows in set (0.00 sec)
5 每个学生的总分
mysql> select id, name, (chinese+english+math) 总分
-> from student;
+------+-----------+--------+
| id | name | 总分 |
+------+-----------+--------+
| 1 | 何东 | 255 |
| 2 | 权筝 | 280 |
| 3 | 何南 | 272 |
| 4 | 叶坦 | 263 |
| 5 | 何西 | 259 |
| 6 | 丁香 | 264 |
| 7 | 何北 | 236 |
| 8 | 唐娇 | 241 |
| 9 | 任知了 | 265 |
| 10 | 王越 | 263 |
+------+-----------+--------+
6 求英语的平均分
mysql> select avg(english) 英语平均分
-> from student;
+-----------------+
| 英语平均分 |
+-----------------+
| 87.8000 |
+-----------------+
1 row in set (0.02 sec)
7 求最高分和最低分
mysql> select max(chinese+english+math) 最高分, min(chinese+english+math) 最低分
-> from student;
+-----------+-----------+
| 最高分 | 最低分 |
+-----------+-----------+
| 280 | 236 |
+-----------+-----------+
1 row in set (0.00 sec)
8 添加列
mysql> alter table student add column class_id int;
Query OK, 0 rows affected (0.85 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from student;//查看
+------+-----------+---------+---------+------+----------+
| id | name | chinese | english | math | class_id |
+------+-----------+---------+---------+------+----------+
| 1 | 何东 | 80 | 85 | 90 | NULL |
| 2 | 权筝 | 90 | 95 | 95 | NULL |
| 3 | 何南 | 80 | 96 | 96 | NULL |
| 4 | 叶坦 | 81 | 97 | 85 | NULL |
| 5 | 何西 | 85 | 84 | 90 | NULL |
| 6 | 丁香 | 92 | 85 | 87 | NULL |
| 7 | 何北 | 75 | 81 | 80 | NULL |
| 8 | 唐娇 | 77 | 85 | 79 | NULL |
| 9 | 任知了 | 95 | 85 | 85 | NULL |
| 10 | 王越 | 94 | 85 | 84 | NULL |
+------+-----------+---------+---------+------+----------+
9 给新加列分组赋值
mysql> update student set class_id = 1
-> where id between 1 and 5;
Query OK, 5 rows affected (0.07 sec)
Rows matched: 5 Changed: 5 Warnings: 0
mysql> update student set class_id = 2
-> where id between 6 and 10;
Query OK, 5 rows affected (0.05 sec)
Rows matched: 5 Changed: 5 Warnings: 0
mysql> select * from student;
+------+-----------+---------+---------+------+----------+
| id | name | chinese | english | math | class_id |
+------+-----------+---------+---------+------+----------+
| 1 | 何东 | 80 | 85 | 90 | 1 |
| 2 | 权筝 | 90 | 95 | 95 | 1 |
| 3 | 何南 | 80 | 96 | 96 | 1 |
| 4 | 叶坦 | 81 | 97 | 85 | 1 |
| 5 | 何西 | 85 | 84 | 90 | 1 |
| 6 | 丁香 | 92 | 85 | 87 | 2 |
| 7 | 何北 | 75 | 81 | 80 | 2 |
| 8 | 唐娇 | 77 | 85 | 79 | 2 |
| 9 | 任知了 | 95 | 85 | 85 | 2 |
| 10 | 王越 | 94 | 85 | 84 | 2 |
+------+-----------+---------+---------+------+----------+
10 求各班级的英语平均分
mysql> select class_id, avg(english) from student group by class_id;
+----------+--------------+
| class_id | avg(english) |
+----------+--------------+
| 1 | 91.4000 |
| 2 | 84.2000 |
+----------+--------------+
2 rows in set (0.02 sec)