MySQL基础知识总结

一、MySQL简述

数据库 是一个有组织的集合，用于存储和管理数据的系统。它是一个软件系统，被设计用来存储、检索和管理数据，并提供数据的快速访问和处理。数据库可以被看作是一种特殊的文件系统，但与传统的文件系统不同的是：它能够更加高效的存储和管理大量结构化数据。

数据库主要由数据库管理系统（DBMS）和数据库组成：

数据

数据是数据库存储与处理的基础素材，涵盖文本、数值、图像等各类信息形式 。在 MySQL 应用场景里，像学生信息管理系统中的学号、姓名，成绩统计里的分数，都属于待存储、分析的数据，是后续数据库操作的核心对象

数据库

• 存储数据的仓库，是长期存放在计算机内、有组织、可共享的大量数据的集合。数据库中的数据按照一定数据模型组织、描述和存储，具有较小的冗余度，较高的独立性和易扩展性，并为各种用户共享，总结为以下几点：

• 数据结构化 excel表 行 一条数据 一列一个属性（数据类型）

• 数据的共享性高，冗余度低，易扩充

• 数据独立性高

• 数据由 DBMS 统一管理和控制（安全性、完整性、并发控制、故障恢复）

  注：

  1. 物理独立性高
     数据的物理存储细节（如存储路径、文件格式、索引类型、分表分库方式等）的改变，不会影响应用程序。
     例如：将数据从 MyISAM 引擎迁移到 InnoDB 引擎，或调整索引为 B + 树 / 哈希索引，应用程序的 SQL 查询无需修改 ——MySQL 通过 “模式 - 内模式映射” 自动处理底层存储与逻辑结构的适配。
  2. 逻辑独立性较强
     当数据的逻辑结构（如表结构、字段增减、表间关系）发生变化时，可通过视图（View）等机制隔离影响，减少对应用程序的改动。
     例如：在用户表中新增 “邮箱” 字段，只要应用程序查询依赖的视图（如仅包含 “ID”“姓名”）未变，原有查询语句可正常运行 —— 通过 “外模式 - 模式映射” 实现逻辑结构与应用的解耦。

常见数据库

关系型数据库

关系型数据库（RDBMS）是基于关系模型（二维表格结构）的数据库，核心特点是：

• 数据结构固定：以表为单位，表由行列组成，字段定义严格（如数据类型、长度），表与表通过外键建立关联，形成严谨的关系模型。
• 强事务支持：遵循 ACID 特性（原子性、一致性、隔离性、持久性），确保数据操作的准确性和完整性，适合需要严格事务的场景（如金融交易、订单管理）。
• 标准化查询：使用 SQL（结构化查询语言）统一操作数据，支持复杂查询（如多表关联、聚合分析）。
• 典型代表：MySQL、Oracle、SQL Server、PostgreSQL、浪潮K-DB 、武汉达梦、南大通用、人大金仓、华为高斯等。

非关系型数据库（NoSQL）

非关系型数据库是为解决海量数据、高并发、灵活结构等需求而设计，核心特点是：

• 数据结构灵活：无固定表结构，支持键值对（Redis）、文档（MongoDB）、列族（HBase）、图形（Neo4j）等多种模型，可按需动态调整数据格式。

• 侧重可用性与扩展性：遵循 BASE 理论（基本可用、软状态、最终一致性），牺牲部分强一致性以换取高吞吐量和横向扩展能力（通过增加节点扩容）。

• 查询方式多样：无统一查询语言，多通过 API 操作，适合简单查询，复杂逻辑需在应用层实现。

• 典型代表：Redis（缓存、计数）、MongoDB（文档存储）、HBase（海量数据）、Neo4j（图形关系）。

  类型	数据模型	核心特点	代表产品	典型应用场景
  键值存储	键（Key）与值（Value）对应	简单高效，查询速度快，适合简单数据映射	Redis、Memcached、Riak	缓存、会话存储、计数器
  文档存储	类似 JSON 的文档格式	支持嵌套结构，查询灵活，适合半结构化数据	MongoDB、CouchDB	内容管理（博客、电商商品描述）、日志存储
  列族存储	按列族（Column Family）组织	擅长读取大量数据，适合数据分析和历史数据存储	HBase、Cassandra	大数据分析、时序数据（如传感器数据）
  图形数据库	节点（Node）和边（Edge）	专注于存储实体间的关系，适合复杂关联查询	Neo4j、JanusGraph	社交网络关系、推荐系统、知识图谱

架构

• 网络连接层：基于 TCP/IP 协议建立客户端与服务器连接，验证用户身份权限，借助连接池管理线程，控制连接数量，可用 show processlist 查看连接状态，负责连接建立与资源分配 。
• 数据库服务层：处理核心逻辑，SQL 接口接收操作命令，解析器校验 SQL 语法语义，优化器选最优执行计划，缓存与缓冲区用内存暂存数据减磁盘 IO，还实现存储过程等跨引擎功能 。
• 存储引擎层：作为可插拔的执行部件，不同引擎（如 InnoDB、MyISAM ）依特性读写数据，InnoDB 适配高并发，MyISAM 适合读多写少，对接服务层计划并操作磁盘文件 。
• 文件系统层：依托磁盘存数据，含日志（记录操作、保障恢复 ）、数据（存储表及索引等 ）、配置文件，实现数据持久化，借日志支持追溯与故障恢复

二、MySQL安装部署

Windows安装MySql8.0

三、数据类型

整数类型

浮点数类型和定点数类型

浮点型：

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

