CREATE DATABASE IF NOT EXISTS db_jdbc CHARACTER SET utf8mb4;

USE db_jdbc;

DROP TABLE IF EXISTS t_emp;

CREATE TABLE t_emp
(
    emp_id   INT AUTO_INCREMENT
        PRIMARY KEY,
    emp_name VARCHAR(20)                 NOT NULL,
    sex      ENUM ('M', 'F') DEFAULT 'M' NULL,
    email    VARCHAR(50)                 NULL,
    prov     VARCHAR(100)                NULL,
    age      INT                         NULL,
    salary   INT                         NULL,
    dept_id  INT                         NULL
);

INSERT INTO t_emp (emp_id, emp_name, sex, email, prov, age, salary, dept_id) VALUES (1, '李三', 'M', 'lisan@emp.com', '江苏', 32, 18000, 1);
INSERT INTO t_emp (emp_id, emp_name, sex, email, prov, age, salary, dept_id) VALUES (2, '王丽', 'F', 'wangli@emp.com', '山东', 25, 9000, 1);
INSERT INTO t_emp (emp_id, emp_name, sex, email, prov, age, salary, dept_id) VALUES (3, '王强', 'M', 'wangqiang@emp.com', '北京', 42, 12000, 2);
INSERT INTO t_emp (emp_id, emp_name, sex, email, prov, age, salary, dept_id) VALUES (4, '何马', 'M', 'hema@emp.com', '江苏', 32, 8000, 2);
INSERT INTO t_emp (emp_id, emp_name, sex, email, prov, age, salary, dept_id) VALUES (5, '赵四海', 'M', 'zhaosihai@emp.com', '河北', 32, 17000, 3);
INSERT INTO t_emp (emp_id, emp_name, sex, email, prov, age, salary, dept_id) VALUES (6, '程兴', 'M', 'chenxing@emp.com', '武汉', 32, 16000, 3);
INSERT INTO t_emp (emp_id, emp_name, sex, email, prov, age, salary, dept_id) VALUES (7, '孙月', 'F', 'sunyue@emp.com', '山东', 22, 10000, 4);
INSERT INTO t_emp (emp_id, emp_name, sex, email, prov, age, salary, dept_id) VALUES (8, '钱金', 'M', 'qianjin@emp.com', '北京', 52, 28000, 4);
INSERT INTO t_emp (emp_id, emp_name, sex, email, prov, age, salary, dept_id) VALUES (9, '刘红', 'F', 'liuhong@emp.com', '安徽', 22, 8000, 5);
INSERT INTO t_emp (emp_id, emp_name, sex, email, prov, age, salary, dept_id) VALUES (10, '蒋一心', 'F', 'jinagyixin@emp.com', '山西', 28, 9000, 5);
INSERT INTO t_emp (emp_id, emp_name, sex, email, prov, age, salary, dept_id) VALUES (11, '刘红', 'F', 'liuhong@emp.com', '河南', 22, 8000, 6);
INSERT INTO t_emp (emp_id, emp_name, sex, email, prov, age, salary, dept_id) VALUES (12, '蒋一心', 'F', 'jinagyixin@emp.com', null, 28, 9000, 6);
INSERT INTO t_emp (emp_id, emp_name, sex, email, prov, age, salary, dept_id) VALUES (13, '马文博', 'M', 'mawenbo@emp.com', '河南', 22, 28000, 7);
INSERT INTO t_emp (emp_id, emp_name, sex, email, prov, age, salary, dept_id) VALUES (14, '成虎', 'M', 'chenhu@emp.com', '新疆', 38, 36000, 7);

DROP TABLE IF EXISTS t_dept;

CREATE TABLE t_dept
(
    dept_id   INT AUTO_INCREMENT
        PRIMARY KEY,
    dept_name VARCHAR(50) NULL
);

INSERT INTO t_dept (dept_id, dept_name) VALUES (1, '人事部');
INSERT INTO t_dept (dept_id, dept_name) VALUES (2, '产品部');
INSERT INTO t_dept (dept_id, dept_name) VALUES (3, '技术部');
INSERT INTO t_dept (dept_id, dept_name) VALUES (4, '运维部');
INSERT INTO t_dept (dept_id, dept_name) VALUES (5, '财务部');
INSERT INTO t_dept (dept_id, dept_name) VALUES (6, '采购部');
INSERT INTO t_dept (dept_id, dept_name) VALUES (7, '研发部');

DROP TABLE IF EXISTS t_account;

CREATE TABLE t_account (
    id INTEGER PRIMARY KEY AUTO_INCREMENT,
    account VARCHAR(20) UNIQUE,
    balance INTEGER UNSIGNED
);

INSERT INTO t_account VALUES (NULL, '8859-1', 2000), (NULL, '8859-2', 2000);