SQL学习笔记第2次

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。。