• 定点型：decimal(size,d) ，作为字符串存储的 DOUBLE 类型，允许固定的小数点，由于float、double类型存在精度丢失问题，即写入数据库的数据未必是插入数据库的数据，而decimal无论写入数据中的数据是多少，都不会存在精度丢失问题，这就是要引入decimal类型的原因，decimal类型常见于银行系统、互联网金融系统等对小数点后的数字比较敏感的系统中，结论：float/double在db中存储的是近似值，而decimal则是以字符串形式进行保存

字符串类型

在MySQL中常用CHAR 和 VARCHAR 表示字符串。两者不同的是：VARCHAR存储可变长度的字符串。
char是固定长度字符串，其长度范围为0~255且与编码方式无关，无论字符实际长度是多少，都会按照指定长度存储，不够的用空格补足，char类型数据时会将结尾的所有空格处理掉

日期与时间类型

MySQL提供的表示日期和时间的数据类型分别是 ：YEAR、DATE、TIME、DATETIME 和 TIMESTAMP。下图列举了日期和时间数据类型所对应的字节数、取值范围、日期格式以及零值：

四、数据库、数据表的基本操作

数据库：create、drop、 alter、show
数据表：insert、delete、update、select
DDL【data definition language】 数据定义语言，用来维护存储数据的结构，代表指令: create, drop, alter
DML【data manipulation language】 数据操纵语言，用来对数据进行操作，代表指令： insert，delete，update
DML中又单独分了一个DQL，数据查询语言，代表指令： select
DCL【Data Control Language】 数据控制语言，主要负责权限管理和事务，代表指令： grant，revoke，commit

数据库的基本操作

[root@master ~]# mysql -uroot -pMysql@123
mysql> create database student;   		#创建数据库
Query OK, 1 row affected (0.00 sec)

mysql> show create database student;    #查看该数据库基本信息
+----------+-----------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                                   |
+----------+-----------------------------------------------------------------------------------------------------------------------------------+
| student  | CREATE DATABASE `student` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+-----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> drop database student;  	#删除数据库
Query OK, 0 rows affected (0.01 sec)

mysql> show databases;				#查询出MySQL中所有的数据库
+--------------------+
| Database           |
+--------------------+
| db1                |
| db3                |
| db4                |
| db5                |
| information_schema |
| mydb4_product      |
| mydb5_sales        |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
10 rows in set (0.00 sec)
mysql>  select user();   		 #查看当前用户
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

数据库的基本操作

数据库创建成功后可在该数据库中创建数据表(简称为表)存储数据。请注意：在操作数据表之前应使用“USE 数据库名;”指定操作是在哪个数据库中进行先关操作，否则会抛出“No database selected”错误。

mysql>  select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| db3                |
| db4                |
| db5                |
| information_schema |
| mydb4_product      |
| mydb5_sales        |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
10 rows in set (0.00 sec)
mysql> use db1
mysql> create table student1 (       #创建student1表
    -> id int ,
    -> name varchar(20),
    -> sex char(2),
    -> age int unsigned,
    -> score float,
    -> addr varchar(50));
ERROR 1050 (42S01): Table 'student1' already exists    #报错student1已存在
mysql> create table student10 ( id int , name varchar(20), sex char(2), agge int unsigned, score float, addr varchar(50));                              #修改为student10
Query OK, 0 rows affected (0.01 sec)

mysql> desc student10;                                 查看表结构
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int          | YES  |     | NULL    |       |
| name  | varchar(20)  | YES  |     | NULL    |       |
| sex   | char(2)      | YES  |     | NULL    |       |
| age   | int unsigned | YES  |     | NULL    |       |
| score | float        | YES  |     | NULL    |       |
| addr  | varchar(50)  | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> drop table student1;                            #·删除student1
Query OK, 0 rows affected (0.00 sec)

mysql> alter table student10 rename student1;          #将student10重命名为student1
Query OK, 0 rows affected (0.01 sec)

mysql> desc student1;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int          | YES  |     | NULL    |       |
| name  | varchar(20)  | YES  |     | NULL    |       |
| sex   | char(2)      | YES  |     | NULL    |       |
| age   | int unsigned | YES  |     | NULL    |       |
| score | float        | YES  |     | NULL    |       |
| addr  | varchar(50)  | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> alter table student1 add phone_num int after score;     #在score列之后插入phone——num列
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table student1 change sex gender char(2) ;        #修改sex列名为gender
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table student1 modify  gender varchar(20) ;		#修改gender数据类型
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table student1 modify  score int ;					#修改score数据类型为int
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table student1 drop addr;							#删除addr列
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student1;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id        | int          | YES  |     | NULL    |       |
| name      | varchar(20)  | YES  |     | NULL    |       |
| gender    | varchar(20)  | YES  |     | NULL    |       |
| age       | int unsigned | YES  |     | NULL    |       |
| score     | int          | YES  |     | NULL    |       |
| phone_num | int          | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> drop table student1;
Query OK, 0 rows affected (0.00 sec)

小结

# 新建表
create tables  表名(列名1 类型  ， 列名2  类型 ……);
# 查看表结构
desc  表名;
# 修改表名
alter  table  旧表名  rename  新表名;
rename table 旧表名 to 新表名;
# 表中添加新列
alter table 表名 add  新列名 列类型  [after|first] 列名;   （after为之后）
# 删除列
alter table 表名 DROP 列名;
# 修改列名
alter table  表名 change  旧列名  新列名 列类型;
# 修改列类型
alter  table  表名  modify  列名  列类型;
# 移动列
alter table 表名 modify  列名  列类型 after 某列;
# 删除表
drop table  if  exists  表名;