MySQL 知识点

1.1 数据库创建和选择

要在MySQL中创建数据库，可以使用CREATE DATABASE语句：

CREATE DATABASE mydatabase;

这将创建一个名为mydatabase的数据库。

要选择数据库，可以使用USE语句：

USE mydatabase;

这将使我们在后续的操作中使用mydatabase这个数据库。

1.2 数据表创建和修改

要在MySQL中创建数据表，可以使用CREATE TABLE语句：

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    salary FLOAT
);

这将创建一个名为employees的数据表，其中包含id、name、age和salary四个字段。

要修改数据表，可以使用ALTER TABLE语句。例如，我们可以添加一个新的email字段：

ALTER TABLE employees ADD COLUMN email VARCHAR(100);

这将在employees表中添加一个email字段，类型为VARCHAR(100)。

1.3 插入数据

要将数据插入到数据表中，可以使用INSERT INTO语句：

INSERT INTO employees (name, age, salary) VALUES ('李白', 25, 5000);

这将向employees表中插入一条记录，其中包含name为’李白’、age为25、salary为5000的数据。

如果要插入多条数据，可以使用逗号将其分隔开：

INSERT INTO employees (name, age, salary) VALUES ('杜甫', 30, 6000), ('王之涣', 35, 7000);

这将向employees表中插入两条记录。

1.4 查询数据

要从数据表中查询数据，可以使用SELECT语句：

SELECT * FROM employees;

这将返回employees表中的所有记录。

您还可以使用条件、排序和限制结果的语句。例如，以下代码将返回年龄大于30岁的前5个员工，按工资降序排列：

SELECT * FROM employees WHERE age > 30 ORDER BY salary DESC LIMIT 5;

1.5 更新和删除数据

要更新数据表中的数据，可以使用UPDATE语句：

UPDATE employees SET salary = 5500 WHERE id = 1;

这将把employees表中id为1的记录的salary字段更新为5500。

要删除数据表中的数据，可以使用DELETE语句：

DELETE FROM employees WHERE id = 2;

这将删除employees表中id为2的记录。

1.6 索引的创建和使用

在MySQL中，索引是用于提高查询效率的重要机制。通过创建合适的索引，可以加快查询速度并减少数据库的负载。下面是创建索引和使用索引的示例代码：

-- 创建索引
CREATE INDEX idx_name ON employees (name);
-- 使用索引查询
SELECT * FROM employees WHERE name = '李白';

示例中，我们使用CREATE INDEX语句创建了一个名为idx_name的索引，该索引针对employees表中的name字段。然后，我们使用SELECT语句查询名字为’李白’的员工信息，由于我们创建了索引，查询速度将会更快。

1.7 外键的使用

MySQL支持外键约束，它们用于维护表与表之间的关系。通过使用外键，可以实现数据的完整性和一致性。下面是创建外键和使用外键的示例代码：

-- 创建外键
ALTER TABLE employees
ADD CONSTRAINT fk_department
FOREIGN KEY (department_id)
REFERENCES departments(id);
-- 使用外键查询
SELECT * FROM employees WHERE department_id = 1;

示例中，我们使用ALTER TABLE语句创建了一个名为fk_department的外键约束，它将employees表中的department_id字段与departments表中的id字段关联起来。然后，我们使用SELECT语句查询department_id为1的员工信息，由于我们创建了外键约束，可以确保查询结果只包含属于部门1的员工。

Python 中使用 MySQL

2.1 连接数据库

要在Python中连接MySQL数据库，需要使用pymysql库。首先，确保已经安装了该库，然后使用以下代码建立连接：

import pymysql
conn = pymysql.connect(host='localhost', user='your_username', password='your_password', database='your_database')

这里的host是MySQL服务器的地址，user是MySQL用户名，password是密码，database是要连接的数据库。

2.2 创建数据库和数据表

在Python中执行SQL语句可以用execute()方法。以下是在Python中创建数据库和数据表的示例代码：

