一、SQL SERVER 篇

Sql Server经典实例
Sql Server数据库的主要对象
Sql Server操作与技巧

一、时间函数
 --getdate 获取当前时间
 select getdate()
 --dateadd 原有时间加： 2013-02-17 13:20:16 此时间加12个月
 select dateadd(MONTH,12,'2013-02-17 13:20:16')  --返回：2014-02-17 13:20:16.000  （参数month可以改为 day，year等日期加相应的值）
 --datediff 两个时间的差 （后面-前面=返回值）
 select datediff(day,'2013-02-01','2013-02-18')   --返回：17  （参数day可以改为 month，year等日期加相应的值）
 --datepart 获取日期的某个部分整数
 select DATEPART(month, '2013-2-17')  --返回 2    （参数month可以改为 day，year等日期加相应的值）
 --datename 获取指定部位的字符串
 select datename(weekday, '2013-2-17')  --返回 星期日 （参数weekday可以改为 day，year等日期加相应的值）
 --day(), month(),year() 获取指定部位的字符串
 select day('2013-2-15') --返回15
 
二、时间格式转换
    select CONVERT(varchar, getdate(), 120 )  --返回 2013-02-17 13:37:54
    select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':','') --返回 20130217133828
    select CONVERT(varchar(12) , getdate(), 111 )  --返回 2013/02/17
    select CONVERT(varchar(12) , getdate(), 112 )  --返回 20130217
    select CONVERT(varchar(12) , getdate(), 102 )  --返回 2013.02.17
    select CONVERT(varchar(12) , getdate(), 101 )  --返回 02/17/2013
    select CONVERT(varchar(12) , getdate(), 103 )  --返回 17/02/2013
    select CONVERT(varchar(12) , getdate(), 104 )  --返回 17.02.2013
    select CONVERT(varchar(12) , getdate(), 105 )  --返回 17-02-2013
    select CONVERT(varchar(12) , getdate(), 106 )  --返回 17 02 2013
    select CONVERT(varchar(12) , getdate(), 107 )  --返回 02 17, 2013
    select CONVERT(varchar(12) , getdate(), 108 )  --返回 13:42:50
    select CONVERT(varchar(12) , getdate(), 109 )  --返回 02 17 2013 
    select CONVERT(varchar(12) , getdate(), 110 )  --返回 02-17-2013
    select CONVERT(varchar(12) , getdate(), 113 )  --返回 17 02 2013 1
    select CONVERT(varchar(12) , getdate(), 114 )  --返回 13:42:24:743
三、时间格式与其他格式转换
   将int转换为时间：convert(datetime,left(DATEid,8),101)
   将时间转换为int: year(CreateDate)*10000 + month(CreateDate)*100 + day(CreateDate)
   将时间格式[2014-07-24 10:32:43.197]转换为int20140724：select cast(replace(convert(char(10),GETDATE(),120),'-','') as int)
 *************************************************************
