【持续更新】实用sql语法汇总

1.SQLServer，查询某个数据库中的所有表的记录总数
select a.name as aa, b.rows as bb from sysobjects as a, sysindexes as b
where a.id = b.id and a.xtype=‘u’ order by bb desc
2.Oracle，，查询某个数据库中的所有表的记录总数
select t.table_name,t.num_rows from all_tables t order by num_rows desc
3.oracle,查看表中有多少字段
select table_name,count() from DBA_TAB_COLS WHERE TABLE_NAME IN (‘table1’,‘table2’) GROUP BY TABLE_NAME;
4.sqlserver,查看表中有多少字段
select table_name,count() from information_schema.COLUMNS where table_name in(‘T_BD_ACCOUNTBANKS’,‘T_BD_ACCOUNTCURRENCY’) group by table_name;
4.年+月比较大小，自动补0
例如：2018年1月与2018年12月比较大小，自动替换为201801，201812比较大小
select * from guest.T_BD_PERIOD where CONCAT(FPERIODYEAR,CASE LEN(FPERIODNUMBER) WHEN 1 THEN CONCAT(‘0’,FPERIODNUMBER) ELSE CONCAT(’’,FPERIODNUMBER) END) >= CONCAT(‘2018’,CASE LEN(‘1’) WHEN 1 THEN CONCAT(‘0’,‘1’) ELSE ‘1’ END) AND CONCAT(FPERIODYEAR,CASE LEN(FPERIODNUMBER) WHEN 1 THEN CONCAT(‘0’,FPERIODNUMBER) ELSE CONCAT(’’,FPERIODNUMBER) END) <= CONCAT(‘2018’,CASE LEN(‘12’) WHEN 1 THEN CONCAT(‘0’,‘12’) ELSE ‘12’ END)
5.创建schema带特殊字符，加反引号
CREATE DATABASE www.mafutian.net DEFAULT CHARSET UTF8;