sql汇总

========================高级查询========
select count(1) from emp;
select count(distinct job) from emp;
select sal,job from emp where sal=(select max(sal) from emp);
select job ,count(job) from emp group by job;
select deptno,count(1) from emp group by deptno 查询每个部门的员工总数
select deptno,max(sal),min(sal) from emp group by deptno 显示每个部门的最高工资和最低工资
按部门进行分组，然后再求出最高和最低工资
select deptno,job,max(sal),min(sal) from emp group by deptno,job
having 将分组以后的结果作为条件处理
显示每个部门的平均工资，筛选出平均工资高于2000的部门
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000
order by group by having顺序
group by having order by
abs sqrt power round concat  replace substr upper lower
to_date  to_char  add_months  lase_day months_between
sysdate  extract (截取)
oracle中两字符串链接用||
==============子查询===============
笛卡尔积
select * from dept,emp;
查询工资高于自己部门平均工资的员工的姓名和工资信息
select ename,sal from emp e1,
    (select deptno,avg(sal) dept_sal from emp group by deptno)avg
where e1.deptno=avg.deptno and e1.sal>avg.dept_sal;
连接查询
左外连接/右外连接
select s.name,se.subject,se.result from info s
left join
exam se on s.stuid = se.esuid;
左表是info，右表是exam，info是主表，exam是从表，左主右从，右主左从，从补主
左右外使用场景：防止丢失数据；例如对学生考试进行查询，假如没有有的学生没有参加考试，左表为学生表（主表），右表为成绩表（从表），
    则使用右外连接会把丢失没有参加考试的学生的数据，因为左表是主表，这里可用作面试经验
内连接（两表的交集）
select s.name, se.subject,se.result from info s
    inner join exam se on s.uid = se.esuid;
这条sql等同于
select s.name,se.subject,se.result from info s,exam
se where s.uid = se.esuid;
自连接
查询员工jack的上级领导的姓名
select a.name,a.massager,b.name from emp a ,emp b
    where a.massager =b.empno and a.name='jack'
递归查询（oracle特殊语句）
select * from emp start with empno=7655 connect by massager=prior empno;(根据主键empno
来查询这个人的所有上级领导)
select * from emp start with empno=7655 connect prior massager= empno;(根据主键empno
来查询这个人的所有下级领导)

oracle里面子查询：单行子查询，多行子查询，多列子查询，内联视图子查询
单行子查询：
查询与员工jack 属于同一部门的员工
select *  from emp where deptno=(select deptno from emp where name='jack')
多行子查询：
查询工作岗位与部门10的工作岗位相同的员工信息
select * from emp where job in(select job from emp where deptno=10)
这句sql可转为
select * from emp e1 where exists (select job from emp e2 where deptno =10
    and e1.job = e2.job)
多列子查询：
30.13
=========事务============
declare
    str varchar2(20);
begin
    
    str:="&hello world";//提示信息
    
    dbms_output.put_line(str);//输出
end;
===========锁======
create table t_tmp as select * from emp
set autocommit on
ddl会自动提交，dml需要手动提交
排他锁
悲观锁
乐观锁
共享锁

pl/sql编程
into 把查询出的结果放入变量中
declare
     v_emp number;
    pi number :=3.14;
    pi constant number :=3.14;
begin
    select empno into v_emp from emp where name='jack'
end;
 : 赋值
复合类型
%type引用变量和数据库列的数据类型

%rowtype 提供标示表中一行的记录类型
declare
    v_emp emp.empno%type;
begin
    select empno into v_emp from emp where name='jack';
    dbms_output.put_line(v_emp);
end;
条件判断
declare
    socre number;
    grade varchar2(10);
    
begin
    score :='&请输入';
    if score>=90 then
        grade:='A';
    elsif score>=80 then
        grade:='B';
    else
        grade:='不及格';
    end if;
    dbms_output.put_line(grade||score);
end;
declare
    v_emp1 emp%rowtype;
begin
    select * into v_emp1 from emp where empno=7369;
    dbms_output.put_line(v_emp1.empno||v_emp1.name);
end;

循环类型
loop
declare
    v_num number:=10;
begin
    loop
     v_num :=v_num -1;
     dbms_output.put_line(v_num);
     exit when vnum<3;
     end loop;
