MySQL的知识总结

（一）、MySQL概述

1. MySQL定义

MySQL是一个开源的关系型数据库管理系统（RDBMS），它使用了一种结构化查询语言（SQL）进行数据管理。MySQL最初由瑞典的MySQL AB公司开发，后来被Sun Microsystems收购，并最终在2009年被Oracle公司收购。由于其体积小、速度快、成本低且功能强大，MySQL成为了Web开发中最受欢迎的数据库系统之一。

2. MySQL特点

• 流行和广泛支持：MySQL拥有庞大的用户群和开发者社区，提供了大量的工具和插件来扩展其功能。
• 跨平台：MySQL可以在多种操作系统上运行，包括Windows、Linux、macOS等。
• 稳定性：MySQL经过长时间的发展和优化，已经变得非常稳定，能够处理大规模的数据和高并发的访问。
• 可扩展性：MySQL支持多种存储引擎，如InnoDB、MyISAM等，每种引擎都有其特点和适用场景。用户可以根据需要选择合适的存储引擎，或者通过分库分表等方式来扩展系统的处理能力。
• 数据完整性：MySQL支持事务处理、外键约束等特性，可以确保数据的完整性和一致性。
• 安全性：MySQL提供了多种安全机制，如用户权限管理、数据加密等，可以保护数据不被非法访问和篡改。

（二）、数据库操作（MySQL）

一、DDL（数据定义语言）

DDL主要用于定义和管理数据库结构，如创建、删除、修改数据库、表、索引等。

1. 创建数据库
   • 示例：

     CREATE DATABASE mydb;

   • 说明：创建一个名为mydb的数据库。
2. 删除数据库
   • 示例：

     DROP DATABASE IF EXISTS mydb;

   • 说明：删除名为mydb的数据库。
3. 使用数据库
   • 示例：

     USE mydb;

   • 说明：切换到mydb数据库，以便在该数据库中进行后续操作。
4. 创建表
   • 示例：

     CREATE TABLE users ( 
     id INT PRIMARY KEY, 
     name VARCHAR(50) NOT NULL, 
     age INT 
     );

   • 说明：在mydb数据库中创建一个名为users的表，包含id（主键）、name（非空）、age三个字段。
5. 删除表
   • 示例：

     DROP TABLE IF EXISTS users;

   • 说明：删除名为users的表。
6.  修改表
   • 添加字段：

     ALTER TABLE users ADD email VARCHAR(100);

   • 删除字段：

     ALTER TABLE users DROP COLUMN age;

   • 修改字段：

     ALTER TABLE users MODIFY name VARCHAR(100);

   • 字段重命名：

     ALTER TABLE users CHANGE name username VARCHAR(50);

7. 重命名表

   将old_employees表重命名为new_employees表：

   RENAME TABLE old_employees TO new_employees;
   

二、DML（数据操作语言）

DML用于对数据库中的数据进行增删改查操作。

1. 插入数据
   • 示例：

     INSERT INTO users (id, username, age) VALUES (1, 'Alice', 25);

   • 说明：向users表中插入一条数据。
2. 更新数据
   • 示例：

     UPDATE users SET age = 26 WHERE id = 1;

   • 说明：将id为1的用户的age字段更新为26。
3. 删除数据
   • 示例：

     DELETE FROM users WHERE id = 1;

   • 说明：删除id为1的用户。
4. 查询数据
   • 示例（基础查询）：

     SELECT * FROM users;

   • 示例（带条件查询）：

     SELECT * FROM users WHERE age > 20;

   • 示例（排序查询）：

     SELECT * FROM users ORDER BY age DESC;

   • 示例（聚合查询）：

     SELECT COUNT(*) FROM users;

     
      

（三）、扩展内容

一、数据类型：

在创建表时，需要为字段指定数据类型，如INT、VARCHAR、DATE等。了解不同数据类型的特点和适用场景，可以帮助我们更好地设计数据库结构。在创建表时，需要为字段指定数据类型。以下是一些常见的数据类型及其使用例子： 

1. INT（整数类型）
   • 示例：

     CREATE TABLE orders ( 
     order_id INT PRIMARY KEY, 
     amount INT NOT NULL 
     );

   • 说明：创建一个名为orders的表，其中order_id是整数类型的主键，amount是整数类型且不允许为空。
2. VARCHAR（可变长度字符串类型）
   • 示例：

     CREATE TABLE customers ( 
     customer_id INT PRIMARY KEY, 
     name VARCHAR(100) NOT NULL 
     );

   • 说明：创建一个名为customers的表，其中customer_id是整数类型的主键，name是可变长度字符串类型且不允许为空，最大长度为100个字符。
