50条PostgreSQL常见SQL语句

原创梧桐 2023-08-18

445

以下是 PostgreSQL 数据库中经常使用的一些命令及其对应的简单示例，这些命令涵盖了数据库管理、数据操作、查询、维护等多个方面，供参考。

命令	说明	示例
SELECT	从表中检索数据	SELECT * FROM employees WHERE department = ‘Sales’;
INSERT	将新行插入表中	INSERT INTO customers (name, email) VALUES (‘John’, ‘john@example.com’);(mailto:john@example.com)
UPDATE	更新表中的现有行	UPDATE orders SET status = ‘Shipped’ WHERE order_id = 123;
DELETE	从表中删除行	DELETE FROM products WHERE stock_quantity < 10;
CREATE DATABASE	创建新数据库	CREATE DATABASE newdb;
CREATE TABLE	创建新表	CREATE TABLE products (id SERIAL PRIMARY KEY, name TEXT);
ALTER TABLE	修改现有表的结构	ALTER TABLE employees ADD COLUMN salary INT;
CREATE INDEX	创建索引以加速查询	CREATE INDEX idx_name ON customers (name);
DROP DATABASE	删除数据库	DROP DATABASE olddb;
DROP TABLE	删除表	DROP TABLE obsolete_table;
DROP INDEX	删除索引	DROP INDEX idx_name;
GRANT	授予用户或角色访问权限	GRANT SELECT, INSERT ON employees TO user;
REVOKE	撤销用户或角色的访问权限	REVOKE UPDATE ON products FROM user;
VACUUM	清理表并回收空间	VACUUM FULL;
ANALYZE	收集统计信息以优化查询性能	ANALYZE customers;
BEGIN	开始事务	BEGIN;
COMMIT	提交事务	COMMIT;
ROLLBACK	回滚事务	ROLLBACK;
SAVEPOINT	设置保存点，以便在事务中进行部分回滚	SAVEPOINT sp1;
PREPARE	准备执行预处理语句	PREPARE get_product_by_id (INT) AS SELECT * FROM products WHERE id = $1;
EXECUTE	执行预处理语句	EXECUTE get_product_by_id(5);
DEALLOCATE	释放预处理语句	DEALLOCATE get_product_by_id;
SHOW	显示运行时参数的值	SHOW work_mem;
EXPLAIN	显示查询计划	EXPLAIN SELECT * FROM orders WHERE customer_id = 10;
COPY	将数据从文件加载到表中，或将数据从表导出到文件	COPY products TO ‘/path/to/file.csv’ CSV HEADER;
TRUNCATE	快速删除表中的所有行	TRUNCATE TABLE logs;
CLUSTER	按索引重组表中的行	CLUSTER products USING idx_name;
CREATE SCHEMA	创建新模式（命名空间）	CREATE SCHEMA myschema;
ALTER SCHEMA	修改现有模式	ALTER SCHEMA public RENAME TO common;
CREATE FUNCTION	创建存储过程或函数	CREATE FUNCTION calculate_tax(amount numeric) RETURNS numeric AS BEGINRETURNamount∗0.1;END; LANGUAGE plpgsql;
CREATE TRIGGER	创建触发器，以在特定事件发生时执行操作	CREATE TRIGGER update_inventory AFTER INSERT ON orders FOR EACH ROW EXECUTE PROCEDURE update_inventory();
CREATE VIEW	创建视图	CREATE VIEW monthly_sales AS SELECT date_trunc(‘month’, order_date) AS month, SUM(total_amount) AS total FROM orders GROUP BY month;
CREATE RULE	创建规则以在特定条件下执行操作	CREATE RULE update_price AS ON UPDATE TO products WHERE NEW.price > OLD.price DO ALSO NOTIFY price_change;
COMMENT	添加注释到数据库对象	COMMENT ON TABLE products IS ‘Table storing product information’;
SET	设置运行时参数的值	SET work_mem = ‘64MB’;
RESET	将运行时参数重置为默认值	RESET work_mem;
REINDEX	重建索引	REINDEX INDEX idx_name;
ALTER USER	修改用户属性	ALTER USER john WITH PASSWORD ‘newpassword’;
ALTER ROLE	修改角色属性	ALTER ROLE admin WITH LOGIN;
DROP FUNCTION	删除存储过程或函数	DROP FUNCTION calculate_tax(numeric);
DROP TRIGGER	删除触发器	DROP TRIGGER update_inventory ON orders;
DROP VIEW	删除视图	DROP VIEW monthly_sales;
ALTER FUNCTION	修改存储过程或函数	ALTER FUNCTION calculate_tax(numeric) OWNER TO new_owner;
ALTER TRIGGER	修改触发器	ALTER TRIGGER update_inventory DISABLE;
ALTER VIEW	修改视图	ALTER VIEW monthly_sales RENAME TO quarterly_sales;
SET CONSTRAINTS	设置或重置约束的延迟检查	SET CONSTRAINTS ALL IMMEDIATE;
CHECKPOINT	手动触发检查点以在后台写入数据	CHECKPOINT;
CREATE TYPE	创建新数据类型	CREATE TYPE mood AS ENUM (‘happy’, ‘sad’, ‘neutral’);
CREATE DOMAIN	创建新域类型	CREATE DOMAIN us_phone AS text CHECK (VALUE ~ ‘^\d{3}-\d{3}-\d{4}$’);
CREATE AGGREGATE	创建聚合函数	CREATE AGGREGATE median (sfunc = median_state, stype = anyelement, finalfunc = median_final) (sfunc = median_state, stype = anyelement, initcond = ‘{}’);

注意，这些示例在实际使用时需要根据情况进行调整、验证，确保在生产环境中正常使用。

postgresql sql 语句

最后修改时间：2023-08-18 09:20:39

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

与PostgreSQL对话（共112篇）

从实践出发，探寻PostgreSQL奥秘和逻辑，与思想共舞，共赴一场系统、数据与人的深度对话。 Starting from practice, we explore the mysteries and logic of PostgreSQL, dance with ideas, and embark on a deep dialogue between systems, data, and people.

50条PostgreSQL常见SQL语句

文章被以下合辑收录

评论