MySQL Operate

Posted Dec 9, 2018 Updated May 23, 2024

By Cc 16 min read

数据库（Database）

库阈值

MySQL 8.0 版本之前，一个数据库中最多可以有 65535 个表。因为采用 16 位最大值为 2^16 - 1 = 65535
MySQL 8.0 开始，采用 32 位整数来存储表的数量，因此最大值变成了 2^32 - 1

数据库规范

命名规范
- 使用有意义且描述性强的名称，避免使用无意义的缩写或简写。
- 使用小写字母命名数据库，表和列名，可以提高可移植性和跨平台兼容性。
- 使用下划线或驼峰命名法作为命名的一种风格，保持一致性
使用字符集
- 指定数据库的字符集和校对规则，确保支持所需的语言和字符集。
- 常用的字符集包括 UTF-8 和 UTF-8mb4，它们支持更广泛的字符范围，适用于多语言环境。
选择存储引擎
- 根据应用需求选择合适的存储引擎，如 InnoDB、MyISAM、Memory 等。
- InnoDB 是 MySQL 默认的存储引擎，支持事务和外键约束，适合大多数应用。
权限管理
- 对数据库的访问权限进行细化和管理，根据用户角色分配不同的权限。
- 避免使用 root 或具有过大权限的账户直接连接数据库。

数据库操作

远程登录数据库：

        
      
mysql -h 127.0.0.1 -P 3306 -u admin -p12345678

查询所有数据库：
1 SHOW DATABASES;

创建数据库，并设置字符集为 UTF-8：

        
      
CREATE DATABASE my_database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

查询数据库：
1 SHOW DATABASES;
选择数据库：
1 USE my_database;
查询当前数据库：
1 SELECT DATABASE();
删除数据库
1 DROP DATABASE my_database;

导出指定数据库（需要包含数据库密码）

        
      
mysqldump -h 127.0.0.1 -P 3306 -u admin -p12345678 my_database > backup.sql

导入指定数据库（需要包含数据库密码）

        
      
mysql -h 127.0.0.1 -P 3306 -u admin -p12345678 my_database < backup.sql

执行外部 SQL 文件

        
<!-- login sql server -->
SOURCE /path/to/your_sql_file.sql;

表（Table）

表阈值

5.7 版本以前的 InnoDB 存储引擎中，每个表的行数限制为约 5000 万行
5.7 版本以后的 InnoDB 存储引擎中，每个表的行数限制则提高到了约 2000 亿行
单表超过了 500 万行或者单表超过 2GB 建议分表

表基础操作

创建表并指定引擎和字符集

        
      
 CREATE TABLE my_table (
   id INT AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(50),
   age INT
 ) ENGINE=InnoDB CHARSET=utf8mb4 COMMENT 'this my_table test';

删除表：
- 完全删除数据库对象，包括数据、结构和元数据信息
1 DROP TABLE my_table;
- 快速删除表中的所有数据，但保留表的结构和元数据信息
1 TRUNCATE TABLE my_table;

修改表名：

        
      
ALTER TABLE old_table_name RENAME TO new_table_name;

修改表的字符集：

        
      
ALTER TABLE my_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

查询所有表：
1 SHOW TABLES;

查询某个表结构：

查询接单结构

        
      
DESCRIBE my_table;

查询 sql

        
      
SHOW CREATE TABLE my_table;

导出表数据

        
      
mysqldump -u your_username -p your_database_name your_table_name > output_file.sql

表索引

创建索引 (CREATE INDEX)：创建一个新的索引以加速查询。
1 CREATE INDEX index_name ON table_name (column_name);
删除索引 (DROP INDEX)：删除现有的索引。
1 DROP INDEX index_name ON table_name;
查看索引 (SHOW INDEX)：查看表格的索引信息。
1 SHOW INDEX FROM table_name;

修改索引 (ALTER TABLE)：修改现有的索引，如添加、删除或修改索引字段。

        
      
ALTER TABLE table_name ADD INDEX index_name (column_name);
ALTER TABLE table_name DROP INDEX index_name;
ALTER TABLE table_name MODIFY COLUMN column_name INT, ADD INDEX index_name (column_name);

