MySQL learns to create a database through files and add data

Turn from: http://blog.163.com/[email protected]/blog/static/170171558201411311547655/

1. # CREATE DATABASE statement

Create DATABASE mydb default character set UTF8;
# using Database statements

Use MyDB;
# Create a table, here simply create a table
# Setting up InnoDB is primarily for the needs of transactional operations

CREATE TABLE MyTable (
ID int primary KEY auto_increment,
Name varchar (20),
count int NOT NULL
) Type=innodb;

# cmd into the database method
Mysql-u root-p
#输入密码进入mysql命令行
#将以上sql语句放到. sql file, and import the database, here is the Windows way
#注意: Here the path can not be added quotation marks, just need to put directly to the source, or error

#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 the table field for errors
+-------+-------------+------+-----+---------+----------------+
| 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. #向表格中导入数据或者导出数据
#首先采用. txt Text Import method
#按照表格字段进行示例如下:
ID Name Count
1 Sheets 31,000
2 Li 4,500
3 King Tiger 100
#将三组数据复制放到新建txt文本中, this is named: Mydb.txt

mysql> load Data infile ' E:\mydb.txt ' into table mytable
Fields terminated by ' \ t ' #表示字段间距
-lines terminated by ' \ n '; #表示行间距
Query OK, 3 rows affected (0.03 sec)
Records:3 deleted:0 skipped:0 warnings:0
#由于数据较少这里通过简单的查询可以查看数据导入信息

Mysql> select * FROM MyTable;
+----+--------+-------+
| ID | name | Count |
+----+--------+-------+
| 1 |  Zhang San | 1000 |
| 2 |   John Doe | 500 |
| 3 |   Tiger King | 100 |
+----+--------+-------+
3 rows in Set (0.05 sec)
#由于Id字段是自动增加的, so try not to add fields here
#预编写sql语句: Load Data local infile ' E:\mydb.txt ' into table MyTable (Name,count);
#导入数据为:
Zhao da 1000
Wang er Xiao 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
#查询新数据, the method here is only suitable for small data situations
Mysql> select * FROM MyTable;
+----+--------+-------+
| ID | name | Count |
+----+--------+-------+
| 1 |  Zhang San | 1000 |
| 2 |   John Doe | 500 |
| 3 |   Tiger King | 100 |
| 4 |  Zhao Da | 1000 |
| 5 |   Wang Two small | 500 |
| 6 |   Sanya Sub | 100 |
+----+--------+-------+
6 rows in Set (0.00 sec)
#可以看到确实增加了三行数据, and the ID is self-increasing.
#这里尝试一下将表中数据导出到txt文件中, here we have the option to export the data if all the exports are similar
#下面方式会报错
SELECT * into outfile ' E:\mydb1.txt '
Lines terminated by ' \ r \ n '
Fields terminated by ' \ t '
From MyTable;
#这个地方有一个奇特的现象fields行必须添加到lines上面即如下操作才不会报错:
Mysql> select * into outfile ' E:\mydb1.txt '
Fields terminated by ' \ t ' #在此例中该行可有可无
-lines terminated by ' \ r \ n '
From MyTable;
Query OK, 6 rows Affected (0.00 sec)
#导出数据并不包括表字段.

MySQL learns to create a database through files and add data