PostgreSQL 简介

基于 C++开发，免费使用、修改、和分发 PostgreSQL，不管是私用、商用、还是学术研究使用。

开始使用

Linux 下的安装

• 第一步：安装
  运行命令：sudo apt-get install postgresql -y

• 第二步：安全设置
  安装 postgresql 之后系统会自动创建一个空密码的 postgres 空密码用户，现在为她添加密码
  sudo passwd postgres

• 第三步：连接数据库
  psql postgres

• 第四步：查看帮助

postgres=# help
You are using psql, the command-line interface to PostgreSQL.
Type:  \copyright for distribution terms
       \h for help with SomethingQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

Mac 下的安装

• 第一步：安装
  运行命令：brew install postgresql

• 第二步：添加用户，创建数据库

createuser postgres
createdb -U postgres postgres

• 第三步：建立别名

alias pg.start='pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start'
alias pg.stop='pg_ctl -D /usr/local/var/postgres stop -s -m fast'

• 第四步：启动、连接数据库

pg.start
psql postgres

• 第五步：查看帮助

postgres=# help
You are using psql, the command-line interface to PostgreSQL.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

pgAdmin3

一个设计，维护和管理 Postgres 数据库用的通用工具。

危险！连接不成功要注意的地方

• 注意服务器的防火墙规则
• 修改服务器连接监听 （ postgersql.conf 配置文件 ）
• 修改连接认真方式 （ pg_hba.conf 配置文件 ）

SQL 的组成

SQL

操作数据库对象

没有权限怎么办？

必须使用超级管理员登陆才可以管理数据库对象，默认的 postgres 可能没有建立数据库权限。

权限

用户

使用 posgres 用户登陆依然可以看到其他用户的，找到你的超级用户且用该账户登陆。

使用 pgAdmin 操作

新建操作

数据库面板

新建面板

CREATE DATABASE apple
  WITH ENCODING='UTF8'
       OWNER="Yugo"
       CONNECTION LIMIT=-1;

COMMENT ON DATABASE apple
  IS '这是我的 Blog 的数据库';

编辑、删除操作

基本属性

编辑

• Create 脚本 （跟创建的时候 SQL 差不多，不过多了一些默认选项）
• 属性 （编辑）
• 删除/移除

数据库名称

修改了数据库的基本属性之后，还是会生成 SQL 语句

ALTER DATABASE apple
  RENAME TO banana;

使用 SQL 语句操作

使用 pgAdmin 练习 SQL

执行任意 SQL

执行 SQL 语句

选择你写好的语句，点击执行按钮

反馈

执行成功后，将会给你相应的反馈

使用终端进行操作

Yugo=# \c
You are now connected to database "Yugo" as user "Yugo".

\c 查看我当前用户，和数据库属于的用户

Yugo=# \l
                              List of databases
   Name    | Owner | Encoding |   Collate   |    Ctype    | Access privileges
-----------+-------+----------+-------------+-------------+-------------------
 Yugo      | Yugo  | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |
 banana    | Yugo  | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |
 db_milk   | Yugo  | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |
 postgres  | Yugo  | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |
 template0 | Yugo  | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/Yugo          +
           |       |          |             |             | Yugo=CTc/Yugo
 template1 | Yugo  | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/Yugo          +
           |       |          |             |             | Yugo=CTc/Yugo
(6 rows)

\l 查看所有数据库,相当于show databases

Yugo=# \c banana
You are now connected to database "banana" as user "Yugo".

\c banana 切换到banana数据库 ,相当于use banana

操作表

我们将通过 pgAdmin 的语句生成功能学习

创建表

