By yusijia

Updated:

Contents 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
SELECT * FROM t_book,t_bookType;

# where那里是两个表的内连接条件
SELECT * FROM t_book,t_bookType WHERE t_book.bookTypeId=t_bookType.id;

# tb和tby是别名，尽量用别名
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;

# 语法：表名1 LEFT|RIGHT JOIN 表名2
# LEFT是左连接，从 表1 返回所有的行，即使 表2 中没有匹配，没有的信息用NULL表示，ON是加条件的
# RIGHT JOIN是右连接，从 表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;




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