mysql导出dat文件linux命令,基于Linux的MySQL操作实例之数据导入/导出，新增/查询表记录...

最新推荐文章于 2022-05-16 21:26:51 发布

weixin_39908070

最新推荐文章于 2022-05-16 21:26:51 发布

阅读量1.2k

点赞数

文章标签： mysql导出dat文件linux命令

基于Linux的MySQL操作实例之数据导入/导出，新增/查询表记录

前言

本篇就数据的导入/导出数据表，新增/查询表记录这些部分进行了实例和基本概念说明。

本篇对板式做了一些修改，希望大家喜欢。

本篇内容结合本人其他几篇一起观看效果更佳，有兴趣的各位可以看看。

数据导入

概述

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

语法格式

LOAD DATA INFILE '文件名' INTO TABLE 表名

FIELDS TERMINATED BY '分隔符' LINES TERMINATED BY '\n';

fields 表示区域划分，即列的划分

lines 表示列的划分

注意事项

字段分隔符要与文件内的一致

指定导入文件的绝对路径

导入数据的表字段类型要与文件字段匹配

实例操作

mysql> create database test_db;

Query OK, 1 row affected (0.00 sec)

mysql> use test_db;

Database changed

mysql> create table userlist ( uname char(25) not null, passwdmark char(1) not null, uid int(3), gid smallint(3), comment varchar(50), homedir char(30), shell char(30), index(uname) );

Query OK, 0 rows affected (0.74 sec)

mysql> desc userlist;

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

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

| uid | int(3) | YES | | NULL | |

| gid | smallint(3) | YES | | NULL | |

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

7 rows in set (0.01 sec)

mysql> load data infile "/etc/passwd" into table userlist fields terminated by ':' lines terminated by "\n";

Query OK, 25 rows affected (0.17 sec)

Records: 25 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from userlist;

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

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

| root | x | 0 | 0 | root | /root | /bin/bash | 01 |

| bin | x | 1 | 1 | bin | /bin | /sbin/nologin | 02 |

| daemon | x | 2 | 2 | daemon | /sbin | /sbin/nologin | 03 |

| adm | x | 3 | 4 | adm | /var/adm | /sbin/nologin | 04 |

| lp | x | 4 | 7 | lp | /var/spool/lpd | /sbin/nologin | 05 |

……

mysql> alter table userlist add u_id int(2) zerofill primary key auto_increment first;