SQLSERVER 函数篇：
一、字符函数
ascii(字符串表达式)
char(整数表达式)
charindex(字符串表达式 1, 字符串表达式2[,整数表达式])
difference(字符串表达式 1，字符串表达式 2)
left(字符串表达式，整数表达式)
right(字符串表达式，整数表达式)
len(字符串表达式)
lower(字符串表达式)
upper(字符串表达式)
ltrim(字符串表达式)
rtrim(字符串表达式)
patindex(字符串表达式 1，字符串表达式 2)
reverse(字符串表达式)
space(整数表达式)
str（float 型小数[,总长度[,小数点后保留的位数]]）
stuff (字符串表达式 1，开始位置，长度，字符串表达式 2)
substring(字符串表达式，开始位置，长度)
replace(字符串表达式 1，字符串表达式 2，字符串表达式 3)
二、数学函数
abs(数值表达式)
cos(浮点表达式)
sin(浮点表达式)
cot(浮点表达式)
ceiling(数值表达式)
floor(数值表达式)
rand([整数表达式])
round(数值表达式[,长度[,操作方式]])
三、日期函数
dateadd(日期部分，数字，日期)
datediff(日期部分，开始日期，结束日期)
datename(日期部分，日期)
datepart(日期部分，日期)
day(日期) month (日期) year (日期)
四、转换函数
convert(数据类型[(长度)]，表达式[，样式]）
cast(表达式 as 数据类型[(长度)])
getdate()
五、系统和功能函数
select newid()
isnumeric(任意表达式)
isdate（任意表达式）  　
六、常用系统内置
常用系统存储过程：
exec sp_databases; --查看数据库
exec sp_tables; --查看表
exec sp_columns student;--查看列
exec sp_helpIndex student;--查看索引
exec sp_helpConstraint student;--约束
exec sp_stored_procedures;
exec sp_helptext 'sp_stored_procedures';--查看存储过程创建、定义语句
exec sp_rename student, stuInfo;--修改表、索引、列的名称
exec sp_renamedb myTempDB, myDB;--更改数据库名称
exec sp_defaultdb 'master', 'myDB';--更改登录名的默认数据库
exec sp_helpdb;--数据库帮助，查询数据库信息
exec sp_helpdb master;
七、常用系统变量：
1、@@IDENTITY<br>返回最后插入的标识值。这个变量很有用，当你插入一行数据时，想同时获得该行的的ID（标示列），就可以用@@IDENTITY
示例：下面的示例向带有标识列的表中插入一行，并用 @@IDENTITY 显示在新行中使用的标识值。
INSERT INTO jobs (job_desc,min_lvl,max_lvl) VALUES ('Accountant',12,125)
SELECT @@IDENTITY AS 'Identity'
2、@@ROWCOUNT
返回受上一语句影响的行数。
示例：下面的示例执行 UPDATE 语句并用 @@ROWCOUNT 来检测是否有发生更改的行。
UPDATE authors SET au_lname = 'Jones' WHERE au_id = '999-888-7777'
IF @@ROWCOUNT = 0
print 'Warning: No rows were updated'
3、@@CONNECTIONS
返回自上次启动 Microsoft SQL Server以来连接或试图连接的次数。
示例：下面的示例显示了到当前日期和时间为止试图登录的次数。
SELECT GETDATE() AS 'Date and Time', @@CONNECTIONS AS 'Login Attempts'
4、@@CPU_BUSY
返回自上次启动 Microsoft SQL Server以来 CPU 的工作时间，单位为毫秒（基于系统计时器的分辨率）。
示例：下面的示例显示了到当前日期和时间为止 SQL Server CPU 的活动
SELECT @@CPU_BUSY AS 'CPU ms', GETDATE() AS 'As of'
5、@@DATEFIRST
返回 SET DATEFIRST 参数的当前值，SET DATEFIRST 参数指明所规定的每周第一天：1 对应星期一，2 对应星期二，依次类推，用 7 对应星期日。
示例：下面的示例将每周第一天设为 5 （星期五），并假定当日是星期六。SELECT 语句返回 DATEFIRST 值和当日是此周的第几天。
SET DATEFIRST 5
SELECT @@DATEFIRST AS '1st Day', DATEPART(dw, GETDATE()) AS 'Today'
6、@@IO_BUSY
返回 Microsoft SQL Server自上次启动后用于执行输入和输出操作的时间，单位为毫秒（基于系统计时器的分辨率）。
示例：下面的示例显示 SQL Server 自启动到目前已用于执行输入/输出操作的毫秒数。
SELECT @@IO_BUSY AS 'IO ms', GETDATE() AS 'As of'
7、@@LANGID
返回当前所使用语言的本地语言标识符(ID)。
示例：下面的示例将当前会话的语言设置为意大利语 (Italian)，然后用 @@LANGID 返回意大利语的 ID。
SET LANGUAGE 'Italian'
SELECT @@LANGID AS 'Language ID'
8、@@LANGUAGE
返回当前使用的语言名。
示例：下面的示例返回当前会话的语言。
SELECT @@LANGUAGE AS 'Language Name'
9、@@MAX_CONNECTIONS
返回 Microsoft SQL Server上允许的同时用户连接的最大数。返回的数不必为当前配置的数值。
示例：下面的示例假定 SQL Server 尚未被重新配置更少的用户连接。
SELECT @@MAX_CONNECTIONS
10、@@PACK_RECEIVED
返回 Microsoft SQL Server自上次启动后从网络上读取的输入数据包数目。
示例 SELECT @@PACK_RECEIVED
11、@@PACK_SENT
返回 Microsoft SQL Server自上次启动后写到网络上的输出数据包数目。示例SELECT @@PACK_SENT
12、@@PACKET_ERRORS
返回自 SQL Server 上次启动后，在 Microsoft SQL Server连接上发生的网络数据包错误数。
示例SELECT @@PACKET_ERRORS
13、@@SERVERNAME
返回运行 Microsoft SQL Server的本地服务器名称。示例SELECT @@SERVERNAME
14、@@SERVICENAME
返回 Microsoft SQL Server正在其下运行的注册表键名。若当前实例为默认实例，则 @@SERVICENAME 返回 MSSQLServer；若当前实例是命名实例，则该函数返回实例名。
示例SELECT @@SERVICENAME
15、@@SPID
返回当前用户进程的服务器进程标识符 (ID)。
示例：下面的示例返回当前用户进程的进程 ID、登录名和用户名。
SELECT @@SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User Name'
16、@@TIMETICKS
返回一刻度的微秒数。示例SELECT @@TIMETICKS
17、@@TOTAL_ERRORS
返回 Microsoft SQL Server自上次启动后，所遇到的磁盘读/写错误数。
示例：下面的示例显示了 SQL Server 到当前日期和时间为止所遇到的错误数。
SELECT @@TOTAL_ERRORS AS 'Errors', GETDATE() AS 'As of'
18、@@TOTAL_WRITE
返回 Microsoft SQL Server自上次启动后写入磁盘的次数。
示例：下面的示例显示了到当前日期和时间为止总的磁盘读写次数。
SELECT @@TOTAL_READ AS 'Reads', @@TOTAL_WRITE AS 'Writes', GETDATE() AS 'As of'
19、@@VERSION<br>返回 Microsoft SQL Server当前安装的日期、版本和处理器类型。
示例：下面的示例返回当前安装的日期、版本和处理器类型。SELECT @@VERSION
20、@@TOTAL_READ
返回 Microsoft SQL Server自上次启动后读取磁盘（不是读取高速缓存）的次数。
示例：下面的示例显示了到当前日期和时间为止的总的磁盘读写次数。
SELECT @@TOTAL_READ AS 'Reads', @@TOTAL_WRITE AS 'Writes', GETDATE() AS 'As of'
 *************************************************************
1.常规表的重建
if exists(select * from sys.objects where name='tmp' and type='U')
    drop table tmp
else
    create table tmp (
    id int,
    name varchar(20)
    )
type='U'通常是用来防止，起名时和系统的对象同名。如果可以保证没有，可以不带。
2.临时表的重建
 if (object_id('tempdb..#tmp') is not null)
    drop table #tmp
else
　create table #tmp(id int,name varchar(20)）
3.SQL：将查询结果插入到另一个表的三种情况：
一：如果要插入目标表不存在：
select * into 目标表 from 表 where …
二：如果要插入目标表已经存在：
insert into 目的表 select * from 表 where 条件
若两表只是有部分（字段）相同，则
insert into b(col1,col2,col3,col4,…) select col1,col2,col3,col4,… from a where…
三：如果是跨数据库操作的话： 怎么把A数据库的atable表所查询的东西，全部插入到B 数据库的btable表中
select * into B.btable from A.atable where …
同样，如果是跨服务器的，也是可以的。

二、ORACLE 篇

Oracle SQL性能优化最常用40条建议：https://zhuanlan.zhihu.com/p/453446262

一、当前时间拆分：
select to_char(sysdate,'yyyy') as nowYear   from dual;   //获取时间的年 
select to_char(sysdate,'mm')    as nowMonth from dual;   //获取时间的月 
select to_char(sysdate,'dd')    as nowDay    from dual;   //获取时间的日
select to_char(sysdate,'day') from dual; 
select to_char(sysdate,'hh24') as nowHour   from dual;   //获取时间的时 
select to_char(sysdate,'mi')    as nowMinute from dual;   //获取时间的分 
select to_char(sysdate,'ss')    as nowSecond from dual;   //获取时间的秒
select to_date('2004-05-07 13:23:44','yyyy-mm-dd hh24:mi:ss')    from dual
二、当前时间自身计算：
当前时间减去 7 分钟的时间
select sysdate,sysdate - interval '7' MINUTE from dual
当前时间减去 7 小时的时间
select sysdate - interval '7' hour from dual
当前时间减去 7 天的时间
select sysdate - interval '7' day from dual
当前时间减去 7 月的时间
select sysdate,sysdate - interval '7' month from dual
当前时间减去 7 年的时间
select sysdate,sysdate - interval '7' year from dual 
三、当前时间其他计算：
START_DATE，END_DATE，计算这两个日期的时间差（分别以天，小时，分钟，秒，毫秒）：
天：   ROUND(TO_NUMBER(sysdate - START_DATE))
小时： ROUND(TO_NUMBER(sysdate - START_DATE) * 24)
分钟： ROUND(TO_NUMBER(sysdate - START_DATE) * 24 * 60)
秒：   ROUND(TO_NUMBER(sysdate - START_DATE) * 24 * 60 * 60)
毫秒： ROUND(TO_NUMBER(sysdate - START_DATE) * 24 * 60 * 60 * 1000)
四、当前时间转换
时间转换为char:
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual;   
char转换为时间:
select to_date('2003-10-17 21:15:37','yyyy-mm-dd hh24:mi:ss') from dual
 ****************************ORACLE 函数篇*********************************
一、字符函数
lower(char)：将字符串转化为小写的格式。
upper(char)：将字符串转化为大写的格式。
length(char)：返回字符串的长度。
substr(char, m, n)：截取字符串的子串，n代表取n个字符的意思，不是代表取到第n个
replace(char1, search_string, replace_string)
instr(C1,C2,I,J) -->判断某字符或字符串是否存在，存在返回出现的位置的索引，否则返回小于1;在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
C1 被搜索的字符串
C2 希望搜索的字符串
I 搜索的开始位置,默认为1
J 出现的位置,默认为1
二、数学函数
round(n,[m]) 该函数用于执行四舍五入
trunc(n,[m]) 该函数用于截取数字。
mod(m,n)取余函数
floor(n) 返回小于或是等于n的最大整数
ceil(n) 返回大于或是等于n的最小整数
abs(n) 返回数字n的绝对值
三、日期函数
sysdate 返回系统时间
add_months函数 可以得到某一时间之前或之后n个月的时间
last_day(d) 返回指定日期所在月份的最后一天
四、转换函数
to_date()函数
to_char()函数
五、系统和功能函数
1)terminal：当前会话客户所对应的终端的标示符，如计算机名
2)language: 语言
3)db_name： 当前数据库名称
4)nls_date_format： 当前会话客户所对应的日期格式
5)session_user： 当前会话客户所对应的数据库用户名
6)current_schema： 当前会话客户所对应的默认方案名
7)host： 返回数据库所在主机的名称
六、空处理函数
1.NVL函数：NVL函数的格式如下：NVL(expr1,expr2)
 含义是：如果oracle第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。
