MySQL基础操作与优化

## 连接与退出 MySQL

要连接到 MySQL 数据库，可以使用以下命令格式：

mysql -h 主机地址 -P 端口号 -u 用户名 -p 密码
例如: mysql -h 127.0.0.1 -P 3306 -u root -p

要退出 MySQL 客户端，只需输入 exit; 或者 quit;。

## 数据库操作

### 创建数据库

CREATE DATABASE 数据库名 [选项];
例如: CREATE DATABASE test DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

字符集和校对规则：
- 字符集：通常使用 utf8。
- 校对规则：
- _ci 结尾的表示不区分大小写。
- _cs 结尾的表示区分大小写。
- _bin 结尾的表示二进制编码比较。

### 查看数据库

SHOW DATABASES;

### 查看数据库创建语句

SHOW CREATE DATABASE 数据库名;

### 修改数据库选项

ALTER DATABASE 数据库名 [新选项];
例如: ALTER DATABASE test DEFAULT CHARACTER SET gbk;

### 删除数据库

DROP DATABASE 数据库名;

### 使用数据库

USE 数据库名;

## 表的操作

### 创建数据表

CREATE TABLE 表名 (
字段1 类型 [选项],
字段2 类型 [选项],
...
) [表选项信息];

例如: CREATE TABLE test (
  id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
  content VARCHAR(100) NOT NULL DEFAULT '' COMMENT '内容',
  time INT(10) NOT NULL DEFAULT 0 COMMENT '时间',
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='测试表';

语法解析：
- NOT NULL：不允许为空。
- AUTO_INCREMENT：自增字段，通常用于主键。
- PRIMARY KEY：定义主键。
- ENGINE：设置存储引擎。
- CHARSET：设置字符集。
- COMMENT：备注信息。

### 查看数据表

SHOW TABLES;

### 模糊查询数据表

SHOW TABLES LIKE '模糊查询表名%';

### 查看表结构

DESC 表名;

### 查看表创建语句

SHOW CREATE TABLE 表名;

### 删除数据表

DROP TABLE [IF EXISTS] 表名;
例如: DROP TABLE IF EXISTS test;

### 修改表名

ALTER TABLE 旧表名 RENAME TO 新表名;

### 修改表结构

#### 添加列

ALTER TABLE 表名 ADD 新列名 类型 [选项];
例如: ALTER TABLE test ADD name CHAR(10) NOT NULL DEFAULT '' COMMENT '名字';

#### 删除列

ALTER TABLE 表名 DROP COLUMN 列名;
例如: ALTER TABLE test DROP COLUMN content;

#### 修改列定义

ALTER TABLE 表名 MODIFY COLUMN 列名 新类型 [新选项];
例如: ALTER TABLE test MODIFY COLUMN name VARCHAR(100) NOT NULL DEFAULT 'admin' COMMENT '修改后名字';

#### 修改列顺序

- 将某列放在最前面：

ALTER TABLE 表名 MODIFY COLUMN 列名 类型 [选项] FIRST;
例如: ALTER TABLE test MODIFY COLUMN name VARCHAR(100) NOT NULL DEFAULT 'admin' COMMENT '最前面' FIRST;

- 将某列放在另一列后面：

ALTER TABLE 表名 MODIFY COLUMN 列名 类型 [选项] AFTER 另一列名;
例如: ALTER TABLE test MODIFY COLUMN name VARCHAR(100) NOT NULL DEFAULT 'admin' COMMENT 'time字段后面' AFTER time;

#### 重命名列

ALTER TABLE 表名 CHANGE 原列名 新列名 新类型 [新选项];
例如: ALTER TABLE test CHANGE name username VARCHAR(50) NOT NULL DEFAULT '' COMMENT '用户名字';

#### 修改表选项

ALTER TABLE 表名 [表选项信息];
例如: ALTER TABLE test ENGINE=MyISAM DEFAULT CHARSET=gbk;

## 数据操作

### 插入数据

INSERT INTO 表名 (字段列表) VALUES (值列表);
例如: INSERT INTO user (id, name, age) VALUES (1, 'admin_a', 50);
INSERT INTO user (name) VALUES ('admin_b');

### 查询数据

SELECT [字段列表] FROM 表名 [查询条件];
例如: SELECT * FROM user; -- 查询所有字段
SELECT name FROM user WHERE age > 0; -- 查询特定字段并加条件

### 删除数据

DELETE FROM 表名 [删除条件];
例如: DELETE FROM user WHERE age <1; -- 删除年龄小于1的数据

### 更新数据

UPDATE 表名 SET 字段1 = 新值1, 字段2 = 新值2 [更新条件];
例如: UPDATE user SET age = 100 WHERE name = 'admin_a';

## MySQL 数据类型

MySQL 支持三种主要的数据类型：数值型、字符串型和日期时间型。

### 数值型

包括整数类型（如 TINYINT, SMALLINT, INT, BIGINT）和浮点数类型（如 FLOAT, DOUBLE, DECIMAL）。

### 字符串型

包括定长字符串（如 CHAR）和变长字符串（如 VARCHAR），以及文本类型（如 TEXT, MEDIUMTEXT, LONGTEXT）。

### 日期时间型

包括 DATE, TIME, DATETIME, TIMESTAMP 和 YEAR。

## MySQL 列属性

MySQL 提供了多种列属性来约束和描述字段，包括：NULL, NOT NULL, DEFAULT, PRIMARY KEY, AUTO_INCREMENT, COMMENT。

### 空属性

NULL 和 NOT NULL 分别表示允许或不允许空值。

### 默认值

DEFAULT 设置默认值，通常与 NOT NULL 一起使用。

### 主键

PRIMARY KEY 是唯一的且不能为空，用来唯一标识表中的每一行。

### 自动增长

AUTO_INCREMENT 为每次插入记录时自动递增。

### 列描述

COMMENT 用于给字段添加注释。

## 索引

索引是一种特殊的文件结构，能够加速数据检索。常见的索引类型包括普通索引、唯一索引、主键索引、组合索引和全文索引。

### 创建索引

CREATE [UNIQUE] INDEX 索引名 ON 表名 (列名称 [排序]);
例如: CREATE UNIQUE INDEX number ON student(seat_number DESC);

### 删除索引

DROP INDEX 索引名 ON 表名;
例如: DROP INDEX number ON student;

## 外键

外键用于维护两个表之间的关系。被约束的表称为副表，引用的表称为主表。外键只能在 InnoDB 存储引擎中使用。

## 存储引擎

MySQL 支持多种存储引擎，常用的有 InnoDB 和 MyISAM。其中 InnoDB 支持事务、行级锁和外键，而 MyISAM 支持全文索引。

## 三范式

三范式是设计数据库表结构时遵循的规范化原则，分为第一范式、第二范式和第三范式，旨在消除冗余数据，确保数据的一致性和完整性。