MySQL 架构、存储引擎、库表操作一站式掌握

⭐️所属专栏MySQL目录什么是数据库认识MySQLMySQL架构SQL分类存储引擎核心考点补充期末考试必记库操作数据库的创建字符集与校验规则三、数据库的修改数据库的删除数据库的备份与恢复查看数据库连接情况表操作表的创建表结构的查看表的修改表的删除什么是数据库文件也可以存储数据为什么还要有数据库虽然一般的文件也提供了数据存储的功能但是没有提供非常好的数据管理能力在用户角度。文件保存数据有以下几个缺点安全性问题不利于数据查询和管理不利于存储海量数据文件在程序中控制不方便数据库的存储介质有磁盘和内存它能更高效的管理数据。数据库一般指的是在磁盘或者内存中存储的特定结构组织的数据在磁盘上存储的一套数据库方案。数据库本质对数据内容存储的一套解决方案根据字段或要求得到想要的结果。建立数据库本质就是在Linux下建立一个目录。在数据库内建表本质就是在Linux下创建对应的文件。数据库本质也是文件只不过这些文件不由我们直接操作而是由数据库服务帮我们操作。认识MySQLMySQL 是一种开源的关系型数据库管理系统数据以表Table 的形式存储表由 行记录 和 列字段 组成通过主键、外键建立表间关联如一对一、一对多关系保证数据的一致性和完整性。mysql 是数据库服务的客户端mysqld 是数据库服务的服务器端。MySQL本质是一种基于CmysqlSmysqld模式的网络服务。登录MySQL初始基本都是免密码登录后面必须设置密码输入密码时不会回显。MySQL架构SQL分类DDLdata definition language数据定义语言用来维护存储数据的结构。create、drop、alter等DMLdata manipulation language数据操作语言用来对数据进行操作。insert、delete、update等DML中又单独分了一个DQL数据查询语言例如selectDCLData Control Language数据控制语言主要负责权限管理和事务。grant、revoke、commit等。存储引擎数据库管理系统如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法就是存储引擎。MySQL的核心就是插件式存储引擎支持多种存储引擎。InnoDB vs MyISAM 核心对比对比维度InnoDBMyISAM事务支持支持 ACID 事务、事务回滚 / 提交不支持事务锁机制行级锁粒度细高并发友好注查询时也会加意向锁表级锁粒度粗写操作阻塞全表外键约束支持外键不支持外键崩溃恢复支持崩溃恢复redo/undo 日志不支持崩溃后易数据损坏索引类型聚簇索引主键索引存储数据二级索引指向主键非聚簇索引索引和数据分开存储全文索引5.6 版本后支持原生支持全文索引存储结构数据 索引存储在.ibd 文件数据 (.MYD)、索引 (.MYI)、表结构 (.frm) 分开计数效率count(*)需全表扫描无专门计数存储count(*)极快单独存储表行数适用场景高并发、需事务 / 外键如电商订单、金融系统读多写少、无需事务如日志、报表查询数据安全高事务 崩溃恢复低无事务崩溃易丢数据核心考点补充期末考试必记库操作数据库的创建CREATEDATABASE[IFNOTEXISTS]库名[CHARACTERSET字符集][COLLATE校验规则];IF NOT EXISTS避免库已存在时报错CHARACTER SET指定字符集如utf8支持中文COLLATE指定字符集的校验规则如utf8_general_ci不区分大小写。简单创建create database db1;默认字符集utf8、校验规则utf8_general_ci指定字符集create database db2 charsetutf8;指定字符集校验规则create database db3 charsetutf8 collate utf8_general_ci;。字符集与校验规则查看系统默认字符集/校验规则showvariableslikecharacter_set_database;showvariableslikecollation_database;查看数据库支持的字符集/校验规则showcharset;showcollation;例如utf8_general_ci不区分大小写创建库时指定该规则后续表中字符串比较会忽略大小写。三、数据库的修改ALTERDATABASE库名[CHARACTERSET新字符集][COLLATE新校验规则];仅支持修改字符集和校验规则。alterdatabasemytestcharsetgbk;-- 将mytest的字符集改为gbk数据库的删除DROPDATABASE[IFEXISTS]库名;说明删除后库文件及内部表会被级联删除需谨慎操作。数据库的备份与恢复语法mysqldump -P端口 -u用户 -p密码-B库名备份文件路径;案例mysqldump-P3306-uroot-p123456-BmytestD:/mytest.sql;扩展备份单表mysqldump -u root -p 库名 表名 路径;备份多库mysqldump -u root -p -B 库1 库2 路径;。source 备份文件路径;-- 如source D:/mytest.sql;注意若备份时未加-B需先创建空库切换到该库后再执行source。查看数据库连接情况语法showprocesslist;作用查看当前连接MySQL的用户、状态可用于排查异常连接/入侵。表操作表的创建CREATETABLE表名(列名1数据类型,列名2数据类型[COMMENT注释])[characterset字符集][collate校验规则][engine存储引擎];字符集/校验规则默认继承数据库的配置COMMENT可给列加说明。createtableusers(idint,namevarchar(20)comment用户名,passwordchar(32)comment密码是32位的md5值,birthdaydatecomment生日)charactersetutf8engineMYISAM;表结构的查看语法desc 表名;示例desc users;展示列名、类型、是否允许空等信息表的修改支持对列、表名的修改常用操作新增列altertableusersaddassetsvarchar(100);-- 新增assets列修改列类型/长度altertableusersmodifynamevarchar(60);-- 将name长度改为60删除列altertableusersdroppassword;-- 删除password列数据会被删除修改表名altertableusersrenametoemployee;-- 表名改为employee修改列名altertableemployee change name xingmingvarchar(60);-- name改为xingming需指定类型表的删除语法DROP TABLE [IF EXISTS] 表名;示例drop table t1;说明删除表会同时删除表结构、数据和索引需谨慎操作。本篇文章的分享就到这里了如果您觉得在本文有所收获还请留下您的三连支持哦~