Task02：数据库的基本使用（MySQL）
本文的所有内容均参照教程进行实践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
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 ID
FROM Product;
4.5.4 常数的查询
USE shop;
SELECT
'商品' AS string,
'2009-05-24' AS date,
product_id,
product_name
FROM Product;
看结果是新增了两列，属于人工添加的备注信息
4.5.5 查询结果去重
- 对单列结果进行去重
SELECT
DISTINCT regist_date
FROM Product;
- 对组合结果进行去重
USE shop;
SELECT
DISTINCT product_type, regist_date
FROM Product;
4.5.6 指定查询条件
语法：
SELECT <字段名>, ……
FROM <表名>
WHERE <条件表达式>;
示例：
SELECT product_name
FROM Product
WHERE product_type = '衣服';
4.6 表的复制
4.6.1 复制全部数据
-- 将整个表复制过来
CREATE TABLE Product_COPY1
SELECT * FROM Product;
SELECT * FROM Product_COPY1;
4.6.1 复制表的结构
USE shop;
-- 通过LIKE复制表结构
CREATE TABLE Product_COPY2
LIKE 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_type
FROM Product
WHERE sale_price = 500;
5.2.2 NULL的处理
不能对NULL使用任何比较运算符，只能通过IS NULL语句来判断：
SELECT
product_name,
purchase_price
FROM Product
WHERE 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_price
FROM Product
WHERE NOT sale_price >= 1000;
5.3.2 AND
SELECT product_type, sale_price
FROM Product
WHERE product_type = '厨房用具'
AND sale_price >= 3000;
5.3.3 OR
SELECT product_type, sale_price
FROM Product
WHERE 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 Product
GROUP 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 Product
WHERE 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 Product
GROUP 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 Product
GROUP BY product_type
HAVING 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_price
FROM 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_price
FROM 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_price
FROM 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的默认值设为0
purchase_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 Product
SET regist_date = '2009-10-10';
SELECT * FROM Product;
7.3.2 指定条件
语法：
UPDATE <表名>
SET <列名> = <表达式>
WHERE <条件>;
示例：
UPDATE Product
SET regist_date = '2021-10-30'
WHERE product_id = '0001';
注意，你也可是使用NULL对表进行更新，不过更新的字段必须满足没有主键和NOT NULL的约束条件。
7.3.3 多列更新
示例：
UPDATE Product
SET
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 pymysql
In [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]: 0
In [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 pymysql
In [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 pymysql
In [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]: 0
In [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 pymysql
In [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 = query
172 return result
D:\Software\Anaconda3\lib\site-packages\pymysql\cursors.py in _query(self, q)
326 self._last_executed = q
327 self._clear_result()
--> 328 conn.query(q)
329 self._do_get_result()
330 return self.rowcount
D:\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_rows
518
D:\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 = result
729 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()
1067
1068 if first_packet.is_ok_packet():
D:\Software\Anaconda3\lib\site-packages\pymysql\connections.py in _read_packet(self, packet_type)
681
682 packet = packet_type(buff, self.encoding)
--> 683 packet.check_error()
684 return packet
685
D:\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)
221
222 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))
852
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
