Create SQL Queries Using Database Explorer App

Using the Database Explorer app, you can open one or multiple database connections simultaneously by clicking Connect in 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 the Data Browser pane 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, see MySQL ODBC for Windows.

• Click a table in the Data Browser pane. The SQL Query pane in the data source tab displays an SQL query that selects all columns and rows of the table. The Data Preview pane shows a preview of the first 10 rows of data in the table. For example, connect to a Microsoft Access^® database and select the inventorytable database table.

  

• In the Join section of the Database Explorer tab, click Join to display the Join tab in the toolstrip. In the Add section of the Join tab, the name of the table selected in the Data Browser pane appears in the left Table list.

  • From the left Column list, select the name of the shared column.

  • From the right Table list, select the name of the table to join.

  • From the right Column list, select the name of the shared column.

  • Click Add Join. The app creates an inner join by default.

  • Close the Join tab.

  For details about joining tables, see Join Tables Using Database Explorer App.

• In the Data Browser pane, expand the table name node of the joined table and select specific check boxes to choose the table columns. The SQL Query and Data Preview panes display the specified columns.

• In the Criteria section, click Where to display the Where tab in the toolstrip. In the Add section, select an operator and value to enter an SQL WHERE condition. Click Add Filter. To represent strings in values, enclose text in single quotes. Close the Where tab.

• In the Criteria section, click Order By to display the Order By tab in the toolstrip. In the Add section, select the column to sort and click Add Sort. Close the Order By tab.

• In the Import section, click to import all SQL query results into the MATLAB^® Workspace as a table.

• In the Query section, click Clear Query to clear the current SQL query and create a new one.

• In the Connections section, close the database connection by clicking Close Connection.

  Note

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

For detailed examples, see the Database Explorer app.

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, see MySQL ODBC for Windows.

• In the Query section, click Manual to 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 _manual to 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 the SQL Query pane.

  Note

  If you click Manual when the active data source tab contains an SQL query in the SQL Query pane, then you can modify the existing SQL query manually. Or, you can click Clear Query to clear the existing SQL query in the new tab and enter a new query.

  For each subsequent time you click Manual, the new tab contains a numbered suffix.

• In the Preview section, click Preview Query. The Database Explorer app executes the SQL query and updates the Data Preview pane with the results. If the SQL query is valid, the Data Preview pane displays the first 10 rows of data by default. To see more rows, adjust the value in the Preview Size box.

• Modify the SQL query and click Preview Query. The Data Preview pane shows the updated results.

• In the Import section, click to import all SQL query results into the MATLAB Workspace as a table.

• In the Connections section, close the database connection by clicking Close Connection.

  Note

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

For a detailed example of entering an SQL query manually, see the Database Explorer app.

Work with Multiple SQL Queries

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

• In the Connections section of the Database Explorer tab, click Connect and select the data source to create a database connection.

• In the connection dialog box, enter the user name and password for your database, and click Connect.

• In the Catalog and Schema dialog box, select the catalog and schema, and click OK. 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, click New Query in the Connections section.

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 the Connect list.

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 SQL SELECT statement only. You cannot enter other SQL statements or multiple SQL statements in the SQL Query pane.

• You cannot modify the database structure or the database data.

• You cannot execute stored procedures.