[学习日志][数据管理]MySQL,Redis 基础知识点整理

数据库

目录

MySQL

基本使用

• 数据库

  • 概述

    • 数据库就是存储和管理数据的仓库，按照一定格式存储数据，用户可对数据进行增删改查。

  • 分类

    • 关系型数据库

• 概述

  • 二维表格模型

• 核心元素

  • 数据行
    - 数据列
    - 数据表
    - 数据库

• 常用数据库

  • Oracle
    - Microsoft SQL Server
    - MySQL
    - SQLite

• 非关系型数据库
  - 概述

    	- key_value方式存储数据
  
    - 常用数据库
  
    	- MongoDB
    - Redis
  

  • 特点

    • 持久化存储

  • 读写速度极高

    • 保证数据的有效性

• 关系型数据库管理系统

  • 架构

    • 关系型数据库服务端软件

      • 管理不同数据库，实际存储数据。

    • 关系型数据库客户端软件

      • 用户操作，实现数据库相关操作。

  • SQL

    • 结构化查询语言，实现数据库客户端和数据库服务端之间的通信。

• MySQL数据库

  • 安装

    sudo apt-get install mysql-server

  • 配置信息

    /etc/mysql/mysql.conf.d/mysqld.cnf

    • port表示端口号，默认为3306

    • bind-address表示服务器绑定的ip，默认为127.0.0.1

    • datadir表示数据库保存路径，默认为/var/lib/mysql

    • log_error表示错误日志，默认为/var/log/mysql/error.log

  • 查看mysql服务效果

    ps -aux|grep mysql

    • ps 查看当前系统中的进程

    • -a 表示所有用户

    • -u 表示显示用户名

    • -x 表示显示所有执行程序

  • 查看mysql服务状态

    sudo service mysql status

  • 停止mysql服务

    sudo service mysql stop

  • 启动mysql服务

    sudo service mysql start

  • 重启mysql服务

    sudo service mysql restart

  • mysql帮助文档

    mysql–help

  • mysql客户端的使用

    mysql -uroot -p

  • 数据引擎

    • 分类

      • InnoDB 支持事务

      • MyISAM 不支持事务，优势时访问速度快

    • 显示数据引擎

      show engines;

    • 修改表的数据引擎

      alter table students engine = ‘MyISAM’;

• 数据类型和约束

  • 数据类型

    • 整数

      • int，bit

    • 小数

      • decimal
        • decimal（5，2）-表示5位数小数占2位

    • 字符串

      • varchar，char
      • char固定长度，不足空格补齐
      • varchar可变长度，多长占多少

    • 日期时间

    • date，time，datetime

    • 枚举类型

    • enum

    • 文本

    • text

      • 储大文本，当字符大于 4000 时推荐使用, 比如技术博客.

  • 数据约束

    • primary key

  • 物理上存储的顺序. MySQL 建议所有表的主键字段都叫 id, 类型为 int unsigned.

  • not null

    • 此字段不允许填写空值

    • unique

  • 此字段的值不允许重复

  • default

    • 当不填写字段对应的值会使用默认值，如果填写时以填写为准

    • foreign key

  • 对关系字段进行约束, 当为关系字段填写值时, 会到关联的表中查询此值是否存在, 如果存在则填写成功, 如果不存在则填写失败并抛出异常.

• 基本操作

  • 操作数据库

    • 查看所有数据库

      show databases;

    • 创建数据库

      create database 数据库名 charset=utf8;

    • 使用数据库

      use 数据库名;

    • 查看当前使用的数据库

      select database();

    • 删除数据库

      drop database 数据库名;

  • 操作表

    • 查看当前数据库所有表

      show tables;

    • 创建表

      create table 表名(
      字段名称 数据类型 可选的约束条件,
      column1 datatype contrai,
      …
      );

    • 修改表添加字段

      alter table 表名 add 列名 类型 约束;

    • 修改表-修改字段类型

      lter table 表名 modify 列名 类型 约束;

      • modify 只能改字段类型和约束，不能修改字段名

    • 修改表-修改字段名和字段类型

      alter table 表名 change 原名 新名 类型及约束;

      • change：既能对字段重命名也能修改字段类型，约束

    • 修改表-删除字段

      alter table 表名 drop 列名;

    • 查看创表SQL语句

      show create table 表名;

    • 查看创库SQL语句

      show create database 数据库名;

    • 删除表

      drop table 表名;

  • 操作数据

    • 查询数据

      select * from 表名;

      select 列1,列2,… from 表名;

    • 添加数据

      insert into 表名 values (…)

    • 修改数据

      update 表名 set 列1=值1,列2=值2… where 条件

    • 删除数据

      delete from 表名 where 条件

