第二天 - 句子，订购，子查询，汇总函数，在数据库中组。（第二天.数据库中.句子.函数.汇总...）

员工信息表:

empid | empname |    designation    |   dept    | salary 
-------+---------+-------------------+-----------+--------
    11 | lakshmi | 软件工程师       | IT        |  50000
    12 | guru    | 经理             | HR        |  40000
    13 | pritha  | 经理             | IT        |  70000
    14 | gokul   | 团队主管         | Sales     |  30000
    15 | raja    | HR                | Marketing |  65000
    16 | rani    | HR                | Sales     |  45000
(6 rows)

表名修改：

ALTER TABLE employee RENAME TO employees;

员工表详细信息：

d employees

向员工表添加多行：

INSERT INTO employees VALUES(105, 'bala', '团队主管', 'AI', 100000), (106, 'kani', '经理', 'CS', 150000);

员工表数据：

SELECT * FROM employees;

所有部门的员工：

SELECT dept FROM employees;

唯一部门：SELECT DISTINCT dept FROM employees; 确保只返回唯一部门值。

WHERE子句：PostgreSQL 的 WHERE 子句用于基于特定条件过滤记录，仅检索满足指定标准的行。语法：SELECT column1, column2 FROM table_name WHERE condition;

IT 部门的员工：

SELECT * FROM employees WHERE dept = 'IT';

IT 部门且职位为“软件工程师”的员工：

SELECT * FROM employees WHERE dept = 'IT' AND designation = '软件工程师';

将职位列重命名为“职位”：

SELECT designation AS 职位 FROM employees;

非IT部门的员工：

SELECT * FROM employees WHERE dept <> 'IT';

薪水大于50000的员工：

SELECT * FROM employees WHERE salary > 50000;

薪水大于等于50000的员工：

SELECT * FROM employees WHERE salary >= 50000;

薪水小于等于50000的员工：

SELECT * FROM employees WHERE salary <= 50000;

薪水在40000到100000之间的员工：

SELECT * FROM employees WHERE salary BETWEEN 40000 AND 100000;

职位为“团队主管”或“经理”的员工：

SELECT * FROM employees WHERE designation IN ('团队主管', '经理');

职位既不是“团队主管”也不是“经理”的员工：

SELECT * FROM employees WHERE designation NOT IN ('团队主管', '经理');

员工'raja'，在IT或市场部门：

SELECT * FROM employees WHERE empname = 'raja' AND (dept = 'IT' OR dept = 'Marketing');

ORDER BY 子句：用于根据一个或多个列的升序(ASC)或降序(DESC)排序结果集。

按职位升序排序的员工：

SELECT * FROM employees ORDER BY designation;

按姓名和薪水升序排序的员工：

SELECT * FROM employees ORDER BY empname, salary;

按薪水降序，姓名升序排序的员工：

SELECT * FROM employees ORDER BY salary DESC, empname ASC;

薪水最低的前三名员工：

SELECT * FROM employees ORDER BY salary LIMIT 3;

薪水最高的前三名员工：

SELECT * FROM employees ORDER BY salary DESC LIMIT 3;

姓名以'r'开头的员工：

SELECT * FROM employees WHERE empname LIKE 'r%';

姓名第二个字符为'a'的员工：

SELECT * FROM employees WHERE empname LIKE '_a%';

姓名第三个字符为'n'的员工：

SELECT * FROM employees WHERE empname LIKE '__n%';

姓名符合模式'p_i__a'的员工：

SELECT * FROM employees WHERE empname LIKE 'p_i__a';

薪水最低的员工：

SELECT empname FROM employees ORDER BY salary LIMIT 1;

聚合函数：在行组上执行计算并返回单个结果。

计数、平均值、总和、最大值、最小值：

SELECT COUNT(*), AVG(salary), SUM(salary), MIN(salary), MAX(salary) FROM employees;

子查询：嵌套在另一个SQL查询中的查询。

第二高薪的员工：

SELECT MAX(salary) FROM employees WHERE salary NOT IN (SELECT MAX(salary) FROM employees);

第三高薪的员工：

SELECT MAX(salary) FROM employees WHERE salary NOT IN (SELECT MAX(salary) FROM employees) AND salary NOT IN (SELECT MAX(salary) FROM employees WHERE salary NOT IN (SELECT MAX(salary) FROM employees));

GROUP BY 子句：通常与聚合函数一起使用，按一个或多个列分组结果集。

每个部门的总工资：

SELECT dept, SUM(salary) FROM employees GROUP BY dept;

每个部门的平均工资：

SELECT dept, ROUND(AVG(salary), 2) FROM employees GROUP BY dept;

每个部门的最高薪水：

SELECT dept, MAX(salary) FROM employees GROUP BY dept;

每个部门的最低薪水：

SELECT dept, MIN(salary) FROM employees GROUP BY dept;

每个部门的员工人数：

SELECT dept, COUNT(*) FROM employees GROUP BY dept;

以上就是第二天 - 句子，订购，子查询，汇总函数，在数据库中组。的详细内容，更多请关注知识资源分享宝库其它相关文章！