PostgreSQL基础学习笔记

PostgreSQL与MySQL

1.PostgreSQL完全免费,采用BSD协议(BSD开源协议是一个给予使用者很大自由的协议。基本上使用者可以”为所欲为”,可以自由的使用,修改源代码,也可以将修改后的代码作为开源或者专有软件再发布);MySQL最开始是瑞典的一个公司开发,后来被sun公司收购,再后来sun被oracle收购

2.PostgreSQL是进程模式(对CPU利用率较高),MySQL是线程模式

3.MySQL 在数据库级管理身份验证。 基本只支持密码认证;PostgreSQL 支持丰富的认证方法:信任认证、口令认证、Kerberos 认证、基于 Ident 的认证、LDAP 认证、PAM 认证

4…….

安装使用

Ubuntu18.04安装:

sudo apt install postgresql

安装后,开放的默认端口为5432

Postgres被设置为使用ident身份验证,这意味着它将Postgres角色与匹配的Unix / Linux系统帐户相关联。如果Postgres和Unix / Linux用户名具有相同名称,则可以作为该角色登录。

安装之后会自动添加一个名为postgres的操作系统用户,密码是随机的;并且会自动生成一个名字为postgres的数据库,用户名也为postgres,密码也是随机的;因此如果修改密码,那么需要修改两次

切换到postgres账户:

sudo -i -u postgres

进入后,可以输入help查看帮助;之后输入:

psql

便可访问postgres提示符,与dbms进行交互;输入help查看帮助,\q退出;

为了方便,可直接访问psql:

sudo -u postgres psql

数据库

创建数据库

命令:

create database xxx;

示例:

postgres=# create database test;
CREATE DATABASE

显示所有数据库

命令:

\l

示例:

1
2
3
4
5
6
7
8
9
10
11
test=# \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
test | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
(4 rows)

进入数据库

命令:

\c xxx

示例:

test=# \c test;
You are now connected to database "test" as user "postgres".

删除数据库

命令(如果不存在则报错):

drop database xxx;

或(如果不存在则发出提示)

drop database xxx if exists xxx;

示例:

drop database t;
ERROR:  database "t" does not exist
test=# drop database if exists t;
NOTICE:  database "t" does not exist, skipping
DROP DATABASE

数据表

数据类型

支持的数据类型有 数值类型、货币类型、字符类型、日期/时间类型、布尔类型、枚举类型、几何类型、网络地址类型、UUID、XML、JSON、数组类型等类型。。。太多了就不列举了,真滴强大

创建数据表

命令:

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( 一个或多个列 )
);

示例:

test=# create table test1(
test(# id int not null,
test(# name text not null,
test(# pass text not null,
test(# primary key(id)
test(# );
CREATE TABLE

显示所有数据表

命令:

\d

示例:

test=# \d
         List of relations
 Schema | Name  | Type  |  Owner   
--------+-------+-------+----------
 public | test1 | table | postgres
(1 row)

查看数据表结构

命令:

\d xxx

示例:

test=# \d test1
               Table "public.test1"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           | not null | 
 name   | text    |           | not null | 
 pass   | text    |           | not null | 
Indexes:
    "test1_pkey" PRIMARY KEY, btree (id)

删除数据表

drop table xxx;

增删改查

大多和MySQL类似,就不写了

添加数据

字段列必须和数据值数量相同,且顺序也要对应:

test=# insert into test1(
test(# id,name,pass)values(
test(# 1,'gtfly','123456');
INSERT 0 1
test=# select * from test1;
 id | name  |  pass  
----+-------+--------
  1 | gtfly | 123456
(1 row)

如果向表中所有字段插入值,那么可以省略字段名:

test=# insert into test1 values(
test(# 2,'admin','666666'
test(# );
INSERT 0 1
test=# select * from test1;
 id | name  |  pass  
----+-------+--------
  1 | gtfly | 123456
  2 | admin | 666666
(2 rows)

运算符

算术运算符

  • %:取余
  • ^:指数
  • |/:平方根
  • ||/:立方根
  • !:阶乘
  • !!:阶乘(前缀操作符,例如!!5)

比较运算符

  • !=:不等于
  • <>:不等于

逻辑运算符

and

not

or

位运算符

  • &:按位与运算符

  • |:按位或运算符

  • #:异或运算符

  • ~:取反运算符

  • <<:二进制左移运算符

  • >>:二进制又移运算符

LIKE

用于模糊匹配,通配符有:

百分号 %,用于贪婪匹配
下划线 _,用于占位

如果没有使用通配符,那么LIKE=作用是一样的;一些用法:

like 'abc%'   # 找出以abc开头的数据
like '%abc%'  # 找出含有abc的数据
like '_abc%'  # 找出第2、3、4位置上对于为abc的数据
...

整型不能用LIKE匹配

联合查询

UNION

UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每个 SELECT 语句中的列的顺序必须相同。

例如,test1表结构和内容:

test=# \d test1;
               Table "public.test1"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           | not null | 
 name   | text    |           | not null | 
 pass   | text    |           | not null | 
Indexes:
    "test1_pkey" PRIMARY KEY, btree (id)

test=# select * from test1;
 id | name  |  pass  
----+-------+--------
  1 | gtfly | 123456
  2 | admin | 666666
(2 rows)

联合查询:

test=# select * from test1 union select 1,2,3;
ERROR:  UNION types text and integer cannot be matched
LINE 1: select * from test1 union select 1,2,3;
                                           ^
test=# select * from test1 union select 1,'2','3';
 id | name  |  pass  
----+-------+--------
  1 | gtfly | 123456
  2 | admin | 666666
  1 | 2     | 3
(3 rows)

UNION ALL

UNION ALL子句可以允许出现重复的行:

test=# select * from test1 union select 1,'gtfly','123456';
 id | name  |  pass  
----+-------+--------
  1 | gtfly | 123456
  2 | admin | 666666
(2 rows)

test=# select * from test1 union all select 1,'gtfly','123456';
 id | name  |  pass  
----+-------+--------
  1 | gtfly | 123456
  2 | admin | 666666
  1 | gtfly | 123456
(3 rows)