PostgreSQL基础介绍

养振濂

2023-12-01

1.PostgreSQL是什么

PostgreSQL是一个功能强大的开源对象关系数据库管理系统(ORDBMS)。用于安全地存储数据; 支持最佳做法，并允许在处理请求时检索它们。
PostgreSQL(也称为Post-gress-Q-L)由PostgreSQL全球开发集团(全球志愿者团队)开发。它不受任何公司或其他私人实体控制。它是开源的，其源代码是免费提供的。
PostgreSQL是跨平台的，可以在许多操作系统上运行，如Linux，FreeBSD，OS X，Solaris和Microsoft Windows等。

2.PostgreSQL特点

PostgreSQL可在所有主要操作系统(即Linux，UNIX(AIX，BSD，HP-UX，SGI IRIX，Mac OS X，Solaris，Tru64)和Windows等)上运行。
PostgreSQL支持文本，图像，声音和视频，并包括用于C/C++，Java，Perl，Python，Ruby，Tcl和开放数据库连接(ODBC)的编程接口。
PostgreSQL支持SQL的许多功能，例如复杂SQL查询，SQL子选择，外键，触发器，视图，事务，多进程并发控制(MVCC)，流式复制(9.0)，热备(9.0))。
在PostgreSQL中，表可以设置为从“父”表继承其特征。
可以安装多个扩展以向PostgreSQL添加附加功能。

3.PostgreSQL工具

有一些开放源码以及付费工具可用作PostgreSQL的前端工具。这里列出几个被广泛使用的工具：

psql：

它是一个命令行工具，也是管理PostgreSQL的主要工具。 pgAdmin是PostgreSQL的免费开源图形用户界面管理工具。
phpPgAdmin:

它是用PHP编写的PostgreSQL的基于Web的管理工具。它基于phpMyAdmin工具管理MySQL功能来开发。它可以用作PostgreSQL的前端工具。
pgFouine：

它是一个日志分析器，可以从PostgreSQL日志文件创建报告。专有工具有Lightning Admin for PostgreSQL, Borland Kylix, DBOne, DBTools Manager PgManager, Rekall, Data Architect, SyBase Power Designer, Microsoft Access, eRWin, DeZign for Databases, PGExplorer, Case Studio 2, pgEdit, RazorSQL, MicroOLAP Database Designer, Aqua Data Studio, Tuples, EMS Database Management Tools for PostgreSQL, Navicat, SQL Maestro Group products for PostgreSQL, Datanamic DataDiff for PostgreSQL, Datanamic SchemaDiff for PostgreSQL, DB MultiRun PostgreSQL Edition, SQLPro, SQL Image Viewer, SQL Data Sets 等等。

4.命令行登录数据库

直接登录psql [-h 127.0.0.1] -U username -d dbname，输入密码后进入psql环境
切换数据库\c dbname [username] [serverlP] [port]
查看psql命令\?

5.常用命令

列出所有的数据库
- \l或\list
切换数据库
- \c dbname
列出当前数据库下的数据表
- \d
列出指定表的所有字段
- \d tablename
查看指定表的基本情况
- \d+ tablename
退出登录
- \q
在Linux shell命令行中创建数据库
- createdb [-h 127.0.0.1] -U postgres -e examdb_my
在Linux shell命令行中删除数据库
- dropdb my_exam_db -U postgres -h 127.0.0.1

6.PostgreSQL数据库操作

创建数据库CREATE DATABASE database_name;
查看数据库列表\l
删除数据库drop database database_name;

7.PostgreSQL创建表

创建表实例

