社区
2024-07-15 18:29:58 4 举报
AI智能生成
mysql基础,数据的增删改查,与扩展
作者其他创作
大纲/内容
mysql:磁盘存储型数据库
基础
mysql系统
SHOW VARIABLES LIKE 'character\_set\_%';
查看所有编码格式
show engines;:查看存储引擎
#查看系统变量
show global variables;
show global variables;
#查看会话变量
show session variables;
show session variables;
库
查看
show databases;:查看所有库
插入
创建库并指定字符序和字符编码:create datebase choose character set utf8mb4 collate utf8mb4_unicode_ci;
删除
drop database demo;
删除库,数据也会删除
修改
切换库
use 库名;
表
查
show tables;:查看所有表
增
创建表:create table name(id int, name varchar(50));
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 表名;
查看表结构
数据
增
插入数据:insert into demo(id,name) values(1,'nihao');
复制
insert into t_name_1 select * from t_name_2;
复制表数据
删
删除表所有数据: delete from t_name;
删除表的所有数据:truncate table t_name;
改
修改数据:update t_student_info set age=29 where sname='李四';
查
基本查询 where
指定查询:select * from t_student_info where id = 222;
指定查询:select * from t_student_info where id in (1,2,3,4,5,6);
排序查询:order by
升序
select * from t_score order by score;
select * from t_score order by score asc;
降序
select * from t_score order by score desc;
分组查询:group by
select sum(score),course_no from t_score group by course_no;
以course_no来分组,查询每一组的score的和and组名
连表查询:join
join:查询出所有能够对应的上的数据
select * from t_teacher t join t_department d on t.department = d.dept_no;
left join:以左表为基准,查询出左表所有的数据右表没有的,则填充null
select * from t_teacher t left join t_department d on t.department = d.dept_no;
left join:与left join正好相反,以右表为基准,查询出左表所有的数据右表没有的,则填充null
select * from t_teacher t right join t_department d on t.department = d.dept_no;
查询所有理工农的老师
select t.* from t_teacher t inner join t_department d on d.dept_no = t.department where d.dept_not_no = 'lgn';
查询分数表中所有年龄大于等于18岁的学生的详细(姓名,年龄,性别)信息和分数以及对应的课程
select stu.sname, stu.age, stu.sex, score.score, cou.cname, cou.course_no
from t_student_info stu join t_score score on stu.stuno = score.stuno
join t_course cou on score.course_no = cou.course_no
where stu.age = 18;
from t_student_info stu join t_score score on stu.stuno = score.stuno
join t_course cou on score.course_no = cou.course_no
where stu.age = 18;
切片:limit
语法:select * from t_name limit' 从哪里开始','多少条数据';
去重查询:distinct
select distinct(stuno) from t_score;
统计查询:count
select count(*) from t_score;
模糊查询:like
select * from t_teacher where tname like '张%';
聚合查询:union
select tname, age, gender from t_teacher union select sname, age, sex from t_student_info limit 100;
需要两张表每条数据查询的数量一致(以上两表都为三个)
将两张表竖着排在一起打印
选择分支查询: case when
select
case
when score >= 90 then 'A'
when score >= 80 then 'B'
when score >= 70 then 'C'
when score >= 60 then 'D'
else 'F'
end as grade,stuno,course_no,score
from t_score;
case
when score >= 90 then 'A'
when score >= 80 then 'B'
when score >= 70 then 'C'
when score >= 60 then 'D'
else 'F'
end as grade,stuno,course_no,score
from t_score;
运算符
# 逻辑运算符 and, or, not !
# 比较运算:>,>=,<,<=, =,!=
# 范围:in, between and
# 比较运算:>,>=,<,<=, =,!=
# 范围:in, between and
存储过程体·
创建
delimiter $$
create procedure name(参数(可写可不写))
begin
mysql命令
end $$
delimiter ;
create procedure name(参数(可写可不写))
begin
mysql命令
end $$
delimiter ;
删除
drop procedure name;
改:删除 + 创建
实战
存储过程:删除其中id为100的学生信息,并且更新教师号为T003的老师的性别为 “女”
触发器
创建
delimiter $$
create trigger insert_teacher_logs after insert on t_teacher for each row
begin
insert into t_logs('insert', 'teacher插入数据');
end $$
delimiter ;
create trigger insert_teacher_logs after insert on t_teacher for each row
begin
insert into t_logs('insert', 'teacher插入数据');
end $$
delimiter ;
开始声明(我要创建触发器了):delimiter $$
创建语法:create trigger inster_teacher_logs after insert on t_teacher for each row
after后面的内容:触发条件
t_teacher每插入一条内容执行一次:insert on t_teacher for each row
执行的命令
begin
insert into t_logs('insert', 'teacher插入数据');
end $$
insert into t_logs('insert', 'teacher插入数据');
end $$
声明结束: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;
备份表为.txt文件
注意
注意:
使用SELECT ... INTO OUTFILE时,MySQL服务器必须有写权限到指定的文件路径。
导出的文件必须不存在,因为MySQL不会覆盖现有文件。
这个语句不会在客户端生成文件,而是在MySQL服务器上生成文件
使用SELECT ... INTO OUTFILE时,MySQL服务器必须有写权限到指定的文件路径。
导出的文件必须不存在,因为MySQL不会覆盖现有文件。
这个语句不会在客户端生成文件,而是在MySQL服务器上生成文件
所以,在运行前,先得看一下mysql默认可以导出的文件路径在哪里
show variables like 'secure_file_priv';
show variables like 'secure_file_priv';
也可在my.cnf的[mysqld]下更改secure_file_priv="..."的值
SELECT * INTO OUTFILE 'file_name';
SELECT *
INTO OUTFILE '/var/lib/mysql-files/t_course.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM t_course;
SELECT *
INTO OUTFILE '/var/lib/mysql-files/t_course.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM t_course;
将数据导入到数据库
load data infile 文件路径
备份库
整库备份:mysqldump -u root -p mydatabase > mydatabase_backup.sql
整库导入:source mysql库文件路径
mysql主从-配置
1.修改my.cnf配置文件
在[mysqld]下加入:
log-bin=mysql-bin
server-id=101
binlog_checksum=none
log-bin=mysql-bin
server-id=101
binlog_checksum=none
2.重启mysql
systemctl restart mysqld
3.设置mysql
1.登录MySQL:mysql -u root -p
2.创建slave用户:# CREATE USER 'slave'@'%' IDENTIFIED BY 'abc123';
3.给予slave用户对所有库和所有表得读的权限
grant REPLICATION SLAVE on *.* to 'slave'@'%';
4.更改用户slave的登录密码验证方式为使用MySQL的原生密码认证插件进行身份验证,密码为abc123
alter user 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'abc123';
5.刷新权限:flush privileges;
6.查看master状态:show master status;
4.设置从服务器
1.修改my.cnf配置文件
在[mysqld]下加入:
server-id=101
server-id=101
2.登录mysql :mysql -u root -p
关闭slave:stop slave;
关闭slave:stop slave;
3.连接主服务器:
change master to master_host='10.0.0.23',master_user='slave',master_password='abc123', master_log_file='mysql-bin.000005',master_log_pos=156;
change master to master_host='10.0.0.23',master_user='slave',master_password='abc123', master_log_file='mysql-bin.000005',master_log_pos=156;
4.启动主从复制:start slave;
5.查看状态是否连接成功:show slave status;
5.查看状态是否连接成功:show slave status;
数据库事务的四大特性
原子性
一致性
持久性
隔离性
读写锁:lock
锁:lock table name read / write;
read:读(没用,锁了也能查询数据,因为读锁是共享锁,大家都能看)
write:写
write:写
解锁:unlock tables;
mysql查询的优化方式
分库,分区,分页
数据分区:根据数据的访问模式或业务逻辑将数据表分区,可以提高查询效率和管理灵活性。
数据分页:对于大数据量的查询,使用LIMIT来限制返回的数据量,以减少网络传输和处理时间。
连表查询
join
创建索引
创建索引:在经常作为查询条件(WHERE子句)、排序(ORDER BY子句)和分组(GROUP BY子句)的列上创建索引可以显著提高查询效率。
避免过多索引:虽然索引可以提高查询速度,但它们也会减慢写操作(INSERT、UPDATE、DELETE),并占用更多的磁盘空间。
索引维护:定期检查和维护索引,确保它们保持高效。
读写分离
连接和并发
增加最大连接数:根据需要调整max_connections的值,以确保数据库能够处理足够的并发连接。
redis
什么是穿透,雪崩
缓存穿透
缓存穿透是指查询一个不存在的数据,由于缓存中没有数据,所以这个查询请求会直接穿过缓存层,到达数据库层,从而增加了数据库的压力。这种情况通常发生在恶意攻击或者系统设计不当的情况下。
原因
1.恶意攻击:攻击者有意请求不存在的数据,以使得系统频繁访问数据库,造成拒绝服务攻击。
2.系统设计不当:系统设计时未对查询参数进行校验,导致查询了不存在的数据。
2.系统设计不当:系统设计时未对查询参数进行校验,导致查询了不存在的数据。
解决方案
1.参数校验:在查询缓存之前,先对请求的参数进行合法性检查,如过滤非法字符、判断参数范围等。
2.布隆过滤器:在缓存层前使用布隆过滤器,对所有可能的数据建立一个布隆过滤器,用于快速判断一个请求的数据是否存在于数据库中。
3.缓存空值:当查询的数据在数据库中不存在时,也将这个空值缓存起来,并设置一个较短的过期时间,这样可以减少后续请求对数据库的访问。
4.增强ID复杂度:避免被猜测ID规律,增加ID的复杂度和随机性。
5.做好数据的基础格式校验:确保传入的数据格式符合预期。
6.加强用户权限校验:确保只有有权限的用户才能访问数据。
2.布隆过滤器:在缓存层前使用布隆过滤器,对所有可能的数据建立一个布隆过滤器,用于快速判断一个请求的数据是否存在于数据库中。
3.缓存空值:当查询的数据在数据库中不存在时,也将这个空值缓存起来,并设置一个较短的过期时间,这样可以减少后续请求对数据库的访问。
4.增强ID复杂度:避免被猜测ID规律,增加ID的复杂度和随机性。
5.做好数据的基础格式校验:确保传入的数据格式符合预期。
6.加强用户权限校验:确保只有有权限的用户才能访问数据。
缓存雪崩
缓存雪崩是指在某一时刻,缓存中大量的缓存数据同时失效或过期,导致大量的请求直接打到数据库或后端系统,从而造成数据库负载激增,甚至导致数据库宕机或系统崩溃的现象。
原因
1.大量缓存同时设置相同的过期时间:如果在某一时刻设置了大量的缓存数据,并为它们都设置了相同的过期时间,那么在未来的某一时刻,这些缓存数据都会同时失效。
2.Redis宕机:如果因为某种原因,Redis服务器突然宕机,那么所有的缓存数据都会消失,导致所有的请求都直接访问数据库。
3.误删缓存数据:如果由于某种原因,大量的缓存数据被误删除,那么也会引发缓存雪崩。
2.Redis宕机:如果因为某种原因,Redis服务器突然宕机,那么所有的缓存数据都会消失,导致所有的请求都直接访问数据库。
3.误删缓存数据:如果由于某种原因,大量的缓存数据被误删除,那么也会引发缓存雪崩。
解决方案
1.设置随机过期时间:为了防止大量的缓存同时失效,可以为每个缓存设置一个随机的过期时间,这样就可以确保不会有太多的缓存同时失效。
2.使用多级缓存:可以在客户端和数据库之间设置多级缓存,如本地缓存、Redis缓存等。当Redis缓存失效时,可以先查询本地缓存,如果本地缓存也没有,再查询数据库。
3.实现缓存数据的自动刷新:通过定时任务或事件触发等方式,在缓存数据即将过期时,自动从数据库中查询最新的数据并更新到缓存中。
4.限流和熔断:在系统中加入限流和熔断机制,当数据库请求到达一定的阈值时,直接拒绝部分请求,以保护数据库不被过度访问。
5.监控和报警:建立监控系统,实时监控缓存和数据库的状态,一旦发现异常情况,立即报警并采取相应的措施。
2.使用多级缓存:可以在客户端和数据库之间设置多级缓存,如本地缓存、Redis缓存等。当Redis缓存失效时,可以先查询本地缓存,如果本地缓存也没有,再查询数据库。
3.实现缓存数据的自动刷新:通过定时任务或事件触发等方式,在缓存数据即将过期时,自动从数据库中查询最新的数据并更新到缓存中。
4.限流和熔断:在系统中加入限流和熔断机制,当数据库请求到达一定的阈值时,直接拒绝部分请求,以保护数据库不被过度访问。
5.监控和报警:建立监控系统,实时监控缓存和数据库的状态,一旦发现异常情况,立即报警并采取相应的措施。
redis安装部署
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
cd redis/
redis-server redis.conf
关于redis.conf的参数
启动ip:bind
本地回环地址:127.0.0.1
所有地址:0.0.0.0
远程连接,主从备份,哨兵模式,需要
端口:port
默认端口:6379
保护模式:protected-mode
是否在后台运行:daemonize
yes
no
备份文件地址:logfile
登录密码:requirepass
主从备份主机(master)密码:masterauth
主从备份主机参数(ip 端口):replicaof
主机ip 端口
redis配置主从备份
关闭防火墙:systemctl stop firewalld
开机自动关闭:systemctl disable firewalld
1.主服务器配置文件需要修改的参数
vim redis.conf
bind:0.0.0.0
port:6379
protected-mode:no
daemonize:yes
logfile:./redis.log
requirepass:abc123
masterauth:abc123
port:6379
protected-mode:no
daemonize:yes
logfile:./redis.log
requirepass:abc123
masterauth:abc123
2.从服务器配置文件需要修改的参数
vim redis.conf
bind:0.0.0.0
port:6379
protected-mode:no
daemonize:yes
logfile:./redis.log
requirepass:abc123
masterauth:abc123
replicaof 192.168.169.100 6379
port:6379
protected-mode:no
daemonize:yes
logfile:./redis.log
requirepass:abc123
masterauth:abc123
replicaof 192.168.169.100 6379
3.启动(重新启动)
1.在redis目录下
2.启动:redis-server redis.conf
3.登录:redis-cli
4.密码验证:auth 密码
5.查看主从配置信息:info replication
主从信息对比
主服务器(master)
# Replication
role:master
connected_slaves:2
slave0:ip=10.0.0.99,port=6379,state=online,offset=718552,lag=1
slave1:ip=10.0.0.100,port=6379,state=online,offset=718552,lag=1
master_failover_state:no-failover
master_replid:04004ddac58721497e67a6897469b5406072dd7b
master_replid2:eb25e838d821ab5552b37e4c3e59a70ed3f6cc52
master_repl_offset:718686
second_repl_offset:180448
repl_backlog_active:1
repl_backlog_size:1048576
repl_backlog_first_byte_offset:156657
repl_backlog_histlen:562030
role:master
connected_slaves:2
slave0:ip=10.0.0.99,port=6379,state=online,offset=718552,lag=1
slave1:ip=10.0.0.100,port=6379,state=online,offset=718552,lag=1
master_failover_state:no-failover
master_replid:04004ddac58721497e67a6897469b5406072dd7b
master_replid2:eb25e838d821ab5552b37e4c3e59a70ed3f6cc52
master_repl_offset:718686
second_repl_offset:180448
repl_backlog_active:1
repl_backlog_size:1048576
repl_backlog_first_byte_offset:156657
repl_backlog_histlen:562030
从服务器(slave)
# Replication
role:slave
master_host:10.0.0.23
master_port:6379
master_link_status:up
master_last_io_seconds_ago:1
master_sync_in_progress:0
slave_read_repl_offset:715458
slave_repl_offset:715458
slave_priority:100
slave_read_only:1
replica_announced:1
connected_slaves:0
master_failover_state:no-failover
master_replid:04004ddac58721497e67a6897469b5406072dd7b
master_replid2:0000000000000000000000000000000000000000
master_repl_offset:715458
second_repl_offset:-1
repl_backlog_active:1
repl_backlog_size:1048576
repl_backlog_first_byte_offset:202094
repl_backlog_histlen:513365
role:slave
master_host:10.0.0.23
master_port:6379
master_link_status:up
master_last_io_seconds_ago:1
master_sync_in_progress:0
slave_read_repl_offset:715458
slave_repl_offset:715458
slave_priority:100
slave_read_only:1
replica_announced:1
connected_slaves:0
master_failover_state:no-failover
master_replid:04004ddac58721497e67a6897469b5406072dd7b
master_replid2:0000000000000000000000000000000000000000
master_repl_offset:715458
second_repl_offset:-1
repl_backlog_active:1
repl_backlog_size:1048576
repl_backlog_first_byte_offset:202094
repl_backlog_histlen:513365
redis哨兵模式配置(主从所有服务器都一样)(在redis目录下)
1.关闭防火墙:systemctl stop firewalld
开机自动关闭:systemctl disable firewalld
2.编辑配置文件
1.删除哨兵模式配置文件:rm -rf sentinel.conf
2.编辑哨兵模式配置文件:vim sentinel.conf
port 16379
# 本机端口
bind 0.0.0.0
# 本机所有ip
protected-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 3000
3000毫秒检查一次
sentinel resolve-hostnames yes
是否解析主机名
# 本机端口
bind 0.0.0.0
# 本机所有ip
protected-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 3000
3000毫秒检查一次
sentinel resolve-hostnames yes
是否解析主机名
3.全部服务器配置完成之后,启动所有服务器redis哨兵模式(在redis目录下)
redis-sentinel sentinel.conf
4.登录
redis-cli -p 16379
5.查看哨兵模式信息
info sentinel
6.开始检测
关闭主服务器redis-server服务
查看其他服务器信息
info sentinel
0 条评论
下一页