创建和管理数据库

1. 概述

在本教程中，我们将学习如何创建和管理数据库。数据库是存储数据的仓库，而数据库管理系统（DBMS）则是用于管理这些数据的软件。我们将使用SQL（结构化查询语言）来与数据库进行交互。

2. 安装和配置数据库管理系统

2.1 选择数据库管理系统

常见的数据库管理系统包括MySQL、PostgreSQL、Oracle和SQL Server。对于初学者，我们推荐使用MySQL或PostgreSQL，因为它们易于安装和使用。

2.2 安装MySQL

以下是在Windows上安装MySQL的步骤：

1. 访问MySQL官方网站并下载安装程序。
2. 运行安装程序并按照提示进行安装。
3. 在安装过程中，设置root用户的密码。

2.3 配置MySQL

安装完成后，启动MySQL服务。你可以通过命令行或图形界面工具（如MySQL Workbench）来管理MySQL。

3. 基本SQL语法

3.1 连接到数据库

使用以下命令连接到MySQL数据库：

mysql -u root -p

系统会提示你输入root用户的密码。

3.2 创建数据库

使用CREATE DATABASE语句创建一个新的数据库：

CREATE DATABASE my_database;

3.3 选择数据库

使用USE语句选择要使用的数据库：

USE my_database;

4. 创建表 (CREATE TABLE)

4.1 表的基本结构

表是数据库中存储数据的基本单位。每个表由行和列组成。

4.2 创建表的语法

使用CREATE TABLE语句创建一个新表：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    salary DECIMAL(10, 2)
);

4.3 表的列类型

• INT：整数类型
• VARCHAR(n)：可变长度字符串，最大长度为n
• DECIMAL(p, s)：定点数，p为总位数，s为小数位数

5. 修改表结构 (ALTER TABLE)

5.1 添加列

使用ALTER TABLE语句添加新列：

ALTER TABLE employees ADD COLUMN department VARCHAR(50);

5.2 修改列

使用ALTER TABLE语句修改现有列：

ALTER TABLE employees MODIFY COLUMN age INT NOT NULL;

5.3 删除列

使用ALTER TABLE语句删除列：

ALTER TABLE employees DROP COLUMN salary;

6. 删除表 (DROP TABLE)

使用DROP TABLE语句删除表：

DROP TABLE employees;

7. 插入数据 (INSERT)

使用INSERT INTO语句向表中插入数据：

INSERT INTO employees (id, name, age, department)
VALUES (1, 'John Doe', 30, 'IT');

8. 更新数据 (UPDATE)

使用UPDATE语句更新表中的数据：

UPDATE employees SET age = 31 WHERE id = 1;

9. 删除数据 (DELETE)

使用DELETE FROM语句删除表中的数据：

DELETE FROM employees WHERE id = 1;

10. 基本查询 (SELECT)

使用SELECT语句从表中查询数据：

SELECT * FROM employees;

11. WHERE子句和条件过滤

使用WHERE子句过滤查询结果：

SELECT * FROM employees WHERE age > 30;

12. 排序 (ORDER BY)

使用ORDER BY子句对查询结果进行排序：

SELECT * FROM employees ORDER BY age DESC;

13. 分组 (GROUP BY)

使用GROUP BY子句对查询结果进行分组：

SELECT department, COUNT(*) FROM employees GROUP BY department;

14. 聚合函数 (SUM, AVG, COUNT, MAX, MIN)

使用聚合函数对数据进行汇总：

SELECT AVG(age) FROM employees;

15. HAVING子句

使用HAVING子句对分组后的结果进行过滤：

SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 1;

16. 子查询

子查询是一个嵌套在其他查询中的查询：

SELECT * FROM employees WHERE age > (SELECT AVG(age) FROM employees);

17. 表连接 (INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN)

使用连接操作合并多个表的数据：

SELECT employees.name, departments.name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;

18. 自连接

自连接是将表与自身连接的操作：

SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

19. UNION 和 UNION ALL

