SQL语句学习笔记

最新推荐文章于 2024-09-14 19:55:29 发布

weixin_30408739

最新推荐文章于 2024-09-14 19:55:29 发布

阅读量109

点赞数

文章标签：数据库人工智能

原文链接：http://www.cnblogs.com/hdu-2010/p/4022176.html

版权

从外部EXCEl文件导入sqlserver数据库操作命令

exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
go

select * into abc1_1 from 
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=文件路径',SQLResults$)

注意：文件路径到excel下某个固定的sheet，sheet名字不要有空格

数据库合并

insert into [新数据库名（合并后的）] select [字段] FROM [数据库] union select [字段] FROM [数据库] union ...

一般性查询语句

建立视图可以快速查询合并后的数据


select count(1) from [数据库名] --查询个数
select top 50 percent * from 表名 order by 排序字段 -- 只查前50%的数据


select  distinct * from dbo.v_AllUser where 
服务区域 = '杭州供电公司' and 联系电话 = '15397048235'

select "联系电话"
from dbo.v_AllUser
where "业务类型" = '投诉' and 服务区域 = '杭州供电公司'
group by "联系电话"
having count(distinct "工作单编号")=3

select tb.*
from (
select "联系电话"
from dbo.v_AllUser
where "业务类型"='投诉' and 服务区域 = '杭州供电公司'
group by "联系电话"
having count(distinct "工作单编号")=3
) ta, dbo.v_AllUser tb
where ta."联系电话"=tb."联系电话"

修改日期格式语句

select distinct
[联系电话],[所属区县],[服务区域],
replace(CONVERT(VARCHAR(19),受理时间,120),'-','') as 受理时间
,[业务类型],case when 业务子类 ='' then '-' else 业务子类 end as [业务子类], 
0 as "是否按5"
from dbo.v_AllUser
where 
服务区域 = '杭州供电公司' and  联系电话 <>'' and [所属区县] <>'' and [服务区域] <>''
and [受理时间] <>'' and [业务类型] <>'' order by 受理时间



/*下面这个求得是加入业务子类的非话务表语句, 受理时间[)的作用*/
select 
[联系电话],[所属区县],[服务区域],
replace(CONVERT(VARCHAR(19),受理时间,120),'-','') as 受理时间,[业务类型],
max(case when 业务子类 ='' then '-' else 业务子类 end )as [业务子类], 
case when 投诉一级分类 ='' then '-' else 投诉一级分类 end as [投诉一级分类],
0 as "是否按5"
from dbo.v_AllUser
where 
服务区域 = '杭州供电公司' and  联系电话 <>'' and [所属区县] <>'' and [服务区域] <>''
and [受理时间] <>'' and [业务类型] <>'' and 受理时间 between '20131001' and '20140101'
group by 联系电话,[所属区县],[服务区域],[受理时间],[业务类型],[投诉一级分类]
order by 受理时间

去除字段文本中特殊的换行字符

update HZ_XX set [受理内容] = replace(replace([受理内容],char(13),''),char(10),'')

或者参考

一定要注意聚合函数的练习，品味下面sql语句的作用

select
[联系电话],[所属区县],[服务区域],
replace(CONVERT(VARCHAR(19),受理时间,120),'-','') as 受理时间,
投诉一级分类,
[业务类型],
max(case when 业务子类 ='' then '-' else 业务子类 end )as [业务子类],
0 as "是否按5"
from dbo.v_AllUser
where 
服务区域 = '杭州供电公司' and  联系电话 <>'' and [所属区县] <>'' and [服务区域] <>''
and [受理时间] <>'' and [业务类型] = '投诉' and 受理时间 between '20140101' and '20140401'
group by 联系电话,[所属区县],[服务区域],[受理时间],[业务类型],投诉一级分类
having count(distinct'联系电话')=1
order by 联系电话,受理时间

A New Begining

建立一个表结构

CREATE TABLE Person(

LastName varchar(30),

FirstName varchar,

Address varchar,

Age int

)

查询数据库中某个表的所有字段name改为*可以查看更多的内容

