【MySQL】MySQL学习笔记

学习笔记

前言

在完成python-socket-GNSS&PIC 融合传输后，现在需要将服务端假设到阿里云服务器上，并完成数据库的存储。

MYSQL 和 MSSQL 的些许区别

MY SQL 是轻量级数据库，要处理大数据时很尴尬。因为内部没有优化只能用其他辅助插件来优化。如果夸服务器还得开启FEDERATED（阿里云不支持FEDERATED的）反正就是很多服务用上才能开启。
MS SQL 老牌子了，但是弊端也很明显，直到2008R2之前都没有一个高效的自带的读写分离方法。直到后面的版本高可用实例出来后才能解决读写分离（需要设置域什么的很麻烦）。

跨服务器也简单还能跨不同的数据库（其实只要支持ODBC做跳板就能解决了的）
跨数据库与服务器的意义在于分部计算。通关不同服务器上建立能允许远程访问的存储过程。直接调用接口同时在不同服务器计算不同的数据分摊计算资源再汇总。消耗IOPS和网络流量换取最快的计算速度。

简单浏览

在仔细阅读教科书前，先简单的浏览一下菜鸟网站的MySQL 教程：MySQL NULL 值处理

• PHP 语法的学习（作为一种连接数据库的方式，个人认为单纯学习PHP 作用不大，有兴趣可以参考JAVA Python 和 C++ 的语法结构）

• 数据类型

• 数据库：连接CREATE\删除DROP\选择USE

• 数据表：创建CREATE\删除DROP

• 数据：插入INSERT\查询SELECT\更新UPDATE\删除DELETE

• 条件：符合WHERE\模糊LIKE\并UNION\符合ON

• 集合：排序ORDER BY\分组GROUP BY\左连接LEFT JOIN\右连接RIGHT JOIN\等值连接INNER JOIN

• NULL：IS NULL\ IS NOT NULL\ <=>

• 正则表达式

• 事务：ACID 特性

  事务控制语句：

  • BEGIN 或 START TRANSACTION 显式地开启一个事务；

  • COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；

  • ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

  • SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；

  SAVEPOINT savepoint_name;    // 声明一个 savepoint
  ROLLBACK TO savepoint_name;  // 回滚到savepoint
  RELEASE SAVEPOINT savepoint_name;  // 删除指定保留点
  

  • RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；

  • ROLLBACK TO identifier 把事务回滚到标记点；

  • SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

  • 接用 SET 来改变 MySQL 的自动提交模式:
    SET AUTOCOMMIT=0 禁止自动提交
    SET AUTOCOMMIT=1 开启自动提交

• 操作表名、字段：增减字段ALTER(DROP \ ADD)(FIRST \ AFTER c)\修改字段ALTER(MODIFY c CHAR(10)\ CHANGE j j INT;)\修改表名ALTER TABLE testalter_tbl RENAME TO alter_tbl

---

未完成

• 索引
• 临时表、复制表
• 元数据、序列使用
• 处理重复数据
• SQL注入
• 数据导入导出、函数及运算符

---

目录概览

在学习一门新技术前，通常会先浏览它的目录，以此先掌握技术的大概结构，这样有助于安排学习规划、侧重重点等，可以更快的入门一门新技术。

1. MySQL 的特性、安装
2. MySQL 的基本操作
   1. 存储引擎、数据类型、运算符
   2. 基本操作函数
   3. 高级函数
3. 数据查询
4. MySQL 索引
5. MySQL 存储过程
6. 触发器
7. MySQL 视图
8. MySQL 事务
9. MySQL 系统管理
10. 数据库备份、还原
11. MySQL 性能优化
12. MySQL 安全技术
13. MySQL 日常事务管理
14. 访问MySQL 数据库
15. 项目

参考链接：
Python访问MySQL:Python MySQL - mysql-connector 驱动
MySQL 教程:MySQL 教程
PHP 教程:PHP 教程

