一、数据库基础#

mysql 登录到mysql终端 默认情况：root（mysql服务的用户） 默认没有密码

退出mysql终端 exit quit

select 查询 select version(); 查询版本号 select user(); 查询当前登录的用户 select database(); 查询当前正在使用的数据库

show 显示 show databases; 显示所有的数据库 show tables; 显示当前库下面的表

use mysql 使用mysql数据库

数据库中是由一张张表组成的

mysqladmin mysql管理员命令

mysqladmin -uroot -p password "moershi" 修改密码

有密码登录 mysql -uroot -p密码（不安全） mysql -uroot -p （安全） 输入密码

修改密码 mysqladmin -uroot -p原始密码 password "新密码'

mysql user 表：用来存储mysql账号的表 select User,authentication_string from mysql.user;

总结 忘记密码

1. 修改mysql配置文件 vim /etc/my.cnf.d/mysql-server.cnf skip-grant-tables 不需要密码验证

2. 重启mysql服务 systemctl restart mysqld

3. 无密码登录 mysql

4. 把密码修改为空 update user set authentication_string="" where user="root";

5. 修改mysql配置文件 vim /etc/my.cnf.d/mysql-server.cnf 去掉skip-grant-tables

6. 重启mysql服务 systemctl restart mysqld

7. 无密码登录 mysql

8. 在mysql终端里面设置密码 alter user root@"localhost" identified by "NSD123456...a"

数据导入 mysql -uroot -pNSD123456...a < moershi.sql

查看表结构 desc 表名

多个判断条件 and 并且（多个条件同时满足） or 或（多个条件满足一个就可以）

如果有括号 先执行括号里面的，在过结果对括号外面的进行过滤

为空 字符串空 “” 用 !=或= 来判断 真空
is null 等于空 is not null 不等于空

合并表头 concat(..)

distinct 去重，对行的去重（每列值都相同的才去重）

二、数据库函数#

字符串函数 用于处理字段类型为char或varchar的字段内容 一个字母占用1个字节 length() 求字节长度 一个汉字占用3个字节（字符编码格式utf-8） 一个汉字占用2个字节（字符编码格式gbk） char_length() 求字符长度 一个字母一个字符 一个汉字一个字符

针对英文字母 upper ucase 转大写 lower lcase 转小写 substr(变量,位置,长度) 字符串截取

instr(变量,"内容") 求内容在变量值的什么位置 trim() 去掉两边的空格

聚合函数：用来统计数据的 sum avg min max count

三、单表增删改查#

比如说 一个班级有男生和女生（男生30，女生20） 根据一个特定数据类型来统计（sum,avg,min,max,count） select sex,count(1) from 表 group by sex sex count(1) 男 30 女 20

group by 用来分组的关键字

排序 order by desc 降序排序（从大到小） asc 升序排序（从小到大）默认情况 逻辑是 先查询结果 然后对结果进行排序

查看2015年1月10号员工编号小于10的工资总额 select basic+bonus from salary where employee_id<10 and date='20150110'

分组后条件过滤 先分组查询结果 然后对分组的结果（通过聚合函数获得的结果进行条件过滤） having

查找部门总人数少于10人的部门编号及人数 select dept_id,count(1) 部门人数 from employees group by dept_id;

select dept_id,count(1) 部门人数 from employees group by dept_id having 部门人数<10;

limit 起始条数-1，每页显示总行数 limit 0 10 limit [0] 每页显示总行数, 默认从0开始 假设每页显示10条数据，第一页，第二页… limit 0,10 limit 10,10 limit 20,10 …

新增数据 insert into 表名(列名1，列名2..) values(a,b,...) insert into 表名 values(a,b,c,d,e) 批量新增 insert into 表名 values(a,b,c,d,e),(1,2,3,4,5)....

修改数据 update 表名 set 列名1=值, 列名2=值 where 条件

update user set homedir=concat("/home/",name)

删除数据 delete from 表 where 条件

四、综合查询#

两个表中有共同的属性就可以关联查询 ER图 表与表直接的关系图

内连接 inner join

查询每个员工所在的部门名 select name,dept_name from employees inner join departments on employees.dept_id=departments.dept_id;

查询员工编号8 的 员工所在部门的部门名称 select name,dept_name from employees inner join departments on employees.dept_id=departments.dept_id where employee_id=8;

select e.dept_id,name,dept_name from employees e inner join departments d on e.dept_id=d.dept_id where employee_id=8;

查询11号员工的名字及2018年每个月总工资 select s.employee_id,name,basic+bonus as total from salary s inner join employees e on s.employee_id=e.employee_id where year(date)=2018 and s.employee_id=11;

