day26.MySQL【Python教程】

day26.MySQL【Python教程】

MySQL数据库简介

  • mysql数据库,是当前应用非常广泛的一款关系型数据库
  • 查看官方网站http://www.mysql.com/
  • 查看数据库排名http://db-engines.com/en/ranking
  • 主要知识点包括:
    • 数据库与表的操作
    • 数据的增加、修改、删除
    • 数据的查询(重点)
    • 与python交互

一、创建库与表

1.1简介

  • 主要知识点包括:能够与mysql建立连接,创建数据库、表,分别从图形界面与脚本界面两个方面讲解
  • 相关的知识点包括:E-R关系模型,数据库的3范式,mysql中数据字段的类型,字段约束
  • 数据库的操作主要包括:
    • 数据库的操作,包括创建、删除
    • 表的操作,包括创建、修改、删除
    • 数据的操作,包括增加、修改、删除、查询,简称crud
  • 学生表结构:
    • id
    • 名称
    • 性别
    • 地址
    • 生日
  • 科目表结构:
    • id
    • 名称

1.2数据库简介

  • 人类在进化的过程中,创造了数字、文字、符号等来进行数据的记录,但是承受着认知能力和创造能力的提升,数据量越来越大,对于数据的记录和准确查找,成为了一个重大难题
  • 计算机诞生后,数据开始在计算机中存储并计算,并设计出了数据库系统
  • 数据库系统解决的问题:持久化存储,优化读写,保证数据的有效性
  • 当前使用的数据库,主要分为两类
    • 文档型,如sqlite,就是一个文件,通过对文件的复制完成数据库的复制
    • 服务型,如mysql、postgre,数据存储在一个物理文件中,但是需要使用终端以tcp/ip协议连接,进行数据库的读写操作

E-R模型

  • 当前物理的数据库都是按照E-R模型进行设计的
  • E表示entry,实体
  • R表示relationship,关系
  • 一个实体转换为数据库中的一个表
  • 关系描述两个实体之间的对应规则,包括
    • 一对一
    • 一对多
    • 多对多
  • 关系转换为数据库表中的一个列 *在关系型数据库中一行就是一个对象

三范式

  • 经过研究和对使用中问题的总结,对于设计数据库提出了一些规范,这些规范被称为范式
  • 第一范式(1NF):列不可拆分
  • 第二范式(2NF):唯一标识
  • 第三范式(3NF):引用主键
  • 说明:后一个范式,都是在前一个范式的基础上建立的

1.2安装

  • 安装

管理服务

  • 启动
  • 停止
  • 重启

允许远程连接

  • 找到mysql配置文件并修改
  • 登录mysql,运行命令
  • 重启mysql

1.3数据完整性

  • 一个数据库就是一个完整的业务单元,可以包含多张表,数据被存储在表中
  • 在表中为了更加准确的存储数据,保证数据的正确有效,可以在创建表的时候,为表添加一些强制性的验证,包括数据字段的类型、约束

字段类型

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

约束

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

1.4使用图形窗口连接

  • 下发windows的navicat
  • 点击“连接”弹出窗口,按照提示填写连接信息,如下图
  • 连接成功后,会在连接名称下面显示出当前的数据库
  • 双击选中数据库,就可以编辑此数据库
  • 下次再进入此软件时,通过双击完成连接、编辑操作

数据库操作

  • 在连接的名称上右击,选择“新建数据库”,弹出窗口,并按提示填写
  • 在数据库上右击,选择“删除数据库”可以完成删除操作

表操作

  • 当数据库显示为高亮时,表示当前操作此数据库,可以在数据中创建表
  • 一个实体对应一张表,用于存储特定结构的数据
  • 点击“新建表”,弹出窗口,按提示填写信息
  • 主键的名称一般为id,设置为int型,无符号数,自动增长,非空
  • 自动增长表示由mysql系统负责维护这个字段的值,不需要手动维护,所以不用关心这个字段的具体值
  • 字符串varchar类型需要设置长度,即最多包含多少个字符
  • 点击“添加栏位”,可以添加一个新的字段
  • 点击“保存”,为表定义名称

数据操作

  • 表创建成功后,可以在右侧看到,双击表打开新窗口,如下图
  • 在此窗口中可以增加、修改、删除数据

逻辑删除

  • 对于重要数据,并不希望物理删除,一旦删除,数据无法找回
  • 一般对于重要数据,会设置一个isDelete的列,类型为bit,表示逻辑删除
  • 大于大量增长的非重要数据,可以进行物理删除
  • 数据的重要性,要根据实际开发决定