select * from syscolumns where id = object_id('表名')

修改表字段的长度

alter table dbo.Person alter column Address varchar(30)

给person增加一列city

alter table dbo.Person add city varchar(30)

插入一行值

INSERT INTO dbo.Person values ('wei', 'Li', 'Hangzhou', 14, 'HZ')

或指定列插入

修改表中某个字段的值

update dbo.Person set LastName = 'xiao' where LastName = 'wei'

删除age为null的行

delete from dbo.person where Age is NULL

删除表结构中相同的行

select distinct * into #a from dbo.Person

delete from dbo.Person

insert into dbo.Person select * from #a

drop Table #a

显示表结构

Sql选择的字段可以另外起名字，pl/sql客户端运行

select from_id fromId, to_id toId, is_join isJoin from sys_area_join where area_name = 10000

把from_id, to_id, is_join 重命名为fromId, toId, isJoin 方便匹配结构体。可能涉及到类的反射机制。

问题：

表A结构

表B结构

合并两个表为C如下形似，按照KPI_TIME合并为如下形式。

n 表B的KPI_TIME范围一定大于表A，合并两个表时，指定时间范围[N, M]。

n 如果表A不存在该日期，则KPI_TOTAL填充为-1

KPI_Time	KPI_TOTAL	hoildy	rain	ratio	templowthreshold	tempupthreshold	week
2015-08-31	-1	0	2	1	23	28	2

这样的SQL怎么写？

select 
b1.kpi_time dateTime,
(case when t_kpi_ykrs.kpi_total is null then '-1' else t_kpi_ykrs.kpi_total end ) as passengerFlow,
b1.holidy ifHoliday,
b1.rain ifRain ,
b1.ratio adjustFactor,
b1.templowthreshold lowTemperature,
b1.tempupthreshold highTemperature,
b1.week
from (
  select t_forecast_param.kpi_time,
  max(case when t_forecast_param.kpi_key='hoildy' then kpi_value else '0' end) as holidy,
  max(case when t_forecast_param.kpi_key='rain' then kpi_value else '0' end) as rain,
  max(case when t_forecast_param.kpi_key='ratio' then kpi_value else '0' end) as ratio,
  max(case when t_forecast_param.kpi_key='templowthreshold' then kpi_value else '0' end) as templowthreshold,
  max(case when t_forecast_param.kpi_key='tempupthreshold' then kpi_value else '0' end) as tempupthreshold,
  max(case when t_forecast_param.kpi_key='week' then kpi_value else '0' end) as week
  from t_forecast_param
  where t_forecast_param.kpi_time >='20140101' and t_forecast_param.kpi_time <='20150630' and t_forecast_param.kpi_area = 14100
  group by t_forecast_param.kpi_time
) b1 left join t_kpi_ykrs on 
(b1.kpi_time=to_char(t_kpi_ykrs.kpi_time, 'yyyymmdd') and t_kpi_ykrs.kpi_area=14100 and t_kpi_ykrs.kpi_code = 'YKRS_05') 
order by dateTime asc

参考资料：

http://www.iteye.com/problems/69179

要求 KPI_TIME, KPI_ID, KPI_CODE, KPI_AREA 一致，就更新KPI_VALUE
否则，插入KPI_TIME, KPI_ID, KPI_CODE, KPI_AREA、KPI_VALUE

merge into t_kpi_rlyp a
using (select 1 from dual ) b
on (a.KPI_TIME=to_date('20150611', 'YYYY-MM-DD HH24:MI:SS') and a.KPI_ID='8006' and a.KPI_CODE='RLYP_06' and a.KPI_AREA='14100')
when matched then
update set a.KPI_VALUE=64559
when not matched then 
insert (KPI_TIME, KPI_ID, KPI_CODE, KPI_AREA, KPI_VALUE)
values (to_date('20150611', 'YYYY-MM-DD HH24:MI:SS'), 8006, 'RLYP_06', 14100, 64558)

参考资料

http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9016.htm

感谢顺子、王小波的帮助

sql取A字段重复记录的B字段值最大的一条记录
2009-10-04 14:47 ashow07 | 浏览 5898 次
SQL

