这次介绍mysql以及在python中如何用pymysql操作数据库, 以及在mysql中存储过程, 触发器以及事务的实现, 对应到pymysql中应该如何操作.
首先我们在cmd窗口中展示常见的sql命令
连接数据库
mysql -u root -p password -h (localhost)
显示数据库
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` ( `nid` INT (11) NOT NULL AUTO_INCREMENT, `name` VARCHAR (32) DEFAULT NULL, `color_nid` INT (11) DEFAULT NULL, PRIMARY KEY (`nid`), CONSTRAINT `userinfo_ibfk_1` FOREIGN KEY (`color_nid`) REFERENCES `color` (`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中的条件和循环语句 - 条件判断
if 条件 then 普通sql语句；elseif 条件 then 普通sql语句；else 普通sql语句；end if;
- 循环语句
- while循环
while 条件 do 普通sql语句；end while;
- 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_imout_1;- - 正常的esql查询语句SELECT * FROM userinfo LIMIT in_1, onout_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 存储过程名
’--------------------------------------------------------------‘
索引相当于为我们指定的列建立一个目录，根据目录我们能快速查找到所需数据
- 索引种类
- 普通索引 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()