oceanbase

1
drop table if exists persons,orders, t1, t2, t3;
2
create table persons (id_p int primary key, lastname varchar(20), firstname varchar(20), address varchar(20), city varchar(20));
3
insert into persons values(1,'a','a1','a2', 'a3');
4
insert into persons values(2,'b','b1','b2', 'b3');
5
insert into persons values(3,'c','c1','c2', 'c3');
6
create table orders(id_o int primary key, orderno int, id_p int);
7
insert into orders values(1,1111,3);
8
insert into orders values(2,2222,3);
9
insert into orders values(3,3333,1);
10
insert into orders values(4,4444,1);
11
insert into orders values(5,5555,65);
12
select persons.lastname, persons.firstname, orders.orderno from persons left join orders on persons.id_p=orders.id_p order by persons.lastname, persons.firstname, orders.orderno;
13
lastname	firstname	orderno
14
a	a1	3333
15
a	a1	4444
16
b	b1	NULL
17
c	c1	1111
18
c	c1	2222
19
select persons.lastname, persons.firstname, orders.orderno from persons right join orders on persons.id_p=orders.id_p order by persons.lastname, persons.firstname, orders.orderno;
20
lastname	firstname	orderno
21
NULL	NULL	5555
22
a	a1	3333
23
a	a1	4444
24
c	c1	1111
25
c	c1	2222
26
select persons.lastname, persons.firstname, orders.orderno
27
from persons
28
inner join orders
29
on persons.id_p=orders.id_p
30
order by persons.lastname, persons.firstname, orders.orderno;
31
lastname	firstname	orderno
32
a	a1	3333
33
a	a1	4444
34
c	c1	1111
35
c	c1	2222
36
select persons.lastname, persons.firstname, orders.orderno from persons full join orders on persons.id_p=orders.id_p order by persons.lastname, persons.firstname, orders.orderno;
37
lastname	firstname	orderno
38
NULL	NULL	5555
39
a	a1	3333
40
a	a1	4444
41
b	b1	NULL
42
c	c1	1111
43
c	c1	2222
44
create table t1(c1 date);
45
create table t2(c1 int primary key);
46
insert into t1 values('2078-10-10'), ('1970-11-01');
47
insert into t2 values(320);
48
select * from t1, t2 where t1.c1<=t2.c1;
49
c1	c1
50
1970-11-01	320
51
drop table if exists t1;
52
create table t1(a bigint);
53
insert into t1 values (32);
54
drop table if exists t2;
55
create table t2(b year(4), key key_b (b));
56
insert into t2 values (1901);
57
create table t3(c year(4));
58
insert into t3 values (1901);
59
select a, b from t1, t2 where a > b;
60
a	b
61
select a, c from t1, t3 where a > c;
62
a	c
63
drop table if exists t1, t2;
64
create table t1(c1 bigint(92));
65
create table t2(c1 year(4) primary key);
66
insert into t1 values(32);
67
insert into t2 values(1901);
68
select * from t1;
69
c1
70
32
71
select * from t2;
72
c1
73
1901
74
select * from t1, t2 where t1.c1>=t2.c1;
75
c1	c1
76
drop table orders,persons, t1, t2, t3;
77