社区

查看所有编码格式

SHOW VARIABLES LIKE 'character\\_set\\_%';

show engines；：查看存储引擎

#查看系统变量show global variables;

#查看会话变量show session variables;

mysql系统

show databases；：查看所有库

查看

创建库并指定字符序和字符编码：create datebase choose character set utf8mb4 collate utf8mb4_unicode_ci;

插入

删除库，数据也会删除

drop database demo;

删除

修改

use 库名;

切换库

库

show tables；：查看所有表

查

复制表数据

insert  into t_name_1 select * from t_name_2;

增

修改表名：rename table t_student_info to t_student;

改

删除表数据：delete from demo;

删除表：drop table name;

删除表的所有数据：truncate table t_name;

删

复制的是表的所有数据，包括表的结构，和表的数据

复制表结构

create table t_name_1 select * from t_name_2;

添加表（字段）结构： alter table t_student_tmp add name varvhar(20) not null comment '注释';

添加约束(not null除外)：alter table t_student_info add constraint unique_con unique(stuno);

添加索引：create index sname_index on t_student_info(sname);

删除表结构，数据也会删除

drop table demo;

删除表字段：alter table t_name drop name;

删除约束(删除唯一约束的时候需要用index来标注删除)：alter table t_student_info drop index unique_con;

修改字段名字：alter table t_name change name1 name2 int(10) not null comment '注释';

修改字段类型：alter table t_student_info modify age int(4) comment '年龄'

查看表结构

desc 表名；

表结构，表字段

表

复制

删除表所有数据： delete from t_name;

修改数据：update t_student_info set age=29 where sname='李四';

指定查询：select * from t_student_info where id = 222;

基本查询 where

select * from t_score order by score;

select * from t_score order by score asc;

升序

select * from t_score order by score desc;

降序

排序查询：order by

以course_no来分组，查询每一组的score的和and组名

分组查询：group by

select * from t_teacher t join t_department d on t.department = d.dept_no;

join：查询出所有能够对应的上的数据

select * from t_teacher t left join t_department d on t.department = d.dept_no;

left join：以左表为基准，查询出左表所有的数据右表没有的，则填充null

select * from t_teacher t right join t_department d on t.department = d.dept_no;

left join：与left join正好相反，以右表为基准，查询出左表所有的数据右表没有的，则填充null

select t.* from t_teacher t inner join t_department d on d.dept_no = t.department where d.dept_not_no = 'lgn';

查询所有理工农的老师

查询分数表中所有年龄大于等于18岁的学生的详细(姓名，年龄，性别)信息和分数以及对应的课程

连表查询：join

语法：select * from t_name limit' 从哪里开始'，'多少条数据';

切片：limit

select distinct(stuno) from t_score;

去重查询：distinct

select count(*) from t_score;

统计查询：count

select * from t_teacher where tname like '张%';

模糊查询：like

将两张表竖着排在一起打印

需要两张表每条数据查询的数量一致（以上两表都为三个）

聚合查询：union

选择分支查询： case when

运算符

数据

delimiter $$create procedure name(参数（可写可不写）)begin  mysql命令end $$delimiter ;

创建

drop procedure name;

改：删除 + 创建

存储过程：删除其中id为100的学生信息，并且更新教师号为T003的老师的性别为 “女”

实战

存储过程体·

开始声明（我要创建触发器了）：delimiter $$

after后面的内容：触发条件

t_teacher每插入一条内容执行一次：insert on t_teacher for each row

创建语法：create trigger inster_teacher_logs after insert on t_teacher for each row

执行的命令

声明结束：delimiter ;

drop trigger insert_teacher_logs;

触发器

基础

create user 用户名 identified by '密码';

创建用户

给用户yunt_teacher表的查询权限：GRANT SELECT ON  t_teacher TO yun;

授权

数据安全

在同一个库里面备份表：create table t_student_info_tmp  select * from t_student_info;

注意：使用SELECT ... INTO OUTFILE时，MySQL服务器必须有写权限到指定的文件路径。导出的文件必须不存在，因为MySQL不会覆盖现有文件。这个语句不会在客户端生成文件，而是在MySQL服务器上生成文件

所以，在运行前，先得看一下mysql默认可以导出的文件路径在哪里show variables like 'secure_file_priv';

