mysql模式样例表

mysql模式仅支持单机HEAP表，样例表如下：

use sales;

DROP TABLE IF EXISTS finance_info;
DROP TABLE IF EXISTS sales_info;
DROP TABLE IF EXISTS sales_info_range;
DROP TABLE IF EXISTS sales_info_list;
DROP TABLE IF EXISTS sales_info_hash;
DROP TABLE IF EXISTS orders_info;
DROP TABLE IF EXISTS product;
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS department;
DROP TABLE IF EXISTS branches;
DROP TABLE IF EXISTS area; 
DROP TABLE IF EXISTS area1;
DROP TABLE IF EXISTS branches1;
  
--区域信息表
CREATE TABLE area
(area_no CHAR(2) NOT NULL PRIMARY KEY,
 area_name VARCHAR2(60),
 DHQ VARCHAR2(20) DEFAULT 'ShenZhen' NOT NULL);
INSERT INTO area VALUES ('01','EastChina','Shanghai');
INSERT INTO area VALUES ('02','WestChina','Chengdu');
INSERT INTO area VALUES ('03','SouthChina','Guangzhou');
INSERT INTO area VALUES ('04','NorthChina','Beijing');
INSERT INTO area VALUES ('05','CentralChina','Wuhan');
  
--机构信息表
CREATE TABLE branches (
    branch_no CHAR(4) PRIMARY KEY,
    branch_name VARCHAR(200) NOT NULL,
    area_no CHAR(2),
    address VARCHAR(200),
    CONSTRAINT c_branches_1 FOREIGN KEY (area_no) REFERENCES area(area_no) ON DELETE SET NULL
);
INSERT INTO branches VALUES ('0001','深圳','03','');
INSERT INTO branches VALUES ('0101','Shanghai','01','Jingan District, Shanghai');
INSERT INTO branches VALUES ('0102','Nanjing','01','City of Nanjing');
INSERT INTO branches VALUES ('0103','Fuzhou','01','');
INSERT INTO branches VALUES ('0104','Xiamen','01','Xiamen');
INSERT INTO branches VALUES ('0401','Beijing','04','');
INSERT INTO branches VALUES ('0402','Tianjin','04','');
INSERT INTO branches VALUES ('0403','Dalian','04','Dalian City');
INSERT INTO branches VALUES ('0404','Shenyang','04','');
INSERT INTO branches VALUES ('0201','Chengdu','02','');
INSERT INTO branches VALUES ('0501','Wuhan','05','');
INSERT INTO branches VALUES ('0502','Changsha','05','');
  
--部门信息表
CREATE TABLE department
(deparment_no CHAR(3) PRIMARY KEY,
 department_name VARCHAR2(20));
INSERT INTO department VALUES('000','Public Dep');
INSERT INTO department VALUES('010','Sales Dep');
INSERT INTO department VALUES('008','Purchasing Dep');
INSERT INTO department VALUES('002','Finance Dep');
  
--员工信息表
CREATE TABLE employees
(branch CHAR(4) ,
 department CHAR(3) ,
 employee_no CHAR(10) NOT NULL PRIMARY KEY,
 employee_name VARCHAR(10),
 sex CHAR(1),
 entry_date DATE,
 CONSTRAINT c_employees_1 FOREIGN KEY(branch) REFERENCES branches(branch_no) ON DELETE SET NULL,
 CONSTRAINT c_employees_2 FOREIGN KEY(department) REFERENCES department(deparment_no) ON DELETE SET NULL
 );
INSERT INTO employees VALUES ('0101','000','0101000001','Mask','1',SYSDATE-100);
INSERT INTO employees VALUES ('0101','000','0101000002','John','1',SYSDATE-200);
INSERT INTO employees VALUES ('0201','010','0201010011','Anna','0',SYSDATE-300);
INSERT INTO employees VALUES ('0201','008','0201008003','Jack','1',SYSDATE-300);
INSERT INTO employees VALUES ('0101','008','0201008004','Jim','1',SYSDATE-200);
  
--产品信息表
CREATE TABLE product
(product_no CHAR(5) PRIMARY KEY,
 product_name VARCHAR2(30),
 cost NUMBER,
 price NUMBER
);
INSERT INTO product VALUES ('11001','product001',8,10);
INSERT INTO product VALUES ('11002','product002',13,16);
INSERT INTO product VALUES ('10001','product001',99,100);
INSERT INTO product VALUES ('10002','product002',199,200);
  
