mysql 条件查询 导出数据_Mysql(三)-数据导入，导出、管理表记录、条件匹配、表查询方式...

##############################################################################################

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

命令：

load data infile "目录/文件"  into table 表名 fields terminated by "字段分隔符" lines terminated by "行分隔符"；

举例：将/etc/passwd 导入到 studb.user

create databases studb;            //创建studb库

use studb；                    //使用库

create table user(            //创建表

name  varchar(50),

password char(1),

uid int(2) zerofill,

gid int(2) zerofill,

comment varchar(50),

index(name)

);

desc  studb.user;                //查看表结构

alter  table  studb.user  add  id  int(2)  primary key  auto_increment first;

导入：

查看数据库使用的默认目录：

show variables like "secure_file_priv"

设置数据库使用的默认目录：

mkdir /mysqlload

chown mysql /mysqlload

vim /etc/my.cnf

[mysql]

secure_file_priv="/mysqlload"

cp /etc/passwd /mysqlload/                //将要 导入的文件放到数据库默认目录下

load data infile "/mysqlload/passwd" into table user fields terminated by ":" lines terminated by "\n";

//passwd文件的字段之间的分隔符是 ：  行间分隔符是回车(\n)

查看结果：

select  * from studb.user;

############################################################################################

二、数据导出：把表记录存储到系统文件中

sql查询 into outfile "/目录/文件名"

sql查询 into outfile "/目录/文件名" fields terminated by "符号" lines terminated by "符号"；

select * from bbs.t1 into outfile "/mysqlload/a.txt";

//注意：因为之前我已经设置了数据库使用的默认目录，所以我的文件导出路径就是我设置的目录下(/mysqlload)。

#########################################################################################################

三、管理表记录：

1、插入表记录

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

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

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

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

2、查询表记录：

select  字段名列表 from  库.表;

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

select   *  from  user;

select   *  from  user  where  name="mysql";

3、更新表记录：

update 库.表 set 字段1=值，字段2=值，字段n=值；

update 库.表 set 字段1=值，字段2=值，字段n=值 where 条件表达式；        //修改满足条件的字段的值

4、删除表记录：

delete from 库.表 where 条件表达式；                                    //删除满足条件的字段的表记录

delete from 库.表；                                                    //删除所有表记录

################################################################################################################

//注意：之前已经将/etc/passwd 导入到studb.user 中，所以接下来操作以user表为例：

四、条件匹配的表达方式：

数值比较：   >=  =  !=

字符比较： =    !=

范围匹配：between 值1 and 值2 ；  in  ； not in

匹配空和非空：is null ; is not null;

逻辑匹配：and  ；  or

模糊查询： where   字段名  like  '表达式';

正则匹配：where   字段名    regexp   '正则表达式';

1、数值比较：字段类型必须为数值：

数值比较   >  >=  

字段名  符号   值

select  name  from  user where  uid=15;

select  *  from  user where  id>10;

###########################################################

2、字符比较   =   !=

字段名  符号   "值"

select  name,shell  from  user where  shell!="/bin/bash";

select  id,name  from  user where  name="apache";

###########################################################

3、范围内匹配

字段名  between  值1 and   值2             在...之间

select  *  from user  where  id  between  10 and   15;

select name  from user  where  uid  between  1 and   10;

字段名  in  (值列表)    在...里

select  id,name  from  user where  name in ("apache","root","bob");

select  id,name,uid  from  user where  uid in  (10,15,9,12);

字段名  not  in  (值列表)    不在...里

select  name from  user  where  uid not in (0,1,5,7);

select  * from  user  where  name not in ("root","mysql","bin");

#####################################################################

4、匹配空     is  null

字段名   is  null

匹配非空  is not  null

字段名 is not  null

select  id from user  where  name is null;

select  id,name,shell from user  where  shell is  not  null;

insert into  user(name)values(""),("null"),(null);

select  id,name from user where name="";

select  id,name from user where name="null";

############################################################################

5、distinct 不显示重复值

distinct  字段名

select  distinct  shell  from  user;

select  distinct  shell  from  user  where uid<=10;

#########################################################################

6、逻辑匹配  ：     有多个条件

逻辑与  and    多个条件必须都成立

逻辑或  or     多个条件有一个条件成立即可

逻辑非  !        取反

select  name  from  user  where    name="zhangsan"   and

uid=500  and  shell="/bin/bash";

select  name  from  user  where    name="zhangsan"   or  uid=500

or  shell="/bin/bash";

###########################################################################

7、数学运算操作   +   -   *   /   %

字段类型必须是数值类型

select  字段名  符号   字段名  from  表   where 条件；

select  uid+gid  from  user where  name="root";

select name,uid,gid,uid+gid he from  user;

select name,uid,gid,uid+gid he from  user  where name="bin";

alter table  user add age  tinyint(2) unsigned default   21  after name;

select name,age,2017-age old from user where name="bob";

select name,uid,gid,(uid+gid)/2 pjz  from  user  where name="bin";

###############################################################################

8、模糊查询 like

where   字段名  like  '表达式';

_ 任意一个字符

% 0个或多个字符

select name  from  user  where  name like  '_ _ _ _';

//匹配name字段，值为4个字符的值。

select name,uid  from  user  where  name like  '_ _ _ _' and uid<=10;

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

//匹配a开头的

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

//匹配字段值中含有a的

select id,name  from  user  where  name like  '_a_';

//匹配字段值为3个字符而且中间一个字符为a的

