MYSQL数据库编程

一，准备数据

　　-- 创建 "京东" 数据库
　　create database jing_dong charset=utf8;

　　-- 使用 "京东" 数据库
　　use jing_dong;

　　-- 创建一个商品goods数据表
　　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
　　);

-- 向goods表中插入数据

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);

二，创建新数据表

-- 创建商品分类表

create table if not exists goods_cates(

　　id int unsigned primary key auto_increment,

　　name varchar(40) not null

);

-- 在创建数据表的时候一起插入数据
-- 注意: 需要对brand_name 用as起别名，否则name字段就没有值
create table goods_brands (
　　id int unsigned primary key auto_increment,
　　name varchar(40) not null) select brand_name as name from goods group by brand_name;

--加入数据

insert into goods_cates (name) (select cate_name from goods group by cate_name);

三，更新原表数据

　　--更改原表中的cate_name为goods_cates表中的id号

　　update goods as g inner join goods_cates as gc on g.cate_name = gc.name set g.cate_name = gc.id;

　　update goods as g inner join goods_brands as b on g.brand_name=b.name set g.brand_name=b.id;

 　　

--更改原表中的cate_name为goods_cates表中的id的数据类型

desc goods;
alter table goods
change cate_name cate_id int unsigned not null,

change brand_name brand_id int unsigned not null;

四，外键

- 在goods表中拥有cate_id和brand_id分别都来自于goods_cates表和goods_brands表
- cate_id字段的值引用自goods_cates的id字段，即cate_id就是"外键"。
- 一旦成为真正的外键，那么外键将会对用户修改cate_id产生约束: cate_id 取值范围就是 goods_cates中id字段的取值范围,否则将报错
- brand_id同理

• 如何防止无效信息的插入,就是可以在插入前判断类型或者品牌名称是否存在呢? 可以使用之前讲过的外键来解决

• 外键约束:对外键字段的值 在更新和插入时进行和引用的表中字段数据进行对比

• 关键字: foreign key,只有 innodb数据库引擎 支持外键约束

1，对于已经存在的字段添加外键约束

-- 给brand_id 添加外键约束成功
alter table goods add foreign key (brand_id) references goods_brands(id);

-- 给cate_id 添加外键失败
-- 会出现1452错误
-- 错误原因:已经添加了一个不存在的cate_id值12,因此需要先删除

delete from goods where cate_id = 12;

alter table goods add foreign key (cate_id) references goods_cates(id);

2，在创建数据表的时候设置外键约束

• 注意: goods 中的 cate_id 的类型一定要和 goods_cates 表中的 id 类型一致

create table goods(
　　id int primary key auto_increment not null,
　　name varchar(40) default '',
　　price decimal(5,2),
　　cate_id int unsigned,
　　brand_id int unsigned,
　　is_show bit default 1,
　　is_saleoff bit default 0,
　　foreign key(cate_id) references goods_cates(id),
　　foreign key(brand_id) references goods_brands(id)
);

3，删除外键约束

-- 需要先获取外键约束名称,该名称系统会自动生成,可以通过查看表创建语句来获取名称

show create table goods;

-- 获取名称之后就可以根据名称来删除外键约束
alter table goods drop foreign key 外键名称;
使用到外键约束会极大的降低表更新的效率, 所以在追求读写效率优先的场景下一般很少使用外键。

五，总结

• 将查询的数据直接插入表中

  insert into xxx (字段名) select 语句
  将select语句的结果集插入到一个表中

• 连表更新

  update 表1 join 表2 on 连接条件
  set 某表.字段=值

• 外键约束作用 子表中的外键字段在插入和更新 新值的时候 新值必须 在主表中相应字段出现过。

　　alter table goods add foreign key(brand_id) references goods_brands(id);

• 需要先获取外键约束名称,该名称系统会自动生成,可以通过查看表创建语句来获取名称

show create table goods;

• 获取名称之后就可以根据名称来删除外键约束

alter table goods drop foreign key 外键名称;

六，数据库编程

从前面我们知道数据库概念包含 数据库文件、服务器和数据库客户端 客户端我们之前已经用过的有navicat/mysql-client等程序。

大家会发现如果用之前客户端几乎是不可能完全这个任务的, 因为我们不可能去构造出那个插入10w行数据的SQL。可是，如果我们有一个功能能够插入一行数据，借助程序强大的特点-重复，就可以轻而易举的就将10w行数据收入麾下。这种通过使用程序代码的方式去连接数据库服务器，通过和服务器进行交互完成对数据库的增删改查的方式，就称为数据库编程。

而此刻学习的pymysql就是一种客户端。

1， Python 中操作 MySQL 步骤

 

如何理解连接 connection 和 游标 cursor connection就像是连接出发地和目的地的 高速公路 cursor就像是在高速公路上的货车-拉货 我们使用游标就可以完成对数据的操作 当我们完成操作完成后就可以停下货车，然后公路再停止使用。

2，pymysql的使用

引入模块
from pymysql import connect

　　1）Connection 对象

• 用于建立与数据库的连接 调用pymysql模块中的connect()方法

