1.创建Account表
create table Account(id int,name varchar(255),class varchar(255));
2.往account里插入数据
insert into account values(1,‘张三‘,‘高三一班‘),(2,‘李四‘,‘高三一班‘),(3,‘王五‘,‘高三二班‘),(4,‘孙六‘,‘高三二班‘);
3.创建exam表
create table exam(id int PRIMARY key,course VARCHAR(255),score int,aid int);
4.往exam里插入数据
insert into exam values(1,‘语文‘,90,1),(2,‘数学‘,95,1),(3,‘英语‘,87,1),
(4,‘语文‘,70,2),(5,‘数学‘,76,2),(6,‘英语‘,92,2),
(7,‘语文‘,75,3),(8,‘数学‘,46,3),(9,‘英语‘,32,3),
(10,‘语文‘,84,4),(11,‘数学‘,82,4),(12,‘英语‘,92,4);
5.查看所有数据库
show batabases;
6.查看所有表
show tables;
7.查看表结构类型
desc Account;
desc eaxm;
8.平均分
SELECT a.class as 班级,e.course as ‘考试科目‘, round(AVG(e.score),2) as ‘平均分‘ from account a,exam e where a.id=e.aid GROUP BY a.class,e.course