一、基本匹配条件

  • 环境准备
[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、数值比较

  • 字段必须是数值类型
类型 比较 例子
= 相等 id = 3
> 大于 uid > 3
>= 大于或等于 uid >= 3
< 小于 uid < 3
<= 小于等于 uid <= 3
!= 不相等 uid != 3
  • 示例
mysql> select  *   from db3.user  where  id <= 5 ;
mysql> select  name  ,  uid  , gid   from db3.user  where  uid  !=  gid ;

2、字符比较

  • 必须是字符类型
类型 比较 例子
= 相等 name = “root”
!= 不相等 name != “root”
is null 空 shell is null
is not null 非空 shell is not null
  • 示例
 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、逻辑匹配

  • 多个判断条件时使用
类型 用途 格式
or 逻辑或 条件1 or 条件2 or 条件3
and 逻辑与 条件1 and 条件2 and 条件3
! 或 not 逻辑非
  • 示例
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、范围匹配

  • 匹配范围内的任意一个值即可
类型 比较
in (值列表) 在…里…

not in (值列表) |不在…里…|
| between 数字 and 数字 | 在…之间…|

  • 示例
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;
//查询第三行的后三行，不包括前三行
  • 示例：全表范围查询
mysql> select id ,name , uid  , gid  from db3.user limit 2 , 5;
//查询第二行的后五行，不包括前两行
mysql> select id ,name , uid  , gid  from db3.user limit 3 , 3;
//查询第三行的后三行，不包括前三行