--订单信息表
CREATE TABLE orders_info (
    order_no CHAR(14) NOT NULL,
    product_no CHAR(5),
    area CHAR(2),
    branch CHAR(4),
    order_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
    salesperson CHAR(10),
    id INT,
    FOREIGN KEY (product_no) REFERENCES product(product_no) ON DELETE SET NULL,
    FOREIGN KEY (area) REFERENCES area(area_no) ON DELETE SET NULL,
    FOREIGN KEY (branch) REFERENCES branches(branch_no) ON DELETE SET NULL,
    FOREIGN KEY (salesperson) REFERENCES employees(employee_no) ON DELETE SET NULL
)
    PARTITION BY RANGE (id)
    (PARTITION p_orders_info_1 VALUES LESS THAN (800),
     PARTITION p_orders_info_2 VALUES LESS THAN MAXVALUE);
INSERT INTO orders_info VALUES ('20010102020001','11001','01','0101',SYSDATE-400,'0201010011',300);
INSERT INTO orders_info VALUES ('20010102020001','11002','02','0201',SYSDATE-400,'0201008003',1300);
INSERT INTO orders_info VALUES ('20010102020001','10001','04','0402',SYSDATE-400,'0201010011',2300);
INSERT INTO orders_info VALUES ('20210102020002','11001','04','0401',SYSDATE-400,'0201008003',400);
INSERT INTO orders_info VALUES ('20210102020002','11002','05','0501',SYSDATE-400,'0201010011',200);
INSERT INTO orders_info VALUES ('20210102020002','10001','01','0102',SYSDATE-400,'0201008003',100);
  
--销售信息表
CREATE TABLE sales_info
(
    year CHAR(4) NOT NULL,
    month CHAR(2) NOT NULL,
    branch CHAR(4),
    product CHAR(5),
    quantity INT DEFAULT 0 NOT NULL,
    amount DECIMAL(10, 2) DEFAULT 0 NOT NULL,
    salesperson CHAR(10),
    FOREIGN KEY (branch) REFERENCES branches(branch_no) ON DELETE SET NULL,
    FOREIGN KEY (product) REFERENCES product(product_no) ON DELETE SET NULL
)
PARTITION BY HASH(branch)
(PARTITION p_sales_info_1,PARTITION p_sales_info_2,PARTITION p_sales_info_3);

CREATE INDEX idx_sales_info_1 ON sales_info (year,month,branch,product) LOCAL;

INSERT INTO sales_info VALUES ('2001','01','0201','11001',30,500,'0201010011');
INSERT INTO sales_info VALUES ('2015','11','0101','11001',20,300,'');
INSERT INTO sales_info VALUES ('2021','10','0402','11001',20,300,'');
INSERT INTO sales_info VALUES ('2000','12','0102','11001',20,300,'');
INSERT INTO sales_info VALUES ('2015','03','0102','11001',20,300,'');
INSERT INTO sales_info VALUES ('2021','05','0101','11001',40,600,'');
  
--范围分区的销售信息表
CREATE TABLE sales_info_range
(year CHAR(4) NOT NULL,
 month CHAR(2) NOT NULL,
 branch CHAR(4),
 product CHAR(5),
 quantity DECIMAL(18,6) DEFAULT 0 NOT NULL,
 amount DECIMAL(10,2) DEFAULT 0 NOT NULL,
 salsperson CHAR(10))
PARTITION BY RANGE(year)
(PARTITION p_sales_info_range_1 VALUES LESS THAN('2011'),
 PARTITION p_sales_info_range_2 VALUES LESS THAN('2021'),
 PARTITION p_sales_info_range_3 VALUES LESS THAN('2031'));
INSERT INTO sales_info_range VALUES ('2001','01','0201','11001',30,500,'0201010011');
INSERT INTO sales_info_range VALUES ('2015','11','0101','11001',20,300,'');
INSERT INTO sales_info_range VALUES ('2021','10','0101','11001',20,300,'');
INSERT INTO sales_info_range VALUES ('2000','12','0102','11001',20,300,'');
INSERT INTO sales_info_range VALUES ('2015','03','0102','11001',20,300,'');
INSERT INTO sales_info_range VALUES ('2021','05','0101','11001',40,600,'');
  
