oracle数据库总结

九大子查询

.1FORM子查询

1.1.1结构

列：select * from(select * from students);

1.1.2用法

数据挖掘

列:select * from(

Select * from(

Select * from user where sex=’女’ )

Where age between 15 and 18) where city=’佛山’;

1.1.3 区别

Mysql:必须取别名  oracle:可以不取

1.1.4 特性:虚表

Select * from #Students(#代表虚表)

Select * from students;

1.2select 子查询

1.2.1结构：

Select (select count(‘x’) from teachers) te,(select count(‘x’) from students) st from dual;

1.2.2用法 数据统计

1.2.3区别：mysql版：没有from子句

Select (select count(1) from teachers) te,(select count(1) from students) st;

1.3where子查询

1.3.1例：

Select * from teachers where id>(select id from teachers where name=”张三”);

1.4exists子查询(oracle不支持，但可以改为存储过程)

1.4.1结构：

If exists(select * from student where score>60)

Update teachers set sal=sal-500 where name =’张三’;

1.4.2  not exists优化

1.5  in子查询

1.5.1结构:

Select * from teachers where city in (select city from teachers where  name=’张三’);

1.6多行多列子查询

16.1结构

Select name,age,sal from teachers where (sal,age) in(select max(sal),age from teachers group by age);

1.7all极限子查询

17.1结构

查询所有工资大于平均工资的教师信息

Select *  from teachers where sal>all(select avg(sal) from teachers  group by id);

1.8 any反极限子查询

Select *  from teachers where sal>any(select avg(sal) from teachers  group by id);

与all极限子查询的区别:查询所有工资大于平均工资的教师信息，

但是第一条信息不会显示

 

1.9having (效率低下，不建议使用)

2、集合操作

2.2

Union（并集，会去重）、union all（并集，不会去重）、intersect（交集）、minus（差集）。

并集，会去重，效率低

Select name from students

Union

Select name from teachers;

并集，不会去重

Select name from students

Union all

Select name from teachers;

交集

Select name from students

Intersect

Select name from teachers;

差集

Select name from students

minus

Select name from teachers;

2.3用法

查询列名可以不同，但是数据类型必须要相同

查询列数一定要一样，当下表查询列数不同于上表时，可用null或者自定义列名补齐；

列属性不同时可使用转化函数

Select name,”在提升” from students

Union

Select name,to_varchar2(sal) from teachers;

3、连接

3.1内连接

3.1.1

Sqlserver版:

Select A.*,B.*’ from A inner join B on A.id=B.id;

3.1.2

Oracle版

Select A.*,B.*’ from A , B where A.id=B.id;

3.2外连接

3.2.1 左外连接

 Select A.*,B.*’ from A left join B where A.id=B.id;

 

 Select A.*,B.*’ from A left outer join B where A.id=B.id;

 

 Select A.*,B.*’ from A , B where A.id=B.id(+);

3.3全连接

3.3.1 Sqlserver版（交叉连接）

Select A.*,B.* from A cross join B on A.id=B.id;

3.3.2Oracle版:

 (1)Select A.*,B.* from A , B where A.id=B.id(+)

Union

Select A.*,B.* from A,B where A.id(+)=B.id;

 (2)

Select A.*,B.* from A full outer join B where A.id=B.id;

3.4不等连接

3.4.1

SELECT student_id, score, grade

  FROM Students_grade sg, Grades g

WHERE sg.score BETWEEN g.low_score AND g.high_score;

     3.5自连接

3.5.1

SELECT s1.student_id, s1.name AS 学生名, s1.monitor_id, s2.name AS 班长名

  FROM Students s1, Students s2

WHERE s1.monitor_id = s2.student_id(+);

其中的s1和s2其实是一张表；

4、储存过程

Set serverout on :输入此语句之后的Dbms_output.put_line();语句才会生效

Declare

//定义变量

A number(5):=9;//基本类型

A teachers%rowtype;//对象类型

A teachers.sal%type;//半对象类型

Begin

A:=5 //赋值         

Select sal into A from teachers where name=’张三’;

If(A<5000)

Then

Update teachers set sal=sal+500 where name=”张三”;

else

update teachers set sal=sal-300 where name=”张三”;

end if

commit;

DBMS_OUTPUT.PUT_LINE（”操作完毕”||A）;

End;

4.2选择结构

4.2.1 if结构

If(条件)

Then…………

Else if…………

Else…………

End if;

4.2.2case结构

Case a