• as和distinct关键字

  • as

    • 起别名

    • 字段名

      select id as 序号, name as 名字, gender as 性别 from students;

      select students.id,students.name,students.gender from students;

    • 表名

      select s.id,s.name,s.gender from students as s;

  • distinct

    • 去除重复数据行

      select distinct 列1,… from 表名;

• where条件查询

  • 格式

    select * from 表名 where 条件;

  • 分类

    • 比较运算符

      • =,!=,<>,>,<,>=,<=

    • 逻辑运算符

      • and or not

    • 模糊查询

      • like

        • 模糊查询关键字

        • %表示任意多个任意字符

        • _表示一个任意字符

    • 范围查询

      • between … and …

        • 表示在一个连续的范围内查询

      • in

        • 表示在一个非连续的范围内查询

    • 空判断查询

      • is null
      • is not null

• 排序

  select * from 表名 order by 列1 asc|desc [,列2 asc|desc,…]

  • 先按列1 ，再按列2
  • asc 升序（默认）
  • desc 降序

• 分页查询

  select * from 表名 limit start,count

  • limit 关键字
  • start 开始行
  • count 每页表示数据

条件查询

• 聚合函数

  • count(col)

    • 求指定列的总行数

  • min/max(col)

    • 求指定列的最小值，最大值

  • sum(col)

    • 求指定列的和

  • avg(col)

    • 求指定列的平均值

  • 对空值处理

    • 聚合函数默认忽略空值，可用ifnull()函数替换默认值。
    • ifnull(height,0)–>为空换成0

• 分组查询

  • 概述

    • 将查询结果按照指定字段进行分组，字段中数据相等的分为一组。

  • 语法

    GROUP BY 列名 [HAVING 条件表达式] [WITH ROLLUP]

    • 列名：作为分组基准的字段
    • HAVING 条件表达式：过滤分组后的数据
    • WITH ROLLUP：在所有记录的最后加上一条记录，显示select查询时聚合函数的统计和计算结果

  • 使用

    • group by + group_concat()

      • group_concat():统计每个分组指定字段的信息集合，每个信息之间使用逗号进行分割。

    • group by + 聚合函数的使用

    • group by + having的使用

    • group by + with rollup的使用

• 连接查询

  • 内连接

    • 概述

      • 取两表交集

    • 语法

      select 字段 from 表1 inner join 表2 on 表1.字段1 = 表2.字段2

  • 左连接

    • 概述

      • 左表为主根据条件查询右表数据，如果根据条件查询右表数据不存在使用null值填充

    • 语法

      select 字段 from 表1 left join 表2 on 表1.字段1 = 表2.字段2

  • 右连接

    • 概述

      • 以右表为主根据条件查询左表数据，如果根据条件查询左表数据不存在使用null值填充

    • 语法

      select 字段 from 表1 right join 表2 on 表1.字段1 = 表2.字段2

  • 自链接

    • 概述

      • 自连接查询就是把一张表模拟成左右两张表，然后进行连表查询。

• 子查询

  • 概述

    • 一个select语句中嵌入另一个select语句，被嵌入的select语句称为子查询语句。

  • 主子关系

    • 1.子查询嵌入到主查询中
    • 2.子查询是辅助主查询，要么充当条件，要么充当数据源。
    • 3.子查询可以独立存在的语句，是一条完整的select语句。

• 外键约束

  • 对已有字段添加

    alter table students add foreign key(cls_id) references classes(id);

  • 创表时添加外键

    foreign key(s_id) references school(id)

  • 删除外键

    • 获取名称

      show create table teacher;

    • 删除

      alter table teacher drop foreign key 外键名;

