MySQL数据库入门看这一篇就够（超级详细，从零学起）

学习MySQL数据库是一个循序渐进的过程，从基本的概念到高级的操作和优化都有很多知识点需要掌握。下面是一个详细的学习指南，涵盖了从基础到高级的内容。

一、MySQL基础知识

1.1 MySQL简介

MySQL是一个开源的关系型数据库管理系统（RDBMS），由瑞典公司MySQL AB开发，目前属于Oracle公司。它广泛应用于各种规模的应用程序中，特别是在Web开发领域。以下是MySQL的一些重要特性和优势：

1.1.1 开源性

MySQL是开源软件，这意味着它的源代码可以公开访问和修改。用户可以自由地下载、使用和分发MySQL，这使得它在开发者社区中非常受欢迎。

1.1.2 高性能和可靠性

MySQL以其高性能和可靠性而著称。它采用多线程、多用户架构，能够有效地处理大量并发连接和数据操作。此外，MySQL支持多种存储引擎，如InnoDB和MyISAM，允许用户根据具体需求选择最合适的存储引擎。

1.1.3 兼容性和移植性

MySQL支持多种操作系统，包括Windows、Linux、macOS和Unix。它还支持多种编程语言和API，如C、C++、Java、PHP、Python、Perl等，使得开发者可以在各种环境下方便地使用MySQL。

1.1.4 丰富的特性

MySQL提供了许多先进的数据库特性，包括：

• 事务支持：MySQL支持ACID事务管理，确保数据的完整性和一致性。
• 索引：MySQL支持多种索引类型，可以大大提高查询速度。
• 视图：视图允许用户创建虚拟表，简化复杂查询。
• 存储过程和触发器：MySQL支持存储过程和触发器，增强了数据库的功能性和灵活性。
• 全文检索：MySQL支持全文检索功能，特别适用于需要快速搜索文本内容的应用。

1.1.5 安全性

MySQL提供了多层次的安全机制，包括用户权限管理、SSL加密连接、数据加密等，确保数据的安全性和隐私性。

1.1.6 社区支持和生态系统

作为一种广泛使用的数据库系统，MySQL拥有庞大的用户社区和丰富的生态系统。用户可以方便地找到各种支持资源、教程和第三方工具，如MySQL Workbench（数据库设计和管理工具）、phpMyAdmin（基于Web的MySQL管理工具）等。

1.1.7 商业支持

除了开源版本，MySQL还提供了商业版本（MySQL Enterprise），包括额外的特性和支持服务，如更高级的监控和管理工具、技术支持等，适用于企业级应用。

1.1.8 使用场景

MySQL广泛应用于以下领域：

• Web开发：与Apache Web服务器和PHP脚本语言一起组成LAMP（Linux, Apache, MySQL, PHP/Perl/Python）堆栈，是Web开发的经典组合。
• 电子商务：由于其高性能和可靠性，MySQL常被用于电子商务网站的后台数据库。
• 数据仓库：MySQL的数据存储和查询能力使其适用于中小型数据仓库应用。
• 内容管理系统（CMS）：如WordPress、Drupal和Joomla等CMS系统广泛使用MySQL作为其数据库后端。

1.2 mysql发展史

MySQL的发展史可以追溯到20世纪90年代，经过多年发展，它已经成为全球最流行的开源数据库之一。下面是MySQL的发展历程中的一些重要里程碑和事件：

• 1994年 - MySQL的起源

• 开发起源：MySQL的历史始于瑞典的David Axmark、Allan Larsson和Michael “Monty” Widenius。他们在20世纪90年代初期开发了一个名为UNIREG的数据库工具。
• 创建MySQL：为了改进UNIREG并使其支持SQL查询语言，三人开始开发MySQL。第一个正式版本（MySQL 1.0）在1995年发布。

• 1995年 - 第一个公开发布
  MySQL 1.0发布：第一个公开版本发布，主要用作一个快速、稳定、开源的SQL数据库。

• 2000年 - MySQL AB成立
  MySQL AB成立：为了更好地推动MySQL的发展，开发团队成立了MySQL AB公司，专注于MySQL的开发和商业化。

