Oracle10G学习笔记之三Sql

SQL语句：
SQL(Structured query language)包含：
数据查询：
DML(data manipulation language):insert update delete merge
DDL(data definition language):create alter drop rename truncate
DCL(数据控制语言):授权和回收权限

create table abc(a varchar2(10),b char(10));  //建表
alter table abc add c number;  //增加一列
alter table abc drop column c;   //删除一列

grant select on dept to scott;       //为scott用户授予查询dept表的权限
revoke select on dept from scott;   //收回scott用户查询dept表的权限

常用系统函数
字符
length、lengthb、ltrim、replace、rtrim、substr、trim
日期
sysdate、current_date、next_day
转换
to_char 、to_date、to_number
to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
to_date('12-3月-04')
聚集函数
sum、avg、max、min、count
其他函数
user、decode、nvl
select user from dual;   //查看当前用户
select sum(decode(sex,'男',1,0)) 男性数,sum(decode(sex,'女',1,0)) 女性数 from emp;   //查询性别为男和性别为女的个数
select nvl(sal,'未输入') from emp;     //如果为空值，则替换为'为输入'

select comm,sal,ename || job,ename || NULL || job as job from emp;
列的别名,在列名和别名之间加入可选关键字as.

||     用于连接列和字符.
select ename ||' is a '|| job as "Employees" from emp;

单引号用于字符串    双引号用于别名
字符和日期都要加单引号
字符型数值是区分大小写的
日期型数值是格式敏感的,默认格式为DD-MON-RR
SELECT * FROM emp WHERE hiredate >= '1-1月-1982';

SELECT * FROM emp
WHERE comm IS NULL AND deptno IN(20,30)

... is null
... is not null
order by ...
order by ... desc
select distinct name from emp;     //剔除重复值，消除相同记录,效率低,尽量不用
group by ...
聚集函数不能出现在where子句中，可以使用having来实现
having ...
select a1,count(a1)  from aa group by a1 having count(a1)>1;

... like ...
_单字符
%多字符

内连接
... from e join d on e....=d....
左外连接(左边表无条件显示)
... from e join d on e....=d....(+)
右外连接(右边表无条件显示)
... from e join d on e....(+)=d....

select * from e where id in (select id from d where id=e.id and id='03')   
... not in ...
select * from e where exists (select id from d)     //子查询有结果集时才显示查询结果
... not exists ...

select id from emp union select id from d;      //返回两个查询结果集的并集
select id from emp intersect select id from d;  //返回两个查询结果集的交集
insert into e(eid,ename) select id,name from d; //批量插入

create table ttt as (select * from sss);   //复制表结构，同时将值批量复制

Oracle里的数据类型：
binary_double
binary_float
blob
clob
char()
date
interval day to second
interval year to month
long
long raw
nclob
number
nvarchar2()
raw()
timestamp
timestamp with local time zone
timestamp with time zone
varchar2()