命令纪录来自 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
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
