第三章掌握MySQL数据库的基本操作

文章目录

一、关系数据库标准语言SQL
- 1.1 SQL的发展历史与特点
- 1.2 SQL的分类
二、数据库的管理
- 2.1 创建数据库
- 2.2 查看数据库
- 2.3 选择数据库
- 2.4 删除数据库
三、MySQL存储引擎
- 3.1 MySQL支持的存储引擎
- 3.2 InnoDB存储引擎
- 3.3 MyISAM存储引擎
- 3.4 选择存储引擎
四、表的管理
- 4.1 数据类型
- 4.2 创建表
- 4.3 查看表结构
- 4.4 修改表
- 4.5 删除表
五、数据维护
- 5.1 插入数据
- 5.2 更新数据
- 5.3 删除数据
六、数据查询
- 6.1 基本查询
- 6.2 分组查询
- 6.3 连接查询
- 6.4 合并查询结果
七、索引和视图
- 7.1 索引
- 7.2 视图
八、结论

MySQL是当今最流行的关系型数据库管理系统之一，广泛应用于各种Web应用和数据分析平台。在本文中，我们将详细介绍MySQL数据库的基本操作，包括SQL语言、数据库管理、存储引擎、表管理、数据维护、数据查询以及索引和视图的创建和管理。

一、关系数据库标准语言SQL

SQL（Structured Query Language）是关系数据库的标准语言，用于在关系数据库中进行数据查询、数据操作、数据定义和数据访问控制。

1.1 SQL的发展历史与特点

SQL最初在1970年代由IBM的研究人员开发，目的是为了方便地管理和操作关系型数据库。SQL的特点包括：

声明性：SQL通过声明性语言描述要执行的操作，而不是程序的详细步骤。
结构化：SQL操作基于关系模型，数据以表格的形式存储。
高度非过程化：SQL允许用户编写非过程化的查询，减少了编程复杂性。

1.2 SQL的分类

SQL可以分为以下几类：

数据定义语言（DDL）：用于定义数据库结构，如CREATE、ALTER和DROP。
数据操作语言（DML）：用于操作数据，如INSERT、UPDATE和DELETE。
数据控制语言（DCL）：用于定义数据库的访问权限，如GRANT和REVOKE。
事务控制语言（TCL）：用于管理数据库事务，如COMMIT和ROLLBACK。

代码示例：

-- DDL: 创建表
CREATE TABLE users (id INT AUTO_INCREMENT,name VARCHAR(100),email VARCHAR(100),PRIMARY KEY (id)
);-- DML: 插入数据
INSERT INTO users (name, email) VALUES ('Kimi', 'kimi@example.com');-- DCL: 授权
GRANT SELECT ON database.users TO 'user'@'localhost';-- TCL: 提交事务
COMMIT;

二、数据库的管理

数据库管理包括创建数据库、查看数据库、选择数据库和删除数据库。

2.1 创建数据库

使用CREATE DATABASE语句创建数据库。

代码示例：

CREATE DATABASE mydatabase;

执行结果：

Database 'mydatabase' created successfully.

2.2 查看数据库

使用SHOW DATABASES语句查看数据库列表。

代码示例：

SHOW DATABASES;

执行结果：

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydatabase         |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

2.3 选择数据库

使用USE语句选择要操作的数据库。

代码示例：

USE mydatabase;

执行结果：

Database switched successfully.

2.4 删除数据库

使用DROP DATABASE语句删除数据库。

代码示例：

DROP DATABASE mydatabase;

执行结果：

Database 'mydatabase' dropped successfully.

三、MySQL存储引擎

MySQL支持多种存储引擎，每种存储引擎都有其特定的功能和优化。

3.1 MySQL支持的存储引擎

常见的存储引擎包括InnoDB、MyISAM、Memory和Archive。

3.2 InnoDB存储引擎

InnoDB是MySQL的默认存储引擎，支持事务处理、行级锁定和外键约束。

特点：

事务安全：支持事务提交、回滚和崩溃恢复。
存储效率：使用B+树索引结构，提高数据访问效率。

3.3 MyISAM存储引擎

MyISAM存储引擎适用于读取密集型的应用场景，不支持事务处理。

特点：

读取速度：提供高速读取操作。锁定机制：支持表级锁定。

