sql笔记

数据库对象：表 视图(view 建立在表上) 序列(sequence) 索引(index) 同义词(synonym)

sql:dml ddl  dcl

创建和管理表(ddl不可回滚)
 创建表
  create table table_name(
  id number(10),
  name varchar2(15),
 )

 create table table_name_01
     as 
     select * from table_name

     管理表
     添加列
     alter table table_name
    add(email varchar2(20))
    修改列
    alter table table_name 
    modify(salary number(10) default 1000)
    重命名一个列
    alter table table_name
    rename column salary to sal;
    删出一个列
    alter table table_name
    drop column email;
    清空表
    truncate table table_name;

    删除表
    drop table table_name

    重命名表
    rename table_name to table_name_01

数据管理(dml:insert delete update select可以回滚 )
(dcl；commit rollback savepoint)
    添加一条数据
    insert into table_name () values ()
    修改数据
    update table_name set salary=1000
    查询数据
    select * 
    from table_name 
    where ... 
    group by ...
    having...
    order by ... desc(asc)

#部门最低的平均工资的这个部门的信息  
select * from department where department_id=
(
select department_id from employee 
where salary=(
select min(avg(salary)) from employee
group by department_id
)
)