Oracle数据库基本操作

Oracle数据库简介

Oracle数据库安装

Oracle数据库基本操作 (select from where, group by, order by)

(前两个会在以后整理好完整的安装包附上再写, 这里从基本操作开始)
select ...
from ...
where ...
group by ...
having ...
order by ...
多表查询, 子查询, top-n, insert, update, delete, 对象操作

基本select语句

数据库数据: 一张员工的信息表

屏幕快照 2016-12-29 下午2.43.32.png

语法格式:
select *(所有) | [distinct](关键字, 去除重复行) column(列) | expression(表达式) alias(别名)... from table;
示例:

select * from emp;
select distinct deptno from emp;//(distinct 去除重复行, 作用域后面的字段, 即一行的范围)
select ename, sal*15 from emp;

对于别名
select empno as "员工号", ename "姓名", sal 月薪 from emp;

这里可以看到, 最基本的是 as "别名" 但是as可以省略, ""也可以省略, 但是要注意省略引号的前提是你别名中不能有空格.
另外, 查询指定列效率高一点

还是上面的表, 计算员工的员工号, 姓名, 月薪, ...年收入.
SQL>select deptno,ename, sal, sal * 12, comm, comm+sal*12 from emp;

结果:显示中有的年收入为空
原因:他们的奖金为空, 因为年收入等于comm+sal * 12, 但是奖金为空, 空的任何表达式都为空(特别注意)
解决:sal * 12+nvl(comm,0) //虑空函数
nvl(a, b); 如果a为空, 结果为b否则结果还是a, 另外空不能用=/!=来判断, 而是用is/is not

• 对于sql语句的修改和编辑, 如果在输入的时候有这个需求, 可以输入edit/ed来调出记事本, 编辑sql, 写完后保存, 然后 / 执行这个语句
• 伪表(dual) 伪列(sysdate)使用: select 2+2, sysdate from dual;

where语句使用(条件过滤) (清屏操作host cls;)

比较运算符结合(= != <> <= >= between and), 逻辑运算符结合,

select * from emp where deptno=10;
select * from emp where ename='KING';
select * from emp where sal <> 1250;

日期格式问题!!!!!!!
查询格式配置信息: select * from v$nls_parameters;
所以查询日期的格式要按照查询出来的格式对应, 并且格式也可以修改
修改会话: alter session set NSL_DATE_FROMAT='YYYY-MM-DD';
恢复原来格式: alter session NSL_DATE_FROMAT set='DD-MON-RR';
但是这样很麻烦, 因为你不知道具体格式是什么, 总不能每次去查询一下格式, 或者别人修改了你则用不了

条件运算符结合 and or berween and(闭区间, 并且从小到大) not

select * from emp where deptno=10 or deptno=20;
select * from emp where deptno=10 and sal=1300;

查询奖金为空, 上文提到空不能使用 = 和 != 判断, 这里可以使用is
select * from emp where comm is null;

当查询多个条件的时候: 规则是sql语句会首先执行右侧的, 所以就是容易判断出结果的条件写到右边即可
比如 and 容易判断出假的写到右边...

in: 在集合中 in(val,val)
select * from emp where deptno in(10, 20);
查询不在10, 20 中

     select * from emp where deptno not in(10, 20);
     select * from emp where deptno not in(10, 20, null);

not in 中不能用空, 因为这个条件永远不成立

like: 模糊查询, % : 匹配任意个字符, _ 匹配一个字符
这里需要注意的是如果查询带下划线的, 需要转义, 转义格式如下:
select * from emp where ename like '%/_%' escape '/';
escape: 转义某个字符后面的字符, 所以那个字符可以自己定义

group by, order by 语句使用

order by 排序, 默认是升序asc 降序(desc), 可以按照列, 别名, 序号

select * from emp order by column(列);
select * from emp order by column1, column2;//如果第一列相同, 则第二列升序排序

如果排序的列中有null值, 那么结果是不正确的.
如果对数据熟悉的话可以使用nvl虑空函数来解决
select * from emp order by nvl(comm, -1);
但是如果不知道数据, 则可以使用 nulls last 意思是将控制排到后面
select * from emp order by comm desc nulls last;