cursor = conn.cursor()
# 创建数据库
cursor.execute("CREATE DATABASE IF NOT EXISTS mydatabase")
# 使用数据库
cursor.execute("USE mydatabase")
# 创建数据表
cursor.execute('''
    CREATE TABLE IF NOT EXISTS employees (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100),
        age INT,
        salary FLOAT
    )
''')

首先，我们创建一个游标对象cursor，它用于执行SQL语句。

然后，我们使用cursor.execute()方法执行SQL语句。这里，我们创建了一个名为mydatabase的数据库（如果不存在），然后使用它。

接下来，我们创建了一个名为employees的数据表，其中包含id、name、age和salary四个字段。

2.3 插入数据

要在Python中插入数据，可以使用参数化查询，以避免SQL注入攻击。以下是插入数据的示例代码：

# 插入单条数据
sql = "INSERT INTO employees (name, age, salary) VALUES (%s, %s, %s)"
values = ('李白', 25, 5000)
cursor.execute(sql, values)
# 插入多条数据
sql = "INSERT INTO employees (name, age, salary) VALUES (%s, %s, %s)"
values = [
    ('杜甫', 30, 6000),
    ('王之涣', 35, 7000)
]
cursor.executemany(sql, values)
# 提交事务
conn.commit()

在这里，我们使用参数化查询来插入数据。参数化查询使用占位符（%s）来代替具体的值，然后通过传递一个值（或多个值的元组/列表）给execute()方法来填充这些占位符。

对于单条数据，我们将SQL语句和值传递给execute()方法。对于多条数据，我们将SQL语句和值的列表传递给executemany()方法。

然后调用commit()方法提交事务，确保数据被永久保存。

2.4 查询数据

要在Python中查询数据，可以使用execute()方法执行SELECT语句，并使用fetchall()方法获取结果集。下面是查询数据的示例代码：

# 查询数据
cursor.execute("SELECT * FROM employees")
result = cursor.fetchall()
for row in result:
    print(f"ID: {row[0]}, Name: {row[1]}, Age: {row[2]}, Salary: {row[3]}")

在这里，我们使用execute()方法执行SELECT语句。然后，我们使用fetchall()方法获取所有的结果。

之后迭代结果集，并打印每一行的数据。

2.5 更新和删除数据

要在Python中更新和删除数据，可以使用execute()方法执行UPDATE和DELETE语句。以下是更新和删除数据的示例代码：

# 更新数据
sql = "UPDATE employees SET salary = %s WHERE id = %s"
values = (5500, 1)
cursor.execute(sql, values)
# 删除数据
sql = "DELETE FROM employees WHERE id = %s"
values = (2,)
cursor.execute(sql, values)
# 提交事务
conn.commit()

代码中，我们使用execute()方法执行UPDATE和DELETE语句。

对于更新操作，我们使用参数化查询，并将要更新的值传递给execute()方法。

对于删除操作，我们也使用参数化查询，并将要删除的记录的ID传递给execute()方法。

最后，我们调用commit()方法提交事务，确保更新或删除操作生效。

2.6 关闭连接

如果不需要时，应该关闭与MySQL的连接：

cursor.close()
conn.close()

在这里，我们使用close()方法关闭游标和数据库连接。

这样就完成了Python与MySQL的交互过程。

2.7 数据库备份和恢复

数据库备份和恢复是保护数据安全的关键步骤。在MySQL中，我们可以使用以下命令进行数据库备份和恢复：

-- 备份数据库
mysqldump -u username -p database_name > backup.sql
-- 恢复数据库
mysql -u username -p database_name < backup.sql

示例中，我们使用mysqldump命令将名为database_name的数据库备份到名为backup.sql的文件中。然后，我们可以使用mysql命令从备份文件中恢复数据库。

注意替换username为您的MySQL用户名，database_name为要备份或恢复的数据库名。

完结