如 有个表里有字段

id,name,.....

当name是重复时取其id最大的一条记录

分享到：
2009-10-05 15:17
提问者采纳

select max(id) id,name from 表 group by name 

--如果表中还有其他字段要查出,那么
select * from 表 where id in
(select max(id) from 表 group by name )

-----------------------------------------------------------  da 
  select * from t_test where rowid in (select max(rowid) from t_test group by id)

批处理建立表结构

declare
gp varchar2(3);
begin
for i in 1..25  loop
  if i<10 then
    gp:='00'||to_char(i);
    else
    gp:='0'||to_char(i);
end if;

execute immediate 'create table home_'||gp||'(msid varchar2(32),lac varchar2(32),ci varchar2(32),staytime number)';
execute immediate 'create table work_'||gp||'(msid varchar2(32),lac varchar2(32),ci varchar2(32),staytime number)';
execute immediate 'create table homework_'||gp||'(msid varchar2(32),lac_home varchar2(32),ci_home varchar2(32),staytime_home number,lac_work varchar2(32),ci_work varchar2(32),staytime_work number)';

end loop;
end;

问题汇集

-------------------------------------------------------answers

select ta.id, tb.lc, ta.val
  from (select id, max(val) as val from t_test group by id) ta, t_test tb
 where ta.id = tb.id
   and ta.val = tb.val;

--------------------------------------------------------------------------------//

select *
  from (select a.*, row_number() over(partition by id order by val desc) td
          from t_test a)
 where td = 1;

sql 分区间求解分布情况

select class_time, count(*) as vol
  from (select a.*,
               case
                 when odtime < 5 then
                  1
                 when odtime >= 5 and odtime < 10 then
                  2
                 when odtime >= 10 and odtime < 20 then
                  3
                 when odtime >= 20 and odtime < 30 then
                  4
                 when odtime >= 30 and odtime < 40 then
                  5
                 when odtime >= 40 and odtime < 50 then
                  6
                 when odtime >= 50 and odtime < 60 then
                  7
                 when odtime >= 60 and odtime < 90 then
                  8
                 when odtime >= 90 and odtime < 120 then
                  9
                 else
                  10
               end as class_time
          from (select a.*,
                       (to_date(timestamp_t, 'yyyymmddhh24miss') -
                       to_date(timestamp_o, 'yyyymmddhh24miss')) * 24 * 60 as odtime
                  from od_1800 a
                 where dis > 400
                   and msid in (select msid
                                  from tbl_msid_attributeid
                                 where attribute = 3)
                   and substr(timestamp_o, 1, 8) = 20151119) a)
 group by class_time
 order by class_time;

深度阅读

