Sql知识点总结

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

视图(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;

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

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

同义词(synonym)

create synonym d for departments;

select * from d;

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

巴特西

Sql知识点总结

最新文章

热门文章