select id,name  from  user  where  name like  'j%' or  "%y";

//匹配j开头，或者y结尾的

###############################################################################

9、正则匹配

where   字段名    regexp   '正则表达式';

.   任意单个字符

^   以什么开头

$   以什么结尾

[  ] 范围内匹配

*   前面字符出现0到多次

|    或

insert into  user(name) values("bob9"),("j7im"),("1yaya");

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

//字段值中含有数字的

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

//字段值的以数字开头的

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

//字段值，字符数最少为2个的字段值

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

//字段值，字符数为2个的字段值

select name,uid  from user  where  name   regexp   'a.*t';

//字段值中有a且有t

select name,uid  from user  where  name   regexp   '^a.*t';

//以a开头且有t

select name,uid  from user  where  name   regexp  '^r|t$'

//以r开头或者t结尾

################################################################

10、统计函数   字段得是数值类型。

求和                求平均值       求最大值         最小值          统计个数

sum(字段名)   avg(字段名)  max(字段名)  min(字段名)  count(字段名)

select  count(name)  from user where  shell="/bin/bash";

select  max(uid)  from user;

select  min(gid)  from user;

select  avg(age)  from user;

select  sum(gid)  from user;

select  sum(gid) , count(name)  from user;

########################################################################

11、查询排序  sql查询  order  by  字段名 asc/desc;

select  name,uid from user where uid  between  10  and  50 ;

//显示name，uid字段，并且满足uid在10到50之间，默认升序排列

select  name,uid from user where uid  between  10  and  50  order by  uid  desc;    //desc降序排列

##########################################################################

12、查询分组  sql查询  group   by   字段名;

select  shell user where uid  between  10  and  50 ;

select  shell from user where uid  between  10  and  50  group by shell;

select  shell from user   group by shell;

//因为不同用户的shell可能相同，所以将相同的shell进行分组，作用与distinct(不显示重复值类似)。

################################################################################

13、限制查询显示行数 limit

sql查询  limit  数字；  显示查询结果的前几行

sql查询  limit  数字1 ， 数字2； 从数字1的下一行开始显示，数字2设置总共显示多少行

select  *  from  user;

select  *  from  user  limit  2 ;

//显示前两行

select  *  from  user  limit  2 ,2 ;

//从第3行开始显示，总共显示2行，所以就显示的时第3，4行

select  * from user order by  uid  desc;

select  * from user order by  uid  desc  limit  5;

select  * from user order by  uid  desc  limit  1;

###############################################################################################

四：表查询方式

单表查询

where嵌套查询

多表查询

连接查询

1、单表查询

前面所用的都属于单表查询。

#############################################################

2、where嵌套查询 ：把内层的查询结果作为外层查询的查询条件。

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

select  name,uid  from user where  uid  >  ( select avg(uid)  from user  );

//显示用户名和uid   uid字段的值 大于 uid字段的平均值。(同一张表)

select name from  user  where name not  in (select user from mysql.user );

//显示用户名，studb.user 中的哪些用户名不在mysql.user表中(不同表)

select name from  user  where name  in (select user from mysql.user where  user="zhangsan");

select name from  user  where name not  in (select user from mysql.user where  user="zhangsan";);

########################################################################################

复制表： 作用： 快速建表 、 备份表

create  table  库.表   sql查询；

create  database dbbak;

create  table  dbbak.user2   select   * from  studb.user;

create  table  dbbak.user3   select   * from  studb.user where  1 = 2;

create  table  dbbak.user4   select  name,uid from  studb.user limit

3:多表查询

select  字段名列表  from 表名列表； 迪卡尔集

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

create  table  studb.t1   select  name,uid,shell from user limit 3;

create  table  studb.t2   select  name,uid,homedir from user limit  4;

show   tables;

select  * from t1;  select * from t2;

//总共3*4=12条表记录

select   *  from t1,t2 where  t1.uid  = t2.uid  and  t1.name=t2.name;

//查询t1和t2中uid与name相同的字段，并显示

select   t1.* , t2.homedir  from t1,t2 where  t1.uid  = t2.uid  and  t1.name=t2.name;

//显示t1中所有字段，t2中的homedir字段，满足t1.uid  = t2.uid  and  t1.name=t2.name;

++++++++++++++++++++++

4、连接查询

左连接查询

select  字段名列表  from  表A  left  join  表B  on  条件；

右连接查询

select  字段名列表  from  表A  right  join  表B  on  条件；

create  table  studb.t3   select  name,uid,shell from user limit 3;

create  table  studb.t4   select  name,uid,shell from user limit 5;

show   tables;

select  * from t3;  select * from t4;

select  *  from  t3  left join  t4  on  t3.uid=t4.uid;

//以t3为参照表，显示结果

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

| name   | uid  | shell         | name   | uid  | shell         |

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

| root   |    0 | /bin/bash     | root   |    0 | /bin/bash     |

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

| daemon |    2 | /sbin/nologin | daemon |    2 | /sbin/nologin |

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

select  *  from  t3  right join  t4  on  t3.uid=t4.uid;

//以t4为参照表，显示结果：

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

| name   | uid  | shell         | name   | uid  | shell         |

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

| root   |    0  | /bin/bash     | root   |    0 | /bin/bash     |

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

| daemon |    2 | /sbin/nologin | daemon |    2 | /sbin/nologin |

| NULL   | NULL | NULL          | adm    |    3 | /sbin/nologin |

| NULL   | NULL | NULL          | lp     |    4 | /sbin/nologin |

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

#################################################################################