当前位置:Gxlcms > 数据库问题 > oracle数据库小总结

oracle数据库小总结

时间:2021-07-01 10:21:17 帮助过:3人阅读

table test1( test2 varchar2(20) primary key, //primary key 主键(一个表只能有一个,且不能重复和为空) test3 number(5,2) not null, //not null 不能为空 null可以为空(默认约束为null) test4 char(10) not null, test5 date default(sysdate) //default 添加默认约束 );

 

2.2 进行对表的增加,修改,删除

  1. <span style="color: #0000ff">alter</span> <span style="color: #0000ff">table</span> test1 <span style="color: #0000ff">add</span>(test2 <span style="color: #0000ff">varchar2</span>(<span style="color: #800000; font-weight: bold">20</span>) <span style="color: #808080">not</span> <span style="color: #0000ff">null</span><span style="color: #000000">); //其中test1为表名 test2为增加的列名,not null为约束
  2. </span><span style="color: #0000ff">alter</span> <span style="color: #0000ff">table</span> test1 modify(test2 vatchar2(<span style="color: #800000; font-weight: bold">20</span>) <span style="color: #808080">not</span> <span style="color: #0000ff">null</span><span style="color: #000000">); //其中varchar2(20) 为修改后的属性 not null 约束根据以前写没写适当添加
  3. </span><span style="color: #0000ff">alter</span> <span style="color: #0000ff">table</span> test1 <span style="color: #0000ff">drop</span><span style="color: #000000">(test2); // test2 为test1中删除的列名
  4. </span><span style="color: #0000ff">drop</span> <span style="color: #0000ff">table</span><span style="color: #000000"> test1                         // 直接删除表test1
  5. </span>

 

2.3 添加约束

  1. <span style="color: #0000ff">alter</span> <span style="color: #0000ff">table</span> test <span style="color: #0000ff">add</span> <span style="color: #0000ff">constraint</span> fk_test1 <span style="color: #0000ff">foreign</span> <span style="color: #0000ff">key</span><span style="color: #000000">(test1) refenerces test3(test1) //添加外键约束,test3表中的test1列为其主键,test1为test表中的要添加的外键
  2. </span><span style="color: #0000ff">alter</span> <span style="color: #0000ff">table</span> test <span style="color: #0000ff">add</span> <span style="color: #0000ff">constraint</span> ck_test1 <span style="color: #0000ff">check</span><span style="color: #000000">(条件) //添加检查约束,赋值时的约束条件
  3. </span><span style="color: #0000ff">alter</span> <span style="color: #0000ff">table</span> test <span style="color: #0000ff">add</span> <span style="color: #0000ff">constraint</span> un_test1 <span style="color: #0000ff">unique</span>(test1) //唯一约束,不能重复,可以有一个为空的值

 

3.DML语言 数据操作语言

  1. <span style="color: #0000ff">insert</span> <span style="color: #0000ff">into</span> test1(test2,<span style="color: #800000; font-weight: bold">3</span>,<span style="color: #800000; font-weight: bold">4</span>) <span style="color: #0000ff">values</span>(<span style="color: #800000; font-weight: bold">2</span>,<span style="color: #800000; font-weight: bold">3</span>,<span style="color: #800000; font-weight: bold">4</span><span style="color: #000000">) //给表进行赋值,test1为表名,Test2,3,4 为要添加的值得列名(不写默认顺序为创建表时的列的顺序) 2,3,4 为具体的值
  2. </span><span style="color: #0000ff">update</span> test <span style="color: #0000ff">set</span> test2<span style="color: #808080">=</span>xx <span style="color: #0000ff">where</span> test2<span style="color: #808080">=</span><span style="color: #000000">xxx; //修改(更新)test表中的数据,将符合条件的test2例的值改为xx(不写条件会默认修改整个test2列的值为xx)
  3. </span><span style="color: #0000ff">delete</span> test1 <span style="color: #0000ff">from</span> test <span style="color: #0000ff">where</span> test1<span style="color: #808080">=</span>xx //同上,删除符合条件的test表中的test1列的值(默认删除test1列全部的值)

 

oracle数据库小总结

标签:upd   外键   不能   ons   date   div   table   size   logs   

人气教程排行