目录

Mysql-条件查询

3

条件查询命令格式

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 字段名 判断条件;


推荐文章

Mysql-创建表