本文详细介绍了如何使用MySQL进行高级操作，包括创建数据库、商家表和商品表，以及添加数据。通过查询操作展示了商品信息，并对商品进行了分类统计。使用Python的pymysql库实现了数据库连接、SQL查询、增删改操作和事务处理。同时，讨论了SQL注入和参数占位防止注入的安全措施。此外，还涉及到了事务的四大特性、储存引擎的选择及索引的创建和管理。
MySQL高级操作
模拟拼多多数据库
创建数据库
create database pdd;
use pdd;
商家表
create table merchants(
id int unsigned primary key auto_increment not null, #商家编号
name varchar(40), #店铺名
phone_number varchar(20), #联系方式
star_num int unsigned default 0, #关注量
sold_num int unsigned default 0 #售出商品量
);
添加数据
insert into merchants values
(default,'梦游专卖店','11122233344',100000,12000),
(default,'学海书城','52013148311',200000,10000),
(default,'联想专卖店','12581818181',300000,20000),
(default,'小熊零食店','60606068888',120000,12000),
(default,'邻家女孩','99666699778',100400,130000),
(default,'隔壁小伙','66666666666',400000,140000),
(default,'莉莉数码配件','11012091100',100000,2800),
(default,'小潮品','12345678901',100,1),
(default,'高考小作坊','40440440444',230000,24000),
(default,'飞燕图书专卖店','100859',10000,8000),
(default,'简繁文具店','393882783636',5000,6000),
(default,'梦乡图书','37373723984',140000,11000),
(default,'啥都卖','9999999999',10000000,1200000);
select * from merchants;
+----+----------------+--------------+----------+----------+
| id | name | phone_number | star_num | sold_num |
+----+----------------+--------------+----------+----------+
| 1 | 梦游专卖店 | 11122233344 | 100000 | 12000 |
| 2 | 学海书城 | 52013148311 | 200000 | 10000 |
| 3 | 联想专卖店 | 12581818181 | 300000 | 20000 |
| 4 | 小熊零食店 | 60606068888 | 120000 | 12000 |
| 5 | 邻家女孩 | 99666699778 | 100400 | 130000 |
| 6 | 隔壁小伙 | 66666666666 | 400000 | 140000 |
| 7 | 莉莉数码配件 | 11012091100 | 100000 | 2800 |
| 8 | 小潮品 | 12345678901 | 100 | 1 |
| 9 | 高考小作坊 | 40440440444 | 230000 | 24000 |
| 10 | 飞燕图书专卖店 | 100859 | 10000 | 8000 |
| 11 | 简繁文具店 | 393882783636 | 5000 | 6000 |
| 12 | 梦乡图书 | 37373723984 | 140000 | 11000 |
| 13 | 啥都卖 | 9999999999 | 10000000 | 1200000 |
+----+----------------+--------------+----------+----------+
商品表
create table goods(
id int unsigned primary key auto_increment not null, #商品编号
merchant_id int unsigned not null,
foreign key(merchant_id) references merchants(id), #商家编号
name varchar(200) not null, #商品名
num int unsigned not null, #商品剩余数量
sold_num int unsigned not null, #已售量
category varchar(40) not null, #商品类别
brand varchar(30) , #品牌名
price decimal(10,3) not null default 0, #商品价格
showed bit not null default 1, #是否显示
on_sale bit not null default 0 #是否有优惠
);
添加数据
insert into goods values
(default,9,"2022解题达人数学选择填空题",20000,20000,"图书","腾远高考",7.92,default,default),
(default,2,"C Primer Plus中文第6版",1000,8000,"图书","人民邮电出版社",53.8,default,default),
(default,1,"大型单机电脑合集",10000000,3984,"电子资源",null,100,default,default),
(default,9,"2022五年高考三年模拟数学",10000,30000,"图书","曲一线",18.8,default,default),
(default,5,"夏季女装",8000,12000,"服装","南极人",29.9,default,1),
(default,6,"夏季男装",7000,10000,"服装","七匹狼",39.33,default,1),
(default,13,"2022五年高考三年模拟数学",20000,100000,"图书","曲一线",20,default,default),
(default,4,"网红手剥笋香条",12000,6012,"食物","庆辉",10.33,default,default),
(default,7,"5V2A手机充电器",1000,600,"数码电子","华为",32.9,default,default),
(default,12,"2022五年高考三年模拟语文",1000,9000,"图书","腾远高考",21.11,0,default),
(default,12,"鲁迅说过的那些话",2000,1200,"图书","鲁迅出版社",31.33,default,1),
(default,9,"2022解题达人数学选择填空题",20000,100000,"图书","腾远高考",7.88,default,default),
(default,13,"内蒙古烤羊腿",200,16,"食物",null,7.92,0,default),
(default,10,"2022解题达人数学选择填空题",2000,3000,"图书","腾远高考",7.44,default,1),
(default,11,"ST头刷题笔",20000,77000,"文具","晨光",0.99,default,1),
(default,12,"2022五年高考三年模拟数学",30000,20000,"图书","曲一线",18,default,default),
(default,3,"联想拯救者R7000笔记本电脑",1000,800,"数码电子","联想",5398,default,1),
(default,2,"2022五年高考三年模拟数学",4000,40000,"图书","曲一线",20,default,default),
(default,10,"2022五年高考三年模拟数学",800,60000,"图书","曲一线",16.6,default,1),
(default,13,"夏季男装",6000,1200,"服装","富贵鸟",35.82,default,1);
select * from goods;
+----+-------------+----------------------------+----------+----------+----------+----------------+----------+----------------+------------------+
| id | merchant_id | name | num | sold_num | category | brand | price | showed | on_sale |
+----+-------------+----------------------------+----------+----------+----------+----------------+----------+----------------+------------------+
| 1 | 9 | 2022解题达人数学选择填空题 | 20000 | 20000 | 图书 | 腾远高考 | 7.920 | 0x01 | 0x00 |
| 2 | 2 | C Primer Plus中文第6版 | 1000 | 8000 | 图书 | 人民邮电出版社 | 53.800 | 0x01 | 0x00 |
| 3 | 1 | 大型单机电脑合集 | 10000000 | 3984 | 电子资源 | NULL | 100.000 | 0x01 | 0x00 |
| 4 | 9 | 2022五年高考三年模拟数学 | 10000 | 30000 | 图书 | 曲一线 | 18.800 | 0x01 | 0x00 |
| 5 | 5 | 夏季女装 | 8000 | 12000 | 服装 | 南极人 | 29.900 | 0x01 | 0x01 |
| 6 | 6 | 夏季男装 | 7000 | 10000 | 服装 | 七匹狼 | 39.330 | 0x01 | 0x01 |
| 7 | 13 | 2022五年高考三年模拟数学 | 20000 | 100000 | 图书 | 曲一线 | 20.000 | 0x01 | 0x00 |
| 8 | 4 | 网红手剥笋香条 | 12000 | 6012 | 食物 | 庆辉 | 10.330 | 0x01 | 0x00 |
| 9 | 7 | 5V2A手机充电器 | 1000 | 600 | 数码电子 | 华为 | 32.900 | 0x01 | 0x00 |
| 10 | 12 | 2022五年高考三年模拟语文 | 1000 | 9000 | 图书 | 腾远高考 | 21.110 | 0x01 | 0x00 |
| 11 | 12 | 鲁迅说过的那些话 | 2000 | 1200 | 图书 | 鲁迅出版社 | 31.330 | 0x01 | 0x01 |
| 12 | 9 | 2022解题达人数学选择填空题 | 20000 | 100000 | 图书 | 腾远高考 | 7.880 | 0x01 | 0x00 |
| 13 | 13 | 内蒙古烤羊腿 | 200 | 16 | 食物 | NULL | 7.920 | 0x01 | 0x00 |
| 14 | 10 | 2022解题达人数学选择填空题 | 2000 | 3000 | 图书 | 腾远高考 | 7.440 | 0x01 | 0x01 |
| 15 | 11 | ST头刷题笔 | 20000 | 77000 | 文具 | 晨光 | 0.990 | 0x01 | 0x01 |
| 16 | 12 | 2022五年高考三年模拟数学 | 30000 | 20000 | 图书 | 曲一线 | 18.000 | 0x01 | 0x00 |
| 17 | 3 | 联想拯救者R7000笔记本电脑 | 1000 | 800 | 数码电子 | 联想 | 5398.000 | 0x01 | 0x01 |
| 18 | 2 | 2022五年高考三年模拟数学 | 4000 | 40000 | 图书 | 曲一线 | 20.000 | 0x01 | 0x00 |
| 19 | 10 | 2022五年高考三年模拟数学 | 800 | 60000 | 图书 | 曲一线 | 16.600 | 0x01 | 0x01 |
| 20 | 13 | 夏季男装 | 6000 | 1200 | 服装 | 富贵鸟 | 35.820 | 0x01 | 0x01 |
+----+-------------+----------------------------+----------+----------+----------+----------------+----------+----------------+------------------+
20 rows in set (0.00 sec)
查询可显示商品
select * from goods where showed=1;
+----+-------------+----------------------------+----------+----------+----------+----------------+----------+----------------+------------------+
| id | merchant_id | name | num | sold_num | category | brand | price | showed | on_sale |
+----+-------------+----------------------------+----------+----------+----------+----------------+----------+----------------+------------------+
| 1 | 9 | 2022解题达人数学选择填空题 | 20000 | 20000 | 图书 | 腾远高考 | 7.920 | 0x01 | 0x00 |
| 2 | 2 | C Primer Plus中文第6版 | 1000 | 8000 | 图书 | 人民邮电出版社 | 53.800 | 0x01 | 0x00 |
| 3 | 1 | 大型单机电脑合集 | 10000000 | 3984 | 电子资源 | NULL | 100.000 | 0x01 | 0x00 |
| 4 | 9 | 2022五年高考三年模拟数学 | 10000 | 30000 | 图书 | 曲一线 | 18.800 | 0x01 | 0x00 |
| 5 | 5 | 夏季女装 | 8000 | 12000 | 服装 | 南极人 | 29.900 | 0x01 | 0x01 |
| 6 | 6 | 夏季男装 | 7000 | 10000 | 服装 | 七匹狼 | 39.330 | 0x01 | 0x01 |
| 7 | 13 | 2022五年高考三年模拟数学 | 20000 | 100000 | 图书 | 曲一线 | 20.000 | 0x01 | 0x00 |
| 8 | 4 | 网红手剥笋香条 | 12000 | 6012 | 食物 | 庆辉 | 10.330 | 0x01 | 0x00 |
| 9 | 7 | 5V2A手机充电器 | 1000 | 600 | 数码电子 | 华为 | 32.900 | 0x01 | 0x00 |
| 11 | 12 | 鲁迅说过的那些话 | 2000 | 1200 | 图书 | 鲁迅出版社 | 31.330 | 0x01 | 0x01 |
| 12 | 9 | 2022解题达人数学选择填空题 | 20000 | 100000 | 图书 | 腾远高考 | 7.880 | 0x01 | 0x00 |
| 14 | 10 | 2022解题达人数学选择填空题 | 2000 | 3000 | 图书 | 腾远高考 | 7.440 | 0x01 | 0x01 |
| 15 | 11 | ST头刷题笔 | 20000 | 77000 | 文具 | 晨光 | 0.990 | 0x01 | 0x01 |
| 16 | 12 | 2022五年高考三年模拟数学 | 30000 | 20000 | 图书 | 曲一线 | 18.000 | 0x01 | 0x00 |
| 17 | 3 | 联想拯救者R7000笔记本电脑 | 1000 | 800 | 数码电子 | 联想 | 5398.000 | 0x01 | 0x01 |
| 18 | 2 | 2022五年高考三年模拟数学 | 4000 | 40000 | 图书 | 曲一线 | 20.000 | 0x01 | 0x00 |
| 19 | 10 | 2022五年高考三年模拟数学 | 800 | 60000 | 图书 | 曲一线 | 16.600 | 0x01 | 0x01 |
| 20 | 13 | 夏季男装 | 6000 | 1200 | 服装 | 富贵鸟 | 35.820 | 0x01 | 0x01 |
+----+-------------+----------------------------+----------+----------+----------+----------------+----------+----------------+------------------+
18 rows in set (0.00 sec)
查询图书商品
select * from goods where showed=1 and category='图书';
+----+-------------+----------------------------+-------+----------+----------+----------------+--------+----------------+------------------+
| id | merchant_id | name | num | sold_num | category | brand | price | showed | on_sale |
+----+-------------+----------------------------+-------+----------+----------+----------------+--------+----------------+------------------+
| 1 | 9 | 2022解题达人数学选择填空题 | 20000 | 20000 | 图书 | 腾远高考 | 7.920 | 0x01 | 0x00 |
| 2 | 2 | C Primer Plus中文第6版 | 1000 | 8000 | 图书 | 人民邮电出版社 | 53.800 | 0x01 | 0x00 |
| 4 | 9 | 2022五年高考三年模拟数学 | 10000 | 30000 | 图书 | 曲一线 | 18.800 | 0x01 | 0x00 |
| 7 | 13 | 2022五年高考三年模拟数学 | 20000 | 100000 | 图书 | 曲一线 | 20.000 | 0x01 | 0x00 |
| 11 | 12 | 鲁迅说过的那些话 | 2000 | 1200 | 图书 | 鲁迅出版社 | 31.330 | 0x01 | 0x01 |
| 12 | 9 | 2022解题达人数学选择填空题 | 20000 | 100000 | 图书 | 腾远高考 | 7.880 | 0x01 | 0x00 |
| 14 | 10 | 2022解题达人数学选择填空题 | 2000 | 3000 | 图书 | 腾远高考 | 7.440 | 0x01 | 0x01 |
| 16 | 12 | 2022五年高考三年模拟数学 | 30000 | 20000 | 图书 | 曲一线 | 18.000 | 0x01 | 0x00 |
| 18 | 2 | 2022五年高考三年模拟数学 | 4000 | 40000 | 图书 | 曲一线 | 20.000 | 0x01 | 0x00 |
| 19 | 10 | 2022五年高考三年模拟数学 | 800 | 60000 | 图书 | 曲一线 | 16.600 | 0x01 | 0x01 |
+----+-------------+----------------------------+-------+----------+----------+----------------+--------+----------------+------------------+
10 rows in set (0.00 sec)
求所有”曲一线“商品的平均价格并保留两位小数
select round(avg(price),2) as book_avg_price from goods where brand='曲一线';
+----------------+
| book_avg_price |
+----------------+
| 18.68 |
+----------------+
1 row in set (0.00 sec)
显示每种商品的平均价格，最高价，最低价，数量
select category,
round(avg(price),2) as avg_price ,
max(price) as most_expensive,
min(price) as cheapest,
count(*) as num
from goods group by category;
+----------+-----------+----------------+----------+-----+
| category | avg_price | most_expensive | cheapest | num |
+----------+-----------+----------------+----------+-----+
| 图书 | 20.26 | 53.800 | 7.440 | 11 |
| 电子资源 | 100.00 | 100.000 | 100.000 | 1 |
| 服装 | 35.02 | 39.330 | 29.900 | 3 |
| 食物 | 9.13 | 10.330 | 7.920 | 2 |
| 数码电子 | 2715.45 | 5398.000 | 32.900 | 2 |
| 文具 | 0.99 | 0.990 | 0.990 | 1 |
+----------+-----------+----------------+----------+-----+
将“2022五年高考三年模拟数学”商品按价格升序排序,并显示店名和店铺关注量
select
goods.name,goods.num,goods.sold_num,goods.price,
merchants.name as merchant_name,merchants.star_num
from goods inner join merchants
on goods.merchant_id = merchants.id
where goods.name='2022五年高考三年模拟数学'
order by price asc;
+--------------------------+-------+----------+--------+----------------+----------+
| name | num | sold_num | price | merchant_name | star_num |
+--------------------------+-------+----------+--------+----------------+----------+
| 2022五年高考三年模拟数学 | 800 | 60000 | 16.600 | 飞燕图书专卖店 | 10000 |
| 2022五年高考三年模拟数学 | 30000 | 20000 | 18.000 | 梦乡图书 | 140000 |
| 2022五年高考三年模拟数学 | 10000 | 30000 | 18.800 | 高考小作坊 | 230000 |
| 2022五年高考三年模拟数学 | 20000 | 100000 | 20.000 | 啥都卖 | 10000000 |
| 2022五年高考三年模拟数学 | 4000 | 40000 | 20.000 | 学海书城 | 200000 |
+--------------------------+-------+----------+--------+----------------+----------+
5 rows in set (0.00 sec)
将查询结果插入到其他表中
为了提取商品分类形成一张‘商品分类表’，需要将分组查询的查询结果插入到新表中。
create table categories (
id int unsigned primary key auto_increment not null,
name varchar(40),
num int unsigned default 0
); #创建categories表
insert into categories(name,num) #插到categories的name,num列
select category,count(*) from goods group by category; #查询结果当做数据源
select * from categories;
+----+----------+------+
| id | name | num |
+----+----------+------+
| 1 | 图书 | 11 |
| 2 | 电子资源 | 1 |
| 3 | 服装 | 3 |
| 4 | 食物 | 2 |
| 5 | 数码电子 | 2 |
| 6 | 文具 | 1 |
+----+----------+------+
6 rows in set (0.00 sec)
使用连接更新字段数据
既然有了categories表，我们可以把goods里的category数据更新成成categories编号id
update
goods left join categories
on goods.category=categories.name #将连接的结果当成一张表
set goods.category=categories.id;
select * from goods;
+----+-------------+----------------------------+----------+----------+----------+----------------+----------+----------------+------------------+
| id | merchant_id | name | num | sold_num | category | brand | price | showed | on_sale |
+----+-------------+----------------------------+----------+----------+----------+----------------+----------+----------------+------------------+
| 1 | 9 | 2022解题达人数学选择填空题 | 20000 | 20000 | 1 | 腾远高考 | 7.920 | 0x01 | 0x00 |
| 2 | 2 | C Primer Plus中文第6版 | 1000 | 8000 | 1 | 人民邮电出版社 | 53.800 | 0x01 | 0x00 |
| 3 | 1 | 大型单机电脑合集 | 10000000 | 3984 | 2 | NULL | 100.000 | 0x01 | 0x00 |
| 4 | 9 | 2022五年高考三年模拟数学 | 10000 | 30000 | 1 | 曲一线 | 18.800 | 0x01 | 0x00 |
| 5 | 5 | 夏季女装 | 8000 | 12000 | 3 | 南极人 | 29.900 | 0x01 | 0x01 |
| 6 | 6 | 夏季男装 | 7000 | 10000 | 3 | 七匹狼 | 39.330 | 0x01 | 0x01 |
| 7 | 13 | 2022五年高考三年模拟数学 | 20000 | 100000 | 1 | 曲一线 | 20.000 | 0x01 | 0x00 |
| 8 | 4 | 网红手剥笋香条 | 12000 | 6012 | 4 | 庆辉 | 10.330 | 0x01 | 0x00 |
| 9 | 7 | 5V2A手机充电器 | 1000 | 600 | 5 | 华为 | 32.900 | 0x01 | 0x00 |
| 10 | 12 | 2022五年高考三年模拟语文 | 1000 | 9000 | 1 | 腾远高考 | 21.110 | 0x00 | 0x00 |
| 11 | 12 | 鲁迅说过的那些话 | 2000 | 1200 | 1 | 鲁迅出版社 | 31.330 | 0x01 | 0x01 |
| 12 | 9 | 2022解题达人数学选择填空题 | 20000 | 100000 | 1 | 腾远高考 | 7.880 | 0x01 | 0x00 |
| 13 | 13 | 内蒙古烤羊腿 | 200 | 16 | 4 | NULL | 7.920 | 0x00 | 0x00 |
| 14 | 10 | 2022解题达人数学选择填空题 | 2000 | 3000 | 1 | 腾远高考 | 7.440 | 0x01 | 0x01 |
| 15 | 11 | ST头刷题笔 | 20000 | 77000 | 6 | 晨光 | 0.990 | 0x01 | 0x01 |
| 16 | 12 | 2022五年高考三年模拟数学 | 30000 | 20000 | 1 | 曲一线 | 18.000 | 0x01 | 0x00 |
| 17 | 3 | 联想拯救者R7000笔记本电脑 | 1000 | 800 | 5 | 联想 | 5398.000 | 0x01 | 0x01 |
| 18 | 2 | 2022五年高考三年模拟数学 | 4000 | 40000 | 1 | 曲一线 | 20.000 | 0x01 | 0x00 |
| 19 | 10 | 2022五年高考三年模拟数学 | 800 | 60000 | 1 | 曲一线 | 16.600 | 0x01 | 0x01 |
| 20 | 13 | 夏季男装 | 6000 | 1200 | 3 | 富贵鸟 | 35.820 | 0x01 | 0x01 |
+----+-------------+----------------------------+----------+----------+----------+----------------+----------+----------------+------------------+
20 rows in set (0.00 sec)
利用已有表的数据创建新表
我们可以用已有表的数据创建新表，来对品牌进行与商品分类类似的操作，创建“品牌表"
create table brands (
id int unsigned primary key auto_increment not null,
name varchar(40) ,
num int unsigned default 0
) select brand as name,count(*) as num from goods group by brand;
#创建表的同时用goods分组查询后的的brand更新brands的name,count(*)更新brands的num
select * from brands;
+----+----------------+------+
| id | name | num |
+----+----------------+------+
| 1 | 腾远高考 | 4 |
| 2 | 人民邮电出版社 | 1 |
| 3 | NULL | 2 |
| 4 | 曲一线 | 5 |
| 5 | 南极人 | 1 |
| 6 | 七匹狼 | 1 |
| 7 | 庆辉 | 1 |
| 8 | 华为 | 1 |
| 9 | 鲁迅出版社 | 1 |
| 10 | 晨光 | 1 |
| 11 | 联想 | 1 |
| 12 | 富贵鸟 | 1 |
+----+----------------+------+
12 rows in set (0.00 sec)
update
goods left join brands
on goods.brand=brands.name #将连接的结果当成一张表
set goods.brand=brands.id;
select * from goods;
+----+-------------+----------------------------+----------+----------+----------+-------+----------+----------------+------------------+
| id | merchant_id | name | num | sold_num | category | brand | price | showed | on_sale |
+----+-------------+----------------------------+----------+----------+----------+-------+----------+----------------+------------------+
| 1 | 9 | 2022解题达人数学选择填空题 | 20000 | 20000 | 1 | 1 | 7.920 | 0x01 | 0x00 |
| 2 | 2 | C Primer Plus中文第6版 | 1000 | 8000 | 1 | 2 | 53.800 | 0x01 | 0x00 |
| 3 | 1 | 大型单机电脑合集 | 10000000 | 3984 | 2 | NULL | 100.000 | 0x01 | 0x00 |
| 4 | 9 | 2022五年高考三年模拟数学 | 10000 | 30000 | 1 | 4 | 18.800 | 0x01 | 0x00 |
| 5 | 5 | 夏季女装 | 8000 | 12000 | 3 | 5 | 29.900 | 0x01 | 0x01 |
| 6 | 6 | 夏季男装 | 7000 | 10000 | 3 | 6 | 39.330 | 0x01 | 0x01 |
| 7 | 13 | 2022五年高考三年模拟数学 | 20000 | 100000 | 1 | 4 | 20.000 | 0x01 | 0x00 |
| 8 | 4 | 网红手剥笋香条 | 12000 | 6012 | 4 | 7 | 10.330 | 0x01 | 0x00 |
| 9 | 7 | 5V2A手机充电器 | 1000 | 600 | 5 | 8 | 32.900 | 0x01 | 0x00 |
| 10 | 12 | 2022五年高考三年模拟语文 | 1000 | 9000 | 1 | 1 | 21.110 | 0x00 | 0x00 |
| 11 | 12 | 鲁迅说过的那些话 | 2000 | 1200 | 1 | 9 | 31.330 | 0x01 | 0x01 |
| 12 | 9 | 2022解题达人数学选择填空题 | 20000 | 100000 | 1 | 1 | 7.880 | 0x01 | 0x00 |
| 13 | 13 | 内蒙古烤羊腿 | 200 | 16 | 4 | NULL | 7.920 | 0x00 | 0x00 |
| 14 | 10 | 2022解题达人数学选择填空题 | 2000 | 3000 | 1 | 1 | 7.440 | 0x01 | 0x01 |
| 15 | 11 | ST头刷题笔 | 20000 | 77000 | 6 | 10 | 0.990 | 0x01 | 0x01 |
| 16 | 12 | 2022五年高考三年模拟数学 | 30000 | 20000 | 1 | 4 | 18.000 | 0x01 | 0x00 |
| 17 | 3 | 联想拯救者R7000笔记本电脑 | 1000 | 800 | 5 | 11 | 5398.000 | 0x01 | 0x01 |
| 18 | 2 | 2022五年高考三年模拟数学 | 4000 | 40000 | 1 | 4 | 20.000 | 0x01 | 0x00 |
| 19 | 10 | 2022五年高考三年模拟数学 | 800 | 60000 | 1 | 4 | 16.600 | 0x01 | 0x01 |
| 20 | 13 | 夏季男装 | 6000 | 1200 | 3 | 12 | 35.820 | 0x01 | 0x01 |
+----+-------------+----------------------------+----------+----------+----------+-------+----------+----------------+------------------+
20 rows in set (0.00 sec)
修改表的结构
将goods表的category和brand字段的类型和字段名更新
alter table goods
change category category_id int unsigned not null,
change brand brand_id int unsigned ;
desc goods;
select * from goods;
+-------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| merchant_id | int unsigned | NO | MUL | NULL | |
| name | varchar(200) | NO | | NULL | |
| num | int unsigned | NO | | NULL | |
| sold_num | int unsigned | NO | | NULL | |
| category_id | int unsigned | NO | | NULL | |
| brand_id | int unsigned | YES | | NULL | |
| price | decimal(10,3) | NO | | 0.000 | |
| showed | bit(1) | NO | | b'1' | |
| on_sale | bit(1) | NO | | b'0' | |
+-------------+---------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)
+----+-------------+----------------------------+----------+----------+-------------+----------+----------+----------------+------------------+
| id | merchant_id | name | num | sold_num | category_id | brand_id | price | showed | on_sale |
+----+-------------+----------------------------+----------+----------+-------------+----------+----------+----------------+------------------+
| 1 | 9 | 2022解题达人数学选择填空题 | 20000 | 20000 | 1 | 1 | 7.920 | 0x01 | 0x00 |
| 2 | 2 | C Primer Plus中文第6版 | 1000 | 8000 | 1 | 2 | 53.800 | 0x01 | 0x00 |
| 3 | 1 | 大型单机电脑合集 | 10000000 | 3984 | 2 | NULL | 100.000 | 0x01 | 0x00 |
| 4 | 9 | 2022五年高考三年模拟数学 | 10000 | 30000 | 1 | 4 | 18.800 | 0x01 | 0x00 |
| 5 | 5 | 夏季女装 | 8000 | 12000 | 3 | 5 | 29.900 | 0x01 | 0x01 |
| 6 | 6 | 夏季男装 | 7000 | 10000 | 3 | 6 | 39.330 | 0x01 | 0x01 |
| 7 | 13 | 2022五年高考三年模拟数学 | 20000 | 100000 | 1 | 4 | 20.000 | 0x01 | 0x00 |
| 8 | 4 | 网红手剥笋香条 | 12000 | 6012 | 4 | 7 | 10.330 | 0x01 | 0x00 |
| 9 | 7 | 5V2A手机充电器 | 1000 | 600 | 5 | 8 | 32.900 | 0x01 | 0x00 |
| 10 | 12 | 2022五年高考三年模拟语文 | 1000 | 9000 | 1 | 1 | 21.110 | 0x00 | 0x00 |
| 11 | 12 | 鲁迅说过的那些话 | 2000 | 1200 | 1 | 9 | 31.330 | 0x01 | 0x01 |
| 12 | 9 | 2022解题达人数学选择填空题 | 20000 | 100000 | 1 | 1 | 7.880 | 0x01 | 0x00 |
| 13 | 13 | 内蒙古烤羊腿 | 200 | 16 | 4 | NULL | 7.920 | 0x00 | 0x00 |
| 14 | 10 | 2022解题达人数学选择填空题 | 2000 | 3000 | 1 | 1 | 7.440 | 0x01 | 0x01 |
| 15 | 11 | ST头刷题笔 | 20000 | 77000 | 6 | 10 | 0.990 | 0x01 | 0x01 |
| 16 | 12 | 2022五年高考三年模拟数学 | 30000 | 20000 | 1 | 4 | 18.000 | 0x01 | 0x00 |
| 17 | 3 | 联想拯救者R7000笔记本电脑 | 1000 | 800 | 5 | 11 | 5398.000 | 0x01 | 0x01 |
| 18 | 2 | 2022五年高考三年模拟数学 | 4000 | 40000 | 1 | 4 | 20.000 | 0x01 | 0x00 |
| 19 | 10 | 2022五年高考三年模拟数学 | 800 | 60000 | 1 | 4 | 16.600 | 0x01 | 0x01 |
| 20 | 13 | 夏季男装 | 6000 | 1200 | 3 | 12 | 35.820 | 0x01 | 0x01 |
+----+-------------+----------------------------+----------+----------+-------------+----------+----------+----------------+------------------+
20 rows in set (0.00 sec)
pymysql包
pymysql这个第三方库让我们可以使用python程序来操作数据库。这称为数据库编程。
使用
导入包
import pymysql
创建连接对象
db_connect = pymysql.connect(host="localhost",port=3306,user="root",password="123",database="pdd",charset="utf8")
- host是服务器地址
- port是端口号
- user是用户名
- password是密码
- database是数据库名
- charset是数据库编码格式
- 返回
pymysql.connections.Connection对象，这里用db_connect接收
获取游标对象
cursor = db_connect.cursor()
- 返回
pymysql.cursors.Cursor对象，即游标对象。 - 游标对象的任务就是执行sql语句
sql查询操作
#准备sql语句
sql="select * from goods;"
#执行sql语句
cursor.execute(sql)
#获取查询结果
row_1=cursor.fetchone() #获取查询结果的一行
print(row_1)
#(1, 9, '2022解题达人数学选择填空题', 20000, 20000, 1, 1, Decimal('7.920'), b'\x01', b'\x00')
rows=cursor.fetchall() #获取查询结果剩下的所有行
print(rows)
"""
(
(2, 2, 'C Primer Plus中文第6版', 1000, 8000, 1, 2, Decimal('53.800'),b'\x01', b'\x00'),
(3, 1, '大型单机电脑合集', 10000000, 3984, 2, None, Decimal('100.000'), b'\x01', b'\x00'),
(4, 9, '2022五年高考三年模拟数学', 10000, 30000, 1, 4, Decimal('18.800'), b'\x01', b'\x00'),
(5, 5, '夏季女装', 8000, 12000, 3, 5, Decimal('29.900'), b'\x01', b'\x01'),
(6, 6, '夏季男装', 7000, 10000, 3, 6, Decimal('39.330'), b'\x01', b'\x01'),
(7, 13, '2022五年高考三年模拟数学', 20000, 100000, 1, 4, Decimal('20.000'), b'\x01', b'\x00'),
(8, 4, '网红手剥笋香条', 12000, 6012, 4, 7, Decimal('10.330'), b'\x01', b'\x00'),
(9, 7, '5V2A手机充电器', 1000, 600, 5, 8, Decimal('32.900'), b'\x01', b'\x00'),
(10, 12, '2022五年高考三年模拟语文', 1000, 9000, 1, 1, Decimal('21.110'), b'\x00', b'\x00'),
(11, 12, '鲁迅说过的那些话', 2000, 1200, 1, 9, Decimal('31.330'), b'\x01', b'\x01'),
(12, 9, '2022解题达人数学选择填空题', 20000, 100000, 1, 1, Decimal('7.880'), b'\x01', b'\x00'),
(13, 13, '内蒙古烤羊腿', 200, 16, 4, None, Decimal('7.920'), b'\x00', b'\x00'),
(14, 10, '2022解题达人数学选择填空题', 2000, 3000, 1, 1, Decimal('7.440'), b'\x01', b'\x01'),
(15, 11, 'ST头刷题笔', 20000, 77000, 6, 10, Decimal('0.990'), b'\x01', b'\x01'),
(16, 12, '2022五年高考三年模拟数学', 30000, 20000, 1, 4, Decimal('18.000'), b'\x01', b'\x00'),
(17, 3, '联想拯救者R7000笔记本电脑', 1000, 800, 5, 11, Decimal('5398.000'), b'\x01', b'\x01'),
(18, 2, '2022五年高考三年模拟数学', 4000, 40000, 1, 4, Decimal('20.000'), b'\x01', b'\x00'),
(19, 10, '2022五年高考三年模拟数学', 800, 60000, 1, 4, Decimal('16.600'), b'\x01', b'\x01'),
(20, 13, '夏季男装', 6000, 1200, 3, 12, Decimal('35.820'), b'\x01', b'\x01')
)
"""
- 查询结果以元组形式返回
- fetchone一行一行接收，fetchall未接收的一起接收
sql增删改操作
#准备sql语句
sql="update goods set num=5000 where id=20"
#执行sql语句
cursor.execute(sql)
try:
db_connect.commit() #提交数据，更新服务器的数据库
except:
db_connect.rollback() #如果提交数据失败，回滚数据(撤销上一步sql操作)
- 只有成功提交了，才能真正修改数据库
关闭游标和连接
cursor.close()
db_connect.close()
sql注入
SQL 注入（SQL Injection）是发生在 Web 程序中数据库层的安全漏洞，是网站存在最多也是最简单的漏洞。主要原因是程序对用户输入数据的合法性没有判断和处理，导致攻击者可以在 Web 应用程序中事先定义好的 SQL 语句中添加额外的 SQL 语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步获取到数据信息。
简而言之，SQL 注入就是在用户输入的字符串中加入 SQL 语句，如果在设计不良的程序中忽略了检查，那么这些注入进去的 SQL 语句就会被数据库服务器误认为是正常的 SQL 语句而运行，攻击者就可以执行计划外的命令或访问未被授权的数据。
如需要用户传入一个姓名，以此来获得对应学号。
sql语句为select * from students where name=‘ …’;
…为需要用户填写的内容
而恶意用户输入:张三‘ or 1=1 or name='李四
这导致sql语句变为select * from students where name=‘ 张三‘ or 1=1 or name='李四’;
这将显示表中的所有数据，泄露信息
参数占位防止sql注入
用%s占位sql语句中需要用户填写的参数，再在程序中通过给execute函数额外传参数来防止sql注入
from pymysql import connect
def main():
student_name = input("请输入：")
student_id = int(input("请输入id:"))
db_connect = pymysql.connect(host="localhost",port="3306",user="root",password="123",database="students_info",charset="utf8")
cursor = db_connect.cursor()
params=[student_name,student_id] #准备参数
sql="select * from students where name=%s and id=%s"
cursor.execute(sql,params) #传入参数
result=cursor.fetchall()
for row in result:
print(row)
cursor.close()
db_connect.close()
if __name__ == '__main__':
main()
"""
请输入：李慧
请输入id:1
(1, '李慧', 16, '女', 110, '1班', 'NO')
"""
事务
事务就是用户定义的一系列执行SQL语句的操作, 这些操作要么完全地执行，要么完全地都不执行， 它是一个不可分割的工作执行单元。
事务的特性保证了数据的安全。
四大特性
-
原子性:一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性
-
一致性：数据库总是从一个一致性的状态转换到另一个一致性的状态。不可能卡在中间状态。
-
隔离性：通常来说，一个事务所做的修改操作在提交事务之前，对于其他事务来说是不可见的。
-
持久性:一旦事务提交，则其所做的修改会永久保存到数据库。
储存引擎
表的存储引擎就是提供存储数据的一种机制，不同存储引擎提供不同的存储机制，存储性能有所不同，功能也有所不同。
在使用事务之前，先要确保表的存储引擎是 InnoDB 类型, 只有这个类型才可以使用事务，MySQL数据库中表的存储引擎默认是 InnoDB 类型。
-- 查看MySQL数据库支持的表的存储引擎
show engine;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
- 常用的表的存储引擎是 InnoDB 和 MyISAM
- InnoDB 是支持事务的
- MyISAM 不支持事务，但访问速度快，对事务没有要求或者以select、insert为主的都可以使用该存储引擎来创建表
查看表使用的储存引擎
show create table students;
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| students | CREATE TABLE `students` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(10) NOT NULL,
`age` tinyint DEFAULT NULL,
`gender` enum('男','女') DEFAULT NULL,
`math_grade` tinyint unsigned DEFAULT NULL,
`class` enum('1班','2班','3班','4班') DEFAULT NULL,
`deleted` enum('NO','YES') DEFAULT 'NO',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb3 |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
- 默认情况下使用InnoDB,支持事务
切换表的储存引擎
alter table students engine = 'MyISAM';
事务的使用
begin; #开启事务
start transaction; #开启事务
...(事务内容，一系列sql语句)
#结束事务
rollback; #回滚事务(回到开始事务时的状态)
commit; #提交事务(更新改变的数据)
- MySQL默认开启自动事务提交功能(autocommit)。即不显式地开始事务的话，每一条sql语句都是一个事务，并且会自动提交
set autocommit = 0;可以取消自动提交。这样每次都要手动开启事务才能真正修改数据库内容。- pymysql也支持事务。commit()提交，rollback()回滚
索引
索引在MySQL中也叫做“键”，它是一个特殊的文件，它保存着数据表里所有记录的位置信息，起着目录的作用。
如果某个字段创建过索引，就可以迅速查询到该字段的数据。
添加索引的优点是查询速度快，但会消耗储存。
查看已有索引
show index from goods;
- 主键列和外键列会自动创建索引
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| goods | 0 | PRIMARY | 1 | id | A | 20 | NULL | NULL | | BTREE | | | YES | NULL |
| goods | 1 | merchant_id | 1 | merchant_id | A | 12 | NULL | NULL | | BTREE | | | YES | NULL |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.01 sec)
创建索引
-- alter table 表名 add index [索引名,可选，不写默认为列名] (列名, ...)
alter table goods add index goods_name (name);
alter table goods add index (num);
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| goods | 0 | PRIMARY | 1 | id | A | 20 | NULL | NULL | | BTREE | | | YES | NULL |
| goods | 1 | merchant_id | 1 | merchant_id | A | 12 | NULL | NULL | | BTREE | | | YES | NULL |
| goods | 1 | goods_name | 1 | name | A | 13 | NULL | NULL | | BTREE | | | YES | NULL |
| goods | 1 | num | 1 | num | A | 13 | NULL | NULL | | BTREE | | | YES | NULL |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
删除索引
-- alter table 表名 drop index 索引名
-- 如果不知道索引名，可以查看创表sql语句
alter table goods drop index num;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| goods | 0 | PRIMARY | 1 | id | A | 20 | NULL | NULL | | BTREE | | | YES | NULL |
| goods | 1 | merchant_id | 1 | merchant_id | A | 12 | NULL | NULL | | BTREE | | | YES | NULL |
| goods | 1 | goods_name | 1 | name | A | 13 | NULL | NULL | | BTREE | | | YES | NULL |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.00 sec)
联合索引
为了减少索引的储存消耗，可以同时对多个字段设置一个共同索引。这称为联合索引。
alter table goods add index left_and_sold(num,sold_num);
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| goods | 0 | PRIMARY | 1 | id | A | 20 | NULL | NULL | | BTREE | | | YES | NULL |
| goods | 1 | merchant_id | 1 | merchant_id | A | 12 | NULL | NULL | | BTREE | | | YES | NULL |
| goods | 1 | goods_name | 1 | name | A | 13 | NULL | NULL | | BTREE | | | YES | NULL |
| goods | 1 | left_and_sold | 1 | num | A | 13 | NULL | NULL | | BTREE | | | YES | NULL |
| goods | 1 | left_and_sold | 2 | sold_num | A | 19 | NULL | NULL | | BTREE | | | YES | NULL |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
5 rows in set (0.01 sec)
- 联合索引遵守最左原则。即,为(a,b,c)创建联合索引，只有查询(a),(a和b),(a,b和c)时才能有效增快查询速度。而查询( b , c),( c ),( b )时失效。
扫一扫
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
