SQL个人学习笔记02

1.表的别名

SELECT t.id,t.name FROM table_name AS t

在一个SELECT语句中，系统首先执行FROM语句，然后执行SELECT语句

2.内连接（INNER JOIN）：仅将两个表中满足连接条件的行组合起来作为结果集，即两表连接笛卡尔集中匹配的行显示出来

1）在WHERE子句中指定连接

SELECT t1.column_name1,t1.column_name2,t2.column_name1,t2.column_name2

FROM table_name1 AS t1,table_name2 AS t2

WHERE t1.id = t2.id

2)使用JOIN和ON关键字指定字连接条件

SELECT t1.column_name1,t1.column_name2,t2.column_name1,t2.column_name2

FROM table_name1 AS t1 INNER JOIN table_name2 AS t2

ON t1.id = t2.id

2.外连接（OUTER JOIN）：参与连接的表有主从之分，连接时以主表中的每一行数据去匹配从表中的数据列，查询的结果是主表中的全部数据和从表中符合条件的与主表相匹配的行数据。当主表中的某一数据没有与之相匹配的从表数据时，则以空值填入。

1）左连接（LEFT OUTER JOIN）：主表在左

SELECT t1.column_name1,t1.column_name2,t2.column_name1,t2.column_name2

FROM table_name1 AS t1 LEFT OUTER JOIN table_name2 AS t2

ON t1.id = t2.id

2)右连接（RIGHT OUTER JOIN）：主表在右

SELECT t1.column_name1,t1.column_name2,t2.column_name1,t2.column_name2

FROM table_name1 AS t1 RIGHT OUTER JOIN table_name2 AS t2

ON t1.id = t2.id

3）完全连接（FULL JOIN）：不限制主从表，按ON条件列出所有数据，并在没有匹配的情况下填入NULL值(可理解为匹配ON条件的合并为单行输出，不匹配的填上null分别输出)

SELECT t1.column_name1,t1.column_name2,t2.column_name1,t2.column_name2

FROM table_name1 AS t1 FULL JOIN table_name2 AS t2

ON t1.id = t2.id

注意：tabel_name1 LEFT OUTER JOIN table_name2等价于table_name2 RIGHT OUTER JOIN table_name1

3.连接两个以上的表

SELECT t1.column_name1,t2.column_name1,t3.column_name1

FROM table_name1 AS t1,table-name2 AS t2,table_name3 AS t3

WHERE t1.id = t2.id

AND t2.id = t3.id

AND t3.id = 1

--------------------------------------------------------------------------------------------

SELECT t1.column_name1,t2.column_name1,t3.column_name1

FROM table_name1 AS t1 INNER JOIN

 table-name2 AS t2 ON t1.id = t2.id INNER JOIN 

table_name3 AS t3 ON t2.id = t3.id

WHERE t3.id = 1