1.5使用命令连接

  • 命令操作方式,在工作中使用的更多一些,所以要达到熟练的程度
  • 打开终端,运行命令
  • 连接成功后如下图
  • 退出登录
  • 退出成功后如下图
  • 登录成功后,输入如下命令查看效果
  • 注意:在语句结尾要使用分号;

远程连接

  • 一般在公司开发中,可能会将数据库统一搭建在一台服务器上,所有开发人员共用一个数据库,而不是在自己的电脑中配置一个数据库
  • 运行命令
  • -h后面写要连接的主机ip地址
  • -u后面写连接的用户名
  • -p回车后写密码

数据库操作

  • 创建数据库
  • 删除数据库
  • 切换数据库
  • 查看当前选择的数据库

表操作

  • 查看当前数据库中所有表
  • 创建表
  • auto_increment表示自动增长
  • 修改表
  • 删除表
  • 查看表结构
  • 更改表名称
  • 查看表的创建语句

数据操作

  • 查询
  • 增加
  • 主键列是自动增长,但是在全列插入时需要占位,通常使用0,插入成功后以实际数据为准
  • 修改
  • 删除
  • 逻辑删除,本质就是修改操作update

备份与恢复

数据备份

  • 进入超级管理员
  • 进入mysql库目录
  • 运行mysqldump命令

数据恢复

  • 连接mysqk,创建数据库
  • 退出连接,执行如下命令

1.6总结

  • 数据库解决的问题,E-R模型,三范式
  • 图形界面操作数据库、表、数据
  • 命令行操作数据库、表、数据

作业

  • 完善学生表、科目表及数据
  • 设计两张表,要求是一对多的关系

二、查询简介

  • 查询的基本语法
  • from关键字后面写表名,表示数据来源于是这张表
  • select后面写表中的列名,如果是*表示在结果中显示表中所有列
  • 在select后面的列名部分,可以使用as为列起别名,这个别名出现在结果集中
  • 如果要查询多个列,之间使用逗号分隔

消除重复行

  • 在select后面列前使用distinct可以消除重复的行

2.1条件

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

比较运算符

  • 等于=
  • 大于>
  • 大于等于>=
  • 小于<
  • 小于等于<=
  • 不等于!=或<>
  • 查询编号大于3的学生
  • 查询编号不大于4的科目
  • 查询姓名不是“黄蓉”的学生
  • 查询没被删除的学生

逻辑运算符

  • and
  • or
  • not
  • 查询编号大于3的女同学
  • 查询编号小于4或没被删除的学生

模糊查询

  • like
  • %表示任意多个任意字符
  • _表示一个任意字符
  • 查询姓黄的学生
  • 查询姓黄并且名字是一个字的学生
  • 查询姓黄或叫靖的学生

范围查询

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

空判断

  • 注意:null与''是不同的
  • 判空is null
  • 查询没有填写地址的学生
  • 判非空is not null
  • 查询填写了地址的学生
  • 查询填写了地址的女生

优先级

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

2.2聚合

  • 为了快速得到统计数据,提供了5个聚合函数
  • count(*)表示计算总行数,括号中写星与列名,结果是相同的
  • 查询学生总数
  • max(列)表示求此列的最大值
  • 查询女生的编号最大值
  • min(列)表示求此列的最小值
  • 查询未删除的学生最小编号
  • sum(列)表示求此列的和
  • 查询男生的编号之后
  • avg(列)表示求此列的平均值
  • 查询未删除女生的编号平均值

2.3分组

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

分组后的数据筛选

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

对比where与having

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

2.4排序

  • 为了方便查看数据,可以对数据进行排序
  • 语法:
  • 将行数据按照列1进行排序,如果某些行列1的值相同时,则按照列2排序,以此类推
  • 默认按照列值从小到大排列
  • asc从小到大排列,即升序
  • desc从大到小排序,即降序
  • 查询未删除男生学生信息,按学号降序
  • 查询未删除科目信息,按名称升序

2.5获取部分行

  • 当数据量过大时,在一页中查看数据是一件非常麻烦的事情
  • 语法
  • 从start开始,获取count条数据
  • start索引从0开始

示例:分页

  • 已知:每页显示m条数据,当前显示第n页
  • 求总页数:此段逻辑后面会在python中实现
    • 查询总条数p1
    • 使用p1除以m得到p2
    • 如果整除则p2为总数页
    • 如果不整除则p2+1为总页数
  • 求第n页的数据

2.6总结

  • 完整的select语句
  • 执行顺序为:
    • from 表名
    • where ....
    • group by ...
    • select distinct *
    • having ...
    • order by ...
    • limit star,count
  • 实际使用中,只是语句中某些部分的组合,而不是全部

