员工信息表:
<code>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)</code>表名修改：
<code class="sql">ALTER TABLE employee RENAME TO employees;</code>
员工表详细信息：
<code class="sql">\d employees</code>
向员工表添加多行：
<code class="sql">INSERT INTO employees VALUES(105, 'bala', '团队主管', 'AI', 100000), (106, 'kani', '经理', 'CS', 150000);</code>
员工表数据：
<code class="sql">SELECT * FROM employees;</code>
所有部门的员工：
<code class="sql">SELECT dept FROM employees;</code>
唯一部门：SELECT DISTINCT dept FROM employees; 确保只返回唯一部门值。
WHERE子句：PostgreSQL 的 WHERE 子句用于基于特定条件过滤记录，仅检索满足指定标准的行。语法：SELECT column1, column2 FROM table_name WHERE condition;
IT 部门的员工：
<code class="sql">SELECT * FROM employees WHERE dept = 'IT';</code>
IT 部门且职位为“软件工程师”的员工：
<code class="sql">SELECT * FROM employees WHERE dept = 'IT' AND designation = '软件工程师';</code>
将职位列重命名为“职位”：
<code class="sql">SELECT designation AS 职位 FROM employees;</code>
非IT部门的员工：
<code class="sql">SELECT * FROM employees WHERE dept <> 'IT';</code>
薪水大于50000的员工：
<code class="sql">SELECT * FROM employees WHERE salary > 50000;</code>
薪水大于等于50000的员工：
<code class="sql">SELECT * FROM employees WHERE salary >= 50000;</code>
薪水小于等于50000的员工：
<code class="sql">SELECT * FROM employees WHERE salary <= 50000;</code>
薪水在40000到100000之间的员工：
<code class="sql">SELECT * FROM employees WHERE salary BETWEEN 40000 AND 100000;</code>
职位为“团队主管”或“经理”的员工：
<code class="sql">SELECT * FROM employees WHERE designation IN ('团队主管', '经理');</code>职位既不是“团队主管”也不是“经理”的员工：
<code class="sql">SELECT * FROM employees WHERE designation NOT IN ('团队主管', '经理');</code>员工'raja'，在IT或市场部门：
<code class="sql">SELECT * FROM employees WHERE empname = 'raja' AND (dept = 'IT' OR dept = 'Marketing');</code>
ORDER BY 子句：用于根据一个或多个列的升序(ASC)或降序(DESC)排序结果集。
按职位升序排序的员工：
<code class="sql">SELECT * FROM employees ORDER BY designation;</code>
按姓名和薪水升序排序的员工：
<code class="sql">SELECT * FROM employees ORDER BY empname, salary;</code>
按薪水降序，姓名升序排序的员工：
<code class="sql">SELECT * FROM employees ORDER BY salary DESC, empname ASC;</code>
薪水最低的前三名员工：
<code class="sql">SELECT * FROM employees ORDER BY salary LIMIT 3;</code>
薪水最高的前三名员工：
<code class="sql">SELECT * FROM employees ORDER BY salary DESC LIMIT 3;</code>
姓名以'r'开头的员工：
<code class="sql">SELECT * FROM employees WHERE empname LIKE 'r%';</code>
姓名第二个字符为'a'的员工：
<code class="sql">SELECT * FROM employees WHERE empname LIKE '_a%';</code>
姓名第三个字符为'n'的员工：
<code class="sql">SELECT * FROM employees WHERE empname LIKE '__n%';</code>
姓名符合模式'p_i__a'的员工：
<code class="sql">SELECT * FROM employees WHERE empname LIKE 'p_i__a';</code>
薪水最低的员工：
<code class="sql">SELECT empname FROM employees ORDER BY salary LIMIT 1;</code>
聚合函数：在行组上执行计算并返回单个结果。
计数、平均值、总和、最大值、最小值：
<code class="sql">SELECT COUNT(*), AVG(salary), SUM(salary), MIN(salary), MAX(salary) FROM employees;</code>
子查询：嵌套在另一个SQL查询中的查询。
第二高薪的员工：
<code class="sql">SELECT MAX(salary) FROM employees WHERE salary NOT IN (SELECT MAX(salary) FROM employees);</code>
第三高薪的员工：
<code class="sql">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));</code>
GROUP BY 子句：通常与聚合函数一起使用，按一个或多个列分组结果集。
每个部门的总工资：
<code class="sql">SELECT dept, SUM(salary) FROM employees GROUP BY dept;</code>
每个部门的平均工资：
<code class="sql">SELECT dept, ROUND(AVG(salary), 2) FROM employees GROUP BY dept;</code>
每个部门的最高薪水：
<code class="sql">SELECT dept, MAX(salary) FROM employees GROUP BY dept;</code>
每个部门的最低薪水：
<code class="sql">SELECT dept, MIN(salary) FROM employees GROUP BY dept;</code>
每个部门的员工人数：
<code class="sql">SELECT dept, COUNT(*) FROM employees GROUP BY dept;</code>
以上就是第二天 - 句子，订购，子查询，汇总函数，在数据库中组。的详细内容，更多请关注php中文网其它相关文章！
每个人都需要一台速度更快、更稳定的 PC。随着时间的推移，垃圾文件、旧注册表数据和不必要的后台进程会占用资源并降低性能。幸运的是，许多工具可以让 Windows 保持平稳运行。
Copyright 2014-2025 https://www.php.cn/ All Rights Reserved | php.cn | 湘ICP备2023035733号
945
收藏
