oracle语法

查询

查看所有表

select *from tab;--查看所有表
select *from tabs;--查看所有表的详细信息

四则运算

四则运算	select sal*12 from emp;
nvl	select sal*12+nvl(comm,1) from emp;	如果comm为空,则返回1,否则返回自身
concat	select concat('姓名',ename) from emp;	得到内容的字符换拼接(在ename的结果前面添加字符串)
||	select '姓名:' || ename from emp;	oracle特有的字符串拼接方法
nvl2	select nvl2(1,5,6)from dual;	--如果第一个参数为空,则返回第三个参数,否则返回第二个参数
nullif	select nullif(1,2)from dual;-	判断两个数是否相等,如果相等就返回null,否则返回第一个参数
coalesce	select coalesce(null,null,3,null,1) from dual;	找到第一个不为null的数,返回
distinct	select distinct mgr from emp	去重,去除查询到的重复内容

取别名

查询所有员工的年薪
select e.sal*12 "年薪",e.* from emp e;

取中文名称

select case ename 
when 'SMITH' then '斯密斯'
when 'ALLEN' then '艾伦'
when 'JONES' then '乔布斯'
  else
    '其他'
  end
 from emp; 

oracle特有取中文名称

select decode(ename,'SMITH','曹操','ALLEN','关羽','貂蝉') from emp;

条件查询

where

关键字	说明	例子
is not null	comm字段不为空	select *from emp where comm is not null;
is null	为空	select *from emp where comm is not null;
between.. and ..	在某个区间	select *from emp where comm between 1 and 2000;
in	包含(名字包含这两个字段的)	select *from emp where ename in('ALLEN','WARD');
like (关键字%_)	模糊查询 _匹配一个字段	select *from emp where ename like 'A%'
escape like匹配关键字	匹配带%的内容	select *from emp where ename like '%/%%' escape '/';

排序

关键字	说明	例子
order by	按照sal排序(默认升序)	select *from emp order by sal
order by 关键字段 asc	按照sal升序	select *from emp order by sal asc
order by 关键字段 desc	按照sal降序	select *from emp order by sal desc

字符串

关键字	说明	例子
substr	截取两个效果一样	select substr('qwer',0,2 ) from dual;--从1下标开始,截取两个字段 select substr('qwer',1,2 ) from dual;--从1下标开始,截取两个字段
length	获取字符串长度	select length('abcdefg') from dual;
trim	去除字符串两边的空格	select trim(' hello ') from dual;
replace	把字符串的w替换成a	select replace('qwer','w','a')from dual;

数字

关键字	说明(不写第二个数,默认都是取整)	例子
round	四舍五入.默认取整数	select round(3.1415926) from dual;
round	四舍五入,精确到某位(保留两位小数点)	select round(3.1415926,2) from dual;
trunc	截取(保留两位小数点,不会四舍五入)	select trunc(3.1415926,2) from dual;
ceil	向大取整(结果为4)	select ceil(3.1415926)from dual;
floor	向小取整(不管后一位是几,都舍去)	select floor(3.1415926)from dual;

日期

关键字	说明	例子
sysdate	查询当前系统时间	select sysdate from dual;
sysdate	查询入职天数	select ceil(sysdate-e1.hiredate) "入职天数" ,e1.* from emp e1;--当前系统时间减去入职时间,然后取整
months_between	返回两个日期间隔的月数	select emp.*, months_between(sysdate,hiredate) "入职月数"from emp;
add_months	计算几个月后的今天	select add_months(sysdate,3)from dual;
sysdate	1.5天后的时间	select sysdate+1.5 from dual;
to_char(时间显示规则)
to_char	当前时间是这周的第几天	select to_char(sysdate,'d')from dual;
to_char	当前时间是这个月的第几天(几号)	select to_char(sysdate,'dd')from dual;
to_char	当前时间是今年的第几天	select to_char(sysdate,'ddd')from dual;
to_char	当前时间是今年的第几周	select to_char(sysdate,'ww')from dual;
to_char	当前时间是今年的第几月(月份)	select to_char(sysdate,'mm')from dual;
to_char	当前时间是今年的第几季	select to_char(sysdate,'q')from dual;
to_char	当前的年份	select to_char(sysdate,'yyyy')from dual;
to_char	显示货币和保留小数点位数	select to_char(sal,'$999,999.9999') from emp;
日期转换符
	2017-06-27 16:30:39格式	select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
	2017-06-27格式	select to_char(sysdate,'yyyy-mm-dd')from dual;

select to_char(sysdate,'yyyy') from dual;

select to_char(sysdate,'mm') from dual;
select to_char(sysdate,'mon') from dual;
select to_char(sysdate,'month') from dual;
-- 一个星期中第几天
select to_char(sysdate,'d') from dual;  -- 5
-- 一个月中第几天
select to_char(sysdate,'dd') from dual;  -- 08
-- 一年中第几天
select to_char(sysdate,'ddd') from dual;  --159

select to_char(sysdate,'dddd') from dual; --1595

字符串转换成日期格式

select to_date('2017-06-08','yyyy-mm-dd') from dual;

查询时间区间

查询1980到1985年之间入职的员工
select *from emp where hiredate between to_date('1980-01-01','yyyy-mm-dd') and to_date('1985-12-31','yyyy-mm-dd');

聚合函数

关键字	说明	例子
count	查询的字段的个数的总和	select count(*) from emp
max	返回字段的最大值	select max(sal) from emp
min	返回字段的最小值	select min(sal) from emp
sum	总和	select sum(sal) from emp
avg	平均	select avg(sal)from emp

分组group by

查询所有部门,和每个部门都有多少人
select deptno,count(deptno) from emp group by deptno;

根部部门分组查询出每个部门工资最高的
select deptno,max(sal) from emp group by deptno

