Mysql 多表查询

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 |
+------+----------+------+------+-------+------+-------+---------+

 

Mysql 多表查询

上一篇:免费网站监控服务阿里云监控,DNSPod监控,监控宝,360云监控使用对比


下一篇:Oracle NUMBER 当p和s都被省略,则当前可表示的数据为浮点型,可以存储正负数、零值、浮点数等