Mysql

引擎
外键
表之间的关联
Python 连接数据库
Python 中类的实现

引擎

查看引擎 show engines

可以了解到默认引擎为 MyISAM，此引擎不支持外键

在这里插入图片描述
修改默认引擎

此处以 phpstudy 修改配置文件 my 为例

文件位置：phpStudy\PHPTutorial\MySQL

第一步：将配置文件里的默认引擎修改

在这里插入图片描述

第二步：关闭 phpstudy 服务

在这里插入图片描述

第三步：删除日志文件

文件位置：phpStudy\PHPTutorial\MySQL\data

在这里插入图片描述
第四步：重启 phpstudy 服务

在这里插入图片描述
第五步：cmder 连接 mysql，查看默认引擎

在这里插入图片描述

外键

创建班级表

CREATE TABLE classes(id INT(4) NOT NULL PRIMARY KEY,NAME VARCHAR(36)
);

创建表的同时便给字段添加外键约束 创建学生表（指定 cid 为外键）

CREATE TABLE student(sid INT(4) NOT NULL PRIMARY KEY,sname VARCHAR(30),cid INT(4),CONSTRAINT fk_cid FOREIGN KEY(cid) REFERENCES classes(id)
);

指定外键具体语法

[CONSTRAINT <外键名>] FOREIGN KEY 字段名1 [，字段名2，…] REFERENCES <主表名> 主键列1 [，主键列2，…]

特别注意

删除主表会报错 drop table classes;

在这里插入图片描述
删除附表不会报错 drop table student;

在这里插入图片描述

创建表之后给字段添加外键约束

创建学生表

CREATE TABLE student(sid INT(4) NOT NULL PRIMARY KEY,sname VARCHAR(30),cid INT(4) NOT NULL
);

添加外键约束

ALTER TABLE student ADD CONSTRAINT fk_cid FOREIGN KEY(cid) REFERENCES classes(id);

删除外键约束

ALTER TABLE student DROP FOREIGN KEY fk_cid;

表之间的关联

创建数据库

CREATE DATABASE `taobao` CHARSET=utf8;

指定使用的数据库

use taobao;

创建表

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 TINYINT NOT NULL DEFAULT 1,is_saleoff TINYINT 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);

创建分类表

CREATE TABLE goods_cates(id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,NAME VARCHAR(40) NOT NULL
);

根据商品种类进行分类

SELECT cate_name FROM goods GROUP BY cate_name;

将分类后的结果查询并插入分类表中

INSERT INTO goods_cates (NAME) SELECT cate_name FROM goods GROUP BY cate_name;

将商品表中的商品分类与分类表中的（id）对应

UPDATE goods g INNER JOIN goods_cates c ON g.`cate_name`=c.`name` SET g.`cate_name`=c.`id`;

Python 连接数据库

数据库内容

在这里插入图片描述

下载 pymysql 库

pip install pymysql -i http://pypi.douban.com/simple/ --trusted-host pypi.douban.com

在 Python 中实现相关代码

import pymysql# 1. 连接 mysql 数据库
# 端口号默认为 3306 主机号默认为 127.0.0.1 或 localhost 用户名为 root 密码为 root 数据库选择 taobao 编码格式选择 utf-8 
conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", passwd="root", db="taobao", charset="utf8")# 2. 获取 cursor 游标对象 并把游标对象赋值给变量 cs
cs = conn.cursor()# 3. 通过游标对象 执行 sql 语句 execute 有返回值 且为受影响记录的行数
r = cs.execute("select * from goods;")
print(r)
# output：21# 4. 获取数据
# 4.1 获取单条
print(cs.fetchone())    # 第 1 条数据
print(cs.fetchone())    # 第 2 条数据
# output：(22, 'r510vc 15.6英寸笔记本', '5', '华硕', Decimal('3399.000'), 1, 0)
# output：(23, 'y400n 14.0英寸笔记本电脑', '5', '联想', Decimal('4999.000'), 1, 0)# 4.2 获取多条  size = None 默认获取一条 
print(cs.fetchmany()) # 第 3 条数据
print(cs.fetchmany(2))  # 获取 size 条 第 4 , 5 条数据
# output:((24, 'g150th 15.6英寸游戏本', '4', '雷神', Decimal('8499.000'), 1, 0),)
# output:((25, 'x550cc 15.6英寸笔记本', '5', '华硕', Decimal('2799.000'), 1, 0), (26, 'x240 超极本', '7', '联想', Decimal('4880.000'), 1, 0))# 4.3 获取全部
print(cs.fetchall())     # 6-21# 4.4 全部获取完之后 获取为空值
print(cs.fetchone())     # 返回 None
# output:None# 5. 关闭
# 5.1 关闭游标对象
cs.close()# 5.2 关闭连接对象
conn.close()

