不是将表中所有数据都查出来。是查询出来符合条件的。
语法格式:
select
字段1,字段2,字段3....
from
表名
where
条件;
14.2、都有哪些条件?
= 等于
查询薪资等于800的员工姓名和编号?
select empno,ename from emp where sal = 800;
查询SMITH的编号和薪资?
select empno,sal from emp where ename = 'SMITH'; //字符串使用单引号
<>或!= 不等于
查询薪资不等于800的员工姓名和编号?
select empno,ename from emp where sal != 800;
select empno,ename from emp where sal <> 800; // 小于号和大于号组成的不等号
< 小于
查询薪资小于2000的员工姓名和编号?
mysql> select empno,ename,sal from emp where sal < 2000;
+-------+--------+---------+
| empno | ename | sal |
+-------+--------+---------+
| 7369 | SMITH | 800.00 |
| 7499 | ALLEN | 1600.00 |
| 7521 | WARD | 1250.00 |
| 7654 | MARTIN | 1250.00 |
| 7844 | TURNER | 1500.00 |
| 7876 | ADAMS | 1100.00 |
| 7900 | JAMES | 950.00 |
| 7934 | MILLER | 1300.00 |
+-------+--------+---------+
<= 小于等于
查询薪资小于等于3000的员工姓名和编号?
select empno,ename,sal from emp where sal <= 3000;
> 大于
查询薪资大于3000的员工姓名和编号?
select empno,ename,sal from emp where sal > 3000;
>= 大于等于
查询薪资大于等于3000的员工姓名和编号?
select empno,ename,sal from emp where sal >= 3000;
between … and …. 两个值之间, 等同于 >= and <=
查询薪资在2450和3000之间的员工信息?包括2450和3000
第一种方式:>= and <= (and是并且的意思。)
select empno,ename,sal from emp where sal >= 2450 and sal <= 3000;
+-------+-------+---------+
| empno | ename | sal |
+-------+-------+---------+
| 7566 | JONES | 2975.00 |
| 7698 | BLAKE | 2850.00 |
| 7782 | CLARK | 2450.00 |
| 7788 | SCOTT | 3000.00 |
| 7902 | FORD | 3000.00 |
+-------+-------+---------+
第二种方式:between … and …
select
empno,ename,sal
from
emp
where
sal between 2450 and 3000;
注意:
使用between and的时候,必须遵循左小右大。
between and是闭区间,包括两端的值。
is null 为 null(is not null 不为空)
查询哪些员工的津贴/补助为null?
mysql> select empno,ename,sal,comm from emp where comm = null;
Empty set (0.00 sec)
mysql> select empno,ename,sal,comm from emp where comm is null;
+-------+--------+---------+------+
| empno | ename | sal | comm |
+-------+--------+---------+------+
| 7369 | SMITH | 800.00 | NULL |
| 7566 | JONES | 2975.00 | NULL |
| 7698 | BLAKE | 2850.00 | NULL |
| 7782 | CLARK | 2450.00 | NULL |
| 7788 | SCOTT | 3000.00 | NULL |
| 7839 | KING | 5000.00 | NULL |
| 7876 | ADAMS | 1100.00 | NULL |
| 7900 | JAMES | 950.00 | NULL |
| 7902 | FORD | 3000.00 | NULL |
| 7934 | MILLER | 1300.00 | NULL |
+-------+--------+---------+------+
10 rows in set (0.00 sec)
注意:在数据库当中null不能使用等号进行衡量。需要使用is null
因为数据库中的null代表什么也没有,它不是一个值,所以不能使用
等号衡量。
查询哪些员工的津贴/补助不为null?
select empno,ename,sal,comm from emp where comm is not null;
+-------+--------+---------+---------+
| empno | ename | sal | comm |
+-------+--------+---------+---------+
| 7499 | ALLEN | 1600.00 | 300.00 |
| 7521 | WARD | 1250.00 | 500.00 |
| 7654 | MARTIN | 1250.00 | 1400.00 |
| 7844 | TURNER | 1500.00 | 0.00 |
+-------+--------+---------+---------+
and 并且
查询工作岗位是MANAGER并且工资大于2500的员工信息?
select
empno,ename,job,sal
from
emp
where
job = 'MANAGER' and sal > 2500;
+-------+-------+---------+---------+
| empno | ename | job | sal |
+-------+-------+---------+---------+
| 7566 | JONES | MANAGER | 2975.00 |
| 7698 | BLAKE | MANAGER | 2850.00 |
+-------+-------+---------+---------+
or 或者
查询工作岗位是MANAGER和SALESMAN的员工?
select empno,ename,job from emp where job = 'MANAGER';
select empno,ename,job from emp where job = 'SALESMAN';
select
empno,ename,job
from
emp
where
job = 'MANAGER' or job = 'SALESMAN';
+-------+--------+----------+
| empno | ename | job |
+-------+--------+----------+
| 7499 | ALLEN | SALESMAN |
| 7521 | WARD | SALESMAN |
| 7566 | JONES | MANAGER |
| 7654 | MARTIN | SALESMAN |
| 7698 | BLAKE | MANAGER |
| 7782 | CLARK | MANAGER |
| 7844 | TURNER | SALESMAN |
+-------+--------+----------+
and和or同时出现的话,有优先级问题吗?
查询工资大于2500,并且部门编号为10或20部门的员工?
select
*
from
emp
where
sal > 2500 and deptno = 10 or deptno = 20;
分析以上语句的问题?
and优先级比or高。
以上语句会先执行and,然后执行or。
以上这个语句表示什么含义?
找出工资大于2500并且部门编号为10的员工,或者20部门所有员工找出来。
select
*
from
emp
where
sal > 2500 and (deptno = 10 or deptno = 20);
and和or同时出现,and优先级较高。如果想让or先执行,需要加“小括号”
以后在开发中,如果不确定优先级,就加小括号就行了。
in 包含,相当于多个 or (not in 不在这个范围中)
查询工作岗位是MANAGER和SALESMAN的员工?
select empno,ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';
select empno,ename,job from emp where job in('MANAGER', 'SALESMAN');
+-------+--------+----------+
| empno | ename | job |
+-------+--------+----------+
| 7499 | ALLEN | SALESMAN |
| 7521 | WARD | SALESMAN |
| 7566 | JONES | MANAGER |
| 7654 | MARTIN | SALESMAN |
| 7698 | BLAKE | MANAGER |
| 7782 | CLARK | MANAGER |
| 7844 | TURNER | SALESMAN |
+-------+--------+----------+
注意:in不是一个区间。in后面跟的是具体的值。
查询薪资是800和5000的员工信息?
select ename,sal from emp where sal = 800 or sal = 5000;
select ename,sal from emp where sal in(800, 5000); //这个不是表示800到5000都找出来。
+-------+---------+
| ename | sal |
+-------+---------+
| SMITH | 800.00 |
| KING | 5000.00 |
+-------+---------+
select ename,sal from emp where sal in(800, 5000, 3000);
// not in 表示不在这几个值当中的数据。
select ename,sal from emp where sal not in(800, 5000, 3000);
+--------+---------+
| ename | sal |
+--------+---------+
| 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 |
+--------+---------+
not 可以取非,主要用在 is 或 in 中
is null
is not null
in
not in
like
称为模糊查询,支持%或下划线匹配
%匹配任意多个字符
下划线:任意一个字符。
(%是一个特殊的符号,_ 也是一个特殊符号)
找出名字中含有O的?
mysql> select ename from emp where ename like '%O%';
+-------+
| ename |
+-------+
| JONES |
| SCOTT |
| FORD |
+-------+
找出名字以T结尾的?
select ename from emp where ename like '%T';
找出名字以K开始的?
select ename from emp where ename like 'K%';
找出第二个字每是A的?
select ename from emp where ename like '_A%';
找出第三个字母是R的?
select ename from emp where ename like '__R%';
select name from t_student where name like '%_%'; //这样不行。
mysql> select name from t_student where name like '%\_%'; // \转义字符。
+----------+
| name |
+----------+
| jack_son |
+----------+