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.4. PostgreSQL 配置
21.4.1. postgresql.conf
21.4.2. pg_hba.conf
21.5. 創建dba用戶
22. Database Administration
22.1. User Account Management
22.1.1. Create User
22.1.2. Drop User
22.1.3. Rename User
22.1.4. SET PASSWORD
22.2. Access Privilege System
22.2.1. SHOW GRANTS
22.2.2. show privileges
22.2.3. Grant privileges
22.2.4. Revoke privileges
22.2.5. Show Privileges
22.2.6. MAX_QUERIES_PER_HOUR/MAX_UPDATES_PER_HOUR
22.2.7. Table Privileges
22.2.8. Column Privileges
22.3. Maintenance 資料庫維護
22.3.1. CHECK 檢查表
22.3.2. ANALYZE 分析表
22.3.3. CHECKSUM
22.3.4. OPTIMIZE 優化表
22.3.5. REPAIR 修復
22.4. Backup and Recovery
22.4.1. Import / Export
22.4.1.1. Export(Backup)
22.4.1.2. Import(Recovery)
22.4.1.3. xml
22.4.1.4. 備份表數據
22.4.1.5. source
22.4.1.6. 使用 mysqlhotcopy 備份 MyISAM 引擎的資料庫
22.4.1.7. AutoMySQLBackup
22.4.1.8. xtrabackup - Open source backup tool for InnoDB and XtraDB.
22.4.2. Snapshot Backup
22.4.2.1. LVM Snapshot
22.4.2.2. Btrfs Snapshot
23. DDL - Data Definition Language
23.1. 資料庫管理(Database)
23.1.1. create
23.1.2. drop
23.1.3. Alter
23.1.4. Rename
23.1.5. CHARACTER
23.1.6. show create database
23.2. 表管理(Table)
23.2.1. 數據類型
23.2.1.1. SET 集合類型
23.2.2. create table ... select
23.2.3. modifiy table
23.2.4. TEMPORARY Table
23.2.5. Collate
23.2.6. CHARACTER
23.2.7. DEFAULT
23.2.7.1. AUTO_INCREMENT
23.2.7.2. 表存儲位置(DATA DIRECTORY)
23.2.8. KEY
23.2.8.1. PRIMARY KEY
23.2.9. COMMENT
23.2.10. Engine
23.2.10.1. FEDERATED
23.2.10.2. BLACKHOLE
23.3. Partitioning
23.3.1. RANGE
23.3.2. LIST
23.3.3. HASH
23.3.3.1. LINEAR HASH
23.3.4. KEY分區
23.3.5. Subpartitioning
23.3.6. 分區管理
23.3.6.1. 新增分區
23.3.6.2. 刪除分區
23.3.6.3. 重建分區
23.3.6.4. 分區維護
23.3.7. EXPLAIN PARTITIONS
23.3.8. SHOW CREATE TABLE
23.3.9. INFORMATION_SCHEMA.partitions 表
23.3.10. 分區數據操作
23.4. Index
23.4.1. SHOW INDEX
23.4.2. CREATE INDEX
23.4.3. DROP INDEX
23.4.4. rebuild
23.5. 外鍵(Foreign Key)
23.5.1. FOREIGN KEY (RESTRICT)
23.6. 視圖(View)
23.7. 存儲過程(PROCEDURE)
23.7.1. 存儲程序
23.7.2. 函數
23.8. 觸發器(Trigger)
23.8.1. create trigger
23.8.2. drop trigger
23.8.3. show triggers
23.8.4. EXAMPLE
23.8.4.1. BEFORE/AFTER
23.8.4.2. UUID
23.8.4.3. CALL PROCEDURE
23.9. 事件調度器(EVENT)
23.9.1. 啟用 EVENT
23.9.2. 創建 EVENT
23.9.3. 禁用/啟用
23.9.4. show events
24. DML (Data Manipulation Language)
24.1. INSERT
24.1.1. INSERT INTO ... SELECT
24.1.2. INSERT IGNORE
24.1.3. INSERT...ON DUPLICATE KEY UPDATE
24.2. REPLACE
24.3. DELETE
24.3.1. 刪除重複數據
25. SQL Statement Syntax
25.1. DISTINCT
25.2. group by
25.3. HAVING
25.4. REGEXP
25.5. IN / NOT IN
25.6. ALL / Any
25.7. exists, not exists
25.8. UNION
25.8.1. UNION ALL
25.9. OUTFILE/LOAD DATA INFILE
25.9.1. Export data to CSV from MySQL
25.9.2. Import data from CSV file.
25.10. MySQL 專有命令
25.10.1. SQL_NO_CACHE
25.10.2. SIGNAL Syntax
25.11. SQL 92
26. Functions and Operators
26.1. COUNT
26.2. UUID()
26.3. String
26.3.1. LEFT/RIGHT
26.3.2. RPAD/LPAD
26.3.3. CONCAT
26.3.4. GROUP_CONCAT
26.3.5. replace
26.3.6. SUBSTRING
26.3.7. SUBSTRING_INDEX
26.3.8. AES_ENCRYPT / AES_DECRYPT
26.4. Date and Time
26.4.1. year/month/day hour:minite:second
26.4.2. Unix time
26.4.3. DATE_FORMAT
26.4.4. DATE_SUB/DATE_ADD
26.4.4.1. DATE_ADD
26.4.5. datediff / timediff
26.5. 數值函數
26.5.1. cast 類型轉換
26.5.2. truncate 保留小數位數
26.5.3. MOD 求余
26.6. Control Flow Functions
27. PostgreSQL GUI
27.1. pgAdmin III
27.2. phpPgAdmin
27.3. Monitoring
27.3.1. PgBadger
27.4. pgModeler - PostgreSQL Database Modeler
28. Barman Backup & recovery for PostgreSQL
29. pgbouncer - lightweight connection pooler for PostgreSQL
29.1. 安裝 pgbouncer
29.1.1. Ubuntu
29.1.2. CentOS
29.2. 配置 pgbouncer
30. Foreign data wrappers
31. Barman for PostgreSQL
32. Connector
32.1. Pomm
33. Replication
33.1. Bucardo
34. FAQ
34.1. Reset root password 重置MySQL root密碼
34.2. 資料庫內容替換
34.3. 查看錯誤代碼
34.3.1. ERROR 1153 (08S01) at line 3168: Got a packet bigger than 'max_allowed_packet' bytes
34.3.2. ERROR 1129 (00000): Host 'XXXXXX' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
34.4. 臨時表是否需要建索引
34.5. Kill 腳本
34.6. ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
34.7. ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
34.8. ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
34.9. 重新整理AUTO_INCREMENT欄位
34.10. 轉換 latin1 到 UTF-8
34.11. this is incompatible with sql_mode=only_full_group_by
34.12. [Warning] Changed limits: max_open_files: 5000 (requested 20480)

PostgreSQL 實用實例參考