Write in front: the blogger is a real combat development after training into the cause of the “hill pig”, nickname from the cartoon “Lion King” in “Peng Peng”, always optimistic, positive attitude towards things around. My technical path from Java full stack engineer all the way to big data development, data mining field, now there are small achievements, I would like to share with you what I have learned in the past, I hope to help you on the way of learning. At the same time, the blogger also wants to build a perfect technical library through this attempt. Any anomalies, errors and matters needing attention related to the technical points of the article will be listed at the end, and everyone is welcome to provide materials in various ways.

  • Please criticize any mistakes in the article and revise them in time.
  • If you have any questions you would like to discuss or learn, please contact me at [email protected].
  • The style of the published article varies from column to column, and all are self-contained. Please correct the deficiencies.

MySQL database interface tool – Navicat common features

Key words: MySQL, database, interface tools, Navicat, common functions \

The article directories

Navicat Premium 15 is used in this article. The features mentioned in this article are also supported in older versions. If you need the latest version of the software (Mac/Windows), you can send a private message to the blogger.

Overview of Navicat

First of all, what is Navicat? Before often see a lot of partners did not install the database, directly throw an error screenshots to the group. Q: Why can’t I access the local database? Therefore, I will politely ask based on the error message: is your database service enabled? Answer: Database service? What’s that? Me: Emmmm… ?????

1. The relationship between DBMS and interface tools

