目录
数据存取演变史
1.文件
数据格式-存储位置(不一致)
2.软件开发目录规范
数据格式(不一致)
3.数据库
数据格式-存储位置(一致)
任意计算机上都可以使用
数据库
1.数据库的软件应用史
计算机本机上的数据库(单击游戏)
远程数据库-数据库集群(网络游戏)
2.数据库的本质
底层原理层面：操作数据的进程(运行在内存中的代码)
现实应用层面：操作界面的应用程序(用于操作进程的界面)
3.数据库的分类
3.1 关系型数据库：表与表之间存在关联关系(有明确的表结构)类似于表格
MySQL：开源
MariaDB：开源 与mysql使用方法类似(同一个作者)
Oracle：收费 安全性极高
PostgreSQL：开源
sqlite：小型数据库(本地测试 django)
server
db2
sq1
3.2 非关系型数据库：表与表之间不存在关联关系(没有表结构) 以k:v键值对存储
redis：缓存型数据库
mongoDB：稳定型数据库
memcache
4.数据库的重要概念
库：文件夹
表：文件
记录：文件里的数据
注：show databases-查看所有库
show tables-查看所有的表
select * from 表名-查看表内所有记录
MySQL数据库
1.简介
MySQL数据库-关系型数据库
版本：5.6X 5.7X 8.0X
注：虽存在多版本 但操作几乎无差别 主要体现在底层运作
2.使用(下载安装)
下载：官方下载
解压安装：存在服务端与客户端
主要文件介绍：
bin文件夹：mysqld.exe 服务端 mysql.exe 客户端
data文件夹：存取数据
my_default.ini文件：配置文件
使用：
启动服务端(mysqld)：MySQL文件启动文件夹所在路径-服务端启动文件
启动客户端(mysql --uroot -p密码):MySQL文件启动文件夹所在路径-服务端启动文件(不用用户名与密码直接登录是游客模式)-用户名-密码
退出：quit exit
3.系统服务制作
3.1 解决服务端、客户端登录查找MySQL文件启动文件夹所在路径问题-bin文件夹路径加入全局环境变量
3.2 解决服务端、客户端开启两个cmd终端窗口问题-将服务端添加至开机自启
开启添加命令：musqld --install
开启命令：net start mysql
关闭命令：net stop mysql
移除命令：mysql remove
查看添加自启：services.msc
4.密码操作
4.1 密码修改：
未登录：mysqladmin -u用户名 -p原密码 password 新密码
已登录：set password = PASSWORD('新密码')
4.2 忘记密码：
1.重装/已知密码文件替换
2.先关闭服务端，然后以不需要校验用户身份的方式启动，修改密码之后重新启动
关闭服务端命名：net stop MySQL
启动命令：mysqld --skip-grant-tables
重新启动一个cmd窗口：命令 mysql -uroot -p
修改密码命令：update mysql.user set password=password(123) where Host='localhost' and User='root';
重新启动服务端：net start mysql
5.语法
5.1 结束符：;
5.2 取消符：\c
5.3 以列查看表数据：查询表语句\G;
6.字符编码与配置文件
6.1 字符编码的修改-防止乱码(5.6版本编码不一致）
修改配置文件-复制配置文件修改配置文件内容-统一将字符编码修改为utf8(复制的配置文件的命名与原文件开头必须一致)
6.2配置文件my-default.ini-复制命名my.ini(苹果电脑或Linux系统后缀可能不一致)
SQL语句
1.SQL与NoSQL
注：数据库的服务端可以支持各种语言的客户端-消耗数据库的服务端资源：制度统一的语法
MySQL客户端、python代码编写的客户端、java代码编写的客户端
SQL语句：操作关系型数据库
NoSQL语句：操作非关系型数据库
2.SQL语句的应用：
2.1 针对库的语句：
查：show databases; # 查询所有库
show creat database 库名; # 查询某个库
增：create database 库名;
改：alter database 库名 charset='utf8'; # 修改字符编码
删：drop database 库名;
2.2 针对表的语句：约束条件与存储引擎可根据需要添加
注：use 库名; # 切换至添加的库名下
查：show tables; # 查询所有的表(库下的所有表名)
show create table 表名; # 查询某一个表(表中的所有字段信息及字符编码类型)
describe 表名;/desc 表名; # 查询某一个表(表中的所有字段信息)
增：create table 表名(字段 数据类型(数字) 主键 约束条件，字段 数据类型(数字) 约束条件)engines=存储引擎;
create table 表名 add 字段 数据类型(数字) 约束条件; # 末尾添加字段
create table 表名 add 字段 数据类型(数字) 约束条件 first; # 首行添加字段
create table 表名 add 字段 数据类型(数字) 约束条件 after 已存在字段; # 添加至已存在字段之后
改：alter table 旧表名 rename 新表名; # 修改表名
alter table 表名 change 旧字段 新字段 数据类型(数字) 约束条件; # 修改字段及类型
alter table 表名 modify 字段名 新的字段类型(数字) 约束条件; # 修改字段及类型
删：drop table 表名; # 删表
alter table 表名 drop 字段名; # 删字段
2.3 针对记录的语句：
查：select * from 表名; # 查询表中所有数据
select 字段名 from 表名; # 查询表中某个字段数据
增：insert into 表名 values(数据,...),(数据,...);
改：update 表名 set 字段名=新数据 where 筛选条件;
删：delete from 表名; # 删除整个表数据
delete from 表名 where 筛选条件; # 删除某个字段数据
2.4 字段起别名/表名：as
2.5 给表、字段添加注释：comment
2.6 分组前字段拼接 concat/concat_ws(统一分隔符)
2.7 执行语法：exists(exists后面sql语句有结果执行前面sql语句 没结果则不执行)
存储引擎
1.简介
1.1 定义：针对数据的不同存取方法
1.2 查看所有引擎指令：show engines;
2.分类
MyISAM：5.5及以前版本默认存储引擎(存储速度快 数据安全性低 功能少)
InnoDB：5.5以后默认存储引擎(存储比MyISAM慢 数据安全性高 功能多 支持事务、行锁、外键等)
Memory：基于内存存储(速度快 断电消失)
BlackHole：黑洞(类似垃圾桶 数据放入消失)
3.产生的表
MyISAM：
.frm 表结构
.MYD 表数据
.MYI 表索引
InnoDB：
.frm 表结构
.idb 表数据/表索引
Memory：
.frm 表结构
BlackHole：
.frm 表结构
约束条件
1.定义：对数据字段设置限制条件
create table 表名(字段名 数据 约束条件1 约束条件2 约束条件3)
2.类型:
unsigned # 取消负号
zerofill # 0补位
not null # 非空
unique # 唯一
default # 默认值
auto_increment # 递增(与键/主键连用) 字段删除后自增键不会重置/后退 truncate 表名 格式化
primary Key # 一张表中只能存在一个主键(非空且唯一)= not null+unique(无主键 第一个定为主键 可以存在多个)
Foreign Key # 记录表与表中存在的关系
3.主键 外键
3.1 主键
1.主键与约束条件的区别
primary Key 仅存在一个
not null unique 可存在多个
2.一个表中一般都存在一个id字段 并且将id设为主键
3.2 外键：记录表与表中存在的关系(实际中可能并不使用外键 数据耦合度太高)
关系：
一对一：外键可以建在任意一方(最好建在查询多的一方)
一对多：外键建在多的一方
多对多：创建第三表建立外键创建连接关系
创建外键：
创建员工表：create table emp(id int primary key,
name varchar(32),
gender enum('meal','female'),
join_date datetime,
sal_id int unique,
foreign key(sal_id) references sal(id)
on update cascade
on delete cascade);
注：先创建被关联的表(没有外键的表)然后再创建关联表(有外键的表)
插入数据时 针对外键字段只能填写被关联表字段已经出现过的数据值
被关联字段无法修改和删除(操作限制性太强)
级联更新、级联删除:被关联数据一旦变动 关联的数据同步变动
级联跟新：on update cascade
级联删除：on delete cascade
字段类型
1.分类
1.1 整型：字符长度不同
tinyint 1bytes
smallint 2bytes
int 3bytes
bigint 4bytes
1.2 浮点型：精确度不同
float 最低(平常业务精准度已够用 追求进准的可以使用字符串)
double 中间
decimal 最高
1.3 字符型：超出范围报错
char 定长(不足空格补位) # 空格补位占内存
varchar 变长(不足有几位显示几位)
1.4 枚举与集合
enum 多选一(如性别)
set 多选多(如爱好)
1.5 日期
datetime
date
time
year
数据查询
1.查询关键字
1.1 select...from...
from 先获取查询数据表 select 获得查询字段
1.2 where
where 从查询字段中进行筛选所需字段
1.3 grop by-配合聚合函数(max/min/sum/avg/count)
grop by 对所需字段进行分组操作(以组为单位)
grop_concat 分组之后拼接字符串
注：分组之后我们研究的对象应该是以组为单位
select后面可以直接填写的字段名只能是分组的依据(其他字段需要借助于一些方法才可以获取)
命令：set global sql_mode='strict_trans_tables,only_full_group_by';
1.4 having
having 分组后进行二次筛选(过滤)
1.5 distinct
distinct 去重(相同数据保留一份)
1.6 order by
order by (asc) 升序
order by desc 降序
1.7 limit
limit 页数 分页(按照需要的页数展示)
1.8 regexp
regexp 正则 通过正则表达式从所需字符串中获得所需字段
2.多表查询
2.1 子查询
所需数据来源与一张表(从一张表中获得所需数据做为另一个中查询数据的条件)
2.2 连表操作
所需数据来源与两张及多张表(拼接表获得所需数据)
inner join a=b(两张表中相同字段)
left join a=b(左连接)
right join a=b(右连接)
union 连接左右表(显示两张表里的所有数据)
Navicat数据库管理工具
1.目的：简化数据库的管理及降低系统管理成本
2.使用(下载安装)
2.1 下载：百度搜索下载(正版收费 可以搜索破解版)
2.2 使用：先建立连接 (id 端口(3306) 用户名及密码)
涉及板块-新建查询(sql语句编写)表、视图、函数、查询、图表、模型等
直接通过鼠标点就可以新建数据库-新建表-数据的增删改查(并且显示sql语句)
表中有逆向表到模型可以查看表与表之间的图表关系更加直接方便
也可以在新建查询中直接编写sql语句
sql语句美化-使语句更加美观
python操作MySQL
1.涉及模块
第三方模块：PyMySQL
下载pymysql：pip3 install pymysql
下载第三方模块的方式：pip3 install 模块名==版本号 -i仓库地址
2.操作代码
import pymysql
2.1 连接服务器
conn = pymysql.connect(
host='127.0.0.1',
post=3306,
user='root',
password='xxx',
database='db5',
charset='utf8mb4',
autocommit=True)
cursor = conn.cursor() # 创建游标
sql1 = 'select * from 表名'
cursor.execute(sql1) # 给服务端发送消息
res2 = cursor.fetchmany(2) # 获取指定条数 # ((1, '三年二班'), (2, '三年三班'))
print(res2)
res = cursor.fetchone() # 获取一行数据 # (3, '一年二班')
print(res)
res1 = cursor.fetchall() # 获取所有数据 # ((4, '二年九班'),)
print(res1)
# 类似于文件光标问题(数据取完后再取数据为空)涉及到光标移动问题
cursor.scroll(2, mode='relative') # 基于当前位置向后移动(数据取完会报错）
res = cursor.fetchone() # 获取一行数据
print(res) # (3, '一年二班')
cursor.scroll(2, mode='absolute') # 基于开头向后移动(从开头移动超过数据范围会报错）
res = cursor.fetchone() # 获取一行数据
print(res) # (3, '一年二班')
3.SQL注入问题
3.1 本质：利用一些特殊符号的组合产生了特殊的含义从而逃脱正常业务逻辑(--注掉了)
select * from userinfo where name='jason' --xixi 'and pwd=''
select * from userinfo where name='xxx' --xixi 'and pwd=''
3.2 解决措施：针对用户输入法人数据不要自己处理 交给专门的方法自动过滤
sql = "select * from userinfo where name=%s and pwd=%s"
cursor.execute(sql, (username, password)) # 自动识别%s 并自动过滤各种符合 最后合并数据
视图
1.定义：通过SQL语句产生的虚拟表保存之后形成视图(没有实质性数据 主要用于数据查询)
2.作用：大部分情况是保障数据的安全性 提高查询效率
3.语句：
保存：create view 视图名 as SQL语句
删除：drop view 视图名
触发器
1.定义：针对表数据的增/改/删 前/后 自动触发的功能(一种特殊的存储过程)/协助应用在数据库端确保数据的完整性(日志记录 数据校验等操作)
2.作用：
可在写入数据表前，强制检验或转换数据
触发器发生错误时，异动的结果会被撤销
部分数据库管理系统可以针对数据定义语言(DDL)使用触发器
可依据特定的情况，替换异动的指令(instead of)
3.分类：DML触发器、DDL触发器、登录触发器
3.语法：creat trigger before/after insert/update/delete on 表名 each row
begin
sql语句
end
注：修改结束符：delimiter $$
存储过程
1.定义:编译并储存在数据库中的一段SQL语句的集合 类似于python中的函数(数据库sql语句的封装与重用)
2.作用：调用存储过程可以简化开发人员的工作 减少数据在数据库与应用服务上的传输
3.语法：
3.1语法结构:
delimiter 临时结束符
create procedure 存储过程名字(参数)
degin
SQL语句
end 临时结束符
delimiter;
3.2 查看存储过程
show create procedure 存储过程名字;
3.3 查看所有存储过程
show procrdure statues;
3.4 删除存储过程
drop procrdure 存储过程名字;
事务
1.定义:最小的不可分割的工作单元 通常一个事务对应一个完整业务 要么全部成功 要么全部失败
2.目的：为数据库提供一个从失败中恢复正常状态的方法 同时提供了数据库即使在异常状态下任保持一致性的方法
当多个应用程序在并发访问数据库时 可以在这些应用程序之间提供一个隔离方法 以防止彼此的操作互相干扰
2.作用：可以保持数据的一致性和完整性(避免异常和错误等导致的数据信息异常)
3.特性(ACID)
原子性：不可分割 全成功 全失败
一致性：数据从一个一致性状态变到另一个一致性状态
隔离性：事务之间隔离互不干扰
持久性：提交数据后永久修改数据库数据
4.影响类型
4.1 脏读
4.2 不可重复读
4.3 幻读
4.4 丢失更新
5.语法
begin;
start transactton; # 开始一个事务
SQL语句;
rollback; # 事务回滚
commit; # 事务确认 确认后也可以执行 rollback但并不能影响结果
6.隔离级别：只有InnoDB存储引擎支持事务
6.1 读未提交(Read uncommitted)：一个事务可以读取到另一个事务未提交的修改(产生脏读、幻读、不可重复读问题)-几乎不用
6.2 读已提交(read committed)：一个事务只能读取另一个事务已提交的修改(产生幻读、不可重复读问题)
6.3 可重复读(repeatable read)：同一个事务多次读取相同的数据返回的结果一致(MySQL中默认的隔离级别)MySQL8之前版本的InnoDB可能存在幻读
6.4 串行化(serializable)事务串行执行 一次值允许一个事务进行操作(最安全 效率最低)
7.日志:提高事务效率
存储引擎在修改表的数据时只需要修改其内存拷贝再把该修改记录持久的在硬盘的事务日志中，而不用每次都将修改的数据本身持久到硬盘
事务日志采用的是追加方式因此在写日志操作是磁盘上一小块区域内的顺序IO而不像随机IO需要随机在多个地方移动磁头所以采用事务日志的方式相对来说要快的多
事务日吃持久之后内存中被修改的数据再后台慢慢刷回磁盘 目前大多数存储引擎都是这样实现的 通常称之为'预写式日志'修改数据需要写两次磁盘
8.扩展知识
MySQL提供两种事务型存储引擎InnoDB和NDB cluster及第三方XtraDB、PBXT
事务(transaction)
回退(rollback)
提交(commit)
保留点(savepoint)
为了支持回退部分事务处理 必须能在事务处理块中合适的位置放置占位符 如果需要回退就可以退回到某个占位符的位置(保留点)
创建占位符：savepoint
savepoint sp01;
回退到占位符地址
rollback to sp01;
# 保留点在执行rollback或者commit之后自动释放
MVCC多版本并发控制
1.产生条件：在read committed(提交读)、repeatable read(可重复读)两种隔离级别下使用
2.实现过程：InnoDB的MVCC通过在每行记录后面保存两个隐藏的列来实现MVCC
一个列保存了行的创建时间
一个列保存了行的过期时间(或删除时间) 本质是系统版本号
每开始一个新的事务版本号都会自动递增，事务开始时刻的系统版本号会作为事务的版本号用来和查询到的每行记录版本号进行比较
内置函数
1.分类
1.1 移除指定字符：Trim/LTrim/RTrim
1.2 大小写转换：upper lower
1.3 获取左右指定个数字符：left right
1.4 返回读音相似值(只对英文由效果)：Soundex
1.5 日期格式：date_format(输出时间格式)
在MySQL中表示时间格式尽量采用2022-11-11形式
adddate # 增加一个日期
addtime # 增加一个时间
datediff # 计算两个日期差值
注：help 函数名 # 查看帮助信息
流程控制
1.种类
if分支
while循环
2.语法
2.1 if分支
delimiter $$
create procedure pro_if()
begin
declare i int default 0;
if i = 1 then
select 1;
elseif i = 2 then
select 2;
else
select 7;
end if;
end $$
delimiter ;
set @i=2; # 定义
call pro_if() # 调用输出结果
2.2 while循环
delimiter $$
create procedure pro_while()
begin
declare num int;
set num = 0;
while num < 10 do
select num;
set num = num + 1;
end while;
end $$
delimiter ;
call pro_while() # 调用查看结果
索引
1.定义：数据库表的字段上添加方便提高查询速度(类似于书的目录 更快的获得所需数据)
2.作用：让获取的数据更具有目的性，从而提高数据库检索数据的性能
3.分类：
3.1 字段特性：
普通索引：index key
唯一索引：unique key
主键索引：primary key
全文索引：fulltext
MyISAM 存储引擎支持Full-text索引，用于查找文本中的关键词，而不是直接比较是否相等
Full-text索引一般使用倒排索引实现，它记录着关键词到其所在文档的映射。
InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持Full-text索引。
3.2 数据结构：
B+tree索引：B树 B+树 B*树
B树(B-树)又叫二叉搜索树：只能有两个分支 所有节点存储一个关键字
优点：提高了查找顺序
缺点：每一个被查找项被查找的概率并不相等(首尾项)效率比较低
B+树：只有叶子节点存放真正的数据 其他节点只存主键值(辅助索引值)
B*树：在树节点添加了通往其他节点的通道 减少查询次数
Hash索引：Memroy存储引擎支持
Full_text索引：一般使用倒排索引实现
3.3 按字段个数
单列索引
联合索引
3.4 物理存储分类
聚集索引：按照每张表的主键构造一颗 B+tree，同时叶子节点中存放的就是整张表的行记录数据，聚集索引的叶子节点被称为数据页
非聚集索引：非聚集索引的结构和聚集索引基本相同（非叶子结点存储的都是索引指针），区别在于叶子节点存放的不是行数据而是数据主键
4.索引的优缺点
优点：索引的存在可以加快数据查询
缺点：会减慢数据的增删 并且建立索引会占用磁盘空间的索引文件
5.使用索引查询数据
5.1.explain语句
explain select name,countrycode from city where id=1;
5.2.扫描方式
全表扫描：没有索引、索引创建有问题、语句又问题时使用(explain语句结果all)
索引扫描：
查看索引扫描类型语句：explain type(访问类型)
5.2.1 索引查询类型
1.index：遍历索引树(all遍历所有数据)
2.range：扫描开始与某一点 返回匹配值域的行(使用索引最低要求)
3.ref：使用唯一索引扫描或者唯一索引前缀扫描 返回匹配某个单独值得记录行
4.eq_ref：使用索引的唯一索引 对于每个索引键值 表中只有一条记录匹配
5.const、system：对查询的部分进行优化 并转化为一个常量时 使用这些类型访问
6.NULL：在优化过程中分解语句 执行时甚至不用访问表或索引
5.2.3.数据库索引的设计原则
1. 选择唯一索引
2. 经常需要排序、分组和联合操作的字段建立索引
3. 常作为查询条件的字段建立索引
4. 尽量使用前缀来索引
posted on
浙公网安备 33010602011771号