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、子查询（一个查询嵌套另一个查询）
- 标量 子查询（返回一个值）
-
把一个 sql 执行返回的一个值，作为另一个 sql 的一个条件
-
标量子查询可以使用符号 >，>=，<，<=，=，!=，<> 进行比较判断
-
mysql> select * from t_course where id = (select id from t_student where name = "zhangsan"); +------+-------+---------+ | id | score | subject | +------+-------+---------+ | 1 | 80 | English | | 1 | 79 | Chinese | +------+-------+---------+
- 列 子查询 （返回一个列）
- 执行一个 sql 把返回的一个列作为另一个 sql 条件
-
列子查询使用符号 in，not in
# 查询出 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)
- 表 子查询 （返回一个表)
- 执行一个 sql 返回的是一个表
- 如下实例中 a 为别名
mysql> select name from (select * from t_student)a; +----------+ | name | +----------+ | zhangsan | | wangwu | | zhaoliu | | lisi | +----------+
3、多表连接
- 内连接查询（查询两个表都符合条件的数据）
- 关键字 inner join
- 基本格式如下
- select 字段 from 表1 inner join 表2 on 表1.字段 = 表2.字段
# 查询出每个人的英语成绩分别是多少 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 | +----------+-------+---------+
- 左连接查询（左边表中数据显示全部，以左表为准）
- 关键字 left join
- 基本格式如下
- select 字段 from 表1 left join 表2 on 表1.字段 = 表2.字段
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 | +----+----------+------+------+-------+------+-------+---------+
- 右连接查询（右边表中数据显示全部，以右表为准）
- 关键字 right join
- 基本格式如下
- select 字段 from 表1 right join 表2 on 表1.字段 = 表2.字段
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 | +------+----------+------+------+-------+------+-------+---------+