--求解常驻出行次数分布
select frequent, count(*) as vol
  from (select a.msid, nvl(b.frequent, 0) as frequent
          from (select msid from tbl_msid_attributeid where attribute = 1) a
          left join (select msid, count(*) as frequent
                      from od_1800
                     where dis > 400
                       and msid in (select msid
                                      from tbl_msid_attributeid
                                     where attribute = 1)
                       and substr(timestamp_o, 1, 8) = 20151119
                     group by msid) b on a.msid = b.msid)
 group by frequent order by frequent;
 
 --求解流动出行次数情况
 
 select frequent, count(*) as vol
  from (select a.msid, nvl(b.frequent, 0) as frequent
          from (select msid from tbl_msid_attributeid where attribute = 3) a
          left join (select msid, count(*) as frequent
                      from od_1800
                     where dis > 400
                       and msid in (select msid
                                      from tbl_msid_attributeid
                                     where attribute = 3)
                       and substr(timestamp_o, 1, 8) = 20151119
                     group by msid) b on a.msid = b.msid)
 group by frequent order by frequent;
 
 
 --求解常驻移动情况
 
 select class_dis, count(*) as vol
  from (select a.*,
               case
                 when dis < 3000 then
                  1
                 when dis >= 3000 and dis < 5000 then
                  2
                 when dis >= 5000 and dis < 10000 then
                  3
                 when dis >= 10000 and dis < 15000 then
                  4
                 when dis >= 15000 and dis < 20000 then
                  5
                 when dis >= 20000 and dis < 25000 then
                  6
                 when dis >= 25000 and dis < 30000 then
                  7
                 else
                  8
               end as class_dis
          from od_1800 a
         where dis > 400
           and msid in
               (select msid from tbl_msid_attributeid where attribute = 1)
           and substr(timestamp_o, 1, 8) = 20151119)
 group by class_dis order by class_dis;
 
 
 --求解流动人口移动情况
 
 select class_dis, count(*) as vol
  from (select a.*,
               case
                 when dis < 3000 then
                  1
                 when dis >= 3000 and dis < 5000 then
                  2
                 when dis >= 5000 and dis < 10000 then
                  3
                 when dis >= 10000 and dis < 15000 then
                  4
                 when dis >= 15000 and dis < 20000 then
                  5
                 when dis >= 20000 and dis < 25000 then
                  6
                 when dis >= 25000 and dis < 30000 then
                  7
                 else
                  8
               end as class_dis
          from od_1800 a
         where dis > 400
           and msid in
               (select msid from tbl_msid_attributeid where attribute = 3)
           and substr(timestamp_o, 1, 8) = 20151119)
 group by class_dis order by class_dis;  
 
 
 
 --常驻出行时间分布情况
 
 select class_time, count(*) as vol
  from (select a.*,
               case
                 when odtime < 5 then
                  1
                 when odtime >= 5 and odtime < 10 then
                  2
                 when odtime >= 10 and odtime < 20 then
                  3
                 when odtime >= 20 and odtime < 30 then
                  4
                 when odtime >= 30 and odtime < 40 then
                  5
                 when odtime >= 40 and odtime < 50 then
                  6
                 when odtime >= 50 and odtime < 60 then
                  7
                 when odtime >= 60 and odtime < 90 then
                  8
                 when odtime >= 90 and odtime < 120 then
                  9
                 else
                  10
               end as class_time
          from (select a.*,
                       (to_date(timestamp_t, 'yyyymmddhh24miss') -
                       to_date(timestamp_o, 'yyyymmddhh24miss')) * 24 * 60 as odtime
                  from od_1800 a
                 where dis > 400
                   and msid in (select msid
                                  from tbl_msid_attributeid
                                 where attribute = 1)
                   and substr(timestamp_o, 1, 8) = 20151119) a)
 group by class_time
 order by class_time;
 
 
 
 --流动出行时间分布情况
 
 select class_time, count(*) as vol
  from (select a.*,
               case
                 when odtime < 5 then
                  1
                 when odtime >= 5 and odtime < 10 then
                  2
                 when odtime >= 10 and odtime < 20 then
                  3
                 when odtime >= 20 and odtime < 30 then
                  4
                 when odtime >= 30 and odtime < 40 then
                  5
                 when odtime >= 40 and odtime < 50 then
                  6
                 when odtime >= 50 and odtime < 60 then
                  7
                 when odtime >= 60 and odtime < 90 then
                  8
                 when odtime >= 90 and odtime < 120 then
                  9
                 else
                  10
               end as class_time
          from (select a.*,
                       (to_date(timestamp_t, 'yyyymmddhh24miss') -
                       to_date(timestamp_o, 'yyyymmddhh24miss')) * 24 * 60 as odtime
                  from od_1800 a
                 where dis > 400
                   and msid in (select msid
                                  from tbl_msid_attributeid
                                 where attribute = 3)
                   and substr(timestamp_o, 1, 8) = 20151119) a)
 group by class_time
 order by class_time;
 
 
 --九区分布情况
 
 
 select c.district, count(1)
  from (select *
          from od_5400 a
         where dis > 400
           and msid in
               (select msid from tbl_msid_attributeid where attribute = 1)) a
  left join (select *
               from (select a.*,
                            row_number() over(partition by msid order by staytime desc) id
                       from homeresultall a) a
              where a.id = 1) b on a.msid = b.msid
  left join tbl_lac_ci_district c on c.lac = b.lac
                                 and c.ci = b.ci group by c.district order by c.district;
                                 
                                 
                                 
                                 
                                 
                                 
                                 
  select c.district, sum(a.dis)/count(1)
  from (select *
          from od_5400 a
         where dis > 400
           and msid in
               (select msid from tbl_msid_attributeid where attribute = 1)) a
  left join (select *
               from (select a.*,
                            row_number() over(partition by msid order by staytime desc) id
                       from homeresultall a) a
              where a.id = 1) b on a.msid = b.msid
  left join tbl_lac_ci_district c on c.lac = b.lac
                                 and c.ci = b.ci group by c.district order by c.district;
                                 
                                 
                                 
                                 
                                 
                                 
                                 
                                 
                                 


  select c.district, sum(a.odtime)/count(1)
  from (select a.*,(to_date(timestamp_t, 'yyyymmddhh24miss') -
                       to_date(timestamp_o, 'yyyymmddhh24miss')) * 24 * 60 as odtime
          from od_5400 a
         where dis > 400
           and msid in
               (select msid from tbl_msid_attributeid where attribute = 1)) a
  left join (select *
               from (select a.*,
                            row_number() over(partition by msid order by staytime desc) id
                       from homeresultall a) a
              where a.id = 1) b on a.msid = b.msid
  left join tbl_lac_ci_district c on c.lac = b.lac
                                 and c.ci = b.ci group by c.district order by c.district;


