postgresql表操作

1.表基本操作

1.1创建表

create table tb4(
	id BIGSERIAL not null primary key,-- 不允许为空，&主键&自增
    name varchar(16) not null, --允许为空
    email varchar(32) null, -- 可以为空(默认)
    age int default 3  -- 插入数据，不给值，默认值为3
);

字符串类型	是否可变长
varchar	是
character	否

1.2 查看表

• 查看所有表

  \d
  

• 查看表信息

  -- \d 表名
  \d flow
  

1.3清空表数据

TRUNCATE TABLE flow;

1.4删除表

DROP TABLE flow;

1.5退出数据库

\q

2.表属性操作

2.1增加列

ALTER TABLE flow ADD age int;

2.2删除列

ALTER TABLE flow DROP COLUMN age;

2.3修改列属性

ALTER TABLE flow ALTER COLUMN date TYPE varchar;
Alter TABLE point alter column point  TYPE geometry USING point ::geometry;
select st_astext(geo) from test;
alter table "表名称" rename "旧列名" to "新名称"; # 修改表列名

2.4增加列非空约束

alter table flow alter column age set not null;

2.5 增加列唯一约束

ALTER TABLE flow ADD CONSTRAINT FlowUniqueConstraint UNIQUE(age);
-- 删除约束
ALTER TABLE flow DROP CONSTRAINT FlowUniqueConstraint ;

2.6删除主键约束

\d flow; # 查看主键的名称
ALTER TABLE flow DROP CONSTRAINT flow_pkey;

2.7创建主键约束

ALTER TABLE flow ADD CONSTRAINT FlowPrimaryKey PRIMARY KEY (ID);
-- 删除主键约束
ALTER TABLE flow DROP CONSTRAINT FlowPrimaryKey ;

• 创建主键自增id

  CREATE SEQUENCE id_seq  
  START WITH 1  
  INCREMENT BY 1  
  NO MINVALUE  
  NO MAXVALUE  
  CACHE 1;   # 设置自增序列
  alter table testtable alter column id set default nextval('id_seq');  # 赋予表自增
  

2.8 删除外键约束

• 删除外键名为test_fkey的外键约束

  ALTER TABLE test DROP CONSTRAINT test_fkey;
  

2.9 增加外键约束

• 为flow表的to列添加外键,为point的id

  alter table flow add CONSTRAINT flow_fkey foreign key("to") references point(id)  on update cascade on delete cascade;
  

2.10 修改表名

ALTER TABLE test RENAME TO test_new;

3.表数据操作

3.1数据查询

• 查询表所有数据

  select * from flow;
  

• 查询表前100条数据

  SELECT * FROM flow ORDER BY id ASC LIMIT 100;
  

• 查询表后100条数据

  SELECT * FROM flow ORDER BY id DESC LIMIT 100;
  

• 查询表某几列数据

  SELECT id,date FROM flow;
  -- 修改列名称
  SELECT id as n1,date as n2 FROM flow;
  

3.2数据条件查询【where】

• >

  -- 查找年龄大于30
  select * from info where age>30;
  -- 查找id大于1
  select * from info where id>1;
  

• =

  -- 查找id等于1
  select * from info where id=1;
  

• >=

  -- 查找id大于等于1
  select * from info where id>=1;
  

• !=

  -- 查找id不等于1
  select * from info where id!=1;
  

• between and

  -- 查找2<=id<=4
  select * from info where id between 2 and 4;
  

• and

  -- 查找name="wxy" 并且 age=19
  select * from info where name="wxy" and age=19;
  

• or

  -- 查找name="wxy" 或者 age=49
  select * from info where name="wxy" or age=49;
  -- 查找name="wxy" 或者 email="123@qq.com" 里面 age=49
  select * from info where (name="wxy" or email="123@qq.com") and age=49;
  

• in

  -- 查找id 等于1或4或6
  select * from info where id in (1,4,6);
  -- 查找id 在depart表中存在的id
  select * from info where id in (select id from depart);
  

