一些常用SQL

最新推荐文章于 2024-08-15 16:05:29 发布

原创最新推荐文章于 2024-08-15 16:05:29 发布 · 153 阅读

0 ·

CC 4.0 BY-SA版权

本文介绍如何使用Oracle和PGSQL进行数据管理，包括删除重复记录、闪回查询及表结构查询等实用操作。

ORACLE

删除重复记录

DELETE 
FROM
	表 a 
WHERE
	( a.Id, a.seq ) IN ( SELECT Id, seq FROM 表 GROUP BY Id, seq HAVING count( * ) > 1 ) 
	AND ROWID NOT IN (
	SELECT
		min( ROWID ) 
	FROM
		表 
	GROUP BY
		Id,
		seq 
HAVING
	count( * ) > 1)

闪回操作，查询指定时间的数据

SELECT
	* 
FROM
	表 AS OF timestamp to_timestamp( 'yyyy-mm-dd hh:mm:ss', 'yyyy-mm-dd hh24:mi:ss' );

查询表结构（字段名，类型，长度，注释）

SELECT
	a.COLUMN_NAME,
	b.comments,
	a.DATA_TYPE,
	a.DATA_LENGTH
FROM
	user_tab_columns a
	INNER JOIN user_col_comments b ON a.COLUMN_NAME = b.COLUMN_NAME 
WHERE
	a.Table_Name = 表;

PGSQL

查询表结构

SELECT
	a.attnum,
	a.attname AS field,
	t.typname AS TYPE,
	a.attlen AS length,
	a.atttypmod AS lengthvar,
	a.attnotnull AS notnull,
	b.description AS COMMENT 
FROM
	pg_class c,
	pg_attribute a LEFT OUTER
	JOIN pg_description b ON a.attrelid = b.objoid 
	AND a.attnum = b.objsubid,
	pg_type t 
WHERE
	c.relname = '${表名}' 
	AND a.attnum > 0 
	AND a.attrelid = c.oid 
	AND a.atttypid = t.oid 
ORDER BY
	a.attnum