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 实用实例参考