Oracle常用语句及问题处理

//直接插入
insert into tablename(fieldname1, fieldname2) values(value1,value2);
//从其他表获取值插入，需列数和类型一致
insert into tablenamea(fieldname1, fieldname2) select fieldname3,fieldname4 from tablenameb;
//创建数据表时插入
create table tablenamea as select fieldname1，fieldname2 from tablenameb;  

//删除表中全部数据（不删除表结构），可回滚
delete from tablename;  
//删除表中符合条件的数据（不删除表结构），可回滚
delete from tablename where filedname expression value; 
//删除表中全部数据（不删除表结构），比delete快，不可回滚
truncate table tablename;  
//删除表中全部数据及表结构，不可回滚
drop table tablename;

select select_list
  from table_list
  [where] [where_clause]
  [and] [and_clause]
  [group_by] [group_by_clause]
  [having] [condition]
  [order_by] [order_by_clause]

//交叉连接--得到两张表的笛卡尔积
select * from tablea a, tableb b;
//自连接--是连接表与自身的连接（示例见实例[2])
select * from tablea a, tablea b
//内连接--返回来自源表中的相关的行，用关系运算符过滤（并不是加了inner join的才叫内连接）
eg：select * from hr.countries t where region_id=1
//外连接
left join…on  左外连接(左边的表不加限制)
right join…on 右外连接(右边的表不加限制)
full join…on  全外连接(左右两表都不加限制)
(+)写法：+表示补充，即哪个表有加号，这个表就是匹配表（eg：如果加号写在右表，左表就是全部显示，是左连接）

// 设置别名
select sysdate time from dual;  或  select sysdate as time from dual;
// 查询结果排序
order by fieldname desc|asc   （desc降序、asc升序）
// 关系操作符
<,>,<>,!=, <=,=, >=           （<>与!=都是不等于，完全等同）     
// 比较操作符
is null, between…and…, in,exists,like   （_代表一个字符，%代表多个字符）
// 逻辑操作符
and, or, not
// 分组
group by  一般和聚合函数一起使用才有意义
having  (给group by添加限制条件)    eg:group by age having count(*)>1
// 连接运算符
union  取并集，去重
union all 取并集，不去重
intersect 取交集
minus 用来返回前面查询减去后面查询的部分

//修改满足条件的数据
update tablename set fieldname1 = value1 where fieldname2 = value2;

CREATE VIEW 视图名 AS SELECT 查询语句;  // 创建视图
ALTER VIEW 视图名 AS SELECT 查询语句;   // 修改视图
SELECT * FROM 视图名;                  // 查询视图
DROP VIEW 视图名;                      // 删除视图

