Hao's Blog
oracle一些常用的sql和知识点
--dual为oracle的虚拟表
select 1+1 from dual;

select user from dual;

select count(*) from dual;

select userenv('language') from dual;

select ename yingwenm, sal xinshui from emp;
--去重复

select distinct job from emp;

select distinct job, deptno from emp;

select * from emp;
--字符串拼接
select concat(ename, '--test') from emp;

select ename '' ' -- ' '' job from emp;

--nvl(e1, e2) 如果e1为null返回e2
select sal * 12 nianxing, sal * 12 + nvl(comm, 0) 年收入 from emp;
--nvl2(e1, e2, e3) 如e1为null返回e3, 否则返回e2
select nvl2(null, 1, 3) from dual; --3
--nullif(e1, e2)如果e1=e2返回null否则返回e1
select nullif(1, 1) from dual;  --null
select nullif(1, 3) from dual;   --1

--返回第一个不为空的值 coalesce(...) 可传入无限个参数
select coalesce(null, null, 1) from dual;

--转义字符的定义 escape
--select * from emp where ename like '%\%%' escape '\';

---排序时null的显示顺序设置 (nulls [last] or [first]) first为默认
select * from emp order by comm desc nulls last;

--数值函数
--向上取整
select ceil(41.241) from dual;  --42
--向下取整
select floor(41.241) from dual;  --41
--保留小数,四舍五入
select round(41.241, 2) from dual;  --result 41.24
select round(41.241, 1) from dual;  --result 41.2
select round(41.241, -1) from dual; --result 40
select round(41.241, -2) from dual; --result 0

--截断
select trunc(41.641, 2) from dual;  --result 41.64
select trunc(41.641, 1) from dual;  --result 41.6
select trunc(41.641, -1) from dual; --result 40
select trunc(41.641, -2) from dual; --result 0

--求余
select mod(9, 3) from dual;  --0
select mod(9, 4) from dual;  --1

/**
* oracle中case.. when.. end的另一种写法
*  decode(字段, if1, then1, if2, then2, else1)
*/
select decode(ename, 'SMITH', 'handsome', 'WARD', 'ugly', 'normal') from emp;

/**
*   sql编写顺序 select...from...where..group by..having..order by..
*   sql执行顺序  from .. where .. group by .. having .. select .. order by..
*/

--中文状态下oracle默认的时间格式是 dd-mm月-yyyy
select * from emp where hiredate = '1980-12-17';
--修改当前会话的日期格式
alter session set nls_date_format = 'yyyy-MM-dd HH24:mi:ss';

select min(sal) from emp group by deptno;

select * from emp e1 left join dept d1 on e1.deptno = d1.deptno where e1.sal in (select min(sal) from emp group by deptno) 
--查询每个部门薪水最低的员工和所在部门的信息
select e1.*, d1.dname, d1.loc from emp e1 join 
(select deptno, min(sal) minsal from emp group by deptno) ls 
on e1.deptno = ls.deptno and e1.sal = ls.minsal
left join dept d1 on e1.deptno = d1.deptno

--工资最高的前三 (rownum默认1,使用>没有意义,1不会大于1,条件永远不成立,则查询不出结果)
select * from (select * from emp order by sal desc) t1 where rownum <= 3

select deptno, avg(sal) avgsal from emp group by deptno;
--薪水大于本部门平均薪水的人
select e1.* from emp e1 join (select deptno, avg(sal) avgsal from emp group by deptno) t1
on e1.deptno = t1.deptno where e1.sal > t1.avgsal

--每年入职人数
select to_char(hiredate, 'yyyy') year, count(1) hireeveryyear from emp group by to_char(hiredate, 'yyyy');

--rowid记录的是数据存放的物理地址,可用来去重
select rowid, emp.* from emp;
--分页
select * from (select rownum rownumber, emp.* from emp) tt where tt.rownumber between 2 and 4;

--union 和 union all为并集. union有去重
select * from emp where sal > 1000
union all
select * from emp where deptno = 10

--差集运算
select * from emp where to_char(hiredate, 'yyyy') = '1981' 
minus
select * from emp where job = 'PRESIDENT' or job = 'MANAGER'

===============================================================================
--建表空间
create tablespace test2
datafile 'c:/test1.dbf'
size 50m
autoextend on
next 5m
maxsize 500m;

--建人员
create user darius identified by 1234 default tablespace test2;

--赋权 常用connect resource dba
grant connect to darius;
grant resource to darius;

--建表
create table demo2(

       id number(10) primary key,
       uname varchar2(15) not null,
       password varchar2(20) not null,
       salary number(10, 2),
       hireday timestamp
);

--新加一列
alter table demo2 add descripttion varchar2(50);
--修改列名
alter table demo2 rename column descripttion to description;
--修改列
alter table demo2 modify description varchar2(40);
--删除列名
alter table demo2 drop column description;

---约束
create table demo3(
       id number(10) primary key,  --主键约束
       name varchar2(10) unique,   --唯一约束
       password varchar2(20) not null, --非空约束
       gender char(4) check(gender in('男', '女', '其他')) --检查约束
);

--外键约束
create table category (
       cid number primary key,
       cname varchar2(20) not null
);

create table product (
       pid number primary key,
       pname varchar2(20) not null,
       cid number references category(cid)
);

insert into category values(1, 'phone');
insert into product values(1, 'hammer', 1);

--delete foreign key constraint
alter table product drop constraint category_cid_product;
--add foreign key constraint for product
alter table product add constraint category_cid_product foreign key (cid) references category(cid) on delete cascade;

select * from category;
select * from product;

delete category where cid = 1;

---oracle transcation
-- key words : commit rollback savepoint

create table mountain(
       position number primary key
);

---plsql
declare

begin
insert into mountain values(1); 
insert into mountain values(2); 
insert into mountain values(3); 
savepoint rest;  --存档点
insert into mountain values(3); 
insert into mountain values(4); 
insert into mountain values(5); 
insert into mountain values(6); 
commit;
exception 
when others then
       rollback to rest; --发生异常回滚至存档点
       commit;
end;

--oracle view
create or replace view v1
as 
select uname, gender from demo1 
with read only;

insert into demo1 values (1, 'darius', '男', sysdate-90);
insert into demo1 values (2, 'xiaoming', '男', sysdate-80);
insert into demo1 values (3, 'xiaohong', '女', sysdate-900);
insert into demo1 values (4, 'mingyue', '男', sysdate-190);
insert into demo1 values (5, 'shuibingyue', '女', sysdate-50);
insert into demo1 values (6, 'caiquan', '男', sysdate-70);

select * from v1;

--为视图建立别名(同义词)
create synonym v1_t for v1;

select * from v1_t;

----序列
create sequence se_test1
start with 1
increment by 2
minvalue 1
maxvalue 30
cycle 
cache 3;

select se_test1.currval from dual;
select se_test1.nextval from dual;