Mysql 与 Pymysql

这次介绍mysql以及在python中如何用pymysql操作数据库, 以及在mysql中存储过程, 触发器以及事务的实现, 对应到pymysql中应该如何操作.

---

首先我们在cmd窗口中展示常见的sql命令:

• 连接数据库 mysql -u root -p

• 显示数据库 show databases;

• 创建数据库, 设置默认编码为utf-8以及默认的排序方式 create database pymysql_db default charset utf8 collate utf8_general_ci;
• 选择用那个数据库 use pymysql_db;
• 显示当前数据库所有的表 show tables;

---

用户操作以及用户权限部分(不做演示, 不常用, 备查即可):

• 创建用户 create user 用户名 @ ip地址 identified by 密码;
• 删除用户 drop user 用户名 @ ip地址;
• 用户名修改 rename user 用户名 @ ip地址 to 新用户名@ip地址;
• 修改指定用户密码 set password for 用户名 @ ip地址 = Password(新密码)
• 查看权限 show grants for 用户 @ ip地址
• 给指定用户增加权限 grant 权限 on 数据库.表 to 用户 @ ip地址
• 取消指定用户的权限 revoke 权限 on 数据库.表 from 用户 @ ip地址
• 常见权限: 除grant外的所有权限 all privileges; 无访问权限 usage; 查询权限 select; 创建表权限 create; 删除表内容权限 delete
• 用户 @ ip地址表示用户在指定ip地址下才能访问, 当ip地址为%时候表示任意地址均可访问(默认即是)

---

数据表的相关操作:

userinfo表创建如下:

-- 创建一个名为userinfo的表
CREATE TABLE `userinfo` (
    --     创建一个int类型的字段nid, 该字段不能为空, 且自动递增(注意: 一个表中只能允许一个自增的字段)
    `nid` INT (11) NOT NULL AUTO_INCREMENT,
    --     创建一个varchar类型的字段name, 默认为空(varchar为变长类型, 这里指的是该字段最多占32位, 但是查询效率不如char定长类型)
    `name` VARCHAR (32) DEFAULT NULL,
    `color_nid` INT (11) DEFAULT NULL,
    --     指定nid为主键(主键在一个表中是唯一不重复的, 此处可以使用多个字段组合成主键, 只要组合不唯一即可, 主键默认会自动创建索引)
    PRIMARY KEY (`nid`),
    --   声明一个名为userinfo_ibfk_1的外键, 该外键由当前表中的color_nid与color表中的nid对应(说白了就是color_nid必须是color表中nid字段具有的值才行)
    CONSTRAINT `userinfo_ibfk_1` FOREIGN KEY (`color_nid`) REFERENCES `color` (`nid`)
--     指定当前数据库的引擎为INNODB, 默认字符集为utf-8(INNODB可支持事务)
) ENGINE = INNODB DEFAULT CHARSET = utf8;

表color创建如下:

