一: 创建数据库 方法一:
[root@host]# mysqladmin -u root -p create database1 Enter password:****** mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | database1 | | mysql | | performance_schema | +--------------------+ 4 rows in set (0.00 sec)方法二:
mysql> create database database2; mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | database1 | | database2 | | mysql | | performance_schema | +--------------------+ 5 rows in set (0.00 sec)二: 建立资料表 mysql> use database1; Database changed mysql> show tables; Empty set (0.00 sec)
这时候需要你去决定你需要什么样的资料库资料表, 各个资料库资料表中有什么样的列. (如果后期相对数据表格式进行修改, 增加列,删除列,修改列的名字之类的) 这个中间涉及到了mysql中数据类型的一些问题 http://www.runoob.com/mysql/mysql-data-types.html 其中name, owner, species我们使用的是VARCHAR变长字符串, 大小未0-65535个字节, sex使用的是单字节的定长字符串, birth, death使用的是DATE日期类型.
mysql> create table pet (name VARCHAR(20), owner VARCHAR(20), -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE); Query OK, 0 rows affected (0.07 sec) mysql> show tables; +---------------------+ | Tables_in_database1 | +---------------------+ | pet | +---------------------+ 1 row in set (0.00 sec) mysql> describe pet; / desc pet; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | name | varchar(20) | YES | | NULL | | | owner | varchar(20) | YES | | NULL | | | species | varchar(20) | YES | | NULL | | | sex | char(1) | YES | | NULL | | | birth | date | YES | | NULL | | | death | date | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 6 rows in set (0.00 sec)三: 填写数据表内容 (重点) 通过LOAD DATA和INSERT语句可以完成。 方法一: 直接load一个文本 (貌似还可以导入csv格式文件, 需要研究一下) 基本语法:
load data [low_priority | concurrent] [local] infile 'filename' [REPLACE | IGNORE] INTO TABLE tbl_name [CHARACTER SET charset_name] [{FIELDS | COLUMNS} [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char'] ] [LINES [STARTING BY 'string'] [TERMINATED BY 'string'] ] [IGNORE number {LINES | ROWS}] [(col_name_or_user_var,...)] [SET col_name = expr,...]其中: https://dev.mysql.com/doc/refman/5.5/en/load-data.html (每个参数权威的解释在官方文件中, 这里直接是我理解的,有些我没用过/不理解的就不说了.) 在读取位于服务器上的文件时, 文件必须处于服务器目录或者是要有对文件的读取权限. low_priority 没有用户在读这个文件的时候,执行load data. local 表示从client host上面读文件, 如果local未指定, 则文件必须位于server host上面. replace 输入行内容将代替已存在的行的内容. ignore 遇到有唯一键值的行则跳过. FIELDS | COLUMNS关键字: terminated by描述字段的分隔符，默认情况下是tab字符（\t） enclosed by描述的是字段的括起字符。 escaped by描述的转义字符。默认的是反斜杠（backslash：\ ） LINES关键字: 每条记录的分隔符默认为’\n’即为换行符. 这个版本默认的格式是每个数值之间用tab键隔开,然后每一组数据之间用enter键隔开, 没有数值的项用\N来表示(显示出来的就是NULL). 如下是我在txt文件中输入的结果:
Fluffy Harold cat f 1993-02-04 \N Claws Gwen cat m 1994-03-17 \N Buffy Harold dog f 1989-05-13 \N Fang Benny dog m 1990-08-27 \N Bowser Diane dog m 1979-08-31 \N Chirpy Gwen bird \N 1997-12-09 \N Whistler Gwen bird \N 1997-12-09 \N Slim Benny snake m 1996-04-29 \N你也可以自己在后面定义自己觉得习惯的格式. 例如我这里想尝试用用””表示字段括起字符, 逗号表示字段分隔符, 换行符\表示每个记录的分隔符.
mysql>load data local infile '/path/Documents/pet.txt' into table pet fields terminated by ',' enclosed by ' '' ' lines terminated by '/n';注意: 同时还遇到了一个问题:
mysql> load data local infile '/path/Documents/pet.txt' into table pet; ERROR 1148 (42000): The used command is not allowed with this MySQL version貌似是个security issues: https://stackoverflow.com/questions/18437689/error-1148-the-used-command-is-not-allowed-with-this-mysql-version https://stackoverflow.com/questions/16285864/how-can-i-correct-mysql-load-error 只需要退出,再用如下命令登录就好了
mysql -u root -p --local-infile **加上--local-infile**查看导入结果:
mysql> load data local infile '/path/Documents/pet.txt' into table pet; Query OK, 8 rows affected (0.03 sec) Records: 8 Deleted: 0 Skipped: 0 Warnings: 0 mysql> select * from pet; +----------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +----------+--------+---------+------+------------+-------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Fang | Benny | dog | m | 1990-08-27 | NULL | | Bowser | Diane | dog | m | 1979-08-31 | NULL | | Chirpy | Gwen | bird | NULL | 1997-12-09 | NULL | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | | Slim | Benny | snake | m | 1996-04-29 | NULL | +----------+--------+---------+------+------------+-------+ 8 rows in set (0.00 sec)方法二: 利用insert语句 上面是批量的导入数据的方法, 如果你希望一次导入一组或者一个新数据的话.(当然load data也是可以指定导入部分内容的) 最简单的形式是，提供每一列的值，其順序与CREATE TABLE语句中列的順序相同。假定Diane把一个新仓鼠命名为Puffball，可以使用下面的INSERT语句新增一条新纪录：
mysql> insert into pet -> values ('Puffball','Diane','hamster','f','1999-03-30',NULL); Query OK, 1 row affected (0.09 sec) mysql> select * from pet; +----------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +----------+--------+---------+------+------------+-------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Fang | Benny | dog | m | 1990-08-27 | NULL | | Bowser | Diane | dog | m | 1979-08-31 | NULL | | Chirpy | Gwen | bird | NULL | 1997-12-09 | NULL | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | | Slim | Benny | snake | m | 1996-04-29 | NULL | | Puffball | Diane | hamster | f | 1999-03-30 | NULL | +----------+--------+---------+------+------------+-------+ 9 rows in set (0.01 sec)注意: 这里字串和日期值均为引号括起来的字串。另外，可以直接用INSERT語句插入NULL表示资料表不存在的值。不能使用LOAD DATA中所示的的\N。
