本文共 3752 字，大约阅读时间需要 12 分钟。
以下是操作的表结构：
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
|---|---|---|---|---|---|---|---|
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
每个字段的字段名分别为：员工编号、员工姓名、员工职位、员工上级领导编号、员工雇佣日期，员工薪水、员工津贴、员工部门编号。
SELECT column_name, column_name ... FROM table_name WHERE condition;
执行顺序：先from，然后where，最后select。
查询工资等于5000的员工姓名：
SELECT ename, sal FROM emp WHERE sal = 5000;
结果如下：| ename | sal ||---------|-------|| KING | 5000.00 |
查询SMITH的工资：
SELECT sal FROM emp WHERE ename = 'SMITH';
结果如下：| sal ||-------|| 800.00 |
找出工资高于3000的员工：
SELECT ename, sal FROM emp WHERE sal > 3000;
结果如下：| ename | sal ||---------|--------|| KING | 5000.00 |
找出工资在1100和3000之间的员工（包括1100和3000）：
SELECT ename, sal FROM emp WHERE sal BETWEEN 1100 AND 3000;
结果如下：| ename | sal ||---------|--------|| JONES | 2975.00 || WARD | 1250.00 || MARTIN | 1250.00 || BLAKE | 2850.00 || SCOTT | 3000.00 || ALLEN | 1600.00 || FORD | 3000.00 |
找出哪些人津贴为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 ename, sal, comm FROM emp WHERE comm IS NOT NULL;
结果如下：| ename | sal | comm ||---------|--------|-------|| ALLEN | 1600.00 | 300.00 || WARD | 1250.00 | 500.00 || MARTIN | 1250.00 | 1400.00 || TURNER | 1500.00 | 0.00 |
找出工作岗位是MANAGER和SALESMAN的员工：
SELECT ename, job FROM emp WHERE job = 'MANAGER' OR job = 'SALESMAN';
结果如下：| ename | job ||---------|----------|| JONES | MANAGER || WARD | SALESMAN || MARTIN | SALESMAN || BLAKE | MANAGER || CLARK | MANAGER || TURNER | SALESMAN |
找出薪资大于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 || FORD | 3000.00 | 20 |
找出工作岗位是MANAGER或SALESMAN的员工（使用IN关键字）：
SELECT ename, job FROM emp WHERE job IN ('MANAGER', 'SALESMAN');结果如下：| ename | job ||---------|----------|| JONES | MANAGER || WARD | SALESMAN || MARTIN | SALESMAN || BLAKE | MANAGER || CLARK | MANAGER || TURNER | SALESMAN |
找出工资在800和5000之间的员工：
SELECT ename, sal FROM emp WHERE sal IN (800, 5000);
结果如下：| ename | sal ||---------|--------|| SMITH | 800.00 || KING | 5000.00 |
找出名字中含有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 name FROM t_user WHERE name LIKE '%_%';
结果如下：| name ||----------|| WANG_WU |
找出名字中最后一个字母是T的员工：
SELECT ename FROM emp WHERE ename LIKE '%T';
结果如下：| ename ||---------|| SCOTT |
BETWEEN和IN关键字在使用时必须遵循左小右大的原则。IS NULL和IS NOT NULL用于判断字段是否为NULL。AND和OR的优先级由括号决定，括号外的AND优先于括号内的OR。LIKE用于模糊查询，%表示任意多个字符，_表示任意一个字符。转载地址：http://orbfk.baihongyu.com/