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_001pctfree 10initrans 1maxtrans 255storage(initial 616Mnext 1Mminextents 1maxextents unlimited);
给表和字段添加注释&＃xff1a;
1.给表添加注释&＃xff1a;
comment on table tablename is &＃39;注释名字&＃39;;2.给字段添加注释&＃xff1a;
comment on column tablename.colis &＃39;字段注释&＃39;;
1.把一个A表的某些记录 形成B表&＃xff0c;应用场景&＃xff1a;备份某些记录到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&＃61;b.id
2.删除A表的某些数据
delete from A t where t.zxjd is null or t.zxwd is null
2.1 truncate table 表名&＃xff1b;
truncate table a用truncate删除表数据&＃xff0c;只是删除表中的数据&＃xff0c;表结构不会被删除&＃xff01;
删除整个表的数据时,过程是系统一次性删除数据,效率比较高
truncate删除释放空间
2.2 删除表&＃xff0c;drop table 表名&＃xff1b;
drop table POP_LOCATE purge
用drop删除表数据&＃xff0c;不但会删除表中的数据&＃xff0c;连表结构也被删除了——通过这个语句删除表&＃xff0c;可以马上看到表空间剩余空间变大
2.3 delete 表名&＃xff1b;
delete from A t where t.zxjd is null or t.zxwd is null用delete删除表数据&＃xff0c;只是删除表中的数据&＃xff0c;表结构不会被删除&＃xff01;
虽然也是删除整个表的数据,但是过程是系统一行一行地删,效率比truncate低
delete删除是不释放空间的
3.把A表某字段前面重复的字符删除
两种方式&＃xff1a;一是字符串截取&＃xff0c;二是用空值替换&＃xff1a;
这个是字符串截取函数&＃xff1a;substr(string&＃xff0c;开始位置&＃xff0c;长度-不写的话-默认到结尾)&＃xff0c;instr&＃xff08;&＃39;源字符串&＃39; , &＃39;目标字符串&＃39; ,&＃39;开始位置&＃39;,&＃39;第几次出现&＃39;&＃xff09;
substr(t.dzqc,4) ---这样会把dzqc前面的三个字去掉&＃xff0c;截取后面所有字符
这个是替换语句&＃xff1a;
update A t set t.dzqc &＃61;Replace(t.z,&＃39;山东省&＃39;,&＃39;&＃39;)
4.给A表添加一个ID字段&＃xff0c;并从1不断增加
首先&＃xff0c;给表添加ID字段&＃xff0c;字段类型是NUMBER
其次&＃xff0c;在PLSQL里面创建一个序列&＃xff1a;
-- Create sequence
create sequence TEST
minvalue 1
maxvalue 99999999
start with 2101401
increment by 1
cache 20;
最后更新A表&＃xff1a;
update A t set t.id &＃61; test.nextval
设置这个表创建触发器&＃xff0c;让它添加记录的时候&＃xff0c;自动增加ID
create or replace trigger aoto_add_idbefore inserton Afor each row
declareprimary_key_value number;
beginselect test.nextval into primary_key_value from dual; //test.nextval 是上面创建的序列:new.id:&＃61;primary_key_value;
end aoto_add_id
创建完毕触发器&＃xff0c;要执行。
5.给A表的时间戳字段添加系统的时间戳&＃xff1a;
update a t set t.gxsj &＃61; systimestamp
格式化查看这个时间戳字段&＃xff1a;
select to_char(t.gxsj,&＃39;YYYY-MM-DD HH24:MI:SS.FF&＃39;) from A_POI t
建表的时候时间戳字段自动添加系统时间戳&＃xff1a;
create table test(id int, t timestamp default systimestamp)
6.多表联合查询
1.查找相同字段值
select a.xx,b.xx from a,b where a.xx&＃61;b.xx
这就把两个表相同的字段&＃xff08;a.xx&＃61;b.xx&＃xff09;&＃xff0c;连接起来
如果要把联合查询的结果保存到表里面&＃xff1a;
create table PP_DZ as select a.xx,b.xx from a,b where a.xx&＃61;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 <&＃61; 10 ORDER BY sal DESC;
在oracle中是使用ROWNUM 来表示前10条的信息。
9.删除某重复字段的记录&＃xff0c;仅保留一条
9.1 重复记录是根据单个字段&＃xff08;Id&＃xff09;来判断
select * from 表a where a.Id in (select b.Id from 表b group by b.Id having count(b.Id) > 1)
9.2 删除表中多余的重复记录&＃xff0c;重复记录是根据单个字段&＃xff08;Id&＃xff09;来判断&＃xff0c;只留有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 另外一种方法
-查看多个字段重复的重复数量&＃xff0c;其他属性也显示&＃xff0c;但是仅仅显示第一行
select *,count(*) from A group by a.uid, a.qid having count(*)>1;-在上面的基础上&＃xff0c;显示这两个字段重复的所有记录
SELECT * FROM A WHERE (uid, qid) IN (SELECT A.uid,A.qid FROM A GROUP BY A.uid,A.qid HAVING COUNT(*)>1)-在上面的基础上&＃xff0c;保留ID最小的&＃xff0c;其他全部选出
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.qidHAVING COUNT(*) > 1);-在上面基础上&＃xff0c;删除选择出来的
-创建中间表,用于临时保存需要删除的数据
CREATE TABLE TT(id INTEGER, uid INTEGER, qid INTEGER);
-将要删除的数据插入中间表
INSERT INTO TT (上面选择出来的记录) ;
-删除中间表
DELETE FROM A WHERE id IN (SELECT id FROM TT);
10.update更新操作&＃xff0c;把某个字段的值写到另外一个字段
update table t set t.a &＃61; t.b where t.a is null
11.字符串转换和数字计算
—字符串截取&＃xff0c;转换为数字update A_Y t set t.age &＃61; (2019-to_number(substr(t.q,0,4))) —-求模运算update A_Y t set t.gender &＃61; &＃39;男&＃39; where mod(to_number(substr(t.sfzh,17,1)),2)&＃61;1
12.授权或者取消用户表权限
-授权
grant select,insert,update,delete,all on tablename to username-取消
revoke select,insert,update,delete,all on tablename to username
13.insert操作&＃xff0c;把查询结果插入表格
insert into tablename select col1,col2,col3 from table1,table2 where table1.id&＃61;table2.id
京公网安备 11010802041100号