mysql优化之SQL(自关联、外键、删除外键约束、MySQL与Python交互、分表、Cursor对象、使用Python连接数据库)

自关联

可以简单的理解为自己与自己进行连接查询。
比如：
一张 areas 表里面有省市区，我们需要在通过这一张表查询某省对应的所有市

SELECT * FROM areas AS p INNER JOIN areas AS c ON p.`id`=c.`pid` HAVING p.name="xxx";

外键

外键介绍

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

• 主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。

创建表时设置外键约束

语法：

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

实现：
•班级表

• 学生表
  

-- 创建班级表
create table classes(
    id int(4) not null primary key,
    name varchar(36)
);

-- 创建学生表
create table student(
    sid int(4) not null primary key,
    sname varchar(30),
    cid int(4) not null
);

在创建 student表时将其 cid 设置为外键。
注意：

• 从表的外键关联必须是主表的主键，并且主键与外键的类型必须保持一致。
  在修改表时添加外键约束

alter table 表名 add constraint 外键名字 foreign key(外键字段名) references 外表表名(主键字段名);

注意：

• 主表需存在时创建从表

删除外键约束

ALTER TABLE student DROP FOREIGN KEY fk_cid;

MySQL与Python交互

准备数据

• 创建数据表

-- 创建一个商品 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() 方法

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')

对象的方法

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

Cursor对象

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

cs1=conn.cursor()

对象的方法

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

使用Python连接数据库

from pymysql import *

try:
    conn = connect(
        host = "localhost",
        port = 3306,
        user = "root",
        passwd = "root",
        db = 'logic_web',
        charset = 'utf8'
    )
    cursor = conn.cursor()
    cursor.execute('select * from users_banner')    
    result = cursor.fetchone()
    cursor.close()
    conn.close()     
except Exception as e:
    print("Error %d:%s"%(e.args[0],e.args[1]))