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;

2. 序列(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;

3. 索引(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);

4. 同义词(synonym)

   create synonym d for departments;
   select * from d;

5. 表(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)