github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/ddl/create_table_like.sql (about) 1 drop database if exists test; 2 create database test; 3 use test; 4 5 -- single column primary key 6 drop table if exists pri01; 7 create table pri01(col1 int primary key , col2 decimal, col3 char, col4 varchar(20), col5 text, col6 double); 8 insert into pri01 values (1, 2, 'a', '23eiojf', 'r23v324r23rer', 3923.324); 9 insert into pri01 values (2, 3, 'b', '32r32r', 'database', 1111111); 10 insert into pri01 values (3, null, null, null, null, null); 11 drop table if exists pri02; 12 create table pri02 like pri01; 13 show create table pri01; 14 show create table pri02; 15 desc pri01; 16 desc pri02; 17 select * from pri01; 18 select * from pri02; 19 drop table pri01; 20 drop table pri02; 21 22 -- multi column primary key 23 drop table if exists pri03; 24 create table pri03(col1 int unsigned, col2 char, col3 binary(10), col4 decimal(20,0)); 25 alter table pri03 add primary key (col1, col3); 26 insert into pri03 values (1, '3', '324', 31.31231); 27 insert into pri03 values (2, 'v', '321', 28390); 28 drop table if exists pri04; 29 create table pri04 like pri03; 30 select * from pri03; 31 select * from pri04; 32 show create table pri03; 33 show create table pri04; 34 desc pri03; 35 desc pri04; 36 drop table pri03; 37 drop table pri04; 38 39 -- partition by 40 drop table if exists test03; 41 create table test03 ( 42 emp_no int not null, 43 birth_date date not null, 44 first_name varchar(14) not null, 45 last_name varchar(16) not null, 46 gender varchar(5) not null, 47 hire_date date not null, 48 primary key (emp_no) 49 ) partition by range columns (emp_no)( 50 partition p01 values less than (100001), 51 partition p02 values less than (200001), 52 partition p03 values less than (300001), 53 partition p04 values less than (400001) 54 ); 55 56 insert into test03 values (9001,'1980-12-17', 'SMITH', 'CLERK', 'F', '2008-12-17'), 57 (9002,'1981-02-20', 'ALLEN', 'SALESMAN', 'F', '2008-02-20'); 58 drop table if exists test04; 59 create table test04 like test03; 60 show create table test03; 61 show create table test04; 62 desc test03; 63 desc test04; 64 select * from test03; 65 select * from test04; 66 drop table test03; 67 drop table test04; 68 69 -- unique key 70 drop table if exists test07; 71 create table test07 (col1 int unique key, col2 varchar(20)); 72 insert into test07 (col1, col2) values (133, 'database'); 73 drop table if exists test08; 74 create table test08 like test07; 75 show create table test07; 76 show create table test08; 77 desc test07; 78 desc test08; 79 select * from test07; 80 select * from test08; 81 drop table test07; 82 drop table test08; 83 84 -- @bvt:issue#15296 85 drop table if exists test07; 86 create temporary table test07(col1 int unique key , col2 decimal, col3 char, col4 varchar(20), col5 text, col6 double, unique index(col1, col2)); 87 insert into test07 values (1, 2, 'a', '23eiojf', 'r23v324r23rer', 3923.324); 88 insert into test07 values (2, 3, 'b', '32r32r', 'database', 1111111); 89 insert into test07 values (3, null, null, null, null, null); 90 drop table if exists test08; 91 create table test08 like test07; 92 show create table test07; 93 show create table test08; 94 desc test07; 95 desc test08; 96 select * from test07; 97 select * from test08; 98 drop table test07; 99 drop table test08; 100 -- @bvt:issue 101 102 -- table with foreign key, then create table like 103 drop table if exists foreign01; 104 drop table if exists foreign02; 105 create table foreign01 (a int primary key, b varchar(5) unique key); 106 create table foreign02 (a int ,b varchar(5), c int, foreign key(c) references foreign01(a)); 107 insert into foreign01 values (101,'abc'),(102,'def'); 108 insert into foreign02 values (1,'zs1',101),(2,'zs2',102); 109 drop table if exists foreign03; 110 drop table if exists foreign04; 111 create table foreign03 like foreign01; 112 create table foreign04 like foreign02; 113 desc foreign01; 114 desc foreign02; 115 desc foreign03; 116 desc foreign04; 117 select * from foreign01; 118 select * from foreign02; 119 select * from foreign03; 120 select * from foreign04; 121 drop table foreign02; 122 drop table foreign01; 123 drop table foreign04; 124 drop table foreign03; 125 126 -- auto_increment 127 drop table if exists null01; 128 create table null01(col1 int auto_increment primary key, col2 char, col3 varchar(20)); 129 insert into null01 values (1, '2', 'database'); 130 insert into null01 values (2, 'a', 'table'); 131 drop table if exists null02; 132 create table null02 like null01; 133 show create table null01; 134 show create table null02; 135 desc null01; 136 desc null02; 137 select * from null01; 138 select * from null02; 139 drop table null01; 140 drop table null02; 141 142 -- create table like in prepare statement 143 drop table if exists prepare01; 144 create table prepare01(col1 int primary key , col2 char); 145 insert into prepare01 values (1,'a'),(2,'b'),(3,'c'); 146 drop table if exists prepare02; 147 prepare s1 from 'create table prepare02 like prepare01'; 148 execute s1; 149 show create table prepare01; 150 show create table prepare02; 151 desc prepare01; 152 desc prepare02; 153 select * from prepare01; 154 select * from prepare02; 155 drop table prepare01; 156 drop table prepare02; 157 158 -- create table like view 159 drop table if exists table10; 160 create table table10 (id int, name varchar(50)); 161 show create table table10; 162 insert into table10 values(1,'ashley'),(2,'ben'),(3,'cindy'); 163 select * from table10; 164 drop view if exists view01; 165 create view view01 as select * from table10; 166 drop table if exists table11; 167 create table table11 like view01; 168 show create view view01; 169 select * from view01; 170 drop view view01; 171 drop table table10; 172 173 drop database test;