sql
DDL defination 数据定义语言(crate创建,alter更改,drop删除)
DCL 数据控制语言(grant授权,revoke回收)
DML manipulation 数据操作语言(增删改查)
DQL 数据查询语言(排序,分组)
TCL Transaction Control Language 事务控制语言(commit提交,rollback回滚)
1. 表空间的创建
create tablespace 名 datafile ‘文件地址.dbf’ size 大小 autoextend on next 大小 maxsize unlimited;
autoextend on next:分配空间用完之后再次拓展的大小
maxsize:最大可为多少
unlimited:无限制扩展大小(以磁盘为准)
注:使用drop删除表空间不会删除系统中的文件,需要加上:
including contents and datafiles
2. 用户相关
create 创建用户 drop 删除 alter 修改
lock 加锁 unlock解锁
grant 授权 revoke 回收
`connect 临时用户权限`
`resource 正式数据库用户权限`
dba 管理员权限
**语法:**grant 权限[or 权限] to 用户名
revoke 权限 from 用户名
3. 表常用操作
表user_tables 用于查看用户下的所有表
表user_tab_cols 用于查看表结构
create table emp_form as select * from emp; 复制表格全部数据到一个新的表(可复制部分数据)
重命名表:alter table 表名 rename to 新表名
添加备注:
comment on table/column 表名/表名.字段名 is 新名
改表结构:
添加列:alter table 表 add(列名 类型)
修改列名:- rename column 列 to 新列名
修改列:- modify(列名 类型 [,列名 类型..])
删除列:- drop column
- drop (列名 [,列名..])
删除表数据(不能回滚):truncate table 表名
distinct 去重
4. 约束
约束类型:
主键约束:primary key
唯一约束:unique 允许为空
选择约束:check
外键约束:foreign key
表user_constraints 用于查看约束
表外添加约束:
add constraint…
使用drop删除约束
select * from user_constraints;
-- 添加表约束
alter table test1 add constraint PK_sal primary key (sal);
-- 外键约束需要引入外表
alter table student add constraint fk_stno foreign key (stno) references grade(stno) ;
alter table test1 add constraint ck_comm check (comm='男' or comm ='女' or comm = '未知');
--语法:alter table 表名 drop constraint 约束名
alter table student drop constraint ck_stage;
5. 序列
创建:create sequence 序列名
当前序列为序列名.currval,下一个序列nextval
oracle中的空表:dual
创建指定参数的序列:
create sequence 序列名
start with 10 --开始值
maxvalue 1000 --最大值
increment by 5 --增量值
cache; --缓存值
6. 常用函数
字符
to_char(hiredate,'yyyy-mm-dd') 转化为特定格式的字符
to_char(sal,'99,999.999') 9代表任意数字
to_date(hiredate,'yyyy-mm-dd') 格式化时间
trunc() 取时间
initcap(待转换的字符串) 字符首字母大写,其余小写
concat(str1,str2) 将两个字符串拼接返回新的字符串(可直接使用||拼接字符串)
substr(str,开始位置,保留数量) 截取字符串
lpad(str,总位数,'x') 左补齐函数,不足位数使用x补齐,rpad() 右补齐函数
instr(str,查找字符,开始位置) 返回该字符出现的第一个位置
length(str) 返回字符串长度
数字
ceil(待取值) 向上取整(有小数则+1) floor()相下取整
round(值,要保留的小数点位数) 四舍五入
trunc(值,保留的小数位) 不会四舍五入
日期函数
add_month(输入的时间,要增加的月份数) 给日期增加指定的月份数后返回
next_day(指定的日期,星期几) 返回指定日期的下一个星期几
last_day(日期) 返回指定日期的所在月份的最后一天
trunc(日期[,'yyyy'/'mm'/'day']) 截断时分秒,返回年月日[返回指定日期所在年份的第一天 / 返回指定日期所在月份的第一天 / 返回指定日期所在星期的第一天]
round(日期[,'yyyy'/'mm'/'day']) 当天已过12小时,四舍五入到下一天
转换函数
year:y表示年的最后一位,类推
month:mm
day:dd表示当月第几天,ddd表示当年第几天,dy表示当周第几天
hour:hh表示12小时制,hh24表示24小时制 --MySQL中HH
minute:mi表示分钟 --MySQL mm
second:ss表示秒
to_date(日期,指定格式时间)
to_char(日期,截取时间)
聚合函数 5个:max(), min(), avg(), sum(), count()
nvl(值,补全值) 值为空时显示补全值
decode(列名,判断1,输出1 [, 判断1,输出1…])
分析函数
- row_number() 排名,值重复时会选择一个值靠前,然后依次排名
row_number() over() 默认从第一行到最后一行排名
row_number() over(order by 排序):根据某一列排序,然后排名
row_number() over(partition by 分组 order by 排序):根据某个值分组,然后某一列排序,然后排名
- rank() 值重复时排名并列,然后跳过原有排名,排名总数不变(后接over()与上同)
- dense_rank() 值重复时排名并列,不跳过原来的排名
7. 连接与查询
内连接 inner join 等值连接找出相关联的满足条件的所有数据
自然连接 natural join 找共同的列名,没有则笛卡尔积
外连接:做(外)连接,右(外)连接,全(外)连接
全连接:full join 查询所有数据
自连接:连接自身表格
交叉连接:笛卡尔积
子查询:在查询中嵌套查询
exists() 判断是否存在
联合查询
并集 union all 连接两个查询,取并集
union 去重取并集
交集 intersect 显示交集
补集 A minus B 减去查询结果B的数据,减去交集,显示剩余结果
分页查询
rownum 相当于行号,只能从1开始,放在子查询中可以在外部查询中使用别名调用,显示范围内数据。
备份
–命令方式:
CMD exp 导出用户/密码@数据库名称 file = “导出地址+文件名.dmp”
CMD imp 导入用户/密码@数据库名称 fromuser = “导出用户名” touser = “导入用户名” file = “D:/scott.dmp”
过程
1. plsql匿名块
declare
--声明变量区
· 变量名 变量类型;
· ~ emp.ename%type; 获取表中的变量类型
· ~(相当于表的变量名) emp%rowtype; 获取表中所有行类型及列,通过变量名.表中的列调用或赋值。
· type 包名 is record( --打包
变量名 类型;
);
变量名 包名; --必须给包重命名,调用时必须通过新的名调用。
begin
--代码执行区(执行sql、存储过程)
--注意:select语句中需要有into赋值
exception
--异常处理部分
end;
--输出语句dbms_output.put_line();
--使用 := 赋值
--使用 & 获取输入值
2. 分支语句
/* 1. if语句 */
--语法
if(条件) then
elsif(条件) then
else
--必须加可执行语句,可使用null; 不会做任何事情
end if; --必须结束if
/* 2. case */
case
when 条件 then
--满足条件执行
else
----注意case中的语句不要分号;
3. 循环语句
--1. while循环 语法
while (条件) loop
--代码区
end loop;
--2. for
for (条件) loop
end loop;
--3. loop
loop
exit when 条件; --满足条件退出循环
end loop;
注意:循环中的赋值使用 := 给变量赋值,for循环时使用a…b代表可取闭区间a到b的值,所有的循环都必须退出。
***结束循环的语句:***3个
continue; 退出本次循环
exit; 退出循环
return; 直接退出程序(过程,方法,匿名块)
goto标签
使用goto(不区分大小写)标签可直接跳到指定的位置,语法如下:
if a>b then
goto targ;
else
return;
end if;
<<targ>> --标签分隔符,定义标签名
c := a-b;
4. 异常的处理
plsql块中,会产生三种错误:
1. 预定义错误:系统定义好的异常
-- no_data_found 无数据返回
-- too_many_rows 返回的数据过多(返回多行数据)
-- duo_val_on_index 唯一索插入重复值,违反主键约束
-- zero_divide 除数为0
-- invalid_cursor 失效的游标
调用:
...
exception
when no_data_found then
--处理异常
end;
2. 非预定义错误:违反了Oracle规范,有错误编号和信息,但系统并未定义异常名称,我们将为这种错误定义一个名字,并绑定他的错误代码。
-- 语法格式
declare
--定义异常
异常名 exception;
pragma exception_init(异常名,-22222); --绑定异常编码(不能与Oracle的异常编码相同)
begin
--过程操作
exception
when 异常名 then
--处理异常
end;
3. 自定义错误:出现某种情况,我们将其定义为异常,违反了用户的自定义异常
--自定义异常,将异常抛出
declare
vsal emp.sal%type;
vsal_error exception; --定义异常
begin
select sal into vsal from emp where empno=&empno
if vsal>3000 then
--定义抛出异常编码值、错误信息,会弹窗提示错误信息及编码
raise_application_error(code, '错误信息');
end if;
end;
--自定义异常,处理
declare
v_sal emp.sal%type;
v_sal_error exception;
begin
select sal into v_sal from emp where empno = &empno;
if v_sal < 3000 then
-- 抛出异常 抛出的异常一定要进行处理
raise v_sal_error;
-- 定义异常编码值
raise_application_error(-20000,'薪资问题');
end if;
exception
--处理抛出的异常
when v_sal_error then
--处理异常
end;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qXPCz1Fr-1628438379821)(C:\Users\Administrator\Desktop\资料\笔记\exception)]
5. 游标
游标是一个存储数据的集合
5.1 静态游标
静态游标分为 隐式游标 和 显式游标
-
隐式游标
plsql程序执行dml语句时,会自动创建隐式游标,名称为sql
隐式游标的属性:
%found sql语句影响一行或多行 返回true
%notfound sql没有影响行返回true
%rowcount 返回sql影响的行数
%isopen 游标是否打开,始终为false(隐式游标是关闭的)
begin update emp set comm = 400 where deptno = 10; if (sql%found) then --使用sql%属性名 调用 --执行操作 end if; end;
-
显式游标
--语法 declare --声明游标(默认关闭,是一个结果集,使用时需要打开) cursor 游标名 is select * from emp; --声明行变量类型 v_emp emp%rowtype; begin --打开游标 open 名; --从游标中提取一行数据 fetch youbiao into v_emp; --关闭游标 close youbiao; end;
在循环中使用游标
for for循环自动打开游标,并提取第一行数据,提取完成后结束循环,自动关闭游标。
for a in youbiao loop
--处理
end loop;
while 循环 使用while需要定义的行变量,需要打开游标,并需要手动提取数据
declare
cursor youbiao is select * from emp;
v_emp emp%rowtype;
begin
open youbiao;
fetch youbiao into v_emp;
while(youbiao%found) loop --找不到数据退出循环
--处理
fetch youbiao into v_emp;
end loop;
close youbiao; --游标一定要关闭,不关闭不会报错,占内存
end;
loop 循环与while同
5.2 动态游标
ref 动态游标可以在运行的时候与不同的语句进行关联,他是动态的。
ref 动态游标被用于处理多行的查询结果集,ref 动态游标是ref 类型的变量,类似于指针。
定义ref 动态游标类型:type<类型名> is ref cursor return <返回类型>;
声明ref 动态游标:<游标名> <类型名>;
打开ref 动态游标:OPEN<游标名> FOR <查询语句>;
--强类型的动态游标 带有return语句
declare
type v_leixing is ref cursor return emp%ROWTYPE;
v_youbiao v_leixing;
v_emp emp%rowtype;
begin
open v_youbiao for select * from emp;
loop
fetch v_youbiao into v_emp;
exit when v_youbiao%notfound;
dbms_output.put_line(v_youbiao%rowcount||'==='||v_emp.ename);
end loop;
close v_youbiao;
end;
--弱类型的动态游标 不带return
declare
type v_leixing is ref cursor;
v_youbiao v_leixing;
v_emp emp%rowtype;
begin
open v_youbiao for select * from emp;
loop
fetch v_youbiao into v_emp;
exit when v_youbiao%notfound;
dbms_output.put_line(v_youbiao%rowcount || '===' || v_emp.ename);
end loop;
close v_youbiao;
end;
6. procedure
语法:
create procedure 名(参数 in type,参数 out type)
--in表示输入参数(外部传入,值不可更改),out表示输出参数,in out表示输入输出函数,可更改。
--参数可设置默认值,调用时需要传入参数,
--若创建时无传入参数则不使用()
as/is --可不声明变量。过程中可以不使用变量
--声明变量块
变量名 变量类型;
begin --过程
--可为某些变量赋初值
--可嵌套内部块 declare...
--sql语句
--pl/sql语句
exception
--异常时执行
end;
或者直接点击过程—>测试。
使用 过程名(参数名 := 需要输出的参数名)
as和is的区别:
在视图view中只能使用as,在游标中cursor只能使用is。
———— 过程批量插入数据:
--存储过程中插入数据
create or replace procedure add_d
is
begin
for i in 1..100 loop
insert into t_emp values
(
i+1000,
dbms_random.string('u',5),
'ClERK',
i+1,
trunc(sysdate-i),
1000+i,
10,
2000
);
end loop;
end;
函数
无参存储函数 有参存储函数
语法:
create function 函数名(参数名 类型[,参数]) --可创建无参存储函数,不可加括号
return 返回类型 --不加分号,不可为空
is
--定义变量区;
begin
--函数实现
return 返回值;
end;
--调用
declare
--定义变量
begin
--函数需要使用变量接收返回值
end;
--可以直接使用查询语句调用
select 函数名(参数) from dual;