--ps辅助:

select c.district, count(*) from (select * from tbl_msid_attributeid where attribute = 1)a left join (select *
               from (select a.*,
                            row_number() over(partition by msid order by staytime desc) id
                       from homeresultall a) a
              where a.id = 1) b on a.msid = b.msid left join tbl_lac_ci_district c on c.lac = b.lac
                                 and c.ci = b.ci group by c.district order by c.district    

--基家出行
select a.vol/b.vol as homebasedrate from
(select count(*) as vol from (select a.*,b.lac||b.ci as laccihome from
(select * from od_5400 where dis>400 and msid in (select msid from tbl_msid_attributeid where attribute=1))a left join 
(select * from(select a.*, row_number() over(partition by msid order by staytime desc)id from  homeresultall a) where id=1) b on a.msid=b.msid)a where 
(laccellfrom=laccihome or laccellto=laccihome) and substr(timestamp_o,1,8)=20151119)a,
(select count(*) as vol from (select a.*,b.lac||b.ci as laccihome from
(select * from od_5400 where dis>400 and msid in (select msid from tbl_msid_attributeid where attribute=1))a left join 
(select * from(select a.*, row_number() over(partition by msid order by staytime desc)id from  homeresultall a) where id=1) b on a.msid=b.msid)a where 
substr(timestamp_o,1,8)=20151119)b;

select a.vol/b.vol as homebasedrate from
(select count(*) as vol from (select a.*,b.lac||b.ci as laccihome from
(select * from od_5400 where dis>400 and msid in (select msid from tbl_msid_attributeid where attribute=1))a left join 
(select * from(select a.*, row_number() over(partition by msid order by staytime desc)id from  homeresultall a) where id=1) b on a.msid=b.msid)a where 
(laccellfrom=laccihome or laccellto=laccihome) and substr(timestamp_o,1,8)=20151121)a,
(select count(*) as vol from (select a.*,b.lac||b.ci as laccihome from
(select * from od_5400 where dis>400 and msid in (select msid from tbl_msid_attributeid where attribute=1))a left join 
(select * from(select a.*, row_number() over(partition by msid order by staytime desc)id from  homeresultall a) where id=1) b on a.msid=b.msid)a where 
substr(timestamp_o,1,8)=20151121)b;


-----建立职住表
create table homeworkresult as select a.msid,a.lac as lac_home,a.ci as ci_home,b.lac as lac_work,b.ci as ci_work from
(select * from(select a.*, row_number() over(partition by msid order by staytime desc)id from  homeresultall a) where id=1 and msid in (select msid from workresultall))a left join
(select * from(select a.*, row_number() over(partition by msid order by staytime desc)id from  workresultall a) where id=1)b on a.msid=b.msid;




