sql语句-oracle数据库

有A,B,C三张表.

表操作:

1.建表:create table A (id varchar2(20),name varchar2(20),age number(4),salary number(10,2),dept varchar2(20));

3.重命名表: alter table A rename to new_A;

2.删表:drop table A;

3.新增字段: alter table A add sex char(5);

 修改字段长度:alter table A modify (sex char(6));//注:改后的长度只能比原来的大,不能比之小.

 修改字段名:  alter table A rename column sex to sex_new;

 删除字段:alter table A drop column sex;

4.复制表: create table A_copy as select * from A;

 复制表结构: create table A_simple as select * from A where 1 = 0;

5.增加主键: alter table A add constraint pk_id primary key (id);

 删除主键: alter table A drop constraint pk_id;

sql基础:

1.查: select * from A;

2.增: insert into A (id,name) values(1,'张三');

3.删: delete from A where id = 1;

4.改: update A set name = '李四' where id = 1;

select 语句之组函数:

1.最大:select max(salary),name from A  where dept = '软件事业部' group by name.

2.最小:select min(salary),name from A  where dept = '软件事业部' group by name.

3.平均:select avg(salary)    from A  where dept = '软件事业部' group by dept;

4.对分组结果进行处理:

 select max(salary),name from A where dept = '软件事业部'  group by name having max(salary) > 5000;

表关联:

1.内连接:

1.select A.id,A.name,B.hight from A inner join B on A.name = B.name where A.id < 5;

//也可写成  select A.id,A.name,B.hight from A inner join B on A.name = B.name and A.id < 5;

(理论上,and要比where执行效率高,因为先走and,再走where)

1.1.select A.id,A.name,B.hight from A,B where A.name = B.name and A.id < 5

(这是一个多表查询语句,等价于内连接1.一般常用这个多表查询语句,而不用上面的内连接.)

2.外连接:

左外: select A.id,A.name,B.hight from A left join B on A.name = B.name where A.name like '李%'; 

右外: select A.id,B.name,B.hight from A right join B on A.name = B.name where B.name like '李%'; 

全外: select A.id,A.name,B.hight from A full join B on A.name = B.name where A.name like '李%';

3.自连接:

select t1.id,t1.name,t2.age from A t1,A t2 where t1.id = t2.age (本sql仅做语法示例,没有任何实际意义); 

with as语句 : 简化代码,优化执行效率

with zb as

(select * from A where id>1 and name like '李%') 

select * from zb where age = (select max(age) from zb)

条件判断语句: case when ,decode

case when 语句

select id, name,salary as 涨前薪资,
       case
         when age <= 15 then
          salary + 500
         when age >= 16 then
          salary + 600
       end 涨后薪资

  from A

//注意: age<=15  会比 age<16 执行效率高.  因为<=15,会直接拿到15操作,而<16则要先拿到16再向前取一位进行操作.

decode 函数

select id,
       name,
       age,
       salary as 涨前薪资,
       decode(sign(age-15),-1,salary+500,0,salary+500,1,salary + 600) 涨后薪资
  from A

//decode函数等价与case when ,但是不能直接用 <,>.可以用sign函数来解决

存储过程:

1.无参.

create or replace procedure sayhelloworld

is

begin

dbms_output.put_line('hello World');

end;

2.带输出参数

create or replace procedure inform(ename in varchar2,

                                                      esalary out number,

                                                      eage out number)

is 

begin

select salary,age into esalary,eage from A where name = ename;

end;

3.光标(应用于返回对象类型)

第一步,创建包(此处是包头)

create or replace package mypackage is

--将光标转为返回实体

type empcursor is ref cursor;

--声明一个过程

procedure queryEmpList(ename in varchar2, empList out empcursor);

end mypackage;

第二部,创建包体,并返回结果

create or replace package body mypackage is

--将包头中的过程拿出来

procedure queryEmpList(ename in varchar2,empList out empcursor) is

begin

打开光标实体

open empList fro select * from emp where name = ename;

end mypackage;

3.loop 循环

create or replace procedure vvbs is
cursor emps is select id,name from A;
new_id A.id%type;
new_name A.name%type;
begin
 open emps;
   loop
   fetch emps into new_id,new_name;
    exit when emps%notfound;
       update A set name = regexp_replace(dwbm,'[A-Z]','A') where id = new_id;
    end loop;
 close emps;
end vvbs;

4.在过程中使用数组

create or replace procedure vvb is
cursor emps is select id,name from A;
new_id A.id%type;
new_name A.name%type;
type array  is varray(7) of varchar2(4);
zm array:=array('A','B','C','D','E','F','G');
i number(4);
begin
   i:=1;
 open emps;
   loop
   fetch emps into new_id,new_name;
    exit when emps%notfound;
       i:=i+1;
       for j in 1..zm.count loop
          if new_name like'%李%'and mod(i,7)=j then update A set name = replace(name,'李',zm(j)) where id = new_id;
          exit when mod(i,7)=j;
          end if;
        end loop;
    end loop;
 close emps;
 commit;

end vvb;

触发器:

1.禁止在周末和非工作时间插入员工信息
create or replace tigger check1
before insert
on A
declare
begin
if to_char(sysdate,'day') in ('Saturday','Sunday')
   or
   to_number(to_char(sysdate,'hh24')) not between 9 and 18;
   --禁止insert新员工
   raise_application_error(-20001,'禁止在非工作时间插入新员');
end if;
end; 

2.行级触发器

*工资不能越涨越少(此处是行级触发器)
:old 表示操作之前的值，:new表示操作之后的值
create or replace tigger check2
before update
on A 
for each row 
declare
begin
if :new.sal<:old.sal then
   raise_application_error(-20002;"涨后的薪水不能少于涨前的薪,涨前的薪水:'+'||:old.sal'涨后的薪水'+||:newsal");
end if;
end