华南俳烁实业有限公司

數(shù)據(jù)庫的其他對象

user_tables

user_objects

select distinct object_type

from user_objects;

table 表

view  視圖

index 索引

sequence 序列

PL/SQL程序塊:

function 函數(shù)

procedure 過程

package 包 (package和package body)

trigger

synonym: 同義詞

database link

1.視圖 view

--sql(select)語句查詢結果的映像

create view v_emp_num

as

select deptno, count(*) num

from emp_ning

where deptno is not null

group by deptno

order by deptno;

select * from v_emp_num;

create or replace view v_emp

as

select empno, ename, job, deptno

from emp_ning;

create or replace view 視圖名

as

SQL語句;

select * from v_emp;

--隱藏數(shù)據(jù)

--簡化查詢

--視圖中不包含數(shù)據(jù),只是基表的映像.

create or replace view top_stu

as

select student_name, total_score

    from (

      select stu.student_name,

           sum(per.test_score) total_score

      from t_student_ning stu join t_performance_ning per on stu.student_id = per.student_id

      where stu.class_id = 1

      group by stu.student_name

      order by total_score desc)

    where rownum < 2;

--創(chuàng)建視圖,內容是每個部門的編碼,名字,位置和在這個部門工作的員工人數(shù).

select * from v_emp_count;

deptno dname       location  num

---------------------------------

10    developer    beijing    5

20    finance      shanghai   4

30.....

create or replace view v_emp_count

as

select d.deptno, d.dname, d.location,

       count(e.empno) num

from emp_ning e join dept_ning d

on e.deptno = d.deptno

group by d.deptno, d.dname, d.location;

create or replace view emp_count

as

select deptno, count(*)  num

    from emp_ning e

    group by deptno;

--另一種實現(xiàn)語法:

create or replace view v_emp_count

as

select  d.deptno, d.dname, d.location, x.num

from dept_ning d join (

    select deptno, count(*) num

    from emp_ning e

    group by deptno) x

on d.deptno = x.deptno;

2.索引 Index

全表掃描 Full Table Scan (FTS)

select * from student

where name = 'zhangsan';

名字             地址

zhangsan        0xABCD

lisi            0xEEFF

名字             位置

Java編程思想    三層211號架

天龍八部        二層467號架

....             .....

1)如果某個列建立PK約束條件,索引自動建立.

create table student_ning6(

id number primary key,

name char(10)

);

--查找主鍵約束的名字

 select constraint_name

 from user_constraints

 where table_name = 'STUDENT_NING6';

SYS_C00658255

--索引自動創(chuàng)建, 查找索引名字

select index_name from user_indexes

where table_name = 'STUDENT_NING6';

SYS_C00658255

insert into student_ning6 values(1,'peter');

insert into student_ning6 values(2,'tom');

索引中有如下結構的數(shù)據(jù):

id    地址

-------------

1    0XABCD

2    0X2345

--如果按id查找,自動使用索引

select * from student_ning6 where id = 2;

--如果按id以外的列查,不會使用索引.

select * from student_ning6 where name = 'tom';

2)手動創(chuàng)建索引

--在經(jīng)常做查詢的列上手動創(chuàng)建索引

create index idx_stu6_name

on student_ning6(name);

索引中有如下結構的數(shù)據(jù):

id       地址

----------------

peter    0XABCD

tom      0X2345

jerry    0X68EF

select * from student_ning6 where name='tom';

索引對查詢有幫助,對DML操作是阻礙作用.

索引由Oracle Server自動維護.

drop index idx_stu6_name;

3.序列 Sequence

create sequence seq_ning;

insert into student_ning6

values(seq_ning.nextval, 'peter');

drop sequence seq_ning;

create sequence seq_ning start with 1000

increment by 10;

select seq_ning.nextval from dual;

select seq_ning.currval from dual;

nextval = next value

currval = current value

--Mysql主鍵自增長

create table student_ning

(id int primary key auto_increment,

name char(10)

);

insert into student_ning(name)

values('peter');

id      name

---------------

1       peter