Oracle学习笔记.docx

1、Oracle学习笔记Oracle 学习笔记 这是荆风在学习Oracle数据库时，自己归纳总结的笔记，非常实用，希望对学Oracle的友友有所帮助！ SQLselect table_name from user_tables: 查询全部表SQLdesc dept 查询dept表结构SQLselect * from dept; 查询dept表中的全部列SQLselect deptno from dept; 查询dept表中的某一列deotnoSQLselect deptno NO,dname “NAME”,loc as XXX from dept; 列换别名SQLselect DISTINT（过滤

2、）mgr from dept; 过滤同列（mgr）中的重复行SQLselect * from dept where dept=10； 查询dept表中某一行（deptno=10）SQLselect * from dept where loc =BOSTON； BOSTON必须大写SQLselect ename , sal from emp 查询sal在12501600之间的用户 where sal between 1250 and 1600; SQLselect ename , salfrom emp 查询sal在1250和1600的用户where sal in (1250,1600); SQ

3、Lselect sum(sal) , max(sal) , avg(sal) from emp; 求sal的最大值，最小值，平均值SQLselect deptno , max(sal) from emp 不同部门的最高工资 group by deptno；SQLselect dept . dname , emp . job dept, emp 为表名；dname,job为列名；deptno为行名 from dept , emp 查看每个人的姓名和工作 where dept . deptno=emp . deptno;SQL select d . dname , e . job 上指令可令别名简

4、写 from dept d , emp e dept, emp 为表名；dname,job为列名；deptno为行名 where d . deptno=e . deptno; 查看每个人的姓名和工作SQLselect d . dname ,e . job from dept d , emp e where d . deptno 20 and e .salselect username From dba_users Where username=D0195； D0195为用户名SQLselect Sname , Sno ,Ssex from student 查询所有姓刘的学生的姓名，学号和性别

