当前位置:网站首页>How to import the SQL file in the book "SQL basic course" into the database?

How to import the SQL file in the book "SQL basic course" into the database?

2021-05-04 11:38:55 Monkey data analysis

For beginners sql Of , I only recommend one book Mick Of 《SQL Basic course 》. Many people on the Internet recommend 《SQL Will know 》, In fact, this book is more suitable for database operation and maintenance , For new friends , Can't understand . and Mick Of 《SQL Basic course 》 My book is easy to understand , It's much more interesting for you to learn . Interest is the reason to learn .

image.png How to import the data in this book into the database .

If you see Mick《sql Basic course 》 No 1 Chapter 1-3《SQ Summary :sql Sentences and their types 》( Corresponding to No 32 page ) Below , It's about importing the data into the database .

image.png First , Make sure you have installed mysql and mysql Graphical client navicat. If not installed , Refer to the steps here to install :

image.png

image.png Now let's talk about how to use it mysql Graphical client navicat Import into the database .

Connect mysql Server side

If you've already created the connection as shown in the previous tutorial , And successfully connected to the database .

When you turn it on navicat The created connection icon is gray , Indicates that the client is not connected to mysql Server side .

Right click on the connection name , And then click “ Open the connection ”

image.png

image.png The first 1 Step , Create database

Click on the menu bar “ Inquire about ” Under the “ New query , You can open the input sql Where the words are .

image.png And then enter the sql sentence , Click on the run , You can see sql Statement execution result .

image.png

image.png Right click connection name , choice “ Refresh ”, You can see the created database

image.png The first 2 Step , Modify database code

Because our database will store Chinese content , So you need to modify the database code , Otherwise the meeting will report a mistake . Set the database code as shown in the figure below .

image.png

image.png After revising , Click on “ determine ” Button .

Create table

database shop The icon in front of the name is gray , Indicates that the database is not currently in use .

Right click on the database name , choice “ Open database ”.

image.png Click database (shop) Query under , Right click to select “ New query ”. In the query editor opened in this way sql Statements are for the current database .

image.png

image.png In the query editor, enter the following to create the commodity table (Product) Of sql sentence , Click on “ function ” The button will execute sql sentence .

image.png

image.png

Right click “ surface ”, Choose “ Refresh ”, We can see the table created . Double click the table name , You can see the created table and sql As defined in the statement .

image.png

image.png insert data

Click on the red box below , Let's go back and write sql Statement in the query editor .

image.png Write the following inserted data in the query editor sql sentence .

image.png

image.png Don't click at this time “ function ”, Because running will take this “ Query editor ” All in sql Do it all once . But the way to create a table sql Statement we have just executed , Now all you need to do is create data sql sentence .

First , Use the mouse to select the part to be executed sql sentence .

image.png Then click on the red box below , Only the selected ones will be executed sql sentence .

image.png We can use the sql Execution results , see sql Whether there is an error in the operation , If no error is reported , Express sql The successful running .

image.png Double click the table name , We can see the data that has been inserted .

image.png ps: If you've opened a watch before , You will encounter the following problems

After running the selected code ,product Table does not generate data .

image.png terms of settlement : Close the open table first , Then reopen

image.png Let's do it in the order shown below , Put the sql Save the statement , In case of need in the future .

image.png After the save sql sentence , You can see it in the red box below

image.png

We can also create a database of sql Save the statement .

image.png We can see , The statement to create the database is saved under the connection name ( The red part of the picture ), Because this part of the statement is to create a database , Not for any specific database .

The database shop I created sql The statement is stored in its own query ( The blue part of the picture ), The statement here refers to the operation of this specific database .

image.png Double click the saved query name , You can open the query editor again , Yes sql Editing .

Export database

We export the database to a file according to the following figure . So you can share the files with others later , Other people can also use your database , Or as a backup of the database , One day the data in your database is lost , You can use this file to recover .

image.png

image.png

image.png After exporting successfully , We can see this in the path where we saved the file just now sql The script file suffix is .sql, This type of file is called sql Script files .

image.png

We use software notepad++ open sql Script files (windows use notepad++, stay mac It can be used sublime text), If you don't have this software, go to the official website to download one , This software can open all kinds of files , It's a necessary tool for technology learning .

image.png You can see sql Script files , That's what we just wrote sql sentence , There are also software export automatically added some sql sentence . therefore sql A script file is one that includes sql sentence , The file name suffix is .sql The file of . If you pay special attention , You'll find that we just exported sql In the script file , There is such a sentence sql:

DROP TABLE IF EXISTS product; in other words , If you already have this table in the database , Then delete the table and execute the following sql sentence .

Because sometimes there are tables in the database , If you don't have this sentence, you will report an error .

image.png Import sql Script files According to the operation of the figure below, just sql File into the database .

image.png Select the... To import sql Script files , Other default , Then click Start .

image.png The following message appears , Indicates that the import was successful . If it's not the information below , Indicates that there is an error in the import , Check the error information , According to the error information to solve .

After importing successfully , Click Close . Refresh the database to see the imported data .

image.png Mick《sql Basic course 》 How to import the data in the database ?

We demonstrated creating a database earlier 、 Where to write sql sentence 、 export sql Script files , Import sql Script files , got it sql How do script files come from . Now let's go back to Mick《sql Basic course 》 No 1 Chapter 1-3《SQ Summary :sql Sentences and their types 》( Corresponding to No 32 page ).

Let's see how to translate what the book says sql Script files (CreateTableProduct.sql) Import it into the database .

image.png To demonstrate the whole process , Let's delete the table we just created .

image.png The first 1 Step , modify sql Script files

use notepad++ open sql Script files , Look what's in it .

image.png We can see that it's about creating tables and adding data sql sentence .

image.png

Because sometimes importing data will report an error , When you run the import again sql Script files , The following error will be reported : The table already exists .

image.png

To prevent the above mistakes , I usually set up a table sql Add this sentence to the front (product Is the name of the table , According to your actual needs , Change it to your corresponding table name ):

-- When table exists , Delete the table first DROP TABLE IF EXISTS Product;

image.png The first 2 Step , Create database

Because we've already created the database . Then import the data , If there is no database , You need to create a database first .

The first 3 Step , Import sql Script files

Follow the steps below

image.png

image.png

The import result is not successful (Unsuccessfully), Let's see how to find out the reason according to the error information .

image.png

Put the picture below 1 Pull the drop-down box at the top , We can see that the detailed error message is :[Err] 1366 - Incorrect string value: "\xD0\xF4" for column.

Put this error message into the search engine , A solution can be found soon . The reason for the error is the coding problem .

image.png Or report the following error :

image.png terms of settlement : use notepad++ open sql Script files , Then modify it as shown in the figure below sql Script file encoding :

image.png stay notpad++ After setting up the code in , Remember to click “ preservation ” send sql Script file modification takes effect .

image.png I'll modify the coded sql The script file is saved in the following file

image.png Run the import again sql file , Successful implementation . Refresh table , You can see the imported data .

image.png summary

Through this tutorial , You have learned :

1) How to use navicat Connect mysql Server side

2) How to write in the query editor sql sentence

2) How to create a database

3) How to import sql Script files

4) How to export sql Script files

5)Mick《sql Basic course 》 How to import data from

Want to learn systematically sql You can see this :

image.png

版权声明
本文为[Monkey data analysis]所创,转载请带上原文链接,感谢
https://chowdera.com/2021/05/20210504113755306O.html