CREATE TABLE `color` (
  `nid` int(11) NOT NULL AUTO_INCREMENT,
  `tag` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`nid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

• 删除表(表结构以及表内容) drop table 表名
• 删除表内容(自增不会影响) delete from 表名
• 删除表内容(自增也清零) truncate table 表名
• 修改表结构
  • 增加一列 alter table 表名 add 列名 类型
  • 删除一列 alter table 表名 drop column 列名
  • 修改列类型 alter table 表名 modify column 列名 新类型;
  • 修改列名称和列类型(类型保持不变也可) alter table 表名 change 原列名 新列名 类型;
• 指定具体列为主键 alter table 表名 add primary key(列名);
• 删除指定列的主键 alter table 表名 modify 列名 int, drop primary key;
• 删除当前表中所有列的主键 alter table 表名 drop primary key;
• 为当前表中列指定外键 alter table 从表(当前表) add constraint 外键名称（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段);
• 删除外键 alter table 表名 drop foreign key 外键名称
• 修改默认值 alter table 表名 alter 列名 set default 新默认值;
• 删除默认值 alter table 表名 alter 列名 drop default;

---

mysql中字段的常见类型:

• 二进制位 bit(长度)
• tinyint[(长度)] [有无符号unsigned] [位数低于长度时候是否填充零zerofill] 有符号表示范围-128 ~ 127, 无负号表示范围 0 ~ 255; 可用tinyint(1)来模拟boolean类型; 整型中的长度不做限制用, 仅仅做显示用, 即长度大于位数时候是否选择用零填充显示
• int[(长度)] [unsigned] [zerofill]
• bigint[(长度)] [unsigned] [zerofill]
• decimal[(数字总个数 [, 小数点个数])] [unsigned] [zerofill] 该类型会以字符串类型存储在mysql, 以此来表示准确的浮点型
• float[(数字总个数, 小数个数)] [unsigned] [zerofill]
• double[(数字总个数, 小数个数)] [unsigned] [zerofill]
• char(长度) 用固定长度存储字符类型, 这里的长度表示所有字符所占长度, 最长为255个字符
• varchar(长度) 变长类型存储字符类型, 这里的长度表示人为定制的最大长度, 查询速度不如char定长类型
• text 变长类型存储大字符串, 最多2**16 − 1个字符
• mediumtext 最多2**24 − 1个字符
• longtext 最多2**32 − 1个字符
• enum(v1 [,v2, v3]) 枚举类型, v1表示可选的值
• set(v1 [, v2, v3]) 集合类型, v1表示可选的不重复的值
• date 以yyyy-mm-dd形式存储
• time 以hh:mm:ss 新式存储
• year 以yyyy新式存储
• datetime 以yyyy-MM-dd hh:mm:ss 形式存储

---

表中数据的操作

• 增加内容 insert into 表 (列名01,列名02...) values (值,值,值...) [,(值,值,值...)] 可以一次增加多条数据
• 删除具体数据 delete from 表 where 条件'
• 修改表中具体数据 update 表 set 列名＝ 值 where 条件
• 查询具体内容 select 列名01 as 别名 , 列名02 from 表 where 条件
• 常见条件: 且关系and; 区间关系between 开始位置 and 结束位置; 在其中的关系in (v1, v2, v3); 不在其中的关系not in (v1, v2, v3); 在某种条件下in (select语句)
• mysql中的两种通配符: %匹配任意零个字符或者任意多个字符; _匹配任意一个
• mysql中的限制条件: limit number 表前number行以内; limit start, number 表示从start行起始的number行以内; limit number offset start 表示从第start'开始的number行以内
• mysql中排序显示: order by 列1 desc,列2 asc 表示先以列1递减排序, 若列1相同时候则以列2递增排序
• 分组: select count(列名),sum(列名),max(列名),min(列名) from 表 where 条件 group by 列名01,列名02 order by 列名 这里的group by需要放在where与order之间, where和order可以不存在
• 连表查询结果
  • select A.xx B.oo from A, B where A.x=B.o 没有A.x=B.o对应的数据则不显示任何结果
  • select A.xx B.oo from A inner join B where A.x=B.o A和B具有对等位置, 没有A.x=B.o对应的数据则不显示任何结果
  • select A.xx B.oo from A left join B where A.x=B.o A表显示所有, 对于B表若无符合A.x=B.o的数据则其值为null来显示
  • select A.xx B.oo from B right join A where A.x=B.o A表显示所有, 对于B表若无符合A.x=B.o的数据则其值为null来显示
• 组合不去重复显示所有查询结果 select 列名 from 表 union all select 列名 from 表
• 组合去重显示 select 列名 from 表 union select 列名 from 表

---

在mysql中的视图概念, 它并不是一个真实存在的表,而是根据自己写的sql语句执行所得的结果集, 方便查询过程和结果比较复杂时候暂存结果以便它用. 使用视图时候, 直接将它作为表来使用即可

• 视图创建

  -- 创建一个名为vw1的视图, 视图内容为select的语句执行结果
  CREATE VIEW vw1 AS
  SELECT
    userinfo.`name` AS uname, color.tag AS color
  FROM
    userinfo
  LEFT JOIN color ON userinfo.color_nid = color.nid

• 视图使用 SELECT * from vw1;

• 删除视图 drop view vw1
• 修改视图

  -- 修改视图vw1, 修改内容直接写上现今要执行的sql语句即可
  ALTER VIEW vw1 AS
  SELECT
    userinfo.nid,userinfo.`name` AS uname, color.tag AS color
  FROM
    userinfo
  LEFT JOIN color ON userinfo.color_nid = color.nid

---

在介绍触发器, 存储过程, 函数以及事务之前我们先简单过一下mysql中的条件和循环语句块

• 条件判断

  if 条件 then
    普通sql语句;
  elseif 条件 then
     普通sql语句;
  else
     普通sql语句;
  end if;

• 循环语句
  • while循环

    while 条件 do
       普通sql语句;
    end while;

  • repeat循环

    repeat
      普通sql语句;
      until 条件; 
    end repeat;

  • loop循环

    loop_label: 标签名
        普通sql语句;
        -- 继续循环
        iterate loop_label;
        -- 跳出循环
        leave loop_label;
    end loop;

---

触发器是在对某个表执行操作(增加, 删除和修改)的前后执行用户特定的行为, 比如对其他的表执行增删改的操作

• 创建触发器

  -- 定义结束符为$, 在mac和linux中很有必要
  delimiter $
  -- 如果存在tri_before_update_userinfo触发器则删除, 方便调试和修改
  DROP TRIGGER if EXISTS tri_before_update_userinfo $
  -- 创建tri_before_update_userinfo触发器, 该触发器会在更新userinfo表之前执行begin和end之间的内容(before表示之前, after表示之后)
  CREATE TRIGGER tri_before_update_userinfo BEFORE UPDATE ON userinfo FOR EACH ROW 
  BEGIN
    -- 如果在userinfo表中更改name为tom的行则会在color表中插入一行(old表示原来的数据)
    IF old.name = 'tom' THEN
        INSERT INTO color(tag) VALUES('black');
    -- 如果在userinfo表中有name修改后为cc则会在color表中插入一行(new表示修改后的数据)
    ELSEIF new.name = 'cc' THEN
        INSERT INTO color(tag) VALUES('yellow');
    END IF;
  end $
  delimiter ;

• 执行触发器只需要修改userinfo表中的数据即可; 对于update操作既有old又有new关键字, 对于insert操作只有new关键字, 对于delete操作只有old关键字
• 删除指定触发器 drop trigger if exists 触发器名

---

存储过程相当于一些sql语句的堆积, 但是sql语句执行后的结果集以及变量都可以返回给用户; 而函数不能返回结果集, 仅仅是变量的操作

• 创建存储过程

  delimiter $
  DROP PROCEDURE IF EXISTS p1 $
  CREATE PROCEDURE p1(
  --     声明仅用传入参数用的整型形参
    in in_1 INT,
  --     声明既可以传入又可以当返回值的整型形参
    INOUT inout_1 int,
  --     声明仅用做返回值的整型形参
    OUT out_1 INT 
  )
  BEGIN
  --     声明语句块中的临时变量
    DECLARE tmp_in_1 INT;
    DECLARE tmp_inout_1 INT;
  --     赋值语句
    SET tmp_in_1 = in_1;
    set tmp_inout_1 = inout_1;
    SET out_1 = tmp_in_1 + tmp_inout_1;
  --     正常的sql查询语句
    SELECT * from userinfo LIMIT in_1, inout_1;
  end $
  delimiter ;

• 使用存储过程

  -- 设置用户变量传值,in类型可不用变量传值, out类型不能传入值, 
  -- set @in_1_tmp=1;
  set @inout_1_tmp=3;
  -- 调用存储过程, 传入参数
  CALL p1 (1,@inout_1_tmp,@out_1_tmp);
  -- 取得存储过程的执行结果, 包括sql语句结果集以及变量值(in, inout以及out类型变量都能取得他们的值)
  SELECT @in_1,@inout_1_tmp,@out_1_tmp;

• 删除存储过程 drop procedure 存储过程名

---

mysql中有许多对变量进行操作的内置函数, 同时我们也可以自定义函数

• 内置函数第一部分

  SELECT
    CHAR_LENGTH("test") AS "字符串长度",
  --     拼接的任意一个参数为null, 则拼接结果为null
    CONCAT("C://", "workplace") AS "字符串拼接",
    CONCAT_WS("-","nick","tom") AS "自定义连接符拼接",
    CONV('c',16,10) AS "进制转换",
    FORMAT(10000.00041,4) AS "格式化数字",
    INSERT("teach",1,2,'xxxx') AS "字符串替换"

• 内置函数第二部分

  SELECT
    INSTR("mttm","tt") AS "字串位置",
    LEFT("hello, world",5) AS "从左截取字符串",
    LOWER("HELLO") AS "转换小写",
    UPPER("world") AS "转换大写",
    LTRIM("     test   ") AS "开始去空格",
    RTRIM("        now     ") AS "结尾去空格",

• 内置函数第三部分

  SELECT
    LOCATE("tt","hehettlolo",2) AS "获取子序列位置",
    REPEAT(" | roor",5) AS "重复字符串生成",
    REPLACE("hello","ll","ww") AS "字符串替换",
    REVERSE("123456") AS "字符串反转",
    RIGHT("hello",3) AS "从右截取字符串",
    SUBSTRING("hello, test, world" FROM -11 FOR 4) AS "自定义截取字符串",
    SPACE(5) AS "返回空格字符串",
    TRIM("  test  ") AS "去除空格"

• 创建函数

  delimiter $
  DROP FUNCTION IF EXISTS func1 $
  CREATE FUNCTION func1(
  --     定义整型形参
    i1 int,
    i2 int
  )
  -- 定义返回参数类型
  RETURNS INT
  BEGIN
    DECLARE tmp INT DEFAULT 0;
    SET tmp = i1 + i2;
    RETURN tmp;
  END $
  delimiter ;

• 使用自定义函数 SELECT func1(1,1);
• 删除自定义函数 DROP FUNCTION IF EXISTS 函数名;

---

事务的本质就是在存储过程中将多条sql语句作为一个原子操作来执行, 其中之一未执行成功则直接回滚到原始状态

• 创建事务

  delimiter $
  CREATE PROCEDURE tp1(
  --     定义返回结果参数
    OUT num_flag_retunr INT
  )
  BEGIN
  --     sql执行发生异常时候, 返回值为2, 并回滚到原始状态
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SET num_flag_retunr=2;
        ROLLBACK;
    END;
  --     sql语言发生警告时候, 返回值为1, 并回滚到原始状态
    DECLARE EXIT HANDLER FOR SQLWARNING
    BEGIN
        SET num_flag_retunr=1;
        ROLLBACK;
    END;
  --     开始事务具体要执行的内容, 正确则提交执行结果否则执行上面的异常
    START TRANSACTION;
        DELETE FROM userinfo WHERE nid=4;
        INSERT INTO color(tag) VALUES("green");
    COMMIT;
    SET num_flag_retunr=0;
  END $
  delimiter ;

• 执行事务

  CALL tp1(@out_1);
  SELECT @out_1;

• 删除事务 DROP PROCEDURE IF EXISTS 事务名

---

索引相当于为我们指定的列建立一个目录, 根据目录我们能快速查找到所需数据

• 索引种类
  • 普通索引 index : 仅仅加速查询, 无约束
  • 唯一索引 unique : 加速查询, 指定的列不能重复, 可出现一次null
  • 主键索引 primary key : 加速查询, 列值唯一切不能为null
  • 组合索引 : 多列作为共同体组成索引, 效率高于多个索引列合并查询
  • 全文索引: 数据库存储时候对存储内容进行分词存储便于搜索查询
• 创建普通索引 CREATE INDEX name_index ON userinfo (name) 或者在创建表时候在表末尾加上 index 索引名 (列); 对于其他索引也是类似
• 删除索引 drop 索引名 on 表名;
• 查看当前表那些字段有索引 SHOW INDEX FROM userinfo;

• 以下情况即使创建了索引也不会使用索引:
  • %开头的模糊匹配条件: select * from 表名 where 列名 like '%其他';
  • 对建立索引的列使用函数查询: select * from 表名 where 函数名(列名) = 其他条件
  • 列类型匹配错误的条件查询时候 : select * from 表名 where 列名 = 不正确的类型;
  • 当or条件中含有未建立索引的列时: select * from 表名 where 条件01 or 条件02';
  • 匹配条件为不等于时候(主键例外) : select * from 表名 where 非主键列 != 其他;
  • 匹配条件为大于的时候(主键或索引为整型例外) : select * from 表名 where 列名 > 其他;
  • 排序时候选择的映射与排序列不一样即使都是索引列也不走索引(主键例外) : select 索引列-01 from 表名 order by 索引列-02 desc;
  • 组合索引为 索引列-01和索引列-02, 当先查索引列-02时候也不走索引
• 用执行计划可以详细显示sql语句(使用查询语句才有意义)的执行情况, 例如如执行EXPLAIN SELECT * from userinfo;

• 关于上述表中详细字段解释 :
  • id 查询顺序标识, 表示sql查询语句的执行顺序
  • select_type 查询语句的类型: 简单查询SIMPLE, 最外层查询PRIMARY, 子查询DERIVED, 映射是子查询SUBQUERY, 联合查询UNION, 联合查询所得UNION RESULT
  • table 正在访问的表名
  • partitions 分区类型
  • type 查询方式, 查询时间性能排序 : 全表扫描all > 全索引扫描index > 索引部分范围内扫描range > 多单列索引合并扫描index_merge > 索引匹配一个或多个值扫描ref > 联合主键索引或者唯一索引扫描eq_ref > 最多有一个匹配行优化后作为常量表扫描const, 还有system特列, 性能与const近似
  • possible_keys 可能使用的索引
  • key 实际使用的索引
  • key_len 字节长度
  • rows 预测找到所需内容要查找的行数
  • extra 其他信息, 多为mysql解决查询的详细信息
• limit分页优化

  -- 查询第1000条数据之后的10条数据
  -- 这种查询方式会进行全文扫描
  SELECT * FROM userinfo LIMIT 1000,5;
  -- 这种方式仅仅优化了一点, 使用了一些普通索引和索引合并查询
  EXPLAIN SELECT * FROM userinfo WHERE nid > (SELECT nid FROM userinfo LIMIT 1000, 1) LIMIT 5;
  -- 直接根据计算所得后的最大条数倒序排列查询
  SELECT * FROM userinfo WHERE nid < 上次最大条数 ORDER BY nid DESC LIMIT 5;

• 慢查询日志可以根据自定义设置记录那些查询性能查的sql语句 :
  • 查看全局变量 show global variables like "%名称"
  • 设置全局变量 set global 变量名=值
  • 是否开启慢日志全局变量 slow_query_log = OFF
  • 时间限制全局变量 long_query_time = 2
  • 日志文件存放位置全局变量 slow_query_log_file = /usr/slow.log
  • 是否记录未使用索引的查询语句全局变量 log_queries_not_using_indexes = OFF
• 格式化查看慢日志 mysqldumpslow [option] 日志存放位置, 常用option如下:
  • 版本 -v或者--verbose
  • 调试模式 -d或者--debug
  • 排序方式 -s 规则选项, 默认是平均查询时间at
  • 倒序排列显示 -r
  • 显示前number条 -t number
  • 不要将sql中数字转换成N，字符串转换成S, 选项为, -a

---

python使用pymysql操作数据库

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql

# 创建连接通道, 设置连接ip, port, 用户, 密码以及所要连接的数据库
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='pymysql_db')

# 创建游标, 操作数据库, 指定游标返回内容为字典类型
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 调用存储过程, 传入参数
cursor.callproc('p1', (1, 5, 0))

# 得到结果集1, 即sql语句执行结果
select_result = cursor.fetchone()
print(select_result)

# 执行存储过程, 获取返回值, @_存储过程名_第一个参数
cursor.execute("select @_p1_0,@_p1_1,@_p_2")
select_result = cursor.fetchone()
print(select_result)

cursor.close()
conn.close()