4 MySQL 数据导入导出管理表记录匹配条件操作查询结果

2019-01-20 本文已影响8人 Kokoronashi

MySQL 数据导入导出管理表记录匹配条件操作查询结果

[TOC]

数据导入导出

1547913164320

设置搜索路径

5.7版本以后要求

查看

系统默认路径 /var/lib/mysql-files/,且此目录 Mysql 必须有写入权限.

mysql> show variables like "secure_file_priv";

mysql> show variables like "secure%";
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_auth      | ON                    |
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
2 rows in set (0.00 sec)

修改

mysql内直接设置变量,提示此变量为只读变量

mysql> set secure_file_priv="/dirdata/";
ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable

修改配置文件,重启修改搜索路径

[root@test ~]# mkdir /datadir && chown mysql. /datadir

[root@test ~]# vim /etc/my.cnf
[mysqld]
secure_file_priv=/datadir

[root@test ~]# systemctl restart mysqld

mysql> show variables like "secure%";
+------------------+-----------+
| Variable_name    | Value     |
+------------------+-----------+
| secure_auth      | ON        |
| secure_file_priv | /datadir/ |
+------------------+-----------+

数据导入

把系统文件的内容存储到数据库的表里

基本用法

LOAD DATA INFLE "/目录名/文件名"
INTO TABLE 表名
fields terminated by "分隔符"
lines terminated by "\n";

注意事项

字段分隔符要与文件内的一致
指定导入文件的绝对路径
导入数据的表字段类型要与文件字段匹配
禁用selinux

示例

创建表user用来存放/datadir/passwd文件中的表

mysql> create table user(
    -> name char(30),
    -> password char(1),
    -> uid smallint(2),
    -> gid smallint(2),
    -> commnet char(100),
    -> homedir char(150),
    -> shell char(150),
    -> index(name)
    -> );

mysql登陆状态下 system + 系统命令可调用系统级命令

mysql> system ls

导入数据

mysql> load data infile "/var/lib/mysql-files/passwd" into table db1.user
    -> fields terminated by ":"
    -> lines terminated by "\n";

最前面添加 id 字段设置为主键自增长

mysql> alter table user 
    -> add id int(2) primary key auto_increment first;

mysql> select * from user where id<3;
+----+------+----------+------+------+---------+---------+---------------+
| id | name | password | uid  | gid  | commnet | homedir | shell         |
+----+------+----------+------+------+---------+---------+---------------+
|  1 | root | x        |    0 |    0 | root    | /root   | /bin/bash     |
|  2 | bin  | x        |    1 |    1 | bin     | /bin    | /sbin/nologin |
+----+------+----------+------+------+---------+---------+---------------+

可重复插入多次,因为添加了id字段,显示错误的字段类型

mysql> load data infile "/dirdata/passwd" into table user
    -> fields terminated by ":"
    -> lines terminated by "\n";
ERROR 1366 (HY000): Incorrect integer value: 'root' for column 'id' at row 1

数据导出

基本用法

把表里的记录存储到系统文件里

SQL查询 INTO OUTFILE "/目录名/文件名"
fields terminated by "分隔符"
lines terminated by "\n";

注意事项

到处的内容由SQL查询语句决定
到处的是表中的记录,不包括字段名
禁用SElinux

示例

把user表前三行全部内容写入user1.csv文件.(csv文件默认以,为列分隔符)

mysql> select * from user where id<=3 into outfile "/datadir/user1.csv"
    -> fields terminated by ",";

选择特定字段,特定行导出

mysql> select id,name,password from user where id <=3
    -> into outfile "/dirdata/user2.txt";

指定 fields 和 lines 分隔符

mysql> select id,name,password from user where id <=3
    -> into outfile "/dirdata/user4.txt"
    -> fields terminated by "###"
    -> lines terminated by "___";

管理表记录

1547919042487

增加表记录

语法

