oracle数据库基本操作-优快云博客

本文链接：https://blog.youkuaiyun.com/yjq30604/article/details/43407655

一、增：有3种方法

1.使用insert插入单行数据：

<code class="hljs sql has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">   <span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">insert</span> [<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">into</span>] <表名> [列名] <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">values</span> <列值>

　　<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">insert</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">into</span> Strdents (name,age) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">values</span> (<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'atm'</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">12</span>)</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li></ul>

2.使用insert,select语句将现有表中的数据添加到已有的新表中

<code class="hljs sql has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">   <span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">insert</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">into</span> <已有的新表> <列名> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> <原表列名> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span> <原表名>

　　<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">insert</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">into</span> newtable (name,class)<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> name,class <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span>  tableinfo</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li></ul>

3.将数据插入原表中（生成测试数据用的较多）

    和第二种方法一样，只是复制到原表中

<code class="hljs sql has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">   <span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">insert</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">into</span> tableinfo (<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'name'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'class'</span>)<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> name,class <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span>  tableinfo</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>

二、删：有3中方法

1.delete删除

<code class="hljs sql has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">    <span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">delete</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span> <表名> [<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">where</span> <删除条件>]　　  

    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">delete</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span> tableinfo <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">where</span> name=<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'atm'</span></span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li></ul>

2.truncate table 删除整个表的数据

<code class="hljs sql has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">     <span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">truncate</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">table</span> <表名>

　   <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">truncate</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">table</span> tableinfo

　  删除表的所有行，但表的结构、列、约束、索引等不会被删除；不能用于有外建约束引用的表</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li></ul>

3、drop删除

<code class="hljs sql has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">    <span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">drop</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">table</span> <表名>
    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">drop</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">table</span> tableinfo
   删除表中所有行，表结构也删除了。</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li></ul>

三、update更新修改

<code class="hljs sql has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">    <span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">update</span> <表名> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">set</span> <列名=更新值> [<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">where</span> <更新条件>]
    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">update</span> tableinfo <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">set</span> age=<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">12</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">where</span> name=<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'atm1'</span>
　　 <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">set</span>后面可以紧随多个数据列的更新值（非数字要引号）；</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li></ul>

四、查

1.普通查询

<code class="hljs sql has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">  <span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> <列名> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span> <表名> [<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">where</span> <查询条件表达试>] [<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">order</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">by</span> <排序的列名>[<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">asc</span>或<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">desc</span>]]

   　<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span>).查询所有数据

　　　　<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> * <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span> tableinfo

　　　<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">2</span>).查询部分行列--条件查询

　　　　<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> name,age   <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span>  tableinfo   <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">where</span> age=<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">11</span>;</span>

　　　3).在查询中使用ＡＳ更改列名

　　　　<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> name <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">as</span> 姓名　<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span> a <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">where</span>  age=<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">11</span>;</span>

　　　4).查询空行

　　　　<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> name <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span> tableinf  <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">where</span> class <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">is</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">null</span>

　　   <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">5</span>).查询返回限制行数(关键字：top )

　　　　<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> top <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">6</span> name <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span> tableinfo

　　　　显示列name的前６行，oracle 中用rownum替代(<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span>   *   <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span>   a <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">where</span>   rownum<<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">6</span> )
　　　　
　　　<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">6</span>).查询排序（关键字：<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">order</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">by</span> , <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">asc</span> , <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">desc</span>）

　　　　例：<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> name <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span> tableinfo <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">where</span> age>=<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">11</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">order</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">by</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">desc</span>(默认为ＡＳＣ升序)</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li><li style="box-sizing: border-box; padding: 0px 5px;">18</li><li style="box-sizing: border-box; padding: 0px 5px;">19</li><li style="box-sizing: border-box; padding: 0px 5px;">20</li><li style="box-sizing: border-box; padding: 0px 5px;">21</li><li style="box-sizing: border-box; padding: 0px 5px;">22</li><li style="box-sizing: border-box; padding: 0px 5px;">23</li><li style="box-sizing: border-box; padding: 0px 5px;">24</li><li style="box-sizing: border-box; padding: 0px 5px;">25</li><li style="box-sizing: border-box; padding: 0px 5px;">26</li><li style="box-sizing: border-box; padding: 0px 5px;">27</li></ul>

２.模糊查询

　　　1).使用like进行模糊查询
请看另一篇文章， SQL like四种用法
　　　　
　　　2).使用between在某个范围内进行查询

<code class="hljs sql has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> * <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span> tableinfo <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">where</span> age between <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">11</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">and</span> <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">22</span></span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>

　　
　　 3).使用in在列举值内进行查询(in后是多个的数据)

<code class="hljs sql has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> name <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span> tableinfo <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">where</span> name <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">in</span> (<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'atm'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'atm1'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'atm2'</span>);</span></code>

1、固定列数的行列转换如
student subject grade
--------- ---------- --------
student1 语文 80
student1 数学 70
student1 英语 60
student2 语文 90
student2 数学 80
student2 英语 100
……
转换为
姓名语文数学英语总分
student1 80 70 60
student2 90 80 100
……
语句如下：
select student,
sum(decode(subject,'语文', grade,null)) "语文",
sum(decode(subject,'数学', grade,null)) "数学",
sum(decode(subject,'英语', grade,null)) "英语",
sum(subject) "总分"
from table
group by student;
2、A表中有B表没有的数据
表a
id name
1 a
2 b
3 c
4 d
表b
id a_id
1 1
2 3
-----------
期望结果
2 b
4 d
-----------
实际上b表中的a_id是和a表中的id对应。
sql: select * from a where id not in (select a.id from a join b on a.id=a_id);
select * from a where id not in (select a_id from b)
select * from a where not exists (select 1 from b where id=a.id)
例如： selectdecode( x , 1 , ‘x is 1 ’, 2 , ‘x is 2 ’, ‘others’) from dual 当x等于1时，则返回‘x is 1’。当x等于2时，则返回‘x is 2’。否则，返回others’。需要，比较2个值的时候，可以配合SIGN()函数一起使用。 SELECT DECODE( SIGN(5 -6), 1 'Is Positive', -1, 'Is Nagative', 'Is Zero') 同样，也可以用CASE实现： SELECT CASE SIGN(5 - 6) WHEN 1 THEN 'Is Positive'WHEN　-1 THEN 'Is Nagative'ELSE　'Is Zero' ENDFROM DUAL此外，还可以在Order by中使用Decode。例如：表table_subject，有subject_name列。要求按照：语、数、外的顺序进行排序。这时，就可以非常轻松的使用Decode完成要求了。 select * from table_subject order by decode(subject_name, '语文', 1, '数学', 2, , '外语',3)
www.2cto.com
将所有的结果全部写出
select * from classes t
数据为
1 1 一班 NUM_1
2 2 二班 NUM_2
3 3 三班 NUM_3
4 4 四班 NUM_4

select t.* from classes t order by decode(t.classnum,'NUM_1',4,'NUM_2',3,'NUM_3',1);