数据库（MySQL）

一、数据库理论知识

数据库：

存储数据的仓库，是长期存放在计算机内、有组织、可共享的大量数据的集合。

数据库中的数据按照一定数据模型组织、描述和存储，具有较小的冗余度，较高的独立性和易扩展性，并为各种用户共享，总结为以下几点：

1. 数据结构化
2. 数据的共享性高，冗余度低，易扩充
3. 数据独立性高
4. 数据由 DBMS 统一管理和控制（安全性、完整性、并发控制、故障恢复）

1.1 数据库管理系统（DBMS）

1.1.1 概念

数据库管理系统(DataBase ManagermentSystem，简称DBMS)是管理数据库的一个软件，它充当所有数据的知识库，并对它的存储、安全、一致性、并发操作、恢复和访问负责。是对数据库的一种完整和统一的管理和控制机制。

DBMS是所有数据的知识库，并对数据的存储、安全、一致性、并发操作、恢复和访问负责。

1.1.2 DBMS支持的数据模型

层次模型：

网状模型：

关系（表）模型：

1.2 MySQL架构

MySQL是一个小型关系数据库管理系统
目前MySQL被广泛地应用在Internet上的中小型网站中。由于体积小、速度快、总体拥有成本低，尤其是开放源代码这一特点，许多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据库。

提供TCP/IP、ODBC和JDBC等多种数据库连接途径。
提供用于管理、检查、优化数据库操作的管理工具。
可以处理拥有上千万条记录的大型数据库。

共分为四层：网络连接层、数据库服务层、存储引擎层、系统文件层

1.2.1 网络连接层

MySQL是一个单进程多线程架构的数据库，通过数据库连接池处理所有客户端接入的工作

可以通过 show processlist; 命令查询所有正在运行的线程：

event_scheduler:事件调度器账户

关于最大连接数的一些命令主要有两条：
show variables like ‘%max_connections%’; ：查询目前 DB 的最大连接数。
set GLOBAL max_connections = 200; ：修改数据库的最大连接数为指定值。
MySQL 本身还会对客户端的连接数进行统计，对于这点可以通过命令 show status like “Threads%”; 查询

其中各个字段的释义如下：

• Threads_cached ：目前空闲的数据库连接数。
• Threads_connected ：当前数据库存活的数据库连接数。
• Threads_created ： MySQL-Server 运行至今，累计创建的连接数。
• Threads_running ：目前正在执行的数据库连接数。

1.2.2 数据库服务层

MySQL 大多数核心功能都位于这一层

包括客户端 SQL 请求解析、语义分析、查询优化、缓存以及所有的内置函数（例如：日期、时间、统计、加密函数…）
所有跨引擎的功能都在这一层实现，譬如存储过程、触发器和视图等一系列服务

• SQL接口组件

  • 作用：接收客户端的 SQL 命令，比如 DML、DDL 语句以及存储过程、触发器等，当收到 SQL 语句时，SQL 接口会将其分发给其他组件，然后等待接收执行结果的返回，最后会将其返回给客户端
  • SQL 语句分为五大类：
    • DML：数据库操作语句，比如 update、delete、insert 等都属于这个分类。
    • DDL：数据库定义语句，比如 create、alter、drop 等都属于这个分类。
    • DQL：数据库查询语句，比如最常见的 select 就属于这个分类。
    • DCL：数据库控制语句，比如 grant、revoke 控制权限的语句都属于这个分类
    • TCL：事务控制语句，例如 commit、rollback、setpoint 等语句属于这个分类

• 解析器：主要作用是词法分析、语义分析、语法树生成等等，即验证 SQL 语句是否正确，以及将 SQL 语句解析成 MySQL 能看懂的机器码指令

• 优化器：作用是生成执行计划，比如选择最合适的索引，选择最合适的 join 方式等，最终会选择出一套最优的执行计划，维护当前连接的线程会负责根据计划去执行 SQL

