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()

posted @ 2017-10-02 13:27 糕同学阅读(491) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

糕同学

码到精神失常

mysql 以及在python中使用pymysql操作数据库

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

用户操作以及用户权限部分

数据表的相关操作

mysql中字段的常见类型

表中数据的操作

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

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

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

python中使用pymysql操作数据库

公告

糕同学

码到精神失常

mysql 以及在python中使用pymysql操作数据库

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

用户操作以及用户权限部分

数据表的相关操作

mysql中字段的常见类型

表中数据的操作

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

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

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

python中使用pymysql操作数据库

公告

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