CREATE DATABASE test;
use test;
CREATE TABLE Department (
dept_id VARCHAR(2) NOT NULL,
dept_name VARCHAR(20) NOT NULL,
dept_leader VARCHAR(10),
PRIMARY KEY (dept_id)
);
CREATE TABLE Personnel (
id VARCHAR(4) NOT NULL,
name VARCHAR(10) NOT NULL,
dept_id VARCHAR(2) NOT NULL,
age INTEGER,
gzsj DATE,
technical_post VARCHAR(10),
salary INTEGER,
PRIMARY KEY (id),
FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
);
INSERT INTO Department (dept_id, dept_name, dept_leader) VALUES ('01', '人事部', '张三');
INSERT INTO Department (dept_id, dept_name, dept_leader) VALUES ('02', '财务部', '李四');
INSERT INTO Department (dept_id, dept_name, dept_leader) VALUES ('03', '市场部', '王五');
INSERT INTO Department (dept_id, dept_name, dept_leader) VALUES ('04', '研发部', '赵六');
INSERT INTO Department (dept_id, dept_name, dept_leader) VALUES ('05', '客服部', '刘七');
INSERT INTO Personnel (id, name, dept_id, age, gzsj, technical_post, salary)
VALUES
('1001', '张三', '01', 25, '2022-01-01', '高级工程师', 8000),
('1002', '李四', '02', 28, '2021-03-01', 'IT工程师', 6000),
('1003', '王五', '03', 30, '2019-05-01', '项目经理', 12000),
('1004', '赵六', '04', 33, '2018-07-01', '产品经理', 15000),
('1005', '刘七', '05', 26, '2020-02-01', '客服专员', 4000),
('1006', '小明', '01', 27, '2021-06-01', '助理工程师', 5000),
('1007', '小红', '02', 26, '2019-08-01', '财务主管', 10000),
('1008', '小强', '03', 23, '2020-12-01', '市场专员', 3500),
('1009', '小芳', '04', 34, '2017-09-01', '架构师', 18000),
('1010', '小李', '05', 29, '2018-11-01', '客服经理', 8000),
('1011', '张三丰', '01', 31, '2016-01-01', '高级工程师', 9000),
('1012', '李白', '02', 24, '2021-02-01', '财务会计', 7000),
('1013', '王大陆', '03', 27, '2019-07-01', '市场总监', 20000),
('1014', '赵四', '04', 30, '2018-06-01', '产品经理', 14000),
('1015', '刘能', '05', 32, '2017-08-01', '客服主管', 6000),
('1016', '小红帽', '01', 29, '2020-10-01', '工程师', 7000),
('1017', '小鸟', '02', 22, '2022-01-01', '财务专员', 4000),
('1018', '小妹', '03', 25, '2021-04-01', '市场助理', 3000),
('1019', '李华', '04', 28, '2020-05-01', '产品设计师', 10000),
('1020', '张大帅', '05', 33, '2019-09-01', '客服专家', 12000),
('1021', '张大', '05', 33, '2019-09-01', '客服专家', 12000);