Mysql数据库实操

数据库安装与初始设置

1. Mysql数据库安装

   # 更新软件源列表
   sudo apt update
   
   # （可选）升级已安装的软件包，保持系统为最新
   sudo apt upgrade -y
   
   # 安装 MySQL 服务器
   sudo apt install mysql-server -y
   
   # 查看 MySQL 运行状态
   sudo systemctl status mysql
   
   # 启动 MySQL 服务
   sudo systemctl start mysql
   # 设置 MySQL 开机自动启动
   sudo systemctl enable mysql

2. 安全配置脚本

   # 启动MySQL安全配置向导
   sudo mysql_secure_installation

3. 登录数据库

   # 尝试用密码登录 MySQL
   sudo mysql -u root -p

4. 修改密码

   -- 在 MySQL 命令行中执行
   ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的新密码';
   FLUSH PRIVILEGES;
   EXIT;

创建数据库和用户

1. 创建一个数据库（比如叫 my_learn_db）

   CREATE DATABASE my_learn_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

2. 创建一个新用户（比如叫 learner，密码设置为 YourPassword123!）

   CREATE USER 'learner'@'localhost' IDENTIFIED BY 'YourPassword123!';

3. 给这个用户授权，让他可以完全管理 my_learn_db 数据库

   GRANT ALL PRIVILEGES ON my_learn_db.* TO 'learner'@'localhost';

4. 刷新权限，使授权生效

   FLUSH PRIVILEGES;

5. 退出 当前 root 用户

   EXIT;

新用户登录并创建一表

1. 通过命令登录

   # 用 learner 用户登录，-p 参数表示需要输入密码
   mysql -u learner -p

2. 连接到你的数据库

   USE my_learn_db;

3. 创建一个简单的表

   CREATE TABLE study_plan (
    id INT AUTO_INCREMENT PRIMARY KEY,
    task_name VARCHAR(255) NOT NULL,
    plan_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
   );

4. 插入一条数据

   INSERT INTO study_plan (task_name, plan_date) VALUES ('学习SQL基本操作', CURDATE());

5. 查询数据

   SELECT * FROM study_plan;

精通SQL查询

准备数据

1. 以learner用户登录 MySQL

   mysql -u learner -p

2. 切换数据库

   USE my_learn_db;

3. 创建并填充数据

   -- 创建员工表
   CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10,2),
    hire_date DATE,
    age INT
   );
   
   -- 插入一些示例数据
   INSERT INTO employees (name, department, salary, hire_date, age) VALUES
   ('张三', '技术部', 8500.00, '2021-06-15', 28),
   ('李四', '市场部', 7200.00, '2022-03-10', 32),
   ('王五', '技术部', 9500.00, '2020-11-01', 35),
   ('赵六', '销售部', 6300.00, '2023-01-20', 26),
   ('孙七', '市场部', 8000.00, '2021-09-05', 29),
   ('周八', '技术部', 11000.00, '2019-04-12', 40),
   ('吴九', '销售部', 5800.00, '2023-07-08', 24),
   ('郑十', '技术部', 9200.00, '2022-08-22', 31);

SELECT 查询基础

1. 查询所有列

   SELECT * FROM employees;

2. 查询特定列

   SELECT name, salary FROM employees;

3. 使用别名 (AS) 让输出更易读，AS 可以省略（用空格代替），但加上更清晰。

   SELECT name AS '姓名', salary AS '月薪' FROM employees;

WHERE 条件筛选

数据过滤的核心，WHERE 用于从表中筛选出满足条件的行。

1. 比较运算符

     - = 等于
     - != 或 <> 不等于
     - > 大于
     - < 小于
     - >= 大于等于
     - <= 小于等于

   示例：

   -- 查询技术部所有员工
   SELECT name, department, salary FROM employees WHERE department = '技术部';
   
   -- 查询月薪大于8000的员工
   SELECT name, salary FROM employees WHERE salary > 8000;
   
   -- 查询年龄小于30的员工
   SELECT name, age FROM employees WHERE age < 30;

