postgresql常用命令

命令纪录来自 http://www.php100.com/manual/PostgreSQL8/

本机环境ubuntu:
adduser postgres        # 如果没有超级管理员 增加超级管理员
sudo su - postgres      # 登录postgres账户
ALTER USER postgres PASSWORD 'new pwd'; # 修改密码 修改密码后需要重启服务
sudo createdb -e demo -E utf-8 # 创建utf-8数据库
CREATE DATABASE dbname TEMPLATE template0; # 通过拷贝 template0 的方法创建一个数据库
psql # 默认进入当前系统账户同名的数据库
SELECT version(); # 查看版本
SELECT current_date; # 查看当前日期


SELECT current_database()  name    当前数据库的名字
SELECT current_schema()    name    当前模式的名字
SELECT current_schemas(boolean)    name[]  在搜索路径中的模式名字
SELECT current_user        name    目前执行环境下的用户名
SELECT inet_client_addr()  inet    连接的远端址
SELECT inet_client_port()  int     连接的远端端口
SELECT inet_server_addr()  inet    连接的本地地址
SELECT inet_server_port()  int     连接的本地端口
SELECT session_user        name    会话用户名
pg_postmaster_start_time()    timestamp with time zone    postmaster 启动的时间
user                name    等于 current_user
version()           text    PostgreSQL 版本信息


命令结尾 ;  # 跟mysql一样
-- # 注释
/* 多行注释 */
+ - * / < > = ~ ! @ # % ^ & | ` ? # 操作符
'字符串中有引号的标准兼容方法是连续输入两个单引号''' 或 'abc\''但已经不建议使用
\q              # 退出
\l              # 查看所有数据库
\dt             # 查看所有表
\i basics.sql   # 导入sql
psql -d database -h localhost -f test.sql # 导入sql
命令大小写不敏感 通常命令大写
# 创建表 空格 tab 换行符 都可以作为分割
CREATE TABLE tablename(
    city varchar(80),
    date date,
    
);
point # 类型的一种 坐标格式 '(1,2)'


CREATE TABLE products (
    city varchar(80) PRIMARY KEY,   -- 主键约束（唯一约束和非空约束）
    PRIMARY KEY (a, c)              -- 主键约束多个字段
    product_no integer DEFAULT nextval('products_product_no_seq'),# 自增 缩写 product_no SERIAL,
    num1 integer DEFAULT 9,     -- 缺省
    product_no integer UNIQUE,  -- 唯一约束
    UNIQUE (product_no)         -- 唯一约束另外一种写法
    UNIQUE (a, c)               -- 多字段唯一
    name text NOT NULL,         --非空约束
    price numeric CHECK (price > 0), -- 字段约束
    discounted_price numeric CHECK (discounted_price > 0),
    CHECK (price > discounted_price) -- 表约束
);
非空约束


删除表
DROP TABLE tablename;
插入数据 # data的插入数据比较灵活 除了数字类型 通常使用单引号包围
INSERT INTO tablename VALUES ('San Francisco','2009-01-01');
INSERT INTO tablename (city, date) VALUES ('San Francisco', '1994-11-29');


批量加入数据COPY
COPY tablename FROM '/home/user/tablename.txt';


查询
SELECT * FROM tablename; # 普通查询
SELECT city FROM tablename; # 查询某列
SELECT city,(num1+num2)/2 AS num FROM tablename; # num1等为可计算变量 num为新别名
SELECT city FROM tablename WHERE city='shanghai' AND data='2008-08-08'; # 常用操作符 AND OR NOT 允许
SELEDCDT * FROM tablename ORDER BY date, city; # 排序 DESC倒续 ASC
SELECT DISTINCT city FROM tablename; # 消除重复的行
SELECT table01.city,table02.name FROM table01,table02 WHERE city=name; # 使用table01的city和table02的name联合查询 会自动忽略table02不存在的数据
SELECT * FROM table01 INNER JOIN table02 ON (table01.city = table02.name);
SELECT * FROM table01 LEFT OUTER JOIN table02 ON (table01.city = table02.name); # 补齐table02不存在的数据 空替代
SELECT * FROM talble01 LIMIT 2 OFFSET 3; # 两条数据 忽略两条数据


比较操作符
<   小于
>   大于
<=  小于或等于
>=  大于或等于
=   等于
<> 或 != 不等于


CASE 略 给返回的数据建立别名 CASE WHEN a=1 THEN 'one'
COALESCE # 详见9.13
NULLIF
GREATEST 和 LEAST


