（较全面）振兴杯数据库知识点整理

数据爬坡ing

已于 2024-05-21 20:52:58 修改

阅读量1.9k

点赞数 1

分类专栏：数据分析文章标签： mysql

于 2023-11-19 14:41:29 首次发布

本文链接：https://blog.csdn.net/m0_67447926/article/details/134490515

版权

数据分析专栏收录该内容

22 篇文章 2 订阅

订阅专栏

主要介绍数据库基础概念，优点，四个特性以及规划与优化的相关内容，包括容量规划、性能规划、数据库设计和查询优化、视图，存储过程，导入导出等。

MySQL是什么呢？是一种流行的开源关系型数据库管理系统（RDBMS），它支持多个操作系统，使用SQL语言进行操作，可以用于存储和管理各种类型的数据，包括文本、图像、音频和视频等。
ACID是指数据库事务应该具有的四个特性，它们是：
原子性(Atomicity)：一个事务中的所有操作要么全部成功完成，要么全部失败回滚。即使在发生故障的情况下，也不会留下部分完成的操作。
一致性(Consistency)：事务开始之前和结束之后，数据库的完整性约束没有被破坏。这意味着事务应该确保数据库中的数据始终处于一致状态，即使在多个并发事务的情况下。
隔离性(Isolation)：每个事务都应该被隔离开来，以避免并发事务之间的干扰。这可以通过使用锁定或其他机制来实现。
持久性(Durability)：一旦事务提交，其结果就应该被永久保存。即使在系统崩溃或停电的情况下，也应该能够恢复已提交的事务。

1，容量规划

第一，优先考虑：原有数据量+数据增长率
第二，清楚预期硬盘容量 =（所有表字段大小+索引大小）* （原记录条数+预计增长条数）/（1-缓冲因子）

2，性能规划

A:考虑什么：业务需求+并发访问量
B:服务器要求怎么样的：高性能
C:引擎要求怎么样的：适应业务
D:有频繁操作的数据怎么办：放入内存
E:数据量大：分区分表，举例如下

create table sales(
id int,
product varchar(50),
sale_date
)
partition by range(year(sale_date)) (
 partition p2020 values less then (2021),
 partition p2021 values less then (2022),
 ..
 );

2.1，服务器选择（类比选择汽车）

-引擎（类似汽车得CPU）,要求要跑得快，且承受负载
-载重（内存）
-存储（类似汽车得存储空间）
-网络（类似汽车得速度+流畅度），速度快，带宽高
-厂商（类似选择汽车品牌），可靠

2.2，引擎选择

A:InnoDB：处理读写，可处理高并发，支持事务
B:MyISAM：处理读，不支持事务
C:Memory：临时存储
D:Archive：历史数据归档
E:CSV：数据交换

3，数据库设计

A:需求设计：从面向对象角度出发，找到有哪些对象，这些对象分别对应有哪些功能
B:概念设计：根据需求确定实体（名词），以及实体间关系/联系
C:逻辑设计：确定实体属性，确定主外键关系（根据关系）
D:物理设计：创表（要求包含字段，约束，主外键，索引）

3.1，字段类型

A:整数：int
B:小数：decimal(参数第一位表示存几位数，第二位表示存几位小数)
C:字符串：varchar(最大长度)-可变的，char(固定长度)
D:日期：date：日期，time：时间，datetime：日期时间（YYYY-MM-DD HH:MM:SS）
E:文本：text(较长)
F:枚举：enum('男'，'女')

3.2，SQL查询优化

A:非索引列

->让其非索引列索引化，就是添加索引

B:列运算/函数/表达式

->存新列

--基础查询语句：
SELECT column1, column2 + column3, UPPER(column4) FROM table_name;
--优化语句：
ALTER TABLE table_name ADD new_column INT; （添加新列）
UPDATE table_name SET new_column = column2 + column3;
SELECT column1, new_column, UPPER(column4) FROM table_name;

C:子查询


--基础查询语句：
SELECT * FROM table1 WHERE column1 IN (SELECT column2 FROM table2);
--优化语句：
CREATE INDEX index_name ON table2 (column2); （为子查询中的列创建索引）
SELECT table1.* FROM table1 JOIN table2 ON table1.column1 = table2.column2;

D:模糊查询：

为模糊查询的列创建索引

E:全表扫描

->减少*，distinct,and,or,not,group by,having
（只返回需要的列）
--基础查询语句：
SELECT * FROM table_name WHERE condition;
--优化语句：
SELECT column1, column2 FROM table_name WHERE condition;

F:多表/嵌套查询

->简化（join进行关联，减少复杂性）
--基础查询语句：
SELECT * FROM table1 JOIN table2 ON table1.column = table2.column
WHERE table1.column IN (SELECT column FROM table3 WHERE condition);
--优化语句：
SELECT * FROM table1
JOIN (SELECT column FROM table3 WHERE condition) AS sub_query
ON table1.column = sub_query.column
JOIN table2 ON table1.column = table2.column;

3.3，创建数据库/表/表间关系

-约束：

 primary key/default/not null/unique/auto_increment(modify修改)
 外键：add constraint 自设外键名 foreign_key(本表字段名) references 非本表表名（非本表主键名）

-表间关系：

      1：1-双向外键
      1：N-N设外键
      N：N-建中间表（包含俩表主键）

3.4，插入加密

insert into 表名(username,password)
values('zhangsan',aes_encrypt加密函数('password','blob加密方法'))

3.5，视图、触发器，存储过程

A:视图

也可以叫窗口/虚表，可看见外面景色，不必了解结构细节

  create view 视图名 as select语句（操作与表一样）

B:触发器