Query OK, 0 rows affected (0.94 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from userlist;

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

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

| 01 | root | x | 0 | 0 | root | /root | /bin/bash |

| 02 | bin | x | 1 | 1 | bin | /bin | /sbin/nologin |

| 03 | daemon | x | 2 | 2 | daemon | /sbin | /sbin/nologin |

| 04 | adm | x | 3 | 4 | adm | /var/adm | /sbin/nologin |

……

数据导出

概述

数据导出: 把表中的记录存储到系统文件里。

语法格式：

sql查询命令 INTO OUTFILE '文件名'；

sql查询命令 INTO OUTFILE '目录名/文件名'；

sql查询命令 INTO OUTFILE '目录名/文件名' fields terminated by "符号"；

sql查询命令 INTO OUTFILE '目录名/文件名' lines terminated by "!!!"；

sql查询命令 INTO OUTFILE '目录名/文件名' fields terminated by "符号" lines terminated by "符号"；

注意事项

导出的内容由SQL查询语句决定

若不指定路径，默认会放在执行导出命令时所在库对应的数据库目录下。

应确保mysql用户对目标文件夹有写权限。

实例操作

//创建文件存放目录

[root@mysql var]# mkdir /var/mysql_doc/

[root@mysql var]# ll -d /var/mysql_doc/

drwxr-xr-x. 2 root root 4096 Jan 17 06:44 /var/mysql_doc/

[root@mysql var]# id mysql

uid=27(mysql) gid=27(mysql) groups=27(mysql)

[root@mysql var]# chown mysql: /var/mysql_doc/

[root@mysql var]# ll -d /var/mysql_doc/

drwxr-xr-x. 2 mysql mysql 4096 Jan 17 06:44 /var/mysql_doc/

mysql> select uname,shell from userlist where shell="/bin/bash" ;

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

| uname | shell |

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

| root | /bin/bash |

| mysql | /bin/bash |

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

2 rows in set (0.00 sec)

//测试当文件存放目录没有操作权限的情况：

[root@mysql var]# chown root: /var/mysql_doc/

[root@mysql var]# ll -d /var/mysql_doc/

drwxr-xr-x. 2 root root 4096 Jan 17 06:44 /var/mysql_doc/

mysql> select uname,shell from userlist where shell="/bin/bash" into outfile "/var/mysql_doc/test1.txt";

ERROR 1 (HY000): Can't create/write to file '/var/mysql_doc/test1.txt' (Errcode: 13 - Permission denied)

//正确操作

[root@mysql var]# chown mysql: /var/mysql_doc/

[root@mysql var]# ll -d /var/mysql_doc/

drwxr-xr-x. 2 mysql mysql 4096 Jan 17 06:44 /var/mysql_doc/

mysql> select uname,shell from userlist where shell="/bin/bash" into outfile "/var/mysql_doc/test1.txt";

Query OK, 2 rows affected (0.00 sec)

[root@mysql var]# cd mysql_doc/

[root@mysql mysql_doc]# ls

test1.txt

[root@mysql mysql_doc]# cat test1.txt

root /bin/bash

mysql /bin/bash

//指定列分割符的情况：

mysql> select uname,homedir from userlist into outfile "/var/mysql_doc/test2.txt" fields terminated by "----";

Query OK, 25 rows affected (0.00 sec)

[root@mysql mysql_doc]# head -5 test2.txt

root----/root

bin----/bin

daemon----/sbin

adm----/var/adm

lp----/var/spool/lpd

//指定行的分割符的情况

mysql> select uname from userlist into outfile "/var/mysql_doc/test3.txt" lines terminated by "||";

Query OK, 25 rows affected (0.00 sec)

[root@mysql mysql_doc]# cat test3.txt

abrt||adm||bin||daemon||dbus||ftp||games||gopher||haldaemon||halt||lp||mail||mysql||nobody||ntp||operator||postfix||root||saslauth||shutdown||sshd||sync||tcpdump||uucp||vcsa||[root@mysql mysql_doc]#

导出时，文件若已经存在，会报错。

不指定文件目录时，文件会默认存放在当前数据库所在的目录中

若没有指定当前的数据库，文件会默认存放在MySQL目录(/var/lib/mysql)中

也可以自行创建目录，但要保证mysql进程的用户对该目录有相应的操作权限。

新增表记录操作(insert into)

语法格式

若是当前操作的表就是当前库之内的可直接使用表名(即使用命令“use 数据库名”切换数据库)

一次插入一条记录给记录的所有字段赋值

insert into 库.表 values(值列表);

一次插入多条记录给记录的所有字段赋值

insert into 库.表 values(值列表),(值列表);

一次插入1条记录给记录的指定字段赋值

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

一次插入多条记录给记录的指定字段赋值

insert into 库.表(字段名列表) values(值列表)，(值列表);

操作实例

mysql> insert into userlist values(34,"job","X",502,600,"job","/home/job/","/bin/bash"), (35,"tom","x",503,601,"tom","/home/job/","/bin/bash");

Query OK, 2 rows affected (0.05 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from userlist where u_id=34 || u_id=35;

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

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

| 34 | job | X | 502 | 600 | job | /home/job/ | /bin/bash |

| 35 | tom | x | 503 | 601 | tom | /home/job/ | /bin/bash |

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

2 rows in set (0.00 sec)

mysql> insert into userlist(uname,uid,gid) values("jack",503,605);

ERROR 1364 (HY000): Field 'passwdmark' doesn't have a default value

mysql> insert into userlist(uname,passwdmark,uid,gid) values("jack","x",503,605);

Query OK, 1 row affected (0.03 sec)

查询表记录(select)

语法格式：

select 字段列表 from 表名;

select 字段列表 from 表名 where 表达式；

select 字段列表 from 表名 where 条件表达式；

实例操作

由于select操作的方式条件过多，我们将分开进行实例说明。

数值比较实例操作

格式：字段名符号数字

符号：= != > >= < <=

//数值比较

mysql> select uname,shell from userlist where u_id=500;

Empty set (0.00 sec)

mysql> select uname,shell from userlist where u_id>500;

Empty set (0.00 sec)

mysql> select uname,shell from userlist where uid>500;

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

| uname | shell |

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

| job | /bin/bash |

| tom | /bin/bash |

| jack | NULL |

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

3 rows in set (0.00 sec)

mysql> select uname,shell from userlist where uid=500;

Empty set (0.00 sec)

mysql> select uname,shell from userlist where uid>=500;

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

| uname | shell |

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

| job | /bin/bash |

| tom | /bin/bash |

| jack | NULL |

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

3 rows in set (0.00 sec)

mysql> select u_id,uname,gid,homedir from userlist where gid<50;

……

mysql> select uname,uid,shell from userlist where uid>10&& uid<20;

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

| uname | uid | shell |

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

| operator | 11 | /sbin/nologin |

| games | 12 | /sbin/nologin |

| gopher | 13 | /sbin/nologin |

| ftp | 14 | /sbin/nologin |

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

4 rows in set (0.00 sec)

mysql> select uname,uid,shell from userlist where uid>10 and uid<20;

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

| uname | uid | shell |

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

| operator | 11 | /sbin/nologin |

| games | 12 | /sbin/nologin |

| gopher | 13 | /sbin/nologin |

| ftp | 14 | /sbin/nologin |

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

4 rows in set (0.00 sec)

字符比较操作实例

格式：字段名符号 "值"

符号：= !=

mysql> select uname,shell from userlist where uname!="root";

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

| uname | shell |

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

| bin | /sbin/nologin |

| daemon | /sbin/nologin |

| adm | /sbin/nologin |

……

mysql> select uname,shell from userlist where uname="root";

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

| uname | shell |

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

| root | /bin/bash |

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

1 row in set (0.00 sec)

范围内比较实例操作

格式：

between ... and ... 在...之间

in (值列表) 在....里

not in (值列表) 不在....里

//适合用于数值型的变量，字符型或其他型虽然不会报错，但是会出现结果显示不准确的情况

mysql> select uname,uid,shell from userlist where uid between 10 and 20;

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

| uname | uid | shell |

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

| uucp | 10 | /sbin/nologin |

| operator | 11 | /sbin/nologin |

| games | 12 | /sbin/nologin |

| gopher | 13 | /sbin/nologin |

| ftp | 14 | /sbin/nologin |

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

5 rows in set (0.00 sec)

mysql> select uname,uid,homedir from userlist where uid in (10,20,30,40,500);

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

| uname | uid | homedir |

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

| uucp | 10 | /var/spool/uucp |

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

1 row in set (0.00 sec)

mysql> select uname,uid,homedir from userlist where uname in ("root","shutdown","mysql","natasha");

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

| uname | uid | homedir |

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

| root | 0 | /root |

| shutdown | 6 | /sbin |

| mysql | 27 | /var/lib/mysql |

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

3 rows in set (0.00 sec)

mysql> select uname,homedir,shell from userlist where shell not in ("/sbin/nologin");

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

| uname | homedir | shell |

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

| root | /root | /bin/bash |

| sync | /sbin | /bin/sync |

| shutdown | /sbin | /sbin/shutdown |

| halt | /sbin | /sbin/halt |

| mysql | /var/lib/mysql | /bin/bash |

| job | /home/job/ | /bin/bash |

| tom | /home/job/ | /bin/bash |

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

7 rows in set (0.00 sec)

匹配null操作实例

格式：

匹配空 is null

匹配非空 is not null

mysql> select u_id,uname,shell from userlist where shell is null;

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

| u_id | uname | shell |

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

| 36 | jack | NULL |

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

1 row in set (0.00 sec)

mysql> select u_id,uname,shell from userlist where shell is not NULL;

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

| u_id | uname | shell |

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

| 01 | root | /bin/bash |

| 02 | bin | /sbin/nologin |

| 03 | daemon | /sbin/nologin |

……

//"null","NULL",""都代表字符串，不表示NULL

mysql> insert into userlist (u_id,uname) values(40,"null"),(41,"NULL"),(42,"");

Query OK, 3 rows affected (0.04 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> select u_id,uname from userlist where u_id>=40;

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

| u_id | uname |

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

| 40 | null |

| 41 | NULL |

| 42 | |

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

3 rows in set (0.00 sec)

mysql> select u_id,uname from userlist where uname="";

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

| u_id | uname |

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

| 42 | |

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

1 row in set (0.00 sec)

mysql> select u_id,uname from userlist where uname="null";

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

| u_id | uname |

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

| 40 | null |

| 41 | NULL |

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

2 rows in set (0.00 sec)

mysql> select u_id,uname from userlist where uname="NULL";

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

| u_id | uname |

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

| 40 | null |

| 41 | NULL |

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

2 rows in set (0.00 sec)

mysql> select u_id,uname from userlist where uname=NULL;

Empty set (0.00 sec)

逻辑比较操作实例

逻辑比较(多个查询条件)

格式：

逻辑与 and 多个查询条件必须同时成立

逻辑或 or 多个查询条件某个条件成立就可以

逻辑非！取反

//and的优先级高于or，()的优先级最高

mysql> select u_id,uid,uname,homedir,shell from userlist where uname="root" or uid=1 or shell="/bin/bash";

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

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

| 02 | 1 | bin | /bin | /sbin/nologin |

| 34 | 502 | job | /home/job/ | /bin/bash |

| 35 | 503 | tom | /home/job/ | /bin/bash |

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

5 rows in set (0.00 sec)

mysql> select u_id,uid,uname,homedir,shell from userlist where uname="bin" and uid=0;

Empty set (0.00 sec)

mysql> select u_id,uid,uname from userlist where uname="root" or uname="bin" and uid=1;

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

| u_id | uid | uname |

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

| 02 | 1 | bin |

| 01 | 0 | root |

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

2 rows in set (0.00 sec)

mysql> select uname,uid from userlist where (uname="root" or uname="bin") and uid=1;

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

| uname | uid |

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

| bin | 1 |

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

1 row in set (0.00 sec)

四则运算操作实例

符号："+","-","*","/","%"

//调整表结构，添加测试数据

mysql> alter table userlist add age tinyint(2) not null default 19;

Query OK, 0 rows affected (0.74 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table userlist add rhcsa_n double(5,2) not null default 0 ,

-> add rhce_n double(5,2) not null default 0;

Query OK, 0 rows affected (0.77 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc userlist;

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

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

| u_id | int(2) unsigned zerofill | NO | PRI | NULL | auto_increment |

| uid | int(3) | YES | | NULL | |

| gid | smallint(3) | YES | | NULL | |

| age | tinyint(2) | NO | | 19 | |

| rhcsa_n | double(5,2) | NO | | 0.00 | |

| rhce_n | double(5,2) | NO | | 0.00 | |

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

11 rows in set (0.00 sec)

mysql> update userlist set rhcsa_n=70;

Query OK, 31 rows affected (0.04 sec)

Rows matched: 31 Changed: 31 Warnings: 0

mysql> update userlist set rhce_n=90;

Query OK, 31 rows affected (0.05 sec)

Rows matched: 31 Changed: 31 Warnings: 0

mysql> select * from userlist;

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

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

| 01 | root | x | 0 | 0 | root | /root | /bin/bash | 19 | 70.00 | 90.00 |

| 02 | bin | x | 1 | 1 | bin | /bin | /sbin/nologin | 19 | 70.00 | 90.00 |

| 03 | daemon | x | 2 | 2 | daemon | /sbin | /sbin/nologin | 19 | 70.00 | 90.00 |

| 04 | adm | x | 3 | 4 | adm | /var/adm | /sbin/nologin | 19 | 70.00 | 90.00 |

| 05 | lp | x | 4 | 7 | lp | /var/spool/lpd | /sbin/nologin | 19 | 70.00 | 90.00 |

……

//where u_id<10 是避免显示结果过多

mysql> select uname,2017-age as s_year ,age from userlist where u_id<10;

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

| uname | s_year | age |

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

| root | 1998 | 19 |

| bin | 1998 | 19 |

| daemon | 1998 | 19 |

| adm | 1998 | 19 |

| lp | 1998 | 19 |

| sync | 1998 | 19 |

| shutdown | 1998 | 19 |

| halt | 1998 | 19 |

| mail | 1998 | 19 |

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

9 rows in set (0.02 sec)

//使用as设置别名，在结果显示处

mysql> select uname,year(now())-age as s_year,age from userlist where u_id<10

-> ;

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

| uname | s_year | age |

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

| root | 1998 | 19 |

| bin | 1998 | 19 |

| daemon | 1998 | 19 |

| adm | 1998 | 19 |

| lp | 1998 | 19 |

| sync | 1998 | 19 |

| shutdown | 1998 | 19 |

| halt | 1998 | 19 |

| mail | 1998 | 19 |

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

9 rows in set (0.03 sec)

mysql> select uname,rhcsa_n,rhce_n,rhcsa_n+rhce_n as add_result,(rhcsa_n+rhce_n)/2 as av_result from userlist where u_id>30;

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

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

| job | 70.00 | 90.00 | 160.00 | 80.000000 |

| tom | 70.00 | 90.00 | 160.00 | 80.000000 |

| jack | 70.00 | 90.00 | 160.00 | 80.000000 |

| null | 70.00 | 90.00 | 160.00 | 80.000000 |

| NULL | 70.00 | 90.00 | 160.00 | 80.000000 |

| | 70.00 | 90.00 | 160.00 | 80.000000 |

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

6 rows in set (0.00 sec)

//请注意as是生成别名，只是一个别名，不是类似与变量，不可以在后续使用，

mysql> select uname,rhcsa_n,rhce_n,rhcsa_n+rhce_n as add_result,add_result/2 as av_result from userlist where u_id>30;

ERROR 1054 (42S22): Unknown column 'add_result' in 'field list'

模糊查询(like)操作实例

格式：

where 字段名 like '表达式'

"_"：任意一个字符

"%"：零个或多个字符

该表达式不同于正则表达式等，是SQL自带的匹配方式。

//"_"表示单个字符，"%"表示任意个字符(零个或多个)

mysql> select uname from userlist where uname like "a___";

+-------+

| uname |

+-------+

| abrt |

+-------+

1 row in set (0.00 sec)

mysql> select uname from userlist where uname like "___a";

+-------+

| uname |

+-------+

| vcsa |

+-------+

1 row in set (0.00 sec)

//uname字段至少两个字符

mysql> select uname from userlist where uname like "_%_";

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

| uname |

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

| abrt |

| adm |

| bin |

| daemon |

| dbus |

| ftp |

……

//以r开头的uname字段的值

mysql> select uname from userlist where uname like "r%";

+-------+

| uname |

+-------+

| root |

+-------+

1 row in set (0.00 sec)

//uname为空(null)的字段

mysql> select u_id,uname from userlist where uname is null;

Empty set (0.00 sec)

mysql> alter table userlist modify uname char(10) ;

Query OK, 31 rows affected (1.37 sec)

Records: 31 Duplicates: 0 Warnings: 0

//插入一条uname为空的字段

mysql> insert into userlist(u_id,uname) values (44,null);

Query OK, 1 row affected (0.04 sec)

mysql> select u_id,uname from userlist where uname is null;

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

| u_id | uname |

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

| 44 | NULL |

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

1 row in set (0.00 sec)

//该方式无法检索到uname为null的条目

mysql> select u_id,uname from userlist where uname like "%";

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

| u_id | uname |

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

| 42 | |

| 18 | abrt |

| 04 | adm |

| 02 | bin |

| 03 | daemon |

| 16 | dbus |

| 14 | ftp |

| 12 | games |

| 13 | gopher |

| 19 | haldaemon |

| 08 | halt |

| 36 | jack |

| 34 | job |

| 05 | lp |

| 09 | mail |

| 25 | mysql |

| 15 | nobody |

| 20 | ntp |

| 40 | null |

| 41 | NULL |

| 11 | operator |

| 22 | postfix |

| 01 | root |

| 21 | saslauth |

| 07 | shutdown |

| 23 | sshd |

| 06 | sync |

| 24 | tcpdump |

| 35 | tom |

| 10 | uucp |

| 17 | vcsa |

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

31 rows in set (0.00 sec)

mysql> select u_id,uname from userlist where uname like "%" and u_id=44;

Empty set (0.00 sec)

正则表达式操作实例

使用正则表达式做查询条件

由于正则表达式是一个独立的兼容性好的其他表达式语法，这里就不展开讲解了。

关于正则表达式的更多细节可以去我的正则表达式的博客查看。

支持符号："^"，"$"，"."，"*"，"[ ]"

格式：字段名 regexp '正则表达式'

//正则表达式操作实例

mysql> insert into userlist(uname) values ("bob"),("plj9"),("pl8j"),("p7lj"),("6plj");

Query OK, 5 rows affected (0.04 sec)

Records: 5 Duplicates: 0 Warnings: 0

mysql> select uname from userlist where uname regexp "bb";

Empty set (0.00 sec)

mysql> select uname from userlist where uname regexp "b.*b";

+-------+

| uname |

+-------+

| bob |

+-------+

1 row in set (0.00 sec)

mysql> select uname from userlist where uname regexp "^b.*b$";

+-------+

| uname |

+-------+

| bob |

+-------+

1 row in set (0.00 sec)

mysql> select uname from userlist where uname regexp "^bb$";

Empty set (0.00 sec)

mysql> select uname from userlist where uname regexp "^a.*t$";

+-------+

| uname |

+-------+

| abrt |

+-------+

1 row in set (0.00 sec)

mysql> select uname from userlist where uname regexp "[0-9]";

+-------+

| uname |

+-------+

| 6plj |

| p7lj |

| pl8j |

| plj9 |

+-------+

4 rows in set (0.00 sec)

mysql> select uname from userlist where uname regexp "^[0-9]";

+-------+

| uname |

+-------+

| 6plj |

+-------+

1 row in set (0.00 sec)

mysql> select uname from userlist where uname regexp "[0-9]$";

+-------+

| uname |

+-------+

| plj9 |

+-------+

1 row in set (0.00 sec)

//mysql不区分大小写，所以正则表达式的大小写依旧不区分

mysql> select uname from userlist where uname regexp "[A-Z]";

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

| uname |

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

| 6plj |

| abrt |

| adm |

| bin |

| bob |

| daemon |

| dbus |

| ftp |

| games |

| gopher |

| haldaemon |

| halt |

……

聚集函数操作实例

max(字段名) 获取最大值

min(字段名) 获取最小值

avg(字段名) 获取平均值

sum(字段名) 求和

count(字段名) 获取字段值个数

//内置函数

//该类函数不能在where后面的表达式中使用，SQL不支持这样操作

mysql> select shell from userlist where shell is null;

+-------+

| shell |

+-------+

| NULL |

+-------+

10 rows in set (0.00 sec)

mysql> select count(shell) from userlist where shell is null;

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

| count(shell) |

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

| 0 |

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

1 row in set (0.00 sec)

mysql> select count(u_id) from userlist where shell is null;

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

| count(u_id) |

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

| 10 |

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

1 row in set (0.00 sec)

mysql> select count(*) from userlist;

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

| count(*) |

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

| 37 |

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

1 row in set (0.00 sec)

mysql> select count(*),count(u_id),count(uname),count(shell) from userlist;

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

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

| 37 | 37 | 36 | 27 |

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

1 row in set (0.00 sec)

//rhcsa_n这一列的数值的和

mysql> select sum(rhcsa_n) from userlist;

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

| sum(rhcsa_n) |

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

| 2170.00 |

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

1 row in set (0.00 sec)

mysql> select avg(rhcsa_n) from userlist;

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

| avg(rhcsa_n) |

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

| 58.648649 |

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

1 row in set (0.00 sec)

mysql> select max(rhcsa_n) from userlist;

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

| max(rhcsa_n) |

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

| 70.00 |

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

1 row in set (0.00 sec)

mysql> select max(uid) from userlist;

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

| max(uid) |

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

| 503 |

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

1 row in set (0.00 sec)

mysql> select min(gid) from userlist;

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

| min(gid) |

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

| 0 |

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

1 row in set (0.00 sec)

//聚合函数不能这样使用

mysql> select uname,rhcsa_n from userlist where rhcsa_n < avg(rhcsa_n);

ERROR 1111 (HY000): Invalid use of group function

分组操作实例

格式：

查询分组 group by 字段名

sql查询命令 group by 字段名;

//group on

mysql> select shell from userlist where uid<10;

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

| shell |

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

| /bin/bash |

| /sbin/nologin |

| /bin/sync |

| /sbin/shutdown |

| /sbin/halt |

| /sbin/nologin |

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

9 rows in set (0.00 sec)

mysql> select shell from userlist where uid<10 group by shell;

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

| shell |

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

| /bin/bash |

| /bin/sync |

| /sbin/halt |

| /sbin/nologin |

| /sbin/shutdown |

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

5 rows in set (0.00 sec)

mysql> select shell from userlist where uid<100 group by shell;

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

| shell |

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

| /bin/bash |

| /bin/sync |

| /sbin/halt |

| /sbin/nologin |

| /sbin/shutdown |

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

5 rows in set (0.00 sec)

//该操作没有意义，只能显示当前shell值的第一次出现的uname的值

mysql> select uname from userlist where uid<100 group by shell;

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

| uname |

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

| root |

| sync |

| halt |

| bin |

| shutdown |

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

5 rows in set (0.00 sec)

mysql> select uid,uname,shell from userlist where uid<100 group by shell;

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

| uid | uname | shell |

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

| 0 | root | /bin/bash |

| 5 | sync | /bin/sync |

| 7 | halt | /sbin/halt |

| 1 | bin | /sbin/nologin |

| 6 | shutdown | /sbin/shutdown |

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

5 rows in set (0.00 sec)

mysql> select uid,uname,shell from userlist group by shell;

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

| uid | uname | shell |

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

| 503 | jack | NULL |

| 0 | root | /bin/bash |

| 5 | sync | /bin/sync |

| 7 | halt | /sbin/halt |

| 1 | bin | /sbin/nologin |

| 6 | shutdown | /sbin/shutdown |

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

6 rows in set (0.00 sec)

//两者效果相同

mysql> select distinct shell from userlist ;

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

| shell |

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

| /bin/bash |

| /sbin/nologin |

| /bin/sync |

| /sbin/shutdown |

| /sbin/halt |

| NULL |

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

6 rows in set (0.00 sec)

查询排序操作实例：

查询排序 order by

格式：

sql查询命令 order by 字段名排序方式

asc 升序(默认)

desc 降序

mysql> select uid,uname,shell from userlist where uid<10 order by uid;

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

| uid | uname | shell |

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

| 0 | root | /bin/bash |

| 1 | bin | /sbin/nologin |

| 2 | daemon | /sbin/nologin |

| 3 | adm | /sbin/nologin |

| 4 | lp | /sbin/nologin |

| 5 | sync | /bin/sync |

| 6 | shutdown | /sbin/shutdown |

| 7 | halt | /sbin/halt |

| 8 | mail | /sbin/nologin |

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

9 rows in set (0.00 sec)

mysql> select uid,uname,shell from userlist where uid<10 order by uid desc;

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

| uid | uname | shell |

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

| 8 | mail | /sbin/nologin |

| 7 | halt | /sbin/halt |

| 6 | shutdown | /sbin/shutdown |

| 5 | sync | /bin/sync |

| 4 | lp | /sbin/nologin |

| 3 | adm | /sbin/nologin |

| 2 | daemon | /sbin/nologin |

| 1 | bin | /sbin/nologin |

| 0 | root | /bin/bash |

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

9 rows in set (0.00 sec)

mysql> select uid,uname,shell from userlist where uid<10 order by uname desc;

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

| uid | uname | shell |

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

| 5 | sync | /bin/sync |

| 6 | shutdown | /sbin/shutdown |

| 0 | root | /bin/bash |

| 8 | mail | /sbin/nologin |

| 4 | lp | /sbin/nologin |

| 7 | halt | /sbin/halt |

| 2 | daemon | /sbin/nologin |

| 1 | bin | /sbin/nologin |

| 3 | adm | /sbin/nologin |

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

9 rows in set (0.00 sec)

mysql> select uid,uname,shell from userlist where uid<10 order by shell;

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

| uid | uname | shell |

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

| 0 | root | /bin/bash |

| 5 | sync | /bin/sync |

| 7 | halt | /sbin/halt |

| 1 | bin | /sbin/nologin |

| 2 | daemon | /sbin/nologin |

| 3 | adm | /sbin/nologin |

| 4 | lp | /sbin/nologin |

| 8 | mail | /sbin/nologin |

| 6 | shutdown | /sbin/shutdown |

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

9 rows in set (0.00 sec)

mysql> select uid,uname,shell from userlist where uid<10 order by uname;

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

| uid | uname | shell |

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

| 3 | adm | /sbin/nologin |

| 1 | bin | /sbin/nologin |

| 2 | daemon | /sbin/nologin |

| 7 | halt | /sbin/halt |

| 4 | lp | /sbin/nologin |

| 8 | mail | /sbin/nologin |

| 0 | root | /bin/bash |

| 6 | shutdown | /sbin/shutdown |

| 5 | sync | /bin/sync |

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

9 rows in set (0.00 sec)

having 条件操作实例

在查询结果里顾虑数据 having 条件

//从结果上来说和and等条件查询是一样的，但是由于having是从查询结果中再次进行匹配和检索

//这样操作的开销会比联合多条件查询要小，速度也会更快，

//多条件的联合查询是逐条进行检索操作

//在开发中或可以使用having语法进行二次检索

//select uname from userlist where uname is not null and uname="tom";

mysql> select uname from userlist where uname is not null having uname="tom";

+-------+

| uname |

+-------+

| tom |

+-------+

1 row in set (0.00 sec)

mysql> select uname from userlist where shell="/bin/bash" having uname="root";

+-------+

| uname |

+-------+

| root |

+-------+

1 row in set (0.00 sec)

mysql> select uname,shell from userlist where shell="/bin/bash" having uname="root";

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

| uname | shell |

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

| root | /bin/bash |

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

1 row in set (0.00 sec)

mysql> select u_id,uname from userlist where uname like '%' having u_id in (30,31,32);

Empty set (0.00 sec)

mysql> select u_id,uname from userlist where uname like '%' having u_id in (10,11,12);

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

| u_id | uname |

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

| 12 | games |

| 11 | operator |

| 10 | uucp |

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

3 rows in set (0.00 sec)

limit操作实例

limit 限制显示查询结果记录的行数。

格式:

limit 数字；

limit 起始行，共显示几行

mysql> select u_id,uname,uid from userlist order by uid desc limit 1;

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

| u_id | uname | uid |

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

| 35 | tom | 503 |

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

1 row in set (0.00 sec)

mysql> select u_id,uname,uid from userlist order by uid desc limit 5,10;

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

| u_id | uname | uid |

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

| 15 | nobody | 99 |

| 22 | postfix | 89 |

| 16 | dbus | 81 |

| 23 | sshd | 74 |

| 24 | tcpdump | 72 |

| 17 | vcsa | 69 |

| 19 | haldaemon | 68 |

| 20 | ntp | 38 |

| 25 | mysql | 27 |

| 14 | ftp | 14 |

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

10 rows in set (0.00 sec)

mysql> select * from userlist limit 1;

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

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

| 01 | root | x | 0 | 0 | root | /root | /bin/bash | 19 | 70.00 | 90.00 |

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

1 row in set (0.00 sec)

mysql> select * from userlist limit 3;

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

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

| 01 | root | x | 0 | 0 | root | /root | /bin/bash | 19 | 70.00 | 90.00 |

| 02 | bin | x | 1 | 1 | bin | /bin | /sbin/nologin | 19 | 70.00 | 90.00 |

| 03 | daemon | x | 2 | 2 | daemon | /sbin | /sbin/nologin | 19 | 70.00 | 90.00 |

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

3 rows in set (0.00 sec)

distinct 操作实例

DISTINCT 不显示重复的值

mysql> select distinct shell from userlist ;

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

| shell |

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

| /bin/bash |

| /sbin/nologin |

| /bin/sync |

| /sbin/shutdown |

| /sbin/halt |

| NULL |

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

6 rows in set (0.00 sec)

where操作实例

where 子查询

把内层查询结果做为外层的查询条件。

select 字段名列表 from 表A 条件 (select 字段名列表 from 表B)；

//where 子查询

//常用于多表查询或单个表的嵌套递归查询

//聚合函数不能这样使用

mysql> select uname,rhcsa_n from userlist where rhcsa_n < avg(rhcsa_n);

ERROR 1111 (HY000): Invalid use of group function

mysql> select uname,rhcsa_n from userlist where rhcsa_n < (select avg(rhcsa_n) from userlist);

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

| uname | rhcsa_n |

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

| NULL | 0.00 |

| bob | 0.00 |

| plj9 | 0.00 |

| pl8j | 0.00 |

| p7lj | 0.00 |

| 6plj | 0.00 |

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

6 rows in set (0.00 sec)

//安装mysql时自带的数据库

mysql> select user,host from mysql.user;

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

| user | host |

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

| root | 127.0.0.1 |

| root | ::1 |

| root | localhost |

| root | mysql.wolf.cn |

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

4 rows in set (0.00 sec)

mysql> select uname from userlist where uname in (select user from mysql.user);

+-------+

| uname |

+-------+

| root |

+-------+

1 row in set (0.00 sec)

mysql> select uname from userlist where uname in (select user from mysql.user where host="localhost");

+-------+

| uname |

+-------+

| root |

+-------+

1 row in set (0.00 sec)

复制表操作

复制表(备份表快速创建新表)

create table 新表名 sql查询命令；

复制全表

create table 新表名 select * from 表名；

create table stu4 select * from student;

复制部分数据

create table 新表名 select 字段名列表

from 表名 where 条件；

create table stu3 select name,homedir,shell from student where uid<=10 ;

只复制表结构

create table 新表名 select * from 表名 where 1 = 2；

create table stu2 select * from student where 1 = 2;

在复制表时，源表的索引属性并不会随之到新表中。例如主键并不会随之复制。

mysql> create table userlist_bak select * from userlist;

Query OK, 37 rows affected (0.52 sec)

Records: 37 Duplicates: 0 Warnings: 0

mysql> show tables;

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

| Tables_in_test_db |

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

| userlist |

| userlist_bak |

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

2 rows in set (0.00 sec)

mysql> select * from userlist_bak;

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

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

| 01 | root | x | 0 | 0 | root | /root | /bin/bash | 19 | 70.00 | 90.00 |

| 02 | bin | x | 1 | 1 | bin | /bin | /sbin/nologin | 19 | 70.00 | 90.00 |

……

//源表的索引属性并不会随之到新表中

mysql> desc userlist_bak;

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

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

| u_id | int(2) unsigned zerofill | NO | | 00 | |

| uid | int(3) | YES | | NULL | |

| gid | smallint(3) | YES | | NULL | |

| age | tinyint(2) | NO | | 19 | |

| rhcsa_n | double(5,2) | NO | | 0.00 | |

| rhce_n | double(5,2) | NO | | 0.00 | |

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

11 rows in set (0.00 sec)

mysql> create table ush_bak select uname,shell from userlist;

Query OK, 37 rows affected (0.57 sec)

Records: 37 Duplicates: 0 Warnings: 0

mysql> desc ush_bak;

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

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

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

2 rows in set (0.00 sec)

mysql> create table uframe_bak select * from userlist where 1=2;

Query OK, 0 rows affected (0.42 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc uframe_bak;

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

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

| u_id | int(2) unsigned zerofill | NO | | 00 | |

| uid | int(3) | YES | | NULL | |

| gid | smallint(3) | YES | | NULL | |

| age | tinyint(2) | NO | | 19 | |

| rhcsa_n | double(5,2) | NO | | 0.00 | |

| rhce_n | double(5,2) | NO | | 0.00 | |

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

11 rows in set (0.01 sec)

mysql> select * from uframe_bak;

Empty set (0.00 sec)

weixin_39908070

关注

0
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
mysql导出dat文件linux命令,基于Linux的MySQL操作实例之数据导入/导出，新增/查询表记录...

基于Linux的MySQL操作实例之数据导入/导出，新增/查询表记录前言本篇就数据的导入/导出数据表，新增/查询表记录这些部分进行了实例和基本概念说明。本篇对板式做了一些修改，希望大家喜欢。本篇内容结合本人其他几篇一起观看效果更佳，有兴趣的各位可以看看。数据导入概述数据导入：把系统文件的内容存储到数据库的表里。语法格式LOAD DATA INFILE '文件名' INTO TABLE 表...
复制链接

扫一扫