MySQL 数据库基础操作详解

---

MySQL 数据库基础操作详解

MySQL 是一种常用的关系型数据库管理系统，广泛应用于 Web 开发、数据分析、企业信息管理等领域。本文将详细介绍 MySQL 数据库的基础操作，包括库的操作和表的操作，并提供了一些实用的 SQL 示例。

1. 基本概念

数据可以存储在文件中，但数据库更安全、便于管理查询和程序访问。数据库本质上是一套数据存储的解决方案，底层还是依托文件进行存储。

数据库通过网络的形式提供服务。用户通过客户端访问服务端，服务端对数据库进行增删查改的操作，再将结果返回给客户端。

mysql -h 127.0.0.1 -P 3306 -u root -p

MySQL中，创建数据库本质就是创建一个目录，创建表本质就是在目录中创建文件，增删查改也就是访问该文件。

MySQL是关系型数据库，数据存储是按行列存储的。通常把一行称为记录，一列称为属性。

最底层是磁盘文件系统，往上是存储引擎，存储引擎是系统文件的封装，帮助我们进行文件操作。其上还有分析器，对SQL语句分析翻译成文件操作。

SQL分类	解释
DDL数据定义语言	用来维护存储数据的结构，如：create、drop、alter
DML数据操纵语言	对数据进行操作增删查改，如：insert、delete、update、select
DCL数据控制语言	负责权限管理和事务管理，如：grant、revoke、commit

2. 库的操作

创建库

使用 CREATE DATABASE 语句创建数据库，可以指定字符集和校验规则。

create database if not exists `my_database` charset=utf8 collate=utf8_general_ci;

如果不指定字符集和校验规则使用默认规则

create database `my_database` 

展示库
使用 SHOW DATABASES 可以展示所有数据库，使用 SHOW CREATE DATABASE 可以查看创建数据库的语句。

show databases;  
show create database `my_database`;

删除库
使用 DROP DATABASE 可以删除数据库。

drop database  `my_database`;

使用库

使用 USE 命令可以切换当前数据库。

use `my_database`;

查看连接情况

show processlist;

存储规则和校验规则

show variables like 'character_set_database';
show variables like 'collation_database';
# 如果存储规则是utf8，校验规则就应是utf8_general_ci。校验规则表示如何看待数据库中的数据，二者应该对应。

备份数据库
使用 mysqldump 命令备份数据库。

mysqldump -P3306 -u root -p123456 -B `db_name` > backup_file

恢复数据库
使用 source 命令恢复数据库。

source backup_file;

3. 表的操作

创建表
使用 CREATE TABLE 创建表，可以指定字段、数据类型和约束。

create table [if not exists] `tb_name` (
	field1 datatype,
    field2 datatype,
	field3 datatype
) [character set 字符集 collate 校验规则 engine 存储引擎];

删除表
使用 DROP TABLE 删除表。

drop table `tb_name`;

查看所有表
使用 SHOW TABLES 查看所有表，使用 DESC 查看表结构。

show tables;
show create database `tb_name`; # 查看创建表的语句

查看表结构
使用 DESC 查看表结构

desc `tb_name`;

更改表
使用 ALTER TABLE 进行改名、添加列、删除列、修改列等操作。

• 改名

alter table `old_name` rename to `new_name`;

• 添加列

alter table `tb_name` add (`field_name2` datatype, ...) after `field_name1`

• 删除列

alter table `tb_name` drop (`field_name` datatype, ...)

• 修改列

alter table `tb_name` modify (`field_name` datatype, ...) # 修改列会覆盖列的原有属性

• 更改列名称

alter table `tb_name` change `old_name` `new_name` datatype; # 改列名也会覆盖列的原有属性

4. 数据操作

插入数据
使用 INSERT INTO 插入数据。

INSERT INTO `my_table` (`name`, `age`, `email`) VALUES ('Alice', 25, 'alice@example.com');

查询数据
使用 SELECT 查询数据。

SELECT * FROM `my_table` WHERE `age` > 30;

更新数据
使用 UPDATE 更新数据。

UPDATE `my_table` SET `email` = 'bob@example.com' WHERE `name` = 'Bob';

删除数据
使用 DELETE FROM 删除数据。

DELETE FROM `my_table` WHERE `age` < 18;

5. 示例

示例一：创建表和插入数据

假设我们要创建一个学生信息表，包含学生的学号、姓名、年龄和性别等信息。

CREATE TABLE IF NOT EXISTS `student` (
    `student_id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(50) NOT NULL,
    `age` INT,
    `gender` ENUM('Male', 'Female', 'Other')
) ENGINE=InnoDB CHARSET=utf8 COLLATE=utf8_general_ci;

INSERT INTO `student` (`name`, `age`, `gender`) VALUES ('张三', 20, 'Male');
INSERT INTO `student` (`name`, `age`, `gender`) VALUES ('李四', 22, 'Female');
INSERT INTO `student` (`name`, `age`, `gender`) VALUES ('王五', 21, 'Other');

示例二：查询数据

查询年龄大于 20 岁的学生信息。

SELECT * FROM `student` WHERE `age` > 20;

示例三：更新数据

将姓名为“张三”的学生信息的年龄更新为 22 岁。

UPDATE `student` SET `age` = 22 WHERE `name` = '张三';

示例四：删除数据

删除年龄小于 20 岁的学生信息。

DELETE FROM `student` WHERE `age` < 20;

通过以上示例，我们可以看到 MySQL 数据库的基本操作方式，包括创建表、插入数据、查询数据、更新数据和删除数据。这些操作是数据库开发中最基础、最常见的操作，掌握好这些操作对于开发者来说是非常重要的。