insert into 库.表[(字段名列表)] values(字段值列表),(字段值列表);

注意事项

字段值要与字段类型相匹配
对于字符类型的字段,要用双或单引号括起来
依次给所有字段赋值时,字段名可以省略
只给一部分字段赋值时,必须明确写出对应的字段名称

查询表记录

语法

select 字段名列表 from 库.表 [ where 条件 ];

注意事项

使用*可匹配所有字段
指定表名时,可采用库名.表名的形式

更新表记录

语法

update 库.表 set 字段名=值,字段名=值  [ where 条件 ];

注意事项

字段值要与字段类型相匹配
对于字符类型的字段,要用双或单引号括起来
若不使用where限定条件,会更新所有记录
限定条件时,只更新匹配的条件记录

删除表记录

语法

 delete from 库.表 [ where 条件 ];

注意事项

若不使用where限定条件,会删除所有表记录.
限定条件时,只删除匹配的条件记录

匹配条件

1547920316062

基本匹配条件

数值比较

数值比较时,字段类型必须是数值类型

符号	说明
=	等于
>, >=	大于,大于或等于
<,<=	小于,小于或等于
!=	不等于

语法

where 字段名 符号 值
where 字段名 符号 字段名

示例

#查找id为3的行
mysql> select * from user where id=3;
#查找uid等于gid的用户
mysql> select name,uid,gid from user where uid=gid;
#超找uid<10的用户
mysql> select name,uid,gid from user where uid<10;
#uid<10的用户 更新 password=F homedir=student
mysql> update user set password="F",homedir="/student" where uid<10;

字符比较

字符比较时,字段类型必须是字符类型

符号	说明
=	相等
!=	不相等
IS NULL	匹配空
IS NOT NULL	非空

语法

where 字段名 符号 "值"

示例

#查询name为"mysql"的行
mysql> select * from user where name="mysql";
#查询shell不是"/bin/bash"的用户和shell
mysql> select name,shell from user where shell!="/bin/bash";
#查询uid为空的用户
mysql> select name,uid from user where uid is null;
#查询gid不为空的用户名和uid
mysql> select name,uid from user where uid is not null;
#修改bob的uid=100
mysql> update user set uid=100 where name="bob";
#修改第三行gid为空
mysql> update user set gid=null where id=3;

逻辑匹配

多个判断条件时使用
逻辑或 or 多个条件某个为真即可
与或共用时,先与后或,括号可以提升优先级

符号	说明
OR	逻辑或
AND	逻辑与
!	逻辑非
()	提高优先级

示例

#查询 name=root 且 uid=0 且 shell=/bin/bash 的用户uid和shell.
mysql> select name,uid,shell from user where name="root" and uid=0 and shell="/bin/b
ash";
+------+------+-----------+
| name | uid  | shell     |
+------+------+-----------+
| root |    0 | /bin/bash |
+------+------+-----------+

#查询 name=root 或 uid=6 或 shell=/sbin/halt 的用户uid和shell
mysql> select name,uid,shell from user where name="root" or uid=6 or shell="/sbin/hallt";
+----------+------+----------------+
| name     | uid  | shell          |
+----------+------+----------------+
| root     |    0 | /bin/bash      |
| shutdown |    6 | /sbin/shutdown |
| halt     |    7 | /sbin/halt     |
+----------+------+----------------+

#查询name=root且uid=0的用户或uid=1的用户
mysql> select name,uid,shell from user where name="root" and uid=0 or uid=1;
+------+------+---------------+
| name | uid  | shell         |
+------+------+---------------+
| root |    0 | /bin/bash     |
| bin  |    1 | /sbin/nologin |
+------+------+---------------+
2 rows in set (0.00 sec)

#查询uid=0或uid=1,且name=root的用户
mysql> select name,uid,shell from user where name="root" and uid=1 or uid=0;
+------+------+-----------+
| name | uid  | shell     |
+------+------+-----------+
| root |    0 | /bin/bash |
+------+------+-----------+