--列表分区的销售信息表
CREATE TABLE sales_info_list
(year CHAR(4) NOT NULL,
 month CHAR(2) NOT NULL,
 branch CHAR(4),
 product CHAR(5),
 quantity DECIMAL(18,6) DEFAULT 0 NOT NULL,
 amount DECIMAL(10,2) DEFAULT 0 NOT NULL,
 salsperson CHAR(10))
PARTITION BY LIST(year)
(PARTITION p_sales_info_list_1 VALUES IN ('2018','2019'),
 PARTITION p_sales_info_list_2 VALUES IN ('2021'));
INSERT INTO sales_info_list VALUES ('2018','10','0101','11001',20,300,'');
INSERT INTO sales_info_list VALUES ('2021','05','0101','11001',40,600,'');
  
--哈希分区的销售信息表
CREATE TABLE sales_info_hash
(year CHAR(4) NOT NULL,
 month CHAR(2) NOT NULL,
 branch CHAR(4),
 product CHAR(5),
 quantity DECIMAL(18,6) DEFAULT 0 NOT NULL,
 amount DECIMAL(10,2) DEFAULT 0 NOT NULL,
 salsperson CHAR(10))
PARTITION BY HASH(year)
(PARTITION p_sales_info_hash_1,
 PARTITION p_sales_info_hash_2);
  
--财务信息表
CREATE TABLE finance_info
(year CHAR(4) NOT NULL,
 month CHAR(2) NOT NULL,
 branch CHAR(4) ,
 revenue_total NUMBER(10,2),
 cost_total NUMBER(10,2),
 fee_total NUMBER(10,2),
 CONSTRAINT c_finance_info_1 FOREIGN KEY(branch) REFERENCES branches(branch_no) ON DELETE SET NULL
);
CREATE INDEX idx_finance_info_1 ON finance_info (year,month,branch);
INSERT INTO finance_info VALUES ('2001','01','0201',2888,2000,300);
INSERT INTO finance_info VALUES ('2021','01','0201',28888,24000,3000);
INSERT INTO finance_info VALUES ('2021','01','0101',38888,34000,4000);
INSERT INTO finance_info VALUES ('2021','02','0101',37778,33000,6000);

--在聚集函数描述中经常使用的表area1
CREATE TABLE area1
(area_no CHAR(2) NOT NULL PRIMARY KEY,
 area_name VARCHAR2(60),
 DHQ VARCHAR2(20) DEFAULT 'ShenZhen' NOT NULL,
 employee_count INT);
INSERT INTO area1 VALUES ('01','EastChina','Shanghai',100);
INSERT INTO area1 VALUES ('02','WestChina','Chengdu',300);
INSERT INTO area1 VALUES ('03','SouthChina','Guangzhou',400);
INSERT INTO area1 VALUES ('04','NorthChina','Beijing',300);
INSERT INTO area1 VALUES ('05','CentralChina','Wuhan',250);
  
--在聚集函数描述中经常使用的表branches1
CREATE TABLE branches1
(branch_no CHAR(4) PRIMARY KEY,
 branch_name VARCHAR2(200) NOT NULL,
 area_no CHAR(2),
 address VARCHAR2(200),
 employee_count INT);
INSERT INTO branches1 VALUES ('0001','深圳','','',40);
INSERT INTO branches1 VALUES ('0102','Nanjing','01','City of Nanjing',70);
INSERT INTO branches1 VALUES ('0103','Fuzhou','01','',12);
INSERT INTO branches1 VALUES ('0104','Xiamen','01','Xiamen',28);
INSERT INTO branches1 VALUES ('0401','Beijing','04','',5);
INSERT INTO branches1 VALUES ('0402','Tianjin','04','',10);
INSERT INTO branches1 VALUES ('0403','Dalian','04','Dalian City',30);
INSERT INTO branches1 VALUES ('0404','Shenyang','04','',6);
INSERT INTO branches1 VALUES ('0201','Chengdu','02','',19);
INSERT INTO branches1 VALUES ('0501','Wuhan','','',30);
INSERT INTO branches1 VALUES ('0502','Changsha','05','',40);

COMMIT;