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;

执行结果-01

执行结果-02

删除存储过程 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()

执行结果

Mysql与Pymysql

Mysql与Pymysql

相关阅读更多精彩内容

友情链接更多精彩内容