mysql>select from emp where sal)2000; EMPNO ENAME I JOB MGR HIREDATE SAL COMM DEPTNO 7566 I MANAGER 7839 11981-04-0212975 NULL 7698 7788 SCOTT ANALYST :1987-84-13 83 1 81-123 6 rows in set (0.05 sec> 示例2：查询出每月可以得到奖金的员工？(ISNOT NULL) Select from emp where comm IS NOT NULL; mysql>Select from emp where conm IS NOT NULL; EMPNO:ENAME JOB MGR I HIREDATE SAL COMM DEPTNO 7521: 76598 18981-9-8168140g 4 rows in set(.1sec 示例3：查询出没有奖金的员工？ Select from emp where comm Is NULL: 示例4：查询出工资大于15O0并且可以章奖金的员工？(AND关键字) Select from emp where sal>1500 and comm IS NOT NULL
示例 2：查询出每月可以得到奖金的员工？（IS NOT NULL） Select * from emp where comm IS NOT NULL; 示例 3：查询出没有奖金的员工？ Select * from emp where comm IS NULL; 示例 4：查询出工资大于 1500 并且可以拿奖金的员工？（AND 关键字） Select * from emp where sal>1500 and comm IS NOT NULL;
nysql>Select from emp where sal>1508 and comm IS NOT NULL; EMPNO ENAME I JOB I MGR I HIREDATE I SAL I COMM DEPTNO :7499:ALLEN:SALESMAN:7698:1981-g2-28:1686:366:38: 1 row in set (0.02 sec> 示例5：查询出工资大于1500，或者可以奖金的员工？(关键字) Select from emp where sal>1500 OR comm IS NOT NULL mysql>Select fron emp where sal)1508 or comn IS NOT NULL; EMPNO ENAME I JOB MGR HIREDATE SAL COMM DEPTNO WARD 1981 1250 65 RTIN SALES 7698 !1981-69-28 1250 148 78391981 2458 油3000000 84 ER 10 rows in set (8.8s6c 示例6：查询出工资不大于1500并且不可以奖金的员工？(NOT关键字) Select from emp where NOT (sal>1500 and comm IS NOT NULL);
示例 5：查询出工资大于 1500,或者可以拿奖金的员工？（OR 关键字） Select * from emp where sal>1500 OR comm IS NOT NULL; 示例 6：查询出工资不大于 1500 并且不可以拿奖金的员工？（NOT 关键字） Select * from emp where NOT (sal>1500 and comm IS NOT NULL);
mysql>Select from enp where NOT (sal>1500 and comn IS NOT NULL>; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7369 SHITH CLERK :7902 1980-12-17:800:ULL 20 98 a4 295。 54 ESMAN 17698 1981-09-28 7782 7839 2458 3 CLERK 769 1981-1283 油0000 7934:MILLER CLERK 7782 :1982-12-23 1300 NULL 13 rous in set (0.00 sec> 示例7：查询出工资大于1500但是小于30o0的员工？(between and关键字) Select*from emp where sal>1500 and sal<3000; mysql>Select from emp where sal>1508 and sal<3080; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 499 ALLEN:SALESHAN26981981-g220160g 7566 JONES MANAGER 78391981-84-822925 NULL 28 7%82 CLARK7839198180925wL in set (0.00 sec>
示例 7：查询出工资大于 1500 但是小于 3000 的员工？（between and 关键字） Select * from emp where sal>1500 and sal<3000;
等同于： Select from emp where sal between 1500 and 3000; 注意between and之间的数字是有顺序的！原则是从小到大！ 字段between最小值and最大值 mysql>Select from emp where sal betveen 1500 and 3000; EHPNO ENAME 1 JOB :MGR HIREDAIE SAL COMM:DEPTNO 7499 69R 1981-02-20 7566 1981-64-82 2925 LYST 1987a4-13 7902:FORD ANALYST 7566:1981-12-83386 NULL rows in set <0.00 sec> 思考：为什么查询出来分别是4行记录和7行记录？ 操作符BETWEEN.AND会选取介于两个值之间的数据范围。这些值可以是数值、 文本或者日期 SQL BETWEEN语法： SELECT column_name(s) FROM table name WHERE column name BETWEEN valuel ANd value2
等同于： Select * from emp where sal between 1500 and 3000; 注意 between and 之间的数字是有顺序的！原则是从小到大！ 字段 between 最小值 and 最大值 思考：为什么查询出来分别是 4 行记录和 7 行记录？ 操作符 BETWEEN . AND 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。 SQL BETWEEN 语法： SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2
示例8：查询出姓名为SMTH的员工的信息（字符串查询）。 Select from emp where ename='SMITH'; nysql>Select fron enp where ename EMPNO ENAME JOB HGR HIREDATE SAL COMM DEPTNO :7369:SMITH:CLERK:79g2:1988-12-17:8G0:NULL:20: 1 row in set (0.04 sec> 注意：对于字符串查询时区分大小写的，如SMITH和smith是有区别的 示例8：查询出编号为7499,7566,7698的员工的信息(N关键字)
示例 8：查询出姓名为 SMITH 的员工的信息（字符串查询）。 Select * from emp where ename=’SMITH’; 注意：对于字符串查询时区分大小写的，如 SMITH 和 smith 是有区别的。 示例 8：查询出编号为 7499，7566,7698 的员工的信息（IN 关键字）