一，准备数据

-- 创建 "京东" 数据库

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