2021-09-07

最新推荐文章于 2025-03-17 17:26:46 发布

原创最新推荐文章于 2025-03-17 17:26:46 发布 · 125 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#sql

本文详细解析了SQL中的左外连接、右外连接和内连接，通过实例演示了它们在查询celebrity表中的应用，包括性别筛选、作者关联、年龄查询等，并对比了'on'和'where'的区别。

多表关联查询，两个表之间连接关系可分为左外连接left join，右连接right join，内连接 join。

table student

table dept

1、左外连接

SELECT *
FROM student s LEFT OUTER JOIN dept d
ON s.dno = d.did;
结果：
1 ZS 1 1 研发部
2 LS 1 1 研发部
3 WW 2 2 测试部
4 ZL
2、右外连接

SELECT *
FROM student s RIGHT OUTER JOIN dept d
ON s.dno = d.did;

结果：
1 ZS 1 1 研发部
2 LS 1 1 研发部
3 WW 2 2 测试部
3 市场部

3、内连接

SELECT *
FROM student s INNER JOIN dept d
ON s.dno = d.did

结果：

1 ZS 1 1 研发部
2 LS 1 1 研发部
3 WW 2 2 测试部

等价于

SELECT *
FROM student s,dept d
where s.dno = d.did;

4、on和where的区别

1） on条件是生成临时表时使用的条件，不管on中的条件是否为真，都会返回左边表中的记录。

2）where条件是在临时表生成好后对临时表进行过滤的条件。这时没有left join的含义了，条件不为真的就全部过滤掉。

SELECT *
FROM student s LEFT JOIN dept d
ON s.dno = d.did
AND s.dno = 1;

结果：

1 ZS 1 1 研发部
2 LS 1 1 研发部
3 WW 2
4 ZL

SELECT *
FROM student s LEFT JOIN dept d
ON s.dno = d.did
WHERE s.dno = 1;

结果：

1 ZS 1 1 研发部
2 LS 1 1 研发部

来个简单的实例

SELECT * FROM celebrity;

-- 1.查询名人表中性别是男的作者
SELECT * FROM celebrity WHERE ssex='男';
-- 2.查询静夜思作者
SELECT celebrity.sname FROM summary inner join celebrity ON celebrity.sid=summary.sid inner join works on works.wid=summary.wid WHERE works.wwork='静夜思';
-- 3.查询李白和杜甫的年纪
SELECT sage FROM celebrity WHERE sname='李白' or sname='杜甫';
-- 4.查询名人表中的男女人数
SELECT sum(case when ssex ='男' then 1 else 0 end)男,sum(case when ssex='女' then 1 else 0 end)女 FROM celebrity;
-- 5.查询白居易作品名称和对应的名句
SELECT works.wwork,summary.sassess FROM summary inner join celebrity on celebrity.sid=summary.sid inner join works on works.wid=summary.wid WHERE celebrity.sname='白居易';
-- 6.查询年纪在25到30岁之间的个数
SELECT SUM(celebrity.sage BETWEEN 25 AND 30) FROM celebrity;
-- 7.查询名人表中年纪最小两条的数据信息
SELECT * FROM celebrity ORDER BY sage LIMIT 0,2;
-- 8.查询李白的作品名称，名句和年纪
SELECT works.wwork,summary.sassess,celebrity.sage FROM summary inner JOIN celebrity on celebrity.sid=summary.sid inner join works on works.wid=summary.wid WHERE celebrity.sname='李白';
-- 9.将作品为望岳的朝代修改为北宋
UPDATE works set wdynasty='北宋' WHERE works.wwork='望岳';
-- 10.新增名人表中的一个作者王维，年纪25，性别男
INSERT into celebrity VALUES (NULL,'王维',25,'男');