Python与Mysql

最新推荐文章于 2024-07-20 17:12:48 发布

菜鸟入行

最新推荐文章于 2024-07-20 17:12:48 发布

阅读量594

点赞数

文章标签： python mysql

安装

安装

sudo apt-get install mysql-server mysql-client
然后按照提示输入

管理服务

启动

service mysql start

停止

service mysql stop

重启

service mysql restart

允许远程连接

找到mysql配置文件并修改

sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
将bind-address=127.0.0.1注释

登录mysql，运行命令

grant all privileges on *.* to 'root'@'%' identified by 'mysql' with grant option;
flush privileges;

重启mysql

字段类型

在mysql中包含的数据类型很多，这里主要列出来常用的几种
数字：int,decimal
字符串：char,varchar,text
日期：datetime，timestamp
布尔：bit

约束

主键primary key
非空not null
惟一unique
默认default
外键foreign key

远程连接

shell> mysql -u USERNAME -p PASSWORD -h IPV4 -p PORT

数据库操作

创建数据库

create database 数据库名 charset=utf8;

删除数据库

drop database 数据库名;

切换数据库

use 数据库名;

查看当前选择的数据库

select database();

数据操作

查询

select * from 表名

增加

全列插入：insert into 表名 values(...)
缺省插入：insert into 表名(列1,...) values(值1,...)
同时插入多条数据：insert into 表名 values(...),(...)...;
或insert into 表名(列1,...) values(值1,...),(值1,...)...;

主键列是自动增长，但是在全列插入时需要占位，通常使用0，插入成功后以实际数据为准
修改

update 表名 set 列1=值1,... where 条件

删除

delete from 表名 where 条件

逻辑删除，本质就是修改操作update

alter table students add isdelete bit default 0;
如果需要删除则
update students isdelete=1 where ...;

数据查询

条件

使用where子句对表中的数据筛选，结果为true的行会出现在结果集中
语法如下：

select * from 表名 where 条件;

比较运算符

等于=
大于>
大于等于>=
小于<
小于等于<=
不等于!=或<>
查询编号大于3的学生

select * from students where id>3;

查询编号不大于4的科目

select * from subjects where id<=4;

查询姓名不是“黄蓉”的学生

select * from students where sname!='黄蓉';

查询没被删除的学生

select * from students where isdelete=0;

逻辑运算符

and
or
not
查询编号大于3的女同学

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

查询编号小于4或没被删除的学生

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

模糊查询

like
%表示任意多个任意字符
_表示一个任意字符
查询姓黄的学生

select * from students where sname like '黄%';

查询姓黄并且名字是一个字的学生

select * from students where sname like '黄_';

查询姓黄或叫靖的学生

select * from students where sname like '黄%' or sname like '%靖%';

范围查询

in表示在一个非连续的范围内
查询编号是1或3或8的学生

select * from students where id in(1,3,8);

between ... and ...表示在一个连续的范围内
查询学生是3至8的学生

select * from students where id between 3 and 8;

查询学生是3至8的男生

select * from students where id between 3 and 8 and gender=1;

空判断

注意：null与''是不同的
判空is null
查询没有填写地址的学生

select * from students where hometown is null;

判非空is not null
查询填写了地址的学生

select * from students where hometown is not null;

查询填写了地址的女生

select * from students where hometown is not null and gender=0;

优先级

小括号，not，比较运算符，逻辑运算符
and比or先运算，如果同时出现并希望先算or，需要结合()使用

聚合

为了快速得到统计数据，提供了5个聚合函数
count(*)表示计算总行数，括号中写星与列名，结果是相同的
查询学生总数

select count(*) from students;

max(列)表示求此列的最大值
查询女生的编号最大值

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

min(列)表示求此列的最小值
查询未删除的学生最小编号

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

sum(列)表示求此列的和
查询男生的编号之后

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

avg(列)表示求此列的平均值
查询未删除女生的编号平均值

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

分组

按照字段分组，表示此字段相同的数据会被放到一个组中
分组后，只能查询出相同的数据列，对于有差异的数据列无法出现在结果集中
可以对分组后的数据进行统计，做聚合运算
语法：

select 列1,列2,聚合... from 表名 group by 列1,列2,列3...

查询男女生总数

select gender as 性别,count(*)
from students
group by gender;

查询各城市人数

select hometown as 家乡,count(*)
from students
group by hometown;

分组后的数据筛选