• not in

  -- 查找id不等于1或4或6
  select * from info where id not in (1,4,6);
  

• exists

  -- 查找是否有select * from depart where id=5，如果有，就查询select * from info
  select * from info where exists(select * from depart where id=5);
  

• not exists

  -- 查找是否有select * from depart where id=5，如果没有，就查询select * from info
  select * from info where not exists(select * from depart where id=5);
  

• 子查询

  -- 查找id>2 里面的age>10的信息
  select * from (select * from info where id>2) as T where age>10;
  

• is

  -- 查询非空数据
  SELECT * FROM flow WHERE "to" is not null;
  

• limit

  -- 取前五条
  select * from info limit 5;
  

• limit offset

  -- 从第二个位置开始取，取三个，offset从0开始
  select * from info limit 3 offset 2;
  

3.3数据表达式查询

• where

  SELECT * FROM flow WHERE "from" = 1000;
  

• like用于模糊查询

  • %【匹配表示0到多个】

    select * from info where name like '%wxy';
    

  • _【匹配一个】

    select * from info where name like "_wxy";
    

3.4函数查询

• count

  -- 查询数据条数
  SELECT COUNT(*) AS "flow" FROM flow;
  

• sum

  SELECT sum("from") AS "flow" FROM flow;
  
  

• max

  SELECT max("from") FROM flow;
  

• min

  SELECT min("from") FROM flow;
  

• DISTINCT 【过滤重复值】

  SELECT DISTINCT id FROM depart;
  

3.5分组

• group by

  select age,max(id),min(id),count(id),sum(id),avg(id) from info group by age;
  select age,count(1) from info group by age;
  select depart_id,count(id) from info group by depart_id;
  

• having【先执行分组再判断条件】

  select depart_id,count(id) from info group by depart_id having count(id)>2;
  

  select age,max(id) from info group by age;
  -- 只获取根据年龄分组的获取最大的id
  select * from info where id in (select max(id) from info group by age);
  

  -- 根据age分组，再查找count(id)>2 
  select age,count(id) from info group by age having count(id)>2;
  -- 聚合条件，having放后面
  select age,count(id) from info where id>4 group by age having count(id)>2;
  

  select age,
  count(id)
  from info where id>2 group by age having count(id)>1 order by age desc limit 1;
  - 要查询的表info
  - 条件 id>2
  - 根据age分组
  - 对分组后的数据再根据聚合条件过滤 count(id)>1
  - 根据age从大到小排序
  - 获取第1条
  

3.6 映射【根据条件添加列，聚合操作】

• 获取自己想要的列

  • 获取想要的数据列

    -- 只获取id,name两列
    select id,name from info;
    -- 将name列起别名为n
    select id,name as n from info;
    -- 增加一列123,所有值都为123
    select id,name as n,123 from info;
  

  -- 查找id,name,添加num列值为666，
  -- 添加mid列值为depart表中id的最大值，
  -- 添加nid列值为depart表中id的最小值
  select id,
  name,
  666 as num,
  (select max(id) from depart) as mid,
  (select min(id) from depart) as nid,
  age 
  from info;
  

  -- 查找id,name，添加一列x1,info中的depart_id=depart中的id为条件，找depart表中的title作为x1的值
  
  select id ,
  name,
  (select title from depart where depart.id=info.depart_id) as x1
  from info;
  

  select 
  id ,
  name,
  (select title from depart where depart.id=info.depart_id) as x1,
  (select title from depart where depart.id=info.id) as x2 
  from info;
  

• case when then end

  -- 添加一列为v1,当depart_id=1时，将v1的值赋值为 "第1部门" 
     select id,
      name,
      case depart_id when 1 then '第1部门' end v1
      from info;
  

• case when then else end

  -- 添加一列为v2,当depart_id=1时，将v2的值赋值为 "第1部门" ,否则赋值为 "其他"
  select 
  id,
  name,
  case depart_id when 1 then '第1部门' else '其他' end v2 
  from info;
  