--通勤出行
select a.vol / b.vol
  from (select count(*) as vol
          from (select a.*,
                       b.lac_home || b.ci_home as laccihome,
                       b.lac_work || b.ci_work as lacciwork
                  from (select *
                          from od_5400
                         where dis > 400
                           and msid in (select msid
                                          from tbl_msid_attributeid
                                         where attribute = 1)) a
                  left join homeworkresult b on a.msid = b.msid) a
         where substr(timestamp_o, 1, 8) = 20151119
           and ((laccellfrom = laccihome and laccellto = lacciwork) or
                (laccellto = laccihome and laccellfrom = lacciwork))) a,
       (select count(*) as vol
          from (select a.*,
                       b.lac_home || b.ci_home as laccihome,
                       b.lac_work || b.ci_work as lacciwork
                  from (select *
                          from od_5400
                         where dis > 400
                           and msid in (select msid
                                          from tbl_msid_attributeid
                                         where attribute = 1)) a
                  left join homeworkresult b on a.msid = b.msid) a
         where substr(timestamp_o, 1, 8) = 20151119) b;
         

select a.vol / b.vol
  from (select count(*) as vol
          from (select a.*,
                       b.lac_home || b.ci_home as laccihome,
                       b.lac_work || b.ci_work as lacciwork
                  from (select *
                          from od_5400
                         where dis > 400
                           and msid in (select msid
                                          from tbl_msid_attributeid
                                         where attribute = 1)) a
                  left join homeworkresult b on a.msid = b.msid) a
         where substr(timestamp_o, 1, 8) = 20151121
           and ((laccellfrom = laccihome and laccellto = lacciwork) or
                (laccellto = laccihome and laccellfrom = lacciwork))) a,
       (select count(*) as vol
          from (select a.*,
                       b.lac_home || b.ci_home as laccihome,
                       b.lac_work || b.ci_work as lacciwork
                  from (select *
                          from od_5400
                         where dis > 400
                           and msid in (select msid
                                          from tbl_msid_attributeid
                                         where attribute = 1)) a
                  left join homeworkresult b on a.msid = b.msid) a
         where substr(timestamp_o, 1, 8) = 20151121) b;

tired

存储过程

1、简单SQL语句，HellWorld示例

--输出信息

begin

  dbms_output.put_line('Oracle Hello World!');

end;

 

2、变量的定义、使用

--定义变量

declare

  sName varchar2(20);

begin

  sName := 'jack';

  dbms_output.put_line(sName);

end;

  

--常用类型

declare

    sNum number(1);

    sCount binary_integer := 0;

    sSal number(7, 2) := 5000.00;

    sDate date := sysdate;

    sPI number(3, 2) := 3.14;

    sValid boolean := true;

    sName varchar2(20) := 'Jackson';

begin

    dbms_output.put_line('sName:' || sName);  

    dbms_output.put_line('sCount:' || sCount);  

    dbms_output.put_line('sSal:' || sSal);

    dbms_output.put_line('sDate:' || sDate);

    dbms_output.put_line('sPI:' || sPI);

    --dbms_output.put_line('sValid:' || sValid);

    dbms_output.put_line('sName:' || sName);

end;

 

--定义Table变量类型

declare 

  type type_table_emp_empno is table of emp.empno%type index by binary_integer;

  empnos type_table_emp_empno;

begin

  empnos(0) := 7369;

  empnos(2) := 6789;

  empnos(-1) := 6543;

  dbms_output.put_line(empnos(-1));

end;

 

--定义record变量类型

declare

  type type_record_dept is record (

       deptno dept.deptno%type,

       dname dept.dname%type,

       loc dept.loc%type

  );

  temp type_record_dept;

begin

  temp.deptno := 56;

  temp.dname := 'software';

  temp.loc := 'gz';

  dbms_output.put_line(temp.deptno || ' ' || temp.dname  || ' ' || temp.loc);

end;

 

--使用rowtype声明record变量

declare

  temp dept%rowtype;