• 缓存和缓冲区

  • 读取缓存（主要是select语句）

    • 作用： MySQL 会对于一些经常执行的查询 SQL 语句，将其结果保存在 Cache 中，因为这些 SQL经常执行，因此如果下次再出现相同的 SQL 时，能从内存缓存中直接命中数据，自然会比走磁盘效率更高

  • 写入缓冲区 （减少大量的磁盘 IO ，从而进一步提高数据库整体性能）

    • 缓冲区的设计主要是：为了通过内存的速度来弥补磁盘速度较慢对数据库造成的性能影响。
    • 数据库进行 写操作 时，都会先从缓冲区中查询是否有要操作的页，如果有，则直接对内存中的数据页进行操作（例如修改、删除等），对缓冲区中的数据操作完成后，会直接给客户端返回成功的信息，然后 MySQL 会在后台利用一种名为 Checkpoint 的机制，将内存中更新的数据刷写到磁盘

1.2.3 存储引擎层

存储引擎也可以理解成 MySQL 最重要的一层

在前面的服务层中，聚集了 MySQL 所有的核心逻辑操作，而引擎层则负责具体的数据操作以及执行工作

MySQL 因为其开源特性，存在很多很多款不同的存储引擎实现，MySQL 为了能够正常搭载不同的存储引擎运行，因此引擎层是被设计成可拔插式的，也就是可以根据业务特性，为自己的数据库选择不同的存储引擎

MySQL 目前有非常多的存储引擎可选择，其中最为常用的则是 InnoDB 与 MyISAM 引擎，可以通过show variables like ‘%storage_engine%’; 命令来查看当前所使用的引擎

1.2.4 系统文件层

日志板块和数据板块。该层是 MySQL 数据库的基础

本质上就是基于机器物理磁盘的一个文件系统，其中包含了配置文件、库表结构文件、数据文件、索引文件、日志文件等各类MySQL 运行时所需的文件，这一层的功能比较简单，也就是与上层的存储引擎做交互，负责数据的最终存储与持久化工作。

日志板块：

1. binlog 二进制日志，主要记录 MySQL 数据库的所有 写操作（增删改）。
2. redo-log 重做/重写 日志， MySQL 崩溃时，对于未落盘的操作会记录在这里面，用于重启时重新落盘（ InnoDB 专有的）。
3. undo-logs 撤销/回滚 日志：记录事务开始前[修改数据]的备份，用于回滚事务。
4. error-log ：错误日志，记录 MySQL 启动、运行、停止时的错误信息。
5. general-log 常规日志，主要记录 MySQL 收到的每一个查询或 SQL 命令。
6. slow-log ：慢查询日志，主要记录执行时间较长的 SQL 。
7. relay-log ：中继日志，主要用于主从复制做数据拷贝。

数据板块：MySQL 的所有数据最终都会落盘（写入到磁盘），而不同的数据在磁盘空间中，存储的格式也并不相同

• db.opt 文件：主要 记录 当前数据库使用的字符集和验证规则等信息
• .frm 文件：存储 表结构的元数据信息文件，每张表都会有一个这样的文件。
• .MYD 文件：用于 存储 表中所有数据的文件（ MyISAM 引擎独有的）。
• .MYI 文件：用于 存储 表中索引信息的文件（ MyISAM 引擎独有的）。
• .ibd 文件：用于 存储 表数据和索引信息的文件（ InnoDB 引擎独有的）。
• .ibdata 文件：用于 存储 共享表空间的数据和索引的文件（ InnoDB 引擎独有）。
• .ibdata1 文件：这个主要是用于 存储 MySQL 系统（自带）表数据及结构的文件。
• .ib_logfile0/.ib_logfile1 文件：用于故障数据恢复时的日志文件。
• .cnf/.ini ： MySQL 的配置文件， Windows 下是 .ini ，其他系统大多为 .cnf
• … …

二、MySQL安装部署

win11安装配置MySQL超详细教程（保姆级）: https://baijiahao.baidu.com/s?id=1786910666566008458&wfr=spider&for=pc

2.1 mysql（win11）安装环境准备

1. 检查本机主机名是否为英文，不能为中文

   • 右键此电脑-属性-高级系统设置-计算机名
     

