Mysql基础知识点

1. 数据库基本概念· 数据库Database存储数据的容器。表Table由行和列组成类似 Excel 表。行Row一条记录。列Column字段定义数据类型。主键Primary Key唯一标识一行非空且唯一。外键Foreign Key关联另一张表的主键保证参照完整性。索引Index加速查询的数据结构常用 B 树。SQL结构化查询语言用于操作数据库。2. SQL 分类分类 说明 常用语句DDL 数据定义 CREATE、ALTER、DROP、TRUNCATEDML 数据操作 INSERT、UPDATE、DELETEDQL 数据查询 SELECTDCL 权限控制 GRANT、REVOKETCL 事务控制 COMMIT、ROLLBACK、SAVEPOINT3. 常用数据类型整数TINYINT、INT、BIGINT小数DECIMAL(m,d) 精确FLOAT/DOUBLE 近似字符串CHAR 定长、VARCHAR 变长、TEXT 大文本日期时间DATE、TIME、DATETIME、TIMESTAMP布尔MySQL 用 TINYINT(1) 表示枚举/集合ENUM、SET二进制BLOB金额建议用 DECIMAL避免浮点误差。4. 建表与约束sqlCREATE TABLE user (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50) NOT NULL,age INT DEFAULT 0,email VARCHAR(100) UNIQUE,dept_id INT,CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES dept(id));常见约束PRIMARY KEY 主键FOREIGN KEY 外键UNIQUE 唯一NOT NULL 非空DEFAULT 默认值AUTO_INCREMENT 自增CHECK 检查约束表操作sqlALTER TABLE user ADD COLUMN sex CHAR(1);DROP TABLE user;TRUNCATE TABLE user; -- 清空表不可回滚自增重置5. 增删改查插入sqlINSERT INTO user(name, age) VALUES (张三, 20);更新sqlUPDATE user SET age 21 WHERE id 1;删除sqlDELETE FROM user WHERE id 1;查询sqlSELECT id, name FROM userWHERE age 18ORDER BY id DESCLIMIT 10;查询关键字执行顺序大致FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT6. 查询进阶去重SELECT DISTINCT age FROM user;模糊查询LIKE %张%范围BETWEEN ... AND ...集合IN (1,2,3)空值IS NULL、IS NOT NULL逻辑AND、OR、NOT排序ORDER BY age ASC/DESC分页LIMIT offset, size7. 聚合函数与分组sqlSELECT dept_id, COUNT(*) AS num, AVG(age)FROM userGROUP BY dept_idHAVING num 5;常用聚合COUNT() 计数SUM() 求和AVG() 平均MAX() / MIN() 最大最小注意WHERE 过滤行不能直接用聚合函数。HAVING 过滤分组后的结果。8. 多表连接内连接只返回匹配行sqlSELECT u.name, d.nameFROM user uINNER JOIN dept d ON u.dept_id d.id;左连接左表全保留右连接右表全保留交叉连接笛卡尔积自连接表自己连接自己9. 子查询sqlSELECT * FROM userWHERE age (SELECT AVG(age) FROM user);常见形式IN / NOT INEXISTS / NOT EXISTS标量子查询派生表FROM (SELECT ...) t10. 索引作用加快查询但会增加写入和存储成本。类型主键索引唯一索引普通索引复合索引全文索引底层InnoDB 常用 B 树。最左前缀原则复合索引 (a,b,c) 通常能用于 a、a,b、a,b,c。索引失效常见原因对索引列使用函数隐式类型转换LIKE %xxOR 条件未全部命中索引不符合最左前缀查看执行计划sqlEXPLAIN SELECT * FROM user WHERE id 1;11. 事务事务保证一组操作要么全部成功要么全部失败。ACIDA 原子性不可分割C 一致性数据状态合法I 隔离性事务间互不干扰D 持久性提交后永久保存隔离级别级别 可能问题读未提交 脏读读已提交 不可重复读可重复读 幻读MySQL 默认串行化 性能最低常见问题脏读读到未提交数据不可重复读同一事务两次读结果不同幻读同一条件两次查询行数不同sqlSTART TRANSACTION;UPDATE account SET money money - 100 WHERE id 1;UPDATE account SET money money 100 WHERE id 2;COMMIT;-- 或 ROLLBACK;12. 视图视图是虚拟表保存查询逻辑。sqlCREATE VIEW v_user ASSELECT id, name FROM user WHERE age 18;优点简化查询、权限控制缺点复杂视图性能可能较差。13. 存储过程、函数、触发器存储过程一组 SQL 集合可带参数。函数有返回值可用于 SQL 表达式。触发器在 INSERT/UPDATE/DELETE 前后自动执行。基础了解即可业务中需注意可维护性。14. 存储引擎引擎 特点InnoDB 默认支持事务、外键、行锁、MVCCMyISAM 不支持事务表锁查询较快Memory 内存表速度快重启丢失15. 字符集推荐sqlCHARACTER SET utf8mb4COLLATE utf8mb4_general_ciutf8mb4 支持 Emoji 和完整 Unicode。16. 权限管理sqlCREATE USER test% IDENTIFIED BY 123456;GRANT SELECT, INSERT ON db.* TO test%;REVOKE INSERT ON db.* FROM test%;FLUSH PRIVILEGES;17. 备份与恢复逻辑备份mysqldumpbashmysqldump -u root -p dbname db.sqlmysql -u root -p dbname db.sql物理备份直接备份数据文件需配合工具。二进制日志 binlog 可用于恢复和主从复制。18. 常见函数字符串CONCAT、SUBSTRING、LENGTH、TRIM、REPLACE数值ROUND、CEIL、FLOOR、ABS日期NOW()、CURDATE()、DATE_FORMAT()、DATEDIFF()条件IF()、IFNULL()、CASE WHEN聚合COUNT、SUM、AVG、MAX、MIN19. 优化基础避免 SELECT *为 WHERE、JOIN、ORDER BY 常用列建索引使用 EXPLAIN 分析执行计划避免索引列运算和隐式转换分页大偏移优化基于主键或游标合理使用联合索引和最左前缀控制事务大小避免长事务开启慢查询日志定位问题 SQL20. 数据库设计基础三大范式1NF字段原子性2NF消除非主属性对主键的部分依赖3NF消除非主属性对主键的传递依赖实际设计常适度反范式提升查询性能。一对多外键放在多的一方。多对多使用中间表。