笔记

1. MySQL 的特性、安装

1）MySQL 的特性

关系型数据库管理系统（RDBMS）是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。

RDBMS 即关系数据库管理系统(Relational Database Management System)的特点：

1. 数据以表格的形式出现
2. 每行为各种记录名称
3. 每列为记录名称所对应的数据域
4. 许多的行和列组成一张表单
5. 若干的表单组成database

RDBMS事务的特性：原子性、一致性、隔离性、持久性（ACID特性）

• 原子性（atomicity）。一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。
• 一致性（consistency）。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
• 隔离性（isolation）。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
• 持久性（durability）。持续性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

RDBMS的术语：
数据库: 数据库是一些关联表的集合。
数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
列: 一列(数据元素) 包含了相同类型的数据, 例如邮政编码的数据。
行：一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
外键：外键用于关联两个表。
复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

2）MySQL 安装

（1）ZIP Archive

1. ZIP Archive
   解压ZIP文件放在文件夹 C:\web\mysql-8.0.11 下，配置my.ini 文件

   [client]
   # 设置mysql客户端默认字符集
   default-character-set=utf8
    
   [mysqld]
   # 设置3306端口
   port = 3306
   # 设置mysql的安装目录
   basedir=C:\\web\\mysql-8.0.11
   # 设置 mysql数据库的数据的存放目录，MySQL 8+ 不需要以下配置，系统自己生成即可，否则有可能报错
   # datadir=C:\\web\\sqldata
   # 允许最大连接数
   max_connections=20
   # 服务端使用的字符集默认为8比特编码的latin1字符集
   character-set-server=utf8
   # 创建新表时将使用的默认存储引擎
   default-storage-engine=INNODB
   

   INI文件全称为initialzation file，即初始化文件。ini文件的功能是作为系统的配置文件，很多时候我们通过图形化界面就可以修改系统配置，但是从原理来讲都是修改了ini文件，因此学会编辑ini文件对于实现系统环境配置有所帮助。.ini，.inc，.asp文件存储常量或数据库链接语句。
   在Windows系统迭代中，注册表显现出逐渐替代INI文件的趋势。

   INI文件由节、键、值组成
   # 节
   [section]
   # 参数（键=值）
   name=value
   # 注解 使用分号表示（;）
   ; comment text
   

   因为INI文件可能是项目中共用的，所以使用[Section Name]段名来区分不同用途的参数区。例如：[Section1 Name]表示传感器灵敏度参数区；[Section2 Name]表示测量通道参数区等等。

   参考链接：Windows 上安装 MySQL

（2）MSI

2. MSI
   直接安装，有时间再更，这里贴一个链接：MySQL8 MSI 安装教程

---

2. MySQL 基本操作

1）几种操作MySQL 的方式

（1）workbench

