matlab-提交、修改数据库

>> myconn=database('students','mytest','deepfuture','com.mysql.jdbc.Driver','jdbc:mysql://localhost:3306/students')
 
myconn =
 
       Instance: 'students'
       UserName: 'mytest'
         Driver: 'com.mysql.jdbc.Driver'
            URL: 'jdbc:mysql://localhost:3306/students'
    Constructor: [1x1 com.mathworks.toolbox.database.databaseConnect]
        Message: []
         Handle: [1x1 com.mysql.jdbc.JDBC4Connection]
        TimeOut: 0
     AutoCommit: 'on'
           Type: 'Database Object'

>> mycurs=exec(myconn,'select * from student')
 
mycurs =
 
        Attributes: []
              Data: 0
    DatabaseObject: [1x1 database]
          RowLimit: 0
          SQLQuery: 'select * from student'
           Message: []
              Type: 'Database Cursor Object'
         ResultSet: [1x1 com.mysql.jdbc.JDBC4ResultSet]
            Cursor: [1x1 com.mathworks.toolbox.database.sqlExec]
         Statement: [1x1 com.mysql.jdbc.StatementImpl]
             Fetch: 0

>> myres=fetch(mycurs)
 
myres =
 
        Attributes: []
              Data: {3x3 cell}
    DatabaseObject: [1x1 database]
          RowLimit: 0
          SQLQuery: 'select * from student'
           Message: []
              Type: 'Database Cursor Object'
         ResultSet: [1x1 com.mysql.jdbc.JDBC4ResultSet]
            Cursor: [1x1 com.mathworks.toolbox.database.sqlExec]
         Statement: [1x1 com.mysql.jdbc.StatementImpl]
             Fetch: [1x1 com.mathworks.toolbox.database.fetchTheData]

>> myres
 
myres =
 
        Attributes: []
              Data: {3x3 cell}
    DatabaseObject: [1x1 database]
          RowLimit: 0
          SQLQuery: 'select * from student'
           Message: []
              Type: 'Database Cursor Object'
         ResultSet: [1x1 com.mysql.jdbc.JDBC4ResultSet]
            Cursor: [1x1 com.mathworks.toolbox.database.sqlExec]
         Statement: [1x1 com.mysql.jdbc.StatementImpl]
             Fetch: [1x1 com.mathworks.toolbox.database.fetchTheData]

>> myres.Data

ans =

    '张三'    [25]    '1班'
    '李四'    [21]    '2班'
    '王五'    [23]    '1班'

 

get得到对象属性

>> v=get(myconn)

v =

              AutoCommit: 'on'
                 Catalog: 'students'
             Constructor: [1x1 com.mathworks.toolbox.database.databaseConnect]
                  Driver: 'com.mysql.jdbc.Driver'
                  Handle: [1x1 com.mysql.jdbc.JDBC4Connection]
                Instance: 'students'
                 Message: []
                ReadOnly: 0
                 TimeOut: 0
    TransactionIsolation: 4
                    Type: 'Database Object'
                     URL: 'jdbc:mysql://localhost:3306/students'
                UserName: 'mytest'
                Warnings: []

>>

 

>> get(myconn,'AutoCommit')

ans =

on

>> get(myres,'Data')

ans =

    '张三'    [25]    '1班'
    '李四'    [21]    '2班'
    '王五'    [23]    '1班'

>>

插入数据

>> colnames={'name','age','class'}

colnames =

    'name'    'age'    'class'

>> insert(myconn,'student',colnames,{'李小明',22,'3班'})
>> help insert
 --- help for database/insert ---

 INSERT Export MATLAB cell array data into database table.
    INSERT(CONNECT,TABLENAME,FIELDNAMES,DATA).
    CONNECT is a database connection handle structure, FIELDNAMES
    is a string array of database column names, TABLENAME is the
    database table, DATA is a MATLAB cell array.    For improved
    write performance and support for additional data types, use the
    function FASTINSERT.
 
 
    Example:
 
 
    The following INSERT command inserts the contents of
    the cell array in to the database table yearlySales
    for the columns defined in the cell array colNames.
 
 
    insert(conn,'yearlySales',colNames,monthlyTotals);
 
    where
 
    The cell array colNames contains the value:
 
    colNames = {'salesTotal'};
 
    monthlyTotals is a cell array containing the data to be
    inserted into the database table yearlySales
   
    insert(conn,'yearlySales',colNames,monthlyTotals);

 