select time from table where to_char(to_date(time,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd')--本日
select time from table where to_char(to_date(time,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd')=to_char(sysdate-1,'yyyy-mm-dd')--昨日
select time from table where to_char(to_date(time,'yyyy-mm-dd hh24:mi:ss'),'iw')=to_char(sysdate,'iw')--本周
select time from table where to_char(to_date(time,'yyyy-mm-dd hh24:mi:ss'),'iw')=to_char(sysdate-7,'iw')--上周
select time from table where to_char(to_date(time,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm')=to_char(sysdate,'yyyy-mm') --本月
select time from table where to_char(to_date(time,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm')=to_char(add_months(sysdate,-1),'yyyy-mm') --上月
select time from table where to_char(to_date(time,'yyyy-mm-dd hh24:mi:ss'),'q')=to_char(sysdate,'q')  --本季
select time from table where to_char(to_date(time,'yyyy-mm-dd hh24:mi:ss'),'q')=to_char(add_months(sysdate,-3),'q')  --上季
select time from table where to_char(to_date(time,'yyyy-mm-dd hh24:mi:ss'),'yyyy')=to_char(sysdate,'yyyy')  --本年
select time from table where to_char(to_date(time,'yyyy-mm-dd hh24:mi:ss'),'yyyy')=to_char(add_months(sysdate,-12),'yyyy')  --去年

select time from table where to_char(time,'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd')--本日
select time from table where to_char(time,'yyyy-mm-dd')=to_char(sysdate-1,'yyyy-mm-dd')--昨日
select time from table where to_char(time,'iw')=to_char(sysdate,'iw')--本周
select time from table where to_char(time,'iw')=to_char(sysdate-7,'iw')--上周
select time from table where to_char(time,'yyyy-mm')=to_char(sysdate,'yyyy-mm') --本月
select time from table where to_char(time,'yyyy-mm')=to_char(add_months(sysdate,-1),'yyyy-mm') --上月
select time from table where to_char(time,'q')=to_char(sysdate,'q')  --本季
select time from table where to_char(time,'q')=to_char(add_months(sysdate,-3),'q')  --上季
select time from table where to_char(time,'yyyy')=to_char(sysdate,'yyyy')  --本年
select time from table where to_char(time,'yyyy')=to_char(add_months(sysdate,-12),'yyyy')  --去年

select table_name from all_all_tables where owner = '大写用户名'

select * from dba_tab_columns a where a.OWNER = '大写用户名' and a.COLUMN_NAME = '大写字段名';

select owner from dba_tables where table_name=upper('大写表名');

select row_number() over (order by a.字段 asc) as number,a.* from 表名 a    

1、查找全部重复记录
select * from 表 where 重复字段 in (select 重复字段 from 表 group by 重复字段 having count(*)>1)
2、查找重复记录只显示一条
select * from 表 where id in (select max(id) from 表 group by title)

1、删除全部重复记录
delete 表 where 重复字段 in (select 重复字段 from 表 group by 重复字段 having count(*)>1)
2、删除重复记录只保留一条
delete hzt where id not in (select max(id) from hzt group by title)

select b.owner, b.object_name, a.session_id, a.locked_mode
  from v$locked_object a, dba_objects b
 where b.object_id = a.object_id;

select 'alter system kill session '''||b.sid||','||b.serial#||''';' 
 from v$locked_object a, v$session b
where a.session_id = b.sid
order by b.logon_time;

create  public  database link 创建的dblink名
　　connect to 另一个库的用户名 identified by 另一个库的密码 
　　using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 另一个库的地址)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))';

select * from dba_db_links  --查询
drop public database link 建立的dblink名  --删除

select created from dba_objects where object_name = 'table_name'

select t.sql_text, t.first_load_time
  from v$sqlarea t
 where 1 = 1
   and t.first_load_time like 'yyyy-MM-dd%' --筛选某时间
   and t.parsing_schema_name in ('大写用户名') --筛选某用户
 order by t.first_load_time desc

MERGE INTO target_table
USING source_table
ON condition
WHEN MATCHED THEN 
operation
WHEN NOT MATCHED THEN 
operation;

merge into oe.promotions_backup a
using oe.promotions b
on (a.promo_id = b.promo_id)
when matched then
  update set a.promo_name = b.promo_name
when not matched then
  insert (promo_id, promo_name) values (b.promo_id, b.promo_name);

(select column_name  
          from user_tab_columns  
         where table_name = 'T_A'  
        minus  
        select column_name  
          from user_tab_columns  
         where table_name = 'T_B')  
union   
(select column_name  
         from user_tab_columns  
        where table_name = 'T_B'  
       minus  
       select column_name  
         from user_tab_columns  
        where table_name = 'T_A');  

(select * from T_A  minus select * from T_B)  
union   
(select * from T_B  minus select * from T_A)  

select * from user_sequences;

select sequence_owner,sequence_name from dba_sequences

select 用户名.序列名.nextval from dual;

drop sequence 用户名.序列名

select * from v$version;

insert into 表名 (字段1, 字段2, 字段3)
select 字段1, 字段2, 字段3 from ...

select decode(b,0,0,a/b) from dual;

select row_number() over(order by a.列名 asc) as value,a.列名 as name
  from (select 列名 from 表名 group by 列名) a

update table set column_name1=column_name2

select chr(39) || REPLACE('a,b',',',chr(39) || ',' || chr(39)) || chr(39) from dual

select * from A where id in (select id from B);    --适用于A>>B
select * from A where exists (select 1 from B where A.id=B.id);  --适用于B>>A

select case when t.a > 1 then '成功' else '失败' end '其他' from table t;   --可以有多组when...then

select decode(to_char(sysdate,'mm'),'01','1月','02','2月','03','3月','04','4月','5-12月') from dual;

select * from t_A a left join t_B b on a.id = b.id;
select * from t_A a,t_B b where a.id=b.id(+);

select * from t_A a right join t_B b on a.id = b.id;
select * from t_A a,t_B b where a.id(+)=b.id;

select * from t_A a full join t_B b on a.id = b.id;

select * from A where id in (select id from B);                    --适用于A>>B
select * from A where exists (select 1 from B where A.id = B.id);  --适用于B>>A

exp 用户名/密码@localhost:1521/orcl file=E:文件名.dmp log=用户名.log owner=(用户名)  --导出表（带数据）
exp 用户名/密码@localhost:1521/orcl file=E:文件名.dmp log=用户名.log owner=(用户名)  rows=n --导出表结构（不带数据） 
imp 用户名/密码@localhost:1521/orcl file=E:文件名.dmp log=用户名.log fromuser=(用户名) touser=(用户名)  --导入

exp 用户名/密码@localhost:1521/orcl file=E:文件名.dmp log=用户名.log tables=(用户名.表名) --导出表（带数据）
exp 用户名/密码@localhost:1521/orcl file=E:文件名.dmp log=用户名.log tables=(用户名.表名) rows=n--导出表结构（不带数据）
imp 用户名/密码@localhost:1521/orcl file=E:文件名.dmp log=用户名.log fromuser=(用户名) tables=(表名)

[1] 账户被锁问题
sqlplus /nolog      # 不以任何用户登录（只是打开登录窗口）
connect as sysdba   # 然后需要输入待解锁的用户名和密码
alter user 用户名 account unlock;  # 账户解锁
alter user 用户名 identified by 新密码;  # 账户修改密码

[2] 对表进行授权
grant create session to 用户名;
grant create table to 用户名;

[3]对表空间无权限
alter user 用户名 quota unlimited on 表空间名

[4] “只有DBA才能导入由其他DBA导出的文件”的问题
grant dba to 用户名

[5] “XXX表空间不存在”的问题
需要创建表空间
create tablespace 表空间名
datafile 'Oracle安装路径/oradata/orcl/表空间名'    --app目录的子目录
size 1M autoextend on next 50M maxsize unlimited;

create table hr.runtime_summary_backup as select * from hr.runtime_summary

insert into hr.runtime_summary_backup select * from hr.runtime_summary

create  public  database link 创建的dblink名
　　connect to 另一个库的用户名 identified by 另一个库的密码 
　　using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 另一个库的地址)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))';

select table_name from all_all_tables where owner = '大写用户名' order by table_name
select table_name from [email protected]创建的dblink名 where owner = '大写用户名' order by table_name

select 'truncate table 用户名.' || t.table_name
  from (select table_name from all_all_tables where owner = '大写用户名') t
执行跑出来的sql语句

select 'insert into 用户名.'|| t.table_name || ' select * from 用户名.' || t.table_name || '@创建的dblink名'
  from (select table_name from all_all_tables where owner = '大写用户名') t
执行跑出来的sql语句

select 'create table bsms.' || table_name ||
       '_backup as select * from 用户名.' || table_name || ';' backup
  from (select table_name from all_all_tables where owner = '大写用户名')
执行跑出来的sql语句

把字符串转为日期格式
select date_format('2011-09-20 08:30:45', '%Y-%m-%d %H:%i:%S');              
把日期转为字符串格式
select date_format(now(),'%Y-%m-%d %H:%i:%S'); 

CASE WHEN total = '0' THEN '暂无数据' ELSE total END total,

select `code`,group_concat(`name` separator ',') from `sys_code` group by `code`

select group_concat(COLUMN_NAME) from information_schema.COLUMNS where table_name = '表名';

1 microsecond 1毫秒、1 second 1秒、1 minute 1分钟、1 hour 1小时
1 day 1天、1 week 1周、1 month 1月、1 quarter 1季、1 year 1年

update table_name a set a.time = date_add(a.time, interval 3 day)

select COLUMN_NAME from information_schema.COLUMNS where table_name = '表名' and table_schema = '数据库名';

SELECT GROUP_CONCAT(COLUMN_NAME SEPARATOR ",") FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = '数据库名' AND TABLE_NAME = '表名'

DELETE
FROM
	表名
WHERE
	id NOT IN (
		SELECT
			dt.minno
		FROM
			(
				SELECT
					MIN(id) AS minno
				FROM
					表名
				GROUP BY
					用于判定重复的字段名
			) dt
	)

ifnull(update_time, create_time)

// 今天
select * from 表名 where to_days(时间字段名) = to_days(now());

// 昨天
SELECT * FROM 表名 WHERE TO_DAYS(NOW()) - TO_DAYS(时间字段名) <= 1

// 近7天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)

// 近30天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)

// 本月
SELECT * FROM 表名 WHERE DATE_FORMAT(时间字段名, '%Y%m') = DATE_FORMAT CURDATE() , '%Y%m')

// 上一月
SELECT * FROM 表名 WHERE PERIOD_DIFF(date_format(now( ) , '%Y%m') , date_format(时间字段名, '%Y%m' )) =1

// 查询本季度数据
select * from 表名 where QUARTER(时间字段名)=QUARTER(now());

// 查询上季度数据
select * from 表名 where QUARTER(时间字段名)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));

