Mysql基础

数据库原理

数据库管理系统主要氛围三类：层次数据库，网状数据库，关系型数据库

常见的关系型数据库系统

MySQL：MySQL，MariaDB，Percona Server
PostgreSQL：PgSQL，EnterpriseDB
Oracle：Oracle
SQLServer：MS SQLServer DB2

数据库排名：DB-Engines Ranking - popularity ranking of database management systems

数据库规范化，又称数据库或资料库的正规化、标准化，是数据库设计中的一系列原理和技术，以减少 数据库中数据冗余，增进数据的一致性。设计关系数据库时，遵从不同的规范要求，设计出合理的关系 型数据库，不同的规范要求被称为不同范式，各种范式呈递次规范，越高的范式数据库冗余越小。数据库三大范式_数据库范式作用-CSDN博客

第一范式（确保每列保持原子性）

第一范式就是属性不可分割，每个字段都应该是不可再拆分的。

即每一个字段应该是最小单位，不能被拆分成多个字段，否则它就是可分割的，不符合第一范式。

第二范式（确保表中的每列都和主键相关）

第二范式是在第一范式的基础上更进一步。第二范式就是要求表中要有主键，表中其他字段都依赖于主键，因此第二范式只要记住主键约束就好了。

第三范式（确保每列都和主键列直接相关，而不是间接相关）

第三范式就是确保数据表中的每一列数据都和主键直接相关，而不能间接相关。也就是要消除传递依赖，方便理解，可以看做是消除冗余，因此第三范式只要记住外键约束就好了。

数据库三大范式的作用

数据库三大范式的作用
   范式可以避免数据冗余，减少数据库的空间，减轻维护数据完整性的麻烦。但是操作困难，因为需要联系多个表才能得到所需要数据，而且范式越高性能就会越差。要权衡是否使用更高范式是比较麻烦的，一般在项目中，用得最多的也就是第三范式。
   实际上设计任何一种数据库应用系统，不论是基于何种数据模型的，都会遇到如何构造合适的数据模式即逻辑结构的问题。
    由于关系模型有严格的数学理论基础，并且可以向别的数据模型转换。所以要设计合适的关系模式，使其逻辑结构更加符合要求，出现了规范化理论。
    而三大范式即第一、第二和第三范式就是规范化理论重要部分，是为了在设计中更好的解决数据冗余，数据有效性检查，提高存储效率。

MySQL数据库中的SQL查询语句执行流程

客户端请求 ---> 连接器（验证用户身份，给予权限）  ---> 查询缓存（存在缓存则直接返回，不存在则执行后续操作） ---> 分析器（对SQL进行词法分析和语法分析操作）  ---> 优化器（主要对执行的sql优化选择最优的执行方案方法）  ---> 执行器（执行时会先看用户是否有执行权限，有才去使用这个引擎提供的接口） ---> 去引擎层获取数据返回（如果开启查询缓存则会缓存查询结果）

MySQL安装和基本使用

安装方式：

包管理器进行安装

[root@rocky86 ~]# yum list mysql mysql-server
[root@rocky86 ~]# yum list mariadb mariadb-server
#安装 mysql-server，会自动安装客户端包
[root@rocky86 ~]# yum install -y mysql-server
#服务状态
[root@rocky86 ~]# systemctl status mysqld.service
#启动服务
[root@rocky86 ~]# systemctl enable --now mysqld.service
#再次查看
[root@rocky86 ~]# systemctl status mysqld.service
#自动创建的账户
[root@rocky86 ~]# getent passwd mysql
#查看家目录
[root@rocky86 ~]# ll /var/lib/mysql
#查看监听端口，默认3306，33060 是8.0版本中的特性
[root@rocky86 ~]# ss -tnlp | grep mysql

MySQL客户端常用选项

mysql [OPTIONS] [database]
#常用选项
-V|--version            #显示客户端版本
-u|--user=name          #指定远程连接用户名
-p|--password[=name]    #指定密码, 默认为空
-h|--host=host          #指定服务端主机
-P|--port=port          #指定端口，默认3306
-S|--socket=name        #指定连接时使用的socket文件，该文件在服务端启动后生成
-D|--database=db        #指定数据库
-H|--html               #以html格式输出
-X|--xml                #以xml格式输出
-t|--table              #以table格式输出，默认项
-E|--vertical           #垂直显示执行结果
-v|--verbose            #显示详细信息，配合 -t 选项
-C|--compress           #启用压缩
-G|--named-commands     #启用长命令
-e|--execute=sql        #执行完就退出，非交互式运行
--prompt=name           #修改命令提示符
--line-numbers          #输出行号
--print-defaults        #打印参数列表，放在最前面
--connect-timeout=N     #连接超时时长，单位S
--max-allowed-packet=N  #一次查交互发送或反回数据的大小，默认16MB，最大值为1GB，最小值为 4096字节

MySQL客户端常用命令

