oracle基础查询语句总结（二）

------------------------------------------第三章--查询--------------------（很重要）
select * from t_student;
select distinct sid from t_score;
select sid as 学号,sname as 姓名 from t_student;
select tt.sid as 学号,tt.sname as 姓名 from t_student tt;
-----过滤
select * from t_student where sid='10005'or sid='10003'or sname='谢娜';
select * from t_student where sname='姚明' and ssex='m';----两个条件同时满足
select * from t_student where sname like '%明';
select * from t_student where sname like '%明%';---前后可有字符
select * from t_student where sname like '_阳';--前面必须有一个字符
--------排序
select * from t_score order by score desc;--asc表升序,默认升
select * from t_score order by score desc,sid desc;--第一个相同时，用第二个来排序

--------连接查询

select * from t_teacher;
select * from t_teachercourse;
-----内联接
select * from t_teacher inner join t_teachercourse on t_teacher.tid=t_teachercourse.tid;
select * from t_teacher tt inner join t_teachercourse tc on tt.tid=tc.tid;
select * from t_teacher tt ,t_teachercourse tc where tt.tid=tc.tid;
select tt.tid,cid from t_teacher tt inner join t_teachercourse tc on tt.tid=tc.tid;

-----外联接
select * from t_teacher tt left join t_teachercourse tc on tt.tid=tc.tid;
select * from t_teacher tt right join t_teachercourse tc on tt.tid=tc.tid;
select * from t_teacher tt full join t_teachercourse tc on tt.tid=tc.tid;

-----交叉连接
select * from t_teacher tt cross join t_teachercourse tc;
select * from t_teacher tt , t_teachercourse tc;

----自联接
----grant all on scott.emp to zhangsan1;系统表中授
select * from scott.emp; 
select e1.empno,e1.ename,e1.mgr,e2.ename from scott.emp e1 join scott.emp e2 on e1.mgr=e2.empno;

---聚合函数
select * from t_teacher;
select max(tage) ,min(tage),avg(tage),sum(tage)from t_teacher;
注意：count（*）查询所有数据个数，count（列名）查询不为空数据个数

----子查询
select tage from t_teacher where tname='张老师'
select * from t_teacher where tage>(  
select tage from t_teacher where tname='张老师'
);
select * from t_score where cid=1;
select * from t_student where sid in (select sid from t_score where cid=1);
select * from t_student where sid not in (select sid from t_score where cid=1);
select * from t_teacher ;
select * from t_course;
select * from t_teachercourse;
------使用from后查询
----查询没有教师上课的课程信息
select * from t_course where  cid in(select distinct cid from t_teachercourse);---distinct防止出现重复
select * from t_course where exists(select *from t_teachercourse where t_teachercourse.cid=t_course.cid);
----成绩表中查出学生平均分最高的(用分组)
select * from t_score;
select avg(score) from t_score group by sid;
select * from(select avg(score)as avgscore from t_score group by sid);
select max(avgscore) as 平均分最大值 from(select avg(score)as avgscore from t_score group by sid);
------查出学生最终成绩(取补考成绩与原来成绩的最大值)
select sid,cid,max(score) from t_score group by sid,cid;

select * from t_teacher;
select rownum as rn,tt.* from t_teacher tt;
select * from(select rownum as rn,tt.* from t_teacher tt)where rn>3 and rn<=6;
-----列中使用子查询
----查出所有学生每门课的成绩
select sid,cid,max(score) as sc from t_score group by sid,cid;
select tst.sname,tc.cname,sc from(select sid,cid,max(score) as sc from t_score group by sid,cid) ts
 join t_student tst on ts.sid=tst.sid
join t_course tc on ts.cid=tc.cid;---表连接

select(select sname from t_student where sid=ts.sid)as sname,
(select cname from t_course where cid=ts.cid)as cname,
max(score)as sc from t_score ts group by sid,cid;---子查询

-------------------------------------------第四章--操作符--------------------------
--算术操作符
----运算符
select sid,cid,score+10 as sc from t_score where cid=2;
-- 比较运算符
select * from t_teacher where tage>=31 and tage<=40;
select * from t_teacher where tage between 31 and 40;----与上条等价30<=X<=40
select * from t_teacher  where tname is not null;

----连接运算符
select *from t_student;
select '学号：'||'姓名:'||sname as info from t_student;

---集合操作符
select * from t_student where sid in(10001,10002,10003)
union---并集 去除重复
select * from t_student where sid='10005';

