mysql学习笔记_一个学生表

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)