MySQL - 数据库基础 | 数据库操作 | 表操作

---

1、数据库基础

1.1为什么要有数据库

文件也有保存数据的能力，那么为什么还需要数据库呢？

文件保存数据有以下几个缺点：

1. 文件的安全性问题
2. 文件不利于数据查询和管理
3. 文件不利于存储海量数据
4. 文件在程序中控制不方便

为了解决上述问题，专家们设计出更加利于管理数据的东西——数据库，它能更有效的管理数据。

数据库储存介质

1. 磁盘
2. 内存

1.2主流的数据库

1. SQL Sever： 微软的产品，.Net程序员的最爱，中大型项目。
2. Oracle： 甲骨文产品，适合大型项目，复杂的业务逻辑，并发一般来说不如MySQL。
3. MySQL：世界上最受欢迎的数据库，属于甲骨文，并发性好，不适合做复杂的业务。主要用在电 商，SNS，论坛。对简单的SQL处理效果好。
4. PostgreSQL :加州大学伯克利分校计算机系开发的关系型数据库，不管是私用，商用，还是学术研 究使用，可以免费使用，修改和分发。
5. SQLite： 是一款轻型的数据库，是遵守ACID的关系型数据库管理系统，它包含在一个相对小的C库中。它的设计目标是嵌入式的，而且目前已经在很多嵌入式产品中使用了它，它占用资源非常的 低，在嵌入式设备中，可能只需要几百K的内存就够了。
6. H2： 是一个用Java开发的嵌入式数据库，它本身只是一个类库，可以直接嵌入到应用项目中。

1.3连接MySQL

mysql -h [hostname] -P [port] -u [username] -p

1. [hostname] 是MySQL服务器的地址（例如localhost）(默认是127.0.0.1，如果省略-h选项默认连接本地)。
2. [port]是MySQL服务器的端口号（默认是3306，如果省略-P选项，则使用默认端口）。
3. [username] 是你的MySQL用户名。
4. -p 选项会提示你输入密码。

如：root为登录用户名，忽略-h ，-P选项。

1.4服务器、数据库、表的关系

1. 所谓安装数据库服务器，只是在机器上安装了一个数据库管理系统程序，这个管理程序可以管理多 个数据库，一般开发人员会针对每一个应用创建一个数据库。
2. 为保存应用中实体的数据，一般会在数据库中创建多个表，以保存程序中实体的数据。

关系图：

1.5 MySQL框架

MySQL的整体架构可以分为几个主要层次，这些层次共同协作以提供数据库服务：

1. 客户端（网络连接层）： 提供与MySQL服务器建立连接的支持。客户端通过各自的API（如JDBC、ODBC等）与MySQL服务器进行通信。 处理连接请求，包括连接管理、用户认证和权限验证。MySQL服务器会维护一个连接池，以提高连接管理的效率。
2. 服务层（MySQL Server）： MySQL Server是数据库的核心部分，负责接收和处理来自客户端的SQL语句。 提供SQL接口，接受用户的SQL命令，并返回查询结果。 包含解析器（Parser），用于将SQL语句解析成可执行的内部数据结构（解析树）。 包含查询优化器（Optimizer），用于根据统计信息和算法生成最优的执行计划。 在早期版本中，还包含查询缓存（Cache），用于缓存查询结果以提高性能（但在MySQL 8.0及以后版本中已被移除）。
3. 存储引擎层：
   存储引擎是MySQL架构中最具特色的部分之一，它提供了数据的物理存储和访问机制。 MySQL支持多种存储引擎，如InnoDB、MyISAM、Memory等，每种存储引擎都有自己的特点和适用场景。 存储引擎负责实现数据的存储、检索、更新和删除等操作，并通过存储引擎API与MySQL Server进行交互。
4. 系统文件层：
   包含MySQL数据库的实际数据文件、日志文件以及相关的配置文件。
   数据文件和日志文件用于存储数据库的数据和事务日志，配置文件则用于设置MySQL服务器的运行参数。

1.6 SQL分类

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

1.7储存引擎

存储引擎是：数据库管理系统如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。MySQL的核心就是插件式存储引擎，支持多种存储引擎。
1.7.1查看引擎

show engines;

1.7.2储存引擎对比

2.数据库操作

在MySQL中，对于大多数SQL指令和关键字（如SELECT、INSERT、UPDATE等），MySQL服务器默认是不区分大小写的。这意味着你可以使用大写、小写或混合大小写来编写这些指令，它们都会被MySQL正确解析和执行。

2.1创建数据库

2.1.1语法

CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [,
create_specification] ...]
create_specification:
[DEFAULT] CHARACTER SET charset_name
[DEFAULT] COLLATE collation_name

