进入终端
sqlplus /nolog
解锁用户
SQL> alter user scott identified by tiger account unlock; 用户已更改。
切换用户
SQL> conn scott/tiger 已连接。
查看当前用户
SQL> show user USER 为 "SCOTT"
查看所有表
SQL> select * from tab;
查看字段信息
SQL> desc emp;
取别名 ename 的别名是first_name 相当于mysql的as
SQL> select ename first_name, sal salary from emp;
连接操作符 把两列连接到一列来显示
SQL> select ename||job from emp; ENAME||JOB ------------------- SMITHCLERK ALLENSALESMAN WARDSALESMAN JONESMANAGER MARTINSALESMAN BLAKEMANAGER CLARKMANAGER SCOTTANALYST KINGPRESIDENT TURNERSALESMAN ADAMSCLERK SQL> 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
去重处理 把所有的值只显示一次，如果有两个字段，那么只有两列结果完全一样才是重复行被显示一列
SQL> select distinct deptno from emp; DEPTNO ---------- 30 20 10 SQL> select distinct deptno,job from emp; DEPTNO JOB ---------- --------- 20 CLERK 30 SALESMAN 20 MANAGER 30 CLERK 10 PRESIDENT 30 MANAGER 10 CLERK 10 MANAGER 20 ANALYST
设置每行显示100字符
SQL> set linesize 100
设置每页显示两百行
SQL> set pagesize 200
用sqldeveloper连接数据库 这是另一种与oracle交互的工具
运行sql语句
where过滤
-- 过滤 deptno 等于10 的数据 SQL> select * from emp where deptno=10; -- 过滤 sal 大于 300 的数据 SQL> select * from emp where sal>3000; -- 对字符串字段进行过滤要加引号 SQL> select * from emp where ename='SCOTT'; -- and or 与或非运算 SQL> select * from emp where sal>1000 and job='CLERK'; -- 时间格式要与数据库显示的一样 用单引号包着 SQL> select * from emp where hiredate ='17-11月 -81'; SQL> select * from emp where hiredate ='17-NOV -81'; ------------ 特殊操作符 -- 区间操作 SQL> select * from emp where sal between 2000 and 3000; -- in 操作 查询集合里面所有的数 SQL> select * from emp where job in ('CLERK', 'SALESMAN'); -- is null is not null 操作空值 0不是空值 SQL> select * from emp where comm is null; SQL> select * from emp where comm is not null;
like 模糊匹配
------------- like 模糊匹配 -- 匹配开头 SQL> select * from emp where ename like 'A%'; -- 匹配中间出现的字符 SQL> select * from emp where ename like '%A%'; -- 只匹配第二个为O的字符 SQL> select * from emp where ename like '_O%';
转义符
SQL> update emp set ename='X_SCOTT' WHERE ename = 'SCOTT'; -- 定制转义符 转义后才能匹配第二个为下划线的字符 SQL> select * from emp where ename like '_\_%' escape ''; -- 声明空格的话空格就是转义符 SQL> select * from emp where ename like '_ _%' escape ' ';
通过 rownum 获取数据
-- 通过 rownum 获取数据 SQL> select * from (select rownum rn,emp.* from emp) where rn=2;
to_char 时间函数
-- 系统默认时间 SQL> select sysdate from dual; SYSDATE -------------- 17-4月 -21 -- 日期函数 SQL> select to_char(sysdate, 'yyy-mm-dd')from dual; TO_CHAR(S --------- 021-04-17 -- 两种格式对比 SQL> select sysdate,to_char(sysdate, 'yyyy-mm-dd')from dual; SYSDATE TO_CHAR(SY -------------- ---------- 17-4月 -21 2021-04-17 -- 英文输出日期 SQL> select to_char(sysdate, 'year-month-day')from dual; TO_CHAR(SYSDATE,'YEAR-MONTH-DAY') ----------------------------------------------------------- twenty twenty-one-4月 -星期六 -- 带时分秒的输出 SQL> select sysdate,to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss')from dual; SYSDATE TO_CHAR(SYSDATE,'YY -------------- ------------------- 17-4月 -21 2021-04-17 17:21:05
格式转换
L 表示浮点类型当前货币符号
9 表示一个数 相当于占位符
0 强制放一个0
$ 放置一个浮点型美元符号
, 打印一个小数点
. 打印一个千位符
SQL> select ename,sal from emp; ENAME SAL ---------- ---------- SMITH 800 ALLEN 1600 WARD 1250 SQL> select ename,to_char(sal,'L99,999.99')from emp; ENAME TO_CHAR(SAL,'L99,999 ---------- -------------------- SMITH ￥800.00 ALLEN ￥1,600.00 WARD ￥1,250.00 SQL> select ename,to_char(sal,'L00,999.99')from emp; ENAME TO_CHAR(SAL,'L00,999 ---------- -------------------- SMITH ￥00,800.00 ALLEN ￥01,600.00 WARD ￥01,250.00 SQL> select sysdate,to_number('￥00,820.00', 'L00,000.00') from dual; SYSDATE TO_NUMBER('￥00,820.00','L00,000.00') -------------- ------------------------------------- 17-4月 -21 820
to_date 时间转换
SQL> select to_date('190-09-05', 'yyyy-dd-mm') from dual; TO_DATE('190-0 -------------- 09-5月 -90
rr日期格式
SQL> select to_char(sysdate, 'yyyy') curr_year, to_char(to_date('07','yy'), 'yyyy') yy07, to_char(to_date('97','yy'), 'yyyy') yy97, to_char(to_date('07','rr'), 'yyyy') rr07, to_char(to_date('97', 'rr'), 'yyyy') rr97 from dual; CURR YY07 YY97 RR07 RR97 ---- ---- ---- ---- ---- 2021 2007 2097 2007 1997
nvl(expr1, expr2) 当expr1 有值是返回expr1 没有值时返回expre2, 可以用来对null值做运算
SQL> select ename,sal,comm,sal+nvl(comm,0)from emp; ENAME SAL COMM SAL+NVL(COMM,0) ---------- ---------- ---------- --------------- SMITH 800 800 ALLEN 1600 300 1900 WARD 1250 500 1750 JONES 2975 2975 MARTIN 1250 1400 2650 BLAKE 2850 2850 CLARK 2450 2450 X_SCOTT 3000 3000 KING 5000 5000 TURNER 1500 0 1500 ADAMS 1100 1100
nvl(expr1, expr2, expr3) 当expr1为空时则为expr3, 不为空时,，为expr2
SQL> select ename,sal,comm,nvl2(comm,comm+sal, sal)from emp; ENAME SAL COMM NVL2(COMM,COMM+SAL,SAL) ---------- ---------- ---------- ----------------------- SMITH 800 800 ALLEN 1600 300 1900 WARD 1250 500 1750 JONES 2975 2975 MARTIN 1250 1400 2650 BLAKE 2850 2850 CLARK 2450 2450 X_SCOTT 3000 3000 KING 5000 5000 TURNER 1500 0 1500 ADAMS 1100 1100
nullif(expr1, expr2) 两数相等则为空， 不想等则为expr1
SQL> select length(ename),length(job), nullif(length(ename), length(job)) from emp; LENGTH(ENAME) LENGTH(JOB) NULLIF(LENGTH(ENAME),LENGTH(JOB)) ------------- ----------- --------------------------------- 5 5 5 8 5 4 8 4 5 7 5
coalesce(expr1, expr2,...,exprn)从后往前取值
SQL> select ename,sal, comm, coalesce(comm, sal, 0) from emp; ENAME SAL COMM COALESCE(COMM,SAL,0) ---------- ---------- ---------- -------------------- SMITH 800 800 ALLEN 1600 300 300 WARD 1250 500 500 JONES 2975 2975 MARTIN 1250 1400 1400
case 表达式， 条件表达式
SQL>select ename, job, sal, case job when 'CLERK' then sal * 1.1 when 'SALESMAN' then sal * 1.15 else sal end rev_sal from emp; ENAME JOB SAL REV_SAL ---------- --------- ---------- ---------- SMITH CLERK 800 880 ALLEN SALESMAN 1600 1840 WARD SALESMAN 1250 1437.5 JONES MANAGER 2975 2975 MARTIN SALESMAN 1250 1437.5 BLAKE MANAGER 2850 2850 CLARK MANAGER 2450 2450 X_SCOTT ANALYST 3000 3000 KING PRESIDENT 5000 5000 TURNER SALESMAN 1500 1725 ADAMS CLERK 1100 1210
decode 函数和上面的case表达式一样的效果
SQL> select ename, job, sal, decode(job, 'CLERK', sal*1.1, 'SALESMAN', sal*1.15, sal) rev_sal from emp; ENAME JOB SAL REV_SAL ---------- --------- ---------- ---------- SMITH CLERK 800 880 ALLEN SALESMAN 1600 1840 WARD SALESMAN 1250 1437.5 JONES MANAGER 2975 2975 MARTIN SALESMAN 1250 1437.5 BLAKE MANAGER 2850 2850 CLARK MANAGER 2450 2450 X_SCOTT ANALYST 3000 3000 KING PRESIDENT 5000 5000 TURNER SALESMAN 1500 1725 ADAMS CLERK 1100 1210
多表联接
SQL> select * from emp,dept where emp.deptno=dept.deptno; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC ---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- ---------- -------------- ------------- 7369 SMITH CLERK 7902 17-12月-80 800 20 20 RESEARCH DALLAS 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 30 SALES CHICAGO 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 30 SALES CHICAGO 7566 JONES MANAGER 7839 02-4月 -81 2975 20 20 RESEARCH DALLAS 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 30 SALES CHICAGO 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 30 SALES CHICAGO 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 10 ACCOUNTING NEW YORK 7788 X_SCOTT ANALYST 7566 19-4月 -87 3000 20 20 RESEARCH DALLAS 7839 KING PRESIDENT 17-11月-81 5000 10 10 ACCOUNTING NEW YORK 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 30 SALES CHICAGO 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 20 RESEARCH DALLAS 7900 JAMES CLERK 7698 03-12月-81 950 30 30 SALES CHICAGO 7902 FORD ANALYST 7566 03-12月-81 3000 20 20 RESEARCH DALLAS 7934 MILLER CLERK 7782 23-1月 -82 1300 10 10 ACCOUNTING NEW YORK -- 多表关联查询 接 and SQL> select * from emp,dept where emp.deptno=dept.deptno and dept.dname='SALES'; -- 空的数据也会联接 SQL> select * from emp,dept where emp.deptno(+)=dept.deptno; -- 笛卡尔联接 SQL> select * from emp cross join dept; -- 自然连接： 等值联接 SQL> select * from emp natural join dept; -- 两张表有多个同名的列 SQL> select * from emp natural join dept using(deptno) -- 两张表没有相同的列 SQL> select * from emp e join dept d on (e.deptno=d.deptno); -- 右外联接 SQL> select * from emp right outer join dept using(deptno); -- 左外联接 SQL> select * from emp left outer join dept using(deptno); --全外联接 SQL> select * from emp full outer join dept using(deptno);
组函数
-- 取最大值 SQL> select max(sal) from emp; -- 取最小值 SQL> select min(sal) from emp; -- 求和 SQL> select sum(sal) from emp; -- 取平均值 SQL> select avg(sal) from emp; -- 取多少个 只能取非空的值 SQL> select count(sal) from emp; -- 去重求个数 SQL> select count(distinct deptno) from emp; -- 计算有多少行数据 SQL> select count(*) from emp; -- 分组求和 SQL> select deptno,sum(sal) from emp group by deptno; -- 取总和为最大的数 SQL> select max(sum(sal)) from emp group by deptno; -- 运算后的值进行过滤 SQL> select deptno,sum(sal) from emp having sum(sal)>9000 group by deptno; -- 求每个工资人数大于等于2的 SQL> select sal,count(sal) from emp having count(*)>1 group by sal; -- 每一年参加工作的雇员数量 SQL> select to_char(HIREDATE, 'yyyy'), count(*) from emp group by to_char(HIREDATE, 'yyyy');
子查询
-- where条件上加子查询 查询工资比 Blake 的工资大的人有哪些 SQL> select ename, sal from emp where sal>(select sal from emp where ename='BLAKE');
& + 标识符就是变量 && 会把变量存起来， 用 define 命令可以查看存起来的变量
SQL> select ename,&column2 from emp; 输入 column2 的值: sal 原值 1: select ename,&column2 from emp 新值 1: select ename,sal from emp SQL> / 输入 column2 的值: hiredate 原值 1: select ename,&column2 from emp 新值 1: select ename,hiredate from emp SQL> select * from &table; 输入 table 的值: salgrade 原值 1: select * from &table 新值 1: select * from salgrade SQL> select * from emp where sal>&salary; 输入 salary 的值: 2000 原值 1: select * from emp where sal>&salary 新值 1: select * from emp where sal>2000 SQL> select * from emp where ename=upper('&salary'); 输入 salary 的值: scott 原值 1: select * from emp where ename=upper('&salary') 新值 1: select * from emp where ename=upper('scott') -- 查看使用过的变量 只限于&& SQL> define DEFINE _DATE = "17-4月 -21" (CHAR) DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR) DEFINE _USER = "SCOTT" (CHAR) DEFINE _PRIVILEGE = "" (CHAR) DEFINE _SQLPLUS_RELEASE = "1102000100" (CHAR) DEFINE _EDITOR = "Notepad" (CHAR) DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR) DEFINE _O_RELEASE = "1102000100" (CHAR) -- 控制 新 老 语句的显示 SQL> show verify verify ON
脚本命令
-- 查看缓冲区 SQL> list 1* select ename,&&col from emp order by &col SQL> l 1* select ename,&&col from emp order by &col -- 把缓冲区的内容存入指定的文件 SQL> sav G:1.sql 已创建 file G:1.sql -- 查看 文件里sql的内容 SQL> get G:1.sql 1* select ename,&&col from emp order by &col -- 用 @ 调用文件里的命令 SQL> @ G:1.sql 原值 1: select ename,&&col from emp order by &col 新值 1: select ename,sal from emp order by sal ENAME SAL ---------- ---------- SMITH 800 JAMES 950 ADAMS 1100 WARD 1250 MARTIN 1250 MILLER 1300 TURNER 1500 ALLEN 1600 CLARK 2450 BLAKE 2850 JONES 2975 X_SCOTT 3000 FORD 3000 -- 控制屏幕显示 SQL> set echo on
arraysize 向屏幕输出的行数 默认每有15行数据向屏幕输出一次 最大值是5000
-- 查看向屏幕输出的行数 SQL> show arraysize arraysize 15 -- 设置向屏幕输出的行数 SQL> set arraysize 5000
查询时显示的行数 默认为6行
SQL> show feedback 用于 6 或更多行的 FEEDBACK ON -- 取消设置 SQL> set feedback on -- 取消后小于6 行的也打印行数 SQL> select * from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 已选择4行。
heading 显示列名
SQL> show heading heading ON -- 关闭列名 SQL> set heading off SQL> select * from dept; 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 已选择4行。
long 控制超长文本显示
SQL> show long long 80 -- 设置后长文本显示的长度 SQL> set long 50000
强制标题字段换一行
SQL> col ename heading 'frist|name' SQL> select * from emp; frist EMPNO name JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 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 -- 清空强制标题字段换行 SQL> col ename cle -- 修改数值的显示风格 SQL> col sal for $99,99.99 SQL> / EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-12月-80 $8,00.00 20 7499 ALLEN SALESMAN 7698 20-2月 -81 $16,00.00 300 30 7521 WARD SALESMAN 7698 22-2月 -81 $12,50.00 500 30 7566 JONES MANAGER 7839 02-4月 -81 $29,75.00 20 -- 控制标题靠左边显示 SQL> col sal justify l for $99,99.99 -- 控制标居中显示 SQL> col sal justify c for $99,99.99 -- 控制空值显示NULL SQL> col comm justify c for $99,99.99 null 'NULL' -- 还原 SQL> col comm cle SQL> col sal cle
去掉连续重复的值的哪一列
SQL> select * from emp order by deptno; 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 7566 JONES MANAGER 7839 02-4月 -81 2975 20 7902 FORD ANALYST 7566 03-12月-81 3000 20 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 7369 SMITH CLERK 7902 17-12月-80 800 20 7788 X_SCOTT ANALYST 7566 19-4月 -87 3000 20 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 7900 JAMES CLERK 7698 03-12月-81 950 30 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 已选择14行。 SQL> break on deptno SQL> / EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 7839 KING PRESIDENT 17-11月-81 5000 7934 MILLER CLERK 7782 23-1月 -82 1300 7566 JONES MANAGER 7839 02-4月 -81 2975 20 7902 FORD ANALYST 7566 03-12月-81 3000 7876 ADAMS CLERK 7788 23-5月 -87 1100 7369 SMITH CLERK 7902 17-12月-80 800 7788 X_SCOTT ANALYST 7566 19-4月 -87 3000 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 7900 JAMES CLERK 7698 03-12月-81 950 7698 BLAKE MANAGER 7839 01-5月 -81 2850 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 已选择14行。 SQL> SQL> clear break breaks 已清除 SQL>
设置表头和表尾
SQL> set pages 30 SQL> tti 'Employee report begin' SQL> bti 'End report' SQL> / 星期六 4月 17 第 1 Employee report begin 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 7566 JONES MANAGER 7839 02-4月 -81 2975 20 7902 FORD ANALYST 7566 03-12月-81 3000 20 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 7369 SMITH CLERK 7902 17-12月-80 800 20 7788 X_SCOTT ANALYST 7566 19-4月 -87 3000 20 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 7900 JAMES CLERK 7698 03-12月-81 950 30 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 End report 已选择14行。 SQL> tti off SQL> bti off SQL>
执行系统命令
-- linux系统 SQL> host pwd /home/oracle -- windows系统 SQL> host chdir E:oracleproduct11.2.0dbhome_1BIN SQL> host dir
把屏幕上的内容显示到文件
SQL> spool G:1.txt SQL> spool off SQL> select * from emp; SQL> spool G:1.txt append SQL> spool off SQL> host chdir E:oracleproduct11.2.0dbhome_1BIN