创建和修改数据库

要创建数据库文件，需要首先创建 SQLConnection 实例。调用其 open() 方法在同步执行模式下打开它，或者调用其 openAsync() 方法在异步执行模式下打开它。 open() 和 openAsync() 方法用于打开到数据库的连接。如果传递的 File 实例引用 reference 参数（第一个参数）的不存在的文件位置，则 open() 或 openAsync() 方法将在该文件位置创建一个数据库文件，并打开到新创建的数据库的连接。

无论创建数据库时调用的是 open() 方法还是 openAsync() 方法，数据库文件的名称都可以是采用任何文件扩展名的任何有效文件名。如果调用 reference 参数为 null 的 open() 或 openAsync() 方法，则将创建新的内存中数据库，而不是在磁盘上创建数据库文件。

以下代码清单说明使用异步执行模式创建数据库文件（新数据库）的过程。在本例中，数据库文件保存在 指向应用程序存储目录 中，文件名为“DBSample.db”：

import flash.data.SQLConnection; 
import flash.events.SQLErrorEvent; 
import flash.events.SQLEvent; 
import flash.filesystem.File; 
     
var conn:SQLConnection = new SQLConnection(); 
     
conn.addEventListener(SQLEvent.OPEN, openHandler); 
conn.addEventListener(SQLErrorEvent.ERROR, errorHandler); 
     
// The database file is in the application storage directory 
var folder:File = File.applicationStorageDirectory; 
var dbFile:File = folder.resolvePath("DBSample.db"); 
     
conn.openAsync(dbFile); 
     
function openHandler(event:SQLEvent):void 
{ 
    trace("the database was created successfully"); 
} 
     
function errorHandler(event:SQLErrorEvent):void 
{ 
    trace("Error message:", event.error.message); 
    trace("Details:", event.error.details); 
} 

<?xml version="1.0" encoding="utf-8"?> 
<mx:WindowedApplication xmlns:mx="http://www.adobe.com/2006/mxml" creationComplete="init()"> 
    <mx:Script> 
        <![CDATA[ 
            import flash.data.SQLConnection; 
            import flash.events.SQLErrorEvent; 
            import flash.events.SQLEvent; 
            import flash.filesystem.File; 
             
            private function init():void 
            { 
                var conn:SQLConnection = new SQLConnection(); 
                 
                conn.addEventListener(SQLEvent.OPEN, openHandler); 
                conn.addEventListener(SQLErrorEvent.ERROR, errorHandler); 
                  
                // The database file is in the application storage directory 
                var folder:File = File.applicationStorageDirectory; 
                var dbFile:File = folder.resolvePath("DBSample.db"); 
                 
                conn.openAsync(dbFile); 
            } 
             
            private function openHandler(event:SQLEvent):void 
            { 
                trace("the database was created successfully"); 
            } 
             
            private function errorHandler(event:SQLErrorEvent):void 
            { 
                trace("Error message:", event.error.message); 
                trace("Details:", event.error.details); 
            } 
        ]]> 
    </mx:Script> 
</mx:WindowedApplication>

要同步执行操作，请在使用 SQLConnection 实例打开数据库连接时，调用 open() 方法。以下示例说明如何创建和打开同步执行其操作的 SQLConnection 实例：

import flash.data.SQLConnection; 
import flash.errors.SQLError; 
import flash.filesystem.File; 
     
var conn:SQLConnection = new SQLConnection(); 
     
// The database file is in the application storage directory 
var folder:File = File.applicationStorageDirectory; 
var dbFile:File = folder.resolvePath("DBSample.db"); 
     
try 
{ 
    conn.open(dbFile); 
    trace("the database was created successfully"); 
} 
catch (error:SQLError) 
{ 
    trace("Error message:", error.message); 
    trace("Details:", error.details); 
} 

<?xml version="1.0" encoding="utf-8"?> 
<mx:WindowedApplication xmlns:mx="http://www.adobe.com/2006/mxml" creationComplete="init()"> 
    <mx:Script> 
        <![CDATA[ 
            import flash.data.SQLConnection; 
            import flash.errors.SQLError; 
            import flash.filesystem.File; 
             
            private function init():void 
            { 
                var conn:SQLConnection = new SQLConnection(); 
                 
                // The database file is in the application storage directory 
                var folder:File = File.applicationStorageDirectory; 
                var dbFile:File = folder.resolvePath("DBSample.db"); 
                  
                try 
                { 
                    conn.open(dbFile); 
                    trace("the database was created successfully"); 
                } 
                catch (error:SQLError) 
                { 
                    trace("Error message:", error.message); 
                    trace("Details:", error.details); 
                } 
            } 
        ]]> 
    </mx:Script> 
</mx:WindowedApplication>

在数据库中创建表包括使用与执行 SELECT 、 INSERT 等 SQL 语句相同的过程对该数据库 执行 SQL 语句 。要创建表，请使用 CREATE TABLE 语句，该语句包括新表的列和约束的定义。有关执行 SQL 语句的详细信息，请参阅 使用 SQL 语句 。

