PostgreSQL 使用总结

本文详细介绍了SQL中JOIN操作符的两种使用方式:ON和USING。通过具体的例子展示了两者之间的区别,特别是USING如何简化重复字段的处理,并指出USING与NATURAL JOIN的等效性。

1. USING的使用

USING是个缩写的概念:它接收一个用逗号分隔的字段名字列表, 这些字段必须是连接表共有的,最终形成一个连接条件,表示这些字段对必须相同。

USING (a, b, c) 等效于 ON (t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c) 只不过是如果使用了ON,那么在结果里 a,b,和 c字段每个都会有两个, 而用USING的时候每个字段就只会有一个。

举个例子

假设我们有一个表 t1

num | name
-----+------
1 | a
2 | b
3 | c
和 t2

num | value
-----+-------
1 | xxx
3 | yyy
5 | zzz

 

=> SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num;
num | name | num | value
-----+------+-----+-------
1 | a | 1 | xxx
3 | c | 3 | yyy
(2 rows)

当使用ON t1.num = t2.num,结果中num列有两个,且内容一样。

 

=> SELECT * FROM t1 INNER JOIN t2 USING (num);
num | name | value
-----+------+-------
1 | a | xxx
3 | c | yyy
(2 rows)

当使用USING(num),结果中只有一列num。

 

SELECT * FROM t1 INNER JOIN t2 USING (num);

等价于

SELECT * FROM t1 NATURAL INNER JOIN t2;

NATURAL 是 USING 的缩写形式:它形成一个 USING 列表, 该列表由那些在两个表里都出现了的字段名字组成。和USING一样,这些字段只在输出表里出现一次。

转载于:https://www.cnblogs.com/mafeifan/p/3840585.html

