-- 如果存在 client 表，则先删除它 DROP TABLE IF EXISTS client;
-- 创建 client 表 CREATE TABLE client ( c_id INTEGER PRIMARY KEY, c_name VARCHAR(100) NOT NULL, c_mail CHAR(30) UNIQUE, c_id_card CHAR(20) UNIQUE NOT NULL, c_phone CHAR(20) UNIQUE NOT NULL, c_password CHAR(20) NOT NULL );
-- 为字段添加注释 COMMENT ON COLUMN client.c_id IS '客户编码'; COMMENT ON COLUMN client.c_name IS '客户名称'; COMMENT ON COLUMN client.c_mail IS '客户邮箱'; COMMENT ON COLUMN client.c_id_card IS '客户身份证'; COMMENT ON COLUMN client.c_phone IS '客户手机号'; COMMENT ON COLUMN client.c_password IS '客户登录密码';
创建band_card表
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
-- 如果存在 bank_card 表，则先删除它 DROP TABLE IF EXISTS bank_card;
-- 为字段添加注释 COMMENT ON COLUMN bank_card.b_number IS '银行卡号'; COMMENT ON COLUMN bank_card.b_type IS '银行卡类型'; COMMENT ON COLUMN bank_card.b_c_id IS '所属客户编号';
创建finances_product表
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
-- 如果存在 finances_product 表，则先删除它 DROP TABLE IF EXISTS finances_product;
-- 为字段添加注释 COMMENT ON COLUMN finances_product.p_name IS '产品名称'; COMMENT ON COLUMN finances_product.p_id IS '产品编号'; COMMENT ON COLUMN finances_product.p_description IS '产品描述'; COMMENT ON COLUMN finances_product.p_amount IS '购买金额'; COMMENT ON COLUMN finances_product.p_year IS '理财年限';
-- 为字段添加注释 COMMENT ON COLUMN insurance.i_name IS '保险名称'; COMMENT ON COLUMN insurance.i_id IS '保险编号'; COMMENT ON COLUMN insurance.i_amount IS '保险金额'; COMMENT ON COLUMN insurance.i_person IS '适用人群'; COMMENT ON COLUMN insurance.i_year IS '保险年限'; COMMENT ON COLUMN insurance.i_project IS '保障项目';
-- 创建 fund 表 CREATE TABLE fund ( f_name VARCHAR(100) NOT NULL, -- 基金名称 f_id INTEGER PRIMARY KEY, -- 基金编号，作为主键 f_type CHAR(20), -- 基金类型 f_amount INTEGER, -- 基金金额 risk_level CHAR(20) NOT NULL, -- 风险等级 f_manager INTEGER NOT NULL -- 基金管理者 );
-- 为字段添加注释 COMMENT ON COLUMN fund.f_name IS '基金名称'; COMMENT ON COLUMN fund.f_id IS '基金编号'; COMMENT ON COLUMN fund.f_type IS '基金类型'; COMMENT ON COLUMN fund.f_amount IS '基金金额'; COMMENT ON COLUMN fund.risk_level IS '风险等级'; COMMENT ON COLUMN fund.f_manager IS '基金管理者';
-- 为字段添加注释 COMMENT ON COLUMN property.pro_id IS '资产编号'; COMMENT ON COLUMN property.pro_c_id IS '客户编号'; COMMENT ON COLUMN property.pro_pif_id IS '商品编号'; COMMENT ON COLUMN property.pro_type IS '商品类型，1表示理财产品；2表示保险；3表示基金'; COMMENT ON COLUMN property.pro_status IS '商品状态'; COMMENT ON COLUMN property.pro_quantity IS '商品数量'; COMMENT ON COLUMN property.pro_income IS '商品收益'; COMMENT ON COLUMN property.pro_purchase_time IS '购买时间';
ALTER table finances_product ADD CONSTRAINT c_p_mount CHECK (p_amount >=0); ALTER table fund ADD CONSTRAINT c_f_mount CHECK (f_amount >=0); ALTER table insurance ADD CONSTRAINT c_i_mount CHECK (i_amount >=0);
查询部分
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
select * from bank_card bc ; select * from property where pro_status = '持有中'; select count(*) from client; select count(*) from bank_card where b_type = '储蓄卡'; select count(*) from bank_card where b_type = '信用卡'; select avg(i_amount) from insurance; select i_name,i_amount from insurance where i_amount = (select max(i_amount) from insurance); select i_name,i_amount from insurance where i_amount = (select min(i_amount) from insurance); select c_id, c_name, c_id_card from client where exists (select 1 from bank_card where bank_card.b_c_id = client.c_id); select c_id, c_name, c_id_card from client where not exists (select 1 from bank_card where bank_card.b_c_id = client.c_id and bank_card.b_number like '622202130202000001%'); select i_name,i_person from insurance where i_amount > (select avg(i_amount) from insurance); select i_id,i_name,i_amount,i_person from insurance where i_id >2 order by i_amount desc; select p_year,count(*) from finances_product group by p_year; select i_person,count(*) from insurance group by i_person having count(*) = 2; with fundinfo as (select f_id,f_name,f_amount,risk_level from fund) select * from fundinfo;
md，全是截图，截得快吐了
视图部分
1 2 3 4 5 6 7 8 9 10
create view v_client as select c.c_id,c.c_name,c.c_id_card from "client" c where exists (select 1 from bank_card b where b.b_c_id = c.c_id); select * from v_client; create or replace view v_client as select c.c_id,c.c_name,c.c_id_card from "client" c join bank_card b on b.b_c_id = c.c_id where b.b_type='信用卡'; select * from v_client; alter view v_client rename to v_client_next; select * from v_client_next; drop view v_client_next; select * from v_client_next vcn ;
索引部分
1 2 3 4
create index idx_property_pro_c_id on property(pro_c_id); reindex index idx_property_pro_c_id; alter index idx_property_pro_c_id rename to idx_property_temp; drop index idx_property_temp;
更新与删除部分
1 2
update bank_card set b_type ='借记卡'from "client" where bank_card.b_c_id = "client".c_id and bank_card.b_c_id <10 deletefrom fund where f_id<3
# 构建用户表 create table users ( user_id serial primary key, -- 用户ID, 自动递增 username varchar(100) not null, -- 用户名 password varchar(255) not null, -- 用户密码，存储加密后的密码 email varchar(255), -- 用户邮箱 role varchar(20) not null, -- 用户角色，使用 varchar 类型 created_at timestamp default current_timestamp, -- 用户创建时间 updated_at timestamp default current_timestamp, -- 用户更新时间 check (role in ('student', 'teacher', 'admin')) -- 使用 check 约束限制角色值 );
-- 添加注释 comment on column users.user_id is '用户唯一标识符，自动递增'; comment on column users.username is '用户登录名，必须唯一'; comment on column users.password is '用户密码，存储加密后的密码'; comment on column users.email is '用户的电子邮件地址'; comment on column users.role is '用户角色，值为 student、teacher 或 admin'; comment on column users.created_at is '记录创建时间，默认当前时间'; comment on column users.updated_at is '记录最后更新时间，默认当前时间';
# 构建题目表 create table questions ( question_id serial primary key, -- 题目ID, 自动递增 content text not null, -- 题目内容 type varchar(20) not null, -- 题目类型 difficulty int not null, -- 难度等级 (1-5) category_id int, -- 分类ID (外键) created_at timestamp default current_timestamp, -- 创建时间 updated_at timestamp default current_timestamp, -- 更新时间 check (type in ('single', 'multiple', 'fill', 'true_false')), -- 题目类型约束 check (difficulty between 1 and 5) -- 难度等级约束 (1-5) );
-- 添加注释 comment on column questions.question_id is '题目唯一标识符，自动递增'; comment on column questions.content is '题目的内容'; comment on column questions.type is '题目类型，值为 single, multiple, fill 或 true_false'; comment on column questions.difficulty is '题目的难度等级，范围为 1 到 5'; comment on column questions.category_id is '题目所属的分类ID'; comment on column questions.created_at is '题目创建时间，默认当前时间'; comment on column questions.updated_at is '题目更新时间，默认当前时间';
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
# 构建选项表 create table options ( option_id serial primary key, -- 选项ID, 自动递增 question_id int not null, -- 题目ID (外键) content varchar(255) not null, -- 选项内容 is_correct boolean not null, -- 是否为正确选项 foreign key (question_id) references questions(question_id) on delete cascade -- 外键约束 );
-- 添加注释 comment on column options.option_id is '选项唯一标识符，自动递增'; comment on column options.question_id is '关联的题目ID'; comment on column options.content is '选项的内容'; comment on column options.is_correct is '选项是否为正确答案';
1 2 3 4 5 6 7 8 9 10 11 12
# 构建题目分类表 create table categories ( category_id serial primary key, -- 分类ID, 自动递增 name varchar(100) not null, -- 分类名称 description text -- 分类描述 );
-- 添加注释 comment on column categories.category_id is '分类唯一标识符，自动递增'; comment on column categories.name is '分类名称'; comment on column categories.description is '分类描述';
# 构建答案表 create table answers ( answer_id serial primary key, -- 答案ID, 自动递增 user_id int not null, -- 用户ID (外键) question_id int not null, -- 题目ID (外键) answer text not null, -- 用户的答案内容 is_correct boolean, -- 用户的答案是否正确 submitted_at timestamp default current_timestamp, -- 提交时间 foreign key (user_id) references users(user_id) on delete cascade, -- 外键约束 foreign key (question_id) references questions(question_id) on delete cascade -- 外键约束 );
-- 添加注释 comment on column answers.answer_id is '答案唯一标识符，自动递增'; comment on column answers.user_id is '提交答案的用户ID'; comment on column answers.question_id is '关联的题目ID'; comment on column answers.answer is '用户的答案内容'; comment on column answers.is_correct is '用户答案是否正确'; comment on column answers.submitted_at is '答案提交时间，默认当前时间';
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
# 构建考试表 create table exams ( exam_id serial primary key, -- 考试ID, 自动递增 name varchar(255) not null, -- 考试名称 start_time timestamp not null, -- 考试开始时间 end_time timestamp not null, -- 考试结束时间 duration int not null, -- 考试时长（分钟） created_at timestamp default current_timestamp -- 创建时间 );
-- 添加注释 comment on column exams.exam_id is '考试唯一标识符，自动递增'; comment on column exams.name is '考试名称'; comment on column exams.start_time is '考试开始时间'; comment on column exams.end_time is '考试结束时间'; comment on column exams.duration is '考试时长，单位为分钟'; comment on column exams.created_at is '考试创建时间，默认当前时间';
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
# 构建考试关联表 create table exam_questions ( exam_id int not null, -- 考试ID (外键) question_id int not null, -- 题目ID (外键) question_order int not null, -- 题目在考试中的顺序 primary key (exam_id, question_id), -- 联合主键 foreign key (exam_id) references exams(exam_id) on delete cascade, -- 外键约束 foreign key (question_id) references questions(question_id) on delete cascade -- 外键约束 );
-- 添加注释 comment on column exam_questions.exam_id is '关联的考试ID'; comment on column exam_questions.question_id is '关联的题目ID'; comment on column exam_questions.question_order is '题目在考试中的顺序';
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
# 构建成绩表 create table scores ( score_id serial primary key, -- 成绩ID, 自动递增 user_id int not null, -- 用户ID (外键) exam_id int not null, -- 考试ID (外键) score decimal(5,2) not null, -- 得分 total_score decimal(5,2) not null, -- 总分 created_at timestamp default current_timestamp, -- 记录时间 foreign key (user_id) references users(user_id) on delete cascade, -- 外键约束 foreign key (exam_id) references exams(exam_id) on delete cascade -- 外键约束 );
-- 添加注释 comment on column scores.score_id is '成绩唯一标识符，自动递增'; comment on column scores.user_id is '用户ID'; comment on column scores.exam_id is '考试ID'; comment on column scores.score is '用户在考试中的得分'; comment on column scores.total_score is '考试的总分'; comment on column scores.created_at is '成绩记录时间，默认当前时间';
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
# 日志表 create table logs ( log_id serial primary key, -- 日志ID, 自动递增 user_id int not null, -- 用户ID (外键) action varchar(255) not null, -- 操作类型（如登录、提交考试等） description text, -- 操作描述 created_at timestamp default current_timestamp, -- 操作时间 foreign key (user_id) references users(user_id) on delete cascade -- 外键约束 );
-- 添加注释 comment on column logs.log_id is '日志唯一标识符，自动递增'; comment on column logs.user_id is '执行操作的用户ID'; comment on column logs.action is '操作类型（如登录、提交考试等）'; comment on column logs.description is '操作的详细描述'; comment on column logs.created_at is '操作时间，默认当前时间';
1 2
# 为题目表添加外键 alter table questions add constraint fk_category foreign key (category_id) references categories(category_id) on delete set null;
# 单表查询 select * from users; select username,email from users; select * from users where role = 'student'; select username,length(username) as len from users; select * from users where role in ('teacher','admin'); select * from users where username like 'user%'; select * from users order by email asc; select count(*) as count from users; select role,count(*) as rolecount from users group by role;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
# 多表查询 select q.content, c.name as category_name from questions q join categories c on q.category_id = c.category_id; select q.content, c.name as category_name, u.username from questions q join categories c on q.category_id = c.category_id join users u on q.category_id = u.user_id; select c1.name as category1, c2.name as category2, c1.description from categories c1 join categories c2 on c1.description = c2.description and c1.category_id <> c2.category_id; select c.name as category_name, q.content as question_content from categories c left join questions q on c.category_id = q.category_id; select username, (select count(*) from users) as total_users from users; select name from categories c where exists (select 1 from questions q where q.category_id = c.category_id); select username from users u where exists (select 1 from questions q where q.category_id = u.user_id); select d.category_name, d.question_count from (select c.name as category_name, count(q.content) as question_count from categories c left join questions q on c.category_id = q.category_id group by c.name) d order by d.question_count desc;
视图部分
1 2 3 4
# 创建视图 create view student_users as select username, email, role from users where role = 'student'; create view student_user_emails as select username, email from student_users; select username from student_user_emails order by username asc;
触发器部分
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
# 创建触发器函数 create or replace function log_user_login() returns trigger as $$ begin insert into logs (user_id, action, created_at, description) values (new.user_id, 'login', current_timestamp, null); return new; end; $$ language plpgsql; # 创建触发器 create trigger user_login after update on users for each row when (new.updated_at is distinct from old.updated_at) execute procedure log_user_login(); # 触发触发器 update users set updated_at = current_timestamp where role = 'admin';
注意opengauss创建触发器使用的是procedure而不是function
新建用户并给予权限
1 2
create user teacher with password 'teacher_password'; grant insert, update, delete on table questions to teacher;
public class Main { public static void main(String[] args) { // 数据库连接信息 String url = "jdbc:postgresql://192.168.92.188:5432/finance"; String user = "gaussdb"; String password = "Opengauss123!";