CREATE TABLE public.student
(
  id integer NOT NULL,
  name character(100),
  subjects character(1),
  CONSTRAINT student_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.student
  OWNER TO postgres;
COMMENT ON TABLE public.student
  IS '这是一个学生信息表';

删除表
```
drop table student;
```

8.PostgreSQL模式（架构）

模式(也叫架构)是指定的表集合。它还可以包含视图，索引，序列，数据类型，运算符和函数。
在PostgreSQL中，CREATE SCHEMA语句用于创建模式。模式不能嵌套
- CREATE SCHEMA myschema;
删除模式DROP SCHEMA myschema;
使用架构的优点
- 模式有助于多用户使用一个数据库，而不会互相干扰。
- 它将数据库对象组织成逻辑组，使其更易于管理。
- 可以将第三方模式放入单独的模式中，以避免与其他对象的名称相冲突。

9.PostgreSQL数据操作

插入数据

格式

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)  
VALUES (value1, value2, value3,...valueN);

实例

INSERT INTO student(id, name, subjects)
VALUES
(1, 'Jack', 'S'),
(2, 'Sam', 'S'),
(3, 'Susan', 'S'),
(4, 'Lucy', 'S'),
(5, 'Tom', 'S');

查询数据

格式

SELECT "column1", "column2".."column" FROM "table_name";

实例

SELECT * FROM "table_name"; #查询所有表中内容
SELECT id,name FROM student;#查看id和name的内容

更新数据

格式

UPDATE table_name  
SET column1 = value1, column2 = value2...., columnN = valueN  
WHERE [condition];

实例

UPDATE student
SET name='汪喆'
WHERE id=1;

删除数据

格式

DELETE FROM table_name  
WHERE [condition];

实例
```
DELETE FROM student  
WHERE id=3;
```

10.PostgreSQL条件查询

ORDER BY子句

格式

SELECT column-list  
FROM table_name  
[WHERE condition]  
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

实例

SELECT *   
FROM student  
ORDER BY id DESC;

GROUP BY子句

格式

SELECT column-list  
FROM table_name  
WHERE [conditions ]  
GROUP BY column1, column2....columnN  
ORDER BY column1, column2....columnN

实例

SELECT count(*)
FROM student
GROUP BY subjects;

Having子句

格式

SELECT column1, column2  
FROM table1, table2  
WHERE [ conditions ]  
GROUP BY column1, column2  
HAVING [ conditions ]  
ORDER BY column1, column2

实例

SELECT count(*) FROM student
GROUP BY name
HAVING count(*)>2;

AND条件

格式

SELECT column1, column2, ..... columnN    
FROM table_name    
WHERE [search_condition]    
AND [search_condition];

实例

SELECT * FROM student
WHERE id=1 
AND name='汪喆';

OR条件

格式

SELECT column1, column2, ..... columnN    
FROM table_name    
WHERE [search_condition]    
OR [search_condition];

实例

SELECT * FROM student
WHERE id=1 
OR id=2;

AND&OR条件

格式

SELECT column1, column2, ..... columnN    
FROM table_name    
WHERE [search_condition]  AND [search_condition]     
OR [search_condition];

实例

SELECT * FROM student
WHERE id=1 AND name='汪喆'
OR id=2;

NOT条件

格式

SELECT column1, column2, ..... columnN    
FROM table_name    
WHERE [search_condition] NOT [condition];

实例

SELECT * FROM student
WHERE id NOT IN (1,2,3);

LIKE条件

格式

SELECT column1, column2, ..... columnN    
FROM table_name    
WHERE [search_condition] LIKE [condition];

实例

SELECT * FROM student
WHERE name LIKE '%m%';

IN条件

格式

SELECT column1, column2, ..... columnN    
FROM table_name    
WHERE [search_condition] IN [condition];

实例

SELECT *
FROM student
WHERE name in ('汪喆','Jack','Sam');

NOT IN条件

格式

SELECT column1, column2, ..... columnN    
FROM table_name    
WHERE [search_condition] NOT IN [condition];

实例

SELECT *
FROM student
WHERE name NOT IN ('汪喆','Jack','Sam');

BETWEEN条件

格式

SELECT column1, column2, ..... columnN    
FROM table_name    
WHERE [search_condition] BETWEEN [condition] AND [condition];

实例

SELECT *
FROM student
WHERE id BETWEEN 1 AND 8;