Mysql database file creation method _ MySQL

Mysql database file creation method bitsCN.com

I recently found that I have forgotten about the database. my work basically does not involve mysql databases.
Years later, people are more impetuous, so I hope to find something to make up for the empty soul. MySQL database used before
Here we take it out and thoroughly study the method of creating a database using a file, which may be used later.

##### --------------------- Mysql database (1 )----------------#####
1. # create a database statement

Create database mydb default character set utf8;
# Using database statements

Use mydb;
# Create a table. only one table is created here.
# Setting InnoDB is mainly for the needs of transaction operations

Create table mytable (
Id int primary key auto_increment,
Name varchar (20 ),
Count int not null
) Type = InnoDB;

# Cmd database access method
Mysql-u root-p
# Enter the password to enter the mysql command line
# Put the preceding SQL statement in the. SQL file and import it to the database. this is a windows method.
# Note: no quotation marks can be added to the path here. you only need to put them directly after source. Otherwise, an error is returned.

# Source $ path/mydb. SQL

Mysql> source E:/mydb. SQL
Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected, 1 warning (0.09 sec)

# Describe mytable or desc mytable check whether the table field is correct
+ ------- + ------------- + ------ + ----- + --------- + ---------------- +
| Field | Type | Null | Key | Default | Extra |
+ ------- + ------------- + ------ + ----- + --------- + ---------------- +
| Id | int (11) | NO | PRI | NULL | auto_increment |
| Name | varchar (20) | YES | NULL |
| Count | int (11) | NO | NULL |
+ ------- + ------------- + ------ + ----- + --------- + ---------------- +
2. # import or export data to or from a table
First-in-first-out. txt text import method
# Example based on table fields:
Id name count
1 Zhang San 1000
2 Li Si 500
3 Wang Tiao 100
# Copy the three groups of data to the new txt text and name it mydb.txt.

Mysql> load data infile 'E:/mydb.txt 'into table mytable
-> Fields terminated by '/t' # indicates the field spacing.
-> Lines terminated by '/N'; # indicates the line spacing.
Query OK, 3 rows affected (0.03 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
# Because the data volume is small, you can view the data import information through simple queries.

Mysql> select * from mytable;
+ ---- + -------- + ------- +
| Id | name | count |
+ ---- + -------- + ------- +
| 1 | Zhang San | 1000 |
| 2 | Li Si | 500 |
| 3 | Wang Tiao | 100 |
+ ---- + -------- + ------- +
3 rows in set (0.05 sec)
# Because the Id field is automatically added, try not to add the field here
# Pre-write SQL statement: load data local infile 'E:/mydb.txt 'into table mytable (name, count );
# Import data as follows:
Zhao Da 1000
Wang Erxiao 500
Sanya sub 100

Mysql> load data local infile 'E:/mydb.txt 'into table mytable (name, count );
Query OK, 3 rows affected (0.07 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
# Query new data. the method here is only applicable when the data size is small.
Mysql> select * from mytable;
+ ---- + -------- + ------- +
| Id | name | count |
+ ---- + -------- + ------- +
| 1 | Zhang San | 1000 |
| 2 | Li Si | 500 |
| 3 | Wang Tiao | 100 |
| 4 | Zhao Da | 1000 |
| 5 | Mr. Wang | 500 |
| 6 | Sanya | 100 |
+ ---- + -------- + ------- +
6 rows in set (0.00 sec)
# We can see that three rows of data are added and the ID is auto-incrementing.

# Here we will try to export the table data to the txt file. here we have the data to be exported. If all the data is exported, it is similar
# The following method will report an error
Select * into outfile 'E:/mydb1.txt'
Lines terminated by '/r/N'
Fields terminated by '/t'
From mytable;

# There is a strange phenomenon in this place. fields rows must be added to lines, that is, the following operation will not report an error:
Mysql> select * into outfile 'E:/mydb1.txt'
-> Fields terminated by '/t' # In this example, this row is optional.
-> Lines terminated by '/r/N'
-> From mytable;
Query OK, 6 rows affected (0.00 sec)
# Exporting data does not include table fields.

##### -------------------- Mysql split line ----------------------#####
Here we will first introduce these things, but they are relatively more practical.

BitsCN.com