使用UNION或UNION ALL合并多个查询的结果：

SELECT name FROM employees
UNION
SELECT name FROM customers;

20. 相关子查询

相关子查询是依赖于外部查询的子查询：

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

21. 字符串函数

使用字符串函数处理字符串数据：

SELECT CONCAT(name, ' (', age, ')') FROM employees;

22. 数值函数

使用数值函数处理数值数据：

SELECT ABS(-10), ROUND(3.14159, 2);

23. 日期和时间函数

使用日期和时间函数处理日期和时间数据：

SELECT NOW(), DATE_FORMAT(NOW(), '%Y-%m-%d');

24. 条件表达式 (CASE WHEN)

使用CASE WHEN表达式进行条件判断：

SELECT name,
       CASE
           WHEN age < 30 THEN 'Young'
           WHEN age >= 30 AND age < 50 THEN 'Middle-aged'
           ELSE 'Old'
       END AS age_group
FROM employees;

25. 类型转换

使用类型转换函数将数据转换为不同的类型：

SELECT CAST('123' AS UNSIGNED);

26. 事务概念

事务是一组SQL语句的执行单元，要么全部执行成功，要么全部回滚。

27. ACID属性

• 原子性（Atomicity）：事务是不可分割的工作单位。
• 一致性（Consistency）：事务执行前后，数据库必须保持一致状态。
• 隔离性（Isolation）：并发执行的事务之间互不干扰。
• 持久性（Durability）：事务一旦提交，其结果是永久性的。

28. 事务控制语句 (BEGIN, COMMIT, ROLLBACK)

使用事务控制语句管理事务：

BEGIN;
UPDATE employees SET salary = salary + 1000 WHERE id = 1;
COMMIT;

29. 事务隔离级别

设置事务的隔离级别：

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

30. 索引类型和创建

创建索引以加快查询速度：

CREATE INDEX idx_name ON employees(name);

31. 执行计划分析

使用EXPLAIN语句分析查询的执行计划：

EXPLAIN SELECT * FROM employees WHERE age > 30;

32. 查询优化技巧

• 使用索引
• 减少子查询
• 避免全表扫描

33. 性能监控和调优

使用数据库的性能监控工具进行调优。

34. 创建和使用存储过程

创建存储过程以封装SQL代码：

DELIMITER //
CREATE PROCEDURE GetEmployeeCount()
BEGIN
    SELECT COUNT(*) FROM employees;
END //
DELIMITER ;

35. 创建和使用自定义函数

创建自定义函数以封装SQL逻辑：

CREATE FUNCTION GetEmployeeName(emp_id INT) RETURNS VARCHAR(100)
BEGIN
    RETURN (SELECT name FROM employees WHERE id = emp_id);
END;

36. 参数传递

在存储过程和函数中传递参数：

CREATE PROCEDURE GetEmployeeByDepartment(IN dept_name VARCHAR(50))
BEGIN
    SELECT * FROM employees WHERE department = dept_name;
END;

37. 错误处理

使用错误处理机制处理异常：

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    ROLLBACK;
    SELECT 'An error occurred';
END;

38. 触发器概念

触发器是在特定事件发生时自动执行的SQL代码。

39. 创建和管理触发器

创建触发器以响应表的插入、更新或删除操作：

CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    SET NEW.name = UPPER(NEW.name);
END;

40. 触发器类型 (BEFORE, AFTER, INSTEAD OF)

• BEFORE：在操作之前执行
• AFTER：在操作之后执行
• INSTEAD OF：替代操作执行

41. 触发器应用场景

触发器常用于数据验证、日志记录等场景。

42. 视图概念和用途

视图是虚拟表，基于查询结果。

43. 创建和管理视图

创建视图以简化复杂查询：

CREATE VIEW EmployeeView AS
SELECT id, name, age FROM employees;

44. 可更新视图

可更新视图允许对视图进行插入、更新和删除操作。

45. 物化视图 (特定数据库系统)

