Oracle修炼路程--基本查询

一 、 范围查询

--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