2.Oracle基础学习

一.oracle表的管理

1. 创建表 ​ create table 表名
2. 删除表 ​ Drop table 表名
3. 修改表 ​ alter table 表名 1）添加列： add(列名 属性) 2）修改列属性： modify ​ 3）修改列名： rename column 列名1 to 列名2 ​ 4）删除列 ： drop column 列名 ​ 5）添加约束： add constraint 约束名 约束内容
4. 相关约束
   1）not null 2）unique 3）primary key 4）foreign key 5）check
5. 修改约束名

alter table itlb rename constraint name1 to name2;

6. 禁止/激活/删除约束

//禁止
alter table itlb  disable constraint constaint_name [cascaed];
//激活
alter table table_name enable constraint  constaint_name;
//删除
alter table table_name drop constraint constraint_name ;

7. 显示约束信息

​ select constraint_ame,table_name,column_name from user_cons_columns where table_name = ‘itlb’;

8. 总结
   ​ 1）增加unique,primary key , foreign key ,check 的时候，必须使用 alter table 的 add子局
   ​ 2）增加not null约束，必须使用alter table 语句的 modify 子句
   ​ 3）在同一个方案中，约束名必须唯一，并且不能和其他对象重名

二.数据操纵语言（DML）

1.增删改查 ----------------与mysql语句相同

查询：select * from tablename;
插入：insert into tablename values(field1,field2,...);
修改：Update tablename set  field = 'name'  where  条件;
删除(清空数据)：delete from tablename;
删除表：drop table tablename;

2.truncate和delete的区别
​ 1) truncate是DDL(数据定义语言)命令，删除的数据不能恢复，delete是DML命令，删除后的数据可以通过日志文件恢复。
​ 2) 如果一个表中数据记录很多，truncate相对较快。

三.序列

1.创建序列的方法

create sequence 序列名
[start with num]
[increment by num]
[maxvalue num|nomaxvalue]
[minvalue num|nominvalue]
[cycle|nocycle]
[cache num|nocache]

start with： 从某一个整数开始，升序默认值是 1，降序默认值是-1。
increment by： 增长数。如果是正数则升序生成，如果是负数则降序生成。升序默认值是 1，降 序默认值是-1。 maxvalue/nomaxvalue： 指最大/最小值。
nomaxvalue： 默认最大值是几，升序的最大值是：1027，降序默认值是-1
nominvalue： 默认最小值是几，升序默认值是 1，降序默认值是-1026。
cycle： 循环升序或者降序
nocycle： 不重新开始，序列升序或降序达到峰值后就会报错。默认nocycle。
cache： 使用 cache选项时，该序列会根据序列规则预生成一组序列号。保留在内存中，当使用 下一个序列号时，可 以更快的响应。当内 存中的序列号用完时，系统再生成一组新的序列号，并保 存在缓存中，这 样可以提高生成序列号的效率。Oracle默认会生产 20 个序列号。
nocache： 不预先在内存中生成序列号。

create sequence seqpersonid;

2.获取序列
在创建序列后，用户需要自己获取序列
nextval：取得序列的下一个内容
currval：取得序列的当前内容

select seqpersonid.nextval from dual; 
select seqpersonid.currval from dual;

3.函数
1.单行函数
1.字符函数

