213 lines
12 KiB
SQL
213 lines
12 KiB
SQL
-- 丙村中学模拟数据SQL脚本
|
||
-- 适用于PostgreSQL数据库结构
|
||
-- 包含：地区、学校、年级、班级的完整数据
|
||
|
||
-- 清理现有测试数据（可选，谨慎使用）
|
||
-- DELETE FROM public.ak_classes WHERE id IN (SELECT c.id FROM public.ak_classes c JOIN public.ak_grades g ON c.grade_id = g.id JOIN public.ak_schools s ON g.school_id = s.id WHERE s.name = '丙村中学');
|
||
-- DELETE FROM public.ak_grades WHERE school_id IN (SELECT id FROM public.ak_schools WHERE name = '丙村中学');
|
||
-- DELETE FROM public.ak_schools WHERE name = '丙村中学';
|
||
|
||
-- Step 1: 创建地区数据（如果不存在）
|
||
-- 假设丙村中学位于广东省梅州市丰顺县
|
||
INSERT INTO public.ak_regions (id, name, parent_id, level, created_at) VALUES
|
||
('f47ac10b-58cc-4372-a567-0e02b2c3d479', '广东省', NULL, 1, now()),
|
||
('f47ac10b-58cc-4372-a567-0e02b2c3d480', '梅州市', 'f47ac10b-58cc-4372-a567-0e02b2c3d479', 2, now()),
|
||
('f47ac10b-58cc-4372-a567-0e02b2c3d481', '丰顺县', 'f47ac10b-58cc-4372-a567-0e02b2c3d480', 3, now()),
|
||
('f47ac10b-58cc-4372-a567-0e02b2c3d482', '丙村镇', 'f47ac10b-58cc-4372-a567-0e02b2c3d481', 4, now())
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
-- Step 2: 创建丙村中学学校数据
|
||
INSERT INTO public.ak_schools (id, name, region_id, type, created_at) VALUES
|
||
('bc123456-78cd-4ef0-a123-456789012345', '丙村中学', 'f47ac10b-58cc-4372-a567-0e02b2c3d482', '初级中学', now())
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
-- Step 3: 创建年级数据（初一到初三）
|
||
INSERT INTO public.ak_grades (id, school_id, name, created_at) VALUES
|
||
('bc111111-78cd-4ef0-a123-456789012345', 'bc123456-78cd-4ef0-a123-456789012345', '初一年级', now()),
|
||
('bc222222-78cd-4ef0-a123-456789012345', 'bc123456-78cd-4ef0-a123-456789012345', '初二年级', now()),
|
||
('bc333333-78cd-4ef0-a123-456789012345', 'bc123456-78cd-4ef0-a123-456789012345', '初三年级', now())
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
-- Step 4: 创建班级数据（每个年级6个班）
|
||
-- 初一年级6个班
|
||
INSERT INTO public.ak_classes (id, grade_id, name, created_at) VALUES
|
||
('bc111101-78cd-4ef0-a123-456789012345', 'bc111111-78cd-4ef0-a123-456789012345', '初一(1)班', now()),
|
||
('bc111102-78cd-4ef0-a123-456789012345', 'bc111111-78cd-4ef0-a123-456789012345', '初一(2)班', now()),
|
||
('bc111103-78cd-4ef0-a123-456789012345', 'bc111111-78cd-4ef0-a123-456789012345', '初一(3)班', now()),
|
||
('bc111104-78cd-4ef0-a123-456789012345', 'bc111111-78cd-4ef0-a123-456789012345', '初一(4)班', now()),
|
||
('bc111105-78cd-4ef0-a123-456789012345', 'bc111111-78cd-4ef0-a123-456789012345', '初一(5)班', now()),
|
||
('bc111106-78cd-4ef0-a123-456789012345', 'bc111111-78cd-4ef0-a123-456789012345', '初一(6)班', now()),
|
||
|
||
-- 初二年级6个班
|
||
('bc222201-78cd-4ef0-a123-456789012345', 'bc222222-78cd-4ef0-a123-456789012345', '初二(1)班', now()),
|
||
('bc222202-78cd-4ef0-a123-456789012345', 'bc222222-78cd-4ef0-a123-456789012345', '初二(2)班', now()),
|
||
('bc222203-78cd-4ef0-a123-456789012345', 'bc222222-78cd-4ef0-a123-456789012345', '初二(3)班', now()),
|
||
('bc222204-78cd-4ef0-a123-456789012345', 'bc222222-78cd-4ef0-a123-456789012345', '初二(4)班', now()),
|
||
('bc222205-78cd-4ef0-a123-456789012345', 'bc222222-78cd-4ef0-a123-456789012345', '初二(5)班', now()),
|
||
('bc222206-78cd-4ef0-a123-456789012345', 'bc222222-78cd-4ef0-a123-456789012345', '初二(6)班', now()),
|
||
|
||
-- 初三年级6个班
|
||
('bc333301-78cd-4ef0-a123-456789012345', 'bc333333-78cd-4ef0-a123-456789012345', '初三(1)班', now()),
|
||
('bc333302-78cd-4ef0-a123-456789012345', 'bc333333-78cd-4ef0-a123-456789012345', '初三(2)班', now()),
|
||
('bc333303-78cd-4ef0-a123-456789012345', 'bc333333-78cd-4ef0-a123-456789012345', '初三(3)班', now()),
|
||
('bc333304-78cd-4ef0-a123-456789012345', 'bc333333-78cd-4ef0-a123-456789012345', '初三(4)班', now()),
|
||
('bc333305-78cd-4ef0-a123-456789012345', 'bc333333-78cd-4ef0-a123-456789012345', '初三(5)班', now()),
|
||
('bc333306-78cd-4ef0-a123-456789012345', 'bc333333-78cd-4ef0-a123-456789012345', '初三(6)班', now())
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
-- Step 5: 验证数据完整性
|
||
-- 查看创建的学校信息
|
||
SELECT
|
||
s.id as school_id,
|
||
s.name as school_name,
|
||
s.type as school_type,
|
||
r.name as region_name
|
||
FROM public.ak_schools s
|
||
LEFT JOIN public.ak_regions r ON s.region_id = r.id
|
||
WHERE s.name = '丙村中学';
|
||
|
||
-- 查看年级和班级统计
|
||
SELECT
|
||
g.name as grade_name,
|
||
COUNT(c.id) as class_count,
|
||
array_agg(c.name ORDER BY c.name) as class_names
|
||
FROM public.ak_grades g
|
||
LEFT JOIN public.ak_classes c ON g.id = c.grade_id
|
||
JOIN public.ak_schools s ON g.school_id = s.id
|
||
WHERE s.name = '丙村中学'
|
||
GROUP BY g.id, g.name
|
||
ORDER BY g.name;
|
||
|
||
-- 查看完整的层级结构
|
||
SELECT
|
||
r.name as region,
|
||
s.name as school,
|
||
g.name as grade,
|
||
c.name as class_name,
|
||
c.id as class_id
|
||
FROM public.ak_regions r
|
||
JOIN public.ak_schools s ON r.id = s.region_id
|
||
JOIN public.ak_grades g ON s.id = g.school_id
|
||
JOIN public.ak_classes c ON g.id = c.grade_id
|
||
WHERE s.name = '丙村中学'
|
||
ORDER BY g.name, c.name;
|
||
|
||
-- Step 6: 创建一些示例教师用户（可选）
|
||
-- 创建学校管理员和年级主任
|
||
INSERT INTO public.ak_users (id, username, email, password_hash, role, school_id, created_at) VALUES
|
||
('550e8400-e29b-41d4-a716-446655440001', 'bingcun_admin', 'admin@bingcun.edu.cn', '$2a$10$dummy_hash_for_admin', 'school_admin', 'bc123456-78cd-4ef0-a123-456789012345', now()),
|
||
('550e8400-e29b-41d4-a716-446655440002', 'zhang_teacher', 'zhang@bingcun.edu.cn', '$2a$10$dummy_hash_for_teacher1', 'teacher', 'bc123456-78cd-4ef0-a123-456789012345', now()),
|
||
('550e8400-e29b-41d4-a716-446655440003', 'li_teacher', 'li@bingcun.edu.cn', '$2a$10$dummy_hash_for_teacher2', 'teacher', 'bc123456-78cd-4ef0-a123-456789012345', now()),
|
||
('550e8400-e29b-41d4-a716-446655440004', 'wang_teacher', 'wang@bingcun.edu.cn', '$2a$10$dummy_hash_for_teacher3', 'teacher', 'bc123456-78cd-4ef0-a123-456789012345', now())
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
-- 分配教师到对应年级（示例）
|
||
INSERT INTO public.ak_teacher_roles (id, user_id, school_id, class_id, role, created_at) VALUES
|
||
-- 张老师负责初一年级
|
||
('550e8400-e29b-41d4-a716-446655440011', '550e8400-e29b-41d4-a716-446655440002', 'bc123456-78cd-4ef0-a123-456789012345', 'bc111101-78cd-4ef0-a123-456789012345', 'class_teacher', now()),
|
||
('550e8400-e29b-41d4-a716-446655440012', '550e8400-e29b-41d4-a716-446655440002', 'bc123456-78cd-4ef0-a123-456789012345', 'bc111102-78cd-4ef0-a123-456789012345', 'teacher', now()),
|
||
|
||
-- 李老师负责初二年级
|
||
('550e8400-e29b-41d4-a716-446655440013', '550e8400-e29b-41d4-a716-446655440003', 'bc123456-78cd-4ef0-a123-456789012345', 'bc222201-78cd-4ef0-a123-456789012345', 'class_teacher', now()),
|
||
('550e8400-e29b-41d4-a716-446655440014', '550e8400-e29b-41d4-a716-446655440003', 'bc123456-78cd-4ef0-a123-456789012345', 'bc222202-78cd-4ef0-a123-456789012345', 'teacher', now()),
|
||
|
||
-- 王老师负责初三年级
|
||
('550e8400-e29b-41d4-a716-446655440015', '550e8400-e29b-41d4-a716-446655440004', 'bc123456-78cd-4ef0-a123-456789012345', 'bc333301-78cd-4ef0-a123-456789012345', 'class_teacher', now()),
|
||
('550e8400-e29b-41d4-a716-446655440016', '550e8400-e29b-41d4-a716-446655440004', 'bc123456-78cd-4ef0-a123-456789012345', 'bc333302-78cd-4ef0-a123-456789012345', 'teacher', now())
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
-- Step 7: 创建一些示例学生用户（每班部分学生）
|
||
-- 初一(1)班学生示例
|
||
INSERT INTO public.ak_users (id, username, email, password_hash, role, school_id, grade_id, class_id, created_at) VALUES
|
||
('550e8400-e29b-41d4-a716-446655440021', 'zhang_xiaoming', 'zhang.xiaoming@student.bingcun.edu.cn', '$2a$10$dummy_hash_student', 'student', 'bc123456-78cd-4ef0-a123-456789012345', 'bc111111-78cd-4ef0-a123-456789012345', 'bc111101-78cd-4ef0-a123-456789012345', now()),
|
||
('550e8400-e29b-41d4-a716-446655440022', 'li_xiaohong', 'li.xiaohong@student.bingcun.edu.cn', '$2a$10$dummy_hash_student', 'student', 'bc123456-78cd-4ef0-a123-456789012345', 'bc111111-78cd-4ef0-a123-456789012345', 'bc111101-78cd-4ef0-a123-456789012345', now()),
|
||
('550e8400-e29b-41d4-a716-446655440023', 'wang_xiaogang', 'wang.xiaogang@student.bingcun.edu.cn', '$2a$10$dummy_hash_student', 'student', 'bc123456-78cd-4ef0-a123-456789012345', 'bc111111-78cd-4ef0-a123-456789012345', 'bc111101-78cd-4ef0-a123-456789012345', now()),
|
||
|
||
-- 初二(1)班学生示例
|
||
('550e8400-e29b-41d4-a716-446655440024', 'chen_xiaoli', 'chen.xiaoli@student.bingcun.edu.cn', '$2a$10$dummy_hash_student', 'student', 'bc123456-78cd-4ef0-a123-456789012345', 'bc222222-78cd-4ef0-a123-456789012345', 'bc222201-78cd-4ef0-a123-456789012345', now()),
|
||
('550e8400-e29b-41d4-a716-446655440025', 'liu_xiaowei', 'liu.xiaowei@student.bingcun.edu.cn', '$2a$10$dummy_hash_student', 'student', 'bc123456-78cd-4ef0-a123-456789012345', 'bc222222-78cd-4ef0-a123-456789012345', 'bc222201-78cd-4ef0-a123-456789012345', now()),
|
||
|
||
-- 初三(1)班学生示例
|
||
('550e8400-e29b-41d4-a716-446655440026', 'zhao_xiaojun', 'zhao.xiaojun@student.bingcun.edu.cn', '$2a$10$dummy_hash_student', 'student', 'bc123456-78cd-4ef0-a123-456789012345', 'bc333333-78cd-4ef0-a123-456789012345', 'bc333301-78cd-4ef0-a123-456789012345', now()),
|
||
('550e8400-e29b-41d4-a716-446655440027', 'sun_xiaofang', 'sun.xiaofang@student.bingcun.edu.cn', '$2a$10$dummy_hash_student', 'student', 'bc123456-78cd-4ef0-a123-456789012345', 'bc333333-78cd-4ef0-a123-456789012345', 'bc333301-78cd-4ef0-a123-456789012345', now())
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
-- Step 8: 最终数据验证和统计
|
||
-- 显示完整的数据统计
|
||
SELECT '=== 丙村中学数据创建完成 ===' as status;
|
||
|
||
SELECT
|
||
'学校信息' as info_type,
|
||
s.name as name,
|
||
s.type as type,
|
||
r.name as location
|
||
FROM public.ak_schools s
|
||
LEFT JOIN public.ak_regions r ON s.region_id = r.id
|
||
WHERE s.name = '丙村中学'
|
||
|
||
UNION ALL
|
||
|
||
SELECT
|
||
'年级统计' as info_type,
|
||
CONCAT(COUNT(DISTINCT g.id), ' 个年级') as name,
|
||
'' as type,
|
||
string_agg(DISTINCT g.name, ', ' ORDER BY g.name) as location
|
||
FROM public.ak_grades g
|
||
JOIN public.ak_schools s ON g.school_id = s.id
|
||
WHERE s.name = '丙村中学'
|
||
|
||
UNION ALL
|
||
|
||
SELECT
|
||
'班级统计' as info_type,
|
||
CONCAT(COUNT(c.id), ' 个班级') as name,
|
||
'' as type,
|
||
'每个年级6个班' as location
|
||
FROM public.ak_classes c
|
||
JOIN public.ak_grades g ON c.grade_id = g.id
|
||
JOIN public.ak_schools s ON g.school_id = s.id
|
||
WHERE s.name = '丙村中学'
|
||
|
||
UNION ALL
|
||
|
||
SELECT
|
||
'用户统计' as info_type,
|
||
CONCAT(COUNT(u.id), ' 个用户') as name,
|
||
CONCAT(
|
||
SUM(CASE WHEN u.role = 'student' THEN 1 ELSE 0 END), ' 学生, ',
|
||
SUM(CASE WHEN u.role = 'teacher' THEN 1 ELSE 0 END), ' 教师, ',
|
||
SUM(CASE WHEN u.role = 'school_admin' THEN 1 ELSE 0 END), ' 管理员'
|
||
) as type,
|
||
'' as location
|
||
FROM public.ak_users u
|
||
WHERE u.school_id = 'bc123456-78cd-4ef0-a123-456789012345';
|
||
|
||
-- 显示详细的班级列表供参考
|
||
SELECT
|
||
g.name as grade_name,
|
||
c.name as class_name,
|
||
c.id as class_id,
|
||
'可用于作业分配' as note
|
||
FROM public.ak_grades g
|
||
JOIN public.ak_classes c ON g.id = c.grade_id
|
||
JOIN public.ak_schools s ON g.school_id = s.id
|
||
WHERE s.name = '丙村中学'
|
||
ORDER BY g.name, c.name;
|
||
|
||
/*
|
||
使用说明：
|
||
1. 此脚本创建了完整的丙村中学数据结构
|
||
2. 包含地区(丙村镇) -> 学校(丙村中学) -> 年级(初一到初三) -> 班级(每个年级6个班)
|
||
3. 创建了示例的教师和学生用户
|
||
4. 使用了固定的UUID以确保数据的一致性
|
||
5. 包含了ON CONFLICT DO NOTHING以避免重复插入
|
||
|
||
数据结构：
|
||
- 1个学校：丙村中学
|
||
- 3个年级：初一、初二、初三年级
|
||
- 18个班级：每个年级6个班
|
||
- 示例用户：4个教师 + 7个学生 + 1个管理员
|
||
|
||
这些班级ID可以直接用于作业分配和教师管理功能的测试。
|
||
*/
|