SQL语句四
1.自关联
可以简单的理解为自己与自己进行连接查询。
比如:
一张areas 表里面有省市区,我们需要在通过这一张表查询某省对应的所有市。
select * from areas as p inner join areas as c on p.`id` = c.`pid` having p.name = '湖南';
2. 外键
2.1外键介绍
MySQL的外键约束(FOREIGN KEY)是表的一个特殊字段。对于两个具有关系的表而言,相关联字段中的主键所在表就是主表(父表),外键所在的表就是从表(子表)。
注意:
- 主键不能包含空值,但允许在外键中出现空值。也就是说,只要外键的每个非空值出现在指定的主键中,这个外键的内容就是正确的。
2.2 创建表时设置外键约束
语法:
[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [,字段名2,…] REFERENCES <主表名> 主键列1 [,主键列2,…]
实现:
- 班级表
- 学生表
在创建 student表时将其 cid 设置为外键。
注意:
- 从表的外键关联必须是主表的主键,并且主键与外键的类型必须保持一致。
2.3 在修改表时添加外键约束
alter table 表名 add constraint 外键名字 foreign key(外键字段名) references 外表表名(主键字段名);
注意:
- 主表需存在时创建从表
2.4 删除外键约束
ALTER TABLE student DROP FOREIGN KEY fk_cid;
3. MySQL与Python交互
3.1 准备数据
- 创建数据表
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);
3.2 分表
分表是因为当一张表的数据量比较多时,但是我们只需要查询其中的某个字段数据,就会导致查询效率降低,所以此处我们需要使用到分表。
商品分类表
-
创建 商品分类 表
-
商品表cate_name 字段 进行 分组
-
将 商品表 中的 商品种类 查询并插入到 商品分类表 里
-
将 商品表 中的 商品种类 更改为 商品表分类表 对应的 id
3.3 安装pymysql
在Windows操作系统上安装
Python3:pip install pymysql
Python2:pip install MySQLdb
Ubuntu安装:https://www.jianshu.com/p/d84cdb5e6273
3.4 Python操作MySQL步骤
3.5 Connection 对象
用于建立与数据库的连接
创建对象:调用 connect() 方法
3.6 使用Python连接数据库
import pymysql
# 1.连接mysql数据库
conn = pymysql.connect(host='127.0.0.1',
port =3306,user= 'root',
passwd ='5203',
db = 'mb',
charset = 'utf8'
)
# 2.获取cursor游标对象
cs =conn.cursor()
# 3.通过游标对象 执行 sql语句
r =cs.execute('select * from goods;')
# 4.获取数据 获取1条
print(cs.fetchone())
print(cs.fetchone())
print(cs.fetchone())
# 4.1 获取多条
print(cs.fetchmany(2))
# 4.2 获取全部
print(cs.fetchall())
# 5 关闭
# 5.1 关闭游标
cs.close()
# 5.2关闭连接
conn.close()
3.7 练习
- 练习1
封装DB类
'''
创建DB类
'''
import pymysql
class MyDb(object):
# 2. 初始化时,自动连接数据库
def __init__(self):
self.my_conn()
# 1. 连接数据库
def my_conn(self):
try:
self.conn = pymysql.connect(
host='127.0.0.1', port=3306,
user='root', passwd='5203',
db='mb', charset='utf8'
)
except Exception as e:
print(e)
# 3.获取多条数据
def get_many(self, n):
# 3.1 获取游标
cs = self.conn.cursor()
# 3.2 执行sql语句
sql = 'select * from goods;'
cs.execute(sql)
# 3.3 获取执行结果
res = cs.fetchmany(n)
# 3.4 关闭游标
cs.close()
return res
# 4.获取所有数据
def get_all(self):
# 4.1 获取游标
cs = self.conn.cursor()
# 4.2 执行sql语句
sql = 'select * from goods;'
cs.execute(sql)
# 4.3 获取执行结果
res = cs.fetchall()
# 4.4 关闭游标
cs.close()
return res
def __del__(self):
self.conn.close()
def main():
db = MyDb()
data = db.get_all()
for i in data:
print(i)
if __name__ == '__main__':
main()
- 练习2
使用面向对象完成商品查询
输入1:查询所有商品
输入2:所有商品种类
输入3:查询所有品牌
输入4:退出
'''
使用面向对象完成商品查询
输入1:查询所有商品
输入2:所有商品种类
输入3:查询所有品牌
输入4:退出
'''
import pymysql
class MB(object):
def __init__(self):
self.coon = pymysql.connect(host = '127.0.0.1',port = 3306,user = 'root',passwd = '5203',db = 'mb',charset = 'utf8')
self.cs = self.coon.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_brabds()
elif num == '4':
break
else:
print('输入有误,请重新输入')
@staticmethod
def print_menu():
print('-----mb shop-----')
print('输入1: 查询所有商品')
print('输入2: 所有商品种类')
print('输入3: 查询所有品牌')
print('输入4: 退出')
num = input('请输入:')
return num
# 查询所有商品
def show_all_goods(self):
sql = 'select * from goods;'
self.exe_sql(sql)
# 查询所有商品种类
def show_all_cates(self):
sql = 'select * from goods_cates;'
self.exe_sql(sql)
# 查询所有品牌
def show_all_brabds(self):
sql = 'select distinct brand_name from goods;'
self.exe_sql(sql)
def exe_sql(self,sql):
self.cs.execute(sql)
res = self.cs.fetchall()
for data in res:
print(data)
# 关闭游标和连接
def __del__(self):
self.cs.close()
self.coon.close()
def main():
db = MB()
db.run()
if __name__ == '__main__':
main()
查看 默认引擎
show engines;
修改默认引擎
在mysql文件 中找到my.ini配置文件 打开修改并保存
'''
数据 增改删 -->数据修改
MyISAM --> 不需要提交事务就可以修改数据
Innodb --> 修改它的表数据 进行提交事务
conn.commit() 提交事务
如果说,多条sql语句执行,只要有一条报错,就都不要插入了
conn.rollback() 实现回滚
'''
import pymysql
def cption_db():
try:
conn = pymysql.connect(host = '127.0.0.1',port =3306,
user = 'root',passwd ='5203',
db = 'mytest',charset = 'utf8')
cs = conn.cursor()
sql = 'insert into teacher (tname) values("gerg")'
cs.execute(sql)
sql1 = 'insert into teacher (sfdsfs) values("dgr")'
cs.execute(sql1)
# 提交事务
conn.commit()
cs.close()
conn.close()
except Exception as e:
print(e)
conn.rollback()
if __name__ == '__main__':
cption_db()