一次mysql关于库，表，相关命令的操作
需求 ：一个sql语法，涵盖大部份的mysql的语法
创建库
CREATE DATABASE `company` CHARACTER SET UTF8MB4 COLLATE UTF8MB4_GENERAL_CI;
创建表
# 切换数据库
use company;
# 部门表
CREATE TABLE departments (
department_id INT AUTO_INCREMENT PRIMARY KEY,
department_name VARCHAR(255) NOT NULL,
location VARCHAR(255) NOT NULL
);
# 雇员表
CREATE TABLE employees (
employee_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
salary DECIMAL(10, 2) NOT NULL,
department_id INT,
FOREIGN KEY (department_id) REFERENCES departments(department_id)
);
示例数据准备
INSERT INTO departments (department_name, location) VALUES
('技术部', '上海'),
('市场部', '北京'),
('人事部', '上海'),
('财务部', '上海');
INSERT INTO employees (name, salary, department_id) VALUES
('张三', 8000.00, 1),
('李四', 6000.00, 1),
('王五', 9000.00, 1),
('赵六', 5500.00, 2),
('孙七', 7000.00, 2),
('周八', 5200.00, 3),
('吴九', 5800.00, 3),
('郑十', 6500.00, 3),
('钱十一', 10000.00, 4),
('刘十二', 9500.00, 4),
('陈十三', 6200.00, 1),
-- 继续添加剩余的90条记录
('张十四', 7500.00, 1),
('李十五', 6500.00, 1),
('王十六', 8000.00, 1),
('赵十七', 5600.00, 2),
('孙十八', 6800.00, 2),
('周十九', 5300.00, 3),
('吴二十', 6000.00, 3),
('郑二十一', 6700.00, 3),
('钱二十二', 9000.00, 4),
('刘二十三', 8500.00, 4),
('陈二十四', 6300.00, 1),
-- 继续添加剩余的80条记录
('张二十五', 7600.00, 1),
('李二十六', 6600.00, 1),
('王二十七', 8100.00, 1),
('赵二十八', 5700.00, 2),
('孙二十九', 6900.00, 2),
('周一十', 5400.00, 3),
('吴三十一', 6100.00, 3),
('郑三十二', 6800.00, 3),
('钱三十三', 9100.00, 4),
('刘三十四', 8600.00, 4),
('陈三十五', 6400.00, 1),
-- 继续添加剩余的70条记录
('张三十六', 7700.00, 1),
('李三十七', 6700.00, 1),
('王三十八', 8200.00, 1),
('赵三十九', 5800.00, 2),
('孙四十', 7000.00, 2),
('周四十一', 5500.00, 3),
('吴四十二', 6200.00, 3),
('郑四十三', 6900.00, 3),
('钱四十四', 9200.00, 4),
('刘四十五', 8700.00, 4),
('陈四十六', 6500.00, 1),
-- 继续添加剩余的60条记录
('张四十七', 7800.00, 1),
('李四十八', 6800.00, 1),
('王四十九', 8300.00, 1),
('赵五十', 5900.00, 2),
('孙五十一', 7100.00, 2),
('周五十二', 5600.00, 3),
('吴五十三', 6300.00, 3),
('郑五十四', 7000.00, 3),
('钱五十五', 9300.00, 4),
('刘五十六', 8800.00, 4),
('陈五十七', 6600.00, 1),
-- 继续添加剩余的50条记录
('张五十八', 7900.00, 1),
('李五十九', 6900.00, 1),
('王六十', 8400.00, 1),
('赵六十一', 6000.00, 2),
('孙六十二', 7200.00, 2),
('周六十三', 5700.00, 3),
('吴六十四', 6400.00, 3),
('郑六十五', 7100.00, 3),
('钱六十六', 9400.00, 4),
('刘六十七', 8900.00, 4),
('陈六十八', 6700.00, 1),
-- 继续添加剩余的40条记录
('张六十九', 8000.00, 1),
('李七十', 7000.00, 1),
('王七十一', 8500.00, 1),
('赵七十二', 6100.00, 2),
('孙七十三', 7300.00, 2),
('周七十四', 5800.00, 3),
('吴七十五', 6500.00, 3),
('郑七十六', 7200.00, 3),
('钱七十七', 9500.00, 4),
('刘七十八', 9000.00, 4),
('陈七十九', 6800.00, 1),
-- 继续添加剩余的30条记录
('张八十', 8100.00, 1),
('李八十一', 7100.00, 1),
('王八十二', 8600.00, 1),
('赵八十三', 6200.00, 2),
('孙八十四', 7400.00, 2),
('周八十五', 5900.00, 3),
('吴八十六', 6600.00, 3),
('郑八十七', 7300.00, 3),
('钱八十八', 9600.00, 4),
('刘八十九', 9100.00, 4),
('陈九十', 6900.00, 1),
-- 继续添加剩余的20条记录
('张九十一', 8200.00, 1),
('李九十二', 7200.00, 1),
('王九十三', 8700.00, 1),
('赵九十四', 6300.00, 2),
('孙九十五', 7500.00, 2),
('周九十六', 6000.00, 3),
('吴九十七', 6700.00, 3),
('郑九十八', 7400.00, 3),
('钱九十九', 9700.00, 4),
('刘一百', 9200.00, 4),
('陈一百零一', 7000.00, 1);
相关操作
1. 查询所有部门的名称和位置。
SELECT department_name, location FROM departments;
2. 查询薪资高于8000的所有员工的姓名和薪资。
SELECT name, salary FROM employees WHERE salary > 8000;
3. 查询位于上海的部门中薪资最低的员工信息。
SELECT
e.*
FROM
employees e
-- 使用JOIN连接employees表和departments表，关联条件是部门ID相等
JOIN
departments d ON e.department_id = d.department_id
WHERE
d.location = '上海'
ORDER BY
e.salary ASC
LIMIT 1;
4. 查询每个部门的员工人数。
SELECT department_id, COUNT(*) AS employee_count FROM employees GROUP BY department_id;
5. 插入一个新的部门记录，部门名为“研发部”，位置为“北京”。
INSERT INTO departments (department_name, location) VALUES ('研发部', '北京');
6. 插入三条新的员工记录，假设部门ID为自动获取。
INSERT INTO employees (name, salary, department_id) VALUES ('小明', 7500.00, 5);
INSERT INTO employees (name, salary, department_id) VALUES ('小红', 8200.00, 5);
INSERT INTO employees (name, salary, department_id) VALUES ('小丽', 6800.00, 5);
7. 更新薪资低于6000的员工，将他们的薪资提高10%。
UPDATE employees SET salary = salary * 1.1 WHERE salary < 6000;
8. 删除薪资最高的员工记录。
DELETE FROM employees WHERE salary = (SELECT MAX(salary) FROM employees);
9. 查询薪资在6000到9000之间的员工信息。
SELECT * FROM employees WHERE salary BETWEEN 6000 AND 9000;
10. 查询没有员工的部门信息。
SELECT * FROM departments WHERE department_id NOT IN (SELECT DISTINCT department_id FROM employees);
11. 查询薪资高于部门平均薪资的员工信息。
-- 从employees表中查询出满足条件的所有记录
SELECT e.*
FROM employees e
-- 通过JOIN连接employees表和计算各部门平均薪资的子查询结果
JOIN (
-- 子查询：计算每个部门的平均薪资
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
) AS avg_salaries
-- 连接条件是部门ID相等
ON e.department_id = avg_salaries.department_id
-- 筛选出员工薪资高于所在部门平均薪资的记录
WHERE e.salary > avg_salaries.avg_salary;
12. 查询薪资最高的员工所在的部门名称。
SELECT
d.department_name
FROM
employees e
JOIN departments d ON e.department_id = d.department_id
WHERE
e.salary = ( SELECT MAX( salary ) FROM employees );
13. 查询每个部门薪资最高的员工信息。
-- 从employees表中选取满足特定条件的所有记录
SELECT
e.*
FROM
employees e
-- 通过JOIN操作连接employees表和一个子查询的结果
JOIN (
-- 子查询：计算每个部门的最大薪资
SELECT
department_id,
MAX(salary) AS max_salary
FROM
employees
GROUP BY
department_id
) AS max_salaries
-- 连接条件为部门ID相等且员工薪资等于该部门的最大薪资
ON e.department_id = max_salaries.department_id
AND e.salary = max_salaries.max_salary;
14. 将“技术部”更名为“信息技术部”。
UPDATE departments SET department_name = '信息技术部' WHERE department_name = '技术部';
15. 查询薪资排名在前5的员工信息。
SET @row_number = 0;
SELECT
employee_id,
NAME,
salary,
@row_number := @row_number + 1 AS rn
FROM
employees
ORDER BY
salary DESC
LIMIT 5;
16. 查询薪资低于部门平均薪资的员工及其部门名称。
-- 从相关表中查询员工姓名、薪资以及所在部门名称，筛选出薪资低于所在部门平均薪资的记录
SELECT
e.NAME,
e.salary,
d.department_name
FROM
employees e
-- 连接员工表和部门表，通过部门ID建立关联
JOIN departments d ON e.department_id = d.department_id
-- 连接员工表和计算各部门平均薪资的子查询结果，通过部门ID建立关联
JOIN (
SELECT
department_id,
AVG(salary) AS avg_salary
FROM
employees
GROUP BY
department_id
) AS avg_salaries ON e.department_id = avg_salaries.department_id
WHERE
e.salary < avg_salaries.avg_salary;
18. 查询每个部门薪资高于7000的员工人数。
SELECT
d.department_name,
COUNT( e.employee_id ) AS high_salary_count
FROM
departments d
LEFT JOIN employees e ON d.department_id = e.department_id
AND e.salary > 7000
GROUP BY
d.department_name;
19. 查询没有上海员工的部门名称。
SELECT
department_name
FROM
departments
WHERE
department_id NOT IN (
SELECT DISTINCT department_id
FROM
employees
WHERE
department_id IN (
SELECT department_id
FROM
departments
WHERE
location = '上海'
)
);
-- 整体说明：
-- 此SQL语句首先通过内层子查询获取位于上海的部门的部门ID，
-- 然后在中间子查询中找出这些部门中员工所在的部门ID并去重，
-- 最后在主查询中从departments表中筛选出部门ID不在上述去重后的部门ID集合中的部门名称。
HAVING专项
1. 找出平均工资高于某个值的部门
SELECT
d.department_name,
AVG( e.salary ) AS avg_salary
FROM
employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY
d.department_id,
d.department_name
HAVING
AVG( e.salary ) > 7000;
+-----------------+-------------+
| department_name | avg_salary |
+-----------------+-------------+
| 信息技术部 | 7411.111111 |
| 财务部 | 9172.222222 |
| 研发部 | 7500.000000 |
+-----------------+-------------+
2. 找出员工数量超过一定数量的部门
SELECT
d.department_name,
COUNT(*) AS employee_count
FROM
employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY
d.department_id,
d.department_name
HAVING
COUNT(*) > 10;
3. 找出最高工资超过某个值的部门
SELECT
d.department_name,
MAX( e.salary ) AS max_salary
FROM
employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY
d.department_id,
d.department_name
HAVING
MAX( e.salary ) > 8000;
4. 找出总工资超过某个值的部门
SELECT
d.department_name,
SUM( e.salary ) AS total_salary
FROM
employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY
d.department_id,
d.department_name
HAVING
SUM( e.salary ) > 8000;
5. 找出最低工资与最高工资差距超过某个值的部门
SELECT
d.department_name,
MIN( e.salary ) AS min_salary,
MAX( e.salary ) AS max_salary,
(
MAX( e.salary ) - MIN( e.salary )) AS salary_range
FROM
employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY
d.department_id,
d.department_name
HAVING
(
MAX( e.salary ) - MIN( e.salary )) > 2000;
6. 找出至少有一个员工工资超过某个值的部门
SELECT
d.department_name
FROM
employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY
d.department_id,
d.department_name
HAVING
MAX( e.salary ) > 9000;
7. 找出平均工资和最高工资都高于某个值的部门
SELECT
d.department_name,
AVG( e.salary ) AS avg_salary,
MAX( e.salary ) AS max_salary
FROM
employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY
d.department_id,
d.department_name
HAVING
AVG( e.salary ) > 5000
AND MAX( e.salary ) > 9000;
8. 找出员工数量是偶数的部门
SELECT
d.department_name,
COUNT(*) AS employee_count
FROM
employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY
d.department_id,
d.department_name
HAVING
MOD ( COUNT(*), 2 ) = 0;
9. 找出平均工资高于全公司平均的部门
SELECT
d.department_name,
AVG( e.salary ) AS avg_salary
FROM
employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY
d.department_id,
d.department_name
HAVING
AVG( e.salary ) > ( SELECT AVG( salary ) FROM employees );
10. 找出至少有 X 个员工工资在 Y 到 Z 之间的部门
SELECT
d.department_name,
COUNT(*) AS count_in_range
FROM
employees e
JOIN departments d ON e.department_id = d.department_id
WHERE
e.salary BETWEEN 7000
AND 9000 -- 假设 Y 和 Z 是具体的值
GROUP BY
d.department_id,
d.department_name
HAVING
COUNT(*) >= 20;-- 假设 X 是具体的员工数量
评论区