语法：

select 列1,列2,聚合... from 表名
group by 列1,列2,列3...
having 列1,...聚合...

having后面的条件运算符与where的相同
查询男生总人数

方案一
select count(*)
from students
where gender=1;
-----------------------------------
方案二：
select gender as 性别,count(*)
from students
group by gender
having gender=1;

对比where与having

where是对from后面指定的表进行数据筛选，属于对原始数据的筛选
having是对group by的结果进行筛选

排序

为了方便查看数据，可以对数据进行排序
语法：

select * from 表名
order by 列1 asc|desc,列2 asc|desc,...

将行数据按照列1进行排序，如果某些行列1的值相同时，则按照列2排序，以此类推
默认按照列值从小到大排列
asc从小到大排列，即升序
desc从大到小排序，即降序
查询未删除男生学生信息，按学号降序

select * from students
where gender=1 and isdelete=0
order by id desc;

查询未删除科目信息，按名称升序

select * from subject
where isdelete=0
order by stitle;

获取部分行

当数据量过大时，在一页中查看数据是一件非常麻烦的事情
语法

select * from 表名
limit start,count

从start开始，获取count条数据
start索引从0开始

关系

创建成绩表scores，结构如下
- id
- 学生
- 科目
- 成绩
思考：学生列应该存什么信息呢？
答：学生列的数据不是在这里新建的，而应该从学生表引用过来，关系也是一条数据；根据范式要求应该存储学生的编号，而不是学生的姓名等其它信息
同理，科目表也是关系列，引用科目表中的数据

创建表的语句如下

create table scores(
id int primary key auto_increment,
stuid int,
subid int,
score decimal(5,2)
);

外键

思考：怎么保证关系列数据的有效性呢？任何整数都可以吗？
答：必须是学生表中id列存在的数据，可以通过外键约束进行数据的有效性验证
为stuid添加外键约束

alter table scores add constraint stu_sco foreign key(stuid) references students(id);

此时插入或者修改数据时，如果stuid的值在students表中不存在则会报错
在创建表时可以直接创建约束

create table scores(
id int primary key auto_increment,
stuid int,
subid int,
score decimal(5,2),
foreign key(stuid) references students(id),
foreign key(subid) references subjects(id)
);

外键的级联操作

在删除students表的数据时，如果这个id值在scores中已经存在，则会抛异常
推荐使用逻辑删除，还可以解决这个问题
可以创建表时指定级联操作，也可以在创建表后再修改外键的级联操作
语法

alter table scores add constraint stu_sco foreign key(stuid) references students(id) on delete cascade;

级联操作的类型包括：
- restrict（限制）：默认值，抛异常
- cascade（级联）：如果主表的记录删掉，则从表中相关联的记录都将被删除
- set null：将外键设置为空
- no action：什么都不做

连接查询

连接查询分类如下：
- 表A inner join 表B：表A与表B匹配的行会出现在结果中
- 表A left join 表B：表A与表B匹配的行会出现在结果中，外加表A中独有的数据，未对应的数据使用null填充
- 表A right join 表B：表A与表B匹配的行会出现在结果中，外加表B中独有的数据，未对应的数据使用null填充
在查询或条件中推荐使用“表名.列名”的语法
如果多个表中列名不重复可以省略“表名.”部分
如果表的名称太长，可以在表名后面使用' as 简写名'或' 简写名'，为表起个临时的简写名称

例子：

查询学生的姓名、平均分

select students.sname,avg(scores.score)
from scores
inner join students on scores.stuid=students.id
group by students.sname;

查询男生的姓名、总分

select students.sname,avg(scores.score)
from scores
inner join students on scores.stuid=students.id
where students.gender=1
group by students.sname;

查询科目的名称、平均分

select subjects.stitle,avg(scores.score)
from scores
inner join subjects on scores.subid=subjects.id
group by subjects.stitle;

查询未删除科目的名称、最高分、平均分

select subjects.stitle,avg(scores.score),max(scores.score)
from scores
inner join subjects on scores.subid=subjects.id
where subjects.isdelete=0
group by subjects.stitle;

子查询

查询支持嵌套使用
查询各学生的语文、数学、英语的成绩

字符串函数

查看字符的ascii码值ascii(str)，str是空串时返回0

select ascii('a');

查看ascii码值对应的字符char(数字)

select char(97);

拼接字符串concat(str1,str2...)

select concat(12,34,'ab');

