Hao's Blog
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');

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' 
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


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); 
when others then
       rollback to rest; --发生异常回滚至存档点

--oracle view
create or replace view v1
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
cache 3;

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