begin

  temp.deptno := 57;

  temp.dname := 'it';

  temp.loc := 'sz';

  dbms_output.put_line(temp.deptno || ' ' || temp.dname  || ' ' || temp.loc);

end;

 

--sql语句完成变量赋值

declare

  v$sal emp.sal%type;

  v$ename emp.ename%type;

begin

  select sal, ename into v$sal, v$ename from emp where rownum = 1;

  dbms_output.put_line(v$sal || ' ' || v$ename);

end;

 

--sql语句完成rowtype变量赋值

declare

  v_row_emp emp%rowtype;

begin

  select * into v_row_emp from emp where empno = 7698;

  dbms_output.put_line(v_row_emp.sal || ' ' || v_row_emp.ename);

end;

 

--sql语句完成变量插入数据

create table dept2 as select * from dept;

declare

   deptno dept.deptno%type := 57;

   dname dept.dname%type := 'software';

   loc dept.loc%type := 'gz';

begin

    insert into dept2 values(deptno, dname, loc);

      commit;

end;

select * from dept2;

code sql

链接：http://www.cnblogs.com/hoojo/archive/2011/05/03/2035335.html

Oracle垃圾回收清理

在10g中bin开头表示已经删除的放在回收站的表，oracle在删除表时并没有彻底的删除，而是把表放入回收站！purge recyclebin清空回收站即可。

★recyclebin字典★

当误删除某些表时，可以通过命令恢复回来：
 flashback table table_name to before drop；
 
 查看是否开启了闪回功能：
 select flashback_on from v$database;
 
 如果确定某些表确实可以删除，可以使用下面的语句，就不会出现BIN$开头的表了
 drop table table_name purge;


删除Bin开头的表,即已经drop但存在于回收站中的表：
 
查询所有此类表
SQL> select * from recyclebin where type='TABLE';

用来删除回收站中所有的表
SQL> PURGE RECYCLEBIN     -------这语句就能清除所有以BIN开头的残留文件

 用来删除指定的表
SQL> PURGE TABLE TABLE_NAME

 用来闪回被删除的表
SQL> FLASHBACK TABLE table_name TO BEFORE DROP;


可以在的Drop表时不产生Bin型表
SQL> DROP TABLE "TableName" purge；


设置falshback功能的开启和关闭
 SQL>startup mount
 SQL>alter database flashback on(off)

View Code

Oracle正斜杠(/)的作用

正斜杠(/)：在Oracle中，用来终止SQL语句。更准确的说，是表示了“运行现在位于缓冲区的SQL代码”。正斜杠也用作分隔项。

例如：执行以下语句


CREATE TABLE TEMP

( AAA VARCHAR2(2 BYTE),

    BBB VARCHAR2(2 BYTE)

)

Insert into TEMP (AAA,BBB) values ('TESTA','TESTB');

就会报错： TABLE OR VIEW DOES NOT EXIST。

在INSERT 语句之前加入正斜杠/即可。


----------------------------------------------------
在pl/sql中
;是语句结束
/是执行语句
先要输入set serveroutput on一句,并用匿名块写法才可以出结果
例子:
set serveroutput on
declare
....
begin
...
dbms_output.put_line(...);
end;
/

Oracle 查看剩余表空间

SELECT F.TABLESPACE_NAME,
(T.TOTAL_SPACE - F.FREE_SPACE) "USED (MB)",
F.FREE_SPACE "FREE (MB)",
T.TOTAL_SPACE "TOTAL (MB)",
(ROUND((F.FREE_SPACE / T.TOTAL_SPACE) * 100)) || '% ' PER_FREE
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BLOCKS *
(SELECT VALUE / 1024
FROM V$PARAMETER
WHERE NAME = 'db_block_size') / 1024)) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT TABLESPACE_NAME, ROUND(SUM(BYTES / 1048576)) TOTAL_SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME

----------------------------------------------------------


SELECT  sum(BYTES/1024/1024/1024) g FROM user_segments
SELECT segment_name AS TABLENAME, BYTES/1024/1024/1024
  FROM user_segments；

-----------------------------------------------------------------