2. 若已经安装了mysql，则：
   （1）检查是否安装了历史mysql，要求在系统中卸载
   （2）删除磁盘中所有mysql命名文件夹（注意隐藏文件）
   

   （3）清除注册表（win+R-输入regedit进入注册表编辑器-HEKY_LOCAL_MACHINE-SYSTEM…）
   

3. 关闭所有安全类软件：360全家桶 杀毒（火绒） 管家（Windows安全中心不用关）

4. 准备好运行库

   • VC_redist.x64：运行库，针对MySQL
   • Scku：运行库，所有都包括

5. 找到运行服务界面（win右键-任务管理器-）

6. 找到环境变量设置界面（电脑搜索栏-高级系统设置-高级-环境变量）

7. 找到 命令控制台 或 powershell （注意管理员权限打开）
   （电脑搜索栏-powershell）

MySQL官网: https://www.mysql.com/

2.2 zip安装

DOWNLOADS

点击社区版，社区版本，开源免费，但不提供官方技术支持。

出名版本：5.7、8.0、8.4（LTS：长期更新）、9.x

2.3 mis安装

双击 msi 文件

软件安装详情步骤： https://baijiahao.baidu.com/s?id=1786910666566008458&wfr=spider&for=pc

2.4 本地RPM包安装

官网下载：链接: https://downloads.mysql.com/archives/community/

虚拟机红帽系统：


使用 xftp 将集合包上传到/目录

三、MySQL库表操作

• 登录MySQL：

mysql -uroot -p

mysql -u用户名 -h服务器的主机地址 -p密码 -A

• -u 后面跟登录数据库的用户名，这里使用root
• -h 后面的参数是服务器的主机地址，在这里客户端和服务器在同一台机器上，所以输入 localhost 或者 IP 地址
• -p 后面是用户登录密码，注意：-p 和密码之间没有空格。如果出现空格，系统将不会把后面的字符串当成密码来对待，没有密码时不写
• -A参数：不预读数据库信息，加上该参数则不预读数据库中的表等信息，不会有表名及列名的补全，但读取速度会加快，不加该参数可能使用数据库时出现下列提示（不影响使用）

3.1 SQL语句基础

SQL：结构化查询语言(Structured Query Language)，在关系型数据库上执行数据操作、数据检索以及数据维护的标准语言。

• 使用SQL语句，程序员和数据库管理员可以完成如下的任务
  • 改变数据库的结构
  • 更改系统的安全设置
  • 增加用户对数据库或表的许可权限
  • 在数据库中检索需要的信息
  • 对数据库的信息进行更新

MySQL致力于支持全套ANSI/ISO SQL标准。在MySQL数据库中，SQL语句主要可以划分为以下几类

• DDL(Data Definition Language):数据定义语言，定义对数据库对象(库、表、列、索引)的操作，如：create、drop、alter、rename、 truncate等
• DML(Data Manipulation Language): 数据操作语言，定义对数据库记录的操作，如：insert、delete、update、select等
• DCL(Data Control Language): 数据控制语言，定义对数据库、表、字段、用户的访问权限和安全级别，如grant、revoke、transaction、control、commit、rollback、savepoint等

SQL语句可单行或多行书写，以“;”结尾
/* */ 和// 为多行注释
-- 和 # 为 单行注释

带有通配符的条件：

%：匹配任意零个或多个字符
_ ：匹配任意单个字符

命令：

• 退出：

退出：exit quit \q

• 清屏：

清屏：\! cls

常用命令：

\G表示将查询结果进行按列打印，即将查到的结构旋转90度变成纵向显示,不需要分号
\g #相当于分号
\! cls #清屏
exit、quit、\q #退出

3.2 数据库操作

3.2.1 数据库常用操作命令

数据库的登录及退出

mysql -uroot -p #登录
exit quit \q #退出

查看当前用户

select database();

mysql> select database();
+------------+
| database() |
+------------+
| NULL |
+------------+

切换数据库

use 数据库名