banana=# create table users
banana-# (
banana(# id int primary key,
banana(# name varchar(25) not null,
banana(# email varchar(60) not null,
banana(# passwd varchar(255) not null);
CREATE TABLE

先创建一个 users 表

banana=# \d
       List of relations
 Schema | Name  | Type  | Owner
--------+-------+-------+-------
 public | users | table | Yugo
(1 row)

\d 显示所有的表

banana=# \d users
            Table "public.users"
 Column |          Type          | Modifiers
--------+------------------------+-----------
 id     | integer                | not null
 name   | character varying(25)  | not null
 email  | character varying(60)  | not null
 passwd | character varying(255) | not null
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)

\d users 显示表 users 的具体内容

脚本

可以看到在 pgAdmin 中已经可以看到表，点击生成脚本试一试

Create 脚本

CREATE TABLE public.users
(
  id integer NOT NULL,
  name character varying(25) NOT NULL,
  email character varying(60) NOT NULL,
  passwd character varying(255) NOT NULL,
  CONSTRAINT users_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.users
  OWNER TO "Yugo";

Select 脚本

SELECT id, name, email, passwd
  FROM public.users;

Insert 脚本

INSERT INTO public.users(
            id, name, email, passwd)
    VALUES (?, ?, ?, ?);

Update 脚本

UPDATE public.users
   SET id=?, name=?, email=?, passwd=?
 WHERE <condition>;

Delete 脚本

DELETE FROM public.users
 WHERE <condition>;

字段

字段也可以显示 Create 脚本

ALTER TABLE public.users ADD COLUMN name character varying(25);
ALTER TABLE public.users ALTER COLUMN name SET NOT NULL;

索引

CREATE INDEX "Index"
    ON public.users (id ASC NULLS LAST)

自己再手写一些脚本来当做练习

create table student(
    id int primary key,
    name varchar(30) not null,
    age numeric(3) not null,
    classroom numeric(4) not null,
    address varchar(40) not null
);

alter table student rename to person;

alter table person rename id to bh;

alter table person alter column name type varchar(50);

alter table person drop column classroom;

alter table person add column birthday date;

drop table if exists person;

数据类型

复习一下简单的类型

整数类型

浮点类型

来试一试这些类型吧

create table temp(
    x smallint,
    y int,
    z real,
    n numeric(5,3)
)

insert into temp values (2,3,2.55,6.213)

insert into temp values (2,3,2.55,44.412)

insert into temp values (2,3,2.55,44.4125) // 四舍五入

select * from temp;

时间与日期类型

动手试一试

create table temp1(
    t time,
    d date,
    tm timestamp
);

insert into temp1 values ('10:04:03','2013-04-23','2031-5-23 10:22:00');

select * from temp1;

字符串类型

动手吧

create table temp3(
    ch char(10),
    vch varchar(30),
    t text
);

insert into temp3 values('大表哥','大表哥1','大表哥2');

select * from temp3;

select concat('{',ch,'}'),concat('{',vch,'}'),concat('{',t,'}') from temp3;

可以看见ch的后边补了一些空白

选择正确的数据类型

主要目的：优化存储，提高数据库性能

• 正确使用整数类型和浮点类型
• 日期与时间类型
• Char 与 Varchar 之间的特点与选择（Char 自动补齐空格，占的空间大，但是在搜索的时候快）

算数运算符

• 加 +
• 减 -
• 乘 ×
• 除 /
• 求余、模运算 %

自己跑一跑

select 3+2 , 3-2 , 4*3 , 5/2, 3%2;

比较运算符

• Least 在俩个或者多个参数时，返回最小值
• greatest 在俩个或者多个参数时，返回最大值
• between and 判断一个值是否落在俩个值之间
• in 判断一个值是否是 in 列表中的任意一个值
• like 通配符匹配

自己跑一跑

select 1=0 , '2'=2, 'b'='b',null=null,null=1;

select 1=0 , '2'=2, 'b'='b',null=null,null=1;

select 'good'<>'god' , 1<>2, 2>1, 4!=4, 2>=1, 1>=1;

select 2 between 1 and 3, 2 between 3 and 5, 3 between 3 and 6  # 闭区间

select 2 in (2,3,4), 2 in (3,4,5), 2 not in (3,4,5);

select 'abc' like 'a%','abc' like '_b_','abc' not like '%d';

逻辑运算符号

• AND
• OR
• NOT

试一试

select not '1', not 'y', not '0', not 'n';

select '1' and 'y' , '1' and '0' , '0' and 'n';

select '1' or 'y' , '1' or '0' , '0' or 'n';

视图

SQL 里面的视图，就像一个函数，把一些语句放入这个函数，减少非专业人士的查询痛苦。

CREATE VIEW myview AS
    SELECT city, temp_lo, temp_hi, prcp, date, location
        FROM weather, cities
        WHERE city = name;

SELECT * FROM myview;

外键

保证数据完整

city    varchar(80) references cities(city)

事务

保证里面内容，都完全完成

BEGIN;
UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';
......
COMMIT;

继承

关键字inherits,继承人有被继承的所有属性，并且查询也会有点不一样。

CREATE TABLE cities (
    name            text,
    population      real,
    altitude        int     -- (单位是英尺)
);

CREATE TABLE capitals (     -- (州首府)
    state           char(2)
) INHERITS (cities);

SELECT name, altitude
    FROM cities
    WHERE altitude > 500;

以上找出所有海拔超过 500 英尺的城市的名字， 包括州首府

SELECT name, altitude
    FROM ONLY cities
    WHERE altitude > 500;

以上找出所有不是州首府并且位于海拔大于或等于 500 英尺的城市

深入查询

语法格式

select
    { * | <字段列表> }
from
    [
        <table_01>,<table_02>...
        [whrere <expression>]
        [group by <字段>]  分组
        [having <expression>]
        [order by <字段>]  排序
        [limit [<offset>,]<row count>
    ]

练习

create table dept (
  d_no int primary key,
  d_name varchar(30),
  d_location varchar(300)
);

insert into dept values (10,'运维部门','美国西雅图');
insert into dept values (20,'销售部门','中光广东');
insert into dept values (30,'测试部门','中国香港');
insert into dept values (40,'集成部门','中国北京');
insert into dept values (50,'财务部门','中国湖北');
insert into dept values (60,'开发部门','中国杭州');

create table employee (
  e_no int primary key, -- 雇员编号
  e_name varchar(30) not null,
  e_gender char(2) not null,
  dept_no int,
  e_job varchar(50) not null,
  e_salary numeric(9,2),
  e_hireDate date,
  constraint fk_tmp_deptno foreign key (dept_no) references dept(d_no)
);

insert into employee values (100,'赵志宇','f',10,'开发工程师',5000,'2010-01-01');
insert into employee values (101,'许啦啦','f',10,'开发工程师',6000,'2013-04-03');
insert into employee values (102,'龙飞机','f',10,'开发经理',8000,'2008-01-01');
insert into employee values (103,'黄飞飞','m',20,'测试工程师',8000,'2009-01-02');
insert into employee values (104,'赵大炮','f',20,'测试工程师',6000,'2012-01-02');
insert into employee values (105,'赵飞机','f',20,'xiaoshou',5000,'2015-01-02');
insert into employee values (106,'吴龟龟','m',30,'xiaoshou',8000,'2014-01-02');
insert into employee values (107,'冯久久','f',30,'xiaoshou',6000,'2002-01-02');
insert into employee values (108,'谭迪迪','f',40,'chaiwu',7000,'2003-01-02');
insert into employee values (109,'廖饼饼','m',40,'chaiwu',6000,'2012-01-02');
insert into employee values (110,'吴大锤','f',null,'实习工程师',2000,'2011-01-02');
insert into employee values (111,'陈大大','f',null,'实习工程师',2000,'2010-01-02');

### select
存取的数据就是用来查的，所以说最基本的操作也是最重要的操作，查询。

select * from dept;

select * from employee;

select e_no,e_name,e_hireDate from employee;  -- 查询部分字段

select e.e_no,e.e_name,e.e_hreDate from employee as e; -- 查询部分字段，且为表设置别名

select e.e_no as a,e.e_name as b,e.e_hreDate as c from employee as e; -- 为字段显示别名

select e_no,e_name,e_salary from employee where e_salary < 5000;

select e_no,e_name,e_gender from employee where e_gender = 'f';

select 2 in (2,3,4); -- true  , 用于 where 中

select e_no,e_name,dept_no from employee where dept_no in (20,30);

select e_no,e_name,dept_no from employee where dept_no not in (20,30);

select 2 between 2 and 4; -- true , 2 in [2,4] 用于日期方便

select e_no,e_name,dept_no,e_hireDate from employee  where e_hireDate between '2012-03-01' and '2015-01-01';

select 'abc' like 'a%';

select 'abc' like 'a__';

select e_no,e_name,e_job from employee where e_job like '实习%';

select e_no,e_name,dept_no from employee where dept_no is null;

select e_no,e_name,dept_no from employee where dept_no = null; -- error ：null 跟任何值都不相等

select e_no,e_name,e_gender,dept_no from employee where e_gender = 'f' and dept_no = 10;

select e_no,e_name,e_gender,dept_no from employee where e_gender = 'f' and dept_no in (10,20);

select e_no,e_name,dept_no from employee where dept_no = 10 or dept_no = 20;

select e_no,e_name,e_salary from employee order by e_salary desc;  -- Desc 降序 asc 升序（默认） null 是最大值

select e_no,e_name,e_salary,e_hireDate from employee order by e_salary asc ,e_hireDate desc;

select e_no,e_name,e_salary,e_hireDate from employee order by e_salary asc ,e_hireDate desc nulls first; -- 这样 null 显示在前面 默认是：nulls last

select * from employee limit 5;

select * from employee limit 5 offset 2; -- 从第二条开始 取五条

select e_no,e_name,e_job,d_no,d_name,d_location from employee,dept where dept_no = d_no; -- 空值不会显示

select e_no,e_name,e_job,d_no,d_name,d_location from employee inner join dept on dept_no = d_no;

select e_no,e_name,e_job,d_no,d_name,d_location from employee left join dept on dept_no = d_no; -- 返回所有，空值无对应照样显示空

select e_no,e_name,e_job,d_no,d_name,d_location from employee left outer join dept on dept_no = d_no; -- 左连接与左外连接相等

select e_no,e_name,e_job,d_no,d_name,d_location from employee left outer join dept on dept_no = d_no where dept_no = 10; 

-- join type : inner/left/right

-- 子查询 ： exists 、 in 、标量

select * from employee where exists 
    (select d_no from dept where d_name = '运维部门' and d_no = dept_no);

select * from employee where not exists 
    (select d_no from dept where d_name = '运维部门' and d_no = dept_no);

select * from employee where dept_no in 
    (select d_no from dept where d_name = '运维部门');

-- || 代表字符串拼接

select e_no, e_name, (select d_name || ' ' || d_location 
    from dept where dept_no = d_no) as address from employee;

-- 结果集合并

select e_no,e_name,dept_no,e_salary from employee where dept_no in (10,20)
union all 
select e_no,e_name,dept_no,e_salary from employee where e_salary > 5000;

-- 去掉重复的行
select e_no,e_name,dept_no,e_salary from employee where dept_no in (10,20)
union
select e_no,e_name,dept_no,e_salary from employee where e_salary > 5000;

-- 空值占位
select e_no,e_name,dept_no,e_salary,e_hireDate from employee where dept_no in (10,20)
union
select e_no,e_name,dept_no,e_salary,null from employee where e_salary > 5000;

函数

select e_no,e_name,e_hireDate, extract(year from e_hireDate),
extract(month from e_hireDate), extract(day from e_hireDate) from employee;

自定义函数

让我们来看看基本语法吧

create function // 声明创建函数
    add(integer,integer)  // 定义函数名称，参数类型
returns integer // 定义函数返回值
    as 'select $1 + $2;'  // 定义函数体
language sql    // 用以实现的函数语言的名称
returns null on null input;  // 定义参数为 Null 的时候的处理情况

create function add(integer,integer) returns integer
  as 'select $1 + $2;'
language sql
returns null on null input;

select add(1,2);

-- or replace 假如数据库中存在这个函数就替换掉

create or replace function concat_test(integer,varchar,date) returns varchar
  as 'select $1||$2||$3;'
  langugage sql
returns null on null input;

select e_no,e_name,e_hireDate,concat(e_no,e_name,e_hireDate),
concat_test(e_no,e_name,e_hireDate) from employee;

drop function concat_test(integer,varchar,date);

Index 索引

提高数据的查询速度，加速表与表之间的连接

-- 默认是 B-tree 索引
create index emp_name_index on employee(e_name);
drop index emp_name_index;

视图

缩短语句的长度利器

create view v_emp_dev as select e_no , e_name ,e_salary , e_hireDate
from employee where dept_no = 10 order by e_salary desc;

select * from v_emp_dev;

drop view v_emp_dev;

其他作者的文章

• 8.x手册
• bruce_wu 写了PostgreSQL一个系列的读书笔记
• Gtlions_Lai CentOS下的编译安装

Rust是Mozilla开发的注重安全、性能和并发性的编程语言。
欢迎来 rust-china 社区 http://rust-lang-cn.org/ 逛逛。