多表操作

外键

什么是外键

外键是指引用另外一个表中的一列或多列，被引用的列应该具有主键约束或唯一性约束。
外键用于建立和加强两个表数据之间的连接。

简单的来说：
MySQL的外键约束(FOREIGN KEY)是表的一个特殊字段。对于两个具有关联关系的表而言，相关联字段中的主键所在表就是主表(父表)，外键所在的表就是从表(子表)。

为了更好的理解，下面举例一个：
image.png
image.png

为表添加外键约束

alter table 表名 add constranint FK_ID foreign key (外键字段名） REFERENCES 主表表名(主键字段名);

注意：
（1）查看详细表结构可以用下面的语句：

SHOW CREATR TABLE 表名；

（2）建立外键的表必须是 InnoDB 型，不能是临时表。因为在MySQL中有 InnoDB 类型的表才支持外键。
（3）定义外键名时，不能加引号，如 constraint ‘FK_ID’ 或 constraint “FK_ID” 都是错误的。

添加外键约束的参数说明

image.png

删除外键约束

alter table 表名 drop foreign key 外键名；

连接查询

当查询结果的列来源于多张表时，需要将多张表连接成一个大的数据集，再选择合适的列返回mysql。这时我们就需要使用表连接。

交叉连接

SELECT * FROM 表1 CROSS JOIN 表2

CROSS JOIN用于连接两个要查询的表，通过该语句可以查询两个表中所有的数据组合。

内连接

内连接仅选出两张表中互相匹配的记录

SELECT 查询字段 FROM 表1 [INNER] JOIN 表2 ON 表1.关系字段 = 表2.关系字段

6-SQL语句 (二） - 图4

外连接（左连接，右连接）

SELECT 所有字段 FROM 表1 LEET | RIGHT [OUTER] JION 表2 ON 表1.关系字段 = 表2.关系字段 WHERE 条件

左连接
查询的结果为两个表匹配到的数据，左表持有的数据，对于右表中不存的数据使用null填充

select * from 表1 left join 表2 on 表1.列 = 表2.列;

6-SQL语句 (二） - 图5

右连接
查询结果为两个表匹配到的数据，右表持有的数据，对于左表中不存在的数据使用null填充。

select * from 表1 right join 表2 on 表1.列 = 表2.列;

6-SQL语句 (二） - 图6

子查询

子查询是指一个查询语句嵌套在另一个查询语句内部的查询。
在执行查询语句时，首先回执行子查询中的语句，然后将返回的结果作为外层查询的过滤条件。
在子查询中可以使用IN, EXISTS, ANY, ALL操作符。

演示：

创建数据库 CREATE DATABASE chapter05;

选择使用数据库 USE chapter05;

创建表 CREATE TABLE grade( id int(4) NOT NULL PRIMARY KEY, name VARCHAR(36) );

CREATE TABLE student( sid INT(4) NOT NULL PRIMARY KEY, sname VARCHAR(36), gid int(4) NOT NULL );

为表(student)添加外键约束 ALTER TABLE student add CONSTRAINT FK_ID FOREIGN KEY (gid) REFERENCES grade(id);

查看表的结构 DESC student; DESC grade;

查看表的详细结构 SHOW CREATE TABLE student;

删除外键约束 alter TABLE student DROP FOREIGN KEY FK_ID;

查看表结构 SHOW CREATE TABLE student;

MySQL与Python交互

  1.           ![](https://cdn.nlark.com/yuque/0/2020/png/704747/1595587768912-0f1e6a5f-385f-483c-9123-96938a1e6e2b.png#from=url&id=CONx0&margin=%5Bobject%20Object%5D&originHeight=435&originWidth=599&originalType=binary&ratio=1&status=done&style=none)

准备数据

  • 创建数据表

    1. -- 创建一个商品 goods 数据表
    2. CREATE TABLE goods(
    3.   id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
    4.   NAME VARCHAR(150) NOT NULL,
    5.   cate_name VARCHAR(40) NOT NULL,
    6.   brand_name VARCHAR(40) NOT NULL,
    7.   price DECIMAL(10,3) NOT NULL DEFAULT 0,
    8.   is_show TINYINT NOT NULL DEFAULT 1,
    9.   is_saleoff TINYINT NOT NULL DEFAULT 0
    10. );

  • 插入数据

    1. -- 插入数据
    2. INSERT INTO goods VALUES(0,'r510vc 15.6英寸笔记本','笔记本','华硕','3399',DEFAULT,DEFAULT); 
    3. INSERT INTO goods VALUES(0,'y400n 14.0英寸笔记本电脑','笔记本','联想','4999',DEFAULT,DEFAULT);
    4. INSERT INTO goods VALUES(0,'g150th 15.6英寸游戏本','游戏本','雷神','8499',DEFAULT,DEFAULT); 
    5. INSERT INTO goods VALUES(0,'x550cc 15.6英寸笔记本','笔记本','华硕','2799',DEFAULT,DEFAULT); 
    6. INSERT INTO goods VALUES(0,'x240 超极本','超级本','联想','4880',DEFAULT,DEFAULT); 
    7. INSERT INTO goods VALUES(0,'u330p 13.3英寸超极本','超级本','联想','4299',DEFAULT,DEFAULT); 
    8. INSERT INTO goods VALUES(0,'svp13226scb 触控超极本','超级本','索尼','7999',DEFAULT,DEFAULT); 
    9. INSERT INTO goods VALUES(0,'ipad mini 7.9英寸平板电脑','平板电脑','苹果','1998',DEFAULT,DEFAULT);
    10. INSERT INTO goods VALUES(0,'ipad air 9.7英寸平板电脑','平板电脑','苹果','3388',DEFAULT,DEFAULT); 
    11. INSERT INTO goods VALUES(0,'ipad mini 配备 retina 显示屏','平板电脑','苹果','2788',DEFAULT,DEFAULT); 
    12. INSERT INTO goods VALUES(0,'ideacentre c340 20英寸一体电脑 ','台式机','联想','3499',DEFAULT,DEFAULT); 
    13. INSERT INTO goods VALUES(0,'vostro 3800-r1206 台式电脑','台式机','戴尔','2899',DEFAULT,DEFAULT); 
    14. INSERT INTO goods VALUES(0,'imac me086ch/a 21.5英寸一体电脑','台式机','苹果','9188',DEFAULT,DEFAULT); 
    15. INSERT INTO goods VALUES(0,'at7-7414lp 台式电脑 linux ）','台式机','宏碁','3699',DEFAULT,DEFAULT); 
    16. INSERT INTO goods VALUES(0,'z220sff f4f06pa工作站','服务器/工作站','惠普','4288',DEFAULT,DEFAULT); 
    17. INSERT INTO goods VALUES(0,'poweredge ii服务器','服务器/工作站','戴尔','5388',DEFAULT,DEFAULT); 
    18. INSERT INTO goods VALUES(0,'mac pro专业级台式电脑','服务器/工作站','苹果','28888',DEFAULT,DEFAULT); 
    19. INSERT INTO goods VALUES(0,'hmz-t3w 头戴显示设备','笔记本配件','索尼','6999',DEFAULT,DEFAULT); 
    20. INSERT INTO goods VALUES(0,'商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT); 
    21. INSERT INTO goods VALUES(0,'x3250 m4机架式服务器','服务器/工作站','ibm','6888',DEFAULT,DEFAULT); 
    22. INSERT INTO goods VALUES(0,'商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT);

    分表

    分表是因为当一张表的数据量比较多时，但是我们只需要查询其中的某个字段数据，就会导致查询效率降低，所以此处我们需要使用到分表。当然后面会细讲。

  • 商品分类表

    • 创建 商品分类 表

      1. create table goods_cates(
      2. id int unsigned primary key auto_increment not null,
      3. name varchar(40) not null
      4. );

    • 将 商品表 中的 商品种类 查询并插入到 商品分类表 里

      1. insert into goods_cates (name) select cate_name from goods group by cate_name;

    • 将 商品表 中的 商品种类 更改为 商品表分类表 对应的 id

      1. 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步骤

image.png

Connection 对象

用于建立与数据库的连接
创建对象：调用 connect() 方法

  1. conn=connect(参数列表)
  3. """
  4. 参数host：连接的mysql主机，如果本机是'localhost'
  5. 参数port：连接的mysql主机的端口，默认是3306
  6. 参数database：数据库的名称
  7. 参数user：连接的用户名
  8. 参数password：连接的密码
  9. 参数charset：通信采用的编码方式，推荐使用utf8
  10. """
  12. import pymysql
  14. con = pymysql.connect(host = 'localhost',port=3306,database='python-01',user='root',password = 'root',charset = 'utf8')
  16. from pymysql import *
  18. conn = connect(host = 'localhost',port=3306,database='python-01',user='root',password = 'root',charset = 'utf8')

对象的方法

  • close()关闭连接
  • commit()提交
  • cursor()返回Cursor对象,用于执行sql语句并获得结果

Cursor对象

  • 用于执行sql语句,使用频度最高的语句为select、insert、update、delete
  • 获取Cursor对象:调用Connection对象的cursor()方法
    1. cs1=conn.cursor()

对象的方法

  • close()关闭 先关闭游标,在关闭链接
  • execute(operation [, parameters ])执行语句，返回受影响的行数，主要用于执行insert、update、delete语句，也可以执行create、alter、drop等语句
  • fetchone()执行查询语句时，获取查询结果集的第一个行数据，返回一个元组
  • fetchall()执行查询时，获取结果集的所有行，一行构成一个元组，再将这些元组装入一个元组返回

使用Python连接数据库

  1. from pymysql import *
  3. try:
  4.     conn = connect(
  5.         host = "localhost",
  6.         port = 3306,
  7.         user = "root",
  8.         passwd = "root",
  9.         db = 'logic_web',
  10.         charset = 'utf8'
  11.     )
  12.     cursor = conn.cursor()
  13.     cursor.execute('select * from users_banner')    
  14.     result = cursor.fetchone()
  15.     cursor.close()
  16.     conn.close()     
  17. except Exception as e:
  18.     print("Error %d:%s"%(e.args[0],e.args[1]))

演示

sql代码

  1. -- 创建数据库
  2. CREATE DATABASE shopping CHARSET='utf8';
  4. -- 切换数据库
  5. USE shopping;
  7. -- 创建一个商品 goods 数据表
  8. CREATE TABLE goods(
  9.     id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
  10.     NAME VARCHAR(150) NOT NULL,
  11.     cate_name VARCHAR(40) NOT NULL,
  12.     brand_name VARCHAR(40) NOT NULL,
  13.     price DECIMAL(10,3) NOT NULL DEFAULT 0,
  14.     is_show TINYINT NOT NULL DEFAULT 1,
  15.     is_saleoff TINYINT NOT NULL DEFAULT 0
  16. );
  18. -- 插入数据
  19. INSERT INTO goods VALUES(0,'r510vc 15.6英寸笔记本','笔记本','华硕','3399',DEFAULT,DEFAULT); 
  20. INSERT INTO goods VALUES(0,'y400n 14.0英寸笔记本电脑','笔记本','联想','4999',DEFAULT,DEFAULT);
  21. INSERT INTO goods VALUES(0,'g150th 15.6英寸游戏本','游戏本','雷神','8499',DEFAULT,DEFAULT); 
  22. INSERT INTO goods VALUES(0,'x550cc 15.6英寸笔记本','笔记本','华硕','2799',DEFAULT,DEFAULT); 
  23. INSERT INTO goods VALUES(0,'x240 超极本','超级本','联想','4880',DEFAULT,DEFAULT); 
  24. INSERT INTO goods VALUES(0,'u330p 13.3英寸超极本','超级本','联想','4299',DEFAULT,DEFAULT); 
  25. INSERT INTO goods VALUES(0,'svp13226scb 触控超极本','超级本','索尼','7999',DEFAULT,DEFAULT); 
  26. INSERT INTO goods VALUES(0,'ipad mini 7.9英寸平板电脑','平板电脑','苹果','1998',DEFAULT,DEFAULT);
  27. INSERT INTO goods VALUES(0,'ipad air 9.7英寸平板电脑','平板电脑','苹果','3388',DEFAULT,DEFAULT); 
  28. INSERT INTO goods VALUES(0,'ipad mini 配备 retina 显示屏','平板电脑','苹果','2788',DEFAULT,DEFAULT); 
  29. INSERT INTO goods VALUES(0,'ideacentre c340 20英寸一体电脑 ','台式机','联想','3499',DEFAULT,DEFAULT); 
  30. INSERT INTO goods VALUES(0,'vostro 3800-r1206 台式电脑','台式机','戴尔','2899',DEFAULT,DEFAULT); 
  31. INSERT INTO goods VALUES(0,'imac me086ch/a 21.5英寸一体电脑','台式机','苹果','9188',DEFAULT,DEFAULT); 
  32. INSERT INTO goods VALUES(0,'at7-7414lp 台式电脑 linux ）','台式机','宏碁','3699',DEFAULT,DEFAULT); 
  33. INSERT INTO goods VALUES(0,'z220sff f4f06pa工作站','服务器/工作站','惠普','4288',DEFAULT,DEFAULT); 
  34. INSERT INTO goods VALUES(0,'poweredge ii服务器','服务器/工作站','戴尔','5388',DEFAULT,DEFAULT); 
  35. INSERT INTO goods VALUES(0,'mac pro专业级台式电脑','服务器/工作站','苹果','28888',DEFAULT,DEFAULT); 
  36. INSERT INTO goods VALUES(0,'hmz-t3w 头戴显示设备','笔记本配件','索尼','6999',DEFAULT,DEFAULT); 
  37. INSERT INTO goods VALUES(0,'商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT); 
  38. INSERT INTO goods VALUES(0,'x3250 m4机架式服务器','服务器/工作站','ibm','6888',DEFAULT,DEFAULT); 
  39. INSERT INTO goods VALUES(0,'商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT);
  41. -- 1.创建分类表
  42. CREATE TABLE goods_cates(
  43. id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
  44. name VARCHAR(40) NOT NULL
  45. );
  47. -- 2.给分类表添加名称
  48. -- 进行分组
  49. SELECT cate_name FROM goods GROUP BY cate_name;
  51. -- 添加数据
  52. -- 此处不是使用values，而是目标表的字段名+查询结果
  53. INSERT INTO goods_cates (name) SELECT cate_name FROM goods GROUP BY cate_name; 
  55. -- 需要将goods表里面的cate_name 换成对应的id
  56. UPDATE goods g INNER JOIN goods_cates c ON g.`cate_name`=c.`name` SET g.`cate_name`=c.`id`;

python代码

  1. import pymysql
  4. # 1.连接数据库
  5. conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', db='shopping', charset='utf8')
  7. # 2.获取cursor游标对象
  8. cs = conn.cursor()
  10. # 3.通过游标对象，执行sql语句
  11. r = cs.execute('select * from goods;')
  12. print(r)
  14. # 4.获取数据
  15. # 方法一   fetchone() 获取单条数据
  16. print(cs.fetchone())    #获取第一条数据
  17. print(cs.fetchone())    #获取第二条数据
  19. # 方法二   fetchmany() size=None，默认获取的是一条数据，返回元组
  20. print(cs.fetchmany())
  21. print(cs.fetchmany(2))
  23. # 方法三   fetchall()  获取所有数据
  24. print(cs.fetchall())
  26. # 关闭
  27. # 关闭游标
  28. cs.close()
  30. # 关闭连接
  31. conn.close()