mysql> use testdb;
Database changed
mysql> select database(); # 再次查看
+------------+
| database() |
+------------+
| testdb |
+------------+

显示时间

mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2023-02-10 09:39:14 |
+---------------------+
1 row in set (0.00 sec)

显示数据库版本

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.37 |
+-----------+
1 row in set (0.00 sec)

3.2.2 查看库 show databases

show databases [like 条件];

注意有 s

mysql> show databases; #注意有 s
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.26 sec)

mysql> select database(); #查看当前库
+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.01 sec)

Information_schema: 主要存储了系统中的一些数据库对象信息，如：用户表信息、列信息、权限信息、字符集信息、分区信息等。(数据字典表)
performance_schema：主要存储数据库服务器的性能参数
mysql: 存储了系统的用户权限信息及帮助信息
sys: 5.7新增，之前版本需要手工导入。这个库是通过视图的形式把information_schema 和performance_schema结合起来，查询出更加令人容易理解的数据

3.2.3 创建库 create database 数据库名

• 数据库命名尽量不超过30个字符
• 数据库命名一般为项目名称+代表库含义的简写，比如IM项目的工作流数据库，可以是 im_flow
• 命名应使用小写

create database 数据库名

mysql> create database mydb1_test;
Query OK, 1 row affected (0.04 sec)

mysql> use mydb1_test;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| mydb1_test |
+------------+
1 row in set (0.00 sec)

3.2.4 删除库 drop database 数据库名

drop database 数据库名

3.3 MySQL字符集

MySQL字符集包括字符集（CHARACTER）和排序规则(校对规则)（COLLATION）两个概念

3.3.1 字符集

Character Set(字符集)：是字符的编码规则，规定了字符在数据库中的存储格式，比如占多少空间，支持哪些字符等

latin1支持西欧字符、希腊字符等
gbk支持中文简体字符，但是不是国际通用字符集
big5支持中文繁体字符
utf8几乎支持世界所有国家的字符。
utf8mb4完全兼容UTF-8，用四个字节存储更多的字符

查看所有支持的字符集

mysql> show character set;
+----------+---------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+---------------------------+---------------------+--------+
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| binary | Binary pseudo charset | binary | 1 |
…………
# 字段含义
# Charset: 字符集的名称；
# Description：字符集的简单描述；
# Default collation：该字符集的默认字符序；
# Maxlen：该字符集中字符最大存储长度。

3.3.2 字符序

字符序就是字符排序的规则集合

• MySQL字符序命名规则：
  • 以字符序对应的字符集名称开头
  • 以国家名居中（或以general居中）
  • 后缀：
    • bin：二进制；
    • ci：大小写不敏感；
    • cs：大小写敏感；
    • ai：口音（Accent）不敏感；
    • as：口音敏感；
    • ks：假名（Kanatype）敏感

# 查看支持的字符序
mysql> show collation;

# 字段含义
	# Collation：字符序名称；
	# Charset：该字符序关联的字符集；
	# Id：字符序ID；
	# Default：该字符序是否是所关联的字符集的默认字符序。armscii8_general_ci就是armscii8的默
	认字符序，而armscii8_bin就不是；
	# Compiled：字符集是否已编译到服务器中；
	# Sortlen：这与对以字符集表示的字符串进行排序所需的内存量有关；
	# Pad_attribute：这表明了字符序在比较字符串时对末尾padding的处理。NO PAD表明在比较字符串	时，末尾的padding也会考虑进去，否则不考虑。

指定条件查询

mysql> show collation where charset = "utf8mb4";

3.4 数据库对象

组成：

数据库对象的命名规则：

生产中对象命名规范：

3.5 表的基本操作

3.5.1 数据类型

可以使用float(M，D)、double(M，D)格式限制宽度按（M）和精度（D），如float（3,2），不指定M、D的时，会按照实际的精度来处理

3.5.2 创建表

create table 表名 （表选项）

表定义选项格式为：

列名1 列类型1 约束, 列名2 列类型2 约束,……

表的名称命名规范：

