mysql 以及在python中使用pymysql操作数据库
这次介绍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()