Sql知识点总结

一、数据库对象:表(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)

posted @ 2019-03-06 09:07 西北野狼 阅读(...) 评论(...) 编辑 收藏