常见sql实例 - 前端笔记 JSRUN

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

-- 查询每个部门的人数和v部门经理 -- 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;