Oracle使用大全

一. 简单增删改

注：1. 复制仅复制表的结构或数据，其他信息不会进行复制，比如（主键、外键、唯一键、索引等）
2. WHERE 1=2 指查询的结果为空，若写成 WHERE 1=1 ，则会将整个表结构及其表数据复制过去

1、表结构

1.插入表结构  《 alter table 表名 add (字段名 字段类型 默认值 是否为空); 》
alter table T_JS add primary key(id);                                --增加主键
alter table T_JS add (xm varchar(50) default '空' not null);         --增加单个列字段
alter table T_JS add (xm varchar(50),xb varchar(10));                --增加多个列字段
alter table T_JS add constraint ck_JS_xb check(xb in ('男','女'));   --增加性别检查约束
alter table T_JS add constraint ck_JS_nl check(nl>0 and nl<=130);    --增加年龄检查约束

2.修改表结构  《 alter table 表名 modify (字段名 字段类型 默认值 是否为空); 》
alter table T_JS rename to T_JS_bak;                   --修改表名称                             
alter table T_JS modify (xm varchar(50),xb char(10));  --修改多个列字段类型 
alter table T_JS rename column dw to dwmc;             --修改列名称

3.删除表结构
alter table T_JS drop primary key(id);                 --删除主键
drop table T_JS;                                       --删除整个表及数据
alter table T_JS drop column dw;                       --删除单个列 
alter table T_JS drop (xm,xb,...);                     --删除多个列

4.复制表结构
create table T_JS_bak as select * from T_JS where 1=2;               
create table T_JS_bak like T_JS;                                     

5.复制表结构和数据
create table T_JS_bak as select * from T_JS;
2、表数据

1.插入表数据
insert into T_JS values (1,' 张三','男',....);          --插入所有列的值
insert into T_JS(id,xm,xb) values (1,'张三','男');      --插入对应列的值           

2.修改表数据
update T_JS  set xh='20221111' where xh='20223333';     --更新满足where条件的数据
update T_JS  set xh='20221111';                         --更新所有数据

3.删除表数据
delete from T_JS  where id=1;                           --删除满足where条件的数据
delete from T_JS;                                       --删除所有数据，保留日志，慢
truncate table T_JS;                                    --删除所有数据，不保留日志，快

4.复制表数据
insert into T_JS_bak select * from T_JS;                             --复制相同表数据
insert into T_JS_bak( id,xm...) select id,xm... from T_JS;           --复制不同表数据
二. 常用语法查询

1、关于=><=、between…and…、like、in、and、or 语句

1、< 小于、<= 小于等于、> 大于、>= 大于等于、!= 不等于
2、betwee…and…的时候，必须左小右大，左大 有小查不出来值，等同于>= and <=
3、in 包含，相当于多个 or （not in不在这个范围中）
4、and为并，or为交，无先后顺序，and的优先级比or高，想要or先执行加小括号

1.查询表T_JS大于等于5，小于等于10  
select * from T_JS  where num>=5 and num<=10
select * from T_JS  where num between 5 and 10

2.查询表T_JS 所有性(xm)张..的人
select * from T_JS  where xm like '张%';

3.查询表T_JS 包含(xm)超并且是男性(xb)
select * from T_JS where xm like '%超%' and xb='1';

4.查询表T_JS 包含张(xm)或单位在海淀(dw)的，并且是男性(xb)
select * from T_JS where (xm like '%张%' or dw like '%海淀%') and xb=1;

5.查询表T_JS 名称(xm)为张三、李四的人
select * from T_JS where xm in ('张三'，'李四');
2、关于 to_char、to_number、to_date 、cast 语句

--oracle
-to_char：转到字符串，例如
    日期转到字符串：to_char(sysdate,'yyyy-mm-dd')  
-to_number：转到数字，例如
    字符串转数字：to_number('100')
-to_date：转到日期，例如
    字符串转日期：to_date('2017-01-01','yyyy-mm-dd')
    
--mysql
-cast：转到字符串，例如
    日期转到字符串：cast(now() as char)  或  DATE_FORMAT(NOW(), '%Y-%m-%d') 
-cast：转到字符串，例如
    字符串转到数字：cast('100' as unsigned)
-cast：转到字符串，例如
    字符串转到日期：cast('19981122' as date)  或  STR_TO_DATE('2017-01-06','%Y-%m-%d')
3、关于 max、min、avg、sum 、count 语句

1.查询T_JS 中缴费(num)最大、最小、平均、总和
select max(num) from T_JS;
select min(num) from T_JS;
select avg(num) from T_JS;
select sum(num) from T_JS;

2.查询T_JS 中姓别(xb)为男性的所有人数
select count(xb) 男性人数 from T_JS where xb='男'
4、关于 round、trunc 语句

round()：数值取值，四舍五入；trunc()：数值截取，非四舍五入

1.查询教师T_JS中金额  例如：1234.567，保留2位小数（需要四舍五入）   ---返回结果 1234.57
select round(1234.567,2) from  T_JS; 
或者                                 ---采用cast与decimal结合，cast表示: cast(字段名 as 转换的类型 )
select cast(1234.567 as decimal(7,2)) from T_JS;  ---decimal(7,2)表示数值中共有7位，保留2位小数

2.查询教师T_JS中金额  例如：1234.567，保留2位小数（不需要四舍五入）  ---返回结果 1234.56
--oralce
select trunc(1234.567,2) from T_JS;
--mysql
select truncate(1234.567,2) from T_JS;
5、关于distinct 、decode、listagg 语句

