Home | 簡體中文 | 繁體中文 | 雜文 | 打賞(Donations) | 雲棲社區 | OSChina 博客 | Facebook | Linkedin | 知乎專欄 | Github | Search | About

部分 V. PostgreSQL

目錄

47. PostgreSQL 安裝
47.1. Ubuntu 12.04.1 LTS 安裝
47.2. YUM 預設源安裝
47.3. PostgreSQL 官方 YUM 源安裝
47.3.1. 9.2
47.3.2. 9.3
47.3.3. 9.4
47.3.4. 9.6
47.3.5. PostgreSQL 10
47.4. PostgreSQL 配置
47.4.1. postgresql.conf
47.4.2. pg_hba.conf
47.5. 創建dba用戶
48. PostgreSQL 命令
48.1. psql - PostgreSQL interactive terminal
49. Database Administration
49.1. User Account Management
49.1.1. Create User
49.1.2. Drop User
49.1.3. Rename User
49.1.4. SET PASSWORD
49.2. Access Privilege System
49.2.1. SHOW GRANTS
49.2.2. show privileges
49.2.3. Grant privileges
49.2.4. Revoke privileges
49.2.5. Show Privileges
49.2.6. MAX_QUERIES_PER_HOUR/MAX_UPDATES_PER_HOUR
49.2.7. Table Privileges
49.2.8. Column Privileges
49.3. Maintenance 資料庫維護
49.3.1. CHECK 檢查表
49.3.2. ANALYZE 分析表
49.3.3. CHECKSUM
49.3.4. OPTIMIZE 優化表
49.3.5. REPAIR 修復
49.4. INFORMATION_SCHEMA
49.4.1. 查詢表欄位
49.4.2. 列出所有觸發器
49.5. Backup and Recovery
49.5.1. Import / Export
49.5.1.1. Export(Backup)
49.5.1.2. Import(Recovery)
49.5.1.3. xml
49.5.1.4. 備份表數據
49.5.1.5. source
49.5.1.6. 使用 mysqlhotcopy 備份 MyISAM 引擎的資料庫
49.5.1.7. AutoMySQLBackup
49.5.1.8. xtrabackup - Open source backup tool for InnoDB and XtraDB.
49.5.1.8.1. Percona yum Repository
49.5.1.8.2. Creating an Incremental Backup
49.5.2. Snapshot Backup
49.5.2.1. LVM Snapshot
49.5.2.2. Btrfs Snapshot
50. DDL - Data Definition Language
50.1. 資料庫管理(Database)
50.1.1. create
50.1.2. drop
50.1.3. Alter
50.1.4. Rename
50.1.5. CHARACTER
50.1.6. show create database
50.2. 表管理(Table)
50.2.1. 數據類型
50.2.1.1. SET 集合類型
50.2.2. create table ... select
50.2.3. modifiy table
50.2.4. TEMPORARY Table
50.2.5. Collate
50.2.6. CHARACTER
50.2.7. DEFAULT
50.2.7.1. AUTO_INCREMENT
50.2.7.2. TIMESTAMP NULL DEFAULT NULL ON UPDATE
50.2.7.3. 表存儲位置(DATA DIRECTORY)
50.2.8. KEY
50.2.8.1. PRIMARY KEY
50.2.9. COMMENT
50.2.10. Engine 存儲引擎
50.2.10.1. 顯示當前資料庫支持引擎
50.2.10.2. 切換引擎
50.2.10.3. FEDERATED
50.2.10.4. BLACKHOLE
50.2.10.5. ARCHIVE
50.2.10.6. CSV
50.3. Partitioning
50.3.1. RANGE
50.3.2. LIST
50.3.3. HASH
50.3.3.1. LINEAR HASH
50.3.4. KEY分區
50.3.5. Subpartitioning
50.3.6. 分區管理
50.3.6.1. 新增分區
50.3.6.2. 刪除分區
50.3.6.3. 重建分區
50.3.6.4. 分區維護
50.3.7. EXPLAIN PARTITIONS
50.3.8. SHOW CREATE TABLE
50.3.9. INFORMATION_SCHEMA.partitions 表
50.3.10. 分區數據操作
50.4. Index
50.4.1. SHOW INDEX
50.4.2. CREATE INDEX
50.4.3. DROP INDEX
50.4.4. rebuild
50.5. 外鍵(Foreign Key)
50.5.1. FOREIGN KEY (RESTRICT)
50.6. 視圖(View)
50.7. 存儲過程(PROCEDURE)
50.7.1. 存儲程序
50.7.2. EXECUTE 執行 SQL
50.7.3. PREPARE 傳遞參數
50.7.4. 存儲過程返回數據
50.7.5. 結果集轉JSON
50.7.6. 例子·過程返回結果
50.8. 函數
50.8.1. TIMESTAMP TO ISO8601
50.9. 觸發器(Trigger)
50.9.1. create trigger
50.9.1.1. Update 更新出發
50.9.1.2. Delete 刪除出發
50.9.1.3. Insert 插入出發
50.9.2. drop trigger
50.9.3. show triggers
50.9.3.1. SHOW CREATE TRIGGER
50.9.4. EXAMPLE
50.9.4.1. BEFORE/AFTER
50.9.4.2. UUID
50.9.4.3. CALL PROCEDURE
50.10. 事件調度器(EVENT)
50.10.1. 啟用 EVENT
50.10.2. 創建 EVENT
50.10.3. 禁用/啟用
50.10.4. show events
50.10.5. 實例·每月創建一個表
51. DML (Data Manipulation Language)
51.1. INSERT
51.1.1. INSERT INTO ... SELECT
51.1.2. INSERT IGNORE
51.1.3. INSERT...ON DUPLICATE KEY UPDATE
51.2. REPLACE
51.3. DELETE
51.3.1. 刪除重複數據
52. SQL Statement Syntax
52.1. DISTINCT
52.2. group by
52.3. HAVING
52.4. REGEXP
52.5. IN / NOT IN
52.6. ALL / Any
52.7. exists, not exists
52.8. UNION
52.8.1. UNION ALL
52.9. OUTFILE/LOAD DATA INFILE
52.9.1. Export data to CSV from MySQL
52.9.2. Import data from CSV file.
52.10. CASE Syntax
52.11. MySQL 專有命令
52.11.1. SQL_NO_CACHE
52.11.2. SIGNAL Syntax
52.12. SQL 92
53. Functions and Operators
53.1. COUNT
53.2. group_concat() 列傳行
53.3. UUID()
53.4. String
53.4.1. LEFT/RIGHT
53.4.2. RPAD/LPAD
53.4.3. CONCAT
53.4.4. CONCAT_WS
53.4.5. 連結所有欄位
53.4.6. GROUP_CONCAT
53.4.7. replace
53.4.8. SUBSTRING
53.4.9. SUBSTRING_INDEX
53.4.10. AES_ENCRYPT / AES_DECRYPT
53.5. Date and Time
53.5.1. year/month/day hour:minite:second
53.5.2. Unix time
53.5.3. DATE_FORMAT
53.5.4. DATE_SUB/DATE_ADD
53.5.4.1. DATE_ADD
53.5.5. datediff / timediff
53.6. 數值函數
53.6.1. cast 類型轉換
53.6.2. truncate 保留小數位數
53.6.3. MOD 求余
53.7. Control Flow Functions
54. PostgreSQL GUI
54.1. pgAdmin III
54.2. phpPgAdmin
54.3. Monitoring
54.3.1. PgBadger
54.4. pgModeler - PostgreSQL Database Modeler
55. Barman Backup & recovery for PostgreSQL
56. pgbouncer - lightweight connection pooler for PostgreSQL
56.1. 安裝 pgbouncer
56.1.1. Ubuntu
56.1.2. CentOS
56.2. 配置 pgbouncer
57. Foreign data wrappers
58. Barman for PostgreSQL
59. Connector
59.1. Pomm
60. Replication
60.1. Bucardo
61. FAQ
61.1. Reset root password 重置MySQL root密碼
61.2. 資料庫內容替換
61.3. 查看錯誤代碼
61.3.1. ERROR 1153 (08S01) at line 3168: Got a packet bigger than 'max_allowed_packet' bytes
61.3.2. ERROR 1129 (00000): Host 'XXXXXX' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
61.4. 臨時表是否需要建索引
61.5. Kill 腳本
61.6. ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
61.7. ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
61.8. ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
61.9. 重新整理AUTO_INCREMENT欄位
61.10. 轉換 latin1 到 UTF-8
61.11. this is incompatible with sql_mode=only_full_group_by
61.12. [Warning] Changed limits: max_open_files: 5000 (requested 20480)
61.13. ERROR 1364: 1364: Field 'id' doesn't have a default value
61.14. ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
61.15. ERROR 1086 (HY000): File '/var/lib/mysql-files/order.txt' already exists
61.16. ERROR 1415: Not allowed to return a result set from a trigger

PostgreSQL 實用實例參考