范围内匹配去重显示

匹配范围内的任意一个值即可
between 数值 and 数值包括数值本身

符号	用途
in (值列表)	在...里...
not in (值列表)	不在...里...
between 数字1 and 数字2	在...之间...
distinct 字段名	去重显示

示例

#查询用户是 nginx apache mysql adm的用户
mysql> select name,uid from user where name in ("nginx","mysql","apache","adm");
+-------+------+
| name  | uid  |
+-------+------+
| adm   |    3 |
| mysql |   27 |
+-------+------+

#查询shell不是/bin/bash和/sbin/nologin的用户和shell
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     |
+----------+----------------+

#查询uid是11,20,30,40的用户
mysql> select name,uid from user where uid in (11,20,30,40);
+----------+------+
| name     | uid  |
+----------+------+
| operator |   11 |
+----------+------+
1 row in set (0.00 sec)

#查询uid是1~4的用户
mysql> select name,uid from user where uid between 1 and 4;
+--------+------+
| name   | uid  |
+--------+------+
| bin    |    1 |
| daemon |    2 |
| adm    |    3 |
| lp     |    4 |
+--------+------+

#查询user表中的shell 去掉重复
mysql> select distinct shell from user;
+----------------+
| shell          |
+----------------+
| /bin/bash      |
| /sbin/nologin  |
| /bin/sync      |
| /sbin/shutdown |
| /sbin/halt     |
| /bin/false     |
| NULL           |
+----------------+
7 rows in set (0.00 sec)

#查询uid小于11的shell 去重
mysql> select distinct shell from user where uid<11;
+----------------+
| shell          |
+----------------+
| /bin/bash      |
| /sbin/nologin  |
| /bin/sync      |
| /sbin/shutdown |
| /sbin/halt     |
+----------------+
5 rows in set (0.00 sec)

高级匹配条件

模糊查询

基本用法

WHERE 字段名 LIKE '通配符'

_ 匹配单个字符
% 匹配 0~N 个字符, %不匹配 NULL

示例

#查询名字是4个字符且uid<10的用户
mysql> select name,uid from user where name like "____" and uid<10;
+------+------+
| name | uid  |
+------+------+
| root |    0 |
| sync |    5 |
| halt |    7 |
| mail |    8 |
+------+------+

#查询name y结尾的用户
mysql> select name from user where name like "%y";
+--------+
| name   |
+--------+
| nobody |
+--------+

正则匹配

基本用法

WHERR 字段名 regexp '正则表达式'

正则元字符

正则元字符	说明
^	匹配输入字行首
$	匹配输入行尾
.	匹配除“\n”和"\r"之外的任何单个字符
*	匹配前面的子表达式任意次
+	匹配前面的子表达式零次或一次
?	匹配前面的子表达式零次或一次
x\|y	匹配x或y
[xyz]	字符集合,匹配所包含的任意一个字符

z|food 匹配 z 或 food
[zf]ood 匹配 zood 或 food
zo* 能匹配 z zo zoo "*" 等价于 (0.)
zo+ 能匹配 zo zoo 不能匹配 z "+" 等价于 (1,)
do(es)? 匹配 do 或 does "?" 等价于 (0,1)

示例

#查询uid为两位的用户
mysql> select name,uid from user where uid regexp '^..$' limit 5;
+----------+------+
| name     | uid  |
+----------+------+
| operator |   11 |
| games    |   12 |
| ftp      |   14 |
| nobody   |   99 |
| dbus     |   81 |
+----------+------+
mysql> select name,uid from user where uid regexp '..' limit 5;
+-----------------+------+
| name            | uid  |
+-----------------+------+
| operator        |   11 |
| games           |   12 |
| ftp             |   14 |
| nobody          |   99 |
| systemd-network |  192 |
+-----------------+------+

