编程学习python全栈开发数据分析

Mysql与Pymysql

2017-03-18  本文已影响7799人  凉茶半盏

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

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

连接后显示 显示所有的数据库

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

**数据表的相关操作: **

*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;

**mysql中字段的常见类型: **

** 表中数据的操作 **

**在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
执行结果 
-- 修改视图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;

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

-- 定义结束符为$$, 在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 ;

** 存储过程相当于一些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;
执行结果-01 执行结果-02

** 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 ;

** 事务的本质就是在存储过程中将多条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;

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

有索引的字段显示 执行计划执行结果 
-- 查询第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;

** 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()
执行结果
上一篇下一篇

猜你喜欢

热点阅读