MySQL 数据库基础

MySQL 是一种广泛使用的关系型数据库管理系统（RDBMS），最初由瑞典 MySQL AB 公司开发，现为 Oracle 旗下产品。它以关联数据表的形式存储信息，将数据拆分为多个独立的表，从而提升查询效率与灵活性。MySQL 采用 SQL 作为标准查询语言，并遵循双授权模式，提供社区版和商业版。其社区版因体积小、速度快、源码开放等特点，成为许多中小型网站的首选数据库，常与 PHP 和 Apache 搭配使用。

环境搭建与基础操作

要使用 MySQL 存储和管理数据，需要完成以下步骤：安装 MySQL 服务端、安装 MySQL 客户端、客户端连接服务端、发送 SQL 命令执行增删改查等操作。

# 下载 MySQL
# http://dev.mysql.com/downloads/mysql/
# 安装（以 yum 为例）
yum install mysql-server

数据库与表管理

• 显示所有数据库： SHOW DATABASES;（默认库：mysql、test、information_schema）
• 切换数据库： USE db_name;
• 显示当前库所有表： SHOW TABLES;

用户与权限管理

创建用户： CREATE USER '用户名'@'IP地址' IDENTIFIED BY '密码';

删除用户： DROP USER '用户名'@'IP地址';

修改用户名： RENAME USER '旧名'@'IP' TO '新名'@'IP';

修改密码： SET PASSWORD FOR '用户'@'IP' = PASSWORD('新密码');

查看权限： SHOW GRANTS FOR '用户'@'IP';

授权： GRANT 权限 ON 数据库.表 TO '用户'@'IP';（常用权限：select, insert, update, delete, all privileges 等）

撤销权限： REVOKE 权限 ON 数据库.表 FROM '用户'@'IP';

目标数据库格式：数据库名.*（所有表）、数据库名.表（指定表）、*.*（所有数据库）。

IP 格式：192.168.1.%（网段）或 %（任意 IP）。

基本数据类型

MySQL 数据类型主要分为数值、时间和字符串三大类。详情可参考官方文档：MySQL 数据类型

数据操作 (CRUD)

• 插入： INSERT INTO 表 (列1, 列2) VALUES (值1, 值2); 或批量插入。
• 删除： DELETE FROM 表 WHERE 条件;
• 更新： UPDATE 表 SET 列=值 WHERE 条件;
• 查询： SELECT * FROM 表 WHERE 条件; 支持排序 (ORDER BY)、分组 (GROUP BY)、连表 (INNER JOIN, LEFT JOIN)、组合 (UNION) 等。
• 限制与通配符： LIMIT 起始行, 行数；LIKE 'ale%'（多字符匹配），LIKE 'ale_'（单字符匹配）。

Python 操作 MySQL：pymysql

pymysql 是 Python 连接 MySQL 的纯 Python 库，用法与 MySQLdb 相似。

安装

pip3 install pymysql

基本使用示例

import pymysql

# 建立连接
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
# 创建游标
cursor = conn.cursor()

# 执行 SQL（返回受影响行数）
effect_rows = cursor.execute("UPDATE hosts SET host = '1.1.1.2'")

# 插入多条
cursor.executemany("INSERT INTO hosts(host, color_id) VALUES(%s, %s)", [("1.1.1.11",1), ("1.1.1.11",2)])

# 提交事务
conn.commit()

# 获取新增数据的自增 ID
new_id = cursor.lastrowid

# 查询数据
cursor.execute("SELECT * FROM hosts")
row = cursor.fetchone()      # 单行
rows = cursor.fetchmany(3)   # 多行
all_rows = cursor.fetchall() # 所有

# 移动游标（滚动）
cursor.scroll(1, mode='relative')  # 相对当前位置
cursor.scroll(2, mode='absolute')  # 绝对位置

# 关闭
cursor.close()
conn.close()

若希望查询结果以字典形式返回，可在创建游标时设置：

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

ORM 框架：SQLAlchemy

SQLAlchemy 是 Python 的 ORM 框架，它将对象映射为 SQL 语句，再通过数据库驱动执行。SQLAlchemy 本身不直接操作数据库，需配合 pymysql 等驱动使用。

底层使用示例

from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1", max_overflow=5)

# 直接执行 SQL
cur = engine.execute("SELECT * FROM hosts")
data = cur.fetchall()

ORM 方式：定义表与操作

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1")
Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    extra = Column(String(16))

# 创建表
Base.metadata.create_all(engine)

# 创建会话
Session = sessionmaker(bind=engine)
session = Session()

# 增
session.add(User(name='alex', extra='sb'))
session.commit()

# 删
session.query(User).filter(User.id > 2).delete()
session.commit()

# 改
session.query(User).filter(User.id == 1).update({"name": "new_name"})
session.commit()

# 查
users = session.query(User).all()
user = session.query(User).filter_by(name='alex').first()

SQLAlchemy 支持一对一、一对多、多对多关系，以及条件过滤、排序、分组、连表等高级查询。

远程服务器管理：Paramiko

Paramiko 是基于 SSH 协议的 Python 模块，用于连接远程服务器并执行命令或传输文件。

安装

pip3 install paramiko

SSHClient：执行远程命令

import paramiko

ssh = paramiko.SSHClient()
ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
ssh.connect(hostname='example.com', port=22, username='user', password='pass')

stdin, stdout, stderr = ssh.exec_command('ls -l')
result = stdout.read().decode()
print(result)

ssh.close()

使用密钥认证：

private_key = paramiko.RSAKey.from_private_key_file('/path/to/id_rsa')
ssh.connect(hostname='example.com', username='user', pkey=private_key)

SFTPClient：文件上传与下载

import paramiko

transport = paramiko.Transport(('example.com', 22))
transport.connect(username='user', password='pass')
sftp = paramiko.SFTPClient.from_transport(transport)

# 上传
sftp.put('/local/file.txt', '/remote/file.txt')
# 下载
sftp.get('/remote/file.txt', '/local/file.txt')

transport.close()

堡垒机（跳板机）实现

堡垒机用于集中管理服务器访问和记录操作日志。核心思路是：用户通过堡垒机认证后，选择一个目标服务器，堡垒机自动建立 SSH 连接，并将用户输入与远程输出实时转发。

示例：使用 select 和 termios 实现交互式终端：

import paramiko
import sys
import select
import termios
import tty
from paramiko.py3compat import u

# 建立连接（略，参考上面的 SSHClient 用法）
# 打开会话并获取终端
chan = tran.open_session()
chan.get_pty()
chan.invoke_shell()

# 保存原终端属性
old_tty = termios.tcgetattr(sys.stdin)
try:
    tty.setraw(sys.stdin.fileno())
    chan.settimeout(0.0)
    while True:
        r, w, e = select.select([chan, sys.stdin], [], [])
        if chan in r:
            try:
                data = u(chan.recv(1024))
                if not data:
                    break
                sys.stdout.write(data)
                sys.stdout.flush()
            except:
                pass
        if sys.stdin in r:
            x = sys.stdin.read(1)
            if not x:
                break
            chan.send(x)
finally:
    termios.tcsetattr(sys.stdin, termios.TCSADRAIN, old_tty)

chan.close()
tran.close()

注意：若在 Windows 下使用，需通过线程模拟终端交互。