3.4 选择存储引擎

选择存储引擎时需要考虑事务支持、并发性和存储效率等因素。

代码示例：

CREATE TABLE users (id INT NOT NULL AUTO_INCREMENT,name VARCHAR(100),email VARCHAR(100),PRIMARY KEY (id)
) ENGINE=InnoDB;

执行结果：

Table 'users' created successfully with InnoDB engine.

四、表的管理

表的管理包括数据类型、创建表、查看表结构、修改表和删除表。

4.1 数据类型

MySQL支持多种数据类型，包括整数、浮点数、字符串和日期时间类型。

4.2 创建表

使用CREATE TABLE语句创建表。

代码示例：

CREATE TABLE users (id INT NOT NULL AUTO_INCREMENT,name VARCHAR(100),email VARCHAR(100),PRIMARY KEY (id)
);

执行结果：

Table 'users' created successfully.

4.3 查看表结构

使用DESCRIBE或SHOW COLUMNS语句查看表结构。

代码示例：

DESCRIBE users;

执行结果：

+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| name      | varchar(100)| YES  |     | NULL    |                |
| email     | varchar(100)| YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+

4.4 修改表

使用ALTER TABLE语句修改表结构。

代码示例：

ALTER TABLE users ADD COLUMN age INT;

执行结果：

Column 'age' added successfully to table 'users'.

4.5 删除表

使用DROP TABLE语句删除表。

代码示例：

DROP TABLE users;

执行结果：

Table 'users' dropped successfully.

五、数据维护

数据维护涉及数据的插入、更新和删除。

5.1 插入数据

使用INSERT语句插入数据。

代码示例：

INSERT INTO users (name, email) VALUES ('yyy', 'yyy@qq.com');

执行结果：

1 row inserted successfully.

5.2 更新数据

使用UPDATE语句更新数据。

代码示例：

UPDATE users SET email = 'newemail@example.com' WHERE name = 'yyy';

执行结果：

1 row updated successfully.

5.3 删除数据

使用DELETE语句删除数据。

代码示例：

DELETE FROM users WHERE name = 'yyy';

执行结果：

1 row deleted successfully.

六、数据查询

数据查询是数据库操作中最常用的功能之一。

6.1 基本查询

使用SELECT语句进行基本查询。

代码示例：

SELECT * FROM users;

执行结果：

+----+-------+------------------+
| id | name  | email           |
+----+-------+------------------+
| 1  | yyy  | yyy@example.com|
+----+-------+------------------+

6.2 分组查询

使用GROUP BY子句进行分组查询。

代码示例：

SELECT age, COUNT(*) FROM users GROUP BY age;

执行结果：

+----+----------+
| age| COUNT(*) |
+----+----------+
| 25 |        1 |
+----+----------+

6.3 连接查询

使用JOIN子句进行表连接查询。

代码示例：

SELECT users.name, orders.product FROM users JOIN orders ON users.id = orders.user_id;

执行结果：

+-------+------------+
| name  | product    |
+-------+------------+
| yyy  | Laptop     |
+-------+------------+

6.4 合并查询结果

使用UNION或UNION ALL子句合并多个查询结果。

代码示例：

SELECT name FROM users WHERE age > 30
UNION
SELECT name FROM users WHERE age < 20;

执行结果：

+-------+
| name  |
+-------+
| yyy  |
+-------+

七、索引和视图

索引用于提高查询效率，视图是数据库中的虚拟表，可以简化复杂的SQL操作。

7.1 索引

使用CREATE INDEX语句创建索引。

代码示例：

CREATE INDEX idx_name

执行结果：

Index 'idx_name' created successfully.

7.2 视图

使用CREATE VIEW语句创建视图。

代码示例：

CREATE VIEW view_users_with_emails AS SELECT name, email FROM users;

执行结果：

 View 'view_users_with_emails' created successfully.

八、结论

MySQL数据库的基本操作是每个数据库管理员和开发者必须掌握的技能。通过熟练使用SQL语言，我们可以有效地管理数据库、表和数据。此外，了解不同的存储引擎、创建索引和视图可以显著提高数据库的性能和易用性。希望这篇文章能帮助你更好地理解和应用MySQL数据库的基本操作。