一、MySQL概述

一、数据库知识总览二、数据库基本概念1、数据库Database数据库是存储和管理数据的容器由一组相关的表组成。一个 MySQL 实例可以管理多个数据库。2、数据库管理系统DBMS数据库管理系统是一种操纵和管理数据库的大型软件。它位于用户或应用程序与操作系统之间提供了一套完整的工具来创建、使用和维护数据库。三、核心组件与概念1、表Table表是数据的结构化存储形式由行记录和列字段组成类似于 Excel 表格。每个表具有唯一的名称并定义列的数据类型和约束。2、SQLStructured Query LanguageSQL 是与 MySQL 交互的标准语言用于查询、插入、更新和删除数据。主要分为DDL数据定义语言定义数据库结构如 CREATE、ALTER、DROP。DML数据操作语言操作数据如 SELECT、INSERT、UPDATE、DELETE。DCL数据控制语言控制访问权限如 GRANT、REVOKE。3、索引Index索引是提高查询效率的数据结构。常见类型包括索引类型特点用途主键索引唯一标识每行记录不允许 NULL 值确保数据行的唯一性是表的主键唯一索引确保列值唯一允许 NULL 值防止数据重复如用户邮箱、身份证号普通索引加速查询允许重复值和 NULL 值提高非唯一列的查询速度4、事务Transaction事务是一组操作遵循 ACID 原则原子性Atomicity事务中的所有操作要么全部完成要么全部回滚。一致性Consistency事务执行前后数据库从一个一致状态转换到另一个一致状态。隔离性Isolation并发执行的事务互不干扰。持久性Durability事务提交后对数据的修改是永久性的。5、存储引擎Storage EngineMySQL 支持多种存储引擎负责数据的存储、处理和检索。常见的有存储引擎特点适用场景InnoDB支持事务、行级锁和外键提供高可靠性需要事务支持、数据一致性要求高的应用如电商、金融MyISAM不支持事务但插入和查询速度快表级锁读密集型、对事务要求不高的场景如日志分析、数据仓库MEMORY数据存储在内存中速度极快重启后数据丢失临时数据存储、高速缓存6、视图View视图是基于 SQL 查询的虚拟表可用于简化复杂查询或隐藏敏感数据。-- 示例创建一个视图隐藏员工的薪资信息CREATEVIEWemployee_publicASSELECTid,name,department,hire_dateFROMemployees;7、触发器Trigger触发器是在特定事件如 INSERT、UPDATE、DELETE发生时自动执行的存储过程。-- 示例在插入新订单后自动更新库存CREATETRIGGERupdate_inventoryAFTERINSERTONordersFOR EACH ROWBEGINUPDATEproductsSETstockstock-NEW.quantityWHEREidNEW.product_id;END;8、存储过程Stored Procedure存储过程是预编译的 SQL 语句集合可重复调用能够减少网络传输并提高应用程序的性能和可维护性。-- 示例一个简单的存储过程根据用户ID获取订单总数DELIMITER//CREATEPROCEDUREGetOrderCount(INuserIdINT,OUTorderCountINT)BEGINSELECTCOUNT(*)INTOorderCountFROMordersWHEREuser_iduserId;END//DELIMITER;9、外键Foreign Key外键用于建立表与表之间的关联确保数据的引用完整性。-- 示例在 orders 表中创建外键关联 users 表ALTERTABLEordersADDCONSTRAINTfk_userFOREIGNKEY(user_id)REFERENCESusers(id)ONDELETECASCADE;四、数据库类型1、关系型数据库基于关系模型以二维表行和列的形式存储数据支持标准的 SQL 语言强调数据的一致性和完整性。代表产品主要特点MySQL开源、流行、性能优异适用于 Web 应用Oracle功能强大、稳定性高常用于大型企业级应用PostgreSQL开源、支持高级特性如 JSON、GIS扩展性强SQL Server微软出品与 .NET 生态集成紧密2、非关系型数据库不依赖固定的表结构采用灵活的数据模型适用于处理大规模、高并发和半结构化/非结构化数据。它进一步细分为键值Key-Value数据库以键值对形式存储读写速度极快常用于缓存。代表Redis。文档数据库以文档如 JSON 格式存储适合存储不规则数据。代表MongoDB。图数据库以节点和边的形式存储擅长处理复杂的关系网络。代表Neo4j。列族数据库以列族方式组织数据适合海量数据的分布式存储。代表HBase、Cassandra。五、MySQL数据类型1、数值类型 (Numeric Types)数值类型用于存储数字数据主要分为整数、浮点数和定点数。整数类型用于存储没有小数部分的数字。随着存储空间的增加可表示的数值范围也随之扩大。类型存储空间有符号范围无符号范围描述TINYINT1 字节-128 到 1270 到 255极小整数SMALLINT2 字节-32,768 到 32,7670 到 65,535小整数MEDIUMINT3 字节-8,388,608 到 8,388,6070 到 16,777,215中等大小整数INT / INTEGER4 字节-2,147,483,648 到 2,147,483,6470 到 4,294,967,295标准整数最常用BIGINT8 字节-9,223,372,036,854,775,808 到 9,223,372,036,854,775,8070 到 18,446,744,073,709,551,615大整数用于存储极大的数值如全局唯一ID浮点数类型用于存储带有小数部分的近似数值适合科学计算等对绝对精度要求不那么极端的场景。类型存储空间描述FLOAT4 字节单精度浮点数精度约 7 位有效数字DOUBLE8 字节双精度浮点数精度约 15 位有效数字定点数类型用于存储精确的小数值底层以字符串形式存储不会出现精度丢失。类型语法描述DECIMALDECIMAL(M, D)精确数值M 是总位数精度D 是小数位数标度。常用于金融、货币等对精度要求极高的字段。-- 示例创建表时使用不同的数值类型CREATETABLEproducts(idINTUNSIGNEDAUTO_INCREMENTPRIMARYKEY,-- 标准整数无符号自增主键priceDECIMAL(10,2),-- 精确价格总10位小数2位weightFLOAT,-- 近似重量stockSMALLINTUNSIGNED-- 无符号小整数表示库存);2、字符串类型 (String Types)字符串类型用于存储文本数据根据长度是否固定和存储方式的不同进行分类。定长字符串CHAR(M)固定长度字符串。无论实际内容多长都会占用 M 个字符的空间。优点是检索速度快适合存储长度固定的数据如身份证号、MD5密码、性别代码。变长字符串VARCHAR(M)可变长度字符串。只占用实际字符长度加 1 或 2 个字节的额外空间。节省存储空间是日常开发中最常用的字符串类型如用户名、邮箱、地址。大文本类型TEXT用于存储长文本最大 65,535 字节。适合存储文章正文、长篇评论等。LONGTEXT用于存储超大文本最大 4GB。二进制字符串BLOB用于存储二进制大对象如图片、音频、视频文件等。类型最大长度描述CHAR(M)M 字符M 范围 0-255定长字符串不足部分用空格填充VARCHAR(M)M 字符M 范围 0-65,535变长字符串按实际长度存储TEXT65,535 字节长文本数据LONGTEXT4 GB超大文本数据BLOB65,535 字节二进制大对象-- 示例创建表时使用不同的字符串类型CREATETABLEusers(idINTPRIMARYKEY,usernameVARCHAR(50)NOTNULL,-- 变长用户名id_cardCHAR(18),-- 定长身份证号bioTEXT,-- 长文本个人简介avatarBLOB-- 二进制存储头像);3、日期和时间类型 (Date and Time Types)用于记录时间相关的数据。日期类型DATE仅表示日期格式为 YYYY-MM-DD。时间类型TIME仅表示时间格式为 HH:MM:SS。日期时间类型DATETIME表示日期和时间格式为 YYYY-MM-DD HH:MM:SS。占用 8 字节与时区无关。TIMESTAMP表示时间戳格式同 DATETIME但占用 4 字节。其值会根据时区进行转换且默认值通常与系统当前时间绑定适合记录数据的创建或更新时间。年份类型YEAR仅表示年份格式为 YYYY。类型格式范围描述DATEYYYY-MM-DD‘1000-01-01’ 到 ‘9999-12-31’日期TIMEHH:MM:SS‘-838:59:59’ 到 ‘838:59:59’时间DATETIMEYYYY-MM-DD HH:MM:SS‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’日期和时间与时区无关TIMESTAMPYYYY-MM-DD HH:MM:SS‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-19 03:14:07’ UTC时间戳受时区影响YEARYYYY1901 到 2155年份-- 示例创建表时使用日期时间类型CREATETABLEorders(order_idINTPRIMARYKEY,order_dateDATE,-- 订单日期delivery_timeTIME,-- 预计送达时间created_atTIMESTAMPDEFAULTCURRENT_TIMESTAMP,-- 创建时间自动记录updated_atTIMESTAMPDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,-- 更新时间自动更新warranty_yearYEAR-- 保修年份);