单行函数, 队一行进行变换得到结果

分为字符函数, 数值函数, 时间函数, 日期函数, 转换函数, 通用函数

• lower 小写，upper 大写，initcap 首字母大写
  select lower('Hello wOrld') 一,upper('Hello wOrld') 二,initcap('hello wOrld') 三 from dual;
• concat(连接符||)，substr 截取字符串，length，lengthb，instr在字符串中
  select concat('hello','world') from dual; //连接2个字符串,只支持2个参数
  select 'hello' || ' world'||' itcast'||' 11111' from dual;
select substr('helloworld',1,3) 一,substr('helloworld',1) 二 ,substr('helloworld',-3) 三 from dual;
select instr('helloworld','owo') 一,instr('helloworld','owow') 二 from dual;
• 是否在字符串中,如果在返回位置,不在返回0 --length 求字符个数，lengthb 求字节个数 gbk 中文占2个
  select length('hello中国'), lengthb('hello中国') from dual;
• lpad，rpad l(r)pad(str,len,C) 得到len长度的字符串,如果str的长度不够,左(右)补齐第三个参数 C
  select lpad('hello',10,'#') ,rpad('hello',10,'#') from dual;
• trim 去左右两端空格，
• trim(C from str) 去掉str首尾为C的字符
  select 'aaa'||trim(' hello world ')||'bbb' from dual;
select trim('H' from 'HHHHHelloHHHworldHHHHH') from dual;
• 相当于去掉字串 replace替换
  select replace('helloworld','llo','kk') from dual;
select replace('helloworld','llo','') from dual;

字符函数

• round 四舍五入 trunc 截断 mod 取模
  select round(45.926, 2) 一, round(45.926, 1) 二, round(45.926, 0) 三, round(45.926, -1) 四, round(45.926, -2) 五 from dual;
select trunc(45.926, 2) 一, trunc(45.926, 1) 二, trunc(45.926, 0) 三, trunc(45.926, -1) 四, trunc(45.926, -2) 五 from dual;
select mod(600,1600),mod(1600,600) from dual;

• 向上取整函数 ,向下取整
  select ceil(121/60),floor(121/60) from dual;

• 数值函数 正、负表示小数点之后，或小数点以前的位数

--把薪水转换为本地货币字符型
select sal,to_char(sal,'L9,999') from emp;
--再转换为原来的number型
select to_number('￥1,250','L9,999') from dual;
--上午的按照日期格式查询信息用转换函数,可以避免设置修改引起的错误
select * from emp where hiredate=to_date('1981-11-17','yyyy-mm-dd');
--to_char 与to_number互相转换
--显示 "yyyy-mm-dd hh24:mi:ss 今天是 星期几"
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss "今天是" day') from dual;
--将上述输出字符串反转回日期
select to_date('2016-12-24 15:55:01 今天是 星期六','yyyy-mm-dd hh24:mi:ss "今天是" day') from dual;
select to_number(to_char(sysdate,'yyyymmddhh24miss')) AAAAAAAAAAAAAAAAAAAAAA from dual;
转换函数 to_char to_number to_date 隐式转换的问题
--显示昨天，今天，明天 oracle 的日期型 加上数值 就相当于加了n 天 select sysdate-1,sysdate,sysdate+1 from dual; --计算员工工龄 可以按日，周，月，年 日期差减方法
select (sysdate-hiredate) 天, (sysdate-hiredate)/7 周, (sysdate-hiredate)/30 月, (sysdate-hiredate)/365 年 from emp;
--日期函数 months_between add_months last_day next_day
select months_between(sysdate,to_date('2016-10-24','yyyy-mm-dd')) from dual;
Select months_between(sysdate,hiredate) from emp;
Select months_between(sysdate,hiredate) 一,(sysdate-hiredate)/30 二 from emp;
--add_months 加月份
select add_months(sysdate,2),add_months(sysdate,-2) from dual;
--last_day 给定日期所在月份最后一天
select last_day(sysdate) from dual;
-- 下一个星期 几
select next_day(sysdate,'星期六') from dual;