oracle易错总结

以scott的几张表举例

1. 查询在1981年雇佣的雇员信息
实际上就是查询1981年1月1日—1981年12月31日
的员工
易犯错是select * from emp where hiredate between 01-1月 -81 and 31-12月 -81;
注意：表示日期的时候用单引号把日期引起来，顺序是(日-月-年)
应当写成select * from emp where hiredate between '01-1月 -81' and '31-12月 -81';
------------------------------------------------------------------------------------------------------------------------------------
二：现在要求查询出雇员的编号，姓名，工作，但是显示的格式：
编号是：7369的雇员，姓名是：SMITH，工作是：CLERK。
要实现上述功能，可以使用Oracle中提供的字符串连接操作，使用"||"表示。如果要加入显示信息的话要用单引号('')引起来。如：
按部门分组，并显示部门的名称，以及每个部门的员工数
select d.dname,count(e.empno)部门人数 from dept d,emp e
where d.deptno=e.deptno group by d.dname;是在结构里显示部门人数
select d.dname,count(e.empno) || '部门人数' from dept d,emp e
where d.deptno=e.deptno group by d.dname;是在字段里面显示。

——————————————————————————————————————————————————————————————
查询select ‘编号’ from emp;就会显示14行编号，又知道字符串在oracle连接时通过||连接的以此类推

查询语句是：select '编号是：' ||  empno ||'的员工，姓名：'||ename || '职位是：' ||job from emp;
三select * from emp where comm is  null;与select * from emp where comm =0;是不同的结果前者可以查到10条记录，而后者只有一条记录，说明空与0是不同的.
select * from emp where comm=null是错误的写法
------------------------------------------------------------------------------------------------------------------------------------
四：Not 与not in 是不同的
select * from emp where not(sal>1500);是正确的
  NOT表示取反的意思
select * from emp where not in(sal>1500);是错误的。
语法格式：字段 not in (值1,值2,值3,值4,....)
------------------------------------------------------------------------------------------------------------------------------------
⑤：不等于符号：在SQL中如果要使用不等于符号，可以有两种形式："<>"、"!="。
------------------------------------------------------------------------------------------------------------------------------------
六：在列的类型中，主要有以下常用的数据类型：
Number(4)：表示是数字，长度为4。
Varchar2(10)：表示的是字符串，长度为10
Date：表示日期
Number(7,2)：表示的是数字，其中小数占两位，整数占5位，共七位。
Char(size):表示存储固定大小的字符
------------------------------------------------------------------------------------------------------------------------------------
七 desc 表名 –查询表的结构。不要单纯认为desc只是排序
八：Select sal*12+nvl(comm,0)*12 “年工资”,ename comm. From emp;
------------------------------------------------------------------------------------------------------------------------------------ 
九：：1 分组函数（count(sal),max(sal)，distinct(sal)等）只能出现在选择列表（select 后）,having ,order by字句中 group by 后面的字段一定也要在select 后面也有除非是函数的形式如count(sal)就可以不在group by 后面有这个字段
select distinct(deptno) from emp order by sal;这是错误的
select distinct(deptno),sal from emp order by sal;这是对的  distinct(deptno)只能放在这个地方。
2如果在select语句中包含group by having order by 那么他们的顺序一定是group by having order by，因为只有先分组在筛选having，在排序
3在选择列如果有列，表达式，和分组函数那么这些列和表达式必须出现在group by 字句中否则会出错
4.假如还有having的话分组字段必须与having后面的字段一致dept d.deptno 不能写成了 emp e.deptno;
5.常见的错误：select empno,ename,sum(sal) from emp  group by empno
将返回错误：
这是由于在ename表列中没有使用group by子句，因此必须加上分组函数。换句话说，必须使用max()、min()、sum()、count()或avg()函数。如果对于指定的表列找不到想使用的合适的分组函数，那么就将该表列移到group by子句中去。
6.having 与where的区别如：
 显示每个地区的总人口数和总面积.仅显示那些面积超过1000000的地区。 SELECT region, SUM(population), SUM(area)
FROM bbc
GROUP BY region
HAVING SUM(area)>1000000
在这里，我们不能用where来筛选超过1000000的地区，因为表中不存在这样一条记录。

总结：1.做数据库复杂的题目一般要将其分为几步方可，不要总想着一步到位。
      2.如果牵涉到两张表的话先将它查出来形成一张没有重复的表然后再分组，否则会出错如：
按部门分组，并显示部门的名称，以及每个部门的员工数
Select d.dname,count(e.ename) from emp e,dept d where e.deptno=d.deptno group by d.dname;
不能直接有重复
Select d.dname,count(e.ename) from emp e,dept d group by d.dname;

