Oracle 10g 学习笔记

锁定设置口令:tiger
全局数据库名:orcl
系统标符:orcl
用户名/口令:scott/tiger
按F10可用IE执行sqlplus

错误:ora-28000 the account is locked
解决:新装完Oracle10g后，用scott/tiger测试，会出现以下错误提示：
        oracle10g the account is locked
        oracle10g the password has expired
原因：默认Oracle10g的scott不能登陆。
解决：
（1）conn sys/sys as sysdba; //以DBA的身份登录
（2）alter user scott account unlock;// 然后解锁
（3）conn scott/tiger //弹出一个修改密码的对话框，修改一下密码就可以了

在运行里面输入cmd在DOS模式下输入sqlplus，以system用户名登录，密码是刚装oracle时自己填写的密码orcl，登录进去以后。

SQL> conn sys/sys as sysdba;       （分号是必须的但是我是以system登录的所在这不应该写conn sys/sys as sysdba应该写conn system/orcl as sysdba;）
         Connected.
SQL> alter user scott account unlock;
         User altered.
SQL> commit;
         Commit complete.
SQL> conn scott/tiger//请输入新密码，并确认后OK
Password changed
Connected.

授权:
conn sys/scott as sysdba;
grant create table,create view to scott;

语句学习:
desc dept/salgrade/emp

select * from dept;
select sal*12 from emp;
select 2*3 from dept;
select ename||sal from emp;(串连接)
select sal sal_anme from emp;(取别名)
select sal "sal dd" from emp;()
select ename||'afasdf' from emp;(连接)
select ename||'sdfs''sdf' from emp;(用双引号显示单引号)

select deptno from emp;
select distinct deptno from emp;(distinct去掉重复的)
select distinct deptno,job from emp;

select * from emp where deptno=10;
select * from emp where ename='CLARK';
select sal,deptno from emp where deptno!=10;(!=与<>等同)
select ename,sal from emp where ename>'CBA';
select ename,sal from emp where sal between 800 and 1500;(其中包括800和1500)
select ename,sal form emp where sal >=800 and <= 1500;

select ename,sal,comm from emp where comm is null;(正确的写法,not null)
select ename,sal,comm from emp where comm=null;(错误的写法)

select ename,sal,comm from emp where sal in (800,1500,2000)(取得等于800,或1500,或2000的值)
select ename,sal,comm from emp where ename in ('SMITH','KING','ABC')(取得满足条件的值)

select ename,sal from emp where deptno=10 and sal>1000; (and可以改成or)
select ename,sal from emp where sal not in (800,1500);(只要不是800,1500的)

%代表0到多个,_代表一个字符,\代表转义字符
select ename from emp where ename like '%ALL%';
select ename from emp where ename like '_A%';(满足第二个字符为A的)
select ename from emp where ename like '%\%%';(满足有%的)
如果不想用\做转义字符.可以这么写:
select ename from emp where ename like '%$%%' escape '$';(即以$为转义字符)

order by语句(排序)
select * from dept;
select * from dept order by deptno desc;
select empno,ename from emp where deptno<> 10 order by empno asc;(先筛选完再排序)
select ename,sal,deptno from emp order by deptno asc,ename desc;(先按deptno升序后,再按ename降序)

函数的使用:
单行函数(给多条就产生多条输出):
select lower(ename) from emp; 小写
select ename from emp where lower(ename) like '_a%';
select ename from emp where ename like '_a%' or ename like '_A%';

select substr(ename,1,3) from emp;(从第一个字符开始写,写三个字符)
select substr(ename,2,3) from emp;(从第二个字符开始写写三个字符)
select chr(65) from dual;(把65转成ASCII字符)
select ascii('A') from dual;
select round(23.652) from dual;(四舍五入)
select round(23.652,2) from dual;(四舍五入到小数点后两位)
select round(23.5443,-1) from dual;(结果20)
上面的函数有个概念即可,不记得查一下就好.下面的函数很重要,无论如何记住它们.
1.to_char转换日期,数字
select to_char(sal,'$99,999.9999') from emp;(让工资以$99,999.9999的形式显示,其实9就是一位数字)(美元)
select to_char(sal,'L99,999.9999') from emp;(人民币)
select to_char(sal,'L00000.000') from emp;(统一格式,不足的以0补齐)
select to_date(hiredate,'YYYY-MM-DD HH:MI:SS') from emp;
select to_date(sysdate,'YYYY-MM-DD HH24:MI:SS') from emp;(把当前日期转成24小时制的.默认是12进制的)
select ename,hiredate from emp where hiredate > to_date('1981-2-20 12:34:56','YYYY-MM-DD HH24:MI:SS') ;
select sal from emp where sal> to_number('$1,250.00','$9,999.99');
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from emp;
select ename,sal*12+comm from emp;(这样只要表达式中有一个空值,结果就为空)
select ename,sal*12+nvl(comm,0) from emp;

多行(组)函数(给出一条或者多条,只会有一行输出)
select max(sal) from emp;
select min(sal) from emp;
select avg(sal) from emp;
select to_char(avg(sal),'999999.99') from emp;
select round(avg(sal),2) from emp;
select sum(sal) from emp;统计总额
select count(*) from emp;统计记录
select count(*) from emp where deptno=10;求出部门号为10的人数为多少.
select count(comm) from emp;(只要不为空值就算一个)
select count(distinct deptno) from emp;对非重复的条目进行统计

select deptno, avg(sal) from emp group by deptno;先按部门分组,再把各组数据平均值
select deptno,job, max(sal) from emp group by deptno,job;(按照两个字段来分组,同时满足)

