SQL基本操作（4）--连接查询

笛卡尔集：

内连接查询

SELECT * FROM t_book,t_bookType WHERE t_book.bookTypeId=t_bookType.id;

SELECT bookName,author,bookTypeName FROM t_book,t_bookType WHERE t_book.bookTypeId=t_bookType.id;

SELECT tb.bookName,tb.author,tby.bookTypeName FROM t_book tb,t_bookType tby WHERE tb.bookTypeId=tby.id;

外连接查询（左连接/右连接/连接/全连接）

SELECT属性名列表FROM表名1 LEFT|RIGHT JOIN表名2 ON表名1.属性名1=表名2.属性名2；

左连接：可以查询出“表名1”的所有记录，而“表名2”中，只能查询出匹配的记录；

右连接：可以查询出“表名2”的所有记录，而“表名1”中，只能查询出匹配的记录；

SELECT * FROM t_book LEFT JOIN t_bookType ON t_book.bookTypeId=t_bookType.id;

SELECT tb.bookName,tb.author,tby.bookTypeName FROM t_book tb LEFT JOIN t_bookType tby ON tb.bookTypeId=tby.id;

SELECT * FROM t_book RIGHT JOIN t_bookType ON t_book.bookTypeId=t_bookType.id;

SELECT tb.bookName,tb.author,tby.bookTypeName FROM t_book tb RIGHT JOIN t_bookType tby ON tb.bookTypeId=tby.id;

使用 UNION 关键字，数据库系统会将所有的查询结果合并到一起，然后去除掉相同的记录

SELECT id FROM t_book UNION SELECT id FROM t_booktype;

使用 UNION ALL，不会去除掉系统的记录

SELECT id FROM t_book UNION ALL SELECT id FROM t_booktype;

多条件连接查询

SELECT tb.bookName,tb.author,tby.bookTypeName FROM t_book tb,t_bookType tby WHERE tb.bookTypeId=tby.id AND tb.price>70;