晚风_END

晚风_END

关注
发信
关注(28)粉丝(399)

postgresql|数据库|数据库查询技巧各种练习（建表，数据和查询技巧）

前言：

数据库的发展历史是比较长的，尤其是关系型数据库，具有里程碑性质的关系型数据库大概得是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;
05-26 20:22
Powered by LMLPHP ©2024 晚风_END 0.001528