物化视图是预先计算并存储的视图，适用于频繁查询的场景。

46. 用户创建和管理

创建和管理数据库用户：

CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password';

47. 授权和撤销权限

授予和撤销用户权限：

GRANT SELECT, INSERT ON my_database.* TO 'new_user'@'localhost';
REVOKE INSERT ON my_database.* FROM 'new_user'@'localhost';

48. 角色管理

创建和管理角色以简化权限管理：

CREATE ROLE 'db_admin';
GRANT ALL ON my_database.* TO 'db_admin';
GRANT 'db_admin' TO 'new_user'@'localhost';

49. 数据加密

使用加密技术保护敏感数据。

50. 窗口函数

窗口函数用于对数据进行分组和排序：

SELECT name, salary,
       RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

51. 公共表表达式 (CTE)

使用CTE简化复杂查询：

WITH EmployeeCTE AS (
    SELECT id, name, age FROM employees
)
SELECT * FROM EmployeeCTE WHERE age > 30;

52. 递归查询

递归查询用于处理层次数据：

WITH RECURSIVE EmployeeHierarchy AS (
    SELECT id, name, manager_id FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id FROM employees e
    INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM EmployeeHierarchy;

53. PIVOT 和 UNPIVOT 操作

PIVOT和UNPIVOT用于数据透视和逆透视：

SELECT * FROM (
    SELECT department, age FROM employees
) AS SourceTable
PIVOT (
    COUNT(age) FOR department IN ('IT', 'HR', 'Finance')
);

54. 数据仓库架构

数据仓库是用于分析的大规模数据存储。

55. 维度建模

维度建模是数据仓库设计的一种方法。

56. ETL过程

ETL（提取、转换、加载）是将数据从源系统移动到数据仓库的过程。

57. OLAP操作

OLAP（联机分析处理）用于多维数据分析。

58. 关系型数据库与NoSQL

NoSQL数据库适用于非结构化数据。

59. JSON数据类型和操作

使用JSON数据类型存储和操作JSON数据：

CREATE TABLE events (
    id INT PRIMARY KEY,
    data JSON
);

60. 全文搜索

使用全文搜索功能进行文本搜索：

CREATE FULLTEXT INDEX idx_fulltext ON articles(content);
SELECT * FROM articles WHERE MATCH(content) AGAINST('search term');

61. 实体关系图 (ERD)

ERD用于可视化数据库设计。

62. 范式化和反范式化

范式化减少数据冗余，反范式化提高查询性能。

63. 设计模式和最佳实践

遵循设计模式和最佳实践以提高数据库设计的质量。

64. 备份和恢复

定期备份数据库以防止数据丢失。

65. 日志管理

管理数据库日志以跟踪操作和错误。

66. 性能监控

监控数据库性能以识别和解决问题。

67. 数据库迁移

将数据库从一个系统迁移到另一个系统。

68. 分片技术

分片是将数据分布到多个数据库实例的技术。

69. 分布式查询处理

在分布式数据库中处理查询。

70. 大规模数据处理策略

处理大规模数据的策略和技术。

71. ORM框架集成

使用ORM（对象关系映射）框架简化数据库操作。

72. 数据库连接池

使用连接池管理数据库连接。

73. 预处理语句和参数化查询

使用预处理语句和参数化查询防止SQL注入。

74. 数据库设计和实现

设计和实现数据库以满足业务需求。

75. 复杂查询和报表生成

生成复杂查询和报表以支持业务分析。

76. 数据仓库项目

实施数据仓库项目以支持企业决策。

77. 主流数据库比较 (MySQL, PostgreSQL, Oracle, SQL Server)

比较不同数据库的特性和适用场景。

78. SQL标准和方言

了解SQL标准和不同数据库的方言。

79. 持续学习和职业发展

持续学习新技术和最佳实践以提升职业发展。

---

通过本教程，你应该已经掌握了创建和管理数据库的基本技能。继续实践和探索，你将能够更深入地理解和应用这些知识。