configure, explore, and import database data -凯发k8网页登录
configure, explore, and import database data
description
the database explorer app lets you quickly connect to a database, explore the database data, and import data into the matlab® workspace in a visual way. if you have minimal proficiency writing sql queries or want to browse the data in your database quickly, use this app to interact with your database.
using the database explorer app, you can:
create and configure odbc and jdbc data sources.
establish multiple connections to the same or different databases.
select tables and columns of interest.
fine-tune selections using sql query criteria.
preview selected data.
customize import options.
import selected data into the matlab workspace for analysis.
save generated sql queries.
generate matlab code.
to watch an introductory video, see .
open the database explorer app
matlab toolstrip: on the apps tab, click the show more arrow to open the apps gallery. then, under database connectivity and reporting, click database explorer.
matlab command prompt: enter
databaseexplorer.
examples
preview rows in single table
connect to a microsoft access® database using the database explorer app. then, select columns from a single table and preview the data. the app previews query results by default.
set up the data source for the
tutorial.accdb database and name it dbdemo. for
details, see .
in the connections section of the database explorer tab, click connect and select the data source for the connection. the connection dialog box opens. leave the user name and password blank, and click connect.
note
for other databases, the catalog and schema dialog box opens. select the name of the catalog and schema from the catalog and schema lists, as appropriate for your database.
the database explorer app creates a connection to the microsoft access database. the database browser pane displays the available tables in the database.
the data source tab, which is named dbdemo, appears to the right of the database browser pane. the data source tab contains two empty panes, sql query and data preview.
for any table, you can select the table information in these ways:
to select tables, click the database table name in the database browser pane. the database explorer app updates the sql query pane with an sql query that selects all columns and rows from the chosen table. simultaneously, the database explorer app updates the data preview pane with a preview of the query results. the first 10 rows of data appear in the data preview pane by default.
to select individual columns from a selected table, expand the table name node in the database browser tree view. select specific check boxes to choose individual table columns and display them in the data preview pane. the sql query adjusts to each selection automatically.
note
the order of the columns in the data preview pane matches the order in which you select them in the database browser pane.
select the table name inventorytable.
to change the data you see, select or clear check boxes in the database browser pane. the app updates the sql query in the sql query pane. the app updates the data in the data preview pane.
the data preview pane displays 10 rows. the total
number of rows selected in the database appears, within parentheses, next to
the name of the pane, data preview. change the number
of rows by selecting or entering a value in the preview
size box in the preview section of the
database explorer tab. select the value
20. the number of rows adjusts in the data
preview pane.
note
the value in the preview size box controls the maximum number of rows displayed in the data preview pane. if this value is larger than the total number of rows in the query results, then the total number of rows is displayed, within parentheses, next to the name of the pane, data preview.
you can sort the rows of data by a specific column. in the criteria section, click order by. the order by tab is displayed in the toolstrip.
in the add section, in the
column list, select the column
price. in the add
section, click add sort. the database explorer app
sorts the data in ascending order in the data preview
pane. to change the order, click descending in the
edit section.
note
to add more sorts, select another column from the column list and click add sort. you can change the position of the sort in the sql query by clicking it in the list in the edit section, and then clicking move up or move down.
in the close section, click close order by to close the order by tab.
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.
join multiple tables and import query results
connect to a microsoft access database using the database explorer app. then, join data in multiple tables by selecting columns in the tables. the app previews query results by default. after previewing the data, import all query results into the matlab workspace and perform simple data analysis.
set up the data source for the
tutorial.accdb database and name it dbdemo. for
details, see .
in the connections section of the database explorer tab, click connect and select the data source for the connection. the connection dialog box opens. leave the user name and password blank, and click connect.
the database explorer app creates a connection to the microsoft access database. the database browser pane displays the available tables in the database.
the data source tab, which is named dbdemo, appears to the right of the database browser pane. the data source tab contains two empty panes, sql query and data preview.
in the database browser pane, select the inventorytable table as the first table for the join. the database explorer app updates the sql query pane with an sql query that selects all columns and rows from the inventorytable table. simultaneously, the app updates the data preview pane with a preview of the query results. the first 10 rows of data appear in the pane by default.
in the join section, click join to display the join tab in the toolstrip. in the add section, the name of the table selected in the database browser pane appears in the left table list. for details about joining tables, see .
in the left column list, select the name of the
shared column productnumber.
in the right table list, select the table
producttable as the table to join. select the
name of the shared column productnumber in this
table in the right column list.
in the add section, click add join. the join diagram pane displays a pictorial representation of the join between the selected tables. the sql query pane updates the sql query with the new join. the data preview pane reflects the results of the updated sql query.
the database explorer app selects the inner join by default.
note
some databases do not support all join types.
in the close section, click close join to close the join tab.
in the tree view of the database browser pane, select productdescription under producttable. the sql query and data preview panes update with the selected table column.
add filter criteria to the sql query. in the criteria section, click where to display the where tab in the toolstrip.
filter the sql query results for prices greater than $10. in the
add section, in the column
list, select inventorytable.price. select the
> operator for the filter in the
operator list. enter 10 in the
value list. click add
filter.
note
if you enter filters using the like or not like operators, then enter the value in single quotes to represent a string.
the sql query and data preview
panes display the updated query results based on the new filter with the
where condition.
in the close section, click close where to close the where tab.
import all sql query results into the
matlab workspace. in the import section, click 
. in the import data dialog box, enter the name
data for the matlab workspace variable, and click ok. the matlab workspace displays the table data.
display the sql query results at the command line.
data
data =
6×5 table
productnumber quantity price inventorydate productdescription
_____________ ________ _____ _____________________ __________________
1 1700 14.5 '2014-09-23 09:38:34' 'building blocks'
3 356 17 '2014-05-14 07:14:28' 'slinky'
4 2580 21 '2013-06-08 14:24:33' 'space cruiser'
...find the maximum product price.
max(data.price)
ans =
24in 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.
join tables using left join and import query results
connect to a microsoft access database using the database explorer app. then, create an sql query that joins two tables using a left join. the database explorer app previews query results by default. after previewing the data, import all query results into the matlab workspace and perform simple data analysis.
set up the data source for the
tutorial.accdb database and name it dbdemo. for
details, see .
in the connections section of the database explorer tab, from the connect list, select the data source for connection. the connection dialog box opens. leave the user name and password blank, and click connect.
the database explorer app creates a connection to the microsoft access database. the database browser pane displays the available tables in the database. the data source tab, which is named dbdemo, appears to the right of the database browser pane. the data source tab contains two empty panes, sql query and data preview.
in the database browser pane, select the suppliers table as the first table for the join.
the database explorer app updates the sql query pane with an sql query that selects all columns and rows from the suppliers table. simultaneously, the database explorer app updates the data preview pane with a preview of the query results. the first 10 rows of data appear in the pane by default.
in the join section, click join to display the join tab in the toolstrip. in the add section, the name of the table selected in the database browser pane appears in the left table list. for details about joining tables, see .
in the left column list, select the name of the
shared column suppliernumber. in the right
table list, select the name
producttable as the table to join. select the
name of the shared column suppliernumber in this
table in the right column list.
in the add section, click add join. the database explorer app creates an inner join by default. in the edit section, click left to change the join from an inner join to a left join. the join diagram pane displays a pictorial representation of the join between the selected tables. the sql query pane updates the sql query with the new join. the data preview pane reflects the results of the updated sql query.
in the close section, click close join to close the join tab.
increase the number of rows displayed in the data
preview pane. in the preview section,
enter 20 in the preview size
box.
in the tree view of the database browser pane, select unitcost under producttable. the data preview pane updates with a new column.
the nan value in the unitcost
column indicates that the corresponding supplier does not supply
products.
add filter criteria to the sql query. in the criteria section, click where to display the where tab in the toolstrip.
filter the sql query results for products with a unit cost greater than
$10. in the add section, in the
column list, select the column name
producttable.unitcost. select the
> operator for the filter in the
operator list. enter 10 in the
value list. click add
filter.
note
if you enter filters using the like or not like operators, then enter the value in single quotes to represent a string.
the sql query and data preview
panes display the updated query results based on the new filter with the
where condition.
change the value of the filter from 10 to
20. click update filter. the
sql query and data preview
panes update with the results of the modified query.
in the close section, click close where to close the where tab.
import all sql query results into the
matlab workspace. in the import section, click . in the import data dialog box, enter the name
data for the matlab workspace variable, and click ok. the matlab workspace displays the table data.
display the sql query results at the command line.
data
data =
2×6 table
suppliernumber suppliername city country faxnumber unitcost
______________ _________________________ __________ _______________ ______________ ________
1008 'the great train company' 'nashua' 'united states' '403 121 3478' 21
1006 'acme toy company' 'new york' 'united states' '212 435 1618' 24
find the maximum product price.
max(data.unitcost)
ans =
24in 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.
sort query results
connect to a microsoft access database using the database explorer app. create a simple sql query and sort the results by the data in one column. the database explorer app previews query results by default. then, import the sorted data into the matlab workspace.
set up the data source for the
tutorial.accdb database and name it dbdemo. for
details, see .
in the connections section of the database explorer tab, from the connect list, select the data source for connection. the connection dialog box opens. leave the user name and password blank, and click connect.
the database explorer app creates a connection to the microsoft access database. the database browser pane displays the available tables in the database. the data source tab, which is named dbdemo, appears to the right of the database browser pane. the data source tab contains two empty panes, sql query and data preview.
in the database browser pane, select the inventorytable table. the sql query pane displays the sql query that selects all columns and rows from this table. the data preview pane displays the first 10 rows of the query results.
sort the results of the sql query. in the criteria section, click order by to display the order by tab in the toolstrip.
in the add section, in the
column list, select the
price column. click add
sort.
in the edit section, click descending to sort the prices in decreasing order. the data preview pane displays the updated query results with sorted prices.
in the close section, click close order by to close the order by tab.
import all sql query results into the
matlab workspace. in the import section, click . in the import data dialog box, enter the name
data for the matlab workspace variable, and click ok. the matlab workspace displays the table data.
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.
filter query results
connect to a microsoft access database using the database explorer app. create a simple sql query and filter the results. use a text filter to retrieve specific rows of data. the database explorer app previews query results by default. then, import the filtered data into the matlab workspace.
set up the data source for the
tutorial.accdb database and name it dbdemo. for
details, see .
in the connections section of the database explorer tab, from the connect list, select the data source for connection. the connection dialog box opens. leave the user name and password blank, and click connect.
the database explorer app creates a connection to the microsoft access database. the database browser pane displays the available tables in the database. the data source tab, which is named dbdemo, appears to the right of the database browser pane. the data source tab contains two empty panes, sql query and data preview.
in the database browser pane, select the producttable table. the sql query pane displays the sql query that selects all columns and rows from this table. the data preview pane displays the first 10 rows of the query results.
add filter criteria to the sql query. in the criteria section, click where to display the where tab in the toolstrip.
filter for products with a product description that starts with the letter
s. in the add section, in the
column list, select
productdescription. in the
operator list, select
like. to filter for text, enclose the text in
single quotes. in the value list, enter
's%'.
click add filter. the data
preview pane displays three rows of data. the product
description in each row starts with the letter s.
in the close section, click close where to close the where tab.
import all sql query results into the
matlab workspace. in the import section, click . in the import data dialog box, enter the name
data for the matlab workspace variable, and click ok. the matlab workspace displays the table data.
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.
remove duplicate rows from query results
connect to a microsoft access database using the database explorer app. create a simple sql query and remove duplicate rows from the query results. the database explorer app previews query results by default. after removing duplicates, import the data into the matlab workspace.
set up the data source for the
tutorial.accdb database and name it dbdemo. for
details, see .
in the connections section of the database explorer tab, from the connect list, select the data source for connection. the connection dialog box opens. leave the user name and password blank, and click connect.
the database explorer app creates a connection to the microsoft access database. the database browser pane displays the available tables in the database. the data source tab, which is named dbdemo, appears to the right of the database browser pane. the data source tab contains two empty panes, sql query and data preview.
in the database browser pane, select the producttable table. clear all the boxes for columns in the producttable table except for suppliernumber. the sql query pane displays the sql query that selects the suppliernumber column from this table. the data preview pane displays the first 10 rows of the query results.
sort the results of the sql query. in the criteria
section, click order by to display the
order by tab in the toolstrip. in the
add section, in the column
list, select the suppliernumber column, and click
add sort.
in the close section, click close order by to close the order by tab.
the data preview pane displays the rows sorted in increasing order, which is the default order.
the data preview pane shows the duplicate supplier
number 1001.
in the selection section, click exclude
duplicates to remove duplicate rows in the data
preview pane. the database explorer app adds the sql
statement distinct to the query in the sql
query pane. this statement removes duplicate rows from the
query results.
the data preview pane displays unique rows only.
import all sql query results into the
matlab workspace. in the import section, click . in the import data dialog box, enter the name
data for the matlab workspace variable, and click ok. the matlab workspace displays the table data.
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.
enter sql query manually
connect to a microsoft access database using the database explorer app. enter an sql query manually or paste an existing sql query into the sql query pane. then, import the query results into the matlab workspace.
set up the data source for the
tutorial.accdb database and name it dbdemo. for
details, see .
in the connections section of the database explorer tab, from the connect list, select the data source for connection. the connection dialog box opens. leave the user name and password blank, and click connect.
the database explorer app creates a connection to the microsoft access database. the database browser pane displays the available tables in the database. the data source tab, which is named dbdemo, appears to the right of the database browser pane. the data source tab contains two empty panes, sql query and data preview.
in the query section, click
manual. a new data source tab appears to the
right of the dbdemo tab with the name
dbdemo_manual. the suffix
_manual attached to the tab name indicates that you
are entering an sql query manually.
enter an sql query in the sql query pane. here,
select all columns and rows from the producttable table,
and rename the unitcost and
productdescription columns. use the sql statement
as to create aliases.
in the preview section, click preview query to preview the query results.
the data preview pane shows the results of the sql query. the pane displays the first 10 rows of data by default.
import all sql query results into the
matlab workspace. in the import section, click . in the import data dialog box, enter the name
data for the matlab workspace variable, and click ok. the matlab workspace displays the table data.
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.
version history
introduced in r2017b
select a web site
choose a web site to get translated content where available and see local events and offers. based on your location, we recommend that you select: .
you can also select a web site from the following list:
americas
- (español)
- (english)
- (english)
europe
- (english)
- (english)
- (deutsch)
- (español)
- (english)
- (français)
- (english)
- (italiano)
- (english)
- (english)
- (english)
- (deutsch)
- (english)
- (english)
- switzerland
- (english)
asia pacific
- (english)
- (english)
- (english)
- 中国
- (日本語)
- (한국어)