DBAmysql数据导入导出管理表记录匹配条件_万金油

本文链接：https://blog.csdn.net/weixin_42816196/article/details/109655451

Mysql 的体系结构

导出导入的路径目录

[root@host50 ~]# ls /var/lib/mysql-files/ ——目录是安装软件的时候就会创建

mysql> show variables like "secure_file_priv"; ——系统默认的路径，可以自定义修改

+------------------+-----------------------+

| Variable_name | Value |

+------------------+-----------------------+

| secure_file_priv | /var/lib/mysql-files/ |

+------------------+-----------------------+

1 row in set (0.01 sec)

自定义数据库的导入导出文件存储目录

[root@host50 ~]# mkdir /mydata

[root@host50 ~]# chown mysql /mydata/ ——把所属者和所属组修改成mysql

[root@host50 ~]# vim /etc/my.cnf ————修改配置文件，添加指定的目录

[mysqld]

secure_file_priv="/mydata"

:wq

[root@host50 ~]# systemctl stop mysqld——重起失败可以查看防火墙和SEliunx是否关闭

[root@host50 ~]# systemctl restart mysqld

mysql> show variables like "secure_file_priv";

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 3

Current database: db3

+------------------+----------+

| Variable_name | Value |

+------------------+----------+

| secure_file_priv | /mydata/ |

+------------------+----------+

1 row in set (0.02 sec)

导入数据的步骤：

1.把文件拷贝导mysql的指定目录下，也可以在数据库的服务里用sql命令执行

mysql> system cp /etc/passwd /mydata/ ————不用分号结束

mysql> system ls /mydata

passwd

mysql> system head -2 /mydata/passwd

root:x:0:0:root:/root:/bin/bash

bin:x:1:1:bin:/bin:/sbin/nologin

2.创建表给表赋值，给信息做索引

mysql> create table user( name char(80), password char(1), uid int(2), gid int(2), comment varchar(150),homedir char(150), shell char(50), index(name));

Query OK, 0 rows affected (0.24 sec)

mysql> desc user;

+----------+--------------+------+-----+---------+-------+

+----------+--------------+------+-----+---------+-------+

| uid | int(2) | YES | | NULL | |

| gid | int(2) | YES | | NULL | |

+----------+--------------+------+-----+---------+-------+

7 rows in set (0.00 sec)

mysql> select * from user;

Empty set (0.00 sec)

复合索引的创建？？

3.导入数据用sql命令导入数据

mysql> load data infile "/mydata/passwd" into table db3.user fields terminated by ":" lines terminated by "\n";

\n 是换行的意思

Query OK, 41 rows affected (0.06 sec)

Records: 41 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from user;

mysql> alter table user add id int(2) primary key auto_increment first;——插入id号，方便查找信息

