月归档：八月 2010

windows下oracle手工创建和删除数据库

发表于 2010 年 8 月 9 日 由 惜分飞

创建oracle_sid为feifei的数据库

1、设置oracle_sid：set oracle_sid=feifei
2、建立pfile文件：文件名为：initfeifei.ora
内容为：

feifei.__db_cache_size=79691776
feifei.__java_pool_size=4194304
feifei.__large_pool_size=4194304
feifei.__shared_pool_size=100663296
feifei.__streams_pool_size=12582912
audit_file_dest='C:\oracle\product\10.2.0/admin/feifei/adump'
background_dump_dest='C:\oracle\product\10.2.0/admin/feifei/bdump'
compatible='10.2.0.1.0'
control_files='C:\oracle\product\10.2.0\oradata\feifei\control01.ctl','C:\oracle\product\10.2.0\oradata\feifei\control02.ctl','C:\oracle\product\10.2.0\oradata\feifei\control03.ctl'
core_dump_dest='C:\oracle\product\10.2.0/admin/feifei/cdump'
db_block_size=8192
db_domain=''
db_file_multiblock_read_count=16
db_name='feifei'
db_recovery_file_dest='C:\oracle\product\10.2.0\flash_recovery_area'
db_recovery_file_dest_size=2147483648
dispatchers='(PROTOCOL=TCP) (SERVICE=feifeiXDB)'
job_queue_processes=10
log_archive_dest_1='location=C:\oracle\product\10.2.0\archive'
open_cursors=300
pga_aggregate_target=20971520
processes=150
remote_login_passwordfile='EXCLUSIVE'
resource_limit=TRUE
sga_target=209715200
sort_area_size=65536#test
undo_management='AUTO'
undo_tablespace='UNDOTBS1'
user_dump_dest='C:\oracle\product\10.2.0/admin/feifei/udump'
utl_file_dir='c:\temp'

note：如果不是很熟悉可以从其他数据库拷贝

3、建立pfile文件中的相关的文件夹
4、建立oracle服务进程，并创建oracle密码文件
oradim -new -sid %ORACLE_SID% -intpwd MYSECRETPASSWORD -startmode M
5、执行sqlplus /nolog和connect sys/MYSECRETPASSWORD as sysdba(conn / as sysdba)(两种不同的认证方式)
6、执行startup nomount命令
7、执行create database命令

create database feifei
 logfile   group 1 ('C:\oracle\product\10.2.0\oradata\feifei\redo1.log') size 10M,
 group 2 ('C:\oracle\product\10.2.0\oradata\feifei\redo2.log') size 10M,
 group 3 ('C:\oracle\product\10.2.0\oradata\feifei\redo3.log') size 10M
 character set          WE8ISO8859P1
 national character set utf8
 datafile 'C:\oracle\product\10.2.0\oradata\feifei\system.dbf'
 size 50M
 autoextend on
 next 10M maxsize unlimited
 extent management local
 sysaux datafile 'C:\oracle\product\10.2.0\oradata\feifei\sysaux.dbf'
 size 10M
 autoextend on
 next 10M
 maxsize unlimited
 undo tablespace UNDOTBS1
 datafile 'C:\oracle\product\10.2.0\oradata\feifei\undo.dbf'
 size 10M
 default temporary tablespace temp
 tempfile 'C:\oracle\product\10.2.0\oradata\feifei\temp.dbf'

note：可以把create database写在notepad中，然后通过@path执行
8、添加需要的表空间

create tablespace Usersdatafile  'C:\oracle\product\10.2.0\oradata\feifei\user01.dbf'
 size 50M
 autoextend on
 next 10M maxsize unlimited

9、执行相关的sql脚本，主要有：
C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catalog.sql(主要是数据字典)
C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catproc.sql(主要是pl/sql相关操作)
C:\oracle\product\10.2.0\db_1\sqlplus\admin\pupbld.sql(主要是sqlplus相关操作)
至此手工创建数据库基本完成，当然在生产系统中，创建数据库要比这个复杂的多，主要集中在创建的pfile文件和create database命令的复杂

删除oracle_sid=feifei数据库
1、注销掉windows服务的oracle进程oradim -delete -sid feifei
2、删除oracle创建的相关文件主要是：
1）C:\oracle\product\10.2.0\oradata\feifei下的文件
2）C:\oracle\product\10.2.0\admin\feifei下的文件
3）C:\oracle\product\10.2.0\db_1\database下文件名有feifei的文件

发表在 Oracle | 评论关闭

常见数据库对日期时间格式化

发表于 2010 年 8 月 8 日 由 惜分飞

1、mysql

