简介
- 主要知识点包括:能够与mysql建立连接,创建数据库、表,分别从图形界面与脚本界面两个方面讲解
- 相关的知识点包括:E-R关系模型,数据库的3范式,mysql中数据字段的类型,字段约束
- 数据库的操作主要包括:
- 数据库的操作,包括创建、删除
- 表的操作,包括创建、修改、删除
- 数据的操作,包括增加、修改、删除、查询,简称crud
- 学生表结构:
- id
- 名称
- 性别
- 地址
- 生日
- 科目表结构:
- id
- 名称
数据库简介
- 人类在进化的过程中,创造了数字、文字、符号等来进行数据的记录,但是承受着认知能力和创造能力的提升,数据量越来越大,对于数据的记录和准确查找,成为了一个重大难题
- 计算机诞生后,数据开始在计算机中存储并计算,并设计出了数据库系统
- 数据库系统解决的问题:持久化存储,优化读写,保证数据的有效性
- 当前使用的数据库,主要分为两类
- 文档型,如sqlite,就是一个文件,通过对文件的复制完成数据库的复制
- 服务型,如mysql、postgre,数据存储在一个物理文件中,但是需要使用终端以tcp/ip协议连接,进行数据库的读写操作
E-R模型
- 当前物理的数据库都是按照E-R模型进行设计的
- E表示entry,实体
- R表示relationship,关系
- 一个实体转换为数据库中的一个表
- 关系描述两个实体之间的对应规则,包括
- 一对一
- 一对多
- 多对多
- 关系转换为数据库表中的一个列 *在关系型数据库中一行就是一个对象
三范式
- 经过研究和对使用中问题的总结,对于设计数据库提出了一些规范,这些规范被称为范式
- 第一范式(1NF):列不可拆分
- 第二范式(2NF):唯一标识
- 第三范式(3NF):引用主键
- 说明:后一个范式,都是在前一个范式的基础上建立的
安装
- 安装
1 | sudo apt-get install mysql-server mysql-client |
管理服务
- 启动
1 | service mysql start |
- 停止
1 | service mysql stop |
- 重启
1 | service mysql restart |
允许远程连接
- 找到mysql配置文件并修改
1 | sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf |
- 登录mysql,运行命令
1 | grant all privileges on *.* to 'root'@'%' identified by 'mysql' with grant option; |
- 重启mysql
数据完整性
- 一个数据库就是一个完整的业务单元,可以包含多张表,数据被存储在表中
- 在表中为了更加准确的存储数据,保证数据的正确有效,可以在创建表的时候,为表添加一些强制性的验证,包括数据字段的类型、约束
字段类型
- 在mysql中包含的数据类型很多,这里主要列出来常用的几种
- 数字:int,decimal
- 字符串:varchar,text
- 日期:datetime
- 布尔:bit
约束
- 主键primary key
- 非空not null
- 惟一unique
- 默认default
- 外键foreign key
使用图形窗口连接
- 下发windows的navicat
- 点击“连接”弹出窗口,按照提示填写连接信息,如下图
- 连接成功后,会在连接名称下面显示出当前的数据库
- 双击选中数据库,就可以编辑此数据库
- 下次再进入此软件时,通过双击完成连接、编辑操作
数据库操作
- 在连接的名称上右击,选择“新建数据库”,弹出窗口,并按提示填写
- 在数据库上右击,选择“删除数据库”可以完成删除操作
表操作
- 当数据库显示为高亮时,表示当前操作此数据库,可以在数据中创建表
- 一个实体对应一张表,用于存储特定结构的数据
- 点击“新建表”,弹出窗口,按提示填写信息
- 主键的名称一般为id,设置为int型,无符号数,自动增长,非空
- 自动增长表示由mysql系统负责维护这个字段的值,不需要手动维护,所以不用关心这个字段的具体值
- 字符串varchar类型需要设置长度,即最多包含多少个字符
- 点击“添加栏位”,可以添加一个新的字段
- 点击“保存”,为表定义名称
数据操作
- 表创建成功后,可以在右侧看到,双击表打开新窗口,如下图
- 在此窗口中可以增加、修改、删除数据
逻辑删除
- 对于重要数据,并不希望物理删除,一旦删除,数据无法找回
- 一般对于重要数据,会设置一个isDelete的列,类型为bit,表示逻辑删除
- 大于大量增长的非重要数据,可以进行物理删除
- 数据的重要性,要根据实际开发决定
使用命令连接
- 命令操作方式,在工作中使用的更多一些,所以要达到熟练的程度
- 打开终端,运行命令
1 | mysql -uroot -p |
- 连接成功后如下图
- 退出登录
1 | quit或exit |
- 退出成功后如下图
- 登录成功后,输入如下命令查看效果
1 | 查看版本:select version(); |
- 注意:在语句结尾要使用分号;
远程连接
- 一般在公司开发中,可能会将数据库统一搭建在一台服务器上,所有开发人员共用一个数据库,而不是在自己的电脑中配置一个数据库
- 运行命令
1 | mysql -hip地址 -uroot -p |
- -h后面写要连接的主机ip地址
- -u后面写连接的用户名
- -p回车后写密码
数据库操作
- 创建数据库
1 | create database 数据库名 charset=utf8; |
- 删除数据库
1 | drop database 数据库名; |
- 切换数据库
1 | use 数据库名; |
- 查看当前选择的数据库
1 | select database(); |
表操作
- 查看当前数据库中所有表
1 | show tables; |
创建表
auto_increment表示自动增长
1
2
3
4
5
6create table 表名(列及类型);
如:
create table students(
id int auto_increment primary key,
sname varchar(10) not null
);修改表
1 | alter table 表名 add|change|drop 列名 类型; |
- 删除表
1 | drop table 表名; |
- 查看表结构
1 | desc 表名; |
- 更改表名称
1 | rename table 原表名 to 新表名; |
- 查看表的创建语句
1 | show create table '表名'; |
数据操作
- 查询
1 | select * from 表名 |
- 增加
1 | 全列插入:insert into 表名 values(...) |
- 主键列是自动增长,但是在全列插入时需要占位,通常使用0,插入成功后以实际数据为准
- 修改
1 | update 表名 set 列1=值1,... where 条件 |
- 删除
1 | delete from 表名 where 条件 |
- 逻辑删除,本质就是修改操作update
1 | alter table students add isdelete bit default 0; |
备份与恢复
数据备份
- 进入超级管理员
1 | sudo -s |
- 进入mysql库目录
1 | cd /var/lib/mysql |
- 运行mysqldump命令
1 | mysqldump –uroot –p 数据库名 > ~/Desktop/备份文件.sql; |
数据恢复
- 连接mysqk,创建数据库
- 退出连接,执行如下命令
1 | mysql -uroot –p 数据库名 < ~/Desktop/备份文件.sql |
简介
- 查询的基本语法
1 | select * from 表名; |
- from关键字后面写表名,表示数据来源于是这张表
- select后面写表中的列名,如果是*表示在结果中显示表中所有列
- 在select后面的列名部分,可以使用as为列起别名,这个别名出现在结果集中
- 如果要查询多个列,之间使用逗号分隔
消除重复行
- 在select后面列前使用distinct可以消除重复的行
1 | select distinct gender from students; |
条件
- 使用where子句对表中的数据筛选,结果为true的行会出现在结果集中
- 语法如下:
1 | select * from 表名 where 条件; |
比较运算符
- 等于=
- 大于>
- 大于等于>=
- 小于<
- 小于等于<=
- 不等于!=或<>
- 查询编号大于3的学生
1 | select * from students where id>3; |
- 查询编号不大于4的科目
1 | select * from subjects where id<=4; |
- 查询姓名不是“黄蓉”的学生
1 | select * from students where sname!='黄蓉'; |
- 查询没被删除的学生
1 | select * from students where isdelete=0; |
逻辑运算符
- and
- or
- not
- 查询编号大于3的女同学
1 | select * from students where id>3 and gender=0; |
- 查询编号小于4或没被删除的学生
1 | select * from students where id<4 or isdelete=0; |
模糊查询
- like
- %表示任意多个任意字符
- _表示一个任意字符
- 查询姓黄的学生
1 | select * from students where sname like '黄%'; |
- 查询姓黄并且名字是一个字的学生
1 | select * from students where sname like '黄_'; |
- 查询姓黄或叫靖的学生
1 | select * from students where sname like '黄%' or sname like '%靖%'; |
范围查询
- in表示在一个非连续的范围内
- 查询编号是1或3或8的学生
1 | select * from students where id in(1,3,8); |
- between … and …表示在一个连续的范围内
- 查询学生是3至8的学生
1 | select * from students where id between 3 and 8; |
- 查询学生是3至8的男生
1 | select * from students where id between 3 and 8 and gender=1; |
空判断
- 注意:null与’’是不同的
- 判空is null
- 查询没有填写地址的学生
1 | select * from students where hometown is null; |
- 判非空is not null
- 查询填写了地址的学生
1 | select * from students where hometown is not null; |
- 查询填写了地址的女生
1 | select * from students where hometown is not null and gender=0; |
优先级
- 小括号,not,比较运算符,逻辑运算符
- and比or先运算,如果同时出现并希望先算or,需要结合()使用
分组
- 按照字段分组,表示此字段相同的数据会被放到一个组中
- 分组后,只能查询出相同的数据列,对于有差异的数据列无法出现在结果集中
- 可以对分组后的数据进行统计,做聚合运算
- 语法:
1 | select 列1,列2,聚合... from 表名 group by 列1,列2,列3... |
- 查询男女生总数
1 | select gender as 性别,count(*) |
- 查询各城市人数
1 | select hometown as 家乡,count(*) |
分组后的数据筛选
- 语法:
1 | select 列1,列2,聚合... from 表名 |
- having后面的条件运算符与where的相同
- 查询男生总人数
1 | 方案一 |
对比where与having
- where是对from后面指定的表进行数据筛选,属于对原始数据的筛选
- having是对group by的结果进行筛选
聚合
- 为了快速得到统计数据,提供了5个聚合函数
- count(*)表示计算总行数,括号中写星与列名,结果是相同的
- 查询学生总数
1 | select count(*) from students; |
- max(列)表示求此列的最大值
- 查询女生的编号最大值
1 | select max(id) from students where gender=0; |
- min(列)表示求此列的最小值
- 查询未删除的学生最小编号
1 | select min(id) from students where isdelete=0; |
- sum(列)表示求此列的和
- 查询男生的编号之后
1 | select sum(id) from students where gender=1; |
- avg(列)表示求此列的平均值
- 查询未删除女生的编号平均值
1 | select avg(id) from students where isdelete=0 and gender=0; |
排序
- 为了方便查看数据,可以对数据进行排序
- 语法:
1 | select * from 表名 |
- 将行数据按照列1进行排序,如果某些行列1的值相同时,则按照列2排序,以此类推
- 默认按照列值从小到大排列
- asc从小到大排列,即升序
- desc从大到小排序,即降序
- 查询未删除男生学生信息,按学号降序
1 | select * from students |
- 查询未删除科目信息,按名称升序
1 | select * from subject |
总结
- 完整的select语句
1 | select distinct * |
- 执行顺序为:
- from 表名
- where ….
- group by …
- select distinct *
- having …
- order by …
- limit star,count
- 实际使用中,只是语句中某些部分的组合,而不是全部
获取部分行
- 当数据量过大时,在一页中查看数据是一件非常麻烦的事情
- 语法
1 | select * from 表名 |
- 从start开始,获取count条数据
- start索引从0开始
示例:分页
- 已知:每页显示m条数据,当前显示第n页
- 求总页数:此段逻辑后面会在python中实现
- 查询总条数p1
- 使用p1除以m得到p2
- 如果整除则p2为总数页
- 如果不整除则p2+1为总页数
- 求第n页的数据
1 | select * from students |
先看个问题
- 问:查询每个学生每个科目的分数
- 分析:学生姓名来源于students表,科目名称来源于subjects,分数来源于scores表,怎么将3个表放到一起查询,并将结果显示在同一个结果集中呢?
- 答:当查询结果来源于多张表时,需要使用连接查询
- 关键:找到表间的关系,当前的关系是
- students表的id—scores表的stuid
- subjects表的id—scores表的subid
- 则上面问题的答案是:
1 | select students.sname,subjects.stitle,scores.score |
- 结论:当需要对有关系的多张表进行查询时,需要使用连接join
连接查询
- 连接查询分类如下:
- 表A inner join 表B:表A与表B匹配的行会出现在结果中
- 表A left join 表B:表A与表B匹配的行会出现在结果中,外加表A中独有的数据,未对应的数据使用null填充
- 表A right join 表B:表A与表B匹配的行会出现在结果中,外加表B中独有的数据,未对应的数据使用null填充
- 在查询或条件中推荐使用“表名.列名”的语法
- 如果多个表中列名不重复可以省略“表名.”部分
- 如果表的名称太长,可以在表名后面使用’ as 简写名’或’ 简写名’,为表起个临时的简写名称
练习
- 查询学生的姓名、平均分
1 | select students.sname,avg(scores.score) |
- 查询男生的姓名、总分
1 | select students.sname,avg(scores.score) |
- 查询科目的名称、平均分
1 | select subjects.stitle,avg(scores.score) |
- 查询未删除科目的名称、最高分、平均分
1 | select subjects.stitle,avg(scores.score),max(scores.score) |
字符串函数
- 查看字符的ascii码值ascii(str),str是空串时返回0
1 | select ascii('a'); |
- 查看ascii码值对应的字符char(数字)
1 | select char(97); |
- 拼接字符串concat(str1,str2…)
1 | select concat(12,34,'ab'); |
- 包含字符个数length(str)
1 | select length('abc'); |
- 截取字符串
- left(str,len)返回字符串str的左端len个字符
- right(str,len)返回字符串str的右端len个字符
- substring(str,pos,len)返回字符串str的位置pos起len个字符
1 | select substring('abc123',2,3); |
- 去除空格
- ltrim(str)返回删除了左空格的字符串str
- rtrim(str)返回删除了右空格的字符串str
- trim([方向 remstr from str)返回从某侧删除remstr后的字符串str,方向词包括both、leading、trailing,表示两侧、左、右
1 | select trim(' bar '); |
- 返回由n个空格字符组成的一个字符串space(n)
1 | select space(10); |
- 替换字符串replace(str,from_str,to_str)
1 | select replace('abc123','123','def'); |
- 大小写转换,函数如下
- lower(str)
- upper(str)
1 | select lower('aBcD'); |
数学函数
- 求绝对值abs(n)
1 | select abs(-32); |
- 求m除以n的余数mod(m,n),同运算符%
1 | select mod(10,3); |
- 地板floor(n),表示不大于n的最大整数
1 | select floor(2.3); |
- 天花板ceiling(n),表示不小于n的最大整数
1 | select ceiling(2.3); |
- 求四舍五入值round(n,d),n表示原数,d表示小数位置,默认为0
1 | select round(1.6); |
- 求x的y次幂pow(x,y)
1 | select pow(2,3); |
- 获取圆周率PI()
1 | select PI(); |
- 随机数rand(),值为0-1.0的浮点数
1 | 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)
1 | select year('2016-12-21'); |
- 日期计算,使用+-运算符,数字后面的关键字为year、month、day、hour、minute、second
1 | 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的整数
1 | select date_format('2016-12-21','%Y %m %d'); |
- 当前日期current_date()
1 | select current_date(); |
- 当前时间current_time()
1 | select current_time(); |
- 当前日期时间now()
1 | select now(); |
视图
- 对于复杂的查询,在多次使用后,维护是一件非常麻烦的事情
- 解决:定义视图
- 视图本质就是对查询的一个封装
- 定义视图
1 | create view stuscore as |
- 视图的用途就是查询
1 | select * from stuscore; |
子查询
- 查询支持嵌套使用
- 查询各学生的语文、数学、英语的成绩
1 | select sname, |
事务
- 当一个业务逻辑需要多个sql完成时,如果其中某条sql语句出错,则希望整个操作都退回
- 使用事务可以完成退回的功能,保证业务逻辑的正确性
- 事务四大特性(简称ACID)
- 原子性(Atomicity):事务中的全部操作在数据库中是不可分割的,要么全部完成,要么均不执行
- 一致性(Consistency):几个并行执行的事务,其执行结果必须与按某一顺序串行执行的结果相一致
- 隔离性(Isolation):事务的执行不受其他事务的干扰,事务执行的中间结果对其他事务必须是透明的
- 持久性(Durability):对于任意已提交事务,系统必须保证该事务对数据库的改变不被丢失,即使数据库出现故障
- 要求:表的类型必须是innodb或bdb类型,才可以对此表使用事务
- 查看表的创建语句
1 | show create table students; |
- 修改表的类型
1 | alter table '表名' engine=innodb; |
- 事务语句
1 | 开启begin; |
示例1
- 步骤1:打开两个终端,连接mysql,使用同一个数据库,操作同一张表
1 | 终端1: |
- 步骤2
1 | 终端1: |
- 步骤3
1 | 终端2: |
示例2
- 步骤1:打开两个终端,连接mysql,使用同一个数据库,操作同一张表
1 | 终端1: |
- 步骤2
1 | 终端1: |
- 步骤3
1 | 终端2: |
关系
- 创建成绩表scores,结构如下
- id
- 学生
- 科目
- 成绩
- 思考:学生列应该存什么信息呢?
- 答:学生列的数据不是在这里新建的,而应该从学生表引用过来,关系也是一条数据;根据范式要求应该存储学生的编号,而不是学生的姓名等其它信息
- 同理,科目表也是关系列,引用科目表中的数据
- 创建表的语句如下
1 | create table scores( |
外键
- 思考:怎么保证关系列数据的有效性呢?任何整数都可以吗?
- 答:必须是学生表中id列存在的数据,可以通过外键约束进行数据的有效性验证
- 为stuid添加外键约束
1 | alter table scores add constraint stu_sco foreign key(stuid) references students(id); |
- 此时插入或者修改数据时,如果stuid的值在students表中不存在则会报错
- 在创建表时可以直接创建约束
1 | create table scores( |
外键的级联操作
- 在删除students表的数据时,如果这个id值在scores中已经存在,则会抛异常
- 推荐使用逻辑删除,还可以解决这个问题
- 可以创建表时指定级联操作,也可以在创建表后再修改外键的级联操作
- 语法
1 | alter table scores add constraint stu_sco foreign key(stuid) references students(id) on delete cascade; |
- 级联操作的类型包括:
- restrict(限制):默认值,抛异常
- cascade(级联):如果主表的记录删掉,则从表中相关联的记录都将被删除
- set null:将外键设置为空
- no action:什么都不做
安装引入模块
- 安装mysql模块
1 | sudo apt-get install python-mysql |
- 在文件中引入模块
1 | import Mysqldb |
Connection对象
- 用于建立与数据库的连接
- 创建对象:调用connect()方法
1 | conn=connect(参数列表) |
- 参数host:连接的mysql主机,如果本机是’localhost’
- 参数port:连接的mysql主机的端口,默认是3306
- 参数db:数据库的名称
- 参数user:连接的用户名
- 参数password:连接的密码
- 参数charset:通信采用的编码方式,默认是’gb2312’,要求与数据库创建时指定的编码一致,否则中文会乱码
对象的方法
- close()关闭连接
- commit()事务,所以需要提交才会生效
- rollback()事务,放弃之前的操作
- cursor()返回Cursor对象,用于执行sql语句并获得结果
Cursor对象
- 执行sql语句
- 创建对象:调用Connection对象的cursor()方法
1 | 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文件,向学生表中插入一条数据
1 | #encoding=utf-8 |
修改
- 创建testUpdate.py文件,修改学生表的一条数据
1 | #encoding=utf-8 |
删除
- 创建testDelete.py文件,删除学生表的一条数据
1 | #encoding=utf-8 |
sql语句参数化
- 创建testInsertParam.py文件,向学生表中插入一条数据
1 | #encoding=utf-8 |
其它语句
- cursor对象的execute()方法,也可以用于执行create table等语句
- 建议在开发之初,就创建好数据库表结构,不要在这里执行
查询一行数据
- 创建testSelectOne.py文件,查询一条学生信息
1 | #encoding=utf8 |
查询多行数据
- 创建testSelectMany.py文件,查询一条学生信息
1 | #encoding=utf8 |
封装
- 观察前面的文件发现,除了sql语句及参数不同,其它语句都是一样的
- 创建MysqlHelper.py文件,定义类
1 | #encoding=utf8 |
添加
- 创建testInsertWrap.py文件,使用封装好的帮助类完成插入操作
1 | #encoding=utf8 |
查询一个
- 创建testGetOneWrap.py文件,使用封装好的帮助类完成查询最新一行数据操作
1 | #encoding=utf8 |
实例:用户登录
创建用户表userinfos
- 表结构如下
- id
- uname
- upwd
- isdelete
- 注意:需要对密码进行加密
- 如果使用md5加密,则密码包含32个字符
- 如果使用sha1加密,则密码包含40个字符,推荐使用这种方式
1 | create table userinfos( |
加入测试数据
- 插入如下数据,用户名为123,密码为123,这是sha1加密后的值
1 | insert into userinfos values(0,'123','40bd001563085fc35165329ea1ff5c5ecbdbbeef',0); |
接收输入并验证
- 创建testLogin.py文件,引入hashlib模块、MysqlHelper模块
- 接收输入
- 根据用户名查询,如果未查到则提示用户名不存在
- 如果查到则匹配密码是否相等,如果相等则提示登录成功
- 如果不相等则提示密码错误
1 | #encoding=utf-8 |