绥化市网站建设_网站建设公司_跨域_seo优化-仙桃市网站建设公司

绥化市网站建设_网站建设公司_跨域_seo优化

2026/1/3 17:33:54 网站建设项目流程

1.MySQL的安装、登录及可视化
2.SQL书写规范
3. 数据库的基本操作
- 3.1 创建数据库
- 3.2 查看数据库
  - 3.2.1. 查看所有存在的数据库：
  - 3.2.2. 查看创建的数据库
- 3.3 选择数据库
- 3.4 删除数据库
4. 表的基本操作
- 4.1 表的创建
- 4.2 表的查看
  - 4.2.1. 查看数据库下所有的表：
  - 4.2.2. 查看表的具体结构
- 4.3 表的删除
- 4.4 表的更新
  - 4.4.1. 创建一张名为Student的表
  - 4.4.2. 更改表名
  - 4.4.3. 插入新字段
  - 4.4.4. 删除字段
  - 4.4.5. 修改字段
- 4.5 表的查询
  - 4.5.1 建表并插入数据
  - 4.5.2 查看表的内容
  - 4.5.3 重命名查看的字段
  - 4.5.4 常数的查询
  - 4.5.5 查询结果去重
  - 4.5.6 指定查询条件
- 4.6 表的复制
  - 4.6.1 复制全部数据
  - 4.6.1 复制表的结构
5.运算符
- 5.1 算数运算符
- 5.2 比较运算符
  - 5.2.1 配合WHERE
  - 5.2.2 NULL的处理
  - 5.2.3 字符串的处理
- 5.3 逻辑运算符
  - 5.3.1 NOT
  - 5.3.2 AND
  - 5.3.3 OR
  - 5.3.4 逻辑运算符和真值
6. 分组查询
- 6.1 聚合函数
  - 6.1.1 *参数
  - 6.1.2 NULL的处理
  - 6.1.3 适用的数据类型
  - 6.1.4 聚合后去重
- 6.2 对表分组
- 6.3 使用WHERE语句
- 6.4 为聚合结果指定条件
- 6.5 对表的查询结果进行排序
  - 6.5.1 单列排序
  - 6.5.2 多列排序
  - 6.5.3 别名排序
7. 数据的插入及更新
- 7.1 数据的插入
  - 7.1.1 数据的插入创建表并插入数据
  - 7.1.2 插入NULL
  - 7.1.3 插入默认值
- 7.2 数据的删除
  - 7.2.1 DROP删除表
  - 7.2.2 DELETE删除
  - 7.2.3 TRUNCATE删除
- 7.3 数据的更新
  - 7.3.1 基本用法
  - 7.3.2 指定条件
  - 7.3.3 多列更新
8.PyMySQL
- 8.1 安装
- 8.2 连接
- 8.3 创建游标
- 8.4 方法
- 8.5 示例
- null
- null

本文的所有内容均参照教程进行实践^[1]

1.`MySQL`的安装、登录及可视化

之前有数据库的实验课，我在Win10上装过了。可视化用的Navicat Premium，相关的过程也没有记录。

2.`SQL`书写规范

SQL 语句要以分号（;）结尾

SQL 不区分关键字的大小写，这对于表名和列名同样适用。

插入到表中的数据是区分大小写的。例如，数据Computer、COMPUTER 或computer，三者是不一样的。

常数的书写方式是固定的，在SQL 语句中直接书写的字符串、日期或者数字等称为常数。常数的书写方式如下所示。

SQL 语句中含有字符串的时候，需要像'abc'这样，使用单引号（'）将字符串括起来，用来标识这是一个字符串。

SQL 语句中含有日期的时候，同样需要使用单引号将其括起来。日期的格式有很多种（'26 Jan 2010' 或者'10/01/26' 等）。

在SQL 语句中书写数字的时候，不需要使用任何符号标识，直接写成1000 这样的数字即可。

单词之间需要用半角空格或者换行来分隔。

SQL中的注释主要采用--和/* ... */的方式，第二种方式可以换行。在MySQL下，还可以通过#来进行注释。

命名规则和数据类型等详见^[1:1]

3. 数据库的基本操作

3.1 创建数据库

创建数据库的命令如下，IF NOT EXISTS（可选）是为了避免重名报错：

CREATE DATABASE [IF NOT EXISTS] <数据库名称>;

示例：

-- 创建名为shop的数据库。
CREATE DATABASE shop;

刷新后我们可以看到，左边的列表当中出现了新建的数据库

