ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle人门学习笔记

oracle人门学习笔记

原创 Linux操作系统 作者:you12323464 时间:2011-03-18 21:10:48 0 删除 编辑

1.如何显示当前用户是谁?
show user;
2.进入sql*plus管理工具,运行里面输入
sqlplusw就可以了。
3.disc断开当前数据库的连接
4.passw修改密码命令
示例:passw 回车
更改snapall的口令
旧口令:*******
新口令:******
重新键入新口令:******
口令已更改
5.如何在忘掉用户密码的情况下修改密码。
alter user sys identified by snapall;
6.ping命令的其他技巧:在一般情况下还可以通过ping对方让对方返回给你的TTL值大小,粗略的判断目标主机的系统类型是Windows系列还是UNIX/Linux系列,一般情况下Windows系列的系统返回的TTL值在100-130之间,而UNIX/Linux系列的系统返回的TTL值在240-255之间,当然TTL的值在对方的主机里是可以修改的,Windows系列的系统可以通过修改注册表以下键值实现。
7.文件操作命令
a.start和@打开
sql>@ d:1.sql
sql>start d:1.sql
b.edit编辑
sql>edit d:1.sql
c.spool将sql*plus上的内容输出到指定文件中去
sql>spool d:1.sql并输入sql>spool off
8.linesize设置显示行的宽度,默认是80
show linesize;
set linesize 90;

pagesize设置每页显示行的数目,默认是14
用法与linesize一样

9.授权和收回权限(系统权限是不会级联回收的,对象权限会级联回收的)
grant connect to user;
grant select on emp to xiaoming;给小明赋予查询当前用户的下的emp表的权限。
revoke connect to user;
revoke select on emp from xiaoming
如果有其它权限,收回select不会对其它权限由影响。
10.resource这个权限可以在任意表空间下面建表。
11.如果是对象权限:
如果想让用户有赋予权限的权限,可以在后面添加 with grant option;
例如:grant select on emp to xiaoming with grant option;

如果是系统权限
添加with admin option;

12.用户登陆次数太多会被限制。
创建profile文件
create profile lock_account limit
failed_login_attempts 3 password_lock_time 1;
alter user xiaoming lock_account;

13.要求用户定期更改密码
create profile myprofile limit password_life_time 10 password_grace_time 2;
aler user xiaoming profile myprofile;
要求用户每10天去修改密码,宽限期为2天。
14.当不需要profile时,可以去删除。
drop profile myprofile cascade;
15.如何查看一个表的结构
desc 表名;
改一个表的名字
rename 旧表名 to 新表名;

16.oracle默认的日期格式是'09-6月-99'
表示是1999年6月9号。
可以更改数据库日期的默认格式。
alter session set nls_date_fromat='yyyy-mm-dd';
17.delete 语句删除后可以恢复;
先savepoint aa;
然后delete;
然后rollback to aa;
18.清频命令
clear
19.使用列的别名排序
select ename,sal*12 "年薪" from emp order by "年薪" asc;
列名需要使用双引号圈中
20.如何取消重复行
select distinct deptno,job from demp;
21.打开显示操作时间的开关
set timing on;
22.疯狂复制法
insert into users (userid,username) select * from users;
然后不断执行;
23.oracle对字段里面的内容是区分的,对字段本身不区分。
24.如何处理NULL值
使用nvl函数
select sal*13+nvl(comm,0)*13 from emp;
25.用查询结果创建表
create tabel mytable1(id,name,sal) as select empno,ename,sal form. emp;
26.union命令
将两个查询出来的集合并,重复的只保留一条数据。
union all 命令

将两个查询出来的集合并,重复的也都保留原有数目。
intersect 命令
取交集
minus 命令
取差集,用前面那个查询结果减去后面那个查询合集。
27.to_date函数
插入时间格式,to_date('1988-12-2','yyyy-mm-dd');
默认格式为2-12月-1988,不要用函数来表示。
28.update 语句跟新数据技巧
update emp set (job,sal,comm)=(select job,sal,comm from emp where ename='SMITH') where ename='SCOTT';
29.创建保存点命令
savepoint a1;
回退命令;
rollback to a1;
回退全部事务
rollback
注意:一旦提交commit;原来的保存点就没有了,也就无法回退。
30.只读事务:只是允许执行查询,不能执行其他dml操作。设置了只读事务后,其它会话更改信息,也不会对当前的只读事务由影响。
命令如下:set transaction read only;