5、where Sname like刘%；SQLselect Snamefrom student 查询姓“欧阳”且全名为3个汉字的学生的姓名where Sname like 欧阳_；SQLselect Sname , Sno from student 查询名字中第二个字为“阳”字的学生的姓名和学号 where Sname like _阳%；SQLselect Student . * ，Sc . * from Student , Sc 将Student表与Sc表中同一学生的Sno行连接起来 where student .Sno = sc . Sno;SQLcreate table tt (id nu

6、mber(7)， name varchar(20) , 创建tt表 tno char(20)；SQLselect id ,dump(id) from tt； 查询tt表中的id数据类型，长度SQLselect count(*) from tt; 统计tt表的数量SQLalter table tt add sge number ; tt表中添加列(sge),并设其数据类型为numberSQLalter table tt add time date default sysdate ;SQLalter table tt drop column sge; tt表中删除列（sge）SQLalter ta

7、ble tt modify (sge number(7); tt表中修改列（sge）的数据长度SQLinsert into tt values (1,aaa,bbb,default); tt表中行号为1的一行插入aaa,bbb内容SQLinsert into tt values (1,aaa);SQLinsert into tt values (3,null); 在id为3的那行中插入空行SQLupdate tt set name=ccc 将tt表中id=3的那行的name名修改为ccc where id=3；SQLupdate tt set id= 4 将tt表中name名修改为ccc的那行

8、的id改为4 where name=ccc；SQLupdate tt set id= 4 将tt表中id=1的那行的id修改为4 where id=1;SQLdelete from ttwhere id=1; 删除tt表中id=1的那行SQLdelete from tt； 删除tt表中的所有行SQLdrop table tt; 删除ttSQLcreate view id_view as 创建tt表中id的视图id_view select id from tt;SQLcreate view name_ up_view as select name from tt where name=bbb w

9、ith check option constraint up_ck;SQLcreate view name_ up_view as select name from tt where name=bbb with check option ;(限制别人只能查询，不能修改)SQLselect * from user_views; 查询数据字典SQLdesc user_views;SQLdrop view name_ up_view; 删除视图name_up_viewSQLselect id from tt; = select * from id_view;SQLcreate index tt_id

10、_ind 创建tt表的索引 on tt(id);SQLdesc user_indexes; 索引字典SQLselect index_name,index_type,table_ower,table_name from user_indexes; 查询索引的名字，类型，用户，表名SQLset autotrsce on SQL 执行计划SQLselect * from scott.tt; （ 仅限管理员查询）SQLselect * /*索引提示语句*/ scott.tt; 强制索引查询SQLset autotrsce off SQL 取消执行计划SQLselect * from scott.tt;

11、 （ 仅限管理员查询）SQL desc user_ind_columns; 索引字典SQLselect index_name,itable_name,column_name from user_ind_columns; 查询索引的名字,表名j及索引所在的列SQLcreate table deptcon( deptno number(3), dname varchar(15) NOT NULL, loc varchar(20);SQLdesc user_constraints; 约束字典SQLselect owner , constraint_name , table_name , column

12、_name from user_cons_columns; 查询约束名，表名，约束所在的列SQLselect owner , constraint_name , constraint_type , table_name from user_constraints; 查询约束名，约束类型，表名SQLalter table deptcon 添加约束（deptcon_dname_uk），其添加类型为UNIQUE， add constraint deptcon_dname_uk UNIQUE(dname); 约束所在列是dnameSQLalter table deptcon 删除约束（deptcon_

13、dname_uk） drop constraint deptcon_dname_SQLalter from deptcon 创建主键（deptcon_deptno_pk），主键所在列是deptno add constraint deptcon_deptno_pk primary key(deptno);SQLalter from deptcon 创建外键（deptcon_deptno_fk），外键所在列是deptno add constraint deptcon_deptno_fk foreign key(deptno) references deptcon(deptno) ;SQLselec

14、t table_name from user_tables: 只用于查表SQLselect distinct object_type from user_object； 用于查表，视图SQLselect * from user_catalog; 用于查表，视图 SQLselect sysdate from dual; dual为空表SQLcreate table dept_2 as 快速创建表dept_2 select * from dept;SQLtruncate table dept_2; 将表dept_2的数据删除，但此表仍在（不会产生日志）SQLdelete table dept_2;

15、 将表dept_2的数据删除，但此表仍在（会产生日志）SQLrename dept_2 to dept_3; 将表dept_2重名为dept_3SQLcommit; 提交SQLrollback; 回滚（到提交时的情况）SQLlist 查看上一次敲的指令（可简写成L）SQLchang/from/fom 将dept表中的2号行中的from改为fom 2 * from deptSQLsave c:scott.txt 将scott.txt文本保存在C盘中SQL:sselect * from emptSQL: /SQLset line 300 将一行设置为300个字符SQL: /SQLcol empno

16、 format a5 将empno列调为5个字符SQL: /SQLexitSQLselect * from v$transaction; 查询所有确定或不确定的事务SQLselect * from v$lock; 查询确定的事务SQLselect * from user_views; 查询数据字典SQLdesc user_views;SQLselect * from v$archived_log: 查询归档日志SQLselect * from v$logfile; 查询在线日志SQLselect * from v$log; 查询日志用户管理及权限SQLsqlplus 用户名/密码 as sys

17、dba; 用户登录SQLshow user 显示登录用户SQLcreate user scott indentified by tiger; 在管理员帐户下，创建用户testSQLalter user scott identified by tiger; 修改scott用户的登录密码为tigerSQLgrant connect to scott; 授用户连接登录的权限SQLgrant resource to scott；SQLgrant create session to scott; 授予scott用户创建session的权限，即登陆权限SQLgrant create table to sc

18、ott; 授用户创建表的权限SQLgrant unlimited tablespace to scott;SQLgrant unlimited session to scott; 授予scott用户使用表空间的权限 SQLgrant connect,resource to scott with adminn option; 把系统权限授予scott用户SQLgrant create session to public; 把创建表的权限赋予所有人SQLgrante drop table to scott; 授予删除表的权限 SQLgrant insert table to scott; 插入表的

19、权限 SQLgrant update table to scott; 修改表的权限 SQLgrant all to public; 授予所有权限(all)给所有用户(public) /*管理员授权*/ SQLselect * from user_col_privs ; 查看当前用户对某个表列的操作权限 SQLselect * from user_tab_privs ; 查看当前用户所有表的权限 oralce对权限管理比较严谨，普通用户之间也是默认不能互相访问SQLgrant select on tablename to scott; 授予scott用户查看指定表的权限 SQLgrant dro

20、p on tablename to scott; 授予删除表的权限 SQLgrant insert on tablename to scott; 授予插入的权限 SQLgrant update on tablename to scott; 授予修改表的权限 SQLgrant update(name) on tt to scott ; 只能够对tt表中的name列进行修改 SQLgrant insert(id) on tablename to scott; SQLgrant update(id) on tablename to scott; 授予对指定表特定字段的插入和修改权限，注意，只能是in

21、sert和update SQLgrant alter all table to scott; 授予scott用户alter任意表的权限 SQLrevoke connect,resource from scott; 收回scott用户的权限SQLgrant select on a01.tt to b01 with grant option; (仅限管理员)告诉a01用户能把a01用户的tt表的查询权限授予b01SQLrevoke select on a01.tt from b01; 收回a01用户的授权SQL grant insert on a01.tt to b01 with grant op

22、tion; (仅限管理员)告诉a01用户能把a01用户的tt表的插入权限授予b01SQLgrant select on tt to b01; a01用户将tt表的查询权限授予b01用户SQL revoke select on tt from b01; a01用户收回b01的查表权限SQLrevoke select on a01.tt from b01; 收回a01用户的授权系统权限的传递：SQLgrant alter table to A with admin option;那么A可以通过把该权限传递给B，如果想B也可以传递下去那么可以也带上with admin option SQLgrant

23、 alter table to B;SQLgrant alter any table to scott ; scott不能够将该权限授予其它用户 SQLgrant alter any table to scott with admin option ; 能将该权限授予其它用户 对象权限的传递: SQLgrant select on mytab to A with grant option; 那么A可以把在表mytab的select权限赋予给B，如果B想也能传递该select权限也可以带上with grant optionSQLgrant select on mytab to B;SQL gra

24、nt select on mytable to thtwinj2ee ; 不具有授权操作 SQLgrant select on mytable to thtwinj2ee with grant option ; 具有授权操作 SQL select * from user_sys_privs; 查看当前用户所有权限（可查询一个系统的视图(数字字典) SQL select * from user_tab_privs; 查看所用用户对表的权限 SQL select username from v$session; 必须用DBA 用户查看所有登录的用户SQLselect * from test.stu

25、dent; sys、system等DBA 用户查看 其他用户(test)中的对象(表)SQL角色即权限的集合，可以把一个角色授予给用户 SQLcreate role myrole; 创建角色myrole SQLgrant create session to myrole; 将创建session的权限授予myrole SQLgrant myrole to zhangsan; 授予zhangsan用户myrole的角色 SQLdrop role myrole; 删除角色 但是有些权限是不能授予给角色的，比如unlimited tablespace和any关键字 一些常用视图的区分dba_table

26、s dba_all_tables user_tables user_all_tables all_tables all_all_tables当前用户所属的所有表(注意大写)SQL select tablespace_name,table_name from user_all_tables where table_name=STUDENT;SQL select table_name,tablespace_name from user_tables where table_name=STUDENT;TABLE_NAME TABLESPACE_NAME- -STUDENT USERSsys 要查看d

27、ba_all_tables,ALL_ALL_TABLES才能查看到 test 用户的表。SQL select owner,table_name,tablespace_name from dba_all_tables where owner=TEST;SQL select owner,table_name,tablespace_name from all_all_tables where owner=TEST;SQL select owner,table_name,tablespace_name from dba_tables where owner=TEST;SQL select owner,

28、table_name,tablespace_name from ALL_tables where owner=TEST;OWNER TABLE_NAME TABLESPACE_NAME-TEST STUDENT USERS1.DBA_ALL_TABLES describes all object tables and relational tables in the database. Its columns are the same as those in ALL_ALL_TABLES.2.ALL_ALL_TABLES describes the object tables and rela

29、tional tables accessible to the current user.3.USER_ALL_TABLES describes the object tables and relational tables owned by the current user. Its columns (except for OWNER) are the same as those in ALL_ALL_TABLES.-SQLCREATE DATABASE database-name 创建数据库SQL drop database dbname 删除数据库SQL create table tabname(col1 type1 not null primary key,col2 type2 not null,.) 创建新表根据已有的表创建新表： A：create table tab_new like tab_old (使用旧表创建新表)B：create table tab_new as select col1,col2 from tab_old definition onlySQL drop table tabname 删除新表SQLAlter table tabname add column col type SQLAlter table tabname add