select ename from emp where sal=(select max(sal) from emp);取得工资最高的所有人

select deptno,max(sal) from emp group by deptno;(select中的字段,不在group内就要在组函数内)
select avg(sal),deptno from emp group by deptno;每个部门的平均薪水

where语句只对当前的记录进行过滤,不能在group by过后过滤,此时就要用到having
select avg(sal) ,deptno from emp group by deptno having avg(sal) >2000;

记住一个重要的顺序,不能错:
select * from emp
where sal > 1000   条件限制
group by deptno    分组
having             限制
order by           排序

例子:
select avg(sal)
from emp
where sal>1200  
group by deptno
having avg(sal) >1500
order by avg(sal) desc;

子查询(在select语句里套select语句):
select ename,sal from emp where sal=(select max(sal) from emp);
select ename,sal from emp where sal>(select avg(sal) from emp);
select ename,sal ,deptno from emp where sal=(select max(sal) from emp group by deptno);(错误句,因为子查询的结果可能不只一个)

select ename,sal from emp
join (select max(sal) max_sal,deptno from emp group by deptno) t 把select max(sal) max_sal,deptno from emp group by deptno查询的结果当成一张表t
on (emp.sal=t.max_sal and emp.deptno=t.deptno);                   进行表连接

自连接,连接相同的两表并取得对应的值
select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno;

交叉连接(cross join)
select ename,dname from emp cross join dept;
等值连接:
select ename,dname from emp,dept where emp.deptno=dept.deptno;    以前的写法
select ename,dname from emp join dept on (emp.deptno=dept.deptno);现在的写法
select ename,dname from emp join dept using (deptno);另一种写法,但不推荐用
不等值连接:
select ename,grade from emp e join salgrade s on (e.sal between s.losal and s.hisal);

select ename,dname,grade from
emp e join dept d on (e.deptno=d.deptno)
join salgrade s on (e.sal between s.losal and s.hisal)
where ename not like '_A%';

select e1.ename,e2.ename form emp e1 join emp e2 on (e1.mgr=e2.empno);(自连接的新语法)

显示空行的方式(左,右外连接)
select e1.ename,e2.ename from emp e1 left join emp e2 on (e1.mgr=e2.empno);(左外连接,即便满足条件的右边没有数据也会显示出来)
select ename,dname from emp e right outer join dept d on (e.deptno=d.deptno);(右外连接)
select ename,dname form emp e full join dept d on (e.deptno=d.deptno);(全外连接)

--求部门平均薪水等级:
select deptno,avg_sal,grade from
(select deptno,avg(sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and s.hisal);
--求部门中哪些人的薪水最高
select ename,sal from emp
join (select max(sal) max_sal,deptno from emp group by deptno) t
on (emp.sal=t.max_sal and emp.deptno=t.deptno);
--求部门平均的薪水等级(平均所有的等级)
select avg(grade) from
(select deptno,ename,grade from emp join salgrade s on (emp.sal between s.losal and s.hisal)) t
group by deptno;
--雇员中有哪些人是经理人
select ename from emp where empno in (select distinct mgr from emp);
--不准用组函数,求薪水的最高值(面试题)
思路:左右两张同样的表,找左边的薪水比右边的薪水高,再找左右边对应不上的薪水值即为最高值.
第一步:
select distinct e1.sal from emp e1 join emp e2 on (e1.sal < e2.sal);
第二步:
select distinct sal from emp where sal not in
(select distinct e1.sal from emp e1 join emp e2 on (e1.sal < e2.sal));
--求平均薪水最高的部门的部门编号
第一步(每个部门的平均薪水):
select avg(sal),deptno from emp group by deptno;
第二步,对上面求出的求最大值
select max(avg_sal) from
(select avg(sal) avg_sal,deptno from emp group by deptno);
第三步:
select deptno,avg_sal from
(select avg(sal) avg_sal,deptno from emp group by deptno)
where avg_sal=
(select max(avg_sal) from
(select avg(sal) avg_sal,deptno from emp group by deptno));
修改:
select deptno,avg_sal from
(select avg(sal) avg_sal,deptno from emp group by deptno)
where avg_sal=
(select max(avg(sal)) from emp group by deptno));
--求平均薪水最高的部门的部门名称
select dname from dept where deptno=
(
select deptno,avg_sal from
    (select avg(sal) avg_sal,deptno from emp group by deptno)
where avg_sal=
    (select max(avg_sal) from
(select avg(sal) avg_sal,deptno from emp group by deptno))
);
--求平均薪水的等级最低的部门的部门名称
1.select deptno,avg(sal) avg_sal from emp group by deptno
2.select deptno,grade,avg_sal from
(select deptno,avg(sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and s.hisal)
3.select min(grade) from
(select deptno,grade,avg_sal from
(select deptno,avg(sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and s.hisal));
4.
select danme,t1.deptno,grade,avg_sal from
(select deptno,grade,avg_sal from
(select deptno,avg(sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and s.hisal)) t1
join dept on (t1.deptno=dept.deptno)
where t1.grade=
(select min(grade) from
(select deptno,grade,avg_sal from
(select deptno,avg(sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and s.hisal)));
改进(利用视图View,简单理解视图就是一张表或是一个子查询):
create view v$_dept_avg_sal_info as
select deptno,grade,avg_sal from
(select deptno,avg(sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and s.hisal)

select danme,t1.deptno,grade,avg_sal from
( v$_dept_avg_sal_info t1
join dept on (t1.deptno=dept.deptno)
where t1.grade=
(select min(grade) from
v$_dept_avg_sal_info);