Mysql-条件查询
条件查询命令格式
select 字段名列表名 from 库名.表名 where 查询条件; #只查询匹配项
条件查询有如下类型
- 数值比较查询
- 字符比较查询
- 范围匹配查询
- 模糊匹配查询
- 正则匹配查询
- 逻辑比较查询
- 别名、拼接、去重查询
别名、拼接、去重
-
别名:
select 字段名 as 别名 from 库名.表名#将name设置别名为用户名、将homedir设置别名为家目录 mysql> select name as 用户名, homedir as 家目录 from tarena.user; +-----------------+--------------------+ | 用户名 | 家目录 | +-----------------+--------------------+ | root | /root | | bin | /bin | | daemon | /sbin | | adm | /var/adm | | lp | /var/spool/lpd | | sync | /sbin | | shutdown | /sbin | | halt | /sbin | | mail | /var/spool/mail | | operator | /root | | games | /usr/games | | ftp | /var/ftp | | nobody | / | | systemd-network | / | | dbus | / | | polkitd | / | | sshd | /var/empty/sshd | | postfix | /var/spool/postfix | | chrony | /var/lib/chrony | | rpc | /var/lib/rpcbind | | rpcuser | /var/lib/nfs | | nfsnobody | /var/lib/nfs | | haproxy | /var/lib/haproxy | +-----------------+--------------------+ 23 rows in set (0.00 sec) -
拼接:
select concat(字段名,"拼接符",字段名) from 库名.表名;# 拼接name和uid字段,使用 ---- 符号作为拼接 mysql> select concat(name,"----",uid) from tarena.user; +-------------------------+ | concat(name,"----",uid) | +-------------------------+ | root----0 | | bin----1 | | daemon----2 | | adm----3 | | lp----4 | | sync----5 | | shutdown----6 | | halt----7 | | mail----8 | | operator----11 | | games----12 | | ftp----14 | | nobody----99 | | systemd-network----192 | | dbus----81 | | polkitd----999 | | sshd----74 | | postfix----89 | | chrony----998 | | rpc----32 | | rpcuser----29 | | nfsnobody----65534 | | haproxy----188 | +-------------------------+ 23 rows in set (0.00 sec) -
去重:
select distinct 字段名 from 库名.表名;# 去重shell字段下的内容 mysql> select distinct shell from user; +----------------+ | shell | +----------------+ | /bin/bash | | /sbin/nologin | | /bin/sync | | /sbin/shutdown | | /sbin/halt | +----------------+ 5 rows in set (0.00 sec)
数值比较
数值比较可以使用 = 、<、>、>=、<=、等符号来比较,但是符号的两边必须是数据类型
select 字段1,字段2,字段3.. from 库名.表名 where 比较条件;
## 查询uid等于gid的数据
mysql> select name,uid,gid from tarena.user where uid = gid;
+-----------------+-------+-------+
| name | uid | gid |
+-----------------+-------+-------+
| root | 0 | 0 |
| bin | 1 | 1 |
| daemon | 2 | 2 |
| nobody | 99 | 99 |
| systemd-network | 192 | 192 |
| dbus | 81 | 81 |
| sshd | 74 | 74 |
| postfix | 89 | 89 |
| rpc | 32 | 32 |
| rpcuser | 29 | 29 |
| nfsnobody | 65534 | 65534 |
| haproxy | 188 | 188 |
+-----------------+-------+-------+
12 rows in set (0.00 sec)
字符比较
字符比较可以使用 = 、!=、is null 、is not null、等符号来比较,但是字符串必须要用引号引起
select 字段1,字段2,字段3.. from 库名.表名 where 比较条件;
mysql> select name,shell from tarena.user where shell = "/bin/bash" ;
+------+-----------+
| name | shell |
+------+-----------+
| root | /bin/bash |
+------+-----------+
1 row in set (0.00 sec)
范围匹配
匹配范围内的任意一个值即可
| 比较符号 | 说明 | 命令 |
|---|---|---|
| in (值列表) | 在.....里 | select 字段名 from 库名.表名 where 字段名 in (条件1,条件2,条件3...); |
| not in (值列表) | 不在....里 | select 字段名 from 库名.表名 where 字段名 not in (条件1,条件2,条件3...); |
| between 数字 and 数字 | 在....之间 | select 字段名 from 库名.表名 where 字段名 between 数字1 and 数字2; |
## 查找uid匹配1,3,5,7随意一项的
mysql> select name,uid from tarena.user where uid in (1,3,5,7,898);
+------+------+
| name | uid |
+------+------+
| bin | 1 |
| adm | 3 |
| sync | 5 |
| halt | 7 |
+------+------+
4 rows in set (0.00 sec)
## 查找uid号在1和5之间的数据,包含1和5
mysql> select name,uid from tarena.user where uid between 1 and 5;
+--------+------+
| name | uid |
+--------+------+
| bin | 1 |
| daemon | 2 |
| adm | 3 |
| lp | 4 |
| sync | 5 |
+--------+------+
5 rows in set (0.00 sec)
模糊匹配
用法:select 字段名 from 库名.表名 where 字段名 like 通配符
通配符:"_" 表示1个字符、"%" 表示0到多个字符
## 查找name字段值是4个字符的数据
mysql> select name from tarena.user where name like '____';
+------+
| name |
+------+
| root |
| sync |
| halt |
| mail |
| dbus |
| sshd |
+------+
6 rows in set (0.00 sec)
## 查找name字段值里有字符a的数据
mysql> select name from tarena.user where name like '%a%';
+----------+
| name |
+----------+
| daemon |
| adm |
| halt |
| mail |
| operator |
| games |
| haproxy |
+----------+
7 rows in set (0.00 sec)
## 查找name字段值至少有5个字符的数据
mysql> select name from tarena.user where name like '_____%';
+-----------------+
| name |
+-----------------+
| daemon |
| shutdown |
| operator |
| games |
| nobody |
| systemd-network |
| polkitd |
| postfix |
| chrony |
| rpcuser |
| nfsnobody |
| haproxy |
+-----------------+
12 rows in set (0.00 sec)
正则匹配
用法:select 字段名 from 库名.表名 where 字段名 regexp '正则表达式';
元字符(正则表达式)
^ 行首
$ 行尾
. 1个字符
[] 范围内
***** 前边表达式出现零次或多次
| 或
.* 表示匹配所有
## 匹配name字段值里有sh字母的数据
mysql> select name from tarena.user where name regexp '.*sh.*';
+----------+
| name |
+----------+
| shutdown |
| sshd |
+----------+
2 rows in set (0.00 sec)
逻辑匹配
有多个判断条件
与 and &&
或 or ||
非 not !
用法:select 字段名 from 库名.表名 where 字段名 判断条件;