• case when then ... when then ... when then ... else... end

  -- case when then ... when then ...  when then ... else... end 
  
  select id,
  name,
  case depart_id when 1 then '第一部门' end v1,
  case depart_id when 2 then '第1部门' else '其他' end v2,
  case depart_id when 1 then '第一部门' when 2 then '第2部门' else '其他' end v3,
  case when age<18 then '少年' end v4,
  case when age<18 then '少年' else '油腻男' end v5,
  case when age<18 then '少年' when age<30 then '青年' else '油腻男' end v6
  from info;
  

3.7 排序(order by)

• desc【降序】

  select * from info order by age desc;
  

• asc【升序】

  select * from info order by age asc;
  

3.8 左右连表【多表查询】

• 主表

  主表 left outer join 从表 on 主表.x=从表.id
  

  -- 左表连接，左边是主表
  -- 右表连接，右边是主表
  select * from info left outer join depart on info.depart_id=depart.id;
  select info.id,info.name,info.email,depart.title from info left outer join depart on info.depart_id=depart.id;
  

• 从表

  从表 right outer join 主表 on 主表.x = 从表.id
  select info.id,info.name,info.email,depart.title from info right outer join depart on info.depart_id=depart.id;
  

3.9 联合【union】

• 列数需相同【union】

   select id,title from depart 
   union 
   select id,name from info;
  

• 【union】自动去重

  -- 注意：列的数据类型需要一致
   select id,title from depart 
   union 
   select id,name from info;
  

• 获取所有【union all不去重】

  select id from depart
  union all 
  select id from info;
  

3.10 数据增删改

• 修改数据

  UPDATE covid19_policy SET id=1 WHERE id=0;
  

• 删除数据

   DELETE FROM covid19_policy WHERE ID = 2;
  

• 添加数据

   INSERT INTO flow (id, date, "from","to",flow_people) VALUES (0, '2020-01-01', 1,2,1);
   -- 多行添加
   INSERT INTO flow (id, date, "from","to",flow_people) VALUES (500000002, '2020-01-01', 1,2,1),(500000001, '2020-01-01', 1,2,1);
  

4.表关系

4.1 一对多【设置一个外键】

• 需要两张表来存储信息，且两张表存在一对多或多对一关系
  • constraint fk_info1_depart1 foreign key (depart_id) references depart1(id)

  create table depart1(
      id int not null primary key,
      title varchar(16) not null
  );
  
  create table info1(
  	id int not null primary key,
  	name varchar(16) not null,
  	email varchar(32) not null,
  	age int,
  	depart_id int not null,
  	constraint fk_info1_depart1 foreign key (depart_id) references depart1(id)
  ) ;
  -- 如果表结构创建好了，额外添加外键
  alter table info1 add CONSTRAINT fk_info1_depart1 foreign key (depart_id) references depart1(id);
  -- 删除外键
  alter table info1 drop CONSTRAINT fk_info1_depart1;
  

4.2 多对多【设置多个外键】

• 需要三张表来存储信息，两张单表+关系表，创造出两张单表之间多对多关系

  create table boy(
  id int not null primary key,
  name varchar(16) not null
  );
  
  create table girl(
  	id int not null primary key,
  	name varchar(16) not null
  );
  
  create table boy_girl(
  	id int not null primary key,
  	boy_id int not null,
  	girl_id int not null,
  	constraint fk_boy foreign key (boy_id) references boy(id),
  	constraint fk_girl foreign key (girl_id) references girl(id)
  );
  -- 创建表之后添加外键
  alter table boy_girl add constraint fk_boy foreign key (boy_id) references boy(id);
  alter table boy_girl add constraint fk_girl foreign key (girl_id) references girl(id);
  constraint fk_info1_depart1 foreign key (depart_id) references depart1(id)
  -- 删除外键
  alter table boy_girl drop CONSTRAINT fk_boy;
  alter table boy_girl drop CONSTRAINT fk_girl;