• 常规表：以t_开头，t代表table的意思，命名规则即 t + 模块（包含模块含义的简写）+ 表（包含表含义的简写），比如用户模块的教育信息表：t_user_eduinfo
• 临时表：temp前缀+模块+表+日期后缀：temp_user_eduinfo_20240520
• 备份表（用于保存和归档历史数据或者作为灾备恢复的数据）命名规则，bak前缀+模块+表+日期后缀：bak_user_eduinfo_20231219
• 同一个模块的表尽可能使用相同的前缀，表名称尽可能表达含义
• 多个单词以下划线 _ 分隔
• 常规表表名尽量不超过30个字符，temp表和bak表视情况而定，也尽量简短为宜，命名应使用小写

mysql> create table t1(float_num float(10,2),double_num double(20,2),decimal_num decimal(20,2));
Query OK, 0 rows affected, 2 warnings (0.11 sec)

mysql> show tables;
+----------------------+
| Tables_in_mydb1_test |
+----------------------+
| t1                   |
+----------------------+
1 row in set (0.07 sec)

mysql> desc t1; #或者 show columns from 表名称;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| float_num   | float(10,2)   | YES  |     | NULL    |       |
| double_num  | double(20,2)  | YES  |     | NULL    |       |
| decimal_num | decimal(20,2) | YES  |     | NULL    |       |
+-------------+---------------+------+-----+---------+-------+
3 rows in set (0.10 sec)

字段意义分析：

Field :字段名称
type：字段类型
null：是否允许为空
key：索引类型
default：默认值
extra：填充

3.5.3 删除表

用户必须拥有执行 drop table 命令的权限，否则数据表不会被删除

drop table [if exists] 表名；

推荐使用if exists字句，即先判断是否存在，存在则删除

mysql> drop table if exists tb1;
Query OK, 0 rows affected (0.01 sec)

3.5.4 修改表

更改表名（重命名）:

# 法1：
alter table 表名 rename 新表名;
# 法2：
rename table 表名 to 新表名;

添加新列

alter table 表名 add 新列名 列类型 [after|first] 列名;
# after：在指定列之后插入新列
# first：在第一列插入新列
# 注意：无before关键字

# 例：增加一列password
mysql> alter table student1 add password char(8);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 注意：数据库操作后得到的反馈，表明操作的结果。这个信息表示：
	# Records: 2: 表示成功导入或处理的记录总数是2条。
	# Duplicates: 0: 表示在操作过程中没有发现重复的记录。
	# Warnings: 0: 表示在操作过程中没有产生任何警告。

删除列

alter table 表名 DROP 列名;

修改列名

alter table 表名 change 旧列名 新列名 列类型;

修改列类型

alter table 表名 modify 列名 列类型;

修改列位置

alter table 表名 modify 列名 列类型 after 某列;

3.5.5 查看表

show tables [from 数据库名][like wild];

mysql> show tables;
+-------------------------+
| Tables_in_mydb2_stuinfo |
+-------------------------+
| class1 |
| student1 |
+-------------------------+
2 rows in set (0.00 sec)
mysql> show tables from mydb3_employee; # 查看另一个库的表示信息
+--------------------------+
| Tables_in_mydb3_employee |
+--------------------------+
| tb1 |
+--------------------------+
1 row in set (0.00 sec)

3.5.6 复制表的结构

方法一：在create table语句的末尾添加like子句，可以将源表的表结构复制到新表中

create table 新表名 like 源表

注意：只是复制结构框架

方法二：在create table语句的末尾添加一个select语句，可以实现表结构的复制，甚至可以将源表的表记
录拷贝到新表中

create table 新表名 select * from 源表

方法三：如果已经存在一张结构一致的表，复制数据:

mysql> select * from student2;
Empty set (0.00 sec)
mysql> insert into student2 select * from student1;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

3.6 表的约束

3.6.1 概念与语法

约束是什么：约束是 在表上 强制执行 的数据校验规则，本质上是Mysql通过 限制用户操作 的方式,来达到 维护数据本身安全及数据完整性 的一套方案

常见的约束：