• 2001年 - MySQL 3.23发布
  MySQL 3.23：这个版本引入了许多关键特性，包括BLOB数据类型、全文索引和内部数据缓存机制。

• 2003年 - MySQL 4.0发布
  MySQL 4.0：该版本引入了事务支持（通过InnoDB存储引擎）、外键支持和更好的性能优化。

• 2004年 - MySQL 4.1和MySQL 5.0发布
  MySQL 4.1：增加了子查询、联合查询和Unicode支持。
  MySQL 5.0：引入了存储过程、触发器、视图和游标，极大地增强了MySQL的功能。

• 2008年 - 被Sun Microsystems收购
  收购：MySQL AB被Sun Microsystems以10亿美元收购。这次收购进一步加速了MySQL的发展，并增强了其在企业市场的地位。

• 2010年 - Oracle收购Sun Microsystems
  Oracle收购：Oracle收购Sun Microsystems，将MySQL纳入其广泛的数据库产品组合中。尽管有一些担忧，MySQL继续作为一个独立的开源项目进行开发。

• 2013年 - MySQL 5.6发布
  MySQL 5.6：改进了性能、可扩展性和安全性，引入了NoSQL接口（通过Memcached API）和全局事务管理器（GTID）。

• 2015年 - MySQL 5.7发布
  MySQL 5.7：进一步提升了性能和可扩展性，增加了JSON数据类型、虚拟列、地理空间支持和更好的优化器。

• 2018年 - MySQL 8.0发布
  MySQL 8.0：这是一个重要的里程碑版本，带来了众多新特性和改进，包括窗口函数、CTE（公共表表达式）、更强的JSON支持、原子DDL操作、改进的并发控制和安全性增强。

• 近期发展
  持续优化和新特性：近年来，MySQL持续改进性能、增加新特性并增强安全性。MySQL团队专注于保持其作为全球领先开源数据库的地位，同时应对现代数据管理需求。

二、mysql数据库环境搭建（重要！！！）

2.1 ubuntu系统安装mysql数据库

系统版本：Ubuntu 20.04.6 LTS
MySql数据版本：8.0.37

2.1.1 更新系统软件包

首先，更新您的系统软件包，以确保您拥有最新的软件包信息。

sudo apt update
1

2.1.2 安装MySQL服务器

使用apt命令来安装MySQL服务器。

sudo apt install mysql-server -y 
1

2.1.3 启动MySQL服务

安装完成后，启动MySQL服务并设置为开机自启动。

# 设置mysql服务启动
sudo systemctl start mysql
# 设置mysql开启自动启动
sudo systemctl enable mysql
1
2
3
4

2.1.4 四、运行安全性脚本

MySQL提供了一个安全性脚本，mysql_secure_installation，可以帮助您提高数据库的安全性。运行该脚本并根据提示进行操作。

sudo mysql_secure_installation
1

设置密码的复杂度。这里测试使用0

是否移除默认用户，输入y

是否禁止root用户远程登录，测试使用因此输入：n（允许远程登录）

是否删除测试数据，输入：y

2.1.5 验证MySQL服务状态

确保MySQL服务正在运行。

sudo systemctl status mysql
1

如果服务正在运行，您会看到类似于以下的输出：

2.1.6 登录MySQL

使用root用户登录MySQL。

sudo mysql -u root -p
1

第一次进入mysql不需要输入密码。进入之后我们可以修改root密码。

2.1.7 修改root用户密码

设置密码为12345678
登录到数据库中后查看用户信息

select user,host from mysql.user;
1

修改密码指令：

ALTER USER 'root'@'localhost' IDENTIFIED BY '12345678';
FLUSH PRIVILEGES;
1
2

2.2 window系统安装mysql数据库

在Windows 10上安装MySQL 8数据库的步骤如下。该过程包括下载MySQL安装程序、安装MySQL服务器、进行基本配置以及验证安装。

一、下载MySQL安装程序