Query OK, 0 rows affected (0.51 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from user;

mysql> select * from user where id=15; ————查找表里信息

+----+------+----------+------+------+--------------------+---------+---------------+

+----+------+----------+------+------+--------------------+---------+---------------+

| 15 | dbus | x | 81 | 81 | System message bus | / | /sbin/nologin |

+----+------+----------+------+------+--------------------+---------+---------------+

1 row in set (0.00 sec)

数据的导出：把数据库服务器的表里记录存储导系统的文件里

1.用sql命令导出数据

mysql SQL查询命令 into outfile "/mydata/文件名"

mysql> select * from t1;

+----+-------+------+----------+

| id | name | age | pay |

+----+-------+------+----------+

| 1 | tom | 21 | 1800.00 |

| 2 | lucy | 25 | 18800.00 |

| 7 | jerry | 33 | 18000.00 |

| 8 | alice | 33 | 18000.00 |

| 9 | jerry | 23 | 18000.00 |

+----+-------+------+----------+

5 rows in set (0.00 sec)

mysql> select * from t1 into outfile "/mydata/t1.txt"; ——导出数据，不指定分隔符，就是默认的tab

Query OK, 5 rows affected (0.00 sec)

[root@host50 ~]# ls /mydata/t1.txt

/mydata/t1.txt

[root@host50 ~]# cat /mydata/t1.txt

1 tom 21 1800.00

2 lucy 25 18800.00

7 jerry 33 18000.00

8 alice 33 18000.00

9 jerry 23 18000.00

mysql> select * from t1 into outfile "/mydata/t2.txt" fields terminated by "#"; ——导出数据，指定分隔符，以#号进行分割

Query OK, 5 rows affected (0.00 sec)

[root@host50 ~]# cat /mydata/t2.txt

1#tom#21#1800.00

2#lucy#25#18800.00

7#jerry#33#18000.00

8#alice#33#18000.00

9#jerry#23#18000.00

mysql> select * from t1 into outfile "/mydata/t3.txt" fields terminated by "#" lines terminated by "!!!";

Query OK, 5 rows affected (0.00 sec)

——导出数据，指定以#号为分割符，行以！号为分割符

[root@host50 ~]# cat /mydata/t3.txt

1#tom#21#1800.00!!!2#lucy#25#18800.00!!!7#jerry#33#18000.00!!!8#alice#33#18000.00!!!9#jerry#23#18000.00!!!

导出数据是不包获，数据的字段名导出数据不可以与目录下文件同名

mysql> select * from t1;

+----+-------+------+----------+

| id | name | age | pay |

+----+-------+------+----------+

| 1 | tom | 21 | 1800.00 |

| 2 | lucy | 25 | 18800.00 |

| 7 | jerry | 33 | 18000.00 |

| 8 | alice | 33 | 18000.00 |

| 9 | jerry | 23 | 18000.00 |

+----+-------+------+----------+

管理表记录

添加新记录 insert into

添加一条记录

insert into 表名 values （字段值列表）

mysql> insert into user values(42,"bob","x",2000,2000,"student user","/home/bob","/bin/bash");

Query OK, 1 row affected (0.03 sec)

添加多条记录

insert into 表名 values （字段值列表），（字段值列表）

mysql> insert into user values(43,"bob","x",2000,2000,"student user","/home/bob","/bin/bash")

-> ,(44,"bob","x",2000,2000,"student user","/home/bob","/bin/bash"),(45,"bob","x",2000,2000,"student user","/home/bob","/bin/bash");

Query OK, 3 rows affected (0.02 sec)

Records: 3 Duplicates: 0 Warnings: 0 ——可以随便改变id 但是添加的类型一定要跟表结构一致

mysql> select * from user;

| 42 | bob | x | 2000 | 2000 | student user | /home/bob | /bin/bash |

| 43 | bob | x | 2000 | 2000 | student user | /home/bob | /bin/bash |

| 44 | bob | x | 2000 | 2000 | student user | /home/bob | /bin/bash |

| 45 | bob | x | 2000 | 2000 | student user | /home/bob | /bin/bash |

添加一条记录指定字段赋值

insert into 表名（字段名列表） values （字段值列表）

mysql> insert into user (name,shell,uid) values("lucy","/sbin/nologin",1998);

Query OK, 1 row affected (0.04 sec)

添加多条记录指定字段赋值

insert into 表名（字段名列表）values （字段值列表），（字段值列表）

mysql> insert into user (name,shell,uid) values("alice","/sbin/nologin",1948);

Query OK, 1 row affected (0.03 sec)

mysql> select * from user;——添加了外键可以自动添加id号

查询记录select

select 字段名列表 from 表 where 条件;

select * from 表名 * 代表所有行所有列

mysql> select name,uid,shell from user;

mysql> select name,uid,shell from user where id=1;

mysql> select name,uid,shell from user where shell="/bin/bash";

无非就是这三种查询方式，变化最多是后面的条件

更新记录字段的值 update

主要是更新那些列的数据

update 表 set 字段名=值，字段名=值，。。。 where 条件;

mysql> update user set password="A",gid=1000;

mysql> select "password","gid" from user;

+----------+-----+

| password | gid |

+----------+-----+

| password | gid |

mysql> select password,gid from user;

+----------+------+

| password | gid |

+----------+------+

| A | 1000 |

mysql> update user set password="x" where id=1;

Query OK, 1 row affected (0.03 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select password,gid from user;

+----------+------+

| password | gid |

+----------+------+

| x | 1000 |

mysql> select password,gid from user where id=1;

+----------+------+

| password | gid |

+----------+------+

| x | 1000 |

+----------+------+

1 row in set (0.00 sec)

删除记录 delete

delete from 表 where 条件；

mysql> select * from user where id=45;

+----+------+----------+------+------+--------------+-----------+-----------+

+----+------+----------+------+------+--------------+-----------+-----------+

| 45 | bob | A | 2000 | 1000 | student user | /home/bob | /bin/bash |

+----+------+----------+------+------+--------------+-----------+-----------+

mysql> delete from user where name="bob";——按添加删除记录

Query OK, 4 rows affected (0.03 sec)

mysql> select * from user where id=45;

Empty set (0.00 sec)

匹配条件的表示方式(select update delete)

对记录做select update delete时可以使用的条件表示方式：

基本匹配条件

数值比较字符比较> >= = < <= ！=

where 字段名符号数值

mysql> select * from user where id <=10;

+----+----------+----------+------+------+----------+-----------------+----------------+

+----+----------+----------+------+------+----------+-----------------+----------------+

| 1 | root | x | 0 | 1000 | root | /root | /bin/bash |

| 2 | bin | A | 1 | 1000 | bin | /bin | /sbin/nologin |

| 3 | daemon | A | 2 | 1000 | daemon | /sbin | /sbin/nologin |

| 4 | adm | A | 3 | 1000 | adm | /var/adm | /sbin/nologin |

| 5 | lp | A | 4 | 1000 | lp | /var/spool/lpd | /sbin/nologin |

| 6 | sync | A | 5 | 1000 | sync | /sbin | /bin/sync |

| 7 | shutdown | A | 6 | 1000 | shutdown | /sbin | /sbin/shutdown |

| 8 | halt | A | 7 | 1000 | halt | /sbin | /sbin/halt |

| 9 | mail | A | 8 | 1000 | mail | /var/spool/mail | /sbin/nologin |

| 10 | operator | A | 11 | 1000 | operator | /root | /sbin/nologin |

+----+----------+----------+------+------+----------+-----------------+----------------+

10 rows in set (0.00 sec)

查看记录修改记录

mysql> select name,uid,gid from user where uid=gid;

+------+------+------+

| name | uid | gid |

+------+------+------+

| lisi | 1000 | 1000 |

mysql> select name from user where name="apache";

Empty set (0.00 sec)

mysql> select name,shell from user where shell !="/bin/bash";——按条件查找记录

+---------------------+----------------+

| name | shell |

+---------------------+----------------+

| bin | /sbin/nologin |

| daemon | /sbin/nologin |

| adm | /sbin/nologin |

mysql> update user set gid=2013 where shell !="/bin/bash";——按条件修改记录

Query OK, 41 rows affected (0.03 sec)

Rows matched: 41 Changed: 41 Warnings: 0

mysql> select name,shell,gid from user where shell !="/bin/bash";——按条件修改记录

+---------------------+----------------+------+

| name | shell | gid |

+---------------------+----------------+------+

| bin | /sbin/nologin | 2013 |

| daemon | /sbin/nologin | 2013 |

| adm | /sbin/nologin | 2013 |

修改空和非空

匹配空 is null 匹配非空 is not null

先写入几条记录：

mysql> insert into user(id,name)values(51,""),(52,"null"),(53,null);

Query OK, 3 rows affected (0.04 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> select id,name from user where id>=51;

+----+------+

| id | name |

+----+------+

| 51 | |

| 52 | null |

| 53 | NULL |

+----+------+

3 rows in set (0.00 sec)

mysql> select name,shell from user where shell is not null;

+---------------------+----------------+

| name | shell |

+---------------------+----------------+

| root | /bin/bash |

| bin | /sbin/nolog

mysql> select name,shell from user where shell is null;

+------+-------+

| name | shell |

+------+-------+

| | NULL |

| null | NULL |

| NULL | NULL |

+------+-------+

mysql> select * from user where name="bin";

+----+------+----------+------+------+---------+---------+---------------+

+----+------+----------+------+------+---------+---------+---------------+

| 2 | bin | A | 1 | 2013 | bin | /bin | /sbin/nologin |

+----+------+----------+------+------+---------+---------+---------------+

1 row in set (0.01 sec)

mysql> update user set password=null where name= "bin";

Query OK, 1 row affected (0.06 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from user where name="bin";

+----+------+----------+------+------+---------+---------+---------------+

+----+------+----------+------+------+---------+---------+---------------+

| 2 | bin | NULL | 1 | 2013 | bin | /bin | /sbin/nologin |

+----+------+----------+------+------+---------+---------+---------------+

1 row in set (0.00 sec)

逻辑比较

OR 逻辑或一个条件成立就可以

mysql> select name,uid from user where name="root" or uid=1;

+------+------+

| name | uid |

+------+------+

| root | 0 |

| bin | 1 |

+------+------+

2 rows in set (0.00 sec)

AND逻辑与两个条件同时成立

mysql> select * from user where name="root" and uid=1 and shell="/bin/bash";

Empty set (0.00 sec)

！逻辑非取反的意思

（）提高优先等级 ——优先执行默认是与优先于或

mysql> select name,uid from user where name="root" or name="bin" and uid=1; ——先执行与，再执行或

+------+------+

| name | uid |

+------+------+

| bin | 1 |

| root | 0 |

+------+------+

2 rows in set (0.00 sec)

mysql> select name,uid from user where (name="root" or name="bin") and uid=1;——优先执行或，再执行与

+------+------+

| name | uid |

+------+------+

| bin | 1 |

+------+------+

1 row in set (0.00 sec)

范围内匹配/去重显示

一。in 值列表 ——在。。。范围内

mysql> select name from user where name in ("root","sync","lucy","bob");

+------+

| name |

+------+

| lucy |

| root |

| sync |

+------+

3 rows in set (0.00 sec)

二。not in 值列表 ——不在。。。范围内

mysql> select name,shell from user where shell not in ("/bin/bash","/sbin/nologin");

+----------+----------------+

| name | shell |

+----------+----------------+

| sync | /bin/sync |

| shutdown | /sbin/shutdown |

| halt | /sbin/halt |

| mysql | /bin/false |

+----------+----------------+

4 rows in set (0.00 sec)

三。between 数字1 and 数字2 ——在什么之间

mysql> select * from user where uid between 10 and 20;

+----+----------+----------+------+------+----------+------------+---------------+

+----+----------+----------+------+------+----------+------------+---------------+

| 10 | operator | A | 11 | 2013 | operator | /root | /sbin/nologin |

| 11 | games | A | 12 | 2013 | games | /usr/games | /sbin/nologin |

| 12 | ftp | A | 14 | 2013 | FTP User | /var/ftp | /sbin/nologin |

+----+----------+----------+------+------+----------+------------+---------------+

四。distinct （只适合select查询使用） ——去重显示

mysql> select shell from user;

mysql> select distinct shell from user; ——测试这两种查找命令的不一样

mysql> select shell from user where uid <=500;

mysql> select distinct shell from user where uid <=500;——测试两种查找命令的结果

高级匹配条件

模糊查询

where 字段名 like '表达式' _匹配任意一个字符 %匹配零个或多个字符

select name from user where name like ' ___'

select name from user where name like '%a%';

select name from user where name like '%';——这两条命令是一样的

select name from user where name like="";

mysql> select name from user where name is null;——字符不为空

正则表达式

where 字段名 regexp “正则表达式”;

正则表达式符号 ^ * $ | . []

mysql> select name from user where name regexp '^a.t*$';

Empty set (0.00 sec)

mysql> select name from user where name regexp '[0-9]';

Empty set (0.00 sec)

mysql> insert into user(name) values ("ya9ya"),("7yaya"),("yaya8");

Query OK, 3 rows affected (0.02 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> select name from user where name regexp '[0-9]';

+-------+

| name |

+-------+

| 7yaya |

| ya9ya |

| yaya8 |

+-------+

3 rows in set (0.00 sec)

mysql> select name,uid from user where uid regexp '....';

+-----------+-------+

| name | uid |

+-----------+-------+

| nfsnobody | 65534 |

| lisi | 1000 |

| bob | 2000 |

| lucy | 1998 |

| alice | 1948 |

| bob | 2000 |

+-----------+-------+

7 rows in set (0.00 sec)

mysql> select name,uid from user where uid regexp '^....$';

+-------+------+

| name | uid |

+-------+------+

| lisi | 1000 |

| bob | 2000 |

| lucy | 1998 |

| alice | 1948 |

| bob | 2000 |

+-------+------+

6 rows in set (0.01 sec)

四则运算 +-*/%（字段类型必须是数值类型）

mysql> select name,uid from user where id<=10;

+----------+------+

| name | uid |

+----------+------+

| root | 0 |

| bin | 1 |

| daemon | 2 |

| adm | 3 |

| lp | 4 |

| sync | 5 |

| shutdown | 6 |

| halt | 7 |

| mail | 8 |

| operator | 11 |

+----------+------+

10 rows in set (0.00 sec)

mysql> update user set uid=uid+1 where id<=10

-> ;

Query OK, 10 rows affected (0.02 sec)

Rows matched: 10 Changed: 10 Warnings: 0

mysql> select name,uid from user where id<=10;

+----------+------+

| name | uid |

+----------+------+

| root | 1 |

| bin | 2 |

| daemon | 3 |

| adm | 4 |

| lp | 5 |

| sync | 6 |

| shutdown | 7 |

| halt | 8 |

| mail | 9 |

| operator | 12 |

+----------+------+

10 rows in set (0.01 sec)

mysql> select name,uid,gid,uid+gid jirguo from user where name="root";

+------+------+------+--------+

| name | uid | gid | jirguo |

+------+------+------+--------+

| root | 1 | 1000 | 1001 |

+------+------+------+--------+

1 row in set (0.00 sec)

mysql> select name,uid,gid,"uid"+"gid" jirguo from user where name="root";

+------+------+------+--------+

| name | uid | gid | jirguo |

+------+------+------+--------+

| root | 1 | 1000 | 0 |

+------+------+------+--------+

数据存储引擎 ***

数据的导入：把系统文件的内容存储导数据库服务器的表里

有格式规律

管理表记录 select update insert delete

DBAmysql数据导入导出管理表记录匹配条件_万金油_新浪博客