Oracle连接笔记

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\\MicrosoftSQLServer\\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.0

sqlplus /nololg //查看账户连接状态

connscott/zhang @niit7-2/zhang

解锁步骤：

conn sys/system as sysdba

alter user scott account unlock;

打开文件： start d:/**.sql @ d:/**.sql

编辑文件：edit d:/....

写文件：spool d:/..... 关闭 spool off 设置显示行的宽度：setlinesize 90 显示行宽度：showlinesize

创建用户： create user 用户名 identified by 密码； 授权： grant connect to 用户名；（授予连接权限） grant create user to scott;

要在连接 sys下授权创建用户！！

数据库操作：

select * from emp where ename = 'SMITH'; selecthiredate,job,ename from emp;

select distinct job from emp; 去掉重复项

DESC emp; 查看表结构

查询语句：

select sal*3 as \"年薪\" ,ename,job FROM emp ;

as ‘年薪’

selectename || ' 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 deptnoDESC,sal DESC;

select ename,(sal+nvl(comm,0))*12 \"年薪\" from emp order by \"年薪\" DESC; nvl(comm.,0) 表示如果comm为null 则补0

select 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; //显示系统时间 selectsysdate-hiredate from emp;

select sysdate-1 from dual; //sysdate-1 表示减去一天

selectadd_months(sysdate,5) from dual;

select next_day(sysdate,'星期三') from dual;

selectlast_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 dual; //截取本月第一天 select trunc(sysdate,'DD') from dual; //截取本日 0：00 select trunc(sysdate,'yyyy') from dual; //截取本年第一天

select to_char(sysdate,'\"今天是\"yyyy day') from dual;

//设置时间格式