#插入yaya9 6yaya yaya5a ya2ya用户
mysql> insert into user(name) values("yaya9"),("6yaya"),("yaya5a"),("ya2ya");
#查询用户名中包含数字的用户
mysql> select name from user where name regexp '[0-9]';
+--------+
| name   |
+--------+
| yaya9  |
| 6yaya  |
| yaya5a |
| ya2ya  |
+--------+

四则运算

符号	说明
+	加法
-	减法
*	乘法
/	除法
%	取余数(求模)

示例

#查询用户,出生年,和年纪(计算)
mysql> alter table user add birth year after name;
mysql> update user set birth=1988;
mysql> select name,birth,2019-birth age from user limit 1;
+------+-------+------+
| name | birth | age  |
+------+-------+------+
| root |  1988 |   31 |
+------+-------+------+
1 row in set (0.00 sec)

#更新birth+1
mysql> update user set birth=birth+1;

#查询uid+gid 除以 2 求pjz,限制uid大于等于10且小于等于30
mysql> select name,uid,gid,(uid+gid)/2 pjz from user where uid>=10 and uid<=30;
+----------+------+------+---------+
| name     | uid  | gid  | pjz     |
+----------+------+------+---------+
| operator |   11 |    0 |  5.5000 |
| games    |   12 |  100 | 56.0000 |
| ftp      |   14 |   50 | 32.0000 |
| mysql    |   27 |   27 | 27.0000 |
+----------+------+------+---------+

#查询uid+gid 除以3 的jsz,限制uid>=10 且 uid<=10,输出到外部文件生成csv文件
mysql> select name,uid,gid,(uid+gid)/3 jsz from user where uid>=10 and uid<=30 
    -> into outfile "/datadir/2.csv"
    -> fields terminated by ",";

操作查询结果

聚集函数

内置数据统计函数	说明
avg(字段名)	统计字段平均值
sum(字段名)	统计字段之和
min(字段名)	统计字段最小值
max(字段名)	统计字段最大值
count(字段名)	统计字段值个数

avg(字段名) sum(字段名) min(字段名) max(字段名) 为数值类型,字符值时不报错,结果没意义
count(字段名) 统计字段值个数不包含NULL

示例

#统计uid平均值
mysql> select avg(uid) from user;
+----------+
| avg(uid) |
+----------+
|  84.3810 |
+----------+

#统计uid累加和
mysql> select sum(uid) from user;
+----------+
| sum(uid) |
+----------+
|     1772 |
+----------+

#最小值uid
mysql> select min(uid) from user;
+----------+
| min(uid) |
+----------+
|        0 |
+----------+

#最大值uid
mysql> select max(uid) from user;
+----------+
| max(uid) |
+----------+
|      999 |
+----------+

#统计uid字段值个数
mysql> select count(uid) from user;
+------------+
| count(uid) |
+------------+
|         21 |
+------------+

#统计 /sbin/nologin shell的name字段有多少个
mysql> select count(name) from user where shell="/sbin/nologin";
+-------------+
| count(name) |
+-------------+
|          15 |
+-------------+

#显示有值的字段统计结果
mysql> select count(*) from user;   
+----------+
| count(*) |
+----------+
|       25 |
+----------+
1 row in set (0.00 sec)

#查看各字段统计字段值数
mysql> select count(id),count(name),count(uid) from user;
+-----------+-------------+------------+
| count(id) | count(name) | count(uid) |
+-----------+-------------+------------+
|        25 |          25 |         21 |
+-----------+-------------+------------+
1 row in set (0.00 sec)

查询结果排序

基本用法

SQL查询 ORDER BY 字段名 [ asc|desc ];

字段名通常是数值类型字段

示例

#查询用户uid,限制条件uid>=10 且 uid<=30 通过uid排序
mysql> select name,uid from user where uid>=10 and uid<=30 order by uid;
+----------+------+
| name     | uid  |
+----------+------+
| operator |   11 |
| games    |   12 |
| ftp      |   14 |
| mysql    |   27 |
+----------+------+

