How to use MySQL Workbench to operate a MySQL/MariaDB database

MySQL Workbench is a visual database management software designed specifically for MySQL. You can remotely manage MySQL databases on your own computer using a graphical interface.

For MySQL remote management software, you can choose from HeidiSQL for Windows, Sequel Ace for MacOS, or MySQL Workbench, a cross-platform client from MySQL.

This article uses MySQL Workbench for Mac edition to explain, Mac edition and Windows edition, Linux edition except the UI is slightly different, the entire operation logic is exactly the same, no matter what operating system and version you use, you can follow this tutorial.

Contents of this tutorial

How to download and install MySQL Workbench

How do I remotely connect to a database using Workbench

Use Workbench to operate the database

1. MySQL Workbench initialization interface
2. Connect to the remote MySQL database
3. Creating a database
4. Create tables in the database
5. View table data
6. Insert data into table
7. Modifying field attributes
8. Delete a table from the database

4. MySQL data import and export

1. Importing SQL Data
2. Exporting SQL Data

Configure the attributes of the MySQL database table

1. Primary key constraint
2. Foreign key constraints
3. Unique constraint: UQ index
4. Not null constraint
5. Default constraints

Execute an SQL query in Workbench

Create trigger and trigger application

Viii. User accounts and access rights

1. Check accounts in the MySQL database
2. Example Create a MySQL database account
3. Example Delete the MySQL database account

An updated version of the Workbench cara Cloud

How to download and install MySQL Workbench

MySQL Workbench is the official database operating software produced by MySQL. You can download it from the official MySQL Workbench download page for free.

In this download page, you can choose the platform and version suitable for you according to your actual situation.

Download it and install it Next.

Read more: A Horizontal Review of 10 MySQL GUI management Tools – Free or Paid?

How do I remotely connect to a database using Workbench

• SSH login is required for your server (SSH login is required for MySQL).
• Adjusting the Server Firewall
• Configure remote login to the MySQL server. (Enable this function if you select remote login to the MySQL server.)

For a tutorial on Workbench remote login, see the full article in How to Remotely Connect to MySQL Server using the Workbench.

For details on how to enable remote login with MySQL, see how to Remotely Connect to MySQL database and Configure Alicloud Exet Connection.

Use Workbench to operate the database

1.MySQL Workbench initialization interface

Workbench initializes the interface

2. Connect to the remote MySQL database

Click the configured remote login card to log in to the remote MySQL server. If you haven’t already configured the remote side, follow the instructions in How to Remotely Connect to MySQL Server using the Workbench.

After you log in to the remote database, the database list that has been created in the remote MySQL database is displayed in the red box.

3. Create a database

In the blank area of the SCHEMAS list, right-click and choose “Create Schema…” from the menu that pops up. The database creation process page is displayed.

Enter the database Name of the new database in Schema Name, and then select Apply

MySQL Workbench automatically helps us generate SQL commands to create databases.

Run the following command to create the vm: You can see that the Kalacloud_demo database we just created is added to the Schemas list

In 4.kalacloud_demoCreate tables in the database

Right click on Kalacloud_demo, select Set as Default Schema and make it the Default database.

Then select the down arrow, right-click Tables and select Create Table to go to the process page for creating the Table.

(1) Name: Fill in the Name of the table

(2) Create columns and column attributes in the table.

(3) Click to Edit to add a column.

(4) Click Apply to execute SQL command.

MySQL Workbench will help us generate SQL commands to create tables.

5. View the table data

(1) Click the “lightning Icon” icon after the name of the table to be viewed

(2) Workben automatically generates an SQL command for us to view the table

(3) After clicking the yellow lightning symbol to execute, the table data of the query table can be seen in the Result Grid

6. Insert data into the table

(1) Use Select to find the contents of the table, add the data to the table directly.

(2) Workbench generates a piece of code to view the table

(3) Show the data in the table in the following table. We can add data to the table here according to our own needs.

(4) Finally click Apply to generate the code to add data

As shown in the figure above, we can see that the data line of The Cara cloud with ID 1 has been converted into SQL command. Click Apply to add the data to the database.

7. Modify the field attributes

Right-click on the Table you want to modify and select “Alter Table…” , the table information is displayed on the right. After you modify the content to be modified, Apply is applied.

8. Delete tables in the database

Right-click the Table you want to Drop, and click “Drop Table…” Can be deleted. Note: This operation is not reversible, choose carefully.

How to find and delete duplicate records in MySQL?

4. MySQL data import and export

1. Import SQL data using MySQL Workbench

On the menu bar, select File → Open SQL Script… Select the SQL file to import

In the dialog box that is displayed, select the SQL file to be imported.

This is the key. Be sure to read it carefully.

(1) In the open SQL file header, write the name of the database to import, this tutorial will import kalacloud_demo database. So USE kalacloud_demo;

(2) Click the yellow Lightning icon to execute the code

(3) Write the four data into the users table corresponding to the database

Using the SELECT query table, you can see that four pieces of data have been imported successfully

2. Use MySQL Workbench to export SQL data

(1) Select the Administration TAB

(2) Find Data Export in MANAGENMENT

(3) Select the database to be exported

(4) Select tables from the database to be exported

(5) Select the exported content

(6) Select a storage path for exporting the SQL file

(7) Start Export

How to import and export SQL data, Excel and CSV in MySQL

Configure the attributes of the MySQL database table