3.2 查看数据库

3.2.1. 查看所有存在的数据库：

SHOW DATABASES [LIKE '数据库名'];

LIKE（可选）从句用于匹配指定的数据库名称。其可以部分匹配，也可以完全匹配。

3.2.2. 查看创建的数据库

SHOW CREATE DATABASE <数据库名>;

官方教程当中的

SHOW CREATE DATABASE shop \G

可能会有点问题，其他地方只找到一篇博客^[2]

两者之间的区别在于，数据库名和/G中间加不加空格，即：

SHOW CREATE DATABASE shop \G
SHOW CREATE DATABASE shop\G

不过似乎都不能够运行成功

3.3 选择数据库

在操作一个数据库之前，需要在命令行当中将环境切换到目标数据库：

USE <数据库名>

3.4 删除数据库

DROP DATABASE [IF EXISTS] <数据库名>

4. 表的基本操作

4.1 表的创建

语法：

CREATE TABLE <表名> (<字段1> <数据类型> <该列所需约束>，<字段2> <数据类型> <该列所需约束>，<字段3> <数据类型> <该列所需约束>，<字段4> <数据类型> <该列所需约束>，...<该表的约束1>， <该表的约束2>，...);

示例：

-- 创建一个名为Product的表
CREATE TABLE Product(
-- 字段、数据类型、约束条件product_id CHAR(4) NOT NULL,product_name VARCHAR(100) NOT NULL,product_type VARCHAR(32) NOT NULL,sale_price INT,purchase_price INT,regist_date DATE,
-- 表的约束条件PRIMARY KEY (product_id)
);

CHAR为定长字符，这里CHAR旁边括号里的数字表示该字段最长为多少字符，少于该数字将会使用空格进行填充。

VARCHAR表示变长字符，括号里的数字表示该字段最长为多少字符，存储时只会按照字符的实际长度来存储，但会使用额外的1-2字节来存储值长度。

PRIMARY KEY表示该列为主键，即该字段对应的内容唯一且不能为空。

NOT NULL表示该字段不能输入空白。

4.2 表的查看

4.2.1. 查看数据库下所有的表：

SHOW TABLES;

4.2.2. 查看表的具体结构

DESC Product;

4.3 表的删除

DROP TABLE <表名>;
-- 例如：DROP TABLE Product;

4.4 表的更新

4.4.1. 创建一张名为Student的表

USE shop;CREATE TABLE Student(id INT PRIMARY KEY,name CHAR(15)
);DESC student;

4.4.2. 更改表名

ALTER TABLE Student RENAME Students;

4.4.3. 插入新字段

USE shop;-- 不同的字段通过逗号分开
ALTER TABLE Students ADD sex CHAR(1), ADD age INT;-- 通过FIRST在表首插入字段stu_num
ALTER TABLE Students ADD stu_num INT FIRST;-- 指定在字段sex后插入字段height
ALTER TABLE Students ADD height INT AFTER sex;DESC students;

4.4.4. 删除字段

USE shop;-- 删除字段stu_num
ALTER TABLE Students DROP stu_num;DESC students;

4.4.5. 修改字段

USE shop;-- 修改字段age的数据类型
ALTER TABLE Students MODIFY age CHAR(3);-- 修改字段name为stu_name，不修改数据类型
ALTER TABLE Students CHANGE name stu_name CHAR(15);-- 修改字段sex为stu_sex，数据类型修改为int
ALTER TABLE Students CHANGE sex stu_sex INT;DESC students;

4.5 表的查询

语法：

SELECT <字段名>, ...
FROM <表名>;

查全部字段：

SELECT *
FROM <表名>;

其中，星号（*）代表全部字段的意思。

示例：

4.5.1 建表并插入数据

/*
-- 创建一个名为Product的表
CREATE TABLE Product(product_id CHAR(4) NOT NULL,product_name VARCHAR(100) NOT NULL,product_type VARCHAR(32) NOT NULL,sale_price INT,purchase_price INT,regist_date DATE,PRIMARY KEY (product_id)
);
*/
-- 向Product表中插入数据
INSERT INTO Product VALUES('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20'),('0002', '打孔器', '办公用品', 500, 320, '2009-09-11'),('0003', '运动T恤', '衣服', 4000, 2800, NULL),('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20'),('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15'),('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20'),('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28'),('0008', '圆珠笔', '办公用品', 100, NULL,'2009-11-11');

4.5.2 查看表的内容

