Create SQL Queries Using Database Explorer App

Using theDatabase Explorerapp, you can open one or multiple database connections simultaneously by clickingConnectin the toolstrip. The Database Explorer app creates a data source tab for each SQL query.

On each data source tab, you can write an SQL query in one of two ways. If you are unfamiliar with the SQL query language or want to explore data in your database, then use theData Browserpane along with the buttons in the toolstrip. Or, if you are already familiar with SQL, then enter an SQL query manually. When you enter a query, you can use more advanced SQL statements (for example,AS,GROUP BY,HAVING). You can also enter SQL code that is proprietary to the database and does not comply with the ANSI standard.

Create SQL Query Using Toolstrip Buttons

Use these steps as a general workflow for creating an SQL query by using buttons in the toolstrip.

Connect to a data source by using the Database Explorer app. For an example, seeMySQL ODBC for Windows。
Click a table in theData Browserpane. TheSQL Querypane in the data source tab displays an SQL query that selects all columns and rows of the table. TheData Previewpane shows a preview of the first 10 rows of data in the table. For example, connect to aMicrosoft^®Access™database and select theinventorytabledatabase table.
In theJoinsection of theDatabase Explorertab, clickJointo display theJointab in the toolstrip. In theAddsection of theJoin选项卡中,选择的表的名称theData Browserpane appears in the leftTablelist.
- From the leftColumnlist, select the name of the shared column.
- From the rightTablelist, select the name of the table to join.
- From the rightColumnlist, select the name of the shared column.
- Click添加连接。The app creates an inner join by default.
- Close theJointab.
For details about joining tables, seeJoin Tables Using Database Explorer App。
In theData Browserpane, expand the table name node of the joined table and select specific check boxes to choose the table columns. TheSQL QueryandData Previewpanes display the specified columns.
In theCriteriasection, clickWhereto display theWheretab in the toolstrip. In theAddsection, select an operator and value to enter an SQLWHEREcondition. ClickAdd Filter。To represent strings in values, enclose text in single quotes. Close theWheretab.
In theCriteriasection, clickOrder Byto display theOrder Bytab in the toolstrip. In theAddsection, select the column to sort and clickAdd Sort。Close theOrder Bytab.
In theImportsection, clickto import all SQL query results into the MATLAB^®Workspace as a table.
In the查询section, clickClear Queryto clear the current SQL query and create a new one.
In theConnectionssection, close the database connection by clickingClose Connection。

Note

If multiple connections are open, close the database connection of your choice by selecting the corresponding data source from theClose Connectionlist.

For detailed examples, see theDatabase Explorer应用程序。

Enter SQL Query Manually

Use these steps as a general workflow for entering an SQL query manually.

Connect to a data source in the Database Explorer app. For an example, seeMySQL ODBC for Windows。
In the查询section, clickManualto open a new data source tab. The tab has the same data source name as the prior active tab, but the Database Explorer app appends the suffix_manualto the tab name. The manual data source tab keeps the same database connection as the prior active tab.
Enter or paste an SQL query into theSQL Querypane.

Note

If you clickManualwhen the active data source tab contains an SQL query in theSQL Querypane, then you can modify the existing SQL query manually. Or, you can clickClear Queryto clear the existing SQL query in the new tab and enter a new query.

For each subsequent time you clickManual, the new tab contains a numbered suffix.
In thePreviewsection, clickPreview Query。The Database Explorer app executes the SQL query and updates theData Previewpane with the results. If the SQL query is valid, theData Previewpane displays the first 10 rows of data by default. To see more rows, adjust the value in thePreview Sizebox.
Modify the SQL query and clickPreview Query。TheData Previewpane shows the updated results.
In theImportsection, clickto import all SQL query results into the MATLAB Workspace as a table.
In theConnectionssection, close the database connection by clickingClose Connection。

Note

If multiple connections are open, close the database connection of your choice by selecting the corresponding data source from theClose Connectionlist.

For a detailed example of entering an SQL query manually, see theDatabase Explorer应用程序。

Work with Multiple SQL Queries

To create different SQL queries using the same database, follow these steps:

In theConnectionssection of theDatabase Explorertab, clickConnectand select the data source to create a database connection.
In the connection dialog box, enter the user name and password for your database, and clickConnect。
In the Catalog and Schema dialog box, select the catalog and schema, and clickOK。If only one catalog or schema is available in the database, the Catalog and Schema dialog box does not open.
The Database Explorer app opens a new tab with the data source name as the tab name.
To create a different SQL query using the same database connection, clickNew Queryin theConnectionssection.

The app opens a new data source tab and appends a numbered suffix to the tab name. The number increases by one in each subsequent data source tab name.

You can also create SQL queries in different catalogs or schemas. Repeat these steps and select a different catalog or schema in the Catalog and Schema dialog box.

To connect to a different database, repeat these steps and select a different data source from theConnectlist.

SQL Query Limitations

The Database Explorer app has these limitations, which you can avoid by using the command line instead.

You can connect to relational databases only.
You can enter a single SQLSELECTstatement only. You cannot enter other SQL statements or multiple SQL statements in theSQL Querypane.
You cannot modify the database structure or the database data.
You cannot execute stored procedures.

External Websites

SQL Tutorial