数据库优化 学习笔记
一、数据准备
1.1、创建数据表
-- 创建 "京东" 数据库
create database jd charset=utf8;
-- 使用 "京东" 数据库
use jd;
-- 创建一个商品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
);
1.2、插入数据
-- 向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);
二、数据表拆分
2.1、创建 “商品分类” 表
create table goods_cates(
id int unsigned primary key auto_increment not null,
name varchar(40) not null
);
- 查询 goods 表中商品的种类
select cate_name from goods group by cate_name;
- 将分组结果写入到 goods_cates 数据表
-- insert into goods_cates(name) 数据
-- 直接添加查询得到的数据时, 不需要 values
insert into goods_cates (name) select cate_name from goods group by cate_name;
2.1、同步表数据
- 需求：把 goods 表的 cate_name 字段，替换成它在 goods_cates 表中对应的 id
- 解决：
（1） 先使用连接查询，得到两个表的数据
（2） 再借用输出表来修改 goods 表
-- update 输出表 set 条件
update goods as g inner join goods_cates as c on g.cate_name=c.name set g.cate_name=c.id;
二、Python 操作 MySQL
2.1、Python mysql 安装
-
安装 pymysql
-
在Windows操作系统上安装
（1） Python3：pip install pymysql
（2） Python2：pip install MySQLdb
2.2、Python 操作 MySQL 步骤
- 创建 Connection 对象，用来连接数据库
- 获取 Cursor 对象，用来执行 sql 语句
- 执行语句
- 获取返回的数据（还有对数据的处理）
- 关闭 Cursor
- 关闭 Connection
（1）. 创建Connection 对象，连接数据库
- 调用 connect() 方法
conn=connect(参数列表)
参数host： 连接的mysql主机，如果本机是’localhost’
参数port： 连接的mysql主机的端口，默认是3306
参数database： 数据库的名称
参数user： 连接的用户名
参数password： 连接的密码
参数charset： 通信采用的编码方式，推荐使用utf8
import pymysql
con = pymysql.connect(host = 'localhost',port=3306,database='python-01',user='root',password = 'root',charset = 'utf8')
或
from pymysql import *
conn = connect(host = 'localhost',port=3306,database='python-01',user='root',password = 'root',charset = 'utf8')
（2）. 获取 Cursor 对象
- 使用 cursor() 方法：
cs1=conn.cursor()
（3）. 执行 sql 语句
- 使用 execute() 方法，返回值是影响的行数
r = cursor.execute('select * from users_banner')
print(r)
（4）. 获取返回的数据
- 使用 fetchone() 方法
# 获取一条数据
print(cursor.fetchone())
print(cursor.fetchone())
print(cursor.fetchone())
# 获取多条数据
print(cursor.fetchmany(2))
# 获取全部
print(cursors.fetchall())
（5）. 关闭 cursor
- 使用 close() 方法：
cursor.close()
（6）. 关闭 connection
- 使用 close() 方法：
conn.close()
2.3、由于在操作数据库时，往往会出现错误，并给出提示，所以还应做异常处理
- 错误返回的 e，形似元组，但又不能以元组的方法读取数据
- 为了方便观察出错是的返回，所以代码有错
from pymysql import *
try:
conn = connect(
host = "localhost",
port = 3306,
user = "root",
passwd = "",
db = 'j3d',
charset = 'utf8'
)
cursor = conn.cursor()
r = cursor.execute('select * from goods')
# print(r)
print(cursor.fetchone())
print(cursor.fetchmany(3))
print(cursor.fetchall())
cursor.close()
conn.close()
except Exception as e:
# print(e) # (1049, "Unknown database 'j3d'")
# print(type(e)) # <class 'pymysql.err.InternalError'>
# print(e.args[0],e.args[1]) # 1049 Unknown database 'j3d'
print("Error %d: %s"%(e.args[0],e.args[1])) # Error 1049: Unknown database 'j3d'
三、练习
3.1、创建 MyDb 类
from pymysql import *
class MyDb():
def __init__(self):
self.conn()
self.cur = self.conn.cursor()
# 连接
def conn(self):
try:
self.conn = connect(
host = 'localhost',
port = 3306,
user = 'root',
passwd = '',
db = 'jd',
charset = 'utf8'
)
except Exception as e:
print(e)
# 获取第一条记录
def get_one(self):
sql = 'select * from goods'
self.cur.execute(sql)
result = self.cur.fetchone()
# self.cur.close()
# self.conn.close() # 在这里关闭了链接, 那么在下面 main() 方法里只能调用一次,
# 需要重新实例化 MyDb 类才能建立连接
return result
# 获取全部记录
def get_more(self):
sql = 'select * from goods'
self.cur.execute(sql)
result = self.cur.fetchall()
return result
# 关闭游标、关闭连接
# 使用类的特殊方法关闭连接, 则在销毁类的实例对象前, 可以多次调用并执行类方法
def __del__(self):
self.cur.close()
self.conn.close()
def main():
obj = MyDb()
res = obj.get_one()
print(f'获取第一条记录：\n{res}\n\n获取整个表的记录：')
# res = obj.get_one() # 若在方法里关闭连接, 则会报错
# # obj = MyDb() # 因此需要重新实例化类
# res = obj.get_one()
# print(res)
resall = obj.get_more()
for item in resall:
print(item)
if __name__ == '__main__':
main()
3.2、练习商品查询
'''
1-查询所有的商品
2-所有的商品种类
3-查询所有的品牌
4-新增商品分类
5-修改商品分类
6-删除商品分类
任意键 - 退出
'''
from pymysql import *
class Goods():
# 默认方法连接数据库, 并建立游标
def __init__(self):
self.conn = connect(host='127.0.0.1',port=3306,user='root',passwd='',db='jd',charset='utf8')
self.cur = self.conn.cursor()
# 静态方法显示功能
@staticmethod
def print_menu():
print("\n\n--- Goods shop ---")
print("1 - 查询所有的商品")
print("2 - 所有的商品种类")
print("3 - 查询所有的品牌")
print("4 - 新增商品分类")
print("5 - 修改商品分类")
print("6 - 删除商品分类")
print("任意键退出...")
num = input('\n请输入功能对应的序号: ')
return num
# 执行 sql 语句（只是执行, 没有提交）
def execute_sql(self,sql):
self.cur.execute(sql)
result = self.cur.fetchall()
for item in result:
print(item)
# 1 - 查询所有的商品
def show_all_goods(self):
sql = 'select * from goods;'
self.execute_sql(sql)
# 2 - 所有的商品种类
def show_all_cate(self):
sql = 'select * from goods_cates;'
self.execute_sql(sql)
# 3 - 查询所有的品牌
def show_all_brand(self):
sql = 'select distinct brand_name from goods;'
self.execute_sql(sql)
# 4 - 新增商品分类
def add_cate(self):
name = input('输入新商品的分类名字:')
sql = 'insert into goods_cates(name) values("%s")' % name
self.cur.execute(sql)
# 提交动作, 不提交, 则 sql 语句执行后不会改变表
self.conn.commit()
# 5 - 修改商品分类
def update_cate(self):
old_name = input('输入要修改的商品分类名字:')
new_name = input('输入要修成什么分类名:')
sql = f'update goods_cates set name="{new_name}" where name="{old_name}"'
self.cur.execute(sql)
self.conn.commit()
# 6 - 删除商品分类
def del_cate(self):
name = input('输入要删除的商品分类名字:')
sql = 'delete from goods_cates where name="%s"' % name
self.cur.execute(sql)
self.conn.commit()
# 判断用户选择的功能, 并执行
def run(self):
while True:
num = self.print_menu()
if num == '1':
self.show_all_goods()
elif num == '2':
self.show_all_cate()
elif num == '3':
self.show_all_brand()
elif num == '4':
self.add_cate()
elif num == '5':
self.update_cate()
elif num == '6':
self.del_cate()
else:
break
def __del__(self):
self.cur.close()
self.conn.close()
def main():
act = Goods()
act.run()
if __name__ == '__main__':
main()
扫一扫
1961
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