31.字符函数
lower(char):将字符串转化为小写的格式。
upper(char):将字符串转化为大写的格式。
length(char):返回字符串的长度。
substr(char,m,n):取字符串的字串。m代表从第几个开始取,n代表取的字符个数。
select upper(substr(ename,1,1))||lower(substr(ename,2,length(ename)-1)) from emp;

replace(char1,search_string,replace_string)
替换函数

32.数字函数
round(n,[m])该函数用于执行四舍五入,如果省掉mround(n),则四舍五入到整数;如果m是正数,则四舍五入到小数点的m位后,如果m是负数,则四舍五入到小数点的m位前。
trunc(n,[m])该函数用于截取数字,如果省掉m,就截取小数部分,如果m是正数就截取到小数点的m位后,如果m是负数,则截取到小数点的前m位。
mod(m,n)
floor(n)返回小于或是等于n的最大整数。
ceil(n)返回大于或是等于n的最小整数。

dual表是一个测试表,用了做测试的。

33. 日期函数
sysdate()------返回系统时间
add_months(d,n)
last_day(d): 返回d日期的月的最后一天

34.转换函数
to_char()
to_char(sal,'L99999.99');
to_char(hiredate,'yyyy-mm-dd hh24:mi:ss')

35.系统函数
sys_context
teminal:当前会话客户所对应的终端的标示符
lanuage:语言
db_name:当前数据库的名称
nls_date_format:当前会话客户所对应的日期格式
session_user:当前会话客户所对应的数据库用户名
current_schema:当前会话客户所对应的默认的方案名

例如:想查询当前用的是哪个数据库
select sys_context('USERENV','db_name') from dual;
host:返回数据库所在主机的名称

36.普通dba没有startup 和shutdown权限。

37.用户名,权限,角色
查询dba_user可以显示所有数据库用户的详细信息
查询数据字典视图dba_sys_privs,可以显示用户所具有的系统权限;
查询数据字典视图dba_tab_privs可以显示用户具有的对象权限;
查询数据字典视图dba_col_privs可以显示用户具有的列权限;
查询数据字典视图dba_role_privs可以显示用户具有的角色;

38.
查询数据库有多少种角色?
select * from dba_roles;
一般预先定义为25个

查询数据库中所有的系统权限,一般是dba
select * from system_privilege_map order by name;

查询数据库中所有的对象权限,一般是dba
select distinct privilege from dba_tab_privs;

查询数据库的表空间
select tablespace_name form. dba_tablespace;

如何查询一个角色包括的权限?
a.一个角色包含的系统权限
select * from dba_sys_privs where grantee='CONNECT';
或者
select * from role_sys_privs where role='';
b.一个角色包含的对象权限
select * from dba_tab_privs where grantee='CONNECT';

如何查看某个用户具有什么用的角色?
select * from dba_role_privs where grantee='';

显示当前用户可以访问的所有数据字典视图
select * from dict where comments like '%grant%';

显示当前数据库的全称
select * from global_name;


39.改变表空间的状态
1.使表空间脱机
alter tablespace 表空间名字|offline;
2.使表空间联机
alter tablespace 表空间名字 online;
3.只读表空间
当设置为只读是,就不能进行修改操作(但是系统表空间无法改为只读状态)
alter tablespace 表空间名字 read only;
4.设置表空间为可读可写
alter tablespace 表空间名字 read write;

40.
1.如果知道表空间名字,想显示该表空间的的所有表
select * from all_tables where tablespace_name='表空间名字';
2.如果知道表名,想查看该表属于哪个表空间
select tablespace_name,table_name from user_tables where table_naem='表名';
3.删除表空间
drop tablespace '表空间' including contents and datafiles;

41.移动数据文件
假如说一块磁盘的一部分磁道被损坏了,改数据文件不能使用了,为了能够重新使用,需要将这些文件的副本移动到其他磁盘,然后恢复。
1.确定数据文件所在的表空间
select tablespace_name from dba_data_files where file_name='数据文件的路径';