查询每个员工2018年的总工资(员工姓名，员工id,总工资) sum求和，根据员工分组 select s.employee_id,name,sum(basic+bonus) as total from salary s inner join employees e on s.employee_id=e.employee_id where year(date)=2018 group by s.employee_id;

查询每个员工2018年的总工资，按总工资升序排列 select s.employee_id,name,sum(basic+bonus) as total from salary s inner join employees e on s.employee_id=e.employee_id where year(date)=2018 group by s.employee_id order by total ;

查询2018年总工资大于30万的员工，按2018年总工资降序排列 select s.employee_id,name,sum(basic+bonus) as total from salary s inner join employees e on s.employee_id=e.employee_id where year(date)=2018 group by s.employee_id having total > 300000 order by total ;

左连接查询例子：输出没有员工的部门名 select dept_name,name from departments d left join employees e on d.dept_id=e.dept_id;

统计每个部门的员工人数 select dept_name,count(e.name) 人数 from departments d left join employees e on d.dept_id=e.dept_id group by dept_name;

输出2018年基本工资的最大值和最小值 select "最高工资" 工资级别, max(basic) 基本工资 from salary where year(date)=2018 union select "最低工资" 工资级别, min(basic) 基本工资 from salary where year(date)=2018;

输出2018年1月10号 基本工资的最大值和最小值 select date,"最高工资" 工资级别, max(basic) 基本工资 from salary where date='2018-1-10' union select date,"最低工资" 工资级别, min(basic) 基本工资 from salary where date='2018-1-10';

select employee_id , name , birth_date from employees where employee_id <= 5 union all select employee_id , name , birth_date from employees where employee_id <= 6;

查询运维部所有员工信息 select dept_id from departments where dept_name ="运维部"

select * from employees where dept_id=(select dept_id from departments where dept_name ="运维部");

查询人事部2018年12月所有员工工资 select dept_id from departments where dept_name ="人事部"

select employee_id from employees where dept_id= (select dept_id from departments where dept_name ="人事部")

select * from salary where year(date)=2018 and month(date)=12 and employee_id in (select employee_id from employees where dept_id= (select dept_id from departments where dept_name ="人事部"))

查询人事部和财务部员工信息 select dept_id from departments where dept_name in ("人事部","财务部")

select * from employees where dept_id in (select dept_id from departments where dept_name in ("人事部","财务部"));

查询2018年12月所有比100号员工基本工资高的工资信息 select basic from salary where employee_id =100 and year(date)=2018 and month(date)=12

select * from salary where year(date)=2018 and month(date)=12 and basic > (select basic from salary where employee_id =100 and year(date)=2018 and month(date)=12)

查询部门员工总人数比开发部总人数少 的 部门名称和人数

select dept_id from departments where dept_name ="开发部"

select count(1) from employees where dept_id = (select dept_id from departments where dept_name ="开发部");

select dept_id,count(1) total from employees where dept_id is not null group by dept_id having total < (select count(1) from employees where dept_id = (select dept_id from departments where dept_name ="开发部"));

查询3号部门 、部门名称 及其部门内 员工的编号、名字 和 email

select dept_name,employee_id,name,email from ( select dept_name,e.* from employees e inner join departments d on e.dept_id=d.dept_id ) tmp_table where dept_id=3

查询每个部门的人数： dept_id dept_name 部门人数 select count(1) from employees where dept_id= ?

select *,(select count(1) from employees where dept_id= d.dept_id) 部门人数 from departments d ;

五、表结构管理#

drop database 数据库名

总结 建库的时候要加 if not exists 删除的时候要加 if exists

字段熟悉 名字，类型，长度，约束，默认值 char(11) 是固定长度 a varchar(100) 不固定长度 a

修改表 alter table 表名 rename 重命名 drop 字段名 add 字段名 after 字段名 在某个字段后面添加 modify 修改字段熟悉 change 改表头的名字

复制表 create table 表名 select * from 表名

如何看建表语句 desc 查看表结构 show create table t2

数值类型 整形 tinyint unsigned 最大255 可以是小数，四舍五入取整 int long 长整形 bigint 超长整形 浮点

枚举类型 enum 单项 set 多选

时间日期类型 date 日期 time 时间 datetime 日期时间 year 年份

primary key 主键 auto_increment 自增长

六、主键外键索引#

255.255.255.255

truncate 删除数据，会把AUTO_INCREMENT设置为1

explain 执行计划，用来验证一个sql语句有没有用到索引

七、数据导入导出与用户管理#

