表 course
mysql> select * from course; +----+-------------+ | id | course_name | +----+-------------+ | 1 | Java | | 2 | MySQL | | 3 | Python | | 4 | Go | | 5 | C++ | +----+-------------+ 5 rows in set (0.00 sec)
表students
mysql> select * from students; +------+--------+------+------+--------+-----------+ | id | name | age | sex | height | course_id | +------+--------+------+------+--------+-----------+ | 1 | Danny | 25 | 1 | 160 | 1 | | 2 | Green | 23 | 1 | 158 | 2 | | 3 | Henry | 23 | 0 | 185 | 1 | | 4 | Jane | 22 | 1 | 162 | 3 | | 5 | Jim | 24 | 0 | 175 | 2 | | 6 | John | 21 | 0 | 172 | 4 | | 7 | Lily | 22 | 1 | 165 | 4 | | 8 | Susan | 23 | 1 | 170 | 5 | | 9 | Thomas | 22 | 0 | 178 | 5 | | 10 | Tom | 23 | 0 | 165 | 5 | +------+--------+------+------+--------+-----------+ 10 rows in set (0.00 sec)
表 stusex
mysql> select * from stusex; +------+------+ | id | sex | +------+------+ | 0 | 女 | | 1 | 男 | +------+------+ 2 rows in set (0.00 sec)
做连接
mysql> select s.id,s.name,s.age, d.sex,c.course_name from students s,stusex d,course c where s.sex = d.id and s.course_id = c.id; +------+--------+------+------+-------------+ | id | name | age | sex | course_name | +------+--------+------+------+-------------+ | 1 | Danny | 25 | 男 | Java | | 2 | Green | 23 | 男 | MySQL | | 3 | Henry | 23 | 女 | Java | | 4 | Jane | 22 | 男 | Python | | 5 | Jim | 24 | 女 | MySQL | | 6 | John | 21 | 女 | Go | | 7 | Lily | 22 | 男 | Go | | 8 | Susan | 23 | 男 | C++ | | 9 | Thomas | 22 | 女 | C++ | | 10 | Tom | 23 | 女 | C++ | +------+--------+------+------+-------------+ 10 rows in set (0.00 sec)