postgresql 数据库学习

1.建用户，建库，建表，增删查改，数据类型，数据类型操作函数和运算符

2.事务的使用，begin开始事务，出错rollback，完成时commit

3.执行语句是使用returning 返回指定值，如insert into users(id) ('10001') returning id;

4.在官方文档根据关键字搜索内容，如搜transaction搜事务相关

Chapter 8. Data Types

https://www.postgresql.org/docs/9.6/static/datatype.html

Chapter 9. Functions and Operators

https://www.postgresql.org/docs/9.6/static/functions.html 

中文网站

http://www.yiibai.com/html/postgresql/2013/080435.html 

前端注意事项：
    a) 如果有字段为空，在前端做默认处理，如：
        1.nickname为空,将其值设置为id;
        2.icon为空，将其值设置为默认icon(默认icon由前端决定);
    b) createTime参数由后端处理,前端只能获取;







1.objects(id,nickname,icon,isUser,custom)

create table if not exists objects (
    id serial8 primary key,
    nickname varchar(200),            
    icon varchar(500),    
    isUser bool not null,
    custom jsonb
);
select setval('objects_id_seq',10000);--设置起始值
例子：
insert into objects(nickname,icon,isUser) values('dy','icon',true);
insert into objects(nickname,icon,isUser) values('xl','icon1',true);
insert into objects(nickname,icon,isUser) values('emb','icon2',false);



2.users(id,password,isManager)

create table if not exists users (
    id int8 references objects(id) on delete cascade,
    password varchar(200) not null,
    isManager bool not null,
    primary key(id)
);

例子：
insert into users(id,password,isManager) values(10001,'123456',false);
insert into users(id,password,isManager) values(10002,'123456',false);


3.groups(id,createtime,custom)

create table if not exists groups (
    id int8 references objects(id) on delete cascade,
    createTime timestamp with time zone default now(),
    primary key(id)
);

例子：
insert into groups(id) values(10003);



4.members(groupID,userID)

create table if not exists members (
    groupID int8 references objects(id) on delete cascade,
    userID int8 references objects(id) on delete cascade,
    primary key(groupID,userID)
);

例子：
insert into members(groupID,userID) values
(10003,10001),
(10003,10002);



5.messages(id,content,sender,receiver,createTime)

create table if not exists messages (
    id serial8 primary key,
    content varchar(2000) not null,
    sender int8 references objects(id) on delete cascade,
    receiver int8 references objects(id) on delete cascade,
    createTime timestamp with time zone default now()
);

例子：
insert into messages(content,sender,receiver) values('hello world!',10001,10002);


查看插入：
select * from objects;
select * from users;
select * from groups;
select * from members;
select * from messages;
清理:
delete from objects;

前端注意事项：
    a) 如果有字段为空，在前端做默认处理，如：
        1.nickname为空,将其值设置为id;
        2.icon为空，将其值设置为默认icon(默认icon由前端决定);
    b) createTime参数由后端处理,前端只能获取;

2.sessions表(senderID int64 fk,receiverID  int64 fk,lastMsgID int64 fk,pk(senderID,receiverID))；
3.users表加account string字段，帐号为手机号；
4.objects表icon字段改为iconURL;


--写那个groups表时发现外键没有默认not null ，即可以插入insert into groups(id) values(100000005);,所以把所有的都加了not null

create table if not exists objects (
    id serial8 primary key,
    nickname varchar(200) not null,            --nickname修改为not null
    iconURL varchar(500) default '/icon/default.ico' not null,            
    isUser bool not null,
    custom jsonb default '{}'::jsonb not null
);
--例子：
select setval('objects_id_seq',99999999);--设置起始值
insert into objects(nickname,isUser) values('老猫',true);
insert into objects(nickname,isUser) values('dy',true);
insert into objects(nickname,isUser) values('Ficow',true);
insert into objects(nickname,isUser) values('Decaf',true);
insert into objects(nickname,isUser) values('Pismery',true);
insert into objects(nickname,isUser) values('嵌入式讨论群',false);
insert into objects(nickname,isUser) values('吹水群6',false);
insert into objects(nickname,isUser) values('kemin',true);
insert into objects(nickname,isUser) values('jimmy',true);
--2.users(id int64 fk pk,account string unique,password string,lastOnlineTime time)
create table if not exists users (
    id int8 primary key,
    account varchar(200) not null,
    password varchar(200) not null,
    lastOnlineTime timestamp with time zone default now() not null
);
alter table users drop constraint if exists fk_id;
alter table users drop constraint if exists unique_account;
alter table users add constraint fk_id foreign key (id) references objects(id) on delete cascade;
alter table users add constraint unique_account unique(account);