1. workbench
   
   MySQL Workbench 是集成在MySQL 软件中的一个数据库可视化开发工具，通过界面实现对数据库的操作。

    参考链接：
    [Mysql Workbench使用教程](https://blog.csdn.net/zs1342084776/article/details/88701261)
    [MySQL Workbench使用教程](http://c.biancheng.net/view/2625.html)
    [Mysql 8.0安装 & Navicat for MySQL 下载安装及使用教程](https://blog.csdn.net/qq_38196234/article/details/89375648?ops_request_misc=%25257B%252522request%25255Fid%252522%25253A%252522160825384516780308340704%252522%25252C%252522scm%252522%25253A%25252220140713.130102334..%252522%25257D&request_id=160825384516780308340704&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~top_click~default-1-89375648.first_rank_v2_pc_rank_v29&utm_term=navicat%20for%20mysql)
   

（2）Navicat for MySQL

2. Navicat for MySQL

   打开界面如图所示：
   点击左上角进行连接：
   
   连接后如图所示：
   

   参考链接：(破解链接就不放了，支持正版，支持正版)
   Mysql 8.0安装 & Navicat for MySQL 下载安装及使用教程

（3）命令行操作

3. 命令行操作
   其实这里也是分为两种：
   第一种mysqlshell，应该使用JavaScript 语句控制，等到写网页的时候再看。
   第二种cmd登录mysql，本片着重讲第二种方法。

   简单SQL 命令：

   # 登陆sql客户端并输入密码
   > mysql -u 用户名(syn) -p
   > Enter password: ***********
   # 注意后面的分号结束标识一定要打 
   
   # 显示数据库
   > SHOW DATABASES;
   +--------------------+
   | Database           |
   +--------------------+
   | homework           |
   | information_schema |
   | mysql              |
   | performance_schema |
   | sakila             |
   | sys                |
   | world              |
   +--------------------+
   
   # 选择数据库
   > use homework;
   
   # 显示表
   > SHOW TABLES;
   +--------------------+
   | Tables_in_homework |
   +--------------------+
   | new_view           |
   | newtable           |
   +--------------------+
   2 rows in set (0.01 sec)
   
   # 插入用户信息
   > INSERT INTO user 
     (host, user, password, 
     select_priv, insert_priv, update_priv) 
     VALUES ('localhost', 'guest', 
     PASSWORD('guest123'), 'Y', 'Y', 'Y');
   > Query OK, 1 row affected (0.20 sec)
   # 或者
   > GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
   > ON TUTORIALS.*
   > TO 'zara'@'localhost'
   > IDENTIFIED BY 'zara123';
   
   # 刷新MySQL的系统权限相关表，否则会出现拒绝访问
   > FLUSH PRIVILEGES;
   > Query OK, 1 row affected (0.01 sec)
   
   # select 选择语句
   > SELECT * FROM newtable;
   +----+------+-----------+-------------+
   | id | name | classname | newtablecol |
   +----+------+-----------+-------------+
   |  1 | v    | b         | v           |
   |  2 | a    | g         | 1           |
   |  3 | s    | h         | 3           |
   |  4 | d    | j         | 2           |
   |  5 | f    | k         | 4           |
   +----+------+-----------+-------------+
   5 rows in set (0.00 sec)
   
   # 显示属性
   > SHOW COLUMNS FROM newtable;
   +-------------+-------------+------+-----+---------+-------+
   | Field       | Type        | Null | Key | Default | Extra |
   +-------------+-------------+------+-----+---------+-------+
   | id          | int         | NO   | PRI | NULL    |       |
   | name        | varchar(45) | NO   |     | NULL    |       |
   | classname   | varchar(45) | NO   |     | NULL    |       |
   | newtablecol | varchar(45) | NO   |     | NULL    |       |
   +-------------+-------------+------+-----+---------+-------+
   4 rows in set (0.01 sec)
   
   # 显示索引
   > SHOW INDEX FROM newtable;
   +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
   | Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
   +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
   | newtable |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
   +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
   1 row in set (0.00 sec)
   
   # 显示数据库 homework 中所有表的信息
   > SHOW TABLE STATUS  FROM homework;  
   +----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
   | Name     | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation          | Checksum | Create_options | Comment |
   +----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
   | new_view | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | 2020-12-18 11:48:53 | NULL                | NULL       | NULL               |     NULL | NULL           | VIEW    |
   | newtable | InnoDB |      10 | Dynamic    |    1 |          16384 |       16384 |               0 |            0 |         0 |           NULL | 2020-12-18 10:41:06 | 2020-12-18 10:47:16 | NULL       | utf8mb4_0900_ai_ci |     NULL |                |         |
   +----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
   2 rows in set (0.00 sec)
   
   # 表名以new开头的表的信息
   > SHOW TABLE STATUS from homework LIKE 'new%';     
   +----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
   | Name     | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation          | Checksum | Create_options | Comment |
   +----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
   | new_view | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | 2020-12-18 11:48:53 | NULL                | NULL       | NULL               |     NULL | NULL           | VIEW    |
   | newtable | InnoDB |      10 | Dynamic    |    1 |          16384 |       16384 |               0 |            0 |         0 |           NULL | 2020-12-18 10:41:06 | 2020-12-18 10:47:16 | NULL       | utf8mb4_0900_ai_ci |     NULL |                |         |
   +----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
   2 rows in set (0.00 sec)
   
   # 加上 \G，查询结果按列打印
   > SHOW TABLE STATUS from homework LIKE 'new%'\G;   
   *************************** 1. row ***************************
          Name: new_view
        Engine: NULL
       Version: NULL
    Row_format: NULL
          Rows: NULL
    Avg_row_length: NULL
       Data_length: NULL
   Max_data_length: NULL
      Index_length: NULL
         Data_free: NULL
    Auto_increment: NULL
       Create_time: 2020-12-18 11:48:53
       Update_time: NULL
        Check_time: NULL
         Collation: NULL
          Checksum: NULL
    Create_options: NULL
           Comment: VIEW
   *************************** 2. row ***************************
              Name: newtable
            Engine: InnoDB
           Version: 10
        Row_format: Dynamic
              Rows: 1
    Avg_row_length: 16384
       Data_length: 16384
   Max_data_length: 0
      Index_length: 0
         Data_free: 0
    Auto_increment: NULL
       Create_time: 2020-12-18 10:41:06
       Update_time: 2020-12-18 10:47:16
        Check_time: NULL
         Collation: utf8mb4_0900_ai_ci
          Checksum: NULL
    Create_options:
           Comment:
   2 rows in set (0.00 sec)
   

（4）python-mysql

4. mysql-connector
   mysql-connector 时MySQL 官方提供的使用python连接数据库的驱动器。我们通过pip命令可以安装：

   python -m pip install mysql-connector
   

   2021-03-25

   今日在新电脑使用上面的语句重新安装mysql，在python中写入该代码一直报错，经过查询原因为mysql-connector版本过旧，因此使用如下命令：

   pip install MySQL-connector-python
   

   展示一个简单的例程

   # 导入数据包
   import mysql.connector
   # 创建连接对象
   mydb = mysql.connector.connect(
     host="localhost",       # 数据库主机地址
     user="syn",    # 数据库用户名
     passwd="asdfgh123456syn"   # 数据库密码
   )
   # 打印数据库对象
   print(mydb)
   
   结果
   <mysql.connector.connection.MySQLConnection object at 0x0000019242953FC8>
   

   同时我们可以使用mydb.cursor()来创建数据库的执行对象

   mycursor = mydb.cursor()
    # 创建数据库
   mycursor.execute("CREATE DATABASE homwwork")
   # 创建数据表sites
   # 这里应该是创建到了默认数据库homework中
   mycursor.execute("CREATE TABLE sites (name VARCHAR(255), url VARCHAR(255))")
   # 展示所有数据库列表
   mycursor.execute("SHOW DATABASES")
   for x in mycursor:
     print(x)
   # 展示所有数据表列表
   mycursor.execute("SHOW TABLES")
   for x in mycursor:
     print(x)
   
   结果
   ('homework',)
   ('information_schema',)
   ('mysql',)
   ('performance_schema',)
   ('sakila',)
   ('sys',)
   ('world',)
   
   ('new_view',)
   ('newtable',)
   ('sites',)
   

   创建、插入数据

   # 创建表的过程中设置主键
   mycursor.execute("CREATE TABLE sites (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), url VARCHAR(255))")
   
   # 这里注意数字（包括整数和浮点数）、字符串、日期时间或其他任意类型，都应该使用%s占位符
   sql = "INSERT INTO newtable (id, name, classname, newtablecol) VALUES (%s, %s, %s, %s)"
   val = (6, "s", "y", "n")
   mycursor.execute(sql, val) 
   mydb.commit()    # 数据表内容有更新，必须使用到该语句
   print(mycursor.rowcount, "记录插入成功。")
   
   sql = "INSERT INTO sites (name, url) VALUES (%s, %s)"
   val = ("RUNOOB", "https://www.runoob.com")
   mycursor.execute(sql, val)
   mydb.commit()    # 数据表内容有更新，必须使用到该语句
   print(mycursor.rowcount, "记录插入成功。")
   
   sql = "INSERT INTO sites (name, url) VALUES (%s, %s)"
   val = [
     ('Google', 'https://www.google.com'),
     ('Github', 'https://www.github.com'),
     ('Taobao', 'https://www.taobao.com'),
     ('stackoverflow', 'https://www.stackoverflow.com/')
   ]
   mycursor.executemany(sql, val) # 插入许多记录
   mydb.commit()    # 数据表内容有更新，必须使用到该语句 
   print(mycursor.rowcount, "记录插入成功。")
   
   # id INT AUTO_INCREMENT PRIMARY KEY 将id设置为主键
   mycursor.execute("ALTER TABLE sites ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY")
   
   sql = "INSERT INTO sites (name, url) VALUES (%s, %s)"
   val = ("Zhihu", "https://www.zhihu.com")
   mycursor.execute(sql, val) 
   mydb.commit()
   print("1 条记录已插入, ID:", mycursor.lastrowid)
   

   查询数据

   import mysql.connector
   mydb = mysql.connector.connect(
     host="localhost",
     user="syn",
     passwd="asdfgh123456syn",
     database="homeworkk+"
   )
   mycursor = mydb.cursor()
   mycursor.execute("SELECT * FROM sites")
   
   # fetchall() 获取所有记录
   myresult = mycursor.fetchall()     
   for x in myresult:
     print(x)
   
   # fetchone()只读取一条数据
   myresult = mycursor.fetchone()
   
   # where 查询指定条件的数据
   mycursor.execute("SELECT * FROM sites WHERE name ='RUNOOB'")
   
   # 可使用通配符 url LIKE '%oo%'
   sql = "SELECT * FROM sites WHERE url LIKE '%oo%'"
   
   # 为了防止数据库查询发生 SQL 注入的攻击
   # 我们可以使用 %s 占位符来转义查询的条件
   sql = "SELECT * FROM sites WHERE name = %s"
   na = ("RUNOOB", )
   mycursor.execute(sql, na)
   myresult = mycursor.fetchall()
   for x in myresult:
     print(x)
   

   整理数据

   import mysql.connector
   
   mydb = mysql.connector.connect(
       host="localhost",
       user="syn",
       passwd="asdfgh123456syn",
       database="homework"
   )
   mycursor = mydb.cursor()
   
   # ORDER BY name ASC/DESC 进行排序
   sql = "SELECT * FROM sites ORDER BY name"
   # 降序
   sql = "SELECT * FROM sites ORDER BY name DESC"
   # LIMIT 限制查询数量为 3 OFFSET 设置起始位置为1
   sql = "SELECT * FROM sites LIMIT 3 OFFSET 1"
   # DELETE FROM 删除记录
   sql = "DELETE FROM sites WHERE name = 'stackoverflow'"
   # 使用 %s 转义条件
   sql = "DELETE FROM sites WHERE name = %s"
   na = ("stackoverflow", )
   mycursor.execute(sql, na)	
   
   # update 更新数据
   sql = "UPDATE sites SET name = 'ZH' WHERE name = 'Zhihu'"
   
   mycursor.execute(sql)
   myresult = mycursor.fetchall()
   for x in myresult:
     print(x)
   

   最后还有删除数据表

   import mysql.connector
    
   mydb = mysql.connector.connect(
       host="localhost",
       user="syn",
       passwd="asdfgh123456syn",
       database="homework"
   )
   mycursor = mydb.cursor()
    
   sql = "DROP TABLE IF EXISTS sites"  # 删除数据表 sites
    
   mycursor.execute(sql)
   

   解决Not all parameters were used in the SQL statement问题

（5）pymysql

5. pymysql

   import pymysql
    	# 打开数据库连接
   db = pymysql.connect(host="localhost", user="root", password="123456", database="homework")
    
   # 使用 cursor() 方法创建一个游标对象 cursor
   cursor = db.cursor()	 
   # 使用 execute()  方法执行 SQL 查询 
   cursor.execute("SELECT VERSION()")
   # 使用 fetchone() 方法获取单条数据.
   data = cursor.fetchone()
   print ("Database version : %s " % data)
   
   # 使用 execute() 方法执行 SQL，如果表存在则删除
   cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
   
   # 使用预处理语句创建表
   sql = """CREATE TABLE EMPLOYEE (
            FIRST_NAME  CHAR(20) NOT NULL,
            LAST_NAME  CHAR(20),
            AGE INT,  
            SEX CHAR(1),
            INCOME FLOAT )""" 
   cursor.execute(sql)
   
   # SQL 插入语句
   sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
            LAST_NAME, AGE, SEX, INCOME)
            VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
   try:
      # 执行sql语句
      cursor.execute(sql)
      # 提交到数据库执行
      db.commit()
   except:
      # 如果发生错误则回滚
      db.rollback()
    
   # 关闭数据库连接
   db.close()
   

   或者我们可以使用如下方式

   # SQL 插入语句
   sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
          LAST_NAME, AGE, SEX, INCOME) \
          VALUES ('%s', '%s',  %s,  '%s',  %s)" % \
          ('Mac', 'Mohan', 20, 'M', 2000)
   
   # 变量输入
   user_id = "test123"
   password = "password"
   con.execute('insert into Login values( %s,  %s)' % \
                (user_id, password))
   

   其中变量输入可以提高程序的可读性，因此重点使用。

（6）示例程序

• 查询

  import pymysql
   
  # 打开数据库连接
  db = pymysql.connect("localhost","testuser","test123","TESTDB" )
   
  # 使用cursor()方法获取操作游标 
  cursor = db.cursor()
   
  # SQL 查询语句
  sql = "SELECT * FROM EMPLOYEE \
         WHERE INCOME > %s" % (1000)
  try:
     # 执行SQL语句
     cursor.execute(sql)
     # 获取所有记录列表
     results = cursor.fetchall()
     for row in results:
        fname = row[0]
        lname = row[1]
        age = row[2]
        sex = row[3]
        income = row[4]
         # 打印结果
        print ("fname=%s,lname=%s,age=%s,sex=%s,income=%s" % \
               (fname, lname, age, sex, income ))
  except:
     print ("Error: unable to fetch data")
   
  # 关闭数据库连接
  db.close()
  

• 更新

  import pymysql
   
  # 打开数据库连接
  db = pymysql.connect("localhost","testuser","test123","TESTDB" )
   
  # 使用cursor()方法获取操作游标 
  cursor = db.cursor()
   
  # SQL 更新语句
  sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M')
  try:
     # 执行SQL语句
     cursor.execute(sql)
     # 提交到数据库执行
     db.commit()
  except:
     # 发生错误时回滚
     db.rollback()
   
  # 关闭数据库连接
  db.close()
  

• 删除

  import pymysql
   
  # 打开数据库连接
  db = pymysql.connect("localhost","testuser","test123","TESTDB" )
   
  # 使用cursor()方法获取操作游标 
  cursor = db.cursor()
   
  # SQL 删除语句
  sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)
  try:
     # 执行SQL语句
     cursor.execute(sql)
     # 提交修改
     db.commit()
  except:
     # 发生错误时回滚
     db.rollback()
   
  # 关闭连接
  db.close()
  