2 NVL2函数：NVL2函数的格式如下：NVL2(expr1,expr2, expr3)
 含义是：如果该函数的第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第三个参数的值。
3. NULLIF函数：NULLIF(exp1,expr2)函数的作用是如果exp1和exp2相等则返回空(NULL)，否则返回第一个值。
4.COALESCE函数 ：COALESCE(expr1, expr2, expr3….. exprn)
 COALESCE是这样来处理这些参数的。如果第一个参数为空，则看第二个参数是否是空，否则则显示第一个参数，如果第二个参数是空再看第三个参数是否为空，否则显示第二个参数，依次类推。
 COALESCE里的所有参数类型必须保持一致，NVL可以不一致。
七、其他函数
Oracle trunc()函数的用法
 /**************日期********************/
 1.select trunc(sysdate) from dual  --2011-3-18  今天的日期为2011-3-18
 2.select trunc(sysdate, 'mm')   from   dual  --2011-3-1    返回当月第一天.
 3.select trunc(sysdate,'yy') from dual  --2011-1-1       返回当年第一天
 4.select trunc(sysdate,'dd') from dual  --2011-3-18    返回当前年月日
 5.select trunc(sysdate,'yyyy') from dual  --2011-1-1   返回当年第一天
 6.select trunc(sysdate,'d') from dual  --2011-3-13 (星期天)返回当前星期的第一天
 7.select trunc(sysdate, 'hh') from dual   --2011-3-18 14:00:00   当前时间为14:41 
 8.select trunc(sysdate, 'mi') from dual  --2011-3-18 14:41:00   TRUNC()函数没有秒的精确
 /***************数字********************/
 /*
 TRUNC（number,num_digits） 
 Number 需要截尾取整的数字。
 Num_digits 用于指定取整精度的数字。Num_digits 的默认值为 0。
 TRUNC()函数截取时不进行四舍五入
 */
 9.select trunc(123.458) from dual --123 
 10.select trunc(123.458,0) from dual --123
 11.select trunc(123.458,1) from dual --123.4
 12.select trunc(123.458,-1) from dual --120
 13.select trunc(123.458,-4) from dual --0
 14.select trunc(123.458,4) from dual  --123.458
 15.select trunc(123) from dual  --123
 16.select trunc(123,1) from dual --123
 17.select trunc(123,-1) from dual --120