• 数据库设计

  • 三范式

    • 1NF

      • 列的原子性，列不能再分

    • 2NF

      • 满足1NF，且必须包含一个主键，非主键字段必须完全依赖于主键，不可部分依赖。

    • 3NF

      • 满足2NF，非主键列必须直接依赖主键，不能存在传递依赖。

  • E-R模式

    • 列出 实体，属性，关系图。

高级使用

• 事务

  • 概述

    • 不可分割的工作执行单元。

  • 特性

    • 原子性
    • 一致性
    • 隔离性
    • 持久性

  • 操作

    • 开启事务

      begin;

      start transaction;

    • 调整非自动提交（默认自动提交）

      set autocommit = 0

      • 先保存到mysql服务端缓存文件中

    • 提交事务

      commit

      • 将本地缓存文件中数据提交到物理表中

    • 回滚事务

      rollback;

      • 放弃本地缓存文件中的缓存数据，表示回到开始事务前的状态

• 索引

  • 一般索引

    • 概述

      • 如书的目录，保存着数据表里所有记录的位置信息

    • 操作

      • 查看表中已有索引

        show index from 表名;

      • 创建索引

        alter table 表名 add index 索引名[可选](列名, …)

        • 主键列会自动创建索引
        • 索引名不指定，默认使用字段名

      • 删除索引

        alter table 表名 drop index 索引名

  • 联合索引

    • 概述

      • 一个索引覆盖表中两个或多个字段，一般用在多个索引一起查询。

    • 优点

      • 减少磁盘开销

    • 注意

      • 最左原则， 必须包括左端索引

    • 操作

      alter table teacher add index (name,age);

• pymysql

  • 安装

    sudo pip3 install pymysql

  • 导包

    import pymysql

  • 连接对象

    • 创建连接对象

      conn=connect(参数列表)

      • 参数host：连接的mysql主机，如果本机是’localhost’
      • 参数port：连接的mysql主机的端口，默认是3306
      • 参数user：连接的用户名
      • 参数password：连接的密码
      • 参数database：数据库的名称
      • 参数charset：通信采用的编码方式，推荐使用utf8

    • 关闭连接

      conn.close()

    • 提交数据

      conn.commit()

    • 撤销数据

      conn.rollback()

    • 数据递交数据库

      conn.commit()

    • 数据回滚数据

      conn.rollback()

  • 获取游标对象

    • 目的

      • 为了执行sql语句

    • 语法

      cur =conn.cursor()

  • 执行sql

    cursor.execute(sql)

  • 获取查询结果集中一条数据

    cur.fetchone()

  • 获取查询结果集中所有数据

    cur.fetchall()

  • 关闭游标

    cur.close()

Redis

NoSQL

• 概述

  • 泛指非关系型数据库
  • 键值对形式存储数据
  • 不支持SQL语法
  • NoSQL没有通用语言，每种nosql数据库都有自己的api和语法。

• 产品

  • Redis
  • Mongodb
  • Hbase hadoop
  • Cassandra hadoop

• 与SQL数据库的比较

  • NoSQL基本不支持事务，sql支持
  • sql适合存储关系复杂的数据

Redis特性

• 支持数据持久化，可将内存中数据保存在磁盘中，重启仍有效。
• 支持数据备份，master-slave模式的数据备份
• 数据类型除简单的kv，还支持list，set，zset，hash

Reids优势

• 读写速度块
• 数据类型丰富
• 所有操作具有原子性
• 支持publish/subscribe，通知key过期等特性。

Redis安装/配置

• 安装参考百度或讲义

• 配置文件

  • 路径

    • /etc/redis/

  • bind

    • 绑定ip

  • port

    • 端口

  • daemonize yes

    • 是否守护进程，yes是

  • dbfilename dump.rdb

    • 数据文件

  • dir /var/lib/redis

    • 数据文件存储路径

  • logfile

    • 日志文件

  • database 16

    • 数据库 默认16

  • slaveof

    • 主从复制 ，类似于双机备份。

Redis操作

• 服务端

  • redis-server --help

  • 查看redis服务器进程

    ps aux|grep redis

  • 杀死redis服务

    sudo kill -9 pid

  • 指定加载配置文件

    sudo redis-server /etc/redis/redis.conf

