Python之操作MySQL数据库

一.MySQL数据库启动与登陆

　　安装这里就不细说了。安装完成之后记得将安装路径配置进环境变量。然后进入到命令行下启动数据库。注：MySQL数据库安装完成后默认的是开启状态。

• 开启MySQL数据库，注：此处输入的服务名是安装时候设置的服务名称，MySQL5.7的版本默认的服务名称是MySQL57
  

•  关闭MySQL数据库服务

• 登陆MySQL数据库

 

二.建表

• 显示数据库

show databases;

•  创建数据库

create database lianxi DEFAULT character set utf8

•  使用数据库

use lianxi;

•  多表之一对多

create table user_info(
id int not null auto_increment primary key,
name char(20),
age int,
gender char(1),
deparment_id int,
constraint 约束名称 foregin key(deparment_id) references dep_info(id)
)engine = innodb default charset=utf8;

create table dep_info(
id int not null auto_increment primary key,
title char(32),
)engine=innode default charset=utf8;

•  多表之多对多（关系表）

create table boy(
id int not null auto_increment primary key,
name char(32)
)engine = innodb default charset=utf8;

create table girl(
id int not null auto_increment primary key,
name char(32)
)engine = innodb default charset=utf8;

create table b2g(
id int not null auto_increment primary key,
b_id int,
g_id int,
constraint 约束名称1 foregin key(b_id) references boy(id),
constraint 约束名称2 foregin key(g_id) references girl(id)
)engine = innodb default charset = utf8;

 应用场景：
1、根据具体业务
2、HTML的场景
创建的时候只需要输入数据的情况下：单表
创建的时候必须要我们选择一个其他数据的场景下：一对多
创建的时候针对某些字段可以进行多选的情况下：多对多

• 删除数据库

drop database db_name;

三.增删改查

• 插入：

insert into table_name(field) values(value),(value2) ;--> 两个(value)表示插如多行数据
insert into table_name(cname) select field from table_name; --> 把select查到的结果,当作数据来赋值给value

•  清空表

delete from table_name; --> 自增列会继续之前的ID
truncate table table_name； --> 物理删除，速度快，重新计算ID

•  删除某一条

delete from table_name where filed = values and/or ...  --> 只删除符合条件的数据
delete from table_name where filed in (1,2,3,4)；  
delete from table_name where id between 5 and 10； 

•  修改

update table_name set field = 'value'； --> 更新所有数据的field字段的值，加 where 只修改匹配到的行
update table_name set id = 8 , name = 'daxin' where age = 18;

•  查询

select * from table_name where id > 2；
select field as '别名' from table_name --> 加别名
select * from table_name where id in (1,2)；
select * from table_name where cid in (select tid from teacher)；

•  条件查询

select * from table_name order by field asc/desc(正序/倒序)
select * from table_name order by field asc limit 1 取第一个值
select * from table_name limit 1,2(起始位置,找几个)
select * from table_name where field like '%key%' --> 查找field字段包含key的数据
% 表示任意个任意字符， _表示任意一个字符

•  分组

select * from table_name group by field --> 分组显示,会去重,需要使用聚合函数来统计重复的次数
select field,count(id) from table_name group by field --> 对id字段进行聚合(其他的还有min(),max(),sum(),avg()等)

•  例子

1、获取每个班级多少人

SELECT class.caption,count(sid) from class
LEFT join student on student.class_id = class.cid group by class.caption;

 2、获取每个班级有多少人并且选出认识大于2的班级

注意：如果针对 group by 的结果进行筛选,那么需要使用 having 不能在使用 where 了.

SELECT class.caption,count(sid) as number from class
LEFT join student on student.class_id = class.cid
group by class.caption
HAVING number >= 2;

 3、每个课程的不及格的人数

select course.cname,count(sid) from score
left join course on score.corse_id = course.cid
where number < 60
group by course.cname;

四.连表

select student.sid,student.sname,class.caption from student LEFT JOIN class on student.class_id = class.cid ;
把class表中的字段放在student表的左边,并且进行 student.class_id = class.cid 匹配后显示，数据量以from指定的表为基准
left join:
以 from 指定的表为基准,对数据进行显示
right join: -->不常用
以 join 后面的表为基准进行显示。
inner join:(join 使用的就是)
只保留连个表中都有数据的条目
例子：
1、id=1的老师任教的课程名称
2、老师姓名瞎猫任教的课程命令
3、已选课程id=1,所有学生的姓名
4、已选体育课，所有学生的姓名
5、已选波多任教任意课程,所有学生姓名