// 查询本年数据
select * from 表名 where YEAR(时间字段名)=YEAR(NOW());

// 查询上年数据
select * from 表名 where year(时间字段名)=year(date_sub(now(),interval 1 year));

// 查询当前这周的数据
SELECT * FROM 表名 WHERE YEARWEEK(date_format(时间字段名,'%Y-%m-%d')) = YEARWEEK(now());

// 查询上周的数据
SELECT * FROM 表名 WHERE YEARWEEK(date_format(时间字段名,'%Y-%m-%d')) = YEARWEEK(now())-1;

// 查询上个月的数据
select * from 表名 where DATE_FORMAT(时间字段名,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') ; 

//查询当前月份的数据 
select * from 表名 where date_format(时间字段名,'%Y-%m')=date_format(now(),'%Y-%m')

// 查询距离当前现在6个月的数据
select * from 表名 where 时间字段名 between date_sub(now(),interval 6 month) and now();

show OPEN TABLES where In_use > 0  //查询是否锁表
show processlist   // 查看锁表进程
kill [ID] // 杀死锁表进程

$ set foreign_key_checks=0; //关闭外键检查
$ 执行sql语句
$ set foreign_key_checks=1; //开启外键检查

SELECT
    CONCAT( 'ALTER TABLE ', TABLE_NAME, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;' ) 
FROM
    information_schema.TABLES 
WHERE
    TABLE_SCHEMA = '数据库名';

SELECT
    CONCAT(
        'ALTER TABLE `',
        TABLE_NAME,
        '` MODIFY `',
        COLUMN_NAME,
        '` ',
        DATA_TYPE,
        '(',
        CHARACTER_MAXIMUM_LENGTH,
        ') CHARACTER SET utf8 COLLATE utf8_unicode_ci',
        ( CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END ),
        ';' 
) 
FROM
    information_schema.COLUMNS 
WHERE
    TABLE_SCHEMA = '数据库名' 
    AND (
    DATA_TYPE = 'varchar' 
    OR DATA_TYPE = 'char')