1. 访问MySQL官网：访问 MySQL官网。
2. 选择操作系统：在下载页面中选择“Microsoft Windows”。
3. 选择安装包：选择“Windows (x86, 64-bit), MSI Installer”并点击下载。
   
   安装 过程略…

三、可视化mysql

3.1 安装navicate for mysql

安装后可以试用14天

3.2 navicate 链接数据库

输入对应的服务器的ip地址，端口默认3306，用户名称以及密码。
远程服务器链接的时候需要注意，服务器是否开启防火墙，需要关闭防火墙才能链接

3.3 创建数据库

右键链接信息，新建数据库

数据数据库名称，使用纯英文，字符集推荐使用utf8mb4,点击确定就可以了。

3.4 创建表

mysql服务可以创建多个数据库，每一个数据库可以创建多个表，右键，新建表

一个表有很多字段，需要填写字段的名称，类型，长度，是否为空。

这个表有四个字段，id为主键。

点击保存的时候需要输入表名。点击确定保存

双击获取表的内容。

四、Mysql数据库操作（重中之重！）

以下内容是通过命令操作数据，这个是必学内容，因为在编写代码的时候都是用sql语句进行控制数据库的数据

4.1 登录数据库

mysql -u root -p 
1

4.2 数据库和表的基本操作

4.2.1 创建数据库：

CREATE DATABASE database_name;
1

4.2.2 显示数据库

SHOW DATABASE;
1

4.2.3 选择数据库

USE database_name;
1

4.2.4 删除数据库

DROP DATABASE database_name;
1

4.2.5 创建表

在MySQL中，创建表的语法结构较为灵活和强大，可以定义各种数据类型、约束和索引。下面是MySQL创建表的语法结构详解，包括示例和常用选项。

• 基本语法结构

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
    columnN datatype constraints,
    table_constraints
);
1
2
3
4
5
6
7

• 详细说明

  • table_name：表的名称。
  • column1, column2, … columnN：表的列定义，每一列都需要指定名称、数据类型和约束（可选）。
  • datatype：列的数据类型，如INT, VARCHAR, DATE等。
  • constraints：列级约束，如PRIMARY KEY, UNIQUE, NOT NULL, DEFAULT等。
  • table_constraints：表级约束，如PRIMARY KEY, FOREIGN KEY等。

• 常见的数据类型

  • INT：整数类型。
  • VARCHAR(size)：可变长度字符串。
  • CHAR(size)：固定长度字符串。
  • DATE：日期类型。
  • DATETIME：日期和时间类型。
  • DECIMAL(p, s)：定点数，p表示总位数，s表示小数位数。

• 常见的列级约束

  • PRIMARY KEY：主键，唯一标识表中的每一行。
  • UNIQUE：唯一约束，确保列中的所有值唯一。
  • NOT NULL：非空约束，确保列中不能有NULL值。
  • DEFAULT：默认值，指定列的默认值。
  • AUTO_INCREMENT：自动递增，一般用于主键。

• 示例

假设我们要创建一个名为 employees 的表，具体要求如下：

• id：员工ID，整数类型，主键，自增。
• first_name：员工的名字，字符串类型，不为空。
• last_name：员工的姓氏，字符串类型，不为空。
• email：员工的电子邮件，字符串类型，唯一，不为空。
• hire_date：员工的入职日期，日期类型，不为空。
• job_title：员工的职位，字符串类型。

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    hire_date DATE NOT NULL,
    job_title VARCHAR(50) NOT NULL
);
1
2
3
4
5
6
7
8

• 外键约束

  如果我们有另一个表 departments，并希望在 employees 表中建立外键引用：

  1. 创建 departments 表：

  CREATE TABLE departments (
      department_id INT AUTO_INCREMENT PRIMARY KEY,
      department_name VARCHAR(100) NOT NULL
  );
  1
  2
  3
  4

  2. 修改 employees 表，添加外键：

  假设每个员工都属于一个部门，我们在 employees 表中添加 department_id 列并设置外键：

  CREATE TABLE employees (
      id INT AUTO_INCREMENT PRIMARY KEY,
      first_name VARCHAR(50) NOT NULL,
      last_name VARCHAR(50) NOT NULL,
      email VARCHAR(100) NOT NULL UNIQUE,
      hire_date DATE NOT NULL,
      job_title VARCHAR(50) NOT NULL,
      department_id INT,
      FOREIGN KEY (department_id) REFERENCES departments(department_id)
  );
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10

