python（pymysql）之mysql简单操作

        

一、mysql简单介绍

　　说到数据库，我们大多想到的是关系型数据库，比如mysql、oracle、sqlserver等等，这些数据库软件在windows上安装都非常的方便，在Linux上如果要安装数据库，咱不得不首先推荐的是mysql数据库了，而且Mysql数据库的第一个版本就是发行在Linux系统上的。

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，目前属于Oracle公司。MySQL是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。MySQL的SQL语言是用于访问数据库的最常用标准化语言。MySQL软件采用了双授权政策（本词条“授权政策”），它分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择MySQL作为网站数据库。由于其社区版的性能卓越，搭配PHP和Apache可组成良好的开发环境

二、安装msql

　　1、centos6.4编译安装mysql

　　参考地址：http://www.centoscn.com/CentosServer/sql/2014/0316/2584.html

　　2、windows安装msyql　　

1 2	官网下载地址： http://dev.mysql.com/downloads/mysql/

三、mysql简单操作

　　可用过客户端远程操作mysql：Navicat for MySql工具

　　1、启动数据库：

　　

　　链接数据库：

　　

　　2、显示数据库

　　　　

1 2 3 4

默认数据库： 　　mysql - 用户权限相关数据 　　test - 用于用户测试数据 　　information_schema - MySQL本身架构相关数据

 　　2、使用数据库

　　　　

　　3、用户授权

　　用户管理　　

1 2 3 4 5 6 7 8 9 10

创建用户     create user '用户名'@'IP地址' identified by '密码'; 删除用户     drop user '用户名'@'IP地址'; 修改用户     rename user '用户名'@'IP地址'; to '新用户名'@'IP地址';; 修改密码     set password for '用户名'@'IP地址' = Password('新密码')    PS：用户权限相关数据保存在mysql数据库的user表中，所以也可以直接对其进行操作（不建议）

 　　授权管理：　　

1 2 3

show grants for '用户'@'IP地址'                  -- 查看权限 grant  权限 on 数据库.表 to   '用户'@'IP地址'      -- 授权 revoke 权限 on 数据库.表 from '用户'@'IP地址'      -- 取消权限

 权限

 数据库

 对于用户和IP

四、表操作

　　1、在库中创建表　　

1 2 3 4 5

use test #选择test库 create table 表名(    #创建表     列名  类型  是否可以为空，     列名  类型  是否可以为空 )

1 2 3

是否可空，null表示空，非字符串     not null    - 不可空     null        - 可空

1 2 3 4 5

默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值     create table tb1(         nid int not null defalut 2,         num int not null     )

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

自增，如果为某列设置自增列，插入数据时无需设置此列，默认将自增（表中只能有一个自增列）     create table tb1(         nid int not null auto_increment primary key,         num int null     )     或     create table tb1(         nid int not null auto_increment,         num int null,         index(nid)     )     注意：1、对于自增列，必须是索引（含主键）。          2、对于自增可以设置步长和起始值              show session variables like 'auto_inc%';              set session auto_increment_increment=2;              set session auto_increment_offset=10;                shwo global  variables like 'auto_inc%';              set global auto_increment_increment=2;              set global auto_increment_offset=10;

1 2 3 4 5 6 7 8 9 10 11

主键，一种特殊的唯一索引，不允许有空值，如果主键使用单个列，则它的值必须唯一，如果是多列，则其组合必须唯一。     create table tb1(         nid int not null auto_increment primary key,         num int null     )     或     create table tb1(         nid int not null,         num int not null,         primary key(nid,num)     )

1 2 3 4 5 6 7 8 9 10 11 12

外键，一个特殊的索引，只能是指定内容     creat table color(         nid int not null primary key,         name char(16) not null     )       create table fruit(         nid int not null primary key,         smt char(32) null ,         color_id int not null,         constraint fk_cc foreign key (color_id) references color(nid)     )

 　　2、数据表操作：　　

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

