Question

类型	函数说明	备注
聚合函数	avg/sum/count
时间格式	date: YY-MM--DD time: HH-MM-SS datetime: YY-MM--DD HH-MM-SS strftime: 格式化字符串 timestamp：秒数
类型转化	将 expr 转化为 target_type CAST(expr AS target_type)	cast (timestamp as datetime)

8.5.1 获取连续时间序列（日历/月历）

表格 42 获取当前日期前三十天的日期

DB	SQL
Oracle	法 1: select to_char(trunc(current_timestamp) - level, 'YYYY-MM-DD') as date_dt from dual connect by level <= 30 法 2: SELECT TRUNC(SYSDATE, 'MM') + ROWNUM - 1 FROM DUAL CONNECT BY ROWNUM <= TO_NUMBER(TO_CHAR(LAST_DAY(SYSDATE), 'dd'))
MySQL	set @mycnt = 1; select date_add(date(now()),interval @mycnt :=@mycnt - 1 day) as day from bbk.users limit 31 法 1: SELECT @num:=@num-1 num, DATE_ADD(DATE_FORMAT(now(), '%Y-%m-%d'),INTERVAL @num DAY) as xx_date FROM bbk.users a, (select @num:=1) num_t WHERE @num < 30 ; 法 2: select date_add(date(now()),interval @i:=@i-1 day) as date from ( select 1 union all select 1 union all select 1 union all select 1) as tmp, (select @i:= 1) t
SQL Server	select convert(varchar(10),dateadd(dd,number,dateadd(month,-1,getdate())),120) as dt from master..spt_values where type='P' and dateadd(dd,number,dateadd(month,-1,getdate()))<=getdate()
SQLite	时间格式必须是类似 YY-MM-DD 这种，时间函数才能生效，如果不是 '-' 分隔，月/天 不是两位数将会出错。 常用时间函数：date time datetime strtftime julianday 可用 excel 的自定义格式将：yy-m-d 转化成 yy-mm-dd
PostgreSQL

备注：表中 SQL 语句未全部验证。

Oracle 查询未来半年的所有月份

SELECT to_char(add_months(SYSDATE, ROWNUM), 'yyyy-mm')
FROM dual
CONNECT BY ROWNUM <= 6

8.5.2 关系数据库 SQL 语句比较

表格 43 关系数据库 MySQL/Oracle/SQL Server 的常用 SQL 语句比较列表

数据库	MySQL	Oracle	SQL Server
创建用户
创建数据库	create database [db]
查询数据库名	show databases;	无库名，只有表空间
查询表名	show tables;	select * from users_; select table_name, tablespace_name from user_tables [where table_name=表名]	select name from sysobjects where xtype='u'
查询表结构	DESC [tbl_name]	从系统表中找
获取总行数	select count(*)	select count(1)
获取某列不重数	select distinct count(column)	同 MySQL
限制返回条数	limit [num]	where rownum < [num]	top [num]
结果排序	order by xxx asc/desc	同 MySQL
结果分组	group by	同 MySQL
空值处理	ifnull()	Nvl()	isnull()
字符串拼接	concat()	||	+
获取系统时间	now()	sysdate	getdate()
日期格式化	date_format(xcs_received_date,'%Y%m%d')	to_char(sysdate,'yyyymmdd')	convert(varchar(8),getdate(),112)
检查有表再删除	drop table if exists tablename	select count( 1 ) from user_tables where table_name = 'xxx'	IF OBJECT_ID('xxx') IS NOT NULL
rownum 为列	select a.* ,@rownum:=@rownum+1 as rownum	可直接当作字段使用，自动生成序列	同 Oracle
主键自增长	create table tb(id int auto increment primary key )	ORACLE 没有自动增长的数据类型，需要建立一个自动增长的序列号，插入记录时要把序列号的下一个值赋于此字段。例：使用序列自动增长： create sequence se_id；start with 1；increment by 1 --使用序列实现自动增长：se_id.nextval	create table tb(id int identity(1,1) primary key )

备注：SQL 语句大小写不敏感，语句之间用 ';' 分隔。

• 常用字符串函数：replace revert substring concat strcmp length lower trim oct hex
• 自动提交 auto commit：MySQL/SQLServer 默认自动提交，Oracle 不自动提交，需要用户手动提交。

字符串替换

update <TABLE> set specification =replace(specification,'  ',' ')

子查询

可以在下列[]的位置用另外一个 select 语句来替换

# SELECT [] FROM [] WHRE []
select *from emp t where t.sal>(select *from emp e where e.empno=7654);
select *from (select *from emp) where rownum>0;

定义变量

# SQLServer
Begin
DECLARE @count int
SET @count=123
Select @count
end

# MySQL
set @num1=(select max(rank) From tmp_ud_test where is_member=1)/3;
set @num2=(select max(rank) From tmp_ud_test where is_member=1)/3*2;
set @num3=(select max(rank) From tmp_ud_test where is_member=1)/3*3;

update tmp_ud_test a set type=
case when   a.rank<=@num1 then 1
when   @num1<a.rank and a.rank<=@num2 then 2
when @num2<a.rank   and a.rank<=@num3 then 3 end
where a.is_member=1;

# Oracle
declare
   count    number := 20;
   currtime  date := sysdate;

 begin
      update xxx set aa=count ,bb=currtime;
 end;

**获取分组的前几名 **

场景：查询出每个班级成绩最高的前三名学生，用到的表名及字段如下

Table: tmp_class_score, 班级：class_id, 成绩：score

# MySQL
select * from (
select b.*,@rownum:=@rownum+1 ,
if(@pdept=b.class_id,@rank:=@rank+1,@rank:=1) as rank,
@pdept:=b.class_id
from (
select * from tmp_class_score order by class_id, score desc
) b ,(select @rownum :=0 , @pdept := null ,@rank:=0) c ) result
where rank<=3;

# Oracle
Select
From (select a.*,row_number() over(partition by class_id order by score) rank
from tmp_class_score a)
where rank<=3

# Sqlserver
同 Oracle

说明：Sqlserver 及 Mysql 还有 rank () OVER 用替换 row_number()over 实现并列排序，就是如果一个班级最高的成绩有 2 个人那用这个函数，取出来的排序，这 2 个同学显示序号都是 1，这 2 个同学下面的同学显示的序号就会跳过 2 直 接为 3。

8.5.3 Oracle 和 IQ 常用 SQL 比较

表格 44 Oracle 和 Sybase IQ 常用 SQL 比较

	Oracle	Sybase IQ
客户端	sqlplus	isql 或 dbisql （图形界面）
连接数据库	sqlplus / as sysdba	isql -Udba -PSQL -Sasiq_demo
查看版本	select version from v$instance;	select @@version
查虚拟表	select * from dual	select * from dummy
看表结构	desc tbl_name	sp_iqcolumn (tbl_name)
查看会话	select * from v$session	sp_iqconnection
查看当前用户	show user	select user
启动进程	ckpt	iqsrv
系统视图	dba_users database_properties dba_source dba_triggers dba_views	sysusers sysoption sysprocedure systrigger sysviews