PostgreSQL数据库操作(2)

一、数据库常规操作

1.创建数据库
// 数据库登录
psql -h 服务器 -U 用户名 -d 数据库 -p 端口
./psql -U postgres -p 5432 -h 127.0.0.1

// 创建数据库
postgres=# create database webgame;
CREATE DATABASE

// 查看数据库
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
 template0 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 webgame   | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
(4 rows)

# 删除数据库
postgres=# drop database webgame;
DROP DATABASE

2.创建数据表
// 创建表
webgame=# create table products(product_no int,name text,price numeric);
CREATE TABLE

//删除表
webgame=# drop table products;
DROP TABLE

// serial自增字段
webgame=# create table products(product_no serial,name text,price numeric);
CREATE TABLE

//带缺省值的表
webgame=# create table products(product_no serial,name text,price numeric default 9.99);
CREATE TABLE

// 唯一键
webgame=# create table products(product_no serial,name text,price numeric default 9.99,UNIQUE(product_no));
CREATE TABLE

// 主键
create table products(product_no serial,name text,price numeric default 9.99,primary key(product_no));

3. 表的修改
// 添加字段
webgame=# alter table products add column describetion text;
ALTER TABLE

//删除字段
webgame=# alter table products drop column describetion;
ALTER TABLE

//修改字段名
webgame=# alter table products rename column product_no to product_number;
ALTER TABLE

//修改表名
webgame=# alter table products rename to items;
ALTER TABLE

4.索引
// Hash
// 散列(Hash)索引只能处理简单的等于比较。当索引列使用等于操作符进行比较时，查询规划器会考虑使用散列索引
webgame=# create index name on items using hash(name);
CREATE INDEX

// B-Tree
// B-Tree索引主要用于等于和范围查询，特别是当索引列包含操作符" <、<=、=、>=和>"作为查询条件时，PostgreSQL的查询规划器都会考虑使用B-Tree索引
webgame=# create index measurement_city_id_index on measurement(city_id);
CREATE INDEX

// 复合索引
webgame=# CREATE TABLE test2 (
        major int,
        minor int,
        name varchar
    );
CREATE TABLE

webgame=# create index test2_mm_idx on test2(major,minor);
CREATE INDEX

//唯一索引
webgame=# create unique index idx_name on test2(name);
CREATE INDEX

 

表的分区
概述分区表：
    分区的意思是把逻辑上的一个大表分割成物理上的几块儿，分区可以提供若干好处：
    1). 某些类型的查询性能可以得到极大提升。
    2). 更新的性能也可以得到提升，因为表的每块的索引要比在整个数据集上的索引要小。如果索引不能全部放在内存里，那么在索引上的读和写都会产生更多的磁盘访问。
    3). 批量删除可以用简单地删除某个分区来实现。
    4). 将很少用的数据可以移动到便宜的、慢一些地存储介质上。

1）创建“主表”，所有分区都从它继承
webgame=# create table measurement(
city_id int not null,
logdate date not null,
peaktemp int);
CREATE TABLE

2）创建几个 子表，这些子表将不会再增加任何字段，我们将子表称作分区
webgame=# create table measurement_yy22mm01()inherits(measurement);
CREATE TABLE
webgame=# create table measurement_yy22mm02()inherits(measurement);
CREATE TABLE
webgame=# create table measurement_yy22mm03()inherits(measurement);
CREATE TABLE

3）postgresql管理子表
alter table measurement_yy22mm03 no inherit measurement;
该方式仅仅是使子表脱离了原有的主表，而存储在子表中的数据仍然可以得到访问，因为此时该表已经被还原成一个普通的数据表了。这样对于数据库的DBA来说，就可以在此时对该表进行必要的维护操作，如数据清理、归档等，在完成诸多例行性的操作之后，
就可以考虑是直接删除该表(DROP TABLE)，还是先清空该表的数据(TRUNCATE TABLE)，之后再让该表重新继承主表

重新继承主表：
ALTER TABLE measurement_yy22mm03 INHERIT measurement;