- 连接查询
-
–内连接查询
JOIN|CROSS JOIN|INNER JOIN 通过ON连接条件,显示两个表中符合连接条件的记录
-
–外连接查询
左外连接 LEFT [OUTER] JOIN 显示左表的全部记录及右表符合连接条件的记录 右外连接 RIGHT [OUTER] JOIN 显示右表的全部记录以及左表符合条件的记录
- 内连接查询练习
- –查询用户表中的编号,用户名,省份表中的省份名字
SELECT id,username,proName FROM cms_user,provinces; #错误
SELECT cms_user.id,username,proName FROM cms_user,provinces; #指定表名
- –用户表cms_user中的proId对应省份表provinces中的id
SELECT cms_user.id,username,proName FROM cms_user,provinces WHERE cms_user.proId=provinces.id;
- –查询cms_user表中的id,username,email,sex,查询provinces表中的proName
SELECT a.id,a.username,a.email,a.sex,b.proName
FROM cms_user AS a
INNER JOIN provices AS b
ON a.proId=b.id;
SELECT a.id,a.username,a.email,a.sex,b.proName
FROM provinces AS b
CROSS JOIN cms_user AS a
ON a.proId=b.id;
- –INNER|CROSS可省略
SELECT a.id,a.username,a.email,a.sex,b.proName
FROM provinces AS b
JOIN cms_user AS a
ON a.proId=b.id;
- –查询cms_user表中的 id,username,sex,查询provinces表中的 proName,条件是cms_user中性别为男的用户
SELECT a.id,a.username,a.sex,b.proName
FROM cms_user AS a
JOIN provinces AS b
ON a.proId=b.id
WHERE u.sex='男';
- –再根据proName分组
SELECT a.id,a.username,a.sex,b.proName,COUNT(*) AS totalUsers
FROM cms_user AS a
JOIN provinces AS b
ON a.proId=b.id
WHERE u.sex='男'
GROUP BY b.proName;
- –再对分组结果进行二次筛选,选出组中人数大于等于1的记录
SELECT a.id,a.username,a.sex,b.proName,COUNT(*) AS totalUsers,GROUP_CONCAT(username)
FROM cms_user AS a
JOIN provinces AS b
ON a.proId=b.id
WHERE u.sex='男'
GROUP BY b.proName
HAVING COUNT(*) >=1;
- –再按照id升序排序
SELECT a.id,a.username,a.sex,b.proName,COUNT(*) AS totalUsers,GROUP_CONCAT(username)
FROM cms_user AS a
JOIN provinces AS b
ON a.proId=b.id
WHERE u.sex='男'
GROUP BY b.proName
HAVING COUNT(*) >=1
ORDER BY a.id ASC;
- –查询cms_news中的id,title,查询cms_cate中的cateName
SELECT a.id,a.title,b.cateName
FROM cms_news AS a
JOIN cms_cate AS b
ON a.cId=b.id;
- –查询cms_news中的id,title,查询cms_admin中的username,role
SELECT a.id,a.title,b.username,b.role
FROM cms_news AS a
JOIN cms_admin AS b
ON a.aId=b.id;
- –三表联查;查询cms_news中的id,title,查询cms_cate中的cateName,查询cms_admin中的username,role
SELECT n.id,n.title,c.cateName,a.username,a.role
FROM cms_news AS n
JOIN cms_cate AS c
ON n.cId=c.id
JOIN cms_admin AS a
ON n.aId=a.id;
- 外连接查询练习
- –左外连接(以左表为主表)
SELECT a.id,a.username,a.email,a.sex,b.proName
FROM cms_user AS a
LEFT JOIN provices AS b
ON a.proId=b.id;
- –右外连接(以右表为主表)
SELECT a.id,a.username,a.email,a.sex,b.proName
FROM provices AS b
RIGHT JOIN cms_user AS a
ON a.proId=b.id;