Shulou(Shulou.com)06/01 Report--

Most people do not understand the knowledge points of this article "how to use VS Code's MySQL extension to manage the database", so the editor summarizes the following contents, detailed contents, clear steps, and has a certain reference value. I hope you can get something after reading this article. Let's take a look at this "how to use VS Code's MySQL extension to manage the database" article.

In many cases, we need to check the database records at any time to ensure that the program is executed correctly. There are also many tools that provide visual interfaces to help us implement these functions, such as phpMyAdmin (you need to install PHP and Web servers), Navicat (a powerful SQL management tool, but requires commercial authorization), and some free tools, such as Workbench, Sequel Pro, HeidiSQL, and so on. Of course, you can also use the mysql console directly to do all the work.

But if you are using VS Code for major development, I recommend using the extensions above to address these simple requirements. First of all, the advantage is free, in the case of limited wallet, do not do cracking white whoring party; second, the function is simple, which means that we do not need to spend energy to master its use.

Install the MySQL extension

Many MySQL management tools can be found in VS Code's Extensions (extension), and almost all of the top ones can meet our simple needs. Search MySQL will come out a lot of related tools, here I chose the developer for this extension of cweijan, of course, other tools can also be selected as you like. I generally pay attention to the installation volume, rating, and final updates of the tool.

The button for the extension will appear in the activity bar of your VS Code interface after the installation is complete. The extension has Chinese documentation, and you can learn some ways to use it directly from the author's documentation. I'll record some MySQL operations commonly used in development below.

Add a MySQL connection

Click to select the Database icon in the activity bar

After opening the DATABASE sidebar, click the Add Connection icon to open the connect editing page

Enter your connection information into the required fields, including Host connection address, port number, Username user name and Password password. Other options can be filled in according to the requirements, such as the database name Showed Database to be displayed (if not, all will be displayed).

Finally, click the Conncet connection to complete the addition.

When a MySQL connection is successfully added, it appears in the sidebar. The default connection name consists of host@port, such as localhost@3306. To the right of the connection name are the Refresh refresh icon, the New Database new database icon, and the Open Terminal open console icon, respectively.

Add a new database

The plug-in does not provide us with a visual UI to add a database, but we can still easily add a new database.

Clicking the New Database icon next to the MySQL connection name in the sidebar will open a SQL edit page with pre-filled statements to create new data:

CREATE DATABASE [name] DEFAULT CHARACTER SET = 'utf8mb4'

Where [name] is the name of the database, please fill in as required. And set the default character set for the database, generally using the default utf8mb4. Finally, click Run SQL at the top of the edit page to execute the statement to successfully add a database.

Add and modify table structure

In a similar way, expand the newly built database, and there are also several function buttons on the right side of the Table table grouping, namely the Refresh refresh button and the Table button.

Click the Table button to open the SQL edit page of the newly created table. This page also provides us with a SQL template for the newly created table:

CREATE TABLE [name] (id int NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT 'Primary Key', create_time DATETIME COMMENT' Create Time', update_time DATETIME COMMENT 'Update Time', [column] VARCHAR) COMMENT'') DEFAULT CHARSET UTF8 COMMENT''

Here we borrow the new User table structure of the Mybatis-Plus document to create a new table that holds the user's data.

CREATE TABLE user (id BIGINT (20) NOT NULL COMMENT 'primary key ID', name VARCHAR (30) NULL DEFAULT NULL COMMENT' name', age INT (11) NULL DEFAULT NULL COMMENT 'age', email VARCHAR (50) NULL DEFAULT NULL COMMENT 'mailbox', PRIMARY KEY (id))

Click Run SQL at the top of the edit page to execute the statement to successfully add a table, and the table will appear under the Table group.

If you want to modify the established table structure, you can directly right-click the table and select Design Table to open the table structure editing page. In this interface, you can change the table name, add new fields, modify existing fields, and edit the index.

Common operations of data table interface

Click the show under the Table grouping to open the data viewing interface for the table. The interface consists of three parts, namely, the SQL statement edit box, the common operation button group and the data table area. After opening the data table, the SQL statement in the figure is used by default to display 100 pieces of data. If you need to query the data according to the conditions, you can edit the statement yourself. Click the green Excute sql button to execute the SQL statement. Other operations such as inserting new data, modifying data, deleting records and exporting data can be done directly through buttons and editing tables in the interface.

Perform SQL operation

In addition to manipulating tables and data through UI, we can also directly use SQL statements to do anything with the data. There are several ways to open the SQL editing page:

Click the Open Query button next to the database name in the sidebar to open a sql editing page directly. After entering the SQL statement, you can execute the statement by clicking Run Selected SQL in the upper right corner.

Click the Select Table SQL button next to the data table name, you can directly open a sql edit page, and preset the data query SELECT statement, you can query the data of the data table by editing the statement.

Expand the data table name, all fields will be listed in the sidebar, click a field name, will open the sql edit page to edit the field, and preset an ALTER TABLE statement, you can modify the properties of a column field by editing the statement.

Import and export table structure and data

Right-click the database name in the sidebar and you will see several buttons for data import and export: Export Data (export data), Export Struct (export table structure), and Import Sql (import SQL)

Right-click on the data table name and there are also Export Data and Export Struct function buttons.

In addition to importing and exporting data, you can also generate simulation test data. Right-click the data table name, select Generate Mock Data to open the mock.json editing page, and then click the Start Generate button in the upper right corner to automatically generate some random data in the table according to the definition of mock.json.

Pits encountered by other extensions

There are also some usage problems encountered during the selection of other MySQL extensions, which are recorded here as a hint.

SQLTools by Matheus Teixeira

This plug-in uses the main management interface and database driver separate installation path, first install the SQLTools extension and then install the database driver you need to use. The method is to click the name of the author in the extension introduction interface, and other plug-ins for the author will be screened in the left search bar. Select SQLTools MySQL/MariaDB and install it to make the extension support MySQL database.

In addition, since the MySQL version installed in my Ubuntu 20.04 environment is 8.0, there will be an error when connecting to the database:

Request connection/GetChildrenForTreeItemRequest failed with message: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client

The problem is that this MySQL driver does not support MySQL 8's caching_sha2_password encryption. If you want to continue to use this plug-in, you can change the user encryption of the database to mysql_native_password. I also explained how to change the method in the previous article, see installing MySQL to Ubuntu 20.04

Also posted on: managing databases using VS Code's MySQL extension

The above is about the content of this article on "how to use VS Code's MySQL extension to manage the database". I believe we all have a certain understanding. I hope the content shared by the editor will be helpful to you. If you want to know more about the relevant knowledge, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.