#查询用户uid,限制条件uid>=10 且 uid<=30 通过uid排序逆序输出
mysql> select name,uid from user where uid>=10 and uid<=30 order by uid desc;
+----------+------+
| name     | uid  |
+----------+------+
| mysql    |   27 |
| ftp      |   14 |
| games    |   12 |
| operator |   11 |
+----------+------+

查询结果分组

基本用法

SQL查询 GROUP BY 字段名;

字段名通常是字符类型字段

示例

#查询shell 限制条件 uid>100 且uid<30 
mysql> select shell from user where uid>=10 and uid<=100;
+---------------+
| shell         |
+---------------+
| /sbin/nologin |
| /sbin/nologin |
| /sbin/nologin |
| /sbin/nologin |
| /sbin/nologin |
| /sbin/nologin |
| /sbin/nologin |
| /sbin/nologin |
| /bin/false    |
| NULL          |
+---------------+

#查询shell 限制条件 uid>100 且uid<30 并用shell分组
mysql> select shell from user where uid>=10 and uid<=100 group by shell;
+---------------+
| shell         |
+---------------+
| NULL          |
| /bin/false    |
| /sbin/nologin |

查询结果过滤

基本用法

SQL查询 HAVING 条件表达式;
SQL查询 where 条件 having 条件表达式;
SQL查询 group by 字段名 having 条件表达式;

示例

#查询name的结果过滤其中含有name=yaya的用户
mysql> select name from user having name="bob";
#where条件
mysql> select name,uid from user where uid>10 having name="bob";
#group by条件
mysql> select shell from user group by shell having shell="/bin/bash";
#%代表0个或任意个字符 不匹配NULL
mysql> select id,name from user where name like '%' having name is null;
#查询shell 限制uid>=10 且uid<=100 按shell分组 过滤结果 shell=/sbin/nologin
mysql> select shell from user where uid>=30 and uid<=100 group by shell having shell="/sbin/nologin";

限制查询结果显示行数

基本用法

语法格式	说明
`SQL查询 limit N;`	显示查询结果前N条记录
`SQL查询 limit N,M;`	显示指定范围内的查询记录从N+1行开始,显示M行

示例

#查询shell 限制uid>=10 且uid<=100 按shell分组结果显示第一行
mysql> select shell from user where uid>=10 and uid<=100 group by shell limit 1;

#查询id用户名,从第4行显示3行
mysql> select id,name from user limit 3,3;
+----+------+
| id | name |
+----+------+
|  4 | adm  |
|  5 | lp   |
|  6 | sync |
+----+------+

4 MySQL 数据导入导出 管理表记录 匹配条件 操作查询结果

MySQL 数据导入导出 管理表记录 匹配条件 操作查询结果

数据导入导出

设置搜索路径

查看

修改

数据导入

基本用法

注意事项

示例

数据导出

基本用法

注意事项

示例

管理表记录

增加表记录

语法

注意事项

查询表记录

语法

注意事项

更新表记录

语法

注意事项

删除表记录

语法

注意事项

匹配条件

基本匹配条件

数值比较

语法

示例

字符比较

语法

示例

逻辑匹配

示例

范围内匹配 去重显示

示例

高级匹配条件

模糊查询

基本用法

示例

正则匹配

基本用法

正则元字符

示例

四则运算

示例

操作查询结果

聚集函数

示例

查询结果排序

基本用法

示例

查询结果分组

基本用法

示例

查询结果过滤

基本用法

示例

限制查询结果显示行数

基本用法

示例

猜你喜欢

热点阅读

4 MySQL 数据导入导出管理表记录匹配条件操作查询结果

MySQL 数据导入导出管理表记录匹配条件操作查询结果

范围内匹配去重显示