设置连接，游标，或drivermanager对象的属性值，使用set

如

>> set(myconn,'AutoCommit','on')
>> set(myconn,'AutoCommit','off')

提交更改为commit,撤销更改为rollback,要求连接的AutoCommit属性值为off

>> myconn
 
myconn =
 
       Instance: 'students'
       UserName: 'mytest'
         Driver: 'com.mysql.jdbc.Driver'
            URL: 'jdbc:mysql://localhost:3306/students'
    Constructor: [1x1 com.mathworks.toolbox.database.databaseConnect]
        Message: []
         Handle: [1x1 com.mysql.jdbc.JDBC4Connection]
        TimeOut: 0
     AutoCommit: 'off'
           Type: 'Database Object'

>> set(myconn,'AutoCommit','on')
>> set(myconn,'AutoCommit','off')

 

>> insert(myconn,'student',colnames,{'JULY FENSE',23,'1-1'})
>> commit(myconn)
>> insert(myconn,'student',colnames,{'JAKE',22,'1-2'})
>> rollback(myconn)
>>

 

更新使用update

>> update(myconn,'student',{'age'},20,'where name=''JAKE''')
>> commit(myconn)

 

详细如下：

update(conn, 'tab', colnames, exdata, 'whereclause')
update(conn, 'tab', colnames, ...
{datA,datAA, ...; datB,datBB, ...; datn, datNN}, ...
{'where col1 = val1'; where col2 = val2'; ... 'where  coln = valn'}

Description

update(conn, 'tab', colnames, exdata, 'whereclause') exports the MATLAB variable exdata in its current format into the database table tab using the database connection conn.exdata can be a cell array, numeric matrix, or structure. Existing records in the database table are replaced as specified by the SQL whereclause command.

Specify column names for tab as strings in the MATLAB cell array colnames. If exdata is a structure, field names in the structure must exactly match field names in colnames.

The status of the AutoCommit flag determines whether update automatically commits the data to the database. View the AutoCommit flag status for the connection using get and change it using set. Commit the data by running commit or a SQL commit statement via the exec function. Roll back the data by runningrollback or a SQL rollback statement via the exec function.

To add new rows instead of replacing existing data, use fastinsert.

update(conn, 'tab', colnames, {datA, datAA, ...; datB, datBB, ...; datn,datNN}, {'where col1 = val1'; where col2 = val2'; ... 'where coln = valn'}) exports multiple records for n where clauses. The number of records in exdata must equal n.
Remarks

The order of records in your database is not constant. Use values of column names to identify records.

An error like the following may appear if your database table is open in edit mode:
[Vendor][ODBC Product Driver] The database engine could
not lock table 'TableName' because it is already in use
by another person or process.

 

In this case, close the table and repeat the update function.

An error like the following may appear if you try to run an update operation that is identical to one that you just ran:
??? Error using ==> database.update
Error:Commit/Rollback Problems

Examples

Example 1 — Update an Existing Record

Update the record in the Birthdays table using the database connection conn, where First_Name is Jean, replacing the current value for Age with 40.

First define a cell array containing the column name that you are updating, Age.
colnames = {'Age'}

 

Define a cell array containing the new data, 40.
exdata(1,1) = {40}

 

Run the update.
update(conn, 'Birthdays', colnames, exdata, ...
  'where First_Name = ''Jean''')

Example 2 — Roll Back Data after Updating a Record

Update the column Date in the Error_Rate table for the record selected by whereclause, using data contained in the cell array exdata. The AutoCommit flag is off. The data is rolled back after the update operation is run.

Set the AutoCommit flag to off for database connection conn.
set(conn, 'AutoCommit', 'off')

 

Update the Date column.
update(conn, 'Error_Rate', {'Date'}, exdata, whereclause)

 

Because the data was not committed, you can roll it back.
rollback(conn)

 

The update is reversed; the data in the table is the same as it was before you ran update.
Example 3 — Update Multiple Records with Different Constraints

Given the table TeamLeagues, where column names are 'Team', 'Zip_Code', and 'New_League':
'Team1'    02116
'Team2'    02138
'Team3'    02116

 

Assign teams with a zip code of 02116 to the A league and teams with a zip code of 02138 to the B league:
update(conn, 'TeamLeagues', {'League'}, {'A';'B'}, ...
{'where Zip_Code =''02116''';'where Zip_Code =''02138'''})

 

此外，exec也可以提交和回滚

>> exec(myconn,'rollback')
 
>> exec(myconn,'commit')