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

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.给表添加注释：
comment on table tablename 
    is '注释名字';

2.给字段添加注释：
comment on column tablename.col
    is '字段注释';

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表某字段前面重复的字符删除

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

这个是字符串截取函数：substr(string，开始位置，长度-不写的话-默认到结尾)，instr（'源字符串' , '目标字符串' ,'开始位置','第几次出现'）

substr(t.dzqc,4)   
 
---这样会把dzqc前面的三个字去掉，截取后面所有字符

这个是替换语句：

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