MySQL - 3、数据查询和过滤

深碍√TFBOYSˉ_ 2023-10-14 00:21 32阅读 0赞

## 0、创建示例表并插入数据： ##

CREATE TABLE employees (
        emp_id INT PRIMARY KEY,
        emp_name VARCHAR(50) NOT NULL,
        department VARCHAR(50),
        age INT,
        salary DECIMAL(10, 2)
    );
    
    INSERT INTO employees (emp_id, emp_name, department, age, salary) VALUES
        (1, 'lfsun-1', 'HR', 30, 45000.00),
        (2, 'lfsun-2', 'Finance', 35, 60000.00),
        (3, 'lfsun-3', 'IT', 28, 55000.00),
        (4, 'lfsun-4', 'Finance', 32, 58000.00),
        (5, 'lfsun-5', 'HR', 29, 47000.00);

## 1、使用SELECT、WHERE 查询数据： ##

-- 查询所有员工的信息
    SELECT * FROM employees;
    
    -- 查询薪资大于等于55000的员工
    SELECT emp_name, department, salary FROM employees WHERE salary >= 55000;

## 2、使用GROUP BY 对数据分组： ##

-- 查询各个部门的员工数量和平均年龄
    SELECT department, COUNT(emp_id) AS num_employees, AVG(age) AS avg_age
    FROM employees
    GROUP BY department;

## 3、使用HAVING进行条件过滤： ##

-- 查询部门中员工数量大于1的部门的平均薪资
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
    HAVING COUNT(emp_id) > 1;

## 4、使用ORDER BY进行排序： ##

-- 按照薪资从高到低排序显示员工信息
    SELECT * FROM employees ORDER BY salary DESC;

## 5、使用LIMIT限制返回结果数量： ##

-- 查询薪资最高的两名员工
    SELECT * FROM employees ORDER BY salary DESC LIMIT 2;

## 6、使用DISTINCT去除重复记录： ##

-- 查询不重复的部门列表
    SELECT DISTINCT department FROM employees;

## 7、使用UNION合并查询结果： ##

-- 查询HR部门和Finance部门员工的姓名
    SELECT emp_name
    FROM employees
    WHERE department = 'HR'
    UNION
    SELECT emp_name
    FROM employees
    WHERE department = 'Finance';

## 8、使用JOIN连接多个表： ##

CREATE TABLE departments (
        dep_id INT PRIMARY KEY,
        dep_name VARCHAR(50) NOT NULL
    );
    
    INSERT INTO departments (dep_id, dep_name) VALUES
        (1, 'HR'),
        (2, 'Finance'),
        (3, 'IT');
    
    -- 内连接查询员工和部门信息
    SELECT emp_name, department, dep_name
    FROM employees
    JOIN departments ON employees.department = departments.dep_name;

## 9、使用EXISTS进行子查询： ##

-- 查询存在薪资低于60000的员工的部门信息
    SELECT DISTINCT department
    FROM employees e
    WHERE EXISTS (
        SELECT 1
        FROM employees
        WHERE salary < 60000 AND department = e.department
    );

## 10、使用IN和NOT IN进行条件匹配： ##

-- 查询年龄为28、29或32岁的员工信息
    SELECT * FROM employees WHERE age IN (28, 29, 32);
    
    -- 查询不属于HR和IT部门的员工信息
    SELECT * FROM employees WHERE department NOT IN ('HR', 'IT');

## 11、使用BETWEEN进行范围匹配： ##

-- 查询薪资在50000到60000之间的员工信息
    SELECT * FROM employees WHERE salary BETWEEN 50000 AND 60000;

## 12、使用LIKE和NOT LIKE进行模糊匹配： ##

-- 查询姓名以'lfsun'开头的员工信息
    SELECT * FROM employees WHERE emp_name LIKE 'lfsun%';
    
    -- 查询姓名不包含'lfsun-1'的员工信息
    SELECT * FROM employees WHERE emp_name NOT LIKE '%lfsun-1%';

## 13、使用IS NULL和IS NOT NULL进行空值判断： ##

-- 查询没有薪资信息的员工信息
    SELECT * FROM employees WHERE salary IS NULL;
    
    -- 查询具有薪资信息的员工信息
    SELECT * FROM employees WHERE salary IS NOT NULL;