目录
1.sql复习和分表操作
# 创建数据库
create database if not exists jing_dong charset = utf8;
# 切换数据库
use jing_dong;
# 创建表
create table goods
(
id int unsigned primary key auto_increment not null,
name varchar(150) not null,
cate_name varchar(40) not null,
brand_name varchar(40) not null,
price decimal(10, 3) not null default 0,
is_show bit not null default 1,
is_saleoff bit not null default 0
);
# 插入数据
insert into goods
values (0, 'r510vc 15.6英寸笔记本', '笔记本', '华硕', '3399', default, default);
insert into goods
values (0, 'y400n 14.0英寸笔记本电脑', '笔记本', '联想', '4999', default, default);
insert into goods
values (0, 'g150th 15.6英寸游戏本', '游戏本', '雷神', '8499', default, default);
insert into goods
values (0, 'x550cc 15.6英寸笔记本', '笔记本', '华硕', '2799', default, default);
insert into goods
values (0, 'x240 超极本', '超级本', '联想', '4880', default, default);
insert into goods
values (0, 'u330p 13.3英寸超极本', '超级本', '联想', '4299', default, default);
insert into goods
values (0, 'svp13226scb 触控超极本', '超级本', '索尼', '7999', default, default);
insert into goods
values (0, 'ipad mini 7.9英寸平板电脑', '平板电脑', '苹果', '1998', default, default);
insert into goods
values (0, 'ipad air 9.7英寸平板电脑', '平板电脑', '苹果', '3388', default, default);
insert into goods
values (0, 'ipad mini 配备 retina 显示屏', '平板电脑', '苹果', '2788', default, default);
insert into goods
values (0, 'ideacentre c340 20英寸一体电脑 ', '台式机', '联想', '3499', default, default);
insert into goods
values (0, 'vostro 3800-r1206 台式电脑', '台式机', '戴尔', '2899', default, default);
insert into goods
values (0, 'imac me086ch/a 21.5英寸一体电脑', '台式机', '苹果', '9188', default, default);
insert into goods
values (0, 'at7-7414lp 台式电脑 linux ）', '台式机', '宏碁', '3699', default, default);
insert into goods
values (0, 'z220sff f4f06pa工作站', '服务器/工作站', '惠普', '4288', default, default);
insert into goods
values (0, 'poweredge ii服务器', '服务器/工作站', '戴尔', '5388', default, default);
insert into goods
values (0, 'mac pro专业级台式电脑', '服务器/工作站', '苹果', '28888', default, default);
insert into goods
values (0, 'hmz-t3w 头戴显示设备', '笔记本配件', '索尼', '6999', default, default);
insert into goods
values (0, '商务双肩背包', '笔记本配件', '索尼', '99', default, default);
insert into goods
values (0, 'x3250 m4机架式服务器', '服务器/工作站', 'ibm', '6888', default, default);
insert into goods
values (0, '商务双肩背包', '笔记本配件', '索尼', '99', default, default);
# 1.查询类型cate_name为 '超极本' 的商品名称、价格
select name, price
from goods
where cate_name = '超级本';
#2. 显示商品的种类
select cate_name
from goods
group by cate_name;
# distinct可以去重
select distinct cate_name
from goods;
# 3.求所有电脑产品的平均价格,并且保留两位小数
select round(avg(price), 2)
from goods;
# 4. 显示每种商品的平均价格
select cate_name, round(avg(price), 2) as avg
from goods
group by cate_name;
# 5. 查询每种类型的商品中 最贵、最便宜、平均价、数量
select cate_name, max(price), min(price), avg(price), count(*)
from goods
group by cate_name;
# 6.查询所有价格大于平均价格的商品，并且按价格降序排序
select *
from goods
where price > (select avg(price) from goods)
order by price desc;
# 创建商品分类表，用于存储所有的商品分类
create table if not exists goods_cates
(
id int unsigned primary key auto_increment,
name varchar(100)
);
# 将商品表中存在的商品分类的名字 写入到 商品分类表
# 1. 得知道现在在商品表中都有哪些商品分类
select cate_name
from goods
group by cate_name;
insert into goods_cates(name)
values ('笔记本'),
('游戏本');
# 新的语法，在将查询结果 插入到表中 insert into 表名 select ....
insert into goods_cates(name)
select cate_name
from goods
group by cate_name;
select *
from goods_cates;
# 2. 将goods表中的 cate_name 都修改为 goods_cate中的id的值
update goods as g join goods_cates as gc on g.cate_name = gc.name
set g.cate_name=gc.id;
# update .... set... 更新数据的语法
# goods join goods_cates 连表查询。 条件 g.cate_name = gc.name
# set 后面的 设置更新的数据， 将 g.cate_name=gc.id
# 3. 将cate_name 字段名 修改为 cate_id
alter table goods change cate_name cate_id int unsigned;
# 获取一下 商品的品牌名称
select brand_name from goods group by brand_name;
# 将goods表中的 brand_name 放到 商品品牌表 goods_brands
# 创建表的同时插入数据。 create table xxx() ...select ......
# 在创建表的同时 将 select查询到的数据 插入到创建的表当中。
create table goods_brands(
id int unsigned primary key auto_increment,
name varchar(100)
)
select brand_name as name from goods group by brand_name;
# 注意点，使用 create table xxx() ...select ...... 插入数据的时候，
# select 后面的字段 要和 创建的表中的字段名一致，一致的时候，才会将数据插入到指定的字段中
# 如果字段不一致，会在表中创建一个新的列，存储查询出来的数据
# drop table goods_brands;
# 2. 将goods表中的brand_name 改为 goods_brands表中 id
update goods g join goods_brands gb on g.brand_name = gb.name set g.brand_name=gb.id;
# 3. 修改表结构， 将 goods表中的 brand_name 改为 brand_id
alter table goods change brand_name brand_id int unsigned;2.pymysql模块
帮助我们在python代码中去对mysql数据库中的数据实现增删改查操作。
2.1 pymysql的使用步骤
-
导包
-
创建连接对象（建立和mysql数据库服务的连接）
-
创建一个游标对象（这个游标对象就是专门去执行sql语句的对象）
-
使用游标对象执行sql语句（增删改查）
-
关闭游标对象
-
关闭连接对象
pysql查询
# 导包
from pymysql import connect
# 创建连接对象
conn = connect(host='127.0.0.1',port=3306,database='jing_dong',user='root',password='123456',charset='utf8')
# 创建游标对象
cur = conn.cursor()
# 执行sql
# 调用execute 返回值是在执行这条sql时受影响的行数。
num = cur.execute('select * from goods where price > 3000 ')
# print(num)
# print(cur.fetchmany(5)) fetchmany 返回指定条数的数据
# print(cur.fetchall()) fetchall 返回查询的所有结果，返回一个元组
# print(cur.fetchone()) fetchone 获取的是查询结果的的单个数据 返回一个元组，元组中的元素就是这条数据的列值
res =cur.fetchone()
print(res)
# 关闭游标对象和连接对象
cur.close()
conn.close()2.1 pymysql 增删改
1.数据提交和数据回滚
- 数据提交commit
数据库中的基本操作: 增删改查. 上一个章节中我们知道如何完成对数据库中数据的获取其实也就是查, 比如获取一条数据或者获取所有数据等.
注意：查数据, 并不会对原有数据库中的数据造成影响. 而增删改这三个操作都会对原有数据库中的数据造成影响. 也就说查数据不会修改原有数据空中的数据, 而增删改会修改原有数据库中的数据.
当对原有数据库中数据有修改时需要使用：
# commit()提交数据到数据库
# 这里可以理解为 数据库询问是否确定修改数据 然后commit()就是确定修改的意思
conn.commit()
- 数据回滚rollback()
当我们在使用pymysql对数据进行响应的操作时, 会有可能会有一些错误操作, 这是如果想要数据返回到最原始的状态可以使用数据回滚操作
注意:数据回滚是需要在 commit() 之前才有效的, 也就是说数据还没有确定修改这时候使用数据回滚才是有效的
# 回滚数据到什么都没做的原始状态 即撤销刚刚所有的修改操作
conn.rollback()
说白了，就是修改数据的时候，数据不会立即保持到mysql服务器中，而是在缓存中。commit就是确认将缓存中修改的数据保持到mysql服务器中，rollback就是撤销修改。
2.pymysql的增删改操作
# 演示pymysql的增删改操作
import pymysql
# 创建连接对象
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='mysql',
charset='utf8'
)
# 连接数据库的时候 可以去指定要操作的数据库名字。 切换数据库： use 数据库名;
conn.select_db('jing_dong')
# 创建游标对象
cursor = conn.cursor()
# sql_str = "show tables;"
# cursor.execute(sql_str)
# 1. 增加数据
# sql_str = "insert into test(name) values('小明');"
# cursor.execute(sql_str)
# 2. 删除数据
# sql_str = 'delete from test where id=1;'
# cursor.execute(sql_str)
# 3. 修改数据
sql_str = "update test set name='小明明' where id=5;"
cursor.execute(sql_str)
# 在使用pymysql对数据进行增删改操作的时候，需要我们手动的提交事务、
conn.commit()
# 获取执行结果
# print(cursor.fetchall())
cursor.close()
conn.close()
# 在使用pymysql进行对数据的 增删改的时候，操作完毕之后，记得 使用连接对象.commit() 对操作的数据进行提交之后才会在表中生效3.sql注入
1. 什么是SQL注入
-
SQL注入:
简单说sql注入是一种可以使得数据库中的数据泄露的方式. 如果有一些人有恶意的目的, 可以利用sql注入完成盗取数据.
-
产生原因:
后台将用户提交的带有恶意的数据和SQL进行字符串方式的拼接，从而影响了SQL语句的语义，最终产生数据泄露的现象.
简单的说, 就是利用各种方式提交数据给程序并让这些数据和SQL语句产生结合, 进而让新产生的SQL语句和之前的原始的SQL语句变成不同的意思, 至此就可以利用新产生的SQL语句获取想要的数据了.
-
防止SQL注入
SQL语句的参数化可以防止SQL注入的产生. 将SQL语句的所有数据参数存在一个列表中传递给execute函数的第二个参数进行执行, 就是SQL语句参数化.
# 把sql语句需要的参数放到一个列表中
my_list = [xxx,xxx,xxx]
# 把列表作为execute方法的第二个参数传递
cursor.execute(sql,my_list)
2.非安全方式的SQL语句
from pymysql import connect
# 创建Connection连接
conn = connect(host='localhost', port=3306, user='root', password='mysql', database='jing_dong', charset='utf8')
# 获得Cursor对象
cur = conn.cursor()
# 获取用户想要查询的物品名称
find_name = input("请输入物品名称：")
# 非安全方式的sql语句
sql = 'select * from goods where name="%s"' % find_name
# 执行sql语句
count = cur.execute(sql)
# 获取查询的结果
result = cur.fetchall()
# 打印查询的结果
print(result)
# 关闭Cursor对象
cur.close()
# 关闭Connection对象
conn.close()
注意：
在输入商品名称的时候，输入
# 双引号也要输入
" or 1=1 or "
这样就完成了一个简单的注入
# 原始的sql语句
sql = 'select * from goods where name="%s"' % find_name
# 输入 " or 1=1 or " 后的sql语句
sql = 'select * from goods where name="" or 1=1 or ""'
这里 name= "" or 1=1 or "" 这个条件是一定成立的,因为or是或的意思多个条件只要有一个条件成立整体就成立,而1=1是一定成立的. 这就造成这个sql语句的意思发生里改变.
3.安全方式的SQL语句
from pymysql import connect
# 创建Connection连接
conn = connect(host='localhost', port=3306, user='root', password='mysql', database='jing_dong', charset='utf8')
# 获得Cursor对象
cur = conn.cursor()
# 获取用户想要查询的物品名称
find_name = input("请输入物品名称：")
# sql语句
# 注意:
# 此处不同于python的字符串格式化，必须全部使用%s占位
# 所有参数所需占位符外不需要加引号
sql = 'select * from goods where name=%s;'
# 安全的方式
# 构造参数列表
params = [find_name]
# 执行select语句
# 注意:
# 如果要是有多个参数，需要进行参数化
# 那么params = [数值1, 数值2....]，此时sql语句中有多个%s即可
count = cur.execute(sql, params)
# 获取查询的结果
result = cur.fetchall()
# 打印查询的结果
print(result)
# 关闭Cursor对象
cur.close()
# 关闭Connection对象
conn.close()
如果构建的不是参数列表，而是一个字典，那么占位符%s，改成 %()s 括号里面写入字典的键
如下：
her = input('请输入：')
her_dicr = {'name':her}
sql = 'select * from hero where hname=%(name)s'
count = cur.execute(sql,her_dicr)利用参数化列表就可以完成防止SQL注入
504
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