• 表级约束示例

  1. 主键约束：

  如果需要定义复合主键，可以使用表级约束：

  CREATE TABLE project_assignments (
      project_id INT,
      employee_id INT,
      role VARCHAR(50),
      PRIMARY KEY (project_id, employee_id)
  );
  1
  2
  3
  4
  5
  6

  2. 外键约束：

  定义外键约束：

  CREATE TABLE project_assignments (
      project_id INT,
      employee_id INT,
      role VARCHAR(50),
      PRIMARY KEY (project_id, employee_id),
      FOREIGN KEY (project_id) REFERENCES projects(project_id),
      FOREIGN KEY (employee_id) REFERENCES employees(id)
  );
  1
  2
  3
  4
  5
  6
  7
  8

• 创建表的完整示例

  以下是一个包含多种约束和数据类型的完整示例：

  CREATE TABLE orders (
      order_id INT AUTO_INCREMENT PRIMARY KEY,
      order_date DATETIME NOT NULL,
      customer_id INT NOT NULL,
      total_amount DECIMAL(10, 2) NOT NULL,
      status ENUM('Pending', 'Shipped', 'Completed', 'Cancelled') DEFAULT 'Pending',
      FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
  );
  1
  2
  3
  4
  5
  6
  7
  8

4.2.6 删除表：

DROP TABLE table_name;
1

在MySQL中创建表是数据库管理的基本操作之一。以下是如何创建一个表的详细实例，包括指定数据类型、设置主键、定义外键和其他约束条件的步骤。

4.3 Mysql数据类型

• 常见数据类型：
  • 数值类型：INT, FLOAT, DOUBLE, DECIMAL
  • 字符类型：CHAR, VARCHAR, TEXT
  • 日期和时间类型：DATE, TIME, DATETIME, TIMESTAMP
  • 布尔类型：BOOLEAN (在MySQL中通常使用TINYINT(1))
    MySQL 支持多种数据类型，每种类型适用于不同的存储和处理需求。以下是 MySQL 数据类型的详细介绍，包括数值类型、日期和时间类型、字符串类型、以及 JSON 类型等。

4.3.1 数值类型

1. 整数类型

• TINYINT: 小整数，范围 -128 到 127 或 0 到 255。
• SMALLINT: 小整数，范围 -32,768 到 32,767 或 0 到 65,535。
• MEDIUMINT: 中等大小整数，范围 -8,388,608 到 8,388,607 或 0 到 16,777,215。
• INT 或 INTEGER: 标准整数，范围 -2,147,483,648 到 2,147,483,647 或 0 到 4,294,967,295。
• BIGINT: 大整数，范围 -9,223,372,036,854,775,808 到 9,223,372,036,854,775,807 或 0 到 18,446,744,073,709,551,615。

可以使用 UNSIGNED 属性将类型限制为非负数。

CREATE TABLE example (
    tiny TINYINT,
    small SMALLINT,
    medium MEDIUMINT,
    standard INT,
    big BIGINT
);
1
2
3
4
5
6
7

2. 浮点类型

• FLOAT§: 单精度浮点数，p 表示有效位数。
• DOUBLE: 双精度浮点数。
• DECIMAL(M,D) 或 NUMERIC(M,D): 定点数，M 表示总位数，D 表示小数位数。

CREATE TABLE example (
    float_example FLOAT(7,4),
    double_example DOUBLE,
    decimal_example DECIMAL(10,2)
);
1
2
3
4
5

4.3.2 日期和时间类型

