oracle 常用SQL

-- 日期查询

select * from tbl_member  ts where password = '56a5aa0561ddd3b88b924315043cf6fb' and to_char(ts.password_modified_date,'yyyy-mm-dd hh:mi:ss') = '2018-10-15 10:01:24';

-- 创建DBlink

-- Create database link
drop database link LINK_V7TEST;
create database link LINK_V7TEST
connect to PCIS_READ identified by pcis_read
using '10.16.55.38/v7rac';

注释：connect to 之后 是用户名，identified by 之后是密码，using 是数据库地址，v7rac是数据库名称；

-- 去重

select distinct * from zswx.TBL_LAWYER_VERIFY;
-- 重复全量的数据备份  
create table zswx.TBL_LAWYER_VERIFY_2018101801 as select * from zswx.TBL_LAWYER_VERIFY t ;
-- 去重之后的数据备份
create table zswx.TBL_LAWYER_VERIFY_2018101802 as select  distinct * from zswx.TBL_LAWYER_VERIFY t ;
-- 删除表中全部数据
truncate table zswx.TBL_LAWYER_VERIFY;
-- 将去重后的数据插入到表
insert into zswx.TBL_LAWYER_VERIFY select * from zswx.TBL_LAWYER_VERIFY_2018101802;

最复杂的去重需要用最为简单粗暴的SQL让他执行；

 

-- 网销会员表user_name 内部用户是否可重复
select * from zswx.tbl_member where user_name in (select user_name from zswx.tbl_member where length(user_name) != 11  group by user_name having count(user_name) > 1)