Oracle连接笔记.docx

环境变量：D:oracleproduct10.1.0Db_1bin;D:oracleproduct10.1.0Db_1jre1.4.2binclient;D:oracleproduct10.1.0Db_1jre1.4.2bin;C:Program FilesJavajdk1.6.0_10bin;E:oracleproduct10.1.0db_1bin;%SystemRoot%system32;%SystemRoot%;%SystemRoot%System32Wbem;C:ProgramFilesMicrosoftSQL Server80ToolsBinn;C:ProgramFilesMicrosoftSQL Server90Toolsbinn;C:ProgramFilesMicrosoftSQL Server90DTSBinn;C:ProgramFilesMicrosoftSQL Server90ToolsBinnVSShellCommon7IDE;C:ProgramFilesMicrosoftVisual Studio8Common7IDEPrivateAssemblies;C:ProgramFilesMicrosoftSQL Server100ToolsBinn;C:ProgramFilesMicrosoftSQL Server100DTSBinn;C:ProgramFilesMicrosoftSQL Server100ToolsBinnVSShellCommon7IDE;C:ProgramFilesMicrosoftVisual Studio 9.0Common7IDEPrivateAssemblies;C:WINDOWSsystem32WindowsPowerShellv1.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;select ename ,lower(ename) from emp; /小写select upper(azsdda) from dual; /大写select initcap(my) from dual; /首字母大写select concat(Hel,lo) from dual; /合并字符串select substr(abcdefg,3,4) from dual; /读出第3位后的4位select length(addcsasdasdasda) from dual; /长度select ename,instr(ename,A) from emp; /A在ename 里的第几个字符select sal,lpad(sal,10,*) from emp; /设置sal长度为10，不够向左边补*select sal,rpad(sal,10,*) from emp; /向右补*select trim(H from Hello) from dual; /去除H字符select round(15.163,-1) from dual; /四舍五入select trunc(16.223,-1) from dual; /去掉后面的select mod(11,4) from dual; /取余alter session set nls_date_format = yyyy-mm-dd hh24:mi:ss; /设置时间格式select sysdate from dual; /显示系统时间select sysdate-hiredate from emp;select sysdate-1 from dual; /sysdate-1 表示减去一天select add_months(sysdate,5) from dual;select next_day(sysdate,星期三) from dual;select last_day(sysdate) from dual;select round(sysdate,yyyy) from dual; /按年四舍五入select round(sysdate) from dual; /按日四舍五入select trunc(sysdate,D) from dual; /截取本周第一天select trunc(sysdate,MM) from d