github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/dml/delete/delete_index_table.result (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 INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK'); 9 INSERT INTO dept VALUES (20,'RESEARCH','DALLAS'); 10 INSERT INTO dept VALUES (30,'SALES','CHICAGO'); 11 INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON'); 12 drop table if exists emp; 13 create table emp( 14 empno int unsigned, 15 ename varchar(15), 16 job varchar(10), 17 mgr int unsigned, 18 hiredate date, 19 sal decimal(7,2), 20 comm decimal(7,2), 21 deptno int unsigned, 22 unique key(empno), 23 unique key(ename) 24 ); 25 INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20); 26 INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30); 27 INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30); 28 INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20); 29 INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30); 30 INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30); 31 INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10); 32 INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,'0087-07-13',3000,NULL,20); 33 INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10); 34 INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30); 35 INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,'0087-07-13',1100,NULL,20); 36 INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30); 37 INSERT INTO emp VALUES (7902,'FORD',NULL,7566,'1981-12-03',3000,NULL,20); 38 INSERT INTO emp VALUES (null,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10); 39 drop table if exists employees; 40 create table employees( 41 empno int unsigned COMMENT '雇员编号', 42 ename varchar(15) COMMENT '雇员姓名', 43 job varchar(10) COMMENT '雇员职位', 44 mgr int unsigned COMMENT '雇员对应的领导的编号', 45 hiredate date COMMENT '雇员的雇佣日期', 46 sal decimal(7,2) COMMENT '雇员的基本工资', 47 comm decimal(7,2) COMMENT '奖金', 48 deptno int unsigned COMMENT '所在部门', 49 unique key(empno, ename) 50 ) COMMENT='雇员表'; 51 INSERT INTO employees VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20); 52 INSERT INTO employees VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30); 53 INSERT INTO employees VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30); 54 INSERT INTO employees VALUES (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20); 55 INSERT INTO employees VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30); 56 INSERT INTO employees VALUES (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30); 57 INSERT INTO employees VALUES (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10); 58 INSERT INTO employees VALUES (7788,'SCOTT','ANALYST',7566,'0087-07-13',3000,NULL,20); 59 INSERT INTO employees VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10); 60 INSERT INTO employees VALUES (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30); 61 INSERT INTO employees VALUES (7876,'ADAMS','CLERK',7788,'0087-07-13',1100,NULL,20); 62 INSERT INTO employees VALUES (7900,NULL,'CLERK',7698,'1981-12-03',950,NULL,30); 63 INSERT INTO employees VALUES (7902,'FORD',NULL,7566,'1981-12-03',3000,NULL,20); 64 INSERT INTO employees VALUES (null,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10); 65 delete from emp t1 where t1.sal > 2000; 66 select * from emp; 67 empno ename job mgr hiredate sal comm deptno 68 7369 SMITH CLERK 7902 1980-12-17 800.00 null 20 69 7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30 70 7521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30 71 7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30 72 7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30 73 7876 ADAMS CLERK 7788 0087-07-13 1100.00 null 20 74 7900 JAMES CLERK 7698 1981-12-03 950.00 null 30 75 null MILLER CLERK 7782 1982-01-23 1300.00 null 10 76 delete from employees where deptno = 10; 77 select * from employees; 78 empno ename job mgr hiredate sal comm deptno 79 7369 SMITH CLERK 7902 1980-12-17 800.00 null 20 80 7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30 81 7521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30 82 7566 JONES MANAGER 7839 1981-04-02 2975.00 null 20 83 7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30 84 7698 BLAKE MANAGER 7839 1981-05-01 2850.00 null 30 85 7788 SCOTT ANALYST 7566 0087-07-13 3000.00 null 20 86 7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30 87 7876 ADAMS CLERK 7788 0087-07-13 1100.00 null 20 88 7900 null CLERK 7698 1981-12-03 950.00 null 30 89 7902 FORD null 7566 1981-12-03 3000.00 null 20 90 delete from employees where empno = 7698 and ename = 'BLAKE'; 91 select count(*) from employees where empno = 7698 and ename = 'BLAKE'; 92 count(*) 93 0 94 delete t1, t2 from emp as t1,dept as t2 where t1.deptno = t2.deptno and t1.deptno = 10; 95 select * from emp; 96 empno ename job mgr hiredate sal comm deptno 97 7369 SMITH CLERK 7902 1980-12-17 800.00 null 20 98 7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30 99 7521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30 100 7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30 101 7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30 102 7876 ADAMS CLERK 7788 0087-07-13 1100.00 null 20 103 7900 JAMES CLERK 7698 1981-12-03 950.00 null 30 104 select * from dept; 105 deptno dname loc 106 20 RESEARCH DALLAS 107 30 SALES CHICAGO 108 40 OPERATIONS BOSTON 109 delete employees, dept from employees, dept where employees.deptno = dept.deptno and sal > 2000; 110 select * from employees; 111 empno ename job mgr hiredate sal comm deptno 112 7369 SMITH CLERK 7902 1980-12-17 800.00 null 20 113 7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30 114 7521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30 115 7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30 116 7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30 117 7876 ADAMS CLERK 7788 0087-07-13 1100.00 null 20 118 7900 null CLERK 7698 1981-12-03 950.00 null 30 119 select * from dept; 120 deptno dname loc 121 30 SALES CHICAGO 122 40 OPERATIONS BOSTON 123 truncate table emp; 124 select * from emp; 125 empno ename job mgr hiredate sal comm deptno 126 truncate table dept; 127 select * from dept; 128 deptno dname loc 129 truncate table employees; 130 select * from employees; 131 empno ename job mgr hiredate sal comm deptno 132 drop table dept; 133 drop table emp; 134 drop table employees;