EXISTS # 详见 9.16
INCREATE DATABASE dbname TEMPLATE template0;
NOT IN
ANY/SOME
ALL
逐行比较


聚合
SELECT max(num1) FROM tablename; #找出num1最大的行 其他有count(数目) sum(求和) min(最小) avg(平均)
SELECT city FROM tablename where num1=max(SELECT max(num1) FROM tablename); # 子查询
SELECT city, max(num1) FROM tablename GROUP BY city HAVING max(num1) < 40; # 分组聚合 使用HAVING再次过滤


UPDATE更新
UPDATE tablename set city='tset' WHERE city='beijing';


DELETE删除
DELETE FROM tablename WHERE city='beijing';


LIKE查询 9.7模式匹配
'abc' LIKE 'abc'    true
'abc' LIKE 'a%'     true
'abc' LIKE '_b_'    true
'abc' LIKE 'c'      false


视图 复杂查询的别名 可以嵌套
CREATE VIEW myview AS SELECT * FROM tablename; # 创建视图
SELECT * FROM myview; # 查询视图


外键创建
CREATE TABLE cities (
    city        varchar(80) primary key,
    location    point
);
CREATE TABLE weather (
    city        varchar(80) references cities(city), # city 缺省模式下 引用主键
    ...
);


删除一个被引用的数据
例 : city varchar(80) references cities(city) ON DELETE RESTRICT
NO ACTION 的意思是如果在检查约束的时候，如果还存在任何引用行，则抛出错误
RESTRICT    禁止删除
CASCADE     删除时 同步删除所有引用的行
SET NULL    删除后 赋值NULL
SET DEFAULT 删除后 设置默认值
除了ON DELETE 还有 ON UPDATE


事务
BEGIN;
UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';
-- 等等
COMMIT;


5.7.1. 创建一个模式


继承
CREATE TABLE cities (
    name            text,
    population      real,
    altitude        int     -- (单位是英尺)
);
CREATE TABLE capitals (
    state           char(2)
) INHERITS (cities);


继承仅查询单表 SELECT UPDATE DELETE都支持
SELECT * FROM ONLY cities;


修改表
增加字段
ALTER TABLE products ADD COLUMN description text;
ALTER TABLE products ADD COLUMN description text CHECK (description <> ''); # 同时增加约束
删除字段
ALTER TABLE products DROP COLUMN description;
ALTER TABLE products DROP COLUMN description CASCADE; # 同时删除任何依赖
增加约束
ALTER TABLE products ADD CHECK (name <> '');
ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);
ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;
ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;# 增加一个不能写成表约束的非空约束
删除约束
\d tablename 交互模式下查找约束
ALTER TABLE products DROP CONSTRAINT some_name;
ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL; # 删除非空约束
改变一个字段的缺省值
ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77; # 不影响已存在字段
ALTER TABLE products ALTER COLUMN price DROP DEFAULT; # 删除
修改一个字段的数据类型
ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);
给字段改名字
ALTER TABLE products RENAME COLUMN product_no TO product_number;
给表改名字
ALTER TABLE products RENAME TO items;


权限
SELECT，INSERT， UPDATE，DELETE，RULE， REFERENCES，TRIGGER，CREATE， TEMPORARY，EXECUTE，USAGE （ALL 所有权限）
权限到人
GRANT UPDATE ON accounts TO joe;
权限到组
GRANT SELECT ON accounts TO GROUP staff;
删除权限
REVOKE ALL ON kinds FROM manuel; # 废除用户 manuel 对视图 kinds 的所有权限
REVOKE ALL PRIVILEGES ON kinds FROM manuel; # 撤销所有我赋予的权限


模式
创建模式
CREATE SCHEMA myschema;
删除模式
DROP SCHEMA myschema;
DROP SCHEMA myschema CASCADE;
public # 默认模式
SHOW search_path; # 显示当前模式


创建索引
CREATE INDEX tablename_id_index ON tablename (id);# test1_id_index索引名 id列
CREATE INDEX tablename_mm_idx ON tablename (major, minor); # 多字段索引
CREATE UNIQUE INDEX name ON table (column [, ...]); # 唯一索引
索引类型 B-tree，R-tree，Hash 和 GiST
创建rtree类型
CREATE INDEX name ON table USING rtree (column);
部分索引，操作符表和表达式索引  略


并发控制和性能提升技巧 略

错误解决：

1.重复键违反唯一约束

原因：手动插入数据导致自曾主键序列没有改变

解决办法：

    查询当前序列：select currval('table_id_seq')

    查询已经存在的id最大值

    修改序列值为1234：alter sequence table_id_seq restart with 1234