oracle 学习记录

一、sql语句：

1、desc：描述……

2、||：追加字母

3、排序，默认是升序，降序是des

distinct:去掉不同的，如：select distinct name from form;

alter user：更改用户

select name,address,salary*12 annual_sa from form where name not like '_a%' and salary > 5000 order by salary desc;

4、sql函数：

a：lower（name）将大写转换成小写，如： select lower(name) from form;

         b：uper（）转换成大写

         c：substr（）单行函数，截取字符串，如 select substr(name,2,3) from form;

         d：chr(),ascii(),编码转换，如：select chr(65) from form;  select ascii('A') from form

         e：round(),四舍五入，如：select round(2323.32,1) from form;

         f：tochar(),字符格式，如：select tochar(salary,'$999,999.9999') from form;用L表示人民币，select tochar(salary,'L000,000.0000') from form;

                   select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from form;

         g: todate（）,日期格式转换

         h：tonumber（）,将字符串转换成相关数字，如：select * from form where salary > tonumber('$1,233.00','$999,999.99');

         i:nvl()……

  以下是组函数：（重要）

         a:sum（）：求和

         b:count（）：求非空的数量

         c:avg():求平均值

         d:max（）,min（）最大，最小

5、group：如：select deptno,avg(sal) from emp group by deptno;

6、出现在select列表里的字段（列表例有组函数），没有出现在组函数里必须出现在group by里面（小难点），如：select ename, max(sal) from emp group by deptno;（可能产生不唯一的值不行）

   select deptno, max(sal) from emp group by deptno;（可以）

7、where语句是对单条语句进行过滤的，不能过滤分组之后的记录（优先于group by执行）

8、having，对分组进行限制，如： select avg(sal),deptno from emp group by deptno having avg(sal) > 2000;

9、select * from emp（顺序）

         where sal > 1000

         group by deptno

         having       

order by

10、select 语句必须非常熟练！！！select avg(sal) from emp where sal > 1200 group by deptno having avg(sal) >1500 order by avg(sal) desc;

11、子查询：select ename from emp where sal =(select max(sal) from emp);

12、如果出现“不是单分组函数”或“此处不允许使用单分组函数”的错误时，尝试子查询！！

13、ed：弹出文本编辑器

14、select ename,sal,deptno from emp where sal in (select max(sal) from emp group by deptno);（错）只要是三个数据之一就会选出来