USE shop;
-- 查看表的全部内容
SELECT * FROM Product;

USE shop;
-- 查看部分字段包含的内容
SELECT product_id,product_name,sale_price FROM Product;

4.5.3 重命名查看的字段

USE shop;SELECT -- 	设定汉语别名时需要使用双引号（"）括起来product_type AS "产品类型"  ,
-- 	，英文字符则不需要product_id AS IDFROM Product;

4.5.4 常数的查询

USE shop;SELECT '商品' AS string,'2009-05-24' AS date,product_id,product_nameFROM Product;

看结果是新增了两列，属于人工添加的备注信息

4.5.5 查询结果去重

对单列结果进行去重

SELECT DISTINCT regist_date FROM Product;

对组合结果进行去重

USE shop;SELECT DISTINCT product_type, regist_dateFROM Product;

4.5.6 指定查询条件

语法：

SELECT <字段名>, ……FROM <表名>WHERE <条件表达式>;

示例：

SELECT product_nameFROM ProductWHERE product_type = '衣服';

4.6 表的复制

4.6.1 复制全部数据

-- 将整个表复制过来
CREATE TABLE Product_COPY1SELECT * FROM Product;SELECT * FROM Product_COPY1;

4.6.1 复制表的结构

USE shop;-- 通过LIKE复制表结构
CREATE TABLE Product_COPY2LIKE Product;SELECT * FROM Product_COPY2;-- DESC Product_COPY2;

5.运算符

5.1 算数运算符

SELECT product_name,sale_price,sale_price * 2 AS "sale_price_x2"FROM Product;

