oracle安装参照： Oracle数据库之安装教程
Oracle数据库总结：
Oracle数据库之基本查询
Oracle数据库之单行函数
Oracle数据库之多行函数
Oracle数据库之多表查询
sql> –查询10号部门的员工
sql> select *
2 from emp
3 where deptno=10;
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ---------- -------- --------- ---------- -------------- ----- ---------- ----------
- 7782 CLARK MANAGER 7839 09-6月 -81 2450 10
- 7839 KING PRESIDENT 17-11月-81 5000 10
- 7934 MILLER CLERK 7782 23-1月 -82 1300 10
***sql> –字符串大小写敏感
sql> –查询名叫KING的员工*
sql> select *
2 from emp
3 where ename=’KING’;
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ---------- -------- --------- ---------- -------------- ----- ---------- ----------
- 7839 KING PRESIDENT 17-11月-81 5000 10
***sql> –日期格式敏感
sql> –查询入职日期是17-11月-81的员工信息*
sql> select *
2 from emp
3 where hiredate=’17-11月-81’;
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ---------- -------- --------- ---------- -------------- ----- ---------- ----------
- 7839 KING PRESIDENT 17-11月-81 5000 10
sql> –改日期格式
sql> select * from v$nls_parameters;
- PARAMETER VALUE
- ---------------------------------------------------------------- ----------------------------------------------------------------
- NLS_LANGUAGE SIMPLIFIED CHINESE
- NLS_TERRITORY CHINA
- NLS_CURRENCY ￥
- NLS_ISO_CURRENCY CHINA
- NLS_NUMERIC_CHARACTERS .,NLS_CALENDAR GREGORIAN
- NLS_DATE_FORMAT DD-MON-RR
- NLS_DATE_LANGUAGE SIMPLIFIED CHINESE
- NLS_CHARACTERSET ZHS16GBK
- NLS_SORT BINARY
- NLS_TIME_FORMAT HH.MI.SSXFF AM
-
- PARAMETER VALUE
- ---------------------------------------------------------------- ----------------------------------------------------------------
- NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
- NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
- NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
- NLS_DUAL_CURRENCY ￥
- NLS_NCHAR_CHARACTERSET AL16UTF16
- NLS_COMP BINARY
- NLS_LENGTH_SEMANTICS BYTE
- NLS_NCHAR_CONV_EXCP FALSE
sql> alter session set NLS_DATE_FORMAT=’yyyy-mm-dd’;
会话已更改。
*sql> select
2 from emp
3 where hiredate=’1981-11-17’;**
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ---------- -------- --------- ---------- ---------- ----- ---------- ----------
- 7839 KING PRESIDENT 1981-11-17 5000 10
sql> alter session set NLS_DATE_FORMAT=’DD-MON-RR’;
会话已更改。
***sql> –between… and
sql> –查询薪水1000~2000之间的员工*
sql> select *
2 from emp
3 where sal between 1000 and 2000;
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ---------- -------- --------- ---------- -------------- ----- ---------- ----------
- 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
- 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
- 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
- 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
- 7876 ADAMS CLERK 7788 23-5月 -87 1100 20
- 7934 MILLER CLERK 7782 23-1月 -82 1300 10
***sql> –in 在集合中
sql> –查询部门号是10和20的员工*
sql> select *
2 from emp
3 where deptno in (10,20);
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ---------- -------- --------- ---------- -------------- ----- ---------- ----------
- 7369 SMITH CLERK 7902 17-12月-80 800 20
- 7566 JONES MANAGER 7839 02-4月 -81 2975 20
- 7782 CLARK MANAGER 7839 09-6月 -81 2450 10
- 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
- 7839 KING PRESIDENT 17-11月-81 5000 10
- 7876 ADAMS CLERK 7788 23-5月 -87 1100 20
- 7902 FORD ANALYST 7566 03-12月-81 3000 20
- 7934 MILLER CLERK 7782 23-1月 -82 1300 10
***sql> –模糊查询
sql> –查询名字以S打头的员工*
sql> select *
2 from emp
3 where ename like ‘S%’;
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ---------- -------- --------- ---------- -------------- ----- ---------- ----------
- 7369 SMITH CLERK 7902 17-12月-80 800 20
- 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
sql> –查询名字是4个字的员工
1 select *
2 from emp
3* where ename like ‘__’
sql> /
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ---------- -------- --------- ---------- -------------- ----- ---------- ----------
- 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
- 7839 KING PRESIDENT 17-11月-81 5000 10
- 7902 FORD ANALYST 7566 03-12月-81 3000 20
sql> – 插入记录
sql> insert into emp(empno,ename,sal,deptno) values(1001,’Tom_AB’,3000,10);
已创建 1 行。
sql> select * from emp;
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ---------- -------- --------- ---------- -------------- ----- ---------- ----------
- 1001 Tom_AB 3000 10
- 7369 SMITH CLERK 7902 17-12月-80 800 20
- 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
- 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
- 7566 JONES MANAGER 7839 02-4月 -81 2975 20
- 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
- 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
- 7782 CLARK MANAGER 7839 09-6月 -81 2450 10
- 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
- 7839 KING PRESIDENT 17-11月-81 5000 10
- 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
-
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ---------- -------- --------- ---------- -------------- ----- ---------- ----------
- 7876 ADAMS CLERK 7788 23-5月 -87 1100 20
- 7900 JAMES CLERK 7698 03-12月-81 950 30
- 7902 FORD ANALYST 7566 03-12月-81 3000 20
- 7934 MILLER CLERK 7782 23-1月 -82 1300 10
sql> –查询名字中含有下划线的员工
sql> select *
2 from emp
3 where ename like ‘%_%’;
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ---------- -------- --------- ---------- -------------- ----- ---------- ----------
- 1001 Tom_AB 3000 10
- 7369 SMITH CLERK 7902 17-12月-80 800 20
- 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
- 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
- 7566 JONES MANAGER 7839 02-4月 -81 2975 20
- 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
- 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
- 7782 CLARK MANAGER 7839 09-6月 -81 2450 10
- 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
- 7839 KING PRESIDENT 17-11月-81 5000 10
- 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
-
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ---------- -------- --------- ---------- -------------- ----- ---------- ----------
- 7876 ADAMS CLERK 7788 23-5月 -87 1100 20
- 7900 JAMES CLERK 7698 03-12月-81 950 30
- 7902 FORD ANALYST 7566 03-12月-81 3000 20
- 7934 MILLER CLERK 7782 23-1月 -82 1300 10
sql> –转义字符
1 select *
2 from emp
3* where ename like ‘%_%’ escape ‘\’
sql> /
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ---------- -------- --------- ---------- -------------- ----- ---------- ----------
- 1001 Tom_AB 3000 10
sql> –查询员工信息，按照月薪排序
sql> select *
2 from emp
3 order by sal;
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ---------- -------- --------- ---------- -------------- ----- ---------- ----------
- 7369 SMITH CLERK 7902 17-12月-80 800 20
- 7900 JAMES CLERK 7698 03-12月-81 950 30
- 7876 ADAMS CLERK 7788 23-5月 -87 1100 20
- 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
- 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
- 7934 MILLER CLERK 7782 23-1月 -82 1300 10
- 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
- 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
- 7782 CLARK MANAGER 7839 09-6月 -81 2450 10
- 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
- 7566 JONES MANAGER 7839 02-4月 -81 2975 20
-
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ---------- -------- --------- ---------- -------------- ----- ---------- ----------
- 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
- 7902 FORD ANALYST 7566 03-12月-81 3000 20
- 7839 KING PRESIDENT 17-11月-81 5000 10
sql> –order by 后面 + 列，表达式，别名，序号
sql> select ename,sal*12
2 from emp
3 order by sal*12 desc;
- ENAME SAL SAL*12
- -------- ----- ----------
- KING 5000 60000
- FORD 3000 36000
- SCOTT 3000 36000
- JONES 2975 35700
- BLAKE 2850 34200
- CLARK 2450 29400
- ALLEN 1600 19200
- TURNER 1500 18000
- MILLER 1300 15600
- WARD 1250 15000
- MARTIN 1250 15000
-
- ENAME SAL SAL*12
- -------- ----- ----------
- ADAMS 1100 13200
- JAMES 950 11400
- SMITH 800 9600
sql> –多个列排序
sql> select *
2 from emp
3 order by deptno,sal;
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ---------- -------- --------- ---------- -------------- ----- ---------- ----------
- 7934 MILLER CLERK 7782 23-1月 -82 1300 10
- 7782 CLARK MANAGER 7839 09-6月 -81 2450 10
- 7839 KING PRESIDENT 17-11月-81 5000 10
- 7369 SMITH CLERK 7902 17-12月-80 800 20
- 7876 ADAMS CLERK 7788 23-5月 -87 1100 20
- 7566 JONES MANAGER 7839 02-4月 -81 2975 20
- 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
- 7902 FORD ANALYST 7566 03-12月-81 3000 20
- 7900 JAMES CLERK 7698 03-12月-81 950 30
- 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
- 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
-
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ---------- -------- --------- ---------- -------------- ----- ---------- ----------
- 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
- 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
- 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
sql> –order by 作用于后面所有的列，desc只作用于离他最近的一列
sql> –查询员工信息，按照奖金排序
sql> select *
2 from emp
3 order by comm;
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ---------- -------- --------- ---------- -------------- ----- ---------- ----------
- 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
- 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
- 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
- 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
- 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
- 7839 KING PRESIDENT 17-11月-81 5000 10
- 7876 ADAMS CLERK 7788 23-5月 -87 1100 20
- 7900 JAMES CLERK 7698 03-12月-81 950 30
- 7902 FORD ANALYST 7566 03-12月-81 3000 20
- 7934 MILLER CLERK 7782 23-1月 -82 1300 10
- 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
-
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ---------- -------- --------- ---------- -------------- ----- ---------- ----------
- 7566 JONES MANAGER 7839 02-4月 -81 2975 20
- 7369 SMITH CLERK 7902 17-12月-80 800 20
- 7782 CLARK MANAGER 7839 09-6月 -81 2450 10
sql> –a命令 append
sql> a desc
3* order by comm desc
sql> /
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ---------- -------- --------- ---------- -------------- ----- ---------- ----------
- 7369 SMITH CLERK 7902 17-12月-80 800 20
- 7782 CLARK MANAGER 7839 09-6月 -81 2450 10
- 7902 FORD ANALYST 7566 03-12月-81 3000 20
- 7900 JAMES CLERK 7698 03-12月-81 950 30
- 7876 ADAMS CLERK 7788 23-5月 -87 1100 20
- 7566 JONES MANAGER 7839 02-4月 -81 2975 20
- 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
- 7934 MILLER CLERK 7782 23-1月 -82 1300 10
- 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
- 7839 KING PRESIDENT 17-11月-81 5000 10
- 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
- 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
- 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
- 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
Oracle数据库总结：
Oracle数据库之集合运算
Oracle数据库之数据处理
@L_404_7@
Oracle数据库之对象视图、索引、序列、同义词