可以看作是特定行为触发特定操作

create trigger 触发器名
    before/after insert/delete/select on 表名
    for each row/for each statement
  begin
    if （new.name新值，old.name旧值）-new是on后面的表名 then
    else             then
    end if;
  end;

B:存储过程

类似函数，可重复调用

  delimiter $
  create procedure get_phone(in id int,out out_name varchar(20),out out_phone varchar(15)) 
  [out也算declare,可直接set]
  begin 
  [可declare变量，先declare定义，再set设置]
  select name,phone into out_name,out_phone from beauty b where b.id=id;
  end $
  delimiter ;
  
    call getphone(1,@outname,@outphone);
  select @out_name,@out_phone;

4，数据库导入数据（外部导入）

--导入数据表：
load data infile 'csv文件路径' into table 表名
fields terminated by ',字段分隔符'
lined terminated by '\n换行分隔符'
ignore n忽略第n行 rows;
--（精确控制，支持多文件格式）

5，备份与恢复（mysql导入，mysqldump导出）

--备份数据库：
mysqldump -uroot -p123456 --databases 数据库名>备份文件.sql
--备份数据表：
mysqldump -uroot -p123456 数据库名 表名>备份文件.sql
--恢复数据库：
mysql -uroot -p123456 数据库名<备份文件.sql
--恢复数据表：
mysql -uroot -p123456 数据库名 表名<备份文件.sql
(-d加上，表示不包含数据)
--导出查询语句：
select*from 表名 into outfile '文件路径';

--自动备份：脚本内容

脚本内容：
#!/bin/bash

# 定义备份目录和备份文件名
backup_dir="备份目录"
backup_filename="备份数据表名_$(date +%F_%H-%M-%S).sql"

# 定义用户名和密码
mysql_user="root"
mysql_password="123456"

# 定义需要备份的数据库名
database_name="mydatabase"

# 创建备份目录（以防万一）
mkdir -p "$backup_dir"

# 备份数据库
mysqldump --user="$mysql_user" --password="$mysql_password" "$database_name" > "$backup_dir/$backup_filename"

# 检查备份文件是否创建成功
if [ -f "$backup_dir/$backup_filename" ]; then
  echo "数据库备份成功"
else
  echo "数据库备份失败"
fi

6，数据库查询

select (distinct) .. upper(字段名),sum(字段名) from .. 
inner/left/right join 表名 on 字段名=字段名
where .. is (not) null
group by
having 有函数[sum(字段名)>100]
（having是建立在group by基础上，所以连用）
order by (desc);

--子查询：>,<,=,in,any,some,all,exists(select .. from ..)
--基于派生表的查询（子查询结果当成一个表，避免原表操作，优化查询常用方法）：
select studentid,avgscore from(
 select studentid,avg(score) as avgscore from score
 group by studentid
) as subquery
where avgscore>80;

7，新建用户/角色，设置权限，授权与回收

A:新建用户
create user '用户名'@'localhost/%/特定访问主机名' identified by '用户密码'（password expire-首次登录更改密码）;
补充：with max_queries_per_hour 100并限制用户每小时只能执行100次查询
      password never expire密码永不过期
B:新建角色
create role '角色1';
grant '角色1' to '用户名'@localhost;
C:授权
grant select/select(name)/all privileges/super on 表名 to 用户/public with grant option;
[允许将同样权限授权给其他用户]
D:回收
revoke select/select(name)/all privileges on 表名 from 用户1 cascade;
[收回用户1以及授权给其他用户的权限]
E:刷新权限：flush privileges;
F:--查看：select user,host,grant.priv from mysql.user

8,性能监控优化

A--查看当前运行线程：

show processlist;

B--参数设置优化：

  innodb_buffer_pool_size:缓冲池，物理内存70%-80%
  innodb_file_per_table:每个表的独立表空间，on/off
  innodb_flush_method:数据刷新方法，大多数系统fsync，linux系统O_DIRECT
  innodb_log_file_size:日志文件大小，>=256MB
  innodb_flush_log_at_trx_commmit:日志刷新策略，1为事务提交时刷新，2为每秒刷新
    （show variables like '',set global ''=value）

C--性能监控

use performance_schema；一个数据库，包含多个性能相关表，实时更新
show tables（like '..%'）;
文件-'%file%'
内存-'%memory%'
设置-'%setup%'
语句事件-'events_statement%'
等待事件-'events_wait%'
事务事件-'events_transaction%'
启用监控：
  收集：update setup_instruments set enabled='yes',timed='yes' where name like 'wait%';
  存储：update  setup_consumers set enabled='yes' where name like 'wait%';

D--分析语句性能：

set profiling=1; 开启
show profiles;  查看所有sql执行时间
show profile;   查看最近sql执行时间
show profile for query 2; 查看指定sql执行时间
show profile all\其他 for query 2\G;  查看指定sql所有类型信息
[其他：
    cpu-系统cpu时间
    ipc-发送+接收消息数量
    swaps-显示swap的次数
    source-函数名称位置
    block io-io执行次数
    page faults-显示页错误数
    context switchs-上下文切换次数
]

E--分析语句执行计划(explain sql语句)

id:相同即从上到下，不同则越大越先
select_type:查询类型，primary外查询，即有子/deriued 临时表/subquery子查询
type:访问类型，system 一条记录/index 全索引/ref非唯一索引/eq-ref唯一索引/all全表扫描
ref:连接表的列
rows:预计读取行数
--慢查询日志
show variables like 'slow_query_log';查看是否开启
show variables like 'slow_query_log_file';查看日志位置
show variables like 'long_query_time';查看时间阈值

(找资源/ai办公/商务合作，感兴趣加好友)