docs/开发文档/数据库/PostgreSQL笔记.md

SQL语句

查询操作

查询json类型数据中指定字符串

-- store_ids为json字段
select phone from account_store_list where exists(select 1 from json_array_elements(store_ids) as elem where elem::text like '%842897384838094848%');

查询字段重复数据

-- 表名dh_org,字段org_id, org_code,org_name,
select org_id,count(*) from dh_org group by org_id having count(*) >1;

基础查询操作

-- 查询
select from video_info where links like '%74002-1-1.html%';
select id_name,sour_name,links from video_info where id_name='666486376699';
select links from video_info where links='https://dgs--2023093012e67.um2a-23616.bd1th1yrt.com:23616/vodplay/75358-1-1.html';
select * from video_info where data_size>0;https://dgs--2023093012e67.um2a-23616.bd1th1yrt.com:23616/vodplay/87607-1-1.html
SELECT * from video_info where links like '%7836-1-1%';


--使用where查询uuid，uuid必须要单引号
update new_video_info set m3u8_link='https://muji2.laoyacdn.com/20240511/Q19ypQo6/2000kb/hls/index.m3u8' where id_name='232b5827-f930-4ff7-9bb0-f98e1699c2a5'::UUID

更新操作

-- 更新表数据
update video_info set id_name='658836077826' where key like 'baf0fdabe9f8dd8c'; 
update video_info set key='',m3u8_link='' where img_link!='' and m3u8_link!='';

更改字段类型

-- 该字段类型的数据为空时
alter table table_name
alter column column_name type new_type;

删除操作

删除字段重复数据

--示例1
-- 表名dh_org,字段org_id, org_code,org_name,删除org_id字段的重复数据
WITH ranked_data AS (
    SELECT 
        org_id, 
        org_code,
        org_name,
        ROW_NUMBER() OVER (PARTITION BY org_id ORDER BY org_id) AS row_num
    FROM dh_org
)
DELETE FROM dh_org
WHERE org_id IN (
    SELECT org_id
    FROM ranked_data
    WHERE row_num > 1
);

--示例2
-- 删除m3u8_link字段的重复数据
DELETE FROM video_info a  
USING (  
  SELECT MIN(id_name) as min_id, m3u8_link  
  FROM video_info  
  GROUP BY m3u8_link  
  HAVING COUNT(m3u8_link) > 1  
) b  
WHERE a.m3u8_link = b.m3u8_link AND a.id_name > b.min_id;

删除表

delete from user_info ;

删除指定行数据

delete from video_info where id_name= '878586287727';

删除表格全部数据,而不删除架构

truncate table table_name

### 插入操作

