PostgreSQL的基础知识

什么是数据库？

数据库（Database）是一个用于存储、检索、管理和分析数据的集合。它是按照一定的数据模型组织、存储的集合，具有统一的结构形式、定义的相互关系、制定的约束条件和一定的冗余度，以便于在各种用户、各种程序之间实现数据共享。数据库能够管理大量数据，并允许用户通过数据库管理系统（DBMS）来访问这些数据。

ORDBMS 术语

ORDBMS，即“Object Relational Database Management System”的缩写，直译为“对象关系数据库管理系统”。

一、基本概念

1. 数据库：数据库是一些关联表的集合，用于存储和管理大量数据。
2. 数据表：表是数据的矩阵，在数据库中以二维表格的形式存在，类似于电子表格。
3. 列：一列（数据元素）包含了相同的数据，如所有用户的姓名或地址。
4. 行：一行（=元组，或记录）是一组相关的数据，代表一个实体或对象的完整信息。

二、数据完整性

1. 主键：主键是表中每条记录的唯一标识，用于确保数据的唯一性和完整性。一个表中只能有一个主键。
2. 外键：外键用于关联两个表，建立表之间的关系。外键的值必须是另一个表主键的有效值。
3. 复合键：复合键（组合键）将多个列作为一个索引键，用于在表中唯一标识一行数据。
4. 索引：索引是对数据库表中一列或多列的值进行排序的一种结构，类似于书籍的目录，用于加快数据的检索速度。
5. 参照完整性：参照完整性要求关系中不允许引用不存在的实体，确保表之间的关系正确无误。

三、高级特性

1. 函数：通过函数，可以在数据库服务器端执行指令程序，实现复杂的逻辑处理。
2. 触发器：触发器是由SQL语句查询所触发的事件，用于在数据发生变化时自动执行预定义的操作，如检查数据完整性。
3. 多版本并发控制（MVCC）：一种并发控制机制，用于在多个用户同时访问数据库时保持数据的一致性。MVCC为每个用户提供一个数据库的“快照”，确保用户在事务内所做的修改对其他用户不可见，直到事务成功提交。
4. 规则（RULE）：规则允许一个查询能被重写，通常用来实现对视图（VIEW）的操作，如插入（INSERT）、更新（UPDATE）、删除（DELETE）等。

四、数据类型

 1.数值类型

•         整数类型：包括smallint（2字节）、integer（4字节）、bigint（8字节）等，用于存储整数值。这些类型在取值空间和存储范围上有所不同，应根据实际需求选择最合适的类型。
• 浮点数类型：包括real（单精度浮点数）和double precision（双精度浮点数），用于存储需要高精度计算的数值。
• 任意精度类型：numeric或decimal，用于存储任意精度的数值，适合需要高精度计算的场景，如金融领域。

2. 字符串类型

• 固定长度字符串：char(n)，用于存储固定长度的字符串，如果存储的字符串长度小于指定的n，则会在后面填充空格直到长度为n。
• 可变长度字符串：varchar(n)或character varying(n)，用于存储可变长度的字符串，最大长度由n指定。如果存储的字符串长度小于n，则实际占用空间会根据字符串长度而定。
• 无长度限制字符串：text，用于存储长度不限的字符串。

3. 日期和时间类型

• 日期类型：date，用于存储日期值，不包含时间部分。
• 时间类型：time（无时区）和time with time zone（有时区），用于存储时间值。
• 时间戳类型：timestamp（无时区）和timestamp with time zone（有时区），用于存储日期和时间值，包括可选的时区信息。
• 时间间隔类型：interval，用于存储时间间隔。

4. 布尔类型

• 布尔类型：boolean，用于存储逻辑值，如真（true）或假（false）。

5. 二进制类型

• 二进制类型：bytea，用于存储二进制数据。

6. 枚举类型

• 枚举类型：使用CREATE TYPE语句定义的枚举类型，包含一系列静态值集合，用于存储固定的值集。

7. 几何类型

• 几何类型：包括点（point）、线（line）、矩形（box）、多边形（polygon）等，用于存储二维空间中的几何数据。

8. 数组类型

• 数组类型：支持一维或多维数组，数组的元素可以是任何基本类型或用户定义的类型

PostgreSQL 语法

一、数据库的操作

        1.创建数据库

CREATE DATABASE dbname;  
-- 可以在创建时指定参数，如所有者、编码格式等  
CREATE DATABASE db_lbh WITH OWNER=postgres ENCODING='utf-8';

        2.修改数据库名称

ALTER DATABASE db_lbh RENAME TO db_lbh3;

         3.删除数据库

DROP DATABASE db_lbh3;

二、表操作

        1.创建表

CREATE TABLE student (  
    id INT,  
    name VARCHAR(30),  
    birthday DATE,  
    score NUMERIC(5,2)  
);

        2.修改表名

ALTER TABLE student RENAME TO student1;

        3.修改字段名

ALTER TABLE student1 RENAME id TO myid;

        4.添加字段

ALTER TABLE student1 ADD COLUMN new_column VARCHAR(20);

        5.删除字段

ALTER TABLE student1 DROP COLUMN birthday;

        6.修改字段类型

ALTER TABLE student1 ALTER COLUMN name TYPE VARCHAR(40);

        7.删除表

DROP TABLE student1;  
-- 如果表存在则删除  
DROP TABLE IF EXISTS student1;

 三、数据的查询与修改

        1.插入数据

INSERT INTO student VALUES (1, '张三', '2023-01-01', 90.5);  
-- 指定字段插入  
INSERT INTO student (id, name) VALUES (2, '李四');

        2.更新数据

UPDATE student SET name = '王五' WHERE id = 2;

        3.删除数据

DELETE FROM student WHERE id = 2;  
-- 删除多条数据  
DELETE FROM student WHERE id BETWEEN 1 AND 3;

        4.查询数据

SELECT * FROM student;  
-- 指定字段查询  
SELECT id, name FROM student;  
-- 条件查询  
SELECT * FROM student WHERE name LIKE '张%';

        5.多表查询

-- 隐式内连接  
SELECT student.name, class.name  
FROM student, class  
WHERE student.classid = class.id;  
  
-- 显示内连接  
SELECT student.name, class.name  
FROM student  
INNER JOIN class ON student.classid = class.id;

四、常见

        1.为用户赋予表的权限

GRANT SELECT ON table_name TO username;  
-- 授予所有权限  
GRANT ALL PRIVILEGES ON table_name TO username;

        2.子查询

SELECT * FROM table_a WHERE id IN (SELECT id FROM table_b);