1.查询T_JS 性别(xb)去重
select distinct(xb) from T_JS;

2.分组转行表T_JS中李四（xm）得多个手机号（sjh），并用，分开
select xm, listagg(sjh,',') within group (order by xm) as sjh from T_JS where xm='李四' group by xm;

3.查询教师表T_JS（xb）中1、2更换为男、女
---oracle
select xm,decode(xb,'1','男','2','女') as sex from T_JS;
---mysql中
select xm,IF(xb='1','男','女') as sex from T_JS;

6、关于 order by、group by、having 语句

group by ：分组查询；having：查询排序，常配合asc、desc使用（重复数据查询）

1.查询T_JS 姓名（xm）排序
select * from T_JS order by xm; (默认升序)
select * from T_JS order by xm desc; (降序)

2.查询T_JS 所在单位（dw）人数
select dw,count(dw) from T_JS group by dw;

3.查询T_JS 男女性别（xb）人数
select xb,COUNT(xb) FROM T_JS where xb IN (1, 2) GROUP BY xb;

4.查询T_JS 所在单位（dw）人数,并且人数大于100
select dw,count(dw) from T_JS group by dw having count(dw)>100;

5.查询T_JS 工号（xh）大于1的重复数据   （根据单字段查询重复数据）
#根据单个字段查重
select xh,count(*) from T_JS group by xh having count(*)>1;     ---查询重复数据数

select * from T_JS where xh in 
(select xh from T_JS group by xh having count(xh)>1);           ---查询重复数据信息
或者   
#using 当同名字段作连接条件，可代替on更好用，可以使用多个字段作为条件（using(id)== on.a.id=b.id）
select * from T_JS inner join 
(select xh from T_JS group by xh having count(xh)>1) as t using(xh);  

#删除以上重复数据信息，根据xh查询，id作为唯一性判断
delete from T_JS where id in                                    
(select t.id from (
select * from T_JS
where xh in (select xh from T_JS group by xh having count(xh)>1)
and id not in (select min(id) from T_JS group by xh having count(xh)>1)) as t   ---mysql特殊需要增加一层嵌套和赋值
);

6.查询T_JS 工号（xh）、姓名（xm）大于1的重复数据  （根据多字段查询重复数据，可同第5点单字段）
#根据多个字段查重
select xh,xm,count(*) from T_JS group by xh,xm having count(*)>1;

select * from T_JS where (xh,xm) in (select xh,xm from T_JS group by xh,xm having count(xh)>1);
或者
select * from T_JS inner join (select xh,xm from T_JS group by xh,xm having count(xh)>1) as t using(xh,xm);

#删除以上重复数据信息，根据xh，xm判断，id作为唯一性判断
delete from T_JS where id in
(select t.id from (
select * from T_JS 
where (xh,xm) in (select xh,xm from T_JS group by xh,xm having count(xh)>1)
and id not in (select min(id) from T_JS group by xh,xm having count(xh)>1)) as t  ---mysql特殊需要增加一层嵌套和赋值
);
7、关于 case when 语句

case when…:：判断条件，满足返回真，不满足返回假 ;例如：case when 1 then ‘男’ when 0 then ‘女’ else ‘未知’ end

1.查询教师T_JS男女性别（xb）所在比例    --如果性别为男女，一般1为男、2为女）
select count(1) as 人口总数,
sum(case when xb='1' then 1 else 0 end) 男生,
sum(case when xb='1' then 1 else 0 end)*1/count(1) 男生所在比,
sum(case when xb='2' then 1 else 0 end) 女生,
sum(case when xb='2' then 1 else 0 end)*1/count(1) 女生所占比,
sum(case when xb='0' then 1 else 0 end) 其他,                  ----考虑到个别情况，'0'代表其他性别，这句可删除
sum(case when xb='0' then 1 else 0 end)*1/count(1) 其他所占比   ----考虑到个别情况，'0'代表其他性别，这句可删除
from T_JS;
 

8、关于 left join / right join / inner join…on 语句
left join：（左连接），以左表为基础，返回左表所有与右边等值的记录
right join：（右连接），以右表为基础，返回右表所有与左边等值的记录
inner join：(等值连接)，返回两表等值的记录
1.根据T_dw表查询教师T_JS的单位（dw）
select a.*,b.dw from T_JS a left join T_dw b on a.dw=b.dw 
9、关于 length、concat、substr 语句

length()：字符串长度；concat()：字符串拼接；substr()：字符串截取

1.查询教师T_JS 身份证号(sfzj)的长度
select length(sfzh) from T_JS;

2.查询教师T_JS 姓名（xm）和身份证号（sfzh）拼接  
select concat(xm,sfzh) from T_JS;
select concat(concat(xm,' '),sfzh) from T_JS   = select xm||' '||sfzh from T_JS;    ---两个字段用空格分开

3.截取教师T_JS 身份证号（sfzh）出生日期  
select substr(sfzh,7,4) from T_JS  where length(sfzh)=18;   --(身份证号确认是18位的，从第7位开始，取4位)
10、关于 union all、union、intersect、minus 语句

union all：表示不去掉重复数据
union：表示去掉重复数据
intersect：表示交集，共有的数据
minus：表示差集，先查出第一个，然后减去第二个的数据

1.查询本科生T_BKS和研究生T_YJS的所有专业（zy）数据           --保证查询字段一致
select xh,zy from T_BKS
union 
select xh,zy from T_YJS;