一、基础知识

数据库概述

数据库是一种有组织的数据集合，通常电子化，并存储在计算机系统中，数据库管理系统（DBMS）用于管理和操作这些数据，常见的数据库系统包括MySQL、PostgreSQL、Oracle和SQL Server等。

关系数据库

关系数据库使用表格来表示数据及其相互关系，表由行和列组成，行即记录，列即字段，关系数据库通过外键来实现表之间的关联。

SQL简介

SQL（Structured Query Language）是用于管理和操作关系数据库的标准化语言，SQL分为以下几类：

DDL (数据定义语言)：用于定义和修改数据库结构，例如CREATE、ALTER、DROP。

DML (数据操纵语言)：用于插入、更新、删除和选择数据，例如INSERT、UPDATE、DELETE、SELECT。

DCL (数据控制语言)：用于控制对数据库的访问权限，例如GRANT、REVOKE。

TCL (事务控制语言)：用于管理数据库事务，例如COMMIT、ROLLBACK。

二、MySQL安装与配置

安装MySQL

在Windows、macOS和Linux上安装MySQL可以通过官方网站下载相应的安装包，以Windows为例：

1、下载MySQL Community Installer。

2、运行安装程序，按照指引完成安装。

3、在安装过程中设置root用户密码。

配置MySQL

MySQL安装完成后，需进行基本配置：

1、登录MySQL：mysql -u root -p

2、创建新用户并授权：

   CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
   GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' WITH GRANT OPTION;
   FLUSH PRIVILEGES;

3、创建数据库：

   CREATE DATABASE dbname;

4、使用数据库：

   USE dbname;

三、数据库设计与管理

设计数据库

1.1 需求分析

首先明确应用的需求和业务流程，确定需要存储的数据种类及其相互关系。

1.2 建立ER模型

实体-联系（ER）图是数据库设计的图形化表示方法，用于描述数据的结构和关系。

1.3 创建数据库和表

根据ER模型创建数据库和表。

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

数据类型与约束

2.1 数据类型

常见的数据类型包括整数型（INT）、浮点型（FLOAT）、字符型（VARCHAR）、日期型（DATE）等，选择合适的数据类型可以优化存储效率。

2.2 约束

约束用于维护数据的完整性和一致性，例如主键（PRIMARY KEY）、外键（FOREIGN KEY）、唯一（UNIQUE）、非空（NOT NULL）。

四、数据操作

插入数据

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

INSERT INTO users (username, password, email) VALUES ('john_doe', 'securepassword', 'john@example.com');

更新数据

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

UPDATE users SET email = 'newemail@example.com' WHERE username = 'john_doe';

删除数据

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

DELETE FROM users WHERE username = 'john_doe';

查询数据

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

SELECT * FROM users;

可以使用WHERE子句添加条件：

SELECT * FROM users WHERE username = 'john_doe';

五、高级特性

索引

索引用于加速数据查询，可以在列上创建索引：

CREATE INDEX idx_username ON users(username);

视图

视图是基于一个或多个表的逻辑表现形式：

CREATE VIEW active_users AS
SELECT * FROM users WHERE status = 'active';

存储过程

存储过程是预编译的SQL代码块，可以复杂运算和逻辑处理：

DELIMITER //
CREATE PROCEDURE GetUserById(IN userId INT)
BEGIN
    SELECT * FROM users WHERE user_id = userId;
END //
DELIMITER ;

触发器

触发器在特定事件发生时自动执行：

CREATE TRIGGER before_insert_user
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    SET NEW.created_at = NOW();
END;

六、实例教程：构建简单的学生管理系统

数据库设计

设计数据库来存储学生信息和课程信息，创建两个表：students和courses。

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    enrollment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL,
    instructor VARCHAR(100) NOT NULL
);

数据操作

插入一些初始数据：

INSERT INTO students (name, email) VALUES ('John Doe', 'john@example.com'), ('Jane Smith', 'jane@example.com');
INSERT INTO courses (course_name, instructor) VALUES ('Mathematics', 'Prof. Smith'), ('Science', 'Prof. Johnson');

查询学生信息：

SELECT * FROM students;

更新与删除数据

更新学生邮箱：

UPDATE students SET email = 'newemail@example.com' WHERE name = 'John Doe';

删除一门课程：

DELETE FROM courses WHERE course_name = 'Mathematics';

使用视图和存储过程

创建一个只读当前活跃学生的视图：

CREATE VIEW active_students AS
SELECT * FROM students WHERE enrollment_date >= NOW() - INTERVAL 1 YEAR;

创建一个存储过程来添加新课程：

DELIMITER //
CREATE PROCEDURE AddNewCourse(IN courseName VARCHAR(100), IN instructorName VARCHAR(100))
BEGIN
    INSERT INTO courses (course_name, instructor) VALUES (courseName, instructorName);
END //
DELIMITER ;