mysql> select * fromscore;+-----+-----------+-------+
| sid | course_id | score |
+-----+-----------+-------+
| 1 | 1 | 76 |
| 1 | 2 | 90 |
| 1 | 3 | 82 |
| 1 | 5 | 56 |
| 2 | 2 | 78 |
| 2 | 3 | 77 |
| 2 | 4 | 92 |
| 2 | 5 | 65 |
| 3 | 1 | 48 |
| 3 | 2 | 95 |
| 3 | 3 | 75 |
| 3 | 4 | 89 |
| 3 | 5 | 92 |
| 4 | 3 | 78 |
| 4 | 4 | 67 |
| 5 | 1 | 75 |
| 5 | 3 | 90 |
| 5 | 4 | 82 |
| 6 | 2 | 58 |
| 6 | 4 | 88 |
| 7 | 1 | 55 |
| 7 | 2 | 65 |
| 7 | 3 | 63 |
| 7 | 4 | 68 |
| 7 | 5 | 70 |
| 8 | 4 | 88 |
| 8 | 5 | 100 |
+-----+-----------+-------+
27 rows in set (0.00sec)
mysql> select * fromstudents;+-----+--------+--------+---------+
| sid | sname | gender | dept_id |
+-----+--------+--------+---------+
| 1 | Andrew | 0 | 1 |
| 2 | Andy | 0 | 1 |
| 3 | Bob | 0 | 1 |
| 4 | Ruth | 1 | 2 |
| 5 | Mike | 0 | 2 |
| 6 | John | 0 | 3 |
| 7 | Cindy | 1 | 3 |
| 8 | Susan | 1 | 3 |
+-----+--------+--------+---------+
8 rows in set (0.00sec)
mysql> select * fromcourse;+----+-------------+------------+
| id | course_name | teacher_id |
+----+-------------+------------+
| 1 | math | 3 |
| 2 | english | 2 |
| 3 | chinese | 4 |
| 4 | history | 1 |
| 5 | biology | 5 |
+----+-------------+------------+
5 rows in set (0.01sec)
mysql> select * fromteacher;+----+-----------+---------+
| id | name | dept_id |
+----+-----------+---------+
| 1 | Zhang san | 1 |
| 2 | Li si | 1 |
| 3 | Wang wu | 2 |
| 4 | Liu liu | 3 |
| 5 | Ding qi | 3 |
+----+-----------+---------+
5 rows in set (0.00sec)
mysql>desc teacher;+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(64) | YES | | NULL | |
| dept_id | int(11) | NO | MUL | NULL | |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.01sec)
mysql> select * from course a inner join teacher b on a.teacher_id=b.id where b.name='zhang san';+----+-------------+------------+----+-----------+---------+
| id | course_name | teacher_id | id | name | dept_id |
+----+-------------+------------+----+-----------+---------+
| 4 | history | 1 | 1 | Zhang san | 1 |
+----+-------------+------------+----+-----------+---------+
1 row in set (0.00sec)
mysql> select * from score where course_id in (select a.id from course a inner join teacher b on a.teacher__id=b.id where b.name='zhang san');+-----+-----------+-------+
| sid | course_id | score |
+-----+-----------+-------+
| 2 | 4 | 92 |
| 3 | 4 | 89 |
| 4 | 4 | 67 |
| 5 | 4 | 82 |
| 6 | 4 | 88 |
| 7 | 4 | 68 |
| 8 | 4 | 88 |
+-----+-----------+-------+
7 rows in set (0.00sec)
mysql> select * from score where course_id not in (select a.id from course a inner join teacher b on a.teaccher_id=b.id where b.name='zhang san');+-----+-----------+-------+
| sid | course_id | score |
+-----+-----------+-------+
| 1 | 1 | 76 |
| 1 | 2 | 90 |
| 1 | 3 | 82 |
| 1 | 5 | 56 |
| 2 | 2 | 78 |
| 2 | 3 | 77 |
| 2 | 5 | 65 |
| 3 | 1 | 48 |
| 3 | 2 | 95 |
| 3 | 3 | 75 |
| 3 | 5 | 92 |
| 4 | 3 | 78 |
| 5 | 1 | 75 |
| 5 | 3 | 90 |
| 6 | 2 | 58 |
| 7 | 1 | 55 |
| 7 | 2 | 65 |
| 7 | 3 | 63 |
| 7 | 5 | 70 |
| 8 | 5 | 100 |
+-----+-----------+-------+
20 rows in set (0.00sec)
mysql> select aa.sid,bb.sname from score aa inner join students bb on aa.sid=bb.sid where course_id not in (select a.id from course a inner join teacher b on a.teacher_id=b.id where b.name='zhang san');+-----+--------+
| sid | sname |
+-----+--------+
| 1 | Andrew |
| 1 | Andrew |
| 1 | Andrew |
| 1 | Andrew |
| 2 | Andy |
| 2 | Andy |
| 2 | Andy |
| 3 | Bob |
| 3 | Bob |
| 3 | Bob |
| 3 | Bob |
| 4 | Ruth |
| 5 | Mike |
| 5 | Mike |
| 6 | John |
| 7 | Cindy |
| 7 | Cindy |
| 7 | Cindy |
| 7 | Cindy |
| 8 | Susan |
+-----+--------+
20 rows in set (0.00sec)
mysql> select aa.sid,bb.sname,score from score aa inner join students bb on aa.sid=bb.sid where course_id not in (select a.id from course a inner join teacher b on a.teacher_id=b.id where b.name='zhang san');+-----+--------+-------+
| sid | sname | score |
+-----+--------+-------+
| 1 | Andrew | 76 |
| 1 | Andrew | 90 |
| 1 | Andrew | 82 |
| 1 | Andrew | 56 |
| 2 | Andy | 78 |
| 2 | Andy | 77 |
| 2 | Andy | 65 |
| 3 | Bob | 48 |
| 3 | Bob | 95 |
| 3 | Bob | 75 |
| 3 | Bob | 92 |
| 4 | Ruth | 78 |
| 5 | Mike | 75 |
| 5 | Mike | 90 |
| 6 | John | 58 |
| 7 | Cindy | 55 |
| 7 | Cindy | 65 |
| 7 | Cindy | 63 |
| 7 | Cindy | 70 |
| 8 | Susan | 100 |
+-----+--------+-------+
20 rows in set (0.00sec)
mysql> select distinct aa.sid,bb.sname,score from score aa inner join students bb on aa.sid=bb.sid where coourse_id not in (select a.id from course a inner join teacher b on a.teacher_id=b.id where b.name='zhang saan');+-----+--------+-------+
| sid | sname | score |
+-----+--------+-------+
| 1 | Andrew | 76 |
| 1 | Andrew | 90 |
| 1 | Andrew | 82 |
| 1 | Andrew | 56 |
| 2 | Andy | 78 |
| 2 | Andy | 77 |
| 2 | Andy | 65 |
| 3 | Bob | 48 |
| 3 | Bob | 95 |
| 3 | Bob | 75 |
| 3 | Bob | 92 |
| 4 | Ruth | 78 |
| 5 | Mike | 75 |
| 5 | Mike | 90 |
| 6 | John | 58 |
| 7 | Cindy | 55 |
| 7 | Cindy | 65 |
| 7 | Cindy | 63 |
| 7 | Cindy | 70 |
| 8 | Susan | 100 |
+-----+--------+-------+
20 rows in set (0.00sec)
mysql> select distinct aa.sid,bb.sname from score aa inner join students bb on aa.sid=bb.sid where course_id not in (select a.id from course a inner join teacher b on a.teacher_id=b.id where b.name='zhang san');+-----+--------+
| sid | sname |
+-----+--------+
| 1 | Andrew |
| 2 | Andy |
| 3 | Bob |
| 4 | Ruth |
| 5 | Mike |
| 6 | John |
| 7 | Cindy |
| 8 | Susan |
+-----+--------+
8 rows in set (0.01sec)
mysql> select distinct aa.sid,bb.sname from score aa inner join students bb on aa.sid=bb.sid where course_id in (select a.id from course a inner join teacher b on a.teacher_id=b.id where b.name='zhang san');+-----+-------+
| sid | sname |
+-----+-------+
| 2 | Andy |
| 3 | Bob |
| 4 | Ruth |
| 5 | Mike |
| 6 | John |
| 7 | Cindy |
| 8 | Susan |
+-----+-------+
7 rows in set (0.00sec)
mysql> select a.id from course a inner join teacher b on a.teacher_id=b.id where b.name='zhang san';+----+
| id |
+----+
| 4 |
+----+
1 row in set (0.00sec)mysql> select * from score where course_id in (4);+-----+-----------+-------+
| sid | course_id | score |
+-----+-----------+-------+
| 2 | 4 | 92 |
| 3 | 4 | 89 |
| 4 | 4 | 67 |
| 5 | 4 | 82 |
| 6 | 4 | 88 |
| 7 | 4 | 68 |
| 8 | 4 | 88 |
+-----+-----------+-------+
7 rows in set (0.00sec)
mysql> select * from score where course_id not in (4);+-----+-----------+-------+
| sid | course_id | score |
+-----+-----------+-------+
| 1 | 1 | 76 |
| 1 | 2 | 90 |
| 1 | 3 | 82 |
| 1 | 5 | 56 |
| 2 | 2 | 78 |
| 2 | 3 | 77 |
| 2 | 5 | 65 |
| 3 | 1 | 48 |
| 3 | 2 | 95 |
| 3 | 3 | 75 |
| 3 | 5 | 92 |
| 4 | 3 | 78 |
| 5 | 1 | 75 |
| 5 | 3 | 90 |
| 6 | 2 | 58 |
| 7 | 1 | 55 |
| 7 | 2 | 65 |
| 7 | 3 | 63 |
| 7 | 5 | 70 |
| 8 | 5 | 100 |
+-----+-----------+-------+
20 rows in set (0.00sec)
mysql> select aa.sid from score aa where course_id in (select a.id from course a inner join teacher b on a.teacher_id=b.id where b.name='zhang san');+-----+
| sid |
+-----+
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
+-----+
7 rows in set (0.00sec)
mysql> Select b.sid From course a inner Join score b on a.id=b.course_id-> Inner join teacher c on a.teacher_id=c.id Where c.name='Zhang san'
->;+-----+
| sid |
+-----+
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
+-----+
7 rows in set (0.00sec)
mysql> select * fromstudents;+-----+--------+--------+---------+
| sid | sname | gender | dept_id |
+-----+--------+--------+---------+
| 1 | Andrew | 0 | 1 |
| 2 | Andy | 0 | 1 |
| 3 | Bob | 0 | 1 |
| 4 | Ruth | 1 | 2 |
| 5 | Mike | 0 | 2 |
| 6 | John | 0 | 3 |
| 7 | Cindy | 1 | 3 |
| 8 | Susan | 1 | 3 |
+-----+--------+--------+---------+
8 rows in set (0.00sec)
mysql> select sid,sname from students where Sid not in
-> (select b.sid From course a inner Join score b on a.id=b.course_id-> Inner join teacher c on a.teacher_id=c.id Where c.name='Zhang san');+-----+--------+
| sid | sname |
+-----+--------+
| 1 | Andrew |
+-----+--------+
1 row in set (0.00 sec)
114
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