#基于Navicat for MySql工具实现操作 show tables; #查询库中有哪些表 create table lcj( id int not null auto_increment PRIMARY KEY,  #PRIMARY KEY主键IP 自增 name char(30) not null, sex char(4) not null, age tinyint unsigned not null, tel char(13) null default  "-");  #电话默认为- SELECT * from lcj;  #查询表 #插入数据 insert into lcj VALUES(0001,'xiaoluo','男',18,'13520617734') #同时插入多条数据 insert into lcj VALUES(  0004,'xiaoluo','男',18,'13520617734'), (0002,'qq','man',18,'13212345432'), (0003,'ww','gril',19,'13567890987');   #更新数据;将ID为3的姓名改为lcj UPDATE lcj set name = 'lcj' WHERE id = 3; #查看字典条数据 SELECT name from lcj where id = 2; #查看所有数据 SELECT * from lcj #删除数据 DELETE from lcj WHERE id = 4;

 　　清空表：　　

1 2	delete from 表名 truncate table 表名

 　　修改表：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

添加列：alter table 表名 add 列名 类型 删除列：alter table 表名 drop column 列名 修改列：         alter table 表名 modify column 列名 类型;  -- 类型         alter table 表名 change 原列名 新列名 类型; -- 列名，类型    添加主键：         alter table 表名 add primary key(列名); 删除主键：         alter table 表名 drop primary key;         alter table 表名  modify  列名 int, drop primary key;    添加外键：alter table 从表 add constraint 外键名称（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段); 删除外键：alter table 表名 drop foreign key 外键名称    修改默认值：ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000; 删除默认值：ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;

 　　基本数据类型

　　MySQL的数据类型大致分为：数值、时间和字符串；

　　参考地址：http://www.runoob.com/mysql/mysql-data-types.html

　　mysql其他操作：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68

1、条件     select * from 表 where id > 1 and name != 'alex' and num = 12;       select * from 表 where id between 5 and 16;       select * from 表 where id in (11,22,33)     select * from 表 where id not in (11,22,33)     select * from 表 where id in (select nid from 表)   2、通配符     select * from 表 where name like 'ale%'  - ale开头的所有（多个字符串）     select * from 表 where name like 'ale_'  - ale开头的所有（一个字符）   3、限制     select * from 表 limit 5;            - 前5行     select * from 表 limit 4,5;          - 从第4行开始的5行     select * from 表 limit 5 offset 4    - 从第4行开始的5行   4、排序     select * from 表 order by 列 asc              - 根据 “列” 从小到大排列     select * from 表 order by 列 desc             - 根据 “列” 从大到小排列     select * from 表 order by 列1 desc,列2 asc    - 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序   5、分组     select num from 表 group by num     select num,nid from 表 group by num,nid     select num,nid from 表  where nid > 10 group by num,nid order nid desc     select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid       select num from 表 group by num having max(id) > 10       特别的：group by 必须在where之后，order by之前   6、连表     无对应关系则不显示     select A.num, A.name, B.name     from A,B     Where A.nid = B.nid       无对应关系则不显示     select A.num, A.name, B.name     from A inner join B     on A.nid = B.nid       A表所有显示，如果B中无对应关系，则值为null     select A.num, A.name, B.name     from A left join B     on A.nid = B.nid       B表所有显示，如果B中无对应关系，则值为null     select A.num, A.name, B.name     from A right join B     on A.nid = B.nid   7、组合     组合，自动处理重合     select nickname     from A     union     select name     from B       组合，不处理重合     select nickname     from A     union all     select name     from B

 

五、基于Pycharm中pymysql模块实现mysql操作

　　1、Pycharm安装pymysql模块　　

　　2、简单使用pymysql某块

　　1）插入数据

　　通过pymysql向远程数据库同时插入多条数据并打印插入数据条数

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

#!/usr/bin/env python # -*- coding:utf-8 -*- # Author:lcj import pymysql #连接数据库 conn = pymysql.connect(host='192.168.1.152',port= 3306,user = 'root',passwd='123123',db='test') #db：库名 #创建游标 cur = conn.cursor() #插入一条数据 # reCount = cur.excute('insert into lcj(name,age) vaules(%s,%s)',('ff',18)) #向test库中的lcj表插入 # ret = cur.executemany("insert into lcj(name,tel)values(%s,%s)", [("kk",13212344321),("kw",13245678906)]) #同时向数据库lcj表中插入多条数据 ret = cur.executemany("insert into lcj values(%s,%s,%s,%s,%s)", [(41,"xiaoluo41",'man',24,13212344332),                                                               (42,"xiaoluo42",'gril',21,13245678948),                                                               (43,"xiaoluo43",'gril',22,13245678949),                                                                  (44,"xiaoluo44",'main',24,13543245648)]) #提交 conn.commit() #关闭指针对象 cur.close() #关闭连接对象 conn.close() #打印结果 print(ret)

 　　2）查询数据