多表

多表查询

select *from emp e,dept d where e.deptno=d.deptno;

左外连接

关键字left outer join...on...

(+)右表的空值添加null

select * from emp e1 left outer join dept d1 on e1.deptno=d1.deptno(+);

not in注意事项

--外连接查询not in()里面如果包含空字段.会出现查不出任何结果,,
--所以在使用not in()时,一定要去除null字段

select *from emp where empno not in(select distinct mgr from emp where mgr is not null);

是否存在exists

替代了in

select * from dept d where exists(select *from emp e where e.deptno=d.deptno);

伪列rownum(分页)

select *from emp where rownum<=3;//查询前三条数据

创建

列的类型

对应的javaBean	sql类型
integer	number(总长度,小数长度) 注意: 小数长度不能大于 总长度
String	varchar(长度) : 当前版本能够使用,但是不保证以后的版本还能够使用 varchar2(长度): 可变长度字符串 不够10位以空格填充,并且如果传入是null, 它仍然占10个空格
date	date年月日时分秒 2017/6/29 9:08:15
date	timestamp : 时间戳, 比date类型更加精确 29-JUN-17 09.08.37.790000 AM +08:00
	Long : 大文本内容, 最大支持2G
	CLOB : CHAR LARGE OBJECT 字符大对象
	BLOB : BINARY LARGE OBJECT 二进制的大对象

表

创建表

create table student(--创建表
sid number,
sname varchar(20)
);

增加字段

alter table student add sex varchar2(10);--添加一列

alter table student add(--同时添加多列
phone varchar2(11),
gender varchar2(2)
);

修改字段属性

关键字: alter table 表名 modify 列名 属性;

alter table student modify sname  varchar(22);

修改字段名字

关键字: alter table 表名 rename column 旧列名 to 新列名

alter table student rename column phone to mobile;

修改表名字

关键字: rename 旧表名to 新表名;

rename  student to stu;

删除表

关键字:drop table 表名;

drop table stu;

表的约束

五大约束

关键字	特征	例子
主键约束 primary key	唯一并且非空	cid number primary key
唯一约束unique	唯一可以为空(此字段不能出现重复的内容)
非空约束 not null	不能为空
检查约束check(条件)	只能写入条件指定的数据,比如性别只能写入男或者女 在mysql中可以去写这样的语法, 但是mysql自动忽略检查约束
外键约束	约束从表必须存在与主表

添加外键约束

关键字:alter table 从表名 add foreign key(字段) references 主表名(字段 );

alter table product add foreign key(cno) references category(cid);

子查询

查询另外一个用户的表

scott.emp 另外一个用户的另外一张表

select * from scott.emp where 1=1;--有表和数据

select * from scott.emp where 1=2;--只有表

as

--使用子查询创建表(等于复制表)
create table emp2 as select *from scott.emp;()

两种删除的区别

删除表得到中的所有数据

DML类型语句

delete from emp2; --逐条删除,DML支持回滚,效率低

DDL类型语句

truncate table emp2;--直接删除整个表,再创建一张空表,不支持回滚

事物

关键字:

declare --生命事物
begin --业务逻辑部分(语句部分)

sql语句...

savepoint 回滚点名字;--设置回滚点

sql语句...

commit;--提交
exception--如果发生异常
when others then
rollback to 回滚点名字;--回滚到回滚点
commit;--提交
end;

declare 
begin  --业务逻辑部分(语句部分)
  insert into louti values(1);
   insert into louti values(2);
    insert into louti values(3); 
    savepoint point;--设置回滚点
    insert into louti values(4);
     insert into louti values(4);
      insert into louti values(5);
  commit;--提交
exception--如果发生异常
when others then     
   rollback to point;--回滚到回滚点
   commit;--提交
end;

视图

实际是对查询结果的封装, 视图本身不存储任何数据,所有的数据都存放在原来的表中

1.封装复杂的查询语句

2.屏蔽表中一些字段

关键字:

create or replace view 视图名字 as (封装的表) with read only;

select e.job,e.ename,e.sal from emp2 e;--要封装的表

create or replace view view_emp2 as (select e.job,e.ename,e.sal from emp2 e);--封装

create or replace view view_emp2 as (select e.job,e.ename,e.sal from emp2 e) with read only;--封装成只读

select *from view_emp2--查询封装后的数据

创建同义词

create synonym eee1 for eee;--为eee表创建一个新的名字,可以通过这个新的名字实现对eee的操作

序列

创建一个序列

create sequence seq1
start with 1--开始索引
increment by 2--每次自增
maxvalue 30--最大值  nomaxvalue不设置最大值
minvalue 0--最小值    nominvalue不设置最小值
cycle --设置缓存       nocycle不设置缓存
cache 10;--设置缓存大小

得到序列

select seq1.nextval from dual;--返回下一个值类似next

select seq1.currval from dual;--返回当前值

开发中常用法

为结果添加一个索引字段

select seq1.nextval ,s.* from student s;

索引(优化sql的查询)

相当于于是一本词典的目录, 能够帮助我们快速查询所要的内容

​ 当内容多的时候才需要是这个目录

索引是一种排好序的数据结构,Btree , B : balance 平衡的意思,平衡多路查找树

创建一个序列

create sequence seq2;   --创建一个序列

创建表并插入数据

create table bigdata(--创建表
id varchar2(20),
name varchar(20)
);


declare--循环插入30000条数据
begin
  for i in 1..30000 loop
    insert into bigdata values ('wid' || seq2.nextval, i);
    end loop;
    commit;
    end;

普通查询

cup的运算次数200多次

select * from bigdata where id='wid10001';

创建索引后的查询

cup的运算次数4次

create index ind on bigdata(id);--根据bigdata表的id创建一个索引

select *from bigdata where id='wid1'