数据库的其他对象

user_tables

user_objects

select distinct object_type

from user_objects;

table 表

view  视图

index 索引

sequence 序列

PL/SQL程序块:

function 函数

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;

--隐藏数据

--简化查询

--视图中不包含数据,只是基表的映像.

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;

--创建视图,内容是每个部门的编码,名字,位置和在这个部门工作的员工人数.

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;

--另一种实现语法:

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

--索引自动创建, 查找索引名字

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');

索引中有如下结构的数据:

id    地址

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

1    0XABCD

2    0X2345

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

select * from student_ning6 where id = 2;

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

select * from student_ning6 where name = 'tom';

2)手动创建索引

--在经常做查询的列上手动创建索引

create index idx_stu6_name

on student_ning6(name);

索引中有如下结构的数据:

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