grant 权限 on 库.表 to 用户 all 拥有所有权限 *.* 所有库所有表 数据库名.* 某个库的所有表 数据库名.表名 ：针对某个有权限

revoke 撤销权限 通过管理员去修改普通用户的密码 set password for 用户="密码"

drop: 删除用户，删除表，删除数据库，删除表中字段

八、备份和恢复#

优点：灵活 mysqldump 主要是把数据导出成一个sql脚本 针对某一个表 mysqldump 库名 表1 表2 > xxx.sql 针对某一个库 mysqldump -B 库1 库2 > xxx.sql 针对所有库 mysqldump -A > xxx.sql

缺点：

1
mysqldump【mysql-当普】 备份和恢复数据时会锁表，锁表期间无法对表做写访问
2
mysqldump【mysql-当普】适合备份数据量比较小的数据或在数据库服务器访问量少的时候备份。

增量备份 当前的备份是基于上一次备份做增量的备份 一般第一次备份要完全备份 第一次：全量—>增加了a 第二次: 备份a--->增加了b 第三次: 备份b--->增加了c 第四次: 备份c--->增加了d 增量备份做数据恢复 全量<---备份a 全量<---备份b 全量<---备份c 最终 全量(abc)

差异备份 当前的备份是基于第一次备份做增量的备份 一般第一次备份要完全备份 第一次：全量—>增加了a 第二次: 备份a--->增加了b 第三次: 备份ab--->增加了c 第四次: 备份abc--->增加了d 全量<—备份abc 最终 全量(abc)

增量备份

• 定义：增量备份只备份自上一次备份（无论是完全备份、增量备份还是差异备份）以来发生变化的数据。

• 工作原理：首先需要一个完整的备份（基础备份），然后每次增量备份都只备份相对于上一次备份后变化的数据。

• 优点：备份速度快，占用空间小，因为每次只备份变化的数据。

• 缺点：恢复数据时，需要先恢复完整备份，然后按照时间顺序依次恢复所有的增量备份，直到指定的时间点。恢复过程复杂且耗时，如果中间任何一个备份损坏，可能导致整个恢复链失效。

• 适用场景：数据量大，备份窗口短，但对恢复时间要求不高的场景。

  当前的备份是基于上一次备份做增量的备份 一般第一次备份要完全备份 第一次：全量—>增加了a 第二次: 备份a--->增加了b 第三次: 备份b--->增加了c 第四次: 备份c--->增加了d

  增量备份做数据恢复 全量<---备份a 全量<---备份b 全量<---备份c 最终 全量(abc)

差异备份

• 定义：差异备份备份自上一次完整备份以来所有发生变化的数据。

• 工作原理：首先需要一个完整备份，然后每次差异备份都会备份自上次完整备份以来所有变化的数据。因此，第二次差异备份会包含第一次差异备份的内容，第三次会包含第一次和第二次的内容，以此类推。

• 优点：恢复时只需要完整备份和最近一次的差异备份，恢复过程相对简单。

• 缺点：随着时间推移，差异备份的大小会越来越大，因为每次都要备份自完整备份以来的所有变化。备份时间可能会逐渐增加。

• 适用场景：数据量变化不大，且希望恢复过程相对简单的场景。

  当前的备份是基于第一次备份做增量的备份 一般第一次备份要完全备份 第一次：全量—>增加了a 第二次: 备份a--->增加了b 第三次: 备份ab--->增加了c 第四次: 备份abc--->增加了d 全量<—备份abc 最终 全量(abc)

对比总结：

• 备份内容：增量备份只备份上一次备份后的变化，而差异备份备份自上次完整备份后的所有变化。
• 备份空间和速度：增量备份占用空间小，速度快；差异备份随着时间推移，占用空间和备份时间都会增加。
• 恢复过程：增量恢复需要完整备份和所有增量备份，步骤多，易出错；差异恢复只需要完整备份和最近一次差异备份，相对简单。

PERCONA【帕扣娜】 xtrabackup【xtra-拜克阿普】是一款强大的在线热备份工具，备份过程中不锁库表，适合生产环境。支持完全备份与恢复、增量备份与恢复、差异备份与恢复。

九、数据库主从同步#

1
   create database db1;      create table db1.user(name char(10));         insert into db1.user values("jim");

157------------------------339--------------------------------------------535---------------------------------------809

10、数据库读写分离#

mycat 本身一个Java应用程序 需要Java运行的环境 jdk

如果mycat启动失败，可以参考 https://blog.csdn.net/weixin_46897073/article/details/120678560

11、数据库分库分表#