也可在my.cnf的[mysqld]下更改secure_file_priv=\"...\"的值

注意

load data infile 文件路径

将数据导入到数据库

备份表为.txt文件

备份表

整库备份：mysqldump -u root -p mydatabase > mydatabase_backup.sql

整库导入：source mysql库文件路径

备份库

数据备份

在[mysqld]下加入：log-bin=mysql-binserver-id=101binlog_checksum=none

1.修改my.cnf配置文件

systemctl restart mysqld

2.重启mysql

1.登录MySQL：mysql -u root -p

2.创建slave用户：# CREATE USER 'slave'@'%' IDENTIFIED BY 'abc123';

grant REPLICATION SLAVE on *.* to 'slave'@'%';

3.给予slave用户对所有库和所有表得读的权限

alter user 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'abc123';

4.更改用户slave的登录密码验证方式为使用MySQL的原生密码认证插件进行身份验证，密码为abc123

5.刷新权限：flush privileges;

6.查看master状态：show master status;

3.设置mysql

在[mysqld]下加入：server-id=101

2.登录mysql ：mysql -u root -p关闭slave：stop slave;

4.启动主从复制：start slave;5.查看状态是否连接成功：show slave status;

4.设置从服务器

mysql主从-配置

原子性

一致性

持久性

隔离性

数据库事务的四大特性

read：读（没用，锁了也能查询数据，因为读锁是共享锁，大家都能看）write：写

锁：lock table name read / write;

解锁：unlock tables;

读写锁：lock

数据分区：根据数据的访问模式或业务逻辑将数据表分区，可以提高查询效率和管理灵活性。

数据分页：对于大数据量的查询，使用LIMIT来限制返回的数据量，以减少网络传输和处理时间。

分库，分区，分页

join

连表查询

创建索引：在经常作为查询条件（WHERE子句）、排序（ORDER BY子句）和分组（GROUP BY子句）的列上创建索引可以显著提高查询效率。

避免过多索引：虽然索引可以提高查询速度，但它们也会减慢写操作（INSERT、UPDATE、DELETE），并占用更多的磁盘空间。

索引维护：定期检查和维护索引，确保它们保持高效。

创建索引

读写分离

增加最大连接数：根据需要调整max_connections的值，以确保数据库能够处理足够的并发连接。

连接和并发

mysql查询的优化方式

mysql：磁盘存储型数据库

缓存穿透是指查询一个不存在的数据，由于缓存中没有数据，所以这个查询请求会直接穿过缓存层，到达数据库层，从而增加了数据库的压力。这种情况通常发生在恶意攻击或者系统设计不当的情况下。

1.恶意攻击：攻击者有意请求不存在的数据，以使得系统频繁访问数据库，造成拒绝服务攻击。2.系统设计不当：系统设计时未对查询参数进行校验，导致查询了不存在的数据。

原因

1.参数校验：在查询缓存之前，先对请求的参数进行合法性检查，如过滤非法字符、判断参数范围等。2.布隆过滤器：在缓存层前使用布隆过滤器，对所有可能的数据建立一个布隆过滤器，用于快速判断一个请求的数据是否存在于数据库中。3.缓存空值：当查询的数据在数据库中不存在时，也将这个空值缓存起来，并设置一个较短的过期时间，这样可以减少后续请求对数据库的访问。4.增强ID复杂度：避免被猜测ID规律，增加ID的复杂度和随机性。5.做好数据的基础格式校验：确保传入的数据格式符合预期。6.加强用户权限校验：确保只有有权限的用户才能访问数据。

解决方案

缓存穿透

缓存雪崩是指在某一时刻，缓存中大量的缓存数据同时失效或过期，导致大量的请求直接打到数据库或后端系统，从而造成数据库负载激增，甚至导致数据库宕机或系统崩溃的现象。

1.大量缓存同时设置相同的过期时间：如果在某一时刻设置了大量的缓存数据，并为它们都设置了相同的过期时间，那么在未来的某一时刻，这些缓存数据都会同时失效。2.Redis宕机：如果因为某种原因，Redis服务器突然宕机，那么所有的缓存数据都会消失，导致所有的请求都直接访问数据库。3.误删缓存数据：如果由于某种原因，大量的缓存数据被误删除，那么也会引发缓存雪崩。

