SQL
一.外键
外键介绍
MySQL的外键约束(FOREIGN KEY)是表的一个特殊字段。对于两个具有关联关系的表而言,相关联字段中的主键所在表就是主表(父表),外键所在的表就是从表(子表)。
注意:
- 主键不能包含空值,但允许在外键中出现空值。也就是说,只要外键的每个非空值出现在指定的主键中,这个外键的内容就是正确的。
- 创建表时设置外键约束
[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [,字段名2,…] REFERENCES <主表名> 主键列1 [,主键列2,…]
实现:
• 班级表
• 学生表
CREATE TABLE cassles(
id INT(4) PRIMARY KEY NOT NULL,
NAME VARCHAR(36)
);
CREATE TABLE students(
sid INT(4) NOT NULL PRIMARY KEY,
sname VARCHAR(30),
cid INT(4) NOT NULL,
CONSTRAINT fk_cid FOREIGN KEY (cid) REFERENCES cassles(id) # 添加外键
);
注意:
- 从表的外键关联必须是主表的主键,并且主键与外键的类型必须保持一致。
在修改表时添加外键约束
alter table 表名 add constraint 外键名字 foreign key(外键字段名) references 外表表名(主键字段名);
注意:
- 主表需存在时创建从表
删除外键约束
ALTER TABLE student DROP FOREIGN KEY fk_cid;
二.Python与MySQL交互
准备数据
- 创建数据表
-- 创建一个商品 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 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 unsigned primary key auto_increment not null,
name varchar(40) not null
);
- 将 商品表 中的 商品种类 查询并插入到 商品分类表 里
insert into goods_cates (name) select cate_name from goods group by cate_name;
- 将 商品表 中的 商品种类 更改为 商品表分类表 对应的 id
update goods as g inner join goods_cates as c on g.cate_name=c.name set g.cate_name=c.id;
安装pymysql
在windows上操作:
- Python3:pip install pymysql
- Python2:pip install MySQLdb
Ubuntu安装:https://www.jianshu.com/p/d84cdb5e6273
Python操作MySQL步骤
Connection 对象
- 用于建立与数据库的连接
- 创建对象:调用 connect() 方法
# 导入模块
import pymysql
# 连接数据库 host:数据库地址;port:数据库端口号;user:用户名;password:密码;databaes:数据库名称
cont = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='root', db='淘宝')
# 游标
co = cont.cursor()
对象的方法
- close()关闭连接
- commit()提交
- cursor()返回Cursor对象,用于执行sql语句并获得结果
Cursor对象
- 用于执行sql语句,使用频度最高的语句为select、insert、update、delete
- 获取Cursor对象:调用Connection对象的cursor()方法
# 游标
co = cont.cursor()
对象的方法
- close()关闭 先关闭游标,在关闭链接
- execute(operation [, parameters ])执行语句,返回受影响的行数,主要用于执行insert、update、delete语句,也可以执行create、alter、drop等语句
- fetchone()执行查询语句时,获取查询结果集的第一个行数据,返回一个元组
- fetchall()执行查询时,获取结果集的所有行,一行构成一个元组,再将这些元组装入一个元组返回
使用Python连接数据库
from pymysql import * # 导入pymysql模块的全部方法
try: # 捕获异常
cont = pymysql.connect(
host='127.0.0.1', # 数据库地址
port=3306, # 数据库端口号
user='root', # 用户名
password='root', # 密码
db='淘宝' # 数据库名称
)
cursor = cont.cursor() # 获取游标
cursor.execute('select * from goods') # 执行SQL语句
result = cursor.fetchone() # 返回第一条结果
cursor.close() # 关闭游标
conn.close() # 关闭连接
except Exception as e: # 异常捕获
print("Error %d:%s"%(e.args[0],e.args[1]))
练习
练习1
- 封装DB类
import pymysql # 导入模块
class TaoBao(object): # 创建DB类
def __init__(self): # 初始化参数
self.cont = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='root',
db='淘宝'
) # 连接数据库
self.con = self.cont.cursor() # 获取指针
def show_all(self): # 定义方法
sql = 'select * from goods' # SQL语句:显示goods表的所有信息
self.con.execute(sql) # 执行SQL语句
show = self.con.fetchall() # 返回结果
print(show) # 打印结果
def __del__(self): # 魔法方法:程序执行完毕后最后执行
self.con.close() # 关闭指针
self.cont.close() # 关闭连接
def main(): # 调用DB类
t = TaoBao()
t.show_all()
if __name__ == '__main__': # 程序主入口
main()
练习2:
使用面向对象完成商品查询
- 输入1:查询所有商品
- 输入2:所有商品种类
- 输入3:查询所有品牌
- 输入4:退出
- 输入5:插入数据
"""
使用面向对象完成商品查询
• 输入1:查询所有商品
• 输入2:所有商品种类
• 输入3:查询所有品牌
• 输入4:退出
• 输入5:插入数据
"""
import pymysql
class TaoBao(object):
def __init__(self):
self.cont = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='root', db='淘宝')
self.con = self.cont.cursor()
def run(self):
while True:
num = self.print_menu()
if num == 1:
self.show_all_goods()
elif num == 2:
self.show_all_castes()
elif num == 3:
self.show_all_brand_name()
elif num == 4:
break
elif num == 5:
self.add_new_goods()
else:
print('你输入错了请重新输入')
@staticmethod
def print_menu():
print('1:查询所有商品')
print('2:所有商品种类')
print('3:查询所有品牌')
print('4:退出')
print('5:插入数据')
num = int(input('请输入要查询的数字:'))
return num
def show_all_goods(self):
sql = 'select * from goods;'
self.execute_sql(sql)
def show_all_castes(self):
sql = 'select * from goods_castes;'
self.execute_sql(sql)
def add_new_goods(self):
name = input("请输入新商品的名字:")
sql = "insert into goods_castes (name) values (%s);"
self.con.execute(sql, (name,))
self.cont.commit()
self.show_all_castes()
def execute_sql(self, sql):
self.con.execute(sql)
res = self.con.fetchall()
for i in res:
print(i)
def show_all_brand_name(self):
sql = 'select distinct brand_name from goods;'
self.execute_sql(sql)
def __del__(self):
self.con.close()
self.cont.close()
def main():
t = TaoBao()
t.run()
if __name__ == '__main__':
main()