作业

  • 对学生表、科目表进行数据的查询

三、MySQL高级

  • 实体与实体之间有3种对应关系,这些关系也需要存储下来
  • 在开发中需要对存储的数据进行一些处理,用到内置的一些函数
  • 视图用于完成查询语句的封装
  • 事务可以保证复杂的增删改操作有效

3.1关系

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

外键

  • 思考:怎么保证关系列数据的有效性呢?任何整数都可以吗?
  • 答:必须是学生表中id列存在的数据,可以通过外键约束进行数据的有效性验证
  • 为stuid添加外键约束
  • 此时插入或者修改数据时,如果stuid的值在students表中不存在则会报错
  • 在创建表时可以直接创建约束

外键的级联操作

  • 在删除students表的数据时,如果这个id值在scores中已经存在,则会抛异常
  • 推荐使用逻辑删除,还可以解决这个问题
  • 可以创建表时指定级联操作,也可以在创建表后再修改外键的级联操作
  • 语法
  • 级联操作的类型包括:
    • restrict(限制):默认值,抛异常
    • cascade(级联):如果主表的记录删掉,则从表中相关联的记录都将被删除
    • set null:将外键设置为空
    • no action:什么都不做

3.2连接

先看个问题

  • 问:查询每个学生每个科目的分数
  • 分析:学生姓名来源于students表,科目名称来源于subjects,分数来源于scores表,怎么将3个表放到一起查询,并将结果显示在同一个结果集中呢?
  • 答:当查询结果来源于多张表时,需要使用连接查询
  • 关键:找到表间的关系,当前的关系是
    • students表的id---scores表的stuid
    • subjects表的id---scores表的subid
  • 则上面问题的答案是:
  • 结论:当需要对有关系的多张表进行查询时,需要使用连接join

连接查询

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

练习

  • 查询学生的姓名、平均分
  • 查询男生的姓名、总分
  • 查询科目的名称、平均分
  • 查询未删除科目的名称、最高分、平均分

3.3自关联

  • 设计省信息的表结构provinces
    • id
    • ptitle
  • 设计市信息的表结构citys
    • id
    • ctitle
    • proid
  • citys表的proid表示城市所属的省,对应着provinces表的id值
  • 问题:能不能将两个表合成一张表呢?
  • 思考:观察两张表发现,citys表比provinces表多一个列proid,其它列的类型都是一样的
  • 意义:存储的都是地区信息,而且每种信息的数据量有限,没必要增加一个新表,或者将来还要存储区、乡镇信息,都增加新表的开销太大
  • 答案:定义表areas,结构如下
    • id
    • atitle
    • pid
  • 因为省没有所属的省份,所以可以填写为null
  • 城市所属的省份pid,填写省所对应的编号id
  • 这就是自关联,表中的某一列,关联了这个表中的另外一列,但是它们的业务逻辑含义是不一样的,城市信息的pid引用的是省信息的id
  • 在这个表中,结构不变,可以添加区县、乡镇街道、村社区等信息
  • 创建areas表的语句如下:
  • 从sql文件中导入数据
  • 查询一共有多少个省
  • 查询省的名称为“山西省”的所有城市
  • 查询市的名称为“广州市”的所有区县

3.4子查询

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

3.5字符串函数

  • 查看字符的ascii码值ascii(str),str是空串时返回0
  • 查看ascii码值对应的字符char(数字)
  • 拼接字符串concat(str1,str2...)
  • 包含字符个数length(str)
  • 截取字符串
    • left(str,len)返回字符串str的左端len个字符
    • right(str,len)返回字符串str的右端len个字符
    • substring(str,pos,len)返回字符串str的位置pos起len个字符
  • 去除空格
    • ltrim(str)返回删除了左空格的字符串str
    • rtrim(str)返回删除了右空格的字符串str
    • trim([方向 remstr from str)返回从某侧删除remstr后的字符串str,方向词包括both、leading、trailing,表示两侧、左、右
  • 返回由n个空格字符组成的一个字符串space(n)
  • 替换字符串replace(str,from_str,to_str)
  • 大小写转换,函数如下
    • lower(str)
    • upper(str)

数学函数

  • 求绝对值abs(n)
  • 求m除以n的余数mod(m,n),同运算符%
  • 地板floor(n),表示不大于n的最大整数
  • 天花板ceiling(n),表示不小于n的最大整数
  • 求四舍五入值round(n,d),n表示原数,d表示小数位置,默认为0
  • 求x的y次幂pow(x,y)
  • 获取圆周率PI()
  • 随机数rand(),值为0-1.0的浮点数
  • 还有其它很多三角函数,使用时可以查询文档

日期时间函数

  • 获取子值,语法如下
    • 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)
  • 日期计算,使用+-运算符,数字后面的关键字为year、month、day、hour、minute、second
  • 日期格式化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的整数
  • 当前日期current_date()
  • 当前时间current_time()
  • 当前日期时间now()