分表的策略 垂直分表（不需要技术含量，会涉及到原来代码修改） 50列，把一些表头拆分另外一张表 拆分的表跟原表做关联关系 水平分表（需要技术来实现） 一张表（500万），现在数据远超过了500万

平摊 user1 （id%3=0） user2 （id%3=1） user3 （id%3=2）

12、redis数据类型#

字符串类型 新增数据 set key value 查询数据 get key 批量新增 mset key1 value1 key2 value2 key3 value3... 批量获取 mget key1 key2 key3...

有效期 px(毫秒) ex (秒)

不存在就是写入 NX 覆盖 XX

递增 incr key 每次+1 incrby key n 每次+n 递减 decr key 每次-1 decrby key n 每次-n

尾部追加或者拼接 append key " 值"

strlen key 求长度

求范围 0123456 ABCDEF -7-6-5-4-3-2-1

keys
* 任意 ? 一个代表一个字符

redis 默认就有16个数据库 0-15 默认0号库 select 选择数据库 数据库之间有隔离性

move key 把某一个key移动到其他库

exists 判断一个key是否存在 1 存在 0 不存在

先有key, 后设置过期时间 expire ttl 查看一个数据存储剩余时间 -1 永久不过期 -2 已经过期或者不存在 正数：剩余的时间

type 查看某个key是属于哪个数据类型

del key1 key2... 删除一个key

flushdb 删除本库的所有的key

flushall 删除所有库的key

list数据类型 lpush 存放数据，从左往右放数据 llen 求数据库数量 lindex 下标 获取某个下标的值 lrange 根据下标范围取出数据 lset 根据下标修改数据

lpush 看做生产者 lpop 看做消费者

rpush 存放数据，从右往左放数据 linsert 插入

散列类型

key : {k1:v1,k2:v2,k3:v3} stu1:{name:张三，age: 12, class: yjs1} stu2:{name:李四, age:16, class: yjs2}

hset user1 name zs age 15

获取数据 hget 只能获取一个 hmget 获取多个 hdel 删除一个元素 hlen 获取个数

集合 无序集合 sadd 向集合中添加元素 smembers 列出集合的元素 sismember key 元素，判断一个元素是否存在，存1 不存在0 scard 获取原生的个数

集合运算 交集 sinter 差集 sdiff 合集 sunion

srandmember 随机获取元素

有序集合 key: 数字1 字符串1 数字2 字符串2 ... 数字1作用用来区分大小 ，通过数字来排序，只有能排序才称之为有序 zadd 添加集合元素 zcard 统计个数 zrange 根据下标获取数据 withscores 显示数值 zrangebyscore 根据数值范围获取数据 zincrby 增加数值 zcount 根据数值范围求个数 zrem 删除一个元素 zrank 获取排名 升序排列 zrevrank 获取排名 降序排列

13、redis集群#

RDB持久化 #save 3600 1 3600秒时，这个只有1条数据更新，才去保存到磁盘中 #save 300 100 300秒时，有100条数据更新， 去保存到磁盘中 #save 60 10000 60秒时， 有1万条数据更新， 去保存到磁盘中

如果你在1分钟出现500条数据，这个要到300秒的时候才去保存， 如果在1分钟到5分钟之间出现了宕机故障，这500条数据可能会丢失 RDB持久化，不是实时保存，有间隔性的保存，实时保存会降低性能 优点，性能高 缺点，很难保证数据不丢失

save 120 10

通过正规的方式停止服务，会做一次持久化 systemctl stop redis —>会产生rdb文件 poweroff --> systemctl stop redis —>会产生rdb文件

systemctl start redis—>读取rdb文件--->内存

AOF文件 redis命令（增删改的命令）类似于mysql的binlog 实时去写日志，一般情况是不会丢数据库。性能低 恢复数据的时候，去重新执行AOF中的命令。恢复比较慢 默认情况下是不开启AOF config set appendonly yes 开启AOF

通过命令的方式去修改redis配置是不需要重启服务的

分片高可用集群 默认最少6台机器 优点：能实现高可用集群方案，可以自主切换主从，从节点可以升级从为主。 缺点：需要的服务器比较多，造成服务器的成本增加

自定义主从结构 优点：介绍成本，配置灵活，实现了读写分离，提高性能 缺点：当主服务宕机了，不能实现写数据库，无法保证高可用，从节点不能升级为主

哨兵服务主从结构 优点：能实现高可用集群方案，通过哨兵监控，从节点可以升级从为主。 缺点：如果哨兵是一台，哨兵存在单节点故障，如果哨兵部署多台，增加成本，主从切换需要延迟，在延迟的时候出现数据不一致。