The database management system we use, or database software, such as: MySQL, Oracle, SQLServer, after the installation, are in the way of system services, unlike our other installation programs, when using directly open an EXE program or shortcut. So before we use the database, we must ensure that the database service is open state, the operation process of different database software is similar, confirm in the service list of the system, or use the corresponding database service management tool. So the interface tool is just a database management software, compared to the database’s own client access program (usually run in a black box… What is black box, it is CMD! It is more convenient to use. So the functions provided by the interface tool are actually interface encapsulation based on the functions supported by the database itself. If there is no database service, it cannot work. So, do you have to install database services to use interface tools? Not entirely. If the database we’re accessing is in the cloud, or on an internet-accessible machine, then we don’t need to install the database software locally.

2. MySQL and Navicat

MySQL is one of the mainstream databases and one of the most representative relational databases. It occupies less resources and has good efficiency and convenient performance. It is usually used in small and medium-sized projects and can also be used to build database clusters.



Navicat is an interface database tool, can remotely connect to the cloud database or convenient access to the local database, many functions can not be operated through SQL, but through the interface to complete the operation, can improve the efficiency of developers, convenient data query and management.

Navicat has many similar tools, such as:MySQL Workbench(Official tools),DataGrip(powerful interface database tool in Jetbrains family),SQLyogFree MySQL database management tool.



If you need to install a MySQL database, you can refer to this article:MySQL 5.x interface installation – Windows.

Navicat’s common functions

For functions not covered in this article, please refer to the official documentation of Navicat software, which can be viewed online and is in Chinese: online-manual. The demonstration of other functions will be explained in subsequent articles.

1. User interface

  • Main toolbar: Provides quick access to common functions, such as connection, query, table, view, and user.
  • Navigation window: An area for browsing database objects such as database connections, databases, tables, views, and so on.
  • Tabs: Each time a function is opened or a database object is manipulated, tabs are added and can be switched on or off.
  • Object toolbar: For each function TAB, the corresponding function control is displayed.
  • Object window: Displays a list of objects in this area, such as tables, views, and other objects.
  • Information window: Displays detailed information, run logs, or preview information about an object.

2. Connect to the database and manage connections

  • A new connection

The first step in database management with Navicat is to establish a database connection,Before establishing a connection, ensure that the database has been successfully installed and the service has been started. To create a new connection, click the Connect button in the main toolbar. You can see that the Premium version of Navicat supports multiple connections.



MySQL > create a connection to the MySQL database.If we want to connect to a higher version of the database, our Version of Navicat should not be too oldBecause connecting to the database requires a driver, many interface drivers are built in and cannot be managed manually like DataGrip.

  • Connect the test

The new connection page is displayed. By default, the host name, port number, and user name have been entered. Specify access to the local database on port 3306. The default user is root (the highest permission user of the MySQL database), so we need to fill in the password set during the installation.

For the connection name part, does not affect the database connection, this is just our own random name, to mark different connections, usually will use the host, port, user information as a reference, can be more efficient management database connection.

If the information is correct, the connection can be established normally. After clicking the test connection, a successful connection window will appear.

  • Save the connection

After the test connection passes, click ok button to complete the new connection, and then it will appear in the navigation window.

  • Connection management

Right-click on an existing connection to view the supported operations, including: Open connection, Edit connection, Create connection, Delete connection, Copy connection, Create database, Create query, command line interface, run SQL file, refresh (database permissions, etc.), manage groups, color, refresh.

The operation of creating a database, running SQL files and refreshing can only be carried out after the connection is opened. The command line interface will open the interactive environment of MySQL client, which is convenient for you to operate, as follows:

3. Database management

  • Viewing the database list

After the connection configuration is complete, you can right-click the connection or double-click the connection to connect the database. After the connection is successful, the database list will be displayed (equivalent to the result of show Databases).

When we connect to the database, all the operations we do will directly affect the database, so we must be careful when operating, do not accidentally lead to their direct escape. Right-click on one of the databases and you can see the following menus: Running SQL files, dumping SQL files, printing databases and other functions must be connected and used, which will be introduced in other articles.

  • Open the database

To open a database, double-click or right-click and click open, and you will see a list of tables (equivalent to the show Tables execution result).

  • Creating a Database

When creating a database, you need to fill in three information: database name, CHARACTER SET (corresponding to CHARACTER SET), COLLATE (corresponding to COLLATE). You can also click SQL Preview to view the complete statement.

The character set determines the type of the character set supported by the database and the default character set configured for the data table. The collation rules mainly determine the rules for storing and querying data and whether they are case-sensitive (utF8 default collation rules is UTF8_general_CI).

  • Edit the database

Click Edit database to modify the character set and collation rules of the database. If you want to change the name of the database, Navicat does not support directly changing the name. You can use the data transfer function to change the name.

For example, an existing database, Database1, needs to be changed to database2.Tools -> Data Transfer:

Select all or required database objects:

Click the Start button:

Click the close button to end:



Once the transfer is complete, delete database1 to complete the database renaming (some other tools provide the ability to rename the database directly).

  • Deleting a Database

When you click Delete, the database will be deleted, including all database objects (tables, views, etc.) in it, so be careful.

4. Data table management

  • Creating a new table

After opening a database, click New Table in the object toolbar section

Click the save button after field setting and fill in the data table name to complete the creation of the table and then appear in the object window.

  • Edit table

Edit data table means to modify the table structure. Select a data table and click Design Table to enter the same interface as above. You can modify the fields. Note that when modifying the data table field type, ensure that the original data of the new type is compatible with the existing data in the table, or delete the faulty data before modifying the field type.

  • Delete table

Selecting The Delete table deletes the table object, along with the stored data.

  • Data management

After the data table is created, you can double-click to open it. At this time, it is equivalent to entering a data editing interface, and all data will be queried (pagination display, 1000 per page by default). We can directly add, modify and delete data in it.

Click + and – in the lower left corner to insert and delete data, directly select a data column for data modification, click the check box to submit and save, it should be noted that the added and deleted data must conform to the type defined in the data table field.

5. ER diagrams

In Navicat, by default, data tables in the database are displayed in the form of tables. We can switch to the form of E-R chart to display the relationship between data tables more clearly. The steps are as follows.

Click on theSee -> ER chart:

6. Run the SQL

If you need to execute your own SQL statements in the tool, you can do so in two ways.

  • Creating a Query Window

Click on theQuery -> Create a query:

In this way, we can get rich hints, and all SQL statements can be saved in the form of SQL files, or you can select a section of SQL statement to execute.

  • The command line window is displayed

Right-click on the database you are using -> Command line interface:



In this way, we can execute statement by statement, ending with a semicolon, and scrolling up and down as we would with commands in mysql client interaction.