postgreSQL的crud操作

postgreSQL学习笔记

欢迎大家指出问题！
版本从6.0开始支持SQL解释器
安装一路下一步。

1.创建数据库

​ //命令行操作
​ createdb database_name;
​ CREATE DATABASE database_name;

psql -l     //展示所有数据库
psql database_name   //进入数据库

2.删除数据库

​ dropdb database;
​ DROP DATABASE database_name;

3.创建表

​ create table 表名 (title varchar(255), content text);

//修改表名
alter table posts rename to 表名;

//查看表信息(结构)
\d 表名
//执行这个sql文件
\i a.sql
//切换数据显示方式（横向和纵向显示） 
\x 

create table posts (
        id serial primary key,
        title varchar(255) not null,
        content text check(length(content) > 3),
        is_draft boolean default FALSE,
        created_date timestamp default 'now');

CREATE TABLE public.students
(
  id integer NOT NULL,
  name character(128),
  subjects character(1),
  CONSTRAINT student2_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.students
  OWNER TO postgres;
COMMENT ON TABLE public.students
  IS '这是一个学生信息表';

案例表：

4.删除表

​ DROP TABLE students;

5.数据类型

​ 数值型：
​ integer real serial(序列型，一般用于自增字段)
​ 文字型：
​ char varchar text
​ 布尔型：
​ boolean
​ 日期型：
​ date time timestamp
​ 特色类型：
​ Array inet(网口类型) JSON XML

6.添加表约束

unique:所在数据中值必须唯一
check：字段设置条件，可以指定函数check(length(content) > 3)必须超过3个字符
default：字段默认值

7.INSERT语句

insert into 表名(cloum1,cloum2) value(a,b);

8.数据抽出选项：

order by asc升序 desc降序
limit 限制返回数据条数
offset 偏移量(从哪条数据开始)

//分页查询limit和offset结合使用。 
select * from users order by score desc limit 3;
select * from users order by score desc limit 3 offset 3;

9.统计抽出数据

distinct 去重
group by/having(在group by之后进行再次筛选) 分组

select team,max(score) from users group by team having max(score)>25 order by max(score);

10.方便的函数

length concat(连接两个字符串) alias(别名) substring(截取字符串) random

 select player,length(player) from users;
 select player,concat(player,"/",team) from users;
 select player,concat(player,"/",team) as "球员信息" from users;
 select concat('我',substring(team,1,1)) as "球队首文字" from users;
 //随机抽奖
 select player from users order by random() limit 1;

11.更新和删除

update [table] set [field=newvalue,…] where …
delete from [table] where …
eg:

 update users set score = score + 100 where team IN ("勇士","骑士");

12.变更表结构

//alter table [tablename] …

//给表添加一条fullname字段
alter table users add fullname varchar(255);

//给哪个表的字段建索引(索引提高查询效率，但是增删效果不好)
create index 索引名 on users(字段名);

//删除索引
drop index 索引名;

//修改列名
alter table users rename player to nba_player;
//修改字段的的长度
alter table users alter nba_player type varchar(128);

13.操作多个表

//两表的关联查询
select users.player,twitters.content from users,twitters where users.id = twitters.user_id;
//别名
select u.player,t.content from users as u,twitters as t where u.id = t.user_id;

14.使用视图

​ 视图：视图就是一个select语句，把业务系统中常用的select语句简化成一个类似于表的对象，便于简单读取和开发。 (对于经常使用的select语句建立视图便于编码和管理)

//创建一个视图(通过 \dv 查看视图描述)
create view curry_twitters as select u.player,t.content from users as u,twitters as t where u.id = t.user_id;

//要进行查询时
select * from curry_twitters;

//删除视图
drop view curry_twitters;    

15.使用事务

数据库事务：是指作为单个逻辑工作单元执行的一系列操作，要么一起成功，要么一起失败。必须满足ACID(原子性、 一致性、隔离性、持久性)

PostgreSQL数据库事务使用
begin 开启事务
commit 提交
rollback 回滚

eg：
begin;
update users set score = 50 where player = '库里';
update users set score = 60 where player = '哈登';
commit;    //如果不想跟新可以 rollback;