decode函数的用法： https://www.jb51.net/article/232218.htm
1. decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值) 该函数的含义如下：
   IF 条件=值1 THEN   RETURN(翻译值1)
   ELSIF 条件=值2 THEN  RETURN(翻译值2)
　　　　......
   ELSIF 条件=值n THEN RETURN(翻译值n)
   ELSE   RETURN(缺省值)  END IF
2. decode(字段或字段的运算，值1，值2，值3） 这个函数运行的结果是，当字段或字段的运算的值等于值1时，该函数返回值2，否则返回值3
 ****************************ORACLE 函数篇*********************************
 ****************************ORACLE 常用系统查询*********************************
1.查询Oracle正在执行的sql语句及执行该语句的用户
SELECT b.sid oracleID,b.username 登录Oracle用户名, b.serial#,spid 操作系统ID,paddr,sql_text 正在执行的SQL, b.machine 计算机名
FROM v$process a, v$session b, v$sqlarea c
WHERE a.addr = b.paddr AND b.sql_hash_value = c.hash_value

2.查看正在执行sql的发起者的发放程序
 SELECT OSUSER 电脑登录身份,PROGRAM 发起请求的程序,USERNAME 登录系统的用户名,SCHEMANAME, B.Cpu_Time 花费cpu的时间,STATUS,B.SQL_TEXT 执行的sql
 FROM V$SESSION A
 LEFT JOIN V$SQL B ON A.SQL_ADDRESS = B.ADDRESS AND A.SQL_HASH_VALUE = B.HASH_VALUE
 ORDER BY b.cpu_time DESC

3.查出oracle当前的被锁对象
 SELECT l.session_id sid,
    s.serial#,
    l.locked_mode 锁模式,
    l.oracle_username 登录用户,
    l.os_user_name 登录机器用户名,
    s.machine 机器名,
    s.terminal 终端用户名,
    o.object_name 被锁对象名,
    s.logon_time 登录数据库时间
 FROM v$locked_object l, all_objects o, v$session s
 WHERE l.object_id = o.object_id AND l.session_id = s.sid ORDER BY sid, s.serial#;
3.1 kill掉当前的锁对象可以为
 alter system kill session 'sid， s.serial#';

4.查找前十条性能差的sql
SELECT * FROM (select PARSING_USER_ID,EXECUTIONS,SORTS, 
COMMAND_TYPE,DISK_READS,sql_text FROM v$sqlarea 
order BY disk_reads DESC )where ROWNUM<10 ;

5.查看占io较大的正在运行的session
SELECT se.sid,se.serial#,pr.SPID,se.username,se.status, se.terminal,se.program,se.MODULE,se.sql_address,st.event,st.p1text,si.physical_reads, si.block_changes 
FROM v$session se,v$session_wait st, v$sess_io si,v$process pr 
WHERE st.sid=se.sid AND st.sid=si.sid AND se.PADDR=pr.ADDR AND se.sid>6 AND st.wait_time=0 AND st.event NOT LIKE '%SQL%' 
ORDER BY physical_reads DESC
6.对比两个库表结构 字段和数据类型是否一致
select u1.OWNER,
       u2.OWNER,
       u1.TABLE_NAME newTableName,
       u2.TABLE_NAME oldTableName,
       u1.COLUMN_NAME newColumnName,
       u2.COLUMN_NAME oldColumnName,
       u1.DATA_TYPE newDataType,
       u2.DATA_TYPE  oldDataType
  from all_tab_columns u1, all_tab_columns u2
   where u1.OWNER = 'AAA'
   AND U2.OWNER = 'BBB'
   and u1.TABLE_NAME = u2.table_name
   and u1.COLUMN_NAME = u2.COLUMN_NAME
   and u1.DATA_TYPE != u2.DATA_TYPE;
****************************ORACLE 常用系统查询*********************************

三、DB2篇

--获取当前日期： select current date from sysibm.sysdummy1; 
                  values current date;  
 --获取当前日期   select current time from sysibm.sysdummy1;
                  values current time; 
 --获取当前时间戳 select current timestamp from sysibm.sysdummy1;
                  values current timestamp;  
 --要使当前时间或当前时间戳记调整到 GMT/CUT，则把当前的时间或时间戳记减去当前时区寄存器：
         values current time -current timezone; 
         values current timestamp -current timezone;  
--获取当前年份   values year(current timestamp);
--获取当前月     values month(current timestamp);
--获取当前日     values day(current timestamp); 
--获取当前时     values hour(current timestamp);
--获取分钟       values minute(current timestamp);  
--获取秒        values second(current timestamp); 
--获取毫秒      values microsecond(current timestamp); 
--从时间戳记单独抽取出日期和时间 
  values date(current timestamp); 07/24/14
  values VARCHAR_FORMAT(current TIMESTAMP,'yyyy-mm-dd'); 
  values char(current date);  2014-07-24-10.46.51.978540
  values time(current timestamp);  10:47:25
--执行日期和时间的计算
  values current date+1 year; 
  values current date+3 years+2 months +15 days;
  values current time +5 hours -3 minutes +10 seconds;  
--计算两个日期之间的天数  
  values days(current date)- days(date('2010-02-20'));   
--时间和日期换成字符串  
  values char(current date);  
  values char(current time);
--要将字符串转换成日期或时间值  
  values timestamp('2010-03-09-22.43.00.000000');
  values timestamp('2010-03-09 22:44:36');  
  values date('2010-03-09');
  values date('03/09/2010');  
  values time('22:45:27');  
  values time('22.45.27'); 
  --计算两个时间戳记之间的时差：  
  --秒的小数部分为单位  
  values timestampdiff(1,char(current timestamp - timestamp('2010-01-01-00.00.00')));   --秒为单位
  values timestampdiff(2,char(current timestamp - timestamp('2010-01-01-00.00.00')));   --分为单位
  values timestampdiff(4,char(current timestamp - timestamp('2010-01-01-00.00.00')));   --小时为单位
  values timestampdiff(8,char(current timestamp - timestamp('2010-01-01-00.00.00')));   --天为单位 
  values timestampdiff(16,char(current timestamp - timestamp('2010-01-01-00.00.00')));   --周为单位  
  values timestampdiff(32,char(current timestamp - timestamp('2010-01-01-00.00.00')));   --月为单位  
  values timestampdiff(64,char(current timestamp - timestamp('2010-01-01-00.00.00')));   --季度为单位  
  values timestampdiff(128,char(current timestamp - timestamp('2010-01-01-00.00.00')));   --年为单位  
  values timestampdiff(256,char(current timestamp - timestamp('2010-01-01-00.00.00')));
  将时间转换为int:CAST(TO_CHAR(IN_DATE,'YYYYMMDD') AS INT) AS DATEID