一 、 范围查询
--between.....and用法:闭区间
SQL> select *from emp where hiredate between '1981-02-22' and '1982-01-23';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
7521 WARD SALESMAN 7698 1981-02-22 1650 500 30
7566 JONES MANAGER 7839 1981-04-02 4575 0 20
7654 MARTIN SALESMAN 7698 1981-09-28 1650 1400 40
7698 BLAKE MANAGER 7839 1981-05-01 4450 0 30
7782 CLARK MANAGER 7839 1981-06-09 4050 0 10
7839 KING PRESIDENT 1981-11-17 7000 0 10
7844 TURNER SALESMAN 7698 1981-09-08 1900 0 30
7900 JAMES CLERK 7698 1981-12-03 1350 0 30
7902 FORD ANALYST 7566 1981-12-03 3400 0 20
7934 MILLER CLERK 7782 1982-01-23 1700 0 10
已选择10行。
SQL> select *from emp where sal between 3400 and 6000 order by sal asc;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 1987-04-19 3400 0 20
7902 FORD ANALYST 7566 1981-12-03 3400 0 20
7782 CLARK MANAGER 7839 1981-06-09 4050 0 10
7698 BLAKE MANAGER 7839 1981-05-01 4450 0 30
7566 JONES MANAGER 7839 1981-04-02 4575 0 20
二、 模糊查询
like一般需要与通配符一起使用
通配符:
1、%:零个或者多个任意字符。
2、_:代表一个任意字符。
3、\:指转义字符,“%”在字符串中表示一个字符“%”。
--查询名字里面带有A子母的员工信息;
SQL> select *from emp where ename like '%A%';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
5741 QINJIALI CLERK 7654 1985-03-20 1900 600 20
7499 ALLEN SALESMAN 7698 1981-02-20 2000 300 30
7521 WARD SALESMAN 7698 1981-02-22 1650 500 30
7654 MARTIN SALESMAN 7698 1981-09-28 1650 1400 40
7698 BLAKE MANAGER 7839 1981-05-01 4450 0 30
7782 CLARK MANAGER 7839 1981-06-09 4050 0 10
7876 ADAMS CLERK 7788 1987-05-23 1500 0 20
7900 JAMES CLERK 7698 1981-12-03 1350 0 30
--以4结尾的部门编号
SQL> select *from emp where empno like '%4';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
7654 MARTIN SALESMAN 7698 1981-09-28 1650 1400 40
7844 TURNER SALESMAN 7698 1981-09-08 1900 0 30
7934 MILLER CLERK 7782 1982-01-23 1700 0 10
--姓名中第二个子母是M的员工信息;
SQL> select *from emp where ename like '_M%';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 1200 0 20
SQL> select *from emp where length(ename)>=7;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
5741 QINJIALI CLERK 7654 1985-03-20 1900 600 20
SQL> select *from emp where ename like '_______%';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
5741 QINJIALI CLERK 7654 1985-03-20 1900 600 20
--姓名中包含:_的员工信息
SQL> select *from emp where ename like '%\_%' escape '\';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
5741 QIN_JIALI CLERK 7654 1985-03-20 1900 600 20
**三 排序查詢**
```sql
--group by
SQL> select *from emp order by sal asc;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 1200 0 20
7900 JAMES CLERK 7698 03-12月-81 1350 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1500 0 20
7521 WARD SALESMAN 7698 22-2月 -81 1650 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1650 1400 40
7934 MILLER CLERK 7782 23-1月 -82 1700 0 10
7844 TURNER SALESMAN 7698 08-9月 -81 1900 0 30
5741 QIN_JIALI CLERK 7654 20-3月 -85 1900 600 20
7499 ALLEN SALESMAN 7698 20-2月 -81 2000 300 30
7788 SCOTT ANALYST 7566 19-4月 -87 3400 0 20
7902 FORD ANALYST 7566 03-12月-81 3400 0 20
7782 CLARK MANAGER 7839 09-6月 -81 4050 0 10
7698 BLAKE MANAGER 7839 01-5月 -81 4450 0 30
7566 JONES MANAGER 7839 02-4月 -81 4575 0 20
7839 KING PRESIDENT 17-11月-81 7000 0 10
已选择15行。
SQL> select *from emp order by sal desc,hiredate asc;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7839 KING PRESIDENT 17-11月-81 7000 0 10
7566 JONES MANAGER 7839 02-4月 -81 4575 0 20
7698 BLAKE MANAGER 7839 01-5月 -81 4450 0 30
7782 CLARK MANAGER 7839 09-6月 -81 4050 0 10
7902 FORD ANALYST 7566 03-12月-81 3400 0 20
7788 SCOTT ANALYST 7566 19-4月 -87 3400 0 20
7499 ALLEN SALESMAN 7698 20-2月 -81 2000 300 30
7844 TURNER SALESMAN 7698 08-9月 -81 1900 0 30
5741 QIN_JIALI CLERK 7654 20-3月 -85 1900 600 20
7934 MILLER CLERK 7782 23-1月 -82 1700 0 10
7521 WARD SALESMAN 7698 22-2月 -81 1650 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1650 1400 40
7876 ADAMS CLERK 7788 23-5月 -87 1500 0 20
7900 JAMES CLERK 7698 03-12月-81 1350 0 30
7369 SMITH CLERK 7902 17-12月-80 1200 0 20
已选择15行。
SQL> select *from emp where deptno in (10,20,30) order by deptno asc;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-6月 -81 4050 0 10
7839 KING PRESIDENT 17-11月-81 7000 0 10
7934 MILLER CLERK 7782 23-1月 -82 1700 0 10
7902 FORD ANALYST 7566 03-12月-81 3400 0 20
7369 SMITH CLERK 7902 17-12月-80 1200 0 20
7876 ADAMS CLERK 7788 23-5月 -87 1500 0 20
5741 QIN_JIALI CLERK 7654 20-3月 -85 1900 600 20
7788 SCOTT ANALYST 7566 19-4月 -87 3400 0 20
7566 JONES MANAGER 7839 02-4月 -81 4575 0 20
7844 TURNER SALESMAN 7698 08-9月 -81 1900 0 30
7900 JAMES CLERK 7698 03-12月-81 1350 0 30
7698 BLAKE MANAGER 7839 01-5月 -81 4450 0 30
7499 ALLEN SALESMAN 7698 20-2月 -81 2000 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1650 500 30
已选择14行。
注意事項:
1)对于命令,不区分大小写;对于数据,严格区分大小写;
四 运算符
1 操作运算符:+ - * / %
2 关系运算符:> >= < <= = !=或者<>
五 去重查询
--distinct
SQL> select distinct(job) from emp;
JOB
---------
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST