选取FROM
SELECT name FROM Customer; //数字可以用四则运算
SELECT * FROM Customer; // *代表所有的条例
SELECT * FROM Customer, Borrower;
产生all possible pair，假设C是3X4（列X行），B是2X3（列X行）
最后出来就是（2+3）X（3*4）（列X行）即5X12（列X行）
不做更改就会列的信息就有重复的情况
条件WHERE/去掉重复DISTINCT
SELECT Borrower.loan_id, Customer.name FROM Customer, Borrower WHERE Customer.customer_id = Borrower.customer_id
WHERE内部用AND, OR, NOT连接条件，可有四则运算与比较
可以用来在表里找东西（注意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（% 任意一个string _ 任意一个字符）
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
就是两个集合的交集
函数
aggregation functions- AVG, MIN, MAX, SUM, COUNT
SELECT AVG(balance) FROM Account WHERE branch_id = 'B2';
return一个平均值
Group by 用在aggregation func里面来一批一批用
SELECT branch_id, AVG(balance) FROM Account GROUP BY branch_id;
return每个id的平均值
要对函数值做出限制用HAVING不是WHERE
SELECT branch_id, AVG(balance) FROM Account GROUP BY branch_id HAVING AVG(balance) >= 650;
JOIN- 和直接from两个table没有区别
OUTER JOIN - 保留其中一个table所有的项目，另外一个table没有对应的就是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;