union:
把两个SQL的结果进行组合(上下合并)
select * from student
union / union all
select * from teacher;
注意上下两个表中的列数要统一
注意：
1、如果所有数据都一致，那么union会对结果进行去重
2、union all ,会保存所有的

五.基本数据类型

MySQL的数据类型大致分为：数值、时间 和 字符串。
数字：
整数
tinyint 小整数,数据类型用于保存一些范围的整数数值范围。
smallint
int
bigint
小数
float 浮点型(长度越长越不精准)
double 浮点型(双精度,精度比float稍高) 范围比float更大
decimal 精准(内部使用字符串进行存储的) -> 适合对精度有要求的
字符串
char(19)[字符长度] 定长字符串 --> 占用空间大，但是效率高
varchar(19)[字符长度] 不定长字符串 --> 占用空间是可变的，但是效率低
注意：最大可以存放255个字符
text() 65535个字符
mediumtext() 16777215个字符
longtext() 4294967254个字符
二进制：
TinyBlob
Blob
MediumBlob
LongBlob
存文件:虽然可以用二进制进行存储,但是一般是存储文件在服务器上的路径(URL)
时间：
date YYYY-MM-DD
time HH:MM:SS
year YYYY
DATETIME YYYY-MM-DD HH:MM:SS -->常用
TIMESTAMP 时间戳格式

六.Python操作MySQL 

python 3.x 中使用 pymysql
python 2.x 中使用 mysqldb

pymysql安装

pip3 install pymysql

 使用操作

1.python操作mysql

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
  
# 创建连接
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='db1')
# 创建游标
cursor = conn.cursor()
  
# 执行SQL，并返回收影响行数
effect_row = cursor.execute("update hosts set host = '1.1.1.2'")
  
# 执行SQL，并返回受影响行数
#effect_row = cursor.execute("update hosts set host = '1.1.1.2' where nid > %s", (1,))
  
# 执行SQL，并返回受影响行数
#effect_row = cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])
  
  
# 提交，不然无法保存新建或者修改的数据
conn.commit()
  
# 关闭游标
cursor.close()
# 关闭连接
conn.close()

 2.获取新增数据自增ID

#!/usr/bin/python
# -*- coding:utf-8 -*-

import pymysql

# 获取数据
conn = pymysql.Connect(host='192.168.12.89',port=3306,user='root',password="123",database="s17day11db",charset='utf8')
cursor = conn.cursor()

cursor.execute('insert into class(caption) values(%s)',['新班级'])
conn.commit()
new_class_id = cursor.lastrowid # 获取新增数据自增ID

cursor.execute('insert into student(sname,gender,class_id) values(%s,%s,%s)',['李杰','男',new_class_id])
conn.commit()

cursor.close()
conn.close()

 3.获取查询数据

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
  
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
cursor = conn.cursor()
cursor.execute("select * from hosts")
  
# 获取第一行数据
row_1 = cursor.fetchone()
  
# 获取前n行数据
# row_2 = cursor.fetchmany(3)
# 获取所有数据
# row_3 = cursor.fetchall()
  
conn.commit()
cursor.close()
conn.close()

 4.用户登陆，sql拼接容易被sql注入

#!/usr/bin/python
# -*- coding:utf-8 -*-
import pymysql

user = input('请输入用户名:')
pwd = input('请输入密码:')

# 获取数据
conn = pymysql.Connect(host='192.168.12.89',port=3306,user='root',password="123",database="s17day11db",charset='utf8')
cursor = conn.cursor()
sql = 'select * from userinfo where username="%s" and password="%s" ' %(user,pwd,)
# user = alex" --
# pwd= asdf
'select * from userinfo where username="alex" -- " and password="sdfsdf"'
# user = asdfasdf" or 1=1  --
# pwd= asdf
'select * from userinfo where username="asdfasdf" or 1=1  -- " and password="asdfasdf"'
v = cursor.execute(sql)
result = cursor.fetchone()
cursor.close()
conn.close()

 5.新用户登录

#!/usr/bin/python
# -*- coding:utf-8 -*-
import pymysql

user = input('请输入用户名:')
pwd = input('请输入密码:')

# 获取数据
conn = pymysql.Connect(host='192.168.12.89',port=3306,user='root',password="123",database="s17day11db",charset='utf8')
cursor = conn.cursor()

v = cursor.execute('select * from userinfo where username=%s and password=%s',[user,pwd])
result = cursor.fetchone()
cursor.close()
conn.close()

print(result)