1. [IF NOT EXISTS]是一个可选部分，用于防止在尝试创建已经存在的数据库时发生错误。如果数据库已经存在，并且你包含了IF NOTEXISTS，那么MySQL将不会创建数据库，也不会返回错误。
2. db_name是你想要创建的数据库的名称。
3. [create_specification [, create_specification] …]部分允许你指定一个或多个创建规范，每个规范之间用逗号分隔。这些规范可以包括：
   [DEFAULT] CHARACTER SET charset_name：设置数据库的默认字符集。字符集定义了数据库中可以存储的字符的集合。如果你不指定字符集，MySQL将使用默认字符集（这通常是utf8mb4，因为它支持完整的Unicode字符集）。
   [DEFAULT] COLLATE collation_name：设置数据库的默认校对规则。校对规则定义了字符的比较和排序方式。如果你指定了字符集但没有指定校对规则，MySQL将使用与该字符集关联的默认校对规则。

2.1.2使用

CREATE DATABASE IF NOT EXISTS mydatabase 
DEFAULT CHARACTER SET utf8mb3 
DEFAULT COLLATE utf8mb3_general_ci;

1. IF NOT EXISTS防止了如果mydatabase已经存在时发生错误。
2. DEFAULT CHARACTER SET utf8mb3设置了数据库的默认字符集为utf8mb3。
3. DEFAULT COLLATE utf8mb3_general_ci设置了数据库的默认校对规则为utf8mb3_general_ci，这是一种不区分大小写的校对规则。

2.1.3查看当前存在的数据库

SHOW DATABASES	

2.2字符集和校验规则

2.2.1查看系统默认的字符集和校验规则

show variables like 'character_set_database';	//字符集
show variables like 'collation_database';	//字符校验规则

2.2.2查看系统支持的字符集和校验规则

show charset;

2.2.3校验规则对数据库的影响

1. 字符集和校验规则不一致可能导致乱码，数据不一致的情况。
2. 是否区分大小写。

2.3删除数据库

2.3.1语法

DROP DATABASE [IF EXISTS] db_name;

IF EXISTS : 这个选项代表如果数据库存在就删除，不存在就不执行任何操作，如果不加时，数据库不存在就会报错。

2.4修改数据库

主要是对字符集的修改
2.4.1语法

ALTER DATABASE db_nameCHARACTER SET charset_nameCOLLATE collation_name;

1. db_name 是你想要更改的数据库的名称。
2. charset_name 是你想要设置为默认字符集的字符集名称。
3. collation_name 是与字符集相关联的校对规则名称。

2.4.2使用

ALTER DATABASE mydatabase CHARACTER SET gbk COLLATE gbk_chinese_ci;

2.4.3查看一个库的字符集和校验规则

SELECT SCHEMA_NAME,          -- 选择数据库的名称DEFAULT_CHARACTER_SET_NAME,  -- 选择数据库的默认字符集DEFAULT_COLLATION_NAME       -- 选择数据库的默认校验规则（校对规则）
FROM information_schema.SCHEMATA  -- 从information_schema数据库的SCHEMATA表中查询
WHERE SCHEMA_NAME = 'mydatabase';  -- 筛选条件：数据库名称必须为'mydatabase'

2.5备份与恢复

2.5.1备份
2.5.1.1语法

 mysqldump -P3306 -u root -p 密码 -B 数据库名 > 数据库备份存储的文件路径	//单个数据库mysqldump -u root -p -B 数据库名1 数据库名2 ... > 数据库存放路径	//多个数据库

2.5.1.2使用

mysqldump -P3306 -u root -p -B  d1 > ./d1.sql

2.5.2恢复
使用SOURCE命令执行备份文件中的SQL语句：

source 文件名.sql

2.6查看连接情况

show processlist

3.表的操作

3.1创建表

3.1.1语法

CREATE TABLE table_name (
field1 datatype comment 注释,
field2 datatype  comment 注释,
field3 datatype  comment 注释
) character set 字符集 collate 校验规则 engine 存储引擎;

1. field 表示列名。
2. datatype 表示列的类型。
3. character set 字符集，如果没有指定字符集，则以所在数据库的字符集为准。
4. collate 校验规则，如果没有指定校验规则，则以所在数据库的校验规则为准。
5. comment 后面跟的是注释。

3.1.2使用
3.1.2.1选择数据库

use 数据库名;//use helloworld;

3.1.2.2 创建表

create table student(-> id int->name varchar(32),->gender varchar(32)-> );

3.2查看表结构

desc 表名;

3.3添加数据

3.3.1语法

INSERT INTO users (字段名字) VALUES (填入的数据); 

users: 表名

3.3.2使用

insert into student(id,name,gender) values(1,'张三','男');