When 1 then……

When 2 then……

Else……  //在case结构当中必须要有

End case;

4.3循环结构

4.3.1 loop死循环

Declare

i  number:=0;

sum1 number:=0;

begin

loop

i:=i+1;

sum1:=sum1+I;

exit when i=100;

end loop;

DBMS_OUTPUT.PUT_LINE（num1）;

4.3.2 while循环

Declare

Avgsal teachers.sal%type;

Begin

Select avg(sal) into avgsal from teachers;

While(avgsal<4000)

Loop

Update teachers set sal=sal+100;

Select avg(sal) into avgsal from teachers;

End loop;

Commit;

End;

4.3.3for循环

Declare

Sum1 number:=0;

Bdgin

For i  in 1..100

Loop

Sum1=sum1+I;

End loop;

End;

5.带参数的储存过程

 Create or replace procedure pro_aa(name1 teachers.name%type)

As

Gongzi teachers.sal%type;

Begin

Select sal into gongzi from teachers where name=name1;

DBMS_POUPUT.PUT_LINE（gongzi）;

End;

执行：execute pro_aa(“张三”);

Java:call pro_aa(“张三”);

5.1带返回值的方法

Create or replace function pro_aa(name1 teachers.name%type)

Return teachers.sal%type;

is

Gongzi teachers.sal%type;

Begin

Select sal into gongzi from teachers where name=name1;

Return gongzi;

End;

执行：select pro_aa(“张三”) from dual;

6、全局变量、隐式游标

 %notfound 询问是否没有结果集
     %found    询问是否存在结果集
     %rowcount 返回受影响的行数
     %isopen   询问游标是否已经打开

 SQL%notfound  --返回Boolean值  存在结果集返回 False
     SQL%found    --返回Boolean值   存在结果集返回 True
     SQL%rowcount  --用户成功提取数据的行数
     SQL%isopen   --在隐式游标里一般这个属性是自动打开和关闭的.且任何时候查询都返回False;

Begin

Delete from teachers where sal>5000;

If(sql%rowcount>10)

Then

Rollback;

Delete from teachers where sal>8000;

End if;

Commit;

End;

7、函数

7.1时间函数

Select current_date from dual;//返回系统时间

Select sysdate from dual;//返回系统时间，和current相比格式更简单

Select current_timestamp from dual;//返回系统的时间戳

Select add_months(sysdate,5) from dual;//在系统时间上增加5个月；

Select months_between(date1,date2) from dual;//用于计算date1与date2之间相隔几个月；

Select next_day(sysdate,”星期三”) from dual;//返回距离系统时间下一次星期三的日期

更多时间函数请百度

7.2数字函数

Avg(score)//返回score的平均数

Sum(score)//返回score的总和

Count(score)//返回有score列的列数

Abs(-1)//返回-1的绝对值

     ROUND(n,n2) //将参数n按照n2指定的小数位进行四舍五入.不指定n2时默认n2为0,即近似到个位.如果第2个参数为负数时将对小数点左边的数作四舍五入,会入到左面的|n|+1位.

FLOOR(n)
返回小于或等于参数的最大整数. 参数为数值型值或可以隐式地转换为数值型值的值.

如floor(5.9)//返回5

7.3字符函数

1.LENGTH(arg1)//返回arg1的字符长度

 

2.INSTR(arg1,to_find,pos,occurrence)
在arg1中查找to_find子字符串,并返回一个整数表示它出现的位置.参数pos指定在参数arg1中开始搜索的位置.如果pos为正,则在该数字指定的位置开始搜索.如果pos为负,则在该数字指定的位置开始搜索,但位置指定是从字符串结尾处开始的.并且反向搜索.参数occurrence指定搜索第几次出现的了字符串.

例：--从左面第1个字符开始搜索,第一次出现ABC的位置
select instr('HABCHABC','ABC',1,1) from dual;//返回2

3 CONCAT(arg1,arg2)
返回arg1与arg2的拼接结果.等同于arg1||arg2；

4. SUBSTR(arg1,pos.len)
从arg1中指定的位置pos开始向右侧截取指定长度len的子字符串.如果pos为正则从字符串arg1左侧开始计数,如果为负则从字符串右侧开始计数.
如果未指定len,则返回至该字符串从位置pos开始到串尾的子串.

7.4分析函数

1.row_number

返回一个唯一的值，当遇到相同数据时，排名按照记录集中记录的顺序依次递增

2.dense_rank

