python和mysql匹配吗_Python | Python学习之mysql交互详解

前言

最近在学习scrapy redis，在复习redis的同时打算把mysql和mongodb也复习一下，本篇为mysql篇，实例比较简单，学习sql还是要动手实操记的比较牢。

安装与启动

安装：sudo apt-get install mysql-server

查看服务：ps ajx | grep mysql

停止服务:sudo service mysql stop

开启服务:sudo service mysql start

重启服务:sudo service mysql restart

链接数据库：mysql -uroot -p后输入密码

查看版本：select version();

常见数据库语句

查看数据库：show database;

创建数据库：create database 库名 [charset = UTF8];

查看建库语句：show create database 库名;

使用数据库：use 库名;

删除数据库：drop 库名;

常见数据表语句

查看表：show table;

查看表结构：desc 表名;

创建表：

CREATE TABLE table_name(

column1 datatype contrai,

column2 datatype,

column3 datatype,

.....

columnN datatype,

PRIMARY KEY(one or more columns)

);

创建表常用属性字段：

-- auto_inorement 表示自动增长

-- not null 表示不为空

-- primary key 表示为主键

-- defaul 表示默认值

删除表：drop table;

修改表结构：

添加字段：alter table 表名 add 列名 类型;

修改字段(重命名)：alter table 表名 change 原名 新名 类型及约束;

修改字段(不重命名)：alter table 表名 modify 列名 类型及约束;

删除字段：alter table 表名 drop 列名;

常用增删改查

基本查询

查看所有列：select * from 表名;

查看指定列：select 列1,列2,... from 表名;

新增

全列插入：insert into 表名 values(...); --需要给主键留下占位符,用0或null皆可。

部分列插入：insert into 表名(列1,...) values(值1,...);

插入多行全列数据：insert into 表名 values(...),(...)...;

插入多行部分列数据：insert into 表名(列1,...) values(值1,...),(值1,...)...;

更新

更新操作：update 表名 set 列1=值1,列2=值2... where 条件;

删除

删除操作(不推荐)：delete from 表名 where 条件;

逻辑删除(推荐)：update 字段名 set isvalid=0 where id=1; --设置删除字段,执行删除字段的操作即对该字段更新。

mysql查询详解

查询消除重复行：select distinct 列1,... from 表名;

条件查询

where条件查询：select * from 表名 where 条件;

where可以与比较运算符、逻辑运算符、模糊查询、范围查询、空判断搭配使用

比较运算符

等于: =

大于: >

大于等于: >=

小于: <

小于等于: <=

不等于: != 或 <>

举个栗子：

select * from students where id > 1;

select * from students where id <= 2;

select * from students where name != '咸鱼';

select * from students where is_delete=0;

逻辑运算符

and

or

not

举个栗子：

select * from students where id > 3 and gender=0;

select * from students where id < 4 or is_delete=0;

select * from students where id not 4;

模糊查询

like

% 表示任意多个任意字符

_ 表示一个任意字符

rlike

举个栗子：

select * from students where name like '咸%'; --查询以咸字开头的内容

select * from students where name like '咸_'; --查询以咸字开头且后面只有一个字的内容

select * from students where name like '咸%' or name like '%鱼'; -- 查询以咸字开头或以鱼字结尾的内容

范围查询

in 表示在一个非连续的范围内

no in 表示不在一个非连续的范围内

between ... and ... 表示在一个连续的范围内

rlike 表示正则查询，可以使用正则表达式查询数据

举个栗子：

select * from students where id in(1,3,8); -- 查询 id 在 1,3,8 当中的内容

select * from students where id not in(1,3,8); -- 查询 id 不在 1,3,8 当中的内容

select * from students where id between 3 and 8; -- 查询 id 在3到8之间的内容

select * from students where name rlike "^咸"; -- 查询 name 是以咸字开头的内容

空判断

判断是否为空 is null

举个栗子：

select * from students where height is null;

以上几种预算符优先级为:

优先级由高到低的顺序为：小括号、not、比较运算符、逻辑运算符。

and比or先运算，如果同时出现并希望先算or，需要结合()使用。

排序

asc 升序

desc 降序

举个栗子：

select * from students order by age desc,height desc; --显示所有的学生信息，先按照年龄从大到小排序，当年龄相同时 按照身高从高到矮排序

聚合函数

count(*)查询总数

max(列)表示求此列的最大值

min(列)表示求此列的最小值

sum(列)表示求此列的和

avg(列)表示求此列的平均值

举个栗子：

select count(*) from students;

select max(id) from students where gender=2;

select min(id) from students where is_delete=0;

select sum(age) from students where gender=1;

select sum(age)/count(*) from students where gender=1; --求平均年龄

select avg(id) from students where is_delete=0 and gender=2;

分组

group by 将查询结果按照1个或多个字段进行分组，字段值相同的为一组

