Navicat for MySQL use notes

Abstract in the graphical tool for managing MySQL database, the most familiar is phpMyAdmin and Mysql-front, and today we share another tool to manage MySQL database---Navicat mysql.

Navicat for MySQL usage instructions

The first section, creating databases and tables

In the graphical tools for managing MySQL databases, phpMyAdmin and Mysql-front are the best known, and today we share another tool for managing MySQL databases---Navicat mysql.

Navicat MySQL can work with any 3.21 or more versions of MySQL and supports most of the latest MySQL features, including triggers, stored procedures, functions, events, views, and administrative users, whether for professional database developers or DB novices, Its well-designed user graphical interface (GUI) provides us with a simple management platform for the safe and convenient operation of MySQL data information. It is not only stable for the Windows platform, but also compatible with Mac OS X and Linux systems. Its official offer Simplified Chinese version for you to download the trial (for 30days),

Figure: 11: Download and install here omit installation steps several two: Create a connection to database 1. "Connection Database" after the installation of the Navicat, in the upper left corner of the navigation window has a "Connect" button, click will pop up a connection properties prompt box, first to "connect" a proper name and then enter the correct connection information, if you want to manage the remote database, Enter the correct IP address in the IP address field. Navicat also supports SSL or SSH connections, in this case I will demonstrate with you as an example of the simplest common connection, which is managing the local database. Figure: 2 After the successful connection, the left navigation window will see the local MySQL database, where the gray indicates that the database is not open, green is the identity of the database has been opened.

Figure: 3 Right-click on a gray database with commands such as Open, close, new, delete and data transfer, manage users in the context menu. 2. "Manage users" in order to ensure the security of the database, the operation of the user hierarchical authorization is very necessary, Navicat provides us with a very powerful and very convenient user management tools. In Figure 1, we select the "Manage Users" command on the right side of "connect" and then the Admin user interface is displayed, and the system defaults to only one root user with all the permissions.

Figure: 4 Select the "Add User" option, enter the user name and related information in the Prompt window, add, the newly added user does not have any operation permission. We can appropriately authorize it according to business requirements, such as only the view (SELECT) command. Figure: 53: New Database and table new database is relatively simple, the Navicat Navigation view (1) in the left side of the navigation bar in the blank area right-click to see the "New Database" command, this very easy, I will build a database to manage students: school. Figure: 6 after the completion of the library, the next thing to do is to build the student table: Students (7), figure: 7 in the process of building a place to pay special attention, that is, "column", for the first time to use Navicat novice, this is unfamiliar, it means that we usually say "field", the toolbar "Add Field" is the meaning of adding fields, do not mix it up, after adding all the fields to set the corresponding "primary key" according to the requirements. If the database is more complex we can also continue to do the relevant settings according to demand, in the "Fields" tab bar also has indexes, foreign keys, triggers for us to call, under the "SQL Preview" tab is the SQL statement, which is very useful for us to learn SQL statements. "Description:" I do not know much about database development, so for functions, events, reports and other advanced application skills can not be fully demonstrated for reference.

Part II, Import/Export Data sheet

IV: Manage and maintain data sheets and databases 1. When we design the table, Ken can devote the most time is to optimize and maintain the NAVICAT also provides us with a "maintenance table" convenient tool, right-click on the table students, in the right-click menu under the position of a "maintenance table" option, which includes the Analysis table, checklist, table optimization, We have 4 orders for our use.

Figure: 82. The Export data table has more formats than phpMyAdmin and Mysql-front, and adds support for Excel2007 or more versions, and when a table is finished, If there are hundreds or more of the original data need to import MySQL database, we can first export the designed data into an Excel table, and then fill in the format to populate the data, and finally the completed data into the MySQL database, we first see how to use NAVICAT Export data , a total of 5 steps. Step 1: Click the "Export Wizard" command in step 1 to select the table type: Excel file

Figure: 9 Step 2: Select the location of the export table to place the exported Excel table in the appropriate location, such as the My Documents diagram: 10 Step 3: Select the exported columns (columns) to export, all fields are selected by default

