SQL复习-dml

选取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;