DB Toolbox连接Matlab和常用的数据库

DB Toolbox连接Matlab和常用的数据库数据库工具箱(DB Toolbox)让用户使用MATLAB强大数据分析和可视化工具功能处理存储在数据库中的数据的复杂分析。

在MATLAB环境下工作，可以用结构化查询语言（SQL）命令读，写数据库的数据,进行简单和高级条件的数据查询。

并且可以连接MATLAB和数据库包括Oracle,Sybase,Microsoft SQL Server和Informix。

可以用单一MATLAB同时访问多个数据库，进行大数据量的事务处理。

不懂SQL的用户可以用Visual Query Builder处理数据。

一、重点1）鲁棒接口能力。

用ODBC/JDBC连接数据库，包括Oracle,Sybase SQL Server,Sybase SQL Anywhere,Microsoft SQL Server, MicrosoftAccess,Informix Ingres.2）SQL语法。

在MATLAB环境直接执行SQL查询3）动态导入数据。

调节SQL查询，把数据导入MATLAB。

数据库工具箱满足小的和大的查询。

4）数据类型保存。

在数据导入和导出行为MATLAB保存数据类型5）同时访问多个数据库。

从数据库导入数据，对该数据执行计算，然后导入到另一个数据库。

6）处理大数据集的能力。

7）连续状态的数据库连接：一旦和某个数据库的连接建立后，数据库一直是打开的，除非在MATLAB中执行关闭语句。

这提高了数据库的读取速度，减少了不必要的命令来调入，输出数据。

