PostgreSQL教程FG007-PG基础操作：数据库/表的创建与简单查询

本文详细介绍PostgreSQL的基础操作，包括数据库的创建与管理、表的创建与设计、简单查询的执行等内容，帮助读者掌握PostgreSQL的核心基础操作。风哥教程参考PostgreSQL官方文档Getting Started、The SQL Language等内容。

本文档风哥主要介绍PG基础操作：数据库/表的创建与简单查询相关内容。风哥教程参考PostgreSQL官方文档Tutorial, SQL Language内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。

本文档介绍PostgreSQL的基础操作，包括创建数据库、创建表、插入数据、简单查询等入门级操作，适合初学者快速上手。风哥教程参考PostgreSQL官方文档Tutorial, SQL Language内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。

Part01-基础概念与理论知识

1.1 数据库基本概念

PostgreSQL中的数据库是一个包含表、视图、索引等对象的集合，用于存储和管理数据。每个数据库都是独立的，拥有自己的权限设置和配置。学习交流加群风哥微信: itpux-com

1.2 表的基本概念

表是PostgreSQL中存储数据的基本单位，由行和列组成。每行代表一条记录，每列代表一个字段，包含特定类型的数据。

风哥教程针对风哥教程针对风哥教程针对生产环境建议：在设计表时，应根据业务需求合理选择数据类型和约束，确保数据的完整性和一致性。学习交流加群风哥QQ113257174

Part02-数据库创建与管理

2.1 创建数据库

使用CREATE DATABASE命令创建PostgreSQL数据库。

# 切换到postgres用户
# su – pgsql # 登录PostgreSQL
$ psql
psql (18.3)
Type “help” for help.

# 创建fgedudb数据库
postgres=# CREATE DATABASE fgedudb;
CREATE DATABASE

# 连接到fgedudb数据库
postgres=# \c fgedudb
You are now connected to fgedudb “fgedudb” as fgedu “postgres”.
fgedudb=#

2.2 数据库管理

数据库管理包括查看数据库信息、修改数据库配置和删除数据库等操作。

# 查看当前数据库信息
fgedudb=# SELECT current_fgedudb();
current_fgedudb
——————
fgedudb
(1 row)

# 查看数据库大小
fgedudb=# SELECT pg_size_pretty(pg_fgedudb_size(‘fgedudb’));
pg_size_pretty
—————-
7833 kB
(1 row)

# 修改数据库所有者
fgedudb=# CREATE USER fgedu WITH PASSWORD ‘fgedu123’;
CREATE ROLE
fgedudb=# ALTER DATABASE fgedudb OWNER TO fgedu;
ALTER DATABASE

# 删除数据库（谨慎操作）
# DROP DATABASE IF EXISTS testdb;
# DROP DATABASE

风哥提示：删除数据库是不可逆操作，在生产环境中务必谨慎执行，建议先备份数据。

Part03-表的创建与设计

3.1 创建表

使用CREATE TABLE命令创建表，定义表的结构和字段。

# 创建fgedu_students表
fgedudb=# CREATE TABLE fgedu_students (
id SERIAL PRIMARY KEY,
student_id VARCHAR(20) UNIQUE NOT NULL,
name VARCHAR(50) NOT NULL,
age INTEGER CHECK (age > 0),
gender VARCHAR(10),
major VARCHAR(50),
enrollment_date DATE,
created_at TIMESTAMP DEFAULT NOW()
);
CREATE TABLE fgedu_# 查看表列表
fgedudb=# \dt
List of relations
Schema | Name | Type | Owner
——–+—————-+——-+———-
public | fgedu_students | table | pgsql (1 row)

3.2 表约束

约束用于确保数据的完整性和一致性，包括主键约束、唯一约束、非空约束、检查约束和外键约束等。