• DATE: 日期，格式为 ‘YYYY-MM-DD’，范围从 ‘1000-01-01’ 到 ‘9999-12-31’。
• TIME: 时间，格式为 ‘HH:MM:SS’，范围从 ‘-838:59:59’ 到 ‘838:59:59’。
• DATETIME: 日期和时间，格式为 ‘YYYY-MM-DD HH:MM:SS’，范围从 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’。
• TIMESTAMP: 时间戳，格式为 ‘YYYY-MM-DD HH:MM:SS’，范围从 ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-19 03:14:07’ UTC。
• YEAR: 年，格式为 ‘YYYY’，范围从 ‘1901’ 到 ‘2155’。

CREATE TABLE example (
    date_example DATE,
    time_example TIME,
    datetime_example DATETIME,
    timestamp_example TIMESTAMP,
    year_example YEAR
);
1
2
3
4
5
6
7

4.3.3 字符串类型

• CHAR(M): 固定长度字符串，最多 255 个字符。
• VARCHAR(M): 可变长度字符串，最多 65,535 个字符。
• BINARY(M): 固定长度二进制数据，最多 255 个字节。
• VARBINARY(M): 可变长度二进制数据，最多 65,535 个字节。
• TINYBLOB: 不超过 255 个字节的二进制大对象。
• BLOB: 二进制大对象，最多 65,535 个字节。
• MEDIUMBLOB: 二进制大对象，最多 16,777,215 个字节。
• LONGBLOB: 二进制大对象，最多 4,294,967,295 个字节。
• TINYTEXT: 短文本字符串，最多 255 个字符。
• TEXT: 文本字符串，最多 65,535 个字符。
• MEDIUMTEXT: 文本字符串，最多 16,777,215 个字符。
• LONGTEXT: 文本字符串，最多 4,294,967,295 个字符。
• ENUM: 枚举类型，字符串对象可以具有一个值，这个值来自于值列表 ‘value1’, ‘value2’, …, ‘valueN’。
• SET: 集合类型，字符串对象可以有零个或多个值，这些值从值列表 ‘value1’, ‘value2’, …, ‘valueN’ 中选出。

