Mysql Join-连接查询(上)

认识

多表连接查询,我感觉应该是关系型数据库最能体现其价值和灵活性的地方吧. 我觉得数据库的作用, 归纳起来无非就是存储和查询.

一言蔽之,数据库就是能灵活地存储和查询数据. 存储上, 也是以文件的方式存的(linux观点, 一切皆文件), 文件就是存储数据的载体, 数据库文件跟其他的数据文件区别在于其是有规则的. 有约束和规范, 不像工作那样,很多地方都在瞎搞,正是因为有事先约定, 因此数据库检索数据是非常快速的.

当然存储方式上,我平时自己用的话就单机存即可, 公司的话一般都是分布式存储的, 数据其实都是分布在各台机器上的,什么主从配置之类的. 我平时学习用对于存储倒不太关注,当然也会练习配置下主从.

显然查询时用的最多的, 我们部门到现在都没有真正用上数据库, 每个系统间的书都有些小问题,日常工作都是从各系统导出查询集,让后手工excel进行vlookup, 纯人工做数便是打算离职的主要原因了. 毕竟vlookup+sumifs/contifs/pivote table 的工作效率上,是远不及sql的select ,join, aggregation的. 但二者完成的工作却是一样的,只是效率的差别.

表连常用的有左(外)连接, 右(外)连接, 内连接, 笛卡尔积等. 平时用的vlookup 其实就是左连接. 表与表之间能进行连接,必然有进行连接的依据,即共同的字段(key), 不然就是直接拼接,而非连接了.

• inner join: 两个表的"交集", 即左表与右表通过共同键的方式连接起来中, 共有的记录
• left join: 就是vlookup, 左表记录全部显示, 右表只显示"匹配"上的记录, 右表没有匹配上左表的地方显示为null
• right join: 跟left是一样的,只不过是right视角

我这大半年的业务,都是vlookup 左连接, 基本不用inner join, 为啥, 业务的要求, 就是要以左表为做数的基础, 不然不断从其他表(右表)去匹配数据, 导致很难程序程序化.这里仍然以前面 student, classes 表进行演示.

简单连接查询(inner, left, right)

-- 登录mysql客户端, 查看下使用的数据集
mysql> use student_db;
database changed
mysql> show tables;
+----------------------+
| tables_in_student_db |
+----------------------+
| classes              |
| clock_in             |
| students             |
+----------------------+
3 rows in set (0.04 sec)

mysql> select * from classes;
+----+------+
| id | name |
+----+------+
|  1 | 科学 |
|  2 | 艺术 |
+----+------+
2 rows in set (0.09 sec)

mysql> select * from students;
+----+----------+-----+--------+--------+----------+-----------+
| id | name     | age | height | gender | class_id | is_delete |
+----+----------+-----+--------+--------+----------+-----------+
|  1 | 爱因斯坦 |  18 | 180.00 | 男     |        1 | 0         |
|  2 | 居里夫人 |  18 | 180.00 | 女     |        2 | 1         |
|  3 | 小王子   |  14 | 185.00 | 男     |        1 | 0         |
|  4 | 李银河   |  59 | 175.00 | 男     |        2 | 1         |
|  5 | 黄蓉     |  38 | 160.00 | 女     |        1 | 0         |
|  6 | 冰心     |  28 | 150.00 | 女     |        2 | 1         |
|  7 | 王祖贤   |  18 | 172.00 | 女     |        1 | 1         |
|  8 | 周杰伦   |  36 | null   | 男     |        1 | 0         |
|  9 | 王小波   |  57 | 181.00 | 男     |        2 | 0         |
| 10 | 林徽因   |  25 | 166.00 | 女     |        2 | 0         |
| 11 | 小星     |  33 | 162.00 | 未填写 |        3 | 1         |
| 12 | 张爱玲   |  12 | 180.00 | 女     |        4 | 0         |
| 13 | 冯唐     |  12 | 170.00 | 男     |        4 | 0         |
| 14 | 胡适     |  34 | 176.00 | 男     |        5 | 0         |
+----+----------+-----+--------+--------+----------+-----------+
14 rows in set (0.25 sec)

如果没有测试数据, 就是自己create database / table, 然后 create table .. 然后 insert into tb_name values (), (),...了呀.

关联依据: students 表的 class_id 和 classes 表的 id 表示的是同一个东西(班级id)

补充个写sql的技巧(大致模拟计算机的运行顺序来)

• 先写from tb_name1 as a, tb_name2 as b (取别名)
• 然后再 连接条件 join on ....(用上别名)
• 然后再写where......
• 最后再写select....

select 最后写, 进行分块查询, 再拼接(join, union...), 脑海里时刻有表的形象.

-- 检索出学生和班级能对应上的信息
select s.*, c.*
from students s, classes c  -- as可省略
where s.class_id = c.id;

