Oracle连接笔记.docxVIP

环境变量：D:\oracle\product\10.1.0\Db_1\bin;D:\oracle\product\10.1.0\Db_1\jre\1.4.2\bin\client;D:\oracle\product\10.1.0\Db_1\jre\1.4.2\bin;C:\Program Files\Java\jdk1.6.0_10\bin;E:\oracle\product\10.1.0\db_1\bin;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;C:\ProgramFiles\MicrosoftSQL Server\80\Tools\Binn\;C:\ProgramFiles\MicrosoftSQL Server\90\Tools\binn\;C:\ProgramFiles\MicrosoftSQL Server\90\DTS\Binn\;C:\ProgramFiles\MicrosoftSQL Server\90\Tools\Binn\VSShell\Common7\IDE\;C:\ProgramFiles\MicrosoftVisual Studio8\Common7\IDE\PrivateAssemblies\;C:\ProgramFiles\MicrosoftSQL Server\100\Tools\Binn\;C:\ProgramFiles\MicrosoftSQL Server\100\DTS\Binn\;C:\ProgramFiles\MicrosoftSQL Server\100\Tools\Binn\VSShell\Common7\IDE\;C:\ProgramFiles\MicrosoftVisual Studio 9.0\Common7\IDE\PrivateAssemblies\;C:\WINDOWS\system32\WindowsPowerShell\v1.0sqlplus /nololg //查看账户连接状态conn scott/zhang @niit7-2/zhang解锁步骤：conn sys/system as sysdbaalter user scott account unlock;打开文件：start d:/**.sql@ d:/**.sql编辑文件： edit d:/....写文件： spool d:/.....关闭 spool off设置显示行的宽度： set linesize 90显示行宽度： show linesize创建用户： create user 用户名 identified by 密码；授权： grant connect to 用户名； （授予连接权限）grant create user to scott;要在连接 sys下授权创建用户！！数据库操作：select * from emp where ename = SMITH;select hiredate,job,ename from emp;select distinct job from emp;去掉重复项DESC emp;查看表结构查询语句：select sal*3 as 年薪 ,ename,job FROM emp ; as ‘年薪’ select ename || is a || job from emp;select * from emp where hiredate 01-2月-81 and hiredate 01-12月-81;select * from emp where ename like S%;//查询以s开头的内容% 表示一个或多个字符 _ 表示一个字符select * from emp where ename in (SMITH,SCOTT,FORD);select * from emp where ename is not null;select * from emp order by sal;select * from emp order by deptno,sal DESC;select * from emp order by deptno DESC,sal DESC;select ename,(sal+nvl(comm,0))*12 年薪 from emp order by 年薪 DESC; nvl(comm.,0) 表示如果comm 为null 则补0select ename, (sal+nvl(comm,0))*12 as 年薪 from emp where (sal+nvl(comm,0))*12 between 20000 and 40000 order by 年薪 DESC;s