1. Primary key constraints

Primary Key (PK) : Specifies that the data in a column is non-empty, unique, and cannot be repeated

(1) Right-click the Table to be set and choose “Alter Table…

(2) PK is the primary key constraint.

2. Foreign key constraints

Foreign Key: specifies that the column record belongs to a record in the main table and is referenced to another data

(1) Right-click the Table to be set and choose “Alter Table…

(2) Select the label “Foreign Keys”

(3) Select the table to be associated

(4) The current table column, select the key to be associated

(5) Select the key to be associated with the column of the associated table

A) Apply B) Apply C) Apply D) Apply

3. Unique constraint: UQ index

Unique: specifies that a column or combination of columns cannot duplicate data

The selected column is the unique constraint index of the data table. Deselecting the column removes the unique constraint index.

4. Non-null constraints

Not Null: Specifies that a column cannot be Null

Select the non-empty constraint of the column data table of NN, and deselect the non-empty constraint of the column.

5. Constraints on default values

The full name of the Default value is Default Constraint

The MySQL default value constraint is used to specify the default value for a column.

In the red box, the default value of States is set to “1”, that is, when a new record is inserted without assigning a value to this field, the system automatically assigns a value of “1” to this field.

MySQL > Alter TABLE AUTO_INCREMENT increment MySQL > Alter table AUTO_INCREMENT increment

Execute an SQL query in Workbench

We can also execute an SQL query directly from Workbench. The following code inserts a row into the Kalacloud_TABLE_demo table in the Kalacloud_demo database.

INSERT INTO `kalacloud_demo`.`kalacloud_table_demo` (`id`, `name`, `phone`, `wechat_id`, `wechat_name`, 'states ') VALUES ('2', 'kalacloud', '13777779999', '2');Copy the code

Write the SQL code directly into the Query TAB, and then click “Yellow lightning” to execute the code.

Then, we run SELECT to see if the last command has been written to the table.

SELECT * FROM kalacloud_demo.kalacloud_table_demo;
Copy the code

And you can see, in the red box, ID 2 is the data that we just wrote.

Read: “how to view the MySQL database, tables, indexes, size? Find footprint the largest table”

Create MySQL trigger and trigger application

1. Right-click the table to which you want to add a trigger.

2. Select the Triggers TAB to go to the trigger Settings page

3. Select the type of trigger to be added.

4. Add trigger code

There are 6 types of MySQL triggers:

• BEFORE INSERT: Checks whether the inserted data complies with the service logic. If the inserted data does not comply with the service logic, an error message is displayed.
• AFTER INSERT: After an account is created in table A, the creation success information is automatically written to table B.
• BEFORE UPDATE: Checks whether the updated data complies with the service logic. If the updated data does not comply with the service logic, an error message is displayed.
• AFTER UPDATE: Records operations in logs after data is updated
• BEFORE DELETE: Check whether associated data exists before deleting data. If yes, stop the deletion.
• AFTER DELETE: After information in table A is deleted, information associated with table A in table B is automatically deleted.

For a detailed tutorial on how to use and apply the six types of triggers, see MySQL Triggers Create, view, delete INSERT, Update, delete Tutorial – Cara Cloud. This tutorial explains how to use all six triggers in detail, using a real-world scenario as a clue.

Viii. User accounts and access rights

1. Check the accounts in the MySQL database

(1) On the menu bar, select Server

(2) Select Users and Privileges to go to the User Management page

2. Create a MySQL database account

(1) Select Add Account

(2) Add account details

(3) Apply

3. Delete the MySQL database account

(1) Select the account you want to delete

(2) Click Delete

(3) Click Refresh to Refresh the list

Finally, Workbench has some features that are unique to paid software, such as drawing ER diagrams, forward and reverse engineering, synchronization between local and remote databases, etc., which I’ve written a separate article about. Use MySQL Workbench to automatically generate ER diagrams and update remote databases synchronously.

MySQL > Create, authorize, cancel, delete, and rename an account

Cara Cloud – a new generation of low code development tools

MySQL Workbench provides us with graphical management tools to manage MySQL, making it easy to manage remote databases locally, but Workbench can only do low-level database operations. It does not apply to the requirement of “build on database, need front-end custom development”.

Kara Cloud is a new generation of low-code development tools, free of installation and deployment, with one-click access to common databases and apis, including MySQL. Not only can complete all Workbench functions, but also according to their own workflow, customized development. No tedious front-end development, just simple drag and drop, can quickly build enterprise internal tools. Months of development can be reduced to days with The Cara Cloud.

Carla cloud provides one-click access to common databases and apis

Kara Cloud can easily build data kanban according to the workflow requirements of the company, and can share data with partners in the group

The following figure shows the background of “coupon issuance and verification” built in 5 minutes by using Cara Cloud. It only needs simple drag and drop to quickly generate front-end components. As long as you can write SQL, you can build a set of convenient database tools. Welcome to the Kara Cloud.

conclusion

In this tutorial, we walk through the basics of working with a MySQL database by MySQL Workbench. More database-related tutorials can be found at Cara Cloud.

MySQL > MySQL > MySQL

• MySQL > select * from timestamps
• How to implement BLOB data type access in MySQL? What are the application scenarios of BLOB?
• How to skip multiple table exports or specify multiple table export backups in MySQL/MariaDB
• How to save MySQL/MariaDB query results to a file
• MySQL > select the first and last entries in a group by group