-- 这种默认的where等值连接, 其实就跟 inner join 是一样的
-- out
+----+----------+-----+--------+--------+----------+-----------+----+------+
| id | name     | age | height | gender | class_id | is_delete | id | name |
+----+----------+-----+--------+--------+----------+-----------+----+------+
|  1 | 爱因斯坦 |  18 | 180.00 | 男     |        1 | 0         |  1 | 科学 |
|  2 | 居里夫人 |  18 | 180.00 | 女     |        2 | 1         |  2 | 艺术 |
|  3 | 小王子   |  14 | 185.00 | 男     |        1 | 0         |  1 | 科学 |
|  4 | 李银河   |  59 | 175.00 | 男     |        2 | 1         |  2 | 艺术 |
|  5 | 黄蓉     |  38 | 160.00 | 女     |        1 | 0         |  1 | 科学 |
|  6 | 冰心     |  28 | 150.00 | 女     |        2 | 1         |  2 | 艺术 |
|  7 | 王祖贤   |  18 | 172.00 | 女     |        1 | 1         |  1 | 科学 |
|  8 | 周杰伦   |  36 | null   | 男     |        1 | 0         |  1 | 科学 |
|  9 | 王小波   |  57 | 181.00 | 男     |        2 | 0         |  2 | 艺术 |
| 10 | 林徽因   |  25 | 166.00 | 女     |        2 | 0         |  2 | 艺术 |
+----+----------+-----+--------+--------+----------+-----------+----+------+

-- inner join on 
select s.*, c.*
from students s
inner join classes c
on s.class_id = c.id;

-- out
+----+----------+-----+--------+--------+----------+-----------+----+------+
| id | name     | age | height | gender | class_id | is_delete | id | name |
+----+----------+-----+--------+--------+----------+-----------+----+------+
|  1 | 爱因斯坦 |  18 | 180.00 | 男     |        1 | 0         |  1 | 科学 |
|  2 | 居里夫人 |  18 | 180.00 | 女     |        2 | 1         |  2 | 艺术 |
|  3 | 小王子   |  14 | 185.00 | 男     |        1 | 0         |  1 | 科学 |
|  4 | 李银河   |  59 | 175.00 | 男     |        2 | 1         |  2 | 艺术 |
|  5 | 黄蓉     |  38 | 160.00 | 女     |        1 | 0         |  1 | 科学 |
|  6 | 冰心     |  28 | 150.00 | 女     |        2 | 1         |  2 | 艺术 |
|  7 | 王祖贤   |  18 | 172.00 | 女     |        1 | 1         |  1 | 科学 |
|  8 | 周杰伦   |  36 | null   | 男     |        1 | 0         |  1 | 科学 |
|  9 | 王小波   |  57 | 181.00 | 男     |        2 | 0         |  2 | 艺术 |
| 10 | 林徽因   |  25 | 166.00 | 女     |        2 | 0         |  2 | 艺术 |
+----+----------+-----+--------+--------+----------+-----------+----+------+

给表, 字段取别名 as

-- 查询前3个艺术班女生的姓名和班级
select s.name as "姓名", c.name as "班级"
from students s
inner join classes c
on s.class_id = c.id
where (s.gender = "女") and (c.name = "艺术")
limit 3;

-- out
+----------+------+
| 姓名     | 班级 |
+----------+------+
| 居里夫人 | 艺术 |
| 冰心     | 艺术 |
| 林徽因   | 艺术 |
+----------+------+
3 rows in set (0.15 sec)

不难发现, 其实sql还是很简单的, 只要你的逻辑是清晰的, 一点一点地查询出来. 其实无非也就两种形式, 多表连接+where+... 或者配合子查询然后再进行union, 套路都是一样的.

-- 查询能对应上班级的学生和班级信息, 并按照班级名称降序
select * 
from students s, classes c
where s.class_id = c.id
order by c.name desc
limit 3;

-- out
+----+--------+-----+--------+--------+----------+-----------+----+------+
| id | name   | age | height | gender | class_id | is_delete | id | name |
+----+--------+-----+--------+--------+----------+-----------+----+------+
|  4 | 李银河 |  59 | 175.00 | 男     |        2 | 1         |  2 | 艺术 |
|  6 | 冰心   |  28 | 150.00 | 女     |        2 | 1         |  2 | 艺术 |
| 10 | 林徽因 |  25 | 166.00 | 女     |        2 | 0         |  2 | 艺术 |
+----+--------+-----+--------+--------+----------+-----------+----+------+
3 rows in set (0.07 sec)

-- 先按班级名降序, 如果班级名相同, 则按身高降序, 再则按学生id升序
select s.name, s.height, c.name
from students s, classes c
where s.class_id = c.id
order by c.name desc, s.height desc, s.id asc
limit 6;