包含字符个数length(str)

select length('abc');

截取字符串
- left(str,len)返回字符串str的左端len个字符
- right(str,len)返回字符串str的右端len个字符
- substring(str,pos,len)返回字符串str的位置pos起len个字符

select substring('abc123',2,3);

去除空格
- ltrim(str)返回删除了左空格的字符串str
- rtrim(str)返回删除了右空格的字符串str
- trim([方向 remstr from str)返回从某侧删除remstr后的字符串str，方向词包括both、leading、trailing，表示两侧、左、右

select trim('  bar   ');
select trim(leading 'x' FROM 'xxxbarxxx');
select trim(both 'x' FROM 'xxxbarxxx');
select trim(trailing 'x' FROM 'xxxbarxxx');

返回由n个空格字符组成的一个字符串space(n)

select space(10);

替换字符串replace(str,from_str,to_str)

select replace('abc123','123','def');

大小写转换，函数如下
- lower(str)
- upper(str)

select lower('aBcD');

数学函数

求绝对值abs(n)

select abs(-32);

求m除以n的余数mod(m,n)，同运算符%

select mod(10,3);
select 10%3;

地板floor(n)，表示不大于n的最大整数

select floor(2.3);

天花板ceiling(n)，表示不小于n的最大整数

select ceiling(2.3);

求四舍五入值round(n,d)，n表示原数，d表示小数位置，默认为0

select round(1.6);

求x的y次幂pow(x,y)

select pow(2,3);

获取圆周率PI()

select PI();

随机数rand()，值为0-1.0的浮点数

select rand();

还有其它很多三角函数，使用时可以查询文档

日期时间函数

获取子值，语法如下
- year(date)返回date的年份(范围在1000到9999)
- month(date)返回date中的月份数值
- day(date)返回date中的日期数值
- hour(time)返回time的小时数(范围是0到23)
- minute(time)返回time的分钟数(范围是0到59)
- second(time)返回time的秒数(范围是0到59)

select year('2016-12-21');

日期计算，使用+-运算符，数字后面的关键字为year、month、day、hour、minute、second

select '2016-12-21'+interval 1 day;

日期格式化date_format(date,format)，format参数可用的值如下
- 获取年%Y，返回4位的整数
  *　获取年%y，返回2位的整数
  *　获取月%m，值为1-12的整数
- 获取日%d，返回整数
  *　获取时%H，值为0-23的整数
  *　获取时%h，值为1-12的整数
  *　获取分%i，值为0-59的整数
  *　获取秒%s，值为0-59的整数

select date_format('2016-12-21','%Y %m %d');

当前日期current_date()

select current_date();

当前时间current_time()

select current_time();

当前日期时间now()

select now();

视图

对于复杂的查询，在多次使用后，维护是一件非常麻烦的事情
解决：定义视图
视图本质就是对查询的一个封装
定义视图

create view stuscore as 
select students.*,scores.score from scores
inner join students on scores.stuid=students.id;

视图的用途就是查询

select * from stuscore;

事务

当一个业务逻辑需要多个sql完成时，如果其中某条sql语句出错，则希望整个操作都退回
使用事务可以完成退回的功能，保证业务逻辑的正确性
事务四大特性(简称ACID)
- 原子性(Atomicity)：事务中的全部操作在数据库中是不可分割的，要么全部完成，要么均不执行
- 一致性(Consistency)：几个并行执行的事务，其执行结果必须与按某一顺序串行执行的结果相一致
- 隔离性(Isolation)：事务的执行不受其他事务的干扰，事务执行的中间结果对其他事务必须是透明的
- 持久性(Durability)：对于任意已提交事务，系统必须保证该事务对数据库的改变不被丢失，即使数据库出现故障
要求：表的类型必须是innodb或bdb类型，才可以对此表使用事务
查看表的创建语句

show create table students;

修改表的类型

alter table '表名' engine=innodb;

事务语句

开启begin;
提交commit;
回滚rollback;

示例1

步骤1：打开两个终端，连接mysql，使用同一个数据库，操作同一张表

终端1：
select * from students;
------------------------
终端2：
begin;
insert into students(sname) values('张飞');

步骤2

终端1：
select * from students;

步骤3

终端2：
commit;
------------------------
终端1：
select * from students;

示例2

步骤1：打开两个终端，连接mysql，使用同一个数据库，操作同一张表

终端1：
select * from students;
------------------------
终端2：
begin;
insert into students(sname) values('张飞');

步骤2

终端1：
select * from students;

步骤3

终端2：
rollback;
------------------------
终端1：
select * from students;

与Python交互

安装引入模块

安装mysql模块 python2.x使用Mysqldb ;python3.x 使用pymysql,其使用用法均一致

sudo apt-get install python-mysql

在文件中引入模块

import Mysqldb

Connection对象

用于建立与数据库的连接
创建对象：调用connect()方法

conn=connect(参数列表)

参数host：连接的mysql主机，如果本机是'localhost'
参数port：连接的mysql主机的端口，默认是3306
参数db：数据库的名称
参数user：连接的用户名
参数password：连接的密码
参数charset：通信采用的编码方式，默认是'gb2312'，要求与数据库创建时指定的编码一致，否则中文会乱码

对象的方法

close()关闭连接
commit()事务，所以需要提交才会生效
rollback()事务，放弃之前的操作
cursor()返回Cursor对象，用于执行sql语句并获得结果

Cursor对象

执行sql语句
创建对象：调用Connection对象的cursor()方法

cursor1=conn.cursor()

对象的方法

close()关闭
execute(operation [, parameters ])执行语句，返回受影响的行数
fetchone()执行查询语句时，获取查询结果集的第一个行数据，返回一个元组
next()执行查询语句时，获取当前行的下一行
fetchall()执行查询时，获取结果集的所有行，一行构成一个元组，再将这些元组装入一个元组返回
scroll(value[,mode])将行指针移动到某个位置
- mode表示移动的方式
- mode的默认值为relative，表示基于当前行移动到value，value为正则向下移动，value为负则向上移动
- mode的值为absolute，表示基于第一条数据的位置，第一条数据的位置为0

对象的属性

rowcount只读属性，表示最近一次execute()执行后受影响的行数
connection获得当前连接对象

增加

创建testInsert.py文件，向学生表中插入一条数据

#encoding=utf-8
import MySQLdb
try:
    conn=MySQLdb.connect(host='localhost',port=3306,db='test1',user='root',passwd='mysql',charset='utf8')
    cs1=conn.cursor()
    count=cs1.execute("insert into students(sname) values('张良')")
    print count
    conn.commit()
    cs1.close()
    conn.close()
except Exception,e:
    print e.message

修改

创建testUpdate.py文件，修改学生表的一条数据

#encoding=utf-8
import MySQLdb
try:
    conn=MySQLdb.connect(host='localhost',port=3306,db='test1',user='root',passwd='mysql',charset='utf8')
    cs1=conn.cursor()
    count=cs1.execute("update students set sname='刘邦' where id=6")
    print count
    conn.commit()
    cs1.close()
    conn.close()
except Exception,e:
    print e.message

删除

创建testDelete.py文件，删除学生表的一条数据

#encoding=utf-8
import MySQLdb
try:
    conn=MySQLdb.connect(host='localhost',port=3306,db='test1',user='root',passwd='mysql',charset='utf8')
    cs1=conn.cursor()
    count=cs1.execute("delete from students where id=6")
    print count
    conn.commit()
    cs1.close()
    conn.close()
except Exception,e:
    print e.message

sql语句参数化

创建testInsertParam.py文件，向学生表中插入一条数据

#encoding=utf-8
import MySQLdb
try:
    conn=MySQLdb.connect(host='localhost',port=3306,db='test1',user='root',passwd='mysql',charset='utf8')
    cs1=conn.cursor()
    sname=raw_input("请输入学生姓名：")
    params=[sname]
    count=cs1.execute('insert into students(sname) values(%s)',params)
    print count
    conn.commit()
    cs1.close()
    conn.close()
except Exception,e:
    print e.message

其它语句

cursor对象的execute()方法，也可以用于执行create table等语句
建议在开发之初，就创建好数据库表结构，不要在这里执行

查询一行数据

创建testSelectOne.py文件，查询一条学生信息

#encoding=utf8
import MySQLdb
try:
    conn=MySQLdb.connect(host='localhost',port=3306,db='test1',user='root',passwd='mysql',charset='utf8')
    cur=conn.cursor()
    cur.execute('select * from students where id=7')
    result=cur.fetchone()
    print result
    cur.close()
    conn.close()
except Exception,e:
    print e.message

查询多行数据

创建testSelectMany.py文件，查询一条学生信息

#encoding=utf8
import MySQLdb
try:
    conn=MySQLdb.connect(host='localhost',port=3306,db='test1',user='root',passwd='mysql',charset='utf8')
    cur=conn.cursor()
    cur.execute('select * from students')
    result=cur.fetchall()
    print result
    cur.close()
    conn.close()
except Exception,e:
    print e.message

封装

观察前面的文件发现，除了sql语句及参数不同，其它语句都是一样的
创建MysqlHelper.py文件，定义类

#encoding=utf8
import MySQLdb

class MysqlHelper():
    def __init__(self,host,port,db,user,passwd,charset='utf8'):
        self.host=host
        self.port=port
        self.db=db
        self.user=user
        self.passwd=passwd
        self.charset=charset

    def connect(self):
        self.conn=MySQLdb.connect(host=self.host,port=self.port,db=self.db,user=self.user,passwd=self.passwd,charset=self.charset)
        self.cursor=self.conn.cursor()

    def close(self):
        self.cursor.close()
        self.conn.close()

    def get_one(self,sql,params=()):
        result=None
        try:
            self.connect()
            self.cursor.execute(sql, params)
            result = self.cursor.fetchone()
            self.close()
        except Exception, e:
            print e.message
        return result

    def get_all(self,sql,params=()):
        list=()
        try:
            self.connect()
            self.cursor.execute(sql,params)
            list=self.cursor.fetchall()
            self.close()
        except Exception,e:
            print e.message
        return list

    def insert(self,sql,params=()):
        return self.__edit(sql,params)

    def update(self, sql, params=()):
        return self.__edit(sql, params)

    def delete(self, sql, params=()):
        return self.__edit(sql, params)

    def __edit(self,sql,params):
        count=0
        try:
            self.connect()
            count=self.cursor.execute(sql,params)
            self.conn.commit()
            self.close()
        except Exception,e:
            print e.message
        return count

添加

创建testInsertWrap.py文件，使用封装好的帮助类完成插入操作

#encoding=utf8
from MysqlHelper import *

sql='insert into students(sname,gender) values(%s,%s)'
sname=raw_input("请输入用户名：")
gender=raw_input("请输入性别，1为男，0为女")
params=[sname,bool(gender)]

mysqlHelper=MysqlHelper('localhost',3306,'test1','root','mysql')
count=mysqlHelper.insert(sql,params)
if count==1:
    print 'ok'
else:
    print 'error'

查询一个

创建testGetOneWrap.py文件，使用封装好的帮助类完成查询最新一行数据操作

#encoding=utf8
from MysqlHelper import *

sql='select sname,gender from students order by id desc'

helper=MysqlHelper('localhost',3306,'test1','root','mysql')
one=helper.get_one(sql)
print one

实例：用户登录

创建用户表userinfos

表结构如下
- id
- uname
- upwd
- isdelete
注意：需要对密码进行加密
如果使用md5加密，则密码包含32个字符
如果使用sha1加密，则密码包含40个字符，推荐使用这种方式

create table userinfos(
id int primary key auto_increment,
uname varchar(20),
upwd char(40),
isdelete bit default 0
);

加入测试数据

插入如下数据，用户名为123,密码为123,这是sha1加密后的值

insert into userinfos values(0,'123','40bd001563085fc35165329ea1ff5c5ecbdbbeef',0);

接收输入并验证

创建testLogin.py文件，引入hashlib模块、MysqlHelper模块
接收输入
根据用户名查询，如果未查到则提示用户名不存在
如果查到则匹配密码是否相等，如果相等则提示登录成功
如果不相等则提示密码错误

#encoding=utf-8
from MysqlHelper import MysqlHelper
from hashlib import sha1

sname=raw_input("请输入用户名：")
spwd=raw_input("请输入密码:")

s1=sha1()
s1.update(spwd)
spwdSha1=s1.hexdigest()

sql="select upwd from userinfos where uname=%s"
params=[sname]

sqlhelper=MysqlHelper('localhost',3306,'test1','root','mysql')
userinfo=sqlhelper.get_one(sql,params)
if userinfo==None:
    print '用户名错误'
elif userinfo[0]==spwdSha1:
    print '登录成功'
else:
    print '密码错误'

菜鸟入行

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
Python与Mysql

安装安装sudo apt-get install mysql-server mysql-client然后按照提示输入管理服务启动service mysql start停止service mysql stop重启service mysql restart允...
复制链接

扫一扫