学习MySQL：简单的基础操作
引言
在现代软件开发中，数据库扮演着至关重要的角色。MySQL，作为一款开源的关系型数据库管理系统（RDBMS），因其高效性、灵活性和强大的社区支持而广受好评。本文将带你从MySQL的基础知识出发，逐步探索其高级应用，以期帮助你全面提升数据库技能。
基础操作：MySQL的入门指南
启动与连接
MySQL服务的管理可以通过操作系统的服务命令实现：
- 启动服务：
net start MySQL - 停止服务：
net stop MySQL要连接到MySQL服务器，可以使用命令行工具，并输入：
mysql -h主机名 -u用户名 -p密码
对于本机连接，命令简化为：
mysql -u用户名 -p
基本命令
以下是一些MySQL中的基本命令：
- 显示帮助信息：
?、\?、help 或 \h - 清除当前输入语句：
\c 或 clear - 设置语句分隔符：
delimiter 或 \d - 退出MySQL：
exit、quit 或 \q - 获取MySQL状态信息：
status 或 \s - 选择数据库：
use 库名获取帮助信息：
HELP;显示当前时间：
SELECT NOW();
数据库与数据表管理
创建与删除数据库
创建数据库的基本命令格式为：
CREATE DATABASE 库名;
如果需要在数据库不存在时才创建，可以使用：
CREATE DATABASE IF NOT EXISTS 库名;
删除数据库的命令为：
DROP DATABASE 库名;
加上条件判断，可以避免删除不存在的数据库：
DROP DATABASE IF EXISTS 库名;
-
创建一个新数据库：
CREATE DATABASE database_name;这个命令会创建一个名为
database_name的新数据库。 -
创建数据库并指定字符集和排序规则：
CREATE DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;这个命令创建了一个使用
utf8mb4字符集和utf8mb4_unicode_ci排序规则的数据库。 -
创建数据库如果它不存在：
CREATE DATABASE IF NOT EXISTS database_name;使用
IF NOT EXISTS可以避免在数据库已存在时执行创建操作。
删除数据库
-
删除一个数据库：
DROP DATABASE database_name;这个命令会删除名为
database_name的数据库。注意：这个操作会删除数据库中的所有表和数据，且无法恢复。 -
删除数据库如果存在：
DROP DATABASE IF EXISTS database_name;使用
IF EXISTS可以避免在数据库不存在时执行删除操作时报错。
注意事项
- 在执行数据库的创建或删除操作之前，确保你有足够的权限来执行这些操作。
- 删除数据库是不可逆的操作，它会永久移除数据库及其包含的所有数据。在执行删除操作之前，请确保已经备份了所有重要数据。
- 创建数据库时，可以根据需要指定字符集和排序规则，这对于支持国际化应用尤为重要。
使用这些基本的MySQL命令，你可以有效地管理你的数据库环境，创建适合你应用需求的数据库，并在必要时删除它们。
数据表的创建与修改
创建数据表的基本命令如下：
CREATE TABLE 表名( 列名1 数据类型 [列/行级别约束…], 列名2 数据类型 [列/行级别约束…], … );
使用ALTER TABLE命令可以修改数据表结构，例如修改字段：
ALTER TABLE 数据表名 CHANGE 旧字段名 新字段名 字段类型 [字段属性];
新增字段：
ALTER TABLE 数据表名 ADD [COLUMN] 新字段名 字段类型 [FIRST | AFTER 字段名];
删除字段：
ALTER TABLE 表名 DROP [COLUMN] 字段名;
-
创建新表：
CREATE TABLE table_name ( column1 datatype, column2 datatype, ... PRIMARY KEY (column1) );这个命令会创建一个新表
table_name，包含多个列，其中column1设为主键。 -
创建表并指定字符集：
CREATE TABLE table_name ( column1 datatype, ... ) CHARACTER SET = utf8mb4;创建新表时，可以指定表的字符集，
utf8mb4是常用的字符集，支持表情等4字节字符。
修改数据表
-
添加新列：
ALTER TABLE table_name ADD column_name datatype;向
table_name表中添加一个新列column_name。 -
修改列的数据类型：
ALTER TABLE table_name MODIFY column_name new_datatype;改变
table_name表中column_name列的数据类型为new_datatype。 -
更改列名：
ALTER TABLE table_name CHANGE old_column_name new_column_name datatype;将
table_name表中的old_column_name列名更改为new_column_name，同时可以指定新的数据类型。 -
删除列：
ALTER TABLE table_name DROP COLUMN column_name;从
table_name表中删除column_name列。 -
重命名表：
RENAME TABLE old_table_name TO new_table_name;将
old_table_name表重命名为new_table_name。 -
添加主键约束：
ALTER TABLE table_name ADD PRIMARY KEY (column_name);为
table_name表的column_name列添加主键约束。 -
删除主键约束：
ALTER TABLE table_name DROP PRIMARY KEY;删除
table_name表的主键约束。 -
添加外键约束：
ALTER TABLE child_table ADD CONSTRAINT fk_name FOREIGN KEY (child_column) REFERENCES parent_table (parent_column);向
child_table表的child_column列添加外键约束，引用parent_table表的parent_column列。 -
删除外键约束：
ALTER TABLE table_name DROP FOREIGN KEY fk_name;删除
table_name表中的名为fk_name的外键约束。 -
修改表的字符集：
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4;将
table_name表的字符集转换为utf8mb4。
注意事项
- 在修改数据表结构时，需要确保这些更改不会违反数据库的完整性约束。
- 在执行某些修改操作之前，可能需要考虑对现有数据的影响，必要时进行数据备份。
- 修改操作如
DROP COLUMN或DROP TABLE是不可逆的，请在执行前仔细检查。
数据操作：增删改查
插入数据
插入新记录的基本命令为：
INSERT INTO 表名(字段名1, 字段名2, …) VALUES(值1, 值2, …);
如果字段顺序和数量与表结构一致，可以使用简化格式：
INSERT INTO 表名 VALUES(值1, 值2, …);
-
插入完整的一行数据（指定列名和值）：
这个语句将向
table_name表的column1、column2和column3列中分别插入value1、value2和value3。 -
插入完整的一行数据（不指定列名，默认所有列）：
如果表中的列顺序和插入的值顺序一致，可以不指定列名，直接插入值。
插入多行数据
- 插入多行数据（一次性插入多行）：
这个语句一次性插入三行数据到
table_name表。
使用查询结果插入数据
- 插入查询结果作为新行：
这个语句将根据从
another_table表中查询的结果，插入新行到table_name表。
插入特殊值
-
插入当前时间戳（使用
NOW()或CURRENT_TIMESTAMP）：如果
created_at列的数据类型是时间戳或日期时间，可以使用NOW()或CURRENT_TIMESTAMP插入当前的日期和时间。 -
插入NULL值：
如果需要在某个列中插入NULL值，直接使用
NULL关键字。
插入并更新自动增长列
- 插入新行并让自动增长列自动更新： 如果表中有一个自动增长的主键列，插入新行时不需要指定该列的值，它会自动增长。
插入默认值
- 插入新行使用列的默认值： 如果只插入
column1的值，其他列将使用它们的默认值或NULL（如果没有默认值）。
插入数据后返回新行
- 插入新行并返回新行数据（如果支持）： 某些MySQL配置支持使用
RETURNING子句来获取新插入的行数据。
确保在插入数据之前，表已经存在，并且插入的数据类型与列的数据类型兼容。如果插入的数据违反了表的约束（如唯一性约束），MySQL将返回错误。
修改数据
使用UPDATE命令可以修改表中的数据：
UPDATE 表名 SET 字段名1 = 值1, 字段名2 = 值2 WHERE 条件;
基本更新操作
-
更新特定列的值：
UPDATE table_name SET column1 = new_value1 WHERE condition;这个命令会更新
table_name表中满足condition条件的记录的column1列的值为new_value1。 -
同时更新多个列：
UPDATE table_name SET column1 = new_value1, column2 = new_value2 WHERE condition;该命令会同时更新
column1和column2两列的值。
更新操作的高级用法
-
更新为其他表中的数据：
UPDATE table_name SET column1 = (SELECT another_column FROM another_table WHERE condition) WHERE condition;这个命令会从另一个表
another_table中选择满足条件的another_column的值来更新当前表的column1。 -
使用
JOIN更新数据：UPDATE table_name INNER JOIN another_table ON table_name.join_column = another_table.join_column SET table_name.column1 = another_table.another_column;通过
JOIN可以基于两个表之间的关系来更新数据。 -
更新记录并限制更新数量：
UPDATE table_name SET column1 = new_value1 WHERE condition LIMIT number;使用
LIMIT可以限制更新操作影响的记录数量。 -
基于比较更新列值：
UPDATE table_name SET column1 = column1 + 1 WHERE condition;这个命令会将满足条件的记录的
column1列的值增加1。 -
使用
CASE语句更新数据：UPDATE table_name SET column1 = CASE WHEN condition1 THEN value1 WHEN condition2 THEN value2 ELSE default_value END WHERE condition;使用
CASE语句可以根据不同的条件更新不同的值。 -
更新NULL值：
UPDATE table_name SET column1 = COALESCE(column1, 'default_value') WHERE column1 IS NULL;这个命令会将
column1列为NULL的记录更新为'default_value'。
更新操作的注意事项
-
更新前备份： 在执行更新操作之前，建议先备份数据库或相关表，以防更新错误导致数据丢失。
-
使用事务确保更新操作的原子性：
START TRANSACTION; UPDATE table_name SET column1 = new_value1 WHERE condition; -- 检查是否有错误或其他条件 COMMIT;将更新操作放在事务中可以确保操作的原子性，如果出现错误可以回滚事务。
-
避免更新主键： 通常不建议更新主键列的值，因为这可能会导致数据一致性问题。
确保在使用UPDATE语句时，你的WHERE子句准确无误，以避免错误地更新了不打算更新的记录。如果需要，可以先使用SELECT语句和相同的WHERE子句来检查将要更新的记录。
删除数据
使用DELETE FROM命令可以删除记录：
DELETE FROM 表名 WHERE 条件;
如果要清空表中的所有数据，可以使用TRUNCATE TABLE，这通常比DELETE更高效：
TRUNCATE TABLE 表名;
基本删除操作
-
删除表中的所有记录：
DELETE FROM table_name;这个命令会删除
table_name表中的所有数据，但不会删除表结构本身。 -
根据条件删除记录：
DELETE FROM table_name WHERE condition;这个命令会删除
table_name表中满足condition条件的所有记录。
删除操作的高级用法
-
限制删除的记录数：
DELETE FROM table_name WHERE condition;使用
LIMIT可以限制删除操作影响的记录数量，number是你想要删除的记录数。 -
使用多表删除（涉及多表的删除操作）：
DELETE t1 FROM t1 INNER JOIN t2 ON t1.id = t2.ref_id WHERE t1.condition;这个命令会根据连接的表
t1和t2以及条件t1.condition来删除t1中的记录。 -
删除重复记录：
DELETE t FROM table_name t WHERE t.id NOT IN ( SELECT MIN(id) FROM (SELECT * FROM table_name GROUP BY column_name) AS subquery );这个命令会删除
table_name表中column_name列值重复的记录，只保留每组重复值中的最小id记录。
删除操作的注意事项
-
删除外键约束引用的记录： 如果表中的数据被其他表通过外键约束引用，直接删除这些数据会违反外键约束，导致删除操作失败。需要先删除或更新引用这些数据的外键表中的记录。
-
删除操作前的备份： 在执行删除操作之前，建议先备份数据库或相关表，以防意外删除重要数据。
-
使用事务确保删除操作的原子性：
START TRANSACTION; DELETE FROM table_name WHERE condition;-- 检查是否有错误或其他条件 COMMIT;将删除操作放在事务中可以确保操作的原子性，如果出现错误可以回滚事务，避免数据丢失。
-
删除操作后更新自动增长的ID（如果需要）： 删除记录后，如果表的自动增长ID有间隔，可以使用
ALTER TABLE命令重置ID：ALTER TABLE table_name AUTO_INCREMENT = 1;这会将
table_name表的自动增长ID重置为1（或指定的数值）。
请在使用DELETE语句时格外小心，因为一旦执行，就无法恢复被删除的数据。始终确保你完全理解删除操作的影响，并在必要时进行适当的测试。
高级查询与数据聚合
单表查询
基本查询语句格式为：
SELECT 查什么 FROM 从哪里查;
完整的查询语句可以包含多个子句：
SELECT [distinct] 输出列 FROM 表名 [WHERE 条件] [GROUP BY 字段名 [HAVING 条件]] [ORDER BY 字段名 ASC|DESC] [LIMIT 记录数];
-
基本查询：
这个查询语句将从
table_name表中选择column1和column2两个字段。 -
条件查询：
这个查询将返回
table_name表中满足特定条件的记录。 -
限制查询结果数量：
-
使用
LIMIT可以限制查询结果的数量，number是你想要返回的记录数。 -
去重查询：
使用
DISTINCT关键字可以返回唯一不同的值。 -
排序查询结果：
使用
ORDER BY对结果进行排序，ASC表示升序，DESC表示降序。 -
聚合查询：
使用聚合函数可以对表中的列进行统计计算。
多表查询
内连接使用JOIN或INNER JOIN：
SELECT [distinct] 输出列 FROM 表名1 INNER JOIN 表名2 ON 表名2.公共列=表名1.公共列 …;
左外连接和右外连接分别使用LEFT JOIN和RIGHT JOIN。
-
内连接查询：
使用
INNER JOIN可以连接两个表，并返回匹配连接条件的记录。 -
左外连接查询：
LEFT JOIN将返回左表（table1）的所有记录，即使右表（table2）中没有匹配的记录。 -
右外连接查询：
-
全外连接查询（MySQL不支持全外连接，但可以通过以下方式模拟）：
这个查询将返回两个表中的所有记录，包括不匹配的记录。
-
交叉连接查询：
-
CROSS JOIN将返回两个表的笛卡尔积，每个表中的每一行都会与另一个表中的每一行组合。
子查询与合并结果集
子查询可以在WHERE子句中使用：
SELECT 主查询 WHERE 字段名 IN (SELECT 子查询);
合并结果集使用UNION：
SELECT 条件查询 UNION SELECT 条件查询;
-
子查询：
-
合并结果集：
使用
UNION ALL可以合并两个查询的结果集，UNION ALL会包含重复的记录。结语
MySQL作为一个功能强大的数据库工具，不仅涵盖了基础的数据存储和查询，还提供了复杂的数据分析和事务处理能力。对于软件开发者和数据库管理员而言，精通MySQL是一项宝贵的技能。本文提供了从基础到高级的MySQL操作指南，旨在帮助你更好地理解和运用MySQL，提升你的数据库管理能力
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
