一、基本匹配条件

• 环境准备

[root@db1 ~]# mkdir /myload
[root@db1 ~]# vim /etc/my.cnf
[mysqld]
secure_file_priv="/myload"
[root@db1 ~]# chown mysql /myload/		//必须要保证mysql用户对这个目录有读写的权限
[root@db1 ~]# ls -ld /myload/
[root@db1 ~]# systemctl restart mysqld

mysql> create database db3 ;
mysql> create table db3.user(
    -> name  char(30) , password  char(1) , uid int  , gid  int  ,
    -> comment  varchar(150) , homedir  char(80) , shell  char(60) 
    -> );
mysql> desc db3.user;

mysql> system cp  /etc/passwd  /myload/		//system调用系统命令
mysql> system  ls /myload/
passwd

mysql>  load data infile "/myload/passwd"
    -> into table db3.user
    -> fields  terminated by ":" 	//定义分隔符，必须要和passwd文件的分隔符一致
    -> lines terminated by "n";	//换行符

1、数值比较

• 字段必须是数值类型

• 示例

mysql> select  *   from db3.user  where  id <= 5 ;
mysql> select  name  ,  uid  , gid   from db3.user  where  uid  !=  gid ;

2、字符比较

• 必须是字符类型

• 示例

 mysql> select name from db3.user where  name  = "mysql" ;
 mysql> select name  , shell  from  db3.user where  shell != "/bin/bash" ;
 mysql> select  name  , uid  , shell from  db3.user where  shell is not  null ;
 mysql> select  name  , uid  , shell from  db3.user where  shell is null ;

3、逻辑匹配

• 多个判断条件时使用

• 示例

mysql> select name , uid  from  db3.user where  name = "sync" or  name = "apache" or  uid=3;			
mysql> select  * from  db3.user where id >= 10  and  id <= 20 ;
mysql> select name , uid  from  db3.user where  name = "sync" ||  name = "apache"  ||  uid=3;
mysql> select  * from  db3.user where id >= 10 &&  id <= 20 ;
mysql> select  name , uid  from  db3.user  where   uid = 1 or  uid = 0  and  name = "root" ;	
//与  或  同时存在,与优先
mysql> select  name , uid  from  db3.user   where  ( uid = 1 or  uid = 0 )  and  name = "root";
//加括号可以提高优先级

4、范围匹配

• 匹配范围内的任意一个值即可

• 示例

mysql> select name  ,  uid  ,  gid  from db3.user where uid between  10  and  40 ;
mysql> select name , uid from  db3.user where uid in (3 ,6,9,11);
mysql> select name from db3.user where name in  ("root" , "sync" , "daemon" , "bin");			
mysql> select shell  from  db3.user where  shell not  in  ("/bin/bash","/sbin/nologin");

二、高级匹配条件

1、模糊查询

• 用法
  • where 字段名 like ‘通配符’
  • _ 表示一个字符
  • % 表示0~n个字符
• 示例

mysql> select  name  from  db3.user where  name like  '___';
//匹配三个字符
mysql> select  name  from  db3.user where  name like  '____';
//匹配四个字符
mysql> select  name  from  db3.user where  name like  '%a%';
//匹配带有a前后有任意字符
mysql> select  name  from  db3.user where  name like  'a%';
//匹配a后带有任意字符

2、正则表达式

• 用法
  • where 字段名 regexp ‘正则表达式’
  • 正则元字符 ^ $ . [] * |
• 示例

mysql> select name , uid  from   db3.user where  name  regexp '^r|t$' ;
//查找以r开头和以t结尾

3、四则运算

• 字段必须是数值类型
  • 加减乘除"+ - * /"、取余"%"、提高优先级"()"
• 示例

mysql> update db3.user set uid=uid+1   where id <= 5;			
mysql> update db3.user set uid=uid-1   where id <= 5;	
		
mysql> alter table db3.user add age tinyint unsigned  not null default 19 after name ;

mysql> select name  ,  2020  - age  s_year  from  db3.user where name = "root" ;
mysql> select name  ,  2020  - age  s_year  from  db3.user ;
mysql> select name,uid,gid ,(uid+gid)/2  pjf   from db3.user  where  name="games";			
mysql> select * from db3.user where id % 2 = 0 ;
mysql> select name , uid  from  db3.user where  uid % 2  !=  0 ;

三、操作查询结果

把查找出来的数据，再处理一下

1、聚集函数

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

mysql> select max(uid) from db3.user ;
//查询uid最大的行
mysql> select max(uid) from db3.user  where id <= 10;
//查询uid<=10的并且是最大的
mysql> select min(uid) from db3.user  where id <= 10;
//查询uid<=10的并且是最小的
mysql> select avg(uid) from db3.user;
//uid的平均值
mysql> select count(name) from  db3.user where shell!="/bin/bash";
//查询/bin/bash解释器的有多少个
mysql> select count(*) from  db3.user;
//查询总共多少行
mysql> select min(uid) , max(gid) from db3.user;
//查询最小uid和最大gid
mysql> select min(uid) zx , max(gid) zd from db3.user;
//给提取出来的结果起名字，临时显示

2、查询结果排序

• 用法
  • SQL查询 order by 字段名 [asc|desc];
  • asc 升序
  • desc 降序
• 示例

 mysql> select name , uid  from  db3.user  where  uid >=10 and  uid <= 800  order by uid asc;

 mysql> select name , uid  from  db3.user  where  uid >=10 and  uid <= 800  order by uid  desc;

3、查询结果分组

• 用法
  • SQL查询 group by 字段名;
• 示例

mysql> select shell  from  db3.user where uid >= 10  group by shell;
mysql> select shell from db3.user group by shell;

• distinct 去重显示 （去掉字段的重复值 输出查询结果）

mysql> select shell from db3.user;
mysql> select  distinct shell from db3.user ;

4、查询结果过滤

• having用法
  • SQL查询 having 条件表达式;
• 示例

mysql> select name from  db3.user where shell != "/bin/bash"  having  name in ("sync","games");

5、限制查询结果显示行数

• 用法
  • SQL查询 limit 数字; //显示查询结果前过少条记录
  • SQL查询 limit 数字1,数字2; //显示指定范围内的查询记录
  • 数字1 起始行 (0表示第一行)
  • 数字2 总行数
• 示例：指定字段的前1、3、5行

 mysql> select name , uid  , gid  from db3.user where  shell !=  "/bin/bash" limit 1;
 mysql> select name , uid  , gid  from db3.user where  shell !=  "/bin/bash" limit 3;
 mysql> select name , uid  , gid  from db3.user where  shell !=  "/bin/bash" limit 5;

• 示例：范围查询，指定字段

mysql> select id ,name , uid  , gid  from db3.user limit 2 , 5;
//查询第二行的后五行，不包括前两行
mysql> select id ,name , uid  , gid  from db3.user limit 3 , 3;
//查询第三行的后三行，不包括前三行

• 示例：全表范围查询

select  *  from  db3.user  where id  <= 10  limit  5 , 2;
//查询第五行的后两行，不包括第五行

最后

以上就是愉快高跟鞋为你收集整理的MySQL匹配条件和操作查询结果一、基本匹配条件二、高级匹配条件三、操作查询结果的全部内容，希望文章能够帮你解决MySQL匹配条件和操作查询结果一、基本匹配条件二、高级匹配条件三、操作查询结果所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。