MySQL 数据库 基础
一、数据库
数据（Data）
- 描述事物的符号记录
- 包括数字，文字，图形，图像
- 以
记录形式按统一的格式进行存储
表
- 将不同的记录组织在一起
- 用来存储具体数据
数据库
- 表的集合
- 以一定的组织方式存储的相互有关的数据集合
- 是按照数据结构来组织，存储和管理数据的仓库
1. 数据库的基本概念
数据管理系统（DBMS）
- 是实现对数据资源有效组织、管理和存取的系统团建
具有 数据库的建立和维护功能、数据定义功能，数据操纵功能、数据库的运行管理功能、通信功能
数据库系统
- 是一个人机系统，由硬件、OS、数据库、DBMS、应用软件和数据库用户组成
- 用户可以通过DBMS或应用程序操作数据库
2. 数据库的发展史
第一代数据库
- 自20世纪60年代起，第一代数据库系统问世
- 是层次模型与网状模型的数据库系统
- 为统一管理和共享数据提供了有力的支持
第二代数据库
- 20世纪70年代初，第二代数据库——关系数据库开始出现
- 20年代20年代初，IBM公司的关系数据库DB2问世，开始逐步取代层次与网状模型的数据库，成为行业主流
- 到目前为止，关系数据库系统仍占领数据库应用的主要地位
第三代数据库
- 自20世纪20年代开始，适应不同领域的新型数据库系统不断涌现
- 面向对象的数据库系统，实用性强、适应面广
- 20世纪90年代后期，形成了多种数据库系统共同支撑应用的局面
- 一些新的元素被添加进主流数据库系统中
- 例如，Oracle支持的
关系-对象数据库模型
- 例如，Oracle支持的
2.1 当今主流数据库
- SQL Server（微软公司产品）
- 面向Windows操作系统
- 简单、易用
- Oracle（甲骨文公司产品）
- 面向所有主流平台
- 安全，完善，操作复杂
- DB2（IBM公司产品）
- 面向所有主流平台
- 大型、安全、完善
- MySQL（甲骨文公司收购）
- 免费、开源、体积小
在DB-Engines流行度排名中，MySQL已连续数年位于流行度前两位。在关系型数据库中MySQL也有着较高的市场份额。根据2022年slintel网站的统计数据，在全球关系型数据库市场中，MySQL市场份额最高，达到43.04%，排名第二的Oracle仅为16.76%。MySOL市场份额几乎占据关系型数据库市场半壁江山，已经成为事实上全球范围内影响最广泛的开源数据库
而在中国，MySOL的应用比例也相当高。根据Shadowserver Foundation在2022年发布的MySOL扫描报告，全球目前共有360万个MSOL实例，其中中国MSOL实例数占比为15.8%，仅次于美国的32.5%。
2.2 数据库分类
数据库目前可分为两大类型，关系数据库（SQL）和非关系型数据库（NoSQL）
这里简单介绍一下，详细可看下面的子标题
- 关系数据库
关系型数据库的典型代表：MySQL 、MariaDB 、PostgreSQL（pgsql）、Oracle 、SQL Server 、DB2
国产数据库代表：阿里云 RDB、华为 高斯、阿里 Oceanbase（分布式数据库）、腾讯 TDBA、人大金仓、达梦、greatSQL
关系型数据库SQL，它的操作命令是：SQL语句，存储的数据结构是：二维表格，存储的数据是：结构化数据
举例：
库 -> 表 -> 二维表格形式的结构化数据
表中的行与列
列（字段）：用来描述对象的一个属性
行（记录）：用来描述一个对象的信息
- 非关系型数据库
NoSQL 没有统一的操作命令，存储结构常用的有：键值对（例如：name=xxx与name：xxx，类似变量的形式）、文档、索引、时间序列等
所以有如下一些非关系数据库类型
缓存型：Redis 、Memcached
文档型：MongoDB
索引型：ElasticSearch
时序性：Prometheus（轻量型的数据库，也是一个监控服务软件）、InfluxDB（Prometheus的升级版）
3. 关系数据库
- 关系数据系统是具与关系模型的数据库系统
- 关系模型的数据结构使用简单移动的二维数据表
- 关系模型可用简单的
实体-关系图来表示（如下图） - 下图中包含了实体（数据对象）、关系和属性三个要素
3.1 关系数据的存储结构
- 关系数据库的存储结构是二维表格
- 每一个二维表中
- 每一行称为一条记录，用来描述一个对象的信息
- 每一列称为一个字段，用来描述对象的一个属性
关系型数据库的典型代表：MySQL 、MariaDB 、PostgreSQL（pgsql）、Oracle 、SQL Server 、DB2
国产数据库代表：阿里云 RDB、华为 高斯、阿里 Oceanbase（分布式数据库）、腾讯 TDBA、人大金仓、达梦、greatSQL
关系型数据库SQL，它的操作命令是：SQL语句，存储的数据结构是：二维表格，存储的数据是：结构化数据
举例：
库 -> 表 -> 二维表格形式的结构化数据
表中的行与列
列（字段）：用来描述对象的一个属性
行（记录）：用来描述一个对象的信息
3.2 关系数据库的优缺点
优点：
易于维护，都是使用表结构，格式一致。
使用方便，SQL语言通用，可用于复杂查询。
复杂操作，支持SQL，可用于一个表以及多个表之间发的复杂查询
缺点：
读写性能比较差，尤其是海量数据的高效率读写；
固定的表结构，灵活度稍欠；
高并发读写需求，传统关系型数据库来说，硬盘I/O是一个很大的瓶颈
4. 非关系数据库
- 非关系数据库也被称为NoSQL（Not Only SQL）
- 存储数据不以关系模型为依据，不需要固定的表格式
- 非关系数据库的优点
- 数据库可高并发读写
- 对海量数据高效率存储与访问
- 数据库具有高扩展性和高可用性
- 常用的非关系数据库：Redis、MongoDB等
NoSQL 没有统一的操作命令，存储结构常用的有：键值对（例如：name=xxx与name：xxx，类似变量的形式）、文档、索引、时间序列等
所以有如下一些非关系数据库类型
缓存型：Redis 、Memcached
文档型：MongoDB
索引型：ElasticSearch
时序性：Prometheus（轻量型的数据库，也是一个监控服务软件）、InfluxDB（Prometheus的升级版）
4.1 非关系数据库的优点
1.数据库可高并发读写。
2.对海量数据高效率存储与访问。nosql可以使用硬盘或者随机存储器作为载体，而关系型数据库只能使用硬盘
3.数据库具有高扩展性与高可用性。
4.成本低：nosql数据库部署简单，基本都是开源软件。
5.格式灵活
存储数据的格式可以是key/value形式、文档形式、图片形式等等，使用灵活，应用场景广泛，而关系型数据库则只支持基础类型。
4.2 非关系型数据库的缺点
1.不提供sql支持，学习和使用成本较高；
2.无事务处理；
3.数据结构相对复杂，复杂查询方面稍欠
二、MySQL数据库介绍
- MySQL是一款深受欢迎的开源关系型数据库
- Oracle旗下的产品
- 遵守GPL协议，可以免费使用与修改
- 特点：
- 性能卓越，服务稳定
- 开源、无版权限制、成本低
- 多线程、多用户
- 基于C/S（客户端/服务器）架构
- 安全可靠
1. MySQL 数据库常用的数据类型
int：整数型（使用最为广泛）
分为两种
- 无符号[0,2^32-1]
- 有符号[-2^31,2^31-1]
浮点类型
- float：单精度浮点，4字节32位
- double：双精度浮点，8字节64位
char：固定长度的字符串类型
varchar：可变长度的字符串类型
text：文本（长文本，很长的字符串，char和varchar也可以看做短文本类型）
image：图片
decimal（5,2）：使用是有限制的，最多5个有效长度数字，小数点后面有2位
建议：
在指定数据类型的时候，一般采用从小原则，比如能用TINY就不用INT，能用FLOAT类型就不用DOUBLE类型，这样会对MySQL在运行效率上提高很大，尤其是大数据量测试条件下
2. 简单的 MySQL 数据库命令
以下所有[]都是可选项
#查看当前服务器中的数据库
SHOW DATABASES;
//可以不用区分大小写，分号`;`表示而结束
#查看数据库中包含的表
use 库名;
show tables; 或 show tables from 库名; #使用这一条可以不使用use切换到对应库
desc [库名.]表名;
#查看数据库表结构（字段）
USE 数据库名；
DESCRIBE [数据库名.]表名；
//可缩写成 DESC 表名；
#添加库以及给库插入数据
create database 库名； #创建库
use 库名； #切换到该库
create table [库名.]表名 (字段1 数据类型, 字段2 数据类型, ....[, primary key (字段)]); #如果没有切换到对应的库，可以在[]中输入对应的库，后面加. 来给库插入数据
#删除
drop table [库名.]表名;
drop database 库名;
3. SQL语句的类型
SQL语句用于维护管理数据库，包括数据查询、数据更新、访问控制、对象管理等功能
分为四类，分别是：
DDL、DML、DQL、DCL
| 语句 | 代表的意思 |
|---|---|
| DDL | 数据定义语言，用于创建数据库对象，如库、表、索引等 |
| DML | 数据操纵语言，用于对表中的数据进行管理 |
| DQL | 数据查询语言，用于从数据表中查找符合条件的数据记录 |
| DCL | 数据控制语言，用于设置或者更改数据库用户或角色权限 |
- DDL: Data Defination Language 数据定义语言
CREATE，DROP，ALTER
- DML: Data Manipulation Language 数据操纵语言
INSERT，DELETE，UPDATE
软件开发：CRUD
- DQL：Data Query Language 数据查询语言
SELECT
- DCL：Data Control Language 数据控制语言
GRANT，REVOKE
- TCL：Transaction Control Language 事务控制语言
COMMIT，ROLLBACK，SAVEPOINT
4. 常用的数据类型
| 类型 | 说明 |
|---|---|
| int | 整型，用于定义整数类型的数据 |
| fload | 单精度浮点4字节32位，准确表示到小数点后六位 |
| double | 双精度浮点8字节64位 |
| char | 固定长度的字符类型，用于定义字符类型数据。 |
| varchar | 可变长度的字符类型 |
| text | 文本 |
| image | 图片 |
| decimal(5,2) | 5个有效长度数字，小数点后面有2位。指定长度数组 |
char
char的长度是不可变。char如果存入数据的实际长度比指定长度要小 会补空格至指定长度 如果存入的数据的实际长度大于指定长度，低版本会被截取高版本会报错。
varchar
varchar长度是可变的，默认会加一个隐藏的结束符，因此结束符会多算一个字节。
注意：选择正确的数据类型对于获得高性能至关重要，
三大原则：
4.1 整数型
tinyint(m) 1个字节 范围(-128~127)
smallint(m) 2个字节 范围(-32768~32767)
mediumint(m) 3个字节 范围(-8388608~8388607)
int(m) 4个字节 范围(-2147483648~2147483647)
bigint(m) 8个字节 范围(+-9.22*10的18次方)
上述数据类型，如果加修饰符unsigned后，则最大值翻倍
如：tinyint unsigned的取值范围为(0~255)
4.2 浮点型 (float 和 double) ，近似值
float(m,d) 单精度浮点型 8位精度(4字节) m总个数，d小数位, 注意: 小数点不占用总个数
double(m,d) 双精度浮点型16位精度(8字节) m总个数，d小数位, 注意: 小数点不占用总个数
设一个字段定义为float(6,3)，如果插入一个数123.45678,实际数据库里存的是123.457，但总个数还以实际为准，即6位
4.3 定点数
在数据库中存放的是精确值,存为十进制
格式 decimal(m,d) 表示 最多 m 位数字,其中 d 个小数,小数点不算在长度内
比如: DECIMAL(6,2) 总共能存6位数字，末尾2位是小数，字段最大值 9999.99 （小数点不算在长度内）
参数m<65 是总个数，d<30且 d<m 是小数位
MySQL5.0和更高版本将数字打包保存到一个二进制字符串中（每4个字节存9个数字）。
例如: decimal(18,9)小数点两边将各存储9个数字，一共使用9个字节：其中，小数点前的9个数字用4个字节，小数点后的9个数字用4个字节，小数点本身占1个字节
浮点类型在存储同样范围的值时，通常比decimal使用更少的空间。float使用4个字节存储。double占用8个字节
因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时
4.4 字符串
char(n) 固定长度，最多255个字符,注意不是字节
varchar(n) 可变长度，最多65535个字符
tinytext 可变长度，最多255个字符
text 可变长度，最多65535个字符
mediumtext 可变长度，最多2的24次方-1个字符
longtext 可变长度，最多2的32次方-1个字符
BINARY(M) 固定长度，可存二进制或字符，长度为0-M字节
VARBINARY(M) 可变长度，可存二进制或字符，允许长度为0-M字节
内建类型：ENUM枚举, SET集合
5. DDL 数据定义
1.创建库：
create database 库名;
2.删除库：
drop database 库名;
3.更改库：
alter database 字符编码;
4.查看库：
show databases;
show create database 库名; #查看库结构
5.使用当前库：
use 库名;
6. DDL 表的管理
6.1 创建表
#创建表：
CREATE TABLE 表名(字段1 数据类型,字段2 数据类型[,...] [,PRIMARY KEY (主键名)]);
示例：
create table www (id int, name varchar(5), sex char(1));
6.2 删除表
#删除表：
drop table 表名; #删除表
truncate table 表名 #删除表数据
6.3 更改表
#更改表：
#1.添加字段：
alter table 表名 add 字段名 字段类型;
#2.修改字段：
alter table 表名 change 旧字段 新字段 数据类型 [字段属性];
#3.删除字段
alter table 表名 drop 字段;
#4.修改表名：
alter table 旧表名 rename 新表名;
6.4 表查询
#1.查询当前所有表：
show tables;
#2.查询表结构：
desc 表名;
#3.查询建表语句：
show create table 表名;
7. DML 数据操纵
7.1 DML 添加数据
#1.给指定字段添加数据：
insert into 表名 (字段1, 字段2, ...) values (字段1的值, 字段2的值, ...);
#2.给全部字段添加数据：
insert into 表名 values (字段1的值, 字段2的值, ...); #要按照表结构的字段顺序设置值
7.2 DML 修改数据
update 表名 set 字段1=值, ... where 条件表达式;
不添加where修改整表。
7.3 DML 删除数据
delete from 表名 where 条件表达式;
8. DQL 数据查询
DQL 数据查询 使用频繁，比较重要，单独写了一篇，可以去那边详细查看
这里举例了一些简单的
1.查看所有字段
select * from 表名 [where 条件表达式];
2.查看指定字段
select 字段1,字段2,... from 表名 [where 条件表达式];
3.纵向查看每行记录字段的值
select * from 表名G
4.分页查询：
select * from 表名 limit N; #查看表的前N行记录
select * from 表名 limit N,M; #查看表的前N行之后的连续M行记录（不包含第N行）
9. DCL 数据库权限控制
9.1 用户管理
#1.查看当前登录的用户
select user();
#2.创建用户：
create user '用户名'@'源地址' identified by '密码';
localhost/IP/网段/主机名/%
#3.修改用户名和允许访问的主机：
rename user '旧用户名'@'源地址' to '新用户名'@'源地址';
#4.修改用户密码：
set password for '用户名'@'源地址' = password('密码');
alter user '用户名'@'源地址' identified by '密码';
9.2 权限管理
#1.指定用户授权：
grant 权限1,权限2,.... on 库名.表名 to '用户名'@'源地址' [identified by '密码']; #5.7版本支持创建用户和权限授权，8.0版本只能用于权限授权
all *.*
#2.查询权限：
show grants for '用户名'@'源地址';
#3.撤销权限：
revoke 权限1,权限2,.... on 库名.表名 from '用户名'@'源地址';
all
10. MySQL 的约束
10.1 常用的约束类型
#示例：
create table www (id int primary key auto_increment, name varchar(5), sex char(1) not null);
10.2 创建外键约束
注意：
与外键关联的主表的字段必须设置为主键。要求从表不能是临时表，主从表的字段具备相同的数据类型、字符长度和约束。
删数数据记录时，要先从表再主表，也就是说删除主键表的记录时必须先删除其他与之关联的表中的记录。
#创建主表 profession
create table profession (pid int(4) primary key, proname varchar(50));
#创建从表 student
create table student (id int(4) primary key auto_increment,name varchar(10),age int(3),proid int(4));
#为从表 student 表添加外键，并将 student 表的 proid 字段和 profession 表的 pid 字段建立外键关联。外键名建议以“FK_”开头。
alter table student add constraint FK_pro foreign key (proid) references profession (pid);
10.3 查看和删除外键约束
show create table student;
desc student;
alter table student drop foreign key FK_pro;
alter table student drop key FK_pro;
11. 数据库高级操作
11.1 克隆表
create table 新表 like 旧表; #克隆表结构
insert into 新表 (select * from 旧表); #克隆表数据 可实现表结构和表数据与旧表都一样
create table 新表 (select * from 旧表); #表数据与旧表一样，表结构与旧表可能不一样
11.2 创建临时表
临时表可以跟普通的表一样增删改查表中的数据，但是show tables是查看不到的，临时表只能在当前会话中有效，在其它会话中或者退出当前会话连接，临时都会失效
create temporary table 表名 (....);
11.3 找回 root 密码
（1）修改 /etc/my.cnf 配置文件，不使用密码直接登录到 mysql
vim /etc/my.cnf
[mysqld]
skip-grant-tables #添加，使登录mysql不使用授权表
systemctl restart mysqld
mysql #直接登录
（2）使用 update 修改 root 密码，刷新数据库
UPDATE mysql.user SET AUTHENTICATION_STRING = PASSWORD('abc123') where user='root';
FLUSH PRIVILEGES;
quit
mysql -u root -pabc123
注意：最后再把 /etc/my.cnf 配置文件里的 skip-grant-tables 删除，并重启 mysql 服务。
12 命令总结
use 库名;
show tables; show tables from 库名;
desc [库名.]表名;
create database 库名;
use 库名;
create table [库名.]表名 (字段1 数据类型, 字段2 数据类型, ....[, primary key (字段)]);
drop table [库名.]表名;
drop database 库名;
修改表结构
改表名 alter table 旧表名 rename 新表名
增加字段 alter table 表名 add 新字段 数据类型 [字段属性];
修改字段 alter table 表名 change 旧字段 新字段 数据类型 [字段属性];
删除字段 alter table 表名 drop 字段;
DML：用于管理表数据
insert into 表名 (字段1, 字段2, ...) values (字段1的值, 字段2的值, ...);
insert into 表名 values (字段1的值, 字段2的值, ...); #要按照表结构的字段顺序设置值
update 表名 set 字段1=值, ... where 条件表达式;
delete from 表名 where 条件表达式;
DQL：用于根据条件查询表数据
select * from 表名 [where 条件表达式];
select 字段1,字段2,... from 表名 [where 条件表达式];
select * from 表名G #纵向查看每行记录字段的值
select * from 表名 limit N; #查看表的前N行记录
select * from 表名 limit N,M; #查看表的前N行之后的连续M行记录（不包含第N行）
MySQL 的约束特性：
primary key 主键约束 字段的值不能重复，且不能为null，一个自建的表只能有一个主键
unique key 唯一性约束 字段的值不能重复，能为null，一个表可以有多个唯一键
not null 非空约束 字段的值不能为null
default 默认值约束 字段的值如果没有设置则使用默认值自动填充
auto_increment 自增约束 字段的值如果没有设置默认会从1开始每次自动递增1，要求自增字段必须设置主键
foreign key 外键约束 保证相关联的表数据的完整性和一致性
int(N) zerofill 零填充
克隆表
create table 新表 like 旧表; #克隆表结构
insert into 新表 (select * from 旧表); #克隆表数据 可实现表结构和表数据与旧表都一样
create table 新表 (select * from 旧表); 表数据与旧表一样，表结构与旧表可能不一样
清空表
delete from 表名; 一条一条的删除记录，效率较慢；自增字段仍然会按照清空前的最大记录继续自增
truncate table 表名; 直接重建表，清空表效率更快；自增字段会重新从1开始自增
临时表
create temporary table 表名 (....); 临时表可以跟普通的表一样增删改查表中的数据，但是show tables是查看不到的，
临时表只能在当前会话中有效，在其它会话中或者退出当前会话连接，临时都会失效
外键约束
主键表： alter table 表名 add primary key (主键字段);
外键表： alter table 表名 add foreign key (外键字段) references 主键表名 (主键字段);
插入新数据时，需要先在主键表插入数据再在外键表插入对应数据；删除数据时，需要先在外键表删除数据再在主键表删除对应数据
DCL：用于管理用户与权限
用户管理
select user(); 查看当前登录的用户
create user '用户名'@'源地址' identified by '密码';
localhost/IP/网段/主机名/%
rename user '旧用户名'@'源地址' to '新用户名'@'源地址';
drop user '用户名'@'源地址';
select user,host,authentication_string from mysql.user;
set password = password('密码');
set password for '用户名'@'源地址' = password('密码');
alter user '用户名'@'源地址' identified by '密码';
找回 root 密码？
1）修改mysql配置文件，在 [mysqld] 下面添加 skip-grant-tables 配置项
2）重启mysqld服务，使用 mysql 密码直接登录 mysql
3）执行 update user set authentication_string=password('密码') where user='root'; 命令修改 root 用户的密码
4）还原mysql配置文件，重启mysqld服务，使用 mysql -u 用户名 -p密码 [-h mysql地址 -P mysql端口] 命令来验证登录
权限管理
grant 权限1,权限2,.... on 库名.表名 to '用户名'@'源地址' [identified by '密码']; #5.7版本支持创建用户和权限授权，8.0版本只能用于权限授权
all *.*
show grants for '用户名'@'源地址';
revoke 权限1,权限2,.... on 库名.表名 from '用户名'@'源地址';
all
