Oracle数据库语句大全

Oracle数据库语句大全

作者： 逸芝
出处： http://blog.csdn.net/yang5726685
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。

Oracle数据库是甲骨文公司的一款关系数据库管理系统。它是在数据库领域一直处于领先地位的产品。下面是Oracle数据库建表、修改表、查询语句。

--创建表

--语法：create table table_name(
--            column_name datatype,
--            ...
--            );
create table emp(
  empno number(10) primary key ,--主键
  ename varchar2(20) not null unique,--不能为空,唯一
  job varchar2(10) default '匿名',--默认值，用单引号
  mgr number(10),
  hiredate date,--默认格式DD-MM-YY
  sal number(10,2),
  comn number(10,2),
  deptno number(10)
);
create table dept(
  deptno number(10) primary key,
  daname varchar(20) not null unique,
  loc varchar2(20)
);
commit;--提交
rollback;--回滚


--查询表
select *from emp;
--查询具体的列
select ename , job , mgr from emp;
--查看表的结构
desc emp_copy;


--复制表
create table emp_copy as select*from emp;
--已经存在一张表的结构，却复制其他表的数据
insert into emp_copy select *from emp;


--增加表字段
alter table emp_copy add (stjobdate date);
--修改字段
alter table emp_copy modify(stjobdate varchar2(20));
--删除字段
alter table emp_copy drop(stjobdate);
--修改列名
alter table emp_copy rename column ename to empname;
alter table emp rename column comn to comm;


--插入记录
--语法：insert into table_name(column1,column2,...) values(value1,value2,...);
insert into emp values(8001,'jodon','selesman',7698,to_date('2016-12-13','yyyy-mm-dd'),1250,1400,30);
insert into dept values(40,'OPERATIONS','BOSTON');
insert into dept(deptno,daname) values(50,'AILEN');


--修改记录
--语法：update table_name set column1=value1,column2=value2,... where 条件;
update emp set empno=1000,ename='Obama' where empno=8001;


--可以按指定条件删除表中指定的记录,可以表的结构仍存在，优点支持事务,删除后仍能恢复
--语法:delete table_name where 条件；
delete from emp where empno=8000;
--剪切表:删除所有表中的记录，性能比delete高，同时维护索引，删除后无法恢复
--语法truncate table_name;
truncate table emp_copy;


--删除表：删除表结构和数据
drop table emp_copy;
--恢复删除表
flashback table emp_copy to before drop;


--列的别名：可用as作为标识符，也可以直接空格加双引号
select empno as "员工编号",ename "员工姓名" from emp;


--where条件
select empno,job from emp where ename='SMITH';
select ename,sal from emp where job='CLERK';


--运算符：
--1、算术运算符：+ - * /  注意：表达式中只要有一个为空，整个表达式结果返回空：null + 1000 =null
--2、比较运算符：> >= < <= = 不等于：!= <>
--3、逻辑运算符：not and or [not]in [not]like between...and
--4、连接运算符：||
select 4+3 from dual;
select ename "姓名",sal "薪水" from emp where sal<>800;
select ename "姓名",sal "薪水" from emp where empno in(7369,7566,7839);--in()查询与括号指定的记录
select ename "姓名",sal "薪水" from emp where sal not between 800 and 1500;
select ename "姓名",sal "薪水" from emp where ename not like 'SMITH';
select 'abc'||'def' from dual;--连接：abcdef
select empno||':'||ename "编号与姓名" from emp;


--运算符优先级：
--1、算术运算符
--2、连接运算符
--3、比较运算符
--4、is[not] null,[not] like,[not]in
--5、[not]between...and
--6、not and or 
select ename "员工姓名",sal "员工薪水" from emp where (sal>800) or (not (sal<=1500));


--获取唯一不重复的记录：distinct
select distinct job from emp;


--null值判断:is null 或者 is not null; 错误：column=null;
select *from emp where mgr is not null;


--排序:order by column1,column2,...;必须放在select语句的最后！两种：(默认)升序:asc 降序:desc
select ename,sal  from emp order by sal desc,ename;
select ename "姓名",sal "工资" from emp order by "工资" desc,"姓名";--用别名排序
select ename,sal  from emp order by 2 desc,1;--用列的顺序排序


--模糊查询[not]like
--通配符：%：任意0-n个任意字符   _:任意一个字符
select *from emp where ename like '%S%';--含有S字符
select *from emp where ename like 'S%';--S字符在前
select *from emp where ename not like '%S';--S字符后
select *from emp where ename like '_L%';--第二个字符为L


--字符串函数
--大小写转换
select lower(ename) from emp;--小写
select upper(ename) from emp;--大写
select lower('AAA') from dual;--aaa
select initcap('AAA') from dual;--首字母大写：Aaa
select concat('aaa','bbb') from dual;--连接字符：aaabbb
select lpad('abcde',10，'*') from dual;--左边填充：*****abcde
select rpad('abcde',10,'*') from dual;--右边填充：abcde*****
select trim('=' from '====abc==defg===') from dual;--两边压缩：abc==defg
select ltrim('====abc==defg===','=') from dual;--左边压缩：abc==defg===
select rtrim('====abc==defg===','=') from dual;--右边压缩：====abc==defg
select length(ename)from emp;--获取字符串的长度
select substr(ename,2,3) "姓名" from emp;--从指定的列第2个字符开始截取3个字符
select instr('abcdef','cd')from dual;--获取子字符串所在的位置
select chr(65)from dual;--获取字符
select ascii('a')from dual;--获取奥斯卡码（ASCII）
select replace('abcfdefdhifjk','f','*') from dual;--将f替换成*：abc*de*dhi*jk


