1、现有两表如下
# t_student +----+----------+------+------+-------+ | id | name | age | sex | class | +----+----------+------+------+-------+ | 1 | zhangsan | 18 | boy | 1 | | 2 | wangwu | 20 | boy | 1 | | 3 | zhaoliu | 19 | boy | 2 | | 4 | lisi | 22 | girl | NULL | +----+----------+------+------+-------+ # t_course +------+-------+---------+ | id | score | subject | +------+-------+---------+ | 1 | 80 | English | | 1 | 79 | Chinese | | 2 | 90 | English | | 2 | 56 | Chinese | | 3 | 77 | English | | 3 | 100 | Chinese | +------+-------+---------+
2、子查询（一个查询嵌套另一个查询）
mysql> select * from t_course where id = (select id from t_student where name = "zhangsan"); +------+-------+---------+ | id | score | subject | +------+-------+---------+ | 1 | 80 | English | | 1 | 79 | Chinese | +------+-------+---------+
# 查询出 1 班所有学生的成绩信息 mysql> select * from t_course where id in (select id from t_student where class = 1); +------+-------+---------+ | id | score | subject | +------+-------+---------+ | 1 | 80 | English | | 1 | 79 | Chinese | | 2 | 90 | English | | 2 | 56 | Chinese | +------+-------+---------+
SELECT * FROM article WHERE (title,content,uid) = (SELECT title,content,uid FROM blog WHERE bid=2)
mysql> select name from (select * from t_student)a; +----------+ | name | +----------+ | zhangsan | | wangwu | | zhaoliu | | lisi | +----------+
3、多表连接
# 查询出每个人的英语成绩分别是多少 mysql> select a.name,b.score,b.subject from t_student a inner join t_course b on a.id = b.id where subject = "English"; +----------+-------+---------+ | name | score | subject | +----------+-------+---------+ | zhangsan | 80 | English | | wangwu | 90 | English | | zhaoliu | 77 | English | +----------+-------+---------+
mysql> select * from t_student a left join t_course b on a.id = b.id; +----+----------+------+------+-------+------+-------+---------+ | id | name | age | sex | class | id | score | subject | +----+----------+------+------+-------+------+-------+---------+ | 1 | zhangsan | 18 | boy | 1 | 1 | 80 | English | | 1 | zhangsan | 18 | boy | 1 | 1 | 79 | Chinese | | 2 | wangwu | 20 | boy | 1 | 2 | 90 | English | | 2 | wangwu | 20 | boy | 1 | 2 | 56 | Chinese | | 3 | zhaoliu | 19 | boy | 2 | 3 | 77 | English | | 3 | zhaoliu | 19 | boy | 2 | 3 | 100 | Chinese | | 4 | lisi | 22 | girl | NULL | NULL | NULL | NULL | +----+----------+------+------+-------+------+-------+---------+
mysql> select * from t_student a right join t_course b on a.id = b.id; +------+----------+------+------+-------+------+-------+---------+ | id | name | age | sex | class | id | score | subject | +------+----------+------+------+-------+------+-------+---------+ | 1 | zhangsan | 18 | boy | 1 | 1 | 80 | English | | 1 | zhangsan | 18 | boy | 1 | 1 | 79 | Chinese | | 2 | wangwu | 20 | boy | 1 | 2 | 90 | English | | 2 | wangwu | 20 | boy | 1 | 2 | 56 | Chinese | | 3 | zhaoliu | 19 | boy | 2 | 3 | 77 | English | | 3 | zhaoliu | 19 | boy | 2 | 3 | 100 | Chinese | +------+----------+------+------+-------+------+-------+---------+
原文：https://www.cnblogs.com/ZhengYing0813/p/12674518.html