2. 逻辑运算符：AND、OR、NOT

   -- 查询技术部且月薪大于9000的员工
   SELECT * FROM employees WHERE department = '技术部' AND salary > 9000;
   
   -- 查询市场部或销售部的员工
   SELECT * FROM employees WHERE department = '市场部' OR department = '销售部';
   
   -- 查询不是技术部的员工
   SELECT * FROM employees WHERE NOT department = '技术部';

3. 特殊条件
   3.1 IN：匹配列表中的任意值

   SELECT * FROM employees WHERE department IN ('技术部', '市场部');

   3.2 BETWEEN ... AND ...：在某个范围内（包含边界）

   SELECT * FROM employees WHERE salary BETWEEN 6000 AND 9000;

   3.3 LIKE：模糊匹配（% 代表任意多个字符，_ 代表一个字符）

   -- 查询姓名以“张”开头的员工
   SELECT * FROM employees WHERE name LIKE '张%';
   
   -- 查询姓名包含“七”的员工
   SELECT * FROM employees WHERE name LIKE '%七%';

   3.4 IS NULL / IS NOT NULL：判断是否为空（注意不能用 = NULL）

   -- 假设我们有某些字段可能为空，例如添加一个 NULL 测试
   -- 查询没有部门信息的员工（演示用，实际数据中没有，但语法如此）
   SELECT * FROM employees WHERE department IS NULL;

4. ORDER BY 排序
   让查询结果按照指定列排序。

   -- 按月薪升序排列（默认 ASC，可省略）
   SELECT name, salary FROM employees ORDER BY salary ASC;
   
   -- 按月薪降序排列
   SELECT name, salary FROM employees ORDER BY salary DESC;
   
   -- 多字段排序：先按部门升序，同部门内按月薪降序
   SELECT name, department, salary FROM employees ORDER BY department ASC, salary DESC;

5. 聚合函数 —— 对一组数据进行计算
   聚合函数用于统计、汇总数据，常与 GROUP BY 配合使用。
   常用聚合函数：
   COUNT(*) 统计行数
   SUM(列) 求和
   AVG(列) 求平均值
   MAX(列) 求最大值
   MIN(列) 求最小值
   示例：

   -- 统计员工总数
   SELECT COUNT(*) AS '总人数' FROM employees;
   
   -- 统计技术部员工人数
   SELECT COUNT(*) FROM employees WHERE department = '技术部';
   
   -- 计算平均月薪
   SELECT AVG(salary) AS '平均月薪' FROM employees;
   
   -- 查看最高、最低月薪
   SELECT MAX(salary) AS '最高月薪', MIN(salary) AS '最低月薪' FROM employees;

多表查询与子查询

1. 准备多表数据
   继续使用 my_learn_db 数据库，我们添加两张新表：departments（部门详细信息）和 projects（项目信息），并通过中间表 emp_proj 建立员工与项目的多对多关系。

   USE my_learn_db;
   
   -- 创建部门表
   CREATE TABLE departments (
    dept_id INT PRIMARY KEY AUTO_INCREMENT,
    dept_name VARCHAR(50) UNIQUE NOT NULL,
    location VARCHAR(100)
   );
   
   INSERT INTO departments (dept_name, location) VALUES
   ('技术部', 'A座3楼'),
   ('市场部', 'B座2楼'),
   ('销售部', 'B座1楼'),
   ('人事部', 'A座5楼');  -- 人事部暂无员工
   
   -- 创建项目表
   CREATE TABLE projects (
    proj_id INT PRIMARY KEY AUTO_INCREMENT,
    proj_name VARCHAR(100) NOT NULL,
    budget DECIMAL(12,2)
   );
   
   INSERT INTO projects (proj_name, budget) VALUES
   ('Alpha项目', 100000),
   ('Beta项目', 150000),
   ('Gamma项目', 80000);
   
   -- 创建员工-项目关联表
   CREATE TABLE emp_proj (
    emp_id INT,
    proj_id INT,
    join_date DATE,
    PRIMARY KEY (emp_id, proj_id),
    FOREIGN KEY (emp_id) REFERENCES employees(id),
    FOREIGN KEY (proj_id) REFERENCES projects(proj_id)
   );
   
   INSERT INTO emp_proj (emp_id, proj_id, join_date) VALUES
   (1, 1, '2024-01-15'),
   (1, 2, '2024-02-01'),
   (2, 1, '2024-01-20'),
   (3, 2, '2024-01-10'),
   (3, 3, '2024-03-01'),
   (4, 3, '2024-02-15'),
   (5, 1, '2024-01-25'),
   (6, 2, '2024-01-05'),
   (7, 3, '2024-02-20');