# 创建fgedu_courses表
fgedudb=# CREATE TABLE fgedu_courses (
course_id VARCHAR(20) PRIMARY KEY,
course_name VARCHAR(100) NOT NULL,
credits INTEGER CHECK (credits > 0),
department VARCHAR(50)
);
CREATE TABLE fgedu_# 创建fgedu_enrollments表，包含外键约束
fgedudb=# CREATE TABLE fgedu_enrollments (
enrollment_id SERIAL PRIMARY KEY,
student_id VARCHAR(20) NOT NULL REFERENCES fgedu_students(student_id),
course_id VARCHAR(20) NOT NULL REFERENCES fgedu_courses(course_id),
enrollment_date DATE DEFAULT NOW(),
grade NUMERIC(3,1) CHECK (grade >= 0 AND grade <= 100) ); CREATE TABLE fgedu_# 查看表结构和约束 fgedudb=# \d fgedu_enrollments Table "public.fgedu_enrollments" Column | Type | Collation | Nullable | Default --------------------+-----------------------+-----------+----------+------------------------------------------------ enrollment_id | integer | | not null | nextval('fgedu_enrollments_enrollment_id_seq'::regclass) student_id | character varying(20) | | not null | course_id | character varying(20) | | not null | enrollment_date | date | | | now() grade | numeric(3,1) | | | Indexes: "fgedu_enrollments_pkey" PRIMARY KEY, btree (enrollment_id) Foreign-key constraints: "fgedu_enrollments_course_id_fkey" FOREIGN KEY (course_id) REFERENCES fgedu_courses(course_id) "fgedu_enrollments_student_id_fkey" FOREIGN KEY (student_id) REFERENCES fgedu_students(student_id) Check constraints: "fgedu_enrollments_grade_check" CHECK ((grade >= (0)::numeric) AND (grade <= (100)::numeric))

3.3 表结构管理

使用ALTER TABLE命令修改表结构，包括添加列、修改列和删除列等操作。

# 添加列
fgedudb=# ALTER TABLE fgedu_students ADD COLUMN email VARCHAR(100) UNIQUE;
ALTER TABLE

# 修改列
fgedudb=# ALTER TABLE fgedu_students ALTER COLUMN major TYPE VARCHAR(100);
ALTER TABLE

# 添加约束
fgedudb=# ALTER TABLE fgedu_students ADD CONSTRAINT fgedu_students_email_check CHECK (email LIKE ‘%@%’);
ALTER TABLE

# 删除列
fgedudb=# ALTER TABLE fgedu_students DROP COLUMN email;
ALTER TABLE

Part04-简单查询操作

4.1 基础SELECT查询

使用SELECT语句查询表中的数据。

# 插入测试数据
fgedudb=# INSERT INTO fgedu_students (student_id, name, age, gender, major, enrollment_date) VALUES
(‘S001’, ‘风哥1号’, 20, ‘男’, ‘计算机科学与技术’, ‘2024-09-01’),
(‘S002’, ‘风哥2号’, 21, ‘女’, ‘软件工程’, ‘2024-09-01’),
(‘S003’, ‘王五’, 22, ‘男’, ‘数据科学’, ‘2023-09-01’),
(‘S004’, ‘赵六’, 20, ‘女’, ‘人工智能’, ‘2024-09-01’),
(‘S005’, ‘孙七’, 21, ‘男’, ‘网络工程’, ‘2024-09-01’);
INSERT 0 5

# 插入课程数据
fgedudb=# INSERT INTO fgedu_courses (course_id, course_name, credits, department) VALUES
(‘C001’, ‘数据库原理与应用’, 3, ‘计算机学院’),
(‘C002’, ‘Python程序设计’, 3, ‘计算机学院’),
(‘C003’, ‘数据结构与算法’, 4, ‘计算机学院’),
(‘C004’, ‘人工智能导论’, 3, ‘计算机学院’);
INSERT 0 4

# 插入选课数据
fgedudb=# INSERT INTO fgedu_enrollments (student_id, course_id, enrollment_date, grade) VALUES
(‘S001’, ‘C001’, ‘2024-09-10’, 85.5),
(‘S001’, ‘C002’, ‘2024-09-10’, 90.0),
(‘S002’, ‘C001’, ‘2024-09-10’, 88.0),
(‘S002’, ‘C003’, ‘2024-09-10’, 92.5),
(‘S003’, ‘C002’, ‘2024-09-10’, 78.5),
(‘S003’, ‘C004’, ‘2024-09-10’, 82.0),
(‘S004’, ‘C001’, ‘2024-09-10’, 95.0),
(‘S004’, ‘C003’, ‘2024-09-10’, 89.5),
(‘S004’, ‘C004’, ‘2024-09-10’, 91.0);
INSERT 0 9

# 查询所有学生数据
fgedudb=# SELECT * FROM fgedu_students;
id | student_id | name | age | gender | major | enrollment_date | created_at
—-+————+——+—–+——–+———————–+—————-+—————————-
1 | S001 | 风哥1号 | 20 | 男 | 计算机科学与技术 | 2024-09-01 | 2026-04-02 12:00:00.123456
2 | S002 | 风哥2号 | 21 | 女 | 软件工程 | 2024-09-01 | 2026-04-02 12:00:00.123456
3 | S003 | 王五 | 22 | 男 | 数据科学 | 2023-09-01 | 2026-04-02 12:00:00.123456
4 | S004 | 赵六 | 20 | 女 | 人工智能 | 2024-09-01 | 2026-04-02 12:00:00.123456
5 | S005 | 孙七 | 21 | 男 | 网络工程 | 2024-09-01 | 2026-04-02 12:00:00.123456
(5 rows)

