LIKE模糊查询操作符:"%"代表任意一个或多个字符，“_"代表任意单个字符；匹配内容用单引号引用。
例：查询员工姓名中有A的员工信息；
SQL@itkaifang> SELECT empno,ename,sal FROM emp WHERE ename LIKE '%A%'; EMPNO ENAME SAL ---------- ---------- ---------- 7499 ALLEN 1600 7521 WARD 1250 7654 MARTIN 1250 7698 BLAKE 2850 7782 CLARK 2450 7876 ADAMS 1100 7900 JAMES 950 7 rows selected. SQL@itkaifang>
SQL@itkaifang> SELECT empno,ename,sal FROM emp WHERE ename LIKE '_A%'; EMPNO ENAME SAL ---------- ---------- ---------- 7521 WARD 1250 7654 MARTIN 1250 7900 JAMES 950 SQL@itkaifang>
使用ESCAPE标识符定义转义符，如匹配字段出现”_“需要搜索，就出现需转义的需求。
例：查询员工姓名包含”_“的员工信息；
SQL@itkaifang> SELECT * FROM emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980-12-17 800 20 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 7566 JONES MANAGER 7839 1981-04-02 2975 20 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 7698 BLAKE MANAGER 7839 1981-05-01 2850 30 7782 CLARK MANAGER 7839 1981-06-09 2450 10 7788 SCOTT ANALYST 7566 1987-04-19 3000 20 7839 KING PRESIDENT 1981-11-17 5000 10 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 7876 ADAMS CLERK 7788 1987-05-23 1100 20 7900 JAMES CLERK 7698 1981-12-03 950 30 7902 FORD ANALYST 7566 1981-12-03 3000 20 7934 MILLER CLERK 7782 1982-01-23 1300 10 8888 QIN_BING 9999 15 rows selected. SQL@itkaifang> SELECT empno,ename,sal FROM emp WHERE ename LIKE '%\_%' ESCAPE '\'; EMPNO ENAME SAL ---------- ---------- ---------- 8888 QIN_BING 9999 SQL@itkaifang>
其中'\'为定义的转义符
QQ客服
新浪微博