mysql连接查询

SELECT * FROMt_book,t_bookType;

内查询:

     查询这2各表内t_book(bookTypeId)等于t_bookType(id)字段的列

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

     和上面语句查询意思相同，只是查询*中多余的改成只显示bookName,author,bookTypeName3个字段

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

    和上面语句查询意思相同，别名用法，只不过设置别名为tb.bookName,tb.author=tb，tby.bookTypeName=tby，查询tb.bookName,tb.author=tby.bookTypeName的列。

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

 

外查询：

左查询LEFT：以左查询内容为重点，列出对应数据，没有的数据以NOLL代替

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

别名左查询

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

 

右查询

右查询LEFT：以右查询内容为重点，列出对应数据，没有的数据以NOLL代替

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

别名查询

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

 

多条件查询

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