2. 表连接 (JOIN)
   2.1 内连接 (INNER JOIN)
   只返回两张表中匹配的行。
   示例：查询每个员工的姓名及其部门位置。

   SELECT e.name, d.dept_name, d.location
   FROM employees e
   INNER JOIN departments d ON e.department = d.dept_name;

   2.2 左连接 (LEFT JOIN)
   返回左表所有行，右表无匹配时显示 NULL。
   示例：列出所有部门及其员工（包括无员工的人事部）。

   SELECT d.dept_name, e.name
   FROM departments d
   LEFT JOIN employees e ON d.dept_name = e.department;

   2.3 右连接 (RIGHT JOIN)
   返回右表所有行，通常可用左连接调换顺序替代。
   示例：所有部门及其员工（与上例等价）。

   SELECT d.dept_name, e.name
   FROM employees e
   RIGHT JOIN departments d ON e.department = d.dept_name;

   2.4 多表连接
   示例：查询每个员工参与的项目名称。

   SELECT e.name, p.proj_name
   FROM employees e
   INNER JOIN emp_proj ep ON e.id = ep.emp_id
   INNER JOIN projects p ON ep.proj_id = p.proj_id;

   若想列出所有员工（包括未参与项目的），使用左连接：

   SELECT e.name, p.proj_name
   FROM employees e
   LEFT JOIN emp_proj ep ON e.id = ep.emp_id
   LEFT JOIN projects p ON ep.proj_id = p.proj_id;

3. 子查询
   3.1 WHERE 子句中的子查询
   示例：查询工资高于公司平均工资的员工。

   SELECT name, salary
   FROM employees
   WHERE salary > (SELECT AVG(salary) FROM employees);

   示例：查询参与过 'Alpha项目' 的员工姓名。

   SELECT name
   FROM employees
   WHERE id IN (SELECT emp_id FROM emp_proj WHERE proj_id = 
            (SELECT proj_id FROM projects WHERE proj_name = 'Alpha项目'));

   3.2 FROM 子句中的子查询
   将子查询结果作为临时表。
   示例：各部门平均工资，然后筛选出平均工资 >8000 的部门。

   SELECT dept, avg_salary
   FROM (SELECT department AS dept, AVG(salary) AS avg_salary 
      FROM employees GROUP BY department) AS dept_avg
   WHERE avg_salary > 8000;

   3.3 SELECT 子句中的子查询
   示例：查询员工姓名、工资，并显示公司平均工资。

   SELECT name, salary, (SELECT AVG(salary) FROM employees) AS company_avg
   FROM employees;

   3.4 相关子查询
   子查询依赖外层查询的当前行。
   示例：查询工资高于其所在部门平均工资的员工。

   SELECT e1.name, e1.salary, e1.department
   FROM employees e1
   WHERE salary > (SELECT AVG(salary) FROM employees e2 
                WHERE e2.department = e1.department);

4. 视图View
   视图是保存的 SQL 查询，可像表一样查询。
   示例：创建视图，包含员工姓名、部门名称和位置。

   CREATE VIEW emp_dept_view AS
   SELECT e.name, d.dept_name, d.location
   FROM employees e
   LEFT JOIN departments d ON e.department = d.dept_name;

   使用视图：

   SELECT * FROM emp_dept_view WHERE dept_name = '技术部';

