day35work
-- 班级表
mysql> create table class(
-> cid int auto_increment primary key,
-> caption int not null
-> )charset utf8;
Query OK, 0 rows affected (0.41 sec)
-- 学生表
mysql> create table student(
-> sid int not null primary key,
-> sname varchar(255) not null default 1,
-> gender enum('male', 'female'),
-> class_id int not null,
-> CONSTRAINT fk_id FOREIGN KEY (class_id) REFERENCES class(cid)
-> )charset utf8;
Query OK, 0 rows affected (0.41 sec)
-- 教师表
mysql> create table teacher(
-> tid int not null primary key,
-> tname varchar(255) not null
-> )charset utf8;
Query OK, 0 rows affected (0.34 sec)
-- 课程表
mysql> create table course(
-> cid int not null primary key,
-> cname varchar(255) not null,
-> teacher_id int not null,
-> constraint fk_tch_id foreign key (teacher_id) references teacher(tid)
-> )charset utf8;
Query OK, 0 rows affected (0.37 sec)
-- 成绩表
mysql> create table score(
-> sid int not null primary key,
-> student_id int not null,
-> course_id int not null,
-> number tinyint not null,
-> constraint fk_stu_id foreign key (student_id) references student(sid),
-> constraint fk_crs_id foreign key (course_id) references course(cid)
-> )charset utf8;
Query OK, 0 rows affected (0.53 sec)
mysql> alter table score add unique key(student_id,course_id);
Query OK, 0 rows affected (0.58 sec)
-- 插入班级
mysql> insert into class values
-> (1, '三年二班'),
-> (2, '三年三班'),
-> (3, '一年二班'),
-> (4, '三年七班');
Query OK, 4 rows affected (0.07 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from class;
+-----+----------+
| cid | capiton |
+-----+----------+
| 1 | 三年二班 |
| 2 | 三年三班 |
| 3 | 一年二班 |
| 4 | 三年七班 |
+-----+----------+
4 rows in set (0.00 sec)
-- 插入学生
mysql> insert into student values
-> (1, '张三', 'male', 1),
-> (2, '李四', 'male', 2),
-> (3, '王五', 'male', 3),
-> (4, '赵六', 'male', 4),
-> (5, '钢蛋', 'female', 4);
mysql> insert into student values
-> (6, '铁锤', 'female', 1),
-> (7, '如花', 'male', 2),
-> (8, '锅炉', 'female', 3),
->
-> (9, '小乔', 'female', 4),
-> (10, '公孙离 ', 'female',1);
Query OK, 5 rows affected (0.05 sec)
mysql> insert into student values
-> (11, '沈雷锴', 'male', 1);
Query OK, 1 row affected (0.06 sec)
mysql> insert into student values
-> (12, 'xzn', 'male', 2),
-> (13, 'cjl', 'male', 3),
-> (14, 'xwj', 'male', 4),
-> (15, 'egon', 'male', 2);
Query OK, 4 rows affected (0.04 sec)
-- 插入老师
mysql> insert into teacher values
-> (1, '张磊'),
-> (2, '李平'),
-> (3, '刘海燕'),
-> (4, '朱云海'),
-> (5, '李杰');
Query OK, 5 rows affected (0.05 sec)
Records: 5 Duplicates: 0 Warnings: 0
-- 插入课程
mysql> insert into course values
-> (1, '生物', 1),
-> (2, '物理', 2),
-> (3, '体育', 3),
-> (4, '美术', 2);
Query OK, 4 rows affected (0.13 sec)
-- 插入成绩
mysql> insert into score values
-> (1, 1, 1, 10),
-> (2, 1, 2, 9),
-> (3, 1, 4, 66),
-> (4, 2, 1, 8),
-> (5, 2, 3, 68),
-> (6, 2, 4, 88),
-> (7, 3, 2, 8),
-> (8, 3, 3, 55),
-> (9, 3, 4, 66),
-> (10, 4, 1, 6),
-> (11, 4, 3, 89),
-> (12, 4, 4, 96),
-> (13, 5, 1, 33),
-> (14, 5, 2, 34),
-> (15, 5, 4, 34);
Query OK, 15 rows affected (0.11 sec)
Records: 15 Duplicates: 0 Warnings: 0
mysql> insert into score values
-> (16,6,1,44),
-> (17,6,2,33),
-> (18,7,3,66),
-> (19,7,4,88),
-> (20,8,1,78),
-> (21,8,2,42),
-> (22,9,3,99),
-> (23,9,4,100),
-> (24,10,1,12),
-> (25,10,4,66),
-> (26,11,1,56),
-> (27,12,1,68),
-> (28,12,2,33);
Query OK, 13 rows affected (0.19 sec)
Records: 13 Duplicates: 0 Warnings: 0
mysql> insert into score values
-> (29,13,1,33),
-> (30,14,1,58),
-> (31,15,1,100);
Query OK, 3 rows affected (0.12 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from class;
+-----+----------+
| cid | capiton |
+-----+----------+
| 1 | 三年二班 |
| 2 | 三年三班 |
| 3 | 一年二班 |
| 4 | 三年七班 |
+-----+----------+
mysql> select * from student;
+-----+---------+--------+----------+
| sid | sname | gender | class_id |
+-----+---------+--------+----------+
| 1 | 张三 | male | 1 |
| 2 | 李四 | male | 2 |
| 3 | 王五 | male | 3 |
| 4 | 赵六 | male | 4 |
| 5 | 钢蛋 | female | 4 |
| 6 | 铁锤 | female | 1 |
| 7 | 如花 | male | 2 |
| 8 | 锅炉 | female | 3 |
| 9 | 小乔 | female | 4 |
| 10 | 公孙离 | female | 1 |
| 11 | 沈雷锴 | male | 1 |
| 12 | xzn | male | 2 |
| 13 | cjl | male | 3 |
| 14 | xwj | male | 4 |
| 15 | egon | male | 2 |
+-----+---------+--------+----------+
mysql> select * from teacher;
+-----+--------+
| tid | tname |
+-----+--------+
| 1 | 张磊 |
| 2 | 李平 |
| 3 | 刘海燕 |
| 4 | 朱云海 |
| 5 | 李杰 |
+-----+--------+
5 rows in set (0.00 sec)
mysql> select * from course;
+-----+-------+------------+
| cid | cname | teacher_id |
+-----+-------+------------+
| 1 | 生物 | 1 |
| 2 | 物理 | 2 |
| 3 | 体育 | 3 |
| 4 | 美术 | 2 |
+-----+-------+------------+
4 rows in set (0.00 sec)
mysql> select * from score;
+-----+------------+-----------+--------+
| sid | student_id | course_id | number |
+-----+------------+-----------+--------+
| 1 | 1 | 1 | 10 |
| 2 | 1 | 2 | 9 |
| 3 | 1 | 4 | 66 |
| 4 | 2 | 1 | 8 |
| 5 | 2 | 3 | 68 |
| 6 | 2 | 4 | 88 |
| 7 | 3 | 2 | 8 |
| 8 | 3 | 3 | 55 |
| 9 | 3 | 4 | 66 |
| 10 | 4 | 1 | 6 |
| 11 | 4 | 3 | 89 |
| 12 | 4 | 4 | 96 |
| 13 | 5 | 1 | 33 |
| 14 | 5 | 2 | 34 |
| 15 | 5 | 4 | 34 |
| 16 | 6 | 1 | 44 |
| 17 | 6 | 2 | 33 |
| 18 | 7 | 3 | 66 |
| 19 | 7 | 4 | 88 |
| 20 | 8 | 1 | 78 |
| 21 | 8 | 2 | 42 |
| 22 | 9 | 3 | 99 |
| 23 | 9 | 4 | 100 |
| 24 | 10 | 1 | 12 |
| 25 | 10 | 4 | 66 |
| 26 | 11 | 1 | 56 |
| 27 | 12 | 1 | 68 |
| 28 | 12 | 2 | 33 |
| 29 | 13 | 1 | 33 |
| 30 | 14 | 1 | 58 |
| 31 | 15 | 1 | 100 |
+-----+------------+-----------+--------+
31 rows in set (0.00 sec)
-- 1. 查询所有大于60分的学生的姓名和学号 (DISTINCT: 去重)
mysql> select distinct student.sid, sname
-> from student left join score on
-> student.sid = score.student_id where
-> number > 60;
+-----+---------+
| sid | sname |
+-----+---------+
| 1 | 张三 |
| 2 | 李四 |
| 3 | 王五 |
| 4 | 赵六 |
| 7 | 如花 |
| 8 | 锅炉 |
| 9 | 小乔 |
| 10 | 公孙离 |
| 12 | xzn |
| 15 | egon |
+-----+---------+
10 rows in set (0.00 sec)
-- 2.查询每个老师教授的课程数量 和 老师信息
mysql> select tid, tname, count(tid)
-> from teacher left join course on
-> tid = teacher_id
-> group by tid;
+-----+--------+------------+
| tid | tname | count(tid) |
+-----+--------+------------+
| 1 | 张磊 | 1 |
| 2 | 李平 | 2 |
| 3 | 刘海燕 | 1 |
| 4 | 朱云海 | 1 |
| 5 | 李杰 | 1 |
+-----+--------+------------+
5 rows in set (0.13 sec)
-- 3. 查询学生的信息以及学生所在的班级信息
mysql> select *
-> from student left join class on
-> class_id = cid;
+-----+---------+--------+----------+------+----------+
| sid | sname | gender | class_id | cid | capiton |
+-----+---------+--------+----------+------+----------+
| 1 | 张三 | male | 1 | 1 | 三年二班 |
| 6 | 铁锤 | female | 1 | 1 | 三年二班 |
| 10 | 公孙离 | female | 1 | 1 | 三年二班 |
| 11 | 沈雷锴 | male | 1 | 1 | 三年二班 |
| 2 | 李四 | male | 2 | 2 | 三年三班 |
| 7 | 如花 | male | 2 | 2 | 三年三班 |
| 12 | xzn | male | 2 | 2 | 三年三班 |
| 15 | egon | male | 2 | 2 | 三年三班 |
| 3 | 王五 | male | 3 | 3 | 一年二班 |
| 8 | 锅炉 | female | 3 | 3 | 一年二班 |
| 13 | cjl | male | 3 | 3 | 一年二班 |
| 4 | 赵六 | male | 4 | 4 | 三年七班 |
| 5 | 钢蛋 | female | 4 | 4 | 三年七班 |
| 9 | 小乔 | female | 4 | 4 | 三年七班 |
| 14 | xwj | male | 4 | 4 | 三年七班 |
+-----+---------+--------+----------+------+----------+
15 rows in set (0.12 sec)
-- 4、学生中男生的个数和女生的个数
mysql> select gender, count(gender)
-> from student
-> group by gender
-> ;
+--------+---------------+
| gender | count(gender) |
+--------+---------------+
| male | 10 |
| female | 5 |
+--------+---------------+
2 rows in set (0.00 sec)
-- 5、获取所有学习'生物'的学生的学号和成绩；姓名
mysql> select student.sid, sname, number
-> from score
-> left join student on
-> score.student_id = student.sid
-> left join course on
-> score.course_id = course.cid where
-> course.cname = '生物';
+------+---------+--------+
| sid | sname | number |
+------+---------+--------+
| 1 | 张三 | 10 |
| 2 | 李四 | 8 |
| 4 | 赵六 | 6 |
| 5 | 钢蛋 | 33 |
| 6 | 铁锤 | 44 |
| 8 | 锅炉 | 78 |
| 10 | 公孙离 | 12 |
| 11 | 沈雷锴 | 56 |
| 12 | xzn | 68 |
| 13 | cjl | 33 |
| 14 | xwj | 58 |
| 15 | egon | 100 |
+------+---------+--------+
12 rows in set (0.11 sec)
-- 6、查询平均成绩大于60分的同学的学号和平均成绩；
mysql> select student.sid, avg(number)
-> from student
-> left join score on
-> student.sid = score.student_id
-> group by student.sid;
+-----+-------------+
| sid | avg(number) |
+-----+-------------+
| 1 | 28.3333 |
| 2 | 54.6667 |
| 3 | 43.0000 |
| 4 | 63.6667 |
| 5 | 33.6667 |
| 6 | 38.5000 |
| 7 | 77.0000 |
| 8 | 60.0000 |
| 9 | 99.5000 |
| 10 | 39.0000 |
| 11 | 56.0000 |
| 12 | 50.5000 |
| 13 | 33.0000 |
| 14 | 58.0000 |
| 15 | 100.0000 |
+-----+-------------+
15 rows in set (0.02 sec)
-- 7、查询姓“李”的老师的个数；
mysql> select count(tname) '姓李的老师的个数'
-> from teacher
-> where tname like '李%';
+------------------+
| 姓李的老师的个数 |
+------------------+
| 2 |
+------------------+
1 row in set (0.00 sec)
-- 8、查询课程成绩小于60分的同学的学号、姓名；
mysql> select distinct student.sid, sname
-> from student left join score on
-> student.sid = score.student_id where
-> number < 60;
+-----+---------+
| sid | sname |
+-----+---------+
| 1 | 张三 |
| 2 | 李四 |
| 3 | 王五 |
| 4 | 赵六 |
| 5 | 钢蛋 |
| 6 | 铁锤 |
| 8 | 锅炉 |
| 10 | 公孙离 |
| 11 | 沈雷锴 |
| 12 | xzn |
| 13 | cjl |
| 14 | xwj |
+-----+---------+
12 rows in set (0.00 sec)
-- 9. 删除学习“叶平”老师课的SC表记录
mysql> delete from score
-> where course_id =(
-> select cid
-> from teacher
-> left join course on
-> teacher.tid = course.teacher_id
-> where tname = '叶平');
Query OK, 0 rows affected (0.00 sec)
-- 10.查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
mysql> select course_id '课程ID', max(number) '最高分' , min(number) '最低分'
-> from score
-> group by course_id;
+--------+--------+--------+
| 课程ID | 最高分 | 最低分 |
+--------+--------+--------+
| 1 | 100 | 6 |
| 2 | 42 | 8 |
| 3 | 99 | 55 |
| 4 | 100 | 34 |
+--------+--------+--------+
4 rows in set (0.00 sec)
-- 11.查询每门课程被选修的学生数
mysql> select cname, count(cname)
-> from score left join course on
-> course.cid = score.course_id
-> group by cname;
+-------+--------------+
| cname | count(cname) |
+-------+--------------+
| 体育 | 5 |
| 物理 | 6 |
| 生物 | 12 |
| 美术 | 8 |
+-------+--------------+
4 rows in set (0.00 sec)
-- 12.查询姓“张”的学生名单；
mysql> select *
-> from student where
-> sname like '张%';
+-----+-------+--------+----------+
| sid | sname | gender | class_id |
+-----+-------+--------+----------+
| 1 | 张三 | male | 1 |
+-----+-------+--------+----------+
1 row in set (0.00 sec)
-- 13.查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
mysql> select course_id, avg(number)
-> from score
-> group by course_id
-> order by avg(number),course_id desc;
+-----------+-------------+
| course_id | avg(number) |
+-----------+-------------+
| 2 | 26.5000 |
| 1 | 42.1667 |
| 3 | 75.4000 |
| 4 | 75.5000 |
+-----------+-------------+
4 rows in set (0.00 sec)
-- 14.查询平均成绩大于85的所有学生的学号、姓名和平均成绩
mysql> select student.sid, student.sname, avg(number)
-> from student
-> left join score on
-> student.sid = score.student_id
-> group by student.sid
-> having avg(number) > 85;
+-----+-------+-------------+
| sid | sname | avg(number) |
+-----+-------+-------------+
| 9 | 小乔 | 99.5000 |
| 15 | egon | 100.0000 |
+-----+-------+-------------+
2 rows in set (0.00 sec)
-- 15.查询课程编号为3且课程成绩在80分以上的学生的学号和姓名；
mysql> select student.sid, student.sname
-> from student
-> left join score on
-> student.sid = score.student_id
-> where
-> course_id = 3 and
-> number > 80;
+-----+-------+
| sid | sname |
+-----+-------+
| 4 | 赵六 |
| 9 | 小乔 |
+-----+-------+
2 rows in set (0.12 sec)
-- 16.查询各个课程及相应的选修人数
mysql> select cname, count(cname)
-> from score left join course on
-> course.cid = score.course_id
-> group by cname;
+-------+--------------+
| cname | count(cname) |
+-------+--------------+
| 体育 | 5 |
| 物理 | 6 |
| 生物 | 12 |
| 美术 | 8 |
+-------+--------------+
4 rows in set (0.00 sec)
-- 17.查询“4”课程分数小于60，按分数降序排列的同学学号
mysql> select student.sid from
-> student left join score on
-> student.sid = score.student_id
->
-> where course_id = 4 and
-> number < 60
-> order by number desc;
+-----+
| sid |
+-----+
| 5 |
+-----+
1 row in set (0.01 sec)
-- 18.删除学号为“2”的同学的“1”课程的成绩
mysql> delete from score
-> where course_id = 1
-> and student_id = 2;
Query OK, 1 row affected (0.04 sec)