数据库SQL

1. 介绍

SQL（结构化查询语言）是一种用于管理和操作关系数据库的标准语言。各大数据库管理系统（DBMS），如 MySQL、Oracle 等，均实现了 SQL 语言的不同版本。SQL 可以分为以下几类：

数据定义语言（DDL）：用于定义和管理数据库结构，包括创建（CREATE）、修改（ALTER）和删除（DROP）表、视图和索引等。

数据操作语言（DML）：用于处理数据库中的数据，包括插入（INSERT）、更新（UPDATE）、删除（DELETE、TRUNCATE）和查询（SELECT）数据。

事务控制语言（TCL）：用于管理事务的执行，以确保 ACID 属性，包括（COMMIT）、（ROLLBACK）等。

数据控制语言（DCL）：用于管理数据库的权限和访问控制（DBA操作），包括授权（GRANT）、撤销（REVOKE）。

注意：SQL 语句的部分内容不区分大小写，但每个语句都应以分号（;）结束。

2. 代码示例

/**
 * 代码中包含详细用法解释
 * 注释的三种形式，分单行和多行
 */

/* 数据定义语言（DDL）部分 */

// 1. 创建数据库
// 如果数据库不存在，则创建它
CREATE DATABASE IF NOT EXISTS company_db;

/** 
 * 2. 使用数据库
 * 选择刚创建的数据库作为当前操作的数据库
 */
USE company_db;

/** 
 * 3. 创建表
 * 如果表不存在，则创建它，并为表和列添加注释。
 * 约束条件包括：`NOT NULL`（不能为空），`UNIQUE`（唯一值），`PRIMARY KEY`（主键），`FOREIGN KEY`（外键），`DEFAULT`（默认值）。
 */
DROP TABLE IF EXISTS `employees`; -- 由于有外键约束，需要先删除 employees 表，再删除 departments 表
DROP TABLE IF EXISTS `departments`;

CREATE TABLE IF NOT EXISTS departments (
    department_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '部门ID，主键，自增',
    department_name VARCHAR(50) NOT NULL COMMENT '部门名称，不能为空'
) COMMENT='部门信息表';

CREATE TABLE IF NOT EXISTS employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '员工ID，主键，自增',
    department_id INT NOT NULL COMMENT '部门ID，不能为空',
    first_name VARCHAR(50) NOT NULL COMMENT '员工名，不能为空',
    last_name VARCHAR(50) NOT NULL COMMENT '员工姓，不能为空',
    email VARCHAR(64) NOT NULL DEFAULT '' COMMENT '员工邮件',
    hire_date DATE COMMENT '入职日期',
    salary DECIMAL(10, 2) COMMENT '薪资，最多10位数字，其中2位小数',
    update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    FOREIGN KEY (department_id) REFERENCES departments(department_id) -- 外键约束
) COMMENT='员工信息表';

/** 
 * 4. 创建索引
 * 检查索引是否存在，如果存在则删除后重新创建。
 * 索引可大大加快数据的检索速度，通常不需要全表扫描，但会占用额外的存储空间。
 */
CREATE INDEX idx_salary ON employees(salary) COMMENT '薪资索引，用于加快薪资查询';

// 删除索引的示例
-- ALTER TABLE employees DROP INDEX idx_salary;

// 向 employees 表中添加创建时间字段
ALTER TABLE employees ADD COLUMN created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间';

// 更新表结构，将薪资字段改为浮点数类型
ALTER TABLE employees MODIFY COLUMN salary FLOAT(10, 2) COMMENT '薪资，浮点数，最多10位数字，其中2位小数';

// 删除表中的字段，删除 update_time 字段
ALTER TABLE employees DROP COLUMN update_time;

/** 
 * 5. 创建视图
 * 如果视图存在，则删除它，然后重新创建。
 * 视图是 SQL 语句结果集的可视化表，无法进行索引操作，可以简化复杂的 SQL 操作。
 */
DROP VIEW IF EXISTS high_salary_employees;
CREATE VIEW high_salary_employees AS
SELECT first_name, last_name, salary
FROM employees
WHERE salary > 60000;

/* 数据操作语言（DML）部分 */

// 1. 插入数据
// 向部门表中插入记录
INSERT INTO departments (department_name)
VALUES ('HR'),
       ('Engineering'),
       ('Sales'),
       ('Marketing');

// 向员工表中插入记录
INSERT INTO employees (first_name, last_name, hire_date, salary, department_id)
VALUES ('John', 'Doe', '2024-08-15', 70000.00, 1),
       ('Jane', 'Smith', '2024-08-16', 75000.00, 1),
       ('Alice', 'Johnson', '2024-08-17', 68000.00, 2),
       ('Bob', 'Brown', '2024-08-18', 71000.00, 3),
       ('Charlie', 'Davis', '2024-08-19', 72000.00, 4);

// 2. 更新数据
// 更新员工的薪资和邮件信息
UPDATE employees
SET salary = 72000.00, email='liangge@163.com'
WHERE first_name = 'John' AND last_name = 'Doe';

