postgresql

建表sql(不同类型)：

create table tableName (
  id numeric(20) primary key,
  numone numeric(20) DEFAULT 0,
  photo varchar(255) DEFAULT null,
  photo_path text DEFAULT null,
  time timestamp DEFAULT null
);

操作语句：

insert into test (id,name,age) values(3,'c','14');

update test set numone=2, numtwo=1 where id=2;

select (sum(numone)/sum(numtwo)) a from test;

select * from information_schema.schemata;查看所有schema

执行sql文件：

psql -U ducetech -d du -f *.sql

登陆数据库
psql -U userName -d datebaseName
创建数据库
psql -U postgres -q -c "CREATE DATABASE datebaseName;"
psql -U postgres -q -c "CREATE USER userName WITH PASSWORD 'userPwd';GRANT ALL PRIVILEGES ON DATABASE datebaseName TO userName;"
psql -U userName -d datebaseName -q -c "CREATE SCHEMA schemaName;"
psql -U userName -d datebaseName -q -c "GRANT ALL PRIVILEGES ON SCHEMA schemaName TO userName;"

在某数据库下创建多个schema
psql -U postgres -q -c "CREATE USER user1 WITH PASSWORD 'userPwd1';GRANT ALL PRIVILEGES ON DATABASE datebaseName TO user1;"
psql -U user1 -d datebaseName -q -c "CREATE SCHEMA schema1;"
psql -U user1 -d datebaseName -q -c "GRANT ALL PRIVILEGES ON SCHEMA schema1 TO user1;"

psql -U postgres -q -c "CREATE USER user2 WITH PASSWORD 'userPwd2';GRANT ALL PRIVILEGES ON DATABASE datebaseName TO user2;"
psql -U user2 -d datebaseName -q -c "CREATE SCHEMA schema2;"
psql -U user2 -d datebaseName -q -c "GRANT ALL PRIVILEGES ON SCHEMA schema2 TO user2;"

创建表到指定的schema下
psql -U userName -d datebaseName -c "CREATE TABLE worker (id SERIAL primary key,name varchar(32) DEFAULT NULL);";
CREATE TABLE worker (id numeric(20) primary key,name varchar(32) DEFAULT NULL);
insert into worker (name) values('a1');
insert into worker (id,name) values(1,'a1');

删除schema：
DROP schema schemaName cascade;(级联删除，例如其对应的sequence，一起删除)
删除user：
revoke all on database public from ducetech;(收回此需删除的用户对数据库的所有权限)
DROP user userName;(现在删除就不会有任何权限受限而删除失败)