Plsql常用语法总结

1、增加表字段:alter table collect_image_url add id number(19) not null

2、删除表字段：alter table  collect_image_url drop column sd

        PG库删除：ALTER TABLE collect_image_url DROP COLUMN IF EXISTS sd;

3、插入数据：insert into flfg(bh,mc,lj,fl) values(14,'sdj,'/mit-web/jccjbk/dgmbacz.html','gzczgf')；

4、通过dblink取别的表中的数据更新 ：update center t set t.code = (select j.code from border@center_db_link j where j.name = t.dw and rownum = 1)

5、创建dblink：create public database link DB_LINK
  connect to DB1 identified by DB1
  using '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.36)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=DSDB)))';

6、通过dblink创建视图:create or replace view v_db as select * from DB1.JSK@DB1;

7、创建表:create table disposeresult（id number(16),sjdbh1 varchar2(50),cjdbh2 varchar2(50),zldbh3 varchar2(50),czfs4 varchar2(50)，
xccztype5 varchar2(50),nbyj6 varchar2(50),qtcontent7 varchar2(2048),tjcontent8 varchar2(2048),tjresult9 varchar2(50));

8、更新数据update border t set t.type = '2'  where t.name = 'skjsa'

9、带有条件的插入数据：update border t set t.type = '2'  where t.name = '的啥叫'

10、创建SEQUENCE

CREATE SEQUENCE SEQ_SJDSJJJ
  START WITH 1//id以多少开始
  MAXVALUE 999999999999999999999999999//最大值
  MINVALUE 1//最小值

increment by 1//增量
  CACHE 20//高速缓存

11、删除某个表：delete from 表名称

12、带有条件的插入数据：insert into center (id) (select y.type from border y where y.name ='测试')

13、根据编号查询此编号数量的个数

select t.bh,count(*) from t_zdxx t group by t.bh

14、 update t_zd t set t.ssdw=(select j.dm from t_bd_dw j where j.qc like '%'|| t.ssdw) where t.ssdwbm is null

16、导出数据库：exp biaoming/biaoming@192.168.2.155:1521/mydb file=d:/biaoming.dmp

17、导入数据库 ：imp biaoming/biaoming@192.168.2.155:1521/mydb file=D:\example.dmp full=y

18、视图 grant create view to 建视图的表名
grant select any table to 建视图的表名
grant select any dictionary to 建视图的表名

19、删除表数据

delete from table.name where 条件；