Home | 簡體中文 | 繁體中文 | 雜文 | 打賞(Donations) | ITEYE 博客 | OSChina 博客 | Facebook | Linkedin | 知乎專欄 | Search | Email

部分 III. PostgreSQL

目錄

21. PostgreSQL 安裝
21.1. Ubuntu 12.04.1 LTS 安裝
21.2. YUM 預設源安裝
21.3. PostgreSQL 官方 YUM 源安裝
21.3.1. 9.2
21.3.2. 9.3
21.3.3. 9.4
21.3.4. 9.6
21.3.5. PostgreSQL 10
21.4. PostgreSQL 配置
21.4.1. postgresql.conf
21.4.2. pg_hba.conf
21.5. 創建dba用戶
22. PostgreSQL 命令
22.1. psql - PostgreSQL interactive terminal
23. Database Administration
23.1. User Account Management
23.1.1. Create User
23.1.2. Drop User
23.1.3. Rename User
23.1.4. SET PASSWORD
23.2. Access Privilege System
23.2.1. SHOW GRANTS
23.2.2. show privileges
23.2.3. Grant privileges
23.2.4. Revoke privileges
23.2.5. Show Privileges
23.2.6. MAX_QUERIES_PER_HOUR/MAX_UPDATES_PER_HOUR
23.2.7. Table Privileges
23.2.8. Column Privileges
23.3. Maintenance 資料庫維護
23.3.1. CHECK 檢查表
23.3.2. ANALYZE 分析表
23.3.3. CHECKSUM
23.3.4. OPTIMIZE 優化表
23.3.5. REPAIR 修復
23.4. Backup and Recovery
23.4.1. Import / Export
23.4.1.1. Export(Backup)
23.4.1.2. Import(Recovery)
23.4.1.3. xml
23.4.1.4. 備份表數據
23.4.1.5. source
23.4.1.6. 使用 mysqlhotcopy 備份 MyISAM 引擎的資料庫
23.4.1.7. AutoMySQLBackup
23.4.1.8. xtrabackup - Open source backup tool for InnoDB and XtraDB.
23.4.2. Snapshot Backup
23.4.2.1. LVM Snapshot
23.4.2.2. Btrfs Snapshot
24. DDL - Data Definition Language
24.1. 資料庫管理(Database)
24.1.1. create
24.1.2. drop
24.1.3. Alter
24.1.4. Rename
24.1.5. CHARACTER
24.1.6. show create database
24.2. 表管理(Table)
24.2.1. 數據類型
24.2.1.1. SET 集合類型
24.2.2. create table ... select
24.2.3. modifiy table
24.2.4. TEMPORARY Table
24.2.5. Collate
24.2.6. CHARACTER
24.2.7. DEFAULT
24.2.7.1. AUTO_INCREMENT
24.2.7.2. 表存儲位置(DATA DIRECTORY)
24.2.8. KEY
24.2.8.1. PRIMARY KEY
24.2.9. COMMENT
24.2.10. Engine
24.2.10.1. FEDERATED
24.2.10.2. BLACKHOLE
24.3. Partitioning
24.3.1. RANGE
24.3.2. LIST
24.3.3. HASH
24.3.3.1. LINEAR HASH
24.3.4. KEY分區
24.3.5. Subpartitioning
24.3.6. 分區管理
24.3.6.1. 新增分區
24.3.6.2. 刪除分區
24.3.6.3. 重建分區
24.3.6.4. 分區維護
24.3.7. EXPLAIN PARTITIONS
24.3.8. SHOW CREATE TABLE
24.3.9. INFORMATION_SCHEMA.partitions 表
24.3.10. 分區數據操作
24.4. Index
24.4.1. SHOW INDEX
24.4.2. CREATE INDEX
24.4.3. DROP INDEX
24.4.4. rebuild
24.5. 外鍵(Foreign Key)
24.5.1. FOREIGN KEY (RESTRICT)
24.6. 視圖(View)
24.7. 存儲過程(PROCEDURE)
24.7.1. 存儲程序
24.7.2. 函數
24.8. 觸發器(Trigger)
24.8.1. create trigger
24.8.2. drop trigger
24.8.3. show triggers
24.8.4. EXAMPLE
24.8.4.1. BEFORE/AFTER
24.8.4.2. UUID
24.8.4.3. CALL PROCEDURE
24.9. 事件調度器(EVENT)
24.9.1. 啟用 EVENT
24.9.2. 創建 EVENT
24.9.3. 禁用/啟用
24.9.4. show events
25. DML (Data Manipulation Language)
25.1. INSERT
25.1.1. INSERT INTO ... SELECT
25.1.2. INSERT IGNORE
25.1.3. INSERT...ON DUPLICATE KEY UPDATE
25.2. REPLACE
25.3. DELETE
25.3.1. 刪除重複數據
26. SQL Statement Syntax
26.1. DISTINCT
26.2. group by
26.3. HAVING
26.4. REGEXP
26.5. IN / NOT IN
26.6. ALL / Any
26.7. exists, not exists
26.8. UNION
26.8.1. UNION ALL
26.9. OUTFILE/LOAD DATA INFILE
26.9.1. Export data to CSV from MySQL
26.9.2. Import data from CSV file.
26.10. MySQL 專有命令
26.10.1. SQL_NO_CACHE
26.10.2. SIGNAL Syntax
26.11. SQL 92
27. Functions and Operators
27.1. COUNT
27.2. UUID()
27.3. String
27.3.1. LEFT/RIGHT
27.3.2. RPAD/LPAD
27.3.3. CONCAT
27.3.4. GROUP_CONCAT
27.3.5. replace
27.3.6. SUBSTRING
27.3.7. SUBSTRING_INDEX
27.3.8. AES_ENCRYPT / AES_DECRYPT
27.4. Date and Time
27.4.1. year/month/day hour:minite:second
27.4.2. Unix time
27.4.3. DATE_FORMAT
27.4.4. DATE_SUB/DATE_ADD
27.4.4.1. DATE_ADD
27.4.5. datediff / timediff
27.5. 數值函數
27.5.1. cast 類型轉換
27.5.2. truncate 保留小數位數
27.5.3. MOD 求余
27.6. Control Flow Functions
28. PostgreSQL GUI
28.1. pgAdmin III
28.2. phpPgAdmin
28.3. Monitoring
28.3.1. PgBadger
28.4. pgModeler - PostgreSQL Database Modeler
29. Barman Backup & recovery for PostgreSQL
30. pgbouncer - lightweight connection pooler for PostgreSQL
30.1. 安裝 pgbouncer
30.1.1. Ubuntu
30.1.2. CentOS
30.2. 配置 pgbouncer
31. Foreign data wrappers
32. Barman for PostgreSQL
33. Connector
33.1. Pomm
34. Replication
34.1. Bucardo
35. FAQ
35.1. Reset root password 重置MySQL root密碼
35.2. 資料庫內容替換
35.3. 查看錯誤代碼
35.3.1. ERROR 1153 (08S01) at line 3168: Got a packet bigger than 'max_allowed_packet' bytes
35.3.2. ERROR 1129 (00000): Host 'XXXXXX' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
35.4. 臨時表是否需要建索引
35.5. Kill 腳本
35.6. ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
35.7. ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
35.8. ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
35.9. 重新整理AUTO_INCREMENT欄位
35.10. 轉換 latin1 到 UTF-8
35.11. this is incompatible with sql_mode=only_full_group_by
35.12. [Warning] Changed limits: max_open_files: 5000 (requested 20480)

PostgreSQL 實用實例參考