1.零时刻导致flyway执行失败

When run the flyway failed in the history scripts because of the scripts with timestamp having no default value.

Check the local mysql environment —— the sql_mode, delete the NO_ZERO_DATE and NO_ZERO_IN_DATE.

SELECT @@GLOBAL.sql_mode;
SET GLOBAL sql_mode = "XXX";

XXX is the result of select SQL and remove the NO_ZERO_DATE and NO_ZERO_IN_DATE. For example:

SET GLOBAL sql_mode = "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

2.使用另一个表填充一个表

您可以通过SELECT语句将数据填充到表中，而另一个表提供另一个表具有一组字段，这是填充第一个表所必需的。 以下是基本语法:

INSERT INTO first_table_name  
   SELECT column1, column2, ...columnN  
      FROM second_table_name 
      [WHERE condition];

3.标识符的使用

sql保留字作为标识符时，需用""或[]或()

4.SQLCE

• 修改列

sqlce中sp_rename仅支持表的修改。

sp_rename 'oldTableName','newTableName';

In Sql Server 2005 Management Studio you have to create a new column with the new name, then you have to update it with the values from your old column and then you have to delete your old column. The last action is difficult if the column is (part of) an index.

ALTER TABLE YOURTABLENAME ADD PVPROC INTEGER (or your datatype);
UPDATE YOURTABLENAME SET PVPROC = PATHSPECIMEN.PVVALUE;
ALTER TABLE School DROP COLUMN PATHSPECIMEN.PVVALUE;

• 查看表中的列属性

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='stu';
SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='stu';

5.确定Firebird SQL版本

SELECT rdb$get_context('SYSTEM', 'ENGINE_VERSION')  as version from rdb$database;