```sql
-- 插入数据
INSERT INTO user_info(uid,key) VALUES ('123asada','sadasda3322');
insert into video_info(id) values('584653085792') where links != '';
-- 使用sql语句插入uuid4
insert into video_info(id) select uuid_generate_v4()；

--通过查询其他表的值插入新表
INSERT INTO video_2024 (id, sour_name, m3u8_link)  
SELECT   
    get_date_string() AS id,  -- 假设这个函数返回的是适合作为ID的字符串  
    (SELECT sour_name FROM new_video_info WHERE key = '0' LIMIT 1) AS sour_name,  
    (SELECT m3u8_link FROM new_video_info WHERE key = '0' LIMIT 1) AS m3u8_link;
delete from new_video_info where sour_name=(SELECT sour_name FROM new_video_info WHERE key = '0' LIMIT 1);

插入触发器

%% 要创建一个触发器，在插入new_video_info表的m3u8_link列之前检查值是否重复，并且如果重复则不插入并提醒，您可以遵循以下步骤：

首先，您需要创建一个触发器函数，该函数将执行检查并抛出异常（如果m3u8_link值已存在）。以下是这个函数的SQL语句： %%

sql
CREATE OR REPLACE FUNCTION check_m3u8_link_unique()  
RETURNS TRIGGER AS 
$$
  
BEGIN  
    IF EXISTS (SELECT 1 FROM new_video_info WHERE m3u8_link = NEW.m3u8_link) THEN  
        RAISE EXCEPTION 'm3u8_link "%" already exists.', NEW.m3u8_link;  
        RETURN NULL; -- 阻止插入  
    END IF;  
    RETURN NEW; -- 允许插入  
END;  
 
$$
 LANGUAGE plpgsql;
%% 接下来，您需要创建一个触发器，该触发器在每次尝试向new_video_info表插入新行之前调用上述函数： %%

sql
CREATE TRIGGER trg_check_m3u8_link_unique  
BEFORE INSERT ON new_video_info  
FOR EACH ROW  
EXECUTE FUNCTION check_m3u8_link_unique();

%% 现在，当您尝试向new_video_info表插入数据时，如果m3u8_link列的值已经存在，触发器会抛出一个异常，并显示您定义的错误消息。否则，如果不存在重复值，记录将被正常插入到表中。
您可以在psql命令行工具或pgAdmin的SQL编辑器中执行这些SQL语句来创建函数和触发器。请注意，在创建触发器之前，您必须首先确保函数check_m3u8_link_unique已经存在。
如果您正在使用pgAdmin，则可以按照以下步骤执行这些SQL语句：
打开pgAdmin并连接到您的PostgreSQL数据库。
在SQL编辑器中打开一个新的查询标签页。
复制并粘贴创建函数check_m3u8_link_unique的SQL语句到查询编辑器中，并执行它。
接下来，复制并粘贴创建触发器trg_check_m3u8_link_unique的SQL语句到查询编辑器中，并执行它。
执行这些步骤后，触发器将被创建，并在每次尝试向new_video_info表插入数据时自动执行所需的检查。 %%

创建容器

# 使用ubuntu手动安装
docker run -itd -h ps10 --name ps10 --network vlan100 -v /var/postgre
sql/ps10:/var/lib/pgsql/data -p 8110:5432 ubuntu /bin/bash

# 使用官方镜像
docker run --restart=always --name=ps10 --network vlan100 -p 9900:5432 -e POSTGRES_PASSWORD=vm123456 -d -v /data/docker_data/database/ps10:/var/lib/pgsql/data postgres:12

# 创建pgadmin
docker run -p 9905:5050 -e PGADMIN_DEFAULT_EMAIL=adhsite@qq.com -e PGADMIN_DEFAULT_PASSWORD=Abc123456 -e PGADMIN_LISTEN_PORT=5050 -v /data/docker_data/pgadmin:/var/data --network vlan100 --name=pgadmin -d dpage/pgadmin4

注意事项

# 创建时注意指定schema,默认的schema是public

# 客户端连接权限配置,容器的配置文件路径/var/lib/postgresql/data/pg_hba.conf

# 系统用户和数据库用户映射,/var/lib/postgresql/data/pg_ident.conf
echo -e "映射名称 \t 系统用户 \t 数据库用户" >> /var/lib/postgresql/data/pg_ident.conf
# 使用postgres用户重载配置,其他用户无法重载
pg_ctl reload

# 使用pg_ctl reload,调用SQL函数pg_reload_conf(), 或用kill -HUP）重新读取配置文件

uuid类型的问题

# 使用psycopg2连接查询
from psycopg2 import extras
with pg.connect(
            database="video_data",
            user="videos",
            password="Vi2023**",
            host="124.71.39.185",
            port="9900"
        ) as conn:
            with conn.cursor() as cursor:
                # 注册uuid扩展
                extras.register_uuid()  
                ddl_sql="UPDATE new_video_info SET key=%s,data_size=%s WHERE id_name=%s;"
                ddl_data=(key,data_size,uuid.UUID(id_name))
                # 执行ddl_sql查询
                cursor.execute(ddl_sql,ddl_data)
                # 提交事务
                conn.commit()
                
# 使用psql,将uuid字符串强制转化为uuid类型
UPDATE new_video_info SET key='645fc757fe9fb9ce',data_size='100' WHERE id_name='29793471-8c61-54a7-9dbd-65d8adde1a09'::uuid;

# 在psql命令行安装uuid模块
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
select uuid_generate_v4() # 返回一个uuid

# 可以在创建表时将ID设为主键,默认值为uuid,即可自动生成uuid
CREATE TABLE my_table (  
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),  
);

# uids扩展
CREATE EXTENSION IF NOT EXISTS uids;
SELECT generate_typeid('user');

常用命令

-- psql连接数据库
psql -U username -h hostname -p port -d dbname

-- 创建用户,可以在linux系统创建同名用户,可以在本地直连
create user username with password 'abc123456';

-- 创建用户数据库
create database dbname owner username;

-- 设置用户拥有数据库所有权限
grant all privileges on database dbname to username;
-- 设置用户具有数据表所有权限
grant all privileges on table to username;

--更改用户密码
alter user postgres with password 'Amv2024*';

-- 退出postsql命令行
\q

-- 查看用户数据库
\du

-- 查看所有数据库
\l

-- 切换数据库
\c dbname

-- 查看自定义类型的值
\dT+ typename;

-- 查看数据表列的类型
\d tablename;

-- 指定schema
set search_path to schemaname;

-- 修改schema所有人
grant all privileges on all tables in schema schemaname to username;
-- alter schema schemaname owner to username;

-- 创建类型
create type typename as enum('M','F');

-- 查询枚举类型的值
select * from pg_enum;

-- 删除枚举类型
drop type public.typename;

-- 删除枚举类型的值
delete from pg_enum where enumlabel='M';

-- 更改原枚举名称
ALTER TYPE "public"."et_type" RENAME TO "et_type2";

-- 创建用于替换的枚举
CREATE TYPE "public"."et_type" AS ENUM ('type1', 'type2', 'type3');

-- 更改枚举拥有者
ALTER TYPE "public"."et_type" OWNER TO "postgres";

-- 更改表对枚举的引用
ALTER TABLE "public"."table" ALTER COLUMN "type" TYPE "et_type" USING "type"::text::et_type;


INSERT INTO "testdata"."employee" ( "emp_no","birth_date","first_name","last_name","gender","hire_date" ) VALUES ( 100000,'1953/9/2','test1','test2','F','1968/3/5'  )