1、使用SHOW语句找出服务器上当前存在什么数据库:
SHOW DATABASES;
+----------+| Database |+----------+| mysql || test || tmp |+----------+
如果test数据库存在,尝试访问它:
mysql> USE test
Database changed
2、创建并选择数据库
mysql> CREATE DATABASE mydb;使用
USE mydb
Database changed
3 创建表,使用一个CREATE TABLE语句指定你的数据库表的布局:
mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
-> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
为了验证你的表是按你期望的方式创建,使用一个DESCRIBE语句:
mysql> DESCRIBE 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 | |
+---------+-------------+------+-----+---------+-------+4.导入csv数据
linux下:
- LOAD DATA INFILE '/home/test/dump/ip_location.csv'
- INTO TABLE ip_location
- CHARACTER SET utf8
- FIELDS TERMINATED BY ',' ENCLOSED BY '"';
--INTO TABLE :导入到哪个表
--FIELDS TERMINATED BY :以什么作为分割符
-- ENCLOSED BY :被什么包围
windows:
- LOAD DATA INFILE "d:/insert_data.csv"
- REPLACE INTO TABLE DEMO
- CHARACTER SET gb2312
- FIELDS TERMINATED BY "," ENCLOSED BY ""
- LINES TERMINATED BY "\r\n";
--LINES TERMINATED BY:这个与linux不同,以什么作为一行的结尾。
5、导出到csv文件 FIELDS TERMINATED BY ---- 字段终止字符
OPTIONALLY ENCLOSED BY ---- 封套符
LINES TERMINATED BY ---- 行终止符
通过mysql客户端shell连接到服务器,选择使用的数据库,输入sql代码:
select * from test_info
into outfile '/tmp/test.csv'
fields terminated by ',' optionally enclosed by '"' escaped by '"'
lines terminated by '\r\n';
里面最关键的部分就是格式参数
这个参数是根据RFC4180文档设置的,该文档全称Common Format and MIME Type for Comma-Separated Values (CSV) Files,其中详细描述了CSV格式,其要点包括:
(1)字段之间以逗号分隔,数据行之间以\r\n分隔;
(2)字符串以半角双引号包围,字符串本身的双引号用两个双引号表示。
6、导出到csv中 按降序排列select f2.user_id, f2.item_id, f2.weight from f2 order by f2.weight desc into outfile "G:/java/tel/fe/2.csv" fields terminated by ',' lines terminated by '\r\n' ;