Table SQL

版本 1.3.0 (3.7 KB) 作者: Tommaso Belluzzo
A script for performing SQL CRUD operations on Matlab tables.
190.0 次下载
更新时间 2020/7/17

查看许可证

# INTRODUCTION #

Basically, this script is a SQL wrapper around Matlab tables. It is especially useful for those who are new to Matlab and have the habit of "thinking in SQL", or for those who need to accomplish complex data manipulation tasks and struggle to achieve them with the built-in table manipulation functions.

# METHODOLOGY & USAGE #

The "table_sql" function accepts a single input argument, which must be a valid SQL CRUD statement (DELETE, INSERT, SELECT or UPDATE) where the table names refer to ordinary Matlab tables defined within the main workspace.

Once the query is parsed, the script sets up an in-memory instance of the SQLite database, imports the tables, performs the specified SQL statement, synchronizes the workspace tables with the database and returns the result. The content of the latter depends upon the performed operation:
> for SELECT statements, an m-by-n table representing the result set returned by the database;
> for DELETE, INSERT and UPDATE statements, an integer representing the number of affected rows.

# EXAMPLES #

1) Setup:
load('patients');
ids = (1:numel(LastName)).';
t1 = table(ids,LastName,Gender,'VariableNames',{'ID' 'LastName' 'Gender'});
t2 = table(ids,Age,Height,Weight,'VariableNames',{'ID' 'Age' 'Height' 'Weight'});

2) Selections (with and without assignment):
t3 = table_sql('SELECT A.ID, A.LastName, B.Age FROM t1 A INNER JOIN t2 B ON B.ID = A.ID');
table_sql('SELECT Age, AVG(Height) FROM t1 A INNER JOIN t2 B ON B.ID = A.ID GROUP BY Age ORDER BY Age');

3) Modifications:
ar = table_sql('DELETE FROM t1 WHERE Gender = "Male"'); t1
ar = table_sql('UPDATE t2 SET Age = Age + 1 WHERE Age >= 40'); t2

# NOTES #

> The script requires a SQLite JDBC driver, which can be downloaded from the following link: https://bitbucket.org/xerial/sqlite-jdbc/downloads/. The ".jar" file must be placed in the script folder.
> The column types of the tables being queried are normalized as follows: categoricals and strings become character arrays, logicals become 8-bit integers (bytes), integers become doubles.

引用格式

Tommaso Belluzzo (2024). Table SQL (https://www.mathworks.com/matlabcentral/fileexchange/68771-table-sql), MATLAB Central File Exchange. 检索来源 .

MATLAB 版本兼容性
创建方式 R2018a
兼容 R2015b 到 R2018b 的版本
平台兼容性
Windows macOS Linux

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!
版本 已发布 发行说明
1.3.0

Minor fixes and improvements.

1.2.9

Improved description.

1.2.8

Minor fixes and improvements.

1.2.7

Minor improvements.

1.2.6

Changed image.

1.2.5

Changed image.

1.2.4

Changed image.

1.2.3

Changed image.

1.2.2

Changed image.

1.2.1

Changed image.

1.2.0

Improved description.

1.1.9

Minor fixes and improvements.

1.1.8

Minor fixes and improvements.

1.1.7

Minor fixes and improvements.

1.1.6

Minor fixes and improvements.

1.1.5

Minor fixes and improvements.

1.1.4

Updated details concerning compatibility & requirements.

1.1.3

Updated details concerning compatibility & requirements.

1.1.2

Minor fixes and improvements.

1.1.1

Project website.

1.1.0

Target release.

1.0.9

Improved tags.

1.0.8

Improved tags.

1.0.7

Improved tags.

1.0.6

Improved description.

1.0.5

Improved description.

1.0.4

Improved description.

1.0.3

Screenshot added.

1.0.2

Minor fixes and improvements.

1.0.1

Added details concerning compatibility & requirements.

1.0.0