5. 索引Index初步认识
   索引用于加速查询。在常用条件列上创建索引。
   查看索引：

   SHOW INDEX FROM employees;

   创建索引：

   CREATE INDEX idx_emp_department ON employees(department);

   删除索引：

   DROP INDEX idx_emp_department ON employees;

6. 综合练习
   请动手完成以下练习，巩固今天的内容：
   查询所有员工及其部门位置（包括可能无部门的员工，如果有）。
   列出每个项目名称及参与人数。
   找出参与项目最多的员工（显示姓名和项目数）。
   查询没有参与任何项目的员工姓名。
   使用子查询，查询工资高于技术部平均工资的员工（不限部门）。
   创建一个视图，显示员工姓名、项目名称和加入项目日期。
   （挑战）查询参与过所有项目的员工姓名（提示：用 COUNT(DISTINCT proj_id) 与总项目数比较）。

   备份与恢复

   备份是防止数据丢失的最后一道防线。我们主要学习逻辑备份（使用 mysqldump）和恢复。
   3.1 使用 mysqldump 进行备份
   mysqldump 是 MySQL 自带的命令行工具，用于导出数据库为 SQL 文件。
   基本语法：

   mysqldump -u 用户名 -p 数据库名 > 备份文件.sql

   常用选项：

   --single-transaction：对于 InnoDB 表，在备份期间使用事务，保证一致性（不锁表）。
   
   --routines：包含存储过程和函数。
   
   --triggers：包含触发器。
   
   --events：包含事件。
   
   --all-databases：备份所有数据库。
   
   --databases db1 db2：备份多个指定数据库。
   
   --where="条件"：只备份满足条件的行。

   示例：

   # 备份单个数据库 my_learn_db（包含表结构和数据）
   mysqldump -u learner -p my_learn_db > /home/learner/backup_$(date +%Y%m%d).sql
   
   # 只备份表结构（不包含数据）
   mysqldump -u learner -p --no-data my_learn_db > structure.sql
   
   # 只备份数据（不包含表结构）
   mysqldump -u learner -p --no-create-info my_learn_db > data.sql
   
   # 备份所有数据库
   mysqldump -u root -p --all-databases > all_db.sql

   注意：执行 mysqldump 时不需要登录 MySQL 客户端，直接在系统终端执行。输入密码时按提示输入即可。
   3.2 恢复数据库
   恢复就是执行备份的 SQL 文件。
   方法一：使用 mysql 命令行客户端

   mysql -u learner -p my_learn_db < backup_20250318.sql

   如果备份文件包含 CREATE DATABASE 语句，可以先在 MySQL 中创建空数据库，或者使用 -D 指定数据库（文件内可能包含 USE 语句）。
   方法二：在 MySQL 内部使用 source 命令

   USE my_learn_db;
   SOURCE /home/learner/backup_20250318.sql;

   3.3 自动化定期备份（可选）
   可以写一个简单的 shell 脚本，配合 cron 实现每日备份。
   例如创建脚本 backup_mysql.sh：

   #!/bin/bash
   BACKUP_DIR="/home/learner/db_backups"
   DB_USER="learner"
   DB_PASS="YourPassword123!"
   DB_NAME="my_learn_db"
   DATE=$(date +%Y%m%d_%H%M%S)
   
   mysqldump -u $DB_USER -p$DB_PASS $DB_NAME > $BACKUP_DIR/${DB_NAME}_$DATE.sql
   # 可选：删除7天前的备份
   find $BACKUP_DIR -type f -name "*.sql" -mtime +7 -delete

   注意：在脚本中直接写密码不安全，可以使用 ~/.my.cnf 配置文件存放凭据，或使用更安全的方式。作为学习，了解即可。
   添加 cron 任务（每天凌晨2点执行）

   crontab -e
   \0 2 * * * /home/learner/backup_mysql.sh