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;