select * from t_student where sid in(10001,10002,10003)
union all---并集重复
select * from t_student where sid='10001';

select * from t_student where sid in(10001,10002,10003)
intersect--交集
select * from t_student where sid='10001';

select * from t_student where sid in(10001,10002,10003)
minus---做差
select * from t_student where sid='10001';
-----------字符函数
select initcap('hello') from dual;---只把首字母变大写

select Lower('HELLO') from dual;---大写变小写
select upper('HELLO') from dual;
select Ltrim('HELLO','Hel') from dual;----删掉左面字符
select Rtrim('HELLO','LO') from dual;---删掉右面字符
select translate('1234561234561212','1234','ab') from dual;----对应替换
select replace('1234561234561212','1234','ab') from dual;----依次对应
select replace('jack and jue jac','jac','abc') from dual;
-----字符函数
select instr('HELLO','LO') from dual;--字符串开始位置
select substr('HELLO',2,2) from dual;--从第二个开始两个字符
select concat('HELLO','1234') from dual;---字符串连接
select chr(67) from dual;---67对应ASII中的值
select lpad('function',15,'=') from dual;----填充
select rpad('function',15,'=') from dual;----填充
select trim('    aaaa      ')||'bbbb'as ab from dual;----去掉空格
select trim('9',from '999777999')from dual;-----去掉两边的9
select decode('a','f','男','c','女','a','非') as sex from dual;--如果a是f就替换成男
------数字函数
select abs(-15) from dual;
select ceil(44.778) from dual;
select floor(100.2) from dual;
select cos(180) from dual;
-------日期函数
select sysdate "系统日期" from dual;
select sysdate,sysdate+1 "日期+一天" from dual;
select sysdate ,add_months(sysdate,1) "日期 + 一个月" from dual;
select sysdate ,add_months(sysdate,-2)"日期 - 两个月" from dual;
select sysdate ,add_months(sysdate,12)"日期 +一年" from dual;
select sysdate ,to_char(sysdate+7,'yyyy-mm-dd HH24:Mi:ss') from dual;

select sysdate,months_between(sysdate,add_months(sysdate,-1))"相差月数" from dual;

select sysdate,last_day(sysdate)as "当前月份的最后一天" from dual;

select sysdate，next_day(sysdate,1) "下一个周的第一天是几号" from dual;
select trunc(sysdate,'year')from dual;----截取到年（本年的第一天）
select trunc(sysdate,'q')from dual;-----截取到月份（本季度的第一天）
select trunc(sysdate,'month')from dual;---截取到月（本月的第一天）
select sysdate,extract(year from sysdate) "日期年份"from dual;
select sysdate,extract(month from sysdate) "日期月份"from dual;
select sysdate,extract(day from sysdate) "日期几号"from dual;

select sysdate,extract(year from sysdate) "日期月份"from dual;