CREATE TABLE example (
    char_example CHAR(10),
    varchar_example VARCHAR(255),
    binary_example BINARY(20),
    varbinary_example VARBINARY(255),
    tinytext_example TINYTEXT,
    text_example TEXT,
    mediumtext_example MEDIUMTEXT,
    longtext_example LONGTEXT,
    tinyblob_example TINYBLOB,
    blob_example BLOB,
    mediumblob_example MEDIUMBLOB,
    longblob_example LONGBLOB,
    enum_example ENUM('value1', 'value2', 'value3'),
    set_example SET('value1', 'value2', 'value3')
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

4.3.4 JSON 类型

• JSON: 用于存储 JSON 文档，MySQL 5.7.8 及以上版本支持。

CREATE TABLE example (
    json_example JSON
);
1
2
3

4.3.5 空间数据类型

MySQL 还支持用于地理信息系统 (GIS) 的空间数据类型：

• GEOMETRY: 可以存储任何类型的几何数据。
• POINT: 存储一个点 (X, Y)。
• LINESTRING: 存储一条线。
• POLYGON: 存储一个多边形。
• MULTIPOINT: 存储多个点。
• MULTILINESTRING: 存储多条线。
• MULTIPOLYGON: 存储多个多边形。
• GEOMETRYCOLLECTION: 存储多个几何对象的集合。

CREATE TABLE example (
    geometry_example GEOMETRY,
    point_example POINT,
    linestring_example LINESTRING,
    polygon_example POLYGON
);
1
2
3
4
5
6

五、数据操作

5.1 mysql插入数据

在MySQL中，插入数据的操作主要使用 INSERT INTO 语句。INSERT INTO 语句可以插入单行数据、多行数据，或者从一个表中选择数据插入到另一个表中。以下是 INSERT INTO 语句的语法结构以及案例讲解。

5.1.1 基本语法结构

• 插入单行数据

  INSERT INTO table_name (column1, column2, column3, ...)
  VALUES (value1, value2, value3, ...);
  1
  2

  • table_name：表的名称。
  • column1, column2, column3, …：要插入数据的列名。
  • value1, value2, value3, …：对应列的值。

5.1.2 插入多行数据

INSERT INTO table_name (column1, column2, column3, ...)
VALUES 
(value1, value2, value3, ...),
(value4, value5, value6, ...),
...;
1
2
3
4
5

5.1.3 插入数据并选择

INSERT INTO table_name (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM another_table
WHERE condition;
1
2
3
4

+示例

假设我们有一个名为 employees 的表，结构如下：

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    hire_date DATE NOT NULL,
    job_title VARCHAR(50) NOT NULL
);
1
2
3
4
5
6
7
8

1. 插入单行数据

INSERT INTO employees (first_name, last_name, email, hire_date, job_title)
VALUES ('John', 'Doe', 'john.doe@example.com', '2022-01-15', 'Software Engineer');
1
2

2. 插入多行数据

INSERT INTO employees (first_name, last_name, email, hire_date, job_title)
VALUES
('Jane', 'Smith', 'jane.smith@example.com', '2023-03-22', 'Data Scientist'),
('Mike', 'Johnson', 'mike.johnson@example.com', '2021-07-10', 'Project Manager'),
('Alice', 'Williams', 'alice.williams@example.com', '2022-08-01', 'UI/UX Designer');
1
2
3
4
5

3. 插入数据并选择

假设我们有另一个名为 new_employees 的表，并且想将其中的数据插入到 employees 表中：

CREATE TABLE new_employees (
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    hire_date DATE,
    job_title VARCHAR(50)
);

INSERT INTO new_employees (first_name, last_name, email, hire_date, job_title)
VALUES
('Tom', 'Brown', 'tom.brown@example.com', '2024-01-10', 'Network Engineer'),
('Emma', 'Davis', 'emma.davis@example.com', '2023-12-20', 'Marketing Specialist');
1
2
3
4
5
6
7
8
9
10
11
12

将 new_employees 表中的数据插入到 employees 表中：

INSERT INTO employees (first_name, last_name, email, hire_date, job_title)
SELECT first_name, last_name, email, hire_date, job_title
FROM new_employees;
1
2
3

5.1.4 插入数据的注意事项

1. 确保数据类型匹配：插入的数据类型应该与表中定义的列的数据类型匹配。
2. 处理 NULL 值：如果某列允许 NULL 值，可以显式插入 NULL。
3. 避免重复数据：如果表中的某列有唯一约束（如 email），插入时要确保该列的值不重复，否则会导致错误。
4. 自动递增列：对于 AUTO_INCREMENT 列，不需要在 INSERT 语句中指定该列的值，MySQL 会自动生成。
5. 使用事务：对于批量插入或需要确保数据一致性的操作，可以使用事务来保证插入操作的原子性。

5.1.6 创建一个简单的商品表并插入数据

假设我们要创建一个名为 products 的表，并插入一些商品数据：

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    quantity INT NOT NULL
);

INSERT INTO products (product_name, price, quantity)
VALUES
('Laptop', 899.99, 50),
('Smartphone', 499.99, 200),
('Tablet', 299.99, 150);
1
2
3
4
5
6
7
8
9
10
11
12

查询 products 表中的数据：

SELECT * FROM products;
1

通过上述语法和示例，您可以熟练地在 MySQL 中插入数据，满足不同的业务需求。

5.2 mysql查询数据

在MySQL中，查询数据是使用 SELECT 语句进行的。SELECT 语句的基本语法结构以及常见用法和实例如下所述。

5.2.1 基本语法结构

SELECT column1, column2, ...
FROM table_name
WHERE condition
GROUP BY column
HAVING condition
ORDER BY column
LIMIT number;
1
2
3
4
5
6
7

5.2.2 详细说明

• SELECT column1, column2, …: 指定要查询的列。可以使用 * 查询所有列。
• FROM table_name: 指定要查询的表。
• WHERE condition: 指定过滤条件，返回满足条件的行。
• GROUP BY column: 按列分组，用于聚合函数（如 COUNT, SUM, AVG 等）。
• HAVING condition: 对分组后的结果进行过滤。
• ORDER BY column: 按列排序。默认升序排列，使用 DESC 表示降序。
• LIMIT number: 指定返回的行数。