返回一个唯一的值，当遇到相同数据时，所有相同数据的排名都是一样的。

3.rank

返回一个唯一的值，当遇到相同数据是，所有相同数据的排名都是一样的，同时会在最后一条相同的记录个下一条不同记录的排名之间空出排名。

7.5其他函数

Nvl（exp1,exp2）//如果exp1的值为null，则返回exp2的值；否则返回exp1的值；

Nvl（exp1,exp2，exp3）//如果exp1的值为null，则返回exp3的值；否则返回exp2的值；

8、操作oracle数据库

8.1创建表空间

Create tablespace db_data

Datafile ‘filename’ [size integer[k|m]]

[autoextend][off|on];

Datafile:指定组成表空间的一个或多个数据文件，当有多个数据文件时使用，分割。

Filename：数据文件的路径和名称

Size：指定文件的大小，用k指定千字节大小，用m指定兆字节大小

Autoextend用来启用或禁用数据文件的自动扩展，设置为on则自动增长，off则反之；

8.2用户操作

8.2.1创建用户

Create user 用户名

Identified by 密码

Default tablespace 表空间名 //指定默认表空间

8.2.2查询用户/表空间

Select * from dba_users/dba_tablespaces;

修改用户密码

Alter user 用户名 identified by 新密码;

删除用户

Drop user 用户名 cascade;

8.2.3给用户授权

Grant  dba to 用户名://广泛授权

Grant resource to 用户名;//普通用户授权

Grant connect to 用户名；//临时用户授权

Grant create table to 用户名；//给用户创建表的权限

Grant create procedure to 用户名；//给用户名创建储存过程的权限

Grant select any table to 用户名；//给用户名查询所有表的权限

Grant delete any table to 用户名；//给用户删除所有表的权限

Grant select on teachers to 用户名；//给用户查询teachers表的权限

8.2.4收回授予的权限

Revoke select on teachers from 用户名；//收回用户查询teachers表的权限

Revoke delete any table from 用户名；//收回用户删除所有表的权限

8.3表操作

8.3.1创建序列

建立序列命令

    CREATE SEQUENCE [user.]sequence_name

    [increment by n]

    [start with n]

    [maxvalue n | nomaxvalue]

    [minvalue n | nominvalue];

    INCREMENT BY： 指定序列号之间的间隔，该值可为正的或负的整数，但不可为0。序列为升序。忽略该子句时，缺省值为1。

    START WITH：指定生成的第一个序列号。在升序时，序列可从比最小值大的值开始，缺省值为序列的最小值。对于降序，序列可由比最大值小的值开始，缺省值为序列的最大值。

    MAXVALUE：指定序列可生成的最大值。

    NOMAXVALUE：为升序指定最大值为1027，为降序指定最大值为-1。

    MINVALUE：指定序列的最小值。

    NOMINVALUE：为升序指定最小值为1。为降序指定最小值为-1026。

删除序列：

Drop sequence 序列名;

8.3.2

复制表

Create table 表名 as select * from 被复制表名（复制结构和数据）

Create table 表名 like select * from 被复制表名（只复制表结构）

Insert into 表名 select  * from 被复制表名（两表结构一样时复制数据）

Insert into 表名（列1，列2……） select 列1，列2……from 被复制表名 pasting（两表结构不一样时复制数据）

8.3.3修改表

添加列

Alter table 表名 Add(列名 数据类型);

删除列

Alter table 表名 drop(列名)；

修改列

Alter table 表名 modify（列名，数据类型）;

增加一个主键约束

Alter table 表名 add constraint 约束名 primary key（列名）

增加一个外键约束

Alter table 从表名 add constraint 约束名 FOREIGN KEY（充当外键的列名）references 主表名（与外键连接的列名）

修改表名

Rename 表名 to新表名；

修改列名

Alter table 表名 rename 旧列名 to 新列名；

修改同义词

Create or  replace （public公有同义词）synonym 同义词名 for 表名

去重查询

Select distinct name from teachers；

默认数据库

scott.emp   、scott.dept

8.3.4分页显示

Select rowed,rownum,name from teachers;

Select * from(select A.*,rownum rn from (select * from teachers) A where rownum>9)where rn>=6;

8.4.1索引

(1)B树索引创建

Create index 索引名 on 表名(列名)；

(2)位图索引创建

Create bitmap index 索引名 on 表名(列名);

(3)反向键索引创建

Create index 索引名 on 表名（列名）reverse;

8.4.2权限查询

