MATLAB connects to the MYSQL database and performs addition and deletion operations

I recently built an experiment management system. The new UI interface of matlab is relatively interactive and user-friendly. I am new to mysql and have never touched a database before, so I will make a record.

1. Environment installation

1. Make sure that matlab has database toolbox installed, which can be queried through the ver command;

2. Download the appropriate mysql version, enter the mysql official website, scroll down, and there is an option “MySQL Community (GPL) Downloads?” -> Select MySQL Installer for Windows -> Select version 8.035 (choose a new version, but select Any one will do), just download and install;

3. Download the database management software DBeaver. Just choose the free version and use it. When installing, choose the exe program to install the Chinese version. Otherwise, you can choose the English version. After installing DBeaver, open the software, database -> find Mysql. Connection, you need to download the driver in the middle, download it together (note this driver, the driver version is very important, the default driver of DBeaver is obviously good, click Database->Driver Manager-mysql->Library to see the current mysql driver , as of the time of writing this article, the driver I am using is 8.0.29);

4. Download and install the driver. Download the driver through the website https://mvnrepository.com/artifact/mysql/mysql-connector-java. After downloading, place the driver in the folder under matlab’s toolbox, but the location does not matter. Just match the path. You can place it in the following folder D:\install\matlab\toolbox\local, then find the classpath.txt file in this folder, open it, and add $ in the last line matlabroot/toolbox/local/mysql-connector-java-8.0.29.jar, close and restart matlab. At this point, the configuration has been completed and operations are ready. You can go directly to Part 4, Example Analysis.

2. Set parameters and connect to the database (I tried this many times before I succeeded)

1. Commonly used connection parameters are as follows. If dbname is not available when you first create the database, you do not need to write it and replace it with ”; password is the password filled in when installing Mysql, which is usually set by yourself; dbport is the default in Mysql The one is 3306.

dbhost = 'localhost'; % database host name
dbport = 3306; % database port number
dbname = 'test'; % database name
username = 'root'; % database username
password = 'xxx'; % database password

2. Create a database connection object

% Create database connection object
conn = database('', username, password, 'Vendor', 'MySQL', 'Server', dbhost, 'Port', dbport);

It should be noted that the current database does not specify a specific database name, which can be created later.

3. Test whether the database connection is successful

if isopen(conn)
    disp('Successfully connected to the database!');
else
    error('Unable to connect to the database.');
end

4. After confirming that the database connection is successful, assume that you want to create a database named “my_database”

create_query = 'CREATE DATABASE IF NOT EXISTS my_database;';
exec(conn, create_query);
conn1=database('my_database', username, password, 'Vendor', 'MySQL', 'Server', dbhost, 'Port', dbport);

3. Addition and deletion of database

The format added to the database adopts the exec(conn, sql statement) format, such as exec(conn, ‘insert into test values(”3”,”李五”)’) , the SQL statement for the add operation is insert into table values(“”,””). After the operation is completed, we check the MySQL database again to observe whether the add operation is successful.

The database performs a modification operation, which is the so-called update operation. The SQL statement we use here is update(conn,’test’,{‘ID’},5,’where name=”Zhao 4”’), the conn in it can be regarded as the global variable of our operation. The meaning of this statement is to update the ID named Zhao Si to 5, where the ID and name are both in the user table. Column variable, where ID is the primary key. After we perform this operation, we query the database.

Perform a database deletion operation, that is, delete a certain row of data from the user table of the database. The SQL statement deleted here is

The specific restrictions in curs = exec(conn,’delete from test where ID = ”5”’) can be set by yourself after where. Here we set the deletion in the database. Data with ID 5 in the user table.

Reference for this piece: MATLAB performs addition, deletion, modification and query on MySQL database_51CTO blog_mysql database addition, deletion and modification query

1. To add a database, you can refer to 2.4. To delete a database, you can use the following statement

% Suppose you want to delete the database named "my_database"
drop_query = 'DROP DATABASE IF EXISTS my_database;';
exec(conn, drop_query);

2. Addition and deletion of data content

try
    % Give any data source
    data_source = struct('country', {'China', 'United States', 'Germany', 'India'}, ...
                     'city', {'Beijing', 'New York', 'Berlin', 'Delhi'}, ...
                     'school', {'Tsinghua University', 'Harvard University', 'Technical University of Munich', 'Indian Institute of Technology'}, ...
                     'class', {'Physics', 'Computer Science', 'Engineering', 'Mathematics'});
    % Create table
    exec(conn, 'CREATE TABLE IF NOT EXISTS my_table0 (id INT PRIMARY KEY AUTO_INCREMENT, country VARCHAR(255), city VARCHAR(255), school VARCHAR(255), class VARCHAR(255));');
    % Assume data_source is a data source containing country, city, school and class data
    
    % If there is an external database, use this insertion method
    for i = 1:length(data_source)
        % Extract country, city, school and class data
        country = data_source(i).country;
        city = data_source(i).city;
        school = data_source(i).school;
        class = data_source(i).class;
        % Insert data into the table
        insert_query = sprintf('INSERT INTO my_table0 (country, city, school, class) VALUES (''%s'', ''%s'', ''%s' ', ''%s'')', country, city, school, class);
        exec(conn, insert_query);
    end
    disp('Data has been successfully inserted into the database.');
   
    % If you insert them one by one, what should you do?
    % Suppose you want to delete the record whose country is China
    delete_query = 'DELETE FROM my_table0 WHERE country = "China";';
    exec(conn, delete_query);
    disp('Data deleted successfully.');
    % Add the table my_table to be deleted
    % Suppose you want to delete the table named "my_table"
    drop_query = 'DROP TABLE IF EXISTS my_table;';
    exec(conn, drop_query);
    disp('Table deleted successfully.');
catch exception
    error(['An error occurred:', exception.message]);
end

4. Example analysis

% Set database connection parameters
dbhost = 'localhost'; % database host name, set to local host
dbport = 3306; % database port number
dbname = 'lab'; % database name
username = 'root'; % database username
password = 'xxx'; % database password

% Create database connection object
conn = database(dbname, username, password, 'Vendor', 'MySQL', 'Server', dbhost, 'Port', dbport);
% Create table
exec(conn, ['CREATE TABLE IF NOT EXISTS lab_test1(id INT AUTO_INCREMENT PRIMARY KEY, time_ VARCHAR(255),' ...
                ' name_ VARCHAR(255), source_ VARCHAR(255), number_ VARCHAR(255),type_ VARCHAR(255),belong_ VARCHAR(255),' ...
                'who_ VARCHAR(255),position_ VARCHAR(255));']);
% Database insertion adopts exce(conn,sql statement) format
if isopen(conn)
    disp('Successfully connected to the database!');
    % Database deletion operation
    exec(conn,'delete from lab_test1 where id="12"');
    % Database addition operation
    exec(conn,'insert into lab_test1(time_,name_,source_,number_,type_,belong_,who_,position_) values("2023-11-14", "xiaozhang","nafei", "20","wu","Xiao Ming","Left 1","22")');
    % Database search operation
    data=fetch(exec(conn, 'SELECT * FROM lab_test1'));
    data.Data
    % Database modification operation
    % update(conn,tablename,colnames,data,whereclause)
    update(conn,'lab_test1',{'number_'},{'21'},'where number_=20')
else
    error('Unable to connect to the database.');
end
close(conn)

The knowledge points of the article match the official knowledge files, and you can further learn relevant knowledge. MySQL entry skill treeSQL advanced skillsCTE and recursive query 78104 people are learning the system