end;
while
for
declare
    v_num number:=0;
begin
    for i in 2..4 loop
     v_num := v_num +1;
    end loop;
    dbms_output.put_line(v_num);
end;

===============索引=========
where后的条件，order by ，group by 等过滤时，加上索引
联合查询，子查询等多表操作的时候，关联自动都加索引
创建索引： create index 索引名 on 表名（列名）
唯一索引：(创建条件：当前列值不相同)create unique index 索引名 on 表名（列名）

组合索引：（创建条件：当两个或者多个列经常一起出现在where条件时，可建立组合索引）
      create unique index in_produce on t_produce(name,type);

反向索引：多个用户对集中在少数数据块上的索引进行修改，容易引起资源的争用，建立反向索引
      create unique index in_produce on t_produce(price) reverse;
游标：用来循环获取数据
================存储过程=================

in 表示参数由外部传递进来
out 表示参数是可以通过存储过程赋值返回到外部
in out --引用类型

in的例子
create or replace procedure n_double(in_num in number,out_num out number)
as
begin
    out_num:= in_num*2;
    in_num:=30;
end;
调用：
declare
    vnum number :=20;
    vresult number;
begin
    n_double(vnum,vresult);
    dbms_output.put_line(vresult||'===='||vnum);
end;
调用结果：40===20;这说明调用in_num传进去的20被使用，而30没有被使用，in类型中在方法中赋值无效。而in out类型
相反，out是输出

create or replace procedure dept_avgsal(deptinfo in out varchar2,avgsal out number)
as
    dept_no number;
begin
    dept_no := to_number(deptinfo)--将string类型转int
    select dname into deptinfo from dept where depto=dept_no;
    select avg(sal) into avgsal from emp where deptno=dept_no;
end;
declare
    d_name varchar2(20);
    avg_sal number;
begin
    d_name :='&部门编号';
    dept_avgsal(d_name,avg_sal);
    dbms_output.put_line(d_name'||====||'avgsal);
end;
视图+游标+存储过程
--返回指定部门的员工信息：编号 姓名 部门编号 部门名称 工资 员工所在的部门的平均工资
--创建视图
create or replace view v_emp_info
as
select empno,ename,d.deptno,d.dname,e.sal,dv.dept_avg from emp e inner join
(select deptno,round(avg(sal)) dept_avg from emp group by deptno) dv
on e.deptno=dv.deptno
inner join dept d on dv.deptno=d.deptno;
select * from v_emp_info;
--创建存储过程
create  or replace procedure emp_info(dept_no in number,emp_cur out sys_refcursor)
is
begin
       open emp_cur for select * from v_emp_info where deptno=dept_no;
end;
--使用
declare
       type emp_cur is ref cursor;--声明动态游标类型
       tmp_cur emp_cur;--声明动态游标
       recrow v_emp_info%rowtype;
       dept_no number;

begin
  dept_no := '&部门编号';
  --调用存储过程
  emp_info(dept_no, tmp_cur);
  loop
    fetch tmp_cur
      into recrow; --提取游标的一行数据给recrow
    exit when tmp_cur%notfound; --当没有记录了则退出
    dbms_output.put_line(tmp_cur%rowcount || '-员工编号:' || recrow.empno ||
                         '，员工姓名：' || recrow.ename ||
                          ',部门编号：'||recrow.deptno||'，部门名称' ||recrow.dname||
                          '，工资：'||recrow.sal||
                          '，所在部门平均工资:'||recrow.dept_avg);
  end loop;
  if tmp_cur%isopen then
     close tmp_cur;
  end if;
end;
===================函数====
创建语法
create or replace function 函数名（参数）
return 表达式
is|as
begin
end；
例1：
create or replace function fn_sum (num1 number,num2 number)
return number
as
    num_sum number;
begin
    num_sum := num+num2;
    return num_sum;
end;
调用
select fn_sum(10,20) from dual;
例2
create or replace function fn_avg(dept_no number) return number
is
    avgsal number;
begin
    select avg(sal) into avgsal from emp where deptno=dept_no;
    return avgsal;
end;
select fn_avg(10) from dual;
存储过程和函数的区别?
注意：赋值时:和=之间不能有空格