2.使表空间脱机
确保数据文件的一致性,将表空间转变为offline的状态。
alter tabespace 表空间名字 offline;

3.使用命令移动数据文件到指定的目标位置
host move 损坏数据文件的路径 新的数据文件的路径;

4.执行alter tablespace命令
在物理上移动了数据文件后,还必须一致性命令对数据文件作逻辑的修改;
alter tablespace 表空间名 rename datafile
'原来的数据文件路径' to '新的数据文件路径';

5.是表空间联机
alter tablespace 表空间名 online;

42.
1.显示表空间的信息
select tablespace_name from dba_tablespaces;
2.显示表空间所包含的数据文件
select file_name,bytes form. dba_date_files where tablespace_name=='表空间名';


43.约束:约束用于确保数据库数据满足特定的商业规则。在oracle中,约束包括:not null(非空),unique(唯一,该列不能重复,但是可以为空),primary key(主键,该列不能重复,而且不能为null),foreign key(外键)和check(限制条件)五种。

如果在建表时忘记建立必要的约束,可以在建表后用alter table命令来为表增加约束,但是要注意,增加not null约束时,需要 使用modify选项,而增加其他四种约束使用add选项。

例如:alter table jacky modify 字段名 not null;
alter table jacky add constraint 约束名 unique(字段名);
alter table jacky add constraint 约束名 check(字段名 in ('zon','df','dfs'));

如何删除约束?
alter table 表名 drop constraint 约束名;
有些主键要删除,可能有外键的关系,无法删除,要用如下命令:
alter table 表名 drop primary key cascade;


如何显示约束信息
1.显示约束信息
select constraint_name,constraint_type,status,validated from user_constraints where table_name='表名';
2.显示约束列
select column_name,position form. user_cons_columns where constraint_name='约束名';


44.索引
create index emp_index on emp(ename,job);

create index emp_index on emp(job,ename);
这两个索引时不同的。


45.权限
系统权限不是级联回收的。
对象权限是级联回收的。

46.角色
创建角色。
create role myrole1 not identified;
给空的角色赋予权限
grant create session to 角色名 with admin option;
分配角色给用户
grant 角色名 to 用户名 with admin option;
删除角色
drop role 角色名;
问:如果一个角色被删除,那被分配这个角色的用户还有权限吗?
答:用户权限没有了。
精细访问控制


47.PLSQL编程
PLSQL是oralce在标准的sql语言上的额扩展。
1.创建一个存储过程
create table mytest(name varchar2(30),passwd varchar2(30));

create or replace procedure jacky219 is
begin
--执行部分
insert into mytest values('殷志刚','123');
commit;
end;
/

如何查看错误信息
show error;

如何调用过程

1.exec 过程名(参数1,参数2...)

2.call 过程名(参数1,参数2...)

编写规范
1.注释
单行注释 --
select * from emp;--取得员工信息
多行注释
/*...*/来划分

2.标示符号的命名规范
1)当定义变量时,建议用v_作为前缀,例如v_sal
2)当定义常量时,建议用c_作为前缀
3)当定义游标时,建议用_cursor作为后缀;
4)当定义例外时,建议用e_作为前缀;

包含定义部分和执行部分的pl/sql快
declare
v_ename varchar2(5);--定义字符串变量
begin
select ename into v_ename from emp where empno=&no;
dbms_output.put_line('雇员名:'||v_ename);
end;
/

重要:其中into代表将查询出来的值插入到v_ename中去,&代表是要我们要从控制台输入值进去。
||代表是连接符。no只是随便是个变量名,可以用aaa,bbb都可以。


48.函数
函数用于返回特定的数据,一般返回一个值。
例:
输入雇员的姓名,返回该雇员的年薪

create function sp_fun2(spName varchar2) return number is yearSal number(7,2);
begin
select sal*12+nvl(comm,0)*12 into yearSal from emp where ename=spName;
return yearSal;
end;

在sqlplus中调用函数的方法
var income number
call sp_fun2('殷志刚') into:income;
print income

1.包
包用于逻辑上组合过程和函数。
创建一个包,声明了该包有一个过程和一个函数
create package sp_package is
procedure update_sal(name varchar2,newsal number);
function annual_income(name varchar2) returen number;
end;

