github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/dml/delete/delete_index_table.sql (about)

     1  drop table if exists dept;
     2  create table dept(
     3  deptno int unsigned COMMENT '部门编号',
     4  dname varchar(15) COMMENT '部门名称',
     5  loc varchar(50)  COMMENT '部门所在位置',
     6  unique key(deptno)
     7  ) COMMENT='部门表';
     8  
     9  INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
    10  INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
    11  INSERT INTO dept VALUES (30,'SALES','CHICAGO');
    12  INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');
    13  
    14  
    15  drop table if exists emp;
    16  create table emp(
    17  empno int unsigned,
    18  ename varchar(15),
    19  job varchar(10),
    20  mgr int unsigned,
    21  hiredate date,
    22  sal decimal(7,2),
    23  comm decimal(7,2),
    24  deptno int unsigned,
    25  unique key(empno),
    26  unique key(ename)
    27  );
    28  
    29  INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
    30  INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
    31  INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
    32  INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
    33  INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
    34  INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
    35  INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
    36  INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,'0087-07-13',3000,NULL,20);
    37  INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
    38  INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
    39  INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,'0087-07-13',1100,NULL,20);
    40  INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
    41  INSERT INTO emp VALUES (7902,'FORD',NULL,7566,'1981-12-03',3000,NULL,20);
    42  INSERT INTO emp VALUES (null,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
    43  
    44  
    45  drop table if exists employees;
    46  create table employees(
    47  empno int unsigned COMMENT '雇员编号',
    48  ename varchar(15) COMMENT '雇员姓名',
    49  job varchar(10) COMMENT '雇员职位',
    50  mgr int unsigned COMMENT '雇员对应的领导的编号',
    51  hiredate date COMMENT '雇员的雇佣日期',
    52  sal decimal(7,2) COMMENT '雇员的基本工资',
    53  comm decimal(7,2) COMMENT '奖金',
    54  deptno int unsigned COMMENT '所在部门',
    55  unique key(empno, ename)
    56  ) COMMENT='雇员表';
    57  
    58  
    59  INSERT INTO employees VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
    60  INSERT INTO employees VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
    61  INSERT INTO employees VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
    62  INSERT INTO employees VALUES (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
    63  INSERT INTO employees VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
    64  INSERT INTO employees VALUES (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
    65  INSERT INTO employees VALUES (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
    66  INSERT INTO employees VALUES (7788,'SCOTT','ANALYST',7566,'0087-07-13',3000,NULL,20);
    67  INSERT INTO employees VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
    68  INSERT INTO employees VALUES (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
    69  INSERT INTO employees VALUES (7876,'ADAMS','CLERK',7788,'0087-07-13',1100,NULL,20);
    70  INSERT INTO employees VALUES (7900,NULL,'CLERK',7698,'1981-12-03',950,NULL,30);
    71  INSERT INTO employees VALUES (7902,'FORD',NULL,7566,'1981-12-03',3000,NULL,20);
    72  INSERT INTO employees VALUES (null,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
    73  
    74  -- Single table deletion test
    75  delete from emp t1 where t1.sal > 2000;
    76  select * from emp;
    77  
    78  -- Compliance with index test
    79  delete from employees where deptno = 10;
    80  select * from employees;
    81  
    82  delete from employees where empno = 7698 and ename = 'BLAKE';
    83  select count(*) from employees where empno = 7698 and ename = 'BLAKE';
    84  
    85  -- Multi table deletion test
    86  delete t1, t2  from emp as t1,dept as t2 where t1.deptno = t2.deptno and t1.deptno = 10;
    87  select * from emp;
    88  select * from dept;
    89  
    90  delete employees, dept from employees, dept where employees.deptno = dept.deptno and sal > 2000;
    91  select * from employees;
    92  select * from dept;
    93  
    94  
    95  truncate table emp;
    96  select * from emp;
    97  
    98  truncate table dept;
    99  select * from dept;
   100  
   101  truncate table employees;
   102  select * from employees;
   103  
   104  
   105  drop table dept;
   106  drop table emp;
   107  drop table employees;