• 非空约束 not null
• 默认值约束 default
• 主键约束 primary key
• 外键约束
• 唯一约束
• 检查约束

列级约束：在定义列的同时定义约束

create table 表名 (列名 类型 约束 )

表级约束：在定义了所有列之后定义的约束

create table 表名 constraint 约束名 约束类型 (列信息)

创建表之后添加约束：

alter table 表名 add constraint 约束名 约束类型(要约束的列名)

创建表之后修改约束：

alter table <数据表名> change column <字段名> <字段名> <数据类型> not null;
# 也可以通过modify修改
mysql> alter table <数据表名> modify  <字段名> <数据类型> not null;

3.6.2 非空约束 not null

作用：限定 某个字段/某列的值 不允许为空
如果不写约束，默认为null

若需要某些字段必须写入，应该怎么处理，如：创建一个班级表，包含班级名和班级所在的教室，站在正常的业务逻辑中：如果班级没有名字，你不知道你在哪个班级，如果教室名字可以为空，就不知道在哪上课，所以我们在设计数据库表的时候，一定要在表中进行限制，满足上面条件的数据就不能插入到表中，这就是“约束

删除not null

alter table <数据表名> modify  <字段名> <数据类型>  null;
# 或者
mysql> alter table <数据表名> modify <字段名> <数据类型>;

3.6.3 默认值约束 default’ ’

作用：给某个字段/某列指定默认值，一旦设置默认值，在插入数据时，如果此字段没有显式赋值，则赋值为默认值。

not null和defalut一般不需要同时出现,因为default本身有默认值,不会为空

3.6.4 列描述 comment’ ’

3.6.5 主键约束 primary key

作用：数据库存储数据不是唯一的目的,还要考虑到提取数据，一般要求 数据库表中的记录要有一个特定的唯一标识 ，来表示唯一性,这个唯一的特定标识就是主键,类似于序号学号这样的唯一标识,可以根据主键来唯一地筛选出一条记录

特点：

• 主键字段不能为空，不能重复
• 一张表中最多只能有一个主键
• 主键所在的列通常是整数类型
• 主键约束是最频繁的约束

不要修改主键字段的值。因为主键是数据记录的唯一标识，如果修改了主键的 值，就有可能会破坏数据的完整性。

3.6.5.1 复合主键 primary key (字段1,字段2)

复合主键：如果有多个字段作为主键,可以使用复合主键,这些字段合在一起是一个主键，也就是让多个字段联合在一起作为唯一标识,单个字段主键重复是没有问题的,只要不是成为复合主键的字段一起冲突就行

在这里插入代码片

3.6.5.2 主键自增长 auto_increment

作用：给主键添加自增长的数值

注意：

• 自增长字段必须是整数,自增长字段可以 不设置初始值,默认从1开始递增.
• 自增长字段也可以插入数据,只要不与已有数据重复即可,之后新增数据会从最大值开始递增
• 任何一个字段要做自增长,前提是本身是一个索引（key一栏有值）
• 一张表当中最多只能有一个自增长的列
• 约束的字段必须具备 NOT NULL 属性

在这里插入代码片

3.6.6 外键约束 foreign key

作用：限定某个表的某个字段的引用完整性

• 主表（父表）：被引用的表、被参考的表
• 从表（子表）：引用别人的表，参考别人的表
• 外键：从表中的某个字段引用自主表的某个字段或多个字段
• 引用键：主表被引用的字段

外键约束主要定义在从表上，主表则必须是有主键约束或唯一键约束，当定义外键后，要求外键列数据必须在主表的主键列存在或为null

格式：

foreign key (从表的字段名称) references 主表名字(主表的字段名称) # 建立外键关联

3.6.7 唯一性约束 uqique

作用：限制某个字段/某列的值不能重复

唯一键和主键的关系：

• 主键只能有一个,唯一键能设置多个
• 主键用来查找,唯一键用来保证数据的完整性

在这里插入代码片

3.6.8 检查约束

作用：检查约束（check）是用来检查数据表中字段值有效性的一种手段，一般用于设置值的范围