查看用户权限

Select * from user_sys_privs;

select * from all_tab_columns –查询所有用户的表的列名等信息(详细但是没有备注). 
select * from user_tab_columns –查询本用户的表的列名等信息(详细但是没有备注).

查看某张表索引

Select * from user_indexes where table_name=’表名’;

查看某张表视图

Select * from user_views where table_name=’表名’;

9、触发器

9.1普通触发器

9.1.1.

删：

create or replace trigger tri_aa

before delete on teachers

begin

  raise_application_error(-20403,'敢删我表！');

end;

9.1.2

改：

create or replace trigger tri_aa1

before update on teachers

begin

  raise_application_error(-20403,'不能修改噢！');

end;

9.1.3

增：

create or replace trigger tri_aa1

before insert on teachers

begin

  raise_application_error(-20403,'不能增加数据！');

end;

9.2时间触发器

create or replace trigger tri_aa2

before update on teachers

begin

  if(to_char(sysdate,'HH24')not between 9 and 17)

   then

   raise_application_error(-20444,'下班啦！');

  end if;

end;

9.3数据触发器

create or replace trigger tri_aa4

before update of wage on teachers

for each row

begin

  if(abs(:new.wage-:old.wage)>0.2*:old.wage)

   then

     raise_application_error(-20233,'修改幅度过大！');

  end if;

end tri_aa4;

9.4通过触发器来实现自增长列

create or replace trigger tri_aa6

before insert on goods for each row

begin

  :new.id:=s2.nextval;

end tri_aa6;

9.5通过触发器来实现自动备份（热备份）

create or replace trigger tri_aa7

after update on goods

for each row

  begin

    insert into

    tt values(:new.id,:new.name,:new.price,sysdate,:old.price);

  end tri_aa7;

10.异常

10.1系统异常（运行时异常）

declare

  a number :=6;

  b number :=0;

  c number :=0;

begin

  c:=a/b;

  Exception

    when ZERO_DIVIDE THEN

      DBMS_OUTPUT.PUT_LINE('除数为0');

    when others then

      DBMS_OUTPUT.PUT_LINE('其他异常！');

end;

10.2自定义异常

declare

 err_or Exception;

 gongzhi Teachers.wage%type;

begin

  select wage into gongzi

    from teachers where name='王彤';

    if(gongzi>4000)

    then

      raise err_or;

    end if;

    Exception

      when err_or then

        DBMS_OUTPUT.PUT_LINE('公司违法');

end;

11、游标

11.1普通游标

declare

  cursor cur_aa

  is select wage from teachers;

  gongzhi teachers.wage%type;

begin

  open cur_aa;

  loop

    fetch cur_aa into gongzhi;

    exit when cur_aa%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(gongzhi);

  end loop;

  close cur_aa;

end;

11.2对象游标

declare

cursor cur_aa2 is

select * from teachers;

begin

  for tea in cur_aa2

    loop

      DBMS_OUTPUT.PUT_LINE(tea.name||tea.wage);

    end loop;

end;

11.3参数游标

declare

cursor cur_aa3(bt teachers.title%type) is

select wage from teachers where title=bt;

gongzhi teachers.wage%type;

begin

  open cur_aa3('教授');

  loop

    fetch cur_aa3 into gongzhi;

    exit when cur_aa3%notfound;

    DBMS_OUTPUT.PUT_LINE(gongzhi);

  end loop;

  close cur_aa3;

end;

11.4对象参数游标

declare

  cursor cur_aa4(chengzhi teachers.title%type)

  is select * from teachers

  where title = chengzhi;

begin

  for tea in cur_aa4('教授')

    loop

      DBMS_OUTPUT.PUT_LINE(tea.name);

      DBMS_OUTPUT.PUT_LINE(tea.wage);

    end loop;

end;

11.5修改游标

declare

cursor cur_aa5

is select title from teachers for update;

zhicheng teachers.title%type;

begin

  open cur_aa5;

  loop

  fetch cur_aa5 into zhicheng;

  exit when cur_aa5%notfound;

  case zhicheng

    when'教授'

      then update teachers set wage=wage*1.2

        where current of cur_aa5;

     when'副教授'

      then update teachers set wage=wage*1.1

        where current of cur_aa5;

     else

        update teachers set wage=wage*1.5

        where current of cur_aa5;  

  end case;

  end loop; 

  commit;

  DBMS_OUTPUT.PUT_LINE('修改成功！');

  close cur_aa5;

end;