---------?? create建表 ??------------------ create table emp_tian(
empno number(4)delete, ename varchar(20), job varchar(20), salary number(7,2), bonus number(7,2), hiredate date,
manager number(4), deptno number(2) )
-----------------------------------------?? insert添加信息 ??------------------ insert into emp_tian values(1001,'zhangwuji','manager',10000,2000,'12-mar-10',1005,10); insert into emp_tian values(1002,'liucangsong','analyst',8000,1000,'01-apr-11',1001,10); insert into emp_tian values(1003,'liyu','analyst',9000,1000,'11-apr-10',1001,10); insert into emp_tian values(1004,'guoferong','programmer',5000,null,'01-jan-11',1001,10); insert into emp_tian values(1005,'zhangsanfeng','persident',15000,null,'15-may-08',null,20); insert into emp_tian values(1006,'yanxiaoliu','manager',5000,400,'01-feb-09',1005,20); insert into emp_tian values(1007,'luwushuang','clerk',4000,500,'01-feb-09',1006,20); insert into emp_tian values(1008,'huangrong','manager',4000,500,'01-may-09',1005,30); insert into emp_tian values(1009,'weixiaobao','salesman',4000,null,'20-feb-09',1008,30); insert into emp_tian values(1010,'guojing','salesman',4500,null,'10-may-09',1008,30); ---------------调节数据显示(仅在Oracle中试用，重登后无效)---------------------- 列宽调节
column empno for 9999 ---------------column 可用col代替 column ename for a10 ---------------format 可用for代替 column manager format 9999 column salary format 99999 column bonus for 9999 col job for a10
col ename format a10 col job format a12 翻页设置
set pagesize 100 set linesize 300
调节数据显示(仅在Oracle中试用，重登后无效) ---------?修改信息?------------------ update emp_tian set job = \where ename = 'jizyue';
commit; --------------提交 --------------拷贝--------------- create table emp_tian1 as
select*from emp_tian;
-------------查询-------------------------- select ename ,job from emp_tian
where lower(job) in('analyst','clerk','programer');
select ename ,deptno from emp_tian where lower(deptno) in(10,20);
select ename ,salary from emp_tian where salary >= 5000 and salary <= 8000;
-------------------------等价于 where salary betwen 5000 and 8000;
nvl(***,0) ----当***中内容为null时，则返回0，否则返回***的值 select ename.salary from emp_tian where nvl(salary,0)=0;
-----------找到内容为空的选项--------------
select ename from emp_tian where salary is null; -----------找到非空的选项---------
select ename,salary from emp_tian where salary is not null; -----------得到某个项目的数目---------------- select count(*) from user_tables;
show user -------显示当前用户
-------通配符%查找文件名为EMP+任意字符的文件-------------- select table_name from user_tables where table_name like 'EMP%';
select ename ,salary,
round(salary*0.12345678) as tax from emp_tian; ---------------round（num,xxx） 四舍五入---------------- ----------------- xxx(截取整数) ----------------- xx0(保留整十数)
----------------- xxx.xx(保留两位小数) select ename ,salary,
round(salary*0.12345678) as tax, round(salary*0.12345678,-1) as tax1, round(salary*0.12345678,2) as tax2 from emp_tian;
---------------trunc直接截取-------------------------------- select ename ,salary,
trunc(salary*0.12345678) as tax, trunc(salary*0.12345678,-1) as tax1, trunc(salary*0.12345678,2) as tax2 from emp_tian;
--------------得到系统日期-------------------dual 是一个虚表 单行单列的表 select sysdate from dual;
---------------------按照制定的格式显示------------------
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as mytime from dual; select to_char(sysdate,'year month mon day dy am') from dual; select to_char(sysdate,'YEAR MONTH mon day dy am') from dual; ---------------按照制定的格式显示某个时间量-------------------
select ename,hiredate,to_char(hiredate,'yyyy-mm-dd') hire from emp_tian;
一.Oracle中的日期处理 select sysdate from dual;
----------------计算两个日期之间的间隔------------------------
select ename,hiredate,sysdate-hiredate as days from emp_tian; ---------------计算间隔天数(整数)-----------------------------
select ename,hiredate,round(sysdate-hiredate) as days from emp_tian; -----------------计算间隔月份----------------------------------------
select ename,hiredate,months_between(sysdate,hiredate) as days from emp_tian; -----------------计算间隔月份整月份--------------------------------------------
select ename,hiredate, round(months_between(sysdate,hiredate)) days from emp_tian;
----------------------计算某月的最后一天------------------------------ select last_day(sysdate) from dual;
------------------------计算三个月后的日期----------------------------------- select add_months(sysdate,3) from dual;
------------------------计算10天前/后的日期------------------------------- select sysdate-10 from dual; select sysdate+10 from dual;
--------------------修改wanxiaofei的入职时间为12年2月1号--------------------- update emp_tian set hiredate = '01-FEB-12' where ename ='wanxiaofei'; ---------------------将字符串变成日期(按照特定格式)-------------------------
update emp_tian set hiredate = to_date('2012/03/01','yyyy/mm/dd') where ename = 'wanxiaofei';
----------------------增加职员：1012,'jerry','2012-01-12'----------------------- insert into emp_tian(empno,ename,hiredate)
values(1012,'jerry',to_date('2012/01/01','yyyy/mm/dd')); --------------------------------------------------
to_char to_number - 日期------------>字符----------->数字 - <----------- <---------- -
to_date tochar -
-------------------------------------------------- 数字 字符 数字 - 10000------------->$10,000.00--------->10000 -
to_char to_number - --------------------------------------------------
二.单行函数计算 -------- 一个数据对应一个结果 nvl(bonus,0)
upper(job) = 'ANALYST' round(salary,2)
to_char(sysdate,'yyyy-mm-ss') to_date('2012/03/01','yyyy/mm/dd')
------------如果有奖金发奖金，没奖金的发工资的一半，都没有的话发一百----------- select ename,salary,bonus,coalesce(bonus,salary*0.5,100) as finalbonus from emp_tian;
-----------------雅思计分-------------------------------- 姓名 听力 阅读 写作 口语 总分
叶凡 5 5 7 8 6.25->6.5
[0,0.25) [0.25,0.75) [0.75,1) 0 0.5 1
select*from ielts_tian;
-----------------------计算每人四项平均结果-----------------------------------
select name,scr1,scr2,scr3,scr4,(scr1+scr2+scr3+scr4) score from ielts_tian; -------------------------------取模------------------------------------------- 得到小数位 mod(7.25,1) -------结果 0.25 得到整数位 trunc(7.25) -------结果 7 case when ....then when ....then when ....then end
整数部分 trunc((scr1+scr2+scr3+scr4)/4) 小数部分 mod((scr1+scr2+scr3+scr4)/4,1)
----------------------雅思算分代码---------------------------------------
select name,scr1,scr2,scr3,scr4,trunc((scr1+scr2+scr3+scr4)/4)+ case when mod((scr1+scr2+scr3+scr4)/4,1)<0.25 then 0 when mod((scr1+scr2+scr3+scr4)/4,1)>=0.25
and mod((scr1+scr2+scr3+scr4)/4,1)<0.75 then 0.5 when mod((scr1+scr2+scr3+scr4)/4,1)>=0.75 then 1 end as ieltscore from ielts_tian;
-----------------------------数据库建函数-------------------------------------- create or replace function calculation_tian(score number) return number is
--定义变量
i number;--整数 j number;--小数
result number;--结果 begin --程序体
i := trunc(score);--数据库中赋值:= j := mod(score,1); if j<0.25 then result :=i+0;
elsif j>=0.25 and j<0.75 then result :=i+0.5; elsif j>=0.75 then result :=i+1; end if;
return result; end; / --执行
show errors --------------检查错误 语法
----------------用自己的函数代替繁杂代码-------------------------------- select name,scr1,scr2,scr3,scr4,(scr1+scr2+scr3+scr4)/4 as agv, calculation_tian((scr1+scr2+scr3+scr4)/4) as ieltsscore from ielts_tian;
select ename,salary,bonus from emp_tian order by bonus; ----------------------分别提薪---------------------方法一 select ename,job,salary,
case job when 'clerk' then nvl(salary,0)*1.05
when 'programmer' then nvl(salary,0)*1.1 when 'analyst' then nvl(salary,0)*1.15 else nvl(salary,0)
end as new_salary from emp_tian;
----------------------分别提薪---------------------方法二 select ename,job,salary,
case when job ='clerk' then nvl(salary,0)*1.05
when job = 'programmer' then nvl(salary,0)*1.1 when job = 'analyst' then nvl(salary,0)*1.15 else nvl(salary,0)