以下示例演示如何使用异步执行模式在现有数据库文件中创建一个名为“employees”的表。请注意，此代码假定存在一个名为 conn 的 SQLConnection 实例，并且该实例已经实例化并连接到数据库。

import flash.data.SQLConnection; 
import flash.data.SQLStatement; 
import flash.events.SQLErrorEvent; 
import flash.events.SQLEvent; 
     
// ... create and open the SQLConnection instance named conn ... 
     
var createStmt:SQLStatement = new SQLStatement(); 
createStmt.sqlConnection = conn; 
     
var sql:String =  
    "CREATE TABLE IF NOT EXISTS employees (" +  
    "    empId INTEGER PRIMARY KEY AUTOINCREMENT, " +  
    "    firstName TEXT, " +  
    "    lastName TEXT, " +  
    "    salary NUMERIC CHECK (salary > 0)" +  
    ")"; 
createStmt.text = sql; 
     
createStmt.addEventListener(SQLEvent.RESULT, createResult); 
createStmt.addEventListener(SQLErrorEvent.ERROR, createError); 
     
createStmt.execute(); 
     
function createResult(event:SQLEvent):void 
{ 
    trace("Table created"); 
} 
     
function createError(event:SQLErrorEvent):void 
{ 
    trace("Error message:", event.error.message); 
    trace("Details:", event.error.details); 
}

<?xml version="1.0" encoding="utf-8"?> 
<mx:WindowedApplication xmlns:mx="http://www.adobe.com/2006/mxml" creationComplete="init()"> 
    <mx:Script> 
        <![CDATA[ 
            import flash.data.SQLConnection; 
            import flash.data.SQLStatement; 
            import flash.events.SQLErrorEvent; 
            import flash.events.SQLEvent; 
             
            private function init():void 
            { 
                // ... create and open the SQLConnection instance named conn ... 
                 
                var createStmt:SQLStatement = new SQLStatement(); 
                createStmt.sqlConnection = conn; 
                 
                var sql:String =  
                    "CREATE TABLE IF NOT EXISTS employees (" +  
                    "    empId INTEGER PRIMARY KEY AUTOINCREMENT, " +  
                    "    firstName TEXT, " +  
                    "    lastName TEXT, " +  
                    "    salary NUMERIC CHECK (salary > 0)" +  
                    ")"; 
                createStmt.text = sql; 
                 
                createStmt.addEventListener(SQLEvent.RESULT, createResult); 
                createStmt.addEventListener(SQLErrorEvent.ERROR, createError); 
                 
                createStmt.execute(); 
            } 
             
            private function createResult(event:SQLEvent):void 
            { 
                trace("Table created"); 
            } 
             
            private function createError(event:SQLErrorEvent):void 
            { 
                trace("Error message:", event.error.message); 
                trace("Details:", event.error.details); 
            } 
        ]]> 
    </mx:Script> 
</mx:WindowedApplication>

以下示例演示如何使用同步执行模式在现有数据库文件中创建一个名为“employees”的表。请注意，此代码假定存在一个名为 conn 的 SQLConnection 实例，并且该实例已经实例化并连接到数据库。

import flash.data.SQLConnection; 
import flash.data.SQLStatement; 
import flash.errors.SQLError; 
     
// ... create and open the SQLConnection instance named conn ... 
     
var createStmt:SQLStatement = new SQLStatement(); 
createStmt.sqlConnection = conn; 
     
var sql:String =  
    "CREATE TABLE IF NOT EXISTS employees (" +  
    "    empId INTEGER PRIMARY KEY AUTOINCREMENT, " +  
    "    firstName TEXT, " +  
    "    lastName TEXT, " +  
    "    salary NUMERIC CHECK (salary > 0)" +  
    ")"; 
createStmt.text = sql; 
     
try 
{ 
    createStmt.execute(); 
    trace("Table created"); 
} 
catch (error:SQLError) 
{ 
    trace("Error message:", error.message); 
    trace("Details:", error.details); 
} 

<?xml version="1.0" encoding="utf-8"?> 
<mx:WindowedApplication xmlns:mx="http://www.adobe.com/2006/mxml" creationComplete="init()"> 
    <mx:Script> 
        <![CDATA[ 
            import flash.data.SQLConnection; 
            import flash.data.SQLStatement; 
            import flash.errors.SQLError; 
             
            private function init():void 
            { 
                // ... create and open the SQLConnection instance named conn ... 
                 
                var createStmt:SQLStatement = new SQLStatement(); 
                createStmt.sqlConnection = conn; 
                 
                var sql:String =  
                    "CREATE TABLE IF NOT EXISTS employees (" +  
                    "    empId INTEGER PRIMARY KEY AUTOINCREMENT, " +  
                    "    firstName TEXT, " +  
                    "    lastName TEXT, " +  
                    "    salary NUMERIC CHECK (salary > 0)" +  
                    ")"; 
                createStmt.text = sql; 
                 
                try 
                { 
                    createStmt.execute(); 
                    trace("Table created"); 
                } 
                catch (error:SQLError) 
                { 
                    trace("Error message:", error.message); 
                    trace("Details:", error.details); 
                } 
            } 
        ]]> 
    </mx:Script> 
</mx:WindowedApplication>