在这里插入代码片

3.6 9 删除约束

# 删除not null约束
	alter table 表名 modify 列名 类型;
# 删除unique约束
	alter table 表名 drop index 惟一约束名;
# 删除primary key约束
	alter table 表名 drop primary key;
# 删除foreign key约束
	alter table 表名 drop foreign key 外键名;

3.7 MySQL库表设计（范式）

3.7.1 范式概念与三范式小结

范式( Normal Form )是指设计数据库时要遵守的一些原则

三范式小结：

• 1NF：确保原子性，表中每一个列数据都必须是不可再分的字段。
• 2NF：确保唯一性，每张表都只描述一种业务属性，一张表只描述一件事。
• 3NF：确保独立性，表中除主键外，每个字段之间不存在任何依赖，都是独立的。

三大范式之间，是递进的关系，后续的范式都基于前一个范式的基础上推行

3.7.2 第一范式（1NF）

原则：库表设计时为了确保原子性，其存储数据具备不可再分性

3.7.3 第二范式（2NF）

原则：表中的所有列，其 数据都必须依赖于主键，也就是一张表只存储同一类型的数据，不能有任何一列数据与主键没有关系

3.7.4 第三范式（3NF）

原则：表中每一列数据不能与主键之外的字段有直接关系

3.7.5 巴斯-科德范式（BCNF）

3.7.6 第四范式（4NF）

3.7.7 第五范式（5NF）/完美范式

3.7.8 第六范式（6NF)/域键范式

3.7.9 反范式

3.8 MySQL数据库账户及授权

3.8.1 密码策略

3.8.2 登录账户管理

3.8.3 新建登录账户

3.8.4 账户授权

3.8.5 回收权限

3.8.6 删除账户

3.9 练习

# 查
show databases; #查看所有库
select database(); #查看当前库（使用的是函数）
show tables; #查看当前库的所有表
show #查看指定库的所有表

#查看表结构
desc 表名; #查看表结构
show columns from 表名; #查看表结构

select * from 表名; #查看此表的具体内容（字段、类型、约束）

select user(); #查看当前登录的用户和主机名
show databases; #查看所有库
create database 表名;#创建库
drop 表名; #删除、修改库
select database(); #查看当前正在使用的库
use 库名; #切换当前库
show tables; #查看当前库的所有表
create table 表名(字段[列名] 类型 约束,字段 类型 约束);#创建表
#写入表内容

#删除、修改表
drop table if exists 表名;
alter table 表名1 rename 表名2;
rename table 表名1 to 表名2
#查看指定表的结构
desc 表名;
show columns from 表名;
select * from 表名; #查看指定表的内容

alter table 表名 drop 列名 ;#删除表的内容
#增加表的内容

alter table 表名 change 列名1 列名2 类型; #修改列名



#修改表的内容的列的位置
alter table 表名 add 列名 类型;
alter table 表名 add 列名 类型 after 列名;#指定位置，...之后
alter table 表名 add 列名 类型 first;第一列

alter table 表名 modify 列名 新类型;#修改表的内容的列的类型

#复制表结构
create table 表2 like 表1; #将表1的结构复制给表2
create table 表2 select * from 表1; #复制表1的结构和内容给表2
insert into 表2 select * from 表1;  #复制表1的结构和内容给表2（前提：两个表的结构必须一样）

3.9.1 现有库、表

共有八个库，新创建四个库，包括 mydb1_test、mydb2_stuinfo、mydb3_employee、mydb4_product

• 库mydb1_test：

共三个表

表 t1：

内容：

表 t2：

表 t3：

• 库mydb2_stuinfo

表class1：

表student1：

表teacher1：

• 库mydb3_employee

• 库mydb4_product

四、DML与DQL

4.1 MySQL之DML

4.2 MySQL之DQL

查询

4.3 多表关联查询

4.4 SQL函数

4.5 一条SQL语句从诞生至结束

4.6 总结与练习

五、MySQL备份恢复

六、MySQL索引视图

七、SQL编程

八 MySQL事务

九、SQL优化