数据库查询一般格式实操介绍

我们可以以一个员工管理系统为例，来展示创建数据库、导入数据以及查询数据的SQL语句。

创建数据库和表

创建数据库：

CREATE DATABASE EmployeeDB;

使用数据库：

USE EmployeeDB;

创建表：

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    salary DECIMAL(10, 2)
);

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

导入数据

插入数据到departments表：

INSERT INTO departments (department_id, department_name) VALUES
(1, 'Human Resources'),
(2, 'Finance'),
(3, 'Engineering'),
(4, 'Marketing');

插入数据到employees表：

INSERT INTO employees (employee_id, first_name, last_name, department_id, salary) VALUES
(101, 'John', 'Doe', 1, 60000),
(102, 'Jane', 'Smith', 2, 75000),
(103, 'Robert', 'Johnson', 3, 80000),
(104, 'Emily', 'Davis', 4, 72000),
(105, 'Michael', 'Brown', 3, 68000),
(106, 'Linda', 'Wilson', 1, 54000),
(107, 'David', 'Taylor', 2, 69000),
(108, 'Barbara', 'Martinez', 4, 58000),
(109, 'James', 'Anderson', 3, 62000),
(110, 'Patricia', 'Thomas', 1, 49000);

数据库情况演示

演示查询语句

查询所有员工的信息：

SELECT * FROM employees;

查询特定部门（例如Engineering）的员工：

SELECT first_name, last_name
FROM employees
WHERE department_id = 3;

按部门统计员工人数：

SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;

查询工资大于65000的员工，并按工资降序排列：

SELECT first_name, last_name, salary
FROM employees
WHERE salary > 65000
ORDER BY salary DESC;

查询每个部门工资总和大于150000的部门：

SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id
HAVING SUM(salary) > 150000;

综合查询

查询每个部门员工人数、平均工资，并按部门名称排序：

SELECT d.department_name, COUNT(e.employee_id) AS num_employees, AVG(e.salary) AS avg_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name
ORDER BY d.department_name;