　　conn=connect(参数列表)

* 参数host：连接的mysql主机，如果本机是'localhost'
* 参数port：连接的mysql主机的端口，默认是3306
* 参数database：数据库的名称
* 参数user：连接的用户名
* 参数password：连接的密码
* 参数charset：通信采用的编码方式，推荐使用utf8

• 关闭连接 conn.close()

• 提交数据 conn.commit()

• 撤销数据 conn.rollback()

• 通过连接获取游标 cur = conn.cursor()返回Cursor对象，用于执行sql语句并获得结果

　　2）Cursor游标对象

　　获取Cursor对象

# 调用Connection对象的cursor()方法
cur =conn.cursor()

　　目的: 执行sql语句（使用频度最高的语句为select、insert、update、delete)

• 使用游标执行SQL语句

　　execute(operation [, parameters ]) 执行SQL语句，返回受影响的行数，主要用于执行insert、update、delete语句，也可以执行create、alter、drop等语句

• 关闭游标 cur.close()

• 获取结果集中的一条

  cur.fetchone()返回一个元组 形如 (1,'妲己',18)

• 获取结果集中的一些

　　　cur.fetchmany() 若无参数则返回一个元组

• 获取结果集中的所有

  cur.fetchall()执行查询时，获取结果集的所有行，一行构成一个元组，再将这些元组装入一个元组返回 形如((1,'公孙离',20),(2,'妲己',18))

　　3）pymysql完成数据查询

import pymysql

# 创建和数据库服务器的连接 服务器地址　　　端口　　　　用户名　　　　　密码 数据库名 通信使用字符和数据库字符集一致
conn = pymysql.connect(host='localhost', port=3306, user='root', password='mysql',database='python_test_1', charset='utf8')

# 获取游标
cursor = conn.cursor()

# 执行ＳＱＬ语句 返回值就是ＳＱＬ语句在执行过程中影响的行数
sql = """select * from hero;"""

row_count = cursor.execute(sql)
print("ＳＱＬ语句执行影响的行数%d" % row_count)

# 取出结果集中一行 返回的结果是一行　(1, '妲己', 2)
# print(cursor.fetchone())

# 取出结果集中的所有数据　　返回　((一行数据),(),())
# ((1, '妲己', 2), (2, '李白', 1), (3, '程咬金', 3), (4, '亚瑟', 5), (5, '荆轲', 99))
for line in cursor.fetchall():
　　print(line)

# 关闭游标
cursor.close()

# 关闭连接
conn.close()

　　4）pymysql完成对数据库的增删改

import pymysql

conn = pymysql.connect(host='127.0.0.1',port=3306,user='root',password='mysql',db='python_test_1', charset='utf8')

# 通过连接获取游标
cursor = conn.cursor()

# sql = "delete from hero where id = 5;"
# sql = insert into hero (name) values ('西部大坏蛋孙悟空');
sql = "update hero set kongfuid=444 where id = 4;"

row_count = cursor.execute(sql)
print("ＳＱＬ语句执行影响的行数%d" % row_count)

# 提交数据到数据库
# conn.commit()

# 回滚数据到什么都不做的状态 即撤销刚刚的修改
conn.rollback()

# 关闭游标和连接
cursor.close()
conn.close()

关于提交commit commit将修改提交到数据库，保存修改

注意pymysql中数据需要手动提交commit才能保存到数据库中

　　5）参数化列表防止SQL注入

什么是SQL注入 产生原因: 后台将用户提交的带有恶意的数据和SQL进行字符串方式的拼接，从而影响了SQL语句的语义，最终产生数据泄露的现象。 如果防止: sql语句的参数化, 将SQL语句的所有数据参数存在一个列表中传递给execute函数的第二个参数

注意

* 此处不同于python的字符串格式化，必须全部使用%s占位
* 所有参数所需占位符外不需要加引号

from pymysql import connect

def main():

find_name = input("请输入物品名称：")

# 创建Connection连接
conn = connect(host='localhost',port=3306,user='root',password='mysql',database='jing_dong',charset='utf8')
# 获得Cursor对象
cs1 = conn.cursor()

# # 非安全的方式
# # 输入 " or 1=1 or " (双引号也要输入)
# sql = 'select * from goods where name="%s"' % find_name
# print("""sql===>%s<====""" % sql)
# # 执行select语句，并返回受影响的行数：查询所有数据
# count = cs1.execute(sql)

# 安全的方式
# 构造参数列表
params = [find_name]
# 执行select语句，并返回受影响的行数：查询所有数据
count = cs1.execute('select * from goods where name=%s', params)
# 注意：
# 如果要是有多个参数，需要进行参数化
# 那么params = [数值1, 数值2....]，此时sql语句中有多个%s即可

# 打印受影响的行数
print(count)
# 获取查询的结果
# result = cs1.fetchone()
result = cs1.fetchall()
# 打印查询的结果
print(result)
# 关闭Cursor对象
cs1.close()
# 关闭Connection对象
conn.close()

if __name__ == '__main__':
　　main()

转载于:https://www.cnblogs.com/njhbk/p/10157360.html