MySQL learns to create a database through files and add data

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.