部分 VI. PostgreSQL
上一頁		下一頁

部分 VI. PostgreSQL

59. PostgreSQL 安裝

59.1. Ubuntu 12.04.1 LTS 安裝

59.2. YUM 預設源安裝

59.3. PostgreSQL 官方 YUM 源安裝

59.3.1. 9.2
59.3.2. 9.3
59.3.3. 9.4
59.3.4. 9.6
59.3.5. PostgreSQL 10

59.4. PostgreSQL 配置

59.4.1. postgresql.conf
59.4.2. pg_hba.conf

59.5. 創建dba用戶

60. Database Administration

60.1. User Account Management

60.1.1. Create User
60.1.2. Drop User
60.1.3. Rename User
60.1.4. SET PASSWORD

60.2. Access Privilege System

60.2.1. SHOW GRANTS
60.2.2. show privileges
60.2.3. Grant privileges
60.2.4. Revoke privileges
60.2.5. Show Privileges
60.2.6. MAX_QUERIES_PER_HOUR/MAX_UPDATES_PER_HOUR
60.2.7. Table Privileges
60.2.8. Column Privileges

60.3. Maintenance 資料庫維護

60.3.1. CHECK 檢查表
60.3.2. ANALYZE 分析表
60.3.3. CHECKSUM
60.3.4. OPTIMIZE 優化表
60.3.5. REPAIR 修復

60.4. INFORMATION_SCHEMA

60.4.1. 查詢表欄位
60.4.2. 列出所有觸發器

60.5. Backup and Recovery

60.5.1. Import / Export

60.5.1.1. Export(Backup)

60.5.1.2. Import(Recovery)

60.5.1.3. xml

60.5.1.4. 備份表數據

60.5.1.5. source

60.5.1.6. 使用 mysqlhotcopy 備份 MyISAM 引擎的資料庫

60.5.1.7. AutoMySQLBackup

60.5.1.8. xtrabackup - Open source backup tool for InnoDB and XtraDB.

60.5.1.8.1. Percona yum Repository
60.5.1.8.2. Creating an Incremental Backup

60.5.2. Snapshot Backup

60.5.2.1. LVM Snapshot
60.5.2.2. Btrfs Snapshot

61. PostgreSQL 命令

61.1. psql - PostgreSQL interactive terminal

61.1.1. 執行檔案

61.2. Backup / Restore

61.2.1. Backup
61.2.2. Restore
61.2.3. import
61.2.4. 導出指定 schema
61.2.5. PostgreSQL for windows
61.2.6. 大型資料庫備份
61.2.7. schema之間遷移數據

62. DDL - Data Definition Language

62.1. 資料庫管理(Database)

62.1.1. create
62.1.2. drop
62.1.3. Alter
62.1.4. Rename
62.1.5. CHARACTER
62.1.6. show create database

62.2. 表管理(Table)

62.2.1. 數據類型

62.2.1.1. SET 集合類型

62.2.2. create table ... select

62.2.3. modifiy table

62.2.4. TEMPORARY Table

62.2.5. Collate

62.2.6. CHARACTER

62.2.7. DEFAULT

62.2.7.1. AUTO_INCREMENT
62.2.7.2. TIMESTAMP NULL DEFAULT NULL ON UPDATE
62.2.7.3. 表存儲位置(DATA DIRECTORY)

62.2.8. KEY

62.2.8.1. PRIMARY KEY

62.2.9. AUTO_INCREMENT 定義初始值

62.2.10. COMMENT

62.2.11. Engine 存儲引擎

62.2.11.1. 顯示當前資料庫支持引擎
62.2.11.2. 切換引擎
62.2.11.3. FEDERATED
62.2.11.4. BLACKHOLE
62.2.11.5. ARCHIVE
62.2.11.6. CSV

62.3. Partitioning

62.3.1. RANGE

62.3.2. LIST

62.3.3. HASH

62.3.3.1. LINEAR HASH

62.3.4. KEY分區

62.3.5. Subpartitioning

62.3.6. 分區管理

62.3.6.1. 新增分區
62.3.6.2. 刪除分區
62.3.6.3. 重建分區
62.3.6.4. 分區維護

62.3.7. EXPLAIN PARTITIONS

62.3.8. SHOW CREATE TABLE

62.3.9. INFORMATION_SCHEMA.partitions 表

62.3.10. 分區數據操作

62.4. Index

62.4.1. SHOW INDEX
62.4.2. CREATE INDEX
62.4.3. DROP INDEX
62.4.4. rebuild

62.5. 外鍵(Foreign Key)

62.5.1. FOREIGN KEY (RESTRICT)

62.6. 視圖(View)

62.7. 存儲過程(PROCEDURE)

