PostgreSQL数据库基本使用

1.简介

PGSQL是一个功能非常强大同时是开源的对象关系型数据库.经过二十几年的发展，PostgreSQL 是目前世界上可以获得的最先进的开放源码数据库系统。

2.基本语法

表是关系型数据库存储结构化数据的基本结构,可以使用CREATE TABLE

创建所需要的结构,其语法:

CREATE TABLE IF NOT EXISTS CITY(
	CITY_ID	INT NOT NULL,
  CITY_NAME VARCHAR(50),
  CJJLSJ DATE
);
-- 添加表注释
COMMENT ON TABLE CITY IS '城市数据表';

-- 添加字段注释
COMMENT ON COLUMN CITY.CITY_ID IS '城市ID';

列是被称为表中的字段,其代表一条数据的一个属性;

增加列:

ALTER TABLE CITY ADD COLUMN ZHGXSJ DATE;

删除列:

ALTER TABLE CITY DROP COLUMN ZHGXSJ;

修改类型:

ALTER TABLE CITY ALTER COLUMN ZHGXSJ TIMESTAMP;

列重命名:

ALTER TABLE CITY RENAME ZHGXSJ TO DT_ZHGXSJ;

表重命名:

ALTER TABLE CITY RENAME TO CITY_1;

增加主键:

ALTER TABLE CITY ADD PRIMARY KEY(CITY_ID);

3.序列

在业务上我们有时需要设置自增主键,MYSQL通过使用AUTO_INCREMENT实现ID自增,PGSQL并不支持这个方法但是有之对应的序列可以实现此功能.

创建序列:

CREATE SEQUENCE city_seq
START WITH 1 	 -- 步长为1
INCREMENT BY 1 -- 每次增加1
MINVALUE 1 		 -- 最小值为1
MAXVALUE 99		 -- 最大值为99

设置序列:

SELECT SETVAL('city_seq', 1)

删除序列:

DROP SEQUENCE city_seq;

3.1使用序列两种方法

创建表时不指定序列:

CREATE TABLE IF NOT EXISTS CITY(
	CITY_ID	INT NULL,
  CITY_NAME VARCHAR(30) NULL
);

INSERT INTO CITY VALUES(NEXTVAL('city_seq'), '杭州');
INSERT INTO CITY VALUES(NEXTVAL('city_seq'), '嵊州');
INSERT INTO CITY VALUES(NEXTVAL('city_seq'), '湖州');

创建表时指定序列:

CREATE TABLE IF NOT EXISTS CITY(
	CITY_ID	INT DEFAULT NEXTVAL('city_seq') NOT NULL,
  CITY_NAME VARCHAR(30) NULL
);

INSERT INTO CITY(CITY_NAME) VALUES('杭州');
INSERT INTO CITY(CITY_NAME) VALUES('嵊州');
INSERT INTO CITY(CITY_NAME) VALUES('湖州');

3.2使用序列清洗数据

场景:  创建表结构时没有指定唯一主键字段,录入数据的时候重复录入造成数据不唯一,需要对重复的数据进行清洗,同时设置主键.

解决思路: 1.在原表中新增一列,2.将新增列设置自增序列.3.进行清洗逻辑如删除同一条数据最大的ID.4.将新增的列删除.5.设置主键.

-- 创建表
CREATE TABLE IF NOT EXISTS CITY(
 CITY_ID INT NULL,
 CITY_NAME VARCHAR(30)
);

-- 添加数据
INSERT INTO CITY VALUES(NULL , '杭州'),
INSERT INTO CITY VALUES(NULL , '杭州'),
INSERT INTO CITY VALUES(NULL , '嵊州'),
INSERT INTO CITY VALUES(NULL , '湖州'),

-- 查询数据
SELECT * FROM CITY;--发现没有设置主键,杭州录入两次,需要对杭州数据进行清洗.

-- 新增一列
ALTER TABLE CITY ADD COLUMN TEMP_ID INT;

--创建序列
CREATE SEQUENCE city_seq
START WITH 1
INCREAMENT BY 1
MINVALUE 1
MAXVALUE 1;

--将新增列设置使用序列
ALTER TABLE CITY LATER COLUMN TEMP_ID SET DEFAULT NEXTVAL('city_seq');

-- 创建存储过程将已有数据添加自增ID
CREATE OR REPLACE FUNCTION ADD_ID() RETURNS INTEGER AS $$
DECLARE
r RECORD;
sql "varchar";
	BEGIN
        sql := 'select CITY_ID, CITY_NAME from CITY';
        FOR r IN EXECUTE sql LOOP
            UPDATE CITY SET TEMP_ID = NEXTVAL('city_seq') WHERE CITY_NAME = r.CITY_NAME;
	END LOOP;
    RETURN NEXTVAL('city_seq');
 END;
$$ LANGUAGE plpgsql;

-- 调用函数
select * FROM ADD_ID();

-- 查询数据
SELECT * FROM CITY;

--对数据进行清洗
DELETE FROM CITY WHERE TEMP_ID IN (
SELECT
	A1.MAXID
FROM
	(
      SELECT MIX(TEMP_ID) AS MAXID,CITY_NAME FROM CITY GROUP BY CITY_NAME HAVING COUNT(1) >= 2
    ) A1
);

-- 将CITY_ID字段删除,同时将TEMP_ID修改为CITY_ID同时设置为主键
-- 删除字段
ALTER TABLE CITY DROP COLUMN CITY_ID;
-- 修改名称
ALTER TABLE CITY RENAME TEMP_ID TO CITY_ID;
-- 设置主键
ALTER TABLE CITY ADD PRIMARY KEY(CITY_ID);

4.总结

以上是个人使用PGSQL的一些总结,简单的记录一下.