Python学习笔记之 mysql
系统说明：windows
一、基本命令
1、启动服务：
说明：以管理员身份运行cmd
格式：net start 服务名称
示例：net start mysql57
2、停止服务
说明：以管理员身份运行cmd
格式：net stop 服务名称
示例：net stop mysql57
3、连接数据库
格式：mysql -u 用户名 -p
示例：mysql -u root -p
输入密码(安装时设置的)
4、退出登录(断开连接)
quit或exit或\q
5、查看版本(连接后可以执行)
示例：select version();
6、显示当前时间(连接后可以执行)
示例：select now();
7、远程连接
格式：mysql -h IP地址 -u 用户名 -P port -p
输入对方mysql密码
二、数据库操作
1、创建数据库
格式：create database 数据库名 charset=utf8;
示例：create database test1 charset=utf8;
2、删除数据库
格式：drop database 数据库名;
示例：drop database test1;
3、切换数据库
格式：use 数据库名
示例：use test;
4、查看当前使用的数据库
select database();
三、表操作
1、查看当前数据库中所有表
show tables;
2、创建表
格式：create table 表名(列及类型);
说明：
auto_increment表示自增长
primary key表示主键
not null表示不为空
示例：create table student(id int auto_increment primary key, name varchar(20) not null,age int not null, gender bit default 1, address varchar(20),isDelete bit default 0);
3、删除表
格式：drop table 表名;
示例：drop table student;
4、查看表结构
格式：desc 表名;
示例：desc student;
5、查看建表语句
格式：show create table 表名;
示例：show create table student;
6、重命名表名
格式：rename table 原表名 to 新表名;
示例：rename table car to newCar;
7、修改表
格式：alter table 表名 add|change|drop 列名 类型;
示例：alter table newCar add isDelete bit default 0;
四、数据操作
1、增
a、全列插入：
格式：insert into 表名 values(……);
说明：主键列是自动增长，但是在全列插入时需要占位，通常使用0，插入成功后以实际数据为准
示例：insert into student values(0,"tom",19,1,"北京",0);
b、缺省插入：
格式：insert into 表名(列1,列2,...) values(值1,值2,...);
示例：insert into student(name,age,address) values("lilei",19,"上海");
c、同时插入多条数据：
格式：insert into 表名 values(...),(...),(...),...;
示例：insert into student values(0,"hanmeimei",19,0,"北京",0),(0,"juen",20,1,"深圳",0),(0,"大神",22,1,"月球",0);
2、删
格式：delete from 表名 where 条件;
示例：delete from student where id=4;
注意：没有条件是全部删除，慎用
3、改
格式：update 表名 set 列1=值1,列2=值2,…… where 条件;
示例：update student set age=16 where id=1;
注意：没有条件是全部列都修改，慎用
4、查
说明：查询表里的全部数据
格式：select * from 表名;
示例：select * from student;
五、查
1、基本语法
格式：select * from 表名;
说明：
a、from关键字后面是表名，表示数据来源于这张表
b、select后面写表中的列名，如果是*表示在结果集中显示表中的所有列
c、在select后面的列名部分，可以使用as为列名起别名，这个别名显示在结果集中
d、如果要查询多个列，之间使用逗号分隔
示例：
select * from student;
select name, age from student;
select name as 姓名, age from student;
2、消除重复行
在select后面列前面使用distinct可以消除重复的行
示例：
select gender from student;
select distinct gender from student;
3、条件查询
a、语法
select * from 表名 where 条件
b、比较运算符
等于 =
大于 >
小于 <
大于等于 >=
小于等于 <=
不等于 !=或<>
需求：查询id值大于2的所有值
示例：select * from student where id>2;
c、逻辑运算符
and 并且
or 或者
not 非
需求：查询id值大于大于3的居住月球的
select * from student where id>3 and address="月球";
d、模糊查询
like
% 表示任意多个任意字符
_ 表示一个任意字符
需求：查询姓习的同学
先插入一条数据方便看效果
insert into student values(0,"习大",60,0,"北京",0),(0,"习大大",60,0,"北京",0);
示例：
select * from student where name like "习%";
select * from student where name like "习_";
e、范围查询
in 表示在一个非连续的范围内
between...and... 表示在一个连续的范围内
需求：查询编号为3、 5、 7的学生
示例：select * from student where id in (3,5,7);
需求：查询编号为6到7的学生
示例：select * from student where id between 6 and 8;
f、空判断
insert into student(name,age) values("特朗普",19);
注意：null与""是不同
判断空：is null
判断非空：is not null
需求：查询没有地址的同学
示例：select * from student where address is null;
需求：查询有地址的同学
示例：select * from student where address is not null;
g、优先级
小括号,not 比较运算符， 逻辑运算符
and比or优先级高，如果同时出现并希望先选or，需要结合()来使用
4、聚合
为了快速得到统计数据，提供了5个聚合函数
a、count(*) 表示计算总行数，括号中可以写*和列名
b、max(列) 表示求此列的最大值
c、min(列) 表示求此列的最小值
d、sun(列) 表示求此列的和
e、avg(列) 表示求此列的平均值
需求：查询学生的总数
示例：select count(*) from student;
需求：查询女生的编号最大值
示例：select max(id) from student where gender=0;
需求：查询女生的编号最小值
示例：select min(id) from student where gender=0;
需求：查询所有学生的年龄和
示例：select sum(age) from student;
需求：查询所有学生年龄平均值
示例：select avg(age) from student;
5、分组
按照字段分组，表示此字段相同的数据会被放到一集合中。分组后，只能查询出相同的数据列，
对于有差异的数据列无法显示在结果中，可以对分组后的数据进行统计，做聚合运算
语法：select 列1,列2,聚合…… from 表名 group by 列1,列2,列3,……;
需求：查询男女生总数
示例；
select gender,count(*) from student group by gender;
select name,gender,count(*) from student group by gender,age;
分组后的数据筛选：select 列1,列2,聚合…… from 表名 group by 列1,列2,列3,…… having 列1,……聚合……;
示例：select gender,count(*) from student group by gender having gender;
where与having的区别：
where是对from后面指定的表进行筛选，属于对原始数据的筛选
having是对group by的结果进行筛选
6、排序
语法：select * from 表名 order by 列1 asc|desc, 列2 asc|desc, ……；
说明：
a、将数据按照列1进行排序，如果莫些列1的值相同，则按照列2进行排序
b、默认按照从小到大的顺序排序
c、asc升序
d、desc降序
需求：将没有被删除的数据按照年龄排序
示例：
select * from student where isDelete=0 order by age desc;
select * from student where isDelete=0 order by age,id desc;
7、分页
语法：select * from 表名 limit start,count
说明：start索引从0开始
示例：
select * from student limit 0,3;
select * from student limit 3,3;
select * from student where gender=1 limit 0,3;
六、关联
建表语句：
1、create table class(id int auto_increment primary key, name varchar(20) not null, stuNum int not null);
2、create table students(id int auto_increment primary key,name varchar(20) not null, gender bit default 1, classid int not null, foreign key(classid) references class(id));
插入一些数据：
insert into class values(0,"python01", 55),(0,"python02", 50),(0,"python03", 60),(0,"python04", 70);
insert into students values(0,"tom",1,1);
insert into students values(0,"lilei",1,10);
insert into students values(0,"lilei",1,2);
select * from students;
关联查询：
select students.name,class.name from class inner join students on class.id=students.classid;
select students.name,class.name from class left join students on class.id=students.classid;
select students.name,class.name from class right join students on class.id=students.classid;
分类：
1、表A inner join 表B
表A与表B匹配的行会出现在结果集中
2、表A left join 表B
表A与表B匹配的行会出现在结果集中，外加表A中独有的数据，未对应的数据使用null填充
3、表A right join 表B
表A与表B匹配的行会出现在结果集中，外加表B中独有的数据，未对应的数据使用null填充
七、Python对数据库的操作：
需要安装pip install PyMysql
- 连接数据库
import pymysql
# 连接数据库
# 参数1：mysql服务所在主机的IP
# 参数2：用户名
# 参数3：密码
# 参数4：要连接的数据库名
db = pymysql.connect("192.168.3.3", "root", "123456","test")
# 创建一个cursor对象
cursor = db.cursor()
sql = "select version()"
# 执行sql语句
cursor.execute(sql)
# 获取返回的信息
data = cursor.fetchone()
print(data)
# 断开
cursor.close()
db.close()
- 创建数据库表
import pymysql
db = pymysql.connect("10.0.142.171", "root", "sunck", "kaige")
cursor = db.cursor()
# 检查表是否存在，如果存则删除
cursor.execute("drop table if exists bandcard")
#建表
sql = 'create table bandcard(id int auto_increment primary key, money int not null)'
cursor.execute(sql)
cursor.close()
db.close()
- 数据库插入数据
import pymysql
db = pymysql.connect("10.0.142.171", "root", "sunck", "kaige")
cursor = db.cursor()
sql = 'insert into bandcard values(0, 300),(0, 400),(0, 500),(0, 600),(0, 700)'
try:
cursor.execute(sql)
db.commit()
except:
# 如果提交失败，回滚到上一次数据
db.rollback()
cursor.close()
db.close()
- 数据库更新数据
import pymysql
db = pymysql.connect("10.0.142.171", "root", "sunck", "kaige")
cursor = db.cursor()
sql = 'update bandcard set money=1000 where id=1'
try:
cursor.execute(sql)
db.commit()
except:
# 如果提交失败，回滚到上一次数据
db.rollback()
cursor.close()
db.close()
- 数据库更新操作
import pymysql
db = pymysql.connect("10.0.142.171", "root", "sunck", "kaige")
cursor = db.cursor()
sql = 'delete from bandcard where money = 200'
try:
cursor.execute(sql)
db.commit()
except:
# 如果提交失败，回滚到上一次数据
db.rollback()
cursor.close()
db.close()
- 数据库查询操作
'''
fetchone()
功能：获取下一个查询结果集，结果集是一个对象
fetchall()
功能：接收全部的返回的行
rowcount:是一个只读属性，返回execute()方法影响的行数
'''
import pymysql
db = pymysql.connect("10.0.142.171", "root", "sunck", "kaige")
cursor = db.cursor()
sql = 'select * from bandcard where money>400'
try:
cursor.execute(sql)
reslist = cursor.fetchall()
for row in reslist:
print("%d--%d" % (row[0], row[1]))
except:
# 如果提交失败，回滚到上一次数据
db.rollback()
cursor.close()
db.close()
- 对数据库的操作进行封装，以后直接调用即可
import pymysql
class SunckSql():
def __init__(self,host, user, passwd, dbName):
self.host = host
self.user = user
self.passwd = passwd
self.dbName = dbName
def connet(self):
self.db = pymysql.connect(self.host,self.user,self.passwd,self.dbName)
self.cursor = self.db.cursor()
def close(self):
self.cursor.close()
self.db.close()
def get_one(self, sql):
res = None
try:
self.connet()
self.cursor.execute(sql)
res = self.cursor.fetchone()
self.close()
except:
print("查询失败")
return res
def get_all(self, sql):
res = ()
try:
self.connet()
self.cursor.execute(sql)
res = self.cursor.fetchall()
self.close()
except:
print("查询失败")
return res
def insert(self, sql):
return self.__edit(sql)
def update(self, sql):
return self.__edit(sql)
def delete(self, sql):
return self.__edit(sql)
def __edit(self,sql):
count = 0
try:
self.connet()
count = self.cursor.execute(sql)
self.db.commit()
self.close()
except:
print("事物提交失败")
self.db.rollback()
return count
封装后的使用方法
from sunckSql import SunckSql
s = SunckSql("10.0.142.171", "root", "sunck", "kaige")
res = s.get_all("select * from bandcard where money>400")
for row in res:
print("%d--%d" % (row[0], row[1]))
