Oracle连接笔记

《Oracle连接笔记》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、环境变量：D:oracleproduct10.1.0Db_1bin;D:oracleproduct10.1.0Db_1jre1.4.2binclient;D:oracleproduct10.1.0Db_1jre1.4.2bin;C:ProgramFilesJavajdk1.6.0_10bin;E:oracleproduct10.1.0db_1bin;%SystemRoot%system32;%SystemRoot%;%SystemRoot%System32Wbem;C:ProgramFilesMicrosoftSQL

2、Server80ToolsBinn;C:ProgramFilesMicrosoftSQLServer90Toolsbinn;C:ProgramFilesMicrosoftSQLServer90DTSBinn;C:ProgramFilesMicrosoftSQLServer90ToolsBinnVSShellCommon7IDE;C:ProgramFilesMicrosoftVisualStudio8Common7IDEPrivateAssemblies;C:ProgramFilesMicrosoftSQLServer

3、100ToolsBinn;C:ProgramFilesMicrosoftSQLServer100DTSBinn;C:ProgramFilesMicrosoftSQLServer100ToolsBinnVSShellCommon7IDE;C:ProgramFilesMicrosoftVisualStudio9.0Common7IDEPrivateAssemblies;C:WINDOWSsystem32WindowsPowerShellv1.0sqlplus/nololg//查看账户连接状态connscott/zhang@niit7

4、-2/zhang解锁步骤：connsys/systemassysdbaalteruserscottaccountunlock;打开文件：startd:/**.sql@d:/**.sql编辑文件：editd:/....写文件：spoold:/.....关闭spooloff设置显示行的宽度：setlinesize90显示行宽度：showlinesize创建用户：createuser用户名identifiedby密码；授权：grantconnectto用户名；（授予连接权限）grantcreateusertoscott;要在连接sys下授权创建用户！！数据库操作：select*fromem

5、pwhereename='SMITH';selecthiredate,job,enamefromemp;selectdistinctjobfromemp;去掉重复项DESCemp;查看表结构查询语句：selectsal*3as"年薪",ename,jobFROMemp;as‘年薪’selectename

6、

7、'isa'

8、

9、jobfromemp;select*fromempwherehiredate>'01-2月-81'andhiredate<'01-12月-81';select*fromempwhereenamelike'S%';//查询以s开头的内容%表示一个或多个字符_表示一个字符

10、select*fromempwhereenamein('SMITH','SCOTT','FORD');select*fromempwhereenameisnotnull;select*fromemporderbysal;select*fromemporderbydeptno,salDESC;select*fromemporderbydeptnoDESC,salDESC;selectename,(sal+nvl(comm,0))*12"年薪"fromemporderby"年薪"DESC;nvl(comm.,0)表示如果comm为null则补0selectename,(sal+nvl(c

11、omm,0))*12as"年薪"fromempwhere(sal+nvl(comm,0))*12between20000and40000orderby"年薪"DESC;selectename,lower(ename)fromemp;//小写selectupper('azsdda')fromdual;//大写selectinitcap('my')fromdual;//首字母大写selectconcat('Hel','lo')fromdual;//合并字符串s