---转换函数
select to_char(0.123,'$0.99999') from dual;
select to_char(123.0233,'FM9999999.0099') from dual;--FM去除空格
select to_char(sysdate,'YYYY-MM-dd HH24:Mi:SS') from dual;
select to_char(sysdate,'fmYYYY "年"MM"月"DD"日" HH24"时"MI"分"SS"秒"'）from dual;
select to_date('2005-12-06','yyyy-mm-dd')from dual;
select to_number('100')from dual;

----其他函数
select empno,sal,comm from scott.emp;

select empno,sal,comm,sal+comm from scott.emp;
select empno,sal,comm,sal+nvl(comm,0) from scott.emp;
select empno,sal,comm,sal+nvl2(comm,comm,0) from scott.emp;
select nullif(100,100) from dual;
select nullif(100,200) from dual;
----分析函数
----给排好序的每一行返回一个编号
select row_number() over(order by sal desc)as 编号,
empno,ename,deptno,sal from scott.emp;
select row_number() over(partition by deptno order by sal desc)as 编号,
empno,ename,deptno,sal from scott.emp;

select rank() over(order by sal desc)as 编号,
empno,ename,deptno,sal from scott.emp;
select rank() over(partition by deptno order by sal desc)as 编号,
empno,ename,deptno,sal from scott.emp;

select dense_rank() over(order by sal desc)as 编号,
empno,ename,deptno,sal from scott.emp;
select dense_rank() over(partition by deptno order by sal desc)as 编号,
empno,ename,deptno,sal from scott.emp;

--------第六章--------
---system中执 授权给张三１
grant all on scott.emp to zhangsan1;
grant all on scott.dept to zhangsan1;
grant create synonym to zhangsan1;
grant create public synonym to zhangsan1;
 ---创建用户，并授权
create user lisi identified by mima;
grant connect,resource to lisi;
grant all on scott.emp to lisi;
grant all on scott.dept to lisi;
--------------------------
---------同义词
select * from scott.emp;
select * from scott.emp;
create synonym emp for scott.emp;
create synonym dept for scott.dept;
select * from emp;
select * from dept;
create  public synonym p_emp for scott.emp;---公有同义词,别的用户可以访问
create public synonym p_dept for scott.dept;
-------在lisi中可以运行select * from p_emp;select * from p_dept;实现查询
------创建序列
---drop table test_1
create table test_1
(
id number,
sname varchar2(20)
)segment creation immediate;
-----创建完表立即分配空间，避免插入第一条记录时，序列对象跳过第一个值
-----drop sequence seq_id;
create sequence seq_id
start with 1
increment by 1
minvalue 1
maxvalue 10
nocycle ----非循环
nocache;------不缓存

alter aequence seq_id maxvalue 20 cycle;---修改序列信息
insert into test_1 values(seq_id.nextval,'aaaaa');
insert into test_1 values(seq_id.nextval,'bbbbb');
insert into test_1 values(seq_id.nextval,'ccccc');
select * from test_1;
select seq_id.currval from dual;----查询当前信息
select seq_id.nextval from dual;----查询下一个　

-----表连接
select * from emp;
select * from dept;
select dname,empno,ename,job,sal from dept left join emp on dept.deptno=emp.deptno;
------视图
---drop view v_dept_emp;
---在system中执行grant create view to zhangsan1;
create view v_dept_emp
as 
  select dname,empno,ename,job,sal from dept
  left join emp on dept.deptno=emp.deptno;
  
select * from v_dept_emp;

create view v_dept_avgsal
as
  select dname,avg(sal)as avgsal from dept
  left join emp on dept.deptno=emp.deptno group by dname;

select * from v_dept_avgsal;

create view eno
as 
select deptno from emp where deptno=20;

-----索引  相当目录
---为了提高查询效率
create unique index index_ename on emp(ename);
select * from emp where ename='WARD';
-----组合索引
create index index_ename_empno on emp(ename,empno);
select * from emp where ename='WARD' and empno=7521;
----drop table depositor;
create  table depositor(
actid number(10) not null,
identity number(10) not null,
lastname varchar2(10) not null,
fristname varchar2(10) not null,
address1 varchar2(200) not null,
address2 varchar2(200),
address3 varchar2(200),
account number(10,2) not null);
select * from depositor;
select count(*) from depositor;
-----drop index ix_depor
create index ix_depor on depositor(identity);---创建普通索引
------1，创建唯一索引，会报错，因为主键上的oracle已经在此列创建
create unique index ix_depositor_actid on depositer(actid);

-----2,打开解释计划窗口执行下面的语句看消耗的资源
select * from depositor where lastname='Rowlands' and firstname='Rosario';

-----3打开解释计划窗口执行下面的语句看消耗的资源
select * from depositor where actid=58765;

-----4打开解释计划窗口执行下面的语句看消耗的资源
select * from depositor where lower(firstname)='jimmy';
------创建基于函数的索引，后再执行上述语句
create index ix_lower_fname on depositor(lower(firstname));

-----5打开解释计划窗口执行下面的语句看消耗的资源
select count(*) from depositor where firstname='Luke';
------创建位图索引，后执行上述语句
create bitmap index ix_bitmap_fname on depositor(firstname);
-----6查看数据字典中的索引信息
select * from user_indexes;
select * from user_ind_columns where table_name='depositor';

------------------第七章----PL/SQL语句-----------------------
 -----plsql语句块
 declare
      v_deptno constant number(2):=10;----constant常量，使数据不能修改
      v_dname varchar2(14);
 begin
     select dname into v_dname from dept where deptno=v_deptno;
     dbms_output.put_line('部门编号：'||v_deptno||'部门名称：'||v_dname);   
 exception     
     when others then dbms_output.put_line('错误信息：'||sqlerrm);
 end;
 -----圆的面积
 declare
    pi constant number(3,2):=3.14;-----number（整数，小数）
    r  number:=6;
    s number;
 begin
   s:=pi*r*r;
   dbms_output.put_line('圆的面积：'||s);
   
 exception      
     when others then dbms_output.put_line('错误信息：'||sqlerrm);
 end;
 -----数据类型
declare
    v_sal number;
begin
    select sal into v_sal  from  emp where empno=7934;
    IF  v_sal<1000  THEN
        update  emp  set  sal=sal+200  where empno=7934;
    ELSIF v_sal>=1000  and  v_sal<2000  THEN
        update  emp  set sal=sal+150 where empno=7934;
    ELSE
        update  emp  set  sal=sal+100 where empno=7934;
    END IF;
end;
----判断奇数还是偶数
declare
   v_num number;
begin
    v_num:=&num1;---实现手动输入
    if   mod(v_num,2)=0 then dbms_output.put_line(v_num||'这个数是偶数');
     else dbms_output.put_line('这个数是奇数'||v_num);
end if;
end;

 
---行属性
select * from emp;
declare
rw scott.emp%rowtype;
begin
  select * into rw from emp
  where empno=7369;
   dbms_output.put_line('姓名为'||ename);
    dbms_output.put_line('职位为'||job);
 end;


select * from emp;
----case语句
declare
  v_empno number;
  v_sal emp.empno%type;----列类型----%rowtype行类型
begin
  v_empno:=&v_empno;
  select sal into v_sal from emp where empno=v_empno;
  case 
    when v_sal<1000 then
      update emp set sal=sal+200  where empno=v_empno;
    when v_sal>=1000 and v_sal <2000 then
      update emp set sal=sal+150  where empno=v_empno;
    else
      update emp set sal=sal+100  where empno=v_empno;
    end case;  
end;

declare 
v_job varchar2(9);
v_empno number(4):=7369;
begin
  select into v_job from emp where empno=v_empno;
  case v_job
    when 'clerk'then
      update emp set sal=sal+200 where empno=v_empno;
       when 'dalesman'then
      update emp set sal=sal+300 where empno=v_empno;
        when 'analyst'then
      update emp set sal=sal+400 where empno=v_empno;
        when 'manager'then
      update emp set sal=sal-100 where empno=v_empno; 
     
      end case;
      end;
 declare
   n number;
   begin
     n:=&n;
     case 
       when n>0 then
          dbms_output.put_line('这是一个正数');
       when n<0 then
           dbms_output.put_line('这是一个负数');
       when n=0 then
           dbms_output.put_line('这是一个0');
      end case;
      end;
----循环执行的语句
create table test_loop(
id number(5),
name varchar2(10));
declare
 v_count number:=1;
begin
  loop
  insert into test_loop 
  values(v_count,'name'||v_count);
  v_count:=v_count+1;
  exit when v_count>100;
   end loop;
end;
select * from test_loop;

----练习：输出1-10的平方数
----loop循环
declare
  n number:=1;
 begin
   loop
     exit when n>10;
     dbms_output.put_line(n||'的平方数为'||n*n);
      n:=n+1;
     end loop;
 end;
 ----while循环
 declare
  n number:=1;
 begin
   whilel n<100 loop
     dbms_output.put_line(n||'的平方数为'||n*n);
      n:=n+1;
     end loop;
 end;
 -----for循环
 begin
   for v_count in 1..100  loop
      insert into test_loop      
      values(v_count,'name'||v_count);
   end loop;
 end;
 select * from test_loop;

 -----跳转goto
 -----异常
declare
	v_name emp.ename%type;
begin
	SELECT ename INTO  v_name FROM emp;
exception
	WHEN TOO_MANY_ROWS then
	dbms_output.put_line('该查询提取多行');
end;

declare
    v_empno  emp.empno%TYPE;
    v_emp emp%ROWTYPE;---行
begin
    
    v_empno:=&员工编号; --要求输入员工编号
    select * into v_emp from emp where empno=v_empno;
    DBMS_OUTPUT.PUT_LINE('编号是'||v_empno||'的员工姓名是'||v_emp.ename);
exception
       WHEN NO_DATA_FOUND THEN
           DBMS_OUTPUT.PUT_LINE('工作是'||v_empno||'的员工不存在！');
       WHEN OTHERS THEN
           DBMS_OUTPUT.PUT_LINE('错误号是'||SQLCODE);
           DBMS_OUTPUT.PUT_LINE('错误原因是'||SQLERRM);
END;

-----修改编号为1234员工的工资，工资加500
declare
v_empno number(4):=7369;
v_sal number;
begin
  select sal into v_sal from emp where empno=v_empno;
     update emp set sal=sal+500 where empno=v_empno; 
 exception      
   WHEN NO_DATA_FOUND THEN
           DBMS_OUTPUT.PUT_LINE('没找到数据');
 WHEN OTHERS THEN
           DBMS_OUTPUT.PUT_LINE('其他错误');
      end;   
  select * from emp;
-----游标
select * from emp;
declare
v_sal varchar2(20);
begin
     select sal into v_sal from emp where empno=7369; 
     IF SQL%NOTFOUND  THEN
                             DBMS_OUTPUT.PUT_LINE('编号未找到');
 	ELSE
		DBMS_OUTPUT.PUT_LINE('编号存在');
	END IF;
end;
---输出emp表中的员工编号，姓名，薪资
declare
cursor cursor_sel is select empno,ename,sal from emp order by sal;
c_empno emp.empno%type;
c_ename emp.ename%type;
c_sal  emp.sal%type;
begin
  open cursor_sel;
 loop
  fetch cursor_sel into c_empno,c_ename,c_sal;
  exit when cursor_sel%Notfound;
 dbms_output.put_line(c_empno||'  '||c_ename||' '||c_sal);
  end loop;
  close cursor_sel;
 end;
  
----使用游标行循环打印员工编号，姓名，工资
declare
cursor cursor_sel is select empno,ename,sal from emp order by sal;
r_emp cursor_sel%rowtype;--游标行

begin
  Open cursor_sel;
  Loop
    Fetch cursor_sel Into r_emp;
  exit when cursor_sel%Notfound;
 dbms_output.put_line(r_emp.empno||'  '||r_emp.ename||' '||r_emp.sal);
  end loop;
  close cursor_sel;
 end;
  
----使用带参数的显示游标循环打印员工编号，姓名，工资
declare
v_deptno emp.deptno%type;---部门编号变量
cursor cursor_sel(dno number) is
 select empno,ename,sal from emp where deptno=dno;
 r_emp cursor_sel%rowtype;---输入参数值


begin
  v_deptno:='&部门编号'; --输入参数值
  Open cursor_sel(v_deptno);--传递参数

  Loop
    Fetch cursor_sel Into r_emp;
  exit when cursor_sel%Notfound;
 dbms_output.put_line(r_emp.empno||'  '||r_emp.ename||' '||r_emp.sal);
  end loop;
  close cursor_sel;
 end;
   ----------------------第九章存储过程
 ----存储
 create or replace procedure p_happy
 as
 begin
   dbms_output.putline('hello');
 end p_happy;
 
 ----执行存储过程
 begin
   p_happy;
 end;

 ----存储
 create or replace procedure p_happy(v_input varchar2(20))
 as
 begin
   dbms_output.putline(v_input);
 end p_happy;
 
 ----执行存储过程
 declare 
 v_input varchar2(10):=&input;
 begin
   p_happy(v_input);
 end;
 
 ----
 create or replace procedure  
 find_empsal(v_empno number)
as
  v_ename varchar2(20);
  v_sal number;
begin
    select ename,sal into v_ename,v_sal from emp
    where empno= v_empno;
    DBMS_OUTPUT.PUT_LINE('雇员的姓名是：'||v_ename||' 工资是：'||v_sal);
   exception
      when NO_DATA_FOUND THEN
          DBMS_OUTPUT.PUT_LINE('雇员编号未找到');
END find_empsal;

------------------------
create or replace procedure
cp_inputtable(v_dname varchar2,v_loc varchar2)
as
begin
  insert into dept values(v_dname,v_deptno,v_loc);
  commit;---提交事务
  exception
    when others then
      dbms
     rollback;
end cp_inputtable;
 ---调用过程
declare
v_dname number;
v_deptno varchar2(20);
v_loc varchar2(20);
begin
  v_dname:=&dname;
v_deptno:=&deptno;
v_loc:=&loc;
 sp_dept_insert(v_dname,v_deptno,v_loc);
end;
----输出平均薪资
create or replace procedure sp_emp_avg(avg_sal out number)
as
begin
  insert into dept values(v_deptno,v_dname,v_loc);
  commit;
 ---执行
 declare 
  v_sal_avg number;
 begin
 end;
-----输入输出综合过程
create or replace procedure
find_empsal_out(v_empno in number,v_ename out varchar2,v_sal out number)
as
begin
  select ename,sal into v_ename,v_sal from emp where
  empno=v_empno;
  end find_empsal_out;
declare
v_ename varchar2(20);---员工姓名（输出）
v_sal number;
begin
     find_empsal_out(7935,v_ename,v_sal); --7035是输入参数
  dbms.out_put.put_line('员工姓名：'||v_ename||'  员工工资'||v_sal);
end;