MYSQL笔记1

1. 什么是数据库
1.0.1. 数据：
描述事物的符号记录， 可以是数字、 文字、图形、图像、声音、语言等，数据有多种形式，它们都可以经过数字化后存入计算机。
1.0.1. 数据库：
存储数据的仓库，是长期存放在计算机内、有组织、可共享的大量数据的集合。数据库中的数据按照一定数据模型组织、描述和存储，具有较小的冗余度，较高的独立性和易扩展性，并为各种用户共享，总结为以下几点：
数据结构化
数据的共享性高，冗余度低，易扩充
数据独立性高
数据由 DBMS 统一管理和控制（安全性、完整性、并发控制、故障恢复）

1.1. 数据库管理系统(DBMS)
1.1.1. 概念
数据库管理系统(DataBase ManagermentSystem，简称（DBMS)是管理数据库的一个软件，它充当所有数据的知识库，并对它的存储、安全、一致性、并发操作、恢复和访问负责。是对数据库的一种完整和统一的管理和控制机制。
数据库管理系统不仅让我们能够实现对数据的快速检索和维护，还为数据的安全性、完整性、并发控制和数据恢复提供了保证。数据库管理系统的核心是一个用来存储大量数据的数据库
DBMS是所有数据的知识库，并对数据的存储、安全、一致性、并发操作、恢复和访问负责。
DBMS有一个数据字典（有时被称为系统表），用于贮存它拥有的每个事物的相关信息，例如名字、结构、位置和类型，这种关于数据的数据也被称为元数据（metadata）

1.2. 数据库与文件系统的区别
1.2.1. 文件系统：
文件系统是操作系统用于明确存储设备（常见的是磁盘）或分区上的文件的方法和数据结构；即在存储设备上组织文件的方法。操作系统中负责管理和存储文件信息的软件机构称为文件管理系统，简称文件系统。
1.2.2. 数据库系统：
数据库管理系统(Database Management System)是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，简称 DBMS。它对数据库进行统一的管理和控制，以保证数据库的安全性和完整性。
1.2.3. 对比区别：
管理对象不同： 文件系统的管理对象是文件，并非直接对数据进行管理，不同的数据结构需要使用不同的文件类型进行保存（举例： txt 文件和 doc 文件不能通过修改文件名完成转换） ；而数据库直接对数据进行存储和管理
存储方式不同：文件系统使用不同的文件将数据分类（.doc/.mp4/.jpg） 保存在外部存储上；数据库系统使用标准统一的数据类型进行数据保存（字母、 数字、符号、时间）调用数据的方式不同：文件系统使用不同的软件打开不同类型的文件；数据库系统由 DBMS 统一调用和管理。

MySQL体系结构
架构图：共分为四层即：网络连接层、数据库服务层、存储引擎层、系统文件层

网络连接层：位于整个MySQL体系架构的最上层，主要担任客户端连接器的角色。提供与MySQL服务器建立连接的能力，几乎支持所有主流的服务端语言，例如：Java、C、C++、Python等，各语言都是通过各自的API接口与MySQL建立连接。

数据库服务层：又称为SQL层，是整个数据库服务器的核心，主要包括了系统管理和控制工具、连接池、SQL接口、解析器、查询优化器和缓存等部分
系统管理和控制工具：提供数据库系统的管理和控制功能，例如对数据库中的数据进行备份和恢复，保证整个数据库的安全性，提供安全管理，对整个数据库的集群进行协调和管理等。
SQL接口：主要负责接收客户端发送过来的各种SQL命令，并将SQL命令发送到其他部分，并接收其他部分返回的结果数据，将结果数据返回给客户端。
解析器：验证和分析SQL命令
查询优化器：对SQL语句进行优化
缓存：由一系列的小缓存组成的，如：表缓存，记录缓存，权限缓存，引擎缓存等，缓存能够提高数据的查询性能
存储引擎层：存储引擎是一种文件访问机制，主要负责数据的写入和读取，与底层的文件进行交互，其特性是插件式的，可以根据应用场景选择合适的存储引擎，最常用、考察最多的是InnoDB和MyISAM引擎
系统文件层：又称为物理文件层，是存储数据的底层文件，与上层的存储引擎进行交互，支持的文件系统类型有EXT3、EXT4、NTFS、NFS等，主要存储：日志文件、数据文件、配置文件、其它文件

命令指令：
清屏：\! cls
退出：exit   或者 \q

mysql服务启动与停止(要退出到命令行模式)
启动：net start mysql80
停止：net stop mysql80

1. 第三章 MySQL库表操作
1.1. SQL语句基础
1.1.1. SQL简介
SQL：结构化查询语言(Structured Query Language)，在关系型数据库上执行数据操作、数据检索以及数据维护的标准语言。使用SQL语句，程序员和数据库管理员可以完成如下的任务
改变数据库的结构
更改系统的安全设置
增加用户对数据库或表的许可权限
在数据库中检索需要的信息
对数据库的信息进行更新

1.1.2. 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等

1.1.3. SQL语句的书写规范
在数据库系统中，SQL语句不区分大小写(建议用大写) ，但字符串常量区分大小写
SQL语句可单行或多行书写，以“;”结尾。
关键词不能跨多行或简写。
用空格和缩进来提高语句的可读性。
子句通常位于独立行，便于编辑，提高可读性。
注释：
/* */ 和// 为多行注释
-- 和 # 为单行注释