4.2 带条件的查询

使用WHERE子句过滤查询结果。

# 查询年龄大于20的学生
fgedudb=# SELECT student_id, name, age FROM fgedu_students WHERE age > 20;
student_id | name | age
————+——+—–
S002 | 风哥2号 | 21
S003 | 王五 | 22
S005 | 孙七 | 21
(3 rows)

# 查询计算机科学与技术专业的学生
fgedudb=# SELECT * FROM fgedu_students WHERE major = ‘计算机科学与技术’;
id | student_id | name | age | gender | major | enrollment_date | created_at
—-+————+——+—–+——–+———————–+—————-+—————————-
1 | S001 | 风哥1号 | 20 | 男 | 计算机科学与技术 | 2024-09-01 | 2026-04-02 12:00:00.123456
(1 row)

# 查询年龄在20到21之间的学生
fgedudb=# SELECT * FROM fgedu_students WHERE age BETWEEN 20 AND 21;
id | student_id | name | age | gender | major | enrollment_date | created_at
—-+————+——+—–+——–+———————–+—————-+—————————-
1 | S001 | 风哥1号 | 20 | 男 | 计算机科学与技术 | 2024-09-01 | 2026-04-02 12:00:00.123456
2 | S002 | 风哥2号 | 21 | 女 | 软件工程 | 2024-09-01 | 2026-04-02 12:00:00.123456
4 | S004 | 赵六 | 20 | 女 | 人工智能 | 2024-09-01 | 2026-04-02 12:00:00.123456
5 | S005 | 孙七 | 21 | 男 | 网络工程 | 2024-09-01 | 2026-04-02 12:00:00.123456
(4 rows)

# 查询专业包含”计算机”的学生
fgedudb=# SELECT * FROM fgedu_students WHERE major LIKE ‘%计算机%’;
id | student_id | name | age | gender | major | enrollment_date | created_at
—-+————+——+—–+——–+———————–+—————-+—————————-
1 | S001 | 风哥1号 | 20 | 男 | 计算机科学与技术 | 2024-09-01 | 2026-04-02 12:00:00.123456
2 | S002 | 风哥2号 | 21 | 女 | 软件工程 | 2024-09-01 | 2026-04-02 12:00:00.123456
5 | S005 | 孙七 | 21 | 男 | 网络工程 | 2024-09-01 | 2026-04-02 12:00:00.123456
(3 rows)

4.3 排序与限制

使用ORDER BY子句排序查询结果，使用LIMIT子句限制返回行数。

from oracle:www.itpux.com

# 按年龄升序排序
fgedudb=# SELECT student_id, name, age FROM fgedu_students ORDER BY age;
student_id | name | age
————+——+—–
S001 | 风哥1号 | 20
S004 | 赵六 | 20
S002 | 风哥2号 | 21
S005 | 孙七 | 21
S003 | 王五 | 22
(5 rows)

# 按年龄降序排序
fgedudb=# SELECT student_id, name, age FROM fgedu_students ORDER BY age DESC;
student_id | name | age
————+——+—–
S003 | 王五 | 22
S002 | 风哥2号 | 21
S005 | 孙七 | 21
S001 | 风哥1号 | 20
S004 | 赵六 | 20
(5 rows)

# 限制返回前3行
fgedudb=# SELECT student_id, name, age FROM fgedu_students ORDER BY age DESC LIMIT 3;
student_id | name | age
————+——+—–
S003 | 王五 | 22
S002 | 风哥2号 | 21
S005 | 孙七 | 21
(3 rows)

# 跳过前2行，返回接下来的2行
fgedudb=# SELECT student_id, name, age FROM fgedu_students ORDER BY age DESC OFFSET 2 LIMIT 2;
student_id | name | age
————+——+—–
S005 | 孙七 | 21
S001 | 风哥1号 | 20
(2 rows)

Part05-实战操作与示例

5.1 完整操作实战

完整的数据库和表操作实战。

# 1. 创建数据库
# su – pgsql $ psql -c “CREATE DATABASE fgedudb;”

# 2. 创建用户并授权
$ psql -c “CREATE USER fgedu WITH PASSWORD ‘fgedu123’;”
$ psql -c “ALTER DATABASE fgedudb OWNER TO fgedu;”

# 3. 使用fgedu用户连接数据库
$ psql -U fgedu -d fgedudb -h localfgedu.net.cn