2.给包 sp_package实现包体
create package body sp_package is
procedure update_sal(name varchar2,newsal number)
is
begin
update emp set sal=newsal where ename=name;
end;
function annual_income(name varchar2)
return number is
annual_salary number;
begin
select sal*12+nvl(comm,0) into annual_salary from emp
where ename=name;
return annual_salary;
end;
end;

3.如何调用包的过程或是函数
exec sp_package.update_sal('SCOTT','120');


49.触发器

触发器是指隐含的执行的存储过程。当定义触发器时,必须要指定触发的事件和触发的操作,常用的触发事件包括insert,update,delete语句,而触发操作实际就是一个pl/sql快。可以用create trigger来建立触发器。

50.变量
1.定义标量

1.1定义一个变长的字符串
v_ename varchar2(10);
1.2定义一个小数 范围-9999.99-9999.99
v_sal number(6,2);
1.3定义一个小数并给一个初始值为5.4:=是pl/sql的赋值号
v_sal2 number(6,2):=5.4
1.4定义一个日期类型的数据
v_hiredate date;
1.5定义一个布尔变量,不能为空,初始值为false
v_valid boolean not null default false;

%type类型
val_jacky emp.ename%type;表示变量val_jacky和表emp中的字段ename,的长度为标准,这样就不会出现长度不够的情况。


2.符合变量
用于存放多个值的变量,主要包括
pl/sl记录,pl/sql表,嵌套表和varray(动态表)

3.复合类型的pl/sql表
相当于高级语言的数组。


4.参照变量-ref cursor游标变量
例如:使用pl/sql编写一个快,可以输入部门号,并显示该部门所有员工的姓名和他的工资。

declare
type sp_emp_cursor is ref cursor;
test_cursor sp_emp_cursor;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
open test_cursor for select ename,sal from emp where deptno=&no;
loop
fetch test_cursor into v_ename,v_sal;
//update emp set sal:=sal+100 where sal<200 and ename=v_ename;
exit when test——cursor%notfound;
dbms_output.put_line('名字:'||v_ename||'工资:'||v_sa;);
end loop;
end;

51.控制结构

1.if then endif;

2.if then else endif;

3.if then elsif then else endif;

4.循环语句
4.1 loop end loop;
4.2 while..loop end loop;
4.3基本for循环
4.4goto语句,跳转;跳转的语句必须加《》
4.5输出语句开关:set serveroutput on;打开
set serveroutput off;关闭


52.编写分页过程
1.将一个表中的记录编号,
select t1.*,rownum rn from (select * fromo emp) t1;

2.可以按照每10条来取。
select t1.*,rownum rn from (select * fromo emp) t1 where rownum<1=0;

3.取第6条到第10条的语法
select * from
(select t1.*,rownum rn from (select * fromo emp) t1 where rownum<10;)
where rn>=6;

4.分页的过程代码
create or replace procedure fenye
(tableName in varchar2,
Pagesize in number,
pageNow in number,
myrows out number,
mypageCount out number,
p_cursor out tespackage.test_cursor
) is
v_sql varchar2(1000);
v_begin number:=(pageNow-1)*Pagesize+1;
v_end number:=pageNow*Pagesize;
begin
v_sql:='select * from (select t1.*,rownum rn from(select * from '|| tableName||') t1 where rownum<='||v_end||' ) where rn>='||v_begin;
open p_cursor for v_sql;
v_sql:='select count(*) from '||tableName;
execute immediate v_sql into myrows;
if mod(myrows,Pagesize)=0 then
myPageCount:=myrows/Pagesize;
else
myPageCount=myrows/Pagesize+1;
end if;
end;

53.视图
视图是一个虚拟表,其内容友查询定义。
1.视图不占用磁盘空间
2.视图不能建索引
3.视图可以简化 复杂查询
4.视图可以提高安全性

创建视图
create view 视图名 as select语句 [with read only(假如添加了这个,那这个视图就不能被修改)]

创建或者修改视图
create or replace 视图名 as select语句[with read only];

删除视图
drop view 视图名;



Link URL: http://you12323464.itpub.net/post/42943/515112

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21187846/viewspace-689939/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2011-03-18

  • 博文量
    5
  • 访问量
    20825