1. 前置说明
1.1 版本说明
1.2 名称约定
table 表 table_name 表名 column_name 列名 column_value 列值 constraint 约束 constraint_name 约束名 index 索引 index_name 索引名 view 视图 view_name 视图名
2. 数据
2. 1 查询
select * from table_name where column_name = column_value;
select column_name1, column_name2 from table_name where column_name1 = column_value1;
2.2 新增
insert into table_name values ( column_value1, column_value1) ;
insert into table_name ( column_name1, column_name2) values ( column_value1, column_value2) ;
2.3 修改
update table_name set column_name1= column_value1, column_name2= column_value2 where column_name = column_value;
2.4 删除
delete from table_name where column_name = column_value;
delete from table_name;
truncate table_name;
3. 字段
3.1 增加
alter table table_name add column_name varchar null ;
alter table table_name add column_name varchar not null ;
3.2 删除
alter table table_name drop column column_name;
alter table table_name rename to table_name1;
alter table table_name alter column column_name type varchar ( 100 ) ;
alter table table_name alter column column_name set default 'example_char' ;
comment on column table_name. column_name is '字段描述' ;
4. 约束
4.1 新增
alter table table_name add primary key ( column_name1, column_name2) ;
alter table table_name add constraint constraint_name primary key ( column_name) ;
alter table table_name add constraint table_name_un unique ( column_name) ;
alter table table_name public . table_name add constraint constraint_name check ( column_name != 'admin' ) ;
4.2 删除
alter table table_name drop constraint if exists constraint_name;
5. 索引
5.1 新增
create index index_name on table_name ( column_name) ;
create unique index index_name3 on table_name ( column_name1, column_name2) ;
5.2 删除
drop index public . index_name;
6. 视图
6.1 新增
create view view_name as
select column_name from table_name where column_name= '1' ;
6.2 删除
drop view view_name;