--数值函数
--1、abs绝对值
--2、ceil  只产生大于或等于指定值的最小整数
--3、floor 只产生小于或等于指定值的最大整数
--4、mod   求余
--5、power 求指数
--6、sqrt  求平方根
--7、round 截断保留，四舍五入
--8、trunc 截断保留，不四舍五入
select abs(-3)from dual;
select ceil(10.5)from dual;
select floor(10.5)from dual;
select mod(5,2)from dual;--余1
select power(2，3)from dual;--
select sqrt(4)from dual;
select round(5.55,1)from dual;--小数点后面1位被截断，并四舍五入：5.6
select round(5.5,0)from dual;--小数点截断，并四舍五入，保留整数：6
select trunc(15.5，-1)from dual;--小数点前面1位被截断，并不四舍五入：10


--日期函数
--1、sysdate
--2、last_day()本月最后一天
--3、add_months（d,n）
--4、months_between(f,s)
--5、current_timestamp
--6、extract 找出日期或间隔值得字段值
--日期格式：
YY,YYYY
Q 季度
MM 月份数
WW 当年第几周
W 本月第几周
DDD 当年第几天
DD 本月第几天
D 本星期第几天
DY 本星期第几天缩写
--时间格式：
24小时制：HH24:MI:SS
12小时制：HH12:MI:SS
select sysdate from dual;--系统时间
select current_timestamp from dual;--系统时间戳
select last_day(sysdate) from dual;
select add_months(sysdate,3) from dual;
select months_between(sysdate,hiredate),sysdate,hiredate from emp; 
select extract(month from sysdate) "This Month" from dual;--找到当前系统时间的月份


--nvl函数：判定空值
--1、nvl格式：nvl(expr1,expr2)：如果expr1为空，则返回expr2
--2、nvl2格式：nvl2(expr1,expr2,expr3)：如果expr1不为空，则返回expr2；如果expr1为空，则返回expr3
--3、nullif函数：nvullif(expr1,expr2):如果expr1,expr2相等则返回空（null），否则返回第一个值expr1
--4、coalesce函数：coalesce(expr1,expr2,expr3,...exprn):返回expr1,expr2,expr3,...exprn中第一个部位null的值
select sal,comm,sal+nvl(comm,0) "工资总和" from emp;
select sal,comm,sal+nvl2(comm,10000,0) "工资总和" from emp;
select sal,comm,sal+nullif(sal,comm) "工资总和" from emp;
select coalesce(null,null,11,22,null,33)from dual;


--转换函数
--1、数值转为字符串
--2、字符串转为数值
--3、日期到字符串
--4、字符串到日期
select 'aaa'||123 from dual;
select to_char(123) from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')from dual;
select to_number('1234') from dual;
select to_date('2016-12-12','yyyy-mm-dd')from dual;--格式要匹配


--多行函数
--1、count()计数
--2、max()最大值
--3、min()最小值
--4、sum()求和
--5、avg()求平均值
select count(*) 总人数 from emp;--不忽略null
select count(comm) 总人数 from emp;--count(列名)会忽略null
select count(*) "总人数",max(sal) "最高薪水", min(sal) "最低薪水" ,sum(sal) "薪水总和",trunc(avg(sal),2) "平均薪水" from emp; 


--分组查询 group by 
--select 之后的非参数列必须出现在group by 列的之后
select deptno "部门编号",count(*) "部门总人数",max(sal) "部门最高薪水", min(sal) "部门最低薪水" ,sum(sal) "部门薪水总和",trunc(avg(sal),2) "部门平均薪水"
  from emp 
    group by deptno;
    
--连接查询
--笛卡尔积:行乘列加
select *from emp,dept;


--等值查询
--内连接：where ,  a inner join b on 条件;内连接可以省略inner
select * from emp,dept where emp.deptno = dept.deptno;
select * from emp inner join dept on emp.deptno = dept.deptno;


--给表取别名:在表后面空格+别名
select * from emp e,dept d where e.deptno = d.deptno;


--全外连接outer join on
select dname，ename from dept d full outer join emp e on e.deptno = d.deptno;
--左外连接left outer join on  右连接right outer join on  全外连接：full outer join on
select dname，ename from dept d left outer join emp e on e.deptno = d.deptno;--左连接
select ename,dname from dept d left join emp e on e.deptno = d.deptno;--右连接


--n张表连接，n-1个条件
select *from emp e,dept d,salgrade s 
  where e.deptno=d.deptno and e.sal between s.losal and s.hisal;


--自连接：自己连接自己
select e1.ename "员工姓名",e2.ename "直接领导"from emp e1 left join emp e2
  on e1.mgr=e2.empno;


--子查询
select * from emp where deptno=(select deptno from emp where ename='SMITH');
select * from emp where sal>(select sal from emp where ename='SMITH');


--all  any   exists存在
select ename from emp where exists(select *from emp where sal>2000);