--例子：
insert into users(id,account,password) values(100000000,'13580452503','123456');
insert into users(id,account,password) values(100000001,'15602309617','123456');
insert into users(id,account,password) values(100000002,'15521133823','123456');
insert into users(id,account,password) values(100000003,'18826054758','123456');
insert into users(id,account,password) values(100000004,'15626030466','123456');
insert into users(id,account,password) values(100000007,'15602303588','123456');
insert into users(id,account,password) values(100000008,'18675502863','123456');
--3.groups(id int64 fk pk,owner int64 fk,createtime time)
create table if not exists groups (
    id int8 primary key,
    owner int8 not null,
    createTime timestamp with time zone default now() not null
);
alter table groups drop constraint if exists fk_id;
alter table groups drop constraint if exists fk_owner;
alter table groups add constraint fk_id foreign key (id) references objects(id) on delete cascade;
alter table groups add constraint fk_owner foreign key (owner) references objects(id) on delete cascade;

--例子：
insert into groups(id,owner) values(100000005,100000000);
insert into groups(id,owner) values(100000006,100000000);
--4.members(groupID int64 fk,userID int64 fk,pk(groupID,userID))

create table if not exists members (
    groupID int8 not null,
    userID int8 not null,
    isManager bool not null,
    primary key (groupID,userID)
);
alter table members drop constraint if exists fk_groupID;
alter table members drop constraint if exists fk_userID;
alter table members add constraint fk_groupID foreign key (groupID) references objects(id) on delete cascade;
alter table members add constraint fk_userID foreign key (userID) references objects(id) on delete cascade;

--例子：
insert into members(groupID,userID,isManager) values 
(100000005,100000000,true),
(100000005,100000001,false),
(100000005,100000002,false),
(100000005,100000003,false),
(100000005,100000004,false),
(100000005,100000007,false),
(100000005,100000008,false);
insert into members(groupID,userID,isManager) values 
(100000006,100000000,true),
(100000006,100000001,false),
(100000006,100000002,true),
(100000006,100000003,false);
DROP TYPE IF EXISTS MsgType;
CREATE TYPE MsgType AS ENUM ('text', 'image','audio', 'file');
--5.messages(id int64 pk,content string,sender int64 fk,receiver int64 fk,createTime time)

create table if not exists messages (
    id serial8 primary key,
    content varchar(2000) not null,
    sender int8 not null,
    receiver int8 not null,
    type MsgType not null,
    createTime timestamp with time zone default now() not null
);
alter table messages drop constraint if exists fk_sender;
alter table messages drop constraint if exists fk_receiver;
alter table messages add constraint fk_sender foreign key (sender) references objects(id) on delete cascade;
alter table messages add constraint fk_receiver foreign key (receiver) references objects(id) on delete cascade;