3.4查看表内容

3.4.1语法

SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[GROUP BY column1, column2, ...]
[HAVING condition]
[ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...]
[LIMIT number_of_rows];

1. SELECT： 这是SQL查询的起始部分，表明你希望从数据库中获取数据。 column1, column2, …：
   你可以指定一个或多个字段（列），这些字段是你希望从表中检索的数据。 使用 * 可以选择所有字段。
2. FROM table_name： 指出你想要查询的表名。
3. [WHERE condition]（可选）： 这是一个条件子句，用于过滤数据。只有满足条件的记录才会被选中。
   可以使用各种比较运算符（如 =, <>, >, <, >=, <=）、逻辑运算符（如 AND, OR, NOT）以及字符串函数等。
4. [GROUP BY column1, column2, …]（可选）： 这个子句用于将结果集中的记录按一个或多个列进行分组。通常与聚合函数（如 COUNT(), SUM(), AVG(), MAX(), MIN()）一起使用。
5. [HAVING condition]（可选）： 用于过滤分组后的数据。与 WHERE 不同，HAVING 是在分组后应用的。
6. [ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], …]（可选）： 用于对结果集进行排序。 ASC表示升序（默认），DESC 表示降序。
7. [LIMIT number_of_rows]（可选）： 限制返回的记录数。 常用于分页或当你只关心结果集的前几条记录时。

3.4.2举例

1. 查询 users 表中的所有字段：

SELECT * FROM users;

2. 查询 users 表中的 name 和 email 字段：

SELECT name, email FROM users;

3. 查询 users 表中年龄大于30的用户：

SELECT * FROM users WHERE age > 30;

4. 按 age 字段对 users 表中的记录进行升序排序：

SELECT * FROM users ORDER BY age ASC;

5. 查询 orders 表中每个客户的订单总数，并按订单总数降序排序：

SELECT customer_id, COUNT(*) AS total_orders
FROM orders
GROUP BY customer_id
ORDER BY total_orders DESC;

6. 查询 orders 表中订单总数超过10的客户：

SELECT customer_id, COUNT(*) AS total_orders
FROM orders
GROUP BY customer_id
HAVING total_orders > 10;

7. 查询 users 表中的前5条记录：

SELECT * FROM users LIMIT 5;

3.5修改表

3.5.1增加字段
3.5.1.1语法

ALTER TABLE tablename ADD (column datatype [DEFAULT expr][, column datatype]...);

1. ALTER TABLE tablename：指定要修改的表名。
2. ADD：表示要添加列到表中。
3. (column datatype [DEFAULT expr][, column datatype]…)：在括号中指定要添加的列及其数据类型。可以为每个列指定一个默认值（DEFAULTexpr），多个列之间用逗号分隔。

3.5.1.2使用
添加年龄字段

ALTER TABLE student ADD (age INT);

3.5.2修改属性
3.5.2.1语法

ALTER TABLE tablename MODIFY (column datatype [DEFAULT expr][, column datatype]...);

MODIFY：表示要修改表中已存在的列的数据类型或默认值。
其他部分与添加列的语法相同。

3.5.2.2使用
将性别字段的varchar(32)改为char

 ALTER TABLE student MODIFY gender char(10);

3.5.3删除字段
3.5.3.1语法

ALTER TABLE users DROP signup_date;

3.5.3.2使用
删除性别字段

ALTER TABLE student DROP gender;

3.5.4修改表名
3.5.4.1语法

alter table 原表名 rename to 目标表名;

3.5.4.2使用
将表名student修改为st

alter table student  rename to st;

3.5.5修改字段
3.5.5.1语法

alter table 表名 change 旧字段 新字段; --新字段需要完整定义

3.5.5.2使用
将name修改为xingming

alter table st change name xingming char(5);

3.6删除表

3.6.1语法

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...

1. DROPTABLE：这是SQL中用于删除表的基本命令。执行此命令后，指定的表以及表中的所有数据将从数据库中永久删除，且无法恢复（除非有备份）。
2. [TEMPORARY]：这是一个可选的关键字。如果指定了TEMPORARY，则命令将仅删除临时表。临时表是在当前会话（或连接）中创建的，仅对当前会话可见。当会话结束时，临时表会自动删除。如果不指定TEMPORARY，则命令将删除永久表。
3. [IF EXISTS]：这也是一个可选的关键字。如果指定了IF EXISTS，那么在尝试删除不存在的表时，SQL命令不会报错。如果未指定IF EXISTS且尝试删除的表不存在，SQL命令将返回一个错误。
4. tbl_name [, tbl_name]…：这里指定了要删除的表的名称。可以一次性删除多个表，表名之间用逗号分隔。

3.6.2使用
删除st表

drop table st；