# 4. 创建表
fgedudb=> CREATE TABLE fgedu_employees (
id SERIAL PRIMARY KEY,
employee_id VARCHAR(20) UNIQUE NOT NULL,
name VARCHAR(50) NOT NULL,
department VARCHAR(50),
position VARCHAR(50),
salary DECIMAL(10,2),
hire_date DATE,
email VARCHAR(100) UNIQUE
);
CREATE TABLE fgedu_# 5. 插入数据
fgedudb=> INSERT INTO fgedu_employees (employee_id, name, department, position, salary, hire_date, email) VALUES
(‘E001’, ‘风哥1号’, ‘技术部’, ‘软件工程师’, 8000.00, ‘2023-01-15’, ‘zhangsan@fgedu.net.cn’),
(‘E002’, ‘风哥2号’, ‘技术部’, ‘高级软件工程师’, 12000.00, ‘2022-03-20’, ‘lisi@fgedu.net.cn’),
(‘E003’, ‘王五’, ‘销售部’, ‘销售经理’, 10000.00, ‘2021-07-10’, ‘wangwu@fgedu.net.cn’),
(‘E004’, ‘赵六’, ‘人事部’, ‘人事专员’, 6000.00, ‘2023-05-01’, ‘zhaoliu@fgedu.net.cn’),
(‘E005’, ‘孙七’, ‘技术部’, ‘测试工程师’, 7000.00, ‘2023-09-15’, ‘sunqi@fgedu.net.cn’);
INSERT 0 5

# 6. 查询数据
fgedudb=> SELECT * FROM fgedu_employees;
id | employee_id | name | department | position | salary | hire_date | email
—-+————-+——+————+—————-+———-+————+————————
1 | E001 | 风哥1号 | 技术部 | 软件工程师 | 8000.00 | 2023-01-15 | zhangsan@fgedu.net.cn
2 | E002 | 风哥2号 | 技术部 | 高级软件工程师 | 12000.00 | 2022-03-20 | lisi@fgedu.net.cn
3 | E003 | 王五 | 销售部 | 销售经理 | 10000.00 | 2021-07-10 | wangwu@fgedu.net.cn
4 | E004 | 赵六 | 人事部 | 人事专员 | 6000.00 | 2023-05-01 | zhaoliu@fgedu.net.cn
5 | E005 | 孙七 | 技术部 | 测试工程师 | 7000.00 | 2023-09-15 | sunqi@fgedu.net.cn
(5 rows)

# 7. 条件查询
fgedudb=> SELECT name, department, salary FROM fgedu_employees WHERE department = ‘技术部’ AND salary > 7500;
name | department | salary
——+————+———-
风哥1号 | 技术部 | 8000.00
风哥2号 | 技术部 | 12000.00
(2 rows)

# 9. 聚合查询
fgedudb=> SELECT department, COUNT(*) AS employee_count, AVG(salary) AS avg_salary FROM fgedu_employees GROUP BY department;
department | employee_count | avg_salary
————+—————-+———————-
人事部 | 1 | 6000.00000000000000
技术部 | 3 | 9000.00000000000000
销售部 | 1 | 10000.00000000000000
(3 rows)

5.2 常用查询示例

一些常用的查询示例。

# 查询表中的记录数
fgedudb=# SELECT COUNT(*) FROM fgedu_students;
count
——-
5
(1 row)

# 查询年龄的最大值、最小值和平均值
fgedudb=# SELECT MAX(age) AS max_age, MIN(age) AS min_age, AVG(age) AS avg_age FROM fgedu_students;
max_age | min_age | avg_age
———+———+———————-
22 | 20 | 20.8000000000000000
(1 row)

# 查询选修了”C001″课程的学生姓名和成绩
fgedudb=# SELECT s.name, e.grade
FROM fgedu_students s
JOIN fgedu_enrollments e ON s.student_id = e.student_id
WHERE e.course_id = ‘C001’;
name | grade
——+——-
风哥1号 | 85.5
风哥2号 | 88.0
赵六 | 95.0
(3 rows)

风哥教程针对风哥教程针对风哥教程针对生产环境建议：在生产环境中，应根据实际需求创建合适的索引，提高查询性能。同时，避免在SELECT语句中使用*，而是明确指定需要的列。更多视频教程www.fgedu.net.cn

本文介绍了PostgreSQL的基础操作，包括数据库的创建与管理、表的创建与设计、简单查询的执行等内容。通过学习本文，读者可以掌握PostgreSQL的核心基础操作，并能够进行简单的数据查询和管理。更多学习教程公众号风哥教程itpux_com

from PostgreSQL:www.itpux.com

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html