Oracle数据库的表的增、删、改SQL操作方法（一）

梦里梦外; 2022-03-21 06:39 547阅读 0赞

0.创建新表  
\---create table  
create table D\_HZ\_MPDZ  
(  
  SYSTEMID     VARCHAR2(50) not null primary key,  
  SSXZQHDM     VARCHAR2(6),  
  ZXWD         FLOAT,  
  CCCJSJ       DATE,  
  LASTUPDATETIME TIMESTAMP  
)  
tablespace MY\_001  
  pctfree 10  
  initrans 1  
  maxtrans 255  
  storage  
  (  
    initial 616M  
    next 1M  
    minextents 1  
    maxextents unlimited  
  );  
1.把一个A表的某些记录 形成B表，应用场景：备份某些记录到B表

create table B as   
    select \* from A t where t.zxjd is null or t.zxwd is null    
1.1 查询的结果插入另外一个表

insert into table \*\*\* (col1,col2,col3)   
    select a.col1,a.col2,b.col3 from table a,table b where a.id=b.id  
2.删除A表的某些数据

delete from A t where t.zxjd is null or t.zxwd is null  
2.1   truncate table 表名；

truncate table a  
   
用truncate删除表数据，只是删除表中的数据，表结构不会被删除！  
删除整个表的数据时,过程是系统一次性删除数据,效率比较高  
truncate删除释放空间  
2.2  删除表，drop table 表名；

drop table POP\_LOCATE purge   
用drop删除表数据，不但会删除表中的数据，连表结构也被删除了  
2.3   delete 表名；

delete from A t where t.zxjd is null or t.zxwd is null  
   
用delete删除表数据，只是删除表中的数据，表结构不会被删除！  
虽然也是删除整个表的数据,但是过程是系统一行一行地删,效率比truncate低  
delete删除是不释放空间的  
3.把A表某字段前面重复的字符删除

两种方式：一是字符串截取，二是用空值替换：

这个是替换语句：

update A t set t.dzqc =Replace(t.z,'山东省','')   
4.给A表添加一个ID字段，并从1不断增加

首先，给表添加ID字段，字段类型是NUMBER  
        其次，在PLSQL里面创建一个序列：

\-- Create sequence   
create sequence TEST  
minvalue 1  
maxvalue 99999999  
start with 2101401  
increment by 1  
cache 20;  
最后更新A表：

update A t set t.id = test.nextval  
设置这个表创建触发器，让它添加记录的时候，自动增加ID

create or replace trigger aoto\_add\_id  
        before insert  
        on A  
        for each row  
declare  
        primary\_key\_value number;  
begin  
        select test.nextval into primary\_key\_value  from dual;    //test.nextval 是上面创建的序列  
        :new.id:=primary\_key\_value;  
end aoto\_add\_id  
创建完毕触发器，要执行。

5.给A表的时间戳字段添加系统的时间戳：

update a t set t.gxsj = systimestamp  
格式化查看这个时间戳字段：

select to\_char(t.gxsj,'YYYY-MM-DD HH24:MI:SS.FF') from A\_POI t  
建表的时候时间戳字段自动添加系统时间戳：

create table test(id int, t timestamp default systimestamp)  
6.多表联合查询  
1.查找相同字段值

select a.xx,b.xx from a,b where a.xx=b.xx  
这就把两个表相同的字段（a.xx=b.xx），连接起来

如果要把联合查询的结果保存到表里面：

create table PP\_DZ as   
     select a.xx,b.xx from a,b where a.xx=b.xx  
2.查找相同字段不同值

select \* from table a where a.id not in (select b.id from table b)  
7.修改表字段  
7.1  修改

alter table 表名 modify (column\_name1 varchar(20) default null,column\_name2 varchar2(30));  
7.2  增加

alter table A\_Y   
           add  
          (  
              age   number(3,0),  
              gender varchar2(4),     
           )  
7.3 删除

alter table A\_GZ\_ZDRY drop(other2)  
8.查询某几行数据  
SELECT \* FROM EMP  WHERE ROWNUM <= 10 ORDER BY sal DESC;  
 在oracle中是使用ROWNUM 来表示前10条的信息。

9.删除某重复字段的记录，仅保留一条  
9.1   重复记录是根据单个字段（Id）来判断

select \* from 表a where a.Id in   
    (select b.Id from 表b group by b.Id having count(b.Id) > 1)  
9.2   删除表中多余的重复记录，重复记录是根据单个字段（Id）来判断，只留有rowid最小的记录

DELETE from 表a WHERE a.id IN   
    ( SELECT b.id FROM 表b GROUP BY b.id HAVING COUNT(b.id) > 1)   
    AND ROWID NOT IN   
    (SELECT MIN(ROWID) FROM 表c GROUP BY c.id HAVING COUNT(\*) > 1);  
9.3   另外一种方法

\-查看多个字段重复的重复数量，其他属性也显示，但是仅仅显示第一行  
select \*,count(\*) from A   
    group by a.uid, a.qid   
    having count(\*)>1;  
   
\-在上面的基础上，显示这两个字段重复的所有记录  
SELECT \* FROM A   
    WHERE (uid, qid) IN   
        (SELECT A.uid,A.qid FROM A   
            GROUP BY A.uid,A.qid   
            HAVING COUNT(\*)>1)  
   
\-在上面的基础上，保留ID最小的，其他全部选出  
SELECT \* FROM A   
    WHERE (uid, qid) IN   
        (SELECT uid,qid FROM A   
            GROUP BY A.uid,A.qid   
            HAVING COUNT(\*) > 1)   
    AND id NOT IN   
        (SELECT MIN(id) FROM A   
            GROUP BY A.uid,A.qid  
            HAVING COUNT(\*) > 1);  
   
\-在上面基础上，删除选择出来的  
\-创建中间表,用于临时保存需要删除的数据  
CREATE TABLE TT(id INTEGER, uid INTEGER, qid INTEGER);   
\-将要删除的数据插入中间表   
INSERT INTO TT (上面选择出来的记录) ;   
\-删除中间表   
DELETE FROM A WHERE id IN (SELECT id FROM TT);   
10.update更新操作，把某个字段的值写到另外一个字段  
update table t set t.a = t.b where t.a is null  
11.字符串转换和数字计算  
—字符串截取，转换为数字  
  update A\_Y t set t.age = (2019-to\_number(substr(t.q,0,4)))   —  
   
\-求模运算  
  update A\_Y t set t.gender = '男' where mod(to\_number(substr(t.sfzh,17,1)),2)=1   
12.授权或者取消用户表权限  
\-授权  
grant select,insert,update,delete,all on tablename to username  
   
\-取消  
revoke select,insert,update,delete,all on tablename to username  
13.insert操作，把查询结果插入表格  
insert into tablename   
    select col1,col2,col3   
        from table1,table2   
            where table1.id=table2.id  
   
\---------------------   
作者：阿甘亮   
来源：CSDN   
原文：https://blog.csdn.net/aganliang/article/details/79921454   
版权声明：本文为博主原创文章，转载请附上博文链接！