#常用命令
?|\?            #显示帮助
help|\h         #显示帮助
clear|\c        #清屏，直接使用无法生效，要配合 system 命令使用
exit|\q         #退出客户端
quit|\q         #退出客户端
status|\s       #显示当前状态
use|\u          #切换数据库
system|\!       #调用系统命令
prompt|\R       #修改提示符
source|\.       #执行SQL脚本文件
connect|\r      #客户端重新连接，使用之前的参数
tee|\T          #设置文件名，将输出结果同时保存一份到指定文件
notee|\t        #不保存输出结果至文件
delimiter|\d    #自定义SQL语句分隔符
go|\g           #将语句送到服务端执行
ego|\G          #将语句送到服务端执行，垂直显示
print|\p        #输出语句，但不执行
warnings|\W     #总是输出告警信息
nowarning|\w    #不输出告警信息
charset|\C      #设置编码
edit|\e         #先编辑SQL语句，再执行

mysqladmin工具

mysqladmin 也是用于管理 mysql 服务的本地工具。

mysqladmin [OPTIONS] command command....
#常用选项
-?|--help                   #显示帮助信息
-V|--version                #显示客户端版本
-f|--force                  #删库时不确认
-C|--compress               #启用压缩
-h|--host=name              #指定服务器地址
-u|--user=name              #指定用户名
-p|--password[=name]        #指定连接服务器的密码
-P|--port=N                 #指定连接端口，默认3306
-l|--local                  #在本地执行，语句不写入binlog
-b|--no-beep                #执行出错时不发出告警音
-s|--silent                 #如果无法连接，则静默退出
-c|--count=N                #总共执行多少次，配合-i选项使用
-i|--sleep=N                #持续执行命令，间隔N秒执行一次
-S|--socket=name            #指定连接时使用的socket文件
-w|--wait[=#]               #如果连接失败，是否等待重试，如果指定了具体数字，则表示重试几次
--plugin-dir=name           #客户端查件目录
--print-defaults            #显示运行参数，
--defaults-file=path        #从指定文件中读取选项
--connect-timeout=N         #指定连接超时时长
--shutdown-timeout=N        #指定关机超时时长
--protocol=name             #指定连接方式 (tcp|socket|pipe|memory)
--ssl                       #使用ssl安全功能

#命令
create databasename             #创建新的数据库
debug                           #开启调试模式，将调试信息写入log
drop databasename               #删除指定数据库
extended-status                 #显示扩展状态
flush-all-statistics            #刷新所有统计表
flush-all-status                #刷新状态和统计信息
flush-client-statistics         #刷新客户端统计信息
flush-hosts                     #刷新所有缓存的主机
flush-index-statistics          #刷新索引统计信息
flush-logs                      #刷新所有日志
flush-privileges                #刷新访问权限
flush-binary-log                #刷新二进制日志
flush-engine-log                #刷新引擎日志
flush-error-log                 #刷新错误日志，开一个新日志文件
flush-general-log               #刷新执行日志
flush-relay-log                 #刷新中继日志
flush-slow-log                  #刷新慢查询日志
flush-status                    #清除状态变量
flush-table-statistics          #清除表统计信息
flush-tables                    #刷新所有表，会强制关闭己打开的表
flush-threads                   #刷新线程缓存
flush-user-statistics           #刷新用户统计信息
flush-user-resources            #刷新用户资源
 

MyCLI工具

MyCLI 是基于 Python 开发的 MySQL 的命令行工具，具有自动完成和语法突出显示功能。

[root@rocky /etc/my.cnf.d]$ yum install python38
[root@rocky /etc/my.cnf.d]$ pip3.8 config set global.index-url https://pypi.tuna.tsinghua.edu.cn/simple
[root@rocky /etc/my.cnf.d]$ pip-3.8 install mycli

SQL语言

SQL中语言注释

单行注释
-- select version()\G;      

多行注释 
/*
select version()\G;
select version()\G; 
*/

select version()\G;

MySQL中的注释

# select version()\G;   MySQL 中的注释 
select version()\G;

数据库中的组件

数据库、表、索引、视图、用户、存储过程、函数、触发器、事件调度器等

sql语句分类

管理数据库

创建数据库

mysql> CREATE TABLE `test`.`Untitled`  (
    ->   `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   `name` varchar(20) NOT NULL,
    ->   `age` bigint(3) UNSIGNED ZEROFILL NOT NULL,
    ->   `grade` varchar(20) NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> );

查看数据库创建语句

SHOW CREATE DATABASE DBNAME;

修改数据库

ALTER {DATABASE | SCHEMA} [db_name]
    alter_specification ...
ALTER {DATABASE | SCHEMA} db_name
    UPGRADE DATA DIRECTORY NAME
alter_specification:
    [DEFAULT] CHARACTER SET [=] charset_name   | [DEFAULT] COLLATE [=] collation_name

修改字符集

MariaDB [(none)]> ALTER DATABASE testdb2 character set utf8mb4 COLLATE utf8mb4_0900_ai_ci;
Query OK, 1 row affected (0.001 sec)
MariaDB [(none)]> show create database testdb2\G
*************************** 1. row ***************************
       Database: testdb2
Create Database: CREATE DATABASE `testdb2` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ 1 row in set (0.00 sec)

删除数据库

DROP DATABASE|SCHEMA [IF EXISTS] 'DB_NAME';

数据类型

数值型

字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET

适用于所有类型的修饰符

适用数值型的修饰符

DDL语句

DDL 语句主要用来操作数据库中的表。