数据导入: 把系统文件的内容存储到数据库服务器的表里
load data infile “目录名/文件名” into table 库名.表名
FIELDS TERMINATED BY “分隔符” LINES TERMINATED
BY “\n”;
load data infile "/etc/passwd" into table userdb.user
fields terminated by ":" lines terminated by "\n";
例子:把系统用户信息存储到数据库服务器的表里
/etc/passwd
用户名:密码站位符号:uid:gid:描述信息:家目录:shell
create database userdb;
create table userdb.user(
name varchar(35),
password char(1),
uid int(2),
gid int(2),
comment varchar(60),
homedir varchar(60),
shell varchar(30),
index(name)
);
mysql> show variables like "secure_file_priv";
#cp /etc/passwd /var/lib/mysql-files/
mysql > load data infile "/var/lib/mysql-files/passwd"
into table userdb.user fields terminated by ":"
lines terminated by "\n";
mysql> alter table userdb.user add id int(2) zerofill
primary key auto_increment first;
自定义数据导入时文件所在的目录
#mkdir /mydata
#chown mysql /mydata
#setenforce 0
#cp /etc/passwd /mydata/
vim /etc/my.cnf
[mysqld]
secure_file_priv= /mydata
:wq
#systemctl restart mysqld
数据导出:把表记录存储到系统文件里
mysql> show variables like "secure_file_priv";
sql查询命令 INTO OUTFILE “目录名/文件名“;
sql查询命令 INTO OUTFILE “目录名/文件名“ fields
terminated by "符号" lines terminated by "符号";
mysql>select * from userdb.user where id=18 into
outfile "/var/lib/mysql-files/user1.txt";
mysql> system cat /var/lib/mysql-files/user1.txt
++++++++++++++++++++++++++++++++++
管理表记录(增 查 改 删)(userdb.user)
insert into 增加表记录
一次插入1条记录给所有字段赋值
insert into 库名.表名 values(值列表);
一次插入N条记录给所有字段赋值
insert into 库名.表名 values(值列表),(值列表),(值列表);
插入记录时只给指定的字段赋值
insert into 库名.表名(字段名列表)values(值列表);
insert into 库名.表名(字段名列表)values(值列表),(值列表),(
值列表);
insert into userdb.user values
(50,"bob","x",2022,2022,"this is
student","/home/bob","/bin/bash");
insert into userdb.user values
(60,"bob","x",2122,2122,"this is
student","/home/bob","/bin/bash"),
(63,"lucy","x",2322,2322,"this is
student","/home/lucy","/bin/bash");
insert into userdb.user(name,uid)values("tom",2300),
("alic",2987);