一.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 ;
6.显示约束信息
select constraint_ame,table_name,column_name from user_cons_columns where table_name = ‘itlb’;
7.总结
1.增加unique,primary key , foreign key ,check 的时候，必须使用 alter table 的 add子局
2.增加not null约束，必须使用alter table 语句的 modify 子句
3.在同一个方案中，约束名必须唯一，并且不能和其他对象重名
二.数据操纵语言（DML）
1.增删改查
----------------与mysql语句相同
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.字符函数
- ascll(x) 返回字符x的ASCII码。
- 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;
格式化字符
参数 示例 说明
9 999 指定位置处显示数字。
. 9.9 指定位置返回小数点
, 99,99 指定位置返回一个逗号
$ $999 数字开头返回一个美元符号
EEEE 9.99EEEE 科学计数法表示
L L999 数字前加一个本地货币符号
PR 999PR 如果数字式负数则用尖括号进行表示
- to_date(x , fmt) 把一个字符串以 fmt 格式转换为一个日期类型
- to_number(x , fmt) 把一个字符串以 fmt 格式转换为一个数字。fmt 格式字符参考上表
5.通用函数
-
nvl(x , value) 如果x为空 ， 返回value (备胎) ，否则返回x
-
nav2(x , value1 , value2) 如果x非空 ， 返回value1 ，否则返回value2
-
decode 函数，该类函数类似 if…else if…else
decode (col/expression, search1,result1,search2, result2....default);- col/expression:列名或表达式
- Search1，search2…:用于比较的条件
- Result1, result2…:返回值
- 如果 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 [过程名];
170
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
