条件查询
条件查询
语法格式:
select
字段,字段…
from
表名
where
条件;
执行顺序:先from,然后where,最后select
查询工资等于5000的员工姓名?
select ename from emp where sal = 5000;
±------+
| ename |
±------+
| KING |
±------+
查询smith的工资?
select sal from emp where ename = ‘smith’;
±-------+
| sal |
±-------+
| 800.00 |
±-------+
找出工资高于3000的员工?
select ename from emp where sal >=3000;
±------+
| ename |
±------+
| SCOTT |
| KING |
| FORD |
±------+
找出工资不等于3000的员工?
select ename,sal from emp where sal ==<>==3000;
select ename,sal from emp where sal ==! ===3000;
找出工资在1100和3000之间的员工,包括1100和3000?
select ename,sal from emp where sal >=1100 and sal <=3000;
select ename,sal from emp where sal between 1100 and 3000;
//between …and …是闭区间 [ 1100 ~ 3000 ] 等同于 >= …<==
select ename,sal from emp where sal between 3000 and 1100;
//查询不到任何数据
between and 在使用过程中必须左小右大
between and 除了可以使用在数字方面之外,还可以使用在字符串方面
select ename from emp where ename between ‘A’ and ‘c’;//左闭右开
±------+
| ename |
±------+
| ALLEN |
| BLAKE |
| ADAMS |
±------+
找出哪些人津贴为null?
在数据库当中null不是一个值,代表什么都没有,为空
空不是一个值,不能用等号衡量
必须使用 is null 或者 is not null
select ename,sal,comm from emp where comm is null;
±-------±--------±-----+
| ename | sal | comm |
±-------±--------±-----+
| SMITH | 800.00 | NULL |
| JONES | 2975.00 | NULL |
| BLAKE | 2850.00 | NULL |
| CLARK | 2450.00 | NULL |
| SCOTT | 3000.00 | NULL |
| KING | 5000.00 | NULL |
| ADAMS | 1100.00 | NULL |
| JAMES | 950.00 | NULL |
| FORD | 3000.00 | NULL |
| MILLER | 1300.00 | NULL |
±-------±--------±-----+
找出哪些人津贴不为null?
select sal,comm,ename from emp where comm is not null;
±--------±--------±-------+
| sal | comm | ename |
±--------±--------±-------+
| 1600.00 | 300.00 | ALLEN |
| 1250.00 | 500.00 | WARD |
| 1250.00 | 1400.00 | MARTIN |
| 1500.00 | 0.00 | TURNER |
±--------±--------±-------+
找出哪些人没有津贴?
select ename,sal,comm from emp where comm is null or comm =0;
±-------±--------±-----+
| ename | sal | comm |
±-------±--------±-----+
| SMITH | 800.00 | NULL |
| JONES | 2975.00 | NULL |
| BLAKE | 2850.00 | NULL |
| CLARK | 2450.00 | NULL |
| SCOTT | 3000.00 | NULL |
| KING | 5000.00 | NULL |
| TURNER | 1500.00 | 0.00 |
| ADAMS | 1100.00 | NULL |
| JAMES | 950.00 | NULL |
| FORD | 3000.00 | NULL |
| MILLER | 1300.00 | NULL |
±-------±--------±-----+
找出工作岗位是manager和salesman的员工?
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 |
±-------±---------+
and 和 or 联合起来用,找出薪资大于1000的并且部门编号是20或者30的员工
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 |
| JAMES | 950.00 | 30 |
| FORD | 3000.00 | 20 |
select ename,sal,deptno from emp where sal>1000 and (deptno = 20 or deptno =30);//正确的
注意:当运算符的优先级不确定的时候加小括号
in 等同于 or :找出工作岗位是manager 和 salesman的员工?
select ename,job from emp where job =‘salesman’ or job=‘manager’;
select ename,job from emp where job in(‘salesman’,‘manager’);
select ename,job from emp where job in(1000,5000);
//in后面的值不是区间,是具体的值
not in:不在这几个值中
select ename,job from emp where job not in(1000,5000);
模糊查询 like?
找出名字当中含有O的?
(在模糊查询中,必须掌握两个特殊的符号,一个是%,一个是_)
%代表任意多个字符,_代表任意一个字符
select ename from emp where ename like ‘%o%’;
±------+
| ename |
±------+
| JONES |
| SCOTT |
| FORD |
找出名字中第二个字母是a的?
select ename from emp where ename like ‘_a%’;
±-------+
| ename |
±-------+
| WARD |
| MARTIN |
| JAMES |
±-------+
找出名字中有下划线的?
select ename from emp where ename like ‘%/_’%;
找出名字中最后一个字母是t的?
select ename from emp where ename like ‘%t’;
±----------+
| ename |
±----------+
| SCOTT |
±---------+