Windows使用mysql创建的数据库目录在哪使用mysql创建数据库的表

转载

IT剑客之家 2024-06-17 07:16:16

文章标签 数据库 oracle 主键自增字段 文章分类 MySQL 数据库

创建一一个员工表和一个部门表，员工表包含:员工ID、姓名、身份证号码、性别、生日(年月日)、部门ID；部门表包含:部门ID，部门名称.

创建员工表：

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for employee
-- ----------------------------
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee`  (
  `employee_id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `identification_number` char(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `sex` tinyint(1) NOT NULL,
  `birthday` date NOT NULL,
  `department_id` int(11) NOT NULL,
  PRIMARY KEY (`employee_id`) USING BTREE,
  INDEX `idx_name`(`employee_name`) USING BTREE,
  INDEX `idx_sex`(`sex`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

提出疑惑：

1. birthday字段的类型为什么不是datatime ？

使用 DATE 类型来存储生日是更为合适的选择，原因如下：

精确度: 生日通常只需要精确到年、月、日，不需要时、分、秒的精确度，因此使用 DATE 类型更符合需求。

节省空间: DATE 类型占用的存储空间比 DATETIME 类型少，因为它不包含时、分、秒的信息，这在大量数据存储时可以节省空间。

简化查询: 使用 DATE 类型存储生日可以简化查询操作，因为不需要考虑时、分、秒的影响，比如针对生日的日期范围查询会更简单。

语义清晰: 使用 DATE 类型能够更清晰地表达字段的含义，使得数据更易于理解和维护。

虽然 DATETIME 类型也可以存储生日信息，但是使用 DATE 类型更为适合，能够更好地满足需求并优化存储。

2. 身份证为啥要采用char？

使用 CHAR 类型来存储身份证号码并指定长度为18是一种有效的选择，这是因为身份证号码通常都是固定长度的字符串，长度为18是普遍的情况。使用 CHAR(18) 能够确保每个身份证号都占用固定的空间，不会因为存储不同长度的字符串而浪费空间。

创建部门表：

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for department
-- ----------------------------
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department`  (
  `department_id` int(11) NOT NULL AUTO_INCREMENT,
  `department_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`department_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

MySQL中的常见索引类型：

B-Tree 索引：这是最常见的索引类型，适用于范围查询、排序和连接操作。在 MySQL 中，普通的索引（没有特别指定类型的索引）通常就是 B-Tree 索引。

哈希索引：这种索引类型使用哈希表来加速数据的检索，适用于等值查询，但不支持范围查询、排序和连接操作。

全文本索引：用于支持全文搜索，可以在文本数据上进行高效的模糊匹配。

空间索引：用于空间数据类型（例如，存储地理位置信息的 POINT 类型），支持空间查询。

组合索引：将多个字段组合在一起形成索引，可以提高多字段查询的效率。

主键设计

在 MySQL 中，主键（Primary Key）是用来唯一标识表中每个记录的字段或一组字段。主键在数据库表中起到非常重要的作用，包括确保数据完整性、提高查询性能以及简化数据操作等方面。

1.自增ID

自增ID主键的优点是数据库自动为每条记录分配唯一的标识符，速度快且编号递增，有利于聚集型主键的顺序存储，提高检索效率。此外，它是数字型的，占用空间小，易于排序和传递。

然而，自增ID也存在一些缺点。首先，当系统与其他系统集成或进行数据导入时，可能会出现主键冲突，特别是在多个数据库间数据复制时。此外，如果其他系统的主键不是数字型，可能需要修改数据类型，影响相关表的修改。在数据缓冲模式下，预先填写主键和外键的值也可能存在困难。另外，维护全局的自增量值会在并发环境中导致加锁解锁操作，可能降低查询性能并成为并发瓶颈。

2.UUID

UUID（Universally Unique Identifier）是在一台机器上生成的数字，确保在同一时空中的所有机器都是唯一的。生成UUID的算法可能会使用诸如网卡MAC地址、IP地址、主机名、进程ID等信息以确保其独特性。

UUID主键的优点包括全局唯一性、安全性和可移植性。它能够确保生成的ID不仅在单个表中是唯一的，而且在整个数据库中也是唯一的，这在划分数据库时非常重要。此外，UUID的独立性使得程序可以在不同的数据库之间迁移而不受影响。

然而，UUID主键也存在一些缺点。首先，在使用InnoDB等聚集主键类型的引擎时，数据会根据主键进行排序。由于UUID的无序性，可能会导致InnoDB产生巨大的IO压力，影响性能。此外，UUID作为主键时长度较长，可能导致主键索引的KeyLength过大，影响基于内存的索引记录数量和命中率，进而影响数据库服务器的整体性能表现。