276 lines
9.1 KiB
Markdown
276 lines
9.1 KiB
Markdown
|
||
## SQL语句
|
||
### 查询操作
|
||
#### 查询json类型数据中指定字符串
|
||
```sql
|
||
-- 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%');
|
||
```
|
||
#### 查询字段重复数据
|
||
```sql
|
||
-- 表名dh_org,字段org_id, org_code,org_name,
|
||
select org_id,count(*) from dh_org group by org_id having count(*) >1;
|
||
```
|
||
#### 基础查询操作
|
||
```sql
|
||
-- 查询
|
||
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
|
||
```
|
||
### 更新操作
|
||
```sql
|
||
-- 更新表数据
|
||
update video_info set id_name='658836077826' where key like 'baf0fdabe9f8dd8c';
|
||
update video_info set key='',m3u8_link='' where img_link!='' and m3u8_link!='';
|
||
```
|
||
|
||
### 更改字段类型
|
||
```sql
|
||
-- 该字段类型的数据为空时
|
||
alter table table_name
|
||
alter column column_name type new_type;
|
||
```
|
||
### 删除操作
|
||
#### 删除字段重复数据
|
||
```sql
|
||
--示例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;
|
||
```
|
||
#### 删除表
|
||
```sql
|
||
delete from user_info ;
|
||
```
|
||
#### 删除指定行数据
|
||
```sql
|
||
delete from video_info where id_name= '878586287727';
|
||
```
|
||
|
||
#### 删除表格全部数据,而不删除架构
|
||
```sql
|
||
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);
|
||
```
|
||
|
||
## 插入触发器
|
||
```sql
|
||
%% 要创建一个触发器，在插入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表插入数据时自动执行所需的检查。 %%
|
||
```
|
||
## 创建容器
|
||
```sh
|
||
# 使用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
|
||
```
|
||
## 注意事项
|
||
```sh
|
||
# 创建时注意指定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类型的问题
|
||
```python
|
||
# 使用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');
|
||
```
|
||
## 常用命令
|
||
```postgresql
|
||
-- 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' )
|
||
```
|