DATE_FORMAT(date,format)
根据format字符串格式化date值。下列修饰符可以被用在format字符串中：

%M  月名字(January……December)
%W  星期名字(Sunday……Saturday)
%D  有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。）
%Y  年, 数字, 4 位
%y  年, 数字, 2 位
%a  缩写的星期名字(Sun……Sat)
%d  月份中的天数, 数字(00……31)
%e  月份中的天数, 数字(0……31)
%m  月, 数字(01……12)
%c  月, 数字(1……12)
%b  缩写的月份名字(Jan……Dec)
%j  一年中的天数(001……366)
%H  小时(00……23)
%k  小时(0……23)
%h  小时(01……12)
%I  小时(01……12)
%l  小时(1……12)
%i  分钟, 数字(00……59)
%r  时间,12 小时(hh:mm:ss [AP]M)
%T  时间,24 小时(hh:mm:ss)
%S  秒(00……59)
%s  秒(00……59)
%p  AM或PM
%w  一个星期中的天数(0=Sunday ……6=Saturday ）
%U  星期(0……52), 这里星期天是星期的第一天
%u  星期(0……52), 这里星期一是星期的第一天
%%  一个文字“%”

2、sql server

sql server转换使用convert来实现 


Select CONVERT(varchar(100), GETDATE(), 0): 05 16 2006 10:57AM
Select CONVERT(varchar(100), GETDATE(), 1): 05/16/06
Select CONVERT(varchar(100), GETDATE(), 2): 06.05.16
Select CONVERT(varchar(100), GETDATE(), 3): 16/05/06
Select CONVERT(varchar(100), GETDATE(), 4): 16.05.06
Select CONVERT(varchar(100), GETDATE(), 5): 16-05-06
Select CONVERT(varchar(100), GETDATE(), 6): 16 05 06
Select CONVERT(varchar(100), GETDATE(), 7): 05 16, 06
Select CONVERT(varchar(100), GETDATE(), 8 ): 10:57:46
Select CONVERT(varchar(100), GETDATE(), 9): 05 16 2006 10:57:46:827AM
Select CONVERT(varchar(100), GETDATE(), 10): 05-16-06
Select CONVERT(varchar(100), GETDATE(), 11): 06/05/16
Select CONVERT(varchar(100), GETDATE(), 12): 060516
Select CONVERT(varchar(100), GETDATE(), 13): 16 05 2006 10:57:46:937
Select CONVERT(varchar(100), GETDATE(), 14): 10:57:46:967
Select CONVERT(varchar(100), GETDATE(), 20): 2006-05-16 10:57:47
Select CONVERT(varchar(100), GETDATE(), 21): 2006-05-16 10:57:47.157
Select CONVERT(varchar(100), GETDATE(), 22): 05/16/06 10:57:47 AM
Select CONVERT(varchar(100), GETDATE(), 23): 2006-05-16
Select CONVERT(varchar(100), GETDATE(), 24): 10:57:47
Select CONVERT(varchar(100), GETDATE(), 25): 2006-05-16 10:57:47.250
Select CONVERT(varchar(100), GETDATE(), 100): 05 16 2006 10:57AM
Select CONVERT(varchar(100), GETDATE(), 101): 05/16/2006
Select CONVERT(varchar(100), GETDATE(), 102): 2006.05.16
Select CONVERT(varchar(100), GETDATE(), 103): 16/05/2006
Select CONVERT(varchar(100), GETDATE(), 104): 16.05.2006
Select CONVERT(varchar(100), GETDATE(), 105): 16-05-2006
Select CONVERT(varchar(100), GETDATE(), 106): 16 05 2006
Select CONVERT(varchar(100), GETDATE(), 107): 05 16, 2006
Select CONVERT(varchar(100), GETDATE(), 108): 10:57:49
Select CONVERT(varchar(100), GETDATE(), 109): 05 16 2006 10:57:49:437AM
Select CONVERT(varchar(100), GETDATE(), 110): 05-16-2006
Select CONVERT(varchar(100), GETDATE(), 111): 2006/05/16
Select CONVERT(varchar(100), GETDATE(), 112): 20060516
Select CONVERT(varchar(100), GETDATE(), 113): 16 05 2006 10:57:49:513
Select CONVERT(varchar(100), GETDATE(), 114): 10:57:49:547
Select CONVERT(varchar(100), GETDATE(), 120): 2006-05-16 10:57:49
Select CONVERT(varchar(100), GETDATE(), 121): 2006-05-16 10:57:49.700
Select CONVERT(varchar(100), GETDATE(), 126): 2006-05-16T10:57:49.827
Select CONVERT(varchar(100), GETDATE(), 130): 28 ????? 1431  3:26:31:180PM
Select CONVERT(varchar(100), GETDATE(), 131): 18/04/1427 10:57:49:920AM

3、oracle

oracle转换日期或者时间用to_char(datetime, format)

format的参数有

HH     一天的小时数 (01-12)
HH12     一天的小时数 (01-12)
HH24     一天的小时数 (00-23)
MI     分钟 (00-59)
SS     秒 (00-59)
SSSS     午夜后的秒 (0-86399)
AM or A.M. or PM or P.M.     正午标识（大写）
am or a.m. or pm or p.m.     正午标识（小写）
Y,YYY     带逗号的年（4 和更多位）
YYYY     年（4和更多位）
YYY     年的后三位
YY     年的后两位
Y     年的最后一位
BC or B.C. or AD or A.D.     年标识（大写）
bc or b.c. or ad or a.d.     年标识（小写）
MONTH     全长大写月份名（9字符）
Month     全长混合大小写月份名（9字符）
month     全长小写月份名（9字符）
MON     大写缩写月份名（3字符）
Mon     缩写混合大小写月份名（3字符）
mon     小写缩写月份名（3字符）
MM     月份 (01-12)
DAY     全长大写日期名（9字符）
Day     全长混合大小写日期名（9字符）
day     全长小写日期名（9字符）
DY     缩写大写日期名（3字符）
Dy     缩写混合大小写日期名（3字符）
dy     缩写小写日期名（3字符）
DDD     一年里的日子(001-366)
DD     一个月里的日子(01-31)
D     一周里的日子(1-7；SUN=1)
W     一个月里的周数
WW     一年里的周数
CC     世纪（2 位）
J     Julian 日期（自公元前4712年1月1日来的日期）
Q     季度
RM     罗马数字的月份（I-XII；I=JAN）－大写
rm     罗马数字的月份（I-XII；I=JAN）－小写

发表在 MySQL, Oracle, SQL Server | 评论关闭

精彩SQL语句

发表于 2010 年 8 月 7 日 由 惜分飞

1、oracle求每月第一天和最后一天

SELECT Trunc(Trunc(SYSDATE, 'MONTH') - 1, 'MONTH') First_Day_Last_Month,
 Trunc(SYSDATE, 'MONTH') - 1 / 86400 Last_Day_Last_Month,
 Trunc(SYSDATE, 'MONTH') First_Day_Cur_Month,
 LAST_DAY(Trunc(SYSDATE, 'MONTH')) + 1 - 1 / 86400 Last_Day_Cur_Month
FROM dual;

2、sql server对日期的常规处理

SELECT REPLACE(CONVERT(varchar(10),GETDATE(),120),N'-0','-')  短日期格式：yyyy-m-d
SELECT STUFF(STUFF(CONVERT(char(8),GETDATE(),112),5,0,N'年'),8,0,N'月')+N'日'  长日期格式：yyyy年mm月dd日 --A. 方法1
SELECT DATENAME(Year,GETDATE())+N'年'+DATENAME(Month,GETDATE())+N'月'+DATENAME(Day,GETDATE())+N'日'   长日期格式：yyyy年mm月dd日 --A. 方法2
SELECT DATENAME(Year,GETDATE())+N'年'+CAST(DATEPART(Month,GETDATE()) AS varchar)+N'月 '+DATENAME(Day,GETDATE())+N'日'  短日期格式：yyyy年m月d日
SELECT CONVERT(char(11),GETDATE(),120)+CONVERT(char(12),GETDATE(),114)  完整日期+时间格式：yyyy-mm-dd hh:mi:ss:mmm
Select CONVERT(varchar(100), GETDATE(), 8 ): 10:57:46
Select CONVERT(varchar(100), GETDATE(), 24): 10:57:47
Select CONVERT(varchar(100), GETDATE(), 108): 10:57:49
Select CONVERT(varchar(100), GETDATE(), 12): 060516
Select CONVERT(varchar(100), GETDATE(), 23): 2006-05-16

3、oracle中400w条记录的表中随机删除20条记录

--使用for
declare 
begin
     for c in (select * from (  select rowid rd from t2  order by dbms_random.value)m where rownum<=20) 
      loop
        delete from t2 where rowid=c.rd;
      end loop;
  commit;
end;
--不使用for
declare abc number;
arid rowid;
cursor cursor_sal is
 select * from (select rowid rd from t2  order by dbms_random.value)m where rownum<=20;
begin
      open cursor_sal;
      loop
        fetch cursor_sal into arid;
         exit when cursor_sal%notfound;
        delete from t2 where rowid=arid;
      end loop;
      close cursor_sal; 
  commit;
end;
发表在 Oracle, SQL Server | 评论关闭