数据库_days3_连接查询

什么是连接查询?

连接查询是将两个或两个以上的表按某个条件连接起来，从中选取需要的数据。连接查询是同时查询两个或两个以上的表时使用的。当不同的表中存在相同意义的字段时，可以通过该字段连接这几个表

内连接查询

简介

• JOIN|CROSS JOIN| INNER JOIN
• 通过ON连接条件
• 显示两个表中符合连接条件的记录

测试内连接查询

查询cms_user id,username：

• provinces,proName；相当于for循环嵌套，不是我们想要的结果

SELECT cms_user.id,username,proName FROM cms_user,provinces;

• 通过=链接

SELECT cms_user.id,username,proName FROM cms_user,provinces
WHERE cms_user.proId=provinces.id;

结果：
+----+----------+---------+
| id | username | proName |
+----+----------+---------+
|  1 | 张三         | 北京        |
|  2 | 张三丰         | 上海       |
|  3 | 章子怡         | 深圳        |
|  4 | long     | 广州        |
|  5 | ring     | 上海       |
|  6 | queen    | 深圳        |
|  7 | king     | 重庆        |
|  8 | blek     | 北京        |
|  9 | rose     | 上海       |
| 10 | lily     | 上海       |
| 11 | john     | 上海       |
| 12 | test1    | 北京        |
+----+----------+---------+

• 通过 JOIN|CROSS JOIN| INNER JOIN 连接

SELECT u.id,u.username,u.email,u.sex,p.proName
FROM cms_user AS u
INNER JOIN provinces AS p
ON u.proId=p.id;

SELECT u.id,u.username,u.email,u.sex,p.proName
FROM provinces AS p
CROSS JOIN cms_user AS u
ON u.proId=p.id;

SELECT u.id,u.username,u.email,u.sex,p.proNam
FROM provinces AS p
JOIN cms_user AS u
ON u.proId=p.id;

• 查询cms_user id,username,sex;
  查询provinces proName;
  条件是cms_user的性别为男的用户，根据proName分组

SELECT u.id,GROUP_CONCAT(u.username),u.sex,p.proName
FROM cms_user AS u
JOIN
provinces AS p
ON u.proId=p.id
WHERE u.sex='男'
GROUP BY p.proName;

• 对分组结果进行筛选,选出组中人数>=1的；
  按照id升序排列，限制显示条数 前2条

SELECT u.id,u.username,u.sex,p.proName,COUNT(*) AS totalUsers,GROUP_CONCAT(username)
FROM cms_user AS u
JOIN
provinces AS p
ON u.proId=p.id
WHERE u.sex='男'
GROUP BY p.proName
HAVING COUNT(*)>=1
ORDER BY u.id ASC
LIMIT 0,2;

外连接查询

简介

• 左外连接：LEFT [OUTER] JOIN显示左表的全部记录及右表符合连接条件的记录
• 右外连接：RIGHT [OUTER] JOIN 显示右表的全部记录以及左表符合条件的记录

测试外连接查询

• 插入错误的数据

INSERT cms_user(username,password,regTime,proId)
VALUES('TEST2','TEST2','1381203974',20);

省份只有五个，proId=20明显是匹配不到结果的，在内连接查询中是查不到新插入的TEST2这个数据的

左外连接

• 主表是 cms_user
  在外连接查询中是可以查到新插入的TEST2这个数据的，省份栏显示NULL

SELECT u.id,u.username,u.email,u.sex,p.proName
FROM cms_user AS u
LEFT JOIN provinces AS p
ON u.proId=p.id;

结果：
+----+----------+-------------+------+---------+
| id | username | email       | sex  | proName |
+----+----------+-------------+------+---------+
|  1 | 张三         | user@qq.com | 男     | 北京        |
|  2 | 张三丰         | user@qq.com | 女    | 上海       |
|  3 | 章子怡         | user@qq.com | 男     | 深圳        |
|  4 | long     | user@qq.com | 女    | 广州        |
|  5 | ring     | user@qq.com | 男     | 上海       |
|  6 | queen    | user@qq.com | 女    | 深圳        |
|  7 | king     | user@qq.com | 男     | 重庆        |
|  8 | blek     | user@qq.com | 女    | 北京        |
|  9 | rose     | user@qq.com | 男     | 上海       |
| 10 | lily     | user@qq.com | 女    | 上海       |
| 11 | john     | user@qq.com | 保密     | 上海       |
| 12 | test1    | user@qq.com | 保密     | 北京        |
| 13 | TEST2    | user@qq.com | NULL | NULL    |
+----+----------+-------------+------+---------+

• 主表是 provinces
  查不到TEST2，因为右表cms_user中TEST2（proId=20）不符合条件

SELECT u.id,u.username,u.email,u.sex,p.proName
FROM provinces AS p
LEFT JOIN cms_user AS u
ON u.proId=p.id;

右外连接

主表为cms_user，谁在最右边谁是主表

SELECT u.id,u.username,u.email,u.sex,p.proName
FROM provinces AS p
RIGHT JOIN cms_user AS u
ON u.proId=p.id;