1.2.1. 数据库的登录及退出
登录格式
mysql    -u    用户名   -h   服务器的主机地址    -p密码    -A

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

查看数据库：
格式：show databases [like 条件];              # 注意有s
带有通配符的条件
%：匹配任意零个或多个字符
_ ：匹配任意单个字符

显示时间：
select now();

显示版本信息：
select version();

创建数据库：
格式：create database 数据库名
查看创建数据库的完整语句：
格式：show  create database   数据库名
注意：在创建数据库或者查看创建数据库语句时，datatbase 没有s

1.2.4. mysql中的反引号
上例中查看的sql语句有反引号，其作用可以避免一些与SQL语法冲突的问题，同时可以使代码更加规范化和易读性更强
如 select name, address from table; 其中table若当做表名但其为关键字回报错，加上反引号可以避免与SQL关键字的冲突：
mysql> select name, address from `table`;
注意：
只有保留关键字和特殊字符需要使用反引号，普通的表名和列名不需要
数据类型之间可以加或者不加反引号，但为了代码规范化，建议加上

1.2.5. 切换数据库
查看当前使用的数据库，
格式：select  database();
mysql> select database();
+------------+
| database() |
+------------+
| NULL |
+------------+
注意：当前没有使用数据库，则显示空

选择数据库,
格式： use 数据库名

1.2.6. 查看当前用户
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+

删除数据库
格式：
drop database 数据库名

1.3. MySQL 字符集
MySQL字符集包括字符集（CHARACTER）和排序规则(校对规则)（COLLATION）两个概念
1.3.1. 字符集
Character Set(字符集)：是字符的编码规则，规定了字符在数据库中的存储格式，比如占多少空间，支持哪些字符等
不同的字符集有不同的编码规则，在运维和使用MySQL数据库中，选取合适的字符集非常重要，如果选择不恰当，轻则影响数据库性能，严重的可能导致数据存储乱码
mysql 5.7的默认字符集是latin1，而8.0中是utf8mb4；

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

1.3.2. 字符序
字符序就是字符排序的规则集合
如：使用A>B>a>b的规则来进行排序或者另一种规则a>b>A>B顺序排序
字符序主要对字符的排序有影响

查看MYSQL字符集：
查看所有支持的字符集：
格式：show character set;

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：该字符集中字符最大存储长度。

查看指定字符集：
mysql> show character set like 'gbk';
+---------+------------------------+-------------------+--------+
| Charset | Description | Default collation | Maxlen |
+---------+------------------------+-------------------+--------+
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
+---------+------------------------+-------------------+--------+
1 row in set (0.00 sec)

查看MySQL字符序：
MySQL字符序命名规则：
以字符序对应的字符集名称开头
以国家名居中（或以general居中）
后缀：
bin：二进制；
ci：大小写不敏感；
cs：大小写敏感；

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

指定条件查询：
show collation where charset = "utf8bm4";

查看当前字符集的设置
通过变量查看:
mysql> show variables like 'character_set%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 # 重要 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 # 重要 |
| character_set_system | utf8mb3 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
变量含义：
character_set_client：MySQL客户端的字符集。
character_set_connection：数据通信链路字符集，当MySQL客户机向服务器发送请求时，请求数据以该字符集进行编码。
character_set_database：数据库默认使用的字符集。
character_set_filesystem：MySQL服务器文件系统字符集，该值是固定的binary。
character_set_results：服务器返回给客户端的结果使用的字符集。
character_set_server：服务器默认的字符集。
character_set_system：服务器存储元数据使用的字符集。



1.4.2. 数据库对象的命名规则
命名使用具有意义的英文词汇，词汇中间以下划线分隔
名称由字母、数字、#、下划线、$组成，必须以英文字母开头
不要使用MySQL的保留字
所有数据库对象使用小写字母，实际上MySQL中是可以设置大小写是否敏感的，为了保证统一性应使用小写表示
在同一个数据库对象集合内对象不能同名

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

1.5. 表的基本操作
1.5.1. 数据类型
类型组成：数字、文本、日期/时间类型

注意：
实际开发的角度，一定要为合适的列选取合适的数据类型，如：
一个枚举字段只有0和1两个枚举值，选用TINYINT就足够了，但在开发场景下却使用了BIGINT，这就造成了资源浪费
简单计算一下，假使该数据表中有100W数据，那么总共浪费了700W字节也就是6.7M左右，如果更多的表这么做了，那么浪费的更多

整型(N)表现形式：有时代码写法为int(10),表示数字宽度限制，要求如下：
无论N等于多少，int永远占4个字节
N表示的是显示宽度，不足则补0，超过长度则会无视长度直接显示整个数字
可以通过命令 show warnings; 查看警告信息

注意：
blob和text都是为了存储很大数据而设计的字符串数据类型，blob采用二进制形式存储无排序规则和字符集，text采用字符形式存储且有排序规则和字符集适用于存储如文章内容、评论等
char是固定长度字符串，其长度范围为0~255且与编码方式无关，无论字符实际长度是多少，都会按照指定长度存储，不够的用空格补足，char类型数据时会将结尾的所有空格处理掉