15、99年语法：select ename,dname from emp cross join dept;

         老语法：select ename,dname from emp,dept where empno = deptno  新语法：select ename,dname from emp join dept on (empno = 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%'（新语法where只用于数据过滤）

16、左（右）外连接：把左（右）边表的多余数据也显示出来，如： select e1.ename,e2.ename from emp e1 left（right）【outer】 join emp e2 on (e1.mgr = e2.empno);

         还有全外连接，如：select ename,dname from emp e full join dept d on (e.deptno = d.deptno);

17、“/”是执行上条语句

 

18、创建视图：create view v$_dept_avg_sal info as + 语句，如：create view v as select avg(sal) avg_sal,deptno from emp group by deptno;

删除视图：drop view viewname

19、创建新表：create table pu (name varchar(50),address varchar(50),age number(20));

         删除表：drop table pu;

19、命令：conn pu/pu [as sysdba] 切换用户

20、命令：grant create table,create view to scott 附权限

21、插入行：insert into form values (‘dingyuanpu’,’shandong’,21); 简单！

         删除行：delete from pu where age = 25;

         更新：update pu set name = 'yuan' where name = 'ding';

22、创建EM

emca -config dbcontrol db -repos create

或者是建过，但后台服务丢了：

emca -config dbcontrol db -repos recreate

23、查询前几条记录：select * from emp where rownum < num;（不同数据库产品不同）

24、以any为例说,该怎么理解‘任何值’呢？比如
select employee_id,last_name from employees where salary > any (select high_salary from salary_grades);

select employee_id,last_name from employees where salary > all (select low_salary from salary_grades);

s有区别!

25、基本的Sql编写注意事项

* 尽量少用IN操作符，基本上所有的IN操作符都可以用EXISTS代替。

* 不用NOT IN操作符，可以用NOT EXISTS或者外连接+替代。

* Oracle在执行IN子查询时，首先执行子查询，将查询结果放入临时表再执行主查询。而EXIST则是首先检查主查询，然后运行子查询直到找到第一个匹配项。NOT EXISTS比NOT IN效率稍高。但具体在选择IN或EXIST操作时，要根据主子表数据量大小来具体考虑。

* 不用“<>”或者“!=”操作符。对不等于操作符的处理会造成全表扫描，可以用“<” or “>”代替。

* Where子句中出现IS NULL或者IS NOT NULL时，Oracle会停止使用索引而执行全表扫描。可以考虑在设计表时，对索引列设置为NOT NULL。这样就可以用其他操作来取代判断NULL的操作。

* 当通配符“%”或者“_”作为查询字符串的第一个字符时，索引不会被使用。

* 对于有连接的列“||”，最后一个连接列索引会无效。尽量避免连接，可以分开连接或者使用不作用在列上的函数替代。

* 如果索引不是基于函数的，那么当在Where子句中对索引列使用函数时，索引不再起作用。

* Where子句中避免在索引列上使用计算，否则将导致索引失效而进行全表扫描。

* 对数据类型不同的列进行比较时，会使索引失效。

* 用“>=”替代“>”。

* UNION操作符会对结果进行筛选，消除重复，数据量大的情况下可能会引起磁盘排序。如果不需要删除重复记录，应该使用UNION ALL。

* Oracle从下到上处理Where子句中多个查询条件，所以表连接语句应写在其他Where条件前，可以过滤掉最大数量记录的条件必须写在Where子句的末尾。

* Oracle从右到左处理From子句中的表名，所以在From子句中包含多个表的情况下，将记录最少的表放在最后。（只在采用RBO优化时有效，下文详述）

* Order By语句中的非索引列会降低性能，可以通过添加索引的方式处理。严格控制在Order By语句中使用表达式。

* 不同区域出现的相同的Sql语句，要保证查询字符完全相同，以利用SGA共享池，防止相同的Sql语句被多次分析。

* 多利用内部函数提高Sql效率。

* 当在Sql语句中连接多个表时，使用表的别名，并将之作为每列的前缀。这样可以减少解析时间。

 

26、EXISTS 和NOT EXISTS与IN和NOT IN 的比较

 

         EXISTS：只检查行的存在性

         IN    ：则要检查值的实际存在性。

 

         EXISTS 性能高于IN，应优先使用。

 

         当用于比较的列值为NULL时：

         NOT EXISTS 返回True

         NOT IN 返回false

         如：

select ename from emp where exists (select comm from emp);

select ename from emp where comm in (select comm from emp);

 

27、包含子查询的UPDATE

         update employees set salary = (select avg(high_salary) from salary_grades) where employee_id = 4;

 

28、包含子查询的DELETE

         delete from employees where salary > (select avg(high_salary) from salary_grades);

 

例题：

查询每个部门的哪些人薪水最高

         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 ename,sal,e.deptno from emp e 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 e1.ename, e2.ename from emp e1,emp e2 where e1.mgr = e2.empno;（为一张表起不同的别名，当成两张表来用）

         或select e1.ename, e2.ename from emp e1 join emp e2 on (e1.mgr = e2.empno);

         或select ename from emp where empno in (select distinct mgr from emp);

 

求部门平均薪水的等级

         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 deptno,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 sal from emp where sal not in (select distinct e1.sal from emp e1 join emp e2 on (e1.sal < e2.sal));

 

求薪水最高的部门的部门编号

select deptno,avg_sal from (select avg(sal) avg_sal,deptno from emp group by deptno) where avg_sal = (select max(avg_sal) max_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);

嵌套分组函数要有group by

 

求薪水最高的部门的部门名称

select dname from dept join (select deptno,avg_sal from (select avg(sal) avg_sal,deptno from emp group by deptno) where avg_sal = (select max(avg_sal) max_sal from (select avg(sal) avg_sal,deptno from emp group by deptno))) t on (dept.deptno = t.deptno); 或

select dname from dept where dept.deptno = (select deptno from (select avg(sal) avg_sal,deptno from emp group by deptno) where avg_sal = (select max(avg_sal) max_sal from (select avg(sal) avg_sal,deptno from emp group by deptno)));

 

求平均薪水的等级最低的部门名称（难？）

select dname from dept where deptno = (select deptno from (select avg(sal) avg_sal,deptno from emp group by deptno) t where t.avg_sal = (select min(avg_sal) from (select avg(sal) avg_sal,deptno from emp group by deptno)));

或：

create view v as select avg(sal) avg_sal,deptno from emp group by deptno;（先创建视图）

select dname from dept where deptno = (select deptno from v t where t.avg_sal = (select min(avg_sal) from v));

 

求部门经理中平均薪水最低的部门名称

create view vi as select ename,sal,deptno from emp where empno in (select mgr from emp);

create view vie as select avg(sal) avg_sal,deptno from vi group by deptno;

select dname,deptno from dept where deptno = (select deptno from vie where avg_sal = (select min(avg_sal) from vie)) ;

 

求比普通雇员中最高薪水还要高的经理名称

select ename from (select * from vi) t where t.sal > (select max(sal) max_sal from emp where ename not in (select ename from vi));

 

求薪水最高的前五名雇员

create view v$ as select  ename,sal from emp order by sal desc;

select ename,sal from v$ where rownum < 6;

 

求薪水最高的第六到第十名雇员（具体见rownum专题）

select * from (select rownum r,v$.* from v$ where rownum <=10) where r >= 6;（高效）

select * from v$ where rownum < 11 minus select * from v$ where rownum < 6;（咋无序？）

select rownum r,v$.* from v$ where rownum < 11 minus select rownum r,v$.* from v$ where rownum < 6;（而这个有序）

select * from (select rownum r,v$.* from v$) where r > 5 and r < 11;（效率不如第一个）

 

比较效率（面试题）

select * from emp where deptno = 10 and ename like ‘%A%’;

select * from emp where ename like ‘%A%’ and deptno = 10;