group_concat 表示分组之后，根据分组结果，使用group_concat()来放置每一组的某字段的值的集合

举个栗子：

select gender from students group by gender; -- 将学生按照性别分组

输出结果：

+--------+

| gender |

+--------+

| 男 |

| 女 |

| 中性 |

| 保密 |

+--------+

select gender,group_concat(name) from students group by gender;

输出结果：

+--------+-----------------------------------------------------------+

| gender | group_concat(name) |

+--------+-----------------------------------------------------------+

| 男 | 小彭,小刘,小周,小程,小郭 |

| 女 | 小明,小月,小蓉,小贤,小菲,小香,小杰 |

| 中性 | 小金 |

| 保密 | 小凤 |

+--------+-----------------------------------------------------------+

分页

select * from 表名 limit start,count

举个栗子：

select * from students where gender=1 limit 0,3; --查询前三行的数据

连接查询

语法：

select * from 表1 inner/left/right join 表2 on 表1.列 = 表2.列

其中：

inner join(内连接查询)：查询的结果为两个表匹配到的数据

right join(右连接查询)：查询的结果为两个表匹配到的数据，右表特有的数据，对于左表中不存在的数据使用null填充

left join(左连接查询)：查询的结果为两个表匹配到的数据，左表特有的数据，对于右表中不存在的数据使用null填充

举个栗子：

select from students inner join classes on students.cls_id = classes.id;

select from students as s left join classes as c on s.cls_id = c.id;

select * from students as s right join classes as c on s.cls_id = c.id;

子查询

在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句。

子查询可以和 in 搭配使用

主查询 where 条件 in (子查询)

数据库的备份与恢复

数据库备份

mysqldump –uroot –p 数据库名 > 备份文件名.sql;

数据库恢复

mysql -uroot –p 新数据库名 < 备份文件名.sql

Python与mysql交互

Python与mysql交互流程

安装与导入

安装相关库：pip install pymysql

导入：from pymysql import *

创建connection对象

connection = connect(host, port, database, user, password, charset)

其中参数如下：

host：连接的mysql主机，如果本机是'localhost'

port：连接的mysql主机的端口，默认是3306

database：数据库的名称

user：连接的用户名

password：连接的密码

charset：通信采用的编码方式，推荐使用utf8

connection对象方法如下：

close()关闭连接

commit()提交

cursor()返回Cursor对象，用于执行sql语句并获得结果

获取cursor

cursor=connection.cursor()

其中常用方法：

close():关闭cursor

execute(operation [, parameters ]):执行语句，返回受影响的行数，主要用于执行insert、update、delete语句，也可以执行create、alter、drop等语句。

fetchone():执行查询语句时，获取查询结果集的第一个行数据，返回一个元组

fetchall():执行查询时，获取结果集的所有行，一行构成一个元组，再将这些元组装入一个元组返回

举个栗子：

from pymysql import *

def main():

conn = connect(host='localhost',port=3306,database='xianyuplus',user='root',password='mysql',charset='utf8')

cs1 = conn.cursor()

count = cs1.execute('insert into xianyufans(name) values("666")')

conn.commit()

cs1.close()

conn.close()

if __name__ == '__main__':

main()

mysql视图

什么是视图？

视图是对若干张基本表的引用，一张虚表，查询语句执行的结果，不存储具体的数据。

视图语句

创建视图:create view 视图名称 as select语句; --建议视图以v_开头

查看视图：show tables;

使用视图：select * from 视图名称;

删除视图：drop view 视图名称;

视图作用

提高了重用性，就像一个函数

对数据库重构，却不影响程序的运行

提高了安全性能，可以对不同的用户

让数据更加清晰

mysql事务

什么是事务？

事务,它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。

事务有什么特点？

原子性，一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性。

一致性，数据库总是从一个一致性的状态转换到另一个一致性的状态。

隔离性，一个事务所做的修改在最终提交以前，对其他事务是不可见的。

持久性，一旦事务提交，则其所做的修改会永久保存到数据库。

事务相关命令

开启事务：start transaction; 或者 begin;

提交事务：commit;

回滚事务：rollback;

mysql索引

什么是索引？

数据库索引好比是一本书前面的目录，能加快数据库的查询速度

索引相关命令

创建索引：create index 索引名称 on 表名(字段名称(长度)) --当指定索引的字段类型为字符串时，应填写长度

查看索引：show index from 表名;

删除索引：drop index 索引名称 on 表名;

注意事项

建立太多的索引将会影响更新和插入的速度，因为它需要同样更新每个索引文件。对于一个经常需要更新和插入的表格，就没有必要为一个很少使用的where字句单独建立索引了，对于比较小的表，排序的开销不会很大，也没有必要建立另外的索引。

建立索引会占用磁盘空间。

尾言

以上就是关于mysql的一些用法，其实是比较基础的，重点部分是关于mysql的查询部分，毕竟在业务应用中主要还是查询为主。