视图

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

3.7事务

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

示例1

  • 步骤1:打开两个终端,连接mysql,使用同一个数据库,操作同一张表
  • 步骤2
  • 步骤3

示例2

  • 步骤1:打开两个终端,连接mysql,使用同一个数据库,操作同一张表
  • 步骤2
  • 步骤3

3.8总结

  • 关系的存储
  • 连接查询
  • 自关联
  • 子查询
  • 常用内置函数
  • 视图
  • 事务

作业

  • 设计班级表,与学生表关联,并进行查询
  • 设计分类表,自关联,并进行查询
  • 创建视图存储上面的两个查询

四、与python交互

  • 在熟练使用sql语句的基础上,开始使用python语言提供的模块与mysql进行交互
  • 这是我们在工作中大事要做的事
  • 先学会sql是基础,一定要熟练编写sql语句

4.1交互类型

安装引入模块

  • 安装mysql模块
  • 在文件中引入模块

Connection对象

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

对象的方法

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

Cursor对象

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

对象的方法

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

对象的属性

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

4.2增删改

增加

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

修改

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

删除

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

sql语句参数化

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

其它语句

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

4.3查询

查询一行数据

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

查询多行数据

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

4.4封装

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

添加

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

查询一个

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

4.5实例:用户登录

创建用户表userinfos

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

加入测试数据

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

接收输入并验证

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

4.6总结

  • python操作数据库的类型及主要成员
  • 使用python类完成crud操作
  • 封装操作数据库的工具类

作业

  • 封装数据库操作工具类
  • 使用工具类完成crud操作
  • 独立实现用户登录
  • 实现用户注册

原文发布于微信公众号 - Java帮帮(javahelp)

原文发表时间:2018-05-30

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Kevin-ZhangCG

[ SSH框架 ] Hibernate框架学习之三

227110
来自专栏MySQL实战分享

【MySQL经典案例分析】关于数据行溢出由浅至深的探讨

故事的开头我们先来看一个常见的sql报错信息, 相信对于这类报错大家一定遇到过很多次了...

37260
来自专栏Jerry的SAP技术分享

使用字面量或者绑定变量在HANA Studio里执行SQL语句

在SAP note 2000002 – FAQ: SAP HANA SQL Optimization里提到了SQL语句的两种执行方式,具体差异体现在where语...

23190
来自专栏Java帮帮-微信公众号-技术文章全总结

第三十天-加强2-多表查询&JDBC&连接池&DBUtils&综合案例【悟空教程】

第三十天-加强2-多表查询&JDBC&连接池&DBUtils&综合案例【悟空教程】

18640
来自专栏数据库

使用VBA创建Access数据表

导读: 本期介绍如何在Access数据库中创建一张空数据表。下期将介绍如何将工作表中的数据存入数据库对应的表中,随后还将介绍如何从数据库的表中取出数据输出到Ex...

31770
来自专栏陈本布衣

SQLite 带你入门

SQLite数据库相较于我们常用的Mysql,Oracle而言,实在是轻量得不行(最低只占几百K的内存)。平时开发或生产环境中使用各种类型的数据库,可能都需要...

47050
来自专栏PingCAP的专栏

TiDB 源码阅读系列文章(四)Insert 语句概览

本文为 TiDB 源码阅读系列文章的第四篇。上一篇文章简单介绍了整体流程,无论什么语句,大体上是在这个框架下运行,DDL 语句也不例外。

45750
来自专栏一“技”之长

iOS开发之AddressBook框架详解

    首先,AddressBook框架是一个已经过时的框架,iOS9之后官方提供了Contacts框架来进行用户通讯录相关操作。尽管如此,AddressBoo...

34110
来自专栏农夫安全

代码审计之SQL注入漏洞

SQL注入: 我的理解很简单,能代入到数据库查询,且没有过滤,或过滤不严谨,就可以造成SQL注入 演示环境:linux+apache+...

37770
来自专栏更流畅、简洁的软件开发方式

预防SQL注入攻击之我见

1、 SQL注入攻击的本质:让客户端传递过去的字符串变成SQL语句,而且能够被执行。 2、 每个程序员都必须肩负起防止SQL注入攻击的责任。   说起防止SQ...

55860

扫码关注云+社区

领取腾讯云代金券