Python 连接中的异常处理机制

import pymysqltry:conn = pymysql.connect(host="127.0.0.1", port=3307, user="root", passwd="root", db="mb", charset="utf8")
except Exception as e:  # e = Exception()print(e)print(e.args[0])# output：(2003, "Can't connect to MySQL server on '127.0.0.1' ([WinError 10061] 由于目标计算机积极拒绝，无法连接。)")# output：2003

Python 中类的实现

商品类

import pymysql
"""
1. 连接数据库 在实例化的时候自动连接数据库
2. 定义获取单条数据的方法
3. 实现在程序执行的完毕的时候 自动关闭连接 
"""class MyTb(object):# 初始化时 自动连接数据库def __init__(self):try:self.conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", passwd="root", db="taobao", charset="utf8")except Exception as e:print(e)# 获取单条数据def get_one(self):# 获取游标cs = self.conn.cursor()# 执行 sql 语句sql = "select * from goods;"cs.execute(sql)# 获取执行的结果onedata = cs.fetchone()# 关闭cs.close()return onedata# 获取多条def get_many(self, s):# 获取游标cs = self.conn.cursor()# 执行 sql 语句sql = "select * from goods;"cs.execute(sql)# 获取执行结果manydatas = cs.fetchmany(s)return manydatas# 获取全部def get_all(self):# 获取游标cs = self.conn.cursor()# 执行 sql 语句sql = "select * from goods;"cs.execute(sql)# 获取执行的结果alldatas = cs.fetchall()# 关闭cs.close()return alldatasdef __del__(self):# 此处不关闭游标没关系 因为每获取一次数据时 都重新创建了游标self.conn.close()def main():tb = MyTb()one = tb.get_one()print(one)many = tb.get_many(2)print(many)all = tb.get_all()print(all)if __name__ == '__main__':main()# output：(22, 'r510vc 15.6英寸笔记本', '5', '华硕', Decimal('3399.000'), 1, 0)
# output：((22, 'r510vc 15.6英寸笔记本', '5', '华硕', Decimal('3399.000'), 1, 0), (23, 'y400n 14.0英寸笔记本电脑', '5', '联想', Decimal('4999.000'), 1, 0))
# output：22 到 42

商品查询类

import pymysql
"""
使用面向对象完成商品查询
• 输入1：查询所有商品
• 输入2：所有商品种类
• 输入3：查询所有品牌
• 输入4：退出
• 输入5：插入数据
"""class TB(object):def __init__(self):self.conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", passwd="root", db="taobao", charset="utf8")self.cs = self.conn.cursor()def run(self):while True:num = self.print_menu()if num == '1':self.show_all_goods()elif num == '2':self.show_all_cates()elif num == '3':self.show_all_brands()elif num == '4':breakelif num == '5':self.add_cate()else:print("输入有误，请重新输入")@staticmethoddef print_menu():print("-----mb shop-----")print("输入1：查询所有商品")print("输入2：所有商品种类")print("输入3：查询所有品牌")print("输入4：退出")print("输入5：添加商品数据")num = input("请输入：")return num# 查询所有商品def show_all_goods(self):sql = "select * from goods;"self.cs.execute(sql)res = self.cs.fetchall()for data in res:print(data)self.execuate_sql(sql)# 所有商品种类def show_all_cates(self):sql = "select * from goods_cates;"self.cs.execute(sql)res = self.cs.fetchall()for data in res:print(data)self.execuate_sql(sql)def execuate_sql(self, sql):self.cs.execute(sql)res = self.cs.fetchall()for data in res:print(data)# 查询所有品牌def show_all_brands(self):sql = "select distinct brand_name from goods;"self.execuate_sql(sql)def add_cate(self):name = input("请输入新商品的分类名字")sql = f"insert into goods_cates (name) values ('{name}')"self.cs.execute(sql)self.conn.commit()def __del__(self):self.cs.close()self.conn.close()def main():tb = TB()tb.run()if __name__ == '__main__':main()

对数据进行增删改查

import pymysql
"""
数据 增改删 --> 数据修改
MyISAM  --> 不需要提交事务就可以修改数据
Innodb  -->  修改它的表数据 进行提交事务
conn.commit() 提交事务如果说多条 sql 语句执行 只要有一条报错 插入不成功
"""def change_tb():try:conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", passwd="root", db="mysql_2", charset="utf8")cs = conn.cursor()"""必要性：以下二处 sql 代码均正确才能插入数据"""sql = "insert into student (sid,sname) values (1,'XXXXXX');"cs.execute(sql)sql1 = "insert into student (sid,sname) values (2,'zs666');"cs.execute(sql1)# 提交事务conn.commit()# 关闭游标对象cs.close()# 关闭连接对象conn.close()except Exception as e:  # 细分异常print(e)if __name__ == '__main__':change_tb()