数据库问题指引

判断表、列是否存在的SQL语句 ORACLE

-- 判断表是否存在，存在返回1，不存在返回0

select count(*) from user_tables where table_name = upper('table_name');

-- 判断列是否存在，存在返回1，不存在返回0

select count(*) from cols where table_name = upper('table_name') and column_name = upper('column_name');

-- drop table if exists

declare
   c int;
begin
   select count(*) into c from user_tables where table_name = upper('table_name');
   if c = 1 then
      execute immediate 'drop table table_name';
   end if;
end;

reference https://stackoverflow.com/questions/1799128/oracle-if-table-exists

-- batch insert data to table with multiple columns where condition is xxx;

INSERT INTO table1 ( column1, column2, someInt, someVarChar )
SELECT  table2.column1, table2.column2, 8, 'some string etc.'
FROM    table2
WHERE   table2.ID = 7;

-- etc.

insert into table_name (column1, column2, column3, ···)
select schoolid, 'some int etc.', 'some string etc.', '1' from table_name2 where condition = '1';

reference https://stackoverflow.com/questions/25969/insert-into-values-select-from

MYSQL

-- 判断表是否存在，存在返回1，不存在返回0

select count(1) from information_schema.tables where table_schema='cfbi_wecom' and table_name='zh_qywx_msg';

Oracle\Mysql时间函数互换

date_format(date,'%Y-%m-%d %H:%i:%S')     -------------->oracle中的to_char(date,'yyyy-MM-dd HH:MM:SS');
str_to_date(date,'%Y-%m-%d %H:%i:%S')     -------------->oracle中的to_date(date,'yyyy-MM-dd HH:MM:SS');

原文链接：Mysql中to_char()和str_to_date()函数

MySQL 插入数据后返回自增id

<insert id="insert" parameterType="com.mmall.pojo.ApprovalProcess" >
    <selectKey resultType="java.lang.Long" keyProperty="id" order="AFTER" >
      SELECT LAST_INSERT_ID()
    </selectKey>
    insert into a (name, type)
    values(#{name}, #{type})
</insert>

如图：

原文链接：MySQL 插入数据后返回自增id的方法

注意：本文归作者所有，未经作者允许，不得转载