3. DATE（日期类型）
   • 示例：

     CREATE TABLE events ( 
     event_id INT PRIMARY KEY, 
     event_name VARCHAR(255), 
     event_date DATE 
     );

   • 说明：创建一个名为events的表，其中event_id是整数类型的主键，event_name是可变长度字符串类型的事件名称，event_date是日期类型的事件日期。

二、主键与外键：

主键用于唯一标识表中的每一行数据，而外键用于建立表与表之间的关系。通过合理设置主键和外键，可以确保数据的完整性和一致性。

1. 主键
   • 示例（设置单列主键）：

     CREATE TABLE students ( 
     student_id INT PRIMARY KEY, 
     name VARCHAR(100) 
     );

   • 示例（设置复合主键）：

     CREATE TABLE student_courses ( 
     student_id INT, 
     course_id INT, 
     PRIMARY KEY (student_id, course_id) 
     );

2. 外键
   • 示例（设置外键）：
     假设我们有两个表，一个是courses（课程表），一个是student_courses（学生选课表）。student_courses中的course_id是courses表中course_id的外键。

     CREATE TABLE courses ( 
     course_id INT PRIMARY KEY, 
     course_name VARCHAR(255) 
     ); 
     
     	
     
     CREATE TABLE student_courses ( 
     student_id INT, 
     course_id INT, 
     FOREIGN KEY (course_id) REFERENCES courses(course_id) 
     );

三、索引：

虽然上面未详细展开，但索引是数据库中非常重要的一部分。通过创建索引，可以加快数据的检索速度，提高查询性能。但需要注意的是，索引也会占用额外的磁盘空间，并可能增加插入、更新和删除操作的开销。因此，在创建索引时需要权衡利弊，根据实际需求进行选择。以下是一个创建索引的例子：

• 示例（创建单列索引）：

  CREATE INDEX idx_name ON users(name);

   
  • 说明：在users表的name字段上创建一个名为idx_name的索引。
• 示例（创建复合索引）：

  CREATE INDEX idx_name_age ON users(name, age);

   
  • 说明：在users表的name和age字段上创建一个名为idx_name_age的复合索引。

四、约束：

在创建表时，可以设置各种约束来限制数据的输入和输出，如非空约束（NOT NULL）、唯一约束（UNIQUE）、主键约束（PRIMARY KEY）、外键约束（FOREIGN KEY）等。这些约束有助于确保数据的准确性和完整性。

1. 非空约束（NOT NULL）
   • 示例（设置非空约束）：

     CREATE TABLE addresses ( 
     address_id INT PRIMARY KEY, 
     street VARCHAR(255) NOT NULL, 
     city VARCHAR(100) NOT NULL 
     );

2. 唯一约束（UNIQUE）
   • 示例（设置唯一约束）：

     CREATE TABLE email_addresses ( 
     email_id INT PRIMARY KEY, 
     email_address VARCHAR(255) UNIQUE 
     );

五、事务处理：

MySQL支持事务处理，可以确保多个操作作为一个整体进行，要么全部成功，要么全部失败。这对于需要保持数据一致性的应用来说非常重要。

MySQL支持事务处理，可以确保多个操作作为一个整体进行。以下是一个使用事务的例子：

• 示例（使用事务进行转账操作）：

  START TRANSACTION; 
  -- 从账户A中减去100元 
  
  UPDATE accounts SET balance = balance - 100 WHERE account_id = 1; 
  -- 向账户B中增加100元 
  
  UPDATE accounts SET balance = balance + 100 WHERE account_id = 2; 
  -- 如果上述两个操作都成功，则提交事务 
  
  COMMIT; 
  -- 如果出现错误，则回滚事务

  
   

二、其他重要方面

除了上述的数据库操作和特点外，MySQL还有许多其他重要方面值得注意：

• 字符集和排序规则：在创建数据库和表时，可以指定字符集和排序规则，以确保数据的正确存储和检索。
• 存储引擎：MySQL支持多种存储引擎，每种引擎都有其独特的优势和适用场景。选择合适的存储引擎对于提高数据库性能至关重要。
• 权限管理：MySQL提供了丰富的权限管理系统，可以控制用户对数据库的访问和操作，确保数据的安全性。
• 性能调优：通过优化SQL查询、调整数据库参数、使用缓存等技术手段，可以提高MySQL的性能和响应速度。
• 备份和恢复：MySQL提供了多种备份和恢复策略，可以确保数据的安全性和可恢复性。定期备份数据库是防止数据丢失的重要措施。