　　在Pycharm控制台输出lcj表中数据

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

# !/usr/bin/env python # -*- coding:utf-8 -*- # Author:lcj import pymysql #连接数据库 conn = pymysql.connect(host='192.168.1.152',port= 3306,user = 'root',passwd='123123',db='test') #db：库名 #创建游标 cur = conn.cursor() #查询lcj表中存在的数据 cur.execute("select * from lcj") #fetchall:获取lcj表中所有的数据 ret1 = cur.fetchall() print(ret1) print("----------------------") #获取lcj表中前三行数据 ret2 = cur.fetchmany(3) print(ret2) print("------------------------------") #获取lcj表中第一行数据 ret3= cur.fetchone() print(ret3) #同时向数据库lcj表中插入多条数据 # ret = cur.executemany("insert into lcj values(%s,%s,%s,%s,%s)", [(41,"xiaoluo41",'man',24,13212344332), #                                                             (42,"xiaoluo42",'gril',21,13245678948), #                                                             (43,"xiaoluo43",'gril',22,13245678949), #                                                                (44,"xiaoluo44",'main',24,13543245648)]) #提交 conn.commit() #关闭指针对象 cur.close() #关闭连接对象 conn.close()

 注：在fetch数据时按照顺序进行，可以使用cursor.scroll(num,mode)来移动游标位置，如：

• cursor.scroll(1,mode='relative')  # 相对当前位置移动【1：表示向下移动一行，-1：表示向上移动一行】
• cursor.scroll(2,mode='absolute') # 相对绝对位置移动 【1：表示向上移动一行，-1：表示向下移动一行】
  

　　3）删除数据　　

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

# !/usr/bin/env python # -*- coding:utf-8 -*- # Author:lcj import pymysql #连接数据库 conn = pymysql.connect(host='192.168.1.152',port= 3306,user = 'root',passwd='123123',db='test') #db：库名 #创建游标 cur = conn.cursor() #删除cj表中数据 cur.execute("delete * from lcj") #提交 conn.commit() #关闭指针对象 cur.close() #关闭连接对象 conn.close()

 　　4）修改表中的数据　　

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

# !/usr/bin/env python # -*- coding:utf-8 -*- # Author:lcj import pymysql #连接数据库 conn = pymysql.connect(host='192.168.1.152',port= 3306,user = 'root',passwd='123123',db='test') #db：库名 #创建游标 cur = conn.cursor() #将lcj表中id=3的name 修改为lcjj cur.execute("UPDATE lcj set name = 'lcjj' WHERE id = 3")  #逼表中所有的操作都可以再此进行操作 #提交 conn.commit() #关闭指针对象 cur.close() #关闭连接对象 conn.close()

 　　5）fetch数据类型

　　关于默认获取的数据是元祖类型，如果想要或者字典类型的数据，即：　　

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

# !/usr/bin/env python # -*- coding:utf-8 -*- # Author:lcj import pymysql #连接数据库 conn = pymysql.connect(host='192.168.1.152',port= 3306,user = 'root',passwd='123123',db='test') #db：库名 #设置游标类型，默认游标类型为元祖形式 #将游标类型设置为字典形式 cur = conn.cursor(cursor=pymysql.cursors.DictCursor) cur.execute("select * from lcj")  #逼表中所有的操作都可以再此进行操作 #将lcj表中所有数据以字典形式输出 ret = cur.fetchall() print(ret)   #[{'age': 18, 'tel': '13520617734', 'name': 'xiaoluo', 'id': 1, 'sex': '?'}, #提交 conn.commit() #关闭指针对象 cur.close() #关闭连接对象 conn.close()