常用数据库sql总结(不断总结更新中)

1.统计表的列数

   oracle:

         select count(*) from USER_TAB_COLUMNS where table_name ='TABLE_NAME';//表名要大写
   sqlserver:
         select count(1) from syscolumns where id =object_id('table_name');

2.复制表结构

   oracle:

         create table dest_table_name  as  select * from src_table_name;

   sqlsever:

         select *  into dest_table_name  from src_table_name  where 0=1

3.复制表结构以及表内数据

   sqlserver:   

         select *  into  dest_table_name from  src_table_name                     //适用于目标表不存在
         insert     into  dest_table_name(fld1, fld2)  select  fld2, 5  from src_table_name  //目标表必须存在
4.日期有关
    sqlserver:   

         CONVERT(varchar(100), sys_time, 23) AS sys_time                   //截取年月日   ex.2009-03-08
         CONVERT(varchar(7), sys_time, 23) AS sys_time                       //截取年月      ex.2009-03
         select DATEPART(weekday,'2009-10-18')                                   //统计日期在一周中的天数 周日为1  周六为7
         select DATENAME(week, '2009-10-18')                                       //统计日期在一年中的周数

        

         //2009-12-30 和2010-1-1 虽然属于同一周 sqlserver把其按年份分割开来

         select  DATEPART(yy, '2009-12-30'),DATENAME(week, '2009-12-30')   //输出:2009    53
         select  DATEPART(yy, '2010-1-1'),DATENAME(week, '2010-1-1')           //输出:2010    1

 

        按周统计的问题
        --2009-10-18为周日
        select DATEpart(week, '2009-10-18') ,DATEpart(weekday,'2009-10-18');       --2009年第43周的第1天

        --2009-10-18为周日
        set datefirst 1;
        select DATEpart(week, '2009-10-18') ,DATEpart(weekday,'2009-10-18');        - -2009年的42周第7天

      

          /*
           * 统计数据的所属年份,所属周数,以及该周拥有几天的数据记录
          */
         set datefirst 1;//设置星期一为一个星期第一天,sqlserver默认星期天为一个星期第一天
         select T.msc_name,T.year,T.week,count(*)  from
                 (SELECT
                     msc_name,
                     DATEPART(yy, sys_time) AS year,
                     DATEPART(week, sys_time) AS week,
                     DATEPART(weekday,sys_time) As weekday
                FROM         dbo.FUN_OMC_GSM_CELL_MSC_NAME
                group by  msc_name,DATEPART(yy, sys_time),DATEPART(week, sys_time),DATEPART(weekday,sys_time)
          ) T

          group by T.msc_name,T.year,T.week
     informix:
           extend(systime,year to day)//2003-01-01
           extend(systime,year to month)//2003-01
           extend(systime,year to year)//2003
           extend(current - 2 units minute)   //当前时间减 2分钟

  

5.sql性能统计
       sqlserver
          declare @d datetime;
          set @d=getdate();
          --sql语句
           select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate());

         

           set statistics time on    --统计时间
           --sql
           set statistics time off
           set statistics io   on      --统计io
           --sql 
          set statistics io  off
6.其他
      sqlserver
      1.清除sqlserver缓存
          DBCC FREEPROCCACHE;
          DBCC DROPCLEANBUFFERS;
      2.from 子查询
         select *  from ( select parent_id,count(*) as count from entity group by parent_id ) a
         -- 子查询需要给统计列给别名,比如 count(*) 别名为count

      3.sqlserver 分页方法一

         -- 每页10条数据,第三页数据,按时间降序排列

         SELECT  * 
           FROM ( SELECT  TOP 30 sysdate
                                  from tb
                                  where 1=1 group by sysdate order by sysdate desc ) temptb
                           where sysdate not in (select top 20  sysdate
                                            from tb
                                            where 1=1 group by sysdate order by sysdate desc )
                           order by sysdate desc
     4.统计查询分页
       SELECT  * 
        FROM ( SELECT  TOP 30 CONVERT(varchar(12),sysdate,112)+lac as pk, sysdate,lac,count(*) as count
             from tb_demo 
             where 1=1 group by sysdate,lac order by sysdate desc,lac) tb
                           where pk not in (
                                   select pk from 
                                   (select top 20 CONVERT(varchar(12),sysdate,112)+lac as pk, sysdate,lac
                                            from tb_demo 
                                            where  1=1 group by sysdate ,lac order by sysdate desc,lac ) tb2 )
                           order by sysdate desc