1. ascll(x) 返回字符x的ASCII码。
2. concat(x,y) 连接字符串x和y。
3. instr(x, str ,start, [n]) 在x中查找str，可以指定从"start"开始，也可以指定从第n次开始。
4. length(x) 返回x的长度。
5. lower(x) x转换为小写。
6. upper(x) x转换为大写。
7. ltrim(x,trim_str) 把x的左边截去trim_str字符串，缺省截去空格。
8. rtrim(x,trim_str) 把x的右边截去trim_str字符串，缺省截去空格。
9. trim( x from trim_str ) 把x的两边截去trim_str字符串，缺省截去空格。
10. replace(x,old,new) 在x中查找old，并替换为new。
11. substr(x,start,length) 返回x的字串，从staart处开始，截取length个字符，缺省length， 默认到结尾。
12. initcap(str) 首字母大写
    2.数字函数
    函数说明示例
    abs(x) x绝对值 abs(-3)=3
    acos(x) x的反余弦 acos(1)=0
    cos(x) 余弦 cos(1)=1.57079633
    ceil(x) 大于或等于x的最小值 ceil(5.4)=6
    floor(x) 小于或等于x的最大值 floor(5.8)=5
    log(x,y) x为底y的对数 log(2,4)=2
    mod(x,y) x除以y的余数 mod(8,3)=2
    power(x,y) x的y次幂 power(2,3)=8
    round(x[,y]) x在第y位四舍五入 round(3.456,2)=3.46
    sqrt(x) x的平方根 sqrt(4)=2
    trunc(x[,y]) x在第y位截断 trunc(3.456,2)=3.45
    3.日期函数
    add_months(d,n) 在日期 d 上，加上月数 n，返回计算后的新日期。
    last_day(d) 返回指定日期当月的最后一天。
    round(d , fmt) 返回一个以 fmt 为格式的四舍五入日期值， d 是日期， fmt 是格式模型。默 认 fmt 为 DDD，即月中的某一天。
    如果 fmt 为“YEAR”则舍入到某年的 1 月 1 日，即前半年舍去，后半年作为下一年。
    如果 fmt 为“MONTH”则舍入到某月的 1 日，即前月舍去，后半月作为下一月。 默认为“DDD”，即月中的某一天，最靠近的天，前半天舍去，后半天作为第二天。
    如果 fmt 为“DAY”则舍入到最近的周的周日，即上半周舍去，下半周作为下一周周日。
    trunc(d , fmt) 直接截取到对应格式的第一天。其他的与第三点相似
    extract(fmt FROM d) ，提取日期中的特定部分fmt 为：year、month、day、hour、 minute、second 。其中 year、month、day可以为 date类型匹配，也可以与 timestamp 类型匹配；但是 hour、 minute、second 必须与 timestamp 类型匹配。 hour匹配的结果中没有加上时区，因此在中国运行的结果小 8 小时。
    –1. 写出计算明天此刻的

select sysdate + 1 from dual;

–2. 查询scott用户下所有员工入职距离现在有多少天

select round(sysdate - hiredate) from emp; 

–3. 查询scott用户下所有员工入职距离现在有多少月

select ceil(months_between(sysdate, hiredate)) from emp;

–4. 查询scott用户下所有员工入职距离现在有多少年

select extract(year from sysdate) - extract(year from hiredate) from emp; 

–5. 查询scott用户下所有员工入职距离现在有多少周

select ceil(round(sysdate - hiredate)/7) from emp;

4.转换函数

to_char (d, fmt) 把日期和数字转换为制定格式的字符串。fmt 是格式化字符串。

select to_char(sysdate,'YYYY "年" MM "月" DD "日" HH24:MI:SS') "date" from dual;

格式化字符

​ 参数示例 说明
.9999 指定位置处显示数字。
​ .9.9 指定位置返回小数点
.99,99 指定位置返回一个逗号
$ $999 数字开头返回一个美元符号
EEEE 9.99EEEE 科学计数法表示
L L999 数字前加一个本地货币符号
PR 999PR 如果数字式负数则用尖括号进行表示
to_date(x , fmt) 把一个字符串以 fmt 格式转换为一个日期类型
to_number(x , fmt) 把一个字符串以 fmt 格式转换为一个数字。fmt 格式字符参考上表

5.通用函数

1. nvl(x , value) 如果x为空 ， 返回value (备胎) ，否则返回x
2. nav2(x , value1 , value2) 如果x非空 ， 返回value1 ，否则返回value2
3. decode 函数，该类函数类似 if…else if…else

decode (col/expression, search1,result1,search2, result2....default); 

​ 1. col/expression:列名或表达式
2.Search1，search2…:用于比较的条件
3.Result1, result2…:返回值
4.如果 col/expression 和 Searchi 匹配就返回 resulti,否则返回 default 的默认值

  case when
	case expr when comparison_expr1 then return_expr1 
	when comparison_expr2 then return_expr2 
	when comparison_exprn then return_exprn 
	else else_expr 
  end;
    -- 等值判断/范围判断 
    -- mysql、oracle通用

2.聚合函数(多行函数)

​ 名称 作用 语法
​ AVG 平均值 AVG(表达式)

​ SUM 求和 SUM(表达式)

MIN、MAX 最小值、最大值 MIN(表达式)、MAX(表达式)

COUNT 数据统计 COUNT(表达式)

四.多表查询

1.基本查询
​–查询员工表和部门表

select * from emp,dept;

2.外连接（左右连接）

--列子
左连接：where e.deptno(+) = d.deptno;
右连接：where e.deptno = d.deptno(+);

3.子连接 -------------返回的表当做查询条件
4. oracle中的伪列
1.rowid ​ -----物理地址
2.rownum -----每次查询都会生成一次的序列号
3.分页查询
–获取6-10行的数据

select * from
(select rownum rn , e.* from emp e) r
where r.rn >5 and r.rn < 11;

或者

select * from
(select rownum rn , e.* from emp e) r
where r.rn between 6 and 10;

