oceanbase

1
set ob_query_timeout=1000000000;
2
--disable_warnings
3
drop database if exists px_test;
4
--enable_warnings
5
create database px_test;
6
use px_test;
7

8
# 场景：一个年级 6 个班，修了2门课，英语和数学
9

10
create table stu (
11
  sid     int,
12
  name    varchar(32),
13
  cls     int,
14
  primary key (cls, sid)
15
) partition by hash(sid) partitions 6;
16

17
create table teacher (
18
  tid        int,
19
  name       varchar(32),
20
  subject    varchar(4),
21
  primary key (tid)
22
) partition by hash(tid) partitions 8;
23

24
create table score (
25
  sid     int,
26
  subject varchar(4),
27
  score   int,
28
  primary key (sid, subject)
29
) partition by hash(sid) partitions 6;
30

31
insert into stu values
32
(11, 'a1', 1),
33
(12, 'b1', 1),
34
(13, 'c1', 1),
35

36
(21, 'a2', 2),
37
(22, 'b2', 2),
38

39
(31, 'a3', 3),
40

41
(41, 'a4', 4),
42
(42, 'b4', 4),
43

44
(51, 'a5', 5),
45
(52, 'b5', 5),
46

47
(61, 'a6', 6),
48
(62, 'b6', 6),
49
(63, 'c6', 6),
50
(64, 'd6', 6);
51

52
insert into teacher values
53
(1, 'Miss Zhang', 'EN'),
54
(2, 'Mr Wang', 'MA');
55

56

57
insert into score values
58
(11, 'EN', 60),
59
(12, 'EN', 70),
60
(13, 'EN', 80),
61

62
(21, 'EN', 58),
63
(22, 'EN', 90),
64

65
(31, 'EN', 80),
66

67
(41, 'EN', 80),
68
(42, 'EN', 90),
69

70
(51, 'EN', 89),
71
(52, 'EN', 99),
72

73
(61, 'EN', 100),
74
(62, 'EN', 90),
75
(63, 'EN', 99),
76
(64, 'EN', 87);
77

78

79
insert into score values
80
(11, 'MA', 60),
81
(12, 'MA', 70),
82
(13, 'MA', 80),
83

84
(21, 'MA', 58),
85
(22, 'MA', 90),
86

87
(31, 'MA', 80),
88

89
(41, 'MA', 80),
90
(42, 'MA', 90),
91

92
(51, 'MA', 89),
93
(52, 'MA', 99),
94

95
(61, 'MA', 100),
96
(62, 'MA', 90),
97
(63, 'MA', 99),
98
(64, 'MA', 87);
99

100
--disable_query_log
101
--disable_result_log
102
select * from stu;
103
select * from teacher;
104
select * from score;
105
--enable_query_log
106
--enable_result_log
107