SQL查询与操作技巧-优快云博客

本文介绍了SQL中的查询、删除、插入、更新等操作，并通过具体示例解释了如何使用HAVING子句、WITH子句及不同类型的JOIN。此外还探讨了一些高级查询技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

2019独角兽企业重金招聘Python工程师标准>>>

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### 这部分内容学习自:http://www.cnblogs.com/weblogical/archive/2011/03/07/1974585.html

<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>

###修改###

转载于:https://my.oschina.net/zjuysw/blog/207341