【13-18】条件查询
2022-02-10 20:36:00 # MySQL

语法格式

1
2
3
4
5
6
select
    字段,字段...
from 
    表名
where
    条件;

查询工资等于5000的员工姓名

1
2
3
4
5
6
mysql> SELECT ename from emp where sal = 5000;
+-------+
| ename |
+-------+
| KING  |
+-------+

查询SMITH的工资

1
2
3
4
5
6
mysql> select sal from emp where ename = 'SMITH';
+--------+
| sal    |
+--------+
| 800.00 |
+--------+

查找工资高于3000的员工

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> select ename from emp where sal > 3000;
+-------+
| ename |
+-------+
| KING  |
+-------+
1 row in set (0.00 sec)

mysql> select ename from emp where sal >= 3000;
+-------+
| ename |
+-------+
| SCOTT |
| KING  |
| FORD  |
+-------+

查找工资不等于3000的员工

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> select ename from emp where sal <> 3000;
+--------+
| ename  |
+--------+
| SMITH  |
| ALLEN  |
| WARD   |
| JONES  |
| MARTIN |
| BLAKE  |
| CLARK  |
| KING   |
| TURNER |
| ADAMS  |
| JAMES  |
| MILLER |
+--------+

查找工资在[1100,3000]的员工

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
mysql> select ename from emp where 1100 <= sal and sal <= 3000;
+--------+
| ename  |
+--------+
| ALLEN  |
| WARD   |
| JONES  |
| MARTIN |
| BLAKE  |
| CLARK  |
| SCOTT  |
| TURNER |
| ADAMS  |
| FORD   |
| MILLER |
+--------+
11 rows in set (0.00 sec)

mysql> select ename from emp where sal between 1100 and 3000;
+--------+
| ename  |
+--------+
| ALLEN  |
| WARD   |
| JONES  |
| MARTIN |
| BLAKE  |
| CLARK  |
| SCOTT  |
| TURNER |
| ADAMS  |
| FORD   |
| MILLER |
+--------+

between…and…

查找首字母在[‘A’,’B’)的员工姓名

1
2
3
4
5
6
7
8
9
mysql> select ename from emp where ename between 'A' and 'A';

mysql> select ename from emp where ename between 'A' and 'B';
+-------+
| ename |
+-------+
| ALLEN |
| ADAMS |
+-------+

is null 和 is not null

null 不是一个值，不能用’=’衡量

查找哪些员工无津贴

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
mysql> select ename,comm from emp where comm is null;
+--------+------+
| ename  | comm |
+--------+------+
| SMITH  | NULL |
| JONES  | NULL |
| BLAKE  | NULL |
| CLARK  | NULL |
| SCOTT  | NULL |
| KING   | NULL |
| ADAMS  | NULL |
| JAMES  | NULL |
| FORD   | NULL |
| MILLER | NULL |
+--------+------+
10 rows in set (0.00 sec)

mysql> select ename,comm from emp where comm is not null;
+--------+---------+
| ename  | comm    |
+--------+---------+
| ALLEN  |  300.00 |
| WARD   |  500.00 |
| MARTIN | 1400.00 |
| TURNER |    0.00 |
+--------+---------+
4 rows in set (0.00 sec)

and 和 or 的优先级

and 优先级比 or 高

找出工作岗位是MANAGER或SALESMAN的员工

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';
+--------+----------+
| ename  | job      |
+--------+----------+
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| JONES  | MANAGER  |
| MARTIN | SALESMAN |
| BLAKE  | MANAGER  |
| CLARK  | MANAGER  |
| TURNER | SALESMAN |
+--------+----------+
7 rows in set (0.00 sec)

找出薪资大于1000的并且部门编号是20或30部门的员工

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select ename,sal,deptno from emp where sal > 1000 and (deptno = 20 or deptno = 30);
+--------+---------+--------+
| ename  | sal     | deptno |
+--------+---------+--------+
| ALLEN  | 1600.00 |     30 |
| WARD   | 1250.00 |     30 |
| JONES  | 2975.00 |     20 |
| MARTIN | 1250.00 |     30 |
| BLAKE  | 2850.00 |     30 |
| SCOTT  | 3000.00 |     20 |
| TURNER | 1500.00 |     30 |
| ADAMS  | 1100.00 |     20 |
| FORD   | 3000.00 |     20 |
+--------+---------+--------+
9 rows in set (0.00 sec)

in 和 not in

等同于 ‘or’

查询工作岗位是MANAGER或者SALESMAN的员工

1
2
3
4
5
6
7
8
9
10
11
12
mysql> select ename,job from emp where job in('SALESMAN','MANAGER');
+--------+----------+
| ename  | job      |
+--------+----------+
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| JONES  | MANAGER  |
| MARTIN | SALESMAN |
| BLAKE  | MANAGER  |
| CLARK  | MANAGER  |
| TURNER | SALESMAN |
+--------+----------+

查询工资是3000或者5000的员工

1
2
3
4
5
6
7
8
9
mysql> select ename,sal from emp where sal in(3000,5000);
+-------+---------+
| ename | sal     |
+-------+---------+
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+
3 rows in set (0.00 sec)

查询工资不是3000也不是5000的员工

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> select ename,sal from emp where sal not in(3000,5000);
+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| MILLER | 1300.00 |
+--------+---------+
11 rows in set (0.00 sec)

like

  1. ‘%’：任意多个字符
  2. ‘_’：任意一个字符

查询名字之中含有’O’的员工

1
2
3
4
5
6
7
8
9
mysql> select ename from emp where ename like '%O%';
+-------+
| ename |
+-------+
| JONES |
| SCOTT |
| FORD  |
+-------+
3 rows in set (0.00 sec)

查询第二个字母是’A’的员工

1
2
3
4
5
6
7
8
9
mysql> select ename from emp where ename like '_A%';
+--------+
| ename  |
+--------+
| WARD   |
| MARTIN |
| JAMES  |
+--------+
3 rows in set (0.00 sec)

查找名字中有下划线的员工

使用转义字符’'

先创建一下表和插入数据 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
mysql> create table t_user(
    ->  id int,
    ->  name varchar(255)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t_user(id,name) values(1,'zhangsan');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_user(id,name) values(2,'lisi');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_user(id,name) values(3,'wangwu');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_user(id,name) values(4,'xx_xx');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_user;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
|    2 | lisi     |
|    3 | wangwu   |
|    4 | xx_xx    |
+------+----------+
4 rows in set (0.00 sec)

查询 

1
2
3
4
5
6
7
mysql> select name from t_user where name like '%\_%';
+-------+
| name  |
+-------+
| xx_xx |
+-------+
1 row in set (0.00 sec)

查询名字最后一个字母是’T’的员工

1
2
3
4
5
6
7
mysql> select ename from emp where ename like '%T';
+-------+
| ename |
+-------+
| SCOTT |
+-------+
1 row in set (0.00 sec)
Prev
2022-02-10 20:36:00 # MySQL
Next