您的位置: 首页  >  文章  >  python（pymysql）之mysql简单操作

python（pymysql）之mysql简单操作

分类: 文章 • 2024-07-03 16:39:22

一、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简单操作

   

　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地址'      -- 取消权限 　　

四、表操作

　　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模块　　

python（pymysql）之mysql简单操作

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

相关推荐