Oracle10g学习笔记详解.pdf

1、解锁scott 用户： alter user scott account unlock; I、在其他用户切换到sysdba 用户 conn sys/root as sysdba II、授权 grant create table,create view ,create session to scott 2、以DBA 身份登陆数据库 sqlplus sys/root as sysdba 3、查看标的结构： desc tablename 4、默认转义字符:\ select * from emp where ename like %\%%//查出ename 中含有%的信息 修改转义字符： select * from emp where ename like %$%%escape $; 5、、数据格式的转换： select to_char(sal,L00000.00000) from emp;//其中L 是代表本地货币符 select to_char(sysdate,YYYY-MM-DD HH:MI:SS) from dual; select to_char(sysdate,YYYY-MM-DD HH:MI:SS) from dual;//日期转化格式 6、字符截取： select substr(ename,1,3) from emp;//从第一位到到低三位 7、日期的转换： SQL select ename,hiredate from emp where hiredateto_date(1981-2-20 11:00:00,YYYY-MM_DD HH:MI:SS); select sal from emp where salto_number($2,33.44477,$99,99.99999); 8】、找出职员年薪与津贴的综合，津贴为空值的用0 代替 select ename,sal*12 + nvl(comm,0) from emp; 9】、精确度： select round(sal,2) from emp; 10】、having、group by 子句的使用： SQL select avg(sal) from emp group by deptno; AVG(SAL) 1566.66667 2175 2916.66667 SQL select avg(sal) from emp group by deptno having avg(sal)2000; AVG(SAL) 2175 2916.66667 11】、两张表的连接（旧语法--1992 ） SQL select ename,dname from emp,dept where emp.deptno=dept.deptno; ENAME DNAME CLARK ACCOUNTING KING ACCOUNTING MILLER ACCOUNTING JONES RESEARCH FORD RESEARCH ADAMS RESEARCH SMITH RESEARCH SCOTT RESEARCH WARD SALES TURNER SALES ALLEN SALES ENAME DNAME JAMES SALES BLAKE SALES MARTIN SALES 两张表的连接（新语法--1999 ） SQL select ename ,dname from emp join dept on(emp.deptno=dept.deptno); ENAME DNAME CLARK ACCOUNTING KING ACCOUNTING MILLER ACCOUNTING JONES RESEARCH FORD RESEARCH ADAMS RESEARCH SMITH RESEARCH SCOTT RESEARCH WARD SALES TURNER SALES ALLEN SALES ENAME DNAME JAMES SALES BLAKE SALES MARTIN SALES 已选择14 行。 12】求部门中哪些人的薪水最高： se