sql 基本语法
- SCOTT>select loc,dname,deptno from dept;
- LOC DNAME DEPTNO
- ------------- -------------- ----------
- NEW YORK ACCOUNTING 10
- DALLAS RESEARCH 20
- CHICAGO SALES 30
- BOSTON OPERATIONS 40
#表里没有行和列没有次序，确切的说oracle称为堆表
sql算术表达式 一个常量和一个列名的组成
- SCOTT>select ename,job,sal,sal+200,comm from emp;
- ENAME JOB SAL SAL+200 COMM
- ---------- --------- ---------- ---------- ----------
- SMITH CLERK 800 1000
- ALLEN SALESMAN 1600 1800 300
- WARD SALESMAN 1250 1450 500
- JONES MANAGER 2975 3175
- MARTIN SALESMAN 1250 1450 1400
- BLAKE MANAGER 2850 3050
- CLARK MANAGER 2450 2650
- SCOTT ANALYST 3000 3200
- KING PRESIDENT 5000 5200
- TURNER SALESMAN 1500 1700 0
- ADAMS CLERK 1100 1300
- JAMES CLERK 950 1150
- FORD ANALYST 3000 3200
- MILLER CLERK 1300 1500
#销售工资加200
如果是null空值，任何null值进行运算都为null
如：
- SCOTT>select ename,job,sal,sal+null,comm from emp;
- ENAME JOB SAL SAL+NULL COMM
- ---------- --------- ---------- ---------- ----------
- SMITH CLERK 800
- ALLEN SALESMAN 1600 300
- WARD SALESMAN 1250 500
- JONES MANAGER 2975
- MARTIN SALESMAN 1250 1400
- BLAKE MANAGER 2850
- CLARK MANAGER 2450
- SCOTT ANALYST 3000
- KING PRESIDENT 5000
- TURNER SALESMAN 1500 0
- ADAMS CLERK 1100
- JAMES CLERK 950
- FORD ANALYST 3000
- MILLER CLERK 1300
- 14 rows selected.
列的别名
SCOTT>select ename,sal,sal*12 year_sal,comm,deptno from emp; #销售的年薪是多少以别名year_sal
- ENAME SAL YEAR_SAL COMM DEPTNO
- ---------- ---------- ---------- ---------- ----------
- SMITH 800 9600 20
- ALLEN 1600 19200 300 30
- WARD 1250 15000 500 30
- JONES 2975 35700 20
- MARTIN 1250 15000 1400 30
- BLAKE 2850 34200 30
- CLARK 2450 29400 10
- SCOTT 3000 36000 20
- KING 5000 60000 10
- TURNER 1500 18000 0 30
- ADAMS 1100 13200 20
- JAMES 950 11400 30
- FORD 3000 36000 20
- MILLER 1300 15600 10
- OTT>select ename,sal,sal*12 as year_sal,comm,deptno from emp; 例子2
- ENAME SAL YEAR_SAL COMM DEPTNO
- ---------- ---------- ---------- ---------- ----------
- SMITH 800 9600 20
- ALLEN 1600 19200 300 30
- WARD 1250 15000 500 30
- JONES 2975 35700 20
- MARTIN 1250 15000 1400 30
- BLAKE 2850 34200 30
- CLARK 2450 29400 10
- SCOTT 3000 36000 20
- KING 5000 60000 10
- TURNER 1500 18000 0 30
- ADAMS 1100 13200 20
- JAMES 950 11400 30
- FORD 3000 36000 20
- MILLER 1300 15600 10
- 14 rows selected.
- SCOTT>select ename,sal,sal*12 as "year sal",comm,deptno from emp; 例子3
- ENAME SAL year sal COMM DEPTNO
- ---------- ---------- ---------- ---------- ----------
- SMITH 800 9600 20
- ALLEN 1600 19200 300 30
- WARD 1250 15000 500 30
- JONES 2975 35700 20
- MARTIN 1250 15000 1400 30
- BLAKE 2850 34200 30
- CLARK 2450 29400 10
- SCOTT 3000 36000 20
- KING 5000 60000 10
- TURNER 1500 18000 0 30
- ADAMS 1100 13200 20
- JAMES 950 11400 30
- FORD 3000 36000 20
- MILLER 1300 15600 10
- 14 rows selected.
列连接
- SCOTT>select ename || job from emp;
- ENAME||JOB
- -------------------
- SMITHCLERK
- ALLENSALESMAN
- WARDSALESMAN
- JONESMANAGER
- MARTINSALESMAN
- BLAKEMANAGER
- CLARKMANAGER
- SCOTTANALYST
- KINGPRESIDENT
- TURNERSALESMAN
- ADAMSCLERK
- JAMESCLERK
- FORDANALYST
- MILLERCLERK
- SCOTT>select ename|| ' is a ' || job from emp;
- ENAME||'ISA'||JOB
- -------------------------
- SMITH is a CLERK
- ALLEN is a SALESMAN
- WARD is a SALESMAN
- JONES is a MANAGER
- MARTIN is a SALESMAN
- BLAKE is a MANAGER
- CLARK is a MANAGER
- SCOTT is a ANALYST
- KING is a PRESIDENT
- TURNER is a SALESMAN
- ADAMS is a CLERK
- JAMES is a CLERK
- FORD is a ANALYST
- MILLER is a CLERK
取消重复行
- SCOTT>select distinct deptno from emp;
- DEPTNO
- ----------
- 30
- 20
- 10
取消重复的列
- SCOTT>select distinct ename,deptno from emp;
- ENAME DEPTNO
- ---------- ----------
- SMITH 20
- JONES 20
- FORD 20
- CLARK 10
- SCOTT 20
- TURNER 30
- JAMES 30
- KING 10
- ADAMS 20
- BLAKE 30
- MILLER 10
- ALLEN 30
- WARD 30
- MARTIN 30
where
用来限制行
- SCOTT>select * from emp where deptno=10; 查找emp表中部门号为10的员工
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
- 7782 CLARK MANAGER 7839 09-JUN-81 2450 10
- 7839 KING PRESIDENT 17-NOV-81 5000 10
- 7934 MILLER CLERK 7782 23-JAN-82 1300 10
- SCOTT>select * from emp where job='SALESMAN'; 查找emp表中所有销售的工资
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
- 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
- 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
- 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
- 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
- SCOTT>select * from emp where hiredate='20-feb-81'; 查找入职时间为20-frb-81年的员工
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
- 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
- SCOTT>select * from emp where sal+100 > 1500; #销售工资+100做逻辑运算且大于1500的打印出来
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
- 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
- 7566 JONES MANAGER 7839 02-APR-81 2975 20
- 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
- 7782 CLARK MANAGER 7839 09-JUN-81 2450 10
- 7788 SCOTT ANALYST 7566 19-APR-87 3000 20
- 7839 KING PRESIDENT 17-NOV-81 5000 10
- 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
- 7902 FORD ANALYST 7566 03-DEC-81 3000 20
逻辑表达式
- SCOTT>select * from emp where sal in (1500,5000,1100);（列表查询结果在（）里）
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
- 7839 KING PRESIDENT 17-NOV-81 5000 10
- 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
- 7876 ADAMS CLERK 7788 23-MAY-87 1100 20
- SCOTT>select * from emp where deptno=30 and sal > 2000; #emp表中查找部门号为30,且销售工资为2000的员工
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
- 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
- SCOTT>select * from emp where sal between 1000 and 2000; #查找emp表中销售工资在1000到2000之间的员工
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
- 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
- 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
- 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
- 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
- 7876 ADAMS CLERK 7788 23-MAY-87 1100 20
- 7934 MILLER CLERK 7782 23-JAN-82 1300 10
- 6 rows selected.
- SCOTT>select * from emp where ename like '%A%'; #查找emp表中名字里面有一个A的员工
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
- 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
- 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
- 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
- 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
- 7782 CLARK MANAGER 7839 09-JUN-81 2450 10
- 7876 ADAMS CLERK 7788 23-MAY-87 1100 20
- 7900 JAMES CLERK 7698 03-DEC-81 950 30
- 7 rows selected.
- SCOTT>select * from emp where ename like '_A%';#查找emp表中员工名字第二个字母为A的员工
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
- 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
- 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
- 7900 JAMES CLERK 7698 03-DEC-81 950 30
- SCOTT>select * from emp where ename like '__A%'; #查找emp表中第三个字母为A的员工，第四个，第五个以此类推
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
- 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
- 7782 CLARK MANAGER 7839 09-JUN-81 2450 10
- 7876 ADAMS CLERK 7788 23-MAY-87 1100 20
- SCOTT> select * from emp where (job='SALESMAN' or job='CLERK') and sal >=1300 ;
- #我的理解是查找销售和秘书 并且工资大于1300括号中优先级高
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
- 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
- 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
- 7934 MILLER CLERK 7782 23-JAN-82 1300 10
- SCOTT> select * from emp where job='SALESMAN' or job='CLERK' and sal >=1300 ;
- 优先级一样，所有销售，秘书，工资大于1300的都打印出来！
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
- 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
- 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
- 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
- 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
- 7934 MILLER CLERK 7782 23-JAN-82 1300 10
- SCOTT>select * from emp order by sal; #销售工资排序oracle默认是从小到达排序
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
- 7369 SMITH CLERK 7902 17-DEC-80 800 20
- 7900 JAMES CLERK 7698 03-DEC-81 950 30
- 7876 ADAMS CLERK 7788 23-MAY-87 1100 20
- 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
- 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
- 7934 MILLER CLERK 7782 23-JAN-82 1300 10
- 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
- 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
- 7782 CLARK MANAGER 7839 09-JUN-81 2450 10
- 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
- 7566 JONES MANAGER 7839 02-APR-81 2975 20
- 7788 SCOTT ANALYST 7566 19-APR-87 3000 20
- 7902 FORD ANALYST 7566 03-DEC-81 3000 20
- 7839 KING PRESIDENT 17-NOV-81 5000 10
- 14 rows selected.
- SCOTT>select * from emp order by sal desc;#销售工资排序 desc 指定从大到小排序
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
- 7839 KING PRESIDENT 17-NOV-81 5000 10
- 7902 FORD ANALYST 7566 03-DEC-81 3000 20
- 7788 SCOTT ANALYST 7566 19-APR-87 3000 20
- 7566 JONES MANAGER 7839 02-APR-81 2975 20
- 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
- 7782 CLARK MANAGER 7839 09-JUN-81 2450 10
- 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
- 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
- 7934 MILLER CLERK 7782 23-JAN-82 1300 10
- 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
- 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
- 7876 ADAMS CLERK 7788 23-MAY-87 1100 20
- 7900 JAMES CLERK 7698 03-DEC-81 950 30
- 7369 SMITH CLERK 7902 17-DEC-80 800 20
- SCOTT>select * from emp order by sal asc; 取消空值
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
- 7369 SMITH CLERK 7902 17-DEC-80 800 20
- 7900 JAMES CLERK 7698 03-DEC-81 950 30
- 7876 ADAMS CLERK 7788 23-MAY-87 1100 20
- 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
- 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
- 7934 MILLER CLERK 7782 23-JAN-82 1300 10
- 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
- 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
- 7782 CLARK MANAGER 7839 09-JUN-81 2450 10
- 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
- 7566 JONES MANAGER 7839 02-APR-81 2975 20
- 7788 SCOTT ANALYST 7566 19-APR-87 3000 20
- 7902 FORD ANALYST 7566 03-DEC-81 3000 20
- 7839 KING PRESIDENT 17-NOV-81 5000 10
- 注意order后面可以接别名
- SCOTT>select * from emp where deptno=30 order by 2;
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
- 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
- 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
- 7900 JAMES CLERK 7698 03-DEC-81 950 30
- 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
- 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
- 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
- 6 rows selected.
- SCOTT>select * from emp where deptno=30 order by 3; #从第二列开始排序
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
- 7900 JAMES CLERK 7698 03-DEC-81 950 30
- 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
- 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
- 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
- 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
- 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
