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
扫一扫
1280
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
