编辑代码

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);

-- select aa.dept_name,aa.dept_leader,bb.num from Department as aa 
-- right join (select dept_id ,count(id)   as num  from Personnel group by dept_id) as bb 
-- on aa.dept_id=bb.dept_id order by bb.num desc;


-- select a.dept_id,a.name,b.dept_name,b.dept_leader from Personnel as a left join Department as b on a.dept_id=b.dept_id 
-- where a.salary>7000 

-- select a.name,b.dept_name from Personnel as a right join  (
--     select aa.dept_id,bb.dept_name from Personnel as aa inner join Department as bb 
--     on aa.dept_id=bb.dept_id where name in ('张三','李四')
-- ) as b on a.dept_id=b.dept_id order by b.dept_name;