--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默认的时间格式是 ｄｄ－ｍｍ月－ｙｙｙｙ
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默认１，使用>没有意义,１不会大于１,条件永远不成立，则查询不出结果)
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;