整理文件发现自己之前刚学Oracle时整理的sql基础，分享下。
--[数据类型]
number -10 38次方--- 10 38次方
char 定长，最多存储2000字符，查询效率高
varchar2 可变长，最多存储4000字符，查询效率低
clob 最大存储4G
blob 存储二进制数据，4G
创建表
create table [表名](
字段 数据类型[not null or primary key，如果没有not null，默认可以为空] ,
字段 数据类型[not null or check ，如果没有not null，默认可以为空] ,
字段 数据类型[not null or unique ，如果没有not null，默认可以为空]
)
修改表的操作
添加字段
alter table [表名] add (字段名，数据类型)
删除字段
alter table [表名] drop column [字段名]
修改字段
alter table [表名] modify (字段名，数据类型)
给已有的表重命名
rename [原表名] to [新表名]
添加主键
alter table [表名] add constraint [约束名] primary key(字段);--如果有多个，称之为联合主键
添加外键
alter table [从表名] add constraint [约束名] foreign key (从表名的外键字段) references 主表(主键字段) on delete cascade
删除约束
alter table [从表名] drop constraint [约束名]
创建表
create table [表名] as select * from [表2] -- 复制表2
create table [表名] as select * from [表2] where 1=2 --复制表结构
create table [表名] as select [字段1],[字段2] from [表名]
插入数据
insert into [表名] values (值1,值2,.......值n);
insert into [表名] (字段1,字段2,....字段n) values (值1,值2,.......值n);
insert into [表名] select [字段1，字段2，.....字段n] from [表名] where 字段='值'；
insert into [表名] ([字段1，字段2，.....字段n]) select [字段1，字段2，.....字段n] from [表名] where 字段='值'；
insert all into [表名1] values (1004,'haha')
into [表名2] values (1005,'heihei') select * from dual;
---删除数据
delete from [表名] where 字段='值'; --速度慢，要写日志：把日志写在回滚表中
truncate table [表名] --速度快，不需要写日志
drop table [表名]
drop user [用户名] cascade
drop view [视图名]
修改数据
update [表名] set [字段]='值' where 字段='值';
update [表名] set [字段]='值' and [字段1]='值' where 字段='值';
查询数据
select 字段1，字段2，......字段n from [表名];
select 字段1，字段2，......字段n from [表名] where 字段='值';
select 字段1，字段2，......字段n from (select 字段1，字段2，......字段n from [表名] where 字段='值' ) where 字段='值'
select 字段1，字段2，......字段n from [表名] where 字段 = select 字段1 from [表名];
组函数--聚合函数
MAX,MIN,AVG,COUNT,SUM
常用的函数
substr() upper() lower() instr() lpad() rpad() trim()
to_char() to_date() trunc() round() nvl() nvl2()
initcap() next_day() length() replace() mod()
------------------
case
when 表达式 then 值1
when 表达式 then 值2
else 默认值
end
decode(字段,
'值1','值1结果',
'值2','值2结果',
'值3','值3结果',
'默认值'
)
-----case 和 decode 区别：decode只能判断等值的表达式
case可以判断区间表达式
联合查询
左连接：
select * from [表名1] left outer join [表名2] on 表名1.字段=表名2.字段
select * from [表名1],[表名2] where 表名1.字段=表名2.字段(+)
右连接
select * from [表名1] right outer join [表名2] on 表名1.字段=表名2.字段
select * from [表名1],[表名2] where 表名1.字段(+)=表名2.字段
全链接
select * from [表名1] full outer join [表名2] on 表名1.字段=表名2.字段
---去重复，(有重复的数据只显示一次)
select * from emp20
union
select * from emp
---不去重复(有重复的数据全部显示)
select * from emp20
union all
select * from emp
---(差集[emp20中除了交集以外的部分])
select * from emp20
minus
select * from emp
---交集
select * from emp20
intersect
select * from emp
分组:group by
排序:order by desc降序 默认升序 asc
分组之后过滤:having
单行过滤:where
select * from emp
where .....
group by .....
having ......
order by .....
使用 group by 要注意的事项：
select 后面的字段必须由：组函数或者是分组的字段
---分页
select * from
(select rownum no, e.* from
(select * from emp order by sal desc)
e where rownum <=10) where no >1;
--- 创建视图
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view[(aliaslist)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT cname]
[WITH READ ONLY]
OR REPLACE表示如果视图已经存在则重新创建
FORCE表示创建视图而不管基表是否存在，NOFORCE表示只有基表存在的情况下才创建视图（默认情况）
view表示视图名
aliaslist表示别名列表（别名之间用逗号隔开，别名的个数需要和子查询中选择的字段或表达式的个数一致）
subquery表示子查询
WITH CHECK OPTION表示只有可访问的行在视图中才能被插入或更新
cname表示为约束指定的名称
WITH READ ONLY表示在该视图中不可执行DML操作
insert into [视图名] values('值1','值2',.....'值n');//此数据插入视图对应的表中
update [视图名] set [字段]= '值' where [字段]= '值';
delete from [视图名] where [字段]= '值';
如果视图中包含下面的内容，就不可以从视图中删除数据
组函数
GROUP BY子句
DISTINCT关键字
伪列ROWNUM关键字
如果视图中包含下面的内容，就不可以在视图中更新数据：
组函数
GROUP BY子句
DISTINCT关键字
伪列ROWNUM关键字
用表达式定义的字段
如果视图中包含下面的内容，就不可以在视图中插入数据：
组函数
GROUP BY子句
DISTINCT关键字
伪列ROWNUM关键字
用表达式定义的字段
基本中的NOT NULL字段不在视图中
/*
创建序列
*/
create sequence seq_semple
increment by 1
start with 1
maxvalue 9999
nocycle
nocache
-- 修改序列
alter sequence seq_semple
increment by 2
maxvalue 9999
nocycle
nocache
insert into test values(seq_semple.nextval,'小明','123');
insert into test values(seq_semple.nextval,'小花','1234');
insert into test values(seq_semple.nextval,'小丽','234');
insert into test values(seq_semple.nextval,'小红','12345');
commit;
select * from test;
select seq_semple.nextval, seq_semple.currval from dual;
select seq_semple.currval from dual;
--删除序列
drop sequence seq_semple;
/*
索引
*/
--创建索引
create table t(
tid number primary key,
tname varchar2(20),
tel number unique not null--默认自动创建索引
)
--创建索引
create index i_test1
on test(tname,tpsw)
--删除索引
drop index i_test1;
--系统权限
create user jm identified by jm123;
create user tom identified by tom123;
--让用户jm需要登录；并且让用户同时具有赋权限的功能
grant create session to jm with admin option
--让jm给tom赋权限（登录）
grant create session to tom;
--收回jm的登录权限，但是，tom依然可以登录；(不是级联回收)
revoke create session from jm;
--对象权限 emp表在scott【方案】，给mqx用户赋予访问(select)emp表的权限
grant select on scott.emp to mqx;
--给jm赋予查emp表的权限，同时权限下放
grant select on scott.emp to jm with grant option;
grant select on scott.emp to tom;
--回收jm的访问emp表的权限(tom不能访问到emp表，证明是级联回收)
revoke select on emp from jm;
--如何创建角色
create role joy;
--给角色授权
grant create table,create view,create sequence,
create procedure to joy;
--给用户授予角色
grant joy to jm;
--创建存储过程
--public void show(){System.out.println();}
create or replace procedure p_pro
is
begin
insert into test values(2,'狗狗','234');
end;
--执行存储过程
begin
p_pro;
end;
/*public static void show(String msg){
System.out.println(msg);
}
public static void main(String args[]){
String msg = "Hello World";
show(msg);
}
*/
--有参数的存储过程
create or replace procedure sp_pro(
iid number,
iname varchar2,
ipass varchar2
)
is
begin
insert into test values(iid,iname,ipass);
end;
--执行存储过程
begin
sp_pro (3,'猪猪','345');
end;
--根据ID查找动物的名字；
create or replace procedure sp_p
(
iid in number,
outname out varchar2,
outtpsw out number
)
is
begin
select tname,tpsw into outname,outtpsw from test where tid=iid;
end sp_p;
/*
int i =0;
String str = "a";
String s;
Scanner input = new Scanner(System.in);
s = input.next();
*/
declare
oname varchar2(20);--接受返回来的值
outpsw varchar2(20);
iid number:='&请输入要查询的ID';
begin
sp_p(iid,oname,outpsw);
dbms_output.put_line('动物的名称：'||oname||'动物的编号：'||outpsw);
--System.out.println("Hello World!");
exception
when no_data_found then
dbms_output.put_line('朋友输入的数据有误，请重新输入');
end;
commit;
select * from test;
truncate table test;
--删除存储过程
drop procedure [存储过程名];
--查询所有存储过程
select * from user_objects where
object_type='PROCEDURE';
--创建函数
create or replace function f_m1
return varchar2
is
begin
return '我真是一个天才啊！';
end;
/*
public String findById(int id){
String msg=null;
if(id==1){
msg="星期一";
}else if(id==2){
msg="星期二";
}
return msg;
}
*/
--执行函数
select f_m1 from dual;
create or replace function f_m2(idd number)
return varchar2
is
outname varchar2(20);
begin
select tname into outname from test where tid=idd;
return outname;
end;
--执行函数【命令窗口执行】
var sname varchar2;
call f_m2(2) into:sname;
--查询所有函数名称
select object_name from user_objects
where object_type='FUNCTION';
--创建触发器
create or replace trigger t_test
before insert on test
for each row
begin
--dbms_output.put_line('用户名'|| :new.tname);
--dbms_output.put_line('密码'|| :new.tpsw);
insert into msgtable values (seq_se.nextval,'宠物名：'||:new.tname||'宠物编号'||:new.tpsw);
end;
--调用存储过程向test表中插入数据
begin
sp_pro(13,'美国摩登考拉No.1','888');
end;
alter table msgtable modify (msg varchar2(50));
commit;
--删除触发器
drop trigger t_test;
select * from msgtable;
/*
游标
*/
declare
cursor cul is select * from test;
srow test%rowtype;
begin
open cul;
fetch cul into srow;
while cul%found loop
dbms_output.put_line(srow.tid||srow.tname||srow.tpsw);
fetch cul into srow;
end loop;
close cul;
end;
declare
v_num number:=10086;
v_context varchar2(20) default '本人很聪明！';
v_ip constant number(3,1):=10.1;
v_sal emp.sal%type;
v_emp emp%rowtype;
begin
v_context:='我们都是好孩子';
dbms_output.put_line(v_context);
end;
declare
v_row emp%rowtype;
begin
select * into v_row from emp where empno = 7369;
dbms_output.put_line(v_row.ename);
end;
declare
age number:= 20;
begin
age:='&请输入你的年龄';
if age<20 then
dbms_output.put_line('我还不够法定结婚年龄！');
else if age >22 then
dbms_output.put_line('我刚大学毕业！');
else
dbms_output.put_line('我该的啦！');
end if;
end if;
end;
|
