oracle数据库操作

时间:2021-01-27 15:19:46

   oracle数据库操作

 

一:解锁Scott/tiger 用户(locked)
alter user scott account lock; --锁定
alter user scott account unlock; --解锁
alter user scott identified by tiger;  --改密码
grant dba to scott; --赋权限


二:查询

2.1:查询表中所有数据

select * from emp;
select * from dept;

2.2:查询部分字段
select empno,ename,job from emp;


2.3:带条件查询
select * from EMP where ename = 'SMITH';--字符串内容中区分大小写


--去掉重复内容
select distinct job from emp;
select count(distinct job) from emp;


--给字段取别名 (无单引号,别名包含空格则需要双引号)
select  ename as 职员姓名,sal "工  资" from emp;

--算数操作(与空值操作结果为空  NULL)
select sal+nvl(comm,0) from emp;


--字符串拼接( ||  可以拼接任何类型)
select '职员姓名:'||ename||' 工资:'||sal from emp;


--排序
select * from emp order by sal desc,empno asc;
select * from emp order by comm desc nulls last;  -- nulls first;


--模糊查询:通配符( % _ ),空值查询,in  between and
select * from emp where comm is  not  null;

% 任意长度,任意内容
_ 一个长度,任意内容

--查询 名字以 J 开头的人
select * from emp where ename like 'J%';
select * from emp where ename like '%A%';
select * from emp where ename like 'J____';


--伪列
--Rowid :是表中行的存储地址
--Rownum :是查询返回的结果集中行的序号
select rowid,rownum,emp.* from emp
        where rownum <=2 ;
-- rownum  作为条件时  只能使用小于或小于等于
--实现SQLServer中的top

--聚合分组 count  sum  avg  max min   group by  having
    --统计每个职位的最高工资
    select job,max(sal) from emp group by job;
    --统计每个部门的平均工资,低于2000的不显示,结果按降序排列
    select deptno,avg(sal)  
    from emp
    group by deptno
    having avg(sal)>=2000
    order by avg(sal) desc;

--连接查询(内连接)  --公共字段
  --查询所有职员以及所在部门的详细信息
  select * from emp a  inner join dept b on a.deptno = b.deptno;
  select * from emp a ,dept b where a.deptno = b.deptno;
  --外连接  left  right full
  select * from emp a full join dept b on a.deptno = b.deptno;
  --交叉连接 cross
  select * from emp a cross join dept b ;
 
create table team
(
 teamname varchar2(10)
);
insert into team values ('A');         
insert into team values ('B');
insert into team values ('C');

select * from team a cross join team b
where  a.teamname>b.teamname;


--自连接 : 查询KING的下属
select * from emp where mgr
       = (select empno from emp where ename='KING');
       
select * from emp a join emp b
         on a.mgr = b.empno
         where b.ename='KING';

-- 子查询  = < >  <=  >=     in     not in
   --查询所有ACCOUNTING部门的职员
   select * from emp where deptno =
          (select deptno from dept where dname ='ACCOUNTING');


   --查询所有位置在NEW YORK与 DALLAS 的部门职员
   select * from emp where deptno in
          (select deptno from dept where loc in ('NEW YORK','DALLAS'));


    --查询所有与SMITH同部门同职位的职员(in,= 可以包含多个字段)
  select * from emp where (deptno , job)
           in (select deptno,job from emp where ename='SMITH');


-- 内联视图子查询
select * from
  (select a.*,b.dname,b.loc from emp a join dept b on a.deptno=b.deptno) Y ;


   --使用连接查询作为子查询 要去掉重复字段

   --查询工资高于每部门平均工资的人
select *from emp a join    
   (select deptno,avg(sal) as avgsal from emp group by deptno) Y
   on a.deptno = y.deptno
   where a.sal>y.avgsal;
   
   --统计每个部门的详细信息与部门平均工资,低于2000的不显示,结果按降序排列
select *from dept a join    
   (select deptno,avg(sal) as avgsal from emp group by deptno) Y
   on a.deptno = y.deptno
   where avgsal>=2000;


   --查询工资最低5人(使用伪列rownum与排序)
select rownum,Y.* from
(select * from emp order by sal ) Y
where rownum<=5;


   -- 分页查询 将伪列转为临时表的一个普通列
   select * from
 ( select rownum as rn,emp.* from emp where rownum<=16) Y
  where y.rn  >=11;  



--函数
--日期函数