--例子：
insert into messages(content,sender,receiver,type) values('10001:你好 user2!我要给你发一条很长的消息，然后让你可以测试一下你的消息气泡的高度是不是有问题！',100000001,100000002,'text');
insert into messages(content,sender,receiver,type) values('10002:嘿 user1!在吗？',100000002,100000001,'text');
insert into messages(content,sender,receiver,type) values('10002:嘿 user4!呃，没事。。。',100000002,100000004,'text');
insert into messages(content,sender,receiver,type) values('10003:你好 user1! user1。。。。。。。',100000003,100000001,'text');
insert into messages(content,sender,receiver,type) values('10001:嘿 group5!我要给你发一条很长的消息，然后让你可以测试一下你的消息气泡的高度是不是有问题！正常吗？',100000001,100000005,'text');
insert into messages(content,sender,receiver,type) values('10001:嘿 group6!呃，我来测试一下你的消息气泡的高度是不是有问题！',100000001,100000006,'text');
insert into messages(content,sender,receiver,type) values('10002:嗨 group5!我要给你发一条很长很长很长的消息，然后让你可以测试一下你的消息气泡的高度是不是有问题！正常吗？要不然，我再说点啥？你再看下，现在看下正常不？？？？？？？',100000002,100000005,'text');
insert into messages(content,sender,receiver,type) values('10003:嗨 group6! 1.0版本啥时候诞生呢？',100000003,100000006,'text');
insert into messages(content,sender,receiver,type) values('10004:你好 group6!我也要给你发一条，一条很长很长的消息，然后让你可以测试一下你的消息气泡的高度是不是有问题！正常了吧？',100000004,100000006,'text');
--6.sessions(id serial8,senderID int64 fk,receiverID  int64 fk,lastMsgID int64 fk,pk(id,senderID,receiverID))；
create table if not exists sessions (
    id serial8 not null,
    senderID int8 not null,
    receiverID int8 not null,
    lastMsgID int8 not null,
    primary key (id,senderID,receiverID)
);
alter table sessions drop constraint if exists fk_senderID;
alter table sessions drop constraint if exists fk_receiverID;
alter table sessions drop constraint if exists fk_lastMsgID;
alter table sessions add constraint fk_senderID foreign key (senderID)  references objects(id) on delete cascade;
alter table sessions add constraint fk_receiverID foreign key (receiverID)  references objects(id) on delete cascade;
alter table sessions add constraint fk_lastMsgID foreign key (lastMsgID)  references messages(id) on delete cascade;


--例子：
insert into sessions(senderID,receiverID,lastMsgID) values(100000002,100000001,2);
insert into sessions(senderID,receiverID,lastMsgID) values(100000002,100000004,3);
insert into sessions(senderID,receiverID,lastMsgID) values(100000003,100000001,4);
insert into sessions(senderID,receiverID,lastMsgID) values(100000002,100000005,7);
insert into sessions(senderID,receiverID,lastMsgID) values(100000004,100000006,9);

--index
drop index if exists objects_id_index;
drop index if exists users_account_index;
drop index if exists users_id_index;
drop index if exists groups_id_index;
drop index if exists members_index;
drop index if exists messages_index;
drop index if exists sessions_index;
create index if not exists objects_id_index on objects (id);
create index if not exists users_account_index on users(account);
create index if not exists users_id_index on users(id);
create index if not exists groups_id_index on groups (id);
create index if not exists members_index on members(groupID,userID);
create index if not exists messages_index on messages(sender,receiver);
create index if not exists sessions_index on sessions (id,senderID,receiverID);

--查表
select * from sessions;
select * from messages;
select * from members;
select * from groups;
select * from users;
select * from objects;
--清除数据
truncate objects cascade;

--删表:
drop table if exists sessions;
drop table if exists messages;
drop table if exists members;
drop table if exists groups;
drop table if exists users;
drop table if exists objects;

--约束
alter table users drop constraint if exists unique_account;
alter table users drop constraint if exists fk_id;
alter table groups drop constraint if exists fk_id;
alter table groups drop constraint if exists fk_owner;
alter table members drop constraint if exists fk_groupID;
alter table members drop constraint if exists fk_userID;
alter table messages drop constraint if exists fk_sender;
alter table messages drop constraint if exists fk_receiver;
alter table sessions drop constraint if exists fk_senderID;
alter table sessions drop constraint if exists fk_receiverID;
alter table sessions drop constraint if exists fk_lastMsgID;
alter table users add constraint unique_account unique(account);
alter table users add constraint fk_id foreign key (id) references objects(id) on delete cascade;
alter table groups add constraint fk_id foreign key (id) references objects(id) on delete cascade;
alter table groups add constraint fk_owner foreign key (owner) references objects(id) on delete cascade;
alter table members add constraint fk_groupID foreign key (groupID) references objects(id) on delete cascade;
alter table members add constraint fk_userID foreign key (userID) references objects(id) on delete cascade;
alter table messages add constraint fk_sender foreign key (sender) references objects(id) on delete cascade;
alter table messages add constraint fk_receiver foreign key (receiver) references objects(id) on delete cascade;
alter table sessions add constraint fk_senderID foreign key (senderID)  references objects(id) on delete cascade;
alter table sessions add constraint fk_receiverID foreign key (receiverID)  references objects(id) on delete cascade;
alter table sessions add constraint fk_lastMsgID foreign key (lastMsgID)  references messages(id) on delete cascade;