選取FROM

SELECT name FROM Customer; //數(shù)字可以用四則運(yùn)算

SELECT * FROM Customer; // *代表所有的條例

SELECT * FROM Customer, Borrower;

產(chǎn)生all possible pair，假設(shè)C是3X4（列X行），B是2X3（列X行）

最后出來就是（2+3）X（3*4）（列X行）即5X12（列X行）

不做更改就會(huì)列的信息就有重復(fù)的情況

條件WHERE/去掉重復(fù)DISTINCT

SELECT Borrower.loan_id, Customer.name FROM Customer, Borrower?WHERE Customer.customer_id = Borrower.customer_id

WHERE內(nèi)部用AND, OR, NOT連接條件，可有四則運(yùn)算與比較

可以用來在表里找東西（注意DISTINCT）

SELECT DISTINCT Branch.name FROM Branch, Loan WHERE Branch.branch_id = Loan.branch_id;

改名

SELECT DISTINCT Branch.name AS 'Branch name' FROM Branch, Loan WHERE Branch.branch_id = Loan.branch_id;

SELECT DISTINCT B.name FROM Branch B, Loan L WHERE B.branch_id = L.branch_id;

LIKE clause（% 任意一個(gè)string? _ 任意一個(gè)字符）

SELECT name FROM Customer WHERE address LIKE '%320%';

排序

SELECT name FROM Customer ORDER BY name ASC;//DESC也可以

IN clause

SELECT DISTINCT customer_id FROM Borrower WHERE customer_id IN (SELECT customer_id FROM Owner);

或者NOT IN

就是兩個(gè)集合的交集

函數(shù)

aggregation functions- AVG, MIN, MAX, SUM, COUNT

SELECT AVG(balance) FROM Account WHERE branch_id = 'B2';

return一個(gè)平均值

Group by 用在aggregation func里面來一批一批用

SELECT branch_id, AVG(balance) FROM Account GROUP BY branch_id;

return每個(gè)id的平均值

要對(duì)函數(shù)值做出限制用HAVING不是WHERE

SELECT branch_id, AVG(balance) FROM Account GROUP BY branch_id HAVING AVG(balance) >= 650;

JOIN- 和直接from兩個(gè)table沒有區(qū)別

OUTER JOIN - 保留其中一個(gè)table所有的項(xiàng)目，另外一個(gè)table沒有對(duì)應(yīng)的就是null（LEFT OUTER JOIN, RIGHT OUTER JOIN）

SELECT *FROM Employee E LEFT OUTER JOIN Department D ON E.department_id = D.department_id;

SELECT *FROM Employee E?RIGHT OUTER JOIN?Department D?ON?E.department_id = D.department_id;