数据库操作sql

一、新建表空间

1、创建表空间名称

create tablespace ZDRY datafile 'ZDRY.dbf' SIZE 50m；

2、创建用户密码，用户名zdry,密码123456

create user zdry identified by 123456  default tablespace ZDRY

3、给用户授权

grant connect,resource,dba to zdry;

二、批量插入（insert 和select）

INSERT INTO t_zdry ( ZJ, RYBH, XM, XMPY, XB ) SELECT

sys_guid () zj,

rybh,

xm,

'' xmpy,

'' xb

FROM

export_table;

三、批量修改（update和select）

UPDATE t_zdry a

SET a.jtgjlxcphm =

  (SELECT b.lxhm FROM export_table33 b WHERE b.glbh=a.rybh AND b.gllx = '08'

  )

WHERE EXISTS

  (SELECT 1 FROM export_table33 b WHERE b.glbh=a.rybh AND b.gllx = '08'

  );

四、行转列

SELECT MAX(t.gllx) gllx,

  t.glbh, WMSYS.wm_concat(t.xxlx1 || ':' ||t.xxqz1) lxhm

FROM

  (SELECT DISTINCT gllx, xxlx1, xxqz1 ,glbh

  FROM ythgkpt.t_fxk_xxkz

  WHERE GLBH IN

    (SELECT rybh FROM ythgkpt.vm_jwzyddc_ryxx t)

  ) t

GROUP BY t.glbh;