mysql1-学习笔记

1，概念

  • 数据库（DB）：一个文件夹，所有数据存放的仓库
  • 数据库管理系统（DBMS）：一种软件
    • 关系型数据库：mysql、oracle、sqllite、sql server、db2、access
    • 非关系型数据库：redis、mongodb、memcache
  • 数据库管理员（DBA）
  • 数据库服务器：一台跑着DBMS的机器
  • 表：一个文件，一张存储了数据的表
  • 数据/记录：表中的信息，一般一行是一条数据
  • 在 MySQL 中，一个完整的数据存储过程总共有 4 步，分别是创建数据库、确认字段、创建数据表、插入数据。
  • 从系统架构的层次上看，MySQL 数据库系统从大到小依次是数据库服务器、数据库、数据表、数据表的行与列。
  • MySQL 数据表由行与列组成，一行就是一条数据记录，每一条数据记录都被分成许多列，一列就叫一个字段。每个字段都需要定义数据类型，这个数据类型叫做字段类型。
  • MySQL 中数据表的主键，是表中的一个字段或者几个字段的组合，可以自行添加一个不会重复的字段来做主键。。它主要有 3 个特征：
    • 必须唯一，不能重复；
    • 不能是空；
    • 必须可以唯一标识数据表中的记录。

2，安装

  • 安装地址：https://downloads.mysql.com/archives/community/

  • 解压后将my-default.ini复制一份并用pycharm打开（保证编码是utf-8编码），重命名为my.ini，并在my.ini中写入以下配置代码:

    [mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[mysqld]
#设置3306端口
port=3306
# 设置mysql的安装目录
basedir=D:\mysql\mysql-5.6.51-winx64                    #此路径改为你解压的文件夹的名字，不能有空格、中文、转移
# 设置mysql数据库的数据的存放目录
datadir=D:\mysql\mysql-5.6.51-winx64\data               #将basedir中的data目录放在这里
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
    • 将my.ini复制回原安装包

    • 配置环境变量

      将bin目录地址（D:\mysql\mysql-5.6.51-winx64\bin）配置到系统变量path中

    • 以管理员身份运行cmd，执行：

      mysqld install

    • （此步报错重装）启动mysql，同样输入

      net start mysql

3，启动clinet端

  • 以上安装步骤成功以后，mysql的server端就配置完成了，接下来只需要作为client端与server端连通就可以正常使用了。

  • cmd中输入命令:

    C:\Users\Asus>mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.51 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

4，服务端安装、启动与停止；客户端登录、退出

server 端

net start mysql
net stop mysql

client 端登录、退出

mysql -uroot -p
mysql -uroot -p -h192.168.14.12                      #连接ip为192.168.14.12的服务器上的mysql
> exit \q quit                                       #退出

5，权限管理/用户相关操作

> select user();                                     #查看当前登陆的用户
> set password = password('***');                    #设置密码
> create user '用户名'@'允许访问的ip地址，比如：192.168.14.%' identified by '密码';                                        #创建新的用户（远程连接要开防火墙）
example:  create user 'guest1'@'10.181.228.79' identified by '123';
> grant 权限类型(all:所有权限；select:查；insert：写入) on 数据库.*（某一个表，*表示所有） to '用户名'@'允许访问的ip地址';        #为用户分配权限 
example:  grant select on 21_8_14.* to 'guest1'@'10.181.228.79';
> grant 权限类型 on 数据库.* to '用户名'@'允许访问的ip地址' identified by '密码';                                           #创建用户同时分配权限
> flush privileges;                                  #刷新使修改生效

6，mysql基础命令

库

=========================================增/创建=========================================
create database 数据库名;                             #创建数据库
=========================================删=========================================
drop database 数据库名；                              #你库没了
=========================================查=========================================
show databases;                                      #查看所有的数据库
=========================================切换到数据库下=========================================
use 数据库名;

表

mysql创建表文档
mysql修改表文档

=========================================增/创建=========================================
create table 表名字
(
字段名1 数据类型 [字段级别约束] [默认值], 
字段名2 数据类型 [字段级别约束] [默认值], 
。。。
[表级别约束]
)
“约束”限定了表中数据应该满足的条件。MySQL 会根据这些限定条件，对表的操作进行监控，阻止破坏约束条件的操作执行，并提示错误，从而确保表中数据的唯一性、合法性和完整性。这是创建表时不可缺少的一部分。
example:  
create table student(name char(12), age int);
create table demo.test
(
    barcode text,
    goodsname text,
    price int，
    itemnumber int primary key auto_increment
);
=========================================增/复制=========================================
create table 表名字2 like 表名字1
=========================================删=========================================

=========================================改-加字段=========================================
增加新字段：alter table 表名字 add column 字段名 数据类型 primary key auto_increment;
* alter table，表示修改表；
* add column，表示增加一列；
* primary key，表示这一列是主键；
* auto_increment，表示每增加一条记录，这个值自动增加。
一次增加多个新字段：alter table 表名字 add column (字段名1 数据类型1, 字段名2 数据类型2, ...)
指定字段的添加位置1：alter table 表名字 add 字段名 数据类型 after 指定字段名
指定字段的添加位置2：alter table 表名字 add 字段名 数据类型 first
=========================================改-改字段=========================================
修改字段数据类型：alter table 表名字 modify column 原字段名 新数据类型 [字段级别约束] [默认值]
同时修改字段名和数据类型：alter table 表名字 change column 原字段名 新字段名 新数据类型 [字段级别约束] [默认值]
删除主键而不删除主键字段：alter table 表名字 drop primary key
=========================================查=========================================
show tables;                                         #查看当前数据库中所有的表
desc 表名字;                                          #查看表结构
describe表名字;                                       #查看表结构

数据

mysql数据插入
mysql数据更新
mysql数据查询

=========================================增=========================================
insert into 表名字 (字段名, 字段名2, ...) values (值1, 值2, ...);
=========================================增+查->插入查询结果=========================================
insert into 表名（字段名）select 字段名或值 from 表名 as 派生表别名 where 条件
=========================================删=========================================
delete from 表名字 where 字段名=字段值;                #删除表中指定的数据
delete 表1 from 数据表关联/查询子结构 where 条件        #删除表1的一些记录，具体删哪些，就看WHERE条件，满足就删
=========================================改-改值=========================================
update 表名称 set 字段名1=字段值1 where 字段名2=字段值2   #仅修改指定数据的字段值
update 表名称 set 字段名=字段值                         #会将所有数据该字段名都修改为指定的字段值
=========================================查=========================================
select * from 表名字
select sum(字段名) from 表名字
select * from 表名字 where 字段名=字段值
select distinct *|字段列表|left(字段,n) from 表名字|数据源 where 条件 group by 字段 having 条件 order by 字段 limit 起始点，行数
* distinct：作用是返回唯一不同的值。
* left(字段,n)：表示返回字符串型字段最左边的 n 个字符。
* group by：作用是告诉 MySQL，查询结果要如何分组，方便对组内的数据进行统计计算，经常与 MySQL 的聚合函数一起使用。
* having：用于筛选查询结果，跟 where类似。两者的区别在于：
  * where直接对表中的字段进行限定，来筛选结果；having 则需要跟分组关键字 group by 一起使用，通过对分组字段或分组计算函数进行限定，来筛选结果。（where 是先筛选后连接，而 having 是先连接后筛选。）
    * where直接用表的字段对数据集进行筛选。如果需要通过关联查询从其他的表获取需要的信息，那么执行的时候，也是先通过 where 条件进行筛选，用筛选后的比较小的数据集进行连接。这样一来，连接过程中占用的资源比较少，执行效率也比较高。
    * having则是要把所有的信息都准备好，包括从关联表中获取需要的信息，对数据集进行分组，形成一个包含所有需要的信息的数据集合，之后再通过having条件的筛选，得到需要的数据。
  * where可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件；having必须要与 group by 配合使用，可以把分组计算的函数和分组字段作为筛选条件；在需要对数据进行分组统计的时候，having 可以完成 where 不能完成的任务。
* from 关键字表示查询的数据源，数据源可以是单张表的表名，数据表关联，查询结果（派生表、子查询，可以用 as 关键字给派生表起一个别名）。
* order by 的作用是将查询结果排序：asc表示升序，desc表示降序。
* limit 的作用是只显示部分查询的结果。
=========================================查-关联查询=========================================
* 在 MySQL 中，有 2 种类型的连接，分别是内连接（INNER JOIN）和外连接（OUTER JOIN）；
  * 内连接（join/inner join/cross join）表示查询结果只返回符合连接条件的记录；
  * 外连接表示查询结果返回某一个表中的所有记录，以及另一个表中满足连接条件的记录，它包括两类，分别是左连接（left join）和右连接(right join)；
    * 左连接（left join）：返回左边表中的所有记录，以及右表中符合连接条件的记录；
    * 右连接（right join）：返回右边表中的所有记录，以及左表中符合连接条件的记录。
* 内连接：seleft 字段列表 from 表1 join/inner join/cross join 表2 on (连接条件)
* 左连接：seleft 字段列表 from 表1 left join 表2 on (连接条件)
* 右连接：seleft 字段列表 from 表1 right join 表2 on (连接条件)

7，mysql数据类型

  • 整数类型：
    • tinyint 1字节
    • smallint 2字节
    • mediumint 3字节
    • int(integer) 4字节
    • bigint 8字节
  • 浮点数类型（不精准）：
    • float 4字节
    • double 8字节
    • real REAL 默认就是 DOUBLE。如果你把 SQL 模式设定为启用“REAL_AS_FLOAT”，那么，MySQL 就认为 REAL 是 FLOAT。
  • 定点数类型（精准）：
    • decimal(M,D) 其中M 表示整数部分加小数部分，一共有多少位，M<=65。D 表示小数部分位数，D<M。
  • 文本类型
    • char(M) 固定M长度字符串
    • varchar(M) 可变长度字符串，最大长度为M
    • text 字符串，系统自动按实际长度存储；由于实际存储的长度不确定，MySQL 不允许 text 类型的字段做主键。遇到这种情况，只能采用 CHAR(M)，或者 VARCHAR(M)。
      • tinytext 最大长度255字符
      • text 最大长度65536字符
      • mediumtext 最大长度16777215 字符
      • longtext 最大长度4294967295 字符（相当于 4GB）
    • enum 枚举类型，取值必须是预先设定的一组字符串值范围之内的一个，必须要知道字符串所有可能的取值。
    • set 一个字符串对象，取值必须是在预先设定的字符串值范围之内的 0 个或多个，也必须知道字符串所有可能的取值。
  • 日期和时间类型
    • year 1字节 1901~2155
    • time 3字节 -835：59：59~835：59：59 不光表示一天之内的时间，还可以用来表示一个时间间隔，时间间隔可以超过 24 小时。
    • date 3字节 1000-01-01~9999-12-3
    • datetime 8字节 1000-01-01 00：00：00~9999-12-3 23：59：59
    • timestamp 4字节 1970-01-01 00：00：01 UTC~2038-01-19 03：14：07 UTC
  • 使用建议：在定义数据类型时，如果确定是整数，就用 INT；如果是小数，一定用定点数类型 DECIMAL；如果是字符串，只要不是主键，就用 TEXT；如果是日期与时间，就用 DATETIME。

8，mysql数据约束

  • 默认约束： default 设置了默认约束，插入数据的时候，如果不明确给字段赋值，那么系统会把设置的默认值自动赋值给字段。
create table 表名字 (字段名 字段类型 default 默认值)
  • 主键约束： primary key
create table 表名字 (字段名 字段类型 primary key)
  • 外键约束： foreign key 用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性。
    • 对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）。
    • 假设我们有 2 个表，分别是表 A 和表 B，它们通过一个公共字段“id”发生关联关系，我们把这个关联关系叫做 R。如果“id”在表 A 中是主键，那么，表 A 就是这个关系 R 中的主表。相应的，表 B 就是这个关系中的从表，表 B 中的“id”，就是表 B 用来引用表 A 中数据的，叫外键。所以，外键就是从表中用来引用主表中数据的那个公共字段。
    • 作用：虽然不用外键约束，也可以进行关联查询，但是有了它，MySQL 系统才会保护数据，避免出现误删的情况，从而提高系统整体的可靠性。
create table 从表名 ([constraint 外键约束名] foreign key (从表字段名) references 主表名 (主表字段名))
alter table 从表名 add constraint 约束名 foreign key (从表字段名) references 主表名 (主表字段名)

select constraint_name, table_name, column_name, referenced_table_name, referenced_column_name from information_schema.KEY_COLUMN_USAGE where constraint_name = '约束名';
  • 非空约束： not null 非空约束表示字段值不能为空，如果创建表的时候，指明某个字段非空，那么添加数据的时候，这个字段必须有值，否则系统就会提示错误。
create table 表名字 (字段名 字段类型 not null)
  • 唯一性约束： unique 唯一性约束表示这个字段的值不能重复，否则系统会提示错误。
    • 跟主键约束相比，唯一性约束要更加弱一些。
    • 在一个表中，我们可以指定多个字段满足唯一性约束，而主键约束则只能有一个。
    • 满足主键约束的字段，自动满足非空约束，但是满足唯一性约束的字段，则可以是空值。
create table 表名字 (字段名 字段类型 unique)
  • 自增约束： auto_increment 可以通过定义自增约束的方式，让系统自动赋值，从而满足唯一性，这样就可以做主键了。
    • 在数据表中，只有整数类型的字段，才可以定义自增约束。自增约束的字段，每增加一条数据，值自动增加 1。
    • 可以给自增约束的字段赋值，这个时候，MySQL 会重置自增约束字段的自增基数，下次添加数据的时候，自动以自增约束字段的最大值加 1 为新的字段值。
    • 字段类型必须是整数类型。
create table 表名字 (字段名 字段类型 auto_increment)

9，mysql主键设置

  • 有三种设置主键的思路：
    • 业务字段做主键：选择表中已有的字段，也就是跟业务相关的字段做主键。
      • 无法预测未来会不会因为业务需要，而出现业务字段重复或者重用的情况；并且业务字段可能无法获取；所以，尽量不要用业务字段做主键，主键最好是另外进行设置的，是与项目业务没有关联的。
    • 自增字段做主键
      • 对于单机系统来说是没问题的，但是如果有多台服务器，各自都可以录入数据，当每台机器各自产生的数据需要合并时，就可能会出现主键重复/冲突的问题。
    • 手动赋值字段做主键
      • 为了解决自增字段的冲突，可以采用手动赋值的方式，通过一定的逻辑，确保字段值在全系统的唯一性。

10，mysql中的函数

10-1-case函数

  • case 表达式 when 值1 then 表达式1 [ when 值2 then 表达式2] else 表达式m end

10-2-常用的聚合函数

  • sum() avg() max() min() count()

10-3-常用的时间处理函数：

  • MySQL 中获取的时间，其实就是 MySQL 服务器计算机的系统时间。如果系统有一定规模，需要在多台计算机上运行，就要注意时间校准问题。
    • 利用 Windows 系统自带的网络同步的方式校准系统时间。
    • 统一从某一MySQL服务器获取时间。
  • extract(type from date)表示从日期date中抽取type指定的部分，type可以是year month day hour minute second（时间单位：https://dev.mysql.com/doc/refman/8.0/en/expressions.html#temporal-intervals）
  • date_add(date, interval 表达式 type)表示计算从时间点“date”开始，向前或者向后一段时间间隔的时间。“表达式”的值为时间间隔数，正数表示向后，负数表示向前，“type”表示时间间隔的单位（比如年、月、日等）。
    • adddate()与date_add()用法一致
    • date_sub(), subdate()与date_add()用法类似，方向相反，执行日期的减操作。
  • last_day(date)表示获取日期时间“date”所在月份的最后一天的日期。
  • curdate()获取当前的日期。日期格式为“YYYY-MM-DD”，也就是年月日的格式。
  • dayofweek()获取日期“date”是周几。1 表示周日，2 表示周一，以此类推，直到 7 表示周六。
  • date_format(日期时间, type)表示将日期时间按照指定格式type（"%T"-时间，"%r"上下午）显示。（格式：https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format）
  • datediff(date1, date2)表示日期“date1”与日期“date2”之间差几天。

10-4-常用的数学函数：

  • 取整函数：
    • round(x,d)x 表示要处理的数，d 表示保留的小数位数，处理的方式是四舍五入。ROUND(x) 表示保留 0 位小数。
    • ceil(x) ceiling(x)向上取整，返回大于等于 X 的最小 int 型整数。
    • floor(d)向下取整，返回小于等于 X 的最大 int 型整数。
  • 绝对值函数：abs(x)求x的绝对值
  • 求余函数：mod(x,y)获取x被y除后的余数

10-5-常用的字符串处理函数：

  • https://dev.mysql.com/doc/refman/8.0/en/string-functions.html
  • concat(x,y)将字符串x和y拼接起来，组成一个字符串
  • cast(表达式 as char)将表达式的值转换为字符串
  • char_length(字符串)获取字符串的长度
  • space(n)获取一个由n个空格组成的字符串
  • substr(s, n)获取从字符串s的第n个位置开始，到s结尾的子字符串
  • mid(s,n,len)获取从字符串s的第n个位置开始，长度为len的子字符串
  • trim(s1 from s)除去字符串s两端所有的字符串s1
  • ltrim(s)除去字符串s左边的所有空格
  • rtrim(s)除去字符串s右边的所有空格

10-6-条件判断函数：

  • ifnull(v1, v2)如果v1的值不为空返回v1，否则返回v2
  • if(表达式, v1, v2)如果表达式为真返回v1，否则返回v2

11，mysql索引

  • MySQL 中的索引是帮助 MySQL 系统快速检索数据的一种存储结构。
    • 可以在索引中按照查询条件检索索引字段的值，然后快速定位数据记录的位置。
    • 数据表中的字段越多，表中数据记录越多，速度提升越是明显。
    • 应该选择经常被用做筛选条件的字段来创建索引；
    • 如果有多个筛选的字段，而且经常一起出现，也可以用多个字段来创建组合索引。
    • 索引成本体现在两个方面：存储空间的开销，数据操作上的开销。因此，索引也不是越多越好。
      • 存储空间的开销，是指索引需要单独占用存储空间。
      • 数据操作上的开销，是指一旦数据表有变动，无论是插入一条新数据，还是删除一条旧的数据，甚至是修改数据，如果涉及索引字段，都需要对索引本身进行修改，以确保索引能够指向正确的记录。
  • 单字段索引
==============================创建索引==============================
给表设定主键约束或者唯一性约束的时候，MySQL 会自动创建主键索引或唯一性索引。
=========直接给数据表创建索引：=========
create index 索引名 on table 表名 (字段);
=========创建表的同时创建索引：=========
create table(
字段 数据类型,
...
{ index | key } 索引名(字段)
)
=========修改表时创建索引：=========
alter table 表明 add { index | key } 索引名(字段);
==============================删除索引==============================
drop index 索引名 on 表名;
=========主键索引通过修改表来删除：=========
alter table 表名 drop primary key;
    • 单字段索引作用原理：使用 explain 关键字查看 SQL 语句的执行细节，包括表的加载顺序，表是如何连接的，以及索引使用情况等。
    • 索引字段的选取原则：在选择索引字段的时候，要选择那些经常被用做筛选条件的字段，这样才能发挥索引的作用，提升检索的效率。
    • 如果有多个索引，而这些索引的字段同时作为筛选字段出现在查询中的时候，MySQL 会选择使用最优的索引来执行查询操作。如果要让多个索引同时发挥作用，就要用到组合索引。
  • 组合索引
==============================创建索引==============================
=========直接给数据表创建索引：=========
create index 索引名 on table 表名 (字段1, 字段2, ...);
=========创建表的同时创建索引：=========
create table(
字段 数据类型,
...
{ index | key } 索引名(字段1, 字段2, ...)
)
=========修改表时创建索引：=========
alter table 表明 add { index | key } 索引名(字段1, 字段2, ...);
==============================删除索引==============================
drop index 索引名 on 表名;
=========主键索引通过修改表来删除：=========
alter table 表名 drop primary key;
    • MySQL 最多支持由 16 个字段组成的组合索引。
    • 组合索引的作用原理：组合索引的多个字段是有序的，遵循左对齐的原则；因此筛选的条件也要遵循从左向右的原则，如果中断，那么，断点后面的条件就没有办法利用索引了；如果筛选的是一个范围，没有办法精确定位，也相当于中断。

12，mysql事务

  • 事务是 MySQL 的一项功能，它可以使一组数据操作（也叫 DML 操作，是英文 Data Manipulation Language 的缩写，包括 select、insert、update 和 delete），要么全部执行，要么全部不执行，不会因为某种异常情况（比如硬件故障、停电、网络中断等）出现只执行一部分操作的情况。
  • 语法
start transaction | begin  （开始事务，通知 MySQL，后面的 DML 操作都是当前事务的一部分。）
一组DML语句
commit  （提交事务，执行当前事务的全部操作，让数据更改永久有效。）
rollback  （事务回滚，取消对数据的更改。）
  • 事务并不会自动处理 SQL 语句执行中的错误，如果对事务中的某一步数据操作发生的错误不做处理，继续提交的话，仍然会导致数据不一致；因此，如果发现事务中的某个操作发生错误，要及时使用回滚；只有事务中的所有操作都可以正常执行，才进行提交；可以通过 MySQL 的函数 row_count() 的返回，来判断一个 DML 操作是否失败，-1 表示操作失败，否则就表示影响的记录数。
    • 在 MySQL 中，并不是所有的操作都可以回滚。比如创建数据库、创建数据表、删除数据库、删除数据表等，这些操作是不可以回滚的，所以，你在操作的时候要特别小心，特别是在删除数据库、数据表时，最好先做备份，防止误操作。
  • 事务的四个重要特征：
    • 原子性(atomicity)：事务中的操作要么全部执行，要么全部不执行，像一个整体，不能从中间打断。
    • 持久性(durability)：事务对数据的修改是永久有效的，不会因为系统故障而失效。
    • 一致性(consistency)：数据的完整性不会因为事务的执行而受到破坏。
    • 隔离性(isolation)：多个事务同时执行的时候，不互相干扰。不同的隔离级别，相互独立的程度不同。
  • 通过对锁的使用，可以实现事务之间的相互隔离。锁的使用方式不同，隔离的程度也不同；事务隔离等级有4种：
    • read uncommitted：可以读取事务中还未提交的被更改的数据。
    • read committed：只能读取事务中已经提交的被更改的数据。
    • repeatable read：表示一个事务中，对一个数据读取的值，永远跟第一次读取的值一致，不受其他事务中数据操作的影响。这也是 MySQL 的默认选项。
    • serializable：表示任何一个事务，一旦对某一个数据进行了任何操作，那么，一直到这个事务结束，MySQL 都会把这个数据锁住，禁止其他事务对这个数据进行任何操作。

13，mysql临时表-复杂查询，保存中间结果

  • 临时表是一种特殊的表，用来存储查询的中间结果，并且会随着当前连接的结束而自动删除；MySQL 中有 2 种临时表：
    • 内部临时表主要用于性能优化，由系统自动产生，我们无法看到
    • 外部临时表通过 SQL 语句创建，我们可以使用
  • 外部临时表语法
create temporary table 表名(
字段名 字段类型,
...
);
==========================
create temporary table 表名 查询语句
  • 临时表的特征：
    • 临时表的创建语法需要用到关键字 TEMPORARY；
    • 临时表创建完成之后，只有当前连接可见，其他连接是看不到的，具有连接隔离性；
    • 临时表在当前连接结束之后，会被自动删除。
  • 根据所采用的存储方式不同，临时表也可分为内存临时表和磁盘临时表：
    • 内存临时表，可以通过指定引擎类型（比如 engine=memory，不指定会默认存储引擎是 InnoDB并存储在磁盘上），来告诉 MySQL 临时表存储在内存中；内存临时表查询速度快，但是掉电丢失数据。
    • 磁盘临时表查询速度慢，但是掉电不丢失数据；临时表会在连接结束的时候自动删除，不会占用磁盘空间。
create temporary table 表名(
...
) engine=memory;

14，mysql视图

  • 对于视图来说，好多公司都明确禁止使用了。比起它的那点查询的封装，后期的维护成本更高。因为开发中要经常对数据表结构做改动，如果对业务不熟的新同事，很容易就忘记修改对应的视图创建语句。而在代码中SQL语句更加直观和不容易遗忘。
  • 视图是一种虚拟表，可以把一段查询语句作为视图存储在数据库中，在需要的时候把视图看做一个表，对里面的数据进行查询。
    • 优点：
      • 将查询模块化，提高开发和维护效率；
      • 视图没有实际存储数据，而是存储查询语句，这样避免了数据存储过程中可能产生的冗余，提高了存储的效率；
      • 具有隔离性，在用户和实际的数据表之间加了一层虚拟表，用户不需要查询数据表，可以直接通过视图获取数据表中的信息，提高了数据表安全性（但是注意对视图中数据的修改也是对原表中数据的修改）
      • 视图的数据结构相对独立，即便实际数据表的结构发生变化，也可以通过修改定义视图的查询语句，让查询结果集里的字段保持不变。这样一来，针对视图的查询就不受实际数据表结构变化的影响了。
    • 缺点：
      • 如果在实际数据表的基础上创建了视图，那么实际数据表的结构变更，就需要及时对相关的视图进行维护。特别是当视图是由视图生成的时候，维护会变得比较复杂。
  • 对视图的操作
=========================================创建=========================================
create|replace view 视图名称[字段列表] as 查询语句

=========================================删除=========================================
drop view 视图名称

=========================================修改=========================================
alter view 视图名称 as 查询语句

=========================================查询=========================================
describe 视图名称
  • 对视图中数据的操作-注意对视图中数据的修改也是对原表中数据的修改
    • 不建议更新视图的数据，因为对视图数据的更改，都是通过对实际数据表里数据的操作来完成的，而且有很多限制条件。
=========================================添加=========================================
* 只有视图中的字段跟实际数据表中的字段完全一样，MySQL 才允许通过视图插入数据
insert into  视图名称 (字段列表) values (值列表)

=========================================删除=========================================
delete from 视图名称 where 条件语句

=========================================修改=========================================
update 视图名称 set 字段=值 where 条件语句

=========================================查询=========================================
select * from 视图名称

15，mysq子查询和派生表

  • 子查询：就是嵌套在另一个查询中的查询。
  • 派生表：如果在查询中把子查询的结果作为一个表来使用，这个表就是派生表。

16，mysq存储过程

  • 简单来说，存储过程就是把一系列 SQL 语句预先存储在 MySQL 服务器上，需要执行的时候，客户端只需要向服务器端发出调用存储过程的命令，服务器端就可以把预先存储好的这一系列 SQL 语句全部执行。
  • 优点：不仅执行效率非常高，而且客户端不需要把所有的 SQL 语句通过网络发给服务器，减少了 SQL 语句暴露在网上的风险，也提高了数据查询的安全性。
  • 开发和调试的成本比较高，不太容易维护。
  • 对存储过程的操作
=========================================创建=========================================
create procedure 存储过程名 ([in | out | inout] 参数名称 类型) 程序体

=========================================删除=========================================
drop procedure 存储过程名

=========================================修改=========================================
删除后重新创建

=========================================查看=========================================
show create procedure 存储过程名

=========================================调用=========================================
call 存储过程名(参数)
  • IN 表示输入的参数，存储过程只是读取这个参数的值。如果没有定义参数种类，默认就是 IN。
  • OUT 表示输出的参数，存储过程在执行的过程中，把某个计算结果值赋给这个参数，执行完成之后，调用这个存储过程的客户端或者应用程序就可以读取这个参数返回的值。
  • INOUT 表示这个参数既可以作为输入参数，又可以作为输出参数使用。
  • 创建存储过程的一般思路：
    • 第一步，把 SQL 语句的分隔符改为“//”。因为存储过程中包含很多 SQL 语句，如果不修改分隔符的话，MySQL 会在读到第一个 SQL 语句的分隔符“;”的时候，认为语句结束并且执行，这样就会导致错误。
    • 第二步，确定需要传入的参数，创建存储过程。同时，用 BEGIN 和 END 关键字把存储过程中的 SQL 语句包裹起来，形成存储过程的程序体。
    • 第三步，恢复分隔符为“;”。
delimiter //
create procedure 存储过程名 (参数名称 类型)
begin
...
end
//
delimiter ;

17，mysq游标-存储函数

  • 游标，也就是能够对结果集中的每一条记录进行定位，并对指向的记录中的数据进行操作的数据结构。
    • 前面学习的 MySQL 数据操作语句，都是针对结果集合的。也就是说，每次处理的对象都是一个数据集合。如果需要逐一处理结果集中的记录，就会非常困难。
    • 虽然可以通过筛选条件 WHERE 和 HAVING，或者是限定返回记录的关键字 LIMIT 返回一条记录，但是，却无法在结果集中像指针一样，向前定位一条记录、向后定位一条记录，或者是随意定位到某一条记录，并对记录的数据进行处理；这个时候就可以用到游标。
  • 游标只能在存储程序（存储过程、存储函数）中使用，并且使用方法是一样的
    • 存储函数和存储过程的区别：
      • 存储函数必须返回一个值或者数据表，存储过程可以不返回。
      • 存储过程可以通过 CALL 语句调用，存储函数不可以。
      • 存储函数可以放在查询语句中使用，存储过程不行。
      • 存储过程的功能更加强大，包括能够执行对表的操作（比如创建表，删除表等）和事务操作，存储函数不行。
=========================================存储函数创建=========================================
create function 函数名称 (参数) returns 数据类型 程序体

=========================================定义游标=========================================
declare 游标名 cursor for 查询语句
游标可以操作的数据集是查询语句返回的结果集。

=========================================打开游标=========================================
open 游标名
打开游标之后，系统会为游标准备好查询的结果集，为后面游标的逐条读取结果集中的记录做准备。

=========================================使用游标读取数据=========================================
fetch 游标名 into 变量列表
通过游标，把当前游标指向的结果集中的那一条数据，赋值给列表中的变量；游标的查询结果集中的字段数，必须跟 INTO 后面的变量数一致。

=========================================关闭游标=========================================
close 游标名
游标会占用系统资源，如果不及时关闭，游标会一直保持到存储过程结束，影响系统运行的效率。

18，mysql-流程控制语句

  • 程控制语句也只能用于存储程序（存储过程和存储函数），经常与游标结合使用，主要有：
    • 跳转语句：iterate, leave
    • 循环语句：loop, while, repeat
    • 条件判断语句：if, case
    • 条件处理语句：declare ... handler for ...
  • 跳转语句：
    • iterate：只能用在循环语句内，表示重新开始循环。
    • leave：可以用在循环语句内，或者以 BEGIN 和 END 包裹起来的程序体内，表示跳出循环或者跳出程序体的操作。
  • 循环语句：
    • loop：LOOP 循环不能自己结束，需要用跳转语句 ITERATE 或者 LEAVE 来进行控制。
    标签: loop
操作
end loop 标签;
    • while：WHILE 循环通过判断条件是否为真来决定是否继续执行循环中的操作（先判断条件，再执行循环体中的操作）。
    while 条件 do
操作
end while;
    • repeat：通过判断条件是否为真来决定是否继续执行循环内的操作（REPEAT 循环是先执行操作，后判断条件）。
    repeat
操作
until 条件 end repeat;
  • 条件判断语句：
    • if：不同的表达式对应不同的操作。
    if 表达式1 then 操作1
[elseif 表达式2 then 操作2] ...
[else 操作n]
end if
    • case：表达式不同的值对应不同的操作。
    case 表达式
when 值1 then 操作1
[when 值2 then 操作2] ...
[else 操作n]
end case;
  • 条件处理语句
    • 语法结构
    declare 处理方式 handler for 问题 操作
    • 处理方式有 2 种选择，分别是continue和exit，表示遇到问题，执行了语法结构中的“操作”之后，是选择继续运行程序，还是选择退出，结束程序。
    • 语法结构中的“问题”是指 SQL 操作中遇到了什么问题；比如not found可以表示游标走到结果集的最后，没有记录了。
    • 执行的操作可以是设置变量set 变量名=值等。

19，mysql-触发器

  • 有 2 个或者多个相互关联的表，在修改其中一个表时，为了保证数据的完整性，必须同时对其他表进行修改；这样一来，就必须把关联的操作步骤写到程序里面，而且要用事务包裹起来，确保这关联操作成为一个原子操作，要么全部执行，要么全部不执行；如果遇到特殊情况，可能还需要对数据进行手动维护，这样就很容易忘记其中的一步，导致数据缺失；这个时候，就可以使用触发器，可以通过创建触发器，让对其中一个表的操作自动触发对其他表的操作，这样一来，就不用担心因为忘记操作其他表而导致的数据缺失或不一致了。
  • 语法结构
=========================================创建=========================================
create trigger 触发器名称 {before | after} {insert | update | delete} on 表名 for each row 表达式/程序体;
* before | after：表示触发的时间。before表示在事件之前触发；after表示在事件之后触发。
* insert | update | delete：表示触发的事件。insert 表示插入记录时触发；update 表示更新记录时触发；delete表示删除记录时触发。
* 表名：表示触发器监控的对象。

=========================================查看=========================================
show triggers \G;
 \G 的作用是控制输出的格式，使得查看触发器的显示结果更直观一些

=========================================删除=========================================
drop trigger 触发器名称
  • 在 MySQL 中，如果触发器中的操作失败了，那么触发这个触发器的数据操作也会失败，不会出现一个成功、一个失败的情况（类似事务的功能）。
  • 优点：确保数据的完整性，帮助记录操作日志，可以在操作数据前对数据进行合法性检查。
  • 缺点：可读性差（触发器本身的隐蔽性），数据的变更（特别是数据表结构的变更）可能会导致触发器出错
  • 使用触发器，最好维护一个完整的数据库设计文档。

20，mysql-权限管理

  • 概念：
    • 横向分组：指用户可以接触到的数据的范围，比如可以看到哪些表的数据
    • 纵向分组：指用户对接触到的数据能访问到什么程度，比如能看、能改，甚至是删除
    • 角色：具有相同数据访问范围和程度的用户归成不同的类别，这种类别就叫角色。
      • 角色是在 MySQL 8.0 中引入的新功能，相当于一个权限的集合。引入角色的目的是方便管理拥有相同权限的用户。
    • 通过角色管理用户对数据库访问的范围和程度，这就是对用户的数据访问权限的管理。
  • 流程：为每一个职责创建一个对应的角色，给角色赋予相关的权限，为每个员工创建一个对应的数据库用户，再把角色赋予用户。
  • 角色语法
=========================================创建=========================================
create role 角色名@主机名;
创建一个角色，角色名称是“角色名”，角色可以登录的主机是“主机名”（比如localhost，就是指运行数据库服务器的计算机），如果不写主机名，默认可以从任何一台主机登录数据库

=========================================激活角色=========================================
set global activate_all_roles_on_login = on
MySQL 中创建了角色之后，默认都是没有被激活的，此时不能用，必须进行激活

=========================================角色授权=========================================
grant 权限 on 表名 to 角色名
权限有：insert，delete，update，select，对应增删改查

=========================================撤销角色某个权限=========================================
revoke 权限 on 表名 from 角色名
权限有：insert，delete，update，select，对应增删改查

=========================================查看权限=========================================
show grants for 角色名

=========================================删除=========================================
drop role 角色名
如果删除了角色，那么用户也就失去了通过这个角色所获得的所有权限。
  • 用户语法
=========================================创建=========================================
create user 用户名 [identified by 密码]

=========================================用户授权=========================================
grant 角色名 to 用户名
grant 权限 on 表名 to 用户名

=========================================撤销用户权限=========================================
revoke 角色名 from 用户名

=========================================查看用户权限=========================================
show grants for 用户名

=========================================删除用户=========================================
drop user 用户名

21，mysql-日志

  • 日志中记录了数据库运行中的诊断信息，包括了错误、警告和注释等信息；除了发现错误，日志在数据复制、数据恢复、操作审计，以及确保数据的永久性和一致性等方面，都有着不可替代的作用。
  • MySQL 的日志种类非常多，包括通用查询日志、慢查询日志、错误日志、二进制日志、中继日志、重做日志和回滚日志。

21-1，通用查询日志

  • 功能：记录了所有用户的连接开始时间和截止时间，以及发给 MySQL 数据库服务器的所有 SQL 指令。
  • 用法：当数据发生异常时，开启通用查询日志，还原操作时的具体场景，可以帮助准确定位问题。
  • 语法：
=========================================查看是否开启=========================================
show variables like '%general%';
+------------------+------------------------------------------------------------------+
| Variable_name    | Value                                                            |
+------------------+------------------------------------------------------------------+
| general_log      | OFF                                                              |
| general_log_file | C:\Program Files\MySQL\MySQL Server 8.0\data\DESKTOP-FK4M550.log |
+------------------+------------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
* 系统变量 general_log 的值是 OFF，表示通用查询日志处于关闭状态。在 MySQL 中，这个参数的默认值是关闭的。因为一旦开启记录通用查询日志，MySQL 会记录所有的连接起止和相关的 SQL 操作，这样会消耗系统资源并且占用磁盘空间。可以通过手动修改变量的值，在需要的时候开启日志。
* general_log_file表示可以在哪查看通用日志的内容

=========================================开启以及设置保存路径=========================================
set global general_log = 'on';
set @@global.general_log_file = '希望的路径\mytest.log';
路径不能有中文，log文件可以用记事本打开进行查看

=========================================备份=========================================
========先关闭========
set global general_log = 'off';
========将旧的log日志移走========
========再开启========
set global general_log = 'on';

21-2，慢查询日志

  • 功能：记录执行时间超过指定时长的查询。
  • 用法：发现那些执行时间特别长的 SQL 查询，并且有针对性地进行优化，从而提高系统的整体效率。当数据库服务器发生阻塞、运行变慢的时候，检查一下慢查询日志，找到那些慢查询，对解决问题很有帮助。
  • 慢查询日志是由 MySQL 的配置文件进行控制的，配置文件地址C:\ProgramData\MySQL\MySQL Server 8.0\my.ini，配置文件中与慢查询日志相关的内容
    slow-query-log=1 -- 表示开启慢查询日志，系统将会对慢查询进行记录。

slow_query_log_file="DESKTOP-FK4M550-slow.log" -- 表示慢查询日志的名称是"DESKTOP-FK4M550-slow.log"。这里没有指定文件夹，默认就是数据目录："C:\ProgramData\MySQL\MySQL Server 8.0\Data"。

long_query_time=10  -- 表示慢查询的标准是查询执行时间超过10秒
  • 如果修改了 MySQL 的配置文件“my.ini”，就需要重启服务器，这样才能使修改生效。
  • 除了配置文件，控制慢查询日志的还有一个系统变量：min_examined_row_limit。这个变量的意思是，查询扫描过的最少记录数。这个变量和查询执行时间，共同组成了判别一个查询是否是慢查询的条件。如果查询扫描过的记录数大于等于这个变量的值，并且查询执行时间超过 long_query_time 的值，那么，这个查询就被记录到慢查询日志中；反之，则不被记录到慢查询日志中。
=====================查看min_examined_row_limit=====================
mysql> show variables like 'min%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| min_examined_row_limit | 0     |
+------------------------+-------+
1 row in set, 1 warning (0.01 sec)
* 这个值默认是 0。与 long_query_time=10 合在一起，表示只要查询的执行时间超过 10 秒钟，哪怕一个记录也没有扫描过，都要被记录到慢查询日志中。

=========================================设置=========================================
set global min_examined_row_limit = 10;

21-3，错误日志

  • 错误日志记录了 MySQL 服务器启动、停止运行的时间，存储引擎 InnoDB 启动、停止的时间，以及系统启动、运行和停止过程中的诊断信息，包括错误、警告和提示等。当数据库服务器发生系统故障时，错误日志是发现问题、解决故障的首选。
  • 错误日志默认是开启的，可以在 MySQL 的配置文件“my.ini”中配置它：
log-error="DESKTOP-FK4M550.err" -- 表示错误日志的名称是"DESKTOP-FK4M550.err"。这里没有指定文件夹，默认就是数据目录："C:\ProgramData\MySQL\MySQL Server 8.0\Data"。

21-4，二进制日志

  • 二进制日志主要记录数据库的更新事件，比如创建数据表、更新表中的数据、数据更新所花费的时长等信息。通过这些信息，我们可以再现数据更新操作的全过程。而且，由于日志的延续性和时效性，我们还可以利用日志，完成无损失的数据恢复和主从服务器之间的数据同步。可以说，二进制日志是进行数据恢复和数据复制的利器。
  • 每当服务器启动、刷新二进制日志或者二进制日志大小超过系统变量 max_binlog_size 指定的值时，系统就会生成一个新的二进制日志文件。
  • 在实际工作中，用二进制日志文件找回数据时经常会遇到问题，主要就是不容易找准起始位置或者截止位置。找早了，会导致数据冲突、重复；找晚了，又会丢失数据。所以，建议在数据备份结束之后，把当前的二进制日志位置记录下来，存放在一个安全的地方，这关系到数据恢复的完整性，一定不要怕麻烦。
  • 二进制日志还有一个重要的功能，就是在主从服务器的架构中，把主服务器的操作复制到从服务器。而这个操作要借助中继日志一起完成。
  • 操作
=========================================查看=========================================
================查看当前正在写入的二进制日志================
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 |      477 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
================查看所有二进制日志================
show binary logs;
================查看日志中所有数据更新事件的 SQL 语句================
show binlog events in 二进制文件名;

=========================================刷新=========================================
flush binary logs;
关闭服务器正在写入的二进制日志文件，并重新打开一个新文件，文件名的后缀在现有的基础上加 1，之后的记录也会保存在新的文件中；
当需要将一段日志文件保存下来时可以用此命令

=========================================用二进制日志恢复数据=========================================
mysqlbinlog --start-positon=xxx --stop-position=yyy 二进制文件名 | mysql -u 用户 -p
执行二进制日志中从位置 xxx 开始，到 yyy 截止的所有数据更新操作。
这里的截止位置也可以不写，意思是从位置 xxx 开始，执行二进制文件中的所有数据更新操作。

=========================================删除二进制文件=========================================
reset master;
需要先将日志文件保存到了安全的地方，然后通过reset语句删除所有二进制日志文件，以释放磁盘空间
purge master logs to 'binlog.000005';
删除比指定二进制日志文件编号小的所有二进制日志文件

21-5，中继日志

  • 中继日志只在主从服务器架构的从服务器上存在。从服务器为了与主服务器保持一致，要从主服务器读取二进制日志的内容，并且把读取到的信息写入本地的日志文件中，这个从服务器本地的日志文件就叫中继日志。然后，从服务器读取中继日志，并根据中继日志的内容对从服务器的数据进行更新，完成主从服务器的数据同步。
  • 搭建好主从服务器之后，中继日志默认会保存在从服务器的数据目录（C:\ProgramData\MySQL\MySQL Server 8.0\Data）下，文件名的格式是：从服务器名 -relay-bin. 序号。
  • 中继日志还有一个索引文件：从服务器名 -relay-bin.index，用来定位当前正在使用的中继日志。
  • 中继日志与二进制日志的格式相同，可以用 mysqlbinlog 工具进行查看。
  • 关于中继日志，有一个很容易踩到的坑。如果从服务器宕机，有的时候为了系统恢复，你要重装操作系统，这样就可能会导致你的服务器名称与之前不同。而中继日志的名称里面是包含从服务器名的。因此，在这种情况下，就可能导致你恢复从服务器的时候，无法从宕机前的中继日志里读取数据，以为是日志文件损坏了，其实是文件名不对了。解决的方法也很简单，只要把从服务器的名称改回之前的名称就可以了。

21-6，回滚日志

  • 回滚日志的作用是进行事务回滚。当事务执行的时候，回滚日志中记录了事务中每次数据更新前的状态。当事务需要回滚的时候，可以通过读取回滚日志，恢复到指定的位置。另一方面，回滚日志也可以让其他的事务读取到这个事务对数据更改之前的值，从而确保了其他事务可以不受这个事务修改数据的影响。
  • 操作：
==============================查看单个回滚日志最大可占用的存储空间大小==============================
mysql> SHOW VARIABLES LIKE '%innodb_max_undo_log_size%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| innodb_max_undo_log_size | 1073741824 |
+--------------------------+------------+
1 row in set, 1 warning (0.07 sec)

=============查看回滚日志回滚日志所在的文件夹、是否加密、是否自动截断回收空间和是否有独立的表空间=============
mysql> SHOW VARIABLES LIKE '%innodb_undo%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_undo_directory    | .\    |      -- 表示回滚日志的存储目录是数据目录，数据目录的位置可以通过查询变量“datadir”来查看。
| innodb_undo_log_encrypt  | OFF   |      -- 表示回滚日志不加密。
| innodb_undo_log_truncate | ON    |      -- 表示回滚日志是否自动截断回收，有效的前提是设置了独立表空间。
| innodb_undo_tablespaces  | 2     |      -- 表示回滚日志有自己的独立表空间，而不是在共享表空间ibdata文件中。
+--------------------------+-------+
4 rows in set, 1 warning (0.00 sec)
* 存储目录为：C:\ProgramData\MySQL\MySQL Server 8.0\Data
* 存储目录中的undo_001为回滚日志独立表空间文件

21-7，重做日志

  • 重做日志是存储在磁盘上的一种日志文件，主要有 2 个作用。
    • 在系统遇到故障的恢复过程中，可以修复被未完成的事务修改的数据；
    • MySQL 为了提高数据存取的效率，减少磁盘操作的频率，对数据的更新操作不会立即写到磁盘上，而是把数据更新先保存在内存中，积累到一定程度，再集中进行磁盘读写操作。这样就存在一个问题：一旦出现宕机或者停电等异常情况，内存中保存的数据更新操作可能会丢失。这个时候就可以通过读取重做日志中记录的数据更新操作，把没来得及写到磁盘上的数据更新写到磁盘上，确保数据的完整性。
  • 操作：
==============================查看重做日志所在的文件夹和文件的数量==============================
mysql> SHOW VARIABLES LIKE '%innodb_log_files_in_group%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| innodb_log_files_in_group | 2     |
+---------------------------+-------+
1 row in set, 1 warning (0.00 sec)
* 表示有两个重做日志文件
* 变量 innodb_log_files_in_group 值的取值范围是 1~4，这四个文件分别用于记录不同的操作：用户创建表的插入操作；用户创建表的更新和删除操作；临时表的插入操作；临时表的更新和删除操作。

21-8，总结：

  • 通用查询日志：可以记录所有连接的起始时间和终止时间，以及连接发送给数据库服务器的所有指令，对我们复原操作的实际场景、发现问题，甚至是对数据库操作的审计都有很大的帮助。
  • 慢查询日志：可以记录运行时间和检查记录数超过指定值的查询，方便我们对查询进行优化。
  • 错误日志：它记录了服务器启动、运行和停止过程中的诊断信息，方便我们了解服务器的状态，从而对服务器进行维护。
  • 二进制日志：主要用于主从服务器之间的数据同步（放在主服务器上），以及服务器遇到故障时数据的无损失恢复。
  • 中继日志：就是主从服务器架构中，从服务器用来存放主服务器二进制日志内容的一个中间文件（放在从服务器上）。从服务器通过读取中继日志的内容，来同步主服务器上的操作。
  • 回滚日志：用来存储事务中数据更新前的状态，以便回滚和保持其他事务的数据一致性。
  • 重做日志：是为了确保数值持久性、防止数据更新丢失的一种日志。
  • 如果遇到数据量大、数据库和数据表很多（比如分库分表的应用）的场景，用二进制日志进行数据恢复，是很有挑战性的，因为起止位置不容易管理。在这种情况下，一个有效的解决办法是配置主从数据库服务器，甚至是一主多从的架构，把二进制日志文件的内容通过中继日志，同步到从数据库服务器中，这样就可以有效避免数据库故障导致的数据异常等问题。
  • https://dev.mysql.com/doc/ 官方文件是个好东西

22，mysql-数据备份和恢复-数据导出和导入

  • 数据备份和恢复
    • 备份的两种形式：
      • 物理备份：通过把数据文件复制出来，达到备份的目的
      • 逻辑备份：通过把描述数据库结构和内容的信息保存起来，达到备份的目的
    • 数据备份工具-mysqldump，它有三种模式：
      • 备份数据库中的表
      mysqldump -h 服务器 -u 用户 -p 密码 数据库名称 [表名称...] > 备份文件名称;
* “-h”后面跟的服务器名称，如果省略，默认是本机“localhost”。
* “-p”后面跟的是密码，如果省略，执行的时候系统会提示录入密码。
* 备份文件是以文本格式保存的，我们可以用记事本打开，看一下备份的内容；备份的文件相当于一个 SQL 执行脚本，里面包括了创建表以及对表进行操作的SQL语句。
      • 备份整个数据库
      mysqldump -h 服务器 -u 用户 -p 密码 --databases 数据库名称... > 备份文件名称;
      • 备份整个数据库服务器
      mysqldump -h 服务器 -u 用户 -p 密码 --all-databases > 备份文件名称;
    • 数据恢复
      • mysqldump 的备份文件包含了创建数据库、数据表，以及插入数据表里原有数据的 SQL 语句，我们可以直接运行这些 SQL 语句，来进行数据恢复。
      • 方式有两种：
        • 使用“mysql”命令行客户端工具进行数据恢复；
        • 使用“SOURCE”语句进行数据恢复。
      • 语句：
        mysql -u 用户 -p 密码 数据库名称 < 备份文件名称;
source 备份文件名称;
* 使用source前需要先用“USE”语句把当前的数据库变更为 备份文件的同名数据库，这样商品信息表和会员表才能恢复到正确的数据库里面。否则，可能会恢复错误。
  • 数据导出和导入
    • 数据导出：把一个表的数据按照一定的格式，导出成一个文件。
      • 语句：
      SELECT 字段列表 INTO OUTFILE 文件名称
FIELDS TERMINATED BY 字符1
LINES TERMINATED BY 字符2
FROM 表名;
* INTO OUTFILE 文件名称，表示查询的结果保存到文件名称指定的文件中；
* FIELDS TERMINATED BY 字符1，表示列之间的分隔符是“字符1”；
* LINES TERMINATED BY 字符2，表示行之间的分隔符是“字符2”。
      • 导出的时候可能因为服务器的“secure-file-priv”参数选项，不允许把文件写入到指定文件中而出现错误，此时可以通过MySQL 的配置文件 C:\ProgramData\MySQL\MySQL Server 8.0\my.ini，来查看一下“secure-file-priv”参数的设定，并且按照这个参数设定的要求对sql导出语句中对文件名称进行修改；或者在配置文件中将“secure-file-priv”参数改为自己想要的文件地址，并重新启动mysql服务器。
    • 数据导入：“LOAD DATA”是很好用的工具，因为它的数据导入速度是非常惊人的。一个 400 万条数据的文件，用“LOAD DATA”语句，只需要几分钟就可以完成，而其他的方法，比如使用 Workbench 来导入数据，就需要花费好几个小时。
      • 语句：
      LOAD DATA INFILE 文件名
INTO TABLE 表名
FIELDS TERMINATED BY 字符
LINES TERMINATED BY 字符;

23，mysql-范式

  • 数据表结构设计不合理，会导致数据重复现象，严重时系统虽然能够运行，承载能力却很差，稍微有点流量，就会出现内存不足、CUP 使用率飙升的情况，甚至会导致整个项目失败；所以，高效可靠的设计是提升数据库工作效率的关键；范式就是用来指导数据表结构设计的；遵循范式的要求，可以减少冗余，结合外键约束，可以防止添加、删除、修改数据时产生数据的不一致问题。
  • 三大范式：
    • 第一范式（1NF）：所有的字段都是基本数据字段，不可进一步拆分；符合该范式的数据表中的每一个字段都是不可拆分的最小信息单元。
    • 第二范式（2NF）：在满足第一范式的基础上，还要满足数据表里的每一条数据记录，都是可唯一标识的；而且所有非主键字段都必须完全依赖主键字段，不能存在部分依赖主键字段的字段。
      • 使用该范式，首先就要确定数据表的主键（可以是联合主键）
      • 确定好了主键以后判断一下：哪些字段完全依赖主键，哪些字段只依赖于主键的一部分。同时，把只依赖于主键一部分的字段，以及它们所依赖的部分主键拆分出去，形成新的数据表，这样新的数据表就是所有字段都完全依赖主键的数据表了。
      • 对于拆分好的数据表分析一下，看是否都满足第二范式，如果存在所有的字段都不能唯一标识表里的记录的情况，就给该表添加一个主键（自增字段“itemnumber”）。
    • 第三范式（3NF）（消除冗余）：在满足第二范式的基础上，数据表中不能包含可以由非主键字段派生出来的字段，或者说，不能存在依赖于非主键字段的字段；如果存在就继续拆分或者删除某个字段。
  • 业务优先原则：
    • 一切以业务需求为主，技术服务于业务。完全按照理论的设计不一定就是最优，还要根据实际情况来决定。（比如第三范式的分析本应删除的某字段，可能因为业务的便利性保留下来。）
    • 本着业务优先的原则，在不影响系统可靠性的前提下，可以容忍一定程度的数据冗余（这样的数据看似冗余，其实对业务来说十分重要）。
  • 其他范式：

A-附录

A-1-概念总结

  • 视图，存储过程，存储函数，触发器这些对象都保存在数据库中，对于应用系统来说具有很大的隐蔽性，维护和管理它们还是有很高成本的。

A-2-数据库备份以及恢复

mysqldump -u 用户 -p 密码 数据库名称 > 备份文件
mysql -u 用户 -p 密码 数据库名称 > 备份文件

B-附录

B-1-常问问题

https://mp.weixin.qq.com/s/CbpU9IVTqDkQlZ5V6c_Txw

posted @ 2021-08-03 16:16  tensor_zhang  阅读(65)  评论(0)  编辑  收藏  举报