<think>我们之前讨论的是MySQL,现在用户要求使用PostgreSQL。在PostgreSQL中删除表、视图和相关对象的基本逻辑类似,但有一些语法和系统特性的差异。 步骤: 1. 先删除视图,因为视图依赖于表。 2. 删除表。注意:如果两个表之间有外键约束,需要先删除引用表(iiot_log)再删除被引用表(iiot_log_type)。或者我们可以使用CASCADE选项。 3. 在PostgreSQL中,可以使用`DROP ... CASCADE`来自动删除依赖对象,但需要谨慎使用。 具体操作: 方案一(推荐安全方式):手动按顺序删除 ```sql -- 删除视图 DROP VIEW IF EXISTS v_iiot_log; -- 删除表iiot_log(如果它引用了iiot_log_type,且没有使用CASCADE,则必须先删除iiot_log) DROP TABLE IF EXISTS iiot_log; -- 删除表iiot_log_type DROP TABLE IF EXISTS iiot_log_type; ``` 方案二:使用CASCADE(自动删除依赖) ```sql -- 删除视图,如果有其他对象依赖此视图,也会被删除(但这里我们不需要考虑) DROP VIEW IF EXISTS v_iiot_log CASCADE; -- 删除表时,如果存在外键引用,使用CASCADE可以自动删除依赖的外键约束,但不会删除其他表,只会删除依赖该表的对象(如视图、外键约束等) -- 但是注意:CASCADE在删除表时,会同时删除依赖于该表的视图、外键约束等,但不会删除其他表。 -- 因此,我们可以分别删除两个表,并加上CASCADE确保删除所有依赖对象。 DROP TABLE IF EXISTS iiot_log CASCADE; DROP TABLE IF EXISTS iiot_log_type CASCADE; ``` 注意:在方案二中,如果两个表互相有外键引用,那么使用CASCADE可以同时删除两个表以及它们之间的约束。但是,通常我们建议按顺序删除(先删除引用表,再删除被引用表)以避免潜在问题。 说明: 1. `IF EXISTS`:如果对象不存在,避免报错。 2. `CASCADE`:自动删除依赖于该对象的任何对象(如视图、外键约束等)。使用时要特别小心,因为它可能会删除你未预料到的对象。 建议: 在不确定依赖关系的情况下,可以先查询依赖关系,再手动按顺序删除。 如何查询依赖关系? 在PostgreSQL中,可以使用以下系统表查询: ```sql -- 查看视图依赖哪些表 SELECT * FROM information_schema.view_table_usage WHERE view_name = 'v_iiot_log'; -- 查看表被哪些对象依赖 SELECT * FROM pg_depend WHERE refobjid = (SELECT oid FROM pg_class WHERE relname = 'iiot_log'); ``` 安全操作步骤: 1. 备份重要数据(如果需要)。 2. 在测试环境中验证。 3. 在维护窗口执行。 因此,完整的SQL脚本(按顺序删除): ```sql -- 删除视图 DROP VIEW IF EXISTS v_iiot_log; -- 删除表iiot_log DROP TABLE IF EXISTS iiot_log; -- 删除表iiot_log_type DROP TABLE IF EXISTS iiot_log_type; ``` 如果存在外键约束导致无法删除表,可以先删除外键约束或者使用CASCADE。如果确定没有其他对象依赖,可以不用CASCADE。 如果存在外键约束,且不想使用CASCADE,可以手动删除约束: ```sql -- 先删除iiot_log表上的外键约束(需要知道约束名) ALTER TABLE iiot_log DROP CONSTRAINT IF EXISTS fk_constraint_name; -- 然后再删除表 ``` 但是,通常使用CASCADE更为方便,尤其是在脚本中。 综合考虑,给出一个较为通用的脚本(使用CASCADE确保删除): ```sql -- 删除视图,并级联删除依赖于此视图的对象(如果有) DROP VIEW IF EXISTS v_iiot_log CASCADE; -- 删除表iiot_log,并级联删除依赖(如外键引用该表的其他表?注意:不会删除其他表,只会删除依赖对象如视图、外键约束等) DROP TABLE IF EXISTS iiot_log CASCADE; -- 删除表iiot_log_type,同样级联删除依赖 DROP TABLE IF EXISTS iiot_log_type CASCADE; ``` 注意:使用CASCADE删除表时,只会删除依赖于该表的对象(比如其他表对该表的外键约束、视图等),而不会删除其他表本身。所以这里删除两个表是安全的。 因此,最终推荐使用带CASCADE的删除方式,以避免因存在依赖而失败。 但是,请注意:在PostgreSQL中,视图并不直接依赖于表的存在(删除表时,视图不会被自动删除,但会变为无效)。因此,我们第一步已经删除了视图,所以表删除时不需要担心视图依赖。但是,表之间可能有外键约束,使用CASCADE可以自动处理外键约束。 总结:使用上述三个带CASCADE的语句可以确保删除成功。 注意:生产环境务必谨慎操作,提前备份。 §§相关问题§§ 1. 在PostgreSQL中,使用`CASCADE`选项删除对象时,可能会带来哪些潜在风险? 2. 如何查询PostgreSQL中某个表的所有外键依赖关系? 3. 在PostgreSQL中,删除一个被视图依赖的表后,对该视图进行查询会发生什么? 4. 除了使用`DROP ... CASCADE`,还有哪些方法可以安全地删除具有外键约束的表?</think>在 PostgreSQL 中删除 `iiot_log` 表、`iiot_log_type` 表以及依赖视图 `v_iiot_log` 的操作如下: ### PostgreSQL 删除操作步骤 ```sql -- 1. 先删除依赖视图 DROP VIEW IF EXISTS v_iiot_log CASCADE; -- 2. 删除主表(自动解除依赖关系) DROP TABLE IF EXISTS iiot_log CASCADE; -- 3. 删除关联表 DROP TABLE IF EXISTS iiot_log_type CASCADE; ``` ### 关键说明(PostgreSQL 特性) 1. **`CASCADE` 参数**: - 自动删除所有依赖对象(外键约束、视图、函数等) - 避免手动解除依赖关系 - **慎用警告**:可能意外删除非目标对象 2. **依赖关系检查**: 删除前建议查询依赖关系: ```sql -- 查看视图依赖 SELECT * FROM pg_depend WHERE refobjid = 'v_iiot_log'::regclass; -- 查看表依赖 SELECT * FROM pg_constraint WHERE confrelid = 'iiot_log_type'::regclass; ``` 3. **事务操作**: ```sql BEGIN; DROP VIEW ...; DROP TABLE ...; COMMIT; -- 失败时 ROLLBACK; ``` 4. **权限要求**: - 需要表所有者权限 - 或 `DROP` 权限(`GRANT DROP ON ALL TABLES IN SCHEMA public TO user;`) ### 注意事项 1. **数据备份**: ```bash pg_dump -t iiot_log -t iiot_log_type dbname > backup.sql ``` 2. **锁行为**: - `DROP TABLE` 获取 `ACCESS EXCLUSIVE` 锁 - 大表删除建议在低峰期操作 3. **替代方案**(避免锁表): ```sql -- 重命名代替删除(可逆操作) ALTER TABLE iiot_log RENAME TO iiot_log_old; -- 后续再删除 DROP TABLE iiot_log_old; ``` 4. **回收空间**: 删除后执行 `VACUUM FULL;` 回收磁盘空间
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值