8）Visual Query Builder.该图形用户接口列数据源和所有表和字段，不懂SQL的用户可以访问和查询数据库二、函数列表2.1数据库访问函数2.1.1 close 关闭数据库连接CLOSE Close database connection.CLOSE(CONNECT) closes the database connection.CONNECT is a database connection object returned by DATABASE.2.1.2 commit 数据库改变参数COMMIT Make database changes permanent.COMMIT(C) makes all changes since the previous commit or rollback permanent and releases connection's database locks. C is the database connection.2.1.3 database 连接数据库DATABASE Connect to database.CONNECT = DATABASE(INSTANCE,USERNAME,PASSWORD,DRIVER,DATABASEURL)returns a database connection object. INSTANCE is the name of the database, USERNAME is the user name. PASSWORD is the password that permits access to the database. DRIVER is a JDBC driver name and DATABASEURL is the URL for the database. The latter arguments are optional but both must be used in conjunction with each other.Example:JDBC connection:conn=database('oracle','scott','tiger','oracle.jdbc.driver.OracleDriver','j dbc:oracle:oci7:')where:'oracle' is the database name.'scott' is the user name.'tiger' is the password.'oracle.jdbc.driver.OracleDriver' is the JDBC driver to be used to make the connection.'jdbc:oracle:oci7:' is the URL as defined by the Driver vendor to establish a connection with the database.2.1.4 exec 执行SQL语句和打开油标EXEC Execute SQL statement and open CursorCURSOR = EXEC(CONNECT,SQLQUERY,QTIMEOUT) returns a cursor object.CONNECT is a database object returned by DATABASE. sqlQuery is a valid SQL statement. Use FETCH to retrieve data associatedwith CURSOR.Example:cursor = exec(connect,'select * from emp')where:connect is a valid database object.'select * from emp' is a valid SQL statement that selects all columns from the emp table.2.1.5 get 得到数据库属性VALUE = GET(HANDLE, PROPERTY) will return the VALUE of the PROPERTYspecified for the given HANDLE to a Database object.VALUE = GET(HANDLE) returns VALUE as a structure containing all theproperty values of the Database object.2.1.6 insert 导出MATLAB单元数组数据到数据库表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);whereThe 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);2.1.7 isconnection 判断数据库连接是否有效ISCONNECTION Detect if database connection is valid.X = ISCONNECTION(C) returns 1 if C is a valid database connection and 0 otherwise.2.1.8isreadonly 判断数据库连接是否只读2.1.9 ping 得到数据库连接信息PING Get status information about database connection.P = PING(CONNECT) determines the status of a database connection.CONNECT is a database connection object. If the connection is valid status information for a database connection is returned.If the connection is invalid then an error message is returned.Example:p = ping(connect)where connect is a database connection.This function returns the following information for an ODBC connection.For a Microsoft Access database:p =DatabaseProductName: ACCESSDatabaseProductVersion: 3.5 JetJDBCDriverName: JDBC-ODBC Bridge (odbcjt32.dll)JDBCDriverVersion: 1.1001 (03.50.3428.00)MaxDatabaseConnections: 64CurrentUserName: adminDatabaseURL: jdbc:odbc:ralphAutoCommitTransactions: TrueThis function returns the following information for a JDBC connection.For an ORACLE database: p =DatabaseProductName: OracleDatabaseProductVersion: Oracle7 Server Release 7.3.3.0.0 - Production Release With the distributed, replication and parallel query options PL/SQL Release 2.3.3.0.0- Production JDBCDriverName: Oracle JDBC driverJDBCDriverVersion: 7.3.3.1.3MaxDatabaseConnections: 0CurrentUserName: scottDatabaseURL: jdbc:oracle:oci7:oracleAutoCommitTransactions: TrueThe information returned details the type of database, connection type anddriver used, username etc.2.1.10 rollback 撤销数据库变化2.1.11set 设置数据库连接属性SET Set properties for database connection.SET(H, 'PROPERTY', 'VALUE') sets the VALUE of the given PROPERTY for theDatabase object, H. SET(H) returns the list of properties that can be modified.2.1.12sql2native 转换JDBC SQL 语法为系统本身的SQL语法SQL2NATIVE Convert JDBC SQL grammar into system's native SQL grammar.N = SQL2NATIVE(C,S) converts the SQL statement string, S, intothe system's native SQL grammar. The native SQL statement is returned.C is the database connection.2.1.13 update 用MATLAB单元数组数据代替数据库表的数据UPDATE Replace data in database table with data from MATLAB cell array.UPDATE(CONNECT,TABLENAME,FIELDNAMES,DATA,WHERECLAUSE).CONNECT is a database connection object, FIELDNAMES is a string array of database column names, TABLENAME is the database table, DATA is a MATLAB cell array and WHERECLAUSE is a SQL where clause used to specify the row(s) to be updated. If WHERECLAUSE is a cell array of SQL where clauses, there should be one record of data for each entry in WHERECLAUSE. Examples:The following UPDATE command updates the contents of the cell array in to the database table yearlySales for the columns defined in the cell array colNames.update(conn,'yearlySales',colNames,monthlyTotals,whereClause);where The cell array colNames contains the value:colNames = {'salesTotal'};monthlyTotals is a cell array containing the data to beused in updating the appropriate rows of the database table yearlySales.whereClause is a string that contains the where condition that must be satisfied for a row in the table to be updated.whereClause = 'where month = ''Nov'''is an example of a valid whereClause.update(conn,'yearlySales',colNames,monthlyTotals,whereClause);To specify multiple constraints, use the following syntax:update(conn,'Table1',{'FloatColumn','realfloatcolumn'},{4,7.8;5,33.5},{'where One = true';'where One = false'});The columns FloatColumn and realfloatcolumn will be updated accordinglydepending on the value of the column One in the corresponding record.2.2数据库游标访问函数2.2.1attr 获得的数据集的列属性ATTR Get attributes of columns in fetched data set.ATTRIBUTES = ATTR(CURSOR,COLUMN) returns the attributes of a single table column, defined by the input argument COLUMN, or all the table columns, if the COLUMN argument is not defined, in the data set set described by the fetch and resultSet elements of the input argument CURSOR. Example:To return the attributes for all columns/fields in the SQLselect statement the sql attr command is used as follows:columnAtts = attr(cursor)This will return a struct array with the following structure elements:fieldName - Database column/field name.typeName - String indicating the data type.typeValue - Numeric value for indicating the data type.columnWidth - Width of database column/field.precision - Precision value for float and double data types. Empty value returned for strings.scale - Precision value for real and numeric data types. Empty value returned for strings.currency - Flag to indicate whether the column/field is a currency column/field.readOnly - Flag to indicate whether column/field is read only.nullable - Flag to indicate whether column/field can contain a null.To access an element of the array and view the elements type columnAtts(1) and the following would be displayed.fieldName: 'EmployeeID'typeName: 'LONG'typeValue: 4columnWidth: 11precision: []scale: []currency: 'false'readOnly: 'true'nullable: 'false'To return the attributes for a single column/field in the SQL .select statement the ATTR command is used as follows:columnAtts = attr(cursor,1)To view the data type columnAtts and the following information would be displayed.fieldName: 'EmployeeID'typeName: 'LONG'typeValue: 4columnWidth: 11precision: []scale: []currency: 'false'readOnly: 'true'nullable: 'false'2.2.2close 关闭游标CLOSE(CURSOR) closes the database cursor.CURSOR is a cursor structure with all elements defined.2.2.3cols 获得的数据集的列数值COLS Get number of columns in fetched data set.COLUMNS = COLS(CURSOR) returns the number of columns in a database table. CURSOR is a cursor structure in which all elements have values.Example:columns = cols(cursor)Function returns the number of columns selected in the SQL query to columns.For example, issuing the SQL query, against the Microsoft Access database Northwind.'select * from employees'returns all columns from the database table employees.Invoking the sqlcols command as follows:columns = cols(cursor)returns the following value to the variable columns:columns = 17indicating there are 17 fields in cursor.2.2.4columnnames 获得的数据集的列名称2.2.5fetch 导入数据到MATLAB单元数组FETCH Import data into MATLAB using connection handle.X = FETCH(CONN,SQLSTRING,ROWINC) imports database data into MATLAB given the connection handle, CONN, the SQL string, SQLSTRING.Optionally, a row increment can be specified to retrieve the data in ROWINC records at a time.For example:x = fetch(conn,'select * from tablename') will return the requested data as a cell array, numeric matrix orstructure depending on the setting of the DataReturnFormat preference.2.2.6get 得到游标对象属性GET Get property of cursor object.VALUE = GET(HANDLE, PROPERTY) will return the VALUE of the PROPERTY specified for the given HANDLE to a Cursor object.VALUE = GET(HANDLE) returns a structure where each field name is the name of a property of HANDLE and each field contains the value of that property.2.2.7querytimeout 数据库SQL查询成功的时间QUERYTIMEOUT Get time allowed for a database SQL query to succeed.TIMEOUT = QUERYTIMEOUT(CURSOR) returns the current value of time allowed for a database query to be successful. CURSOR is a cursor object.Example:time = querytimeout(cursor)where cursor is a valid database cursor.time will contain the current value for timing out on a SQL query.2.2.8rows 获取数据集的行数ROWS Get number of rows in fetched data set.NROWS = ROWS(CURSOR) returns the number of rows retrieved by a database fetch operation. CURSOR is a cursor structure in which all elements have values.Example:nrows = rows(cursor)upon execution of the function nrows contains the the number of rows returned by the fetch. cursor=exec(conn,'select * from employees');cursor=fetch(cursor);nrows=rows(cursor)nrows = 9indicating there are 9 rows returned by FETCH.2.2.9set 设置游标获取的行限制SET Set RowLimit for cursor fetch.SET(H, 'PROPERTY', 'VALUE') sets the VALUE of the given PROPERTY for the Database Cursor object, H.S = SET(H) returns the list of valid properties.2.2.10width 获取数据集的列宽WIDTH Get field size of column in fetched data set.WTH = WIDTH(CURSOR,COLUMNNUMBER) returns the width of a specific column in database table. CURSOR is a cursor object and COLUMNNUMBER is an integer that specifies the column. Example:cursor=exec(conn,'select * from employees');cursor=fetch(cursor);colwidth=width(cursor,3)MATLAB returns:colwidth = 10indicating the width of column 3 is 12 bytes.2.3数据库元数据函数2.3.1bestrowid 得到数据库表唯一行标识BESTROWID Get database table unique row identifier.B = BESTROWID(D,C,S,T) returns the set of table columns that uniquely identifies a row. D is the database metadata object,C is the catalog, S is the schema, and T is the table.B = BESTROWID(D,C,S) returns the unique row identifier for all tables associated with the given catalog and schema.2.3.2columnprivileges 得到数据库列优先权COLUMNPRIVILEGES Get database column privileges.P = COLUMNPRIVILEGES(D,C,S,T,L) returns the privileges for the columns associatedwith the database metadata D, the catalog C, the schema S, the table T, and the column L.P = COLUMNPRIVILEGES(D,C,S,T) returns the privileges for all columns for the given catalog, schema, and table.2.3.3columns 得到数据库表列名称COLUMNS Get database table column names.P = COLUMNS(D,C,S,T) returns the columns for the given database metadata D, the catalog C, the schema S, and the table T.P = COLUMNS(D,C,S) returns the columns for all tables for the givencatalog and schema.P = COLUMNS(D,C) returns all columns for all tables of all schemas for the given catalog.2.3.4crossreference 得到主健和外健信息CROSSREFERENCE Get information about primary and foreign keys.E = CROSSREFERENCE(D,PC,PS,PT,FC,FS,FT) returns the foreign and primary key information for a given database metadata object D, primary catalog PC,primary schema PS, primary table PT, foreign catalog FC, foreign schema FS, and foreign table FT.2.3.5dmd 创建数据库元数据对象DMD Construct database metadata object.D = DMD(C) constructs a database metadata object. The metadata object is used to call methods that return the properties of the database connection's metadata. C is a database connection object.2.3.6exportedkeys 得到导出外部健信息EXPORTEDKEYS Get information about exported foreign keys.E = EXPORTEDKEYS(D,C,S,T) returns the exported key information for a given database metadata object D, catalog C, schema S, and table T.E = EXPORTEDKEYS(D,C,S) returns the exported key information for all tables associated with the database metadata object D, catalog C, and schema S.2.3.7get 得到数据库元数据属性GET Get database metadata properties.V = GET(D,'PropertyName') returns the value of the specified properties for the database meta data object, D. 'PropertyName' is a string or cell array of strings containing property names.V = GET(D) returns a structure where each field name is the nameof a property of D and each field contains the value of that property2.3.8importedkeys 得到导入外健信息IMPORTEDKEYS Get information about imported foreign keys.E = IMPORTEDKEYS(D,C,S,T) returns the imported key information for a given database metadata object D, catalog C, schema S, and table T.E = IMPORTEDKEYS(D,C,S) returns the imported key information for all tables associated with the database metadata object D, catalog C, and schema S.2.3.9indexinfo 得到数据库表的索引和统计INDEXINFO Get indices and statistics for database table or schema.N = INDEXINFO(D,C,S,T) returns the table indices and statistics for the database metadata object D, catalog C, schema S, and table T.2.3.10primarykeys 从数据库表或结构得到主健信息PRIMARYKEYS Get primary key information for database table or schema（结构）.E = PRIMARYKEYS(D,C,S,T) returns the primary key information for a given database metadata object D, catalog C, schema S, and table T.E = PRIMARYKEYS(D,C,S) returns the primary key information for all tables associated with the database metadata object D, catalog C, and schema S.2.3.11procedurecolumns 得到目录存储程序参数和结果列PROCEDURECOLUMNS Get catalog's stored procedure parameters and result columns.P = PROCEDURECOLUMNS(D,C,S) returns stored procedure parameter information for the given database metadata D, catalog C, and schema S.P = PROCEDURECOLUMNS(D,C) returns stored procedure parameter information for all schemas of the given catalog.2.3.12procedures 得到目录存储程序PROCEDURES Get catalog's stored procedures.P = PROCEDURES(D,C,S) returns stored procedure information for the given database metadata D, catalog C, and schema S.P = PROCEDURES(D,C) returns stored procedure information for all schemas of the given catalog.2.3.13supports 判断是否支持数据库元数据SUPPORTS Detect if property is supported by database metadata object.V = SUPPORTS(D,'PropertyName') returns 1 if the specified properties for the database meta data object, D, are supported and0 otherwise. 'PropertyName' is a string or cell array of strings containing property names.V = SUPPORTS(D) returns a structure where each field name is the name of a property of D and each field contains a 1 or 0 for a supported or unsupported property, respectively.2.3.14tableprivileges 得到数据库表优先权TABLEPRIVILEGES Get database table privileges.P = TABLEPRIVILEGES(D,C,S,T) returns the privileges for the tables associated with the database metadata D, the catalog C, the schema S, and the table T.P = TABLEPRIVILEGES(D,C,S) returns the privileges for all tables for the given catalog and schema. P = TABLEPRIVILEGES(D,C) returns the privileges for all tables of all schemas of the given catalog.2.3.15tables 得到数据库表名称TABLES Get database table names.T = TABLES(D,C,S) returns the list of tables and table types for the database with the catalog name C and schema name S. D is the database metadata object resulting from a database connection object.T = TABLES(D,C) returns all tables and table types for all schemas of the given catalog.2.3.16versioncolumns 得到自动更新表列VERSIONCOLUMNS Get automatically updated table columns.P = VERSIONCOLUMNS(D,C,S,T) returns the table's columns that are automatically updated when any row value is updated given the database metadata object, catalog C, schema S and table T.P = VERSIONCOLUMNS(D,C,S) returns the information for all tables associated with the given catalog and schema.P = VERSIONCOLUMNS(D,C) returns the information for all tables of all schemas of the given catalog.2.4 数据库工具箱驱动器函数2.4.1driver 创建数据库驱动器对象DRIVER Construct database driver object.D = DRIVER(S) constructs a database driver object from variableS which is a database URL string of the form jdbc:odbc:<name> or <name>. The first driver which recognizes the given database URL is returned.2.4.2 get 获得数据库驱动器属性GET Get database driver properties.V = GET(D,'PropertyName') returns the value of the specified properties for the database driver object, D. 'PropertyName' isa string or cell array of strings containing property names.V = GET(D) returns a structure where each field name is the nameof a property of D and each field contains the value of that property.2.4.3 isdriver 判断驱动器是否为有效的JDBC驱动对象ISDRIVER Detect if driver is a valid JDBC driver object.X = ISDRIVER(D) returns 1 if D is a valid JDBC driver object.2.4.4 isURl 判断数据库URL是否是有效的ISURL Detect if the database URL is valid.X = ISURL(D,S) returns 1 if S is a valid database URL for the given driver, D. The given URL, of the form 'jdbc:odbc:<name>' for example, is tested against the given database driver object D.2.4.5 isJDBC 判断驱动器是否为JDBCISJDBC Detect if driver is JDBC-compliant.X = ISJDBC(D) returns 1 if D is JDBC compliant.2.4.6 register 加载数据库驱动器REGISTER Load database driver.REGISTER(D) loads the database driver D.2.5 数据库工具箱结果设置函数2.5.1 clearwarnings取消结果设置警报CLEARWARNINGS Clear the warnings for the resultset.CLEARWARNINGS(R) clears the warnings reported for the resultset R.2.5.2close 关闭结果显示对象CLOSE Close resultset object.CLOSE(R) closes the resultset object, R, and frees its associated resources.2.5.3get 获得结果设置对象的属性GET Get resultset properties.V = GET(D,'PropertyName',N) returns the value of the specified properties for the resultset object, D. 'PropertyName'is a string or cell array of strings containing property names. N is the column number or name for which the properties are returned.V = GET(D,N) returns a structure where each field name is the nameof a property of D and each field contains the value of that property for the column index or name N. All properties are returned for the given columns.V = GET(D) returns all properties for all columns.2.5.4 isnullcolumn 判断结果设置的最后记录是否为空ISNULLCOLUMN Detect if last record read in resultset was null.X = ISNULLCOLUMN(R) returns 1 if the last record of the resultset,R, read was null and 0 otherwise.2.5.5 namecolumn 获得结果设置对象列的列名NAMECOLUMN Map resultset column name to resultset column index.X = NAMECOLUMN(R,N) maps a resultset column name, N, to a resultset column index. R is the resultset. N is a cell array of strings.2.5.6 resultset 创建结果设置对象RESULTSET Construct resultset object.R = RESULTSET(C) returns a resultset object given a cursor object,C.2.6 可视化SQl查询建立函数（Visual Query Builder functions:）2.6.1 getdatasources 获得系统有效数据源GETDATASOURCES Return valid data sources on system.D = GETDATASOURCES returns the valid data sources on the system.If D is empty, the ODBC.INI file is valid but no data sources have been defined. If D equals -1, the ODBC.INI file could not be opened.D is returned as a cell array of strings containing the data source namesif any are present on the system.2.6.2 loginconnect 数据源连接LOGINCONNECT Datasource connection.LOGINCONNECT(DATASOURCE) Prompts for the datasource username and password.2.6.3querybuilder建立可视化SQl查询窗口QUERYBUILDER Start visual SQL query builder.2.6.4 showdata 在交互窗口中显示数据SHOWDATA Display data in interactive window.SHOWDATA(A,F,T) displays the given cell array of data, A, in an interactive figure window.The unique entries for each column of data are displayed.Clicking on a item in the figure displays information regarding the number of matching items found and additional correlation data. F is a cellarray of strings used as X axis labels.If F is not supplied, no X axislabels are displayed.T is the figure window title string. If T is not supplied, no title is displayed. SHOWDATA(A,F,T) displays the given cell array of data, A, in an interactive figure window. The unique entries for each column of data are displayed.Clicking on a item in the figure displays information regarding the numberof matching items found and additional correlation data.F is a cell array of strings used as X axis labels.If F is not supplied, no X axis labels are displayed.T is the figure window title string.If T is not supplied, no title is displayed.四、例子sourceName=input('Enter the source Name:','s'); %获取数据源的名称（dbtest）Timeout=logintimeout(5); %允许登录连接时间最长为5sconn=database(sourceName,'sa','123'); %获取数据库连接对象ping(conn) %测试数据库连接状态dbmeta=dmd(conn); %获取数据元对象t=tables(dbmeta,'tutorial'); %获取cata为tutorial的表名[trow,tcolumn]=size(t); %获取返回数组的大小index=1;for i=1:trow %由于表中既包含了系统表格if strcmp(t{i,2},'TABLE') %又包含了用户表格，需要在其中tablename{1,index}=t{i,1}; %找出用户表格，对t数组的每一行 index=index+1; %的第二个元素判断是table则为用end %户表。