重命名索引 (RENAME INDEX)：重命名现有的索引。

        
      
ALTER TABLE table_name RENAME INDEX old_index_name TO new_index_name;

使用索引提示 (USE INDEX)：指定查询使用的索引。
1 SELECT * FROM table_name USE INDEX (index_name);

事务操作

事务（Transaction）是一组数据库操作，被视为一个逻辑单元，要么全部执行成功，要么全部失败回滚，以保持数据的一致性和完整性。

开始事务 (BEGIN 或 START TRANSACTION)：开始一个新的事务。
1 2 3 BEGIN; -- 或者 START TRANSACTION;
提交事务 (COMMIT)：将事务中的所有操作永久保存到数据库。
1 COMMIT;
回滚事务 (ROLLBACK)：撤销事务中的所有操作，恢复到事务开始之前的状态。
1 ROLLBACK;
设置事务隔离级别 (SET TRANSACTION ISOLATION LEVEL)：设置当前会话的事务隔离级别。
1 SET TRANSACTION ISOLATION LEVEL isolation_level;

完整例子

简单使用

        
START TRANSACTION;

INSERT INTO table1 (column1, column2) VALUES (value1, value2);
UPDATE table2 SET column1 = new_value WHERE condition;
DELETE FROM table3 WHERE condition;

COMMIT;

表视图

什么是视图

视图（View）是数据库中的一种虚拟表格，它是基于一个或多个实际表格的查询结果动态生成的。视图本身并不存储数据，而是根据定义的查询语句在查询时动态生成结果集。

视图可以看作是一个存储在数据库中的预定义查询，它将复杂的查询逻辑封装起来，以简化对数据的访问和操作。通过视图，用户可以以简单的方式访问和操作数据库中的数据，而无需了解复杂的数据库结构或编写复杂的查询语句。
视图的主要优点包括：
1. 简化查询：通过视图，用户可以使用简单的 SELECT 语句从视图中检索数据，而无需编写复杂的查询语句。
2. 隐藏复杂性：视图可以隐藏底层表格的复杂性和结构，用户只需关注视图的接口即可。
3. 安全性：通过视图可以控制用户对数据库中数据的访问权限，可以限制用户只能访问特定的数据子集。
4. 重用性：视图可以被多个查询和应用程序重复使用，提高了代码的重用性和可维护性。

视图在数据库中广泛应用于各种场景，包括报表生成、数据分析、权限控制等。使用视图可以使数据库更加灵活、易于管理，并提高应用程序的性能和可扩展性。

视图操作

创建视图 (CREATE VIEW)：创建一个新的视图。

        
      
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

查询视图 (SELECT)：从视图中检索数据。
1 SELECT * FROM view_name;
更新视图 (UPDATE)：更新视图中的数据。在某些情况下，可以更新视图，但要注意视图的定义必须符合一定的规则。
1 2 3 UPDATE view_name SET column1 = value1 WHERE condition;
删除视图 (DROP VIEW)：删除现有的视图。
1 DROP VIEW view_name;
查看视图定义 (SHOW CREATE VIEW)：查看视图的定义。
1 SHOW CREATE VIEW view_name;

替换视图 (CREATE OR REPLACE VIEW)：如果视图已存在，则替换现有的视图。

        
      
CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

视图嵌套 (Nested Views)：可以在一个视图中引用另一个视图。
使用视图权限 (GRANT 和 REVOKE)：授予或撤销用户对视图的权限
完整例子

假设我们有一个表格 employees 存储员工信息，包含 employee_id、first_name 和 last_name 字段。我们要创建一个视图，显示员工的全名

init

        
      
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

INSERT INTO employees (employee_id, first_name, last_name)
VALUES (1, 'John', 'Doe'),
      (2, 'Jane', 'Smith'),
      (3, 'Alice', 'Johnson');

然后，我们创建一个视图来显示员工的全名

        
      
CREATE VIEW employee_names AS
SELECT employee_id, CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

现在，我们可以从 employee_names 视图中检索员工的全名：

        
      
SELECT * FROM employee_names;

结果

        
      
