github.com/matrixorigin/matrixone@v0.7.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", 29,'zbcvdf');
    38  drop table t3;
    39  
    40  create table t4(a int, b int, key(c));
    41  
    42  create table t5(a int, b int, unique key(a));
    43  show create table t5;
    44  create index b on t5(b);
    45  show create table t5;
    46  drop index b on t5;
    47  show create table t5;
    48  drop table t5;
    49  
    50  create table t6(a int, b int, unique key(a));
    51  show create table t6;
    52  create index b on t6(a, b);
    53  show create table t6;
    54  drop index b on t6;
    55  show create table t6;
    56  drop table t6;
    57  
    58  create table t7(a int, b int);
    59  create unique index x ON t7(a) comment 'x';
    60  show create table t7;
    61  drop table t7;
    62  
    63  create table t8(a int, b int);
    64  create index x ON t8(a) comment 'x';
    65  show create table t8;
    66  drop table t8;
    67  
    68  create table t9(a int, b int, unique key(a) comment 'a');
    69  show create table t9;
    70  drop table t9;
    71  
    72  create table t10(a int, b int, key(a) comment 'a');
    73  show create table t10;
    74  drop table t10;
    75  
    76  create table t11(a int, b int, unique key(a) comment 'a');
    77  create unique index x ON t11(a) comment 'x';
    78  create index xx ON t11(a) comment 'xx';
    79  show create table t11;
    80  drop table t11;
    81  
    82  create table t12(a text);
    83  create unique index x on t12(a);
    84  create index x2 on t12(a);
    85  drop table t12;
    86  
    87  create table t13(a blob);
    88  create unique index x on t13(a);
    89  create index x2 on t13(a);
    90  drop table t13;
    91  
    92  create table t14(a json);
    93  create unique index x on t14(a);
    94  create index x2 on t14(a);
    95  drop table t14;
    96  
    97  create table t15(
    98  col1 int unsigned,
    99  col2 varchar(15),
   100  col3 varchar(10),
   101  col4 int unsigned,
   102  col5 date,
   103  col6 decimal(7,2),
   104  col7 decimal(7,2),
   105  col8 int unsigned,
   106  unique index(col1,col2,col3,col6)
   107  );
   108  INSERT INTO t15 VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
   109  INSERT INTO t15 VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
   110  INSERT INTO t15 VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
   111  INSERT INTO t15 VALUES (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
   112  INSERT INTO t15 VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
   113  INSERT INTO t15 VALUES (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
   114  INSERT INTO t15 VALUES (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
   115  INSERT INTO t15 VALUES (7788,'SCOTT','ANALYST',7566,'0087-07-13',3000,NULL,20);
   116  INSERT INTO t15 VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
   117  INSERT INTO t15 VALUES (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
   118  INSERT INTO t15 VALUES (7876,'ADAMS','CLERK',7788,'0087-07-13',1100,NULL,20);
   119  INSERT INTO t15 VALUES (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
   120  INSERT INTO t15 VALUES (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
   121  INSERT INTO t15 VALUES (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
   122  
   123  create unique index idx_1 on t15(col1,col2,col3,col6);
   124  select * from t15;
   125  drop table t15;