Oracle常用知识点与脚本语句

1.插入：
insert into www_yttt (XM,XB,MZ,JG,GH,HF,ZZMM,HKXZ) 
VALUES ('李现',0,'汉族','我爱新华',5,0,0,1);
COMMIT;(不点提交按钮)

2.字段：
Name Type Nullable(打钩可为空) comment（备注）

3.key主键
命名：表名_PK_主键字段（WWW_YTTT_PK_GH）

4.Indexes索引
命名：表名_PK_主键字段（WWW_YTTT_PK_GH）           Columns:GH
表名：WWW_YTTT_XMXB     Columns:XM,XB

5.多行插入
insert all
into www_yttt (XM,XB,MZ,JG,GH,HF,ZZMM,HKXZ) VALUES ('喻言',1,'汉族','青春有你',3,0,0,1)
into www_yttt (XM,XB,MZ,JG,GH,HF,ZZMM,HKXZ) VALUES ('胡歌',1,'汉族','仙剑奇侠传',2,0,0,1)
select 1 from dual;
COMMIT;

6.多行查询
select * from WWW_YTTT t;
select * from WWW_K t;

7.导出表view,右键导出结果，在notepad++打开（无编码工具）

8.查询指定结果集
（1）select * from WWW_YTTT t WHERE T.XM='易烊千玺';
（2）select * from WWW_YTTT WHERE XM IN('易烊千玺','李现');

（3）select * from WWW_YTTT t WHERE T.XM !='易烊千玺';
（4）select * from WWW_YTTT t WHERE T.XM <> '李现';
（5）select * from WWW_YTTT WHERE XM NOT IN('易烊千玺','李现');
为什么要带别名T(Table),因为打t,可以快捷查询自己想要的表；

9.多表查询规范：
（1）查询对应的列并 临时 更改属性名显示：
select t1.JG as DF,t1.GH as HM from WWW_YTTT T1 WHERE T1.XB=1;
（2）查询一定范围内的符合的数量
select count(*) as shuliang from WWW_YTTT T1 WHERE T1.XB=1;
（3）关联表合并查询
SELECT * FROM WWW_YTT T1,WWW_YTTT T2 WHERE T1.XH=T2.GH;
（4）数量
SELECT COUNT(*) AS shuliang FROM WWW_YTT T1,WWW_YTTT T2 WHERE T1.XH=T2.GH;

10.求和
(1)无where:
select sum(GH) as sum_gh from WWW_YTTT；
(2)有where:
select sum(GH) as sum_gh from WWW_YTTT t where t.jg in ('王者','江南');
11.分组统计数量
(1)无where:
SELECT XB,COUNT(*) AS COUNT_XB FROM WWW_YTTT
GROUP BY XB;
(2)有where:
SELECT XB,COUNT(*) AS COUNT_XB FROM WWW_YTTT
where jg in ('王者','江南')
GROUP BY XB;

12.分组求和
select XB,sum(GH) as sum_gh from WWW_YTTT t 
where t.jg in ('王者','江南')
GROUP BY XB;

13.分组求和  与  统计数量
select XB,sum(GH) as sum_gh,count(*) as count_xb 
from WWW_YTTT t 
where t.jg in ('王者','江南')
GROUP BY XB;

14.查找唯一值
(1)select w.xb from WWW_YTTT w group by w.xb;
(2)select distinct w.xb from WWW_YTTT w;

15.最小、最大、平均值
select min(w.GH) from WWW_YTTT w;
select max(w.GH) from WWW_YTTT w;
select avg(w.GH) from WWW_YTTT w;

16.一条表示最小、最大、平均值
select min(GH),max(GH),avg(GH) from WWW_YTTT;
select min(w.GH) as min,max(w.GH) as max,avg(w.GH) as avg from WWW_YTTT w;

17.创建表
-- Create table
create table WWW_YT
(
  XH   NUMBER not null,
  XM   NVARCHAR2(20) not null,
  XN   NUMBER not null,
  XQ   NUMBER not null,
  KM   NUMBER not null,
  CX   NUMBER,
  CJ   NUMBER
);

-- Add comments to the columns 
comment on column WWW_YT.XH
  is '学号';
comment on column WWW_YT.XM
  is '姓名'; 
comment on column WWW_YT.XN
  is '学年';
comment on column WWW_YT.XQ
  is '学期';
comment on column WWW_YT.KM
  is '科目';
comment on column WWW_YT.CX
  is '是否重修';
comment on column WWW_YT.CJ
  is '成绩';

插入语句见图；

查询
select max(w.CJ) as 最高分,min(w.CJ) as 最低分,avg(w.CJ) as 平均分 from WWW_YT w where w.XN in (2018);
select max(w.CJ) as 最高分,min(w.CJ) as 最低分,avg(w.CJ) as 平均分 from WWW_YT w where w.KM in ('数学');
select max(w.CJ) as 最高分,min(w.CJ) as 最低分,avg(w.CJ) as 平均分 from WWW_YT w where w.XM in ('易烊千玺');

18.一表复制
insert into WWW_YT select * from WWW_YT;
SELECT COUNT(*) FROM WWW_YT;

19.删除整个表（表头还在）
DELETE FROM WWW_YT
where 1=1;
SELECT COUNT(*) FROM WWW_YT;

20.快速删除（无日志）
Truncate table WWW_YTT；

21.删除整个表（表头都没了）
DROP TABLE WWW_YTT;

22.更新数据
update WWW_YTTT set XM='莫奕' where XM='喻言';

23.查找丢失的数据（闪回）
select * from WWW_YTTT as of timestamp to_timestamp('2020-06-11 18:30:03','yyyy-mm-dd hh24:mi:ss');

24.返回SCN
SELECT timestamp_to_scn(to_timestamp('2020-06-11 18:30:03','yyyy-mm-dd hh24:mi:ss')) SCN FROM 表名

25.还原数据
ALTER TABLE WWW_YTTT ENABLE ROW MOVEMENT;
FLASHBACK TABLE WWW_YTTT TO SCN 8643995;