PostgreSQL入门之CRUD(一)

最新推荐文章于 2024-12-26 00:39:26 发布

原创最新推荐文章于 2024-12-26 00:39:26 发布 · 357 阅读

1 ·

CC 4.0 BY-SA版权

PostgreSQL 专栏收录该内容

4 篇文章

订阅专栏

本文深入讲解PostgreSQL数据库的基础CRUD操作，包括表约束、数据插入、查询、更新和删除等核心概念。同时，文章还介绍了如何进行复杂的数据筛选、统计和函数调用，以及表结构变更、多表操作、视图和事务处理等高级主题。

PostgreSQL入门之CRUD

表约束

create table posts(
  id serial primary key,
  title varchar(255) not null,
  content text check(length(content)>8),
  is_draft boolean default TRUE,
  is_del boolean default FALSE,
  create_date timestamp default 'now'
);
/*说明
not null:不能为空
unique:所有数据中值必须唯一
primary key(not null,unique):主键不能为空，且不能重复
check:字段设置条件
default:字段默认值
*/

INSERT语句

/*
知识点
*/
insert INTO [tablename] (field,...) values(value,...)
示例：
insert INTO posts (title,content) values('title1','content11');
insert INTO posts (title,content) values('title2','content12');
insert INTO posts (title,content) values('title3','content13');
SELECT * FROM public.posts;

SELECT 语句

create table uesrs (
  id serial primary key,
  player varchar(255) not null,
  score real,
  team varchar(255)
);
insert into users (player,score,team) values
('库里',28.3,'勇士'),
('哈登',30.2,'火箭'),
('杜兰特',25.6,'勇士'),
('詹姆斯',27.8,'骑士');

数据抽出选项
- orderby
- limit
- offset

  select * from users order by score asc;
  select * from users order by score desc limit 3;
  select * from users order by score desc limit 3 offset 1;
  select * from users order by score desc limit 3 offset 2;

统计抽出数据
- distinct
- sum
- max/min
- group by/having

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

函数
- length
- concat(组合函数)
- alias（别名）
- substring（字符串分割）
- random（随机）

   select player,concat(player,'/',team) as "球员信息" from users;
   select concat('我',substring(team,1,1)) as "球队首文字" from public.uesrs;

更新和删除
```sql
/*
说明
*/
update [table] set [field=newvalue,…] where …
delete from [table] where …
示例：
update uesrs set score=29.1 where player=‘詹姆斯’;
update uesrs set score=score+100 where team IN (‘勇士’,‘骑士’)


* 变更表结构
```sql
  alter table [tablename] ...
  create index ...
  drop index ...
  示例：
  alter table uesrs add fullname varchar(255);
  alter table uesrs rename player to nba_player;
  alter table uesrs alter nba_player type varchar(100);
  create index nba_player_index on uesrs(nba_player);
  SELECT * FROM public.uesrs;

操作多个表

create  table twiters(
  id serial primary key,
  user_id integer,
  content varchar(255) not null
);
insert into twiters (user_id,content) values
(1,'今天又是大胜'),
(2,'今晚得了60分'),
(3,'获胜咱不怕，缺谁谁尴尬'),
(1,'明年听说有一条大鱼要来？');
示例
select u.player,t.content from users as u,twiters as t where u.id=t.user_id and u.id=1;

使用视图
视图将业务系统中的select语句简化成一个类似于表的对象，便于简单读取和开发

  select u.player,t.content from uesrs as u,twiters as t where u.id=t.user_id and u.id=1;
  create view curry_twitters as select u.nba_player,t.content from public.uesrs as u,twiters as t where u.id=t.user_id and u.id=1;
  select * from curry_twitters;

使用事务

  select * from uesrs;
  begin;
  update uesrs set score =50 where nba_player='库里';
  update users set score =60 where nba_player='哈登';
  commit;
  select * from uesrs;
  begin;
  update uesrs set score =0 where nba_player='库里';
  update users set score =0 where nba_player='哈登';
  rollback;
  select * from uesrs;