员工信息表:
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;
以上就是第二天 – 句子，订购，子查询，汇总函数，在数据库中组。的详细内容，更多请关注php中文网其它相关文章！
微信扫一扫打赏
支付宝扫一扫打赏
