Home | 简体中文 | 繁体中文 | 杂文 | 打赏(Donations) | 云栖社区 | OSChina 博客 | Facebook | Linkedin | 知乎专栏 | Github | Search | About

部分 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. PostgreSQL 命令
60.1. psql - PostgreSQL interactive terminal
61. Database Administration
61.1. User Account Management
61.1.1. Create User
61.1.2. Drop User
61.1.3. Rename User
61.1.4. SET PASSWORD
61.2. Access Privilege System
61.2.1. SHOW GRANTS
61.2.2. show privileges
61.2.3. Grant privileges
61.2.4. Revoke privileges
61.2.5. Show Privileges
61.2.6. MAX_QUERIES_PER_HOUR/MAX_UPDATES_PER_HOUR
61.2.7. Table Privileges
61.2.8. Column Privileges
61.3. Maintenance 数据库维护
61.3.1. CHECK 检查表
61.3.2. ANALYZE 分析表
61.3.3. CHECKSUM
61.3.4. OPTIMIZE 优化表
61.3.5. REPAIR 修复
61.4. INFORMATION_SCHEMA
61.4.1. 查询表字段
61.4.2. 列出所有触发器
61.5. Backup and Recovery
61.5.1. Import / Export
61.5.1.1. Export(Backup)
61.5.1.2. Import(Recovery)
61.5.1.3. xml
61.5.1.4. 备份表数据
61.5.1.5. source
61.5.1.6. 使用 mysqlhotcopy 备份 MyISAM 引擎的数据库
61.5.1.7. AutoMySQLBackup
61.5.1.8. xtrabackup - Open source backup tool for InnoDB and XtraDB.
61.5.1.8.1. Percona yum Repository
61.5.1.8.2. Creating an Incremental Backup
61.5.2. Snapshot Backup
61.5.2.1. LVM Snapshot
61.5.2.2. Btrfs Snapshot
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. COMMENT
62.2.10. Engine 存储引擎
62.2.10.1. 显示当前数据库支持引擎
62.2.10.2. 切换引擎
62.2.10.3. FEDERATED
62.2.10.4. BLACKHOLE
62.2.10.5. ARCHIVE
62.2.10.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.1. DISTINCT
64.2. group by
64.3. HAVING
64.4. REGEXP
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 实用实例参考