一、将查询结果插入到其他表中
（1）创建表：
create table good_cates(id int not null primary key auto_increment, name varchar(50) not null);
（2）将其他表中的信息添加到新创建的表中：
insert into good_cates(name) select cate_name from goods group by cate_name;
● insert into … select … 表示把查询结果插入到指定表中，意为表复制
二、使用连接更新表中某个字段数据
（1）查询：
select * from goods inner join good_cates on goods.cate_name = good_cates.name;
（2）更新：
update goods inner join good_cates on goods.cate_name = good_cates.name set goods.cate_name=good_cates.id;
● 连接更新表中数据使用 update…join… 语句
三、创建表并给某个字段添加数据
（1）创建表并添加数据：
create table brands(id int not null primary key auto_increment, name varchar(20) not null) select brand_name as name from goods group by brand_name;
（2）更新：
update goods inner join brands on goods.brand_name=brands.name set goods.brand_name=brands.id;
● 创建表并给字段插入数据使用 create table …select 语句
四、修改表结构
修改多个字段：
alter table goods change cate_name cate_id int not null, change brand_name brand_id int not null;
● alert table 可以同时修改多个字段信息
五、PyMySQL 的使用
1. 导入 pymysql 包
import pymysql
2. 创建连接对象
"""
host：连接的mysql主机，如果是本机用localhost
port：连接的mysql主机的端口，默认是3306
user：连接的用户名
password：连接的密码
database：数据库的名称
charset：通信采用的编码方式，推荐使用utf-8
"""
conn = pymysql.connect(host="192.168.1.250",
port=3306,
user="root",
password="123456",
database="sxf",
charset="utf8")
连接对象操作说明：
● 关闭连接：conn.close()
● 提交数据：conn.commit()
● 撤销数据：conn.rollback()
3. 获取游标对象
# 调用连接对象的cursor()方法获取游标对象
cursor = conn.cursor()
游标操作说明：
● 使用游标执行 SQL 语句：execute(operation [parameters ]) 执行 SQL 语句，返回受影响的行数，主要用于执行 insert、update、delete、select 等语句
● 获取查询结果集中的一条数据：cur.fetchone() 返回一个元组
● 获取查询结果集中的所有数据：cur.fetchall() 返回一个元组
● 关闭游标：cur.close() 表示和数据库操作完成
4. pymysql 完成数据的查询操作
import pymysql
"""
host：连接的mysql主机，如果是本机用localhost
port：连接的mysql主机的端口，默认是3306
user：连接的用户名
password：连接的密码
database：数据库的名称
charset：通信采用的编码方式，推荐使用utf-8
"""
conn = pymysql.connect(host="192.168.1.250",
port=3306,
user="root",
password="123456",
database="sxf",
charset="utf8")
# 调用连接对象的cursor()方法获取游标对象
cursor = conn.cursor()
# sql语句
sql = "select * from areas;"
# 返回影响的行数
row_count = cursor.execute(sql)
print(row_count)
# 通过游标获取查询结果，返回的是一个元组数据
# 获取一条数据
# result = cursor.fetchone()
# print(result)
# 获取所有数据，整体是一个元组类型，里面的每条数据都是一个小元组
result = cursor.fetchall()
print(result[2])
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
5. pymysql 完成对数据的增删改
import pymysql
"""
host：连接的mysql主机，如果是本机用localhost
port：连接的mysql主机的端口，默认是3306
user：连接的用户名
password：连接的密码
database：数据库的名称
charset：通信采用的编码方式，推荐使用utf-8
"""
conn = pymysql.connect(host="192.168.1.250",
port=3306,
user="root",
password="123456",
database="jing_dong",
charset="utf8")
# 调用连接对象的cursor()方法获取游标对象
cursor = conn.cursor()
sql = 'insert into good_cates values(0, "手机");'
try:
row_count = cursor.execute(sql)
print("执行sql语句后影响的行数：", row_count)
conn.commit()
except Exception as e:
print(e)
conn.rollback()
finally:
cursor.close()
conn.close()
六、SQL 注入
1. SQL 注入的介绍
用户提交带有恶意的数据与 SQL 语句进行字符串方式的拼接，从而影响了 SQL 语句的语义，最终产生数据泄露的现象。
2. SQL 注入演示
import pymysql
"""
host：连接的mysql主机，如果是本机用localhost
port：连接的mysql主机的端口，默认是3306
user：连接的用户名
password：连接的密码
database：数据库的名称
charset：通信采用的编码方式，推荐使用utf-8
"""
conn = pymysql.connect(host="192.168.1.250",
port=3306,
user="root",
password="123456",
database="jing_dong",
charset="utf8")
# 调用连接对象的cursor()方法获取游标对象
cursor = conn.cursor()
# sql语句
sql = "select * from good_cates where name = '%s';" % "台式机 ' or 1 = 1 or '"
print(sql)
# sql = "select * from good_cates where name = '台式机';"
# 返回影响的行数
row_count = cursor.execute(sql)
print(row_count)
# 通过游标获取查询结果，返回的是一个元组数据
# 获取一条数据
# result = cursor.fetchone()
# print(result)
# 获取所有数据，整体是一个元组类型，里面的每条数据都是一个小元组
result = cursor.fetchall()
for i in result:
print(i)
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
3. 防止 SQL 注入-1
import pymysql
"""
host：连接的mysql主机，如果是本机用localhost
port：连接的mysql主机的端口，默认是3306
user：连接的用户名
password：连接的密码
database：数据库的名称
charset：通信采用的编码方式，推荐使用utf-8
"""
conn = pymysql.connect(host="192.168.1.250",
port=3306,
user="root",
password="123456",
database="jing_dong",
charset="utf8")
# 调用连接对象的cursor()方法获取游标对象
cursor = conn.cursor()
# sql语句，防止sql注入，使用%s来占位
sql = "select * from good_cates where name = %s;"
print(sql)
# sql = "select * from good_cates where name = '台式机';"
# 返回影响的行数
# row_count = cursor.execute(sql, ('台式机'))
row_count = cursor.execute(sql, (' or 1=1 or '))
print(row_count)
# 通过游标获取查询结果，返回的是一个元组数据
# 获取一条数据
# result = cursor.fetchone()
# print(result)
# 获取所有数据，整体是一个元组类型，里面的每条数据都是一个小元组
result = cursor.fetchall()
for i in result:
print(i)
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
4. 防止 SQL 注入-2
import pymysql
"""
host：连接的mysql主机，如果是本机用localhost
port：连接的mysql主机的端口，默认是3306
user：连接的用户名
password：连接的密码
database：数据库的名称
charset：通信采用的编码方式，推荐使用utf-8
"""
conn = pymysql.connect(host="192.168.1.250",
port=3306,
user="root",
password="123456",
database="jing_dong",
charset="utf8")
# 调用连接对象的cursor()方法获取游标对象
cursor = conn.cursor()
# sql语句，防止sql注入，使用%s来占位
# sql = "select * from good_cates where name = %s;"
sql = "insert into good_cates values(%s, %s);"
# sql = "select * from good_cates where name = '台式机';"
print(sql)
# 返回影响的行数
# row_count = cursor.execute(sql, ('台式机'))
# row_count = cursor.execute(sql, (' or 1=1 or ',))
row_count = cursor.execute(sql, [0, '电脑包'])
print(row_count)
conn.commit()
# 通过游标获取查询结果，返回的是一个元组数据
# 获取一条数据
# result = cursor.fetchone()
# print(result)
# 获取所有数据，整体是一个元组类型，里面的每条数据都是一个小元组
result = cursor.fetchall()
for i in result:
print(i)
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
SQL 语句参数化：
● SQL 语句中的参数使用 %s 来占位，此处不是 python 中的字符串格式化操作
● 将 SQL 语句中的 %s 占位所需要的参数存在一个列表中，把参数列表传递给 execute 方法中的第二个参数
七、事务
1. 事务的介绍
事务时用户定义的一系列执行 SQL 语句的操作，这些操作要么完全地执行，要么完全地不执行，它是一个不可分割的工作执行单元。
2. 事务的四大特性
原子性：
一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚。对于一个事务来说，不可能只执行其中的一部分操作。
一致性：
数据库总是从一个一致性的状态转换到另一个一致性的状态。
隔离性：
通常来说，一个事务所做的修改操作在提交事务之前，对于其他事务来说是不可见的。
持久性：
一旦事务提交，则其所做的修改会永久保存到数据库中。
3. 事务的使用
在使用事务之前，先要确保表的存储引擎是 InnoDB 类型，只有这个类型才可以使用事务，MySQL 数据库中表的存储引擎默认是 InnoDB 类型。表的存储引擎就是提供存储数据的一种机制，不同表的存储引擎提供不同的存储机制。
4. MySQL 数据库的存储引擎
查看MySQL数据库支持的表的存储引擎：
show engines;
● 常用的表的存储引擎是 InnoDB 和 MyISAM
● InnoDB 是支持事务的
● MyISAM 不支持事务，优势是访问速度快，对事务没有要求或者以 select、insert 为主的都可以使用该存储引擎来创建表
查看创表语句：
show create table goods;
开启事务：
begin;
start transaction;
● 开启事务后执行修改命令，变更数据会保存到本地缓存文件中，而不维护到物理表中
● MySQL 数据库默认采用自动提交（autocommit）模式，如果没有显示的开启一个事务，那么每条 sql 语句都会被当作一个事务执行提交的操作
● 当设置 autocommit=0 就是取消了自动提交事务模式，直到显示的执行 commit 和 rollback 表示该事务结束
八、索引
1. 索引的介绍
索引在 MySQL 中也叫“键”，它是一个特殊的文件，它保存着数据表里所有记录的位置信息，通俗来讲，数据库索引好比是书前面的目录，能够加快数据库的查询速度。
2. 索引的使用
查看表中已有索引：
show index from 表名;
● 主键列会自动创建索引
索引的创建：
alter table 表名 add index 索引名【可选】(列名1, …)
● 不指定索引名，默认使用字段名
索引的删除：
show create table 表名;
alter table 表名 drop index 索引名;
3. 添加索引测试查询性能
创建测试表：
create table test(test varchar(10));
插入测试数据：
import pymysql
conn = pymysql.connect(host="192.168.1.250",
port=3306,
user="root",
password="123456",
database="jing_dong",
charset="utf8")
# 调用连接对象的cursor()方法获取游标对象
cursor = conn.cursor()
for i in range(1000000):
sql = 'insert into test values("test%d");' % i
try:
row_count = cursor.execute(sql)
print("执行sql语句后影响的行数：", row_count)
conn.commit()
except Exception as e:
print(e)
conn.rollback()
验证索引性能：
# 开启运行时间监测
set profiling=1;
# 查询第 99999 条数据
select * from test where test='test99999';
# 查看执行时间
show profiles;
# 添加索引
alter table test add index (test);
# 再次查询
select * from test where test='test99999';
# 再次查看执行时间
show profiles;
4. 联合索引
联合索引又叫复合索引，即一个索引覆盖表中两个或者多个字段，一般用在多个字段一起查询的时候。
create table st(id int not null primary key aoto_increment, name varchar(20), age int);
alter table st add index (name, age);
联合索引的好处：
减少磁盘空间开销，因为每创建一个索引，其实就是创建了一个索引文件，那么会增加磁盘空间的开销。
5. 联合索引的最左原则
在使用联合索引的时候，要遵守最左原则，即 index(name, age) 支持 name、name 和 age 组合查询，而不支持单独 age 查询，因为没有用到创建的联合索引。
6. MySQL 中索引的优点和缺点以及使用原则
优点：
（1）加快数据的查询速度。
缺点：
（1）创建索引会耗费时间和占用磁盘空间，并且随着数据量的增加所耗费的时间也会增加。
使用原则：
（1）对经常更新的表就避免对其进行过多索引的创建，对经常用于查询的字段应该创建索引。
（2）数据量小的表最好不要使用索引，因为由于数据较少，可能查询全部数据花费的时间比遍历索引的时间还要短，索引就可能不会产生优化效果。
（3）在一个字段上相同值比较多不要建立索引，比如一个字段只有两个不同的值（“男”和“女”）。
545
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
