一、数据库对象：表(table) 视图(view) 序列(sequence) 索引(index) 同义词(synonym)

  1. 视图(view) : 存储起来的 select 语句

    create view emp_vu5
as
select employee_id, last_name, salary
from employees
where department_id = 90;

select * from emp_vu5;

select * from employees;

update emp_vu5
set last_name = 'King'
where employee_id = 100;

–复杂视图:只能查询，不能增删改

create view emp_vu6
as
select department_id, avg(salary) avg_sal
from employees
group by department_id;

select * from emp_vu6;

update emp_vu6
set avg_sal = 10000
where department_id = 100;

  1. 序列(sequence) ：用于生成一组有规律的数值。(通常为主键设置值)

    create sequence emp_seq4
   start with 1
   increment by 1
   maxvalue 100000
   nocache
   cycle;

select emp_seq4.currval from dual;
select emp_seq4.nextval from dual;

–序列的问题：裂缝 1). 多个数据库对象使用同一个序列。 2). rollback 3). 发生异常

insert into emp(id, name)
values(emp_seq4.nextval, '张三');

select * from emp;

  1. 索引(index) ：用于提高查询效率
    –自动创建：数据服务器会为具有唯一约束(主键约束，唯一约束)的列自动创建索引

    create table emp2(
    id number(10) primary key,
    name varchar2(30)
);

–手动创建:

create index emp2_name_idx
on emp2(name);

–创建联合索引

create index emp2_nameId_idx
on emp2(id, name);

  1. 同义词(synonym)

    create synonym d for departments;
select * from d;

  2. 表(table)
    DML : 数据操纵语言

insert into ... values ...
delete from ... where ...
update ... set ... where ...

select …组函数(MAX/MIN/AVG/SUM/COUNT)
from… (内连接 join…on… 左外连接：left join…on… 右外连接: right join…on… 满外连接：full join…on…)
where… between…and…/in(…, …, …)/like/is (not) null
group by 出现在 select 子句中的非分组函数，一定出现在 group by 子句后
having 过滤组函数
order by … asc(升序)-默认 desc(降序)

DDL : 数据定义语言(create table/alter table/drop table/rename…to…/truncate table)
DCL : 数据控制语言(commit; rollback; grant…to…/ revoke)

版权声明：本文为androidsuperman原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://www.cnblogs.com/androidsuperman/p/10481183.html