假设我们有一个名为 employees 的表，结构如下：

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    hire_date DATE NOT NULL,
    job_title VARCHAR(50) NOT NULL,
    salary DECIMAL(10, 2) NOT NULL
);
1
2
3
4
5
6
7
8
9

5.2.2.1. 查询所有列

查询 employees 表的所有列：

SELECT * FROM employees;
1

5.2.2.2. 查询特定列

只查询 first_name 和 last_name 列：

SELECT first_name, last_name FROM employees;
1

5.2.2.3. 使用 WHERE 过滤条件

查询职位为 Software Engineer 的员工：

SELECT * FROM employees WHERE job_title = 'Software Engineer';
1

查询工资大于 50000 的员工：

SELECT * FROM employees WHERE salary > 50000;
1

5.2.2.4. 使用 ORDER BY 排序

按 hire_date 升序排序查询结果：

SELECT * FROM employees ORDER BY hire_date;
1

按 salary 降序排序查询结果：

SELECT * FROM employees ORDER BY salary DESC;
1

5.2.2.5. 使用 LIMIT 限制返回行数

查询前5条记录：

SELECT * FROM employees LIMIT 5;
1

5.2.2.6. 使用聚合函数和 GROUP BY

查询每个职位的平均工资：

SELECT job_title, AVG(salary) AS average_salary
FROM employees
GROUP BY job_title;
1
2
3

查询每个职位的员工数量：

SELECT job_title, COUNT(*) AS number_of_employees
FROM employees
GROUP BY job_title;
1
2
3

5.2.2.7. 使用 HAVING 过滤分组结果

查询员工数量大于1的职位及其员工数量：

SELECT job_title, COUNT(*) AS number_of_employees
FROM employees
GROUP BY job_title
HAVING COUNT(*) > 1;
1
2
3
4

5.2.2.8. 使用 JOIN 进行表连接

假设我们有另一个表 departments，结构如下：

CREATE TABLE departments (
    department_id INT AUTO_INCREMENT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);
1
2
3
4

在 employees 表中增加一个 department_id 列：

ALTER TABLE employees ADD department_id INT;
1

插入一些数据：

INSERT INTO departments (department_name) VALUES ('Engineering'), ('Marketing'), ('Sales');

UPDATE employees SET department_id = 1 WHERE job_title = 'Software Engineer';
UPDATE employees SET department_id = 2 WHERE job_title = 'Marketing Specialist';
UPDATE employees SET department_id = 3 WHERE job_title = 'Sales Representative';
1
2
3
4
5

查询员工及其所属部门的信息：

SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
JOIN departments ON employees.department_id = departments.department_id;
1
2
3

5.2.2.9. 子查询

查询工资最高的员工：

SELECT * FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);
1
2

查询平均工资以上的员工：

SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
1
2

5.3 更新数据

• 更新特定行：

  UPDATE table_name
  SET column1 = value1, column2 = value2, ...
  WHERE condition;
  1
  2
  3

5.4 删除数据

• 删除特定行：

  DELETE FROM table_name
  WHERE condition;
  1
  2

六、高级操作

6.1 事务管理

• 开始事务：

  START TRANSACTION;
  1

• 提交事务：

  COMMIT;
  1

• 回滚事务：

  ROLLBACK;
  1

6.2. 索引

• 创建索引：

  CREATE INDEX index_name
  ON table_name (column1, column2, ...);
  1
  2

• 删除索引：

  DROP INDEX index_name
  ON table_name;
  1
  2

6.3. 视图

• 创建视图：

  CREATE VIEW view_name AS
  SELECT column1, column2, ...
  FROM table_name
  WHERE condition;
  1
  2
  3
  4

• 删除视图：

  DROP VIEW view_name;
  1

6.4 mysql常用统计函数

在MySQL中，常用的统计函数有助于对数据进行汇总、计算和分析。这些函数包括 COUNT(), SUM(), AVG(), MIN(), MAX() 等。下面是这些常用统计函数的说明和实例详解。

