Ai分享|
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 |
什么是条件查询？ 不是将表中所有数据都查出来。是查询出来符合条件的。 语法格式： select 字段1,字段2,字段3.... from 表名 where 条件; 都有哪些条件？ 等于 : = 不等于 : <> 或 != 小于 : < 大于 : > 小于等于 : <= 大于等于 : >= 两值之间 : between 空值 : null 不为空值 : is not null 并且 : and 或者 : or 包含 : in 取非 : not 模糊查询 : like |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
//等于 : = //查询薪资等于800的员工姓名和编号？ mysql> select empno,ename from emp where sal = 800; +-------+-------+ | empno | ename | +-------+-------+ | 7369 | SMITH | +-------+-------+ //查询SMITH的编号和薪资？ ////字符串使用单引号 mysql> select empno,sal from emp where ename = 'SMITH'; +-------+--------+ | empno | sal | +-------+--------+ | 7369 | 800.00 | +-------+--------+ |
|
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 35 36 37 38 39 |
//不等于 : <> 或 != //查询薪资不等于800的员工姓名和编号？ mysql> select empno,ename from emp where sal != 800; +-------+--------+ | empno | ename | +-------+--------+ | 7499 | ALLEN | | 7521 | WARD | | 7566 | JONES | | 7654 | MARTIN | | 7698 | BLAKE | | 7782 | CLARK | | 7788 | SCOTT | | 7839 | KING | | 7844 | TURNER | | 7876 | ADAMS | | 7900 | JAMES | | 7902 | FORD | | 7934 | MILLER | +-------+--------+ mysql> select empno,ename from emp where sal <> 800; +-------+--------+ | empno | ename | +-------+--------+ | 7499 | ALLEN | | 7521 | WARD | | 7566 | JONES | | 7654 | MARTIN | | 7698 | BLAKE | | 7782 | CLARK | | 7788 | SCOTT | | 7839 | KING | | 7844 | TURNER | | 7876 | ADAMS | | 7900 | JAMES | | 7902 | FORD | | 7934 | MILLER | +-------+--------+ |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
//小于 : < //查询薪资小于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 | +-------+--------+---------+ |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
//小于等于 : <= //查询薪资小于等于3000的员工姓名和编号？ mysql> select empno,ename,sal from emp where sal <= 3000; +-------+--------+---------+ | empno | ename | sal | +-------+--------+---------+ | 7369 | SMITH | 800.00 | | 7499 | ALLEN | 1600.00 | | 7521 | WARD | 1250.00 | | 7566 | JONES | 2975.00 | | 7654 | MARTIN | 1250.00 | | 7698 | BLAKE | 2850.00 | | 7782 | CLARK | 2450.00 | | 7788 | SCOTT | 3000.00 | | 7844 | TURNER | 1500.00 | | 7876 | ADAMS | 1100.00 | | 7900 | JAMES | 950.00 | | 7902 | FORD | 3000.00 | | 7934 | MILLER | 1300.00 | +-------+--------+---------+ |
|
1 2 3 4 5 6 7 8 |
//大于 : > //查询薪资大于3000的员工姓名和编号？ mysql> select empno,ename,sal from emp where sal > 3000; +-------+-------+---------+ | empno | ename | sal | +-------+-------+---------+ | 7839 | KING | 5000.00 | +-------+-------+---------+ |
|
1 2 3 4 5 6 7 8 9 10 |
//大于等于 : >= //查询薪资大于等于3000的员工姓名和编号？ mysql> select empno,ename,sal from emp where sal >= 3000; +-------+-------+---------+ | empno | ename | sal | +-------+-------+---------+ | 7788 | SCOTT | 3000.00 | | 7839 | KING | 5000.00 | | 7902 | FORD | 3000.00 | +-------+-------+---------+ |
|
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 35 36 37 38 |
//两值之间 : between //between … and …. 两个值之间, 等同于 >= and <= //查询薪资在2450和3000之间的员工信息？包括2450和3000 //第一种方式：>= and <= （and是并且的意思。） mysql> 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是闭区间，包括两端的值。 */ mysql> select empno,ename,sal from emp where sal between 2450 and 3000; +-------+-------+---------+ | empno | ename | sal | +-------+-------+---------+ | 7566 | JONES | 2975.00 | | 7698 | BLAKE | 2850.00 | | 7782 | CLARK | 2450.00 | | 7788 | SCOTT | 3000.00 | | 7902 | FORD | 3000.00 | +-------+-------+---------+ |
|
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 |
//空值 : 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 | +-------+--------+---------+------+ /* 注意：在数据库当中null不能使用等号进行衡量。需要使用is null 因为数据库中的null代表什么也没有，它不是一个值，所以不能使用 等号衡量 */ |
|
1 2 3 4 5 6 7 8 9 10 11 |
//不为空值: is not null //查询哪些员工的津贴/补助不为null？ mysql> 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 | +-------+--------+---------+---------+ |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
//并且 : and //查询工作岗位是MANAGER并且工资大于2500的员工信息？ select empno,ename,job,sal from emp where job = 'MANAGER' and sal > 2500; mysql> 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 | +-------+-------+---------+---------+ |
|
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 |
//或者 : or //查询工作岗位是MANAGER和SALESMAN的员工？ select empno,ename,job from emp where job = 'MANAGER' or job = 'SALESMAN'; mysql> 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 | +-------+--------+----------+ |
|
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 35 36 37 38 39 40 41 |
//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先执行，需要加“小括号” 以后在开发中，如果不确定优先级，就加小括号就行了。 */ mysql> select -> * -> from -> emp -> where -> sal > 2500 and (deptno = 10 or deptno = 20); +-------+-------+-----------+------+------------+---------+------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+-------+-----------+------+------------+---------+------+--------+ | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | +-------+-------+-----------+------+------------+---------+------+--------+ |
|
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 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
//包含 : 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'); mysql> 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 | +-------+--------+----------+ //查询薪资是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都找出来。 mysql> select ename,sal from emp where sal in(800, 5000); +-------+---------+ | ename | sal | +-------+---------+ | SMITH | 800.00 | | KING | 5000.00 | +-------+---------+ // not in 表示不在这几个值当中的数据 //select ename,sal from emp where sal in(800, 5000, 3000); //select ename,sal from emp where sal not in(800, 5000, 3000); mysql> 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 | +--------+---------+ |
|
1 2 3 4 5 6 7 8 9 10 |
//取非 : not /* not 可以取非，主要用在 is 或 in 中 is null is not null in not in 使用方式可以往上看上面的几个例子 */ |
|
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 35 36 |
//模糊查询 : 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 '%_%'; //这样不行。 select name from t_student where name like '%\_%'; // \转义字符。 |
