***********************************************************************************************************
通用
***********************************************************************************************************
-【定义变量】
DECLARE @find varchar(30)
SET @find = 'Ring%'
SELECT au_lname, au_fname, phone
FROM authors
WHERE au_lname LIKE @find
SET NOCOUNT ON
GO
DECLARE @pub_id char(4), @hire_date datetime
--SET @pub_id = '0877'
--SET @hire_date = '1/01/93'
SELECT @pub_id = '0877', @hire_date = '1/01/93'
SET NOCOUNT OFF
SELECT fname, lname
FROM employee
WHERE pub_id = @pub_id and hire_date >= @hire_date
***********************************************************************************************************
TT
***********************************************************************************************************
-【实体存放】
select 'drop '||object_type||' '||object_name||';' from dba_objects where owner='dbusername';
select * from tables;
***********************************************************************************************************
ORCL
***********************************************************************************************************
-【数据库启动】
[oracle@ngbillsuse60] /opt/oracle> sqlplus /nolog
SQL> connect sys/oracle as sysdba;
SQL> startup;
[oracle@ngbillsuse60] /opt/oracle> lsnrctl start
-【查看表空间】
select a.tablespace_name "表空间名称",
100-round((nvl(b.bytes_free,0)/a.bytes_alloc)*100,2)||'%' "使用率(%)",
round(nvl(b.bytes_free,0)/1024/1024/1024,2) "空闲(G)",
round(a.bytes_alloc/1024/1024/1024,2) "容量(G)",
round((a.bytes_alloc-nvl(b.bytes_free,0))/1024/1024/1024,2) "使用(G)",
round((nvl(b.bytes_free,0)/a.bytes_alloc)*100,2) "空闲率(%)" ,
to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "采样时间"
from (select nvl(f.tablespace_name,'合计') tablespace_name, sum(f.bytes) bytes_alloc from dba_data_files f group by cube(tablespace_name)) a,
(select nvl(f.tablespace_name,'合计') tablespace_name, sum(f.bytes) bytes_free from dba_free_space f group by cube(tablespace_name)) b
where a.tablespace_name = b.tablespace_name(+) /*AND a.tablespace_name LIKE 'D_DAT_%'*/ ORDER BY 1;
-【同义词信息存储】
select*from dba_synonyms;
-【创建表】
DECLARE
existflagINT;
BEGIN
SELECTCOUNT(1)INTO existflag FROM User_Objects vWHERELower(v.OBJECT_NAME)='tablename';
IF existflag=1THEN
EXECUTEIMMEDIATE'creat talbe tablename(balabala,balabalaba)';
ENDIF;
END;
-【授权】
GRANTALLON My_TableTO dbuser;
-【忘记密码】
conn /as sydba
alter user system identified by zhangshibo
-【表空间和用户创建】
CREATEUSER newuserIDENTIFIEDBY newuser;
CREATETABLESPACE SPACE_DATADATAFILE'D:\DB\data.dbf'SIZE100M;
CREATETABLESPACE SPACE_INDDATAFILE'D:\DB\ind.dbf'SIZE100M;
ALTERUSER newuserDEFAULTTABLESPACE SPACE_DATA;
ALTERUSER newuserQUOTAUNLIMITEDON SPACE_IND;
GRANTCONNECT,RESOURCETO newuser;
DROPTABLESPACE SPACE_DATAINCLUDINGcontents;
-【日期字符串插入设置】
ORCAL数据库向日期型字段插入字符需要添加的注册表项:nls_date_format yyyymmddhh24miss
-【日期转换】
SELECT TO_DATE('2011-09-01','yyyy-mm-dd') FROM dual
SELECT to_date('20100901 20:59:59','yyyymmdd hh24:mi:ss') FROM dual
SELECT regexp_substr('aaa,bbb,ccc','[^,]+',1,2) FROM dual返回 bbb
-【用户解锁】
ALTER USER USERNAME ACCOUNT UNLOCK;
-【输入密码错误限制】
select profile from dba_users where username='dbusername'
alter profile DEFAULT limit failed_login_attempts unlimited;
***********************************************************************************************************
SQL SERVER
***********************************************************************************************************
-【实体定义】
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P ' AND name = 'sp_populace_change_sel_id ')
BEGIN
DROP Procedure sp_populace_change_sel_id
END
-------------------------------------------------------------------------------------------------------------
-【数据库备份】
CREATEPROCEDURE [dbo].[sp_BackupDatabase]
@databaseNamesysname,@backupTypeCHAR(1)
AS BEGIN
SET NOCOUNTON;
DECLARE @sqlCommand NVARCHAR(1000)
DECLARE @dateTime NVARCHAR(20)
SELECT @dateTime=REPLACE(CONVERT(VARCHAR,GETDATE(),111),'/','')+
REPLACE(CONVERT(VARCHAR,GETDATE(),108),':','')
IF @backupType='F'
SET @sqlCommand='BACKUP DATABASE'+@databaseName '+
TO DISK =''C:\Backup\'+ @databaseName '+_Full_'+ @dateTime '.BAK'''
IF@backupType='D'SET@sqlCommand='BACKUP DATABASE' @databaseName 'TO DISK =''C:\Backup\' @databaseName '_Diff_' @dateTime '.BAK''WITH DIFFERENTIAL'IF@backupType='L'SET@sqlCommand='BACKUP LOG' @databaseName 'TO DISK =''C:\Backup\' @databaseName '_Log_' @dateTime '.TRN'''EXECUTEsp_executesql@sqlCommandENDgo
-------------------------------------------------------------------------------------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
--=============================================
--Author: RyanDing
--Create date: 2010-10-10
--Description:备份数据库
--Parameter1:数据库名
--Parameter2:备份类型F=全部, D=差异, L=日志
--=============================================
ALTER PROCEDURE [dbo].[sp_BackupDatabase]
@databaseName sysname,@backupType CHAR(1)
AS BEGIN
SET NOCOUNT ON;
DECLARE @sqlCommand NVARCHAR(1000)
DECLARE @dateTime NVARCHAR(20)
SELECT @dateTime=REPLACE(CONVERT(VARCHAR,GETDATE(),111),'/','')+
REPLACE(CONVERT(VARCHAR,GETDATE(),108),':','')
IF @backupType='F'
SET @sqlCommand='BACKUP DATABASE'+@databaseName+
'TO DISK =''C:\Backup\'+@databaseName+'_Full_'+@dateTime+'.BAK'''
EXECUTE sp_executesql@sqlCommand
END
-------------------------------------------------------------------------------------------------------------