// 3. 删除数据
// 从员工表中删除某个记录
DELETE FROM employees
WHERE first_name = 'Charlie' AND last_name = 'Davis';

// 清空表中所有的数据
-- TRUNCATE TABLE employees;

/** 
 * 4. 查询数据
 * 查询所有员工的信息，并按薪资降序排序。
 * DISTINCT 用于返回不同值，所有列的值相同才算相同。
 * LIMIT 限制返回的行数。
 * ORDER BY 用于一列或多列排序，默认升序。
 */
SELECT DISTINCT first_name, last_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 0, 10;

// 查询视图 'high_salary_employees' 中的数据
SELECT * FROM high_salary_employees;

// 5. 连接查询
// 使用内连接（INNER JOIN），只有当两个表都存在满足条件时，才返回行。
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

/** 
 * 6. 右连接查询
 * 从员工表和部门表中查询数据，使用右连接（RIGHT JOIN）。
 * 返回右边的表中所有的记录。
 */
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;

/** 左连接查询
 * 左连接（LEFT JOIN）返回左边的表中所有的记录。
 * SELECT e.first_name, e.last_name, d.department_name
 * FROM employees e
 * LEFT JOIN departments d ON e.department_id = d.department_id;
 */

// 7. 使用 UNION 查询，组合两个查询的结果集，去掉重复的记录。
SELECT first_name, last_name FROM employees
UNION
SELECT first_name, last_name FROM employees;

/** 
 * 8. 使用子查询
 * 将一个 `SELECT` 查询的结果作为另一个 SQL 语句的条件。
 * 查找薪资高于员工表中最高薪资的员工。
 */
SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT MAX(salary) FROM employees);

// 9. 查询不重复的部门名称
SELECT DISTINCT department_name
FROM departments;

/** 
 * 10. 使用 GROUP BY 和 HAVING
 * GROUP BY 用于分组，HAVING 用于对汇总结果进行过滤。
 * 按部门分组，计算每个部门的员工数量，并仅显示员工数量大于1的部门。
 */
SELECT department_name, COUNT(*) AS employee_count
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY department_name
HAVING COUNT(*) > 1
ORDER BY employee_count DESC;

/** 
 * 11. 使用 WHERE
 * 查询薪资在60000到80000之间的员工，且姓名中包含 'John'。
 */
SELECT * FROM employees
WHERE salary BETWEEN 60000 AND 80000
AND first_name LIKE '%John%';

// 12. 使用 LIKE 查询
// 查找姓氏以 'S' 开头的员工
SELECT * FROM employees WHERE last_name LIKE 'S%';

/* 事务控制语言（TCL）部分 */

// 1. 开始事务
START TRANSACTION;

// 2. 进行一些数据操作
INSERT INTO employees (first_name, last_name, hire_date, salary, department_id)
VALUES ('Eve', 'Taylor', '2024-08-20', 69000.00, 3);

INSERT INTO employees (first_name, last_name, hire_date, salary, department_id)
VALUES ('Wang', 'Liang', '2024-08-21', 89000.00, 4);

// 3. 提交事务
COMMIT;

// 4. 回滚事务（如果需要）
-- ROLLBACK;

/* 数据控制语言（DCL）部分 */

// 创建用户（如果尚未创建）
CREATE USER 'manager_role'@

'localhost' IDENTIFIED BY '123456';

// 授予 SELECT 权限
GRANT SELECT ON company_db.* TO 'manager_role'@'localhost';

// 刷新权限
FLUSH PRIVILEGES;

// 查看权限
SHOW GRANTS FOR 'manager_role'@'localhost';

// 验证权限的正确性
-- mysql -uroot -p
-- SHOW GRANTS FOR CURRENT_USER; // 查询权限
-- SHOW DATABASES; // 查看数据库
-- USE company_db; // 选择数据库
-- SHOW TABLES; // 查看表
-- SELECT * FROM employees; // 正确显示

// 撤销 SELECT 权限
REVOKE SELECT ON company_db.* FROM 'manager_role'@'localhost';

// 刷新权限
FLUSH PRIVILEGES;

// 删除用户
DROP USER 'manager_role'@'localhost';

/* 启用 MySQL 服务器的远程访问权限 */
-- 配置MySQL服务器以接收远程连接
-- 修改绑定地址
-- sudo nano /etc/mysql/my.cnf
-- 将 bind-address 设置为 0.0.0.0，允许任何IP连接；
-- sudo systemctl restart mysql
-- 调整MySQL权限
-- mysql -u root -p
-- GRANT ALL PRIVILEGES ON database_name.* TO 'manager_role'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
-- FLUSH PRIVILEGES;
-- 防火墙允许访问端口，比如3306
-- sudo mysql_secure_installation // 提高MySQL的安全性
-- 设置 root 用户密码
-- 移除匿名用户
-- 禁用 root 用户远程登录
-- 删除测试数据库
-- 重新加载权限表

以上是关于 SQL 的基本介绍和示例。通过这些内容，希望能帮助大家更好地理解 SQL 的结构和用法。如果有任何疑问，请随时提问！