四则运算所使用的运算符(+、-、*、/）称为算术运算符。

在运算表达式中，也可以使用()，括号中的运算表达式优先级会得到提升。

NULL的计算结果，仍然还是NULL。

上文有提到常数查询，既然我们可以新增一列常数作为备注，那么自然可以通过对已有列进行运算来新增一列备注

5.2 比较运算符

5.2.1 配合`WHERE`

SELECT product_name, product_typeFROM ProductWHERE sale_price = 500;

5.2.2 `NULL`的处理

不能对NULL使用任何比较运算符，只能通过IS NULL语句来判断：

SELECT product_name,purchase_priceFROM ProductWHERE purchase_price IS NULL;

5.2.3 字符串的处理

MySQL中字符串的排序与数字不同，典型的规则就是按照字典顺序进行比较，也就是像姓名那样，按照条目在字典中出现的顺序来进行排序。例如：
'1' < '10' < '11' < '2' < '222' < '3'

5.3 逻辑运算符

5.3.1 NOT

SELECT product_name,product_type,sale_priceFROM ProductWHERE NOT sale_price >= 1000;

5.3.2 AND

SELECT product_type, sale_priceFROM ProductWHERE product_type = '厨房用具' AND sale_price >= 3000;

5.3.3 OR

SELECT product_type, sale_priceFROM ProductWHERE product_type = '厨房用具'OR sale_price >= 3000;

5.3.4 逻辑运算符和真值

符号 NOT、AND 和 OR 称为逻辑运算符；

真值就是值为真（TRUE）或假（FALSE）；

在查询NULL时，SQL中存在第三种真值，不确定（UNKNOWN），NULL和任何值做逻辑运算结果都是不确定；

考虑 NULL 时的条件判断也会变得异常复杂，因此尽量给字段加上NOT NULL的约束。

6. 分组查询

6.1 聚合函数

COUNT：计算表中的记录数（行数）

SUM：计算表中数值列中数据的合计值

AVG：计算表中数值列中数据的平均值

MAX：求出表中任意列中数据的最大值

MIN：求出表中任意列中数据的最小值

示例：

6.1.1 *参数

注意点1：除了COUNT可以将*作为参数，其它的函数均不可以。

USE shop;-- 计算全部数据的行数
SELECT COUNT(*) FROM Product;-- 注意点1：除了COUNT可以将*作为参数，其它的函数均不可以。

6.1.2 NULL的处理

注意点2：当将字段名作为参数传递给函数时，只会计算不包含NULL的行。

COUNT函数忽略了值为NULL的行。SUM，AVG函数时也一样，计算时会直接忽略，并不会当做0来处理！特别注意AVG函数，计算时分母也不会算上NULL行。

6.1.3 适用的数据类型

注意点3：MAX/MIN函数几乎适用于所有数据类型的列，包括字符和日期。SUM/AVG函数只适用于数值类型的列。

6.1.4 聚合后去重

SELECT COUNT(DISTINCT product_type)FROM Product;

DISTINCT必须写在括号中。这是因为必须要在计算行数之前删除 product_type 字段中的重复数据。

6.2 对表分组

语法：

SELECT <列名1>, <列名2>, <列名3>, ……FROM <表名>GROUP BY <列名1>, <列名2>, <列名3>, ……;

示例：

SELECT product_type, COUNT(*)FROM ProductGROUP BY product_type;

在该语句中，我们首先通过GROUP BY函数对指定的字段product_type进行分组。分组时，product_type字段中具有相同值的行会汇聚到同一组。

最后通过COUNT函数，统计不同分组的包含的行数。

举例：

简单来理解：

例如做操时，老师将不同身高的同学进行分组，相同身高的同学会被分到同一组，分组后我们又统计了每个小组的学生数。

将这里的同学可以理解为表中的一行数据，身高理解为表的某一字段。

分组操作就是GROUP BY，GROUP BY后面接的字段等价于按照身高分组，统计学生数就等价于在SELECT后用了COUNT(*)函数。

注意：GROUP BY 子句的位置一定要写在FROM 语句之后（如果有 WHERE 子句的话需要写在 WHERE 子句之后）

1. SELECT → 2. FROM → 3. WHERE → 4. GROUP BY

当被聚合的键中，包含NULL时，在结果中会以“不确定”行（空行）的形式表现出来，也就是字段中为NULL的数据会被聚合为一组。

6.3 使用WHERE语句

语法：

SELECT <列名1>, <列名2>, <列名3>, ……FROM <表名>WHERE GROUP BY <列名1>, <列名2>, <列名3>, ……;

示例：

-- WHERE语句先将表中类型为衣服的行筛选出来
-- 然后再按照purchase_price来进行分组
SELECT purchase_price, COUNT(*)FROM ProductWHERE product_type = '衣服'GROUP BY purchase_price;

其实际顺序为：

FROM → WHERE → GROUP BY → SELECT

使用GROUP BY子句时，SELECT子句中不能出现聚合键之外的字段名。即，若GROUP BY选中purchase_price字段进行分组，则在SELECT语句中只能选中purchase_price字段，其它字段如product_id等均不行。

WHERE语句中，不可以使用聚合函数。WHERE子句只能指定记录（行）的条件，而不能用来指定组的条件。即WHERE MAX(purchase_price) > 1000这样的语句是非法的。

6.4 为聚合结果指定条件

为了解决WHRER不能用聚合函数的问题，我们可以使用HAVING语句：

SELECT <列名1>, <列名2>, <列名3>, ……FROM <表名>GROUP BY <列名1>, <列名2>, <列名3>, ……
HAVING <分组结果对应的条件>

在HAVING的子句中能够使用的 3 种要素如下所示：

常数

聚合函数

GROUP BY子句中指定的字段名（即聚合键）

举例：

-- 使用HAVING语句
-- 通过HAVING语句将销售平均价格大于等于2500的组给保留了
SELECT product_type, AVG(sale_price)FROM ProductGROUP BY product_type
HAVING AVG(sale_price) >= 2500;

可以看到使用HAVING语句后，输出的结果有所变化。大致流程如下：

首先，FROM语句会选中表Product；

然后，GROUP BY语句会选中字段product_type进行分组；

之后，通过HAVING语句将销售平均价格大于等于2500的组保留下来；

最后，通过SELECT语句将保留下的组的产品类型和平均价格显示出来；

如果是对表的行进行条件指定，WHERE和HAVING都可以生效：

USE shop;-- 下面两条语句执行结果一致
SELECT product_type, COUNT(*)FROM ProductGROUP BY product_typeHAVING product_type = '衣服';-- SELECT product_type, COUNT(*)
--   FROM Product
--   WHERE product_type = '衣服'
--  GROUP BY product_type;

但是，一般而言如果是对表的行进行条件指定，最好还是使用WHERE语句，因为WHERE的执行速度更快。

6.5 对表的查询结果进行排序

语法：

SELECT <列名1>, <列名2>, <列名3>, ……FROM <表名>ORDER BY <排序基准列1>, <排序基准列2>, ……

示例：

6.5.1 单列排序

USE shop;-- 根据字段sale_price的值进行排序
SELECT product_id, product_name, sale_price, purchase_priceFROM Product
ORDER BY sale_price;-- 默认是ASC的升序
-- ORDER BY sale_price ASC
-- DESC是降序
-- ORDER BY sale_price DESC

6.5.2 多列排序

SELECT regist_date, product_id, sale_price, purchase_priceFROM Product
ORDER BY regist_date, product_id;

可以看到先按照regist_date的大小进行排序，在字段regist_date中具有相同的值的行，接着会按照product_id进行排序。

使用含有 NULL 的列作为排序键时，NULL 会在结果的开头或末尾汇总显示。

6.5.3 别名排序

在ORDER BY子句中可以使用SELECT子句中定义的别名。因为ORDER BY 的执行次序在SELECT之后。

-- 将product_id命名为ID，然后按照ID进行排序
SELECT product_id as ID, product_name, sale_price, purchase_priceFROM Product
ORDER BY ID;

7. 数据的插入及更新

7.1 数据的插入

语法：

-- 往表中插入一行数据
INSERT INTO <表名> (字段1, 字段2, 字段3, ……) VALUES (值1, 值2, 值3, ……);-- 往表中插入多行数据
INSERT INTO <表名> (字段1, 字段2, 字段3, ……) VALUES (值1, 值2, 值3, ……),(值1, 值2, 值3, ……),...;

示例：

7.1.1 数据的插入创建表并插入数据

-- 创建表
CREATE TABLE ProductIns
(product_id CHAR(4) NOT NULL,product_name VARCHAR(100) NOT NULL,product_type VARCHAR(32) NOT NULL,sale_price INTEGER DEFAULT 0, -- DEFAULT 0：表示将字段sale_price的默认值设为0purchase_price INT ,regist_date DATE ,PRIMARY KEY (product_id));-- 通过单行方式插入
INSERT INTO ProductIns(product_id, product_name, product_type, sale_price, purchase_price, regist_date)VALUES ('0001', '打孔器', '办公用品', 500, 320, '2009-09-11');-- 当对表插入全字段时，可以省略表后的字段清单
INSERT INTO ProductIns VALUES('0002', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');-- 通过多行方式插入
INSERT INTO ProductIns VALUES ('0003', '菜刀', '厨房用具', 3000, 2800, '2009-09-20'),('0004', '订书机', '办公用品', 100, 50, '2009-09-11'),('0005', '裙子', '衣服', 4100, 3200, '2009-01-23'),('0006', '运动T恤', '衣服', 4000, 2800, NULL),('0007', '牙刷', '日用品', 20, 10, '2010-03-22');SELECT * FROM ProductIns;

7.1.2 插入NULL

INSERT INTO ProductIns VALUES ('0008', '叉子', '厨房用具', 500, NULL, '2009-09-20');

7.1.3 插入默认值

在前面我们创建表时，字段sale_price包含了一条约束条件，默认为0。我们在插入数据时，可以直接用DEFAULT对该字段赋值。前提是，该字段被指定了默认值。

-- 通过显式方法设定默认值
INSERT INTO ProductIns (product_id, product_name, product_type, sale_price, purchase_price, regist_date)VALUES ('0009', '擦菜板', '厨房用具', DEFAULT, 790, '2009-04-28');-- 通过隐式方法插入默认值
INSERT INTO ProductIns (product_id, product_name, product_type, purchase_price, regist_date)VALUES ('0010', '擦菜板', '厨房用具', 790, '2009-04-28');

7.2 数据的删除

7.2.1 DROP删除表

语法：

DROP <表名>;

7.2.2 DELETE删除

语法：

DELETE FROM <表名>;

或者以通过WHERE语句来指定删除的条件：

DELETE FROM <表名>WHERE <条件>;

需要注意的是，DELETE语句的删除对象并不是表或者列，而是记录（行）。

7.2.3 TRUNCATE删除

语法：

TRUNCATE FROM <表名>;

在MySQL中，还存在一种删除表的方式，就是利用TRUNCATE语句。它的功能和DROP类似，但是不能通过WHERE指定条件，优点是速度比DROP快得多。

7.3 数据的更新

7.3.1 基本用法

语法：

UPDATE <表名>SET <字段名> = <表达式>;

示例：

-- -- 由于前面演示删除语句时，表Product的内容已清空
-- -- 所以，这里从新进行数据插入
-- INSERT INTO Product VALUES
--   ('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20'),
--   ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11'),
--   ('0003', '运动T恤', '衣服', 4000, 2800, NULL),
--   ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20'),
--   ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15'),
--   ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20'),
--   ('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28'),
--   ('0008', '圆珠笔', '办公用品', 100, NULL,'2009-11-11')
--  ;-- 修改表中所有行regist_date的值
UPDATE ProductSET regist_date = '2009-10-10';SELECT * FROM Product;

7.3.2 指定条件

语法：

UPDATE <表名>SET <列名> = <表达式>WHERE <条件>;

示例：

UPDATE ProductSET regist_date = '2021-10-30'WHERE product_id = '0001';

注意，你也可是使用NULL对表进行更新，不过更新的字段必须满足没有主键和NOT NULL的约束条件。

7.3.3 多列更新

示例：

UPDATE ProductSET sale_price = sale_price * 10,purchase_price = purchase_price / 2
WHERE product_type = '厨房用具';SELECT * FROM Product;

8.`PyMySQL`

8.1 安装

pip install PyMySQL

8.2 连接

import pymysql# 这里登陆到我之前创建的admin账户
db = pymysql.connect(host='localhost',user='admin',password='mysql123',database='shop',				charset='utf8mb4',cursorclass=pymysql.cursors.DictCursor
)

host：数据库服务器地址，默认localhost；

user：所要登陆的用户名；

password：用户的登录密码；

database：所要连接的数据库库名；

charset：使用的字符类型；

cursorclass：定义游标使用的类型，通过指定游标使用的类型，在返回输出的结果时将按照指定的类型进行返回。例如，这里设置为字典游标。

8.3 创建游标

关于游标，可以理解为在命令行中的光标。在命令行中，我们是在光标处键入语句的。这里游标的起到类似作用。

# 创建游标
cursor = db.cursor()

实际上，除了在初始化connect的实例时指定游标类型，我们在初始化游标时也可以指定游标类型，默认为元组类型。

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

cursors共支持四类游标：

Cursor: 默认，元组类型

DictCursor: 字典类型

SSCursor: 无缓冲元组类型

SSDictCursor: 无缓冲字典类型

8.4 方法

初始化完类connect和cursor的实例后，我们先来了解一下这两个类下包含的方法。
了解这些方法有利于我们后面在python下操作mysql：

connect下的类方法：

close()：在完成操作后，需要关闭与数据库之间的连接；

commit()：如果执行语句中发生了数据更改，需要提交更改到稳定的存储器；
-cursor(cursor=None)：创建一个游标，前面我们在初始化connect类是指定了游标类型，通过cursor初始化游标时，也可以进行游标类型指定；

rollback()：事务回滚；

pymysql.cursors下的类方法：

close()：结束时，关闭游标；

execute()：通过游标执行语句；

executemany()：通过游标执行多条语句；

fetchone()：获取单条数据；

fetchmany(size=None)：获取size条数据；

fetchall()：获取多条数据；

scroll(value, mode)：数据的查询操作都是基于游标，可以通过scroll控制游标的位置。

mode=absolute：绝对位置移动，控制游标位置到上一次查询的第value条数据，最小值为0；

mode=relative：相对位置移动，基于当前位置，跳过value条数据；

8.5 示例

示例1：创建表和插入数据。

Python 3.7.10 (default, Feb 26 2021, 13:06:18) [MSC v.1916 64 bit (AMD64)]
Type 'copyright', 'credits' or 'license' for more information
IPython 7.21.0 -- An enhanced Interactive Python. Type '?' for help.In [1]: import pymysqlIn [2]: # 以admin身份连接到数据库shop...: connection = pymysql.connect(...:     host='localhost',...:     user='admin',...:     password='mysql123',...:     database='shop',...:     charset='utf8mb4',...: )In [3]: # 创建游标...: cursor = connection.cursor(cursor=pymysql.cursors.DictCursor)In [4]: # 1. 创建了一个表...: sql = """...: CREATE TABLE Employee(...:     id INT PRIMARY KEY,...:     name CHAR(15) NOT NULL...:     )...:     """In [5]: # 提交执行...: cursor.execute(sql)
Out[5]: 0In [6]: # 2. 往表中插入数据...: sql = "INSERT INTO Employee (id, name) VALUES (%s, %s)"...: values = [(1, 'XiaoBai'),...:           (2, 'XiaoHei'),...:           (3, 'XiaoHong'),...:           (4, 'XiaoMei'),...:           (5, 'XiaoLi')]In [7]: try:...:     # 通过executemany可以插入多条数据...:     cursor.executemany(sql, values)...:     # 提交事务...:     connection.commit()...: except:...:     connection.rollback()...:In [8]: # 3. 关闭光标及连接...: cursor.close()...: connection.close()

示例2：继续执行查询工作。

(base) PS D:\Project\PyScripts> ipython
Python 3.7.10 (default, Feb 26 2021, 13:06:18) [MSC v.1916 64 bit (AMD64)]
Type 'copyright', 'credits' or 'license' for more information
IPython 7.21.0 -- An enhanced Interactive Python. Type '?' for help.In [1]: import pymysqlIn [2]: # 以admin身份连接到数据库shop...: connection = pymysql.connect(...:     host='localhost',...:     user='admin',...:     password='mysql123',...:     database='shop',...:     charset='utf8mb4',...: )In [3]: with connection:...:     # 创建游标...:     cursor = connection.cursor(cursor=pymysql.cursors.DictCursor)...:...:     # 1. 通过fetchone只查询一条...:     cursor.execute("SHOW CREATE TABLE Employee")...:     result = cursor.fetchone()...:     print(f'查询结果1： \n{result}')...:...:     # 2. 通过fetchmany查询size条...:     cursor.execute("DESC Employee")...:     result = cursor.fetchmany(size=2)...:     print(f'查询结果2： \n{result}')...:...:     # 3. 通过fetchall查询所有...:     cursor.execute("SELECT * FROM Employee")...:     result = cursor.fetchall()...:     print(f'查询结果3： \n{result}')...:...:     # 4. 通过scroll回滚到第0条进行查询...:     cursor.scroll(0, mode='absolute')...:     result = cursor.fetchone()...:     print(f'查询结果4： \n{result}')...: ^I^I...:     # 5. 通过scroll跳过2条进行查询...:     cursor.scroll(2, mode='relative')...:     result = cursor.fetchone()...:     print(f'查询结果5： \n{result}')...:...:     cursor.close()...:
查询结果1：
{'Table': 'Employee', 'Create Table': 'CREATE TABLE `employee` (\n  `id` int NOT NULL,\n  `name` char(15) NOT NULL,\n  PRIMARY KEY (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci'}
查询结果2：
[{'Field': 'id', 'Type': 'int', 'Null': 'NO', 'Key': 'PRI', 'Default': None, 'Extra': ''}, {'Field': 'name', 'Type': 'char(15)', 'Null': 'NO', 'Key': '', 'Default': None, 'Extra': ''}]
查询结果3：
[{'id': 1, 'name': 'XiaoBai'}, {'id': 2, 'name': 'XiaoHei'}, {'id': 3, 'name': 'XiaoHong'}, {'id': 4, 'name': 'XiaoMei'}, {'id': 5, 'name': 'XiaoLi'}]
查询结果4：
{'id': 1, 'name': 'XiaoBai'}
查询结果5：
{'id': 4, 'name': 'XiaoMei'}

示例3：演示SQL注入问题
- 先建立一个表并插入数据：

(base) PS D:\Project\PyScripts> ipython
Python 3.7.10 (default, Feb 26 2021, 13:06:18) [MSC v.1916 64 bit (AMD64)]
Type 'copyright', 'credits' or 'license' for more information
IPython 7.21.0 -- An enhanced Interactive Python. Type '?' for help.In [1]: import pymysqlIn [2]: # 以admin身份连接到数据库shop...: connection = pymysql.connect(...:     host='localhost',...:     user='admin',...:     password='mysql123',...:     database='shop',...:     charset='utf8mb4',...: )In [3]: # 创建游标...: cursor = connection.cursor(cursor=pymysql.cursors.DictCursor)In [4]: sql = """...:         CREATE TABLE UserInfo(...:           id INT PRIMARY KEY,...:           name VARCHAR(15),...:           password CHAR(15) NOT NULL...:           )...:     """In [5]: cursor.execute(sql)
Out[5]: 0In [6]: sql = "INSERT INTO UserInfo (id, name, password) VALUES (%s, %s, %s)"...: values = [(1, 'XiaoBai', '123'),...:           (2, 'XiaoHei', '234'),...:           (3, 'XiaoHong', '567'),...:           (4, 'XiaoMei', '321'),...:           (5, 'XiaoLi', '789')]In [7]: cursor.executemany(sql, values)...: connection.commit()

根据输入判定登陆是否成功：

(base) PS D:\Project\PyScripts> ipython
Python 3.7.10 (default, Feb 26 2021, 13:06:18) [MSC v.1916 64 bit (AMD64)]
Type 'copyright', 'credits' or 'license' for more information
IPython 7.21.0 -- An enhanced Interactive Python. Type '?' for help.In [1]: import pymysqlIn [2]: # 以admin身份连接到数据库shop...: connection = pymysql.connect(...:     host='localhost',...:     user='admin',...:     password='mysql123',...:     database='shop',...:     charset='utf8mb4',...: )In [3]: # 创建游标...: cursor = connection.cursor(cursor=pymysql.cursors.DictCursor)In [4]: while True:...:     user = input("输入用户：").strip()...:     password = input("输入密码：").strip()...:     sql = "select name, password from UserInfo where name='%s' and password='%s' " % (user, password)...:...:     cursor.execute(sql)...:     # 打印用户和密码...:     result=cursor.fetchone()...:     print(result)...:...:     if result:...:         print("成功登陆\n")...:     else:...:         print("登陆失败\n")...:
输入用户：XiaoBai
输入密码：123
{'name': 'XiaoBai', 'password': '123'}
成功登陆输入用户：XiaoBai
输入密码：321
None
登陆失败输入用户：XiaoBai' -- dsd
输入密码：321
{'name': 'XiaoBai', 'password': '123'}
成功登陆输入用户：XiaoBai --
输入密码：321
None
登陆失败输入用户：XiaoBai' --
输入密码：321
---------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)
<ipython-input-4-b8f895a664ca> in <module>4     sql = "select name, password from UserInfo where name='%s' and password='%s' " % (user, password)5
----> 6     cursor.execute(sql)7     # 打印用户和密码8     result=cursor.fetchone()D:\Software\Anaconda3\lib\site-packages\pymysql\cursors.py in execute(self, query, args)168         query = self.mogrify(query, args)169
--> 170         result = self._query(query)171         self._executed = query172         return resultD:\Software\Anaconda3\lib\site-packages\pymysql\cursors.py in _query(self, q)326         self._last_executed = q327         self._clear_result()
--> 328         conn.query(q)329         self._do_get_result()330         return self.rowcountD:\Software\Anaconda3\lib\site-packages\pymysql\connections.py in query(self, sql, unbuffered)514                 sql = sql.encode(self.encoding, 'surrogateescape')515         self._execute_command(COMMAND.COM_QUERY, sql)
--> 516         self._affected_rows = self._read_query_result(unbuffered=unbuffered)517         return self._affected_rows518D:\Software\Anaconda3\lib\site-packages\pymysql\connections.py in _read_query_result(self, unbuffered)725         else:726             result = MySQLResult(self)
--> 727             result.read()728         self._result = result729         if result.server_status is not None:D:\Software\Anaconda3\lib\site-packages\pymysql\connections.py in read(self)1064     def read(self):1065         try:
-> 1066             first_packet = self.connection._read_packet()10671068             if first_packet.is_ok_packet():D:\Software\Anaconda3\lib\site-packages\pymysql\connections.py in _read_packet(self, packet_type)681682         packet = packet_type(buff, self.encoding)
--> 683         packet.check_error()684         return packet685D:\Software\Anaconda3\lib\site-packages\pymysql\protocol.py in check_error(self)218             errno = self.read_uint16()219             if DEBUG: print("errno =", errno)
--> 220             err.raise_mysql_exception(self._data)221222     def dump(self):D:\Software\Anaconda3\lib\site-packages\pymysql\err.py in raise_mysql_exception(data)107         errval = data[3:].decode('utf-8', 'replace')108     errorclass = error_map.get(errno, InternalError)
--> 109     raise errorclass(errno, errval)ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '321'' at line 1")

可以看出，第1组和第2组验证正常，但是第三组出现了异常，输入错误的密码却可以正确登陆。

这是因为在MySQL中--的含义是注释，如果通过字符串进行拼接：
select name, password from UserInfo where name='XiaoBai' -- dsd' and password='321'
实际等价于：
select name, password from UserInfo where name='XiaoBai'
解决办法：通过execute或者executemany来进行拼接。将语句：
sql = "select name, password from UserInfo where name='%s' and password='%s' " % (user, password)
cursor.execute(sql)
改为：
sql = "select name, password from UserInfo where name=%s and password=%s"
cursor.execute(sql, (user, password))

fun-rec/docs/第二章推荐系统实战/2.2新闻推荐系统实战/docs/2.2.1.1 Mysql基础.md ↩︎ ↩︎
SHOW CREATE DATABASE Syntax ↩︎

标签：网站建设企业官网项目流程 UI设计前端开发