62.7.1. 存儲程序
62.7.2. EXECUTE 執行 SQL
62.7.3. PREPARE 傳遞參數
62.7.4. 存儲過程返回數據
62.7.5. 結果集轉JSON
62.7.6. 例子·過程返回結果

62.8. 函數

62.8.1. TIMESTAMP TO ISO8601

62.9. 觸發器(Trigger)

62.9.1. create trigger

62.9.1.1. Update 更新出發
62.9.1.2. Delete 刪除出發
62.9.1.3. Insert 插入出發

62.9.2. drop trigger

62.9.3. show triggers

62.9.3.1. SHOW CREATE TRIGGER

62.9.4. EXAMPLE

62.9.4.1. BEFORE/AFTER
62.9.4.2. UUID
62.9.4.3. CALL PROCEDURE

62.10. 事件調度器(EVENT)

62.10.1. 啟用 EVENT
62.10.2. 創建 EVENT
62.10.3. 禁用/啟用
62.10.4. show events
62.10.5. 實例·每月創建一個表

63. DML (Data Manipulation Language)

63.1. INSERT

63.1.1. INSERT INTO ... SELECT
63.1.2. INSERT IGNORE
63.1.3. INSERT...ON DUPLICATE KEY UPDATE

63.2. REPLACE

63.3. DELETE

63.3.1. 刪除重複數據

64. SQL Statement Syntax

64.5. IN / NOT IN

64.6. ALL / Any

64.7. exists, not exists

64.8. UNION

64.8.1. UNION ALL
64.8.2. 兩張表欄位不對等解決方法

64.9. OUTFILE/LOAD DATA INFILE

64.9.1. Export data to CSV from MySQL
64.9.2. Import data from CSV file.

64.10. CASE Syntax

64.11. MySQL 專有命令

64.11.1. SQL_NO_CACHE
64.11.2. SIGNAL Syntax

64.12. SQL 92

65. Functions and Operators

65.1. COUNT

65.2. group_concat() 列傳行

65.3. UUID()

65.4. String

65.4.1. LEFT/RIGHT
65.4.2. RPAD/LPAD
65.4.3. CONCAT
65.4.4. CONCAT_WS
65.4.5. 連結所有欄位
65.4.6. GROUP_CONCAT
65.4.7. replace
65.4.8. SUBSTRING
65.4.9. SUBSTRING_INDEX
65.4.10. AES_ENCRYPT / AES_DECRYPT

65.5. Date and Time

65.5.1. year/month/day hour:minite:second

65.5.2. Unix time

65.5.3. DATE_FORMAT

65.5.4. DATE_SUB/DATE_ADD

65.5.4.1. DATE_ADD

65.5.5. datediff / timediff

65.6. 數值函數

65.6.1. cast 類型轉換
65.6.2. truncate 保留小數位數
65.6.3. MOD 求余

65.7. Control Flow Functions

66. PostgreSQL GUI

66.1. pgAdmin III

66.2. phpPgAdmin

66.3. Monitoring

66.3.1. PgBadger

66.4. pgModeler - PostgreSQL Database Modeler

67. Barman Backup & recovery for PostgreSQL

68. pgbouncer - lightweight connection pooler for PostgreSQL

68.1. 安裝 pgbouncer

68.1.1. Ubuntu
68.1.2. CentOS

68.2. 配置 pgbouncer

69. Foreign data wrappers

70. Barman for PostgreSQL

71. Connector

71.1. Pomm

72. Replication

72.1. Bucardo

73. FAQ

73.1. Reset root password 重置MySQL root密碼

73.1.1. MySQL 5.7.x
73.1.2. MySQL 8.0

73.2. 資料庫內容替換

73.3. 查看錯誤代碼

73.3.1. ERROR 1153 (08S01) at line 3168: Got a packet bigger than 'max_allowed_packet' bytes
73.3.2. ERROR 1129 (00000): Host 'XXXXXX' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

73.4. 臨時表是否需要建索引

73.5. Kill 腳本

73.6. ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

73.7. ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

73.8. ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

73.9. 重新整理AUTO_INCREMENT欄位

73.10. 轉換 latin1 到 UTF-8

73.11. this is incompatible with sql_mode=only_full_group_by

73.12. [Warning] Changed limits: max_open_files: 5000 (requested 20480)

73.13. ERROR 1364: 1364: Field 'id' doesn't have a default value

73.14. ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

73.15. ERROR 1086 (HY000): File '/var/lib/mysql-files/order.txt' already exists

73.16. ERROR 1415: Not allowed to return a result set from a trigger

73.17. Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such fileor directory

73.18. com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Public Key Retrieval is not allowed

PostgreSQL 實用實例參考

上一頁		下一頁
第 58 章 LeoFS	起始頁	第 59 章 PostgreSQL 安裝