• 客户端

  • redis-cli --help

  • 连接redis

    redis-cli

  • 运行测试命令

    ping

  • 切换数据库

    select 数据库号（0-15）

  • 强制关闭redis导致无法持久化问题

    • 错误提示

      MISCONF Redis is configured to save RDB snapshots, but is currently not able to persist on disk. Commands that may modify the data set are disabled. Please check Redis logs for details about the error.

    • 解决方法

      • 运行config set stop-writes-on-bgsave-error no　命令后，关闭配置项stop-writes-on-bgsave-error解决该问题。

• 数据类型及操作

  • 键

    • 查找键

      keys *

      keys a*

    • 判断键是否存在

      exists key

    • 查看键对应的value类型

      type key

    • 删除键及对应的值

      del key1 key2 …

    • 设置过期时间

      expire key seconds

    • 查看有效时间

      ttl key

  • string

    • 概述

      • 在redis中式二进制型的，可接收任何格式数据。
      • string类型的value最多可容纳的数据长度未512M

    • 添加/修改

      • 设置单值

        set key value

        • 键存在则修改，没有则添加

      • 设置有效时间

        setex key seconds value

        • seconds 以秒为单位

      • 多值设置

        mset key1 value1 key2 value2 …

      • 追加

        append key value

    • 获取

      • 单键获取

        get key

        • 不存在返回 nil

      • 多键获取

        mget key1 key2 …

  • hash

    • 概述

      • 用于存储对象，对象结构为属性，值，值的类型为string

    • 增改

      • 设置单个属性

        hset key field value

      • 设置多个属性

        hmset key field1 value1 field2 value2

    • 获取

      • 获取指定键所有属性

        hkeys key

      • 获取一个属性的值

        hget key field

      • 获取多个属性值

        hmget key field1 field2 …

      • 获取所有属性值

        hvals key

    • 删除

      • 删除整个hash键值

        del 键

      • 删除属性

        hdel key field1 field2 …

  • list

    • 概述

      • 列表的元素类型为string
      • 按照插入顺序排序

    • 增加

      • 在左侧插入

        lpush key value1 value2 …

      • 在右侧插入

        rpush key value1 value2 …

      • 在指定元素前后加入

        linsert key before或after 现有元素 新元素

    • 获取

      • 指定起止截取

        lrange key start stop

      • 查看列表的所有元素

        lrange key 0 -1

    • 修改

      • 指定位置修改

        lset key index value

    • 删除

      • 删除指定元素

        • 将列表中前count次出现的值为value的元素移除

          lrem key count value

          • count >0 从头往尾移除
          • count<0 从尾往头移除
          • count=0 移除所有

  • set

    • 概述

      • 无序集合，元素类型为string，不重复
      • 集合没有修改操作

    • 增加

      • 添加元素

        sadd key member1 member2 .

    • 获取

      • 返回左右元素

        smembers key

    • 删除

      • 删除指定元素

        srem key

  • zset

    • 概述

      • 有序集合，元素具有唯一性，元素类型为string
      • 每个元素都会关联⼀个double类型的score，表示权重，通过权重将元素从⼩到⼤排序
      • 没有修改操作

    • 增加

      zadd key score1 member1 score2 member2 .

    • 获取

      • 根据索引获取

        zrange key start stop

      • 根据score获取

        zrangebyscore key min max

      • 返回元素的权重值

        zscore key member

    • 删除

      • 删除指定元素

        zrem key member1 member2 …

      • 删除权重在指定范围的元素

        zremrangebyscore key min max

• 与python交互

  • 调用模块

    from redis import StrictRedis

  • 创建对象

    sr = StrictRedis(host=‘localhost’, port=6379, db=0)

  • 对象方法与redis命令类似，这里不详记

    sr.set(‘name’,‘itcast’)

• 搭建主从与集群

  • 理解原理即可

  • 主从

    • master-slave
    • master 负责写
    • slave 负责读
    • 一主多从

  • 集群

    • 集群是一组相互独立的、通过高速网络互联的计算机，它们构成了一个组，并以单一系统的模式加以管理。
    • 提高可用性和可缩放行，防止主服务器因同时访问量过多而挂掉。
    • 哈希槽算法(hash slot) 分配节点
    • 存活主节点数小于总结点一半，整个集群无法提供服务
    • 主节点数必须大于等于3个