（7）错误处理

3. 数据查询

1）集合

（1）MySQL 的连接：等值连接、左连接和右连接

mysql> show tables;
+--------------------+
| Tables_in_homework |
+--------------------+
| employee           |
| new_view           |
| newtable           |
| runoob_tbl         |
| sites              |
| tcount_tbl         |
+--------------------+
6 rows in set (0.01 sec)

mysql> SELECT * FROM tcount_tbl; 
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| 菜鸟教程      |           10 |
| RUNOOB.COM    |           20 |
| Google        |           22 |
+---------------+--------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM runoob_tbl;
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
|         1 | 学习 PHP     | 菜鸟教程      | 2020-12-23      |
|         2 | 学习 MySQL   | 菜鸟教程      | 2020-12-23      |
|         3 | JAVA 教程    | RUNOOB.COM    | 2016-05-06      |
|         4 | 学习python   | RUNOOB.COM    | 2016-05-06      |
|         5 | 学习c        | FK            | 2016-05-06      |
+-----------+--------------+---------------+-----------------+
5 rows in set (0.00 sec)

mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
+-----------+---------------+--------------+
| runoob_id | runoob_author | runoob_count |
+-----------+---------------+--------------+
|         1 | 菜鸟教程      |           10 |
|         2 | 菜鸟教程      |           10 |
|         3 | RUNOOB.COM    |           20 |
|         4 | RUNOOB.COM    |           20 |
+-----------+---------------+--------------+
4 rows in set (0.00 sec)

mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
+-----------+---------------+--------------+
| runoob_id | runoob_author | runoob_count |
+-----------+---------------+--------------+
|         1 | 菜鸟教程      |           10 |
|         2 | 菜鸟教程      |           10 |
|         3 | RUNOOB.COM    |           20 |
|         4 | RUNOOB.COM    |           20 |
|         5 | FK            |         NULL |
+-----------+---------------+--------------+
5 rows in set (0.00 sec)

mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a RIGHT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
+-----------+---------------+--------------+
| runoob_id | runoob_author | runoob_count |
+-----------+---------------+--------------+
|         1 | 菜鸟教程      |           10 |
|         2 | 菜鸟教程      |           10 |
|         3 | RUNOOB.COM    |           20 |
|         4 | RUNOOB.COM    |           20 |
|      NULL | NULL          |           22 |
+-----------+---------------+--------------+
5 rows in set (0.00 sec)

15. 项目实战

1）windows数据库——图片、文本数据

步骤：

1. 分析表、数据类型
2. 分析存储机制
3. 编写程序

1. 分析表、数据类型

MySQL 数据类型：找到适合图片存储的数据类型

Text   长文本类型
	TINYTEXT:   256 bytes
	TEXT:     65,535 bytes   => ~64kb
	MEDIUMTEXT:  16,777,215 bytes  => ~16MB
	LONGTEXT:   4,294,967,295 bytes => ~4GB
Blob  二进制类型

import pymysql
# 打开数据库
db = pymysql.connect("localhost", "syn", "asdfgh123456syn", "homework")
# 建立cursor对象
cursor = db.cursor()

# 使用 execute() 方法执行 SQL，如果表存在则删除
cursor.execute("DROP TABLE IF EXISTS test")
sql = """CREATE TABLE test(
   id INT PRIMARY KEY AUTO_INCREMENT,
   content LONGTEXT,
   image LONGBLOB )
    """
cursor.execute(sql)
# 此部分输入
id = 1
f = open('camera0.jpg','rb')
data = f.read()
content = "$GPGGA,061346.80,3208.0138462,N,11841.7793013,E,2,15,2.22,54.09,M,2.10,M,2,0806*66"
# 插入数据库
sql = 'INSERT INTO TEST(ID, CONTENT, IMAGE) \
        VALUES (%s, %s, %s)'
val =  (id, content, data) 

try:
    cursor.execute(sql, val)
    db.commit()
except:
    db.rollback()