1.设置随机过期时间：为了防止大量的缓存同时失效，可以为每个缓存设置一个随机的过期时间，这样就可以确保不会有太多的缓存同时失效。2.使用多级缓存：可以在客户端和数据库之间设置多级缓存，如本地缓存、Redis缓存等。当Redis缓存失效时，可以先查询本地缓存，如果本地缓存也没有，再查询数据库。3.实现缓存数据的自动刷新：通过定时任务或事件触发等方式，在缓存数据即将过期时，自动从数据库中查询最新的数据并更新到缓存中。4.限流和熔断：在系统中加入限流和熔断机制，当数据库请求到达一定的阈值时，直接拒绝部分请求，以保护数据库不被过度访问。5.监控和报警：建立监控系统，实时监控缓存和数据库的状态，一旦发现异常情况，立即报警并采取相应的措施。

缓存雪崩

什么是穿透，雪崩

redis安装部署文档：https://www.cnblogs.com/hunanzp/p/12304622.html

1.需要的环境：yum install gcc tcl

2.上传安装包 && 解压缩：tar -zxvf name.tar.gz

3.编译，编译安装：make && make install

4.启动，cd到redis目录下，有redis.conf即可cd redis/redis-server redis.conf

redis安装部署

本地回环地址：127.0.0.1

远程连接，主从备份，哨兵模式，需要

所有地址：0.0.0.0

启动ip：bind 

默认端口：6379

端口：port

保护模式：protected-mode

yes

no

是否在后台运行：daemonize

备份文件地址：logfile

登录密码：requirepass

主从备份主机（master）密码：masterauth

主机ip 端口

主从备份主机参数（ip 端口）：replicaof

关于redis.conf的参数

开机自动关闭：systemctl disable firewalld

关闭防火墙：systemctl stop firewalld

bind：0.0.0.0port：6379protected-mode：nodaemonize：yeslogfile：./redis.logrequirepass：abc123masterauth：abc123

vim redis.conf

1.主服务器配置文件需要修改的参数

bind：0.0.0.0 port：6379 protected-mode：no daemonize：yes logfile：./redis.log requirepass：abc123masterauth：abc123replicaof 192.168.169.100 6379

2.从服务器配置文件需要修改的参数

1.在redis目录下

2.启动：redis-server redis.conf

3.登录：redis-cli

4.密码验证：auth 密码

主服务器（master）

# Replicationrole:slavemaster_host:10.0.0.23master_port:6379master_link_status:upmaster_last_io_seconds_ago:1master_sync_in_progress:0slave_read_repl_offset:715458slave_repl_offset:715458slave_priority:100slave_read_only:1replica_announced:1connected_slaves:0master_failover_state:no-failovermaster_replid:04004ddac58721497e67a6897469b5406072dd7bmaster_replid2:0000000000000000000000000000000000000000master_repl_offset:715458second_repl_offset:-1repl_backlog_active:1repl_backlog_size:1048576repl_backlog_first_byte_offset:202094repl_backlog_histlen:513365

从服务器（slave）

主从信息对比

5.查看主从配置信息：info replication

3.启动（重新启动）

redis配置主从备份

1.关闭防火墙：systemctl stop firewalld

1.删除哨兵模式配置文件：rm -rf sentinel.conf

port 16379# 本机端口bind 0.0.0.0# 本机所有ipprotected-mode yes# 是否开启保护模式daemonize yes# 是否在后台运行logfile \"./sentinel.log\"# 哨兵模式日志文件路径sentinel monitor mymaster 192.168.169.100 6379 2# 主服务器ip redis-server运行端口sentinel auth-pass mymaster abc123# 主服务器redis-server的密码sentinel down-after-milliseconds mymaster 30003000毫秒检查一次sentinel resolve-hostnames yes是否解析主机名

2.编辑哨兵模式配置文件：vim sentinel.conf

2.编辑配置文件

redis-sentinel sentinel.conf

3.全部服务器配置完成之后，启动所有服务器redis哨兵模式（在redis目录下）

redis-cli -p 16379

4.登录

info sentinel

5.查看哨兵模式信息

关闭主服务器redis-server服务

查看其他服务器信息

6.开始检测

redis哨兵模式配置（主从所有服务器都一样）（在redis目录下）

redis

mysql与redis