github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/ddl/create_index.sql (about)

     1  drop table if exists t1;
     2  create table t1(id int PRIMARY KEY,name VARCHAR(255),age int);
     3  insert into t1 values(1,"Abby", 24);
     4  insert into t1 values(2,"Bob", 25);
     5  insert into t1 values(3,"Carol", 23);
     6  insert into t1 values(4,"Dora", 29);
     7  create unique index idx on t1(name);
     8  select * from t1;
     9  drop table t1;
    10  
    11  create table t2 (
    12  col1 bigint primary key,
    13  col2 varchar(25),
    14  col3 float,
    15  col4 varchar(50)
    16  );
    17  create unique index idx on t2(col2) comment 'create varchar index';
    18  insert into t2 values(1,"Abby", 24,'zbcvdf');
    19  insert into t2 values(2,"Bob", 25,'zbcvdf');
    20  insert into t2 values(3,"Carol", 23,'zbcvdf');
    21  insert into t2 values(4,"Dora", 29,'zbcvdf');
    22  select * from t2;
    23  drop table t2;
    24  
    25  create table t3 (
    26  col1 bigint primary key,
    27  col2 varchar(25),
    28  col3 float,
    29  col4 varchar(50)
    30  );
    31  create unique index idx on t3(col2,col3);
    32  insert into t3 values(1,"Abby", 24,'zbcvdf');
    33  insert into t3 values(2,"Bob", 25,'zbcvdf');
    34  insert into t3 values(3,"Carol", 23,'zbcvdf');
    35  insert into t3 values(4,"Dora", 29,'zbcvdf');
    36  select * from t3;
    37  insert into t3 values(4,"Dora", 28,'zbcvdf');
    38  -- @pattern
    39  insert into t3 values(5,"Dora", 29,'zbcvdf');
    40  
    41  drop table t3;
    42  
    43  create table t4(a int, b int, key(c));
    44  
    45  create table t5(a int, b int, unique key(a));
    46  show create table t5;
    47  create index b on t5(b);
    48  show create table t5;
    49  drop index b on t5;
    50  show create table t5;
    51  drop table t5;
    52  
    53  create table t6(a int, b int, unique key(a));
    54  show create table t6;
    55  create index b on t6(a, b);
    56  show create table t6;
    57  drop index b on t6;
    58  show create table t6;
    59  drop table t6;
    60  
    61  create table t7(a int, b int);
    62  create unique index x ON t7(a) comment 'x';
    63  show create table t7;
    64  drop table t7;
    65  
    66  create table t8(a int, b int);
    67  create index x ON t8(a) comment 'x';
    68  show create table t8;
    69  drop table t8;
    70  
    71  create table t9(a int, b int, unique key(a) comment 'a');
    72  show create table t9;
    73  drop table t9;
    74  
    75  create table t10(a int, b int, key(a) comment 'a');
    76  show create table t10;
    77  drop table t10;
    78  
    79  create table t11(a int, b int, unique key(a) comment 'a');
    80  create unique index x ON t11(a) comment 'x';
    81  create index xx ON t11(a) comment 'xx';
    82  show create table t11;
    83  drop table t11;
    84  
    85  create table t12(a text);
    86  create unique index x on t12(a);
    87  create index x2 on t12(a);
    88  drop table t12;
    89  
    90  create table t13(a blob);
    91  create unique index x on t13(a);
    92  create index x2 on t13(a);
    93  drop table t13;
    94  
    95  create table t14(a json);
    96  create unique index x on t14(a);
    97  create index x2 on t14(a);
    98  drop table t14;
    99  
   100  create table t15(
   101  col1 int unsigned,
   102  col2 varchar(15),
   103  col3 varchar(10),
   104  col4 int unsigned,
   105  col5 date,
   106  col6 decimal(7,2),
   107  col7 decimal(7,2),
   108  col8 int unsigned,
   109  unique index(col1,col2,col3,col6)
   110  );
   111  INSERT INTO t15 VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
   112  INSERT INTO t15 VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
   113  INSERT INTO t15 VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
   114  INSERT INTO t15 VALUES (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
   115  INSERT INTO t15 VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
   116  INSERT INTO t15 VALUES (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
   117  INSERT INTO t15 VALUES (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
   118  INSERT INTO t15 VALUES (7788,'SCOTT','ANALYST',7566,'0087-07-13',3000,NULL,20);
   119  INSERT INTO t15 VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
   120  INSERT INTO t15 VALUES (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
   121  INSERT INTO t15 VALUES (7876,'ADAMS','CLERK',7788,'0087-07-13',1100,NULL,20);
   122  INSERT INTO t15 VALUES (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
   123  INSERT INTO t15 VALUES (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
   124  INSERT INTO t15 VALUES (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
   125  
   126  create unique index idx_1 on t15(col1,col2,col3,col6);
   127  select * from t15;
   128  drop table t15;