SQL-oracle

来源：互联网发布：金融类的工作知乎编辑：程序博客网时间：2024/05/16 12:54

m%代表m开头的任意长度的字符串，m_代表m开头的长度为3的字符串。

select * from scott.emp where job not like 'M%';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-04-02 2975.00 20
7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-05-01 2850.00 30
7782 CLARK MANAGER 7839 1981-06-09 2450.00 10
7788 SCOTT ANALYST 7566 1987-04-19 3000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-05-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-03 950.00 30
7902 FORD ANALYST 7566 1981-12-03 3000.00 20
7934 MILLER CLERK 7782 1982-01-23 1300.00 10

select * from scott.emp where job not like 'M_';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30
7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30
7788 SCOTT ANALYST 7566 1987-04-19 3000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-05-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-03 950.00 30
7902 FORD ANALYST 7566 1981-12-03 3000.00 20
7934 MILLER CLERK 7782 1982-01-23 1300.00 10

//排序

order by job asc,sal desc; 先按job asc,若一样再按照sal desc.

//分组

group by job,sal having sal <= 2000; == where sal <= 2000 group by job;

//相加适合于数值的相加

select sal+mgr from scott.emp;

//联结

scott.dept有4条记录，scott.emp有14条记录，其笛卡尔积为:4*14=56;

//简单的嵌套查询

select emp.empno from scott.emp where sal >= (select sal from scott.emp where ename = 'WARD');