泰安招聘信息58同城_斗图在线制作网站_上海seo公司_厦门百度整站优化服务

MySQL 入门笔记

数据库基础操作与实践

---

文档结构导航

1. 数据库核心操作

1.1 库操作模板

-- 安全创建数据库
CREATE DATABASE IF NOT EXISTS school_db
DEFAULT CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;-- 删除数据库（危险操作！）
DROP DATABASE IF EXISTS temp_db;

1.2 表操作规范

-- 标准建表语句
CREATE TABLE courses (course_id INT UNSIGNED AUTO_INCREMENT COMMENT '课程ID',course_name VARCHAR(30) NOT NULL DEFAULT '未命名课程' COMMENT '课程名称',credit DECIMAL(3,1) UNSIGNED NOT NULL COMMENT '学分',duration TINYINT UNSIGNED NOT NULL COMMENT '学时',PRIMARY KEY (course_id),UNIQUE INDEX idx_course_name (course_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课程信息表';

最佳实践

• 始终指定存储引擎和字符集
• 使用COMMENT添加字段描述
• 主键推荐使用无符号整型

1.3 表结构维护

-- 增加字段示例
ALTER TABLE students
ADD COLUMN wechat VARCHAR(30) COMMENT '微信账号' AFTER email;-- 修改字段示例
ALTER TABLE teachers
MODIFY COLUMN phone VARCHAR(15) NOT NULL COMMENT '联系电话';

---

2. 数据操作语言 (DML)

2.1 插入操作模板

方式	语法示例	适用场景
全字段插入	INSERT INTO table VALUES (...)	需要插入所有字段时
定向插入	INSERT INTO table (col1, col2) VALUES (...)	字段可选时推荐方式
批量插入	INSERT INTO table VALUES (...), (...)	多数据插入效率优化

2.2 更新删除规范

-- 严格WHERE条件更新
UPDATE course_schedule 
SET status = '已取消' 
WHERE course_id = 1024 AND start_time > NOW();-- 安全删除操作
DELETE FROM temp_logs 
WHERE create_time < DATE_SUB(NOW(), INTERVAL 30 DAY);

危险操作警示

-- 禁用无条件的删除/更新
UPDATE users SET status = 0;  -- ❌ 错误示范
DELETE FROM logs;             -- ❌ 错误示范

---

3. 数据查询语言 (DQL)

3.1 查询模板示例

-- 多条件复合查询
SELECT stu.name AS '姓名',cls.class_name AS '班级',cou.course_name AS '课程'
FROM students stu
JOIN classes cls ON stu.class_id = cls.id
JOIN course_selection cou ON stu.id = cou.stu_id
WHERE cls.grade = '2023级'AND cou.score >= 60
ORDER BY cou.score DESC;

3.2 分页方案对比

分页方式	语法	优劣分析
LIMIT传统分页	LIMIT 0, 10	简单易用，大数据量性能差
Keyset分页	WHERE id > 100 LIMIT 10	高效性能，不支持跳页
窗口函数分页	ROW_NUMBER() OVER()	SQL2018+ 支持，功能最强大

---

4. 常用函数速查

4.1 日期处理示例

-- 计算学生年龄
SELECT name,birth_date,TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age
FROM students;-- 下学期开始日期计算
SELECT DATE_ADD(CURDATE(), INTERVAL 3 MONTH);

4.2 JSON处理函数

-- 解析学生扩展信息
SELECT stu_id,JSON_EXTRACT(ext_info, '$.emergency_contact') AS emergency
FROM student_info
WHERE JSON_CONTAINS(ext_info, '{"blood_type": "A"}');

---

5. 高级查询模式

5.1 CTE递归查询

WITH RECURSIVE org_tree AS (SELECT id, name, parent_idFROM departmentsWHERE id = 1  -- 顶级部门UNION ALLSELECT d.id, d.name, d.parent_idFROM departments dINNER JOIN org_tree ot ON d.parent_id = ot.id
)
SELECT * FROM org_tree;

5.2 窗口函数应用

SELECT department,name,salary,RANK() OVER(PARTITION BY department ORDER BY salary DESC) as dept_rank
FROM employees;

---

6. 事务控制与优化

6.1 事务保存点机制

START TRANSACTION;UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SAVEPOINT transfer_start;UPDATE accounts SET balance = balance + 100 WHERE id = 2;
IF @@ERROR_COUNT <> 0 THENROLLBACK TO transfer_start;
END IF;COMMIT;

6.2 锁机制对比

锁类型	粒度	冲突概率	性能影响
行级锁	细	低	内存占用高
表级锁	粗	高	影响吞吐量

---

7. 知识体系扩展

7.1 版本差异注意

-- MySQL 8.0+ 新特性示例
CREATE TABLE temporal_table (id INT PRIMARY KEY,sys_period_start DATETIME(6) GENERATED ALWAYS AS ROW START,sys_period_end DATETIME(6) GENERATED ALWAYS AS ROW END,PERIOD FOR SYSTEM_TIME(sys_period_start, sys_period_end)
) WITH SYSTEM VERSIONING;

7.2 性能优化技巧

-- 查询缓存查看
SHOW VARIABLES LIKE 'query_cache%';-- 执行计划分析
EXPLAIN ANALYZE SELECT * FROM large_table WHERE status = 'active';

---

8. 实战演练建议

1. 在线沙盒练习
   推荐使用 DB Fiddle 进行SQL实验

2. 可视化工具推荐

   • MySQL Workbench (官方工具)
   • DBeaver (开源多平台)

3. 学习路线推荐

/www.db-fiddle.com/) 进行SQL实验

2. 可视化工具推荐

   • MySQL Workbench (官方工具)
   • DBeaver (开源多平台)

3. 学习路线推荐