$ psql -d fgedudb -U fgedu
Password for fgedu fgedu:
psql (18.3)
Type “help” for help.

fgedudb=>

— 普通标识符示例
CREATE TABLE fgedu_employees (
employee_id SERIAL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
hire_date DATE NOT NULL,
salary NUMERIC(10, 2) NOT NULL
);

CREATE TABLE fgedu_– 分隔标识符示例（包含特殊字符或关键字）
CREATE TABLE fgedu_”fgedu-employees” (
“employee-id” SERIAL PRIMARY KEY,
“first-name” VARCHAR(50) NOT NULL,
“last-name” VARCHAR(50) NOT NULL,
“email” VARCHAR(100) UNIQUE NOT NULL,
“date” DATE NOT NULL,
“select” VARCHAR(10) NOT NULL
);

CREATE TABLE fgedu_– 查看创建的表
\dt
List of relations
Schema | Name | Type | Owner
——–+—————–+——-+———-
public | fgedu-employees | table | fgedu
public | fgedu_employees | table | fgedu
(2 rows)

— 尝试使用关键字作为普通标识符（会失败）
CREATE TABLE fgedu_order (
order_id SERIAL PRIMARY KEY,
select VARCHAR(10) NOT NULL,
from VARCHAR(50) NOT NULL,
where VARCHAR(100) NOT NULL
);
ERROR: syntax error at or near “select”
LINE 3: select VARCHAR(10) NOT NULL,
^

— 使用分隔标识符（双引号）处理关键字
CREATE TABLE fgedu_order (
order_id SERIAL PRIMARY KEY,
“select” VARCHAR(10) NOT NULL,
“from” VARCHAR(50) NOT NULL,
“where” VARCHAR(100) NOT NULL
);

CREATE TABLE fgedu_– 插入数据到包含关键字的表
INSERT INTO fgedu_order (“select”, “from”, “where”)
VALUES (‘value1’, ‘source1’, ‘condition1’);

INSERT 0 1

— 查询包含关键字的表
SELECT order_id, “select”, “from”, “where” FROM fgedu_order;
order_id | select | from | where
———-+——–+——–+———–+
1 | value1 | source1 | condition1
(1 row)

— 算术表达式示例
SELECT 10 + 5 AS addition,
10 – 5 AS subtraction,
10 * 5 AS multiplication,
10 / 5 AS division,
10 % 3 AS modulus;
addition | subtraction | multiplication | division | modulus
———-+————-+—————-+———-+———+
15 | 5 | 50 | 2 | 1
(1 row)

— 比较表达式示例
SELECT 10 = 5 AS equal,
10 != 5 AS not_equal,
10 > 5 AS greater_than,
10 < 5 AS less_than, 10 >= 5 AS greater_or_equal,
10 <= 5 AS less_or_equal; equal | not_equal | greater_than | less_than | greater_or_equal | less_or_equal -------+-----------+--------------+-----------+------------------+----------------+ f | t | t | f | t | f (1 row) -- 逻辑表达式示例 SELECT (10 > 5) AND (5 < 8) AS logical_and, (10 > 5) OR (5 > 8) AS logical_or,
NOT (10 > 5) AS logical_not;
logical_and | logical_or | logical_not
————-+————+————-+
t | t | f
(1 row)

— 字符串表达式示例
SELECT ‘Hello’ || ‘ ‘ || ‘World’ AS concatenation,
LENGTH(‘Hello World’) AS length,
UPPER(‘Hello World’) AS uppercase,
LOWER(‘Hello World’) AS lowercase,
SUBSTRING(‘Hello World’ FROM 1 FOR 5) AS substring;
concatenation | length | uppercase | lowercase | substring
—————+——–+————-+————-+———–+
Hello World | 11 | HELLO WORLD | hello world | Hello
(1 row)

— 日期表达式示例
SELECT CURRENT_DATE AS current_date,
CURRENT_TIME AS current_time,
CURRENT_TIMESTAMP AS current_timestamp,
CURRENT_DATE + INTERVAL ‘1 day’ AS tomorrow,
CURRENT_DATE – INTERVAL ‘1 week’ AS last_week;
current_date | current_time | current_timestamp | tomorrow | last_week
————–+——————–+——————————-+————+————-+
2026-04-02 | 14:30:45.678901+08 | 2026-04-02 14:30:45.678901+08 | 2026-04-03 | 2026-03-26
(1 row)

— 有效标识符
CREATE TABLE fgedu_valid_table_names (
id SERIAL PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email_address VARCHAR(100),
fgedu_id INTEGER,
order_date DATE,
total_amount NUMERIC(10, 2)
);

CREATE TABLE fgedu_– 有效分隔标识符
CREATE TABLE fgedu_”valid-table-names” (
“id” SERIAL PRIMARY KEY,
“first-name” VARCHAR(50),
“last-name” VARCHAR(50),
“email address” VARCHAR(100),
“fgedu id” INTEGER,
“order date” DATE,
“total amount” NUMERIC(10, 2)
);