------------------------------------------------------------------------------------------------------------------------------------

十：对于多表查询的时候，要找到他们的共同点，即相同的字段，给他们起个别名，a1,字段=a2.字段 ，但是表的数量-1个条件才可以查到

查询两张表：Select ?,?,? from dept a1,emp a2 where a1.deptno=a2.deptno;
------------------------------------------------------------------------------------------------------------------------------------
十一：a:单行子查询 ：子查询只返回一行数据
b：多行子查询：返回多行的数据或记录 此时不能再用等于号了，要用in  all any some
------------------------------------------------------------------------------------------------------------------------------------
十二：clear scr;清屏
------------------------------------------------------------------------------------------------------------------------------------
十三：所有的函数应用都涉及了吧select upper('aaa') from dual;将aaa变成AAA
select lower('AAA') from dual;将AAA变成aaa
select empno,initcap(ename) from emp; 首字母大写
select * from emp where length(ename) = 5;查询长度等于5的员工
select replace(ename,'A','a') 姓名 from emp;
select concat('aa','bb') from dual; 字符串相连接
select substr('hello',3) from dual; 字符串截取
select length('hello') from dual; 字符串截取
select replace('hello','l','k') from dual; 字符串截取

select round(12.536,2) from dual; 数值函数
select trunc(788.536,-1) from dual; 数值函数
select mod(11,3) from dual; 数值函数
select sysdate from dual; 数值函数

select empno,ename,round((sysdate-hiredate)/7) from emp; 显示入职星期数
select empno,ename,round((sysdate-hiredate)/7) from emp;
select months_between(sysdate,'11-11月 -09') from dual;
select add_months('1-11月 -09',10) from dual;
select next_day(sysdate,'星期日') from dual;
select last_day(sysdate) from dual;

,select to_char(sysdate,'yyyy-mm-dd') from dual（要单引号）
太大的金额都习惯用','来分隔位数。
select sal,to_char(sal,'99.999') from emp;
to_number()可以将字符串变成数字的一种函数
to_date()可以将一个字符串变成Date数据类型。
要求查询出每个雇员的姓名，工作，雇员的直接上级领导的姓名。
select e.ename,e.job,m.ename,d.dname from emp e,emp m,dept d where e.mgr=m.empno and e.deptno=d.deptno;
查询雇员的名称，雇用日期，雇佣年份，和雇佣月份。
select ename,hiredate,to_char(hiredate,'yyyy') year,
to_char(hiredate,'mm') month from emp order by
month,year;

总结只要to_char行数中后面单引号包含的有yyyy  mm dd任意格式，或单独的年，月，日，还可以将yyyy变成一个y ，两个y 三个y ，大小写无关。当将mm写成了Mon时就会将月份后的数字加上一个月字
 

ORACLE中TRUNC()函数的使用方法 
 对于ORACLE中的TRUNC()函数也许还有人对它不是很了解，这篇文章将对它进行讲解.
 TRUNC()函數分兩種
 1.TRUNC(for dates) --为指定元素而截去的日期值
   其具体的语法格式如下：
   TRUNC(date[,fmt])
   其中： date是一个日期值，fmt是日期格式，该日期将由指定的元素格式所截去。忽略它则由最近的日期截去。比如：select TRUNC(TO_DATE('2004-12-14 11:04:57','yyyy-mm-dd hh:mi:ss')) from dual;得到的结果为： 2004-12-14
select TRUNC(TO_DATE('2004-12-14 11:04:57','yyyy-mm-dd hh:mi:ss'),'hh') from dual;
得到的结果为：2004-12-14 11:00:00
2.TRUNC(for number)
   函数返回处理后的数值，其工作机制与ROUND函数极为类似，只是该函数不对指定小数前或后的部分做相应舍入选择处理，而统统截去。
比如：select TRUNC(0.95) from dual;得到的结果为：0
select TRUNC(45) from dual;得到的结果为：45

1. SYSDATE
返回当前的日期和时间
SELECT sysdate FROM dual;
SYSDATE
----------
05-3月 -03
2. ADD_MONTHS(d, no_of_month)
当前日期"m"后推"no_of_month"个月。参数"no_of_month"可为任何整数(正数和负数)。
示例
SELECT add_months(sysdate,2) FROM dual;
3. LAST_DAY(month_day)
返回变量"month_day"中所指定月份的最后一天的日期。
示例
SELECT last_day(sysdate) FROM dual;
ADD_MONTHS
----------
05-5月 -03

4. MONTHS_BETWEEN(d1, d2)
返回日期 d1 和 d2 之间的月份数。如果 d1 晚于d2，结果为正，否则返回负数。
示例
SELECT months_between(sysdate,to_date('20030101','YYYYMMDD')) FROMdual;

