???????????????SQL

???????????? ???????[ 2014/7/15 11:09:27 ] ????????SQL ?????

???????????????z??????????????????????:
????SELECT A.x + A.y AS z
????FROM A
????WHERE (A.x + A.y) = 10
???????????????????????????????????????????????????????????????????
????3????????????????UNION???????ORDER BY???????????????UNION???????????????????????SQL???????????SQL????????????????С?????Щ????????????????????????????????????????????UNION?????????
???????????????????????е???????????????????????????2?????????????MySQL??PostgreSQL????SQLite??
??????????????????????
??????????SQL?????????????????????????????????????????????е??????????????????Щ?????????Щ?????
?????????????????????????????????????????????????????????????????LINQ???????
????3??SQL???????????????
??????????????????????????????????????????SQL???е??????????????????????????????????????á?
??????SQL?????????FROM??????￡?
????<from clause> ::= FROM <table reference> [ { <comma> <table reference> }... ]
????ROM????"output"?????б?????????????????á???????????????????Щ??
????FROM a?? b
???????????????a+b???????????????a??3?к?b??5?У????"?????"????8??3+5???С?
???????????????????????????????/?????????axb?????仰???????a?????????????b????????????a??3???????b??5??????????????????????y?????15?????(3×5)??
??????WHERE???????GROUP BY?????"output"??"fed"/"piped"??????????μ?"output"??????????????????
??????????????????/?????????????????Щ?????????SQL?????????????????????????SQL??佫??????????????????????μ?????
??????????????????????
????????????????y???????????????????????????????sql???????????
????4??SQL?????????????????
???????????????????????????????????JOIN????????????SELECT???????????????"special"?????????????????????SQL??????ж??壨???????
????<table reference> ::=
????<table name>
????| <derived table>
????| <joined table>
???????????????????????????????
????FROM a?? b
????a?????????????????磺
????a1 JOIN a2 ON a1.id = a2.id
??????????????????????????????
????FROM a1 JOIN a2 ON a1.id = a2.id?? b
????????????????????????????????????????????????????????????????????????????????????y???a1+a2+b???
???????????????????????????????????????????
??????????????????????
???????????????????????????????????????????????????sql??????????????????????????????????????ι?????
????????????????????JOIN?????????????????????SELECT???????????Щ?????????JOIN?????????????????á?
????5??????SQL JOIN??????????????????
??????棬????????????????
????FROM a?? b
???????SQL??????????????????ò????????????б???????????????????JOINs???????????????SQL?????????????????????
????????????????????????????????ν???????????????
????FROM a?? b?? c?? d?? e?? f?? g?? h
????WHERE a.a1 = b.bx
????AND a.a2 = c.c1
????AND d.d1 = b.bc
????-- etc...
???????join??????????
?????????????????????ν???????????????????????
???????????????????????????????????????????????
??????????????????????
???????JOIN?????????????FROM?????????????????á?
????6??SQL????????????????
???????????????????????
????EQUI JOIN
????SEMI JOIN
????ANTI JOIN
????CROSS JOIN
????DIVISION
??????Щ????????????????????????????????????????SQL????ò?????????????????????:
????EQUI JOIN??????????
?????????????JOIN???????????????????:
????INNER JOIN(???????JOIN)
????OUTER JOIN(??????β???LEFT?? RIGHT??FULL OUTER JOIN)
?????????????е??????????:

-- This table reference contains authors and their books.

-- There is one record for each book and its author.

-- authors without books are NOT included

author JOIN book ON author.id = book.author_id

-- This table reference contains authors and their books

-- There is one record for each book and its author.

-- ... OR there is an "empty" record for authors without books

-- ("empty" meaning that all book columns are NULL)

author LEFT OUTER JOIN book ON author.id = book.author_id

????SEMI JOIN?????????
?????????????????SQL????????????????INν??????EXISTSν??"Semi"????????????е?"half"??????????????????????????"half"??????á???ο??????????????????顣?????????????????????/??????????????Щ?????????????顣??????????????д:
????-- Using IN
????FROM author
????WHERE author.id IN (SELECT book.author_id FROM book)
????-- Using EXISTS
????FROM author
????WHERE EXISTS (SELECT 1 FROM book WHERE book.author_id = author.id)
?????????????????????????IN????EXISTS?????????й?????????????????????
????IN??????EXISTS????????
????EXISTS??????IN???????????????????????︴?????????
???????????????????????????????????Щ??????????о??????????
???????INNER JOIN?п????????????????????????????????????????????DISTINCT????????????????????????????????????????
????-- Find only those authors who also have books
????SELECT DISTINCT first_name?? last_name
????FROM author
?????????????????????????ж???
????????????????????????к??????????????У??????????????
????????????????????????????????????????????????????????????JOIN?????????????????????????????????????
????????????DISTINCT?????????????????????????
????ANTI JOIN?????????
?????????????????????????????????????????? NOT ?????????IN ?? EXISTS?????????????????У??????????Щ????κ??鼮???????
????-- Using IN
????FROM author
????WHERE author.id NOT IN (SELECT book.author_id FROM book)
????-- Using EXISTS
????FROM author
????WHERE NOT EXISTS (SELECT 1 FROM book WHERE book.author_id = author.id)
???????????????????????????????????á???????????NOT IN???NULLs???????С???棬????????е?????????Χ??
????CROSS JOIN???????????
??????????????е?????????????е??????????????join?????????????μ????????????????????????????FROM????????????????????????????????????????￡???????SQL??????????д???CROSS JOIN??
????-- Combine every author with every book
????author CROSS JOIN book
????DIVISION????????
?????????????????????????????ι???????????????????JOIN???????????????JOIN?????????SQL?У??????????????????????????????????????????????????????????????Χ??????????????????????????????????????
??????????????????????
?????????????潲????????????μ??????SQL??????á???????????????????á????????????SQL?????????е????????????????е???????????????????SQL????????????????????????????????????????JOIN??????????????????????????SQL??