CREATE TABLE fgedu_– 无效标识符（以数字开头）
CREATE TABLE fgedu_123table (
id SERIAL PRIMARY KEY
);
ERROR: syntax error at or near “123table”
LINE 1: CREATE TABLE fgedu_123table (
^

— 无效标识符（包含特殊字符）
CREATE TABLE fgedu_invalid@table (
id SERIAL PRIMARY KEY
);
ERROR: syntax error at or near “@”
LINE 1: CREATE TABLE fgedu_invalid@table (
^

— 无效标识符（SQL关键字）
CREATE TABLE fgedu_select (
id SERIAL PRIMARY KEY
);
ERROR: syntax error at or near “select”
LINE 1: CREATE TABLE fgedu_select (
^

— 创建包含关键字的表
CREATE TABLE fgedu_report (
report_id SERIAL PRIMARY KEY,
“select” VARCHAR(50) NOT NULL,
“from” VARCHAR(50) NOT NULL,
“where” VARCHAR(100) NOT NULL,
“group” VARCHAR(50) NOT NULL,
“order” VARCHAR(50) NOT NULL,
“limit” INTEGER NOT NULL,
“offset” INTEGER NOT NULL
);

CREATE TABLE fgedu_– 插入数据
INSERT INTO fgedu_report (“select”, “from”, “where”, “group”, “order”, “limit”, “offset”)
VALUES (‘fgedu_id, fgeduname’, ‘fgedus’, ‘status = ”active”’, ‘department’, ‘fgedu_id DESC’, 10, 0);

INSERT 0 1

— 查询数据
SELECT report_id, “select”, “from”, “where”, “group”, “order”, “limit”, “offset”
FROM fgedu_report;
report_id | select | from | where | group | order | limit | offset
———–+——————–+——-+————————-+———–+—————-+——-+——–+
1 | fgedu_id, fgeduname | fgedus | status = ‘active’ | department | fgedu_id DESC | 10 | 0
(1 row)

— 更新数据
UPDATE fgedu_report
SET “limit” = 20,
“offset” = 10
WHERE report_id = 1;

UPDATE 1

— 删除数据
DELETE FROM fgedu_report WHERE report_id = 1;

DELETE 1

— 使用表达式计算工资
CREATE TABLE fgedu_salaries (
employee_id SERIAL PRIMARY KEY,
base_salary NUMERIC(10, 2) NOT NULL,
bonus_rate NUMERIC(5, 2) NOT NULL DEFAULT 0.1
);

CREATE TABLE fgedu_INSERT INTO fgedu_salaries (base_salary, bonus_rate)
VALUES (5000.00, 0.1),
(6000.00, 0.15),
(7000.00, 0.2),
(8000.00, 0.25),
(9000.00, 0.3);

INSERT 0 5

— 使用表达式计算总工资
SELECT
employee_id,
base_salary,
bonus_rate,
base_salary * bonus_rate AS bonus,
base_salary + (base_salary * bonus_rate) AS total_salary
FROM fgedu_salaries;
employee_id | base_salary | bonus_rate | bonus | total_salary
————-+————-+————+——-+————–+
1 | 5000.00 | 0.10 | 500.00 | 5500.00
2 | 6000.00 | 0.15 | 900.00 | 6900.00
3 | 7000.00 | 0.20 | 1400.00 | 8400.00
4 | 8000.00 | 0.25 | 2000.00 | 10000.00
5 | 9000.00 | 0.30 | 2700.00 | 11700.00
(5 rows)

— 使用表达式进行条件判断
SELECT
employee_id,
base_salary,
CASE
WHEN base_salary < 6000 THEN '低工资' WHEN base_salary < 8000 THEN '中工资' ELSE '高工资' END AS salary_level FROM fgedu_salaries; employee_id | base_salary | salary_level -------------+-------------+--------------+ 1 | 5000.00 | 低工资 2 | 6000.00 | 中工资 3 | 7000.00 | 中工资 4 | 8000.00 | 高工资 5 | 9000.00 | 高工资 (5 rows) -- 使用表达式进行字符串处理 CREATE TABLE fgedu_fgedus ( fgedu_id SERIAL PRIMARY KEY, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL ); CREATE TABLE fgedu_INSERT INTO fgedu_fgedus (first_name, last_name) VALUES ('张', '三'), ('李', '四'), ('王', '五'), ('赵', '六'), ('钱', '七'); INSERT 0 5 -- 使用表达式拼接姓名 SELECT fgedu_id, first_name, last_name, first_name || ' ' || last_name AS full_name, UPPER(first_name) || ' ' || UPPER(last_name) AS upper_full_name, LOWER(first_name) || ' ' || LOWER(last_name) AS lower_full_name FROM fgedu_fgedus; fgedu_id | first_name | last_name | full_name | upper_full_name | lower_full_name ---------+------------+-----------+-----------+------------------+------------------+ 1 | 张 | 三 | 张 三 | 张 三 | 张 三 2 | 李 | 四 | 李 四 | 李 四 | 李 四 3 | 王 | 五 | 王 五 | 王 五 | 王 五 4 | 赵 | 六 | 赵 六 | 赵 六 | 赵 六 5 | 钱 | 七 | 钱 七 | 钱 七 | 钱 七 (5 rows)