1、Alias 别名,简单理解就是给列取一个昵称。
SELECT p.age,po.LastName FROM person AS p, person_old AS po WHERE p.lastname='bush' AND po.LastName='bush'; |
2、为了测试join什么的,加了一个person_order例子表,并对person表新增了一行order_number(id_o是person_order的PK(主键))
ALTER TABLE person ADD order_number INT(2); UPDATE person SET order_number=01 WHERE LastName='Adams'; UPDATE person SET order_number=02 WHERE LastName='Bush'; UPDATE person SET order_number=03 WHERE LastName='Carter'; |
3、连接person和person_order表,列也可以用as。
SELECT p.FirstName AS '名字',p.city AS '城市',p.age AS '年龄',po.item AS '商品' FROM person AS p,person_order AS po WHERE p.order_number = po.id_o; #两个表连接的关键是order_number 和 po的id_o |
4、四种JOIN:
JOIN: 如果表中有至少一个匹配,则返回行
SELECT * FROM person INNER JOIN person_order ON person.order_number = person_order.id_o; #效果跟直接连一样 |
LEFT JOIN: 即使右表中没有匹配,也从左表返回所有的行
SELECT * FROM person LEFT JOIN person_order ON person.order_number = person_order.id_o; #效果跟直接连一样 |
RIGHT JOIN: 即使左表中没有匹配,也从右表返回所有的行
SELECT * FROM person RIGHT JOIN person_order ON person.order_number = person_order.id_o; #Person_orde会全部显示出来,看起来就是LEFT会全部显示左边的所有行,RIGHT会显示右边的所有行。。。 |
FULL JOIN: 只要其中一个表中存在匹配,就返回行
SELECT * FROM person FULL JOIN person_order ON person.order_number = person_order.id_o; #我说怎么报错。。百度一下原来MYSQL不支持FULL JOIN。。 |