-- out
+----------+--------+------+
| name     | height | name |
+----------+--------+------+
| 王小波   | 181.00 | 艺术 |
| 居里夫人 | 180.00 | 艺术 |
| 李银河   | 175.00 | 艺术 |
| 林徽因   | 166.00 | 艺术 |
| 冰心     | 150.00 | 艺术 |
| 小王子   | 185.00 | 科学 |
+----------+--------+------+
6 rows in set (0.09 sec)

left join 我在工作中每天都在用, 功能跟excel的vlookup一样的, 都以左表为基础进行匹配, 没匹配上则显示为null

-- 从classes表中, 匹配出student的姓名和班级
select s.name as "姓名", c.name as "班级"
from students s 
left join classes c
on s.class_id = c.id;

-- out
+----------+------+
| 姓名     | 班级 |
+----------+------+
| 爱因斯坦 | 科学 |
| 小王子   | 科学 |
| 黄蓉     | 科学 |
| 王祖贤   | 科学 |
| 周杰伦   | 科学 |
| 居里夫人 | 艺术 |
| 李银河   | 艺术 |
| 冰心     | 艺术 |
| 王小波   | 艺术 |
| 林徽因   | 艺术 |
| 小星     | null |
| 张爱玲   | null |
| 冯唐     | null |
| 胡适     | null |
+----------+------+
14 rows in set (0.18 sec)

-- 查询出没有对应班级的学生姓名
select s.name, c.name
from students s
left join classes c
on s.class_id = c.id
having c.name is null;

+--------+------+
| name   | name |
+--------+------+
| 小星   | null |
| 张爱玲 | null |
| 冯唐   | null |
| 胡适   | null |
+--------+------+
4 rows in set (0.05 sec)

-- 过滤条件用where也是可以的
select s.name, c.name 
from students s 
left join classes c 
on s.class_id = c.id
where c.name is null;

where 与 having的区别

• where 是在分组前对数据进行过滤, where 后边不能用聚合函数
• hvaing 是在分组后对数据进行过滤, having 后面可以用聚合函数

• 查询中的执行顺序: from > where > group by 及 聚合函数 > having > order > select

• having 即用来弥补where在group by 时的不足, 因为where > 聚合函数

子查询

所谓子查询,无法就是select 嵌套呀, 这个只要有学过一点编程的小伙伴都立马能get, 就算不会编程, 我看我同事用excel的公式嵌套也很厉害的, 外层一个sumifs, 里面一个vlookup.... 反正原理都一样的.

• 标量子查询: 查询的结果是一个值
• 列子查询: 返回的结果是一个列
• 行子查询: 返回的结果是一个行

-- 标量: 查询出高于平均身高的学生姓名, 身高, 所在班级

-- step 1: 先查出身高均值(标量)
select avg(height) from students;
+-------------+
| avg(height) |
+-------------+
| 172.076923  |
+-------------+
1 row in set (0.07 sec)

-- step2: 嵌套进来呀
select s.name, s.height, c.name
from students s
left join classes c
on class_id = c.id
where s.height > (select avg(height) from students);

+----------+--------+------+
| name     | height | name |
+----------+--------+------+
| 爱因斯坦 | 180.00 | 科学 |
| 小王子   | 185.00 | 科学 |
| 居里夫人 | 180.00 | 艺术 |
| 李银河   | 175.00 | 艺术 |
| 王小波   | 181.00 | 艺术 |
| 张爱玲   | 180.00 | null |
| 胡适     | 176.00 | null |
+----------+--------+------+
7 rows in set (0.11 sec)

列子查询: 即一查多, 关键字: in

-- 查询出, 根据班级id, 所对应的学生名字

-- 分析,一个班级id -> 多个名字, 是一查多(列子查询)
select s.name
from students s
where s.class_id in (select id from classes)

-- out
| name    |
+----------+
| 爱因斯坦 |
| 居里夫人 |
| 小王子   |
| 李银河   |
| 黄蓉     |
| 冰心     |
| 王祖贤   |
| 周杰伦   |
| 王小波   |
| 林徽因   |
+----------+

行级子查询 (将多个字段合成一个行元素)

-- 查询出 年龄最大, 且身高最高的的学生信息
select * 
from students s
where (s.age, s.height) = (select max(age), max(height) from students);

-- out: 可能不存在这样牛逼的记录
empty set

小结

• 数据库最核心的两个功能在于存储和检索数据

• 查询高效的原因在于,数据文件的组织,存储方式等是有规则约定的.

• 执行顺序是from > where > group by 及 聚合函数 > having > order > select

• 常用的表连接有inner join, left join, right join ... 我平时用最多的left join 因为excel的vlookup 原因

• 子查询其实就是sql的嵌套, 根据返回的结果有标量, 列, 行的形式

接下的讲讲自连接, 多表联合查询进阶, 即子查询配合表连接, where, union这些....