6.4.1. COUNT() 函数

功能: 计算表中某列的行数。

语法:

COUNT(column_name)
1

实例: 计算 employees 表中员工的总数。

SELECT COUNT(*) AS total_employees FROM employees;
1

计算 employees 表中每个职位的员工数量：

SELECT job_title, COUNT(*) AS number_of_employees
FROM employees
GROUP BY job_title;
1
2
3

6.4.2. SUM() 函数

功能: 计算某列的总和。

语法:

SUM(column_name)
1

实例: 计算 employees 表中所有员工的总工资。

SELECT SUM(salary) AS total_salary FROM employees;
1

计算每个职位的总工资：

SELECT job_title, SUM(salary) AS total_salary
FROM employees
GROUP BY job_title;
1
2
3

6.4.3. AVG() 函数

功能: 计算某列的平均值。

语法:

AVG(column_name)
1

实例: 计算 employees 表中所有员工的平均工资。

SELECT AVG(salary) AS average_salary FROM employees;
1

计算每个职位的平均工资：

SELECT job_title, AVG(salary) AS average_salary
FROM employees
GROUP BY job_title;
1
2
3

6.4.4. MIN() 函数

功能: 找出某列的最小值。

语法:

MIN(column_name)
1

实例: 查找 employees 表中最低的工资。

SELECT MIN(salary) AS minimum_salary FROM employees;
1

查找每个职位的最低工资：

SELECT job_title, MIN(salary) AS minimum_salary
FROM employees
GROUP BY job_title;
1
2
3

6.4.5. MAX() 函数

功能: 找出某列的最大值。

语法:

MAX(column_name)
1

实例: 查找 employees 表中最高的工资。

SELECT MAX(salary) AS maximum_salary FROM employees;
1

查找每个职位的最高工资：

SELECT job_title, MAX(salary) AS maximum_salary
FROM employees
GROUP BY job_title;
1
2
3

6.4.6 综合实例

假设我们有一个 sales 表，结构如下：

CREATE TABLE sales (
    sale_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    sale_date DATE NOT NULL
);
1
2
3
4
5
6
7

插入一些数据：

INSERT INTO sales (product_name, quantity, price, sale_date)
VALUES
('Laptop', 5, 899.99, '2023-01-15'),
('Smartphone', 10, 499.99, '2023-01-16'),
('Tablet', 8, 299.99, '2023-01-17'),
('Laptop', 3, 899.99, '2023-01-18'),
('Smartphone', 6, 499.99, '2023-01-19');
1
2
3
4
5
6
7

1. 计算总销售额

SELECT SUM(quantity * price) AS total_sales FROM sales;
1

2. 计算每种产品的总销售额

SELECT product_name, SUM(quantity * price) AS total_sales
FROM sales
GROUP BY product_name;
1
2
3

3. 查找销售数量最多的产品

SELECT product_name, MAX(quantity) AS max_quantity_sold
FROM sales
GROUP BY product_name;
1
2
3

4. 计算平均销售价格

SELECT AVG(price) AS average_price FROM sales;
1

5. 查找销售日期最早和最晚的记录

SELECT MIN(sale_date) AS earliest_sale_date, MAX(sale_date) AS latest_sale_date FROM sales;
1

七、备份和恢复

7.1 备份数据库

• 使用mysqldump：

  mysqldump -u username -p database_name > backup_file.sql
  1

7.2 恢复数据库

• 导入SQL文件：

  mysql -u username -p database_name < backup_file.sql
  1

七、常用工具

1. 图形化工具

• MySQL Workbench：官方提供的综合性数据库设计和管理工具。
• phpMyAdmin：基于Web的MySQL管理工具。

2. 命令行工具

• mysql：命令行客户端。
• mysqldump：备份工具。

八、学习资源

8.1. 官方文档

• MySQL官方文档

8.2. 书籍

• 《MySQL必知必会》
• 《高性能MySQL》

通过系统化的学习和实践，你将能够熟练掌握MySQL数据库的各个方面。多动手操作，多查阅资料，可以帮助你更好地理解和应用所学知识。