日期显示格式是日-月-年
------------------------------------------------------------------------------------------------------------------------------------
 十四：连接
 --自连接查询
select e.ename,e.job,m.ename from emp e,emp m
where e.mgr=m.empno;
--连接查询
--左连接
select e.ename,e.job,m.ename from emp e left join emp m
on e.mgr=m.empno;
--右连接
select e.ename,e.job,m.ename from emp e right join emp m
on e.mgr=m.empno;
--全外连接
select e.ename,e.job,m.ename from emp e full join emp m
on e.mgr=m.empno;
--内连接
select e.ename,e.job,m.ename from emp e inner join emp m
on e.mgr=m.empno;
------------------------------------------------------------------------------------------------------------------------------------
十五：oracle复制表
Create table 表名称 as (子查询);
如： create table aa as (select * from emp);
如果子查询写的是:select * from emp where 1=2,加入了一个永远不成立的条件，表示只复制表结构，但是不复制表内容。
------------------------------------------------------------------------------------------------------------------------------------
十六:oracle分页
Rownum:表示行号，实际上此是一个列，但是这个列是一个伪列，此列可以在每张表出现。

Select * from (select rownum r ,empno,deptno from emp where rownum<10) where r>6;
------------------------------------------------------------------------------------------------------------------------------------
十七：集合操作
在orcle中提供了三种类型集合操作：并(union)、交(intersect)、
差(minus)
Union:将多个查询的结果组合到一个查询结果之中，没有重复的内容。
Union All:将多个查询结果组合到一个查询之中，但是包含重复值。
Intersect：返回多个查询结果中相同的部分。
Minus:返回两个查询结果的差集。
------------------------------------------------------------------------------------------------------------------------------------
十八oracle中创建和使用角色
要创建角色，用户必须有创建角色的特权。
1.Create role 角色名
Create role aa;
2、为角色授权
GRANT语句可以用来将特权授予角色。
语法：grant 权限1,权限2,.. On 用户名.对象名 to 角色名
grant select on scott.emp to aa;
3、将角色授予用户
语法：grant 角色名 to 用户名
4、角色授权给角色语法：
grant 角色1 to 角色2
5、启用角色或禁用角色
在默认情况下用户连接到数据库上时，就可以自动使用这种角色了
为了增强安全性，也可以默认地禁用一个角色；在这种情况中，当用户连接到数据库上时，在使用某个角色之前，必须首先启用这种角色。如果角色具有密码，那么用户在启用角色之前，必须输入密码。
语法：Create role 角色名 【identified by 密码】
设置登录不启用角色
语法：alter user 用户名 default role all except 角色名
用户登录之后启用角色
语法：set role 角色名 identified  by中文;
6、撤销用户的角色
语法：REVOKE 角色名 FROM 用户名
7、从角色中撤消特权
语法：revoke权限1,权限2,.. On 用户名.对象名 from角色名
8、删除角色
语法：drop role 角色名
------------------------------------------------------------------------------------------------------------------------------------
十九：游标：是内存中用于装载记录的一个区域。
如果要想使用游标，需要按照以下步骤进行处理
1、声明游标
语法：cursor 游标名 is 查询语句
2、打开游标
语法：open 游标名
3、取得游标进行PL/SQL的使用，将内容放到变量之中
语法： FETCH cursor_name INTO variable[,variable,....]
    其中:cursor_name为游标名,variable为在声明中定义的变量名
4、关闭游标
语法：close 游标名；

-----------------------------------------------------------------------------------------------------------------------------------二十：-

各种注意的查询
求出每个部门的平均工资。
select deptno, avg(sal) from emp group by deptno;
总结能在一张表里面查究尽量在一张表里查。
 

  
要求求出平均工资大于2000的部门编号和平均工资。
此时应该用having这个关键字
就可以了
select deptno,avg(sal) from emp
group by deptno having avg(sal) > 2000;

oracle 是区分大小写的如：
 Select * from emp where sal>(select sal from emp where ename= 'SMITH');能查出13行
而 Select * from emp where sal>(select sal from emp where ename= 'smith');查出0条

5、列出所有"clerk"(办事员)的姓名及其部门名称，部门的人数。
Select d.dname,e.ename,(select count(f.ename) from emp f where f.deptno=d.deptno ) 部门人数  from emp e,dept d where e.deptno=d.deptno  and
e.job=’CLERK’;
------------------------------------------------------------------------------------------------------------------------------------

 

评论

2 楼 abc98103 2011-03-03  

非常感谢啊。。。

1 楼 abc98103 2011-03-03  

太感谢了。。。