【Python】进阶之 MySQL入门教程

数据库概述

• 数据库的由来

  发展历程	说明
  人工管理阶段	用纸带等进行数据的存储
  文件系统阶段	数据存储在文件中
  数据库阶段	解决了文件系统问题
  高级数据库阶段	分布式数据库

• 数据库的分类

  数据库分类	说明	常用库
  关系型数据库	采用了关系模型来组织数据的数据库：关系模型指的就星二维表格模型	Oracle Microsoft SQL Server MySQL SQLite
  非关系型数据库	Not Only SQL 强调Key-Value的方式存储数据	Mongodb redis

• 关系型数据库的使用场景
  Web网站系统、日志记录系统、数据仓库系统、嵌入式系统

Mysql概述

MySQL是一个$关系型数据库管理系统$软件。

• 关系型数据库管理系统-RDBMS
  MySQL是一个关系型数据库管理系统软件，在WEB应用方面，MySQL是最好的RDBMS(Relational Database Management System, 关系数据库管理系统）应用软件，它是由瑞典MySQL AB司开发，目前属于Oracle旗下产品，MySQL是最流行的关系型数据库管理系统中的一个。

• 关系型数据库管理系统可以分为：

  1. 关系型数据库服务端软件-Server
  2. 关系型数据库客户端软件-Client

• 关系型数据库管理系统的流程图：
  

• 关系型数据库管理系统的通信流程效果图：
  

• SQL介绍
  SQL(Structured Query Language)是$结构化查询语言$，是一种用来操作$RDBMS$的数据库的语言，也就是说通过SQL可以操作oracle, sql server, mysql, sqlite等关系型的数据库。

• SQL语言的分类

  简写	语义	说明
  DQL	数据查询语句	select
  DML	数据操作语句	insert update delete
  DDL	数据定义语句	creat drop
  TPL	事务处理语言	begin transaction commit rollback
  DCL	数据控制语言	grant revoke

• MySQL的特点

  1. MySQL是开源的，所以你不需要支付额外的费用。
  2. M小ySQL支持大型的数据库。可以处理拥有上干万条记录的大型数据库。
  3. MySQL使用标准的SQL数据语言形式。
  4. MySQL可以安装在不同的操作系统，并且提供多种编程语言的操作接口。这些编程语言包括C、C++、Python、Java、Ruby等等。

• MySQL的数据类型

  数据类型	说明
  int, bit	整数
  decimal	小数
  varchar, char	字符串
  date, time, datetime	日期和时间
  enum	枚举类型

• MySQL的字段约束

  约束参数	说明
  primary key	主键约束
  not null	非空约束
  unique	唯一约束
  default	默认约束

Mysql安装与使用

• Mysql安装
  MySQL数据库$服务$端软件的安装：sudo apt-get install mysql-server
  MySQL数据库$客户$端软件的安装：sudo apt-get insatll mysql-client
• Mysql数据库服务端启动
  查看MySQL服务状态：sudo service mysql status
  停止MySQL服务：sudo service mysql stop
  启动MySQL服务：sudo service mysql start
  重启MySQL服务：sudo service mysql restart
• Mysql的配置文件
  1. mqsql配置文件的路径：/etc/mysql/mysql.conf.d/
     
  2. 配置项介绍
     <1> port表示端口，默认为3306；
     <2> bind-address表示服务器绑定的ip，默认为127.0.0.1；
     <3> datadir表示数据库保存路径，默认为/var/lib/mysql;
     <4> log_error表示错误日志，默认为/var/log/mysql/error.log;

Navicat安装和使用

• Navicat介绍
  Navicat是一款图形化界面的数据库客户端软件。
• Navicat的安装——基于Linux操作系统
  1. 官网下载安装包：https://www.navicat.com.cn
  2. 解压navicat安装包：tar -zxvf navicat112_mysql_cs_x64.tar.gz
  3. cd到navicat安装包文件夹目录：cd navicate112_mysql_cs_x64
  4. 运行navicat：./start_navicat

  安装包及破解工具下载： 百度网盘 【提取码：4KaE】
  破解教程：Navicat Premium15永久破解版安装教程

• Navicat的连接mysql服务
  

Mysql终端指令操作

• Mysql登录登出客户端
  连接mysql服务端指令：mysql -uroot -p
  显示当前时间：select now();
  退出连接：exit/quit/[control+d]
  
• Mysql数据库操作
  查看所有数据库：show databases;
  创建数据库：create database 数据库名 charset=utf-8;
  使用数据库：use 数据库名;
  查看当前使用的数据库：select database();
  删除数据库：drop database 数据库名;
  
• Mysql表操作
  查看所有当前库中的所有表：show tables;
  创建表：create table 表名(字段名称 数据类型 可选的约束条件, column1 datatype contral, ...);
  修改表字段类型：alter table 表名 modify 列名 类型 约束;
  删除表：drop table 表名;
  查看表结构：desc 表名;
  
• Mysql -CRUD操作
  1. 查询数据
     查询所有列：select * from 表名;
     查询指定列：select 列名1, 列名2,... from 表名;
     
  2. 增加数据
     全列插入 - 值的顺序必须和字段顺序完全一致：insert into 表名 values(...);
     部分列插入 - 值的顺序和给出的列的顺序对应：insert into 表名(列1, 列2,...) values(值1, 值2,...)
     全列多行插入：insert into 表名 values(...), (...), (...);
     部分列多行插入：insert into 表名(列1, 列2,...) values(值1,...), (值1,...), (值1,...)
     
  3. 修改数据：update 表名 set 列1=值1, 列2=值2,... where 条件;
     
  4. 删除数据：delete from 表名 where 条件;
     
• Mysql数据库备份和恢复

  1. 备份导出：mysqldump -u用户名 -p密码 数据库名 表名 > data.sql （注意：不写表名默认导出所有数据表）
     

  2. 恢复导入：

     cd 到数据文件路径下
     mysql -u用户名 -p密码
     use 数据库
     source data.sql
     

     

Mysql和python交互

• pymysql的安装
  安装pymysql：sudo pip3 install pymysql
  查看安装情况：pip show pymysql / pip3 list
  卸载pymysql：sudo pip3 uninstall pymysql
  

• pymysql的使用

  1. 导包：import pymysql
  2. 创建和mysql服务端的连接对象：conn = pymysql.connect(参数列表)
  3. 获取游标对象：cursor = conn.cursor()
  4. 执行sql语句：row_count = cursor.execute(sql)
  5. 获取查询结果集：result = cursor.fetchall()
  6. 将增加和修改操作提交到数据库：conn.commit()
  7. 回滚数据：conn.rollback()
  8. 关闭游标对象：cursor.close()
  9. 关闭连接：conn.close()

• pymysql查询数据

  # 查询数据库goods下student表中的所有数据
  # 1. 导包
  import pymysqltry:# 2. 连接mysql数据库的服务conn = pymysql.Connect(# mysql服务端的IP，默认127.0.0.1/localhost,也可填真实iphost='192.168.90.172',user='root',password='mysql',database='goods',port=3306,charset='utf8')# 3. 创建游标对象cur = conn.cursor()# 4. 编写sql语句sql = 'select * from students;'# 5. 使用游标对象去调用sqlcur.execute(sql)# 6. 获取查询的结果并打印result = cur.fetchall()print(result)# 7. 关闭游标对象cur.close()# 8. 关闭连接conn.close()
  except Exception as e:print(e)
  

  结果如下：
  

• pymysql增删改数据

  '''
  对数据库goods下的students表进行如下操作：
  1. 增加数据：李磊 35 男
  2. 修改数据：李磊的名字改为王磊
  3. 删除数据：王磊
  '''# 1. 导包
  import pymysql# 2. 连接mysql数据库的服务
  conn = pymysql.Connect(# mysql服务端的IP，默认127.0.0.1/localhost,也可填真实iphost='192.168.90.172',user='root',password='mysql',database='goods',port=3306,charset='utf8'
  )
  # 3. 创建游标对象
  cur = conn.cursor()
  try:# 4. 编写增加、修改、删除的sql语句add_sql = 'insert into students values(%s, %s, %s, %s);'add_data = [0, '李磊', 35, '男']update_sql = 'update students set name=%s where name = "李磊";'update_data = ['王磊']delete_sql = 'delete from students where name=%s;'delete_data = ['王磊']# 5. 使用游标对象执行sqlcur.execute(add_sql, add_data)cur.execute(update_sql, update_data)cur.execute(delete_sql, delete_data)# 6. 提交操作conn.commit()
  except Exception as e:print(e)# 数据回滚conn.rollback()
  finally:# 7. 关闭游标对象cur.close()# 8. 关闭连接conn.close()
  

订单管理案例实现

• 需求分析
  
  ① 创建数据库orders
  ② 创建orders订单表
  ③ 插入测试数据orders.sql
  ④ Python和Mysql交互
  ⑤ 查询订单数据
  ⑥ 增加订单数据
  ⑦ 删除订单数据
  ⑧ 修改订单数据

• 实现步骤

  1. 建库和建表

     --创建数据库
     create database orders charset=utf8;--创建数据表
     create table orders(id int not null auto_increment primary key,count int not null,price decimal(10,2) not null,freight decimal(10,2) not null,user varchar(50) not null,status enum('待支付','待发货','待收货') default '待支付' not null,time date not null);
     

  2. 插入测试数据
     ① cd素材的目录下
     ② 登录mysql客户端：mysql -uroot -p
     ③ 切换数据库： use orders
     ④ 导入测试数据：source orders.sql
     

  3. 实现订单查询功能
     ① 导包pymysql
     ② 连接mysql服务
     ③ 创建游标对象
     ④ 编写查询所有订单SQL语句
     ⑤ 执行SQL语句
     ⑥ 获取所有查询的结果
     ⑦ 将获取结果转换成列表字典格式
     ⑧ 对Mysql的操作加Try处理

     # 1 导包
     import pymysqldef order()# 2 链接mysql的服务端conn = pymysql.Connect(user='root',password='mysql',host='192.168.90.172',database='orders',port=3306,charset='utf8')# 3 创建游标对象cur = conn.cursor()try:# 4 编写 查询orders表的所有数据SQLsql = 'select * from orders;'# 5 使用游标对象执行SQLcur.execute(sql)# 6 获取查询的所有数据 fetchall() ==>元组result = cur.fetchall()print('查询数据：', result)# 打印结果如下：# 查询数据：((1, 2, Decimal('100.00'), Decimal('10.00'), '老王'， ‘待收货', datetime.data(2020, 1, 1)), (2, 3, Decimal('200.00'), Decimal('25.00'), '钱红'， ’待支付', datatime.date(2020, 4,1)))# 7 将数据转换成[{}, {}]data_lst = []for row in result:data_lst.append({'id': row[0],'count': row[1],'price': str(row[2]),'freight': str(row[3]),'user': row[4],'status': row[5],'time': str(row[6])})# 8 加个try优化下except Exception as e:print('报错信息：', e)finally:# 关闭游标对象cur.close()# 关闭连接conn.close()
     

  4. 实现订单增加功能
     ① 导包pymysql
     ② 连接mysql服务
     ③ 创建游标对象
     ④ 编写增加订单的SQL语句
     ⑤ 执行SQL语句并提交
     ⑥ 对Mysql的操作加Try处理

     # 1 导包
     import pymysqldef add(data):# 2 链接mysql的服务端conn = pymysql.Connect(user='root',password='mysql',host='192.168.90.172',database='orders',port=3306,charset='utf8')# 3 创建游标对象cur =conn.cursor()try:# 4 编写 增加数据的SQLsql = 'insert into orders values(%s,%s,%s,%s,%s,%s,%s)'# 5 使用游标对象执行SQL并提交cur.execute(sql, data)conn.commit()# 6 加个try优化下except Exception as e:print('报错信息：', e)# 回滚数据conn.rollback()finally:# 关闭游标对象cur.close()# 关闭连接conn.close()
     

  5. 实现封装功能函数
     查询和增加的功能重复度比较高，均有：导包、连接mysql、创建游标对象、执行SQL语句、Try语句。实现封装函数的操作：
     ① 定义封装函数的名字
     ② 粘贴重复度高的代码
     ③ 提交参数
     ④ 测试

     # 1 导包
     import pymysql# 封装一个执行CRUD的函数
     def execute_crud_sql(sql,data):# 2 链接mysql的服务端conn = pymysql.Connect(user='root',password='mysql',host='192.168.90.172',database='orders',port=3306,charset='utf8')# 3 创建游标对象cur = conn.cursor()try:# 4 编写 查询orders表的所有数据SQL# 5 使用游标对象执行SQLcur.execute(sql, data)# 6 提交操作conn.commit()# 8 加个try优化下except Exception as e:print('报错信息：', e)# 回滚数据conn.rollback()finally:# 关闭游标对象cur.close()# 关闭连接conn.close()def add(data):sql = 'insert into orders values(%s,%s,%s,%s,%s,%s,%s)'execute_crud_sql(sql, data)
     

  6. 实现订单修改和删除功能

     # 修改的SQL语句
     update orders set count=%s, price=%s, freight=%s, user=%s, status=%s, time=%s where id=%s;
     # 将参数中id的顺序从第一个颠倒到最后一个
     data.append(data.pop(0))# 删除的SQL语句
     delete from orders where id=%s;
     

     # 1 导包
     import pymysql# 封装一个执行CRUD的函数
     def execute_crud_sql(sql,data):# 2 链接mysql的服务端conn = pymysql.Connect(user='root',password='mysql',host='192.168.90.172',database='orders',port=3306,charset='utf8')# 3 创建游标对象cur = conn.cursor()try:# 4 编写 查询orders表的所有数据SQL# 5 使用游标对象执行SQLcur.execute(sql, data)# 6 提交操作conn.commit()# 8 加个try优化下except Exception as e:print('报错信息：', e)# 回滚数据conn.rollback()finally:# 关闭游标对象cur.close()# 关闭连接conn.close()# 修改订单数据
     def update(data):print('修改的数据：', data)   # ['7', '99', '9.90', '1.00', '明明'，'待收货', '2020-03-29']data.append(data.pop(0))# 1. 修改的SQL语句sql = 'update orders set count=%s, price=%s, freight=%s, user=%s, status=%s, time=%s where id=%s'# 2. 执行调用execute_crud_sql(sql, data)# 删除订单数据    
     def delete(data):# 1. 删除的SQL数据sql = 'delete from orders where id=%s;'# 2. 执行execute_crud_sql(sql, data)