orcale 初学 到 精通 各种 demo 包含 练习 中使用的表 数据脚本

第一课：客户端 1.Dos命令行下：sqlplus 用户名：scott 密码：tiger,sqlplus scott/tiger 2.开始－运行－sqlplus 图形版的SQLPLUS 3.http://localhost:5560/isqlplus Toad:管理 Plsql Developer 第二课：更改用户 1.sqlplus sys/bjsxt as sysdba 2.alter user scott account unlock;(解锁) 第三课：table_structures 1.desc 表名 2.select * from 表名 第四课：select语句 1.select ename, sal*12 annual_sal from emp; select ename sal*12 "annual sal" from emp;双引号可以保持原大小写，并且中间可以加空 格，不加全变为大写 2.desc dual，select 2*3 from dual，dual是系统自带的一张空表，计算数据时可以使用该表 3.任何含有空值的算术表达式的计算结果是空值 4.select ename||sal from emp; 5.select ename || 'abcdefg' from emp; 6.select ename || 'abcd''efg' from emp;当字符串中含有单引号时，可用两个单引号代表一 个单引号 7.set linesize 200;--用于设定每行显示的宽度 8.set pagesize 30;--设置显示的页数 第五课：distinct 1.select distinct deptno from emp; 2.select distinct deptno,job from emp; 第六课：where 1.select * from emp where empno = 10; 2.select * from emp where empno <> 10; 3.select * from emp where ename = 'hebe'; 4.select * from emp where sal (not) between 800 and 1500; 5.select * from emp where comm is (not) null; 6.select * from emp where ename (not) in ('smith','philip','jay'); 7.select * from emp where ename like '_A%'; 8.select * from emp where ename like '_\%a%';系统默认转义符是\,可以自己指定转义符 select * from emp where ename like '_$%a%' escape '$'; 第七课：order by 1.select * from dept; 2.select * from dept order by dept desc;(默认为asc) 3.select ename, sal, deptno from emp where sal > 2000 order by deptno asc,ename desc; 第八课：sql_function 1.select ename,sal*12 annual_sal from emp where ename not like '_A%' and sal > 1500 order by sal desc; 2.select lower(ename) from emp; 3.select ename from emp where lower(ename) like '_a%'; 4.select substr(ename,2,3) from emp;从第二字符截，一共截三个字符。 5.select chr(65) from dual;结果为A 6.select ascii('a') from dual;结果为65 7.select round(35.572) from dual;结果为36 8.select round(35.572,2) from dual;结果为35.57 9.select round(35.572,-1) from dual;结果为40 10.select to_char(sal,'$99,999.9999') from emp;to_char函数主要用于对日期和数字进行格 式化 11.select to_char(sal,'L99,999.9999') from emp;人民币符号，L代表本地符号。 12.select birthdate from emp; 显示为：BIRTHDATE --------------------- 22-3月-87 改为：select to_char(birthdate,'YY YY-MM-DD HH24:MI:SS') from emp; 13.to_data函数 select ename,birthdate from emp where birthdate > to_date('1987-3-22 11:22:33','YYYY-MM-DD HH24:MI:SS');不能直接写birthdate>'1987-2-22 11:22:33'会出现格 式不匹配，因为表中格式为DD-MM月－YY, 14.select sal from emp where sal > to_number('$12,444.99','$99,999.99'); 15.select ename, sal*12+nvl(comm,0) from 这样防止comm为空时，sal*12相加也为空的情况 第九课：Group function 组函数 1.select max(sal) from emp; 2.select min(sal) from emp; 3.select to_char(avg(sal), '$999,999,999.99') from emp; 4.select round(sum(sal),2) from emp; 5.select count(*) from emp where sal > 1500; 6.select count(comm) from emp; 7.select count(distinct deptno) from emp; 第十课：Group by语句 1.select avg(sal) from emp group by deptno; 2.select deptno,avg(sal) from emp group by deptno; 3.select deptno,job,max(sal) from emp group by deptno,job; 4.求薪水值最高的人的名称select ename,max(sal) from emp;出错，因为max只能有一个值，但 是等于max的值可能有好几个，不能匹配。 可以写成select ename from emp where sal=(select max(sal) from emp); Group by 语句应注意，出现在select中的字段，如果没有出现在组函数中，必须出现在Group by语句中。 第十一课：Having对分组结果筛选 1.where是对单条记录进行筛选,Having是对分组结果进行筛选 select avg(sal),deptno from emp group by deptno having avg(sal) > 2000; 2.查询工资大于2000的雇员,按照部门编号进行分组,分组后平均薪水大于1500,按工资倒序排列 select deptno,avg(sal) from emp where sal > 2000 group by deptno having avg(sal) > 1500 order by avg(sal) desc; 第十二课:子查询 1.select 语句中嵌套select 语句,求哪些人工资在平均工资之上. select ename,sal from emp where sal > (select avg(sal) from emp); 2.查找每个部门挣钱最多的那个人的名字. select ename, deptno from emp where sal in (select max(sal) from emp group by deptno) 查询会多值.正确写法是: 应把select max(sal),deptno from emp group by deptno当成一个表,语句如下: 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); 第十三课:self_table_connection select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr = e2.empno; 第十四课:SQL1999_table_connection 1.select ename,dname,grade from emp e,dept d,sqlgrade s where e.deptno=d.deptno and e.sal between s.losal and s.hisal and job <> 'CLERK'; 这种写法没有把过滤条件和连接条件分开来,由于这个原因,sql1999标准推出来了. 2.select ename,dname from emp,dept;(旧标准) sele ct ename,dname from emp cross join dept;(1999标准) 3.select ename,dname from emp,dept where emp.deptno = dept.deptno;(旧) select ename,dname from emp join dept on (emp.deptno = dept.deptno) 4.select ename,grade from emp e join salgrade s on(s.sal between s.losal and ` s.hisal); 5.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%'; 6.select e1.ename,e2.ename from emp e1 join emp e2 on (e1.mgr = e2.deptno); 7.select e1.ename,e2.ename from emp e1 left (outer) join emp e2 on (e1.mgr = e2.empno); 8.select ename,dname from emp e right outer join dept d on (e.deptno = d.deptno); 9.select ename,dname from emp e full join dept d on (e.deptno = d.deptno); 第十六至二十三课:求部门平均薪水等级 1.A.求部门平均薪水的等级 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) 2.求部门平均的薪水等级 select deptno,avg(grade) from (select deptno,ename,grade from emp e join salgrade s on (emp.sal between s.losal and s.hisal)) t group by deptno; 3.哪些人是经理 select ename2 from (select e1.ename ,e1.mgr mgr1,e2.ename ename2 from emp e1 join emp e2 on (e1.mgr = e2.empno)) t join emp on (t.mgr1 = emp.empno) select ename from emp where empno in (select distinct mgr from emp) 4.不准用组函数,求薪水的最高值 select distinct sal from emp where sal not in( select distinct e1.sal from emp e1 join emp e2 on (e1.sal < e2.sal) ) 5.求平均薪水最高的部门编号 select deptno,avg_sal from ( select deptno,avg(sal) avg_sal from dept group by deptno ) where avg_sal = ( select max(avg_sal) from ( select deptno,avg(sal) avg_sal from dept group by deptno ) ) select deptno,avg_sal from (select deptno,avg(sal) avg_sal from dept group by deptno) where avg_sal = (select max(avg(sal)) from dept group by deptno) 6.求平均薪水最高的部门名称 select dname from dept where deptno = ( select deptno from (select deptno,avg(sal) avg_sal from dept group by deptno) where avg_sal = ( select max(avg_sal) from (select deptno,avg(sal) avg_sal from dept group by deptno) ) ) 7.求平均薪水等级最低的部门的部门名称 错误写法: select min(avg_grade),deptno from ( select deptno,avg(grade) avg_grade ( select ename,deptno,grade from emp e join salgrade s on (e.sal between s.losal and s.hisal) ) group by deptno ) group by deptno 正确写法: select dname,t1.deptno,grade,avg_s al from ( 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) ) t1 join dept on (t1.deptno = dept.deptno) where t1.grade = ( select min(grade) from ( select deptno,avg_sal,grade from (select deptno,avg(sal)