+-------------+--------------+
| employee_id | full_name    |
+-------------+--------------+
|           1 | John Doe     |
|           2 | Jane Smith   |
|           3 | Alice Johnson|
+-------------+--------------+

表字段操作

最后面添加字段：

        
      
ALTER TABLE table_name ADD column_name INT;

最前面添加字段：

        
      
ALTER TABLE table_name ADD column_name INT FIRST;

某个字段后面添加字段：

        
      
ALTER TABLE table_name ADD column_name INT AFTER existing_column_name;

删除字段：

        
      
ALTER TABLE table_name DROP COLUMN column_name;

修改字段名和类型：

        
      
ALTER TABLE table_name CHANGE old_column_name new_column_name INT;

修改字段类型和位置：

        
      
ALTER TABLE table_name MODIFY column_name VARCHAR(100) AFTER existing_column_name;

字段约束

主键约束（PRIMARY KEY）：

确保字段的唯一性，每个表只能有一个主键，并且主键字段不能为 NULL。

        
      
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) NOT NULL
);

唯一约束（UNIQUE）：

确保字段的唯一性，但允许字段为 NULL。

        
      
  CREATE TABLE products (
      product_id INT,
      product_name VARCHAR(100),
      UNIQUE (product_id)
  );

非空约束（NOT NULL）：

确保字段不允许为空值。

        
      
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE DEFAULT CURRENT_DATE
);

默认值约束（DEFAULT）：

指定字段的默认值，在插入数据时如果没有指定值，则自动使用默认值。

        
      
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    hire_date DATE DEFAULT '2024-01-01'
);

检查约束（CHECK）：

对字段的取值进行条件检查，确保满足指定的条件。

        
      
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    age INT CHECK (age >= 18),
    grade VARCHAR(2) CHECK (grade IN ('A', 'B', 'C', 'D', 'F'))
);

属性设置

数据类型 (Data Types)：指定字段可以存储的数据类型，如整数、字符、日期等。
NULL / NOT NULL：指定字段是否允许为空值。如果设置为 NOT NULL，则该字段在插入新行时必须有值。
默认值 (Default)：为字段指定默认值，在插入新行时如果未指定该字段的值，则将使用默认值。
主键 (Primary Key)：唯一标识表中的每一行，并确保其唯一性。
自增 (Auto Increment)：当插入新行时，自动为字段生成唯一的递增值。
唯一键 (Unique Key)：确保字段的值在表中是唯一的，但允许字段为 NULL。
外键 (Foreign Key)：建立表格之间的关系，确保参考另一个表中存在的值。
索引 (Index)：为字段创建索引，加快查询速度。
自定义约束 (Check Constraint)：定义字段值必须满足的条件。
自定义注释 (Comment)：为字段添加描述性注释，以便其他人理解字段的用途。

例子

依赖表

        
      
  CREATE TABLE departments (
      department_id INT AUTO_INCREMENT PRIMARY KEY
  );

完整例子

        
      
CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'Employee unique identifier',
    first_name VARCHAR(50) NOT NULL COMMENT 'Employee first name',
    last_name VARCHAR(50) NOT NULL COMMENT 'Employee last name',
    email VARCHAR(100) UNIQUE COMMENT 'Employee email address',
    hire_date DATE NOT NULL COMMENT 'Employee hire date',
    salary DECIMAL(10, 2) DEFAULT 0.00 COMMENT 'Employee salary',
    department_id INT COMMENT 'Foreign key referencing department table',
    CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments(department_id),
    INDEX idx_last_name (last_name)
) COMMENT 'Table to store employee information';

在这个例子中：

employee_id 是主键，使用 AUTO_INCREMENT 自增，有注释说明。
first_name 和 last_name 是必填字段，有注释说明。
email 是唯一字段，有注释说明。
hire_date 是必填字段，有注释说明。
salary 有默认值，默认为 0.00，有注释说明。
department_id 是外键，参考另一个表格中的值，有注释说明。
last_name 字段有索引，以提高查询效率。
整个表格有一个注释，说明了该表格的用途。

Dev, Database, Mysql

Database SQL

This post is licensed under CC BY 4.0 by the author.