前言:
数据库的发展历史是比较长的,尤其是关系型数据库,具有里程碑性质的关系型数据库大概得是Oracle数据库了,如果没有记错得话,大概是从80年代就有Oracle数据库了
而数据库做为电子信息工程内得基础设施,重要性不言而喻,本文并不希望探讨数据库的具体的发展历史,只是从一个大的时间线来简单说一下数据库的发展历史
最初的事件记录我们应该是使用的账本,当然材质千奇百怪,比如账目记录到羊皮卷上,账目记录到石板上,账目记录到纸张上,现在的电子信息时代,我们可以将账目记录在电脑内,比如,记录在记事本也就是txt文件内,也可以选择记录在专业的办公软件excel,word内,当然了,如果想要更有效率的记录账目,自然是记录在各种各样的关系型数据库,比如,MySQL数据库,postgreslq数据库,Oracle数据库,access数据库等等能够满足自身需求的数据库内,或者非关系型数据库,比如redis,mongodb,elasticsearch等等软件内
🆗,为什么我们不能选择txt文件或者excel文件来在软件工程中记录各式各样的账目呢?
很简单,这些简单的记录载体不能满足我们的各种需求,例如,数据序列化,快速查询,快速记录,各种各样的增删改查,毕竟,打开一个txt,查找到需要的信息,尤其是在信息量十分巨大的情况下,首先就得问问你的电脑的cpu和内存是否支持读取大文件了,即使你将数据存放在了N个小tx文件内,那么,如何管理这些文件,又是一个巨大的挑战。
因此,在软件工程领域,我们必须使用各种各样的数据库,并且能够高效,准确的使用存放在数据库内的数据,对数据做各种增删改查操作
本质上来说,数据库就是一个专门存放各类数据的工具,并且该工具具有管理这些存放其中数据的功能
🆗,本文将就一个比较经典的emp管理系统在postgresql数据库如何创建表,以及对这些表如何查询,如何提高查询技巧做一个简单的介绍
一、
创建emp表,dept表,salgrade表
emp表---员工信息表,主要包括员工编号,员工姓名,员工职务,员工上级,入职日期,员工薪资,员工奖金,部门编号
dept表---部门信息表,也可以简单理解为组织信息表,主要是部门的属性,部门名称,部门所属地,部门编号
salgrade表---薪资等级表,员工信息表的部分属性扩展
说明:
本次案例使用的三个表,是按照数据库的3NF范式标准创建的,什么是数据库的范式就不在这里废话了,感兴趣的自行百度
三个表都有设置主键,emp表有两个外键,查询相关的索引也一并创建了
表字段都是postgresql数据库使用的
总的来说,有外键约束和非空约束,主键约束,主键,这些都是以理想状态创建,其实在生产活动中,这些约束很多是需要自己理解并创建的
-- 如果部门表不存在,则创建部门表(dept),包含部门编号、部门名称及位置信息
CREATE TABLE IF NOT EXISTS dept (
deptno SERIAL PRIMARY KEY, -- 部门编号,自动递增主键
dname VARCHAR(20) NOT NULL, -- 部门名称,非空字段
loc VARCHAR(20) -- 部门位置
);
-- 如果员工表不存在,则创建员工表(emp),包含员工编号、姓名、职务、上级编号、入职日期、薪资等信息
CREATE TABLE IF NOT EXISTS emp (
empno SERIAL PRIMARY KEY, -- 员工编号,自动递增主键
ename VARCHAR(20) NOT NULL, -- 员工姓名,非空字段
job VARCHAR(20), -- 职务
mgr INTEGER REFERENCES emp(empno), -- 上级员工编号,自引用外键
hiredate DATE, -- 入职日期
sal NUMERIC(7, 2), -- 薪资
comm NUMERIC(7, 2), -- 奖金
deptno INTEGER REFERENCES dept(deptno) -- 所属部门编号,外键关联dept表
);
-- 如果薪资等级表不存在,则创建薪资等级表(salgrade),包含等级、最低薪资、最高薪资
CREATE TABLE IF NOT EXISTS salgrade (
grade INTEGER PRIMARY KEY, -- 薪资等级,主键
losal NUMERIC(7, 2), -- 最低薪资
hisal NUMERIC(7, 2) -- 最高薪资
);
-- 插入部门数据
INSERT INTO dept VALUES (10, '战略部', '咸阳');
INSERT INTO dept VALUES (20, '战事部', '武汉');
INSERT INTO dept VALUES (30, '后勤部', '洛阳');
INSERT INTO dept VALUES (40, '外交部', '荆州');
INSERT INTO dept VALUES (50, '战忽局', '新疆');
-- 插入薪资等级数据
INSERT INTO salgrade VALUES (1, 500, 1000);
INSERT INTO salgrade VALUES (2, 1001, 1500);
INSERT INTO salgrade VALUES (3, 1501, 2000);
INSERT INTO salgrade VALUES (4, 2001, 2500);
INSERT INTO salgrade VALUES (5, 2501, 3000);
-- 插入员工数据
INSERT INTO emp VALUES (1, '曹操', '统帅', NULL, '195-01-01'::DATE, 1800.00, 200.00, 10);
INSERT INTO emp VALUES (2, '刘备', '主公', NULL, '184-01-01'::DATE, 2000.00, NULL, 10);
INSERT INTO emp VALUES (3, '关羽', '大将', 1, '190-01-01'::DATE, 1200.00, 500.00, 20);
INSERT INTO emp VALUES (4, '张飞', '大将', 1, '192-01-01'::DATE, 1100.00, 300.00, 20);
INSERT INTO emp VALUES (5, '诸葛亮', '军师', NULL, '207-01-01'::DATE, 1500.00, NULL, 20);
INSERT INTO emp VALUES (6, '赵云', '将领', 1, '191-01-01'::DATE, 1300.00, 400.00, 20);
INSERT INTO emp VALUES (7, '孙权', '君主', NULL, '195-01-01'::DATE, 1900.00, NULL, 30);
INSERT INTO emp VALUES (8, '周瑜', '都督', 7, '198-01-01'::DATE, 1600.00, 200.00, 30);
INSERT INTO emp VALUES (9, '黄盖', '老将', 7, '185-01-01'::DATE, 1400.00, 100.00, 30);
INSERT INTO emp VALUES (10, '司马懿', '谋士', NULL, '179-01-01'::DATE, 1700.00, 150.00, 10);
INSERT INTO emp VALUES (11, '夏侯惇', '将领', 1, '189-01-01'::DATE, 1000.00, 100.00, 10);
INSERT INTO emp VALUES (12, '典韦', '猛将', 1, '186-01-01'::DATE, 900.00, 80.00, 10);
INSERT INTO emp VALUES (13, '庞统', '副军师', NULL, '208-01-01'::DATE, 1400.00, NULL, 20);
INSERT INTO emp VALUES (14, '马超', '将军', 1, '176-01-01'::DATE, 1100.00, 250.00, 20);
INSERT INTO emp VALUES (15, '姜维', '少将', 5, '229-01-01'::DATE, 800.00, 50.00, 20);
INSERT INTO emp VALUES (16, '陆逊', '都督', 7, '203-01-01'::DATE, 1550.00, 150.00, 30);
INSERT INTO emp VALUES (17, '吕蒙', '大都督', 7, '180-01-01'::DATE, 1300.00, 120.00, 30);
INSERT INTO emp VALUES (18, '甘宁', '水军大将', 7, '181-01-01'::DATE, 1200.00, 80.00, 30);
INSERT INTO emp VALUES (19, '徐晃', '大将', 1, '187-01-01'::DATE, 1050.00, 70.00, 10);
INSERT INTO emp VALUES (20, '张辽', '先锋', 1, '183-01-01'::DATE, 1150.00, 90.00, 10);
INSERT INTO emp VALUES (21, '孙尚香', '公主', 7, '199-01-01'::DATE, 800.00, 50.00, 30);
INSERT INTO emp VALUES (22, '貂蝉', '舞姬', 6, '191-06-01'::DATE, 700.00, 30.00, 20);
INSERT INTO emp VALUES (23, '黄忠', '老将', 1, '181-01-01'::DATE, 950.00, 60.00, 20);
INSERT INTO emp VALUES (24, '魏延', '大将', 1, '191-01-01'::DATE, 1000.00, 70.00, 20);
INSERT INTO emp VALUES (25, '关平', '少将', 3, '200-01-01'::DATE, 600.00, 40.00, 20);
INSERT INTO emp VALUES (26, '马岱', '少将', 14, '201-01-01'::DATE, 550.00, 30.00, 20);
INSERT INTO emp VALUES (27, '张郃', '大将', 1, '189-01-01'::DATE, 900.00, 60.00, 10);
INSERT INTO emp VALUES (28, '于禁', '将领', 1, '184-01-01'::DATE, 800.00, 40.00, 10);
INSERT INTO emp VALUES (29, '乐进', '将领', 1, '185-01-01'::DATE, 750.00, 30.00, 10);
INSERT INTO emp VALUES (30, '李典', '将领', 1, '186-01-01'::DATE, 700.00, 20.00, 10);
-- 更多员工数据插入...
-- 创建索引
CREATE INDEX IF NOT EXISTS idx_salgrade_losal ON salgrade (losal); -- 为losal字段创建索引
CREATE INDEX IF NOT EXISTS idx_salgrade_hisal ON salgrade (hisal); -- 为hisal字段创建索引
-- 对于emp表
CREATE INDEX IF NOT EXISTS idx_emp_name ON emp (ename); -- 为ename字段创建索引
CREATE INDEX IF NOT EXISTS idx_emp_dept ON emp (deptno); -- 为deptno字段创建索引
-- 对于dept表
CREATE INDEX IF NOT EXISTS idx_dept_name ON dept (dname); -- 为dname字段创建索引
二、
SQL查询练习
1、两表列合并
查询emp表所有信息,并添加员工所属地信息:
SELECT emp.*,
DEPT.DNAME AS "所属部门"
FROM EMP
left JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO
等价查询:
SELECT *,
DEPT.DNAME AS "所属部门"
FROM EMP
inner JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO
此时,不能使用right连接查询,因为dept.deptno里有冗余数据 40 50两个部门,只可以使用left和inner连接查询,inner关键字可以省略
2、三表列合并
查询emp员工表所有信息并附加每个员工所属地和薪资等级
SELECT
emp.*,
dept.dname AS department_name,
dept.loc AS department_location,
salgrade.grade AS salary_grade
FROM
emp
JOIN
dept ON emp.deptno = dept.deptno
JOIN
salgrade ON emp.sal BETWEEN salgrade.losal AND salgrade.hisal;
3、查询所有员工信息并添加列,列信息为该员工的总薪资
SELECT *,
COALESCE(EMP.SAL + EMP.COMM, EMP.SAL) AS "总薪资"
FROM EMP
注意,这里emp.sal和emp.comm 这两个列字段属性必须一致,都为numeric才可以相加;如果e.comm 也就是奖金为null的时候,只取基本薪资sal的值
4、查询每个部门有多少人,以部门名称,人数形式输出
SELECT d.dname, COUNT(e.empno) AS "部门人数"
FROM dept d
LEFT JOIN emp e ON d.deptno = e.deptno
GROUP BY d.dname;
5、查询出所有领导,并给出每个领导有几名手下,也就是输出形式是领导名,下属人数
SELECT
e.ename AS "领导",
COUNT(es.empno) AS "下属人数"
FROM
emp e
LEFT JOIN
emp es ON e.empno = es.mgr
GROUP BY
e.ename
HAVING
COUNT(es.empno) > 0; -- 这里检查是否有下属,间接意味着是领导
SQL编写思路,首先,确定mgr为空的是领导,因此,先查询出领导,然后,编写分查询,每个领导的下属人数统计,两者合并查询结果就得出最终结果了
实际上不需要直接检查e.mgr
是否为NULL
,因为在我们的左连接查询结构中,领导(即没有上级的员工)自然就是那些没有匹配到子员工的记录。
这里需要注意,比如司马懿,庞统,他们的mgr是空,但他们没有下属,被having后的条件排除了,而mgr是表内外键,因此,是可以左连接过滤出来的,但不能右连接
6、查询出所有领导,并给出每个领导有几名手下,并且该领导属于哪个部门,所属部门的所在地,也就是输出形式是领导名,下属人数,所属部门,部门所在地
这个查询是上一个查询的扩展,这里需要特别注意外键deptno的使用
SELECT
e.ename AS "领导",
COUNT(es.empno) AS "下属人数",
d.dname AS "部门名称",
d.loc AS "部门所在地"
FROM
emp e
LEFT join
dept d on e.deptno=d.deptno
LEFT JOIN
emp es ON e.empno = es.mgr
GROUP BY
e.ename,d.dname,d.loc
HAVING
COUNT(es.empno) > 0
order by "下属人数"; -- 这里检查是否有下属,间接意味着是有下属的领导,当然,如果COUNT(es.empno) = 0,则此人并不一定是领导,但没有下属
7、查询出所有领导,并给出每个领导有几名手下,并且该领导属于哪个部门,所属部门的所在地,也就是输出形式是领导名,下属人数,所属部门,部门所在地,以及该名领导的薪资等级
这个查询仍然是上一个查询的扩展,编写思路基本和上面一致,只是要记得group by 分组后面要添加字段
SELECT
e.ename AS "领导",
COUNT(es.empno) AS "下属人数",
d.dname AS "部门名称",
d.loc AS "部门所在地",
s.grade AS "薪资等级",
COALESCE(e.SAL + e.COMM, e.SAL) AS "总薪资"
FROM
emp e
LEFT join
salgrade s ON e.sal BETWEEN s.losal AND s.hisal
right join
dept d on e.deptno=d.deptno
LEFT JOIN
emp es ON e.empno = es.mgr
GROUP BY
e.ename,d.dname,d.loc,COALESCE(e.SAL + e.COMM, e.SAL),s.grade
HAVING
COUNT(es.empno) > 0
order by "下属人数"; -- 这里检查是否有下属,间接意味着是有下属的领导,当然,如果COUNT(es.empno) = 0,则此人并不一定是领导,但没有下属
8、查询拥有最多手下的人员信息,该人员的姓名,所属部门,所属部门归属地,有几名下属
WITH SubordinateCounts AS (
SELECT
mgr AS boss_empno,
COUNT(*) AS subordinate_count
FROM
emp
WHERE
mgr IS NOT NULL
GROUP BY
mgr
)
SELECT
E.ename AS "领导姓名",
D.dname AS "领导所属部门名称",
D.loc AS "部门所在地",
SC.subordinate_count AS "下属人数"
FROM
SubordinateCounts SC
JOIN
emp E ON E.empno = SC.boss_empno
JOIN
dept D ON E.deptno = D.deptno
ORDER BY
SC.subordinate_count DESC
LIMIT 1;
9、查询员工里薪资最高的,输出形式为员工名称,员工职位,员工基础薪资
SELECT emp.ename,emp.job,emp.sal FROM emp ORDER by emp.sal DESC LIMIT 1
10、查询员工薪资里排名前三的,输出形式为员工名称,员工职位,员工基础薪资,前三名都输出
此查询是上一条SQL查询的扩展
这个CTE中使用了DENSE_RANK()
窗口函数根据薪资从高到低对所有员工进行排名。DENSE_RANK()
会为相同薪资的员工分配相同的排名,但不会跳过任何一个排名(与ROW_NUMBER()
不同,后者会给相同薪资的员工分配不同的排名)。然后,从这个CTE中选择排名前三(即薪资排名小于等于3)的员工的名称、职位和基础薪资。
使用DENSE_RANK()
函数对员工的薪资进行了降序排名,然后在外部查询中通过WHERE "薪资排名" < 4
来筛选结果。这意味着查询会返回所有薪资排名在前四名的员工,注意这里的"小于4"实际上是包含排名为1、2、3的员工,因为排名是始于1的。
换句话说,当你设置"薪资排名" < 4
时,你正在请求所有薪资排名前三的员工信息,因为排名最高的员工是第1名,接下来是第2名和第3名,而到了第4名时,就不满足条件了(因为4不小于4)。因此,这个条件正是用来选取薪资排名前三的员工的正确方式。"薪资排名" = 1的时候自然就是薪资第一名了,这个时候该SQL等价于上面的第九个SQL语句
这里的子查询也就是关键查询是DENSE_RANK() OVER (ORDER BY sal DESC) AS "薪资排名"
WITH RankedEmployees AS (
SELECT
ename AS "员工名称",
job AS "员工职位",
sal AS "基础薪资",
DENSE_RANK() OVER (ORDER BY sal DESC) AS "薪资排名"
FROM
emp
)
SELECT
"员工名称",
"员工职位",
"基础薪资"
FROM
RankedEmployees
WHERE
"薪资排名" < 4;
11、查询有最多员工的领导,输出形式为领导姓名,领导id,下属人数
WITH MgrSubCounts AS (
SELECT
emp.mgr AS "领导id",
COUNT(*) AS "下属人数"
FROM
emp
WHERE
mgr IS NOT NULL
GROUP BY
mgr
)
SELECT
E.ename AS "领导姓名",
E.empno AS "领导id",
MSC."下属人数"
FROM
MgrSubCounts MSC
JOIN
emp E ON E.empno = MSC."领导id"
WHERE
MSC."下属人数" = (
SELECT
MAX("下属人数")
FROM
MgrSubCounts
);
WITH ManagerCounts AS (
SELECT
mgr AS manager_id,
COUNT(*) AS sub_count
FROM
emp
WHERE
mgr IS NOT NULL
GROUP BY
mgr
),
MaxCount AS (
SELECT
MAX(sub_count) AS max_subs
FROM
ManagerCounts
)
SELECT
E.ename AS "领导姓名",
MC.sub_count AS "下属人数"
FROM
ManagerCounts MC
JOIN
emp E ON E.empno = MC.manager_id
CROSS JOIN MaxCount
WHERE
MC.sub_count = MaxCount.max_subs;
12、查询有最多员工的领导,输出形式为领导姓名,领导id,下属人数,该领导所在部门名称,该部门所在地
WITH MgrSubCounts AS (
SELECT
emp.mgr AS "领导id",
COUNT(*) AS "下属人数"
FROM
emp
WHERE
mgr IS NOT NULL
GROUP BY
mgr
),
DeptInfo AS (
SELECT
d.deptno,
d.dname AS "部门名称",
d.loc AS "部门所在地"
FROM
dept d
)
SELECT
E.ename AS "领导姓名",
E.empno AS "领导id",
MSC."下属人数",
DI."部门名称",
DI."部门所在地"
FROM
MgrSubCounts MSC
JOIN
emp E ON E.empno = MSC."领导id"
JOIN
DeptInfo DI ON E.deptno = DI.deptno -- 假设emp表中有deptno字段关联dept表
WHERE
MSC."下属人数" = (
SELECT
MAX("下属人数")
FROM
MgrSubCounts
);
也可以使用窗口函数,SQL如下:
WITH MgrSubCounts AS (
SELECT
emp.mgr AS "领导id",
COUNT(*) AS "下属人数",
DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) AS Rank
FROM
emp
WHERE
mgr IS NOT NULL
GROUP BY
mgr
),
DeptInfo AS (
SELECT
d.deptno,
d.dname AS "部门名称",
d.loc AS "部门所在地"
FROM
dept d
)
SELECT
E.ename AS "领导姓名",
E.empno AS "领导id",
MSC."下属人数",
DI."部门名称",
DI."部门所在地"
FROM
MgrSubCounts MSC
JOIN
emp E ON E.empno = MSC."领导id"
JOIN
DeptInfo DI ON E.deptno = DI.deptno
WHERE
MSC.Rank = 1;
13、查询第二多下属人数的领导名称,领导编号,下属人数,领导所属部门,部门所属地
WITH MgrSubCounts AS (
SELECT
emp.mgr AS "领导id",
COUNT(*) AS "下属人数",
DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) AS Rank
FROM
emp
WHERE
mgr IS NOT NULL
GROUP BY
mgr
),
DeptInfo AS (
SELECT
d.deptno,
d.dname AS "部门名称",
d.loc AS "部门所在地"
FROM
dept d
)
SELECT
E.ename AS "领导姓名",
E.empno AS "领导id",
MSC."下属人数",
DI."部门名称",
DI."部门所在地"
FROM
MgrSubCounts MSC
JOIN
emp E ON E.empno = MSC."领导id"
JOIN
DeptInfo DI ON E.deptno = DI.deptno
WHERE
MSC.Rank = 2;
14、查询第二多下属员工的领导,输出为领导名称,领导id,下属员工数量,该领导的总薪资,总薪资也就是包括奖金,该领导所属部门,该部门所在地(如果,奖金为零,则总薪资就是基础薪资)
这个SQL比较灵活,比如,MSC.Rank < 3;将是查询前两名的信息,MSC.Rank = 1 则是第一名的信息
CTE里包含了DeptInfo,这里给SQL语句加入了扩展功能,关键的还是在窗口函数和emp表的外键
WITH MgrSubCounts AS (
SELECT
emp.mgr AS "领导id",
COUNT(*) AS "下属人数",
DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) AS Rank
FROM
emp
WHERE
mgr IS NOT NULL
GROUP BY
mgr
),
DeptInfo AS (
SELECT
d.deptno,
d.dname AS "部门名称",
d.loc AS "部门所在地"
FROM
dept d
)
SELECT
E.ename AS "领导姓名",
E.empno AS "领导id",
MSC."下属人数",
E.sal AS "领导薪资",
COALESCE(E.sal+E.comm,E.sal) AS "领导总薪资",
DI."部门名称",
DI."部门所在地"
FROM
MgrSubCounts MSC
JOIN
emp E ON E.empno = MSC."领导id"
JOIN
DeptInfo DI ON E.deptno = DI.deptno
WHERE
MSC.Rank = 2;