MySQL数据导入导出 、 管理表记录 、 匹配条件 、 MySQL管理工具

1数据导入导出 （批量管理数据）
1.1 检索目录（导入导出数据时调用的文件必须在此目录里）
查看目录
mysql> show variables ；看所有变量
mysql> show variables like “%file%” ; 模糊查找
mysql> show variables like “secure_file_priv” ; 查看指定变量的值
±-----------------±----------------------+
| Variable_name | Value |
±-----------------±----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
±-----------------±----------------------+
1 row in set (0.00 sec)

mysql>

	修改目录

]# vim /etc/my.cnf
[mysqld]
secure_file_priv="/myload"
:wq
[root@host50 ~]# mkdir /myload
[root@host50 ~]# chown mysql /myload
[root@host50 ~]# ls -ld /myload/
drwxr-xr-x. 2 mysql root 6 8月 15 10:45 /myload/
[root@host50 ~]# setenforce 0
[root@host50 ~]# systemctl restart mysqld
]# mysql -uroot -p密码
mysql> show variables like “secure_file_priv” ;
±-----------------±---------+
| Variable_name | Value |
±-----------------±---------+
| secure_file_priv | /myload/ |
±-----------------±---------+

1.2 数据导入：把系统文件的内容存储到表里
	命令格式？
	mysql>  load  data  infile "/目录名/文件名"  into   table  库名.表名

fields terminated by “符号” lines terminated by “\n” ;
注意事项？
数据导入的操作步骤：
1 根据导入文件的内容创建符合存储数据的表结构
2 把系统文件拷贝到检索目录下
3 执行导入数据的命令
4 查看数据

例子： 把/etc/passwd文件的尼尔存储到 db3库下user表里
mysql> create database db3;
mysql> create table db3.user(name char(30) , password char(1) , uid int , gid int , comment varchar(200) , homedir char(60) , shell char(50) );
mysql> select * from db3.user;
mysql> system cp /etc/passwd /myload/

mysql> system ls /myload
passwd

mysql> load data infile “/myload/passwd” into table db3.user
-> fields terminated by “:” lines terminated by “\n” ;

mysql> select * from db3.user;

mysql> alter table db3.user add id int primary key auto_increment first;

mysql> select * from db3.user;
mysql> select * from db3.user where id=12;

1.3 数据导出： 把表里数据存放到系统文件里
	注意事项？
	命令格式1 
	select 查询   into  outfile  "/目录名/文件名";

mysql> select * from db3.user into outfile “/myload/a.txt” ;
mysql> system ls /myload/
a.txt passwd
mysql> system cat /myload/a.txt

	命令格式2 
	select 查询   into  outfile  "/目录名/文件名"   fields terminated by  "符号" ;

mysql> select * from db3.user into outfile “/myload/b.txt” fields terminated by “:” ;
mysql> system ls /myload/
a.txt b.txt passwd
mysql>
mysql> system cat /myload/b.txt

	命令格式3
	select 查询   into  outfile  "/目录名/文件名"   fields terminated by  "符号"    lines   terminated by  "符号"   ;

mysql> select name , homedir , shell from db3.user where id <= 3 into outfile “/myload/d.txt” fields terminated by “#” lines terminated by “!!!”;

mysql> system cat /myload/d.txt
root#/root#/bin/bash!!!bin#/bin#/sbin/nologin!!!daemon#/sbin#/sbin/nologin!!!mysql>

2管理表记录
2.1 插入表记录（添加新行）insert into
mysql> insert into db3.user values (21,“a”,“x”,3000,3000,“test user” , “/bin/bash”, “/bin/bash”);

mysql> insert into db3.user values
(22,“b”,“x”,3000,3000,“test user” , “/bin/bash”, “/bin/bash”),(25,“C”,“y”,3001,3001,“test user”,"/home/c","/sbin/nologin");

mysql> insert into db3.user (name,uid) values (“bob2”,2009)；

mysql> insert into db3.user (name,uid) values (“bob”,2009),(“jerry”,3008),(“lilei”,2020);

2.2 查询表记录 select
	select  字段名列表   from  库名.表名；
	select  字段名列表   from  库名.表名 where  条件；

	字段名列表 控制显示的列
	条件控制显示的行。

	select  * from  db3.user;  查看所有行所有列
	select  name , shell , homedir   from  db3.user;
	select  name , shell , homedir   from  db3.user where  id <= 5;

2.3 更新表记录（修改行中列的值） update
	update  库.表   set 列名=值，列名=值  ； 修改所有行列的值
	update  库.表   set 列名=值，列名=值   where  条件；只修改与条件匹配行  列的值

mysql> update db3.user set password=“A”;
mysql> update db3.user set password=“x” , comment=“admin” where name = “root” ;

2.4 删除表记录 d