Oracle入门
oracle 登录数据库端口为1158 登录地址：http://demotraining:1158/em/c
本地 命令行测试：sqlplus scott/scott
远程命令行测试：sqlplus scott/scott@192.168.56.101:1521/orcl
（注意，如果需要远程连接，当前电脑需要安装sqlplus instantclient_12_1 ，将其目录添加到环境变量，否则无法使用会会提示）sqlplus 不是命令
数据目录文件：x:\oracle\product\10.2.0\oradata\orcl
Sql类型 ：
DML（data manipulation Language 数据操作语言）: insert update delete select
DDL(Data Definition Language 数据定义语言): create table,alter table,drop table,truncate table
DCL(Data Control Language 数据控制语言): grant(授权) revoke（撤销权限）
·
常用命令：
| host cls | 清屏 |
|---|---|
| show user | 当前用户 |
| select * from tab; | 当前用户下的表 |
| desc emp | 查询表结构 |
| show linesize 和 linesize 80 和 set linesize 150 | 设置行宽 |
| col ename for a8 和 col sal for 9999 | --设置列宽 |
| set pagesize 20 | 设置命令行每页的大小 |
| c /form/from | 修改语句错误 |
| as | 列别名（注意 没有引号和引号的区别） |
| || | 字符串连接 select ename ||'的信息是'||sal from emp; |
| set NLS_DATE_FORMAT | 修改日期格式alter system set NLS_DATE_FORMAT='yyyy-mm-dd';（范围可以是当前会话，也可以是系统） |
| 转义符 \ | like '%\_%' escape '\' escape (指定转义符，转移符可以是其他的) 例如：like '%a_%' escape 'a'; |
| & | 地址符 INSERT INTO ADDR VALUES ('&ID','&NAME'); |
| select * from v$nls_parameters; | 查看系统格式 |
| set feedback off | 关闭执行状态行信息 |
| set timing on | 打开执行时间 |
| rollback to savepoint a | 回滚到指定的保存点 |
| set transaction read only | 设置事务为只读 |
| show recyclebin | purge recyclebin; | Oracle的回收站 | 清空回收站 |
| flashback table TESTSAVEPOINT to before drop; | 删除已经恢复的表 （闪回删除） |
| create synonym myemp for emp | 给表创建别名 |
| exp imp | 数据导入 & 导出 增强 expdp impdp |
| nulls last | 将空值的数据放到最后，一般跟在 desc 后面 |
| alter table “TABLE_NAME ”shrink space; | 碎片整理 |
| col error | 显示错误 |
单行函数的使用：
{{{width="auto" height="auto"}}}
- 字符串函数
- concat
- substr
- length /lengthb
- instr
- lpad / rpad
- trim(替换指定位置)
- replace
- lower
- upper
- initcap
| upper | select upper('wwww') from dual; | 小写转大写 |
|---|---|---|
| lower | select lower('WWWW') from dual; | 大写转小写 |
| substr | select substr('hello',3) from dual; | 字符串截取 |
| instr | select instr('hello','o') from dual; | 返回指定字符存在的位置（从1开始） |
| length | select length('Hello World') from dual; | 字符数 |
| lengthb | select lengthb('Hello World') from dual; | 字节数 |
| length 与 lengthb 区别 （中文，后者一个占2个字节） | ||
| lpad | select lpad('hello',10,'*')from dual; | 左填充（数字10 表示补充满10位） |
| rpad | select rpad('hello',10,'*')from dual; | 右填充（数字10 表示补充满10位） |
| trim | select trim('H' from 'Hello WorldH') from dual; | 去掉前后指定的字符 |
| replace | ||
| concat | select concat('Hello','World') from dual; | 拼接字符串 |
- 数值函数
- round (四舍五入)
- trunc （截断）
- mod （求于）
| round | 。。 | 四舍五入 |
|---|---|---|
| trunc | 截断 |
| mod | 。。 | 取余 |
|---|
{{{width="auto" height="auto"}}}
日期函数
- sysdate
- months_between
- add_months
- next_day
- last_day
- round
- trunc
| sysdate | select sysdate from dual; | 当前系统时间 |
|---|---|---|
| months_between | select months_between(sysdate,hiredate) from emp; | 返回两个日期之间的月份数。 |
| add_months | select add_months(sysdate,56) from dual; | 多少个月之后，是哪一年（负数就往当前日期前面推） |
| next_day | select next_day(sysdate,'星期日') from dual; | 表示大下个星期的那一天(返回一个日期）； |
| next_day的应用：每个星期一自动备份数据 | 1. 分布式数据库 2. 快照 触发器 | |
| last_day | select last_day(sysdate) from dual; | 最后一个 |
通用函数
- nvl
- nvl2
- nullif
- coalesce
| nvl | nvl(col,0) | 滤空函数 |
|---|---|---|
| nvl2 | select sal*12+nvl2(comm,comm,0) from emp; | 如果第一个值不为null返回第二个，如果为空返回第三个值 |
| nullif | select nullif('abc','ac') 值 from dual; | 当a=b的时候，返回null；否则返回a |
| coalesce | select comm,sal,coalesce(comm,sal) "第一个不为null的值" | 从左到右找到第一个不为null的值 |
转换函数
-
隐式
-
显式
-
to_number
-
to_date
-
to_cahr
| to_cahr | select to_char(sysdate,'yyyy-mm-dd / hh24:mi:ss') from dual; | 将日期转换为str |
|---|---|---|
| to_date | select to_date('2015-11-22 / 22:56:22','yyyy-mm-dd / hh24:mi:ss') from dual; | 将str 转换为日期（相反） |
组函数
{{{width="auto" height="auto"}}}
| avg | ||
|---|---|---|
| count | ||
| max | ||
| min | ||
| sum |
If else 繁琐（sql99语法）
SQL> select ename,job,sal 涨前,
2 case job when 'PRESIDENT' then sal+1000
3 when 'MANAGER' then sal+800
4 else sal+400
5 end 涨后
6 from emp;
Oracle 自己的语法
SQL> select ename,job,sal 涨前,
2 decode(job,'PRESIDENT',sal+1000,
3 'MANAGER',sal+800,
4 sal+400) 涨后
5 from emp;
select count(\*) Total,
sum( decode(to\_char(hiredate,'yyyy'),'1980',1,0)) "1980",
sum( decode(to\_char(hiredate,'yyyy'),'1981',1,0)) "1981",
sum( decode(to\_char(hiredate,'yyyy'),'1982',1,0)) "1982",
sum( decode(to\_char(hiredate,'yyyy'),'1987',1,0)) "1987"
from emp;
{{{width="auto" height="auto"}}}
优化（数据库的优化）：
- 尽量使用列名，能不用* 则不用*
- 尽量使用where
- 尽量使用多表查询
- 尽量不要使用集合运算
- where解析顺序： 右》左 尽量条件安右边;
滤空函数的使用：
{{{width="auto" height="auto"}}}
使用滤空函数之后
{{{width="auto" height="auto"}}}
案例：
报表：
gourp by 增强，子分组
select deptno,job,sum(sal) from emp group by rollup(deptno,job);
break on deptno skip 2
、、、break on null
| 连接查询 | 等值和不等值 | |
|---|---|---|
| 外链接查询 | ||
| 自然连接查询 |
{{{width="auto" height="auto"}}}
{{{width="auto" height="auto"}}}
自然连接查询：
--自连接不适合操作大表
--层次查询
select level,empno,ename,mgr -----------------level 伪列 （深度）
2 from emp
3 connect by prior empno=mgr
4 start with mgr is null
5 order by 1;
{{{width="auto" height="auto"}}}
集合运算符
| any | 和集合中的任意一个值比较 | |
|---|---|---|
| all | 和集合中的所有值比较 | |
| union/union all | 并集 | 联合查询 |
| intersect | 交集 | |
| minus | 差集 | |
| rownum | 行号 | rownum永远按照默认的顺序生成。 |
| top-n问题分析 | 不能使用>怎么判断5-8之间的数据（不能使用betweent and） | |
| ROWID | 行id (有规律) | AAAMfPAAEAAAAAgAAF |
top-n问题解决办法（rownum）：
--oracle分页(Pageing Query)
select *
from (select rownum r,e1.*
from (select * from emp order by sal) e1
where rownum <=8
)
where r >=5;
事务的标致：
read_commited(默认);
serializable
read_only
起始标志：事务中第一条DML语句
结束标志：
提交： 显式 commit
隐式 正常退出（exit），DDL，DCL
回滚: 显式 rollback
隐式 非正常退出，掉电，宕机
| 授权命令 | ||
|---|---|---|
| 视图 | grant create view to scott | 授予scott账户创建视图的权限，必须管理员授权 |
| 授予用户查询某个表的权限 |
授权
{{{width="auto" height="auto"}}}
{{{width="auto" height="auto"}}}
{{{width="auto" height="auto"}}}
浙公网安备 33010602011771号