Figure: 11 Step 4: Define additional options It is important to tick the "include column headings" item so that the data in the exported Excel table looks more complete.

Figure: 12 Step 5: After the export completes the above settings, we click the "Start" button to perform the export, after the export is completed, in the [MSG] message window, the category of the exported table, location and other information tips.

Figure: 13 Let's take a look at the contents of the exported Excel table, because there is no input data in the previous design table example, so the Excel exported here is an empty table, then we manually input some data to demonstrate the operation of the imported data table.

Figure: 143. Import data table in the Excel table shown in Figure 14 I manually entered some data, and now I import the table into the database, import the data table A total of 8 steps, we demonstrate Step 1: Run the Import Wizard, select the type of import data

Figure: 15 Step 2: Select a file data source

Figure: 16 Step 3: Unknown, the system went through step 2 directly into step 4, do not know if this is not a system bug, in order to synchronize with the prompt box, omitted here. Step 4: Define some additional options for the source file the option in the Red box must be selected correctly, otherwise the correct import will not be completed. [Column name line]: The row position where the data table field is located (see Figure 14), enter 1;[First data row]: The imported source data starts with the number of lines, I enter 2 here (see Figure 14); [Last data row]: The imported source data to the end of the first line, I entered here is 6 (see Figure 14)

Figure: 17 Step 5: Select the target table or create a new table

Figure: 18 Step 6: Define the source field and the Target field of the corresponding relationship, if the target field set the primary key, in this step must be checked, otherwise it will not import data normally

Figure: 19 Step 7: Select Import Mode if it is a new table, we choose the first item: Add, depending on the situation we can choose different options, interested friends can test what effect each

Figure: 20 Step 8: Click on the "Start" button to execute the import command, after the import is completed there will be a [MSG] message prompt

Figure: 21 After execution, we go to the database to verify:

Figure: 22 in Figure 22 we can clearly see all the added data, in the lower left corner of the window has a small row of buttons to note, where the "+" and "-" is to insert and delete a record button, if you want to add a record, you can rest the cursor at the last value of the last row, and then press " TAB key to add the data down.

The third part, Implementing automatic Database Backup

V. Backup and restore MySQL database

In the database management, backup and restore is necessary to do things carefully, if negligence or do rough, then once the consequences of database failure is unthinkable, so navicat also have backup and restore function, compared to create features, its backup function is very simple.

1, backup database in the main view window select the "Backup" function button, in the following navigation bar click "New Backup" pop up its prompt window, in the Prompt window, click the "Start" button to execute the backup command.

Figure: 23 After performing a complete command, you can see information about the backup in the navigation bar. Right-click on the backup time and check the General command to see where the backup file is stored, the file size, and when it was created. Figure: 242, restore the database to perform the "Restore Backup" command in the view shown in Figure 24, click the "Start" button in the pop-up window, for the warning of course we choose to determine the button.

Figure: 25 After performing the restore command, there will still be a friendly message prompt.

Figure: 263. Creating a backup plan manually backing up the database is easy, but can you create a scheduled backup? This is perfectly possible, and navicat can do the same. Step 1: Set up a scheduled batch job

Figure: 27 in the "General" navigation bar on the left, select the database you want to back up, and then click the "Save" button to make a nice name, for example. After you save this batch job, you can set up scheduled tasks for it. Step 2: Set up a scheduled task in the View window of the batch job, click the "Set Scheduled Tasks" option button and a dialog box will pop up, and we need to schedule the time settings to complete in this view. Click the "New" button to create a new schedule, select the scheduled execution period in the "Scheduled Tasks" drop-down list: daily/weekly/monthly, etc.

Figure: 28 The following is a weekly illustration:

Figure: 29 After setting the scheduled execution time, click the "Apply" or "OK" button. As long as we do the database backup management work, there is no fear of database failure, of course, even if the backup work is foolproof, we do not want database problems.

Navicat for MySQL use notes