7.常用oracle命令

//查看表结构
desc tab;
//显示当前用户的所有表
select tname from tab;
//查找sequence
select SEQUENCE_NAME from user_sequences;
//查看oracle数据库字符集
select userenv('language') from dual;

//改变sqlplus日期显示格式
alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';

//建触发器
create or replace trigger iqcar_transfer_to_misc_user
  after insert or update on user_order 
  for each row
declare
  -- local variables here
  dinnerid varchar2(10);
begin
  dinnerid := '10086';
  if inserting then
    if ( trim(:New.Dinner_Id) = dinnerid) and ( trim(:New.State) = 'D01' )  then
       insert into MISC_USER values (SEQ_MISC_USER_ID.NEXTVAL,:NEW.CELL_NUMBER,:New.Dinner_Id,:New.Order_Date,:NEW.STATE);
    end if;  
  end if;
  if updating then
    if ( trim(:Old.Dinner_Id) = dinnerid) and ( trim(:New.State) = 'D10') then
       insert into MISC_USER values (SEQ_MISC_USER_ID.NEXTVAL,:Old.CELL_NUMBER,:Old.Dinner_Id,:NEW.END_DATE,:New.STATE);
    end if;
  end if;
end transfer_to_misc_user;

//查找cell_number重复的所有记录
select * from user_order where cell_number in (select cell_number from cell_user_order group by cell_number having(count(*))>1)

//查看大字节类型数据内容
set long 2000;
//显示当前用户
show user ;
//设置sqlplus行长度
set linesize 1000;
//表复制
create table b as select * from iqcar_misc_user

 

// sqlplus执行多条sql

SET   SERVEROUTPUT   ON
 --多条sql 在这

/       -- 用  / 表示结束

 //查看触发器主题内容
set long 2000;
SET   SERVEROUTPUT   ON ;
select TRIGGER_BODY from user_triggers where  TRIGGER_NAME ='MISC_USER';

 

//查看数据表锁
select   lpad( '   ',decode(l.xidusn,0,3,0))||l.oracle_username   User_name,
              o.owner,o.object_name,o.object_type
from   v$locked_object   l,dba_objects   o
where   l.object_id=o.object_id
order   by   o.object_id   desc ;

 select sess.sid,
         sess.serial#,
         lo.oracle_username,
         lo.os_user_name,
         ao.object_name,
         lo.locked_mode
   from  v$locked_object lo,dba_objects ao,v$session sess
  where  ao.object_id = lo.object_id
    and  lo.session_id = sess.sid;
//除去锁
  alter system kill session '113,8359';

--查询sequence
select SEQUENCE_NAME from user_sequences where  SEQUENCE_NAME  like 'SEQ_IQCAR%';
show error;  --显示错误
drop trigger trigger_name;
show user;   --显示当前用户

sqlserver2005客户端访问:
58.19.183.230,1393  -- 格式如: IP,Port

//导出clob数据,user/passwd@servicename
  exp govhubei/govhubei@govhubei   
  select dbms_lob.instr(ziduan ) from biao

//创建表空间 (plsql->command window)
  create tablespace TSB datafile 'D:\oracle\temp\TSB.dbf' size 200m autoextend on next 10m maxsize unlimited;
  alter database datafile 'D:\oracle\temp\TSB .dbf' autoextend on;
  alter database datafile 'D:\oracle\temp\TSB .dbf' resize 220m;

//含大数据类型数据表的导入导出问题，比如 服务器A表空间TSA转移到服务器B表空间TSB
  1.本地建TSB表空间  见上命令
  2.拷贝TSA中的表到TSB中
     create table TSB.dest_table_name  as  select * from TSA.src_table_name;
  3.导出大数据类型表 用exp命令,目前所有数据表在表空间TSB下,见上命令
  4.登录目的服务器B,导入到目的表空间

 

//导入dmp
    imp user/passwd file=f:\jc_clob.dmp full=y
    imp user/passwd file=f:\jc_clob.dmp full=y
    imp user/password@db file = /home/wcity/jcw/jcw_server.dmp ignore=y full =y

 

//查看表所在表空间
select TABLESPACE_NAME from all_tables where table_name='TB_NEWS_CONTENT';
//查看TB开头的所有表
select tname from tab where tname like 'TB%'
//排序后的前100条记录
select * from (select * from tb t where t.category_id ='9' order by t.order desc) where rownum<=100;

//oracle表重命名
alter table TB_NEWS_CONTENT1 rename to TB_NEWS_CONTENT