--日期转换(一般采用字符串转换的形式描述日期) to_date(日期字符串,日期格式)
select to_date('2012-7-28','yyyy-mm-dd') from dual;


--1.查询系统时间 sysdate
select sysdate from dual;


--2.在一段时间上添加月份 add_months(日期,数字)
select add_months(sysdate,-10) from dual;


--3.求某日期所在月的最后一天 last_day(日期)
select last_day(sysdate) from dual;


--4.求两个日期之间的月份差(返回小数) months_between(日期1,日期2)
select months_between(to_date('2012-12-12','yyyy-mm-dd'),sysdate)*30 from dual;


 --** 相差的天数:减法
select to_date('2012-12-12','yyyy-mm-dd')-sysdate  from dual;
--5.求指定日期之后第一个星期几  next_day(日期,星期几)
select next_day(sysdate,'星期六') from dual;
select next_day(sysdate,7) from dual;
       --(1-7  星期日-星期六)  

--6.四舍五入 round(日期,日期代码)
select round(sysdate,'yyyy') from dual;
--7.提取年月日 extract(year/month/day from 日期)
select extract(year from sysdate) from dual;   --返回数字
select extract(month from sysdate) from dual;
select extract(day from sysdate) from dual;
--8.to_char 提取日期部分 to_char(日期,格式字符串)
select to_char(hiredate,'yyyy"年"mm"月"dd"日" hh24:mi:ss ')
 from emp;
 
select to_char(hiredate,'yyyy') from emp; --字符串
select to_char(hiredate,'mm') from emp;
select to_char(sysdate,'day') from dual;  --星期

-- 查询所有周末入职的职员
select * from emp where to_char(hiredate,'d') in ('1','7');

--字符串
--字符串长度 length(字符串) vsize(字符串)
select length('hello world!!!!')  from dual;
select vsize('你好') from dual;
--拼接字符串 concat(字符串1,字符串2)  ||
select concat('hello','world') from dual;
--截取字符串 substr(字符串,开始,长度)
select substr('hello world !!!!',1,5) from dual;
 --0 1 都表示第一个

--去掉首尾的指定内容 trim(字符 from 字符串)
select trim('x' from 'xxxxxxxxxxxxxhexxlloxxxxxxxxx') from dual;
--去空格 trim()
select trim(' he  llo  ') from dual;
--内容替换 replace(字符串,替换内容,替换字符)
select replace('hello','l','x') from dual;
select replace(' he  llo  ',' ','') from dual;

--多重替换translate(字符串,替换内容,替换字符)
select translate('hello world!!!!','lr','xx') from dual;

--查找字符 instr(字符串,查找内容)
select instr('hello world!!!!','l') from dual;
         -- instr(字符串,查找内容,开始位置)
select instr('hello world!!!!','l',6) from dual;
         --instr(字符串,查找内容,开始位置,第几个)
select instr('hello world world !!!!','l',6,2) from dual;


--ascii码  ascii(字符)  chr(数字)
select ascii('a') from dual;
select chr(97) from dual;


--转为大小写  upper  lower
select upper('hello') ,lower('HELLO') from dual;


--数学函数
select mod(3,10) from dual;   --取模
select ceil(99.0000000001) from dual; --向上取整
select floor(99.999999) from dual;  --向下取整
select round(99.9) from dual;  --四舍五入
select round(99.93,1) from dual;  --四舍五入,精确位数
select dbms_random.value(55,100) from dual;  --随机数
select dbms_random.value() from dual;

--转换函数  to_char(内容,格式)  to_number(数字字符) to_date(日期字符,日期格式)
select to_char(sysdate,'yyyy-mm-dd') from dual;
select to_char(12345.126,'L999999.99') from dual;  --L  local


--空值处理
select * from emp;
select sal+comm from emp;


--空值转为替代值  nvl(字段,替换值)
select sal+nvl(comm,0) from emp;


--不为空 转为值1 为空转为值2  nvl2(字段,值1,值2)
select nvl2(comm,comm,0) from emp;


--参数1 等于参数2 则变为空  否则为参数1  nullif(参数1,参数2)
select nullif(comm,300) from emp;

select * from emp;
--wm_concat    --行列转换函数 10g
select deptno,  wm_concat(ename)  from emp group by deptno

--分析函数(高级排序功能)
select  rownum,Y.* from (
select  emp.* from emp order by sal) Y


select ename,sal,row_number() over(order by sal  )  ,
       rank() over(order by sal ),
       dense_rank() over(order by sal )
 from scott.emp  ;