Oracle学习笔记.docx
《Oracle学习笔记.docx》由会员分享，可在线阅读，更多相关《Oracle学习笔记.docx（30页珍藏版）》请在冰豆网上搜索。
Oracle学习笔记
Oracle学习笔记
这是荆风在学习Oracle数据库时，自己归纳总结的笔记，非常实用，希望对学Oracle的友友有所帮助！
SQL>selecttable_namefromuser_tables:
查询全部表
SQL>descdept查询dept表结构
SQL>select*fromdept;查询dept表中的全部列
SQL>selectdeptnofromdept;查询dept表中的某一列deotno
SQL>selectdeptnoNO,dname“NAME”,locasXXXfromdept;列换别名
SQL>selectDISTINT（过滤）mgrfromdept;过滤同列（mgr）中的重复行
SQL>select*fromdept
wheredept=10；查询dept表中某一行（deptno=10）
SQL>select*fromdept
whereloc=‘BOSTON’；BOSTON必须大写
SQL>selectename,sal
fromemp查询sal在1250—1600之间的用户
wheresalbetween1250and1600;
SQL>selectename,sal
fromemp查询sal在1250和1600的用户
wheresalin（1250,1600）;
SQL>selectsum（sal）,max（sal）,avg（sal）
fromemp;求sal的最大值，最小值，平均值
SQL>selectdeptno,max（sal）
fromemp不同部门的最高工资
groupbydeptno；
SQL>selectdept.dname,emp.jobdept,emp为表名；dname,job为列名；deptno为行名
fromdept,emp查看每个人的姓名和工作
wheredept.deptno=emp.deptno;
SQL>selectd.dname,e.job上指令可令别名简写
fromdeptd,empedept,emp为表名；dname,job为列名；deptno为行名
whered.deptno=e.deptno;查看每个人的姓名和工作
SQL>selectd.dname,e.job
fromdeptd,empe
whered.deptno>20ande.sal<2500;
SQL>selectusername
Fromdba_users
Whereusername=‘D0195’；D0195为用户名
SQL>selectSname,Sno,Ssex
fromstudent查询所有姓刘的学生的姓名，学号和性别
whereSnamelike‘刘%’；
SQL>selectSname
fromstudent查询姓“欧阳”且全名为3个汉字的学生的姓名
whereSnamelike‘欧阳__’；
SQL>selectSname,Sno
fromstudent查询名字中第二个字为“阳”字的学生的姓名和学号
whereSnamelike‘__阳%’；
SQL>selectStudent.*，Sc.*
fromStudent,Sc将Student表与Sc表中同一学生的Sno行连接起来
wherestudent.Sno=sc.Sno;
SQL>createtablett
（idnumber（7），
namevarchar（20）,创建tt表
tnochar（20））；
SQL>selectid,dump（id）fromtt；查询tt表中的id数据类型，长度
SQL>selectcount（*）fromtt;统计tt表的数量
SQL>altertablettaddsgenumber;tt表中添加列（sge）,并设其数据类型为number
SQL>altertablettaddtimedatedefaultsysdate;
SQL>altertablettdropcolumnsge;tt表中删除列（sge）
SQL>altertablettmodify（sgenumber（7））;tt表中修改列（sge）的数据长度
SQL>insertintott
values（1,‘aaa’,‘bbb’,default）;tt表中行号为1的一行插入‘aaa’,‘bbb’内容
SQL>insertintott
values（1,‘aaa’）;
SQL>insertintott
values（3,null）;在id为3的那行中插入空行
SQL>updatett
setname=‘ccc’将tt表中id=3的那行的name名修改为ccc
whereid=3；
SQL>updatett
setid=4将tt表中name名修改为ccc的那行的id改为4
wherename=‘ccc’；
SQL>updatett
setid=4将tt表中id=1的那行的id修改为4
whereid=1;
SQL>deletefromtt
whereid=1;删除tt表中id=1的那行
SQL>deletefromtt；删除tt表中的所有行
SQL>droptablett;删除tt
SQL>createviewid_view
as创建tt表中id的视图id_view
selectidfromtt;
SQL>createviewname_up_view
as
selectnamefromtt
wherename=‘bbb’
withcheckoptionconstraintup_ck;
SQL>createviewname_up_view
as
selectnamefromtt
wherename=‘bbb’
withcheckoption;（限制别人只能查询，不能修改）
SQL>select*fromuser_views;查询数据字典
SQL>descuser_views;
SQL>dropviewname_up_view;删除视图name_up_view
SQL>selectidfromtt;=select*fromid_view;
SQL>createindextt_id_ind创建tt表的索引
ontt（id）;
SQL>descuser_indexes;索引字典
SQL>selectindex_name,index_type,table_ower,table_name
fromuser_indexes;查询索引的名字，类型，用户，表名
SQL>setautotrsceon
SQL>执行计划
SQL>select*fromscott.tt;（仅限管理员查询）
SQL>select*/*索引提示语句*/scott.tt;强制索引查询
SQL>setautotrsceoff
SQL>取消执行计划
SQL>select*fromscott.tt;（仅限管理员查询）
SQL>descuser_ind_columns;索引字典
SQL>selectindex_name,itable_name,column_name
fromuser_ind_columns;查询索引的名字,表名j及索引所在的列
SQL>createtabledeptcon（
deptnonumber（3）,
dnamevarchar（15）NOTNULL,
locvarchar（20））;
SQL>descuser_constraints;约束字典
SQL>selectowner,constraint_name,table_name,column_name
fromuser_cons_columns;查询约束名，表名，约束所在的列
SQL>selectowner,constraint_name,constraint_type,table_name
fromuser_constraints;查询约束名，约束类型，表名
SQL>altertabledeptcon添加约束（deptcon_dname_uk），其添加类型为UNIQUE，
addconstraintdeptcon_dname_ukUNIQUE（dname）;约束所在列是dname
SQL>altertabledeptcon删除约束（deptcon_dname_uk）
dropconstraintdeptcon_dname_
SQL>alterfromdeptcon创建主键（deptcon_deptno_pk），主键所在列是deptno
addconstraintdeptcon_deptno_pk
primarykey（deptno）;
SQL>alterfromdeptcon创建外键（deptcon_deptno_fk），外键所在列是deptno
addconstraintdeptcon_deptno_fk
foreignkey（deptno）referencesdeptcon（deptno）;
SQL>selecttable_namefromuser_tables:
只用于查表
SQL>selectdistinctobject_typefromuser_object；用于查表，视图
SQL>select*fromuser_catalog;用于查表，视图……
SQL>selectsysdatefromdual;dual为空表
SQL>createtabledept_2
as快速创建表dept_2
select*fromdept;
SQL>truncatetabledept_2;将表dept_2的数据删除，但此表仍在（不会产生日志）
SQL>deletetabledept_2;将表dept_2的数据删除，但此表仍在（会产生日志）
SQL>renamedept_2todept_3;将表dept_2重名为dept_3
SQL>commit;提交
SQL>rollback;回滚（到提交时的情况）
SQL>list查看上一次敲的指令（可简写成L）
SQL>chang/from/fom将dept表中的2号行中的from改为fom
2*fromdept
SQL>savec:
\scott.txt将scott.txt文本保存在C盘中
SQL>:
sselect*fromempt
SQL>:
/
SQL>setline300将一行设置为300个字符
SQL>:
/
SQL>colempnoformata5将empno列调为5个字符
SQL>:
/
SQL>exit
SQL>select*fromv$transaction;查询所有确定或不确定的事务
SQL>select*fromv$lock;查询确定的事务
SQL>select*fromuser_views;查询数据字典
SQL>descuser_views;
SQL>select*fromv$archived_log:
查询归档日志
SQL>select*fromv$logfile;查询在线日志
SQL>select*fromv$log;查询日志
用户管理及权限
SQL>sqlplus用户名/密码assysdba;用户登录
SQL>showuser显示登录用户
SQL>createuserscottindentifiedbytiger;在管理员帐户下，创建用户test
SQL>alteruserscottidentifiedbytiger;修改scott用户的登录密码为tiger
SQL>grantconnecttoscott;授用户连接登录的权限
SQL>grantresourcetoscott；
SQL>grantcreatesessiontoscott;授予scott用户创建session的权限，即登陆权限
SQL>grantcreatetabletoscott;授用户创建表的权限
SQL>grantunlimitedtablespacetoscott;
SQL>grantunlimitedsessiontoscott;授予scott用户使用表空间的权限
SQL>grantconnect,resourcetoscottwithadminnoption;把系统权限授予scott用户
SQL>grantcreatesessiontopublic;把创建表的权限赋予所有人
SQL>grantedroptabletoscott;授予删除表的权限
SQL>grantinserttabletoscott;插入表的权限
SQL>grantupdatetabletoscott;修改表的权限
SQL>grantalltopublic;授予所有权限（all）给所有用户（public）/*管理员授权*/
SQL>select*fromuser_col_privs;查看当前用户对某个表列的操作权限
SQL>select*fromuser_tab_privs;查看当前用户所有表的权限
oralce对权限管理比较严谨，普通用户之间也是默认不能互相访问
SQL>grantselectontablenametoscott;授予scott用户查看指定表的权限
SQL>grantdropontablenametoscott;授予删除表的权限
SQL>grantinsertontablenametoscott;授予插入的权限
SQL>grantupdateontablenametoscott;授予修改表的权限
SQL>grantupdate（name）ontttoscott;只能够对tt表中的name列进行修改
SQL>grantinsert（id）ontablenametoscott;
SQL>grantupdate（id）ontablenametoscott;授予对指定表特定字段的插入和修改权限，注意，只能是insert和update
SQL>grantalteralltabletoscott;授予scott用户alter任意表的权限
SQL>revokeconnect,resourcefromscott;收回scott用户的权限
SQL>grantselectona01.tttob01withgrantoption;（仅限管理员）告诉a01用户能把a01用户的tt表的查询权限授予b01
SQL>revokeselectona01.ttfromb01;收回a01用户的授权
SQL>grantinsertona01.tttob01withgrantoption;（仅限管理员）告诉a01用户能把a01用户的tt表的插入权限授予b01
SQL>grantselectontttob01;a01用户将tt表的查询权限授予b01用户
SQL>revokeselectonttfromb01;a01用户收回b01的查表权限
SQL>revokeselectona01.ttfromb01;收回a01用户的授权
系统权限的传递：
SQL>grantaltertabletoAwithadminoption;
那么A可以通过把该权限传递给B，如果想B也可以传递下去那么可以也带上withadminoptionSQL>grantaltertabletoB;
SQL>grantalteranytabletoscott;scott不能够将该权限授予其它用户
SQL>grantalteranytabletoscottwithadminoption;能将该权限授予其它用户
对象权限的传递:
SQL>grantselectonmytabtoAwithgrantoption;
那么A可以把在表mytab的select权限赋予给B，如果B想也能传递该select权限也可以带上withgrantoption
SQL>grantselectonmytabtoB;
SQL>grantselectonmytabletothtwinj2ee;不具有授权操作
SQL>grantselectonmytabletothtwinj2eewithgrantoption;具有授权操作
SQL>select*fromuser_sys_privs;查看当前用户所有权限（可查询一个系统的视图（数字字典）
SQL>select*fromuser_tab_privs;查看所用用户对表的权限
SQL>selectusernamefromv$session;必须用DBA用户查看所有登录的用户
SQL>select*fromtest.student;sys、system等DBA用户查看其他用户（test）中的对象（表）
SQL
角色即权限的集合，可以把一个角色授予给用户
SQL>createrolemyrole;创建角色myrole
SQL>grantcreatesessiontomyrole;将创建session的权限授予myrole
SQL>grantmyroletozhangsan;授予zhangsan用户myrole的角色
SQL>droprolemyrole;删除角色
但是有些权限是不能授予给角色的，比如unlimitedtablespace和any关键字
一些常用视图的区分
dba_tablesdba_all_tablesuser_tablesuser_all_tablesall_tablesall_all_tables
当前用户所属的所有表（注意大写）
SQL>selecttablespace_name,table_namefromuser_all_tableswheretable_name='STUDENT';
SQL>selecttable_name,tablespace_namefromuser_tableswheretable_name='STUDENT';
TABLE_NAMETABLESPACE_NAME
------------------------------------------------------------
STUDENTUSERS
sys要查看dba_all_tables,ALL_ALL_TABLES才能查看到test用户的表。
SQL>selectowner,table_name,tablespace_namefromdba_all_tableswhereowner='TEST';
SQL>selectowner,table_name,tablespace_namefromall_all_tableswhereowner='TEST';
SQL>selectowner,table_name,tablespace_namefromdba_tableswhereowner='TEST';
SQL>selectowner,table_name,tablespace_namefromALL_tableswhereowner='TEST';
OWNERTABLE_NAMETABLESPACE_NAME
-------------------------------------------------------------------------------------
TESTSTUDENTUSERS
1.DBA_ALL_TABLESdescribesallobjecttablesandrelationaltablesinthedatabase.ItscolumnsarethesameasthoseinALL_ALL_TABLES.
2.ALL_ALL_TABLESdescribestheobjecttablesandrelationaltablesaccessibletothecurrentuser.
3.USER_ALL_TABLESdescribestheobjecttablesandrelationaltablesownedbythecurrentuser.Itscolumns（exceptforOWNER）arethesameasthosein
ALL_ALL_TABLES.
----------------------------------------------------------------------
SQL>CREATEDATABASEdatabase-name创建数据库
SQL>dropdatabasedbname删除数据库
SQL>createtabletabname（col1type1[notnull][primarykey],col2type2[notnull],..）创建新表
根据已有的表创建新表：
A：
createtabletab_newliketab_old（使用旧表创建新表）
B：
createtabletab_newasselectcol1,col2…fromtab_olddefinitiononly
SQL>droptabletabname删除新表
SQL>Altertabletabnameaddcolumncoltype
SQL>Altertabletabnameadd
升级会员 