oracle序列
create sequence id_seq;
select id_seq.nextval from dual;
select id_seq.currval from dual;
分页查询
分页查询
SELECT * FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM TABLE_NAME) A WHERE ROWNUM <= 10) WHERE RN >= 0
排序分页查询
SELECT * FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM TABLE_NAME ORDER BY ID DESC) A WHERE ROWNUM <= 10) WHERE RN >= 0
触发器 (自动执行的 类似于 onclick)
1、创建触发器 (主要定义什么条件下触发执行),几个触发时机 after,before,instead of)
create trigger tri_name
after delete or update or insert on emp(真对的是哪个表触发)
for each row(代表行级触发,即每执行一行就会触发,如果不加的话是默认是语句级触发器即一条语句触发一次)
begin
if deleting then
insert into emp_log values('delete',:old.tid); (记录日志)
elsif updating then
insert into emp_log values('update',:new.tid);
elsif inserting then
insert into emp_log values('insert',:new.tid);
end if;
end tri_name;
/
2、创建日志表:
create table emp_log(logname varchar2(20),logid number);
3、删除触发器
drop trigger tri_name;
备份与恢复(必须到dos里面执行) 物理备份直接考文件和逻辑备份
1.导出,备份表:
exp username/password file=c://u.dbf tables=(test.emp)
2.导入,恢复表:
imp system/password file=c://u.dbf full=y
3.看所有参数解释
exp -?
过程,过程先编译后执行
1.创建过程的语法
create procedure procedure_name
as
begin
dbms_output.put_line('hello');
end procedure_name;
/
2.查看用户所有存储过程:desc user_procedures;
select object_name from user_procedures;
3.删除过程
drop procedure procedure_name;
3.创建加参数的过程
create procedure procedure_name(tname varchar2)
as (不定义长度)
msg varchar2(20);(定义长度)
begin
msg:='hello';
dbms_output.put_line(msg||tname);
end procedure_name;
/
4.使用过程:
execute procedure_name('first name');
会输出 hello first name
5.语句中不能有return 但可以用out来返回值
过程参数的模式:in(传入), out(传出)
in out(传入或传出), 默认in
create procedure
procedure_name(tname in varchar2,result out varchar2)
as (传入) (传出)
msg varchar2(20);
begin
msg:='hello';
result:=msg||tname;
end procedure_name;
/
6.另一种使用过程的方式:
declare
temp varchar2(20);
begin
procedure_name('test name',temp);
dbms_output.put_line(temp);
end;
/
函数
1.创建函数
create function function_name return varcahr2
as
p1 number:=22;
p2 number:=33;
begin
return 'result is:'||(p1+p2);
end function_name;
/
2.查询用户创建的对象: desc user_objects;
select object_name form user_objects;
3.执行函数
select function_name from dual;
oracle sql命令
1.查看用户的所有角色:desc user_role_privs;
select granted_role from user_role_privs;
查看用户的权限:desc user_sys_privs;
select privilege from user_sys_privs where username like 'username';
2.查看用户所拥有的对象权限:desc user_tab_privs;
select * from user_tab_privs;
3.查看管理员所有的对象权限:desc dba_tab_privs;
select * from dab_tab_privs;
4.oracle数据字典 desc dictionary; 或desc dict;含有名字和描述
select comments from dict where table_name like 'USER_TABLES';
desc dict_columns;可以查看表中每个字段代表什么意思;
查看系统里面的所有表:
select table_name from dict;
查看表面和对应的字段:
desc table_name,column_name from dict_columns;
查看单独的一个表:desc user_tables;
查看描述:select column_name,comments from dict_columns where
table_name like 'usr_tables' and column_name like 'table_name';
5.查看当前正在使用的是哪一个用户:select user from dual; 或show user;
查看当前用户创建的所有表和表使用的是哪个表空间:
select table_name,tablespace_name form user_tables;
查询oracle所有的权限:desc system_privilege_map;
select name from system_privrlege_map;
查询oracle中所有的角色:desc dba_roles;
select role from dba_roles;
查询角色对应的权限:desc role_sys_privs;
select privilege from role_sys_privs where role='CONNECT';
查看用户中的所有权限:desc session_privs;
select * from session_privs;
查看用户的所有角色:desc user_role_privs;
select granted_role from user_role_privs;
oracle sql语句
1.查询用户创建了哪些表:
desc user_tables;
select table_name from user_tables;
2.创建表:create table emp(id int,name varchar2(20),birth date);
删除表:drop table emp;
3.查看表结构:desc emp;
4.修改表结构:
添加一列:alter table emp add sex varchar2(10);
修改表列数据类型:alter table emp modify sex varchar(20);
修改表列名:alter table emp rename column sex to sex_a;
删除表结构一列:alter table emp drop column sex_a;
5.添加数据:insert into emp values(1,'nihao','12-12月-12');
添加带转换的日期数据:
inert into emp values(1,'nihao',to_date('2012-12-12','yyyy-mm-dd'));
6.查询数据:select * from emp;
查询带转换的日期数据:
select name,to_char(birth,'yyyy-mm-dd') from emp;
在查询改变日期格式怎么该显示的列名:
select name,to_char(birth,'yyyy-mm-dd') birth from emp;
7.修改数据:
update emp set id=2,name='ni' where name='nihao';
8.删除数据:
方法1:delete from emp where name='nihao'; 大数据较慢,可rollback恢复
可以设定回滚点savepoint sp
回滚时用rollback sp;
方法2:truncate table emp;大数据较快,不可恢复;
表的复制
1.复制表结构和表所有数据
create table new_emp as select * from emp;
2.复制表结构和加条件的表数据
create table new_emp as select * from emp where id<5;
3.只复制表结构:
create table new_emp as select * from emp where 1=2;
4.复制表中的部分字段:
create table new_emp as select name,sex from emp;
5.已有表结构只复制另一个表中的数据:
insert into new_emp select * from emp;
6.已有表结构只复制另一个表中的部分数据:
insert into new_emp(name,sex) select name,sex from emp where id<5;
序列(伪自增)
1.创建序列:
create sequence s_name
start with 1
increment by 1
maxvalue 1000
nocycle 或 cycle
cache 5 (当访问频率大时设定个缓存,存储几个默认值)
/
查询自己创建的序列:desc user_sequences;
select sequence_name from user_sequences;
或select * from user_sequences;
2.取出序列的值:
select s_name.nextval from dual;
查询当前的序列值:
select s_name.currval from dual;
3.使用序列来当成自增主键添加数据
create table emp(id int,name varchar2());
insert into username values(s_name.nextval,'nihao');
或者添加数据时数据也加上数子,中间用||拼接:
insert into username value(s_name.nextval,'nihao'||s_name.currval);
4.修改序列
alter sequence s_name maxvalue 1111 cycle;
修改后查询一下 select * from user_sequences;
5.删除序列
drop sequence s_name;
索引