五.oracle高级

1.视图
创建视图需要 dba 或者 create view 权限才可以
–查询角色权限sql

select * from role_sys_privs where role='CONNECT';

创建视图的语法：

create view 视图名称 as 子查询
create view empd20 as select * from emp where deptno = 20;

创建视图的另一种用法：

create or replace view 视图名称 as 子查询
create or replace view empd20 as select * from emp where deptno = 20;

2.索引

-- 创建单列索引
CREATE index 索引名 on 表名(列名)
-- 创建多列索引
Create index emp_idx1 on emp(job,ename);
触发规则：是 where 条件必须是索引列的原始值。

3. PL/SQL

1.符号说明
​ 类型符号说明
赋值运算符 :=
Java和C#中都是等号，PL/SQL的赋值是 :=
​ 特殊字符 || 字符串连接操作符。
​ – PL/SQL中的单行注释。
​ /* , *****/ PL/SQL中的多行注释，多行注释不能 嵌套。
标签分隔符 <<,>> 只为了标识程序特殊位置。

范围操作符 … 比如：1…5 标识从１到 ５
算术运算 符 +，-，*，/ 基本算术运算符。
求幂操作 ** 比如：3**2=9

关系运算 符 >，<,>=,<=,= 基本关系运算符，=表示相等关系，不是 赋值。
不等关系 <>,!=
逻辑运算符 AND,OR,NOT 逻辑运算
2.变量声明

declare 
    i number(2) := 66;
begin
    dbms_output.put_line(i);
end;

3.常亮声明:constant

declare
    pi constant number := 3.14;
    r  number :=5 ;
   area number;
begin
  area := power(r,2) * pi;
  dbms_output.put_line('圆面积是：' || area);
  end;

4.pl/sql 数据类型
1.标量数据类型
varchar2(长度)
number(精度，小数)
date
timestamp :日期类型
char(长度)
long
boolean
binary_integer
pls_integer :比上一个速度更快
natural
naturaln :不能为空
positive :binary_integer的子类型，正整数
positiven :和positive 一样，但是不为null
real :浮点数
int,integer,smallint :整数
signtype ：值有1，-1，0
string :和varchar2相同
2.属性数据类型
当声明一个变量的值是数据库中的一行或者是数据库中某列时，可以直接使用属性类型来声明。
%rowtype ：行
%type ：列
5.pl/sql条件控制和循环控制
1.条件控制

if-then
if-then-else
if-then-elseif-else
case :相当于switch

2.循环结构
loop-end :无条件循环

LOOP 
--循环体
END LOOP;

while ：循环

WHILE 条件 LOOP
--循环体
END LOOP;

for

FOR 循环变量 IN [REVERSE] 循环下限..循环上限 LOOP
--循环体
END LOOP；

3.游标

CURSOR 游标名 [ (参数名 数据类型,参数名 数据类型,...)] IS SELECT 语句;
列子：
cursor c1 is select ename from emp;

declare
cursor pc is
select * from emp;
pemp emp%rowtype;
begin
open pc;
loop
fetch pc
into pemp;
exit when pc%notfound;
dbms_output.put_line(pemp.empno || ' ' || pemp.ename);
end loop;
close pc;
end;

六. PL/SQL中动态执行SQL语句

execute immediate 动态语句字符串
[into 变量列表]
[using 参数列表]

七.PL/SQL的异常处理[应用]

1.预定义异常
异常名称 异常码 描述

DUP_VAL_ON_INDEX ORA-00001 试图向唯一索引列插入重复值
INVALID_CURSOR ORA-01001 试图进行非法游标操作。
INVALID_NUMBER ORA-01722 试图将字符串转换为数字
NO_DATA_FOUND ORA-01403 SELECT INTO语句中没有返回任何记录。
TOO_MANY_ROWS ORA-01422 SELECT INTO语句中返回多于1条记录。
ZERO_DIVIDE ORA-01476 试图用0作为除数。
CURSOR_ALREADY_OPEN ORA-06511 试图打开一个已经打开的游标

2.语法

BEGIN
--可执行部分
EXCEPTION -- 异常处理开始
WHEN 异常名1 THEN
--对应异常处理
WHEN 异常名2 THEN
--对应异常处理
……
WHEN OTHERS THEN
--其他异常处理
END;

3.自定义异常

异常定义：myexception EXCEPTION；
异常引发：RAISE myexception;

4.子程序

1.过程【理解】
--语法：
create [or replace] procedure 过程名[(参数名 in/out 数据类型)]
{is|as}
--声明部分
begin
--可执行部分
[exception]
--异常处理部分
end [过程名];