SQL 查询-删除——学习笔记

##从例子中领悟查询## 注意：avg_bal的用法

<pre><code> SELECT branch_name, avg_bal FROM ( select branch_name, avg (balance) from account group by branch_name ) as result (branch_name, avg_bal) HERE avg_bal > 500 ; </pre></code>

注意：having的用法，avg可以这样使用

<pre><code> select branch_name, avg(balance) from account group by branch_name having avg(balance)>500 </code></pre>

with clause allows views to be defined locally to a query, rather than globally

<pre><code> with max_balance(value) as select max(balance) from account select account_number from account, max_balance where account.balance=max_balance.value; </code></pre>

The simplest solution uses the contains comparison which was included in the original System R Sequel language but is not present in the subsequent SQL versions.

<pre><code> select S.company name from company S where not exists ((select city from company where company name = ’Small Bank Corporation’) except (select city from company T where S.company name = T.company name)) </code></pre>

###删除###

<pre><code> delete from table/view where condition </code></pre>

提示：并不是每种view都可以进行删除。(有些view)有很多操作不能执行 from table;在这里只能是一个table

###插入###

<pre><code> INSERT INTO table|view c1,c2,… VALUES e1,e2,… </code></pre>

###更新###

<pre><code> UPDATE table | view SET c1=e1 ,c2=e2, WHERE condition </pre></code>

###join### 这部分内容学习自:

<pre><code> Table A aid adate 1 a1 2 a2 3 a3 TableB bid bdate 1 b1 2 b2 4 b4 两个表a,b相连接,要取出id相同的字段 select * from a inner join b on a.aid = b.bid这是仅取出匹配的数据. 此时的取出的是: 1 a1 b1 2 a2 b2 那么left join 指: select * from a left join b on a.aid = b.bid 首先取出a表中所有数据,然后再加上与a,b匹配的的数据 此时的取出的是: 1 a1 b1 2 a2 b2 3 a3 空字符 同样的也有right join 指的是首先取出b表中所有数据,然后再加上与a,b匹配的的数据 此时的取出的是: 1 a1 b1 2 a2 b2 4 空字符 b4 </code></pre>

###修改###