一、基本匹配条件
- 环境准备
[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 (值列表) | 在…里… |
- 示例
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;
//查询第三行的后三行,不包括前三行
评论(0)
您还未登录,请登录后发表或查看评论