计算co1课程的平均成绩mysql_MySQL练习
⼀、表关系
⼆、创建数据库和表
(⼀)创建数据库
创建数据库test2，⽤于存放上述表foreign key references用法
mysql> create database test2 defaultcharset utf8 collate utf8_general_ci;
Query OK,1 row affected (0.00 sec)
(⼆)创建表
创建上述表以及相应的约束
创建班级表
mysql> create tableclass (-> cid int primary key auto_increment comment '主键ID',-> caption varchar(64) not null comment '班级名称'
-> )charset=utf8 collate=utf8_general_ci engine=innodb comment='班级表
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+-----------------+
| Tables_in_test2 |
+-----------------+
| class |
+-----------------+
1 row in set (0.00 sec)
mysql> desc class;
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| cid | int(11) | NO | PRI | NULL | auto_increment |
| caption | varchar(64) | NO | | NULL | |
+---------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
创建学⽣表
mysql> create tablestudent(-> sid int primary key auto_increment comment '主键ID',-> sname varchar(32) not null comment '学⽣名字',-> gender enum('男','⼥') not null default '男' comment '性别',-> class_id int comment '关联班级外键',-> constraint
fk_student_class foreign key(class_id) referencesclass(cid)-> )charset=utf8 collate=utf8_general_ci engine=innodb comment '学⽣表';
Query OK,0 rows affected (0.03sec)
mysql>show tables;+-----------------+
| Tables_in_test2 |
+-----------------+
| class |
| student |
+-----------------+
2 rows in set (0.00sec)
mysql> descstudent;+----------+-----------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-----------------+------+-----+---------+----------------+
| sid | int(11) | NO | PRI | NULL | auto_increment |
| sname | varchar(32) | NO | | NULL | |
| gender | enum('男','⼥') | NO | | 男 | |
| class_id | int(11) | YES | MUL | NULL | |
+----------+-----------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
创建教师表
mysql> create tableteacher(-> tid int primary key auto_increment comment '主键ID',-> tname varchar(32) comment '⽼师名字'
-> )charset=utf8 collate=utf8_general_ci engine=innodb comment='教师表';
Query OK,0 rows affected (0.02sec)
mysql> descteacher;+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| tid | int(11) | NO | PRI | NULL | auto_increment |
| tname | varchar(32) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
创建课程表
mysql> create tablecourse(-> cid int primary key auto_increment comment '课程表',-> cname varchar(32) comment '课程名称',-> uniqueindex_cname(cname),-> teacher_id int comment '关联教师表的外键',-> constraint fk_course_teacher foreign
key(teacher_id) referencesteacher(t
id)-> )charset=utf8 collate=utf8_general_ci engine=innodb comment='课程表';
Query OK,0 rows affected (0.03sec)
mysql> desccourse;+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| cid | int(11) | NO | PRI | NULL | auto_increment |
| cname | varchar(32) | YES | UNI | NULL | |
| teacher_id | int(11) | YES | MUL | NULL | |
+------------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
创建成绩表
mysql> create tablescore(-> sid int primary key auto_increment comment '成绩表',-> student_id int comment '添加关联学⽣表的外键字段',-> constraint fk_score_student foreign key(student_id) referencesstudent(sid),-> course_id int comment '添加关联课程表的外键字段',-> constraint fk_score_course foreign key(course_id) referencescourse(cid),-> unique
index_together(student_id,course_id) comment '将学⽣和成绩进⾏联合唯⼀索引',
-> number float(255,2) comment'学⽣成绩'-> )charset=utf8 collate=utf8_general_ci engine=innodb comment='成绩表';
Query OK, 0 rows affected (0.03 sec)
mysql> desc score;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| sid | int(11) | NO | PRI | NULL | auto_increment |
| student_id | int(11) | YES | MUL | NULL | |
| course_id | int(11) | YES | MUL | NULL | |
| number | float(255,2) | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
三、创建测试数据
班级表添加数据
mysql> insert into class(caption)values('三年⼆班'),('⼀年三班'),('三年⼀班');
Query OK,3 rows affected (0.00sec)
Records:3 Duplicates: 0 Warnings: 0
学⽣表添加数据
mysql> insert into student(sname,gender,class_id) values('钢蛋','⼥',1),('铁锤','⼥',1),('⼭炮','男',2);
Query OK,3 rows affected (0.00sec)
Records:3 Duplicates: 0 Warnings: 0
教师表添加数据
mysql> insert into teacher(tname) values('波多'),('苍空'),('饭岛');
Query OK,3 rows affected (0.00sec)
Records:3 Duplicates: 0 Warnings: 0
课程表添加数据
mysql> insert into course(cname,teacher_id) values('⽣物',1),('体育',1),('物理',2);
Query OK,3 rows affected (0.00sec)
Records:3 Duplicates: 0 Warnings: 0
成绩表添加数据
mysql> insert into score(student_id,course_id,number) values(1,1,60),(1,2,59),(2,2,100);
Query OK,3 rows affected (0.00sec)
Records:3 Duplicates: 0 Warnings: 0
四、操作数据表
1、查询“⽣物”课程⽐“物理”课程成绩⾼的所有学⽣的学号
分析：先查出所有⽣物课程成绩的学⽣；其次查出所有物理课程成绩的学⽣；最后将上述两个结果进⾏相连通过where语句查出符合要求的学⽣
#查出⽣物相关成绩的学⽣
mysql> select s.student_id,came,s.number from course c left join score s onc
.urse_id where came='⽣物';+------------+-------+--------+
| student_id | cname | number |
+------------+-------+--------+
| 1 | ⽣物 | 60.00 |
+------------+-------+--------+
1 row in set (0.00sec)
#查出物理成绩相关学⽣
mysql> select s.student_id,came,s.number from course c left join score s onc
.urse_id where came='物理';+------------+-------+--------+
| student_id | cname | number |
+------------+-------+--------+
| NULL | 物理 | NULL |
+------------+-------+--------+
1 row in set (0.00sec)
#查出符合条件的结果
mysql> select d.student_id,dame,d.number from (selects.student_id,came,s
.number from course c left join score s on c.urse_id where came='⽣物')asd-> left join (select
s.student_id,came,s.number from course c left joinscore son c.urse_id where came='物理') as e on d.student_id=e.student
_idwhere
-> d.number > ifnull(e.number,0);+------------+-------+--------+
| student_id | cname | number |
+------------+-------+--------+
| 1 | ⽣物 | 60.00 |
+------------+-------+--------+
1 row in set (0.01sec)
mysql>
2、查询平均成绩⼤于60分的同学的学号和平均成绩
分析：
先查出所有同学的学号、各科成绩；
其次是分组，根据学⽣的学号进⾏分组；
计算出每⼀个学⽣的平均成绩；
平均成绩⼤于60分的筛选出来；
#查出所有学⽣的学号和各科⽬的成绩
mysql> select sc.student_urse_id,number from student st left joinscore
scon st.sid =sc.student_id;+------------+-----------+--------+
| student_id | course_id | number |
+------------+-----------+--------+
| 1 | 1 | 60.00 |
| 1 | 2 | 59.00 |
| 2 | 2 | 100.00 |
| NULL | NULL | NULL |
+------------+-----------+--------+
4 rows in set (0.00sec)
#进⾏分组
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论